How to Delete 'INCOMPLETE' and 'APPROVED' Purchase Order (PO) using API in Oracle Apps EBS R12 - API to delete purchase Orders

How to Delete Purchase Order (PO) using API in Oracle Apps EBS R12 -  API to delete purchase Orders

/*-- API to delete purchase Orders
-- API "po_headers_sv1.delete_po" can be used to delete POs.

-- Parameters :
--            X_po_header_id     => PO Header ID
--            X_type_lookup_code => Type Lookup Code
            -- STANDARD/PLANNED/BLANKET/CONTRACT
--            p_skip_validation  => N/Y
-- p_skip_validation -> By default it is 'N', if it is 'Y', It checks the encumbered flag in po distribution table against the po header.
*/
/*
Note:- INCOMPLETE POs can be deleted from Purchase Order form itself.
       Though this API can delete Approved POs as well, it is recomended that use this API to delete only INCOMPLETE Pos,
       because APPROVED POs have entry in inventory and archive tables and this API does not delete the related entries from these tables.
       Records will be deleted only from base tables.
*/


DECLARE
   L_RESULT              BOOLEAN;
   L_PO_HEADER_ID        NUMBER;        -- := <PO_HEADER_ID> ;
   L_TYPE_LOOKUP_CODE    VARCHAR2(20);  -- := <LOOKUP_CODE>  ;
   L_VALIDATION          VARCHAR2(10) := 'N';  -- := <VALIDATION_FLAG> ;
 
   CURSOR CUR_PO_DEL
   IS
     
SELECT   PH.PO_HEADER_ID,
                 PH.TYPE_LOOKUP_CODE,
                 PH.SEGMENT1 PO_NUM,
                 PH.AUTHORIZATION_STATUS,
                 PH.CLOSED_CODE
            FROM PO_HEADERS_ALL PH
           WHERE PH.SEGMENT1 = :PO_NUM
             AND PH.AUTHORIZATION_STATUS = 'INCOMPLETE'
        ORDER BY PH.PO_HEADER_ID;
   
BEGIN
   DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||l_po_header_id);

   FOR CUR_REC IN CUR_PO_DEL
   LOOP
 
           L_RESULT := PO_HEADERS_SV1.DELETE_PO (X_PO_HEADER_ID     => CUR_REC.PO_HEADER_ID,
                                                 X_TYPE_LOOKUP_CODE => CUR_REC.TYPE_LOOKUP_CODE,
                                                 P_SKIP_VALIDATION  => L_VALIDATION);

           IF l_result = TRUE THEN
              COMMIT;
              DBMS_OUTPUT.put_line ( 'PO with header: '||CUR_REC.PO_HEADER_ID||',Deleted Successfully');
           ELSE
              ROLLBACK;
              DBMS_OUTPUT.put_line ( 'PO with header: '||CUR_REC.PO_HEADER_ID||',Failed to Delete');
           END IF;
           DBMS_OUTPUT.put_line ( 'Deletion Process Over');
 
   END LOOP;
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);
END;