Oracle Applications : Receivables Auto invoice Interface Technical Snippets

This blog post gives the technical components required to implement an interface from the legacy system to Oracle Receivables.
Auto Invoice is a powerful and a flexible tool that is used to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos and on-account credits in Oracle receivables.
Auto Invoice selects data from the interface tables and creates transactions in Receivables. Auto Invoice interface allows you to automatically create invoices for your customers.

Oracle Receivables Tables

RA_BATCHES_ALL

RA_CUSTOMER_TRX_ALL

RA_CUSTOMER_TRX_LINES_ALL

RA_CUST_TRX_LINE_GL_DIST_ALL

RA_CUST_ TRX_LINE_SALESREP_ALL

AR_PAYMENT_SCHEDULES_ALL

AR_RECEIVABLE_APPLICATIONS_ALL

AR_ADJUSTMENTS_ALL

High Level Function Design 

Purpose of the AutoInvoice Interface 

The purpose of the Auto Invoice Interface is used to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos and on-account credits in Oracle receivables via the Auto Invoice open interface.

Function 

The following logical function for AutoInvoice Interface

1. Load the Data to Temporary Table

   The legacy system will transfer the legacy file in a flat-file format to the temporary table XXAR_AUTOINV_TEMP.

2. Validate and load data into the interface table

   Once the data is loaded into temporary tables, validation on the data will take place and then be load into the interface tables.

3. Creation of Invoices in Oracle Receivables

   Run the Autoinvoice Open Interface and import the standard invoices

          4. Generate the Errors File    

1. SQL-LOADER Bad file 

2. On running the AutoInvoice Master Import Program, the error records are dumped into 

    RA_INTERFACE_ERRORS table.

General Assumption 

Following assumption for the Autoinvoice interface 

1. Organization is defined.

2. Transaction sources have been setup

3. Grouping Rules have been set up

4. Accounting Rules have been setup 

5. Line Ordering Rules have been set up

6. Transaction flexfield needs to have been appropriately defined for that batch source.

Source System	Source Field	Data Type	Delimiter	Required for Module
LS	INTERFACE_LINE_CONTEXT	CHAR	COMMA	YES
LS	BATCH_SOURCE_NAME	CHAR	COMMA	YES
LS	SET_OF_BOOKS_ID	NUMBER	COMMA	YES
LS	LINE_TYPE	CHAR	COMMA	YES
LS	CURRENCY_CODE	CHAR	COMMA	YES
LS	DESCRIPTION	CHAR	COMMA	YES
LS	AMOUNT	NUMBER	COMMA	NO
LS	CUST_TRX_TYPE_ID	NUMBER	COMMA	NO
LS	TERM_ID	NUMBER	COMMA	NO
LS	BILL_CUSTOMER_ID	NUMBER	COMMA	YES
LS	BILL_ADDRESS_ID	NUMBER	COMMA	YES
LS	SHIP_CUSTOMER_ID	NUMBER	COMMA	YES
LS	SHIP_ADDRESS_ID	NUMBER	COMMA	YES
LS	LINK_TO_LINE_CONTEXT	CHAR	COMMA	YES
LS	ORDER_ID	CHAR	COMMA	YES
LS	LEGACY_LINE_ID	CHAR	COMMA	YES
LS	CONVERSION_TYPE	CHAR	COMMA	YES
LS	CONVERSION_RATE	NUMBER	COMMA	NO
LS	CONVERSION_DATE	DATE	COMMA	NO
LS	TRX_DATE	DATE	COMMA	NO
LS	QUANTITY	NUMBER	COMMA	NO
LS	QUANTITY_ORDERED	NUMBER	COMMA	NO
LS	UNIT_SELLING_PRICE	NUMBER	COMMA	NO
LS	UNIT_STANDARD_PRICE	NUMBER	COMMA	NO
LS	PRINTING_OPTION	CHAR	COMMA	NO
LS	PRIMARY_SALESREP_ID	NUMBER	COMMA	NO
LS	INVENTORY_ITEM_ID	NUMBER	COMMA	NO
LS	UOM_CODE	CHAR	COMMA	NO
LS	VAT_TAX_ID	NUMBER	COMMA	NO
LS	TAX_EXEMPT_FLAG	CHAR	COMMA	NO
LS	ORG_ID	NUMBER	COMMA	NO
LS	AMOUNT_INCLUDES_TAX_FLAG	CHAR	COMMA	NO

 

