Overview
Auto Lockbox is a service that commercial banks offer corporate customers to enable them to outsource their accounts receivable payment processing. Auto Lockbox eliminates manual data entry by automatically processing receipts that are sent directly to your bank. You can also use Auto Lockbox for historical data conversion. For example, you can use Auto Lockbox to transfer receipts from your previous accounting system into Receivables. Auto Lockbox ensures that the receipts are accurate and valid before transferring them into Receivables.

Benefit of auto lockbox

• Eliminates manual data entry
• Streamlines the application of receipt to outstanding transactions
• Effectively manages cash flow by reducing turnover for converting checks into cash

When can I use lockbox

• apply receipt to outstanding invoice
• import historical receipt data
• auto lockbox report , such as the post quick cash Execution Report, are a good tool to reconcile auto lockbox receipt with the bank transmission

What is inside the lockbox process

This consist of 3 steps process, viz

1. Import: During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader script.

2. Validation: The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL) . At this point, you can optionally query your receipts in the QuickCash window and change how they will be applied before submitting the final step, Post QuickCash.

3. Post QuickCash: This step applies the receipts and updates your customer's balances.

Process Flow
Here is schematic data flow and process flow of lockbox process

Set Up
These are the Normal Set Required for Auto lockbox. This is part of AR Set up:

• Define Banks
  • Define Remittance Bank with Account use Internal where checks from customer are deposited.
• Define Receipt Class
  • Define a Receipt class to determine the required processing steps for receipts to which you assign payment methods with this class.
• Define Payment Methods
  • Define a payment method with all receipt accounts.
• Define Receipt Source
  • Define Receipt Batch Source and attach receipt class, payment method and remittance bank account information to the Receipt Source.
• Define Lockbox
  • Define Lockboxes to use the Receivables Autolockbox program.
• Define Transmission Format
  • Define the Transmission Format which Auto Lockbox uses when importing data into Receivables.
• Define AutoCash Rule Set
  • autocash rule sets determine how a quickcash receipt is applied to open debit items.
  • enter a sequence for automatiocally applying receipts
  • select one or more autocash rules for receipt application from the list of autocash Rule options
  • Define AutoCash Rule Sets to determine the sequence of rules that Post
  • QuickCash uses to update Customer's account balances.
• Control file
  • Create a control file which is used by SQL * Loader to import the data into interface table and it is placed in the directory $AR_TOP/bin
• Data file
  • data are kept into directory $AR_TOP/bin

This is in continuation to API’s availability,Here are the API’s availability in AR.

Invoices

• Manual Entry and Updating: Manual entry or update of invoices requires the user to have access to the Oracle Application's Accounts Receivable module and specifically the Enter Invoices form. You can enter and update invoices for valid customers. AR uses AutoAccounting to create the revenue accounting combination based on the invoice line information.
• AutoInvoice Interface: AutoInvoice interface provides a mechanism for transferring large volumes of invoices, credit memos, debit memos, and on-account credits from third-party and legacy systems into Receivables. Information is loaded into the RA_INTERFACE_LINES_ALL (which stores information about each interface line imported) and RA_INTERFACE_SALESCREDITS_ALL (containing the sales credit information) tables then imported into the Oracle Applications by the AutoInvoice Import process.You can also refer my old post for autoinvoice details.

Receipts

• Manual Entry and Updating: Manual entry or update of receipts requires the user to have access to the Oracle Application's Accounts Receivable module and specifically the Enter Receipts form. You can enter and update receipts for open and future accounting periods.
• AutoLockbox Interface: AutoLockbox interface provides a mechanism for transferring large volumes of payments from bank files or third party systems into Receivables. Information is loaded into the AR_PAYMENTS_INTERFACE_ALL table then imported into the Oracle Applications by the AutoLockbox Import process.
  The AR_PAYMENTS_INTERFACE_ALL table stores imported lockbox information that has not been validated. AutoLockbox creates one row in this table for each record in a transmission. When the validation step of AutoLockbox is run, Oracle Receivables transfers the information from the AR_PAYMENTS_INTERFACE_ALL tables to the AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL tables.

Customers

