DECLARE
l_header_id NUMBER := 1192882;
l_result VARCHAR2(1000);
l_file_name VARCHAR2(1000);
CURSOR rows_to_fix
IS
SELECT l.line_id
FROM oe_order_lines_all l
WHERE l.header_id = l_header_id
AND l.open_flag = 'Y'
AND l.flow_status_code = 'FULFILLED'
AND Nvl(l.fulfilled_flag,'N') = 'Y'
AND l.fulfilled_quantity>0
AND EXISTS
(
SELECT 1
FROM wf_process_activities wfa,
wf_item_activity_statuses wfs
WHERE wfa.instance_id = wfs.process_activity
AND wfs.item_key = To_Char(l.line_id)
AND wfs.item_type = 'OEOL'
AND wfa.activity_name = 'FULFILL_LINE'
AND wfs.activity_status = 'NOTIFIED'
) ;
BEGIN
-- Setup debugging
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 log is located at: ' || Oe_debug_pub.g_dir || '/' ||Oe_debug_pub.g_file);
oe_debug_pub.ADD(' Looping the error lines..');
Dbms_Output.put_line(' Looping the error lines..');
FOR i IN rows_to_fix LOOP
BEGIN
oe_debug_pub.ADD(' Setting context values for line id: ' || i.line_id);
Dbms_Output.put_line(' Setting context values for line id: ' || i.line_id);
Oe_standard_wf.OEOL_Selector(p_itemtype => 'OEOL',
p_itemkey => to_char(i.line_id),
p_actid => 12345,
p_funcmode => 'SET_CTX',
p_result => l_result);
oe_debug_pub.ADD('Context values set for line ' || i.line_id);
Dbms_Output.put_line('Context values set for line ' || i.line_id);
oe_debug_pub.ADD('Result: '||l_result );
oe_debug_pub.ADD('Calling handleerror for Line...'||i.line_id);
Dbms_Output.put_line('Calling handleerror for Line...' || i.line_id);
wf_engine.handleerror('OEOL',to_char(i.line_id),'FULFILL_LINE','SKIP',NULL);
oe_debug_pub.ADD(' Skip done..');
Dbms_Output.Put_Line(' Skip done..');
EXCEPTION
WHEN Others THEN
oe_debug_pub.ADD('Error:'||i.line_id||' : '||SQLERRM);
Dbms_Output.Put_Line('Error:'||i.line_id||' : '||SQLERRM);
END;
END LOOP;
COMMIT;
oe_debug_pub.ADD('Script completed.');
Dbms_Output.Put_Line('Script completed.');
oe_debug_pub.debug_off;
END;
/
SPOOL OFF;
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.