Target Application Table	Target Application Table Column	Target Column Datatype	Not Null	Source System Table/File Name	Source System Table/File Field Name	Source System  Datatype	Default Value
RA_INTERFACE_LINES_ALL	Interface_line_context	CHAR	Y	XXAR_AUTOINV_TEMP	Interface_line_context	CHAR
RA_INTERFACE_LINES_ALL	Batch_source_name	CHAR	Y	XXAR_AUTOINV_TEMP	Batch_source_name	CHAR
RA_INTERFACE_LINES_ALL	Set_of_books_id	NUMBER	Y	XXAR_AUTOINV_TEMP	Set_of_books_id	NUMBER
RA_INTERFACE_LINES_ALL	Line_type	CHAR	Y	XXAR_AUTOINV_TEMP	Line_type	CHAR
RA_INTERFACE_LINES_ALL	Currency_code	CHAR	Y	XXAR_AUTOINV_TEMP	Currency_code	CHAR
RA_INTERFACE_LINES_ALL	Description	CHAR	Y	XXAR_AUTOINV_TEMP	Description	CHAR
RA_INTERFACE_LINES_ALL	Amount	NUMBER	N	XXAR_AUTOINV_TEMP	Amount	NUMBER
RA_INTERFACE_LINES_ALL	Cust_trx_type_id	NUMBER	N	XXAR_AUTOINV_TEMP	Cust_trx_type_id	NUMBER
RA_INTERFACE_LINES_ALL	Term_id	NUMBER	N	XXAR_AUTOINV_TEMP	Term_id	NUMBER
RA_INTERFACE_LINES_ALL	Bill_customer_id	NUMBER	Y	XXAR_AUTOINV_TEMP	Bill_customer_id	NUMBER
RA_INTERFACE_LINES_ALL	Bill_address_id	NUMBER	Y	XXAR_AUTOINV_TEMP	Bill_address_id	NUMBER
RA_INTERFACE_LINES_ALL	Ship_customer_id	NUMBER	Y	XXAR_AUTOINV_TEMP	Ship_customer_id	NUMBER
RA_INTERFACE_LINES_ALL	Ship_address_id	NUMBER	Y	XXAR_AUTOINV_TEMP	Ship_address_id	NUMBER
RA_INTERFACE_LINES_ALL	Link_to_line_context	CHAR	Y	XXAR_AUTOINV_TEMP	Link_to_line_context	CHAR
RA_INTERFACE_LINES_ALL	Order_id	CHAR	Y	XXAR_AUTOINV_TEMP	Order_id	CHAR
RA_INTERFACE_LINES_ALL	Legacy_line_id	CHAR	Y	XXAR_AUTOINV_TEMP	Legacy_line_id	CHAR
RA_INTERFACE_LINES_ALL	Conversion_type	CHAR	Y	XXAR_AUTOINV_TEMP	Conversion_type	CHAR
RA_INTERFACE_LINES_ALL	Conversion_rate	NUMBER	N	XXAR_AUTOINV_TEMP	Conversion_rate	NUMBER
RA_INTERFACE_LINES_ALL	Conversion_date	DATE	N	XXAR_AUTOINV_TEMP	Conversion_date	DATE
RA_INTERFACE_LINES_ALL	Trx_date	DATE	N	XXAR_AUTOINV_TEMP	Trx_date	DATE
RA_INTERFACE_LINES_ALL	Quantity	NUMBER	N	XXAR_AUTOINV_TEMP	Quantity	NUMBER
RA_INTERFACE_LINES_ALL	Quantity_ordered	NUMBER	N	XXAR_AUTOINV_TEMP	Quantity_ordered	NUMBER
RA_INTERFACE_LINES_ALL	Unit_selling_price	NUMBER	N	XXAR_AUTOINV_TEMP	Unit_selling_price	NUMBER
RA_INTERFACE_LINES_ALL	Unit_standard_price	NUMBER	N	XXAR_AUTOINV_TEMP	Unit_standard_price	NUMBER
RA_INTERFACE_LINES_ALL	Printing_option	CHAR	N	XXAR_AUTOINV_TEMP	Printing_option	CHAR
RA_INTERFACE_LINES_ALL	Primary_salesrep_id	NUMBER	N	XXAR_AUTOINV_TEMP	Primary_salesrep_id	NUMBER
RA_INTERFACE_LINES_ALL	Inventory_item_id	NUMBER	N	XXAR_AUTOINV_TEMP	Inventory_item_id	NUMBER
RA_INTERFACE_LINES_ALL	Uom_code	CHAR	N	XXAR_AUTOINV_TEMP	Uom_code	CHAR
RA_INTERFACE_LINES_ALL	Vat_tax_id	NUMBER	N	XXAR_AUTOINV_TEMP	Vat_tax_id	NUMBER
RA_INTERFACE_LINES_ALL	Tax_exempt_flag	CHAR	N	XXAR_AUTOINV_TEMP	Tax_exempt_flag	CHAR
RA_INTERFACE_LINES_ALL	Org_id	NUMBER	Y	XXAR_AUTOINV_TEMP	Org_id	NUMBER
RA_INTERFACE_LINES_ALL	Amount_includes_tax_flag	CHAR	N	XXAR_AUTOINV_TEMP	Amount_includes_tax_flag	CHAR
RA_INTERFACE_LINES_ALL	Created_by	NUMBER	N	XXAR_AUTOINV_TEMP	Created_by	NUMBER
RA_INTERFACE_LINES_ALL	Creation_date	DATE	N	XXAR_AUTOINV_TEMP	Creation_date	DATE

                                                                                                 N : Number  V : Varchar2

