AR Tables Info

1. Overview of Transactions entered into Receivables.

This is to show how various transactions entered into Receivables (AR) affect different tables in the database. The transactions that will be covered are:-

o Invoices
o Credit Memos
o On Account Credits
o Receipts


2. Invoices

When you enter an invoice records are inserted into the following tables:-

o RA_CUSTOMER_TRX_ALL (Invoice Header)
o RA_CUSTOMER_TRX_LINES_ALL (Line Details)
o RA_CUST_TRX_LINE_GL_DIST_ALL (Information for posting to GL)
o RA_CUST_TRX_LINES_SALESREPS_ALL (Sales Credit Information)

Once an Invoice has been marked as "Complete" then it will also have at least one record in the table:-

o AR_PAYMENT_SCHEDULES_ALL (Running Totals for Invoice
Amounts)

Normally each invoice will only have 1 payment schedule record but there may be more if the invoice is to be paid in installments - 1 record per installment.

2.1 RA_CUSTOMER_TRX_ALL

Invoice Header information such as the customer details, invoice number, date, where payment should be sent etc.

1 Record per Invoice

Key = CUSTOMER_TRX_ID (generated from sequence RA_CUSTOMER_TRX_S)

Important Fields

TRX_NUMBER - User Entered Invoice Number
CUST_TRX_TYPE_ID - Foreign key to RA_CUST_TRX_TYPES
BILL_TO_CUSTOMER_ID - Foreign Key to RA_CUSTOMERS
SHIP_TO_CUSTOMER_ID - Foreign key to RA_CUSTOMERS
TRX_DATE - Invoice Date
COMPLETE_FLAG - Y or N


2.2 RA_CUSTOMER_TRX_LINES_ALL

Line details, what is being invoiced and how much of it, what tax is
applicable.


At least 1 record per Invoice.

Key = RA_CUSTOMER_TRX_LINE_ID (from sequence RA_CUSTOMER_TRX_LINES_S)

Important Fields

CUSTOMER_TRX_ID - Foreign key to RA_CUSTOMER_TRX
LINE_TYPE - LINE, TAX, FREIGHT
QUANTITY_INVOICED - Line Quantity
UNIT_SELLING_PRICE - Price per unit
EXTENDED_AMOUNT - Quantity * Price
LINK_TO_CUST_TRX_LINE_ID - Null for LINE, for TAX and FREIGHT
lines contains the
RA_CUSTOMER_TRX_LINE_ID of
associated LINE record

2.3 RA_CUST_TRX_LINE_GL_DIST_ALL

Information that needs to be posted across to the General Ledger.

1 record for each line record plus 1 for the Invoice Header

Key = CUST_TRX_LINE_GL_DIST_ID (from sequence)

Important Fields

CUSTOMER_TRX_ID - Foreign key to RA_CUSTOMER_TRX
CUSTOMER_TRX_LINE_ID - Foreign key to RA_CUSTOMER_TRX_LINES
Or Null if this relates to Invoice
Header.
AMOUNT - Value of distribution entered
currency
ACCOUNTED_AMOUNT - Value of distribution - book currency
CODE_COMBINATION_ID - Foreign key to GL_CODE_COMBINATIONS
POSTING_CONTROL_ID - -3 if unposted
GL_DATE - Accounting date.
GL_POSTED_DATE - Date Invoice posted to GL.


2.4 RA_CUST_TRX_LINE_SALESREPS_ALL

Who gets credited for the sale represented by this invoice. Not many people use this functionality and few problems reported on it, so I'll skip it.

2.5 AR_PAYMENT_SCHEDULES_ALL

Keeps running totals of the Invoice amounts for line, tax, and freight. Records both the original amounts and the amounts remaining - ie. money expected from the customer. Problems often show up in this table because whenever transactions are applied to Invoices they update the rows in this table.

Normally 1 record per Invoice.

Key = PAYMENT_SCHEDULE_ID (from sequence AR_PAYMENT_SCHEDULES_S)

Important Fields

CUSTOMER_TRX_ID - Foreign key to RA_CUSTOMER_TRX
STATUS - (OP)en or (CL)losed
ACTUAL_DATE_CLOSED - 31-DEC-4712 if still open, otherwise
date payment schedule closed.
GL_DATE_CLOSED - 31-DEC-4712 if still open, otherwise
The gl date of the closing
transaction.
AMOUNT_DUE_ORIGINAL - Invoice Total Amount
AMOUNT_DUE_REMAINING - Total Amount outstanding for Invoice.
AMOUNT_LINE_ITEMS_ORIGINAL - Sum of Invoice Line amounts.
AMOUNT_LINE_ITEMS_REMAINING - Outstanding Line amounts.
TAX_ORIGINAL - Total Tax for Invoice.
TAX_REMAINING - Outstanding Tax for Invoice
FREIGHT_ORIGINAL - Total Freight For Invoice
FREIGHT_REMAINING - Outstanding Freight
AMOUNT_APPLIED - Total payments applied to this
Invoice.
AMOUNT_CREDITED - Total Credit Memos applied.
AMOUNT_ADJUSTED - Total value of adjustments.

