In this article we will see complete PL/SQL code for the Oracle Apps AR Invoice Interface. This code will assist you in converting AR invoices in Oracle Apps. You’ll learn how to import AR invoices in Oracle Interface in detail, step by step. This code allows you to enter data in the required format in the AR invoicing interface.
As a suitable way to inserting data in the AR Interface table, we should first input the AR data in the Staging table, and then move the data to the AR Standard Interface Tables after validation.
Important table of AR interface In Oracle Apps R12
RA_INTERFACE_LINES_ALL
The RA_INTERFACE_LINES_ALL table contains interface information for each invoice line imported into Oracle Receivables via AutoInvoice. INTERFACE_LINE_ID is the main key of this table.
select * from RA_INTERFACE_LINES_ALL where trunc(creation_date) = trunc(sysdate);
RA_INTERFACE_DISTRIBUTIONS_ALL
This table are linked to the appropriate transaction lines in the ra_interface_lines via the transaction flexfield. Though the distribution for ‘REC’ account class is at the invoice level, it may be linked to any transaction line of the invoice in ra_interface_lines. AutoInvoice will then correctly transfer all distributions to RA_CUST_TRX_LINE_GL_DIST_ALL.
select * from RA_INTERFACE_DISTRIBUTIONS_ALL where trunc(creation_date) = trunc(sysdate);
RA_INTERFACE_ERRORS_ALL
This table stores Interface lines that failed validation and were not imported into Receivables tables. The AutoInvoice Validation Report is generated by Receivables using the data in this table.
PL/SQL procedure script to Import Data from AR Interface Table to AR Invocie Base Tables in Oracle Apps R12
CREATE OR REPLACE PROCEDURE XX_ARINVOICE_INTERFACE (errbuf out varchar2, rectcode out varchar2)
AS
l_org_id hr_operating_units.organization_id%type;
l_sob_id hr_operating_units.set_of_books_id%type;
l_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%type;
l_gl_id_rev ra_cust_trx_types_all.gl_id_rev%type;
l_cust_trx_type_name ra_cust_trx_types_all.name%type;
l_currency_code fnd_currencies.currency_code%type;
l_term_id ra_terms_tl.term_id%type;
l_term_name ra_terms_tl.name%type;
l_address_id hz_cust_acct_sites_all.cust_acct_site_id%type;
l_customer_id hz_cust_accounts.cust_account_id%type;
l_verify_flag char(1);
l_error_message varchar2(2500);
BEGIN
BEGIN
SELECT organization_id, SET_OF_BOOKS_ID
INTO l_org_id, l_sob_id
FROM hr_operating_units
WHERE name =’Operating Unit’
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := ‘N’;
l_error_message := ‘Invalide Operating Unit…’;
END;
BEGIN
SELECT cust_trx_type_id,name,gl_id_rev
INTO l_cust_trx_type_id,l_cust_trx_type_name, l_gl_id_rev
FROM ra_cust_trx_types_all
WHERE set_of_books_id = l_sob_id
AND org_id = l_org_id
AND name = ‘xxx-Spares-Inv’;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := ‘N’;
l_error_message := ‘Invalide Invoice Type…’;
END;
BEGIN
select currency_code
into l_currency_code
from fnd_currencies
where currency_code = ‘USD’;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := ‘N’;
l_error_message := ‘Invalide Currency Code…’;
END;
BEGIN
SELECT term_id,name
into l_term_id,l_term_name
FROM ra_terms_tl
WHERE upper(name) = upper(’30 Days’);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := ‘N’;
l_error_message := ‘Invalide Terms Name…’;
END;
BEGIN
SELECT DISTINCT HCAS.cust_acct_site_id,HCA.cust_account_id
INTO l_address_id,l_customer_id
FROM hz_parties HP
,hz_party_sites HPS
,hz_cust_accounts HCA
,hz_cust_acct_sites_all HCAS
,hz_cust_site_uses_all HCSU
WHERE HCA.party_id = HP.party_id
AND HP.party_id = HPS.party_id
AND HCA.cust_account_id = HCAS.cust_account_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
AND HCSU.site_use_code = ‘BILL_TO’
AND HCSU.primary_flag = ‘Y’
AND upper (ltrim (rtrim (HP.party_name))) = upper (ltrim (rtrim (‘Customer Name’)))
AND HCAs.org_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := ‘N’;
l_error_message := ‘Invalide Customer Name…’;
END;
INSERT INTO ra_interface_lines_all(
INTERFACE_LINE_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
CUST_TRX_TYPE_ID,
cust_trx_type_name,
TRX_DATE,
GL_DATE,
CURRENCY_CODE,
term_id,
term_name,
orig_system_bill_customer_id,
orig_system_bill_address_id,
orig_system_sold_customer_id,
QUANTITY,
AMOUNT,
DESCRIPTION,
conversion_type,
conversion_rate,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
org_id
)
Values
(
RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
‘Invoice Migration’,
‘LINE’,
l_cust_trx_type_id,
l_cust_trx_type_name,
sysdate,
sysdate,
l_currency_code,
l_term_id,
l_term_name,
l_customer_id,
l_address_id,
l_customer_id,
1,
1000,
‘AR Invoice 001’,
‘User’,
1,
‘Invoice Conversions’,
‘1001’,
l_org_id
);
INSERT INTO ra_interface_distributions_all
(
INTERFACE_LINE_ID
,account_class
,amount
,code_combination_id
,percent
,interface_line_context
,interface_line_attribute1
,org_id
)
VALUES
(
RA_CUSTOMER_TRX_LINES_S.CURRVAL,
‘REV’
,1000
,l_gl_id_rev
,100
,’Invoice Conversions’,
‘1001’,
l_org_id
);
Commit;
END XX_ARINVOICE_INTERFACE;
/