Dimension Tables
This section lists the dimension tables.
Account
Stores details about all accounts from the source system with all related attributes.
The following UDF columns are populated by the ETL process supplied with the product.
• UDF1: Customer Class
• UDF2: Account Management Group
• UDF3: CIS Division
• UDF4: Bill Cycle
• UDF5: Collection Class
Properties
Property
Value
Target Table
CD_ACCT
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_ACCT
Stage Table
STG_CD_ACCT
ODI Package
B1_PKG_CD_ACCT
ETL View
B1_D_ACCT_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
ACCT_KEY
Account Dimension Surrogate Key
 
Populated with the sequence from SPL_ACCT_SEQ.
SRC_ACCT_ID
Account ID
CI_ACCT.ACCT_ID
 
ACCT_INFO
Account
 
Populated with the primary name of main customer, customer class, and account ID.
UDF1_CD
Customer Class Code
CI_ACCT.CUST_CL_CD
 
UDF1_DESCR
Customer Class
CI_CUST_CL_L.DESCR
 
UDF2_CD
Account Management. Group Code
CI_ACCT.ACCT_MGMT_GRP_CD
 
UDF2_DESCR
Account Management. Group
CI_ACCT_MGMT_GR_L.DESCR
 
UDF3_CD
Division Code
CI_ACCT.CIS_DIVISION
 
UDF3_DESCR
Division
CI_CIS_DIVISION_L.DESCR
 
UDF4_CD
Bill Cycle Code
CI_ACCT.BILL_CYC_CD
 
UDF4_DESCR
Bill Cycle
CI_BILL_CYC_L.DESCR
 
UDF5_CD
Collection Class Code
CI_ACCT.COLL_CL_CD
 
UDF5_DESCR
Collection Class
CI_COLL_CL_L.DESCR
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
UDF1_NUM
User Defined Field 1 Number
 
 
UDF2_NUM
User Defined Field 2 Number
 
 
UDF3_NUM
User Defined Field 3 Number
 
 
UDF4_NUM
User Defined Field 4 Number
 
 
UDF5_NUM
User Defined Field 5 Number
 
 
UDF6_NUM
User Defined Field 6 Number
 
 
UDF7_NUM
User Defined Field 7 Number
 
 
UDF8_NUM
User Defined Field 8 Number
 
 
UDF9_NUM
User Defined Field 9 Number
 
 
UDF10_NUM
User Defined Field 10 Number
 
 
UDF1_DATE
User Defined Field 1 Date
 
 
UDF2_DATE
User Defined Field 2 Date
 
 
UDF3_DATE
User Defined Field 3 Date
 
 
UDF4_DATE
User Defined Field 4 Date
 
 
UDF5_DATE
User Defined Field 5 Date
 
 
UDF6_DATE
User Defined Field 6 Date
 
 
UDF7_DATE
User Defined Field 7 Date
 
 
UDF8_DATE
User Defined Field 8 Date
 
 
UDF9_DATE
User Defined Field 9 Date
 
 
UDF10_DATE
User Defined Field 10 Date
 
 
FLEXFIELD1
Flex Field 1
 
 
FLEXFIELD2
Flex Field 2
 
 
FLEXFIELD3
Flex Field 3
 
 
FLEXFIELD4
Flex Field 4
 
 
FLEXFIELD5
Flex Field 5
 
 
FLEXFIELD6
Flex Field 6
 
 
FLEXFIELD7
Flex Field 7
 
 
FLEXFIELD8
Flex Field 8
 
 
FLEXFIELD9
Flex Field 9
 
 
FLEXFIELD10
Flex 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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Address
Stores address related attributes from the premises defined in the source system.
The following UDF columns are populated by the ETL process supplied with Oracle Utilities Extractors and Schema.
• UDF1 - City
• UDF2 - County
• UDF3 - Postal
• UDF4 - State
• UDF5 - Country
• UDF6 - Geo Code
Properties
Property
Value
Target Table
CD_ADDR
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_PREM
Stage Table
STG_CD_ADDR
ODI Package
B1_PKG_CD_ADDR
ETL View
B1_D_ADDR_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
ADDR_KEY
Address Dimension Surrogate Key
 
This field is populated with the sequence from SPL_ADDR_SEQ.
SRC_ADDR_ID
CC&B Premise ID
CI_PREM.PREM_ID
 
ADDR_INFO
Address
CI_PREM.ADDRESS1
 
CI_PREM.CITY
 
CI_PREM.STATE
 
CI_PREM.POSTAL
This field is populated with the Address Line1, City, State, and Postal details.
ADDR_LINE1
CC&B Address Line 1
CI_PREM.ADDRESS1
 
ADDR_LINE2
CC&B Address Line 2
CI_PREM.ADDRESS2
 
ADDR_LINE3
CC&B Address Line 3
CI_PREM.ADDRESS3
 
ADDR_LINE4
CC&B Address Line 4
CI_PREM.ADDRESS4
 
CITY
City
CI_PREM.CITY_UPR
 
COUNTY
County
CI_PREM.COUNTY
 
POSTAL
Postal Code
CI_PREM.POSTAL
 
STATE_CD
State Code
CI_PREM.STATE
 
STATE_DESCR
State
CI_STATE_L.DESCR
 
COUNTRY_CD
Country Code
CI_PREM.COUNTRY
 
COUNTRY_DESCR
Country
CI_COUNTRY_L.
DESCR
 
GEO_CODE
Geographical Code
CI_PREM.GEO_CODE
 
CROSS_STREET
Cross Street
 
 
SUBURB
Suburb
 
 
UDF1_CD
City Code
CI_PREM.CITY_UPR
 
UDF1_DESCR
City
CI_PREM.CITY_UPR
 
UDF2_CD
County Code
CI_PREM.COUNTY
 
UDF2_DESCR
County
CI_PREM.COUNTY
 
UDF3_CD
Postal Code
CI_PREM.POSTAL
 
UDF3_DESCR
Postal Code
CI_PREM.POSTAL
 
UDF4_CD
State Code
CI_PREM.STATE
 
UDF4_DESCR
State
CI_PREM.DESCR
 
UDF5_CD
Country Code
CI_PREM.COUNTRY
 
UDF5_DESCR
Country
CI_COUNTRY_L.
DESCR
 
UDF6_CD
Geo Code Value
CI_PREM.GEO_CODE
 
UDF6_DESCR
Geo Code
CI_PREM.GEO_CODE
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
UDF16_CD
User Defined Field 16 Code
 
 
UDF16_DESCR
User Defined Field 16 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Adjustment Type
Stores various adjustment types defined in the system.
Properties
Property
Value
Target Table
CD_ADJ_TYPE
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_ADJ_TYPE
Stage Table
STG_CD_ADJ_TYPE
ODI Package
B1_PKG_CD_ADJ_TYPE
ETL View
B1_D_ADJ_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
ADJ_TYPE_KEY
Adjustment Type Key
 
This field is populated using the sequence from SPL_ADJ_TYPE_SEQ.
ADJ_TYPE_CD
Adjustment Type Code
CI_ADJ_TYPE.ADJ_
TYPE_CD
 
ADJ_TYPE_DESCR
Adjustment Type Description
CI_ADJ_TYPE_L.
DESCR
 
UDF1_CD
Account Payable Request Type Code
CI_ADJ_TYPE.AP_
REQ_TYPE_CD
 
UDF1_DESCR
Account Payable Request Type
CI_APREQ_TYPE_L.
DESCR
 
UDF2_CD
Distribution Code Value
CI_ADJ_TYPE.DST_
ID
 
UDF2_DESCR
Distribution Code
CI_DST_CODE_L.
DESCR
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
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).
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Bill Cancel Reason
Stores various possible reasons for bill cancellation.
Properties
Property
Value
Target Table
CD_BILL_CAN_RSN
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_BILL_CAN_RSN_L
Stage Table
STG_CD_BILL_CAN_RSN
ODI Package
B1_PKG_CD_BILL_CAN_RSN
ETL View
B1_D_BILL_CAN_RSN_VW
Fields
Target Field
OAS Field
Source Field
Transformation /
Calculation Logic
BILL_CAN_RSN_KEY
Bill Cancel Reason Dimension Surrogate Key
 
Transformation Logic:
This field is populated with the sequence from B1_BILL_CAN_RSN_SEQ.
BILL_CAN_RSN_CD
Bill Cancel Reason Code
CI_BILL_CAN_RSN_L.CAN_RSN_CD
 
BILL_CAN_RSN_DESCR
Description
CI_BILL_CAN_RSN_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
DATA_SOURCE_IND
Data Source Indicator
B1_PROD_INSTANCE.DSI
Transformation Logic:
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
Transformation Logic:
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
Transformation Logic:
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
Transformation Logic:
This field is populated with the ODI job execution session number.
 
