Scenario - Order is in Booked status but order lines are not populated in WSH (shipping) tables.
SET serveroutput ON
SET verify OFF
SPOOL om_14578684.lst;
DECLARE
l_result VARCHAR2(30);
l_db_name VARCHAR2(60);
l_line_id NUMBER;
l_file_name VARCHAR2(255);
CURSOR lines
IS
SELECT ool.line_id, ool.flow_status_code
FROM oe_order_lines_all ool
WHERE ool.header_id IN (435881) -- header_id
AND ool.open_flag = 'Y'
AND ool.booked_flag = 'Y'
AND NVL(ool.fulfilled_flag,'N') = 'N'
AND ool.flow_status_code = 'BOOKED'
AND Nvl(ool.shipping_interfaced_flag,'N') = 'N'
AND EXISTS (SELECT 1
FROM wf_items wfs
WHERE wfs.item_key = TO_CHAR(ool.line_id)
AND wfs.item_type = 'OEOL'
AND wfs.end_date IS NULL
) ;
BEGIN
Oe_debug_pub.debug_ON;
Oe_debug_pub.initialize;
Oe_debug_pub.setdebuglevel(5);
l_file_name := Oe_debug_pub.set_debug_mode('FILE');
Dbms_Output.put_line('DEBUG FILE IS located AT :' || l_file_name);
oe_debug_pub.ADD('DEBUG FILE IS located AT :' || l_file_name);
select name INTO l_db_name from V$DATABASE;
oe_debug_pub.ADD('Running for databse: ' || l_db_name);
oe_debug_pub.add('Processing line info ....');
FOR c IN lines
LOOP
l_line_id := TO_CHAR(c.line_id);
---
UPDATE oe_ordeR_lines_all
SET flow_status_code = 'AWAITING_SHIPPING',
last_updated_by = -1,
last_update_date = SYSDATE
WHERE line_id = c.line_id;
---
oe_debug_pub.add('Seting Context for line ' || c.line_id);
Oe_standard_wf.OEOL_Selector(p_itemtype => 'OEOL', p_itemkey => l_line_id, p_actid => 12345, p_funcmode => 'SET_CTX', p_result => l_result);
oe_debug_pub.add('Result: '||l_result );
---
oe_debug_pub.add('Calling handleerror for Line...');
--******* Set this flag to Y if Ship_line activity should be skipped without any checking
--OE_SHIPPING_WF.G_DEV_SKIP := 'Y';
wf_engine.HandleError('OEOL',l_line_id, 'SHIP_LINE', 'RETRY', NULL);
END LOOP;
Oe_debug_pub.debug_OFF;
NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' Error in the base script : '||sqlerrm);
oe_debug_pub.add('### Error in the base script : '||sqlerrm);
Oe_debug_pub.debug_OFF;
END;
/
spool OFF;
COMMIT;
No comments:
Post a Comment