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;