PROCEDURE FOR PO & PROCEDURE STEPS

CREATE OR REPLACE PROCEDURE XX_PO_INTERFACE11
(Errbuf  OUT VARCHAR2,
                                 Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM XX_PO_HEADERS_STAGE;
CURSOR c2 IS SELECT * FROM XX_PO_LINES_STAGE;

l_vendor_id number(10);
l_item      varchar2(150);
l_flag      varchar2(4) default 'A';
l_msg       varchar2(200);
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id    number(6);

BEGIN
FOR x1 IN c1 LOOP
 BEGIN
   SELECT vendor_id
   INTO   l_vendor_id
   FROM po_vendors
   WHERE vendor_name = x1.VENDOR_NAME;
--   AND   ORG_ID = Fnd_Profile.Value('ORG_ID');
 EXCEPTION
   WHEN OTHERS THEN
    l_flag        := 'E';
    l_vendor_id := 0;
    l_msg       := 'Vendor id is Not in SYSTEM';
 END;
 --Vendor Site code  Validation
/*  begin
    select  vendor_site_code
    into    l_site_code
    from    po_vendor_sites_all
    where   vendor_site_code = x1.vendor_site_code;
    EXCEPTION
       WHEN OTHERS THEN
        l_flag        := 'E';
     l_site_code   := 0;
                 l_msg         := 'Vendor Site Code is Not in SYSTEM';
    END;*/
--End of Site Code Validation
--Currency Code Validation
  Begin
  select currency_code
  into   l_curr_code
  from   fnd_currencies
  where  currency_code = x1.CURRENCY_CODE;
   EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
     l_curr_code   := 0;
                 l_msg         := 'Currency Code is Invalid';
    END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
   select organization_id
   into   l_org_id
   from   hr_operating_units
   where  organization_id = x1.org_id;
   EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
     l_org_id      := 0;
                 l_msg         := 'Invalid Organization ID';
    END;
--End of the ORG ID Validation
  IF l_flag != 'E' THEN
     INSERT INTO po_headers_interface
   (
   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
  ,creation_date
  ,APPROVAL_STATUS
  ,APPROVED_DATE
  ,FREIGHT_TERMS
)
VALUES
(
   x1.INTERFACE_HEADER_ID
  ,x1.batch_id
  ,x1.action
  ,x1.org_id                                
  ,x1.document_type_code                          
  ,x1.CURRENCY_CODE                              
  ,x1.AGENT_NAME              
  ,x1.VENDOR_NAME
  ,x1.VENDOR_SITE_CODE
  ,x1.SHIP_TO_LOCATION
  ,x1.BILL_TO_LOCATION
  ,SYSDATE-10                          
  ,x1.APPROVAL_STATUS
  ,SYSDATE                            
 ,x1.FREIGHT_TERMS
 );
end if;
END LOOP;
FOR x2 IN c2  LOOP
l_flag := 'A';
--Item Validation
begin
  select segment1
  into   l_item
  from   mtl_system_items_b
     where  segment1        = x2.item
     AND    ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
    l_flag        := 'E';
    l_vendor_id := 0;
    l_msg       := 'Item is not valid Item';
 END;
--End of the Item Validation
 if  l_flag != 'E' then
 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
)
VALUES
(
   x2.INTERFACE_LINE_ID
  ,x2.INTERFACE_HEADER_ID
  ,x2.LINE_NUM
  ,x2.SHIPMENT_NUM
  ,x2.LINE_TYPE
  ,x2.ITEM
  ,x2.ITEM_DESCRIPTION
  ,x2.item_id
  ,x2.UOM_CODE
  ,x2.QUANTITY,
   X2.UNIT_PRICE,
  X2.SHIP_TO_ORGANIZATION_CODE,
  X2.SHIP_TO_LOCATION,
  X2.NEED_BY_DATE,
  X2.PROMISED_DATE,
  X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END XX_PO_INTERFACE11;
/


1)Create the Staging tables
2)Develop the Control files and register as concurrent program
3)Develop the PL/SQL Program and write the validations and insert into interface table
4)run the standard program called Import Standard Purchase orders from PO Reponsibility
Parameter : Default Buyer          :null
            Create or update items :No
PO Status  :APPROVED
Batch ID :13 (We can get from headers interface table)
5)Take the Request ID execute following query we can get the PO numbers

  select segment1 POnumber
  from   po_headers_all
  where  request _id = 145233;
6)Go to the PO Application and Query the PO from as per the PO number.