How to Approve back cancelled PR (Oracle Apps)
DECLARE
CURSOR c
IS
SELECT b.requisition_header_id, b.requisition_line_id, b.quantity,
a.segment1
FROM po_requisition_headers_all a,
po_requisition_lines_all b,
po_req_distributions_all c
WHERE a.segment1 IN (
SELECT DISTINCT prh.segment1 requisition_num
--, fu.user_name requestor,
-- papf.full_name, TO_CHAR (prh.creation_date) creation_date,
-- prh.creation_date, prh.authorization_status, prh.closed_code
FROM apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt,
po_req_distributions_all prd,
fnd_user fu,
per_all_people_f papf,
po_distributions_all pda
WHERE 1 = 1
AND prh.org_id = 121
AND pdt.document_type_code = 'REQUISITION'
-- AND prl.line_location_id IS not NULL
-- AND (prh.closed_code IN ('OPEN') OR prh.closed_code IS NULL)
AND prh.requisition_header_id =
prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.authorization_status = 'CANCELLED'
AND prh.creation_date BETWEEN TRUNC (SYSDATE - 90)
AND TRUNC (SYSDATE)
-- AND prh.segment1 = '201800949'
AND fu.employee_id = preparer_id
-- AND fu.user_name NOT IN ('PACASCM')
AND papf.person_id = fu.employee_id
AND prd.requisition_line_id =
prl.requisition_line_id
AND pda.req_distribution_id(+) =
prd.distribution_id
AND papf.effective_start_date
BETWEEN papf.effective_start_date
AND papf.effective_end_date
-- and pda.req_distribution_id is null
--ORDER BY prh.creation_date ASC
)
AND a.requisition_header_id = b.requisition_header_id
AND c.requisition_line_id = b.requisition_line_id;
BEGIN
FOR i IN c
LOOP
UPDATE po_requisition_lines_all
SET cancel_flag = '',
cancel_date = '',
quantity_cancelled = ''
WHERE requisition_header_id = i.requisition_header_id;
UPDATE po_req_distributions_all
SET req_line_quantity = i.quantity
WHERE requisition_line_id = i.requisition_line_id;
-- Approve the PR
xx_approve_pr (i.segment1);
/*
CREATE OR REPLACE PROCEDURE xx_approve_pr (l_pr_new_appr NUMBER)
AS
l_itemkey VARCHAR2 (200);
v_req_id NUMBER;
v_org_id NUMBER;
l_po_id NUMBER; -- := 3492; --:po_header_id;
l_pr_number VARCHAR2 (200) := l_pr_new_appr; --:requisition_number;
BEGIN
SELECT l_po_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO l_itemkey
FROM DUAL;
BEGIN
--Check PO HEADER_ID is exists ?
BEGIN
SELECT requisition_header_id, org_id
INTO v_req_id, v_org_id
FROM po_requisition_headers_all a
WHERE a.segment1 = l_pr_number;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('No data found : PR NO ==> ');
END;
l_po_id := v_req_id;
wf_engine.createprocess ('REQAPPRV',
l_itemkey,
'MAIN_REQAPPRV_PROCESS',
NULL,
'9484'
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'RESPONSIBILITY_ID',
avalue => 50746 --responsibility_id
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'APPLICATION_ID',
avalue => 201 --:application_id
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'USER_ID',
avalue => 1115 --user_id
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SUBMITTER_ID',
avalue => 1115 --buyer_id
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_ID',
avalue => l_po_id
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_NUMBER',
avalue => l_pr_number
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'ORG_ID',
avalue => v_org_id -- :org_id
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_SUBTYPE',
avalue => 'PURCHASE'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE',
avalue => 'REQUISITION'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE_DISP',
avalue => 'Purchase Requisition'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS',
avalue => 'APPROVED'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS_DISP',
avalue => 'Approved'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SEND_CREATEPO_TO_BACKGROUND',
avalue => 'Y'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'INTERFACE_SOURCE_CODE',
avalue => 'PO_FORM'
);
DBMS_OUTPUT.put_line (l_itemkey);
wf_engine.startprocess ('REQAPPRV', l_itemkey);
COMMIT;
END;
END;
*/
END LOOP;
COMMIT;
END;
Comments
Post a Comment