Deleting customer orders
I have been asked "I have all of these customer orders that were done and not connected properly. I would like everything that is a Customer Order older than today (10/9/15) deleted."
First I thought 'no problen - change their status to cancelled'.
However, when I looked at act.customerOrderPharmacy, its status is only allowed to be 'IN_PROGRESS' or 'POSTED'.
So I wondered if things were set up with enough referential integrity that I could actually delete the orders using SQL.
Looking at the act_relationships file it has foreign keys for both the source and target acts with On Delete = Cascade set.
If I understand this correctly I can just delete all records in the act_relationships file where the arch_short_name is actRelationship.customerOrderItemPharmacy and the act with its id matching the source_id has status IN_PROGRESS and act_start_time < 2015-09-10. And magically all the order and order items will also be deleted.
However, the above does not allow for act_details and act_relationship_details. Hence I suspect that prior to deleting the act_relationship records one should first delete all the act_details and act_relationship_details records involved.
Have I got it correct?
Regards, Tim G
Re: Deleting customer orders
In general, there shouldn't be any pharmacy orders left hanging around. If there is, it may indicate a workflow issue, such as check-out being performed before all of the outstanding orders have completed.
But yes, you will need to delete act_relationship_details and act_details first.
Re: Deleting customer orders
Hi Tim,
Yes, but in my experience there are workflow issues that are not always avoidable (or not easily)...
Examples:
- Sometimes customer orders arrive for a patient that is not in OVPMS but is registered on our Cubex. For example some items are dispensed to a "Floor Stock" patient on occasion as we don't want to bill them. This order needs to be manually removed from OVPMS.
- Because Pharamcy Returns require exiting the workflow these are often not finalised. So remain in the Customer Order worklist. In our case we have just disallowed Returns, however would like to be able to allow this. There is a project that has been started to allow credits/returns in invoices, but until this gets up and running there will be Returns that are just not actioned because it requires exiting the workflow and there is no obvious benefit for the staff member to do it.
- Sometimes people just don't always follow the checkout process. This could be because of lack of training, casual staff members, human fallibility, etc. However it is unavoidable unless the way the software works makes this impossible.
So, looking at our list there is an entry for example where if you try to invoice it you get a the "Pharmacy Order (1169428) is related to a different invoice (1167507)"... Someone has done something abnormal in the workflow. However now the new staff member who does get this message doesn't know what this means/if it matters/how to fix it....
So, to some extent these will be inevitable. I feel they could be reduced by forcing people to follow a stricter workflow but this requires locking things down in a way not possible currently in OVPMS.