• Manual Entry and Updating: Manual entry or update of customers requires the user to have access to the Oracle Application's Accounts Receivable module and specifically the Enter Customers form. You can enter and update customers, multiple customer addresses (sites), and multiple contact(s) for each address.
• Customer Interface: The customer interface provides a mechanism for transferring large volumes of customers from third party and legacy systems into Receivables. Information is loaded into the RA_CUSTOMER_INTERFACE, RA_CONTACT_PHONES_INTERFACE, RA_CUSTOMER_PROFILES_INTERFACE, RA_CUSTOMER_BANKS_INTERFACE, and RA_CUST_PAY_METHOD_INTERFACE tables then imported into the Oracle Applications by the Customer Import process.
• TCA API's: The customer information can also be loaded into application by TCA API's.

Sales Tax Rates

• Manual Entry and Updating: Manual entry or update of sales tax data requires the user to have access to the Oracle Application's Accounts Receivable module and specifically the Tax Locations and Rates form. You can enter and update location and rate information through this form.
• Sales Tax Rate Interface: The Sales Tax Rate Interface lets you load sales tax records into your Oracle Receivables application from your sales tax feeder system. Information is loaded into the AR_TAX_INTERFACE table then imported into the Oracle Applications by the Sales Tax Rate Interface process.
  The AR_TAX_INTERFACE table is used to import location, postal code and sales tax rate information into Oracle Receivables. Rows are inserted in this table and then the Sales Tax Interface Program is run to create records in AR_LOCATION_VALUES and AR_LOCATION_RATES. Each row can define a new location and assign to it multiple postal code and effectively date ranges, and each range may have an optional sales tax rate.

Tax Vendor Extension

Oracle provides a Tax Vendor Extension to integrate external tax calculation programs with the Oracle Application's Accounts Receivable module. This extension allows you to provide for complex tax calculation requirements while retaining the full power of Receivables to create and store other tax data. The tax extension is called whenever a tax rate is calculated by the Receivables Tax Engine.

Understanding the profile options and some parameter is essential for autoinvoice execution. Understanding is especially helpful when trouble shooting is required to figure out why and where the processing has failed for certain data scenarios. Here is some informative information for profile Options and parameter required for autoinvoice.

As we know system setup can be done from system options, which is as below.

 1.Max Memory (in bytes)
  As marked 1 in above picture. This is memory setting for allocating autoinvoice program for validation. It is recommended to set Value to 3,000,000 Bytes.

2.Log File Message Level

This is at 2 in above picture. This is number represents the amount of detail we wish to display Auto Invoice log file.

• Message Level 0
  This will gives the following entries in the log file:
  Product Version, Program Name, Auto Invoice Start Time, AutoInvoice Concurrent request Arguments, Error and Warning Messages, AutoInvoice End Time, auto Invoice logical Steps.
•  Message Level 1
  This gives all of the above entries plus:  Time-Stamped function labels
•  Message Level 2
  This gives you all of the above entries plus:  Sizes of Allocated Arrays, Dynamic SQL Statements, Number of Rows Updated, Inserted and Deleted
•  Message Level 3
    This gives you all of the above entries plus: Method IV SQL Array Values.

3. Purge Interface Tables
This is marked 3 in above picture. If this is checked then

• Purges records that are successfully imported into receivables.

It recommended to avoid getting interface tables filled with data that is no more required

4.AR: Autoinvoice Gather Statistics
Specify whether AutoInvoice process analyzes the interface tables or not

• Null is taken as Yes
• This profile should be set to "Yes" if type of data populated in the interface tables changes

5.Number of Instances
This is for Load Balancing.

• Number of CPU -1
• Maximum allowed 500

6.AR: Maximum Lines per Autoinvoice worker
This is setting for let worker know how many records need to pick. This setting is more important, if you are loading very huge set of data. If any one is doing conversion, it is essential to check this value.

The default value is 100000,But the optimal value should be 10000.

Hope this will give a fair idea who is going to utilize autoinvoice First time.

Why set up is important for transaction flex field in autoinvoice? Does my Autoinvoice get failed if I would not do set up or not passing information for Transaction flex field? Yes, You can't. The way the auto invoicing works is designed it worked to Uniquely identify for each transaction.

Then what are those Transaction Flex Fields?