The payment schedule should balance within itself, ie the following
Calculations should be true:-

AMOUNT_DUE_ORIGINAL = AMOUNT_LINE_ITEMS_ORIGINAL
+ TAX_ORIGINAL
+ FREIGHT_ORIGINAL

AMOUNT_DUE_REMAINING = AMOUNT_LINE_ITEMS_REMAINING
+ TAX_REMAINING
+ FREIGHT_REMAINING

AMOUNT_DUE_ORIGINAL = AMOUNT_DUE_REMAINING
+ AMOUNT_APPLIED
- AMOUNT_CREDITED
+ AMOUNT_ADJUSTED


3. Credit Memos

This section refers to Credit Memos that are entered directly against a
particular invoice. They are stored in the same tables as the invoices. When you add such a credit it inserts records into the following tables.


o RA_CUSTOMER_TRX_ALL (CM Header)
o RA_CUSTOMER_TRX_LINES_ALL (CM Line Details)
o RA_CUST_TRX_LINE_GL_DIST_ALL (GL Posting Info)
o AR_PAYMENT_SCHEDULES_ALL (CM Running Totals)
o AR_RECEIVABLE_APPLICATIONS_ALL (How much of CM applied to
Invoice)

It will also update the following records on the corresponding Invoice to reduce the outstanding balance by the amount of the credit.

o AR_PAYMENT_SCHEDULES_ALL (Invoices Running Totals)

If the profile option "Use Invoice accounting for Credit Memos" is Yes the Credit lines and distribution lines will be copied from those of the invoice being credited.

3.1 RA_CUSTOMER_TRX_ALL

Similar information is held for Credits as for Invoices at the header level.


3.2 RA_CUSTOMER_TRX_LINES_ALL

Again similar info is held at the line level except that the following fields will also be populated:-

QUANTITY_CREDITED
PREVIOUS_CUSTOMER_TRX_ID (Invoices
Customer_trx_id)
PREVIOUS_CUSTOMER_TRX_LINE_ID (Invoice line_id)
and QUANTITY_INVOICED will be NULL.



3.3 RA_CUST_TRX_LINE_GL_DIST_ALL

Similar to Invoice distribution lines.





3.4 AR_PAYMENT_SCHEDULES_ALL (for the Credit Memo)

If the credit memo is fully applied to the Invoice then the payment schedule will reflect that. All of the remaining amount fields will be 0, and it will have a closed status.


3.5 AR_RECEIVABLE_APPLICATIONS_ALL

This is the key table used to link the credit memo and Invoice together. A record will be inserted here which will be posted to the Ledger and has the following characteristics:-

Key = RECEIVABLE_APPLICATION_ID (from sequence)

Important Fields

CUSTOMER_TRX_ID - Key to Credit Memos Header Record
CASH_RECEIPT_ID - NULL
STATUS - 'APP' lied
APPLIED_CUSTOMER_TRX_ID - Key to Invoices Header Record
APPLIED_CUSTOMER_TRX_LINE_ID - Key to Invoices Line Record
APPLIED_PAYMENT_SCHEDULE_ID - Key to Invoices Payment
Schedule
AMOUNT_APPLIED - Total amount of Credit applied.
LINE_APPLIED - Line amount of Credit applied.
TAX_APPLIED - Tax amount of credit applied.
FREIGHT_APPLIED - Freight amount of credit applied
GL_POSTED_DATE - When posted to GL


3.6 AR_PAYMENT_SCHEDULES_ALL (for the Invoice)

The various remaining amount fields are reduced by the amount of the credit applied. The AMOUNT_CREDITED contains a running total of credit memos applied against this invoice.

4. On Account Credits

Credits that are entered against a specific customer, but not against a
Specific invoice are referred to as 'On Account' credits. In terms of the data stored in the tables they are very similar to Credit Memos, the main differences being:-

o When first entered the payment schedule will be fully
remaining.

o When first entered no records are inserted into
AR_RECEIVABLE_APPLICATIONS_ALL.

o The line records will have NULL values in
PREVIOUS_CUSTOMER_TRX_ID
PREVIOUS_CUSTOMER_TRX_LINE_ID

o The distribution lines have to be typed in as there is no
invoice to copy them from.

It is because of the last point that on account credits can later be applied against any invoice and later reapplied to different invoices. Credits that were entered against a specific invoice will have lines and distributions that specifically match that invoice, so cannot be reapplied to other invoices.



5. Cash Receipts

When a receipt is entered the following tables are inserted into:-

