Sunday, December 16, 2007

AutoInvoice splitting one order into two invoices, or AutoInvoice generating one invoice for multiple orders:
AutoInvoice uses grouping rules to group invoices, credit memos and debit memos. Grouping rules contain various transaction attributes (columns in RA_INTERFACE_LINES_ALL table) that must be identical for the same transaction.
There are 2 kinds of attributes: mandatory and optional. You can only add or drop optional attributes.
Check the grouping rule that you are using. You may have defined an optional attribute that is different for two lines of the same invoice, and is, therefore, causing a second invoice to be generated. Check both optional and mandatory attributes of the two lines.
If AutoInvoice is generating one invoice for multiple orders, then you may not have defined sales order as an optional attribute.
How AutoInvoice grouping works

Setting for the 'Log file message level':
For day to day business needs and to improve performance, set this to 0. If AutoInvoice errors out, set the message level to 3 to get detail messages in the log to help debug the problem.

Message Level 0 gives the following entries in the log file:
* Product Version
* Program Name
* AutoInvoice Start Time
* AutoInvoice Concurrent Request Arguments
* Error and Warning Messages
* AutoInvoice End Time
* AutoInvoice Logical Steps

Message Level 1 gives you all of the above entries plus:
* Time- Stamped function labels

Message Level 2 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 gives you all of the above entries plus:
* Method IV SQL Array Values

If the errors are regarding Autoaccounting, message level can be set to 10 or above to get more detailed Autoaccounting messages in the log.

How to apply a credit memo (from a legacy system) against an invoice using AutoInvoice: To link the credit to an invoice there are 2 options:
1) Populate REFERENCE_LINE_ID on RA_INTERFACE_LINES_ALL with the CUSTOMER_TRX_LINE_ID of the invoice. or
2) Populate REFERENCE_LINE_ATTRIBUTE1 to 15 with the INTERFACE_LINE_ATTRIBUTE1 to 15 of the invoice.


To create an on-account credit (i.e. not linked to an invoice) do not populate REFERENCE_LINE_ID, REFERENCE_LINE_ATTRIBUTES or REFERENCE_LINE_CONTEXT.

Can Adjustments be imported through AutoInvoice?
No. Only Invoices, Credit Memos, Debit Memos, and On-account Credits can be imported through AutoInvoice. The adjustments can be imported by using Adjustment API's. This API allows users to create, approve, update, and reverse adjustments for invoices using simple calls to PL/SQL functions.

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

Can changes be made to existing Invoices via AutoInvoice ?
No. You can only create credit memos and apply them to existing invoices if they are still open (or if Allow Overapplication is checked for that Transaction Type). You cannot update existing invoices.
Once a transaction in AR has any activity against it you cannot update it manually. Activity is defined as cash applied, credit memo applied, adjustment, posting to GL and, printing.
How To Update An Already Created Invoice Through AutoInvoice Program

What should be verified when I get the 'Please Correct Revenue (or Receivable or Freight or Tax) 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).

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.

Once the transactions have been imported successfully into AR using AutoInvoice, how can they be purged from the interface tables?
If the system option 'Purge Interface Tables' is set to Yes, data that has been validated and successfully imported in AR will be purged automatically. If the purge option is set to No, you can submit the AutoInvoice Purge Program from the Run AutoInvoice form to purge the interface tables. This program will only purge rows that have been processed successfully.

Navigation to check the 'Purge Interface Tables' option:
- Menu: Setup>System>System Options
- Alternate region 'Trans and customers'
- 'Purge Interface Tables' checkbox

Navigation to submit the AutoInvoice purge program:
- Menu: Interface>AutoInvoice
- Request name: AutoInvoice Purge Program

How often can you run AutoInvoice?
AutoInvoice can be run as many times as you like. It can run at anytime of the day, month, year. You need to set the rules for the running of this process based on your company's business needs. You can also schedule to run AutoInvoice automatically at regular intervals.

In multi-org environment, can I run AutoInvoice simultaneously?
Yes, you may, there are no incompatibilities setup for AutoInvoice against itself.
You can setup an incompatibility as follows:
Under System Administrator responsibility,
Menu: Define>Concurrent>Program
Click on Incompatibilities
You will see the RAXMTR to be incompatible with itself and thus not allow AutoInvoice to be run simultaneously.
Also when you have only one org, AutoInvoice can be run in parallel for different batch sources.

