Receivables uses the following tables to store your invoice and deposit information:
RA_CUSTOMER_TRX
RA_CUSTOMER_TRX_LINES
RA_CUST_TRX_LINE_GL_DIST
AR_PAYMENT_SCHEDULES
AR_ADJUSTMENTS
Consider a sample invoice:
Invoice Number: I-102
Bill-To: ABC Inc
Invoice Date: 22-May-94
Invoice Lines:
| Invoice Line | Amount | Tax | Total Amount |
|---|---|---|---|
| 1 Table @ $1000 | 1000.00 | 100.00 | $1100.00 |
with a sample deposit:
Deposit Number: D-101
Bill-To: ABC Inc
Deposit Date: 20-May-94
Amount: $500
Invoice I-102 applied against deposit D-101 is stored in this table as follows:
| customer_trx_id | trx_number | bill_to_customer_id | trx_date |
|---|---|---|---|
| 10895 | I-102 | ABC Inc | 22-May-94 |
Invoice I-102 applied against deposit D-101 is stored in this table as follows:
| customer_trx_line_id | customer_trx_id | link_to_cust_ trx_line_id | line_type | extended_amount |
|---|---|---|---|---|
| 110 | 10895 | LINE | 1000 | |
| 111 | 10895 | 110 | TAX | 100 |
If there had been a sales credit for this invoice, records relating to the sales credit would be inserted in the table RA_CUST_TRX_LINE_SALESREPS, linked via the column customer_trx_line_id.
Invoice I-102 applied against deposit D-101 is stored in this table as follows:
| cust_trx_line_gl_dist_id | code_combination_id | customer_trx_line_id | account_class | amount |
|---|---|---|---|---|
| 111213 | 01-1200-1000-3000 | REC | 1100 | |
| 111214 | 01-8100-1000-3000 | 110 | REV | 1000 |
| 111215 | 01-4100-1000-3000 | 111 | TAX | 100 |
Invoice I-102 applied against deposit D-101 is stored in this table as follows:
| payment_schedule_id | amount_due_original | amount_due_remaining | customer_trx_id | cash_receipt_id | trx_number | status | amount_applied | class |
|---|---|---|---|---|---|---|---|---|
| 302301 | 1100 | 1100 | 10895 | NULL | I-102 | OP | NULL | INV |
The payment schedule for the invoice originally shows an amount_due_remaining of 1100.
Invoice I-102 applied against deposit D-101 is stored in this table as follows:
| adjustment_id | amount | customer_trx_id | type | payment_schedule_id | code_combination_id |
|---|---|---|---|---|---|
| 45678 | -500 | 10895 | INVOICE | 302301 | 01-6200-1000-3000 |
When the invoice is applied to the deposit, Receivables inserts a record into AR_ADJUSTMENTS to record an adjustment against the invoice. The amount column equals the inverse of the amount_due_remaining from the AR_PAYMENT_SCHEDULES table for the deposit or the total value of the invoice lines, whichever is smaller. Receivables uses the customer_trx_id to link the adjustment to the invoice. The payment_schedule_id column links the adjustment to the invoice payment schedule in the table, AR_PAYMENT_SCHEDULES.
The code_combination_id column stores the unearned revenue account of the deposit. Receivables will use this account to reverse the unearned revenue distribution, originally created by the deposit, and will use the receivable account of the invoice to reduce the invoice balance.
Invoice I-102 applied against deposit D-101 is stored in this table as follows:
| payment_schedule_id | amount_due_original | amount_due_remaining | customer_trx_id | trx_number | status | amount_applied | class | amount_adjusted |
|---|---|---|---|---|---|---|---|---|
| 302301 | 1100 | 600 | 10895 | I-102 | OP | NULL | INV | -500 |
The invoice payment schedule record in AR_PAYMENT_SCHEDULES is updated to reflect the adjustment of the deposit. The amount_due_remaining column is reduced by 500 and the amount_adjusted column is -500.
Receivables does not update the payment schedule record of the deposit in AR_PAYMENT_SCHEDULES when an invoice is applied to the deposit. The payment schedule of the deposit will be updated as adjustments and receipts are applied to this independent billing.