o AR_CASH_RECEIPTS_ALL
o AR_CASH_RECEIPT_HISTORY_ALL
o AR_PAYMENT_SCHEDULES_ALL
o AR_RECEIVABLE_APPLICATIONS_ALL

When it is applied to an Invoice an additional pair of records are inserted into AR_RECEIVABLE_APPLICATIONS_ALL and the Invoices AR_PAYMENT_SCHEDULE_ALL record is updated.










+------------+
5.1 AR_CASH_RECEIPTS_ALL

This stores the basic receipt information, 1 record per receipt.

Key = CASH_RECEIPT_ID (from sequence AR_CASH_RECEIPTS_S)

Important Fields

AMOUNT - Value of receipt in entered currency
RECEIPT_NUMBER - Payment Number entered by user.
STATUS - (APP) lied, (UNAPP) lied, (REV) ersed
Payment (STOP) payment, (NSF)
insufficient funds.
It will only change to APP once the
Whole amount of the receipt is
applied.
REVERSAL_DATE - NULL unless receipt reversed
PAY_FROM_CUSTOMER - Contains CUSTOMER_ID for RA_CUSTOMERS


5.2 AR_CASH_RECEIPT_HISTORY_ALL

Used mainly for automatic receipts, for manually entered receipts this table will normally contain 1 record per receipt. Information from this table is posted to GL. Reversing a receipt inserts a new row into the table.

Key = CASH_RECEIPT_HISTORY_ID (from sequence)

Important Fields

CASH_RECEIPT_ID - Foreign key to AR_CASH_RECEIPTS
record.
STATUS - CLEARED for manually input
receipts.
GL_DATE - Accounting date
ACCOUNT_CODE_COMBINATION_ID - Key to GL_CODE_COMBINATIONS
POSTING_CONTROL_ID - -3 if unposted
REVERSAL_POSTING_CONTROL_ID - NULL unless payment reversed
CURRENT_RECORD_FLAG - Y if this is latest record
PRV_STAT_CASH_RECEIPT_HIST_ID- Key to previous receipt history
record.

5.3 AR_PAYMENT_SCHEDULES_ALL

Each receipt has a record in this table to keep a running total of how much has been applied. The details are similar to those held on Invoices, but there are some significant differences.

Key = PAYMENT_SCHEDULE_ID (from sequence)

Important Fields

CUSTOMER_TRX_ID - NULL
CASH_RECEIPT_ID - Foreign key to AR_CASH_RECEIPTS
record.
AMOUNT_DUE_ORIGINAL - Total amount of receipt (usually
negative)
AMOUNT_DUE_REMAINING - Unapplied amount of receipt.
AMOUNT_APPLIED - How much of this receipt is applied .
STATUS - (OP)en or (CL)osed. Will only be
closed if AMOUNT_DUE_REMAINING is
zero.

All of the fields holding LINE, TAX and FREIGHT amounts are NULL.

5.4 AR_RECEIVABLE_APPLICATIONS

One record is inserted into this table when the receipt is first created, all further inserts here are done in pairs as the receipt is applied/unapplied to invoices. i.e.

Record 1 UNAPP 700

{ Record 2 UNAPP -200
{ Record 3 APP 200 cross referenced to the Invoice

{ Record 4 UNAPP -500
{ Record 5 APP 500 cross referenced to 2nd Invoice

The sum of the amounts on records that have a particular status should add up to the running totals on the payment schedules, but with the opposite sign.

i.e. In the example above

AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL = -700
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING = 0
AR_PAYMENT_SCHEDULES.AMOUNT_APPLIED = -700

UNAPP = 700 -200 -500 = 0
APP = 200 + 500 = 700

Statuses of these records can be:-

UNAPP - Unapplied
APP - Applied
ACC - On Account
UNID - Unidentified (Customer Not known)

You can see the application records if you query the invoice/credit/receipt in the Transaction History form.
5.5 AR_PAYMENT_SCHEDULE (Invoice)

When the receipt is applied to an Invoice it updates the invoices payment
schedule record. The remaining amount fields of the record are reduced by the
amount of the payment. If the remaining amount is reduced to zero then the
invoices payment schedule will be closed.

For Example, if a 200.00 receipt is applied to a 1175.00 invoice which includes
175.00 of tax the invoices payment schedule is adjusted as follows:-

Before After

AMOUNT_DUE_REMAINING 1175.00 975.00
AMOUNT_LINE_ITEMS_REMAINING 1000.00 800.00
TAX_REMAINING 175.00 175.00
FREIGHT_REMAINING 0.00 0.00

Note that receipts are applied in a fixed sequence:-

1. Line Amounts
2. Tax Amounts
3. Freight Amounts

ie The TAX_REMAINING figure will only start to decrease when the
AMOUNT_LINE_ITEMS_REMAINING is zero.

This causes problems when trying to apply credits against partly paid invoices.
You may need to unapply any payments before you can apply the credit, then