Introduction
In the oracle EBS the concept of Purging and before purging the order management related tables, pre-requisite to be done for the smooth purging process. The below script will change the PO status as Finally Closed.
Why we need to do
To make the orders eligible to purge we need to have the below pre-requisite steps.
How do we solve
— Update for release
1.
update po_line_locations_all set CLOSED_CODE = ‘FINALLY CLOSED’
where po_Release_id in (select po_Release_id from po_releases_all
where po_header_id in (select distinct po_header_id from po_releases_All pra
where pra.po_header_id in (
SELECT DISTINCT pha.po_header_id
FROM oe_order_headers_all a,
oe_order_lines_all b,
oe_drop_ship_sources odd,
po_headers_all pha
WHERE a.header_id = b.header_id
AND b.source_type_code = ‘EXTERNAL’
AND TRUNC (a.creation_date) <= ’31-DEC-2017′
AND a.header_id = odd.header_id
AND b.line_id = odd.line_id
AND odd.po_header_id = pha.po_header_id)));
2.
update po_releases_all set CLOSED_CODE= ‘FINALLY CLOSED’
where po_header_id in (select distinct po_header_id from po_releases_All pra
where pra.po_header_id in (
SELECT DISTINCT pha.po_header_id
FROM oe_order_headers_all a,
oe_order_lines_all b,
oe_drop_ship_sources odd,
po_headers_all pha
WHERE a.header_id = b.header_id
AND b.source_type_code = ‘EXTERNAL’
AND TRUNC (a.creation_date) <= ’31-DEC-2017′
AND a.header_id = odd.header_id
AND b.line_id = odd.line_id
AND odd.po_header_id = pha.po_header_id));
SET SERVEROUTPUT ON;
DECLARE
x_action CONSTANT VARCHAR2 (20) := ‘FINALLY CLOSE’;
x_calling_mode CONSTANT VARCHAR2 (2) := ‘PO’;
x_conc_flag CONSTANT VARCHAR2 (1) := ‘N’;
x_return_code_h VARCHAR2 (100);
x_auto_close CONSTANT VARCHAR2 (1) := ‘N’;
x_origin_doc_id NUMBER;
x_returned BOOLEAN;
CURSOR C1 IS
SELECT DISTINCT
pha.segment1, pha.po_header_id po_header_id
FROM oe_order_headers_all a,
oe_order_lines_all b,
oe_drop_ship_sources odd,
po_headers_all pha–,
— PO_RELEASES_ALL pra
WHERE a.header_id = b.header_id
— AND pra.po_header_id = pha.po_header_id
AND b.source_type_code = ‘EXTERNAL’
AND TRUNC (a.creation_date) <= ’31-DEC-2017′
AND a.header_id = odd.header_id
AND b.line_id = odd.line_id
AND odd.po_header_id = pha.po_header_id; — 212898
BEGIN
fnd_global.apps_initialize (user_id => 0,
resp_id => 20707,
resp_appl_id => 201
);
FOR I IN C1 LOOP
x_returned :=
po_actions.close_po (p_docid => I.PO_HEADER_ID,
p_doctyp => ‘PO’,
p_docsubtyp => ‘STANDARD’,
p_lineid => NULL,
p_shipid => NULL,
p_action => x_action,
p_reason => NULL,
p_calling_mode => x_calling_mode,
p_conc_flag => x_conc_flag,
p_return_code => x_return_code_h,
p_auto_close => x_auto_close,
p_action_date => SYSDATE,
p_origin_doc_id => NULL
);
IF x_returned = TRUE
THEN
DBMS_OUTPUT.put_line
(‘Purchase Order which just got Closed/Finally Closed is ‘||i.segment1);
COMMIT;
ELSE
DBMS_OUTPUT.put_line
( ‘API Failed to Close/Finally Close the Purchase Order’
|| x_return_code_h
);
END IF;
END LOOP;
END;
Conclusion
Order management purging process will be complete for the above given PO’s
