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;