AR Invoice Import In Oracle EBS

Sample PL Sql Package for importing AR Invoices in Oracle EBS

CREATE OR REPLACE PACKAGE IDC_IMPORT_ARINV_PKG AS
  PROCEDURE IDC_INSERT_ARINV
 (P_TRX_TYPE VARCHAR2,
 P_BILL_TO VARCHAR2,
 P_ITEM_DESCRIPTION VARCHAR2,
 P_PAYMENT_TERM VARCHAR2,
 P_INVOICE_DATE VARCHAR2,
 P_QUANTITY VARCHAR2,
 P_UNITSELLPRICE VARCHAR2,
 P_UOM VARCHAR2,
 P_REC_STATUS VARCHAR2,
 P_SUBMIT_IMPORT VARCHAR2);
 END IDC_IMPORT_ARINV_PKG;
 /
 CREATE OR REPLACE PACKAGE BODY IDC_IMPORT_ARINV_PKG AS
  PROCEDURE IDC_INSERT_ARINV
 (P_TRX_TYPE VARCHAR2,
 P_BILL_TO VARCHAR2,
 P_ITEM_DESCRIPTION VARCHAR2,
 P_PAYMENT_TERM VARCHAR2,
 P_INVOICE_DATE VARCHAR2,
 P_QUANTITY VARCHAR2,
 P_UNITSELLPRICE VARCHAR2,
 P_UOM VARCHAR2,
 P_REC_STATUS VARCHAR2,
 P_SUBMIT_IMPORT VARCHAR2) is
 l_request_id number;
 v_bill_to_cust_id number;
 v_bill_to_add_id number;
 v_term_id number;
 Begin
 -- you need to create a new context or use an exsting context for 
 -- the Line transaction flexfield in Receivables
 -- the combination of values for the attributes for line transaction flexfield 
 -- has to be unique
 -- for eg. if your line transaction flexfield has 
 -- interface_line_attribute1 and interface_line_attribute2
 -- then the combination of values for these attributes should be unique for each record
 -- the call to fnd_global is required only if you want to submit 
 -- the concurrent request from the procedure
 fnd_global.apps_initialize(1318,50559,222,0); 
 SELECT
          hca.cust_account_id ,hcsa.cust_acct_site_id into 
          v_bill_to_cust_id ,v_bill_to_add_id
 FROM   hz_parties hp
      , hz_party_sites hps
      , hz_locations hl
      , hz_cust_accounts_all hca
      , hz_cust_acct_sites_all hcsa
      , hz_cust_site_uses_all hcsu
 WHERE  hp.party_id = hps.party_id
 AND    hps.location_id = hl.location_id
 AND    hp.party_id = hca.party_id
 AND    hcsa.party_site_id = hps.party_site_id
 AND    hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
 AND    hca.cust_account_id = hcsa.cust_account_id
 --AND    hca.account_number = 1608
 and  hp.party_name =P_BILL_TO
 AND  HCSU.SITE_USE_CODE='BILL_TO'
 AND hcsa.bill_to_flag ='P'
 and hl.country ='US'
 and rownum <2;
 select term_id  into v_term_id from ra_terms_tl where  name =P_PAYMENT_TERM and rownum <2;
 insert into ra_interface_lines_all
 (
 ORIG_SYSTEM_BILL_CUSTOMER_ID,
 ORIG_SYSTEM_BILL_ADDRESS_ID,
 AMount,
 currency_code,
 batch_source_name,
 CUST_TRX_TYPE_NAME,
 cust_trx_type_id,
 line_type,
 --TRX_NUMBER,
 description,
 term_id,
 TERM_NAME,
 trx_date,
 quantity,
 unit_selling_price,
 uom_code,
 tax_exempt_flag,
 creation_date,
 created_by,
 last_updated_by,
 last_update_date,
 interface_line_context,
 interface_line_attribute1,
 interface_line_attribute2,
 conversion_type ,
 conversion_rate
 --primary_salesrep_id
 )
 values
 (
 v_bill_to_cust_id , -- bill_to_customer_id
 v_bill_to_add_id, -- bill_to_address_id
 P_QUANTITY * P_UNITSELLPRICE,-- invoice_amount
 'USD', -- invoice_currency_code
 'LEGACY', -- batch source name
 'Invoice',
 1, -- cust trx type id for transaction type 'Invoice'
 'LINE', -- line_type
 --'IDC-INV2',
 P_ITEM_DESCRIPTION, --description
 v_term_id, -- term id for payment term of net 30
 P_PAYMENT_TERM,
 P_INVOICE_DATE, -- invoice_date
 P_QUANTITY , -- quantity 
 P_UNITSELLPRICE , -- unit selling price will be same as amount if quantity is 1
 P_UOM, -- uom
 'S', -- standard tax
 sysdate,
 1318, -- user id of user  operations
 1318,
 sysdate,
 'LEGACY', -- interface line context
 1, -- interface_line_attribute1 
 RA_CUSTOMER_TRX_LINES_S.NEXTVAL, -- interface_line_attribute2 
 'User', -- conversion_type
 1 -- conversion_rate
 ---3 -- sales rep id for No Sales Credit
 );
 COMMIT;
 -- submits the Autoinvoice Master Program
 if P_SUBMIT_IMPORT ='Y' then
 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
 application => 'AR',
 program => 'RAXMTR',
 description => null,
 start_time => null,
 sub_request => FALSE,
 argument1 => 1, 
 argument2 => 1228, -- batch source id for batch source LEGACY
 argument3 => 'LEGACY',
 argument4 => to_char(sysdate,'RRRR/MM/DD "00:00:00"'), -- default date
 argument5 => null,
 argument6 => null,
 argument7 => null,
 argument8 => null,
 argument9 => null,
 argument10 => null,
 argument11 => null,
 argument12 => null,
 argument13 => null,
 argument14 => null,
 argument15 => null,
 argument16 => null,
 argument17 => null,
 argument18 => null,
 argument19 => null,
 argument20 => null,
 argument21 => null,
 argument22 => null,
 argument23 => null,
 argument24 => null,
 argument25 => 'Y',
 argument26 => null,
 argument27 => 204
 );
 dbms_output.put_line('Request_id '||l_request_id);
 --DELETE FROM APPS.RA_INTERFACE_LINES where  upper(batch_source_name) = 'LEGACY';
 commit;
 end if;
 End IDC_INSERT_ARINV;
 END IDC_IMPORT_ARINV_PKG;
 /
 --select * from user_errors where name = 'IDC_IMPORT_ARINV_PKG'

Sample CSV

TRX_TYPE,BILL_TO_CUSTOMER,ITEM DESCRIPTION,PAYMENT TERM,INVOICE_DATE,QUANTITY,UNITSELLINGPRICE,UOM,REC_STATUS,SUBMIT_IMPORT
 Invoice,Carquest,IDC Mouse1,30 NET,sysdate,1,44,Ea,,
 Invoice,Carquest,IDC Keyboard1,30 NET,sysdate,2,37,Ea,,
 Invoice,Carquest,IDC Computer Hardisk1,30 NET,sysdate,3,20,Ea,,Y

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
      Shopping cart