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'
