Overview

The order is stuck as partial in hs0a. There are two dispatches created for that order, one is already done and the second one is dead. However, the order is still not processed to the completed status.

Solution

There could be many reasons why the order is stuck as partial. Please reach out to the Support team and provide the order(s) id(s) which you are experiencing this problem and the support team will investigate and get back to you with the root cause and solution.

<supportagent>

• The agent should have access to the DB.
• 'n1','n2', ... used in the SQL code are order ids provided by the customer.

First, run this SQL query to confirm that order(s) is(are) in the partial state:

select sor_num, sor_ref, mail.get_order_status(sor_num) from sor_Defs where sor_ref in ( 'n1','n2', ... )

To resolve this the agent should delete the excesses despatch using the following SQL code.

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.zd2888196_gtrans_items2 as
select i.* 
from gtrans_items i inner join gtrans_defs d on i.gtrans_num = d.gtrans_num
where sor_num in (
  select sor_num from sor_defs where sor_ref in ('n1','n2', ... )
) and d.gtrans_state = 'DEAD'

Created backup table sup.zd2888196_gtrans_items2 will have a list of the gtrans_num and sor_num that you will use in your next queries. Let's take for the next examples gtrans_num = 29823327 and sor_num = 4421938
Create Backup tables:

create table sup.zd2888196_gtrans_items2_pick as
select * from gtrans_items where gtrans_num = 29823327

create table sup.zd2888196_gtrans_defs2_pick as
select * from gtrans_defs where gtrans_num = 29823327

create table sup.zd2888196_gtrans_items2_desp as
select * from gtrans_items where sor_num = 4421938 and orig_dsp_num = 29823327

Removal:

delete from gtrans_items where (gtrans_num, gitem_num, sor_num, sitem_num) in (
select gtrans_num, gitem_num, sor_num, sitem_num from sup.zd2888196_gtrans_items2 
)
and sor_num in (
  select sor_num from sor_defs where sor_ref in ('n1','n2', ... )
);

Note:
In case any sor_num is in the PICKING state you will need additional steps to remove the pick and despatch:

exec proliba.set_pick(true);
update gtrans_defs set hh_state=null where gtrans_num=29823327;
exec proliba.set_pick(false);


delete from gtrans_items where gtrans_num = 29823327;
delete from gtrans_items where sor_num = 4421938 and orig_dsp_num = 29823327;

</supportagent>