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 |