Tuesday, September 4, 2012

Order Booked Workflow not populated

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