How Payment Activities are Stored

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.

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