PURCHASE ORDER INTERFACE(CODE)

PURCHASE ORDER INTERFACE                                                       
                                                                     
                                                                     
                                             
 CREATE OR REPLACE PROCEDURE PO_Int(Errbuf  OUT VARCHAR2,
                           Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM xx_staging_headers;
CURSOR c2 IS SELECT * FROM xx_staging_lines1;
l_vendor_id number(10);
l_item      varchar2(150);
l_flag     varchar2(4) default 'A';
l_msg      varchar2(200);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
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;
  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_CARRIER
  ,FOB
  ,FREIGHT_TERMS
)
VALUES
(
   x1.INTERFACE_HEADER_ID
  ,x1.batch_id
  ,x1.action
  ,x1.org_id                                    --"Puffer-Sweiven"
  ,x1.document_type_code                            --done
  ,x1.CURRENCY_CODE                                              --done
  ,x1.AGENT_NAME                  --done
  ,x1.VENDOR_NAME
  ,x1.VENDOR_SITE_CODE
  ,x1.SHIP_TO_LOCATION
  ,x1.BILL_TO_LOCATION
  ,SYSDATE-10                            --done
  ,x1.APPROVAL_STATUS
  ,SYSDATE                               --done
  ,x1.FREIGHT_CARRIER
  ,x1.FOB
  ,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
  ,SYSDATE                              --done
  ,SYSDATE                              --done
  ,x2.list_price_per_unit
);
end if;
END LOOP;
COMMIT;
END PO_Int;
/