Transaction flexfields are descriptive flexfields that AutoInvoice uses to identify transactions and transaction lines. Oracle Receivables lets you determine how you want to build your transaction flexfield structure and what information you want to capture. There are four types of transaction flexfields:

• Line Transaction Flexfield
• Reference Transaction Flexfield
• Link-To Transaction Flexfield
• Invoice Transaction Flexfield

The key point is the Line Transaction Flexfield must be defined if AutoInvoice is being used. The Line Transaction Flexfield can be used to reference and link to other lines because the Line Transaction Flexfield is unique for each transaction line. AutoInvoice always uses the Line Transaction Flexfield structure for both the Link-to and Reference information when importing invoices. It is only necessary to explicitly define the Link-to, Reference, and Invoice Transaction Flexfield structures if this information is to be displayed on a custom window.

You must define both the line-level and header-level Transaction Flexfield. To define the line-level Transaction Flexfield, query 'Line Transaction Flexfield' in the Title field of the Descriptive Flexfield Segments window and enter the context and segments associated with this Transaction Flexfield. To define the Transaction Flexfield at the header-level, query 'Invoice Transaction Flexfield' and enter the context and segments associated with this Transaction Flexfield. All segments in the line level transaction flexfield that refer to header information must also exist in the header level transaction flexfield. For example if you define a line-level Transaction Flexfield with 4 segments and only the last 2 segments refer to line-level information, define the header Transaction Flexfield using the first two segments.

Where to setup
AR gives you the option of using QuickPicks to display reference information for imported invoices. You can use the System Profile Option AR: Transaction Flexfield QuickPick to select the Transaction Flexfield Segment you want to display in Invoice QuickPicks throughout Oracle Receivables.

Business Case:

Lets say my autoinvoice having two sources,

1.More4Apps - where data is coming from excel based More4apps.
2.PROJECTS INVOICES - for Creating Project invoices.

So to process these, we have set up like this:

If Source is More4Apps then required INTERFACE_LINE_ATTRIBUTE1 till 2 (Fig :2)
If source is PROJECTS INVOICES then required INTERFACE_LINE_ATTRIBUTE1 till 8 (Fig :3)

Fig 2: Setup for Fisrt source as above

Fig 3: Setup for second source as above

What is mean, pretty simple based out of source the respective column should be populated. At the time of validation auto invoice will try to validate first the DFF field, based out of Context Value, The value in context field would be the same which appears while defining. And the process should take the value as reference and create a transaction in the AR.

Here is note on one of most efficient tool used in Oracle application. It is Auto Invoice, most industry accepted tool in Oracle apps.

What is Auto Invoice??

Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data.

Where its fits 

 This fits well with in Oracle ERP or to integrate with any third party application< ?xml:namespace prefix ="" o />

What Module data can be integrated?

• Oracle Order Management
• Oracle Project Accounting
• Oracle services

To make fully functional  what else required

• Loader program
• Validation program

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool

2. Supports Oracle & Non-Oracle Systems

3. Import Large Amount of Data

4. Calculate or Import Tax

5. Group Lines & Invoices

6. Online Error Correction

7 .Lines Validation

8. Derive GL Date

9 .Import Flex fields

10.Import or Derive Accounting Info

What is inside AutoInvoice

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

§         Master (RAXMTR)

§         Import (RAXTRX)

§         Purge (RAXDEL)

1.       Auto Invoice Master program RAXMTR

Selects and marks records in the interface tables to  be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

 

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables

•Marks interface records for processing by marking request_id

•Submits multiple workers for Parallel Processing by creating instances for request.

 

2.       Auto Invoice Import Program Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

 

• Workhorse of Auto invoice
• Validates data
• Inserts records
• Deletes interface data
• Only when system option purge set to ‘Y’

 

3.       Auto Invoice Purge Program Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’

•Ra_interface_lines

•Ra_interface_distributions

•Ra_interface_salescredits

How to start

As discussed above, oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.

Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works

Normally, Auto Invoice can be divided into three major phases

• Pre-grouping: here the validates all of the line level data takes place
• Grouping: groups lines based on the grouping rules and validates header level data
• Transfer :validates information that exists in Receivables tables

 What happen when Auto invoice run

Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

