AR invoice interface tables in oracle apps r12.AR Invoice Conversion in Oracle apps r12

AR invoice interface tables in oracle apps r12.AR Invoice Conversion in Oracle apps r12

In this Post , I will share you the Complete PLSQL code of AR Invoice Interface in Oracle Apps. This code helps to do the Conversion of the AR invoices in Oracle Apps. You will get the complete set by step process to import AR invoices in Oracle Interface. This Code helps to insert the data in the AR invoices Interface in the Required format.

 

As a good approach to insert the Data in the AR Interface table is this , First we should enter the AR data in the Staging table( Any custom Table ) and then after Validation we should move the Data to AR Standard Interface Tables.

In AR Invoice Interface , These below three tables use.

ra_interface_lines_all

ra_interface_distributions_all

 

To Find the AR Invoice Interface Rejected records we can go through AR Interface Error Table As Below.

ra_interface_errors_all

 

Concurrent Program To Import Data from AR Interface Table to AR Invocie Base Tables

------------------------------------------------------------

Autoinvoice Import Program

 

Here Below we are sharing the PLSQL script to Insert the Data in the AR invoice interface 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,

--40000

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;

/