Closing old PO's and Blanket Agreements in Bulk Script

[admin]

-- Closing Blanket Agreement

DECLARE
x_action CONSTANT VARCHAR2 (20) := 'FINALLY CLOSE'; -- Change this parameter as per requirement
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;
l_ret_sts VARCHAR2 (1);
l_ret_code VARCHAR2 (40);
l_exc_msg VARCHAR2 (2000);
l_online_report_id NUMBER;

CURSOR c1
IS
SELECT pra.po_release_id, pra.org_id, pra.closed_code
FROM po_releases_all pra, po_headers_all pha
WHERE pra.po_header_id = pha.po_header_id
AND pra.closed_code <> 'FINALLY CLOSED'
AND pha.segment1 IN ('7002496');
BEGIN
FOR i IN c1
LOOP
mo_global.init ('PO');
mo_global.set_policy_context ('S', i.org_id);

PO_DOCUMENT_ACTION_PVT.do_manual_close (
p_action => 'FINALLY CLOSE',
p_document_id => i.po_release_id,
p_document_type => 'RELEASE',
p_document_subtype => 'BLANKET',
p_line_id => NULL,
p_shipment_id => NULL,
p_reason => NULL,
p_action_date => SYSDATE,
p_calling_mode => x_calling_mode,
p_origin_doc_id => NULL,
p_called_from_conc => FALSE,
p_use_gl_date => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO,
x_return_status => l_ret_sts,
x_exception_msg => l_exc_msg,
x_return_code => l_ret_code,
x_online_report_id => l_online_report_id);

IF l_ret_sts <> 'S'
THEN
DBMS_OUTPUT.PUT_LINE (
'API Failed to Close/Finally Close the Purchase Order'
|| l_exc_msg);
ELSE
DBMS_OUTPUT.PUT_LINE (
'Purchase Order which just got Closed/Finally Closed.'
|| i.po_release_id);
COMMIT;
END IF;
END LOOP;
END;

-- Standard PO

DECLARE
lv_result BOOLEAN;
lv_return_code VARCHAR2 (20);

CURSOR c_po_details
IS
SELECT pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.closed_code,
pha.closed_date
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
AND authorization_status = 'APPROVED'
--AND pha.closed_code <> 'FINALLY CLOSED'
AND segment1 IN ('7005295');
BEGIN
FOR po_details_rec IN c_po_details
LOOP
BEGIN
lv_result :=
PO_ACTIONS.CLOSE_PO (
P_DOCID => po_details_rec.po_header_id,
P_DOCTYP => 'PO', --'PO',
P_DOCSUBTYP => 'STANDARD', -- Can be STANDARD, BLANKET, RELEASE
P_LINEID => NULL, -- If want to close Line
P_SHIPID => NULL, -- If want to close Shipment
P_ACTION => 'CLOSE',
P_REASON => 'Close Purchase Order ',
P_CALLING_MODE => po_details_rec.document_type_code,
P_CONC_FLAG => 'N',
P_RETURN_CODE => lv_return_code,
P_AUTO_CLOSE => 'N',
P_ACTION_DATE => SYSDATE,
P_ORIGIN_DOC_ID => NULL);

IF lv_result = TRUE
THEN
DBMS_OUTPUT.PUT_LINE (
'Successfully closed PO#' || po_details_rec.segment1);
ELSE
DBMS_OUTPUT.PUT_LINE (
'Cannot close PO#' || po_details_rec.segment1);
END IF;
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
' Exception closing PO using PO_ACTIONS.CLOSE_PO' || SQLERRM);
END;