How do I check the Set Up for AutoInvoice?
1) Note:226429.1 /Note:204790.1
The Receivables Set Up Details Report (ARSSETUP.rdf) identifies all critical Set Up steps done in Receivable before performing an important process like running AutoInvoice. All the vital information are be available in one place by running this report instead of searching for it in various places.
2) Note:202260.1 This pl/sql test will provide detailed data on the Oracle Receivables set up. The output will be spooled to an HTML file.
References AutoInvoice Setup White Paper
Oracle Support Diagnostic Release Announcement

How can I prevent partial transactions from being created, when program error occurs.
To prevent partial transaction from being created when program error occurs, apply the following patches: Patch 2127349 for Release 11 and Release 11i.

How can the errors in the interface tables lines be corrected?
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.

How does the GL date get derived?
For invoices without Rules: AutoInvoice first uses the GL date in the interface table (RA_INTERFACE_LINES_ALL), if one exists.

If one does not exist then it is derived as follows:
If the Derive Date box is checked for your batch source (Menu: Setup>Transactions>Sources, query your batch source, alternate region Accounting), AutoInvoice first uses the ship date in the interface table. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date entered in the Submit Request window when AutoInvoice was run.

If the Derive Date box is not checked for your batch source, AutoInvoice uses the date entered in the Submit Request window when AutoInvoice was run.

For Invoices with Rules: AutoInvoice first uses the GL date in the interface table (RA_INTERFACE_LINES_ALL), if one exists.

If the Invoicing Rule is 'Bill In Advance', AutoInvoice uses the Rule Start Date for the GL date.

If the Invoicing Rule is 'Bill in Arrears' and the invoice line has an accounting rule of type 'Accounting, Fixed Duration' and a period of 'Specific Date', AutoInvoice computes an end date using the earliest accounting rule date.

For all other Accounting rules, AutoInvoice computes an ending date for each invoice line, and then takes the earliest date of these lines and uses it as the GL date of the invoice.

If your invoice does not use a fixed rule accounting duration and the rule start date is not provided in the interface table, GL date is derived as follows:

If the Derive Date box is checked in batch source options, AutoInvoice first uses the ship date. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date entered in the Submit Request window.

If the Derive Date box is not checked, AutoInvoice uses the date entered in the Submit Request window.
References: AUTOINVOICE - Date Derivation
AutoInvoice Date Derivation White Paper

AutoInvoice is taking too long to run. How can I improve its performance? Although there are many reasons which can contribute towards poor AutoInvoice performance, the most common ones are as follows:

a) Indexes have not been defined for the Line Transaction Flexfield on RA_INTERACE_LINES_ALL. See Notes under Reference for details.
b) Old application or AutoInvoice code: Make sure you are on the latest patchset and have applied the latest performance patch for AutoInvoice, if any, for your application release.
c) Custom indexes or triggers: If any custom indexes or triggers have been defined, drop them to see if performance improves.
d) Missing or disabled indexes or triggers on AR tables: Check the Technical Reference Manual to verify you have all the indexes and triggers on the AR tables.
e) Run Gather Schema Statistics from the SYSADMIN responsibility


The Import program (RAXTRX) does not get spawned when I run AutoInvoice. What could be the reason?
In this case the Master Program (RAXMTR) may complete without error but will not spawn the Import Program (RAXTRX). The log file for RAXMTR will show the 'No data Found' message.

AutoInvoice executes a statement similar to the following to select records for importing:



In the above example: 'POS Batches' is the Batch Source name. In your case, there may be no records returned by the above statement, and hence the 'NO Data Found' message. You can verify if there are records in the interface table (RA_INTERFACE_LINES_ALL) to be imported by running the following statement:

where BATCH_SOURCE_NAME='&batch_source_name';

The INTERFACE_STATUS should not be P and the REQUEST_ID column should be NULL.

Moreover, also verify if the necessary attributes (ATTRIBUTE1-ATTRIBUTE15) are NULL.

If you have interfaced orders from Order Entry, the records may not have been populated into the interface table. Check the log of the Receivables Interface process for any error messages even if the process completed successfully.

How to get closed invoices into AR using AI process?
The AutoInvoice program will only let you process open invoices. It cannot be used to bring in invoices for historical purposes.

