How Transaction Activities are Stored

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

Note: 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

Note: 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

Note: 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

Note: 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

Note: 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

Note: 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

Note: 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

Note: 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

Note: 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

Note: 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.

Note: 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.