Bill Cycle Schedule
Stores the bill cycle and its schedule information.
Properties
Property
Value
Target Table
CD_BILL_CYC_SCH
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_BILL_CYC_SCH
Stage Table
STG_CD_BILL_CYC_SCH
ODI Package
B1_PKG_CD_BILL_CYC_SCH
ETL View
B1_D_BILL_CYC_SCH_VW
Fields
Target Field
OAS Field
Source Field
Transformation /
Calculation Logic
BILL_CYC_SCH_KEY
Bill Cycle Schedule Dimension Surrogate Key
 
Transformation Logic:
This field is populated with the sequence from B1_BILL_CYC_SCH_SEQ.
BILL_CYC_CD
Bill Cycle
CI_BILL_CYC_SCH.
BILL_CYC_CD
 
BILL_CYC_DESCR
Bill Cycle Description
CI_BILL_CYC_L.
DESCR
 
BILL_CYC_WIN_START_DT
Window Start Date
CI_BILL_CYC_SCH.
WIN_START_DT
 
BILL_CYC_WIN_END_DT
Window End Date
CI_BILL_CYC_SCH.
WIN_END_DT
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Bill Segment Status
Stores the possible statuses of a bill segment.
Properties
Property
Value
Target Table
CD_BSEG_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_BSEG_STATUS
ODI Package
B1_PKG_CD_BSEG_STATUS
ETL View
B1_D_BSEG_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
BSEG_STATUS_KEY
Bill Segment Status Dimension Surrogate Key
 
This field is populated with the sequence from B1_BSEG_STATUS_SEQ.
BSEG_STATUS_CD
Bill Segment Status Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
 
BSEG_STATUS_
DESCR
Bill Segment Status Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Billing Day in Window
Stores the age ranges indicating the day of the bill window when the bill segment was frozen. These age ranges are configured in the source system.
Properties
Property
Value
Target Table
CD_BILL_DAY_IN_WIN
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
F1_BKT_CONFIG
Stage Table
STG_CD_BILL_DAY_IN_WIN
ODI Package
B1_PKG_CD_BILL_DAY_IN_WIN
ETL View
B1_D_BILL_DAY_IN_WIN_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
BILL_DAY_IN_WIN_KEY
Billing Day in Window Dimension Surrogate Key
 
This field is populated with the sequence from B1_BILL_DAY_IN_WIN_SEQ.
BILL_DAY_IN_WIN_DESCR
Billing Day in Window Description
F1_BKT_CONFIG_
VAL_L.DESCR
 
WIN_STATUS_CD
Window Status Code
F1_BKT_CONFIG.
BKT_TYPE_CD
 
WIN_STATUS_DESCR
Windows Status Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If the override description is not present, regular description is used.
WIN_CATEGORY_CD
Window Category Code
F1_BKT_CONFIG_VAL.BKT_VAL_TYPE_CD
 
WIN_CATEGORY_DESCR
Window Category Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
RANGE_START
Start Range
F1_BKT_CONFIG_VAL.BKT_START_RANGE
 
RANGE_END
End Range
F1_BKT_CONFIG_VAL.BKT_END_RANGE
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
Note: The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (SA Billing Fact in this case), should be truncated and reloaded.
For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Installation and Configuration Guide.
Campaign
Stores the campaign programs designed to offer a set of packages to customers.
Properties
Property
Value
Target Table
CD_CAMPAIGN
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_CAMPAIGN
Stage Table
STG_CD_CAMPAIGN
ODI Package
B1_PKG_CD_CAMPAIGN
ETL View
B1_D_CAMPAIGN_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
CAMPAIGN_KEY
Campaign Dimension Surrogate Key
 
This field is populated with the sequence from SPL_CAMPAIGN_SEQ.
CAMPAIGN_CD
Campaign Code
CI_CAMPAIGN.CAMPAIGN_CD
 
CAMPAIGN_DESCR
Description
CI_CAMPAIGN.
DESCR
 
UDF1_CD
Campaign Status Code
CI_CAMPAIGN.CAMP_STATUS_FLG
 
UDF1_DESCR
Campaign Status Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
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).
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Case Condition
Stores various possible states of a case.
Properties
Property
Value
Target Table
CD_CASE_COND
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_CASE_COND
ODI Package
B1_PKG_CD_CASE_COND
ETL View
B1_D_CASE_COND_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
CASE_COND_KEY
Case Condition Dimension Surrogate Key
 
This field is populated with the sequence from SPL_CASECOND_
SEQ.
CASE_COND_CD
Case Condition Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
 
CASE_COND_DESCR
Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description is extracted.
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Case Type Status
Stores various possible case types and their states.
Properties
Property
Value
Target Table
CD_CASETYPE_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_CASE_STATUS
Stage Table
STG_CD_CASETYPE_STATUS
ODI Package
B1_PKG_CD_CASETYPE_STATUS
ETL View
B1_D_CASETYPE_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
CASETY_STAT_KEY
Case Type Status Dimension Surrogate Key
 
This field is populated with the sequence from SPL_CASETY_STAT_SEQ.
CASE_TYPE_CD
Case Type Code
CI_CASE_TYPE.CASE_TYPE_CD
 
CASE_TYPE_DESCR
Case Type Description
CI_CASE_TYPE_L.DESCR
 
CASE_STATUS_CD
Case Status Code
CI_CASE_STATUS.CASE_STATUS_CD
 
CASE_STATUS_
DESCR
Case Status Description
CI_CASE_STATUS_L.STATUS_LBL
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Collectible Process Status
Stores various possible states of the collection process/overdue process defined in a lookup.
Properties
Property
Value
Target Table
CD_COLLPROC_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_COLLPROC_STATUS
ODI Package
B1_PKG_CD_COLLPROC_STATUS
ETL View
B1_D_COLLPROC_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
COLLPROC_STAT_
KEY
Collectible Process Status Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLPROC_STATUS_SEQ.
COLPROC_STAT_CD
Collectible Process Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
COLPROC_STAT_
DESCR
Collectible Process Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description is extracted.
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Collectible Process Template
Stores both collection process templates and overdue process templates from the source system.
Properties
Property
Value
Target Table
CD_COLLPROC_TMPL
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_COLL_PROC_TM/CI_OD_PROC_TMP
Stage Table
STG_CD_COLLPROC_TMPL
ODI Package
B1_PKG_CD_COLLPROC_TMPL
ETL View
B1_D_COLLPROC_TMPL_VW
Fields
Source 1 - Collection Process Template (CI_COLL_PROC_TM)
Target Field
OAS Field
Source Field
Transformation Logic
COLL_TMPL_KEY
Collection Process Template Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLTMPL_SEQ.
COLL_TMPL_CD
Collection Process Template Code
CI_COLL_PROC_TM.
COLL_PROC_TMPL_
CD
The collection process template retrieved is prefixed with ‘C_’.
COLL_TMPL_DESCR
Collection Process Template Description
CI_COLL_PROC_TM_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Source 2 - Overdue Process Template (CI_OD_PROC_TMP)
Target Field
OAS Field
Source Field
Transformation Logic
COLL_TMPL_KEY
Collection Process Template Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLTMPL_SEQ.
COLL_TMPL_CD
Collection Process Template Code
CI_OD_PROC_TMP.
OD_PROC_TMPL_CD
The template code retrieved is prefixed with ‘OD_’.
COLL_TMPL_DESCR
Collection Process Template Description
CI_OD_PROC_TMP_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Collection Event Type
Stores various types of collection events, cut events, severance events, and overdue events.
Properties
Property
Value
Target Table
CD_COLLEVT_TYP
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_COLL_EVT_TYP
CI_OD_EVT_TYPE
CI_CUT_EVT_TYPE
CI_SEV_EVT_TYPE
Stage Table
STG_CD_COLLEVT_TYP
ODI Package
B1_PKG_CD_COLLEVT_TYP
ETL View
B1_D_COLLEVT_TYP_VW
B1_D_ODEVT_TYP_VW
B1_D_CUTEVT_TYP_VW
B1_D_SEVEVT_TYP_VW
Fields
Source 1 - Collection Event Type (CI_COLL_EVT_TYP)
Target Field
OAS Field
Source Field
Transformation Logic
CEVT_TYPE_KEY
Collection Event Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.
CEVT_TYPE_CD
Collection Event Type Code
CI_COLL_EVT_TYP.COLL_EVT_TYP_CD
This field is populated with the collection event type code prefixed with ‘C_’.
CEVT_TYPE_DESCR
Collection Event Type Description
CI_COLL_EVT_TYP_L.DESCR
 
CEVT_TY_FLG_CD
Collection Event Type Flag Code
CI_COLL_EVT_TYP.COLL_EVT_TYPE_FLG
 
CEVT_TY_FLG_
DESCR
Collection Event Type Flag Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
CUST_EVT_CD
Customer Event Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
CUST_EVT_DESCR
Customer Event Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Source 2 - Overdue Event Type (CI_OD_EVT_TYPE)
Target Field
OAS Field
Source Field
Transformation Logic
CEVT_TYPE_KEY
Collection Event Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.
CEVT_TYPE_CD
Collection Event Type Code
CI_OD_EVT_TYPE.
OD_EVT_TYPE_CD
The Template Code from Oracle Utilities Customer Care and Billing is prefixed with “OD_”.
CEVT_TYPE_DESCR
Collection Event Type Description
CI_OD_EVT_TYPE_L.DESCR
 