How to allow duplicate value in INTERFACE_LINE_ATTRIBUTE1 in AutoInvoice?
Line Transaction Flexfield has an associated unique Context field. The combination of the segments plus Context has to be unique.
AutoInvoice Setup White Paper
Line Transaction Flexfields

How do you default the batch source on the AutoInvoice submission screen?
- Go to Concurrent -> Program -> Define
- Pull up RAXMTR
- Click on Parameters
- Go to Batch Source ID
- Change the default type to Constant
- Change the Default value to the NAME (not id) of the value you want "Order Entry" (it is case sensitive).
- Save.

How do you get the AutoInvoice Exceptions forms to appear on the menu?
You will need to add the option and the function to the menu if you do not have them. How do we get the new Autoinvoice Exceptions forms to appear on the menu?

What is the difference between the attribute_category and the header_attribute_category columns are on the ra_interface_lines_all table.
They are for 2 different descriptive flexfields.
What is the difference between the attribute_category and the header_attribute_category

How to import gapless transaction numbers with AutoInvoice?
Oracle does not gaurantee 100% gapless transaction numbering even with the cache on the sequence turned off. You can make the invoice numbering follow your document sequencing and this will bring you closer to 100% gapless numbering.
Gapless Invoice Numbering is Required
How To Make Document Transaction Numbering Gapless?

How to run an example AutoInvoice Import?
Check available information in Setup & Usage for Autoinvoice
RAXTRX: How To Import An Invoice And Tax Into Oracle
Quick Steps to run an example AutoInvoice Import

AutoInvoice Setup and Usage Instructions
AutoInvoice Current Issues
AutoInvoice Troubleshooting Guide

Tuesday, November 27, 2007


Normally, there are two basic accounting methods available in the business world:
  • Cash
  • Accrual

And most of the ERP accounting products weather its SUN system, Oracle financial or SAP have functionality to capture on the basis of set up.

Cash Basis Accounting This is what “Based on Realization“
- We Most of us use the cash method to keep track of our personal financial activities.
- The cash method recognizes revenue when payment is received, and recognizes expenses when cash is paid out.
- For example, our local grocery store’s record is based on the cash method. Expenses are recorded when cash is paid out and revenue is recorded when cash or check deposits are received.
- If we summarize, under the cash basis accounting, revenues and expenses are recognized as follows:

  • Revenue recognition: Revenue is recognized when cash is received.
  • Expense recognition: Expense is recognized when cash is paid.

- Take a note the word “cash” is not meant literally - it also covers payments by check, credit card, barter, etc.
- Moreover it is not standard method in compliance with accountings matching principle.

Accrual Basis Accounting: This is what “Based on Recognition“
- The accrual method of accounting requires that revenue be recognized and assigned to the accounting period in which it is earned. Similarly, expenses must be recognized and assigned to the accounting period in which they are incurred.
- Then the underline question is what is accounting Period, Let explain like this normally a company tracks the summary of the accounting activity in time intervals, which we normally called as Accounting periods. These periods are usually a month long. It is also common for a company to create an annual statement of records. This annual period is also called a Fiscal or an Accounting Year.
- In the accrual method relies on the principle of matching revenues and expenses. This principle says that the expenses for a period, which are the costs of doing business to earn income, should be compared to the revenues for the period, which are the income earned as the result of those expenses. In other words, the expenses for the period should accurately match up with the costs of producing revenue for the period.

Example: Company is doing a business and they have to pay sales commissions expense, so sales commissions expense should be reported in the period when the sales were made (and not reported in the period when the commissions were paid). Similarly, Salary/Wage to employees are reported as an expense in the week/month when the employees worked and not in the week/month when the employees are paid. If a company agrees to give its employees 2-month equivalent salary of its 2006 revenues as a bonus on January 25, 2007, the company should report the bonus as an expense in 2006 and the amount unpaid at December 31, 2006 as a liability. This is most simple kind of matching principal normally has.

In general, there are two types of adjustments that need to be made at the end of the accounting period.

  1. The first type of adjustment arises when more expense has been recorded than was actually incurred or earned during the accounting period.
  2. Similarly, there may be revenue that was received but not actually earned during the accounting period. Also known as Un-earned Revenue.

The accrual method generates tax obligations before the cash has been collected (because revenue leads to tax and revenue is recognized against receivable and not against receipt of money).

