AR Invoice Interface

AR Invoice Interface

The main three steps for AR Invoice Interface are:

1] Put the data into your staging tables.

2] Calls your package to validate the data and load into AR Interface tables (RA_INTERFACE_LINES_ALL & RA_INTERFACE_DISTRIBUTIONS_ALL).

3] Then submits a concurrent request for AutoInvoice.

If any errors occur it can be found in ra_interface_errors_all table. The concurrent program has 2 stages. First the Master program fires which intern kicks of the Import Program. Once this is completed data is inserted into the following tables.

1) ra_customer_trx_all (Invoice Header Info)

2) ra_customer_trx_lines_all (Invoice Line Level Info)

3) ra_cust_trx_line_gl_dist_all (Accounting Info. One record for each Account Type is inserted into this… ex. Receivable Revenue Tax Freight etc)

4) ar_payment_schedules_all (All Payment related info)

Validations:

Validation are generally done on the below columns.

• Batch_source_name
• Set_of_books_id
• Orig_sys_batch_name
• orig_system_bill_customer_ref
• orig_system_bill_address_ref
• Line_Type
• Currency_Code
• Term_name
• Transaction_type
• Interface_line_attribute1-7
• Account_class
• Accounting Flexfields segments

1- AR Transaction Type Validation: Check if the Transaction type provided in data file is defined in AR transaction types (RA_CUST_TRX_TYPES_ALL)

2- Transaction Batch Source Validation: Check if the source provided in data file is defined in AR transaction Batch source (RA_BATCH_SOURCES_ALL).

3- Invoice Currency Validation: Check if the currency provided in data file is defined in AR Currency (FND_CURRENCIES).

4- Customer Validation: Check if the Bill to Customer Number, Ship to Customer Number, Bill to Custom Location, Ship to Customer Location provided in the data file is defined in AR Customer (RA_CUSTOMERS).

5- Primary Sales Representative Validation: Sales representative number to be hardcode to “-3” for “No Sales Credit.”

6- Term Name: Check if the Term name provided in the data file is defined in Payment terms (RA_TERMS)

7- Inventory Item Validation: Check if the Item provided in data file is defined in Inventory Items (MTL_SYSTEM_ITEMS).

8- Unit of Measurement validation: Check if the UOM provided is defined in MTL_UNITS_OF_MEASURE Table

9- Invoice Tax Code Validation: Check if the Tax Code provided in data file is defined in AR_VAT_TAX_ALL_B Table.

10- Invoice GL Date Validation: Check if the GL Data of provided invoices is in open period.

For MOAC:

You need to add the below columns and need to do validations if your application supports MOAC.

• conversion_type
• conversion_rate
• conversion_date

Sample Code to run Autoinvoice Master Program:

DECLARE
v_phase 	VARCHAR2(100);
v_dev_phase 	VARCHAR2(100);
v_status 	VARCHAR2(100);
v_dev_status 	VARCHAR2(100);
v_message 	VARCHAR2(100);
v_reqid 	NUMBER(15);
v_pid 		BOOLEAN;
v_user_id  	NUMBER(30);
v_batch_source_id NUMBER;
v_order    	NUMBER;
v_org_id   	NUMBER;
v_resp_id  	number;
v_resp_appl_id 	number;
v_appl_short_name fnd_application.application_short_name%TYPE;

CURSOR c1 IS
select fcr.responsibility_id
,fr.application_id
from fnd_concurrent_requests fcr
,fnd_responsibility fr
where fcr.request_id = '${4}'
and   fcr.responsibility_id = fr.responsibility_id;

CURSOR c2 IS
select fa.application_short_name
from fnd_concurrent_programs fcp, fnd_application fa
where fcp.concurrent_program_name = v_program_short_name
and fcp.application_id = fa.application_id;

CURSOR c_batch_id IS
SELECT 1, batch_source_id, name
FROM apps.ra_batch_sources_all
WHERE name IN (SELECT distinct a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name like '%DEBIT'
AND filename = '${file1}')
UNION
SELECT 2, batch_source_id, name
FROM apps.ra_batch_sources_all
WHERE name IN (SELECT distinct a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name like '%CREDIT'
AND filename = '${file1}')
order by 1;

BEGIN
open c1;
fetch c1 into v_resp_id,v_resp_appl_id;
close c1;

open c2;
fetch c2 into v_appl_short_name;
close c2;

FOR v_batch_data IN c_batch_id LOOP

fnd_global.apps_initialize('${FCP_USERID}',v_resp_id,v_resp_appl_id);

v_reqid := fnd_request.submit_request('AR',
'RAXMTR',
NULL,
to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
FALSE,
'1',
-99,
v_batch_data.batch_source_id,
v_batch_data.name,
to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
'Y',
NULL);
commit;
v_pid := fnd_concurrent.wait_for_request(v_reqid,
3,
0,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message);
END LOOP;
END;