procure to pay(p2p) cycle with complete flow from backend using query and scripts

****************************************************************************
Summary of the below p2p from using.

1. To Insert Data into the staging Table.
2. after insert submit the import program"Import Standard Purchase Orders" to generate the po#.
3. after generating the po# need to approve.
4. after approving load the data in to rcv interface tables.
5. after loading into the rcv interface tables run the "Receiving Transaction Processor" program to generate receipt.
6. To generate the invoice run the concurrent program "Pay On Receipt AutoInvoice".
***************************************************************************

--1. To Insert Data into the staging Table

CREATE OR REPLACE PROCEDURE XX_TABLE_INSERT
AS
  lErrbuf  VARCHAR2(10);
  lRetcode NUMBER;
BEGIN
  INSERT
  INTO xx_po_header_stg
    (
      INTERFACE_HEADER_ID ,
      BATCH_ID ,
      ACTION ,
      ORG_ID ,
      DOCUMENT_TYPE_CODE ,
      CURRENCY_CODE ,
      AGENT_NAME ,
      VENDOR_NAME ,
      VENDOR_SITE_CODE ,
      SHIP_TO_LOCATION ,
      BILL_TO_LOCATION ,
      APPROVAL_STATUS ,
      FREIGHT_CARRIER ,
      FOB ,
      FREIGHT_TERMS
    )
    VALUES
    (
      po_headers_interface_s.NEXTVAL ,
      13 ,
      'ORIGINAL',
      204,
      'STANDARD',
      'USD',
      'Stock, Ms. Pat',
      '3M Health Care',
      'CORP HQ',
      'M1- Seattle Mfg',
      'V1- New York City',
      'APPROVED' ,
      'UPS',
      'Origin',
      'Due'
    );
  FND_FILE.PUT_LINE(FND_FILE.LOG,'DATA LOADED IN TO THE xx_po_header_stg STAGING TABLE');
  COMMIT;
  INSERT
  INTO xx_po_line_stg
    (
      interface_header_id ,
      interface_line_id ,
      LINE_NUM ,
      SHIPMENT_NUM ,
      LINE_TYPE ,
      ITEM ,
      ITEM_DESCRIPTION ,
      item_id ,
      UOM_CODE ,
      QUANTITY ,
      UNIT_PRICE ,
      SHIP_TO_ORGANIZATION_CODE ,
      SHIP_TO_LOCATION ,
      list_price_per_unit
    )
    VALUES
    (
      po_headers_interface_s.currval,
      po_lines_interface_s.nextval,
      1,
      2,
      'Goods',
      'AS54999',
      'Sentinel Standard Desktop - Rugged',
      2155,
      'Ea',
      10,
      120,
      'V1',
      'V1- New York City',
      45
    );
  FND_FILE.PUT_LINE(FND_FILE.LOG,'DATA LOADED IN TO THE xx_po_line_stg STAGING TABLE');
  COMMIT;
  --call the package for inserting the data from staging table to interface table
  XX_PO_INS_PKG.XX_PO_INS_PRC(lErrbuf,lRetcode);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'package => XX_PO_INS_PKG.XX_PO_INS_PRC compiled'||'lErrbuf'||lErrbuf||'lRetcode'||lRetcode );
  UPDATE po_headers_interface SET AGENT_ID=27 WHERE creation_date LIKE sysdate;
  COMMIT;
END XX_TABLE_INSERT;
/

--2. after insert submit the import program to generate the po#.

CREATE OR REPLACE PROCEDURE submit_po_program
 AS
    l_responsibility_id NUMBER;
    l_application_id    NUMBER;
    l_user_id           NUMBER;
    l_request_id        NUMBER;
  BEGIN

      SELECT user_id INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'PRUTHVI';
--To set environment context.
 apps.fnd_global.apps_initialize (l_user_id,50578,201);
 -- --Submitting Concurrent Request
  l_request_id := fnd_request.submit_request ( application => 'PO',
                                              program => 'POXPOPDOI',
                                              description => 'Import Standard Purchase Orders',
                                              start_time => sysdate,
                                              sub_request => FALSE,
                                              argument1 => NULL ,
                                              argument2 => 'Yes',
                                              argument3 => 'APPROVED',
                                              argument4 => NULL);
                                               COMMIT;
 IF l_request_id = 0
 THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent request failed to submit =>Import Standard Purchase Orders');
 ELSE
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully Submitted the Concurrent Request =>Import Standard Purchase Orders');
 END IF;
 EXCEPTION
 WHEN OTHERS THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;
