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.
If the cash receipt isn't confirmed in the AR_CASH_RECEIPT_HISTORY table, the applications of that receipt aren't reflected in the payment schedule of the transaction the receipt is applied against.
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.