Script to Approve PO Requisition

/* Formatted on 2010/05/15 11:23 (Formatter Plus v4.8.0) */
DECLARE
l_itemkey VARCHAR2 (200);
l_po_id NUMBER := :PO_HEADER_ID;
l_po_number VARCHAR2 (200) := :REQUISITION_NUMBER;
BEGIN
SELECT l_po_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO l_itemkey
FROM DUAL;

wf_engine.createprocess ('REQAPPRV',
l_itemkey,
'MAIN_REQAPPRV_PROCESS',
NULL,
:USER_NAME
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'RESPONSIBILITY_ID',
avalue => :RESPONSIBILITY_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'APPLICATION_ID',
avalue => :APPLICATION_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'USER_ID',
avalue => :USER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SUBMITTER_ID',
avalue => :BUYER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_ID',
avalue => l_po_id
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_NUMBER',
avalue => l_po_number
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'ORG_ID',
avalue => :ORG_ID
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_SUBTYPE',
avalue => 'PURCHASE'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE',
avalue => 'REQUISITION'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE_DISP',
avalue => 'Purchase Requisition'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS',
avalue => 'APPROVED'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS_DISP',
avalue => 'Approved'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SEND_CREATEPO_TO_BACKGROUND',
avalue => 'Y'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'INTERFACE_SOURCE_CODE',
avalue => 'PO_FORM'
);

DBMS_OUTPUT.put_line (l_itemkey);
wf_engine.startprocess ('REQAPPRV', l_itemkey);
COMMIT;
END;
/

Reset The PO

-- Reset_po.sql
set serveroutput on size 100000
DECLARE
CURSOR potoreset IS
SELECT wf_item_type, wf_item_key, po_header_id, segment1,
       revision_num, type_lookup_code
FROM po_headers_all
WHERE segment1 = '&po_number'
AND org_id = &org_id
AND authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
AND NVL(cancel_flag, 'N') = 'N'
AND NVL(closed_code, 'OPEN') != 'FINALLY_CLOSED';
CURSOR maxseq(id number, subtype po_action_history.object_sub_type_code%TYPE) IS
SELECT NVL(MAX(sequence_num), 0)
FROM   po_action_history
WHERE  object_type_code IN ('PO', 'PA')
AND    object_sub_type_code = subtype
AND    object_id = id
AND    action_code IS NULL;
CURSOR poaction(id number, subtype po_action_history.object_sub_type_code%TYPE) IS
SELECT NVL(MAX(sequence_num), 0)
FROM   po_action_history
WHERE  object_type_code IN ('PO', 'PA')
AND    object_sub_type_code = subtype
AND    object_id = id
AND    action_code = 'SUBMIT';
submitseq po_action_history.sequence_num%TYPE;
nullseq po_action_history.sequence_num%TYPE;
BEGIN
DBMS_OUTPUT.put_line('------------------------------------');
DBMS_OUTPUT.put_line('Data Manipulation Scripts Disclaimer');
DBMS_OUTPUT.put_line('------------------------------------');
DBMS_OUTPUT.put_line('As always please ask customer to run the scripts on their test instance first ');
DBMS_OUTPUT.put_line('before applying it on production.  Make sure the data is validated for ');
DBMS_OUTPUT.put_line('correctness and related functionality is verified after the script has been ');
DBMS_OUTPUT.put_line('run on a test instance. Customer is responsible to authenticate and verify ');
DBMS_OUTPUT.put_line('correctness of data manipulation scripts.');
FOR pos IN potoreset LOOP
      DBMS_OUTPUT.put_line('Processing '||pos.type_lookup_code
                            ||' PO Number: '
                            ||pos.segment1);
      DBMS_OUTPUT.put_line('......................................');
      DBMS_OUTPUT.put_line('Closing Notifications...');
      BEGIN
          UPDATE wf_notifications SET status = 'CANCELED'
          WHERE notification_id IN (
               SELECT ias.notification_id
               FROM wf_item_activity_statuses ias,
                    wf_notifications ntf
               WHERE ias.item_type = pos.wf_item_type
               AND ias.item_key  = pos.wf_item_key
               AND ntf.notification_id  = ias.notification_id)
          AND NVL(status, 'OPEN') = 'OPEN';
      EXCEPTION
        WHEN OTHERS THEN
             NULL;
      END;
      DBMS_OUTPUT.put_line('Aborting Workflow...');
      BEGIN
          WF_Engine.AbortProcess(pos.wf_item_type, pos.wf_item_key);
      EXCEPTION
        WHEN OTHERS THEN
             NULL;
      END;
      DBMS_OUTPUT.put_line('Updating PO Status...');
      UPDATE po_headers_all
      SET authorization_status = DECODE(pos.revision_num, 0, 'INCOMPLETE',
                                        'REQUIRES REAPPROVAL'),
          wf_item_type = NULL,
          wf_item_key = NULL,
          approved_flag = DECODE(pos.revision_num, 0, 'N', 'R')
      WHERE po_header_id = pos.po_header_id;
      OPEN  maxseq(pos.po_header_id, pos.type_lookup_code);
      FETCH maxseq INTO nullseq;
      CLOSE maxseq;
      OPEN  poaction(pos.po_header_id, pos.type_lookup_code);
      FETCH poaction INTO submitseq;
      CLOSE poaction;
      IF nullseq > submitseq THEN
        DBMS_OUTPUT.put_line('Deleting PO Action History...');
    DELETE FROM po_action_history
        WHERE object_id = pos.po_header_id
        AND  object_type_code IN ('PO', 'PA')
        AND object_sub_type_code = pos.type_lookup_code
        AND sequence_num >= submitseq;
      END IF;
      DBMS_OUTPUT.put_line('Done Processing.');
      DBMS_OUTPUT.put_line('................');
      DBMS_OUTPUT.put_line('Please issue commit, if no errors found.');
END LOOP;
END;