If we summarize, under the accrual basis accounting, revenues and expenses are recognized as follows:

  • Revenue recognition: Revenue is recognized when both of the following conditions are met:
    1. Revenue is earned
    - i.e. when products are delivered or services are provided.
    2. Revenue is realized or realizable.
    - i.e. either cash is received or it is reasonable to expect that cash will be received in the future.
  • Expense recognition: Expense is recognized in the period in which related revenue is recognized (Matching Principle).

Timing differences in recognizing revenues and expenses:

Various accounting books did mention four potential timing differences in recognizing revenues and expenses between these of two. Just to recap of those:
a. Accrued Revenue: Revenue is recognized before cash is received.

b. Accrued Expense: Expense is recognized before cash is paid.

c. Deferred Revenue: Revenue is recognized after cash is received.

d. Deferred Expense: Expense is recognized after cash is paid.

Compare with a Case to explain these two methods
Your company purchase a new Laptop on credit in May 2007 and pay $1,500 for it in July 2007, two months later.
Under the both case see how this makes a difference:

  • Using the cash method accounting, you would record a $1,500 payment for the month of July, the month when the money is actually paid.
  • Under the accrual method, you would record the $1,500 payment in May, when you take the Laptop and become obligated to pay for it.

Pros and cons of these Two accounting method

Maintence: The cash method is easier to maintain because you don’t record income until you receive the cash, and you don’t record an expense until the cash is paid, where as in the accrual method, you will typically record more transactions.
Cash-basis accounting defers all credit transactions to a later date. It is more conservative for the seller in that it does not record revenue until cash receipt. In a growing company, this results in a lower income compared to accrual-basis accounting.

GAAP: Most of ERP follows these. Lets explain this way:The word”generally accepted accounting principles” (or “GAAP”) consists of three important sets of rules:

(1) The basic accounting principles and guidelines,

