Autoinvoice is a powerful and flexible tool, used to import and validate transaction data from external financial systems or other modules of Oracle Applications. Users can create Invoices, debit memos, credit memos, and on-account credits in Oracle Receivables using Autoinvoice.
Auto invoice Process:
Data for creating invoices comes in from various sources such as external systems, Order Management, Oracle Projects, Oracle Services and so on. The interface loading program is run to populate the interface tables. When the Autoinvoice Master Program and Import Program are run, the data passes through certain validation processes and finally populates the tables of the Oracle Receivables module.
The feeder program populates data into the Autoinvoice interface tables, namely
RA_INTERFACE_LINES_ALL,
RA_INTERFACE_DISTRIBUTIONS_ALL and
RA_INTERFACE_SALESCREDITS_ALL
Every line must use the RA_INTERFACE_LINES_ALL table. The other two tables are optional.
RA_INTERFACE_LINES_ALL Table:
This table contains information related to all transactions to be processed by AutoInvoice. Transactions include invoices, debit memos, credit memos, and on-account credits. This table holds the key data such as bill-to customer, ship-to customer, and transaction date.
• Each record contains line, tax, freight, or finance charges information.
• The Line Type field identifies the type of information contained in the record.
• A record can be a parent record: Line, Header Freight, or Charges; or a child record: Tax or line-level Freight.
• A child record is linked to the parent record using the Link-To Transaction flex field.
RA_INTERFACE_DISTRIBUTIONS_ALL Table:
Oracle Order Management does not use this table because Auto Accounting creates the distributions in Oracle Receivables.
This table contains accounting distributions to be used by the transactions defined in RA_INTERFACE_LINES_ALL.
• Accounts defined in this table override any accounts created using Auto Accounting.
• You can choose to pass some or all account information to AutoInvoice. Any accounts that are not passed will be derived using Auto Accounting.
• Records in this table are linked to records in the RA_INTERFACE_LINES_ALL table using the Transaction flex field.
RA_INTERFACE_SALESCREDITS_ALL Table:
This table contains all sales credit information for the transactions in the
RA_INTERFACE_LINES_ALL table.
• The two tables are linked using the Transaction flex field.
• This table is required to track sales credits.
When the Autoinvoice program is run, data is populated into the tables listed below
-RA_BATCHES_ALL
-RA_CUSTOMER_TRX_ALL
-RA_CUSTOMER_TRX_LINES_ALL
-RA_CUST_TRX_LINE-GL_DIST_ALL
-RA_CUST_TRX_LINE_SALESREPS_ALL
-AR_RECEIVABLE_APPLICATIONS_ALL
-AR_PAYMENT_SCHEDULES_ALL
-AR_PAYMENT_SCHEDULES_ALL
-RA_INTERFACE_ERRORS_ALL
Grouping Rules:
Autoinvoice uses grouping rules to group lines to create one transaction. Mandatory grouping attributes cannot be dropped. However, users can add optional grouping attributes.
The grouping rule ‘DEFAULT’ is a seeded value, which contains all the mandatory grouping attributes.
Grouping rules are mandatory and determine how transaction lines are grouped into transactions Optionally, you can use line ordering rules to determine the order in which lines are displayed on a transaction.
To uniquely identify imported transactions and link the tax, freight, commitments, and credit memos, define the Transaction flex fields.
AutoInvoice validates your data by ensuring that the columns in Receivables Interface Tables reference the appropriate values and columns in Receivables.
Autoinvoice uses the following hierarchy to determine the grouping rule that is to be used:
· Transaction batch source
· Customer site level
· Customer profile level
· System Options Window
If a grouping rule is not specified, either, at the Transaction Source or at the Customer Site level or in the Customer Profile Classes window, Autoinvoice uses the default grouping rule specified in the System Options.
Transaction Flex fields:
Transaction flex fields are descriptive flex fields that AutoInvoice uses to uniquely identify transaction lines.
Because they are unique for each transaction line, they can also be used to reference and link to other lines.
Receivables lets you determine how you want to build your transaction flex field structure and what information you want to capture.
Define a flex field for each import source. Specify which one to use during import.
Use the Reference Flex field to link a credit memo line to a transaction. This passes information like order number, project number, and shipping information.
There are four types of transaction flex fields are
· Line Transaction Flex field
· Reference Transaction Flex field
· Link-to Transaction Flex field
· Invoice Transaction Flex field
Line Transaction Flex Field: Use columns INTERFACE_LINE_ATTRIBUTE1-15 and INTERFACE_LINE_CONTEXT to define the Line Transaction Flex field. They are unique for each record in the interface tables and therefore can be used as record identifiers.
The INTERFACE_LINE_CONTEXT field in RA_INTERFACE_LINES_ALL indicates to Autoinvoice, the Line Transaction Flex field code that is to be used. The Context value corresponds to the code field. For example Order Entry, Intercompany etc.
The Line Transaction Flexfield must be defined mandatorily. Autoinvoice always uses the Line Transaction Flexfield structure for both, the Link-to and Reference information when importing invoices. You need to explicitly define the Link-to, Reference and Invoice Transaction Flexfield structures, only if this information is to be displayed on a custom window.
Source: ORDER MANAGEMENT the line transaction flex filed code is ORDER ENTRY.
Reference Transaction Flexfield:
Reference Transaction Flexfields have the same structure as the Line Transaction Flexfields. Reference Transaction Flexfields are used to refer to previously processed transactions.
For example, to import a credit memo and apply it to an invoice or associate an invoice to a specific commitment, use the Reference Transaction Flexfield.
To refer a credit memo to a specific invoice use the REFERENCE_LINE_ATTRIBUTE1-15 and REFERENCE_LINE_CONTEXT columns of the credit memo to enter the value of the Line Transaction Flexfield of the invoice. If the invoice being credited, is already in AR, populate the REFERENCE_LINE_ID column of the table RA_INTERFACE_LINES_ALL.
Link-to Transaction Flexfield:
Link-to transaction flexfields also have the same structure as the Line Transaction flexfield. Use Link-to transaction flexfields to link transaction lines, to each other. For example, to import a freight charge that is associated with a particular transaction line, use the LINK_TO_LINE_ATTRIBUTE1-15 and LINK_TO_LINE_CONTEXT columns of the freight line and populate the value of the Line Transaction Flexfield of the particular transaction line.
Invoice Transaction Flexfield:
Invoice Transaction Flexfield has a structure similar to that of the Line Transaction Flexfield, but includes only the header level segments. Segments included in the Invoice Transaction Flexfield should be included in the Autoinvoice grouping rules. To define the header level transaction flexfield, query Invoice Transaction Flexfield in the title field of the descriptive flexfield segments window and enter the context and segments associated with this transaction flexfield.
All segments in the Line Level transaction Flexfield that refer to the header information must also exist in the header level transaction flexfield.
Auto invoice Validation:
AutoInvoice Validation Validates lines for:
-Accounting Rules
-Accounting Periods.
AutoInvoice validates the data for compatibility with Receivables. It ensures that the columns in Receivables’ interface tables reference the appropriate values and columns in Recievables. It checks for:
Existence: Ensures that the values for some columns are already defined.
Batch Sources: Ensures consistent values for fields in the Transaction Sources window.
Uniqueness: Ensures that the invoice number you supply is unique within a given batch source.
Precision: Ensures that the amount and accounted amount have the correct precision.
Cross Validation: Ensures that column values agree with each other.
Autoinvoice Master Program:
Autoinvoice Master Program selects and marks records in the interface tables, for processing based on the parameters entered. Once the records are selected, the Autoinvoice Import Program is spawned. Autoinvoice Master Program does not provide any report or output. The Master program allows running of several instances of Autoinvoice to improve system performance and to facilitate importing of transactions quickly.
The AutoInvoice Master Program can be submitted from the Standard Report Submission (SRS) window. This process can be schedule to run on specific intervals using the SRS scheduling features. Process specific transactions or a range of transactions by entering report parameters as listed on the slide above.
Use different selection criteria to submit individual transactions or groups of transactions.
Submit a maximum of fifteen instances.
Autoinvoice Import Program:
Autoinvoice Import Program validates the selected records and creates transactions. Any record that fails validation is left in the interface table with an error code. Depending on the overall setup, related records may be rejected as well. An output files called Autoinvoice Execution Report and Validation Report, can be viewed by clicking the View Report button in the Requests Window.
Autoinvoice Purge Program:
Autoinvoice Purge Program deletes records from the interface tables. Only records that have been successfully processed by Autoinvoice are purged. If the Purge Interface Table option in the System Options window is set to No, Autoinvoice does not delete processed records from the interface tables after each run and the Autoinvoice Purge Program will have to be submitted independently.
Reports:
The Autoinvoice Master Program log file, gives details of the concurrent request initiated by the Autoinvoice Master Program and any errors encountered therein.
The Autoinvoice Import Program log file, gives details of the concurrent request carried out for the import of transaction and any errors encountered therein.
The Autoinvoice Execution Report lists the total number of transaction, sales credit and distribution, lines that was successfully imported as well as those that failed.
The Autoinvoice Validation Report is created if any records have failed different phases of validation. It displays all error messages associated with each transaction, sales credit or distribution line that failed validation.
Correcting Auto invoice Errors Overview:
The AutoInvoice Exception Handling Windows can be used to correct errors that are in the AutoInvoice Interface Table. Records that pass validation are transferred into Receivables transaction tables. Records that fail validation are called exceptions; these records remain in the AutoInvoice interface tables.
Once you have corrected the errors, you must resubmit AutoInvoice. Valid lines cannot be changed in the AutoInvoice Exception Handling windows. Only those lines with errors can be changed
Interface Exceptions Window:
Displays the interface ID, exception type, error message, and the invalid value associated with each error. Cannot edit data in this window, but you can view the error message and easily correct the error by clicking the Details button.
Note: The error message and column name with erroneous data is displayed in the Message column, and the value that needs to be corrected is displayed in the Invalid Value column.
Interface Lines Window:
Displays all records in the interface tables that failed validation, provides an error message, and can be used to correct invalid records.
Click the Details button, the window opens where the errors can be corrected. Buttons are provided to correct specific types of errors.
Correct errors in these windows.
Line Errors Window:
Displays all errors associated with a specific line and can only be opened from the Interface Lines window. View all errors associated with a single line by clicking the Errors button in the Interface Lines window. Cannot edit data in this window. Displays the interface ID, the error type, error message, and the invalid value.
Note: You might use this window when you access the Interface Lines window directly, which does not display the error messages. The type indicates which button to click in the Interface Lines window.
How is the Tax code derived in AutoInvoice:
AutoInvoice first looks at the transaction type setup (Menu: Setup>Transactions>Transaction Types) and the TAX_EXEMPT_FLAG column in RA_INTERFACE_LINES_ALL. If the 'Calculate Tax' box is checked on the transaction type OR the TAX_EXEMPT_FLAG column is 'R'(for Required), it tries to derive the Tax code.
To get the tax code, AutoInvoice looks at the following places in the following order, stopping at the first place where it finds a tax code.
-Ship to site; -Bill-to-site; -Customer; -item
Useful Queries:
==============================
select
*
from
AR_VAT_TAX_ALL_B (VAT Code id);
===============================
Select
*
from
AR_VAT_TAX_ALL_B
Where
set_of_books_id=1002;
==============================
select
set_of_books_id,
tax_code,
tax_rate,
tax_type,
start_date,
org_id
from
ar_vat_tax_all
where
vat_tax_id = 10007;
=================================
Correcting AR Interface Errors:
The errors in the interface tables can be corrected with the help of the AutoInvoice Validation Report and the AutoInvoice Errors window which displays records that failed AutoInvoice validation. Depending on the error you may need to make changes either in Receivables, or to your feeder program or to the imported records in the interface tables.
Menu: Interfaces>Control>AutoInvoice>Interface Lines
Interface Lines Window - This window lets you see all of the interface lines that have been processed but rejected by AutoInvoice. You can view and edit data in this window.
Menu: Interfaces>Control>AutoInvoice>Interface Exceptions
Interface Errors Window - This window lets you see all of the errors generated by AutoInvoice. This window is read-only, but you can drill down to view errors in more detail, and modify data in these windows
Within the table RA_INTERFACE_LINES_ALL, there is a column called INTERFACE_LINE_ID. When the Autoinvoice process runs, this column is populated with a unique sequence value. If the process errors, the INTERFACE_LINE_ID can be referenced in the RA_INTERFACE_ERRORS_ALL table. This table will also include the error message for the particular order line in the main interface table.
Error: The total amount of your credit memo cannot exceed the balance of the debit item it is crediting.
Option1:
In the interface table , keep the curose after sale order field and click show field to check for the reference_line_id and make that field as null and resubmit the autoinvoice.
Option2:
update ra_interface_lines_all
set reference_line_id = null
where sales_order = :errored_sales_order
and reference_line_id is not null;
Error: You cannot apply a transaction with a negative amount to another transaction with a negative balance and vice versa.
Option1:
In the interface table , keep the curose after sale order field and click show field to check for the reference_line_id and make that field as null and resubmit the autoinvoice.
Option2:
update ra_interface_lines_allset reference_line_id = nullwhere sales_order = :errored_sales_orderand reference_line_id is not null;
Error: You can supply at most one Receivables account for a transaction (an invoice, a debit memo or a credit memo)
This error is caused by violating the mandatory grouping rules as defined by seeded Oracle functionality. This can occur on any number of fields so the key would be to review the following fields to ensure they are match per sales order:
AGREEMENT_ID
COMMENTS
CONS_BILLING_NUMBER
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1-15
HEADER_ATTRIBUTE_CATEGORY
HEADER_GDF_ATTRIBUTE1-15
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PAYMENT_SET_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER
This is a difficult error to resolve, but involves reveiwing the interface line attributes.
Error: You can not apply more than the original line amount
Option1: Control > Autoinvoice > Interface Lines
Query for the Order which has the above error message, go to folder and query for Reference Line ID, Delete this reference id.
Option2: update ra_interface_lines_all
set reference_line_id = null
where sales_order = :errored_sales_order
and reference_line_id is not null
Error: Unable to locate a valid sales tax rate for transaction
This error occurs when the Oracle Tax engine or Tax Extension (for third party tax programs) fails to fetch a sales tax code or rate for the destination location, or fails to derive a tax code from the defaults defined through the system options.
The common causes of this error are listed below.
- Missing or incorrect tax rate for the sales tax location combination.
- Obsolete or unmaintained applications, tax engine or tax vendor code.
- The invoice or order transaction date is not within the tax code effective dates.
- When using third party tax vendors, may have overlapping zip code ranges when sales tax data was uploaded.
- A tax code is assigned (customer, site or item) but system option tax defaults are not enabled.
- Location based tax could not be found because transaction country <> home country.
- Addresses are incorrect or not created properly due to invalid database triggers.
- Sales tax location flexfield may be invalid
Error:Please correct revenue account or Receivables account or freight or tax account assignment
Change the revenue account assignment to a valid revenue account assignment.
Autoaccounting is used to determine the accounts when the distribution table is not populated. When AutoInvoice gives this error, it generally gives the account with the missing segment(s).
Review the setups:
Menu: Setup>Transactions>Autoaccounting Query the account mentioned in the above error and note the setup for that missing segment. If it is based on:
- Transaction Type : - Menu: Setup>Transaction>Transaction Types, and verify that all accounts are populated.
- Salesperson - Menu: Setup>Transactions>Salespersons, and verify that all accounts are populated.
- Standard Lines For an Inventory Item navigate to Menu: Setup>Transactions>Items>Inventory items, and verify that the 'Sales Account' is populated in the 'Invoicing' alternate region. For a Memo Line, navigate to Setup =>Transactions =>Memo Lines, and make sure that 'Revenue Account' is populated.
-Taxes Menu: Setup>Tax>Codes and verify that account is populated in 'Tax' column under the 'Accounting' alternate region.
Additionally the following can be checked: Setup of Cross Validation Rules. Run Cross validation Listing Report in GL to identify the cross validation rule stopping the import of this line.
Unable to Derive A GL Date for One Transaction.
Populate GL_DATE with a date or populate the date with the created date in the interface table that falls in an open period.
Useful Queries :
=========================
Select
distinct
interface_line_attribute1,
purchase_order
from ra_interface_lines_all
order by Purchase_order;
=========================
select count(1),
message_text
from ra_interface_errors_all
group by message_text;
========================
SELECT
*
FROM
RA_INTERFACE_ERRORS_ALL
WHERE
interface_line_id
IN
(
SELECT
interface_line_id
FROM
RA_INTERFACE_LINES
WHERE
interface_line_attribute1 = <> );
===================================
Select
interface_line_id,
message_text
from
ra_interface_errors_all
where
message_text
like '%Invalid Warehouse ID%';
===========================
No comments:
Post a Comment