IEX_ESSBASE_BAL_TRANSFER
This table stores all the aging bucket amount information and pre-calculated Essbase metrices value for each payment schedule record. These information is required for Aging bucket balance transfer and WADL, WADP, ADL calculation.
Details
-
Schema: FUSION
-
Object owner: IEX
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
IEX_ESSBASE_BAL_TRANSFER_PK |
PAYMENT_SCHEDULE_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| PAYMENT_SCHEDULE_ID | NUMBER | 18 | Yes | Foreign Key to AR_PAYMENT_SCHEDULES.PAYMENT_SCHEDULE_ID. | |
| CURRENCY | VARCHAR2 | 15 | Yes | Functional currency which is dervied 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 or chargeback this is the accounting 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. Maps to time dimension in the cube. | ||
| 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. | ||
| PS_CLOSED_DATE | DATE | Yes | This will be used only for DOCUMENT_TYPE invoice. It will be the GL_CLOSED_DATE from AR_PAYMENT_SCHEDULES for the invoice. Otherwise the column is null. | ||
| WEIGHTED_DAYS_LATE | NUMBER | Product of the applied amount and applied days late. The applied days late is calculated as difference between the application accounting date of receipt, adjustment or credit memo and due date of the payment schedule to which it is applied. Used to calculate the Weighted Average Days Late for closed payment schedules. | |||
| WEIGHTED_DAYS_PAID | NUMBER | Product of applied amount and applied days paid. The applied days paid is calculated as difference between the application accounting date of receipt, adjustment or credit memo and accounting date of the payment schedule to which it is applied. Used to calculate the Weighted Average Days Paid for closed payment schedules. | |||
| TOTAL_ACTIVITY_COUNT | NUMBER | Total number of adjustment or receipt application activities associated with the payment schedule. | |||
| TARGET_AGING_BUCKET_INDEX | NUMBER | Yes | The target bucket index of the payment schedule in current aging balance transfer program. This would be the start bucket index of the payment schedule in the consecutive execution of this program. | ||
| AMOUNT_CHANGE_START_BUCKET | NUMBER | Yes | The starting aging bucket index from where the amount of the payment schedule is impacted by adjustment or receipt application. | ||
| BUCKET_ACCT_CURRENT | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in current bucket. | ||
| BUCKET_ACCT_30DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 1-30 days bucket. | ||
| BUCKET_ACCT_60DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 31-60 days bucket. | ||
| BUCKET_ACCT_90DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 61-90 days bucket. | ||
| BUCKET_ACCT_120DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 91-120 days bucket. | ||
| BUCKET_ACCT_150DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 121-150 days bucket. | ||
| BUCKET_ACCT_180DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 151-180 days bucket. | ||
| BUCKET_ACCT_181DAYS | NUMBER | Yes | Amount due remaining in functional currency for payment schedule in 181+ days bucket. | ||
| BUCKET_GLOBAL_CURRENT | NUMBER | Yes | Amount due remaining in global currency for payment schedule in current bucket. | ||
| BUCKET_GLOBAL_30DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 1-30 days bucket. | ||
| BUCKET_GLOBAL_60DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 31-60 days bucket. | ||
| BUCKET_GLOBAL_90DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 61-90 days bucket. | ||
| BUCKET_GLOBAL_120DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 91-120 days bucket. | ||
| BUCKET_GLOBAL_150DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 121-150 days bucket. | ||
| BUCKET_GLOBAL_180DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 151-180 days bucket. | ||
| BUCKET_GLOBAL_181DAYS | NUMBER | Yes | Amount due remaining in global currency for payment schedule in 181+ days bucket. | ||
| PS_STATUS | VARCHAR2 | 30 | Yes | Lookup code for the status of the payment schedule. | |
| BATCH_ID | NUMBER | 18 | Batch indentifier to help data extraction program to filter the records for aging balance transfer calculation. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| JOB_DEFINITION_NAME | VARCHAR2 | 100 | Enterprise Service Scheduler: indicates the name of the job that created or last updated the row. | ||
| JOB_DEFINITION_PACKAGE | VARCHAR2 | 900 | Enterprise Service Scheduler: indicates the package name of the job that created or last updated the row. |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| IEX_ESSBASE_BAL_TRANSFER_N1 | Non Unique | FUSION_TS_TX_DATA | PS_STATUS, BATCH_ID, PS_DUE_DATE, TARGET_AGING_BUCKET_INDEX |
| IEX_ESSBASE_BAL_TRANSFER_N2 | Non Unique | FUSION_TS_TX_DATA | CUST_ACCOUNT_ID, SITE_USE_ID, SALESREP_ID, CURRENCY, ACTIVITY_DATE, PS_DUE_DATE |
| IEX_ESSBASE_BAL_TRANSFER_N3 | Non Unique | FUSION_TS_TX_DATA | BATCH_ID |
| IEX_ESSBASE_BAL_TRANSFER_U1 | Unique | Default | PAYMENT_SCHEDULE_ID |