AR_TRX_BAL_SUMMARY
This table stores the fields that summarize data over time, such as, Open Balance, Open Invoices Count, and Open Invoices Value. This table stores the business unit identifier to ensure that data can be retrieved by bill to customer account site. The lowest level of granularity at which the data is stored and retrieved from this table is for a given currency at a specified customer bill to location for a given customer account of a party.
Details
-
Schema: FUSION
-
Object owner: AR
-
Object type: TABLE
-
Tablespace: SUMMARY
Primary Key
| Name | Columns |
|---|---|
|
AR_TRX_BAL_SUMMARY_PK |
CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, 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 | 15 | Yes | Currency code | |
| 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. | ||
| BEST_CURRENT_RECEIVABLES | NUMBER | Amount due remaining on open payment schedules (installments) of all transactions, such as, Invoices, Debit Memos, Bills Receivables, Chargebacks and Deposits, that have a due date greater than or equal to the system date for the specific account, site and currency. | |||
| TOTAL_DSO_DAYS_CREDIT | NUMBER | Not currently used. | |||
| OP_INVOICES_VALUE | NUMBER | This column value contains the sum of amount_due_remaining on open payment schedules (installments) of all the Invoices the specific Account, Site and Currency. | |||
| OP_INVOICES_COUNT | NUMBER | This column value contains a count of the open payments schedules (installments) of all the Invoices for the specific Account, Site and Currency | |||
| OP_DEBIT_MEMOS_VALUE | NUMBER | This column value contains a count of the open payments schedules (installments) of all the Debit Memos for the specific Account, Site and Currency | |||
| OP_DEBIT_MEMOS_COUNT | NUMBER | This column value contains the sum of amount_due_remaining on open payment schedules (installments) of all the Debit Memos for the specific Account, Site and Currency. | |||
| OP_DEPOSITS_VALUE | NUMBER | This column value contains the sum of amount_due_remaining on open payment schedules (installments) of all the Deposits for the specific Account, Site and Currency. | |||
| OP_DEPOSITS_COUNT | NUMBER | This column value contains a count of the open payments schedules (installments) of all the Deposits for the specific Account, Site and Currency. | |||
| OP_BILLS_RECEIVABLES_VALUE | NUMBER | Remaining open balance on all bills receivable in a particular currency that belong to one customer site. | |||
| OP_BILLS_RECEIVABLES_COUNT | NUMBER | Number of bills receivable with an open balance in a particular currency that belong to one customer site. | |||
| OP_CHARGEBACK_VALUE | NUMBER | This column value contains the sum of amount_due_remaining on open payment schedules (installments) of all the Chargebacks for the specific Account, Site and Currency | |||
| OP_CHARGEBACK_COUNT | NUMBER | This column value contains a count of the open payments schedules (installments) of all the Chargebacks for the specific Account, Site and Currency. | |||
| OP_CREDIT_MEMOS_VALUE | NUMBER | This column value contains the sum of amount_due_remaining on open payment schedules (installments) of all the Credit Memos for the specific Account, Site and Currency. | |||
| OP_CREDIT_MEMOS_COUNT | NUMBER | This column value contains a count of the open payments schedules (installments) of all the Credit Memos for the specific Account, Site and Currency. | |||
| UNRESOLVED_CASH_VALUE | NUMBER | This column value contains the sum of unresolved cash available on all the Cash Receipts for the specific Account, Site and Currency. | |||
| UNRESOLVED_CASH_COUNT | NUMBER | This column value contains count of all the Cash Receipts for the specific Account, Site and Currency that have unresolved cash on them. | |||
| RECEIPTS_AT_RISK_VALUE | NUMBER | This column value contains the sum of cash receipt amounts for the specific Account, Site and Currency that have a status of 'At Risk'. | |||
| INV_AMT_IN_DISPUTE | NUMBER | This column value contains the sum of the amount in dispute on open payment schedules (installments) of all the invoices for the specific Account, Site and Currency. | |||
| DISPUTED_INV_COUNT | NUMBER | This column value contains the count of open payment schedules (installments) of all Invoices for the specific Account, Site and Currency that have a dispute amount. | |||
| PENDING_ADJ_VALUE | NUMBER | This column value contains the sum of the adjustment amounts on all debit items for the specific Account, Site and Currency that have not been approved, i.e., the adjustment status is not 'Approved' or 'Rejected'. | |||
| LAST_DUNNING_DATE | DATE | Not currently used. | |||
| DUNNING_COUNT | NUMBER | Not currently used. | |||
| PAST_DUE_INV_VALUE | NUMBER | This column value contains the sum of the amount_due_remaining of the open payments schedules (installments) of all the Invoices for the specific Account, Site and Currency, that are past their due date. | |||
| PAST_DUE_INV_INST_COUNT | NUMBER | This column value contains a count of the open payments schedules (installments) of all the Invoices for the specific Account, Site and Currency, that are past their due date. | |||
| LAST_PAYMENT_AMOUNT | NUMBER | Amount of the most recent cash receipt (based on receipt date) for the specific Account, Site, Currency and a date (identified by as_of_date column). | |||
| LAST_PAYMENT_DATE | DATE | Date of the most recent cash receipt (based on receipt date) for the specific Account, Site, Currency. | |||
| LAST_PAYMENT_NUMBER | VARCHAR2 | 30 | cash_receipt_id of the most recent cash receipt (based on receipt date) for the specific Account, Site, Currency. | ||
| 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_BAL_SUMMARY | hz_cust_site_uses_all | SITE_USE_ID |
| AR_TRX_BAL_SUMMARY | hz_cust_accounts | CUST_ACCOUNT_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| AR_TRX_BAL_SUMMARY_N1 | Non Unique | Default | SITE_USE_ID, CURRENCY |
| AR_TRX_BAL_SUMMARY_N2 | Non Unique | Default | REFERENCE_1 |
| AR_TRX_BAL_SUMMARY_U1 | Unique | Default | CUST_ACCOUNT_ID, SITE_USE_ID, CURRENCY, ORG_ID |