PO Requisitions Import EBS

Sample PL Sql Package for importing requisitions in Oracle EBS

CREATE OR REPLACE PACKAGE IDC_IMPORT_POREQ_PKG AS
  PROCEDURE IDC_INSERT_REQS
 (P_REQ_NUMBER_SEGMENT1 VARCHAR2,
  P_INTERFACE_SOURCE_CODE VARCHAR2, 
  P_REQUISITION_TYPE VARCHAR2,
  P_AUTHORIZATION_STATUS VARCHAR2,
  P_SOURCE_TYPE_CODE VARCHAR2,
  P_DESTINATION_TYPE_CODE VARCHAR2,
  P_PREPARER_NAME VARCHAR2,
  P_ITEM VARCHAR2,
  P_QUANTITY VARCHAR2,
  P_UOM VARCHAR2,
  P_PRICE VARCHAR2,
  P_NEED_BY_DATE VARCHAR2,
  P_REC_STATUS VARCHAR2,
  P_SUBMIT_IMPORT VARCHAR2);
  END IDC_IMPORT_POREQ_PKG;
 /
 CREATE OR REPLACE PACKAGE BODY IDC_IMPORT_POREQ_PKG AS
  PROCEDURE IDC_INSERT_REQS
  (P_REQ_NUMBER_SEGMENT1 VARCHAR2,
  P_INTERFACE_SOURCE_CODE VARCHAR2, 
  P_REQUISITION_TYPE VARCHAR2,
  P_AUTHORIZATION_STATUS VARCHAR2,
  P_SOURCE_TYPE_CODE VARCHAR2,
  P_DESTINATION_TYPE_CODE VARCHAR2,
  P_PREPARER_NAME VARCHAR2,
  P_ITEM VARCHAR2,
  P_QUANTITY VARCHAR2,
  P_UOM VARCHAR2,
  P_PRICE VARCHAR2,
  P_NEED_BY_DATE VARCHAR2,
  P_REC_STATUS VARCHAR2,
  P_SUBMIT_IMPORT VARCHAR2) IS 
 v_item_id number(10);
  ln_request_id number(15);
  v_req_desc VARCHAR2(200);
 BEGIN
 v_req_desc :='Spreadsheet upload Requisition# '||P_REQ_NUMBER_SEGMENT1;
 IF P_DESTINATION_TYPE_CODE ='INVENTORY' THEN
 select inventory_item_id  into v_item_id from mtl_system_items_b where segment1=P_ITEM and organization_id =204 and rownum <2;
 INSERT INTO PO.PO_REQUISITIONS_INTERFACE_ALL
 (REQ_NUMBER_SEGMENT1,INTERFACE_SOURCE_CODE, REQUISITION_TYPE,
  AUTHORIZATION_STATUS,
  SOURCE_TYPE_CODE,
  DESTINATION_TYPE_CODE,
  PREPARER_NAME,
  QUANTITY,
  CHARGE_ACCOUNT_ID,
  DESTINATION_ORGANIZATION_ID,
  DELIVER_TO_LOCATION_ID,
  DELIVER_TO_REQUESTOR_ID,
  ITEM_ID,
  NEED_BY_DATE,header_description) VALUES
  (P_REQ_NUMBER_SEGMENT1  ,
  P_INTERFACE_SOURCE_CODE  , 
  P_REQUISITION_TYPE  ,
  P_AUTHORIZATION_STATUS  ,
  P_SOURCE_TYPE_CODE  ,
  P_DESTINATION_TYPE_CODE,
  P_PREPARER_NAME,
  P_QUANTITY,
  13401,
  204,
  204,
  25,
  v_ITEM_ID,
  P_NEED_BY_DATE,v_req_desc);
 END IF;
 IF P_DESTINATION_TYPE_CODE ='EXPENSE' THEN
 INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
  (REQ_NUMBER_SEGMENT1,INTERFACE_SOURCE_CODE, requisition_type,
  AUTHORIZATION_STATUS,
  source_type_code,
  destination_type_code,
  PREPARER_NAME,
  QUANTITY,UNIT_PRICE, uom_code,
  CHARGE_ACCOUNT_ID,
  DESTINATION_ORGANIZATION_ID,
  DELIVER_TO_LOCATION_ID,
  DELIVER_TO_REQUESTOR_ID,
  ITEM_DESCRIPTION,
  category_id,header_description) values
  (P_REQ_NUMBER_SEGMENT1,P_INTERFACE_SOURCE_CODE, P_REQUISITION_TYPE,
  P_AUTHORIZATION_STATUS,
  P_SOURCE_TYPE_CODE  ,
  P_DESTINATION_TYPE_CODE,
  P_PREPARER_NAME,
  P_QUANTITY,
  P_PRICE,
  P_UOM,
  13401,
  204,
  204,
  25,
  P_ITEM,
  1,v_req_desc);
   END IF;
 if P_SUBMIT_IMPORT ='Y' then
  begin
  fnd_global.apps_initialize(1318 ,50578, 201);
  mo_global.init('PO');
    ln_request_id := FND_REQUEST.SUBMIT_REQUEST
                 (application   => 'PO', -- Application Name
                  program       => 'REQIMPORT', -- Program
                  description   => 'Requisition Import' ,
                  start_time    =>  NULL,       -- START_TIME
                  sub_request   =>  FALSE,      -- SUB_REQUEST
          argument1 => 'ADS',
          argument2 => NULL,
          argument3 => 'ITEM',
          argument4 => NULL,
          argument5 => 'N',
          argument6 => 'N'
          );
          dbms_output.put_line('Req id ' || ln_request_id);
  end;
  end if;
  END; 
 END  IDC_IMPORT_POREQ_PKG;
/
--- Submit concurrent program to import the requisitions from interface

 DECLARE
  ln_request_id number;
   begin
  fnd_global.apps_initialize(1318 ,50578, 201);
  mo_global.init('PO');
    ln_request_id := FND_REQUEST.SUBMIT_REQUEST
                 (application   => 'PO', -- Application Name
                  program       => 'REQIMPORT', -- Program
                  description   => 'Requisition Import' ,
                  start_time    =>  NULL,       -- START_TIME
                  sub_request   =>  FALSE,      -- SUB_REQUEST
          argument1 => 'ADS',
          argument2 => NULL,
          argument3 => 'ITEM',
          argument4 => NULL,
          argument5 => 'N',
          argument6 => 'N'
          );
          dbms_output.put_line('Req id ' || ln_request_id);
  end;
/
      
SELECT * FROM user_errors where name ='IDC_IMPORT_POREQ_PKG'

Sample CSV

REQ_NUMBER_SEGMENT1;INTERFACE_SOURCE_CODE;REQUISITION_TYPE;AUTHORIZATION_STATUS;SOURCE_TYPE_CODE;DESTINATION_TYPE_CODE;PREPARER_NAME;ITEM;QUANTITY;UOM;PRICE;NEED_BY_DATE;REC_STATUS;SUBMIT_IMPORT
910111;ADS;PURCHASE;INCOMPLETE;VENDOR;INVENTORY;Stock, Ms. Pat;AS54999;5;;;22-Jun-09;;
910112;ADS;PURCHASE;INCOMPLETE;VENDOR;INVENTORY;Stock, Ms. Pat;AS54888;4;;;22-Jun-09;;
910111;ADS;PURCHASE;INCOMPLETE;VENDOR;EXPENSE;Stock, Ms. Pat;IDC ITEM1;1;EA;300;;;
910112;ADS;PURCHASE;INCOMPLETE;VENDOR;EXPENSE;Stock, Ms. Pat;IDC ITEM;2;EA;200;;;Y