6.4 Tables Used by the SCD Component
The following are the database tables and columns used by the SCD component:
- SETUP_MASTER
- V_COMPONENT_CODE: This column is not used by the OFSEFPA solution. This column acts as a primary key for ALMBI.
- V_COMPONENT_DESC: This column value is hard coded in the database view definitions for DIM_PRODUCT_V, DIM_GL_ACCOUNT_V, DIM_COMMON_COA_V, and DIM_ORG_UNIT_V to obtain the Hierarchy ID from the REV_HIER_FLATTENED table. For this reason, the value for this column should be unique.
Note:
The value in V_COMPONENT_DESC must exactly match with the value used in the SQL to create the DIM_<dimension>_V view. The View SQL contains a section referencing the SETUP_MASTER table. You must use the same upper and/or lower case letters in V_COMPONENT_DESC as used in this section of the View SQL.- V_COMPONENT_VALUE: This is the hierarchy ID to be processed and this can be obtained by executing the following query:
select b.object_definition_id,short_desc,long_desc from fsi_m_object_definition_b b inner join fsi_m_object_definition_tl t on b.object_definition_id = t.object_definition_id and b.id_type = 5
Note:
For any newly defined Hierarchy, a row will have to be inserted to this table manually for SCD to process that Hierarchy. You can only specify one Hierarchy for each dimension.Examples:
| V_COMPONENT_CODE | V_COMPONENT_VALUE | V_COMPONENT_DESC |
| COMMON_COA_HIER | 1000063952 | COMMON_COA_HIER1 |
| GL_ACCOUNT_HIER | 200000808 | GL_ACCOUNT_HIER1 |
| ORG_HIER | 200282 | ORG_UNIT_HIER1 |
| PRODUCT_HIER | 1000004330 | PRODUCT_HIER1 |
- SYS_TBL_MASTER
The solution installer populates one row per dimension for the seeded dimensions in this table.
| Column Name | Data Type | Column Description |
| MAP_REF_NUM | NUMBER(3) NOT NULL | The Mapping Reference Number for this unique mapping of a Source to a Dimension Table. |
| TBL_NM | VARCHAR2(30) NOT NULL | Dimension Table Name. |
| STG_TBL_NM | VARCHAR2(30) NOT NULL | Staging Table Name. |
| SRC_PRTY | NUMBER(2) NULL | Priority of the Source when multiple sources are mapped to the same target. |
| SRC_PROC_SEQ | NUMBER(2) NOT NULL | The sequence in which the various sources for the DIMENSION will be taken up for processing. |
| SRC_TYP | VARCHAR2(30) NULL | The type of the Source for a Dimension, that is, Transaction Or Master Source. |
| DT_OFFSET | NUMBER(2) NULL | The offset for calculating the Start Date based on the Functional Requirements Document (FRD). |
| SRC_KEY | NUMBER(3) NULL |
Example:
This is the row inserted by the solution installer for the product dimension.
| MAP_REF_NUM | 128 |
| TBL_NM | DIM_PRODUCT |
| STG_TBL_NM | DIM_PRODUCT_V |
| SRC_PRTY | |
| SRC_PROC_SEQ | 1 |
| SRC_TYP | MASTER |
| DT_OFFSET | 0 |
Note:
For any newly defined dimension, a row will have to be inserted to this table manually.- SYS_STG_JOIN_MASTER
The solution installer populates this table for the seeded dimensions.
| Column Name | Data Type | Column Description |
| MAP_REF_NUM | NUMBER(3) NOT NULL | The Mapping Reference Number for this unique mapping of a Source to a Dimension Table. |
| COL_NM | VARCHAR2(30) NOT NULL | Name of the column in the Dimension Table. |
| COL_TYP | VARCHAR2(30) NOT NULL | Type of column. The possible values are given in the following section. |
| STG_COL_NM | VARCHAR2(60) NULL | Name of the column in the Staging Table. |
| SCD_TYP_ID | NUMBER(3) NULL | SCD type for the column. |
| PRTY_LOOKUP_REQD_FLG | CHAR(1) NULL | Column to determine whether Lookup is required for Priority of Source against the Source Key Column or not. |
| COL_DATATYPE | VARCHAR2(15) NULL | The list of possible values are VARCHAR, DATE, and NUMBER, based on the underlying column datatype. |
| COL_FORMAT | VARCHAR2(15) NULL |
The possible values for column type (the COL_TYPE column) in SYS_STG_JOIN_MASTER table are:
PK: Primary Dimension Value (can be the multiple of the given Mapping Reference Number)
SK: Surrogate Key
DA: Dimensional Attribute (may be multiple for a given Mapping Reference Number)
SD: Start Date
ED: End Date
LRI: Latest Record Indicator (Current Flag)
CSK: Current Surrogate Key
PSK: Previous Surrogate Key
SS: Source Key
LUD: Last Updated Date/Time
LUB: Last Updated By
Example:
This is the row inserted by the solution installer for the product dimension.
| MAP_REF_NUM | 128 |
| COL_NM | V_PRODUCT_NAME |
| COL_TYP | DA |
| STG_COL_NM | V_PRODUCT_NAME |
| SCD_TYP_ID | 2 |
| PRTY_LOOKUP_REQD_FLG | N |
| COL_DATATYPE | VARCHAR |
| COL_FORMAT |
Note:
For any newly defined dimension, the column details will have to be inserted to this table manually.- DIM_< dimension name >_V: The database view which SCD uses as the source.
DIM_PRODUCTS_V
These views come as part of install for the dimensions seeded with the application.
Note: For any newly defined dimension, a view will have to be created, which is similar to that of DIM_PRODUCTS_V.
A sequence should be created for every user-defined dimension, using the following query:
create sequence SEQ_< DIMENSION > minvalue 1
maxvalue 999999999999999999999999999
increment by 1