CEVT_TY_FLG_CD
Collection Event Type Flag Code
 
 
CEVT_TY_FLG_
DESCR
Collection Event Type Flag Description
 
 
CUST_EVT_CD
Customer Event Code
 
 
CUST_EVT_DESCR
Customer Event Description
 
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Source 3 - Cut Event Type (CI_CUT_EVT_TYPE)
Target Field
OAS Field
Source Field
Transformation Logic
CEVT_TYPE_KEY
Collection Event Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.
CEVT_TYPE_CD
Collection Event Type Code
CI_CUT_EVT_TYPE.
CUT_EVT_TYPE_CD
The Template Code from Oracle Utilities Customer Care and Billing is prefixed with “CUT_”.
CEVT_TYPE_DESCR
Collection Event Type Description
CI_CUT_EVT_TYPE_L.DESCR
 
CEVT_TY_FLG_CD
Collection Event Type Flag Code
 
 
CEVT_TY_FLG_
DESCR
Collection Event Type Flag Description
 
 
CUST_EVT_CD
Customer Event Code
 
 
CUST_EVT_DESCR
Customer Event Description
 
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Source 4 - Severance Event Type (CI_SEV_EVT_TYPE)
Target Field
OAS Field
Source Field
Transformation Logic
CEVT_TYPE_KEY
Collection Event Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_COLLEVT_TY_SEQ.
CEVT_TYPE_CD
Collection Event Type Code
CI_SEV_EVT_TYPE.
SEV_EVT_TYPE_CD
The Template Code from Oracle Utilities Customer Care and Billing is prefixed with “S_”.
CEVT_TYPE_DESCR
Collection Event Type Description
CI_SEV_EVT_TYPE_L.DESCR
 
CEVT_TY_FLG_CD
Collection Event Type Flag Code
CI_SEV_EVT_TYPE.
SEV_EVT_TYPE_FLG
 
CEVT_TY_FLG_
DESCR
Collection Event Type Flag Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
CUST_EVT_CD
Customer Event Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
 
CUST_EVT_DESCR
Customer Event Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Customer Contact Type
Stores various types of customer contacts.
Properties
Property
Value
Target Table
CD_CC_TYPE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_CC_TYPE
Stage Table
STG_CD_CC_TYPE
ODI Package
B1_PKG_CD_CC_TYPE
ETL View
B1_D_CC_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
CC_TYPE_KEY
Customer Contact Dimension Surrogate key
 
This field is populated with the sequence from SPL_CC_TYPE_SEQ.
CC_TYPE_CD
Customer Contact Type Code
CI_CC_TYPE.CC_
TYPE_CD
 
CC_TYPE_DESCR
Contact Type
CI_CC_TYPE_L.
DESCR
 
CC_CL_CD
Customer Contact Class Code
CI_CC_CL_L.CC_CL_
CD
 
CC_CL_DESCR
Contact Class
CI_CC_CL_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Date
Holds the date information. It is unique in the sense that it is populated by an ODI package, based on the variables configured in the package.
Properties
Property
Value
Target Table
CD_DATE
Table Type
Dimension
SCD Type
n/a
Source System Driver Table
n/a
Stage Table
STG_CD_DATE
ODI Package
B1_PKG_CD_DATE
ETL View
n/a
Fields
Target Field
OAS Field
Source Field
Transformation Logic
DATE_KEY
 
 
 
CAL_DT
Calendar Date
 
 
DAY_NBR_IN_
MONTH
Day in Calendar Month(1-31)
 
 
DAY_NBR_IN_WEEK
Day Number in Week (1-7)
 
 
DAY_NBR_IN_YEAR
Day Number in Year (1-366)
 
 
WORK_DAY_IND
Work Day Indicator
 
 
ABS_MONTH_NBR
Absolute Month Number
 
 
CAL_MONTH_NBR
Calendar Month Number (1-12)
 
 
MONTH_END_DT
Month End Date
 
 
ABS_QTR_NBR
Absolute Quarter Number
 
 
CAL_QTR_NBR
Calendar Quarter Number (1-4)
 
 
QTR_END_DT
Quarter End Date
 
 
ABS_WEEK_NBR
Absolute Week Number
 
 
CAL_WEEK_NBR
Calendar Week Number (1-53)
 
 
WEEK_END_DT
Week End Date
 
 
CAL_YEAR
Calendar Year
 
 
YEAR_END_DT
Year End Date
 
 
UDF1_CD
Day of Week Code
 
 
UDF1_DESCR
Day of Week
 
 
UDF2_CD
Calendar Quarter Code
 
 
UDF2_DESCR
Calendar Quarter
 
 
UDF3_CD
Season Code
 
 
UDF3_DESCR
Season
 
 
UDF4_CD
Workday Code
 
 
UDF4_DESCR
Workday
 
 
UDF5_CD
Calendar Month Code
 
 
UDF5_DESCR
Calendar Month
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
Days of Unbilled Usage
Stores the age ranges indicating the number of days that a service agreement should have been actually billed for, but is not. These age ranges are configured in the source system.
Properties
Property
Value
Target Table
CD_DAYS_UNBILLED_USG
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
F1_BKT_CONFIG
Stage Table
STG_CD_DAYS_UNBILLED_USG
ODI Package
B1_PKG_CD_DAYS_UNBILLED_USG
ETL View
B1_D_DAYS_UNBILLED_USG_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
DAYS_UNBILLED_
USG_KEY
Days of Unbilled Usage Dimension Surrogate Key
 
This field is populated with the sequence from B1_DAYS_UNBILLED_USG_SEQ.
DAYS_UNBILLED_USG_DESCR
Days of Unbilled Usage Description
F1_BKT_CONFIG_VAL_L.DESCR
 
RANGE_START
Start Range
F1_BKT_CONFIG_VAL.BKT_START_RANGE
 
RANGE_END
End Range
F1_BKT_CONFIG_VAL.BKT_END_RANGE
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Note: The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Arrears Snapshot Fact in this case), should be truncated and reloaded.
For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
 
Days Since Last Frozen BS
Stores the age ranges indicating the number of days since a service agreement had a frozen bill segment. These age ranges are configured in the source system.
Properties
Property
Value
Target Table
CD_DAYS_LAST_FRZ_BS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
F1_BKT_CONFIG
Stage Table
STG_CD_DAYS_LAST_FRZ_BS
ODI Package
B1_PKG_CD_DAYS_LAST_FRZ_BS
ETL View
B1_D_DAYS_LAST_FRZ_BS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
DAYS_LAST_FRZ_BS_KEY
Days Since Last Frozen Dimension Surrogate Key
 
This field is populated with the sequence from B1_DAYS_LAST_FRZ_BS_SEQ.
DAYS_LAST_FRZ_BS_DESCR
Days Since Last Frozen BS Bucket Description
F1_BKT_CONFIG_VAL_L.DESCR
 
RANGE_START
Start Range
F1_BKT_CONFIG_VAL.BKT_START_
RANGE
 
RANGE_END
End Range
F1_BKT_CONFIG_VAL.BKT_END_RANGE
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
Note: The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Arrears Snapshot Fact in this case), should be truncated and reloaded.
For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
 
Days to Window Closure
Stores the age ranges indicating the number of days left before bill window closure. These age ranges are configured in the source system.
Properties
Property
Value
Target Table
CD_DAYS_TO_WIN_CLS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
F1_BKT_CONFIG
Stage Table
STG_CD_DAYS_TO_WIN_CLS
ODI Package
B1_PKG_CD_DAYS_TO_WIN_CLS
ETL View
B1_D_DAYS_TO_WIN_CLS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
DAYS_TO_WIN_CLS_KEY
Days to Window Closure Dimension Surrogate Key
 
This field is populated with the sequence from B1_DAYS_TO_WIN_CLS_SEQ.
DAYS_TO_WIN_CLS_DESCR
Days to Window Closure Bucket Description
F1_BKT_CONFIG_VAL_L.DESCR
 
WIN_CLOSURE_
STATE_CD
Window Closure State Code
F1_BKT_CONFIG.BKT_TYPE_CD
 
WIN_CLOSURE_
STATE_DESCR
Window Closure State Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
RANGE_START
Start Range
F1_BKT_CONFIG_VAL.BKT_START_RANGE
 
RANGE_END
End Range
F1_BKT_CONFIG_VAL.BKT_END_RANGE
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Note: The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (SA Billing Fact in this case), should be truncated and reloaded.
For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
 
Fiscal Period
Stores the accounting periods defined in the source system.
Properties
Property
Value
Target Table
CD_FISCAL_CAL
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_CAL_PERIOD
Stage Table
STG_CD_FISCAL_CAL
ODI Package
B1_PKG_CD_FISCAL_CAL
ETL View
B1_D_FISCAL_CAL_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
FISCAL_CAL_KEY
Fiscal Period Dimension Surrogate Key
 
This field is populated with the sequence from SPL_FISCAL_SEQ.
FISCAL_CAL_CD
Fiscal Calendar Code
CI_CAL_PERIOD.CALENDAR_ID
 
