AR Invoice Interface
April 11, 2011 Leave a comment
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;
Dibyajyoti Koch:A Blog on Oracle Application 