Previous | Next | Contents | Index | Navigation | Glossary | Library |
RA_CUSTOMER_TRX | |||
---|---|---|---|
customer_trx_id | trx_number | bill_to_customer_id | trx_date |
Table 1 - 99. (Page 1 of 1) |
The RA_CUSTOMER_TRX table stores invoice, debit memo, commitment and credit memo header information. Each of these transactions is stored as a unique record, based on the primary key, customer_trx_id. The transaction number, transaction date and billing customer are stored in the trx_number, trx_date and bill_to_customer_id columns, respectively.
Additional information stored in this table includes ship-to customer, document sequence number, currency code and a transaction complete flag. The transaction type for the invoice is stored in the RA_CUST_TRX_TYPES table, but can be referenced via the foreign key cust_trx_type_id.
RA_CUSTOMER_TRX_LINES | ||||
customer_ trx_line_id | customer_trx_id | link_to_cust_ trx_line_id | line_type | extended_ amount |
Table 1 - 100. (Page 1 of 1) |
The RA_CUSTOMER_TRX_LINES table stores invoice, debit memo, commitment and credit memo line level information. Each transaction line is stored as a unique record, based on the primary key, customer_trx_line_id column. The customer_trx_id column is a foreign key to the RA_CUSTOMER_TRX table. The line_type column identifies the type of data contained in the record. Valid line types are CHARGES, FREIGHT, LINE and TAX. Any record with a line type of TAX or FREIGHT refers to the original invoice line via the link_to_cust_trx_line_id column, except for header freight transactions. The total amount for each transaction line is stored in the column extended_amount.
RA_CUST_TRX_LINE_SALESREPS | |||||
---|---|---|---|---|---|
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 |
Table 1 - 101. (Page 1 of 1) |
((vertical COM)) ?
RA_CUST_TRX_LINE_SALESREPS stores sales credit assignments for invoice lines. Each assignment is stored as a unique record, based on the primary key, cust_trx_line_salesrep_id. If you base your accounting distributions on sales credits, the sales credit assignments in this table map to the RA_CUST_TRX_LINE_GL_DIST table. The sales_rep_id column identifies the salesperson receiving the credit for this transaction. The customer_trx_line_id column is a foreign key to the RA_CUSTOMER_TRX_LINES table.
((vertical COM)) ?
The revenue_amount_split column stores the amount of the invoice line assigned to this salesperson. The non_revenue_amount_split column stores the amount of the non-header freight and tax lines assigned to this salesperson. If the sales credit were derived based on a percentage of the transaction line rather than a specific amount, the columns revenue_percent_split and non_revenue_percent_split would store the percentages of the transaction lines assigned to this salesperson. The prev_cust_trx_line_salesrep_id column references another sales credit assignment to which the current record is being applied.
RA_CUST_TRX_LINE_GL_DIST | ||||
---|---|---|---|---|
cust_trx_line_ gl_dist_id | code_combination_id | customer_ trx_line_id | account_class | amount |
Table 1 - 102. (Page 1 of 1) |
RA_CUST_TRX_LINE_GL_DIST stores the accounting distribution for invoice, debit memo, commitment, and credit memo transactions. Each distribution is stored as a unique record, based on the primary key, cust_trx_line_gl_dist_id. The customer_trx_line_id column is a foreign key to the RA_CUSTOMER_TRX_LINES table. The account_class column describes the account type, while the code_combination_id column identifies the general ledger account. Valid account classes are CHARGES, FREIGHT, REC, REV, SUSPENSE, TAX, UNBILL and UNEARN. The account_class, REC, represents the receivable account distribution. The amount column for REC records is equal to the sum of all invoice lines. Therefore, there is no link to RA_CUSTOMER_TRX_LINES and the column customer_trx_line_id is null for these records. The REC record is linked to the table, RA_CUSTOMER_TRX, via the customer_trx_id column. For all other account classes, credits are represented by positive numbers and debits are represented by negative numbers.
AR_PAYMENT_SCHEDULES | ||||||||
---|---|---|---|---|---|---|---|---|
payment_ schedule _id | amount_ due_ original | amount_ due_ remaining | customer _trx_id | cash_ receipt_ id | trx_ number | status | amount_ applied | class |
Table 1 - 103. (Page 1 of 1) |
AR_PAYMENT_SCHEDULES stores customer balance information at the transaction level. Each transaction's balance is stored as a unique record, based on the primary key, payment_schedule_id. The class column identifies the transaction type and determines which columns Receivables updates when a transaction is stored. For billing transactions, the AR_PAYMENT_SCHEDULES table joins the RA_CUSTOMER_TRX table via the customer_trx_id column and stores NULL in the cash_receipt_id column. For payment transactions, the AR_PAYMENT_SCHEDULES table joins the AR_CASH_RECEIPTS table via the cash_receipt_id column and stores NULL in the customer_trx_id column.
TRANSACTION | CLASS | FOREIGN KEY | TABLE |
Invoices | INV | customer_trx_id | RA_CUSTOMER_TRX |
Debit Memos | DM | customer_trx_id | RA_CUSTOMER_TRX |
Credit Memos | CM | customer_trx_id | RA_CUSTOMER_TRX |
Deposits | DEP | customer_trx_id | RA_CUSTOMER_TRX |
Guarantees | GUAR | customer_trx_id | RA_CUSTOMER_TRX |
Chargebacks | CB | customer_trx_id | RA_CUSTOMER_TRX |
Receipts | PMT | cash_receipts_id | AR_CASH_RECEIPTS |
For the amount_due_original and amount_due_remaining columns debit items, such as invoices, are stored as positive numbers and credit items, such as credit memos and payments, are stored as negative numbers. The current customer balance is reflected by the sum of the amount_due_remaining column for all confirmed payment schedules for a given customer.
AR_ADJUSTMENTS | |||||
---|---|---|---|---|---|
adjustment _id | amount | customer_trx _id | type | payment_ schedule_id | code_ combination_id |
Table 1 - 104. (Page 1 of 1) |
AR_ADJUSTMENTS stores information about invoice adjustments. Each adjustment is stored as a unique record, based on the primary key, adjustment_id. The amount column stores the amount of the adjustment. Receivables uses the customer_trx_id and payment_schedule_id to link the adjustment to the adjusted transaction and to update the amount_due_remaining and amount_adjusted columns of the adjusted transaction's payment schedule in the AR_PAYMENT_SCHEDULES table. The type column stores a description of the transaction to which the adjustment applies. Valid types include:
AR_RECEIVABLE_APPLICATIONS | |||||||
---|---|---|---|---|---|---|---|
receivable_ application _id | amount_ applied | status | payment_ schedule_ id | code_ combination_ id | cash_ receipt _id | applied_ payment_ schedule_ id | applied_ customer_ trx_id |
Table 1 - 105. (Page 1 of 1) |
AR_RECEIVABLE_APPLICATIONS stores account distributions for receipt and credit memo applications and maps the application transaction to the applied transaction. Each accounting distribution is stored as a unique record, based on the primary key, receivable_application_id. The payment_schedule_id column links the receipt or credit memo to its payment schedule in the AR_PAYMENT_SCHEDULES table. The cash_receipt_id column stores the receipt id of payment transactions, while the cust_trx_id column, which is not shown, stores the transaction id for credit memo transactions. The applied_payment_schedule_id and applied_customer_trx_id columns reference the transaction to which this record applies.
The status column describes the state of the application transaction. For credit memos, the status will always be APP to identify the credit memo as applied. For receipt transactions, valid status values are APP, UNAPP, UNID, REV, NSF, and STOP. The code_combination_id column stores the general ledger account for the application transaction, based on the status. The amount_applied column stores the amount of the receipt or credit memo as a positive value.
Note: For cash basis accounting, Receivables uses the table AR_CASH_BASIS_DISTRIBUTIONS to store account distribution information. This table shows the distribution to revenue accounts of a given receipt based on the application of the receipt.
AR_CREDIT_MEMO_AMOUNTS | |||
---|---|---|---|
credit_memo_amount_id | customer_trx_line_id | gl_date | amount |
Table 1 - 106. (Page 1 of 1) |
AR_CREDIT_MEMO_AMOUNTS stores the GL dates and amounts for credit memos to use when they are applied to invoices with rules. Each credit memo application date is stored as a unique record, based on the primary key, credit_memo_amount_id. The customer_trx_line_id references the transaction line to which this credit memo applies. The gl_date column stores the date the credit memo should be applied to the invoice and the amount column stores the amount to apply.
AR_CASH_RECEIPTS | ||||
cash_receipt_id | amount | status | receipt_number | type |
Table 1 - 107. (Page 1 of 1) |
AR_CASH_RECEIPTS stores a unique record for each receipt, based on the primary key, cash_receipt_id. The status column describes the state of the receipt in relation to customer invoices and balances. Valid status values are:
AR_CASH_RECEIPT_HISTORY | ||
---|---|---|
cash_receipt_history_id | amount | status |
Table 1 - 108. (Page 1 of 1) |
AR_CASH_RECEIPT_HISTORY stores the current status and history of a receipt. Each status change is stored as a unique transaction, based on the primary key, cash_receipt_history_id. The status column describes which step of the receipt's life cycle the receipt has reached. Valid status values are:
AR_MISC_CASH_DISTRIBUTIONS | ||
---|---|---|
misc_cash_distribution_id | cash_receipt_id | code_combination_id |
Table 1 - 109. (Page 1 of 1) |
AR_MISC_CASH_DISTRIBUTIONS stores the accounting distribution for miscellaneous cash receipts. Each distribution is stored as a unique record, based on the primary key, misc_cash_distribution_id. The distributions are linked to the receipt by the column cash_receipt_id. The code_combination_id column stores the general ledger account assigned to this receipt.
Previous | Next | Contents | Index | Navigation | Glossary | Library |