CREATE OR replace PROCEDURE Xx_cancel_po(p_org_id NUMBER,
p_po_header_id NUMBER,
v_po_release_id NUMBER,
p_po_line_id NUMBER,
p_action VARCHAR2)
AS
v_return_status VARCHAR2 (10);
v_msg_data VARCHAR2(1000);
v_action VARCHAR2(10) := p_action; -- 'CANCEL';
v_action_date DATE := SYSDATE;
CURSOR c IS
SELECT DISTINCT PHA.org_id,
--DECODE(po_RELEASE_ID,PRA.PO_RELEASE_ID,PRA.PO_RELEASE_ID,NULL) PO_RELEASE_ID,
pdt.document_subtype,
pdt.document_type_code,
PHA.created_by,
pha.authorization_status,
pha.agent_id,
pha.segment1,
pha.po_header_id
FROM apps.po_headers_all PHA,
apps.po_releases_all PRA,
apps.po_document_types_all PDT
WHERE 1 = 1
AND CASE
WHEN PHA.type_lookup_code = 'PLANNED'
AND v_po_release_id IS NOT NULL THEN 'SCHEDULED'
ELSE PHA.type_lookup_code
END = pdt.document_subtype
--and pra.RELEASE_TYPE = pdt.document_subtype
AND PHA.org_id = PDT.org_id
AND PHA.po_header_id = PRA.po_header_id(+)
--and pha.type_lookup_code =
AND PDT.document_type_code = CASE
WHEN pha.type_lookup_code = 'PLANNED'
AND v_po_release_id IS NULL THEN
'PO'
WHEN PHA.type_lookup_code = 'PLANNED'
AND v_po_release_id IS NOT NULL
THEN
'RELEASE'
WHEN PHA.type_lookup_code = 'STANDARD'
THEN 'PO'
WHEN PHA.type_lookup_code = 'BLANKET'
AND v_po_release_id IS NOT NULL
THEN
'RELEASE'
WHEN PHA.type_lookup_code = 'BLANKET'
AND v_po_release_id IS NULL THEN
'PA'
END
AND PHA.po_header_id = p_po_header_id
AND ( PRA.po_release_id = v_po_release_id
OR v_po_release_id IS NULL );
BEGIN
fnd_global.Apps_initialize (user_id => 0, resp_id => 50260,
resp_appl_id => 201)
;
mo_global.Init ('PO');
mo_global.Set_policy_context ('S', p_org_id);
FOR c1 IN c LOOP
dbms_output.Put_line ('Calling API For Cancelling Documents');
po_document_control_pub.Control_document (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false,
x_return_status => v_return_status, p_doc_type => c1.document_type_code,
p_doc_subtype => c1.document_subtype, p_doc_id => Nvl(v_po_release_id,
p_po_header_id),
p_doc_num => NULL, p_release_id => NULL, p_release_num => NULL,
p_doc_line_id => p_po_line_id, p_doc_line_num => NULL,
p_doc_line_loc_id => NULL
, p_doc_shipment_num => NULL, p_action => v_action,
p_action_date => v_action_date, p_cancel_reason => 'OLD PURCHASE ORDER',
p_cancel_reqs_flag => 'Y', p_print_flag => NULL,
p_note_to_vendor => NULL,
p_use_gldate => NULL, p_org_id => p_org_id);
COMMIT;
dbms_output.Put_line('The Return Status of the API : '
|| v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
dbms_output.Put_line ('Cancellation of PO is Sucessfull : '
||p_po_header_id);
ELSE
dbms_output.Put_line ('Cancellation of PO Failed ');
ROLLBACK;
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
v_msg_data := fnd_msg_pub.Get(p_msg_index => i, p_encoded => 'F');
dbms_output.Put_line(i
|| ') '
|| v_msg_data);
END LOOP;
END IF;
END LOOP;
END;
----------------------*---------------------
CREATE OR replace PROCEDURE Xx_close_po_wrapper(p_org_id NUMBER,
p_cut_off_date DATE)
AS
CURSOR c IS
SELECT A.org_id,--Open & Approved PO
a.po_header_id,
NULL PO_RELEASE_ID,
Nvl(a.authorization_status, 'INCOMPLETE') AUTHORIZATION_STATUS
FROM po_headers_all a
WHERE Nvl(closed_code, 'OPEN') = 'OPEN'
AND approved_date < To_date(p_cut_off_date) + 1
AND org_id = p_org_id
AND EXISTS (SELECT 1
FROM po_lines_all b
WHERE b.po_header_id = a.po_header_id
AND Nvl(B.closed_code, 'OPEN') = 'OPEN')
--and authorization_status='REJECTED'
--AND SEGMENT1='19657500811'
UNION
SELECT A.org_id,--Open & Created PO
a.po_header_id,
NULL PO_RELEASE_ID,
Nvl(a.authorization_status, 'INCOMPLETE') AUTHORIZATION_STATUS
FROM po_headers_all a
WHERE Nvl(closed_code, 'OPEN') = 'OPEN'
AND creation_date < To_date(p_cut_off_date) + 1
--and authorization_status='REJECTED'
AND approved_date IS NULL
AND org_id = p_org_id
AND EXISTS (SELECT 1
FROM po_lines_all b
WHERE b.po_header_id = a.po_header_id
AND Nvl(B.closed_code, 'OPEN') = 'OPEN')
--AND SEGMENT1='19657500811'
-----------------------------------------------------------------------
UNION
SELECT A.org_id,--Open & Approved PO Release
a.po_header_id,
A.po_release_id,
Nvl(a.authorization_status, 'INCOMPLETE') AUTHORIZATION_STATUS
FROM po_releases_all a
WHERE Nvl(closed_code, 'OPEN') = 'OPEN'
AND approved_date < To_date(p_cut_off_date) + 1
AND org_id = p_org_id
AND EXISTS (SELECT 1
FROM po_line_locations_all b
WHERE b.po_header_id = a.po_header_id
AND Nvl(B.closed_code, 'OPEN') = 'OPEN'
AND B.po_release_id = A.po_release_id)
--and authorization_status='REJECTED'
--AND SEGMENT1='19657500811'
UNION
SELECT A.org_id,--Open & Created PO Release
a.po_header_id,
A.po_release_id,
Nvl(a.authorization_status, 'INCOMPLETE') AUTHORIZATION_STATUS
FROM po_releases_all a
WHERE Nvl(closed_code, 'OPEN') = 'OPEN'
AND creation_date < To_date(p_cut_off_date) + 1
--and authorization_status='REJECTED'
AND approved_date IS NULL
AND org_id = p_org_id
AND EXISTS (SELECT 1
FROM po_line_locations_all b
WHERE b.po_header_id = a.po_header_id
AND Nvl(B.closed_code, 'OPEN') = 'OPEN'
AND B.po_release_id = A.po_release_id)
--AND SEGMENT1='19657500811'
;
BEGIN
FOR c1 IN c LOOP
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, NULL, 'FINALLY CLOSE');
END LOOP;
FOR c1 IN c LOOP
--If there is any issue in 'FINALLY CLOSE', will make them 'CLOSE'
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, NULL, 'CLOSE');
END LOOP;
COMMIT;
FOR c1 IN c LOOP
IF Nvl(c1.authorization_status, 'INCOMPLETE') != 'APPROVED' THEN
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, NULL, 'CANCEL');
--If there is any issue in 'FINALLY CLOSE' or 'CLOSE', will cancel theose POs
FOR c2 IN (SELECT DISTINCT po_line_id
FROM po_line_locations_all
WHERE po_header_id = c1.po_header_id
AND Nvl(po_release_id, -1) =
Nvl(c1.po_release_id, -1)
AND Nvl(closed_code, 'OPEN') = 'OPEN') LOOP
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, c2.po_line_id,
'CANCEL'
);
--If there is issue in 'FINALLY CLOSE'/'CLOSE'/'CANCEL' to PO Header, will 'CANCEL' PO lines
END LOOP;
ELSE
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, NULL, 'FINALLY CLOSE');
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, NULL, 'CLOSE');
FOR c2 IN (SELECT DISTINCT po_line_id
FROM po_line_locations_all
WHERE po_header_id = c1.po_header_id
AND Nvl(po_release_id, -1) =
Nvl(c1.po_release_id, -1)
AND Nvl(closed_code, 'OPEN') = 'OPEN') LOOP
Xx_cancel_po(c1.org_id, c1.po_header_id, NULL, c2.po_line_id,
'CLOSE')
;
END LOOP;
END IF;
END LOOP;
END;
No comments:
Post a Comment