/

--3. after generating the po# need to approve 

CREATE OR REPLACE procedure APPROVE_PO
AS
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 = '6259';
BEGIN
fnd_global.apps_initialize (user_id => 1013455,resp_id => 50578,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;
/

--4. after approving load the data in to rcv interface tables

CREATE OR REPLACE PROCEDURE RCV_PO_NUMBER
AS
X_USER_ID NUMBER;
X_PO_HEADER_ID NUMBER;
X_VENDOR_ID NUMBER;
X_SEGMENT1 VARCHAR2(20);
X_ORG_ID NUMBER;
X_LINE_NUM NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('***ezROI RCV API Insert Script***');
SELECT PO_HEADER_ID , VENDOR_ID , SEGMENT1 , ORG_ID
INTO X_PO_HEADER_ID , X_VENDOR_ID , X_SEGMENT1 , X_ORG_ID
FROM PO_HEADERS_ALL
WHERE SEGMENT1 = 6259;--'&PO_NUMBER';
--AND ORG_ID = &ORG_ID;
SELECT USER_ID INTO X_USER_ID
FROM FND_USER
WHERE USER_NAME = UPPER('PRUTHVI');

INSERT INTO RCV_HEADERS_INTERFACE
(
HEADER_INTERFACE_ID ,
GROUP_ID ,
PROCESSING_STATUS_CODE ,
RECEIPT_SOURCE_CODE ,
TRANSACTION_TYPE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
VENDOR_ID ,
EXPECTED_RECEIPT_DATE ,
VALIDATION_FLAG ,
CREATION_DATE
)
SELECT
RCV_HEADERS_INTERFACE_S.NEXTVAL ,
RCV_INTERFACE_GROUPS_S.NEXTVAL ,
'PENDING' ,
'VENDOR' ,
'NEW' ,
SYSDATE ,
X_USER_ID ,
0 ,
X_VENDOR_ID ,
SYSDATE ,
'Y' ,
SYSDATE
FROM DUAL;

DECLARE
CURSOR PO_LINE IS
SELECT PL.ITEM_ID , PL.PO_LINE_ID , PL.LINE_NUM ,
PLL.QUANTITY , PL.UNIT_MEAS_LOOKUP_CODE ,
MP.ORGANIZATION_CODE , PLL.LINE_LOCATION_ID ,
PLL.CLOSED_CODE , PLL.QUANTITY_RECEIVED ,
PLL.CANCEL_FLAG, PLL.SHIPMENT_NUM
FROM PO_LINES_ALL PL ,
PO_LINE_LOCATIONS_ALL PLL ,
MTL_PARAMETERS MP
WHERE PL.PO_HEADER_ID = X_PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID;
BEGIN
FOR CURSOR1 IN PO_LINE LOOP
IF CURSOR1.CLOSED_CODE IN ('APPROVED','OPEN')
AND CURSOR1.QUANTITY_RECEIVED < CURSOR1.QUANTITY
AND NVL(CURSOR1.CANCEL_FLAG,'N') = 'N'
THEN
INSERT INTO RCV_TRANSACTIONS_INTERFACE
(
INTERFACE_TRANSACTION_ID ,
GROUP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
TRANSACTION_TYPE ,
TRANSACTION_DATE ,
PROCESSING_STATUS_CODE ,
PROCESSING_MODE_CODE ,
TRANSACTION_STATUS_CODE ,
PO_HEADER_ID,
PO_LINE_ID ,
ITEM_ID ,
QUANTITY ,
UNIT_OF_MEASURE ,
PO_LINE_LOCATION_ID ,
AUTO_TRANSACT_CODE ,
RECEIPT_SOURCE_CODE ,
TO_ORGANIZATION_CODE ,
SOURCE_DOCUMENT_CODE ,
HEADER_INTERFACE_ID ,
VALIDATION_FLAG
)
SELECT
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL ,
RCV_INTERFACE_GROUPS_S.CURRVAL ,
SYSDATE ,
X_USER_ID ,
SYSDATE ,
X_USER_ID ,
0 ,
'RECEIVE' ,
SYSDATE ,
'PENDING' ,
'BATCH' ,
'PENDING' ,
X_PO_HEADER_ID,
CURSOR1.PO_LINE_ID ,
CURSOR1.ITEM_ID ,
CURSOR1.QUANTITY ,
CURSOR1.UNIT_MEAS_LOOKUP_CODE ,
CURSOR1.LINE_LOCATION_ID ,
'RECEIVE' ,
'VENDOR' ,
CURSOR1.ORGANIZATION_CODE ,
'PO' ,
RCV_HEADERS_INTERFACE_S.CURRVAL ,
'Y'
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('PO line: '||CURSOR1.LINE_NUM||' Shipment: '||CURSOR1.SHIPMENT_NUM||' has been inserted into ROI.');
ELSE
DBMS_OUTPUT.PUT_LINE('PO line '||CURSOR1.LINE_NUM||' is either closed, cancelled, received.');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('***COMPLETE - End ***');
END;
COMMIT;
END;
/

--5. after loading into the rcv interface tables run the "Receiving Transaction Processor" program to generate receipt.

CREATE OR REPLACE PROCEDURE submit_rcv_trans_program
 AS
    l_responsibility_id NUMBER;
    l_application_id    NUMBER;
    l_user_id           NUMBER;
    l_request_id        NUMBER;
  BEGIN

      SELECT user_id INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'PRUTHVI';
--To set environment context.
 apps.fnd_global.apps_initialize (l_user_id,50578,201);
 -- --Submitting Concurrent Request
  l_request_id := fnd_request.submit_request ( application => 'PO',
                                              program => 'RVCTP',
                                              description => 'Receiving Transaction Processor',
                                              start_time => sysdate,
                                              sub_request => FALSE,
                                              argument1 => 'BATCH' ,
                                              argument2 => NULL,
                                              argument3 => NULL);
                                               COMMIT;
 IF l_request_id = 0
 THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent request failed to submit =>Receiving Transaction Processor');
 ELSE
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully Submitted the Concurrent Request =>Receiving Transaction Processor'||'REQUEST_ID'||l_request_id);
 END IF;
 EXCEPTION
 WHEN OTHERS THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
 END;
 /

****************************************************************************
CODE FOR THE PACKAGE XX_PO_INS_PKG

create or replace PACKAGE XX_PO_INS_PKG IS
PROCEDURE XX_PO_INS_PRC(Errbuf OUT VARCHAR2, Retcode OUT number) ;
END;

==================================================================
create or replace PACKAGE BODY XX_PO_INS_PKG
IS
PROCEDURE XX_PO_INS_PRC (errbuf OUT VARCHAR2, retcode OUT number)
IS
CURSOR cur_head
IS
SELECT *
FROM xx_po_header_stg;
CURSOR cur_line (p_interface_header_id NUMBER)
IS
SELECT *
FROM xx_po_line_stg where interface_header_id=p_interface_header_id;
CURSOR cur_dist (p_interface_line_id NUMBER)
IS
SELECT *
FROM xx_po_distribution_stg where interface_line_id=p_interface_line_id;
lv_vendor_id NUMBER (10);
lv_agent_id NUMBER(10);
lv_itemid NUMBER;
lv_site_code VARCHAR2 (100);
lv_lookup_code VARCHAR2(25);
lv_curr_code VARCHAR2 (10);
lv_org_id NUMBER (6);
BEGIN
BEGIN
SELECT organization_id
INTO lv_org_id
FROM hr_operating_units
WHERE NAME LIKE 'Vision Operations';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid org_id');
END;
BEGIN
FOR var1 IN cur_head
LOOP
BEGIN
SELECT vendor_id
INTO lv_vendor_id
FROM po_vendors
WHERE vendor_name=var1.vendor_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid vendor_id');
END;
BEGIN
SELECT vendor_site_code
INTO lv_site_code
FROM po_vendor_sites_all
WHERE vendor_site_code = var1.vendor_site_code;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'invalid vendor_site_code');
END;
BEGIN
SELECT currency_code
INTO lv_curr_code
FROM fnd_currencies
WHERE currency_code = var1.currency_code;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid currency_code');
END;
BEGIN
SELECT DOCUMENT_TYPE_CODE
INTO lv_lookup_code
FROM PO_DOCUMENT_TYPES
WHERE DOCUMENT_TYPE_CODE = var1.DOCUMENT_TYPE_CODE;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid typecode');
END;
BEGIN
SELECT person_id
INTO lv_agent_id
FROM PER_ALL_PEOPLE_F
WHERE full_name= var1.full_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid Buyer');
END;
BEGIN
INSERT INTO po_headers_interface
(interface_header_id
, batch_id
, org_id
,action
, document_type_code
, currency_code
,agent_id
, agent_name
, vendor_name
, vendor_site_code
, ship_to_location
, bill_to_location
,approval_status
, freight_terms
,fob
,freight_carrier
,created_by
, creation_date
, last_update_date
, last_updated_by
)
VALUES
(PO_HEADERS_INTERFACE_S.NEXTVAL
, var1.batch_id
, lv_org_id
,var1.action
, var1.document_type_code
, var1.currency_code
, lv_agent_id
,var1.full_name
,var1.vendor_name
, var1.vendor_site_code
, var1.ship_to_location
, var1.bill_to_location
, var1.approval_status
, var1.freight_terms
,var1.fob
,var1.freight_carrier
, -1
, SYSDATE
, SYSDATE
, -1
);
EXCEPTION WHEN OTHERS THEN
FND_FILE.put_line(FND_FILE.output,'insertion successful into headers int');
END;
FOR var2 IN cur_line (var1.interface_header_id)
LOOP
BEGIN
SELECT INVENTORY_ITEM_ID
INTO lv_itemid
FROM mtl_system_items_b
WHERE segment1 = var2.item
AND organization_id =lv_org_id;
EXCEPTION
WHEN OTHERS
THEN
-- lv_item := NULL;
fnd_file.put_line (fnd_file.LOG, 'invalid item');
END;
BEGIN
INSERT INTO po_lines_interface
(interface_line_id
, interface_header_id
, line_num
, shipment_num
, line_type
,item
, item_description
, item_id
, uom_code
, quantity
, unit_price
,ship_to_organization_code
, ship_to_location
,NEED_BY_DATE
-- ,PROMISED_DATE
, list_price_per_unit
, created_by
, creation_date
, last_update_date
, last_updated_by
)
VALUES
(PO_LINES_INTERFACE_S.NEXTVAL
,PO_HEADERS_INTERFACE_S.CURRVAL
, var2.line_num
, var2.shipment_num
, var2.line_type
, var2.item
, var2.item_description
, var2.item_id
,var2.uom_code
, var2.quantity
, var2.unit_price
,var2.ship_to_organization_code
,var2.ship_to_location
,SYSDATE
-- ,SYSDATE
, var2.list_price_per_unit
, -1
,SYSDATE
, SYSDATE
, -1
);
EXCEPTION WHEN OTHERS THEN
FND_FILE.put_line(FND_FILE.output,'insertion successful into lines int');
END;
for var3 in cur_dist(var2.interface_line_id)
LOOP
BEGIN
INSERT INTO po_distributions_interface (
interface_header_id ,
interface_line_id ,
interface_distribution_id ,
org_id ,
quantity_ordered ,
destination_organization_id ,
set_of_books_id ,
charge_account_id ,
distribution_num
, created_by
, creation_date
, last_update_date
, last_updated_by
)
VALUES
(
PO_HEADERS_INTERFACE_S.CURRVAL,
PO_LINES_INTERFACE_S.CURRVAL,
PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL ,
lv_org_id ,
var3.quantity_ordered ,
var3.destination_organization_id ,
var3.set_of_books_id ,
var3.charge_account_id ,
var3.distribution_num
, -1
,SYSDATE
, SYSDATE
, -1 );
EXCEPTION WHEN OTHERS THEN
FND_FILE.put_line(FND_FILE.output,'insertion successful into dist int');
END;
END LOOP;
END LOOP;
END LOOP;
END;
COMMIT;
END XX_PO_INS_PRC;

END XX_PO_INS_PKG ;