Overview

You have the order that has two dispatches and one of them is dead with 0 quantities. 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

Find the actual order number and confirm the number or line items for that order.

select sor_num from sor_defs where sor_ref = '<n1>' 
select * from sor_items where sor_num = <sor_num acquired in previous query>

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.

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 0 quantity has been deleted, the other dispatch for the order will be able to be processed correctly and the order will move to a Complete state.