How Payment Activities are Stored
This topic describes how Receivables stores payment activities.
These payment activities are:
-
Unapplied Receipts
-
Applied Receipts
-
Reverse Receipts
-
Miscellaneous Receipts
Unapplied Receipts
When you create a receipt, Receivables creates records in the following tables:
-
AR_CASH_RECEIPTS
-
AR_CASH_RECEIPT_HISTORY
-
AR_PAYMENT_SCHEDULES
-
AR_RECEIVABLE_APPLICATIONS
Consider this sample receipt:
-
Receipt Number: R-101
-
Received From: ABC Inc
-
Receipt Date: 05-Jul-11
-
Receipt Amount: 4000
The AR_CASH_RECEIPTS table stores information from Receipt R-101 as follows:
credit_receipt_id |
amount |
status |
receipt_number |
type |
---|---|---|---|---|
338700 |
4000 |
UNAPP |
R-101 |
CASH |
The AR_CASH_RECEIPT_HISTORY table stores information from Receipt R-101 as follows:
cash_receipt_history_id |
amount |
status |
---|---|---|
457890 |
4000 |
CLEARED |
The AR_PAYMENT_SCHEDULES table stores information from Receipt R-101 as follows:
payment_schedule_id |
amount_due_original |
amount_due_remaining |
cash_receipt_id |
customer_ trx_id |
trx_number |
status |
amount_applied |
class |
---|---|---|---|---|---|---|---|---|
510555 |
-4000 |
-4000 |
338700 |
NULL |
R-101 |
OP |
0 |
PMT |
The example receipt has a status of OP (open) and an amount_applied of NULL because the receipt hasn't been applied to a customer balance. The amount_due_original column equals the sum of the amount column in the AR_CASH_RECEIPTS table for the given cash_receipts_id. The class is PMT because this is a receipt related to a receivable activity. The amount_due_original and amount_due_remaining columns equal the inverse amount of the receipt.
The AR_RECEIVABLE_APPLICATIONS table stores information from Receipt R-101 as follows:
payment_schedule_id |
amount_applied |
status |
payment_schedule_id |
code_combination_id |
cash_receipt_id |
applied_payment_schedule_id |
applied_customer_trx_id |
---|---|---|---|---|---|---|---|
408289 |
4000 |
UNAPP |
400100 |
01-1100-1000 |
338700 |
NULL |
NULL |
The applied_payment_schedule_id and applied_customer_trx_id columns are NULL because the receipt hasn't been applied to a specific transaction. The amount_applied column equals the amount of the receipt. The code_combination_id column stores the general ledger account associated with unapplied cash receipts.
Applied Receipts: Same Currency
When you apply a receipt, Receivables creates records in the following tables:
-
AR_CASH_RECEIPTS: Stores one record for each receipt.
-
AR_PAYMENT_SCHEDULES: Stores customer balance information at the transaction level.
-
AR_RECEIVABLE_APPLICATIONS: Stores accounting entries for cash and credit memo applications.
Consider sample receipt R-101, which is now applied to customer invoice I-101 for 6400 USD:
-
Receipt Number: R-101
-
Received From: ABC Inc
-
Receipt Date: 05-Jul-11
-
Receipt Amount: 4000
The AR_CASH_RECEIPTS table stores information from Receipt R-101 as follows:
credit_receipt_id |
receipt_number |
amount |
status |
type |
currency |
rate |
---|---|---|---|---|---|---|
1521 |
R-101 |
4000 |
UNAPP |
CASH |
USD |
NULL |
After you apply the receipt, Receivables updates the status column from UNAPP to APP. If the receipt were only partially applied, the status would remain UNAPP.
The AR_PAYMENT_SCHEDULES table stores information from Receipt R-101 as follows:
payment_schedule_id |
amount_due_original |
amount_due_remaining |
cash_receipt_id |
customer_ trx_id |
trx_number |
status |
amount_applied |
class |
currency |
---|---|---|---|---|---|---|---|---|---|
2211 |
6400 |
2400 |
NULL |
1422 |
I-101 |
OP |
4000 |
INV |
USD |
2225 |
-4000 |
0 |
1521 |
NA |
R-101 |
CL |
-4000 |
PMT |
USD |
The payment schedule of invoice I-101 has a class of INV, while the payment schedule of receipt R-101 has a class of PMT. The payment schedule record of the receipt is updated to reduce the amount_due_remaining column by the amount applied. Since the entire amount is applied, the amount_due_remaining is zero. The status of the receipt is changed to CL, and the amount_applied is -4000.
Receivables updates the payment schedule record of the invoice to reduce the amount_due_remaining by the amount of the applied receipt. The status is still OP because the entire balance hasn't been paid. Receivables updates the amount_applied to reflect the amount applied to the invoice.
The AR_RECEIVABLE_APPLICATIONS table stores information from Receipt R-101 as follows:
receivable_application_id |
status |
trx_number |
amount_applied |
code_combination_id |
---|---|---|---|---|
3132 |
UNAPP |
NULL |
4000 |
01-1100-1000 |
3134 |
UNAPP |
NULL |
- 4000 |
01-1200-1100 |
3135 |
APP |
I-101 |
4000 |
01-1200-1100 |
Receivables inserts three records into the AR_RECEIVABLE_APPLICATIONS table. The first record, with a status of UNAPP, records the original unapplied receipt. The second record, with a status of UNAPP, offsets the original unapplied receipt. The third record, with a status of APP, stores the applied receipt information, including a reference to the applied invoice, from the trx_number column.
The code_combination_id column stores the general ledger account for this receipt, based on the status of the receipt. For the UNAPP record, the code_combination_id represents the general ledger account associated with unapplied receipts. For the APP record, the code_combination_id is the receivable account associated with the invoice transaction to which this receipt is applied.
Applied Receipts: Cross Currency
Consider the sample receipt R-102, which, according to the customer remittance advice, is to fully pay invoice I-102, using a cross currency rate of 1 CAD = 0.729355 EUR.
Receipt Number: R-102:
-
Received From: ABC Inc.
-
Transaction Date: 5-JUL-11
-
Receipt Amount: 100 EUR
-
Conversion Rate: 1 EUR = .860956 USD
Invoice Number: I-102:
-
Transaction Date: 05-JUN-11
-
Invoice Amount: 52.50
-
Conversion Rate: 1 CAD = .666667 USD
The AR_CASH_RECEIPTS table stores information from Receipt R-102 as follows:
credit_receipt_id |
receipt_number |
amount |
status |
type |
currency |
rate |
---|---|---|---|---|---|---|
1521 |
R-102 |
100 |
APP |
CASH |
EUR |
.865956 |
When you apply the entire receipt, Receivables updates the status column from UNAPP to APP. If the receipt were only partially applied, the status would remain UNAPP.
The AR_PAYMENT_SCHEDULES table stores information from Receipt R-102 as follows:
payment_schedule_id |
amount_due_original |
amount_due_remaining |
cash_receipt_id |
customer_ trx_id |
trx_number |
status |
amount_applied |
class |
currency |
---|---|---|---|---|---|---|---|---|---|
2212 |
52.5 |
0 |
NA |
1423 |
I-102 |
CL |
52.5 |
INV |
CAD |
2224 |
-100 |
0 |
1520 |
NA |
R-102 |
CL |
-100 |
PMT |
EUR |
The payment schedule of the invoice has a class of INV, while the payment schedule of the receipt has a class of PMT. The payment schedule record of the receipt is updated to reduce the amount_due_remaining column by the amount applied. Since the entire amount is applied, the amount_due_remaining is zero. The status of the receipt is changed to CL, and the amount_applied is -100.
The AR_RECEIVABLE_APPLICATIONS table stores information from Receipt R-102 as follows:
receivable_application_id |
status |
trx_number |
amount_applied |
amount_applied_from |
trx_to_rcpt_rate |
acct_amt_applied_to |
acct_amt_applied_from |
code_combination_id |
---|---|---|---|---|---|---|---|---|
3142 |
UNAPP |
NULL |
100 |
NA |
NA |
NA |
33.33 |
01-1100-1000 |
3134 |
UNAPP |
NULL |
-100 |
-100 |
NA |
-33.33 |
-33.33 |
01-1200-1100 |
3135 |
APP |
I-102 |
52.5 |
100 |
1.9048 |
35 |
33.33 |
01-1200-1000 |
Receivables inserts three records into the AR_RECEIVABLE_APPLICATIONS table. The first record, with a status of UNAPP, records the original unapplied receipt. The second record, with a status of UNAPP, offsets the original unapplied receipt. The third record, with a status of APP, stores the applied receipt information, including a reference to the applied invoice, from the trx_number column.
The code_combination_id column stores the general ledger account for this receipt, based on the status of the receipt. For the UNAPP record, the code_combination_id represents the general ledger account associated with unapplied receipts. For the APP record, the code_combination_id is the receivable account associated with the invoice transaction to which this receipt is applied.
Reverse Receipts
When you reverse a receipt, Receivables creates records in the following tables:
-
AR_CASH_RECEIPTS
-
AR_CASH_RECEIPT_HISTORY
-
AR_PAYMENT_SCHEDULES
-
AR_RECEIVABLE_APPLICATIONS
If receipt R-101 isn't an actual receipt, then enter a reverse receipt transaction to cancel the receipt.
The AR_CASH_RECEIPTS table stores information for the reversed receipt as follows:
credit_receipt_id |
amount |
status |
receipt_number |
type |
---|---|---|---|---|
338700 |
4000 |
REV |
R-101 |
CASH |
Receivables updates the status column of the original receipt from applied (APP) to reversed (REV).
The AR_CASH_RECEIPTS_HISTORY table stores information for the reversed receipt as follows:
cash_receipt_history_id |
amount |
status |
---|---|---|
545352 |
4000 |
REVERSED |
A new record, which isn't postable, is inserted into the AR_CASH_RECEIPT_HISTORY table to record the reversed receipt. Additionally, the current_record_flag of the original cash receipt record is updated to null, while the reverse_gl_date column of the original receipt record is populated.
The AR_PAYMENT_SCHEDULES table stores information for the reversed receipt as follows:
payment_s chedule_id |
amount_due_original |
amount_due_remaining |
cash_receipt_id |
customer_trx_id |
trx_number |
status |
amount_applied |
class |
---|---|---|---|---|---|---|---|---|
510555 |
-4000 |
0 |
338700 |
NULL |
R-101 |
CL |
0 |
PMT |
30191 |
6400 |
6400 |
NULL |
101467 |
I-101 |
OP |
0 |
INV |
The payment schedule of the invoice has a class of INV, while the payment schedule of the receipt has a class of PMT. Because the receipt was reversed, the amount_due_remaining and amount_applied columns are zero and the status column is closed (CL).
Receivables updates the payment schedule record of the invoice to increase the amount_due_remaining by the amount of the reversed receipt. The status is still OP because the entire balance isn't paid. The amount_applied column is zero because no transactions have been applied to the invoice.
The AR_RECEIVABLE_APPLICATIONS table stores information for the reversed receipt as follows:
receivable_application_id |
amount_applied |
status |
payment_schedule_id |
code_combination_id |
cash_receipt_id |
applied_payment_schedule_id |
applied_customer_trx_id |
---|---|---|---|---|---|---|---|
408292 |
-4000 |
APP |
400100 |
01-1200-1100 |
338700 |
30191 |
101467 |
408293 |
4000 |
UNAPP |
400100 |
01-1100-1000 |
338700 |
NULL |
NULL |
408294 |
-4000 |
UNAPP |
400100 |
01-1100-1000 |
338700 |
NULL |
NULL |
Receivables inserts three records into the AR_RECEIVABLE_APPLICATIONS table. The first record, with a status of APP, offsets the original application of the receipt, including a reference to the applied invoice, from the applied_payment_schedule_id and applied_customer_trx_id columns.
The second and third records, with a status of UNAPP, offset the original unapplied transactions. The code_combination_id for the APP record is the receivable account associated with the invoice to which this receipt was originally applied. The code_combination_id for the two UNAPP records is the general ledger account associated with unapplied receipts.
Miscellaneous Receipts
When you create a miscellaneous receipt, Receivables creates records in the following tables:
-
AR_CASH_RECEIPTS
-
AR_CASH_RECEIPT_HISTORY
-
AR_MISC_CASH_DISTRIBUTIONS
Consider this sample miscellaneous receipt:
-
Receipt Number: R-102
-
Received From: Stock Broker
-
Receipt Date: 07-Jul-11
-
Receipt Amount: 500
The AR_CASH_RECEIPTS table stores information for the miscellaneous receipt as follows:
credit_receipt_id |
amount |
status |
receipt_number |
type |
---|---|---|---|---|
345678 |
500 |
APP |
R-102 |
MISC |
Receivables uses a status of APP for miscellaneous receipts. The type column is MISC for receipts unrelated to a receivables activity. The amount column stores the net amount of the receipt, while the receipt_number column stores the receipt number.
The AR_CASH_RECEIPTS_HISTORY table stores information for the miscellaneous receipt as follows:
cash_receipt_history_id |
amount |
status |
---|---|---|
467890 |
500 |
CLEARED |
The only valid status values for a miscellaneous receipt are REMITTED, CLEARED, and REVERSED.
The AR_MISC_CASH_DISTRIBUTIONS table stores information for the miscellaneous receipt as follows:
misc_cash_distribution_id |
cash_receipt_id |
code_combination_id |
amount |
---|---|---|---|
101789 |
345678 |
01-1190-1000-3000 |
250 |
101790 |
345678 |
01-1195-1000-3000 |
250 |
The code_combination_id stores the general ledger account associated with miscellaneous receipts. Each receipt may have multiple account distributions. The sum of the distributions for a given receipt will equal the amount of the receipt.