Overview

You have an order that has two dispatches, one of them is Dead with 0 quantities and the other one is Done. As a result, an order that should be Complete is shown as Partial. You want to delete the 0 dispatch to be able to process the other dispatch with the correct information and complete the order.

Solution

Please reach out to the Support team and provide the order ID. The support team will remove the despatch line that has 0 quantity and remove the pick line that corresponds with the despatch directly in the DB.

<supportagent>

Notes: 

• The agent should have access to the customer's DB.
• n1 - order ID provided by the customer
• n2 - pick id that corresponds with the despatch

It is recommended to create backup tables first before changing the data and drop the backup tables after the solution is confirmed.  Use unique names for temporary tables so that they can be identified.

Use the following SQL code to delete the despatch line that has 0 quantity and remove the pick line that corresponds with the despatch:

Backup:

create table sup.zd2748464_gtrans_items_dsp as 
select * from gtrans_items where sor_num = (select sor_num from sor_defs where sor_ref = 'n1' ) and tot_item_qty = 0

Created backup table sup.zd2748464_gtrans_items_dsp will have the pick id (gtrans_num) that you will use in your next queries:

create table sup.zd2748464_gtrans_defs as 
select * from gtrans_defs where gtrans_num = n2

create table sup.zd2748464_gtrans_items as 
select * from gtrans_items where gtrans_num = n2

Removal:

delete from gtrans_items where gtrans_num = n2;
delete from gtrans_items where sor_num = (select sor_num from sor_defs where sor_ref = 'n1' ) and tot_item_qty = 0;
commit;

</supportagent>

Testing

Once the dispatch with quantity 0 has been removed, the order should be completed and a payment record for the items that have been shipped will be generated.