• Line, accounting, and sales credit information for each line populates 3 interface tables
• Lines are ordered and grouped
• Tax is calculated
• GL date is determined
• GL accounts are assigned using Auto Accounting
• Tax, freight, commitments, and credit memos are linked to transaction lines
• All transactions are batched
• Validated lines are used to create the transaction

 How Data is flowing

Select, insert and update and delete take place on certain tables once it is logged out.

Selects

            – RA_INTERFACE_LINES_ALL

            – RA_INTERFACE_DISTRIBUTIONS_ALL

            – RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert

            – RA_INTERFACE_ERRORS_ALL

            – RA_CUSTOMER_TRX_ALL

            – RA_CUSTOMER_TRX_LINES_ALL

            – AR_PAYMENT_SCHEDULES_ALL

            – AR_RECEIVABLE_APPLICATIONS_ALL

Inserts

            – RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling

Records that fail validation are called ‘Exceptions’

• Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL
• Errors can be corrected in the Exception Handling window
• Once corrections are made, Auto invoice must be resubmitted
• Records that pass validation get transferred to Receivables tables 

AutoInvoice Exception Handling Windows

• Interface Exception window displays exception messages associated with all invalid records
• Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
• The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
• Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
• Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
• Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

Next post, I would summarize more information for Transaction Flexfield,Grouping , profile options and parameter setting.

Oracle iPayments module supports online collection of receivables from customers of the organization.

One of the major feature of Oracle Applications, particularly the Distribution and Receivables modules can be extended to include this feature of online payments.

Oracle iPayments supports new payment methods in the form of purchase/credit cards and bank account transfers. It supports different kinds of payment processing namely, Credit Card Payments, Bank Account Transfers, Cyber Cash and CheckFree.

Oracle iPayments ensures effective risk mitigation setups to facilitate the process of web-enabling payment functions by identifying all levels of risk among transactions. For example, the categories of risk can be user-defined in order to assess credit ratings of each customer and to manage risk of processing payments over the internet.

A brief look at the features offered by iPayments :

• Easy installation, administration and extension
• Risk management by identifying fraudulent transactions
• A separate iPayment administrative user (from the system administration)
• Supports several routing options, payment methods, processing moeles and security features
• Rule based payment processing
• Single API integration with any web based or client server applications
• Integration with iStore, Order Management and Receivables
• Manual authorizations of credit card payments
• Application of risk factors on parameters such as amount, frequency of payment, amount limits, payment history and bill-to/ship-to addresses
• Holds created on credit cards based on the risk assessment of the customer
• Encryption / masking of credit card details

The APIs defined for iPayment processing do so for various transaction types like authorization of credit card, bank transfer of funds, cancellation / modification of funds query generation, transaction wise risk information, payment history etc.

Fig 1, below shows ipayment technology Schematic with istore Options.

The risk evaluation and management of the iPayment process can be performed at the customer setup in Order Management, which is used to capture the transaction details of each customer that opts for the functionality, after receiving appropriate authorizations. This will also include definition of clear credit risk management structure within Order management for each of those customers where the iPayment functionality is enabled.

Integration and Interface

Oracle iPayment module tightly integrates with core Oracle modules such as:

1. Order Management
2. Accounts Receivables
3. Accounts Payables
4. Order Capture
5. Telesales
6. Collections
7. Oracle iStore

Oracle iPayment is a single open application programming interface (API) that can be integrated with any web-based or client-server
application. Oracle iPayment supports both single and multi-site installation of electronic commerce (EC) and client-server applications.

Ipayment Technology

• Java Servlet-Based Data Communication
• On-line and Batch Mode of Processing
• Stand-Alone and Integrated with Apps
• Public API

Payment system Relationship Between Oracle

Ipayment, Payment Systems And Beps(Backend Payment Processors) Oracle has well-established relationships with Payment System Providers.

Names include VeriSign, Paymentech, Citibank, First Data (North) and Concord EFSnet.

These providers have compatible servlets, that are either seeded within Oracle Applications or can be easily installed.

The payment system interfaces with BEP’s on a real time basis. Few of the BEP service providers include Paymentech, AMEX and Merchant
eSolutions. There are few business entities (such as Paymentech), that provide both Payment System services as well as BEP services.

Quasi Integrated Vendors Verisign (Paypal) is also feasible options.