Overview
You have found orders in your reports that are over 10 years old that are stuck at "SEND" even though they should be in a state of "DEAD". You want to know why this is happening and change their status so they are not shown in the reports.
Solution
The reason these orders are stuck at "SEND" is because they are waiting for the payment to be completed. This may be due to a declined card or not additional reversal credits. Because these are very old orders, Prologic is not able to get the specific details that caused these orders to wait for the outstanding payment to be settled.
Progressing these orders may trigger the charging process. As such, the solution to this issue is to manually mark the orders despatches from SEND to DEAD.
To fix this issue, contact Prologic Support and provide the list of orders that need to be modified in an Excel file. Make sure to include at least the sor_num of the order.
<supportagent>It is recommended to create backup tables first before changing the data and drop the backup tables after the solution is confirmed. It is also recommended to import the list provided to a table (this table will also be dropped after the solution is confirmed). Refer to Import data from Excel to SQL Server for more information. In the following example, the list was imported into the sup.zd2321935_orders table.
-- get information about the orders that need to be DEAD
create table sup.zd2321935_to_dead as
select distinct i.sor_num, mail.get_order_status(i.sor_num) as order_state,
d.gtrans_num, d.gtrans_state
from gtrans_defs d inner join gtrans_items i on d.gtrans_num = i.gtrans_num
where i.sor_num in (
select sor_num from sup.zd2321935_orders)
and mail.get_order_status(i.sor_num) not in ( 'Delivered', 'Cancelled','Completed')
-- get orders where date is not in an open stock keeping period create table sup.zd2321935_company_calendar2 as
select distinct cc.* from (
select p.gtrans_num, p.gtrans_stamp from sup.zd2321935_to_dead d
inner join gtrans_defs p on d.gtrans_num = p.gtrans_num) a
inner join company_calendar cc on a.gtrans_stamp >= cc.period_start
and a.gtrans_stamp <= cc.period_end
where skp_open = 'N'
-- reopen the stock keeping period, modify the state to DEAD, and close stock keeping period update company_calendar set skp_open ='Y'
where wk_code in ( select wk_code from sup.zd2321935_company_calendar2 );
update gtrans_defs set gtrans_state = 'DEAD'
where gtrans_num in (select gtrans_num from sup.zd2321935_to_dead )
and gtrans_state = 'SEND';
update company_calendar set skp_open ='N'
where wk_code in ( select wk_code from sup.zd2321935_company_calendar2 );
</supportagent>
Testing
Once the orders have been modified, they should have a DEAD state. Additionally, these will not be displayed in your reports.
Comments
0 comments
Please sign in to leave a comment.