Thursday, October 13, 2016

API Script for Oracle Order Header Close

DECLARE
l_header_id      VARCHAR2(100);
l_org_id         NUMBER;
l_count          NUMBER := 0;
l_cnt            NUMBER := 0;
l_result         VARCHAR2(30);
l_activity_label VARCHAR2(30);

-- Introduced the hint NO_UNNEST in the subquery, for the performance
CURSOR TO_CLOSE IS SELECT P.INSTANCE_LABEL, WAS.ITEM_KEY, H.ORDER_NUMBER, H.ORG_ID
                   FROM   WF_ITEM_ACTIVITY_STATUSES WAS,
                          WF_PROCESS_ACTIVITIES P,
                          OE_ORDER_HEADERS_ALL H
                   WHERE TO_NUMBER(WAS.ITEM_KEY) = H.HEADER_ID                   
                AND   H.org_id IN (363, 364, 365)
                   AND   WAS.PROCESS_ACTIVITY = P.INSTANCE_ID
                   AND   P.ACTIVITY_ITEM_TYPE = 'OEOH'
                   AND   P.ACTIVITY_NAME = 'CLOSE_WAIT_FOR_L'
                   AND   WAS.ACTIVITY_STATUS = 'NOTIFIED'
                   AND   WAS.ITEM_TYPE = 'OEOH'
                   AND   NOT EXISTS ( SELECT /*+ NO_UNNEST */ 1
                                      FROM   OE_ORDER_LINES_ALL
                                      WHERE  HEADER_ID = TO_NUMBER(WAS.ITEM_KEY)
                                      AND    OPEN_FLAG = 'Y');
CURSOR WF_ERRORS(P_ERROR_TYPE VARCHAR2) IS SELECT I.ITEM_KEY, I.ITEM_TYPE
                                           FROM   WF_ITEMS I
                                           WHERE  I.ITEM_TYPE = P_ERROR_TYPE
                                           AND    I.PARENT_ITEM_TYPE = 'OEOH'
                                           AND    I.PARENT_ITEM_KEY = l_header_id
                                           AND    I.END_DATE IS NULL
                                           FOR    UPDATE NOWAIT;
BEGIN
  FOR C IN TO_CLOSE LOOP
      BEGIN
         l_header_id := c.item_key;
         FOR E IN WF_ERRORS('WFERROR') LOOP
             BEGIN
                 WF_ENGINE.ABORTPROCESS(ITEMTYPE =>E.ITEM_TYPE, ITEMKEY=>E.ITEM_KEY);
             EXCEPTION WHEN OTHERS THEN
                       UPDATE WF_ITEMS
                       SET    END_DATE = SYSDATE
                       WHERE  CURRENT OF WF_ERRORS;
             END;
             WF_PURGE.ITEMS(ITEMTYPE =>E.ITEM_TYPE, ITEMKEY=>E.ITEM_KEY, DOCOMMIT=>FALSE, FORCE=>TRUE);
         END LOOP;
         FOR E IN WF_ERRORS('OMERROR') LOOP
             BEGIN
                 WF_ENGINE.ABORTPROCESS(ITEMTYPE =>E.ITEM_TYPE, ITEMKEY=>E.ITEM_KEY);
             EXCEPTION WHEN OTHERS THEN
                 UPDATE WF_ITEMS
                 SET    END_DATE = SYSDATE
                 WHERE  CURRENT OF WF_ERRORS;
             END;
             WF_PURGE.ITEMS(ITEMTYPE =>E.ITEM_TYPE, ITEMKEY=>E.ITEM_KEY, DOCOMMIT=>FALSE, FORCE=>TRUE);
         END LOOP;   
      BEGIN
         OE_STANDARD_WF.OEOH_SELECTOR
         (p_itemtype => 'OEOH'
         ,p_itemkey => c.item_key
         ,p_actid => 12345
         ,p_funcmode => 'SET_CTX'
         ,p_result => l_result
         );
      EXCEPTION WHEN NO_DATA_FOUND THEN
               FND_CLIENT_INFO.SET_ORG_CONTEXT(C.ORG_ID);
               FND_PROFILE.PUT('ORG_ID', TO_CHAR(C.ORG_ID));
      END;
      WF_ENGINE.HANDLEERROR('OEOH', C.ITEM_KEY, C.INSTANCE_LABEL, 'RETRY',NULL);     
      l_count := l_count + 1;  
    EXCEPTION WHEN OTHERS THEN
       dbms_output.put_line('sqlerror'||C.ITEM_KEY||sqlerrm);
    END;
    END LOOP;
  dbms_output.put_line('Total Orders:'||l_count);
END;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect