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:

view sourceprint?

01

DECLARE

02	v_phase     VARCHAR2(100);

03	v_dev_phase     VARCHAR2(100);

04	v_status    VARCHAR2(100);

05	v_dev_status    VARCHAR2(100);

06	v_message   VARCHAR2(100);

07	v_reqid     NUMBER(15);

08	v_pid       BOOLEAN;

09	v_user_id   NUMBER(30);

10	v_batch_source_id NUMBER;

11	v_order     NUMBER;

12	v_org_id    NUMBER;

13	v_resp_id   number;

14	v_resp_appl_id  number;

15	v_appl_short_name fnd_application.application_short_name%TYPE;

16

17	CURSOR c1 IS

18	select fcr.responsibility_id

19	,fr.application_id

20	from fnd_concurrent_requests fcr

21	,fnd_responsibility fr

22	where fcr.request_id = '${4}'

23	and   fcr.responsibility_id = fr.responsibility_id;

24

25	CURSOR c2 IS

26	select fa.application_short_name

27	from fnd_concurrent_programs fcp, fnd_application fa

28	where fcp.concurrent_program_name = v_program_short_name

29	and fcp.application_id = fa.application_id;

30

31	CURSOR c_batch_id IS

32	SELECT 1, batch_source_id, name

33	FROM apps.ra_batch_sources_all

34	WHERE name IN (SELECT distinct a.batch_source_name

35	FROM xxfin.xxfin_ar_ol_invoices a

36	WHERE a.batch_source_name like '%DEBIT'

37	AND filename = '${file1}')

38

UNION

39	SELECT 2, batch_source_id, name

40	FROM apps.ra_batch_sources_all

41	WHERE name IN (SELECT distinct a.batch_source_name

42	FROM xxfin.xxfin_ar_ol_invoices a

43	WHERE a.batch_source_name like '%CREDIT'

44	AND filename = '${file1}')

45	order by 1;

46

47

BEGIN

48

open c1;

49	fetch c1 into v_resp_id,v_resp_appl_id;

50

close c1;

51

52

open c2;

53	fetch c2 into v_appl_short_name;

54

close c2;

55

56	FOR v_batch_data IN c_batch_id LOOP

57

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

59

60	v_reqid := fnd_request.submit_request('AR',

61

'RAXMTR',

62

NULL,

63	to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),

64

FALSE,

65

'1',

66

-99,

67	v_batch_data.batch_source_id,

68	v_batch_data.name,

69	to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),

70	NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,

71	NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,

72

'Y',

73

NULL);

74

commit;

75	v_pid := fnd_concurrent.wait_for_request(v_reqid,

76

3,

77

0,

78

v_phase,

79

v_status,

80	v_dev_phase,

81	v_dev_status,

82	v_message);

83

END LOOP;

84

END;