FISCAL_CAL_DESCR
Fiscal Calendar Description
CI_CAL_GL_L.DESCR
 
ABS_PERIOD_NBR
Absolute Period Number
 
This field increments the sequence for each period within the calendar code.
 
For example: The first period in the calendar code C1 starts with 1.
FISCAL_YEAR
Fiscal Year
CI_CAL_PERIOD.FISCAL_YEAR
 
PERIOD_NBR
Fiscal Period Code
CI_CAL_PERIOD.ACCOUNTING_PERIOD
 
PERIOD_DESCR
Fiscal Period Description
CI_CAL_PERIOD_L.
PERIOD_DESCR
 
PERIOD_START_DT
Fiscal Period Start Date
CI_CAL_PERIOD.
BEGIN_DT
 
PERIOD_END_DT
Fiscal Period End Date
CI_CAL_PERIOD.END_DT
 
UDF1_CD
Fiscal Quarter Number Code
 
 
UDF1_DESCR
Fiscal Quarter Number Description
 
 
UDF2_CD
Fiscal Quarter Code
 
 
UDF2_DESCR
Fiscal Quarter Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Financial Transaction Type
Stores the financial transaction types defined in a standard lookup in the source system.
Properties
Property
Value
Target Table
CD_FT_TYPE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_FT_TYPE
ODI Package
B1_PKG_CD_FT_TYPE
ETL View
B1_D_FT_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
FT_TYPE_KEY
FT Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_FT_TYPE_SEQ.
FT_TYPE_CD
FT Type Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
FT_TYPE_DESCR
Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description is extracted.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
General Ledger
Stores the general ledger account details.
Properties
Property
Value
Target Table
CD_GL_ACCT
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_FT_GL
Stage Table
STG_CD_GL_ACCT
ODI Package
B1_PKG_CD_GL_ACCT
ETL View
B1_D_GL_ACCT_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
GL_ACCT_KEY
GL Account Dimension Surrogate Key
 
This field is populated with the sequence from SPL_GL_ACCT_SEQ.
SRC_GL_ACCT_ID
General Ledger Account ID (Natural Key)
CI_FT_GL.GL_ACCT
This field fetches the distinct non-blank general ledger accounts and trims any extra spaces.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Installments Count
Stores the age ranges indicating the installment counts for payment arrangements. These age ranges are configured in the source system.
Properties
Property
Value
Target Table
CD_INSTALLMENT_CNT
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
F1_BKT_CONFIG
Stage Table
STG_CD_INSTALLMENT_CNT
ODI Package
B1_PKG_CD_INSTALLMENT_CNT
ETL View
B1_D_INSTALLMENT_CNT_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
INSTALLMENT_CNT_KEY
Installments Count Dimension Surrogate Key
 
This field is populated with the sequence from B1_INSTALLMENT_CNT_SEQ.
INSTALLMENT_CNT_DESCR
Installments Count Description
F1_BKT_CONFIG_VAL_L.DESCR
 
F1_BKT_CONFIG.BKT_TYPE_CD
 
RANGE_START
Start Range
F1_BKT_CONFIG_VAL.BKT_START_RANGE
 
RANGE_END
End Range
F1_BKT_CONFIG_VAL.BKT_END_RANGE
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Note: The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run are not captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Payment Arrangement Accumulation/ Snapshot Facts in this case), should be truncated and reloaded.
For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
 
Measurement Type
Stores the type of usage (scalar, interval, both, or N/A) for the bill segment.
Properties
Property
Value
Target Table
CD_MSRMT_TYPE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_MSRMT_TYPE
ODI Package
B1_PKG_CD_MSRMT_TYPE
ETL View
B1_D_MSRMT_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
MSRMT_TYPE_KEY
Measurement Type Dimension Surrogate Key
 
This field is populated with the sequence from B1_MSRMT_TYPE_
SEQ.
MSRMT_TYPE_CD
Measurement Type Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
MSRMT_TYPE_
DESCR
Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Order Cancel Reason
Stores the possible reasons for an order cancellation.
Properties
Property
Value
Target Table
CD_ORDER_CAN_RSN
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_ENRL_CAN_RSN_L
Stage Table
STG_CD_ORDER_CAN_RSN
ODI Package
B1_PKG_CD_ORDER_CAN_RSN
ETL View
B1_D_ORDER_CAN_RSN_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
ORDER_CAN_RSN_
KEY
Order Cancel Reason Dimension Surrogate Key
 
This field is populated with the sequence from SPL_ORDER_CAN_RSN_SEQ.
ORDER_CAN_RSN_
CD
Order Cancel Reason Code
CI_ENRL_CAN_RSN_L.ENRL_CAN_RSN_
CD
 
ORDER_CAN_RSN_
DESCR
Description
CI_ENRL_CAN_RSN_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Order Status
Stores the possible states of an enrollment order.
Properties
Property
Value
Target Table
CD_ORDER_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_ORDER_STATUS
ODI Package
B1_PKG_CD_ORDER_STATUS
ETL View
B1_D_ORDER_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
ORDER_STATUS_
KEY
Order Status Dimension Surrogate Key
 
This field populates the sequence from SPL_ORDER_STATUS_SEQ.
ORDER_STATUS_CD
Order Status Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
ORDER_STATUS_
DESCR
Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description is extracted.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Package
Stores information about the goods and services offered to a customer or a prospect.
Properties
Property
Value
Target Table
CD_PKG
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_PKG_L
Stage Table
STG_CD_PKG
ODI Package
B1_PKG_CD_PKG
ETL View
B1_D_PKG_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PKG_KEY
Package Dimension Surrogate Key
 
This field is populated with the sequence from SPL_PKG_SEQ.
PKG_CD
Package Code
CI_PKG_L.PACKAGE_ID
 
PKG_DESCR
Description
CI_PKG_L.DESCR
This field’s description is based on CI_PKG_L.PACKAGE_ID.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Pay Method
Stores various methods of payment.
Properties
Property
Value
Target Table
CD_PAY_METHOD
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_PAY_METH
Stage Table
STG_CD_PAY_METHOD
ODI Package
B1_PKG_CD_PAY_METHOD
ETL View
B1_D_PAY_METHOD_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PAY_METHOD_KEY
Pay Method Dimension Surrogate Key
 
This field is populated with the sequence from B1_PAY_METH_SEQ.
PAY_METHOD_CD
Pay Method Code
CI_PAY_METH.PAY_
METH_CD
 
PAY_METHOD_
DESCR
Description
CI_PAY_METH_L.DESCR
 
APAY_IND
Auto Pay Indicator
CI_PAY_METH.APAY_SW
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Pay Plan Status
Stores all possible statuses of a pay plan.
Properties
Property
Value
Target Table
CD_PAY_PLAN_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_PAY_PLAN_STATUS
ODI Package
B1_PKG_CD_PAY_PLAN_STATUS
ETL View
B1_D_PAY_PLAN_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PAY_PLAN_STATUS_KEY
Pay Plan Status Dimension Surrogate Key
 
This field is populated with the sequence from B1_PAY_PLAN_STATUS_SEQ.
PAY_PLAN_STATUS_CD
Pay Plan Status Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
 
PAY_PLAN_STATUS_DESCR
Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Pay Plan Type
Stores the information about types of pay plans and their respective debt class.
Properties
Property
Value
Target Table
CD_PAY_PLAN_TYPE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_PP_TYPE
Stage Table
STG_CD_PAY_PLAN_TYPE
ODI Package
B1_PKG_CD_PAY_PLAN_TYPE
ETL View
B1_D_PAY_PLAN_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PAY_PLAN_TYPE_
KEY
Play Plan Type Dimension Surrogate Key
 
This field populates the sequence from B1_PAY_PLAN_TYPE_SEQ.
PAY_PLAN_TYPE_CD
Play Plan Type Code
CI_PP_TYPE.PP_TYPE_CD
 
PAY_PLAN_TYPE_
DESCR
Description
CI_PP_TYPE_L.
DESCR
 
DEBT_CL_CD
Debt class Code
CI_PP_TYPE.DEBT_
CL_CD
 
DEBT_CL_DESCR
Debt Class Description
CI_DEBT_CL_L.
DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Payment Arrangement Status
Stores various possible states of a payment arrangement.
Properties
Property
Value
Target Table
CD_PA_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_PA_STATUS
ODI Package
B1_PKG_CD_PA_STATUS
ETL View
B1_D_PA_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PA_STATUS_KEY
Payment Arrangement Status Dimension Surrogate Key
 
This field is populated by the sequence from B1_PA_STATUS_SEQ.
PA_STATUS_CD
Payment Arrangement Status Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
 
PA_STATUS_DESCR
Payment Arrangement Status Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Payment Cancel Reason
Stores various possible reasons for payment cancellation.
Properties
Property
Value
Target Table
CD_PAY_CAN_RSN
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_PAY_CAN_RSN
Stage Table
STG_CD_PAY_CAN_RSN
ODI Package
B1_PKG_CD_PAY_CAN_RSN
ETL View
B1_D_PAY_CAN_RSN_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PAY_CAN_RSN_KEY
Payment Cancel Reason Surrogate Key
 