(2) The detailed rules and standards issued by FASB(Financial Accounting Standards Board and its predecessor the Accounting Principles Board (APB)

(3) The generally accepted industry practices.

Normally Standard GAAP will have various guided Principal, such as

  • Economic Entity Assumption
  • Time Period Assumption
  • Cost Principle
  • Matching Principle
  • Revenue Recognition Principle

ERP/Oracle Financials Oracle Financials have been developed to meet GAAP requirements as well as the special needs of different countries. For example, in Oracle Payables you can choose whether to record journal entries for invoices and payments on an accrual basis, a cash basis, or a combined basis where accrual journal entries are posted to one set of books and cash basis journal entries are sent to a second set of books.

Monday, November 26, 2007

Customer Interface

“A customer is someone who makes use of or receives the products or services of an individual or organization.” Its means it is one who become a entity in your business world, irrespective of your line of business. If you are manufacturer the customer is one to whom you provide the product and get the money or services for which your get paid.
Time to time the customer definition has been changed and now in today economy it can be redefined as:
A customer..may include users, consumers, demanders, commanders, and requestors. Any person or entity who interacts directly or indirectly with any business system, thus it can be a client within internal departments, a supplier from the procurement process, an employee, or someone who is ringing up the cash register.

What information is important to keep in Business?
Typical information required for any customer is address, contact, bank , profile,class. Oracle standard form does have more than 8 tabs which hold most of the information.

Fig: Standard Setup process for customer

Fig : Entity Model for Customer Setup

What is Customer Interface ?
Customer Interface is a oracle seeded tool that is used to import and validate current or historical customer information from other systems into Receivables. Once customer information is imported into the system, you can use Customer Interface to import additional data for that customer (such as additional contacts or addresses) and to update existing information. This is yet another options to enter Customer information other than manually update and enter new information using the Customer windows.

Customer Interface and Customer in 11i
11i version uses 23+ tables

TCA model - how its drived
RA_CUSTOMERS, previously the main customer table is now a view.This become view which consists of data in HZ_CUST_ACCOUNTS and HZ_PARTIES tables.
New Customer Tables - also known as HZ Tables
The new HZ Customer Tables have tables for Customer Accounts and Parties

Considering Customer as Parties
HZ_PARTIES stores information about organizations, groups, and people.
If a party becomes a customer then the information for the customer is stored in the HZ_CUST_ACCOUNTS table.
A Party record in the Parties table can have multiple customer account records in the Customer Accounts table.
One row is created in HZ_PARTIES for every customer record that is imported through the Customer Interface.
CRM uses the customer module making it a requirement for all customers to have a party id and customer id.

11i Customer Interface Vs Oracle Base table
Here is summarize information for interface Vs base table. Once Customer Import get completed successfully , the data moved to these tables:
Where to start for Customer Interface
1.The first steps would be your is preparing Receivables setup activity
Be sure to set up new data in Receivables that the Customer Interface should import. For example:
  • AutoCash Rule Sets
  • AutoInvoice Grouping Rules
  • Collectors
  • Customer Addresses
  • Customer Bank Information
  • Customer Exemptions
  • Customer Profile Classes
  • Demand Classes
  • Dunning Letter Sets
  • Freight Carriers
  • Payment Methods
  • Payment Terms
  • Statement Cycles
  • Tax Codes

Be sure to also set up Lookups in Receivables that the Customer Interface should import. These are the lookups:

  • Countries
  • Site Use Codes
  • Credit Ratings
  • Risk Codes
  • Account Statuses
  • Communication Types
  • Customer Classes

2. Next is to map the Interface Tables











3. RUN the Import Program
- Run Import after AR Customer Interface tables have been populated
- Program will validate the data in the interface table before creating records in Receivables
- Run the Customer Interface process through the Submit Request window
- But, a separate navigational path is also providedInterfaces -> Customer
- Check output file for errors

Common Errors...

a3: Bill_To_Orig_Address_Ref is not a valid bill-to address:
- Verify the Bill-To address reference is valid. Keep in mind that when using the bill-to reference with a ship-to address record… the bill-to must already exist in Receivables.
- Note: Ran into this issue. Try running bill-to records through the interface first and ship-to records as second batch - this will resolve the error. Do not Interface with both in the same batch.

a1:Customer record for insert must have validated profile record defined
- New customers and each Bill-To record must have a customer level profile in the RA_CUSTOMER_PROFILES_INT_ALL table.

a8: Conflicting profile classes specified for this customer/site
Profile classes for customer and bill-to must be the same. Sites cannot have a profile class different from the customer.

J1: Site_USE_CODE is not updateable.

J3: LOCATION is not updateable.

J2: PRIMARY_SITE_USE_FLAG is not updateable.
- Keep in mind that site_use_code, primary_use_flag, and location may not be updateable through the Customer Interface

A3: Customer reference for insert is already defined.

A5: Customer Number already assigned to a different customer.
- Customer reference and Customer number are values that must be unique. Verify the customer reference or customer number does not already exist for another customer.

Tips and Technique

1. Check out some of the Profile Options hitting Customer Import

  • HZ: Generate Party Number
    This the profile option can be updated at Site, Application, Responsibility and User levels.This profile option determines whether party number should be auto-generated. If value is ‘No’,means party number must be passed in by the user else if ‘Yes’ or if the value is not set, party number will be auto-generated.
  • HZ: Generate Party Site Number
    same as above for party site number set at all leval.
  • HZ: Internal Party
    This profile option is used as a part of CRM setup. This must be set if CRM is installed. It is used for data migration purpose.
  • HZ: Generate Contact Number
    This profile option determines whether contact number should be auto-generated.If the value is ‘No’, contact number must be passed in by the user. If the value is ‘Yes’ or if the value is not set, contact number will be auto-generated.

2. Automatic sequence number for customer number
Many times AR department is not like oracle seeded number which start by default 1000.Options are there:
You cannot change the sequence via the forms and therefore any change that you make to the sequence would have to bethrough SQLPlus and that would not be supported.
To set the sequence number

  • Step 1. In the Application Developer responsibility,

Menu: Application=>Database=>Sequence

  • Step 2. Query on sequence RA_CUSTOMERS_NUM_S

This will bring up the sequence for the customer numbers and you can enter the number that you want it to start from.

To set automatic numbering for customer after setting the sequence:

  • Step 1. Menu:=>System=>System Options
  • Step 2. Region - Invoicing and Customers
  • Step 3. Check the box for Automatic Customer Numbering.

3. When doing Migration from other system, adviced to use TRIM Function
- When loading interface tables remove all trailing spaces from import data.Example: LTRIM(RTRIM(customer_name))

4.If importing large number of customers, run in smaller batches instead of all at once.
Oracle benchmark is about 10,000 records per batch is ideal, it is suggested to keep the batch size small.

5.When rolling out in Multi-Org , then you must populate the org_IDs in the interface tables and run the customer interface for each organization set-up responsiblity.