Overview

You may have a notification from the warehouse that some orders have been canceled but when you check on HS0A, these orders are in the delivered state with no items listed. These orders may have 2 despatches, one in DEAD/WARN state and one in pick state. These orders need to be set to the canceled state.

Solution

In order to change the status of such canceled orders, please reach out to the Support team providing order ids and the support team will correct the records in the database for these orders.

<supportagent>

Note: The agent should have access to the customer's DB.

You can check the order records in the db with the following query:

select sor_num, sor_ref, mail.get_order_status(sor_num)
from sor_defs where sor_ref in (
'XXXX','YYYY'
)

where 'XXXX', 'YYYY' are order numbers provided by the customer. 

It is recommended to create backup tables first before changing the data and drop the backup tables after the solution is confirmed:

create table sup.zd2582489_gtrans_items_dsp as
select * from gtrans_items where 
sor_num in (
  select sor_num
  from sor_defs where sor_ref in (
  'XXXX','YYYY'
  )
)
and tot_item_qty <> 0 

create table sup.zd2582489_gtrans_defs_pick as
select *
from gtrans_defs where gtrans_num in (
select orig_dsp_num from sup.zd2582489_gtrans_items_dsp
)

create table sup.zd2582489_gtrans_item_pick as
select  * from gtrans_items where gtrans_num in (
  select orig_dsp_num from sup.zd2582489_gtrans_items_dsp
)

where 'XXXX', 'YYYY' are order numbers provided by the customer. 

Note: Please use unique names for backup tables each time since they are being used in the subsequent update query also.

Use the following SQL code to correct the orders' records by removing the pick line items and updating the despatch line item to have 0 quantity:

exec proliba.set_pick(true);
update gtrans_defs set hh_state = null where gtrans_num in (
  select gtrans_num from sup.zd2582489_gtrans_defs_pick
) and hh_state = 'P';
exec proliba.set_pick(false);

delete from gtrans_items where gtrans_num in (
select gtrans_num from sup.zd2582489_gtrans_item_pick 
)

update gtrans_items set qty4=null, qty8= null, tot_item_qty=0 where gtrans_num in (
  select gtrans_num from sup.zd2582489_gtrans_items_dsp
) and tot_item_qty <> 0 
commit

</supportagent>