This field is populated with the sequence from SPL_PAY_CAN_RSN_SEQ.
PAY_CAN_RSN_CD
Payment Cancel Reason Code
CI_PAY_CAN_RSN.CAN_RSN_CD
 
PAY_CAN_RSN_
DESCR
Description
CI_PAY_CAN_RSN_L.DESCR
 
UDF1_CD
NSF Charge Flag Code
CI_PAY_CAN_RSN.NSF_CHARGE_SW
 
UDF1_DESCR
NSF Charge Flag
CI_MSG_L.MESSAGE_TEXT_OVRD
 
CI_MSG_L.MESSAGE_TEXT
This field is retrieved based on the NSF charge switch CI_PAY_CAN_RSN.NSF_CHARGE_SW.
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
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).
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Rate
Stores the rate component information from the source system.
Properties
Property
Value
Target Table
CD_RATE
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_RS
Stage Table
STG_CD_RATE
ODI Package
B1_PKG_CD_RATE
ETL View
B1_D_RATE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
RATE_KEY
Rate Dimension Surrogate Key
 
This field is populated with the sequence from SPL_RATE_SEQ.
RATE_SCHED_CD
Rate Code
CI_RS.RS_CD
 
RATE_SCHED_
DESCR
Rate Code Description
CI_RS_L.DESCR
 
UDF1_CD
Service Type Code
CI_RS.SVC_TYPE_CD
 
UDF1_DESCR
Service Type Description
CI_SVC_TYP_L.
DESCR
 
UDF2_CD
Frequency Code
CI_RS.FREQ_CD
 
UDF2_DESCR
Frequency Description
CI_FREQ_L.DESCR
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
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).
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Recurring Charge Amounts
Stores the age ranges indicating the recurring charge amount ranges of payment arrangement. These age ranges are configured in the source system.
Properties
Property
Value
Target Table
CD_REC_CHARGE_AMOUNT
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
F1_BKT_CONFIG
Stage Table
STG_CD_REC_CHARGE_AMOUNT
ODI Package
B1_PKG_CD_REC_CHARGE_AMOUNT
ETL View
B1_D_REC_CHARGE_AMOUNT_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
REC_CHARGE_AMOUNT_KEY
Recurring Charge Amount Dimension Surrogate Key
 
This field is populated with the sequence from B1_REC_CHARGE_AMOUNT_SEQ.
REC_CHARGE_AMOUNT_DESCR
Recurring Charge Amount Description
F1_BKT_CONFIG_VAL_L.DESCR
 
F1_BKT_CONFIG.BKT_TYPE_CD
 
RANGE_START
Start Range
F1_BKT_CONFIG_VAL.BKT_START_RANGE
 
RANGE_END
End Range
F1_BKT_CONFIG_VAL.BKT_END_RANGE
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
Note: The bucket dimension ELT job is configured to be initial load only. Any incremental changes to these buckets after the initial run will not be captured in the data warehouse. However, if there arises a need to reconfigure the buckets, then data should be truncated in the star schema tables and reloaded to reflect the changes. The bucket dimensions, along with the associated facts (Payment Arrangement Accumulation/ Snapshot Facts in this case), should be truncated and reloaded.
For details about reloading the data, see the Data Reload section in Oracle Utilities Analytics Warehouse Installation and Configuration Guide.
 
Service Agreement Status
Stores various possible statuses of the service agreement defined in a lookup.
Properties
Property
Value
Target Table
CD_SA_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_SA_STATUS
ODI Package
B1_PKG_CD_SA_STATUS
ETL View
B1_D_SA_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
SA_STATUS_KEY
Service Agreement Status Dimension Surrogate Key
 
This field is populated with the sequence from SPL_SA_STATUS_SEQ.
SA_STATUS_CD
Service Agreement Status Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
 
SA_STATUS_DESCR
Service Agreement Status Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Service Quantity Identifier
Stores the service quantity identifiers defined in the source system.
Properties
Property
Value
Target Table
CD_SQI
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_SQI_L
Stage Table
STG_CD_SQI
ODI Package
B1_PKG_CD_SQI
ETL View
B1_D_SQI_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
SQI_KEY
Service Quantity Identifier Dimension Surrogate Key
 
This field is populated with the sequence from SPL_SQI_SEQ.
SQI_CD
Service Quantity Identifier Code
CI_SQI_L.SQI_CD
 
SQI_DESCR
Service Quantity Identifier Description
CI_SQI_L.DESCR
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Tender Source
Stores the details of various tender sources.
Properties
Property
Value
Target Table
CD_TNDR_SRCE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_TNDR_SRCE
Stage Table
STG_CD_TNDR_SRCE
ODI Package
B1_PKG_CD_TNDR_SRCE
ETL View
B1_D_TNDR_SRCE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TNDR_SRCE_KEY
Tender Source Dimension Surrogate Key
 
This field is populated with the sequence from SPL_TNDR_SRCE_
SEQ.
TNDR_SOURCE_CD
Tender Source Code
CI_TNDR_SRCE.TNDR_SOURCE_CD
 
TNDR_SOURCE_DESCR
Description
CI_TNDR_SRCE_L.DESCR
 
TNDR_SOURCE_TYPE_CD
Tender Source Type Code
CI_TNDR_SRCE.TNDR_SRCE_TYPE_FLG
 
TNDR_SOURCE_TYPE_DESCR
Tender Source Type Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Tender Status
Stores the various pay tender statuses.
Properties
Property
Value
Target Table
CD_TNDR_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_TNDR_STATUS
ODI Package
B1_PKG_CD_TNDR_STATUS
ETL View
B1_D_TNDR_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TNDR_STATUS_KEY
Tender Status Dimension Surrogate Key
 
This field is populated with the sequence from SPL_TNDR_STATUS_SEQ.
TNDR_STATUS_CD
Tender Status Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
TNDR_STATUS_
DESCR
Description
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_LOOKUP_VAL_L.DESCR
If an override description is not available, regular description is extracted.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Tender Type
Stores the various tender types.
Properties
Property
Value
Target Table
CD_TNDR_TYPE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_TENDER_TYPE_L
Stage Table
STG_CD_TNDR_TYPE
ODI Package
B1_PKG_CD_TNDR_TYPE
ETL View
B1_D_TNDR_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TNDR_TYPE_KEY
Tender Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_TNDR_TYPE_
SEQ.
TNDR_TYPE_CD
Tender Type Code
CI_TENDER_TYPE_L.TENDER_TYPE_CD
 
TNDR_TYPE_DESCR
Description
CI_TENDER_TYPE_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Time
Holds the time details. It is unique in the sense that it is populated by an ODI package, based on the variables configured in the package.
Properties
Property
Value
Target Table
CD_TIME
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
n/a
Stage Table
STG_CD_TIME
ODI Package
B1_PKG_CD_TIME
ETL View
n/a
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TIME_KEY
Time Dimension Surrogate Key
 
This field is populated with the sequence from SPL_TIME_SEQ.
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
SRC_TIME
Time (Natural Key)
 
 
AM_IND
AM Indicator
 
 
HOUR
Hour
 
 
MINUTE
Minute
 
 
SECOND
Second
 
 
UDF1_CD
Hour Code
 
 
UDF1_DESCR
Hour
 
 
UDF2_CD
15 Minute Interval Code
 
 
UDF2_DESCR
15 Minute Interval
 
 
UDF3_CD
5 Minute Interval Code
 
 
UDF3_DESCR
5 Minute Interval
 
 
UDF4_CD
Time of Day Code
 
 
UDF4_DESCR
Time of Day
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
 
Time of Use
Stores the information about various times of use defined in the source system.
Properties
Property
Value
Target Table
CD_TOU
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_TOU
Stage Table
STG_CD_TOU
ODI Package
B1_PKG_CD_TOU
ETL View
B1_D_TOU_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TOU_KEY
Time of Use Dimension Surrogate Key
 
This field is populated with the sequence from SPL_TOU_SEQ.
TOU_CD
Time of Use Code
CI_TOU_L.TOU_CD
 
TOU_DESCR
Time of Use Description
CI_TOU_L.DESCR
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Uncollectible Event Type
Stores the types of write-off events as defined in the source system.
Properties
Property
Value
Target Table
CD_UCOLEVT_TYPE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_WO_EVT_TYP
Stage Table
STG_CD_UCOLEVT_TYPE
ODI Package
B1_PKG_CD_UCOLEVT_TYPE
ETL View
B1_D_UCOLEVT_TYPE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
UCEVT_TYPE_KEY
Uncollection Event Type Dimension Surrogate Key
 
This field is populated with the sequence from SPL_UCEVT_TYPE_SEQ.
UCEVT_TYPE_CD
Uncollection Event Type Code
CI_WO_EVT_TYP.WO_EVT_TYP_CD
 
UCEVT_TYPE_DESCR
Uncollection Event Type Description
CI_WO_EVT_TYP_L.DESCR
 
