AR_TRX_SUMMARY
This table stores the fields, such as 'Total Invoices Value', 'Total Invoices Count', 'Total Cash Receipts Value', etc., that require summarization for a specified period of time. The lowest level of granularity at which the data can be stored and retrieved from this table is as of a specified date for a given currency at a specified 'BILL_TO' location for a specific account of a party.
Details
-
Schema: FUSION
-
Object owner: AR
-
Object type: TABLE
-
Tablespace: SUMMARY
Primary Key
| Name | Columns |
|---|---|
|
AR_TRX_SUMMARY_PK |
CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, AS_OF_DATE, ORG_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| CUST_ACCOUNT_ID | NUMBER | 18 | Yes | Customer Account Identifier | |
| SITE_USE_ID | NUMBER | 18 | Yes | Site Use Identifier | |
| ORG_ID | NUMBER | 18 | Yes | Indicates the identifier of the business unit associated to the row. | |
| CURRENCY | VARCHAR2 | 30 | Yes | Currency code | |
| AS_OF_DATE | DATE | Yes | Specific date for which the current summary record has been created. | ||
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| OP_BAL_HIGH_WATERMARK | NUMBER | This column value contains the highest Open Receivables Balance for the specific Account, Site, Currency and date (identified by as_of_date). The Open Receivables Balance is calculated by summing the amount_due_remaining on the payment schedules of all the debit items and unresolved cash on all cash receipts. | |||
| OP_BAL_HIGH_WATERMARK_DATE | DATE | Highest open receivables balance, which is the sum of the amount_due_remaining on the payment schedules of the debit items and unresolved cash on all cash receipts, for a specific account, site, currency, and date. | |||
| TOTAL_CASH_RECEIPTS_VALUE | NUMBER | Sum of the amounts of all Cash Receipts for the specific Account, Site, Currency and a date. | |||
| TOTAL_CASH_RECEIPTS_COUNT | NUMBER | Count of payment schedules (installments) of all Chargebacks for the specific Account, Site, Currency and a date. | |||
| TOTAL_INVOICES_VALUE | NUMBER | Sum of the amount_due_original on payment schedules (installments) of all Invoices for the specific Account, Site, Currency and a date. | |||
| TOTAL_INVOICES_COUNT | NUMBER | Count of the payment schedules (installments) of all Invoices for the specific Account, Site, Currency and a date. | |||
| INV_PAID_AMOUNT | NUMBER | Sum of the total amount paid against closed invoices for a specific Account, Site, Currency and as of a given date. | |||
| INV_INST_PMT_DAYS_SUM | NUMBER | Summation of the product of number of days that it took to make payments against invoice installments and the paid amount for a specific Account, Site, Currency and a date i.e.(amount_applied*(apply_date - due_date)). | |||
| TOTAL_BILLS_RECEIVABLES_VALUE | NUMBER | Sum of the original amount of all bills receivable in a particular currency and on a particular date that belong to one customer site. | |||
| TOTAL_BILLS_RECEIVABLES_COUNT | NUMBER | Number of bills receivable in a particular currency and on a particular date that belong to one customer site. | |||
| TOTAL_CREDIT_MEMOS_VALUE | NUMBER | Sum of the amount_due_original on payment schedules (installments) of all Credit Memos for the specific Account, Site, Currency and a date. | |||
| TOTAL_CREDIT_MEMOS_COUNT | NUMBER | Count of payment schedules (installments) of all Credit Memos for the specific Account, Site, Currency and a date. | |||
| TOTAL_DEBIT_MEMOS_VALUE | NUMBER | Sum of the amount_due_original on payment schedules (installments) of all Debit Memos for the specific Account, Site, Currency and a date. | |||
| TOTAL_DEBIT_MEMOS_COUNT | NUMBER | Count of payment schedules (installments) of all Debit Memos for the specific Account, Site, Currency and a date. | |||
| TOTAL_CHARGEBACK_VALUE | NUMBER | Sum of the AMOUNT_DUE_ORIGINAL on payment schedules (installments) of all chargebacks for the specific customer account, site, currency and date. | |||
| TOTAL_CHARGEBACK_COUNT | NUMBER | This column value contains a count of the payment schedules (installments) of all chargebacks for the specific customer account, site, currency and date. | |||
| TOTAL_EARNED_DISC_VALUE | NUMBER | Sum of the Earned Discounts that were given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date. | |||
| TOTAL_EARNED_DISC_COUNT | NUMBER | Sum of the Earned Discounts that were given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date. | |||
| TOTAL_UNEARNED_DISC_VALUE | NUMBER | Sum of the Unearned Discounts that was given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date. | |||
| TOTAL_UNEARNED_DISC_COUNT | NUMBER | Count of the Unearned Discounts that were given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date. | |||
| TOTAL_ADJUSTMENTS_VALUE | NUMBER | Sum of the adjustment amounts on the payment schedules of all the debit items for the specific Account, Site, Currency and a date (identified by as_of_date column). Special adjustments, such as Adjustment Reversal, are not included because they are used for internal accounting purposes only. | |||
| TOTAL_ADJUSTMENTS_COUNT | NUMBER | Count of the adjustments on the payment schedules of all the debit items for the specific Account, Site, Currency and a date. Special adjustments, such as Adjustment Reversal are not included. | |||
| TOTAL_DEPOSITS_VALUE | NUMBER | Sum of the amount_due_original on payment schedules (installments) of all Deposits for the specific Account, Site, Currency and a date. | |||
| TOTAL_DEPOSITS_COUNT | NUMBER | Count of payment schedules (installments) of all Deposits for the specific Account, Site, Currency and a date. | |||
| SUM_APP_AMT_DAYS_LATE | NUMBER | Summation of the product of amount applied and the difference in apply date and due date for all the cash receipt applications against the Invoices Installments for the specific Account, Site, Currency and a date i.e. (amount_applied*(apply_date - due_date)). | |||
| SUM_APP_AMT | NUMBER | Sum of the amount applied against the closed payment schedules (installments) of all the Invoices for the specific Account, Site, Currency and date. | |||
| COUNT_OF_TOT_INV_INST_PAID | NUMBER | Count of the payment schedules (installments), that have been paid off completely, i.e., status = CL, of all Invoices for the specific Account, Site, Currency and a date. | |||
| COUNT_OF_INV_INST_PAID_LATE | NUMBER | Count of the payment schedules (installments) that have been paid late, i.e., status = CL and actual_date_closed > due_date of all Invoices for the specific Account, Site, Currency and a date. | |||
| COUNT_OF_DISC_INV_INST | NUMBER | Count of the payment schedules (installments) with earned or unearned discounts applied to Invoices for the specific Account, Site, Currency and a date | |||
| LARGEST_INV_AMOUNT | NUMBER | Transaction amount corresponding to the largest invoice for the specific Account, Site, Currency and a date. | |||
| LARGEST_INV_DATE | DATE | Transaction date corresponding to the largest invoice for the specific Account, Site, Currency and a date. | |||
| LARGEST_INV_CUST_TRX_ID | NUMBER | 18 | Unique identifier of the largest invoice for the specific Account, Site, Currency and a date. | ||
| DAYS_CREDIT_GRANTED_SUM | NUMBER | Summation of the product of amount_due_original and difference of the due_date and trx_date, i.e., (amount_due_original * (due_date - trx_date)), for the payment schedules (installments) of all Invoices for the specific Account, Site, Currency and a date. | |||
| NSF_STOP_PAYMENT_COUNT | NUMBER | Count of all cash receipts having a status of Reversed with a Reversal Category of Non Sufficient Funds for the specific Account, Site, Currency and a date. | |||
| NSF_STOP_PAYMENT_AMOUNT | NUMBER | Sum of the amount on all Cash Receipts having a status of Reversed with a Reversal Category of Non Sufficient Funds for the specific Account, Site, Currency and a date. | |||
| REFERENCE_1 | VARCHAR2 | 240 | Reference columns for future usage | ||
| REFERENCE_2 | VARCHAR2 | 240 | Reference columns for future usage | ||
| REFERENCE_3 | VARCHAR2 | 240 | Reference columns for future usage | ||
| REFERENCE_4 | VARCHAR2 | 240 | Reference columns for future usage | ||
| REFERENCE_5 | VARCHAR2 | 240 | Reference columns for future usage | ||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| AR_TRX_SUMMARY | hz_cust_site_uses_all | SITE_USE_ID |
| AR_TRX_SUMMARY | hz_cust_accounts | CUST_ACCOUNT_ID |
| AR_TRX_SUMMARY | ra_customer_trx_all | LARGEST_INV_CUST_TRX_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| AR_TRX_SUMMARY_N1 | Non Unique | Default | SITE_USE_ID, CURRENCY, AS_OF_DATE |
| AR_TRX_SUMMARY_U1 | Unique | Default | CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, AS_OF_DATE, ORG_ID |