How Transaction Activities are Stored
This topic describes how Receivables stores transaction activities.
These transaction activities are:
-
Invoices and Debit Memos
-
Credit Memos
-
On-Account Credit Memos
-
Chargebacks
-
Adjustments
Invoices and Debit Memos
When you create an invoice or debit memo either manually or using AutoInvoice, Receivables creates records in the following tables:
-
RA_CUSTOMER_TRX
-
RA_CUSTOMER_TRX_LINES
-
RA_CUST_TRX_LINE_SALESREPS
-
RA_CUST_TRX_LINE_GL_DIST
-
AR_PAYMENT_SCHEDULES
Consider this sample invoice:
-
Invoice Number: I-101
-
Bill-to Customer: ABC Inc
-
Invoice Date: 22-May-11
-
Invoice Lines: 5
Item
Amount
Tax
Total Amount
10 chairs @ $200
$2,000
$160
$2,160
10 tables @ $300
$3,000
$240
$3,240
Freight
$1000
NA
$1000
TOTAL
NA
NA
$6400
The RA_CUSTOMER_TRX table stores information from Invoice I-101 as follows:
CUSTOMER_TRX_ID |
TRX_NUMBER |
BILL_TO_CUSTOMER_ID |
TRX_DATE |
---|---|---|---|
101467 |
I-101 |
ABC Inc |
22-May-11 |
The RA_CUSTOMER_TRX_LINES table stores information from Invoice I-101 as follows:
CUSTOMER_TRX_LINE_ID |
CUSTOMER_TRX_ID |
LINK_TO_CUST_TRX_LINE _ID |
LINE_TYPE |
EXTENDED_AMOUNT |
---|---|---|---|---|
100 |
101467 |
NA |
LINE |
2000 |
101 |
101467 |
100 |
TAX |
160 |
102 |
101467 |
NA |
LINE |
3000 |
103 |
101467 |
102 |
TAX |
240 |
104 |
101467 |
NA |
FREIGHT |
1000 |
Because the sample invoice had freight at the header level, it isn't linked to any line and therefore the link_to_cust_trx_line_id column is null.
The RA_CUST_TRX_LINE_SALESREPS table reflects the sales credits allotted to salespersons 1492, 1525, and 1624. The revenue and non-revenue amounts associated with the first line item of the invoice are split between salesperson 1492 and salesperson 1525. Salesperson 1624 gets the complete sales credit for the second line item of the invoice, while all three share the credit for the header level freight.
The RA_CUST_TRX_LINE_SALESREPS table stores information from Invoice I-101 as follows:
CUST_TRX_LINE_SALESREP_ID |
SALES_REP_ID |
CUSTOMER_TRX_LINE_ID |
REVENUE_AMOUNT_SPLIT |
NON_REVENUE_AMOUNT_SPLIT |
PREV_CUST_TRX_LINE _SALESREP_ID |
---|---|---|---|---|---|
140195 |
1492 |
100 |
1000 |
0 |
NULL |
140196 |
1525 |
100 |
1000 |
0 |
NULL |
140197 |
1492 |
101 |
0 |
80 |
NULL |
140198 |
1525 |
101 |
0 |
80 |
NULL |
140199 |
1624 |
102 |
3000 |
0 |
NULL |
140200 |
1624 |
103 |
0 |
240 |
NULL |
140201 |
1492 |
104 |
0 |
200 |
NULL |
140202 |
1525 |
104 |
0 |
200 |
NULL |
140203 |
1624 |
104 |
0 |
600 |
NULL |
The RA_CUST_TRX_LINE_GL_DIST table stores information from Invoice I-101 as follows:
CUST_TRX_LINE_GL_DIST_ID |
CODE_COMBINATION_ID |
CUSTOMER_TRX_LINE_ID |
ACCOUNT_CLASS |
AMOUNT |
---|---|---|---|---|
10866 |
01-1200-1000-3000 |
NA |
REC |
64000 |
10867 |
01-8100-1000-3000 |
100 |
REV |
2000 |
10868 |
01-4100-1000-3000 |
101 |
TAX |
160 |
10869 |
01-8200-1000-3000 |
102 |
REV |
3000 |
10870 |
01-4200-1000-3000 |
103 |
TAX |
240 |
10871 |
01-4400-1000-3000 |
104 |
FREIGHT |
1000 |
If you enter an invoice with rules, the account distributions aren't built when the invoice is first created. Instead the RA_CUST_TRX_LINE_GL_DIST table stores an account set, which represents how the actual distribution rows should be created and what percentage of the actual distribution should be allocated to each account. Account sets can be identified by a Y in the account_set_flag column. The actual distribution records are built when you run revenue recognition.
The AR_PAYMENT_SCHEDULES table reflects the current status of Invoice I-101. The invoice has a status of OP (open) and an amount_applied of NULL, because no payment has been applied against it. Once payment is received in full, the status will change to CL (closed), the amount_applied will change to 6400, and the amount_due_remaining will be zero.
The AR_PAYMENT_SCHEDULES table stores information from Invoice I-101 as follows:
PAYMENT_SCHEDULE_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE _REMAINING |
CUSTOMER_TRX_ID |
CASH_RECEIPT_ID |
TRX_NUMBER |
STATUS |
AMOUNT _APPLIED |
CLASS |
---|---|---|---|---|---|---|---|---|
30191 |
6400 |
6400 |
101467 |
NULL |
I-101 |
OP |
NULL |
INV |
Receivables handles debit memos the same as invoices in all tables, except that in the AR_PAYMENT_SCHEDULES table it sets the class of the payment schedule to DM instead of INV.
Credit Memos
When you enter a credit memo against an invoice, Receivables creates records in the following tables:
-
RA_CUSTOMER_TRX
-
RA_CUSTOMER_TRX_LINES
-
RA_CUST_TRX_LINE_SALESREPS
-
RA_CUST_TRX_LINE_GL_DIST
-
AR_PAYMENT_SCHEDULES
-
AR_RECEIVABLE_APPLICATIONS
Consider a sample credit memo against line number 1 of Invoice I-101:
-
Credit Memo Number: CM-101
-
Bill-to Customer: ABC Inc
-
Credit Memo Date: 01-Jun-11
-
Credit Memo Amount: -1000
The RA_CUSTOMER_TRX table stores Credit Memo CM-101 as follows:
CUSTOMER_TRX_ID |
TRX_NUMBER |
BILL_TO_CUSTOMER_ID |
TRX_DATE |
PREVIOUS_CUSTOMER _TRX_ID |
---|---|---|---|---|
123456 |
CM-101 |
ABC Inc |
01-Jun-11 |
101467 |
The previous_customer_trx_id column references the original transaction you have credited.
The RA_CUSTOMER_TRX_LINES table stores Credit Memo CM-101 as follows:
CUSTOMER_TRX_LINE_ID |
CUSTOMER_TRX_ID |
LINK_TO_CUST_TRX_LINE_ID |
LINE_TYPE |
EXTENDED_AMOUNT |
PREVIOUS_CUSTOMER_TRX_ID |
PREVIOUS_CUSTOMER_TRX_LINE_ID |
---|---|---|---|---|---|---|
150 |
123456 |
NA |
LINE |
-926 |
101467 |
110100 |
151 |
123456 |
150 |
TAX |
-74 |
101467 |
NA |
Based on the sample credit memo, Receivables inserts two records into the RA_CUSTOMER_TRX_LINES table. The total value of the credit memo is prorated between the invoice and tax lines associated with line 1 of the original invoice. The previous_customer_trx_line_id column references the customer_trx_line_id column of the original invoice you have credited.
The RA_CUST_TRX_LINE_SALESREPS table stores Credit Memo CM-101 as follows:
CUST_TRX_LINE_SALESREP_ID |
SALES_REP_ID |
CUSTOMER_TRX_LINE_ID |
REVENUE_AMOUNT_SPLIT |
NON_REVENUE_AMOUNT_SPLIT |
PREV_CUST_TRX_LINE _SALESREP_ID |
---|---|---|---|---|---|
150205 |
1492 |
100 |
-463 |
0 |
140195 |
150206 |
1525 |
100 |
-463 |
0 |
140196 |
150207 |
1492 |
101 |
0 |
-37 |
140197 |
150208 |
1525 |
101 |
0 |
-37 |
140198 |
Assuming the credit memo is only applied to the first line of the invoice, salesperson 1492 and salesperson 1525 will split the loss of the sales credit. The prev_cust_trx_line_salesrep_id column references the original sales credit from the original invoice.
The RA_CUST_TRX_LINE_GL_DIST table stores Credit Memo CM-101 as follows:
CUST_TRX_LINE_GL_DIST_ID |
CODE_COMBINATION_ID |
CUSTOMER_TRX_LINE_ID |
ACCOUNT_CLASS |
AMOUNT |
---|---|---|---|---|
150160 |
01-1200-1000-3000 |
NA |
REC |
-1000 |
150161 |
01-8100-1000-3000 |
150 |
REV |
-926 |
150162 |
01-4100-1000-3000 |
151 |
TAX |
-74 |
Because this is a credit memo, the revenue and tax accounts will be debited and the receivable account will be credited.
The AR_PAYMENT_SCHEDULES table stores Credit Memo CM-101 as follows:
PAYMENT_SCHEDULE_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE_REMAINING |
CUSTOMER_TRX_ID |
TRX_NUMBER |
STATUS |
AMOUNT_APPLIED |
CLASS |
AMOUNT_CREDITED |
---|---|---|---|---|---|---|---|---|
400100 |
-1000 |
0 |
123456 |
CM-101 |
CL |
-1000 |
CM |
NULL |
The class column of the credit memo payment schedule is CM. The example credit memo has a status of CL (closed) and the amount_applied column equals the amount of the credit memo, because the credit memo has been applied to an invoice. The amount_due_original column equals the amount of the credit memo, -1000. The amount_due_remaining is zero because the credit memo has been applied to an invoice.
After applying the credit memo, the AR_PAYMENT_SCHEDULES table stores Invoice I-101 as follows:
PAYMENT_SCHEDULE_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE_REMAINING |
CUSTOMER_TRX_ID |
TRX_NUMBER |
STATUS |
AMOUNT_APPLIED |
CLASS |
AMOUNT_CREDITED |
---|---|---|---|---|---|---|---|---|
30191 |
6400 |
5400 |
101467 |
I-101 |
OP |
NULL |
INV |
-1000 |
Receivables updates the payment schedule of the invoice to reflect the application of the credit memo. The amount_due_remaining column is reduced by -1000 and the amount_credited column is -1000, the amount of the credit memo.
The AR_RECEIVABLE_APPLICATIONS table stores Credit Memo CM-101 as follows:
RECEIVABLE_APPLICATION_ID |
AMOUNT_APPLIED |
STATUS |
PAYMENT_SCHEDULE_ID |
CUSTOMER_TRX_ID |
CASH_RECEIPT_ID |
APPLIED_PAYMENT_SCHEDULE_ID |
APPLIED_CUSTOMER_TRX_ID |
---|---|---|---|---|---|---|---|
400 |
1000 |
APP |
400100 |
123456 |
NULL |
30191 |
101467 |
Receivables uses the AR_RECEIVABLE_APPLICATIONS table to store the mapping of the credit memo to the invoice being credited. The payment_schedule_id and customer_trx_id columns contain the credit memo data, while the applied_payment_schedule_id and applied_customer_trx_id reference the original invoice. If the credit memo applies to an invoice with multiple payment schedules, a record is inserted into AR_RECEIVABLE_APPLICATIONS for each payment schedule of the invoice. The code_combination_id column, which isn't shown, stores the receivable account of the invoice. However, when the transaction is posted to the general ledger, it posts as two distributions. One entry is posted to the receivable account of the credit memo, as stored in the RA_CUST_TRX_LINE_GL_DIST table, and the other entry is posted to the receivable account of the invoice, as stored in the RA_CUST_TRX_LINE_GL_DIST table.
For a standard credit memo, the receivable account of the credit memo is debited, while the receivable account of the invoice is credited. Normally, the receivable accounts will be the same, but this process permits the flexibility of using a unique receivable account to record your credit memos.
On-Account Credit Memos
When you enter an on-account credit without a specific invoice reference, Receivables creates records in the following tables:
-
RA_CUSTOMER_TRX
-
RA_CUSTOMER_TRX_LINES
-
RA_CUST_TRX_LINE_GL_DIST
Consider a sample on-account credit applied to customer ABC Inc:
-
Transaction Number: OC-101
-
Bill-to Customer: ABC Inc
-
Transaction Date: 05-Jun-11
-
Credit Amount: -1000
The RA_CUSTOMER_TRX table stores On-Account Credit transaction number OC-101 as follows:
CUSTOMER_TRX_ID |
TRX_NUMBER |
ABC Inc BILL_TO_CUSTOMER_ID |
TRX_DATE |
PREVIOUS_CUSTOMER_TRX_ID |
---|---|---|---|---|
660108 |
OC-101 |
NA |
05-Jun-11 |
NULL |
The previous_customer_trx_id column is NULL because the credit doesn't apply to a specific invoice.
The RA_CUSTOMER_TRX_LINES table stores On-Account Credit transaction number OC-101 as follows:
CUSTOMER_TRX_LINE_ID |
CUSTOMER_TRX_ID |
LINK_TO_CUST_TRX_LINE_ID |
LINE_TYPE |
EXTENDED_AMOUNT |
PREVIOUS_CUSTOMER_TRX_ID |
PREVIOUS_CUSTOMER_TRX_LINE_ID |
---|---|---|---|---|---|---|
170 |
660108 |
NA |
LINE |
-1000 |
NA |
NA |
If there had been a sales credit for this invoice, records relating to the revenue credit would be inserted in the RA_CUST_TRX_LINE_SALESREPS table, linked using the column customer_trx_line_id.
For on-account credits, Receivables inserts one record into the RA_CUSTOMER_TRX_LINES table. The total value of the credit is stored in the EXTENDED_AMOUNT column. The previous_customer_trx_line_id and previous_customer_trx_id columns are null because the credit doesn't apply to a specific invoice.
The RA_CUST_TRX_LINE_GL_DIST table stores On-Account Credit transaction number OC-101 as follows:
CUST_TRX_LINE_GL_DIST_ID |
CODE_COMBINATION_ID |
CUSTOMER_TRX_LINE_ID |
ACCOUNT_CLASS |
AMOUNT |
---|---|---|---|---|
210220 |
01-1200-1000-3000 |
NA |
REC |
-1000 |
210221 |
01-8100-1000-3000 |
170 |
REV |
-1000 |
Chargebacks
You create chargebacks to decrease the balance of an invoice and to create another debit item for the same amount. Receivables handles chargebacks the same as invoices, but also creates an adjustment to decrease the balance of the invoice.
Receivables uses the following tables to store chargeback information:
-
RA_CUSTOMER_TRX
-
RA_CUSTOMER_TRX_LINES
-
RA_CUST_TRX_LINE_GL_DIST
-
AR_ADJUSTMENTS
-
AR_PAYMENT_SCHEDULES
Consider again sample invoice I-101:
-
Invoice Number: I-101
-
Bill-to Customer: ABC Inc
-
Invoice Date: 22-May-11
-
Invoice Total: $6400
You receive a payment for $2000 on June 1, 2011, and decide to create chargeback CB-101 for the balance due of $4400.
The RA_CUSTOMER_TRX table stores CB-101 as follows:
CUSTOMER_TRX_ID |
TRX_NUMBER |
BILL_TO_CUSTOMER_ID |
TRX_DATE |
---|---|---|---|
765432 |
CB-101 |
ABC Inc |
01-Jun-11 |
The RA_CUSTOMER_TRX_LINES table stores CB-101 as follows:
CUSTOMER_TRX_LINE_ID |
CUSTOMER_TRX_ID |
LINK_TO_CUST_TRX_LINE_ID |
LINE_TYPE |
EXTENDED_AMOUNT |
---|---|---|---|---|
711 |
765432 |
NA |
CB |
4400 |
Receivables creates one record in RA_CUSTOMER_TRX_LINES for the chargeback with a line_type of CB and the EXTENDED_AMOUNT equal to the balance of the invoice.
This has no impact on the RA_CUST_TRX_LINE_SALESREPS table.
The RA_CUST_TRX_LINE_GL_DIST table stores CB-101 as follows:
CUST_TRX_LINE_GL_DIST_ID |
CODE_COMBINATION_ID |
CUSTOMER_TRX_LINE_ID |
ACCOUNT_CLASS |
AMOUNT |
---|---|---|---|---|
660116 |
01-1200-1000-3000 |
NULL |
REC |
4400 |
660117 |
01-8100-1000-3000 |
711 |
REV |
4400 |
Receivables inserts two records into the RA_CUST_TRX_LINE_GL_DIST table. The code_combination_id of the REC record stores the receivable account distribution for the chargeback. The code_combination_id of the REV record stores the revenue account distribution for the chargeback.
The AR_ADJUSTMENTS table stores CB-101 as follows:
ADJUSTMENT_ID |
AMOUNT |
CUSTOMER_TRX_ID |
TYPE |
PAYMENT_SCHEDULE_ID |
CODE_COMBINATION_ID |
---|---|---|---|---|---|
57931 |
-4400 |
101467 |
INVOICE |
30191 |
01-8100-1000-3000 |
When the chargeback is created, Receivables inserts a record into the AR_ADJUSTMENTS table to record an adjustment against the invoice. The amount column equals the inverse of the amount_due_remaining column on the invoice payment schedule in the AR_PAYMENT_SCHEDULES table. The customer_trx_id and the payment_schedule_id columns reference the original invoice.
For chargebacks, the type column is always INVOICE. The code_combination_id column stores the revenue account of the chargeback. This transaction will offset the REV distribution from the RA_CUST_TRX_LINE_GL_DIST table. To link this adjustment with the chargeback, the chargeback_customer_trx_id column, which isn't shown, stores the customer_trx_id of the chargeback.
The AR_PAYMENT_SCHEDULES table stores CB-101 as follows:
PAYMENT_SCHEDULE_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE_REMAINING |
CUSTOMER_TRX_ID |
TRX_NUMBER |
STATUS |
AMOUNT_APPLIED |
CLASS |
AMOUNT_ADJUSTED |
---|---|---|---|---|---|---|---|---|
565785 |
4400 |
4400 |
765432 |
CB-101 |
OP |
NULL |
CB |
NULL |
The class column identifies this payment schedule as a chargeback. The example chargeback has a status of OP (open) and an amount_applied of NULL, because no payment has been applied against it. The amount_due_original and amount_due_remaining columns equal the amount of the chargeback.
After creating the chargeback , the AR_PAYMENT_SCHEDULES table stores Invoice I-101 as follows:
PAYMENT_SCHEDULE_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE_REMAINING |
CUSTOMER_TRX_ID |
TRX_NUMBER |
STATUS |
AMOUNT_APPLIED |
CLASS |
AMOUNT_ADJUSTED |
---|---|---|---|---|---|---|---|---|
30191 |
6400 |
0 |
101467 |
I-101 |
CL |
2000 |
INV |
-4400 |
Receivables updates the invoice payment schedule in the AR_PAYMENT_SCHEDULES table by reducing the amount_due_remaining column to zero, to reflect the application of the chargeback to the invoice. The amount_adjusted column equals the amount of the chargeback and the status column is changed to closed (CL).
Adjustments
You can create adjustments to increase or decrease invoice balances. You can make adjustments to invoices, lines, tax, or freight. Receivables uses the following tables to store adjustment information:
-
AR_ADJUSTMENTS
-
AR_PAYMENT_SCHEDULES
Consider an adjustment to invoice I-101 to write off the remaining balance of $2400.
The AR_ADJUSTMENTS table stores the adjustment to I-101 as follows:
ADJUSTMENT_ID |
AMOUNT |
CUSTOMER_TRX_ID |
TYPE |
PAYMENT_SCHEDULE_ID |
CODE_COMBINATION_ID |
---|---|---|---|---|---|
987654 |
-2400 |
899143 |
INVOICE |
646566 |
01-5100-3000-1000 |
Receivables inserts a record into the AR_ADJUSTMENTS table to record adjustment details, such as the amount, the type of adjustment, the customer_trx_id, and the payment_schedule_id of the invoice to adjust. The amount column equals the amount of the adjustment. The code_combination_id column stores the general ledger distribution for the adjustment transaction.
The AR_PAYMENT_SCHEDULES table stores the adjustment to I-101 as follows:
PAYMENT_SCHEDULE_ID |
AMOUNT_DUE_ORIGINAL |
AMOUNT_DUE_REMAINING |
CUSTOMER_TRX_ID |
TRX_NUMBER |
STATUS |
AMOUNT_APPLIED |
CLASS |
AMOUNT_ADJUSTED |
---|---|---|---|---|---|---|---|---|
646566 |
6400 |
0 |
899143 |
I-101 |
CL |
4000 |
INV |
-2400 |
Receivables updates the payment schedule record of the invoice in AR_PAYMENT_SCHEDULES, by adjusting the amount_due_remaining to zero, changing the status to CL, and changing the amount_adjusted to -2400.