UCEVT_TY_FLG_CD
Uncollection Event Type Flag Code
CI_WO_EVT_TYP.WO_EVT_TYPE_FLG
 
UCEVT_TY_FLG_
DESCR
Collection Event Type Flag Description
CI_LOOKUP_VAL_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Uncollectible Process Status
Stores various possible states of a write-off process in the source system.
Properties
Property
Value
Target Table
CD_UCOLPROC_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_UCOLPROC_STATUS
ODI Package
B1_PKG_CD_UCOLPROC_STATUS
ETL View
B1_D_UCOLPROC_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
UCPROC_STAT_KEY
Uncollectible Process Status Dimension Surrogate Key
 
This field is populated with the sequence from SPL_UCOLPROC_STATUS_SEQ.
UCPROC_STAT_CD
Uncollectible Process Code
CI_LOOKUP_VAL_L.FIELD_VALUE
 
UCPROC_STAT_
DESCR
Uncollectible Process Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description is extracted.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Uncollectible Process Template
Stores the write-off process template details from the source system.
Properties
Property
Value
Target Table
CD_UCOLPROC_TMPL
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_WO_PROC_TMPL
Stage Table
STG_CD_UCOLPROC_TMPL
ODI Package
B1_PKG_CD_UCOLPROC_TMPL
ETL View
B1_D_UCOLPROC_TMPL_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
UCPROC_TMPL_KEY
Uncollection Process Template Dimension Surrogate Key
 
This field is populated with the sequence from SPL_UNCOLLTMPL_SEQ.
UCPROC_TMPL_CD
Uncollection Process Template Code
CI_WO_PROC_TMPL.WO_PROC_TMPL_CD
 
UCPROC_TMPL_
DESCR
Uncollection Process Template Description
CI_WO_PROC_TMPL_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
EFF_START_DTTM
Effective Start Date/Time
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Unit of Measure
Stores various units of measure defined in the source system.
Properties
Property
Value
Target Table
CD_UOM
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_UOM
Stage Table
STG_CD_UOM
ODI Package
B1_PKG_CD_UOM
ETL View
B1_D_UOM_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
UOM_KEY
Unit of Measure Dimension Surrogate Key
 
This field is populated with the sequence from SPL_UOM_SEQ.
UOM_CD
Unit of Measure Code
CI_UOM.UOM_CD
 
UOM_DESCR
Unit of Measure Description
CI_UOM_L.DESCR
 
MEAS_PEAK_IND
Measure Peak Indicator
CI_UOM.MSR_PEAK_QTY_SW
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Message
Stores all messages and message category details as defined in the source system.
Properties
Property
Value
Target Table
CD_MSG
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_MSG_L
Stage Table
STG_CD_MSG
ODI Package
B1_PKG_CD_MSG
ETL View
B1_D_MSG_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
MSG_KEY
Message Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_MSG_SEQ.
MSG_CD
Message Code (Natural Key)
CI_MSG_L.MESSAGE_CAT_NBR
 
CI_MSG_L.MESSAGE_NBR
The message category and number are converted to be 5 digit wide by prefixing with zeros when they are short and then concatenated. This ensures the uniqueness of the concatenated value.
MSG_DESCR
Message Description
CI_MSG_L.MESSAGE_TEXT
 
CI_MSG_L.MESSAGE_TEXT_OVRD
If an override description is not available, regular description will be used.
MSG_TYPE_CD
Message Type Code
CI_MSG_L.MESSAGE_CAT_NBR
 
MSG_TYPE_DESCR
Message Type Description
CI_MSG_CATEGORY_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDL1_CD
User Defined Language 1 Code
 
 
UDL1_DESCR
User Defined Language 1 Description
 
 
UDL2_CD
User Defined Language 2 Code
 
 
UDL2_DESCR
User Defined Language 2 Description
 
 
UDL3_CD
User Defined Language 3 Code
 
 
UDL3_DESCR
User Defined Language 3 Description
 
 
UDL4_CD
User Defined Language 4 Code
 
 
UDL4_DESCR
User Defined Language 4 Description
 
 
UDL5_CD
User Defined Language 5 Code
 
 
UDL5_DESCR
User Defined Language 5 Description
 
 
COMMENT1
Comments 1
 
 
COMMENT2
Comments 2
 
 
COMMENT3
Comments 3
 
 
COMMENT4
Comments 4
 
 
COMMENT5
Comments 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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Note:
• The fields CROSS_STREET and SUBURB are not populated in the dimension for the Oracle Utilities Customer care and Billing source system. Address is a shared dimension and these fields are populated by other source systems.
• In the previous releases (v2.4.1 or earlier), UDF fields were populated with the City, County, Postal, State, Country, and Geo Code information. At a later release, named columns have been introduced in the Address dimension for these attributes. But, the base product ETL processes still populate these details in the UDF fields, as well for backward compatibility purposes.
 
Person
Stores person related attributes from the source system.
Properties
Property
Value
Target Table
CD_PER
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_PER
Stage Table
STG_CD_PER
ODI Package
B1_PKG_CD_PER
ETL View
B1_D_PER_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PER_KEY
Person Dimension Surrogate Key
 
This field is populated with the sequence from SPL_PER_SEQ.
SRC_PER_ID
Person ID (Natural Key)
CI_PER.PER_ID
 
PER_INFO
Customer Information
CI_PER_NAME.ENTITY_NAME
This field is populated with the primary name of the customer.
PER_NAME
Customer Name
CI_PER_NAME.ENTITY_NAME
This field is populated with the primary name of the customer.
PER_PHONE_NBR
Customer Phone Number
CI_PER_PHONE.PHONE
 
CI_PER_PHONE.EXTENSION
This field is populated with the primary phone number of the customer, which is concatenated as Phone 'x' Extension.
BUSINESS_IND
Person/Business Indicator
CI_PER.PER_OR_BUS_FLG
This field is populated with ‘1’ when the person flag is “B”. It is populated with ‘0’ when the person flag is “P”.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
UDF1_NUM
User Defined Field 1 Number
 
 
UDF2_NUM
User Defined Field 2 Number
 
 
UDF3_NUM
User Defined Field 3 Number
 
 
UDF4_NUM
User Defined Field 4 Number
 
 
UDF5_NUM
User Defined Field 5 Number
 
 
UDF6_NUM
User Defined Field 6 Number
 
 
UDF7_NUM
User Defined Field 7 Number
 
 
UDF8_NUM
User Defined Field 8 Number
 
 
UDF9_NUM
User Defined Field 9 Number
 
 
UDF10_NUM
User Defined Field 10 Number
 
 
UDF1_DATE
User Defined Field 1 Date
 
 
UDF2_DATE
User Defined Field 2 Date
 
 
UDF3_DATE
User Defined Field 3 Date
 
 
UDF4_DATE
User Defined Field 4 Date
 
 
UDF5_DATE
User Defined Field 5 Date
 
 
UDF6_DATE
User Defined Field 6 Date
 
 
UDF7_DATE
User Defined Field 7 Date
 
 
UDF8_DATE
User Defined Field 8 Date
 
 
UDF9_DATE
User Defined Field 9 Date
 
 
UDF10_DATE
User Defined Field 10 Date
 
 
FLEXFIELD1
Flex Field 1
 
 
FLEXFIELD2
Flex Field 2
 
 
FLEXFIELD3
Flex Field 3
 
 
FLEXFIELD4
Flex Field 4
 
 
FLEXFIELD5
Flex Field 5
 
 
FLEXFIELD6
Flex Field 6
 
 
FLEXFIELD7
Flex Field 7
 
 
FLEXFIELD8
Flex Field 8
 
 
FLEXFIELD9
Flex Field 9
 
 
FLEXFIELD10
Flex 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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Premise
Stores premise related attributes from the premises defined in the source system.
The following UDF columns are populated by the ETL process supplied with the product.
• UDF1 - Jurisdiction
• UDF2 - Premise Type
• UDF3 - Life Support Flag
• UDF4 - Trend Area
• UDF5 - In City Limit
Properties
Property
Value
Target Table
CD_PREM
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_PREM
Stage Table
STG_CD_PREM
ODI Package
B1_PKG_CD_PREM
ETL View
B1_D_PREM_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
PREM_KEY
Premise Dimension Surrogate Key
 
This field is populated with the sequence from SPL_PREM_SEQ.
SRC_PREM_ID
Premise ID (Natural Key)
CI_PREM.PREM_ID
 
PREM_INFO
Premise Information
CI_PREM.ADDRESS1
 
CI_PREM.CITY
 
CI_PREM.STATE
 
CI_PREM.POSTAL
 
CI_PREM_TYPE_L.DESCR
 
CI_PREM.PREM_ID
The fields will be concatenated as Address Line 1, City, State, Postal, Premise Type, Premise ID.
UDF1_CD
Jurisdiction Code
CI_PREM.CIS_DIVISION
 
UDF1_DESCR
Jurisdiction
CI_CIS_DIVISION_L.DESCR
 
UDF2_CD
Premise Type Code
CI_PREM.PREM_TYPE_CD
 
