Total Pageviews

February 26, 2016

2/26/2016 08:48:00 PM
Oracle Receivables Auto Invoice

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.
ebiztechnics Receivables Auto Invoice  Data flow diagram


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.

 

 
Related Posts Plugin for WordPress, Blogger...