Thursday, November 7, 2013

Script to push SO Line status from FULFILLED to CLOSED


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.