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 0 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 Cancelled state.

Solution

To fix this issue it is necessary to cancel the orders as well as remove the related despatches and the attached picks. Please reach out to the Prologic Support team to request assistance on this matter. Make sure to include the sor_ref of all orders with this issue (Reference displayed on HS0A). 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.zd2801205_dsp_items as
select * from gtrans_items where sor_num in (
  select sor_num from sor_defs
  where sor_ref in ('<provided by the customer>')
)
and tot_item_qty <> 0

create table sup.zd2801205_pick_items as
select * from gtrans_items 
where gtrans_num in (
  select orig_dsp_num from gtrans_items where sor_num in (
    select sor_num from sor_defs
    where sor_ref in ('<provided by the customer>')
  ) and orig_dsp_num is not null 
);

create table sup.zd2801205_pick_defs as 
select hh_state from gtrans_defs where gtrans_num in (
  select orig_dsp_num from gtrans_items where sor_num in (
    select sor_num from sor_defs
    where sor_ref in ('<provided by the customer>')
  ) and orig_dsp_num is not null 
);

create table sup.zd2801205_dsp_defs as
select * from gtrans_defs where gtrans_num in (
  select distinct gtrans_num from gtrans_items where sor_num in (
    select sor_num from sor_defs
    where sor_ref in ('<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 queries to find the Despatch and linked Pick respectively (We have to find these in order to know what/which fields need to be updated).

Despatch Lines

select * from gtrans_items where sor_num = (select sor_num from sor_defs where sor_ref = 'n1')

Pick Lines

select * from gtrans_items where gtrans_num in (
     select orig_dsp_num from gtrans_items
     where sor_num = (select sor_num from sor_defs where sor_ref = 'n1' )
)

Then, use the following SQL code to correct the orders' records by updating the pick line items and removing the despatch line items:

exec proliba.set_pick(true);
update gtrans_defs set hh_state=null where gtrans_num in (
  select gtrans_num from sup.zd2801205_pick_items
);
exec proliba.set_pick(false);
delete from gtrans_items where gtrans_num in (
  select gtrans_num from sup.zd2801205_pick_items
);
delete  from gtrans_items where gtrans_num in (
 select gtrans_num from sup.zd2801205_dsp_items
);

</supportagent>

Testing

After this issue has been fixed, the order with 0 line items must have a Cancelled state instead of Delivered.