AR one time insert process

AR INVOICE INTERFACE
************************

Interface Tables  are  :-
-----------------------
ra_interface_lines_all

ra_interface_distributions_all

ra_interface_errors_all

Concurrent Program to import data from interface tables :-
------------------------------------------------------------
Autoinvoice Import Program

INSERT INTO ra_interface_lines_all(

INTERFACE_LINE_ID,

BATCH_SOURCE_NAME,

LINE_TYPE,

TRX_DATE,

GL_DATE,

CURRENCY_CODE,

term_id,

term_name,

orig_system_bill_customer_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',

       '31-DEC-2012',

       '31-dec-2012',

       'USD',

        1002,

       'Due 10th',

       2636,

       2636,

       10,

       1000,

       'Sentinel Standard Desktop - Rugged',

       'User',

         1,

       'Invoice Migration',

       'RINV_000000002',

        204

       )

  

--delete from ra_interface_lines_all

select * from ra_interface_lines_all

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'

,4000

,1001

,100

,'Invoice Migration',

'RINV_000000002',

204

);

select * from ra_interface_distributions_all where INTERFACE_LINE_ID = 1273450

select * from ra_customer_trx_all where TRX_NUMBER= '501141'

select * from ra_customer_trx_lines_all where CUSTOMER_TRX_ID = 759022

select * from mtl_system_items_b where segment1='AS54999'

fnd_global.apps_initialize

mo_global.set_policy_context

xla_security_pkg.set_security_context

INSERT INTO RA_INTERFACE_LINES_ALL

(batch_source_name,line_type,description,conversion_type,currency_code,

inventory_item_id,created_by,creation_date,last_updated_by,last_update_date,amount,term_name,

term_id,

trx_date, primary_salesrep_id,

cust_trx_type_id,

orig_system_bill_customer_id,

orig_system_bill_address_id,

orig_system_sold_customer_id,

conversion_rate, uom_code,

line_number,

quantity,org_id

)

VALUES ('LEGACY', 'LINE', 'Maintenance', 'User',

'USD',2155, 1012015,SYSDATE,1012015,SYSDATE,10000, 'NET60', 1020,SYSDATE, 1005,

1,2636,1000,2636,1, 'Ea',1,

2, 204

);

select * from RA_INTERFACE_LINES_ALL

select * from ra_interface_errors_all where INTERFACE_LINE_ID=1273452

select * from ra_customer_trx_all where TRX_NUMBER= '501141'

select * from ra_customer_trx_lines_all where CUSTOMER_TRX_ID = 759022

select * from mtl_system_items_b where segment1='AS54999'

select * from all_tables where table_name like 'RA_CUST%'

select * from RA_CUST_TRX_LINE_SALESREPS_ALL

select * from ra_batch_sources_all

select * from RA_BATCH_SOURCES_ALL

select * from RA_CUST_TRX_TYPES_ALL

select * from RA_TERMS

select * from MTL_UNITS_OF_MEASURE

select * from RA_ADDRESSES_ALL

select * from ra_customer_trx_all where trunc(creation_date) = trunc(sysdate)

select * from RA_CUSTOMERS where CUSTOMER_NAME='A. C. Networks'

select * from RA_INTERFACE_SALESCREDITS_ALL

select * from ra_interface_lines_all

delete from ra_interface_lines_all

select * from RA_INTERFACE_DISTRIBUTIONS_ALL

select * from ra_interface_errors_all

INSERT INTO RA_INTERFACE_LINES_ALL

(batch_source_name,line_type,description,conversion_type,currency_code,

inventory_item_id,created_by,creation_date,last_updated_by,last_update_date,amount,term_name,

term_id,

trx_date, primary_salesrep_id,

cust_trx_type_id,

orig_system_bill_customer_id,

orig_system_bill_address_id,

orig_system_sold_customer_id,

conversion_rate, uom_code,

line_number,

quantity,org_id,INTERFACE_LINE_CONTEXT,INTERFACE_LINE_ATTRIBUTE1

)

VALUES ('LEGACY', 'LINE', 'Maintenance', 'User',

'USD',2155, 1012015,SYSDATE,1012015,SYSDATE,10000, 'Net 15', 1020,SYSDATE, 51022,

1684,5453,5453,5453,1, 'Ea',1,

2, 204,'VISION BUILD','YAL001'

);

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;