UDF2_DESCR
Premise Type
CI_PREM_TYPE_L.DESCR
 
UDF3_CD
Life Support Flag Code
CI_PREM.LS_SL_FLG
 
UDF3_DESCR
Life Support Flag
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
Lookup Name - LS_SL_FLG

If the override description is not available, the regular description is used.
UDF4_CD
Trend Area Code
CI_PREM.TREND_AREA_CD
 
UDF4_DESCR
Trend Area
CI_TREND_AREA_L.DESCR
 
UDF5_CD
In City Limit Code
CI_PREM.IN_CITY_LIMIT
 
UDF5_DESCR
In City Limit
CI_PREM.IN_CITY_LIMIT
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
UDF16_CD
User Defined Field 16 Code
 
 
UDF16_DESCR
User Defined Field 16 Description
 
 
UDF17_CD
User Defined Field 17 Code
 
 
UDF17_DESCR
User Defined Field 17 Description
 
 
UDF18_CD
User Defined Field 18 Code
 
 
UDF18_DESCR
User Defined Field 18 Description
 
 
UDF1_NUM
User Defined Field 1 Number
 
 
UDF2_NUM
User Defined Field 2 Number
 
 
UDF3_NUM
User Defined Field 3 Number
 
 
UDF4_NUM
User Defined Field 4 Number
 
 
UDF5_NUM
User Defined Field 5 Number
 
 
UDF6_NUM
User Defined Field 6 Number
 
 
UDF7_NUM
User Defined Field 7 Number
 
 
UDF8_NUM
User Defined Field 8 Number
 
 
UDF9_NUM
User Defined Field 9 Number
 
 
UDF10_NUM
User Defined Field 10 Number
 
 
UDF1_DATE
User Defined Field 1 Date
 
 
UDF2_DATE
User Defined Field 2 Date
 
 
UDF3_DATE
User Defined Field 3 Date
 
 
UDF4_DATE
User Defined Field 4 Date
 
 
UDF5_DATE
User Defined Field 5 Date
 
 
UDF6_DATE
User Defined Field 6 Date
 
 
UDF7_DATE
User Defined Field 7 Date
 
 
UDF8_DATE
User Defined Field 8 Date
 
 
UDF9_DATE
User Defined Field 9 Date
 
 
UDF10_DATE
User Defined Field 10 Date
 
 
FLEXFIELD1
Flex Field 1
 
 
FLEXFIELD2
Flex Field 2
 
 
FLEXFIELD3
Flex Field 3
 
 
FLEXFIELD4
Flex Field 4
 
 
FLEXFIELD5
Flex Field 5
 
 
FLEXFIELD6
Flex Field 6
 
 
FLEXFIELD7
Flex Field 7
 
 
FLEXFIELD8
Flex Field 8
 
 
FLEXFIELD9
Flex Field 9
 
 
FLEXFIELD10
Flex 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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
Note: The fields UDF6 through UDF8 are being populated for Oracle Utilities Network Management System. Hence, there might be inconsistencies if these columns are customized.
Service Agreement
Stores information about all service agreements defined in the source system.
The following UDF columns are populated by the ETL process supplied with the product.
• UDF1 - Service Type
• UDF2 - CIS Division
• UDF3 - SA Type
• UDF4 - Revenue Class
• UDF5 - SIC Code
• UDF6 - Deposit Class
• UDF7 - Campaign
• UDF8 - Debt Class
Properties
Property
Value
Target Table
CD_SA
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_SA
Stage Table
STG_CD_SA
ODI Package
B1_PKG_CD_SA
ETL View
B1_D_SA_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
SA_KEY
SA Dimension Surrogate Key
 
This field is populated with the sequence from SPL_SA_SEQ.
SRC_SA_ID
SA ID (Natural Key)
CI_SA.SA_ID
 
SPECIAL_ROLE_CD
Special Role Code
CI_SA_TYPE.SPECIAL_ROLE_FLG
 
SPECIAL_ROLE_
DESCR
Special Role Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
Lookup Name - SPECIAL_ROLE_FLG. If the override description is not available, regular description is used.
UDF1_CD
Service Type (SA) Code
CI_SA_TYPE.SVC_TYPE_CD
 
UDF1_DESCR
Service Type
CI_SVC_TYPE_L.
DESCR
 
UDF2_CD
CIS Division (SA) Code
CI_SA.CIS_DIVISION
 
UDF2_DESCR
CIS Division
CI_CIS_DIVISION_L.DESCR
 
UDF3_CD
SA Type Code
CI_SA.SA_TYPE_CD
 
UDF3_DESCR
SA Type
CI_SA_TYPE_L.
DESCR
 
UDF4_CD
Revenue Class Code
CI_SA_TYPE.REV_CL_CD
 
UDF4_DESCR
Revenue Class
CI_REV_CL_L.DESCR
 
UDF5_CD
SIC Code Value
CI_SA.SIC_CD
 
UDF5_DESCR
SIC Code
CI_SIC_L.DESCR
 
UDF6_CD
Deposit Class Code
CI_SA_TYPE.DEP_CL_CD
 
UDF6_DESCR
Deposit Class
CI_DEP_CL_L.DESCR
 
UDF7_CD
Campaign Code
CI_ENRL.CAMPAIGN_CD
 
UDF7_DESCR
Campaign
CI_CAMPAIGN_L.
DESCR
 
UDF8_CD
Debt Class Code
CI_SA_TYPE.DEBT_
CL_CD
 
UDF8_DESCR
Debt Class
CI_DEBT_CL_L.
DESCR
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
UDF16_CD
User Defined Field 16 Code
 
 
UDF16_DESCR
User Defined Field 16 Description
 
 
UDF17_CD
User Defined Field 17 Code
 
 
UDF17_DESCR
User Defined Field 17 Description
 
 
UDF18_CD
User Defined Field 18 Code
 
 
UDF18_DESCR
User Defined Field 18 Description
 
 
UDF19_CD
User Defined Field 19 Code
 
 
UDF19_DESCR
User Defined Field 19 Description
 
 
UDF1_NUM
User Defined Field 1 Number
 
 
UDF2_NUM
User Defined Field 2 Number
 
 
UDF3_NUM
User Defined Field 3 Number
 
 
UDF4_NUM
User Defined Field 4 Number
 
 
UDF5_NUM
User Defined Field 5 Number
 
 
UDF6_NUM
User Defined Field 6 Number
 
 
UDF7_NUM
User Defined Field 7 Number
 
 
UDF8_NUM
User Defined Field 8 Number
 
 
UDF9_NUM
User Defined Field 9 Number
 
 
UDF10_NUM
User Defined Field 10 Number
 
 
UDF1_DATE
User Defined Field 1 Date
 
 
UDF2_DATE
User Defined Field 2 Date
 
 
UDF3_DATE
User Defined Field 3 Date
 
 
UDF4_DATE
User Defined Field 4 Date
 
 
UDF5_DATE
User Defined Field 5 Date
 
 
UDF6_DATE
User Defined Field 6 Date
 
 
UDF7_DATE
User Defined Field 7 Date
 
 
UDF8_DATE
User Defined Field 8 Date
 
 
UDF9_DATE
User Defined Field 9 Date
 
 
UDF10_DATE
User Defined Field 10 Date
 
 
FLEXFIELD1
Flex Field 1
 
 
FLEXFIELD2
Flex Field 2
 
 
FLEXFIELD3
Flex Field 3
 
 
FLEXFIELD4
Flex Field 4
 
 
FLEXFIELD5
Flex Field 5
 
 
FLEXFIELD6
Flex Field 6
 
 
FLEXFIELD7
Flex Field 7
 
 
FLEXFIELD8
Flex Field 8
 
 
FLEXFIELD9
Flex Field 9
 
 
FLEXFIELD10
Flex 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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
To Do
Stores information about all To Do entries created in the source system.
Properties
Property
Value
Target Table
CD_TD
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_TD_ENTRY
Stage Table
STG_CD_TD
ODI Package
B1_PKG_CD_TD
ETL View
B1_D_TD_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TD_KEY
To Do Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_TD_SEQ.
SRC_TD_ENTRY_ID
To Do Entry ID (Natural Key)
CI_TD_ENTRY.TD_ENTRY_ID
 
TD_MESSAGE
To Do Message
CI_MSG_L.MESSAGE_TEXT
 
CI_MSG_L.MESSAGE_TEXT_OVRD
The parameters provided in the To Do entry will be placed in the available place holders in the message.
 
Note: If the override message text is not available, the regular message text will be used.
TD_COMMENTS
To Do Comments
CI_TD_ENTRY.COMMENTS
 
TD_INFO
To Do Info String
CI_TD_TYPE_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
 
CI_TD_ENTRY.TD_ENTRY_ID
This field is populated as the concatenation of To Do Type, Status Description, To Do Entry ID.
 
