Fact Tables
This section lists the fact tables.
Billed Usage
Stores the information of the financial transactions and the usage associated with corresponding bills and bill segments.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_BILLED_USAGE |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_FT |
Stage Table Name | STG_CF_BILLED_USAGE |
ODI Package Name | B1_PKG_CF_BILLED_USAGE |
ETL View Name | B1_F_BILLED_USAGE_VW |
Materialized View Name | B1_BILLEDUSAGE_MON_MV1 B1_BILLEDUSAGE_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation |
|---|---|---|---|
BILLED_USAGE_KEY | Billed Usage Key | This field is populated with the sequence from SPL_BILLED_USAGE_SEQ. | |
SRC_BILL_ID | Bill ID | CI_FT.PARENT_ID | |
SRC_BSEG_ID | Bill Segment ID | CI_FT.SIBLING_ID | |
SRC_FT_ID | Financial Transaction ID | CI_FT.FT_ID | |
SRC_SQI_CD | Service Quantity Identifier Code | ||
SRC_TOU_CD | Time of Use Code | ||
SRC_UOM_CD | Unit of Measure Code | ||
SRC_RATE_SCHED_ CD | Rate Schedule Code | This field is populated with the rate code from bill segment calculation header. Every primary non-null rate schedule code on the bill segment calculation header leads to a new row in this fact. | |
BILLED_QTY | Billed Quantity | CI_BSEG_SQ.BILL_SQ CI_BSEG_READ. FINAL_REG_QTY | If there is a single primary rate on bill segment calculation headers, use Bill SQ value from Bill Segment Service Quantity. Else, for each rate, for each usage period, sum up the Final Registered Quantity from bill segment read for the UOM/TOU/SQI combination. |
CALC_AMT | Calculated Amount | CI_BSEG_CALC_LN. CALC_AMT CI_BSEG_CALC. CALC_AMT | If single primary rate, sum up the calculation amount from bill segment calculation lines. Else, for each rate, calculate the sum of calculation amounts for the UOM/TOU/SQI of the calculation header's usage period. Note: The amounts are grouped on the UOM/TOU/SQI combination. |
CURRENCY_CD | Currency Code | CI_FT.CURRENCY_ CD | |
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
INIT_QTY | Initial Quantity | CI_BSEG_SQ.INIT_SQ CI_BSEG_READ.MSR_QTY | If there is a single primary rate single on bill segment calculation headers, use initial SQ value from bill segment service quantity. Else, for each rate, for each usage period, sum up the Measured Quantity from bill segment read for the UOM/TOU/SQI combination. |
SEG_DAYS | Bill Segment Days | This field is populated with the difference between the end date and the start date of the respective bill segment. | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_FT.SA_ID | This field is populated with the service agreement on the financial transaction. |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | This field is populated with the account on the service agreement for which the bill segment was generated. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the respective account. |
ADDR_KEY | Address Dimension Surrogate Key | This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used. | |
BILL_DATE_KEY | Date Dimension Surrogate Key | CI_BILL.CRE_DTTM | This field is populated with the creation date of the bill associated with the bill segment. |
BSEG_STRT_DATE_KEY | Date Dimension Surrogate Key | CI_BSEG.START_DT | This field is populated with the start date of the respective bill segment. |
BSEG_END_DATE_KEY | Date Dimension Surrogate Key | CI_BSEG.END_DT | This field is populated with the end date of the respective bill segment. |
FISCAL_CAL_KEY | Fiscal Period Dimension Surrogate Key | CI_GL_DIVISION.CALANDER_CD | This field is populated with the calendar code from GL_DIVISION based on the input accounting date. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used. |
RATE_KEY | Rate Dimension Surrogate Key | CI_BSEG_CALC.RS_ CD | This field is populated with rate code from the bill segment calculation header. Every primary non-null rate schedule code on the bill segment calculation header leads to a new row in this fact. |
SQI_KEY | Service Quantity Identifier Dimension Surrogate Key | CI_BSEG_SQ.SQI_CD | This field is populated with the SQI code associated with bill segment service quantity. |
TOU_KEY | Time of Use Dimension Surrogate Key | CI_BSEG_SQ.TOU_CD | This field is populated with the TOU code associated with bill segment service quantity. |
UOM_KEY | Unit of Measure Dimension Surrogate Key | CI_BSEG_SQ.UOM_ CD | This field is populated with the UOM code associated with bill segment service quantity. |
BUSG_UDD1_KEY | Bill Segment Usage User Defined Dimension 1 Surrogate Key | ||
BUSG_UDD2_KEY | Bill Segment Usage User Defined Dimension 2 Surrogate Key | ||
UDM1 | User Defined Measure 1 | CI_FT.FREEZE_DTTM CI_BSEG.END_DT | This field is populated with the bill lag. Bill lag is calculated as the difference between the bill segment freeze (financial transaction freeze date) and the meter read date (bill segment end date). |
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. | |
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
Case
Stores all cases defined in the source system, and also their duration.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CASE |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_CASE |
Stage Table Name | STG_CF_CASE |
ODI Package Name | B1_PKG_CF_CASE |
ETL View Name | B1_F_CASE_VW |
Materialized View Name | B1_CASE_MON_MV1 B1_CASE_MON_MV2 B1_CASE_TOPX_MON_MV1 B1_CASE_TOPX_MON_MV2 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
ACCT_KEY | Account Dimension Surrogate Key | CI_CASE.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_CASE.PREM_ID | |
CASETY_STAT_KEY | Case Type Status Dimension Surrogate Key | CI_CASE.CASE_TYPE_CD CI_CASE.CASE_ STATUS_CD | |
CASE_COND_KEY | Case Condition Dimension Surrogate Key | CI_CASE.CASE_COND_FLG | |
CASE_LEN | Duration | CI_CASE.CASE_COND_FLG | This field is populated with '0' if the case is open. Else, it is populated, in hours, with the difference between the closed date and the open date. |
CASE_UDD1_KEY | Case User Defined Dimension 1 Surrogate Key | ||
CASE_UDD2_KEY | Case User Defined Dimension 2 Surrogate Key | ||
CASE_UDD3_KEY | Case User Defined Dimension 3 Surrogate Key | ||
CASE_UDD4_KEY | Case User Defined Dimension 4 Surrogate Key | ||
CASE_UDD5_KEY | Case User Defined Dimension 5 Surrogate Key | ||
OPEN_DATE_KEY | Date Dimension Surrogate Key | CI_CASE_LOG.LOG_DTTM | This field is populated with the date of the open log for the respective case. |
CLOSE_DATE_KEY | Date Dimension Surrogate Key | CI_CASE_LOG.LOG_DTTM | This field is populated only when the case is closed. It is populated with the maximum log date. |
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
PER_KEY | Person Dimension Surrogate Key | CI_CASE.PER_ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_CASE.PREM_ID | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
USER_KEY | User Dimension Surrogate Key | CI_CASE.USER_ID | |
CASE_KEY | Case Fact Key | This field is populated with the sequence from B1_CASE_SEQ. | |
SRC_CASE_ID | Case ID (Natural Key) | CI_CASE.CASE_ID | |
CURRENCY_CD | Currency Code | CI_INSTALLATION.CURRENCY_CD | |
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGENL11 | User Defined Long Degenerate Dimension 11 | ||
UDDGENL12 | User Defined Long Degenerate Dimension 12 | ||
UDDGENL13 | User Defined Long Degenerate Dimension 13 | ||
UDDGENL14 | User Defined Long Degenerate Dimension 14 | ||
UDDGENL15 | User Defined Long Degenerate Dimension 15 | ||
UDDGENL16 | User Defined Long Degenerate Dimension 16 | ||
UDDGENL17 | User Defined Long Degenerate Dimension 17 | ||
UDDGENL18 | User Defined Long Degenerate Dimension 18 | ||
UDDGENL19 | User Defined Long Degenerate Dimension 19 | ||
UDDGENL20 | User Defined Long Degenerate Dimension 20 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
OPEN_TIME_KEY | Time Dimension Surrogate Key | CI_CASE_LOG.LOG_DTTM | This field is populated with the time of the open log for the respective case. |
CLOSE_TIME_KEY | Time Dimension Surrogate Key | CI_CASE_LOG.LOG_DTTM | This field is populated with 'null' if the case is in open condition. If the case is closed, then it is populated with the maximum log time. |
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date 1 | ||
UD_DATE2 | User Defined Date 2 | ||
UD_DATE3 | User Defined Date 3 | ||
UD_DATE4 | User Defined Date 4 | ||
UD_DATE5 | User Defined Date 5 | ||
UD_DATE6 | User Defined Date 6 | ||
UD_DATE7 | User Defined Date 7 | ||
UD_DATE8 | User Defined Date 8 | ||
UD_DATE9 | User Defined Date 9 | ||
UD_DATE10 | User Defined Date 10 | ||
UD_DATE1_KEY | User Defined Date Key 1 | ||
UD_DATE2_KEY | User Defined Date Key 2 | ||
UD_DATE3_KEY | User Defined Date Key 3 | ||
UD_DATE4_KEY | User Defined Date Key 4 | ||
UD_DATE5_KEY | User Defined Date Key 5 | ||
UD_DATE6_KEY | User Defined Date Key 6 | ||
UD_DATE7_KEY | User Defined Date Key 7 | ||
UD_DATE8_KEY | User Defined Date Key 8 | ||
UD_DATE9_KEY | User Defined Date Key 9 | ||
UD_DATE10_KEY | User Defined Date Key 10 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Case Log
Stores the case state transition changes in the source system. This fact captures information to indicate if the case is in initial or final state, the time the case was open, and the time the case spent in the previous state.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CASE_LOG |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_CASE_LOG |
Stage Table Name | STG_CF_CASE_LOG |
ODI Package Name | B1_PKG_CF_CASE_LOG |
ETL View Name | B1_F_CASE_LOG_VW |
Materialized View Name | B1_CASELOG_MON_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
CASELOG_KEY | Case Log Fact Key | This field is populated with the sequence from B1_CASE_LOG_SEQ. | |
CASE_LOG_SEQ | Case Log Sequence | CI_CASE_LOG.SEQ_NUM | |
SRC_CASE_ID | Case ID (Natural Key) | CI_CASE_LOG.CASE_ID | |
CURRENCY_CD | Currency Code | CI_INSTALLATION.CURRENCY_CD | |
INITIAL_STATUS_ IND | Initial Status Indicator | CI_CASE_LOG.CASE_LOG_TYPE_FLG | This field is populated with '1' when the Log Type is “Created”. It is populated with '0' when the Log Type is “Status Transition”. Otherwise, it is populated with ‘0’. |
FINAL_STATUS_IND | Final Status Indicator | CI_CASE_LOG.CASE_LOG_TYPE_FLG CI_CASE.CASE_COND_FLG | This field is populated with ‘1’ when the Log Type is “Status Transition” and Case Condition Flag is “Closed”. |
TIME_CASE_OPEN | Case Open Time | CI_CASE_LOG.LOG_DTTM | This field is populated with ‘0’ if the log type is “Created”. If the log type is “Status Transition”, it is populated, in hours, with difference between the current log date and the open log date. |
TIME_IN_PREV_ST | Time in Previous Status | CI_CASE_LOG.LOG_DTTM | This field is populated with ‘0’ if the log type is “Created”. If the log type is “Status Transition”, it is populated, in hours, with the difference between the current log date and the previous “Status Transition” log date. |
ACCT_KEY | Account Dimension Surrogate Key | CI_CASE.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_CASE.PREM_ID | |
CASETY_STAT_KEY | Case Log Type Status Dimension Surrogate Key | CI_CASE_LOG.CASE_TYPE_CD | |
LOG_DATE_KEY | Date Dimension Surrogate key | CI_CASE_LOG.LOG_DTTM | This field is populated using the date portion from the source. |
LOG_TIME_KEY | Time Dimension Surrogate Key | CI_CASE_LOG.LOG_DTTM | This field is populated using the time portion from the source. |
OPEN_DATE_KEY | Date Dimension Surrogate key | CI_CASE_LOG.LOG_DTTM | This field is populated using the date portion of the “Created” log entry for the current case. |
OPEN_TIME_KEY | Time Dimension Surrogate Key | CI_CASE_LOG.LOG_DTTM | This field is populated using the time portion of the “Created” log entry for the current case. |
PER_KEY | Person Dimension Surrogate Key | CI_CASE.PER_ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_CASE.PREM_ID | |
PV_CASETY_STAT_ KEY | Case Log Type Status Dimension Surrogate Key | CI_CASE_LOG.CASE_STATUS_CD | This field is populated only when the log type is “Status Transition”. It is populated with the case status code from the previous “Status Transition” log. |
USER_KEY | User Dimension Surrogate Key | CI_CASE_LOG.USER_ID | |
CASELOG_UDD1_ KEY | Case Log User Defined Dimension 1 Surrogate Key | ||
CASELOG_UDD2_ KEY | Case Log User Defined Dimension 2 Surrogate Key | ||
CASELOG_UDD3_ KEY | Case Log User Defined Dimension 3 Surrogate Key | ||
CASELOG_UDD4_ KEY | Case Log User Defined Dimension 4 Surrogate Key | ||
CASELOG_UDD5_ KEY | Case Log User Defined Dimension 5 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with ODI job execution session number. |
Note: Only the case logs with type as “Created” or “Status Transition” are retrieved.
Collectible Event
Stores the collection events, cut event, over due event, and severity event information.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_COLL_EVT |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_COLL_EVT CI_SEV_EVT CI_OD_EVT CI_CUT_EVT |
Stage Table Name | STG_CF_COLL_EVT |
ODI Package Name | B1_PKG_CF_COLL_EVT |
ETL View Name | B1_F_COLL_EVT_VW |
Materialized View Name |
Fields
Source 1 - Collectible Event (CI_COLL_EVT)
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
COLL_EVT_KEY | Collectible Event Fact Surrogate Key | This field is populated with the sequence from B1_COLL_EVT_SEQ. | |
COLL_PROC_ID | Collection Process ID | CI_COLL_EVT.COLL_PROC_ID | All completed collection events (Status 30) are to be retrieved for this fact. |
COLL_EVT_SEQ | Collection Event Sequence | CI_COLL_EVT.EVT_ SEQ | |
COLL_EVT_SRC_IND | Collectible Event Source Indicator | This indicator is set to ‘0’. | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | This field is populated with the account’s currency code. |
ACCT_KEY | Account Dimension Surrogate Key | CI_COLL_PROC.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with the mailing premise ID of the account. If not found, the characteristic premise on the service agreement is used. |
CEVT_TYPE_KEY | Collectible Event Type Dimension Key | CI_COLL_EVT.COLL_EVT_TYP_CD | Prefix the Collection Event Type with ‘C_’ and store it in the column. |
COLL_TMPL_KEY | Collectible Process Template Dimension Key | CI_COLL_PROC.COLL_PROC_TMPL_CD | Prefix the Collection Process Template Code with ‘C_’. |
EVENT_DATE_KEY | Date Dimension Surrogate Key | CI_COLL_EVT.COMPLETION_DT | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with the mailing premise ID of the account. If not found, the characteristic premise on the service agreement is used. |
COLLEVT_UDD1_ KEY | Collectible Event User Defined Dimension 1 Surrogate Key | ||
COLLEVT_UDD2_ KEY | Collectible Event User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Source 2 - Severance Event (CI_SEV_EVT)
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
COLL_EVT_KEY | Collectible Event Fact Surrogate Key | This field is populated with the sequence from B1_COLL_EVT_SEQ. | |
COLL_PROC_ID | Collection Process ID | CI_SEV_EVT.SEV_PROC_ID | All completed severance events (Status 30) need to be retrieved for this fact |
COLL_EVT_SEQ | Collection Event Sequence | CI_SEV_EVT.EVT_ SEQ | |
COLL_EVT_SRC_IND | Collectible Event Source Indicator | This indicator is set to ‘1’. | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | This field is populated with the account’s currency code. |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | This field is populated with the account on the service agreement of the severence process. |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used. |
CEVT_TYPE_KEY | Collectible Event Type Dimension Key | CI_SEV_EVT.SEV_ EVT_TYPE_CD | Prefix the severance event type code with ‘S_’ and use. |
COLL_TMPL_KEY | Collectible Process Template Dimension Key | CI_COLL_PROC.COLL_PROC_TMPL_CD | Use the collection template corresponding to severance processes’ collection process ID and prefix it with ‘C_’. |
EVENT_DATE_KEY | Date Dimension Surrogate Key | CI_SEV_EVT.COMPLETION_DT | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ID CI_ACCT.MAILING_PREM_ID | This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise ID on the account is used. |
COLLEVT_UDD1_ KEY | Collectible Event User Defined Dimension 1 Surrogate Key | ||
COLLEVT_UDD2_ KEY | Collectible Event User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Source 3 - Overdue Event (CI_OD_EVT)
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
COLL_EVT_KEY | Collectible Event Fact Surrogate Key | This field is populated with the sequence from B1_COLL_EVT_SEQ. | |
COLL_PROC_ID | Collection Process ID | CI_OD_EVT.OD_ PROC_ID | Completed overdue events (Status 30) are retrieved for this fact. |
COLL_EVT_SEQ | Collection Event Sequence | CI_OD_EVT.EVT_ SEQ | |
COLL_EVT_SRC_IND | Collectible Event Source Indicator | This indicator is set to ‘2’. | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | This field is populated with the account’s currency code. |
ACCT_KEY | Account Dimension Surrogate Key | CI_OD_PROC.ACCT_ ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with the mailing premise ID of the account. If not found, use the characteristic premise from the service agreement. |
CEVT_TYPE_KEY | Collectible Event Type Dimension Key | CI_OD_EVT.OD_EVT_TYPE_CD | Prefix the overdue event type code with ‘OD_’ and use it. |
COLL_TMPL_KEY | Collectible Process Template Dimension Key | CI_OD_PROC.OD_ PROC_TMP_CD | This field is populated with Collection Process Template Code prefixed with ‘OD_’. |
EVENT_DATE_KEY | Date Dimension Surrogate Key | ||
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with the mailing premise ID of the account. If not found, use the characteristic premise from the service agreement. |
COLLEVT_UDD1_ KEY | Collectible Event User Defined Dimension 1 Surrogate Key | ||
COLLEVT_UDD2_ KEY | Collectible Event User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Source 4 - Cut Event (CI_CUT_EVT)
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
COLL_EVT_KEY | Collectible Event Fact Surrogate Key | This field is populated with the sequence from B1_COLL_EVT_SEQ. | |
COLL_PROC_ID | Collection Process ID | CI_CUT_EVT.CUT_ PROC_ID | All completed cut events are retrieved for this fact. |
COLL_EVT_SEQ | Collection Event Sequence | CI_CUT_EVT.EVT_ SEQ | |
COLL_EVT_SRC_IND | Collectible Event Source Indicator | This indicator is set to ‘3’. | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | This field is populated with the account’s currency code. |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | This field is based on the service agreement ID. It is populated with the respective account ID. |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise mailing ID on the account is used. |
CEVT_TYPE_KEY | Collectible Event Type Dimension Key | CI_CUT_EVT.CUT_ EVT_TYPE_CD | Retrieve the cut event type code and prefix with ‘CUT_’ and use it. |
COLL_TMPL_KEY | Collectible Process Template Dimension Key | CI_OD_PROC.OD_ PROC_TMP_CD | Use the process template corresponding to cut processes’ overdue process ID and prefix with ‘OD_’. |
EVENT_DATE_KEY | Date Dimension Surrogate Key | CI_CUT_EVT.CUT_EVT_STAT_DTTM | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the characteristic premise on the service agreement. If not found, the mailing premise mailing ID on the account is used. |
COLLEVT_UDD1_ KEY | Collectible Event User Defined Dimension 1 Surrogate Key | ||
COLLEVT_UDD2_ KEY | Collectible Event User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Collectible Process
Stores the collection process as well as overdue process information from the source system.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_COLL_PROC |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_COLL_PROC CI_OD_PROC CI_SEV_PROC CI_CUT_PROC |
Stage Table Name | STG_CF_COLL_PROC |
ODI Package Name | B1_PKG_CF_COLL_PROC |
ETL View Name | B1_F_COLL_PROC_VW B1_F_OD_PROC_VW |
Materialized View Name | B1_COLLPROC_MON_MV1 B1_COLLPROC_MON_MV2 B1_COLLPROC_MON_TOPX_MV1 B1_COLLPROC_MON_TOPX_MV2 |
Fields
Source 1 - Collection Process (CI_COLL_PROC)
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
COLLPROC_KEY | Collectible Process Fact Surrogate Key | This field is populated with the sequence from SPL_COLL_PROC_SEQ. | |
SRC_COLLPROC_ID | Collection Process ID | CI_COLL_PROC.COLL_PROC_ID | |
ARRS_AT_START | Arrears at Start | CI_FT.TOT_AMT | This field is populated with the balance of all collection service agreements that are linked to the collection process, by using the collection arrears date and creation date of collection process as debit and credit dates. |
ARRS_AT_END | Arrears at End | CI_FT.CUR_AMT | This is populated with a zero if collection process is active. Else, is populated with the balance of all active service agreements associated with collection process using Arrears Date and End Date of collection process as debit and credit dates. |
ARRS_DIFF | Arrears at End - Arrears at Start | This field is populated with the difference between the arrears at start and arrears at end. | |
COLLPROC_DURATION | Collection Process Duration | This field is populated with the difference, in hours, between the end date and start date of the collection process. | |
COLL_PROC_SRC | Collection Process Source | This field is populated with ‘0’. | |
CURRENCY_ID | Currency Code | CI_COLL_PROC.CURRENCY_CD | |
ACCT_KEY | Account Dimension Surrogate Key | CI_COLL_PROC.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with mailing premise ID of the account. If not found, the characteristic premise ID of the service agreement is used. |
COLLPROC_STAT_ KEY | Collectible Process Status Dimension Key | CI_COLL_PROC.COLL_STATUS_FLG | 1. Set status as ‘Active’ when collection process is active or if collection process is completed and has a pending severance process. 2. ‘Effective’ when collection process has been canceled or completed, but all associated severance process are canceled. 3. ‘Ineffective’ if collection process is completed and atleast one severance process is completed or no severance processes are found. |
COLL_TMPL_KEY | Collectible Process Template Dimension Key | CI_COLL_PROC.COLL_PROC_TMPL_CD | This field is populated with Collection Process Template Code prefixed with ‘C_’. |
CEVT_TYPE_KEY | Collectible Event Type Dimension surrogate Key | CI_COLL_EVT.COLL_EVT_TYP_CD | This field is populated only if the collection process is in ‘Effective’ status. Retrieve the event type of the last completed collection event that was successful in collecting the payment. If multiple events are completed at same time, use the one with highest sequence. Prefix with ‘C_’ and use it. |
START_DATE_KEY | Date Dimension Surrogate Key | CI_COLL_PROC.CRE_DTTM | |
END_DATE_KEY | Date Dimension Surrogate Key | CI_SEV_EVT.COMPLETION_DT CI_COLL_EVT.COMPLETION_DT CI_SEV_PROC.CRE_ DTTM | If the collection process status is ‘Active’, no end date would exist. Else, use the maximum severance event completion date. If it is not found, use the collection event completion date. If it is not found, use the creation date of the collection process. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | Set person ID = account’s main person ID |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with mailing premise ID of the account. If it is not found, the characteristic premise ID of the service agreement is used. |
COLLPROC_UDD1_KEY | Collectible Process User Defined Dimension 1 Surrogate Key | ||
COLLPROC_UDD2_KEY | Collectible Process User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Source 2 - Overdue Process (CI_OD_PROC)
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
COLLPROC_KEY | Collectible Process Fact Surrogate Key | This field is populated with the sequence from SPL_COLL_PROC_ SEQ. | |
SRC_COLLPROC_ID | Collection Process ID | CI_OD_PROC. OD_PROC_ID | |
ARRS_AT_START | Arrears at Start | CI_FT.CUR_AMT | Retrieve the overdue bill associated with the overdue process using the Overdue Bill Characteristic Type defined on source and fetch the open item bill amounts. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
ARRS_AT_END | Arrears at End | This field is populated with the unpaid bill amount only when the status is not active. Else, it would be zero. | |
ARRS_DIFF | Arrears at End - Arrears at Start | This field is populated with the difference between the arrears at start and arrears at end. | |
COLLPROC_DURATION | Collection Process Duration | This field is populated with the difference, in hours, between the end date and start date of the overdue process. | |
COLL_PROC_SRC | Collection Process Source | This field is populated with ‘2’. | |
CURRENCY_ID | Currency Code | CI_OD_PROC. CURRENCY_CD | |
ACCT_KEY | Account Dimension Surrogate Key | CI_OD_PROC.ACCT_ ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | This field is populated with the mailing premise ID on the account. If it is not found, characteristic premise on the service agreement is used. |
COLLPROC_STAT_ KEY | Collectible Process Status Dimension Key | 1. Set status as ‘Active’ when the overdue process status is ‘Active’ or the process is complete and at least one cut process is active. 2. Set status as ‘Effective’ if the overdue process has been canceled or is complete, and all associated cut processes are canceled. 3. Set status as ‘Ineffective’ if overdue process is complete, and either no cut processes are spawned or at least one of the cut process is complete. | |
COLL_TMPL_KEY | Collectible Process Template Dimension Key | CI_OD_PROC.OD_PROC_TMP_CD | Prefix the overdue process template with ‘OD_’ and use it. |
CEVT_TYPE_KEY | Collectible Event Type Dimension surrogate Key | CI_OD_EVT.OD_EVT_TYP_CD | If the overdue process status is ‘Effective’, use the overdue event type of last completed overdue event. If multiple events have same completion date, pick the one with highest sequence and prefix it with 'OD_'. |
START_DATE_KEY | Date Dimension Surrogate Key | CI_OD_PROC.CRE_DTTM | |
END_DATE_KEY | Date Dimension Surrogate Key | CI_CUT_EVT.CUT_EVT_STAT_DTTM CI_OD_EVT.OD_EVT_STAT_DTTM CI_OD_PROC.CRE_DTTM | 1. If overdue process status flag is ‘Active’, the end date is null. 2. Else, use maximum cut event completion date. If it is not found, use the maximum overdue event collection date. 3. If it is still not found, use the creation date of overdue process. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ID | Set person ID = account’s main person ID |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_PREM_ID CI_SA.CHAR_PREM_ID | This field is populated with the mailing premise ID on the account. If not found, the characteristic premise on the service agreement is used. |
COLLPROC_UDD1_ KEY | Collectible Process User Defined Dimension 1 Surrogate Key | ||
COLLPROC_UDD2_ KEY | Collectible Process User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of ‘1’. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDFK1_KEY | User Defined Dimension Surrogate Key 1 | ||
UDDFK2_KEY | User Defined Dimension Surrogate Key 2 | ||
UDDFK3_KEY | User Defined Dimension Surrogate Key 3 | ||
UDDFK4_KEY | User Defined Dimension Surrogate Key 4 | ||
UDDFK5_KEY | User Defined Dimension Surrogate Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION. ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Customer Contact
Stores the details of all customer contacts and their attributes.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_CC |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_CC |
Stage Table Name | STG_CF_CC |
ODI Package Name | B1_PKG_CF_CC |
ETL View Name | B1_F_CC_VW |
Materialized View Name | B1_CC_HOU_MV1 B1_CC_MON_MV1 B1_CC_TOPX_MON_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
CC_KEY | Customer Contact Fact Key | This field is populated with the sequence from B1_CC_SEQ. | |
SRC_CC_ID | Customer Contact ID (Natural Key) | CI_CC.CC_ID | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | |
CC_DATE_KEY | Customer Contact Date (Date Dimension Surrogate Key) | CI_CC.CC_DTTM | This field extracts only the date from CI_CC.CC_DTTM. |
CC_TIME_KEY | Customer Contact Time (Time Dimension Surrogate Key) | CI_CC.CC_DTTM | This field extracts only the time from CI_CC.CC_DTTM. |
ACCT_KEY | Account Dimension Surrogate Key | CI_ACCT_PER.ACCT_ ID | The account is fetched using the person ID. 1. If one account is linked to the person, return this account. 2. If there are more than one accounts linked to the person: a. Select the account linked to the non-closed and non-canceled service agreement with the latest start date where the person is linked as main person. If there is more than one account, return the first account. b. If there are no non-canceled and non-closed service agreements linked to all of the accounts, return the first account. |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID | Retrieve the first characteristic premise of the input account’s non-canceled and non-closed service agreement with the latest start date. |
PER_KEY | Person Dimension Surrogate Key | CI_CC.PER_ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID | Retrieve the first characteristic premise of the input account’s non-canceled and non-closed service agreement with the latest start date. |
USER_KEY | User Dimension Surrogate Key | CI_CC.USER_ID | |
CC_TYPE_KEY | Customer Contact Dimension Surrogate key | CI_CC.CC_TYPE_CD | |
CC_UDD1_KEY | Customer Contact User Defined Dimension 1 Surrogate Key | ||
CC_UDD2_KEY | Customer Contact User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of 1. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION. ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Financial
Stores all frozen financial transactions defined in the source system.
ETL has the capability of excluding up to three adjustment types. These adjustment types need to be configured as part of the BI configuration in the source system.
For more details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_FT |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_FT |
Stage Table Name | STG_CF_FT |
ODI Package Name | B1_PKG_CF_FT |
ETL View Name | B1_F_FT_VW |
Materialized View Name | B1_FT_MON_MV1 B1_FT_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ID CI_ACCT.MAILING_PREM_ID | Use the characteristic premise ID from the service agreement. If it is not found, use the mailing premise ID from the account. |
ADJ_TYPE_KEY | Adjustment type Dimension Surrogate Key | CI_FT.PARENT_ID | This field is populated when the financial transaction corresponds to adjustment/adjustment cancellation using the Parent ID column. |
ARREARS_DT_KEY | Arrears Date (Date Dimension Surrogate Key) | CI_FT.ARS_DT | |
CURRENCY_CD | Currency Code | CI_FT.CURRENCY_ CD | |
CURR_AMT | Current Amount | CI_FT.CUR_AMT | |
FACT_CNT | Count | This field is populated with the standard value of 1. | |
FISCAL_CAL_KEY | Fiscal Period Dimension Surrogate Key | CI_GL_DIVISION. CALENDER_ID | This field is populated using the calendar from GL division based on the accounting date. |
FREEZE_DT_KEY | Freeze Date | CI_FT.FREEZE_DTTM | |
FT_KEY | Financial Fact Key | ||
FT_TYPE_KEY | Financial Transaction Type Dimension Surrogate Key | CI_FT.FT_TYPE_FLG | |
FT_UDD1_KEY | Financial User Defined Dimension 1 Surrogate Key | ||
FT_UDD2_KEY | Financial User Defined Dimension 2 Surrogate Key | ||
OTHER_AMT | Other Amount | ||
PAYOFF_AMT | Payoff Amount | CI_FT.TOT_AMT | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | Use the characteristic premise ID from the service agreement. If it is not found, use the mailing premise ID from the account. |
RATE_KEY | Rate Dimension Surrogate Key | CI_BSEG_CALC.RS_ CD | This field is populated when a financial transaction is of type bill segment or bill segment cancellation. Rate Schedule Code from the bill segment calculation header is used. |
REVENUE_AMT | Revenue Amount | CI_FT_GL.AMOUNT | This field is populated with the absolute value of the total amount of all financial transaction GLs whose distribution code’s characteristic type/ revenue characteristic value match with the characteristic type/value configured as part of the BI configuration in the source system and effective on the specific date. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_FT.SA_ID | |
SIBLING_ID | Sibling ID (Natural Key) | CI_FT.SIBLING_ID | |
SRC_FT_ID | Financial Transaction ID (Natural Key) | CI_FT.FT_ID | |
TAX_AMT | Tax Amount | CI_FT_GL.AMOUNT | This field is populated with the absolute value of the total amount of all financial transaction GLs whose distribution code’s characteristic type/tax characteristic value match with the characteristic type/value configured as part of the BI configuration in the source system and effective on the specific date. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
USER_KEY | User Dimension Surrogate Key | CI_FT.FREEZE_USER_ID | |
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Financial General Ledger
Stores the financial transaction general ledger information, along with its attributes, defined in the source system.
ETL has the capability of excluding up to three adjustment types. These adjustment types need to be configured as part of the BI configuration in the source system.
For more details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_FT_GL |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_FT_PROC |
Stage Table Name | STG_CF_FT_GL |
ODI Package Name | B1_PKG_CF_FT_GL |
ETL View Name | B1_F_FT_GL_VW |
Materialized View Name |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
FT_GL_KEY | Financial General Ledger Fact Key | This field is populated with the sequence from B1_FT_GL_SEQ. | |
SRC_FT_ID | Financial Transaction ID (Natural Key) | CI_FT_GL.FT_ID | |
SRC_GL_SEQ_NBR | General Ledger Sequence Number (Natural Key) | CI_FT_GL.GL_SEQ_NBR | |
SIBLING_ID | Sibling ID | CI_FT.SIBLING_ID | |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | This field is populated with account information from the CI_SA table. |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the service agreement’s characteristic premise ID. If not found, it is populated with the account’s mailing premise ID. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the service agreement’s characteristic premise ID. If not found, it is populated with the account’s mailing premise ID. |
FREEZE_DT_KEY | Freeze Date (Date Dimension Surrogate Key) | CI_FT.FREEZE_DTTM | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_FT.SA_ID | |
USER_KEY | User Dimension Surrogate Key | CI_FT.FREEZE_USER_ID | |
ADJ_TYPE_KEY | Adjustment type Dimension Surrogate Key | CI_FT.PARENT_ID | This field is populated when a financial transaction corresponds to adjustment/adjustment cancellation using the Parent ID column. |
FT_TYPE_KEY | Financial Transaction Type Dimension Surrogate Key | CI_FT.FT_TYPE_FLG | |
RATE_KEY | Rate Dimension Surrogate Key | CI_BSEG_CALC.RS_ CD | This field is populated when a financial transaction is of type bill segment or bill segment cancellation. The Rate Schedule Code from bill segment calculation header is used. |
FISCAL_CAL_KEY | Fiscal Period Dimension Surrogate Key | CI_GL_DIVISION. CALENDER_ID | This field is populated using the calendar from GL division based on the accounting date. |
GL_ACCT_KEY | GL Account Dimension Surrogate Key | CI_FT_GL.GL_ACCT | |
FT_GL_UDD1_KEY | FT GL User Defined Dimensions 1 Surrogate Key | ||
FT_GL_UDD2_KEY | FT GL User Defined Dimensions 1 Surrogate Key | ||
FACT_CNT | Count | This field is populated with the standard value of “1”. | |
CURRENCY_CD | Currency Code | CI_FT.CURRENCY_ CD | |
DEBIT_AMT | Debit Amount | CI_FT_GL.AMOUNT | All positive AMOUNT values |
CREDIT_AMT | Credit Amount | CI_FT_GL.AMOUNT | All negative AMOUNT values |
GL_AMT | General Ledger Amount | CI_FT_GL.AMOUNT | |
STATISTIC_AMT | Statistic Amount | CI_FT_GL.STATISTIC_AMT | |
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
ARREARS_DT_KEY | Arrears Date (Date Dimension Surrogate Key) | CI_FT.ARS_DT | |
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Order
Stores all enrollment orders that are defined in the source system. This fact stores the duration for completed orders.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_ORDER |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_ENRL |
Stage Table Name | STG_CF_ORDER |
ODI Package Name | B1_PKG_CF_ORDER |
ETL View Name | B1_F_ORDER_VW |
Materialized View Name |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
ORDER_KEY | Order Fact Key | This field is populated with the sequence from B1_ORDER_SEQ. | |
SRC_ORDER_ID | Order ID (Natural Key) | CI_ENRL.ENRL_ID | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD B1_PROD_INSTANCE.CURRENCY_CD | If no account is associated with the Order, then the currency code available in the Product Instance metadata configuration will be populated. |
DURATION | Duration | CI_ENRL_LOG.ENRL_LOG_DTTM | This field is populated with the difference in hours between the timestamp in the Order Creation log entry and the Order Completion/Cancellation log entry. Note: This field is populated only if the order is complete or cancelled. |
ACCT_KEY | Account Dimension Surrogate Key | CI_ENRL.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ENRL.PREM_ID | |
CAMPAIGN_KEY | Campaign Dimension Surrogate Key | CI_ENRL.CAMPAIGN_CD | |
CREATE_DATE_KEY | Date Dimension Surrogate Key | CI_ENRL_LOG.ENRL_LOG_DTTM | This field is populated with the date in the Order Creation log entry. |
END_DATE_KEY | Date Dimension Surrogate Key | CI_ENRL_LOG.ENRL_LOG_DTTM | This field is populated with the date in the Order Completion or Cancellation log entry. |
ORDER_CAN_RSN_ KEY | Order Cancel Reason Dimension Surrogate Key | CI_ENRL.ENRL_CAN_RSN_CD | |
ORDER_STATUS_ KEY | Order Status Dimension Surrogate Key | CI_ENRL.ENRL_STATUS_FLG | |
PER_KEY | Person Dimension Surrogate Key | CI_ENRL.PER_ID | |
PKG_KEY | Package Dimension Surrogate Key | CI_ENRL.PACKAGE_ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_ENRL.PREM_ID | |
START_DATE_KEY | Date Dimension Surrogate Key | CI_ENRL.START_DT | |
FACT_CNT | Fact Count | This field populates the standard value of “1”. | |
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION.ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Pay Plan Accumulation
Stores all pay plans defined in the source system.
This fact is special in the sense that the ETL process setup to load this fact is configured as a daily refresh job. It is primarily to keep the measures updated to reflect on the current status.
The fact captures a variety of information about pay plans, such as the duration from the start, the number of days to go till the last payment, total payment amount, the amount paid so far, the number of future payments, etc.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_PAY_PLAN |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_PP |
Stage Table Name | STG_CF_PAY_PLAN |
ODI Package Name | B1_PKG_CF_PAY_PLAN |
ETL View Name | B1_F_PAY_PLAN_VW |
Materialized View Name | B1_PAY_PLAN_MON_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
PAY_PLAN_KEY | Pay Plan Accumulation Fact Key | This field is populated with the sequence from B1_PAY_PLAN_ACCUM_SEQ. | |
SRC_PAY_PLAN_ID | Source Pay Plan ID | CI_PP.PP_ID | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | |
TOT_SCHED_PAY_AMT | Total Scheduled Payment Amount | CI_PP_SCHED_PAY.PP_SCHED_AMT | This field is populated with the sum of all scheduled payments for the respective pay plan ID. |
DUR_FROM_START | Duration from Start | CI_PP.START_DT CI_PP.LAST_STAT_DTTM | • If pay plan is “Active”: This field is populated with the difference (in hours) between the start date and the current date. • If pay plan is “Canceled”, “Kept”, or “Broken”: This field is populated with the difference (in days) between the start date and the last status update date. |
DAYS_TO_GO | Days to Go | CI_PP_SCHED_PAY.PP_SCHED_DT | This field is populated with the difference (in days) between the last scheduled payment and the current date. Note: Populated for active pay plans only. |
PAID_TO_DATE_AMT | Paid to Date | CI_PP_SCHED_PAY.PP_SCHED_AMT | • If pay plan is “Active”. This field is populated with the sum of all past scheduled payments. • If pay plan is “Kept”. This field is populated with the sum of all scheduled payments. • If pay plan is “Canceled”. This field is populated with the sum of all scheduled payments before the end date. • If pay plan is “Broken”: This field is populated with the sum of all scheduled payments before the scheduled payment closest to the end date. |
NBR_TOT_SCHED_PAYMENTS | Total Scheduled Payments | CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT | This field is populated with the total number of scheduled payments. |
NBR_FUTURE_SCHED_PAYMENTS | Future Scheduled Payments | CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT | This field is populated with the number of payments scheduled in the future. Note: If the pay plan is “Broken”, this field includes the scheduled payment that lapsed and did not clear the payment. |
TOTAL_FUTURE_PAY_AMT | Future Payment Amount | CI_PP_SCHED_PAY.PP_SCHED_AMT | This field is populated with the sum of all payments scheduled in the future. If the pay plan is “Broken/Canceled”, this field is populated with zero. |
FUTURE_PAY_AMT_BUCKET1 | Future Payment Amount Bucket 1 | CI_PP_SCHED_PAY.PP_SCHED_AMT | This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for pay plans in the source system. For details, see PP Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
FUTURE_PAY_AMT_BUCKET2 | Future Payment Amount Bucket 2 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET3 | Future Payment Amount Bucket 3 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET4 | Future Payment Amount Bucket 4 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET5 | Future Payment Amount Bucket 5 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET6 | Future Payment Amount Bucket 6 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET7 | Future Payment Amount Bucket 7 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET8 | Future Payment Amount Bucket 8 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET9 | Future Payment Amount Bucket 9 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_BUCKET10 | Future Payment Amount Bucket 10 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
START_DTTM | Start Date/Time | CI_PP.START_DT | |
END_DTTM | End Date/Time | CI_PP.LAST_STAT_DTTM | This field is populated only when the pay plan status is “Canceled”, “Broken”, or “Kept”. |
START_DATE_KEY | Start Date (Date Dimension Surrogate) Key | CI_PP.START_DT | |
START_TIME_KEY | Start Time (Time Dimension Surrogate) Key | CI_PP.START_DT | |
END_DATE_KEY | End Date (Date Dimension Surrogate) Key | CI_PP.LAST_STAT_DTTM | This field is populated only when the pay plan status is “Canceled”, “Broken”, or “Kept”. |
END_TIME_KEY | End Time (Time Dimension Surrogate) Key | CI_PP.LAST_STAT_DTTM | This field is populated only when the pay plan status is “Canceled”, “Broken”, or “Kept”. |
PAY_PLAN_STATUS_KEY | Pay Plan Status Dimension Surrogate Key | CI_PP.PP_STAT_FLG | |
PAY_PLAN_TYPE_ KEY | Pay Plan Type Dimension Surrogate Key | CI_PP.PP_TYPE_CD | |
PAY_METHOD_KEY | Pay Method Dimension Surrogate Key | CI_PP.PAY_METH_CD | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | The main customer of the account associated with the pay plan is used to populate this field. |
ACCT_KEY | Account Dimension Surrogate Key | CI_PP.ACCT_ID | |
PAYOR_ACCT_KEY | Payor Account (Account Dimension Surrogate) Key | CI_PP.PAYOR_ACCT_ ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
PAY_PLAN_UDD1_ KEY | Pay Plan User Defined Dimension 1 Surrogate Key | ||
PAY_PLAN_UDD2_ KEY | Pay Plan User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Note: The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.
For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Pay Plan Snapshot
Provides a snapshot of the pay plans defined in the source system. Once a pay plan reaches its final state (Kept, Canceled, or Broken), it will no longer be included in the snapshots of the further periods.
This fact captures a variety of information about pay plans, such as the duration from the start, the number of days to go till the last payment, total payment amount, the amount paid so far, the number of future payments, etc.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_PAY_PLAN_SNAP |
Table Type | Fact |
Fact Type | Snapshot |
Driver Table | CI_PP |
Stage Table Name | STG_CF_PAY_PLAN_SNAP |
ODI Package Name | B1_PKG_CF_PAY_PLAN_SNAP |
ETL View Name | n/a |
Materialized View Name | B1_PAY_PLAN_SNAP_MON_MV1 B1_PAY_PLAN_SNP_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
PAY_PLAN_SNAP_ KEY | Pay Plan Snapshot Fact Key | This field is populated with the sequence from B1_PAY_PLAN_SNAP_SEQ. | |
SRC_PAY_PLAN_ID | Source Pay Plan ID | CI_PP.PP_ID | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | |
TOT_SCHED_PAY_ AMT | Total Scheduled Payment Amount | CI_PP_SCHED_PAY.PP_SCHED_AMT | This field is populated with the sum of all scheduled payments for that pay plan ID. |
DUR_FROM_START | Duration from Start | CI_PP.START_DT CI_PP.LAST_STAT_DTTM | • If pay plan is “Active”: This field is populated with the difference (in days) between the start date and the current date. • If pay plan is “Canceled”, “Kept”, or “Broken”: This field is populated with the difference (in days) between the start date and the last status update date. |
DAYS_TO_GO | Days to Go | CI_PP_SCHED_PAY.PP_SCHED_DT | This field is populated with the difference (in days) between the last scheduled payment and the snapshot end date. Note: Populated for active pay plans only. |
PAID_TO_DATE_AMT | Paid to Date | CI_PP_SCHED_PAY.PP_SCHED_AMT | • If pay plan is “Active”. This field is populated with the sum of all past scheduled payments. • If pay plan is “Kept”. This field is populated with the sum of all scheduled payments. • If pay plan is “Canceled”. This field is populated with the sum of all scheduled payments before the end date. • If pay plan is “Broken”: This field is populated with the sum of all scheduled payments before the scheduled payment closest to the end date. |
NBR_TOT_SCHED_ PAYMENTS | Total Scheduled Payments | CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT | This field is populated with the total number of scheduled payments. |
NBR_FUTURE_ SCHED_PAYMENTS | Future Scheduled Payments | CI_PP_SCHED_PAY.PP_ID,PP_SCHED_DT | This field is populated with the number of payments scheduled in the future. Note: If the pay plan is “Broken”, this field includes the scheduled payment that lapsed and did not clear the payment. |
TOTAL_FUTURE_PAY_AMT | Future Payment Amount | CI_PP_SCHED_PAY.PP_SCHED_AMT | This field is populated with the sum of all payments scheduled in the future. If the pay plan is Broken/Canceled, this field is populated with zero. |
FUTURE_PAY_AMT_ BUCKET1 | Future Payment Amount Bucket 1 | CI_PP_SCHED_PAY.PP_SCHED_AMT | This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for pay plans in the source system. For details, see PP Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
FUTURE_PAY_AMT_ BUCKET2 | Future Payment Amount Bucket 2 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET3 | Future Payment Amount Bucket 3 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET4 | Future Payment Amount Bucket 4 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET5 | Future Payment Amount Bucket 5 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET6 | Future Payment Amount Bucket 6 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET7 | Future Payment Amount Bucket 7 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET8 | Future Payment Amount Bucket 8 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET9 | Future Payment Amount Bucket 9 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
FUTURE_PAY_AMT_ BUCKET10 | Future Payment Amount Bucket 10 | CI_PP_SCHED_PAY.PP_SCHED_AMT | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. |
START_DTTM | Start Date/Time | CI_PP.START_DT | |
END_DTTM | End Date/Time | CI_PP.LAST_STAT_DTTM | This field is populated only when the pay plan status is “Canceled”, “Broken”, or “Kept”. |
SNAP_TYPE_CD | Snap Type Code | This field is populated with 'M'(Monthly) or 'W'(Weekly) based on the configuration of the ETL process for this snapshot fact. | |
SNAPSHOT_DT | Snapshot Date | This field is populated with the last date of the current snapshot period. | |
SNAPSHOT_DATE_ KEY | Snapshot Date (Date Dimension Surrogate) Key | This field is populated with the last date of the current snapshot period. | |
START_DATE_KEY | Start Date (Date Dimension Surrogate) Key | CI_PP.START_DT | This field is populated with the last date of the current snapshot period. |
START_TIME_KEY | Start Time (Time Dimension Surrogate) Key | CI_PP.START_DT | |
END_DATE_KEY | End Date (Date Dimension Surrogate) Key | CI_PP.LAST_STAT_ DTTM | This field is populated only when the pay plan status is “Canceled”, “Broken”, or “Kept”. |
END_TIME_KEY | End Time (Time Dimension Surrogate) Key | CI_PP.LAST_STAT_ DTTM | This field is populated only when the pay plan status is “Canceled”, “Broken”, or “Kept”. |
PAY_PLAN_STATUS_KEY | Pay Plan Status Dimension Surrogate Key | CI_PP.PP_STAT_FLG | |
PAY_PLAN_TYPE_ KEY | Pay Plan Type Dimension Surrogate Key | CI_PP.PP_TYPE_CD | |
PAY_METHOD_KEY | Pay Method Dimension Surrogate Key | CI_PP.PAY_METH_CD | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account associated with the pay plan. |
ACCT_KEY | Account Dimension Surrogate Key | CI_PP.ACCT_ID | |
PAYOR_ACCT_KEY | Payor Account (Account Dimension Surrogate) Key | CI_PP.PAYOR_ACCT_ ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
PAY_PLAN_SNAP_UDD1_KEY | Pay Plan User Defined Dimension 1 Surrogate Key | ||
PAY_PLAN_SNAP_UDD2_KEY | Pay Plan User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Note: The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.
For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Payment Arrangement Accumulation
Stores all payment arrangements (excluding those in pending start state) in the source system.
The fact is special in the sense that the ETL process setup to load this fact will be configured as a daily refresh job. It is to primarily keep the measures updated to reflect the current status.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_PA |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_SA |
Stage Table Name | STG_CF_PA |
ODI Package Name | B1_PKG_CF_PA |
ETL View Name | B1_F_PA_VW |
Materialized View Name | B1_PA_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
PA_KEY | Payment Arrangement Accumulation Fact Key | This field is populated with the sequence from B1_PA_ACCUM_SEQ. | |
SRC_SA_ID | Source Service Agreement ID | CI_SA.SA_ID | |
CURRENCY_CD | Currency Code | CI_SA.CURRENCY_CD | |
REC_CHARGE_AMT | Total Scheduled Payment Amount | CI_SA_RCHG_HIST.RCR_CHG_AMT | This field is populated with the recurring charge amount effective as of the sysdate. |
TOTAL_PA_AMT | Total Payment Arrangement Amount | CI_ADJ.ADJ_AMT | This field is populated with the adjustment amount for adjustment(s) used to transfer the original service agreement’s balance. • If the payment arrangement is “active” or “kept”, then all non-canceled xfer adjustments are contributed to the arrangement amount. • If the payment arrangement is “canceled”, all canceled xfer adjustments are contributed to the arrangement amount. • If the payment arrangement is “broken”, then all canceled xfer adjustments are contributed to the arrangement amount. |
INSTALLMENT_CNT | Total Number of Installments | This field is calculated as the total payment arrangement amount divided by the recurring charge amount. | |
DUR_FROM_START | Duration from Start | CI_SA.START_DT CI_SA.END_DT | If the payment arrangement’s service agreement has an end date, this field populates the difference between start date and end date. Else, it populates the difference between start date and current date. |
PAID_TO_DATE_AMT | Paid to Date Amount | CI_FT.CUR_AMT | This field is populated with the sum of all payments made against the pay arrangement service agreement. |
FUTURE_PAY_AMT | Future Payment Amount | Future payment amount is set to zero when the payment arrangement is in Broken/Canceled state. Else, it is the difference between total pay agreement amount and paid to date columns. | |
FUTURE_PAY_AMT_ BUCKET1 | Future Payment Amount Bucket 1 | This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for payment arrangements in the source system. Future scheduled payments are identified based on the remaining amount, billing frequency, and bill cycle schedule. For details, see PA Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. | |
FUTURE_PAY_AMT_ BUCKET2 | Future Payment Amount Bucket 2 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET3 | Future Payment Amount Bucket 3 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET4 | Future Payment Amount Bucket 4 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET5 | Future Payment Amount Bucket 5 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET6 | Future Payment Amount Bucket 6 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET7 | Future Payment Amount Bucket 7 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET8 | Future Payment Amount Bucket 8 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET9 | Future Payment Amount Bucket 9 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_ BUCKET10 | Future Payment Amount Bucket 10 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
START_DTTM | Start Date/Time | CI_SA.START_DT | |
END_DTTM | End Date/Time | CI_SA.END_DT | |
START_DATE_KEY | Start Date (Date Dimension Surrogate) Key | CI_SA.START_DT | |
START_TIME_KEY | Start Time (Time Dimension Surrogate) Key | CI_SA.START_DT | |
END_DATE_KEY | End Date (Date Dimension Surrogate) Key | CI_SA.END_DT | |
END_TIME_KEY | End Time (Time Dimension Surrogate) Key | CI_SA.END_DT | |
PA_STATUS_KEY | Payment Arrangement Status Dimension Surrogate Key | CI_SA.SA_STATUS_FLG | 1. Set status as ‘Active’ if the service agreement’s status is ‘Active’, ‘Pending Stop’, ‘Stopped’, or ‘Reactivated’. 2. ‘Broken’ if the service agreement’s status is ‘Closed’ with a broken payment arrangement characteristic. 3. Kept if the service agreement’s status is ‘Closed’ without the broken payment arrangement characteristic. (Broken characteristic type/value is defined as a parameter on source. For more details on the parameters, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. 4. ‘Canceled’ if the service agreement is canceled. |
INSTALLMENT_CNT_KEY | Installments Count Dimension Surrogate Key | This field populates the appropriate key using the installment amount and age ranges from the Installment Count dimension. For details, see PA Number of Installments Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. | |
REC_CHARGE_AMOUNT_KEY | Recurring Charge Bucket Dimension Surrogate Key | CI_SA_RCHG_HIST. RCR_CHG_AMT | This field populates the appropriate key using the recurring charge amount and age ranges from the Recurring Charge Amount Bucket dimension. The recurring charge amount age ranges are defined on the source. For details, see PA Recurring Charge Amount Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field populates the person ID with the main customer of the account |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_SA.SA_ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
PA_UDD1_KEY | Payment Arrangement User Defined Dimension 1 Surrogate Key | ||
PA_UDD2_KEY | Payment Arrangement User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with a standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Note: The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.
For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Payment Arrangement Snapshot
Stores snapshots of all payment arrangements that started before the snapshot date and excludes the service agreements of pending start, canceled, and incomplete statuses. The snapshot also excludes closed service agreements where the difference (in days) between the snapshot end date and the service agreement end date is greater than the 'X' number of days to exclude closed service agreements.
The value 'X' will have to be configured as part of the BI configuration in the source system.
For more details on the parameters, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_PA_SNAP |
Table Type | Fact |
Fact Type | Snapshot |
Driver Table | CI_SA |
Stage Table Name | STG_CF_PA_SNAP |
ODI Package Name | B1_PKG_CF_PA_SNAP |
ETL View Name | B1_F_PA_SNAP_VW |
Materialized View Name | B1_PA_SNAP_MON_MV1 B1_PA_SNAP_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
PA_SNAP_KEY | Payment Arrangement Snapshot Fact Key | This field is populated with the sequence from B1_PA_SNAP_SEQ. | |
SRC_SA_ID | Source Service Agreement ID | CI_SA.SA_ID | |
CURRENCY_CD | Currency Code | CI_SA.CURRENCY_ CD | |
REC_CHARGE_AMT | Total Scheduled Payment Amount | CI_SA_RCHG_HIST. RCR_CHG_AMT | This field is populated with the recurring charge amount effective as of the snapshot date. |
TOTAL_PA_AMT | Total Payment Arrangement Amount | CI_ADJ.ADJ_AMT | This field is populated with the adjustment amount for the adjustment(s) used to transfer the original service agreement’s balance. • If the payment arrangement is still “active” or “kept”, then all non-canceled xfer adjustments are contributed to the arrangement amount. • If the payment arrangement is “broken”, then all canceled xfer adjustments are contributed to the arrangement amount. |
INSTALLMENT_CNT | Total Number of Installments | This field is populated with the value calculated by dividing the total payment arrangement amount by recurring charge amount. | |
DUR_FROM_START | Duration from Start | CI_SA.START_DT CI_SA.END_DT | If the pay arrangement service agreement has an end date, this field is populated with the difference between start date and end date. |
PAID_TO_DATE_AMT | Paid to Date Amount | CI_FT.CUR_AMT | This field is populated with the sum of all payments made against the pay arrangement service agreement. |
FUTURE_PAY_AMT | Future Payment Amount | The future payment amount is set to zero when the payment arrangement is in Broken/Canceled states. Else, it is the difference between the Total PA Amount and Paid To Date columns. | |
FUTURE_PAY_AMT_BUCKET1 | Future Payment Amount Bucket 1 | This field is populated with the sum of all future scheduled payments that fall into the age ranges configured for the bucket 1 slot for payment arrangements in the source system. Future scheduled payments are identified based on the remaining amount, billing frequency, and bill cycle schedule. For details, see PA Future Payment Age Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. | |
FUTURE_PAY_AMT_BUCKET2 | Future Payment Amount Bucket 2 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET3 | Future Payment Amount Bucket 3 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET4 | Future Payment Amount Bucket 4 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET5 | Future Payment Amount Bucket 5 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET6 | Future Payment Amount Bucket 6 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET7 | Future Payment Amount Bucket 7 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET8 | Future Payment Amount Bucket 8 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET9 | Future Payment Amount Bucket 9 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
FUTURE_PAY_AMT_BUCKET10 | Future Payment Amount Bucket 10 | See the FUTURE_PAY_AMT_BUCKET1 field for transformation logic. | |
START_DTTM | Start Date/Time | CI_SA.START_DT | |
END_DTTM | End Date/Time | CI_SA.END_DT | |
SNAP_TYPE_CD | Snap Type Code | ||
SNAPSHOT_DT | Snapshot Date | ||
SNAPSHOT_DATE_ KEY | Snapshot Date (Date Dimension Surrogate) Key | ||
START_DATE_KEY | Start Date (Date Dimension Surrogate) Key | CI_SA.START_DT | |
START_TIME_KEY | Start Time (Time Dimension Surrogate) Key | CI_SA.START_DT | |
END_DATE_KEY | End Date (Date Dimension Surrogate) Key | CI_SA.END_DT | |
END_TIME_KEY | End Time (Time Dimension Surrogate) Key | CI_SA.END_DT | |
PA_STATUS_KEY | Payment Arrangement Status Dimension Surrogate Key | CI_SA.SA_STATUS_ FLG | 1. Set status as ‘Active’ if the service agreement’s status is ‘Active’, ‘Pending Stop’, ‘Stopped’, or ‘Reactivated’. 2. ‘Broken’ if the service agreement’s status is ‘Closed’ with a broken payment arrangement characteristic. 3. Kept if the service agreement’s status is ‘Closed’ without the broken payment arrangement characteristic. (Broken characteristic type/value is defined as a parameter on source. For more details, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. 4. ‘Canceled’ if the service agreement is canceled. 5. ‘Active’ if the service agreement is closed and the service agreement start date is before the snapshot date, but the end date is after the snapshot date. |
INSTALLMENT_CNT_KEY | Installments Count Dimension Surrogate Key | Based on the installment amount and the age ranges, the dimension key has to be identified. The installment count age ranges are defined on the source. For details, see PA Number of Installments Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. | |
REC_CHARGE_AMOUNT_KEY | Recurring Charge Bucket Dimension Surrogate Key | CI_SA_RCHG_HIST. RCR_CHG_AMT | This field is populated with the recurring charge amount and age ranges from the Recurring Charge Amount bucket dimension. The recurring charge amount age ranges are defined on the source. For details, see PA Recurring Charge Amount Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_SA.SA_ID | |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID | |
PA_SNAP_UDD1_KEY | Payment Arrangement User Defined Dimension 1 Surrogate Key | ||
PA_SNAP_UDD2_KEY | Payment Arrangement User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Note: The Future Payment Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.
For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Payment Tender
Stores all pay tenders defined in the source system.
The ETL process for this fact is special in the sense that it considers ‘deletes’ in the source system. If a pay tender is deleted in the source system, it will also be deleted from the fact table. This fact stores the tender amount information.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_PAY_TNDR |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_PAY_TNDR |
Stage Table Name | STG_CF_PAY_TNDR |
ODI Package Name | B1_PKG_CF_PAY_TNDR |
ETL View Name | B1_F_PAY_TNDR_VW |
Materialized View Name |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
PAY_TNDR_KEY | Payment Tender Fact Key | This field is populated with the sequence from SPL_PAY_TNDR_SEQ. | |
SRC_PAY_TENDER_ ID | Payment Tender ID (Natural Key) | CI_PAY_TNDR.PAY_ TENDER_ID | |
CURRENCY_CD | Currency Code | CI_PAY_TNDR.CURRENCY_CD | |
TNDR_CTRL_ID | Tender Control ID | CI_PAY_TNDR.TNDR_CTL_ID | |
TNDR_AMT | Tender Amount | CI_PAY_TNDR.TENDER_AMT | |
CANCEL_DATE_KEY | Date Dimension Surrogate Key | CI_FT.FREEZE_DTTM CI_PAY_EVENT.CRE_DTTM | This field is populated only when the pay tender has been canceled. The freeze date on the pay segment cancellation FT will be used. Otherwise, the pay event's creation date will be used. |
PAYEVT_DATE_KEY | Date Dimension Surrogate Key | CI_PAY_EVENT.PAY_DT | |
TNDR_CTRL_DATE_KEY | Date Dimension Surrogate Key | CI_TNDR_CTL.CRE_DTTM | Only the date portion from the source field will be used. |
TNDR_STATUS_KEY | Tender Status Dimension Surrogate Key | CI_PAY_TNDR.TNDR_STATUS_FLG | |
TNDR_TYPE_KEY | Tender Type Dimension Surrogate Key | CI_PAY_TNDR.TENDER_TYPE_CD | |
TNDR_SRCE_KEY | Tender Source Dimension Surrogate Key | CI_TNDR_CTL.TNDR_SOURCE_CD | |
ACCT_KEY | Account Dimension Surrogate Key | CI_PAY_TNDR.PAYOR_ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID | This field is populated with the characteristic premise of the payer account’s non-cancelled and non-closed service agreement with latest start date. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | The main customer of the payor account will be used to populate this field. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID | This field is populated with the characteristic premise of the payer account’s non-cancelled and non-closed service agreement with latest start date. |
PAY_CAN_RSN_KEY | Payment Cancel Reason Dimension Surrogate Key | CI_PAY_TNDR.CAN_ RSN_CD | |
PAY_TNDR_UDD1_ KEY | Payment Tender User Defined Dimension 1 Surrogate Key | ||
PAY_TNDR_UDD2_ KEY | Payment Tender User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Service Agreement Arrears Snapshot
Stores the snapshots of all non-canceled service agreements that started before the snapshot date. The snapshot also excludes the closed service agreements where the difference (in days) between the snapshot end date and the service agreement end date is greater than the 'X' number of days to exclude the closed service agreements. The value 'X' should be configured as part of the BI configuration in the source system.
Note that only UDM1 to UDM10 fields are used to populate the bucketized arrears information. If more than 10 age buckets are configured in Oracle Utilities Customer Care and Billing for arrears, only the first 10 buckets will be considered by the out-of-the-box ETL. If the arrears amount need to be bucketized into more than 10 buckets, customize it to populate the remaining arrears bucket columns.
For details, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Entity Relationship Diagram
Properties
Property | Value |
|---|---|
Target Table Name | CF_ARREARS |
Table Type | Fact |
Fact Type | Snapshot |
Driver Table | CI_SA |
Stage Table Name | STG_CF_ARREARS |
ODI Package Name | B1_PKG_CF_ARREARS |
ETL View Name | B1_F_ARREARS_VW |
Materialized View Name | B1_ARREARS_MON_MV1 B1_ARREARS_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | |
ARREARS_KEY | Arrears Fact Generated Key | This field is populated with the sequence from B1_ARREARS_SEQ. | |
CURRENCY_CD | Currency Code | CI_SA.CURRENCY_ CD | |
CURR_BAL_AMT | Current Balance | CI_FT.CUR_AMT | |
DATE_KEY | Date Dimension Surrogate Key | This field is populated with the snapshot date. | |
FISCAL_CAL_KEY | Fiscal Period Dimension Surrogate Key | CI_CAL_PERIOD.CALENDER_ID | This field is populated using the calendar based on accounting date. |
PAYOFF_BAL_AMT | Payoff Balance Amount | CI_FT.TOT_AMT | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | This field is populated with the Char Prem ID. If not found, it is populated with the mailing premise ID from account. |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_SA.SA_ID | |
SNAP_TYPE_CD | Snap Type | ||
UDM1 | User Defined Measure 1 | The arrears date and arrears amounts are identified by fetching the debits and credits. Amounts are summed and put in the UDM columns based on the age ranges. These age ranges are defined on the source. For details, see SA Arrears Buckets in the Configuring Oracle Utilities Analytics Warehouse chapter in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. | |
UDM2 | User Defined Measure 2 | See the UDM1 field for transformation logic. | |
UDM3 | User Defined Measure 3 | See the UDM1 field for transformation logic. | |
UDM4 | User Defined Measure 4 | See the UDM1 field for transformation logic. | |
UDM5 | User Defined Measure 5 | See the UDM1 field for transformation logic. | |
UDM6 | User Defined Measure 6 | See the UDM1 field for transformation logic. | |
UDM7 | User Defined Measure 7 | See the UDM1 field for transformation logic. | |
UDM8 | User Defined Measure 8 | See the UDM1 field for transformation logic. | |
UDM9 | User Defined Measure 9 | See the UDM1 field for transformation logic. | |
UDM10 | User Defined Measure 10 | See the UDM1 field for transformation logic. | |
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDM21 | User Defined Measure 21 | ||
UDM22 | User Defined Measure 22 | ||
UDM23 | User Defined Measure 23 | ||
UDM24 | User Defined Measure 24 | ||
UDM25 | User Defined Measure 25 | ||
UDM26 | User Defined Measure 26 | ||
UDM27 | User Defined Measure 27 | ||
UDM28 | User Defined Measure 28 | ||
UDM29 | User Defined Measure 29 | ||
UDM30 | User Defined Measure 30 | ||
FACT_CNT | Fact Count | This field is populated with a standard value of “1”. | |
RATE_KEY | Rate dimension surrogate key | CI_SA_RS_HIST.RS_ CD | This field uses the Rate Code from service agreement rate history based on the effective date. |
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
ARREARS_UDD1_ KEY | Arrears User Defined Dimension 1 Surrogate Key | ||
ARREARS_UDD2_ KEY | Arrears User Defined Dimension 2 Surrogate Key | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
SRC_SA_ID | SA Identifier | CI_SA.SA_ID | |
SNAPSHOT_DT | Snapshot Date | ||
DAYS_LAST_FRZ_BS | Number of Days Since Last Frozen BS | CI_BSEG.END_DT | This field is populated with the difference (in days) between the freeze date of the last frozen bill segment for the service agreement and the snapshot end date. Note: If there is no frozen bill segment for the service agreement, use the service agreement start date. |
DAYS_LAST_FRZ_BS_KEY | Days Since Last Frozen Dimension Surrogate Key | Based on the measure retrieved above, fetch the corresponding dimension key. | |
DAYS_UNBILLED_ USG | Number of Days of Unbilled Usage | CI_BSEG.END_DT CI_SA.END_DT | 1. If there is a frozen bill segment: a. If the service agreement has ended (Status 60), use the difference between the agreement end date and the end date of bill segment latest before the snapshot date. b. Else, use the difference between the snapshot date and the end date of bill segment latest before the snapshot date. 2. If there is no frozen bill segment: a. If the service agreement has ended (Status 60), use the difference between agreement’s end date and start date. b. Else, use the difference between the snapshot date and the service agreement’s start date. |
DAYS_UNBILLED_ USG_KEY | Days of Unbilled Usage Dimension Surrogate Key | Based on the measure retrieved above, fetch the corresponding dimension key. | |
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Note: The Arrears Age buckets configured in the source are loaded in the MDADM.B1_RANGE_LOOKUP table in the data warehouse. The ELT job for this is configured to be initial load only. Any incremental changes to these buckets after the initial data load will not be reflected in the warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated and reloaded in the fact table and the range lookup table to reflect the changes.
For details about Range look-up and reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Service Agreement Billing
Stores the details of the service agreements eligible for billing in a bill window based on the bill segment creation. Information related to the latest bill segment for a service agreement, bill cycle, and window start date combination is stored in this fact. Manual bills are excluded from this fact.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_SA_BILLING |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_BSEG |
Stage Table Name | STG_CF_SA_BILLING |
ODI Package Name | B1_PKG_CF_SA_BILLING |
ETL View Name | B1_F_SA_BILLING_VW |
Materialized View Name | B1_SA_BILLING_MON_MV1 B1_SA_BILLING_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
SA_BILLING_KEY | SA Billing Fact Key | This field is populated with the sequence from B1_SA_BILLING_ACCUM_SEQ. | |
SRC_SA_ID | Service Agreement ID | CI_BSEG.SA_ID | |
SRC_BILL_CYC_CD | Bill Cycle Code | CI_BSEG.BILL_CYC_ CD | If a bill segment does not have a bill cycle, it is retrieved from the bill. |
WIN_START_DTTM | Window Start Date/Time | CI_BSEG.WIN_START_DT CI_BILL.WIN_START_DT | If a bill segment does not have a window start date populated, it is fetched from the bill. |
FROZEN_DUR | Duration to Frozen | CI_BILL.CRE_DTTM CI_FT. FREEZE_DTTM | This field is populated with the difference (in hours) between the bill’s creation date and the bill segment freeze date. |
REBILL_IND | Re-billed Indicator | CI_BSEG.REBILL_ SEG_ID | This field is populated with “1” if the latest bill segment for the service agreement is a re-bill. Else, “0”. |
ORIG_REV_AMT | Original Revenue Amount | CI_FT_GL.AMOUNT | This field is populated with the revenue amount of the original bill segment if the current bill segment being processed is a re-billed one. |
REV_AMT | Revenue Amount | CI_FT_GL.AMOUNT | This field is populated with the absolute value of total amount of all financial transaction GLs whose distribution code’s characteristic type/ revenue characteristic value match with the characteristic type/value configured as part of the BI configuration in the source system and ‘Effective’ on the specific date. For more details, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
CURRENCY_CD | Currency Code | CI_FT.CURRENCY_ CD | |
FIRST_BS_IND | First Bill Segment of SA Indicator | CI_SA.START_DT CI_BSEG.START_DT | This field is set to 1 if the service agreement start date matches with the bill segment start date. Else, it is set to zero. |
EST_IND | Estimated Indicator | CI_BSEG.EST_SW | This field is populated with “1” if the latest bill segment is an estimate. Else, “0”. |
HIGH_BILL_CASE_ IND | High Bill Complaint Indicator | Set the indicator to 1 if all the below conditions return true: 1. If there exists a case with Case Characteristic Value same as bill ID of the bill segment being processed. Characteristic type corresponding to the bill would be defined on the source system extract parameters. 2. The above case should be of Case type corresponding to High Bill Complaint. These Case types are again provided by users on the source system extract parameters. 3. Case should not be in a status same as the ‘Exclusion’ status mentioned on source system extract parameters. For more information on these parameters that have to be configured on source, see BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. | |
FREEZE_DTTM | Freeze Date/Time | CI_FT.FREEZE_DTTM | |
CRE_DTTM | Creation Date/Time | CI_BILL.CRE_DTTM | |
BILL_CYC_SCH_KEY | Bill Cycle Schedule Dimension Surrogate Key | CI_BSEG.BILL_CYC_CD + WIN_START_DT CI_BILL.BILL_CYC_CD + WIN_START_DT | If the bill segment does not have information, it is fetched from the bill. |
BSEG_STATUS_KEY | Bill Segment Status Dimension Surrogate Key | CI_BSEG.BSEG_STAT_FLG | 1. Use the actual status if bill segment is not in error or deleted. 2. If status is ‘Error’, look for the error message. If error displayed is “Awaiting Bill Determinants from MDM”, use the ‘Awaiting BD’ status. 3. When the bill segment is deleted, use the ‘Deleted’ status. |
BILL_CAN_RSN_KEY | Bill Cancel Reason Dimension Surrogate Key | CI_BSEG.CAN_RSN_ CD | The Bill Cancel Reason value should be populated only if the service agreement’s bill segment is pending cancel or canceled. If the latest bill segment is a Rebill bill segment, the cancel reason should be on the older BS. |
MSG_KEY | Message Dimension Surrogate Key | CI_BSEG_EXCP.MESSAGE_CAT_NBR CI_BSEG_EXCP.MESSAGE_NBR | |
BILL_DAY_IN_WIN_KEY | Day In Window Dimension Surrogate Key | CI_FT.FREEZE_DTTM CI_BILL_CYC_SCH.FREEZE_DTTM | This logic is applicable for bill segments with ‘Frozen’ status. If the freeze date is between the bill cycle schedule start and end dates, set 'In Window', else 'Outside Window'. The billing day in window can be calculated as difference between the freeze date and the window start date. Based on this difference, suitable age range needs to be identified. These age ranges are configured on source. For details, see Billing Day In Window Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
DAYS_TO_WIN_CLS_KEY | Days to Window Closure Dimension Surrogate Key | CI_BILL_CYC_SCH.WIN_END_DT | This field is populated only if the latest bill segment for the service agreement is in status Error/Deleted/Awaiting BD. This is the difference between the window end date and current date. If the window end date is greater than sysdate, pick the range from open window age configuration. Else, pick the one from closed window age configurations, which are defined on source. For details, see Days Before Bill Window Closes Buckets in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
MSRMT_TYPE_KEY | Measurement Type Dimension Surrogate Key | C1-USAGE.USG_DATA_AREA | To determine the Measurement Type, check Special Role flag on SA Type. 1. If it’s “Bill Determinants Required”, check if there’s a Usage Request for the service agreement’s latest bill segment and determine the measurement type based on the usage request list (each entry in the list has a ‘usage type” associated with it). 2. If it’s “Interval”, set measurement type to “Interval”. 3. If it’s any other special role flag value (billable charge, deposit, PA, etc), set measurement type to “N/A”. 4. If it’s blank, check the valid SP Types linked to the SA Type and see if any have a subtype of “Meter”. If yes, set measurement type to “Scalar”. Otherwise, set to “N/A”. |
FREEZE_DATE_KEY | Freeze Date (Date Dimension Surrogate) Key | CI_FT.FREEZE_DTTM | |
FREEZE_TIME_KEY | Freeze Time (Time Dimension Surrogate) Key | CI_FT.FREEZE_DTTM | |
CRE_DATE_KEY | Creation Date (Date Dimension Surrogate) Key | CI_BILL.CRE_DTTM | |
CRE_TIME_KEY | Creation Time (Time Dimension Surrogate) Key | CI_BILL.CRE_DTTM | |
WIN_START_DATE_KEY | Window Start Date (Date Dimension Surrogate) Key | CI_BSEG.WIN_START_DT CI_BILL.WIN_START_DT | |
WIN_START_TIME_KEY | Window Start Time (Time Dimension Surrogate) Key | CI_BSEG.WIN_START_DT CI_BILL.WIN_START_DT | |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | If the characteristic premise is not found, use the mailing premise from account. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | The field is populated with the main customer of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID CI_ACCT.MAILING_ PREM_ID | If the characteristic premise is not found, use the mailing premise from account. |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_BSEG.SA_ID | |
RATE1_KEY | Rate Dimension Surrogate Key | CI_SA_RS_HIST.RS_ CD CI_BSEG_CALC.RS_ CD | If the bill segment has no calculation header, retrieve the rate effective on the service agreement as of the bill segment start date. If bill segment has calculation headers, pick the first two distinct primary rate schedules and store these in two columns. |
RATE2_KEY | Rate Dimension Surrogate Key | CI_SA_RS_HIST.RS_CD CI_BSEG_CALC.RS_CD | See RATE1_KEY description for logic details. |
SA_BILLING_UDD1_KEY | SA Billing User Defined Dimension 1 Surrogate Key | ||
SA_BILLING_UDD2_KEY | SA Billing User Defined Dimension 2 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with a standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Service Agreement
Stores all service agreements defined in the source system.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_SA |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_SA |
Stage Table Name | STG_CF_SA |
ODI Package Name | B1_PKG_CF_SA |
ETL View Name | B1_F_SA_VW |
Materialized View Name | B1_SA_TOPX_MON_MV1 B1_SA_TOPX_MON_MV2 B1_SA_MON_MV1 B1_SA_MON_MV2 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
SA_FACT_KEY | Service Agreement Fact Key | This field is populated with the sequence from B1_SA_SEQ. | |
SRC_SA_ID | Service Agreement ID (Natural Key) | CI_SA.SA_ID | |
CURRENCY_CD | Currency Code | CI_SA.CURRENCY_CD | |
SA_DURATION | Service Agreement Duration | CI_SA.START_DT CI_SA.END_DT | If the end date is not populated, set SA Duration = 0. Else, set SA Duration (computed in hours) = End Date – Start Date. |
FACT_CNT | Count | This field is populated with the standard value of “1”. | |
ACCT_KEY | Account Dimension Surrogate Key | CI_SA.ACCT_ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID | |
START_DATE_KEY | Start Date (Date Dimension Surrogate Key) | CI_SA.START_DT | |
END_DATE_KEY | End Date (Date Dimension Surrogate Key) | CI_SA.END_DT | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main person of the account. |
PREM_KEY | Premise Dimension Surrogate Key | CI_SA.CHAR_PREM_ ID | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_SA.SA_ID | |
SA_STATUS_KEY | Service Agreement Status Dimension Surrogate Key | CI_SA.SA_STATUS_ FLG | |
SA_UDD1_KEY | Service Agreement User Defined Dimension 1 Surrogate Key | ||
SA_UDD2_KEY | Service Agreement User Defined Dimension 2 Surrogate Key | ||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDM6 | User Defined Measure 6 | ||
UDM7 | User Defined Measure 7 | ||
UDM8 | User Defined Measure 8 | ||
UDM9 | User Defined Measure 9 | ||
UDM10 | User Defined Measure 10 | ||
UDM11 | User Defined Measure 11 | ||
UDM12 | User Defined Measure 12 | ||
UDM13 | User Defined Measure 13 | ||
UDM14 | User Defined Measure 14 | ||
UDM15 | User Defined Measure 15 | ||
UDM16 | User Defined Measure 16 | ||
UDM17 | User Defined Measure 17 | ||
UDM18 | User Defined Measure 18 | ||
UDM19 | User Defined Measure 19 | ||
UDM20 | User Defined Measure 20 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
UDDGENL1 | User Defined Long Degenerate Dimension 1 | ||
UDDGENL2 | User Defined Long Degenerate Dimension 2 | ||
UDDGENL3 | User Defined Long Degenerate Dimension 3 | ||
UDDGENL4 | User Defined Long Degenerate Dimension 4 | ||
UDDGENL5 | User Defined Long Degenerate Dimension 5 | ||
UDDGENL6 | User Defined Long Degenerate Dimension 6 | ||
UDDGENL7 | User Defined Long Degenerate Dimension 7 | ||
UDDGENL8 | User Defined Long Degenerate Dimension 8 | ||
UDDGENL9 | User Defined Long Degenerate Dimension 9 | ||
UDDGENL10 | User Defined Long Degenerate Dimension 10 | ||
UDDGEN_NUM1 | User Defined Numeric Degenerate Dimension 1 | ||
UDDGEN_NUM2 | User Defined Numeric Degenerate Dimension 2 | ||
UDDGEN_NUM3 | User Defined Numeric Degenerate Dimension 3 | ||
UDDGEN_NUM4 | User Defined Numeric Degenerate Dimension 4 | ||
UDDGEN_NUM5 | User Defined Numeric Degenerate Dimension 5 | ||
UDDGEN_NUM6 | User Defined Numeric Degenerate Dimension 6 | ||
UDDGEN_NUM7 | User Defined Numeric Degenerate Dimension 7 | ||
UDDGEN_NUM8 | User Defined Numeric Degenerate Dimension 8 | ||
UDDGEN_NUM9 | User Defined Numeric Degenerate Dimension 9 | ||
UDDGEN_NUM10 | User Defined Numeric Degenerate Dimension 10 | ||
UDDFK1_KEY | User Defined Dimension Foreign Key 1 | ||
UDDFK2_KEY | User Defined Dimension Foreign Key 2 | ||
UDDFK3_KEY | User Defined Dimension Foreign Key 3 | ||
UDDFK4_KEY | User Defined Dimension Foreign Key 4 | ||
UDDFK5_KEY | User Defined Dimension Foreign Key 5 | ||
UDDFK6_KEY | User Defined Dimension Foreign Key 6 | ||
UDDFK7_KEY | User Defined Dimension Foreign Key 7 | ||
UDDFK8_KEY | User Defined Dimension Foreign Key 8 | ||
UDDFK9_KEY | User Defined Dimension Foreign Key 9 | ||
UDDFK10_KEY | User Defined Dimension Foreign Key 10 | ||
UD_DATE1 | User Defined Date Field 1 | ||
UD_DATE2 | User Defined Date Field 2 | ||
UD_DATE3 | User Defined Date Field 3 | ||
UD_DATE4 | User Defined Date Field 4 | ||
UD_DATE5 | User Defined Date Field 5 | ||
UD_DATE6 | User Defined Date Field 6 | ||
UD_DATE7 | User Defined Date Field 7 | ||
UD_DATE8 | User Defined Date Field 8 | ||
UD_DATE9 | User Defined Date Field 9 | ||
UD_DATE10 | User Defined Date Field 10 | ||
UD_DATE1_KEY | User Defined Date Key Field 1 | ||
UD_DATE2_KEY | User Defined Date Key Field 2 | ||
UD_DATE3_KEY | User Defined Date Key Field 3 | ||
UD_DATE4_KEY | User Defined Date Key Field 4 | ||
UD_DATE5_KEY | User Defined Date Key Field 5 | ||
UD_DATE6_KEY | User Defined Date Key Field 6 | ||
UD_DATE7_KEY | User Defined Date Key Field 7 | ||
UD_DATE8_KEY | User Defined Date Key Field 8 | ||
UD_DATE9_KEY | User Defined Date Key Field 9 | ||
UD_DATE10_KEY | User Defined Date Key Field 10 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Uncollectible Event
Stores all write-off events defined in the source system.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_UCOL_EVT |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_WO_EVT |
Stage Table Name | STG_CF_UCOL_EVT |
ODI Package Name | B1_PKG_CF_UCOL_EVT |
ETL View Name | B1_F_UCOL_EVT_VW |
Materialized View Name | n/a |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
ACCT_KEY | Account Dimension Surrogate Key | CI_WO_PROC.ACCT_ ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | If the mailing premise on the account is not found, then the characteristic premise on the service agreement will be used. |
SRC_UCPROC_ID | Uncollectible Process ID (Natural Key) | CI_WO_PROC.WO_ PROC_ID | |
UCOL_EVT_KEY | Uncollectible Event Fact Key | This field is populated with the sequence from B1_UCOL_EVT_SEQ. | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | This field is populated with the currency code from account. |
EVT_DATE_KEY | Uncollectible Event Date | CI_WO_EVT.COMPLETION_DT | |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account associated with the write-off event. |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | If the mailing premise on the account is not found then the characteristic premise on the service agreement will be used. |
UCEVT_TYPE_KEY | Uncollectible Event Type Dimension Surrogate Key | CI_WO_EVT.WO_EVT_TYP_CD | |
UCOL_EVT_SEQ | Uncollectible Event Sequence | CI_WO_EVT.EVT_SEQ | |
UCPROC_TMPL_KEY | Uncollectible Process Template Dimension Surrogate Key | CI_WO_PROC.WO_PROC_TMPL_CD | |
FACT_CNT | Count | This field is populated with the standard value of “1”. | |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UCEVT_UDD1_KEY | Uncollectible Event User Defined Dimension 1 Surrogate Key | ||
UCEVT_UDD2_KEY | Uncollectible Event User Defined Dimension 2 Surrogate Key | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Uncollectible Process
Stores all write-off processes defined in the source system. This fact captures metrics, such as the duration of the process, the arrears at the start of the process, and at the end of the process.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_UCOL_PROC |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_WO_PROC |
Stage Table Name | STG_CF_UCOL_PROC |
ODI Package Name | B1_PKG_CF_UCOL_PROC |
ETL View Name | B1_F_UCOL_PROC_VW |
Materialized View Name | B1_UCOLLPROC_MON_MV1 B1_UCOLLPROC_MON_MV2 B1_UCOLLPROC_MON_TOPX_MV1 B1_UCOLLPROC_MON_TOPX_MV2 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
UCPROC_KEY | Uncollectible Process Fact Key | This field is populated with the sequence from SPL_UCPROC_SEQ. | |
SRC_UPROC_ID | Uncollectible Process ID (Natural Key) | CI_WO_PROC.WO_PROC_ID | |
CURRENCY_CD | Currency Code | CI_ACCT.CURRENCY_CD | |
UCPROC_DURATION | Uncollectible Process Duration | CI_WO_PROC.CRE_DTTM CI_WO_EVT.COMPLETION_DT | This field is populated as the difference (in hours) between the creation date of the write-off process and the maximum completion date of the associated write-off events. |
ARRS_AT_START | Arrears at Start | CI_FT.TOT_AMT | This field is calculated as the sum of total balance of all the service agreements linked to the write-off process as of the creation date. |
ARRS_AT_END | Arrears at End | CI_FT.CUR_AMT | This field is populated only if status of the write-off process is not active. It is calculated as the sum of the current balance of all the service agreements linked to the write-off process as of the max completion date of the associated write-off events. |
ARRS_DIFF | Arrears at End - Arrears at Start | This field is populated only if status of the write-off process is not active. It is calculated as the difference between the arrears at start and arrears at end. | |
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
ACCT_KEY | Account Dimension Surrogate Key | CI_WO_PROC.ACCT_ ID | |
ADDR_KEY | Address Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | If the mailing premise on the account is not found, the characteristic premise on the service agreement will be used. |
PER_KEY | Person Dimension Surrogate Key | CI_ACCT_PER.PER_ ID | This field is populated with the main customer of the account associated with the write-off process. |
PREM_KEY | Premise Dimension Surrogate Key | CI_ACCT.MAILING_ PREM_ID CI_SA.CHAR_PREM_ ID | If the mailing premise on the account is not found, the characteristic premise on the service agreement will be used. |
START_DATE_KEY | Start Date (Date Dimension Surrogate Key) | CI_WO_PROC.CRE_ DTTM | |
END_DATE_KEY | Uncollectible Process End Date | CI_WO_EVT.COMPLETION_DT CI_WO_PROC.CRE_ DTTM | The field is populated only when the write-off process is not active. It is populated with the maximum write-off event completion date. If no event completion dates are found, creation date of the write-off process will be used. |
UCPROC_STAT_KEY | Uncollectible Process Status Dimension Surrogate Key | CI_WO_PROC.WO_STATUS_FLG | Note that the write-off process statuses from the source will be transformed while storing in the fact. • If the write-off process is ‘Active’, the same status will be retained. • If the write-off process is ‘Inactive’ and status reason is ‘Completed’, then the status will be marked as ‘Ineffective’ in the fact. • Otherwise the status will be marked as ‘Effective’ in the fact. |
UCPROC_TMPL_KEY | Uncollectible Process Template Dimension Surrogate Key | CI_WO_PROC.WO_PROC_TMPL_CD | |
UCPROC_UDD1_KEY | Uncollectible Process User Defined Dimension 1 Surrogate Key | ||
UCPROC_UDD2_KEY | Uncollectible Process User Defined Dimension 2 Surrogate Key | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
Recent To Do Entry
The Recent To Do Entry fact stores all To-Do entries that have been completed in the last ‘X’ days and also the incomplete To-Do entries.
The value ‘X’ has to be configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_RECENT_TD_ENTRY |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_TD_ENTRY |
Stage Table Name | STG_CF_RECENT_TD_ENTRY |
ODI Package Name | B1_PKG_CF_RECENT_TD_ENTRY |
ETL View Name | B1_F_TD_ENTRY_VW |
Materialized View Name | n/a |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
TD_ENTRY_KEY | To Do Dimension Surrogate Key | This field is populated with the sequence from OUBI_TD_ENTRY_SEQ. | |
SRC_TD_ENTRY_ID | To Do Entry ID (Natural Key) | CI_TD_ENTRY.TD_ENTRY_ID | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the SA ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
PER_KEY | Person Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Person ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
ACCT_KEY | Account Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Account ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
PREM_KEY | Premise Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
ADDR_KEY | Address Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
USER_KEY | User Dimension Surrogate Key | CI_TD_ENTRY.ASSIGNED_USER_ID | |
TD_TYPE_KEY | To Do Type Dimension Key | CI_TD_ENTRY.TD_TYPE_CD | |
TD_ROLE_KEY | To Do Role Dimension Key | CI_TD_ENTRY.ROLE_ID | |
TD_STATUS_KEY | To Do Status Dimension Key | CI_TD_ENTRY.ENTRY_STATUS_FLG | |
TD_PRIORITY_KEY | To Do Priority Dimension Key | CI_TD_ENTRY.TD_PRIORITY_FLG | |
TD_SKILL_KEY | To Do Skill Dimension Surrogate Key | CI_TD_TYPE_CHAR.ADHOC_CHAR_VAL CI_TD_TYPE_CHAR.CHAR_TYPE_CD CI_TD_TYPE_CHAR.CHAR_VAL | This field is populated based on the characteristic types configured on the To Do entry’s To Do type. Only those characteristic types configured as skills in the AQM feature configuration will be considered. If a To Do type has both regular skills and ad-hoc skills, then the ad-hoc skills will be given the priority. The first skill to match the message category/number on the To Do entry will be picked up. |
MSG_KEY | Message Dimension Surrogate Key | CI_TD_ENTRY.MESSAGE_CAT_NBR CI_TD_ENTRY.MESSAGE_NBR | |
TD_KEY | To Do Dimension Surrogate Key | CI_TD_ENTRY.TD_ENTRY_ID | |
CREATE_DATE_KEY | Create Date (Date Dimension Surrogate Key) | CI_TD_ENTRY.CRE_DTTM | This field is populated with the date portion. |
CREATE_TIME_KEY | Create Time (Time Dimension Surrogate Key) | CI_TD_ENTRY.CRE_DTTM | This field is populated with the time portion. |
CREATE_DTTM | Create Date/Time | CI_TD_ENTRY.CRE_DTTM | |
CMPL_DATE_KEY | Complete Date (Date Dimension Surrogate Key) | CI_TD_ENTRY.COMPLETE_DTTM | This field is populated with the date portion. |
CMPL_TIME_KEY | Complete Time (Time Dimension Surrogate Key) | CI_TD_ENTRY.COMPLETE_DTTM | This field is populated with the time portion. |
CMPL_DTTM | Complete Date/Time | CI_TD_ENTRY.COMPLETE_DTTM | |
ASSIGN_DATE_KEY | Assign Date (Date Dimension Surrogate Key) | CI_TD_ENTRY.ASSIGNED_DTTM | This field is populated with the date portion. |
ASSIGN_TIME_KEY | Assign Time (Time Dimension Surrogate Key) | CI_TD_ENTRY.ASSIGNED_DTTM | This field is populated with the time portion. |
ASSIGN_DTTM | Assign Date/Time | CI_TD_ENTRY.ASSIGNED_DTTM | This field is populated with the last assigned date/time of the respective To-Do entry. |
OPEN_IND | Open Indicator | CI_TD_ENTRY.ENTRY_STATUS_FLG | This field is populated with “1” if the To Do entry is still ‘Open’. Else, it is populated with “0”. |
ASSIGN_IND | Assigned Indicator | CI_TD_ENTRY.ENTRY_STATUS_FLG | This field is populated with “1” if To Do entry is still ‘Assigned’. Else, it is populated with “0”. |
CMPL_IND | Completed Indicator | CI_TD_ENTRY.ENTRY_STATUS_FLG | This field is populated with “1” if To Do entry is still ‘Completed’. Else, it is populated with “0”. |
REL_TODOS_CNT | Number of Related To Dos | This field is populated with the count of other incomplete To Do entries that reference the same set of characteristics as the current To Do entry. | |
HOURS | Hours | CI_TD_ENTRY.CRE_ DTTM CI_TD_ENTRY.COMPLETE_DTTM | This field is populated with the number of hours between the Create Time and Completion Time. It is populated with “0” if the respective To Do is incomplete. |
OPEN_HOURS | Open Hours | CI_TD_LOG.LOG_ DTTM | This field is populated with the total number of hours the To Do entry was in “Open” state (not assigned). |
ASSIGN_HOURS | Assign Hours | CI_TD_LOG.LOG_ DTTM | This field is populated with the total number of hours the To Do entry was in “Being Worked On” state. It is populated with “0” if the respective To Do is still being worked on. It is populated with “0” if the respective To Do is still Open. |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
DATA_SOURCE_IND | Data Source Indicator | CI_INSTALLATION. ENV_ID | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |
To Do Entry
Accumulates all To-Do entries created in the source system. This fact captures metrics, such as the number hours it was open, assigned, total hours, and also the number of related To Dos.
Entity Relationship Diagram
Properties
Property | Value |
Target Table Name | CF_TD_ENTRY |
Table Type | Fact |
Fact Type | Accumulation |
Driver Table | CI_TD_ENTRY |
Stage Table Name | STG_CF_TD_ENTRY |
ODI Package Name | B1_PKG_CF_TD_ENTRY |
ETL View Name | B1_F_TD_ENTRY_VW |
Materialized View Name | B1_TD_ENTRY_DOW_MV1 B1_TD_ENTRY_DOW_MV2 B1_TD_ENTRY_MON_TOPX_MV1 |
Fields
Target Field | OAS Field | Source Field | Transformation Logic |
|---|---|---|---|
TD_ENTRY_KEY | To Do Dimension Surrogate Key | This field is populated with the sequence from OUBI_TD_ENTRY_SEQ. | |
SRC_TD_ENTRY_ID | To Do Entry ID (Natural Key) | CI_TD_ENTRY.TD_ ENTRY_ID | |
SA_KEY | Service Agreement Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the SA ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
PER_KEY | Person Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Person ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
ACCT_KEY | Account Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Account ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
PREM_KEY | Premise Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
ADDR_KEY | Address Dimension Surrogate Key | CI_TD_ENTRY_CHA.CHAR_VAL_FK1 | This field is populated with the Premise ID associated with the To Do Entry for characteristic type configured as part of the BI configuration in the source system. For details, see the BI-Oriented Master Configuration in the Oracle Utilities Customer Care & Billing (CCB) section (in the Configuring Oracle Utilities Analytics Warehouse chapter) in the Oracle Utilities Analytics Warehouse Installation and Configuration Guide. |
USER_KEY | User Dimension Surrogate Key | CI_TD_ENTRY.ASSIGNED_USER_ID | |
TD_TYPE_KEY | To Do Type Dimension Key | CI_TD_ENTRY.TD_TYPE_CD | |
TD_ROLE_KEY | To Do Role Dimension Key | CI_TD_ENTRY.ROLE_ID | |
TD_STATUS_KEY | To Do Status Dimension Key | CI_TD_ENTRY.ENTRY_STATUS_FLG | |
TD_PRIORITY_KEY | To Do Priority Dimension Key | CI_TD_ENTRY.TD_PRIORITY_FLG | |
TD_SKILL_KEY | To Do Skill Dimension Surrogate Key | CI_TD_TYPE_CHAR.ADHOC_CHAR_VAL CI_TD_TYPE_CHAR.CHAR_TYPE_CD CI_TD_TYPE_CHAR.CHAR_VAL | This field is populated based on the characteristic types configured on the To Do entry’s To Do type. Only those characteristic types configured as skills in the AQM feature configuration will be considered. If a To Do type has both regular skills and ad-hoc skills, then the ad-hoc skills will be given the priority. The first skill to match the message category/number on the To Do entry will be picked up. |
MSG_KEY | Message Dimension Surrogate Key | CI_TD_ENTRY.MESSAGE_CAT_NBR CI_TD_ENTRY.MESSAGE_NBR | |
TD_KEY | To Do Dimension Surrogate Key | CI_TD_ENTRY.TD_ ENTRY_ID | |
CREATE_DATE_KEY | Create Date (Date Dimension Surrogate Key) | CI_TD_ENTRY.CRE_ DTTM | This field is populated with the date portion. |
CREATE_TIME_KEY | Create Time (Time Dimension Surrogate Key) | CI_TD_ENTRY.CRE_ DTTM | This field is populated with the time portion. |
CREATE_DTTM | Create Date/Time | CI_TD_ENTRY.CRE_ DTTM | |
CMPL_DATE_KEY | Complete Date (Date Dimension Surrogate Key) | CI_TD_ENTRY.COMPLETE_DTTM | This field is populated with the date portion. |
CMPL_TIME_KEY | Complete Time (Time Dimension Surrogate Key) | CI_TD_ENTRY.COMPLETE_DTTM | This field is populated with the time portion. |
CMPL_DTTM | Complete Date/Time | CI_TD_ENTRY.COMPLETE_DTTM | |
ASSIGN_DATE_KEY | Assign Date (Date Dimension Surrogate Key) | CI_TD_ENTRY.ASSIGNED_DTTM | This field is populated with the date portion. |
ASSIGN_TIME_KEY | Assign Time (Time Dimension Surrogate Key) | CI_TD_ENTRY.ASSIGNED_DTTM | This field is populated with the time portion. |
ASSIGN_DTTM | Assign Date/Time | CI_TD_ENTRY.ASSIGNED_DTTM | This field is populated with the last assigned date/time of the respective To-Do entry. |
OPEN_IND | Open Indicator | CI_TD_ENTRY.ENTRY_STATUS_FLG | This field is populated with “1” if the To Do entry is still ‘Open’. Else, it is populated with “0”. |
ASSIGN_IND | Assigned Indicator | CI_TD_ENTRY.ENTRY_STATUS_FLG | This field is populated with “1” if To Do entry is still ‘Assigned’. Else, it is populated with “0”. |
CMPL_IND | Completed Indicator | CI_TD_ENTRY.ENTRY_STATUS_FLG | This field is populated with “1” if To Do entry is still ‘Completed’. Else, it is populated with “0”. |
REL_TODOS_CNT | Number of Related To Dos | This field is populated with the count of other incomplete To Do entries that reference the same set of characteristics as the current To Do entry. | |
HOURS | Hours | CI_TD_ENTRY.CRE_ DTTM CI_TD_ENTRY.COMPLETE_DTTM | This field is populated with the number of hours between the Create Time and Completion Time. It is populated with “0” if the respective To Do is incomplete. |
OPEN_HOURS | Open Hours | CI_TD_LOG.LOG_ DTTM | This field is populated with the total number of hours the To Do entry was in “Open” state (not assigned). |
ASSIGN_HOURS | Assign Hours | CI_TD_LOG.LOG_ DTTM | This field is populated with the total number of hours the To Do entry was in “Being Worked On” state. It is populated with “0” if the respective To Do is still being worked on. It is populated with “0” if the respective To Do is still Open. |
UDM1 | User Defined Measure 1 | ||
UDM2 | User Defined Measure 2 | ||
UDM3 | User Defined Measure 3 | ||
UDM4 | User Defined Measure 4 | ||
UDM5 | User Defined Measure 5 | ||
UDDGEN1 | User Defined Degenerate Dimension 1 | ||
UDDGEN2 | User Defined Degenerate Dimension 2 | ||
UDDGEN3 | User Defined Degenerate Dimension 3 | ||
UDDGEN4 | User Defined Degenerate Dimension 4 | ||
UDDGEN5 | User Defined Degenerate Dimension 5 | ||
UDDGEN6 | User Defined Degenerate Dimension 6 | ||
UDDGEN7 | User Defined Degenerate Dimension 7 | ||
UDDGEN8 | User Defined Degenerate Dimension 8 | ||
UDDGEN9 | User Defined Degenerate Dimension 9 | ||
UDDGEN10 | User Defined Degenerate Dimension 10 | ||
TD_ENTRY_UDD1_KEY | To Do Entry User Defined Dimension 1 Surrogate Key | ||
TD_ENTRY_UDD2_KEY | To Do Entry User Defined Dimension 2 Surrogate Key | ||
TD_ENTRY_UDD3_KEY | To Do Entry User Defined Dimension 3 Surrogate Key | ||
TD_ENTRY_UDD4_KEY | To Do Entry User Defined Dimension 4 Surrogate Key | ||
TD_ENTRY_UDD5_KEY | To Do Entry User Defined Dimension 5 Surrogate Key | ||
FACT_CNT | Fact Count | This field is populated with the standard value of “1”. | |
DATA_SOURCE_IND | Data Source Indicator | B1_PROD_INSTANCE.DSI | This field is populated with the DSI value on the source product instance configuration. This table is populated as part of the initial setup and the DSI value is extracted from the environment ID of the source system (CI_INSTALLATION.ENV_ID). |
JOB_NBR | Job Number | This field is populated with the ODI job execution session number. |