Thursday, February 15, 2018

Use of PO_REQAPPROVAL_INIT1.START_WF_PROCESS API to Approve PO, BPA & PR


Use of PO_REQAPPROVAL_INIT1.START_WF_PROCESS API to Approve Purchase Orders, Blanket Purchase Agreements & Purchase Requisition ...
Some times, we are wondering what exactly Oracle is doing when we are clicking on the Approve button in the Purchase Order/Requisition Form. And How does Approve button call the Purchasing approval workflow.

  • When the Approve button is clicked, the approval modal window form for purchasing approvals iscalled (this is form POXDOAPP.fmb and its attached corresponding library file POXAPAPC.pll.). Both Enter Requisition and Enter Purchase Order forms call the the same approval form.
  • The library file POXAPAPC.pll has a procedure PO_WF_APPROVE_C.SetUpWorkFlow that calls the procedure PO_REQAPPROVAL_INIT1.Start_WF_Process in package file POXWPA1B.pls.
  • This server side procedure calls the workflow and initiates the workflow and processes the document through the workflow...

-- R12 - PO - SAMPLE SCRIPT TO APPROVE PURCHASE ORDER
DECLARE
v_item_key VARCHAR2(100);

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.authorization_status
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 in ('INCOMPLETE', 'REQUIRES REAPPROVAL')
AND segment1 = '11170000860'; -- Enter the Purchase Order Number
BEGIN
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);
FOR p_rec IN c_po_details
LOOP

mo_global.init (p_rec.document_type_code);
mo_global.set_policy_context ('S', p_rec.org_id);

SELECT p_rec.po_header_id '-' to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;
dbms_output.put_line (' Calling po_reqapproval_init1.start_wf_process for po_id=>' p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => 'POAPPRV'
, ItemKey => v_item_key
, WorkflowProcess => 'POAPPRV_TOP'
, ActionOriginatedFrom => 'PO_FORM'
, DocumentID => p_rec.po_header_id -- po_header_id
, DocumentNumber => p_rec.segment1 -- Purchase Order Number
, PreparerID => p_rec.agent_id -- Buyer/Preparer_id
, DocumentTypeCode => p_rec.document_type_code--'PO'
, DocumentSubtype => p_rec.document_subtype --'STANDARD'
, SubmitterAction => 'APPROVE'
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => 'N'
, FaxFlag => 'N'
, FaxNumber => NULL
, EmailFlag => 'N'
, EmailAddress => NULL
, CreateSourcingRule => 'N'
, ReleaseGenMethod => 'N'
, UpdateSourcingRule => 'N'
, MassUpdateReleases => 'N'
, RetroactivePriceChange => 'N'
, OrgAssignChange => 'N'
, CommunicatePriceChange => 'N'
, p_Background_Flag => 'N'
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => 'N'
, p_source_type_code => NULL);
commit;

DBMS_OUTPUT.PUT_LINE ('The PO which is Approved Now =>' p_rec.segment1);
END LOOP;
END;

-- R12 - PO - SAMPLE SCRIPT TO APPROVE BLANKET PURCHASE AGREEMENT

DECLARE

v_item_key VARCHAR2(100);

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.authorization_status,
pha.approved_flag,
pha.wf_item_type,
pha.wf_item_key
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 = 'PA'
AND authorization_status in ('INCOMPLETE', 'REQUIRES REAPPROVAL')
AND segment1 = '11170000021'; -- Enter the BPA Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

FOR p_rec IN c_po_details

LOOP
mo_global.init ('PO');
mo_global.set_policy_context ('S', p_rec.org_id);

SELECT p_rec.po_header_id '-' to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;

dbms_output.put_line ('Calling po_reqapproval_init1.start_wf_process for po_id=>' p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => 'POAPPRV'
, ItemKey => v_item_key
, WorkflowProcess => 'POAPPRV_TOP'
, ActionOriginatedFrom => 'PO_FORM'
, DocumentID => p_rec.po_header_id -- po_header_id
, DocumentNumber => p_rec.segment1 -- Purchase Order Number
, PreparerID => p_rec.agent_id -- Buer/Preparer_id
, DocumentTypeCode => p_rec.document_type_code--'PA'
, DocumentSubtype => p_rec.document_subtype --'BLANKET'
, SubmitterAction => 'APPROVE'
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => 'N'
, FaxFlag => 'N'
, FaxNumber => NULL
, EmailFlag => 'N'
, EmailAddress => NULL
, CreateSourcingRule => 'N'
, ReleaseGenMethod => 'N'
, UpdateSourcingRule => 'N'
, MassUpdateReleases => 'N'
, RetroactivePriceChange => 'N'
, OrgAssignChange => 'N'
, CommunicatePriceChange => 'N'
, p_Background_Flag => 'N'
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => 'N'
, p_source_type_code => NULL);
commit;
dbms_output.put_line ('The BPA which is Approved Now =>' p_rec.segment1);

END LOOP;
END;

-- R12 - PO - SAMPLE SCRIPT TO APPROVE PURCHASE REQUISITION

DECLARE

v_item_key VARCHAR2(100);

Cursor c_req_details is

SELECT
prh.requisition_header_id,
prh.org_id,
prh.preparer_id,
prh.segment1,
pdt.document_subtype,
pdt.document_type_code,
prh.authorization_status
FROM apps.po_requisition_headers_all prh, apps.po_document_types_all pdt
WHERE prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND pdt.document_type_code = 'REQUISITION'
AND NVL (authorization_status, 'INCOMPLETE') = 'INCOMPLETE'
AND segment1 = '21170000200'; -- Enter The Requisition Number
BEGIN

fnd_global.apps_initialize (user_id => 1805,
resp_id => 20707,
resp_appl_id => 201);

FOR p_rec IN c_req_details

LOOP

mo_global.init ('PO');
mo_global.set_policy_context ('S', p_rec.org_id);

SELECT p_rec.requisition_header_id '-' to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;

dbms_output.put_line (' Calling po_reqapproval_init1.start_wf_process for requisition =>' p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => NULL
, ItemKey => v_item_key
, WorkflowProcess => 'POAPPRV_TOP'
, ActionOriginatedFrom => 'PO_FORM'
, DocumentID => p_rec.requisition_header_id -- requisition_header_id
, DocumentNumber => p_rec.segment1 -- Requisition Number
, PreparerID => p_rec.preparer_id
, DocumentTypeCode => p_rec.document_type_code-- REQUISITION
, DocumentSubtype => p_rec.document_subtype -- PURCHASE
, SubmitterAction => 'APPROVE'
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => 'N'
, FaxFlag => 'N'
, FaxNumber => NULL
, EmailFlag => 'N'
, EmailAddress => NULL
, CreateSourcingRule => 'N'
, ReleaseGenMethod => 'N'
, UpdateSourcingRule => 'N'
, MassUpdateReleases => 'N'
, RetroactivePriceChange => 'N'
, OrgAssignChange => 'N'
, CommunicatePriceChange => 'N'
, p_Background_Flag => 'N'
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => 'N'
, p_source_type_code => NULL);

commit;

dbms_output.put_line ('The Requisition which is Approved =>' p_rec.segment1);
END LOOP;
END;

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...