IEX_ESSBASE_BALANCES_GT

This table stores information about the receivable transactions, receipts and adjustments.This table is used to temporarily store the data and populate the same into essbase cube for calculation of various metrics.

Details

  • Schema: FUSION

  • Object owner: IEX

  • Object type: TABLE

  • Tablespace: Default

Primary Key

Name Columns

IEX_ESSBASE_BALANCES_GT_PK

IEX_ESSBASE_BALANCES_ID

Columns

Name Datatype Length Precision Not-null Comments
IEX_ESSBASE_BALANCES_ID NUMBER 18 Yes Unique identifier for this table to isolate multiple receipt or adjustment with same Payment Schedule id.
PAYMENT_SCHEDULE_ID NUMBER 18 Yes Payment schedule identifier. Foreign Key to AR_PAYMENT_SCHEDULES.PAYMENT_SCHEDULE_ID.
DOCUMENT_TYPE VARCHAR2 80 Yes The type of document which is populated from the lookup_type AR_DOCUMENT_TYPE. Maps to document type dimension in the cube.
CURRENCY VARCHAR2 15 Yes Functional currency which is derived from the ledger for the business unit the document is created in. Maps to currency dimension in the cube.
ORG_ID NUMBER 18 Yes Indicates the identifier of the business unit associated to the row.
CUST_ACCOUNT_ID NUMBER 18 Yes Foreign key to the HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID. Maps to account hierarchy of customer dimension in the cube.
SITE_USE_ID NUMBER 18 Yes Foreign key to the HZ_CUST_SITE_USES_ALL.SITE_USE_ID. Maps to site use hierarchy of customer dimension in the cube.
SALESREP_ID NUMBER 18 Foreign key to JTF_RS_SALESREPS.RESOURCE_SALESREP_ID. Maps to sales representative dimension in the cube.
ACTIVITY_DATE DATE Yes Date is based on the DOCUMENT_TYPE. For invoices, debit memos, credit memos and chargebacks this is the transaction date. For cash applications it is the application date. For cash unapplications it is the reversal date. For receipts it is the receipt date. For adjustments it is the adjustment date. For promise it is the initial load or creation or cancelation or reconciliation date. Maps to time dimension in the cube.
TRX_ACCOUNTING_DATE DATE Accounting date for invoices, debit memos, and chargebacks.
PS_DUE_DATE DATE Yes Date is based on the DOCUMENT_TYPE. For invoices, chargebacks, debit memos, and credit memos it is the due date from AR_PAYMENT_SCHEDULES. For receipts, this contains the due date of the payment_schedule _id to which receipt is applied, unapplied or reversed. For adjustments this column stores the due date of the of payment_schedule_id being adjusted. For promise this column keeps the promise date.
PS_CLOSED_DATE DATE This is used only for DOCUMENT_TYPE invoice. It is the GL_CLOSED_DATE from AR_PAYMENT_SCHEDULES for the invoice. Otherwise the column is null. Null for promise.
AMOUNT NUMBER Yes Remaining amount of invoice, debit memo, credit memo, chargeback, receipt, adjustment in functional currency. Deposits and Guarantees excluded. Promise amount for promise.
AMOUNT_GLOBAL NUMBER Yes Remaining amount of invoice, debit memo, credit memo, chargeback, receipt, adjustment in global currency. Deposits and Guarantees excluded. Promise amount in global currency for promise.
AGING_BUCKET_INDEX NUMBER Yes The aging bucket index is populated. 0 signifies current bucket, 30 as 1 to 30 bucket, 60 as 31 to 60 bucket, 90 as 61 to 90 bucket, 120 as 91 to 120 bucket, 150 as 121 to 150 bucket, 180 as 151 to 180 bucket, 181 as 181+ bucket. For promise, it always be 0.
PS_STATUS VARCHAR2 30 Yes Lookup code for the status of the payment schedule.
BROKEN_AMOUNT NUMBER Broken amount of a promise. Not null for DOCUMENT_TYPE promise.
BROKEN_AMOUNT_GLOBAL NUMBER Broken amount in global currency. Not null for DOCUMENT_TYPE promise.

Indexes

Index Uniqueness Columns
IEX_ESSBASE_BALANCES_GT_N1 Non Unique CUST_ACCOUNT_ID, SITE_USE_ID
IEX_ESSBASE_BALANCES_GT_N2 Non Unique DOCUMENT_TYPE, CURRENCY, ORG_ID, SITE_USE_ID, SALESREP_ID, ACTIVITY_DATE, AGING_BUCKET_INDEX
IEX_ESSBASE_BALANCES_GT_U1 Unique IEX_ESSBASE_BALANCES_ID