Oracle Transaction Interface, Auto Accounting Setups and Auto Invoice Concepts by Pranay Tiwari

Before Proceeding towards interface concepts , We have to go through the base table of Oracle transactions.

Base Tables : 

RA_CUSTOMER_TRX_ALL   holds the header level data
RA_CUSTOMER_TRX_LINES_ALL  holds line level data
RA_CUST_TRX_LINE_GL_DIST_ALL holds distribution level data
RA_BATCHES_ALL    table contains batch level information
RA_BATCH_SOURCES_ALL table contains batch name
RA_CUST_TRX_TYPES_ALL table contain information regarding transaction type data

Interface Tables : 

RA_INTERFACE_LINES_ALL     Line Interface Level Data
RA_INTERFACE_DISTRIBUTIONS_ALL Distribution interface level data
RA_INTERFACE_ERRORS    contains the information of rejected records from auto invoice program


Auto Accounting rules and Set ups in Oracle .

Auto accounting rule is nothing but a feature in oracle which automatically creates general ledger accounting flexifields at Distribution Level. In-order to set up Auto Accounting rule

Go to Setup -->  Transaction --> Transaction Types

After clicking on Transaction Types , Go to Accounts tab fill the fields Receivable Accounts , Revenue Accounts and Tax account if there any taxes.

If you are working on Oracle Transaction Interface program to load data by using Auto accounting rule . Then only RA_INTERFACE_LINES_ALL table needs to populated.



Transaction Interface Code 

Below code will create Transaction in Oracle .After execution of below code , Auto Invoice master program needs to call which in turns call Auto Invoice Import Program . If it fails to call  Auto Invoice Import Program then there might be in issue in data .

BEGIN
   mo_global.SET_POLICY_CONTEXT ('S', 414);                     -- your org id
   mo_global.init ('AR');
   Fnd_Global.APPS_INITIALIZE (USER_ID        => 120,
                               RESP_ID        => 51218,
                               RESP_APPL_ID   => 222);

   INSERT INTO ra_interface_lines_all (interface_line_context,
                                       interface_line_attribute1,
                                       interface_line_attribute2,
                                       interface_line_attribute4,
                                       batch_source_name,
                                       set_of_books_id,
                                       line_type,
                                       description,
                                       currency_code,
                                       cust_trx_type_name,
                                       term_NAME,
                                       orig_system_bill_customer_id,
                                       orig_system_bill_address_id,
                                       conversion_type,
                                       conversion_rate,
                                       trx_date,
                                       gl_date,
                                       amount,
                                       org_id,
                                       trx_number)
     VALUES   ('XXXX BS400',
               XXNUC_CUSTOM_S.NEXTVAL,
               '1',
               'NA',
               'XXXX BS400',
               '2103',
               'LINE',
               'Test Interface invoice through Auto Accounting  ',
               'USD',
               'DM custom Bm',
               '.5% 10 NET 30',
               40040,                                      ---Cust account  id
               6825,                                       --Cust acct site id
               'User',
               '1',
               TO_DATE (SYSDATE),
               TO_DATE (SYSDATE),
               109560,
               '414',
               XXNUC_CUSTOM_S.NEXTVAL);

   COMMIT;
END;




Note: If you are using Auto accounting rule then only RA_INTERFACE_LINES_ALL table is enough to create transaction where RA_INTERFACE_DISTRIBUTIONS_ALL is not needed , Since all distribution accounts will set up through Auto Accounting Rule



After execution of this script , Auto Invoice Program Needs to be called which in turns call Auto Invoice Import Program.




Tracking Auto Invoice Errors 

In order check records rejected by Auto invoice front end . Kindly follow the below steps to track the erroneous entries.


Go to Receivable Managers--> Control

Control-->AutoInvoice-->Interface Exceptions

After clicking on Interface Exceptions , Just query the form and you will get erroneous entries information.

In order to find the rejected records from back end. RA_INTERFACE_ERRORS holds the information  of records rejected by Auto Invoice Program