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

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