Friday, 17 March 2017

Purchase order getting stuck with "in process" Status

API to Cancel Workflow
STEP1:
DECLARE
   CURSOR c1
   IS
      SELECT item_type, item_key, process_name
        FROM hr_api_transactions hat
       WHERE     status = 'Y'
             AND process_name = 'HR_GENERIC_APPROVAL_PRC'
             AND item_key = '254954';
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         wf_engine.
          abortprocess (itemtype   => i.item_type,
                        itemkey    => i.item_key,
                        process    => i.process_name);

         COMMIT;
         DBMS_OUTPUT.put_line ('Item Key has been Aborted/Cancelled: ' || i.item_key);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.
             put_line ('Inner Exception: ' || SQLERRM || ' - ' || i.item_key);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

Step2: Query Purchase order data with correct po number:

 SELECT wf_item_key, wf_item_type, authorization_status,
org_id, last_update_date, segment1 po_no
FROM po_headers_all
WHERE authorization_status = ‘IN PROCESS’
AND org_id = 337
AND segment1 in (‘po_no’,’po_no2′)
ORDER BY last_update_date desc



Step3:
update purchase order authorizing status to " REQUIRES REAPPROVAL"

update po_headers_all
set AUTHORIZATION_STATUS='REQUIRES REAPPROVAL'
where AUTHORIZATION_STATUS='IN PROCESS'
and poh.segment1=PO_NUMBER>;
commit;

No comments:

Post a Comment