1. User Guide
  2. Dimension Loading Process
  3. Overview of SCD Process
  4. Tables Used by the SCD Component

4.2.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 OFS EFPA application.
    • V_COMPONENT_DESC: This column value is hardcoded 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
    Example:

    Table 4-4 SETUP_MASTER

    V_COMPONENT_CODE V_COMPONENT_DESC V_COMPONENT_VALUE
    COMMON_COA_HEIR COMMON_COA_HEIR1 1000063952
    GL_ACCOUNT_HEIR GL_ACCOUNT_HEIR1 200000808
    ORG_HIER ORG_UNIT_HIER1 200282
    PRODUCT_HIER PRODUCT_HIER1 1000004330

    Note:

    For any newly defined Hierarchy, a row must be inserted to this table manually for SCD to process that Hierarchy. You can only specify one Hierarchy for each dimension.
  • SYS_TBL_MASTER

    The application installer populates one row per dimension for the seeded dimensions in this table.

    SCD for GL Dimension fails if V_COMPONENT_VALUE is used with DEFAULT value. To avoid this failure, run the following query: 

    SELECT
distinct hierarchy_id
FROM
dim_general_ledger_hier;

    This query fetches the hierarchy IDs (in numbers). You can use any of the fetched hierarchy IDs to update the SETUP_MASTER table with the following query: 

    UPDATE setup_master
SET
v_component_value = '<AMHM HierarchyID to be used for SCD>'
WHERE
v_component_code = 'GL_ACCOUNT_HIER';

    Table 4-5 SYS_TBL_MASTER

    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

    		 The 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 application installer for the product dimension.

    Table 4-6 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 must be inserted to this table manually.
  • SYS_STG_JOIN_MASTER
    The application installer populates this table for the seeded dimensions.

    Table 4-7 SYS_STG_JOIN_MASTER

    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.
    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 possible values are VARCHAR, DATE, and NUMBER, based on the underlying column data type.
    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 (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
    • NN: Not Null
    Example: This is the row inserted by the application installer for the product dimension.

    Table 4-8 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 must be inserted to this table manually.
  • DIM_< dimension name >_V: The database view which SCD uses as the source.

    Example: DIM_PRODUCTS_V

    These views come as part of the installation for the dimensions seeded with the application.

    Note:

    For any newly defined dimension, a view must be created, which is similar to that of DIM_PRODUCTS_V.

    A sequence should be created for every user-defined dimension, using the below query:

    Example:

    create sequence SEQ_< DIMENSION > minvalue 1
maxvalue 999999999999999999999999999
increment by 1