Alternative Way of Deleting an Incomplete Purchase Order

Alternative Way of Deleting an Incomplete Purchase Order
************************************************************

Before approving the Purchase Order & when the PO is in INCOMPLETE status, Oracle allows us to delete the same from the Purchase Order Form. The same can be achieved using the following mentioned API.

But we need to remember few points before using this API for deleting Incomplete PO.

- The PO Should be INCOMPLETE & Should not be Approved Once.
- Once the PO is approved, records gets inserted into mtl_supply & all the archive tables.
This API never deletes the records from the mtl_supply & archive tables and only deletes the
records from the base tables.

-- Sample Script to Delete Incomplete PO Using 'PO_HEADERS_SV1'

--=================================================

DECLARE

l_deleted BOOLEAN;

CURSOR c_po_det IS

SELECT po_header_id, segment1,org_id
FROM po.po_headers_all pha
WHERE 1 = 1
AND segment1 = '11170002356'
AND org_id = 308
AND NVL (approved_flag, 'N') <> 'Y'
AND NVL (cancel_flag, 'N') = 'N'
AND type_lookup_code = 'STANDARD'
AND authorization_status = 'INCOMPLETE'
AND NOT EXISTS ( SELECT 1
FROM po_headers_archive_all a
WHERE pha.po_header_id = a.po_header_id
AND pha.org_id = a.org_id)
AND NOT EXISTS ( SELECT 1
FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id AND
supply_type_code = 'PO');

BEGIN

FOR c1 IN c_po_det LOOP

DBMS_OUTPUT.put_line ( 'Calling PO_HEADERS_SV1 API To Delete PO');
DBMS_OUTPUT.put_line ( '==========================');

l_deleted := po_headers_sv1.delete_po (c1.po_header_id, 'STANDARD');

IF l_deleted = TRUE
THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');
END IF;
END LOOP;

END;