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

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Cloud Technologies Blog
Logo
Enable registration in settings - general
Compare items
  • Total (0)
Compare
0
Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Shopping cart