Note: Lookup Name - ENTRY_STATUS_FLG. If the override description is not available, the regular description will be used.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 7 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
UDF11_CD
User Defined Field 11 Code
 
 
UDF11_DESCR
User Defined Field 11 Description
 
 
UDF12_CD
User Defined Field 12 Code
 
 
UDF12_DESCR
User Defined Field 12 Description
 
 
UDF13_CD
User Defined Field 13 Code
 
 
UDF13_DESCR
User Defined Field 13 Description
 
 
UDF14_CD
User Defined Field 14 Code
 
 
UDF14_DESCR
User Defined Field 14 Description
 
 
UDF15_CD
User Defined Field 15 Code
 
 
UDF15_DESCR
User Defined Field 15 Description
 
 
UDF1_NUM
User Defined Field 1 Number
 
 
UDF2_NUM
User Defined Field 2 Number
 
 
UDF3_NUM
User Defined Field 3 Number
 
 
UDF4_NUM
User Defined Field 4 Number
 
 
UDF5_NUM
User Defined Field 5 Number
 
 
UDF6_NUM
User Defined Field 6 Number
 
 
UDF7_NUM
User Defined Field 7 Number
 
 
UDF8_NUM
User Defined Field 8 Number
 
 
UDF9_NUM
User Defined Field 9 Number
 
 
UDF10_NUM
User Defined Field 10 Number
 
 
UDF1_DATE
User Defined Field 1 Date
 
 
UDF2_DATE
User Defined Field 2 Date
 
 
UDF3_DATE
User Defined Field 3 Date
 
 
UDF4_DATE
User Defined Field 4 Date
 
 
UDF5_DATE
User Defined Field 5 Date
 
 
UDF6_DATE
User Defined Field 6 Date
 
 
UDF7_DATE
User Defined Field 7 Date
 
 
UDF8_DATE
User Defined Field 8 Date
 
 
UDF9_DATE
User Defined Field 9 Date
 
 
UDF10_DATE
User Defined Field 10 Date
 
 
FLEXFIELD1
Flex Field 1
 
 
FLEXFIELD2
Flex Field 2
 
 
FLEXFIELD3
Flex Field 3
 
 
FLEXFIELD4
Flex Field 4
 
 
FLEXFIELD5
Flex Field 5
 
 
FLEXFIELD6
Flex Field 6
 
 
FLEXFIELD7
Flex Field 7
 
 
FLEXFIELD8
Flex Field 8
 
 
FLEXFIELD9
Flex Field 9
 
 
FLEXFIELD10
Flex 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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
To Do Priority
Stores various priority levels that can be set for a To Do entry in the source system.
Properties
Property
Value
Target Table
CD_TD_PRIORITY
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_TD_PRIORITY
ODI Package
B1_PKG_CD_TD_PRIORITY
ETL View
B1_D_TD_PRIORITY_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TD_PRIORITY_KEY
To Do Priority Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_TD_PRIORITY_SEQ.
TD_PRIORITY_CD
To Do Priority Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
Lookup Name - TD_PRIORITY_FLG
TD_PRIORITY_
DESCR
To Do Priority Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description will be used.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
To Do Role
Stores various roles defined in the source system, who may view and work on the To Do entries.
Properties
Property
Value
Target Table
CD_TD_ROLE
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_ROLE_L
Stage Table
STG_CD_TD_ROLE
ODI Package
B1_PKG_CD_TD_ROLE
ETL View
B1_D_TD_ROLE_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TD_ROLE_KEY
To Do Role Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_TD_ROLE_
SEQ.
TD_ROLE_CD
To Do Role Code (Natural Key)
CI_ROLE_L.ROLE_ID
 
TD_ROLE_DESCR
To Do Role Description
CI_ROLE_L.DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
To Do Skill
Stores the characteristic types defined as skills in the AQM feature configuration list in the source system. The characteristic types are extracted along with their characteristic values.
Properties
Property
Value
Target Table
CD_TD_SKILL
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_WFM_OPT/CI_CHAR_VAL_L
Stage Table
STG_CD_TD_SKILL
ODI Package
B1_PKG_CD_TD_SKILL
ETL View
B1_D_TD_SKILL_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TD_SKILL_KEY
To Do Skill Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_TD_SKILL_SEQ.
TD_SKILL_LVL_CD
To Do Skill Level Code (Natural Key)
CI_CHAR_VAL_L.
CHAR_TYPE_CD
 
CI_CHAR_VAL_L.
CHAR_VAL
This field is populated with Characteristic Type Code <space> Characteristic Value Code.
TD_SKILL_LVL_
DESCR
To Do Skill Level Description
CI_CHAR_VAL_L.
DESCR
 
CI_CHAR_TYPE_L.
DESCR
This field is populated with the Characteristic Type Description / Characteristic Value Description value.
TD_SKILL_TYPE_CD
To Do Skill Type Code
CI_WFM_OPT.WFM_
OPT_VAL
The characteristic types defined as Skill on the Activity Queue Management feature configuration list is extracted.
TD_SKILL_TYPE_DESCR
To Do Skill Type Description
CI_CHAR_TYPE_L.
DESCR
The characteristic type descriptions for those characteristic types retrieved as Skills will be populated.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
To Do Status
Extracts the statuses of a To-Do entry. The data is retrieved from the Entry Status Flag (ENTRY_STATUS_FLG) lookup field.
Properties
Property
Value
Target Table
CD_TD_STATUS
Table Type
Dimension
SCD Type
Type 1
Source System Driver Table
CI_LOOKUP_VAL_L
Stage Table
STG_CD_TD_STATUS
ODI Package
B1_PKG_CD_TD_STATUS
ETL View
B1_D_TD_STATUS_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TD_STATUS_KEY
To Do Status Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_TD_STATUS_SEQ.
TD_STATUS_CD
To Do Status Code
CI_LOOKUP_VAL_L.
FIELD_VALUE
Lookup Name - ENTRY_STATUS_FLG
TD_STATUS_DESCR
To Do Status Description
CI_LOOKUP_VAL_L.DESCR
 
CI_LOOKUP_VAL_L.DESCR_OVRD
If an override description is not available, regular description is used.
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load time-stamp value.
UPDATE_DTTM
Update Date/Time
 
This field is populated with the updated time-stamp value.
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
To Do Type
Stores all To Do types defined in the source system.
Properties
Property
Value
Target Table
CD_TD_TYPE
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
CI_TD_TYPE_L
Stage Table
STG_CD_TD_TYPE
ODI Package
B1_PKG_CD_TD_TYPE
ETL View
B1_D_TD_TYPE_VW
 
Fields
Target Field
OAS Field
Source Field
Transformation Logic
TD_TYPE_KEY
To Do Type Dimension Surrogate Key
 
This field is populated with the sequence from OUBI_TD_TYPE_SEQ.
TD_TYPE_CD
To Do Type Code (Natural Key)
CI_TD_TYPE_L.TD_
TYPE_CD
 
TD_TYPE_DESCR
To Do Type Description
CI_TD_TYPE_L.
DESCR
 
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.
 
User
Stores the user details from the source system.
Properties
Property
Value
Target Table
CD_USER
Table Type
Dimension
SCD Type
Type 2
Source System Driver Table
SC_USER
Stage Table
STG_CD_USER
ODI Package
B1_PKG_CD_USER
ETL View
B1_D_USER_VW
Fields
Target Field
OAS Field
Source Field
Transformation Logic
USER_KEY
User Dimension Surrogate Key
 
This field is populated with the sequence from SPL_USER_SEQ.
USER_CD
User Code (Natural Key)
SC_USER.USER_ID
 
USER_DESCR
User Description
SC_USER.LAST_
NAME
 
SC_USER.FIRST_
NAME
This field is populated with Last Name, First Name.
UDF1_CD
User Defined Field 1 Code
 
 
UDF1_DESCR
User Defined Field 1 Description
 
 
UDF2_CD
User Defined Field 2 Code
 
 
UDF2_DESCR
User Defined Field 2 Description
 
 
UDF3_CD
User Defined Field 3 Code
 
 
UDF3_DESCR
User Defined Field 3 Description
 
 
UDF4_CD
User Defined Field 4 Code
 
 
UDF4_DESCR
User Defined Field 4 Description
 
 
UDF5_CD
User Defined Field 5 Code
 
 
UDF5_DESCR
User Defined Field 5 Description
 
 
UDF6_CD
User Defined Field 6 Code
 
 
UDF6_DESCR
User Defined Field 6 Description
 
 
UDF7_CD
User Defined Field 7 Code
 
 
UDF7_DESCR
User Defined Field 8 Description
 
 
UDF8_CD
User Defined Field 8 Code
 
 
UDF8_DESCR
User Defined Field 8 Description
 
 
UDF9_CD
User Defined Field 9 Code
 
 
UDF9_DESCR
User Defined Field 9 Description
 
 
UDF10_CD
User Defined Field 10 Code
 
 
UDF10_DESCR
User Defined Field 10 Description
 
 
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).
DATA_LOAD_DTTM
Data Load Date/Time
 
This field is populated with the load timestamp value.
EFF_START_DTTM
Effective Start Date/Time
 
 
EFF_END_DTTM
Effective End Date/Time
 
 
JOB_NBR
Job Number
 
This field is populated with the ODI job execution session number.