Overview
The scheduled task that creates an inventory file for your web partner failed as part of the usual queue, and now there is no stock in your online channel for web orders. This article describes the workaround that should be applied in this case.
Solution
There are multiple reasons why a scheduled task can fail. For example, if you tried running the failed task under the other queue but the file that was generated contains only header tags and running in debug level 6 is giving a message like this:
Stock Total file created as '/procims/CLP001_T_Inventory_Snapshot_20210105020559.xml' but size not determined.
The reason for the failure is the high number of records being processed by the task.
As a workaround, while the issue is investigated and fixed, we can manually populate the Online channel stock status in the DB directly in order to allow the consecutive process, such as exporting the file for third-party consumption can proceed with a better stock picture.
Please create a Customer Support ticket with the description of the issue and logs attached. A Customer Support agent will reach out to you with the solution or workaround.
<supportagent>
The agent needs access to the customer's DB.
Previously TM Lewin has faced this issue and the agent used the script below to populate pro.TMP_GAR_CHANNEL_STK table in DB. Use this script as a baseline, adjust if needed and execute in the customer's DB:
-- Populate the Channel back-up table with the stock currently sitting in the CLP001 location
-- for a select number of Stock Types (just those that are channelable and have stock)
-- The channel is hard-coded to ONLINE as this is the only channel that TM Lewin are surrently using
begin
for i in (select
loc_num,
season,
sty_num,
sty_qual,
bf_mat_char_val,
sty_size,
sum(tot_item_qty)"SUM_TOT",
sum(nvl(qty1,0))"SUM_QTY1",
sum(nvl(qty2,0))"SUM_QTY2",
sum(nvl(qty3,0))"SUM_QTY3",
sum(nvl(qty4,0))"SUM_QTY4",
sum(nvl(qty5,0))"SUM_QTY5",
sum(nvl(qty6,0))"SUM_QTY6",
sum(nvl(qty7,0))"SUM_QTY7",
sum(nvl(qty8,0))"SUM_QTY8",
sum(nvl(qty9,0))"SUM_QTY9",
sum(nvl(qty10,0))"SUM_QTY10",
sum(nvl(qty11,0))"SUM_QTY11",
sum(nvl(qty12,0))"SUM_QTY12"
from gar_stk a
where loc_num=90020
and gstock_type in ('CLP-PETE-STK','CLP-SHER-STK','GAR-CUST-ALLOC','GAR-RET-STOCK','RET-ALLOC-STOCK','RETAIL-STOCK')
group by loc_num,
season,
sty_num,
sty_qual,
bf_mat_char_val,
sty_size
)
LOOP
insert into tmp_gar_channel_stk_old
values( 'DD-MMM-YY', -- replace 'DD-MMM-YY' with the current date
'M',
i.season,
i.sty_num,
i.sty_qual,
i.bf_mat_char_val,
i.sty_size,
i.sum_tot,
i.sum_qty1,
i.sum_qty2,
i.sum_qty3,
i.sum_qty4,
i.sum_qty5,
i.sum_qty6,
i.sum_qty7,
i.sum_qty8,
i.sum_qty9,
i.sum_qty10,
i.sum_qty11,
i.sum_qty12,
i.loc_num
);
END LOOP;
END;
commit;
-- Now copy the back up table into the live table
begin
for i in (select
in_date,
channel_code,
season,
sty_num,
sty_qual,
bf_mat_char_val,
sty_size,
tot_qty,
qty1,
qty2,
qty3
qty4,
qty5,
qty6,
qty7,
qty8,
qty9,
qty10,
qty11,
qty12,
loc_num
from pro.tmp_gar_channel_stk_old a
)
LOOP
insert into pro.tmp_gar_channel_stk (in_date,
channel_code,
season,
sty_num,
sty_qual,
bf_mat_char_val,
sty_size,
tot_qty,
qty1,
qty2,
qty3,
qty4,
qty5,
qty6,
qty7,
qty8,
qty9,
qty10,
qty11,
qty12,
loc_num
)
values (i.in_date,
i.channel_code,
i.season,
i.sty_num,
i.sty_qual,
i.bf_mat_char_val,
i.sty_size,
i.tot_qty,
i.qty1,
i.qty2,
i.qty3,
i.qty4,
i.qty5,
i.qty6,
i.qty7,
i.qty8,
i.qty9,
i.qty10,
i.qty11,
i.qty12,
i.loc_num
);
END LOOP;
END;
commit;
If the second part of the script fails (copying the backup table into the live table), there are already records in the pro.tmp_gar_channel_stk table. When this happens, all existing records must be removed from pro.tmp_gar_channel_stk and from pro.tmp_gar_channel_stk_old.
delete
from pro.tmp_gar_channel_stk_old
where loc_num=90020
and channel_code='M'
;
delete
from pro.tmp_gar_channel_stk
where loc_num=90020
and channel_code='M'
;
After the records have been removed, re-run both scripts.
</supportagent>
Testing
After manually populating the online channel stock status in the DB directly, you should be able to process orders again and the inventory file should be generated normally during the next task run.