Approach

This module consists of PL/SQL stored procedure that inserts data into the receivables interface tables.

Technical Overview

Execution Method:

The XXARAINVVLD Concurrent Program will be used to validate and do the process accordingly after validation.

Assumptions:

1. Organization has been defined.

2. Transaction Source has been set up.

3. Grouping rules have been set up.

4. Accounting rules have been set up.

5. Line Ordering Rules have been set up.

6. Transaction Flexfield has been defined to uniquely identify every invoice.

Detailed Program Logic (Pseudo Code)
1. Validate batch name from RA_BATCH_SOURCES_ALL where batch_source_Type=’FOREIGN’.
2. Check for set of books from AR_SYSTEM_PARAMETERS_ALL
3. Check for Cust_Trx_Type from RA_CUST_TRX_TYPES_ALL
4. Check Term ID from RA_TERMS
5. Check Bill_To customer from RA_CUSTOMERS.
6. Check Bill_To Address with the following validation –
SELECT A.Address_ID INTO V_Bill_Address_ID
   FROM RA_CUSTOMERS C,RA_ADDRESSES_ALL A,RA_SITE_USES_ALL S
       WHERE A.Customer_ID=C.Customer_ID AND
 C.Customer_ID=I.Bill_Customer_ID AND
 A.Address_ID=I.Bill_Address_ID AND
 A.Org_Id=I.ORG_ID AND
 A.Address_ID=S.Address_ID AND
 S.Site_Use_Code='BILL_TO' AND
 S.ORG_ID=I.ORG_ID;   (where ‘I’ is cursor variable)
7. Check Ship_To customer from RA_CUSTOMERS.
8. Check Ship_To Address with the following validation –
SELECT A.Address_ID INTO V_Bill_Address_ID
   FROM RA_CUSTOMERS C,RA_ADDRESSES_ALL A,RA_SITE_USES_ALL S
       WHERE A.Customer_ID=C.Customer_ID AND
 C.Customer_ID=I.Ship_Customer_ID AND
 A.Address_ID=I.Ship_Address_ID AND
 A.Org_Id=I.ORG_ID AND
 A.Address_ID=S.Address_ID AND
 S.Site_Use_Code='SHIP_TO' AND
 S.ORG_ID=I.ORG_ID;   (where ‘I’ is cursor variable)
9. Check for Printing_Option from AR_LOOKUPS_OLD where  Lookup_Type=‘INVOICE_PRINT_OPTION’
10 Check for salesrep_ID from RA_SALESREPS_ALL
11 Check for valid item from INV.MTL_SYSTEM_ITEMS_B and INVOICE_ENABLED=’Y’.
12 Check for UOM_Code from MTL_UNITS_OF_MEASURE.
13 Check for Tax_Exempt_Flag from AR_LOOKUPS_OLD where  Lookup_Type=‘ TAX_CONTROL_FLAG’
14   Check that the values of Order_ID and Legacy_Line_ID that form the Line_Transaction_Flexfield are in accordance to the setup in the system. These are to be populated into the interface_line_attribute columns.
15 Insert Valid Data into RA_INTERFACE_LINES_ALL table if they pass all these validations or else update the error_des field for the error record with the valid error message. The imported_flag should be updated to ‘Y’ or ‘N’ accordingly.