Inbound SCV Core table creation

The Sync SCV Configuration program generates or updates an inbound SCV Core table as follows:

Determines the related SCV Core table name:

If <current entity>.C_IS_SYSTEM = "T" then <table name> = SC_<current entity>.C_NAME
Else <table name> = SUC_<current entity>.C_NAME

If a table is not available in the SCV database with the corresponding SCV Core table name:

Creates an SCV table from entity definition by using the following planning process, control, and custom columns specified in the core_flag column of the column registry table:

Planning process columns:
    cycle_period_id             NVARCHAR(80)  
    scenario_id                 NVARCHAR(80)  
    source                      NVARCHAR(80)  

Control columns:
    scp_ref                     NVARCHAR(150)  
    external_ref                NVARCHAR(150)  
    deleted_flag                NVARCHAR(1)  
    --  
    created_by                  NVARCHAR(80)  
    creation_time               DATETIME2(7)  
    modified_by                 NVARCHAR(80)  
    modification_time           DATETIME2(7)  

Custom columns:
    custom_string_1-10          NVARCHAR(80)  
    custom_number_1-10          FLOAT  
   custom_date_1-5             DATETIME2(7)

Creates an index on the ID column.

Creates this profile table:

CREATE TABLE <current table>_P AS (  
    id              BIGINT NOT NULL,  
    profile_id      BIGINT NOT NULL  
);  

ALTER TABLE <current table>_P ADD CONSTRAINT PK_<current table>_P  
PRIMARY KEY (id, profile_id);

Creates this hierarchy-link table:

CREATE TABLE <current table>_H AS (  
    hierarchy_id        BIGINT  
    hierarchy_type      NVARCHAR(50)  
    base_level_value    NVARCHAR(400)  
    id                  BIGINT  
);  

ALTER TABLE <current table>_H ADD CONSTRAINT PK_<current table>_H  
PRIMARY KEY (id, hierarchy_id, base_level_value);

Creates this extension data table if the extended_flag is set:

If <current entity>.C_IS_SYSTEM = "T" then: 

CREATE TABLE SC_EXT_<<current entity>.C_NAME> AS (  
    Planning process columns:
        All PK columns of the entity core table. 
            // Note that, in order to do this at this stage PK constraints 
            // should be synced before starting table generation. 
    Extension columns 
        Defined in the extension properties table
    Control columns:
        source                      NVARCHAR(80)
        not_in_core                 NVARCHAR(1)
        created_by                  NVARCHAR(80)  
        creation_time               DATETIME2(7)  
        modified_by                 NVARCHAR(80)  
        modification_time           DATETIME2(7)  
        id                          BIGINT NOT NULL         
);  


Else If <current entity>.C_IS_SYSTEM = "F" then: 

CREATE TABLE SUC_EXT_<<current entity>.C_NAME> AS (  
    Planning process columns:
        All PK columns of the entity core table. 
            // Note that, in order to do this at this stage PK constraints 
            // should be synced before starting table generation. 
    Extension columns 
        Defined in the extension properties table
    Control columns:
        source                      NVARCHAR(80)
        not_in_core                 NVARCHAR(1)
        created_by                  NVARCHAR(80)  
        creation_time               DATETIME2(7)  
        modified_by                 NVARCHAR(80)  
        modification_time           DATETIME2(7)  
        id                          BIGINT NOT NULL         
);

Creates this extended core view if the extended_flag is not set:

If <current entity>.C_IS_SYSTEM = "T" then: 

CREATE VIEW SC_V_EXT_<current entity> AS (
    SELECT c.* 
    FROM SC_<current entity> c
);  


Else If <current entity>.C_IS_SYSTEM = "F" then: 

CREATE VIEW SUC_V_EXT_<current entity> AS (
    SELECT c.* 
    FROM SUC_<current entity> c
);

Creates this extended core view if the extended_flag is set:

If <current entity>.C_IS_SYSTEM = "T" then: 

CREATE VIEW SC_V_EXT_<current entity> AS (
    SELECT
        // Calculated column. For each xs_<column_name> or xc_<column_name> in x that has a corresponding <column_name> in c, select one column as follows:   
        COALESCE(x.xs_<column_name>, c.<column_name>)   AS res_<column_name>        (this will exclude PK columns)
        COALESCE(x.xc_<column_name>, c.<column_name>)   AS res_<column_name>        (this will exclude PK columns)
        COALESCE(x.xs_<column_name>, x.xc_<column_name>, c.<column_name>)   AS res_<column_name>    (if both xs_* and xc_* columns exist; this will exclude PK columns)


        // Main column for imported values. For each <column_name> in c, select one column as follows: 
        c.<column_name>                                 AS <column_name>            (this will include PK columns)

        // Extended column for user/override values. For each xs_<column_name> or xc_<column_name> in x, select one column as follows: 
        x.xs_<column_name>                              AS xs_<column_name>     
        x.xc_<column_name>                              AS xc_<column_name>     

    FROM SC_<current entity> c LEFT JOIN SC_EXT_<current entity> x 
                        ON  c.<PK columns 1> = x.<PK columns 1> AND
                            c.<PK columns 2> = x.<PK columns 2> AND 
                            . . .
);  



Else If <current entity>.C_IS_SYSTEM = "F" then: 

CREATE VIEW SUC_V_EXT_<current entity> AS (
    SELECT
        // Calculated column. For each xc_<column_name> in x that has a corresponding <column_name> in c, select one column as follows:   
        COALESCE(x.xc_<column_name>, c.<column_name>)   AS res_<column_name>        (this will exclude PK columns)

        // Main column for imported values. For each <column_name> in c, select one column as follows: 
        c.<column_name>                                 AS <column_name>          (this will include PK columns)

        // Extended column for user/override values. For each xc_<column_name> inx, select one column as follows: 
        x.xc_<column_name>                              AS xc_<column_name>     

    FROM SUC_<current entity> c LEFT JOIN SUC_EXT_<current entity> x 
                        ON  c.<PK columns 1> = x.<PK columns 1> AND
                            c.<PK columns 2> = x.<PK columns 2> AND 
                            . . .
);

Creates this core profile views:

If <current entity>.C_IS_SYSTEM = "T" then: 
CREATE VIEW SC_V_<current entity> AS
    SELECT SC_<current entity>_P.profile_id, SZ_PROFILE.profile_name, SC_V_EXT_<current entity>.*
    FROM SZ_PROFILE, SC_<current entity>_P, SC_V_EXT_<current entity>
    WHERE SC_<current entity>_P.profile_id = SZ_PROFILE.profile_id  AND 
          SC_<current entity>_P.id         = SC_V_EXT_<current entity>.id

Else If <current entity>.C_IS_SYSTEM = "F" then: 

CREATE VIEW SUC_V_<current entity> AS
    SELECT SUC_<current entity>_P.profile_id, SZ_PROFILE.profile_name, SUC_V_EXT_<current entity>.*
    FROM SZ_PROFILE, SUC_<current entity>_P, SUC_V_EXT_<current entity>
    WHERE SUC_<current entity>_P.profile_id = SZ_PROFILE.profile_id  AND 
          SUC_<current entity>_P.id         = SUC_V_EXT_<current entity>.id

If the table is available in the SCV database with the corresponding SCV Core table name:

Updates an SCV table from entity definition if the corresponding column is not available in <table name> for each property in <current entity>.

Updates this extended view if the extended_flag is not set, and the corresponding column is not available in <table name> for each property in <current entity>:

If <current entity>.C_IS_SYSTEM = "T" then: 

ALTER VIEW SC_V_EXT_<current entity> AS (
    SELECT c.* 
    FROM SC_<current entity> c
);  

Else If <current entity>.C_IS_SYSTEM = "F" then: 

ALTER VIEW SUC_V_EXT_<current entity> AS (
    SELECT c.* 
    FROM SUC_<current entity> c
);

Updates this extended view if the extended_flag is set, and the corresponding column is not available in <table name> for each property in <current entity>:

If <current entity>.C_IS_SYSTEM = "T" then: 

ALTER VIEW SC_V_EXT_<current entity> AS (
    SELECT
        // Calculated column. For each xs_<column_name> or xc_<column_name> in x that has a corresponding <column_name> in c, select one column as follows:   
        COALESCE(x.xs_<column_name>, c.<column_name>)   AS res_<column_name>        (this will exclude PK columns)
        COALESCE(x.xc_<column_name>, c.<column_name>)   AS res_<column_name>        (this will exclude PK columns)
        COALESCE(x.xs_<column_name>, x.xc_<column_name>, c.<column_name>)   AS res_<column_name>    (if both xs_* and xc_* columns exist; this will exclude PK columns)

        // Main column for imported values. For each <column_name> in c, select one column as follows: 
        c.<column_name>                                 AS <column_name>            (this will include PK columns)

        // Extended column for user/override values. For each xs_<column_name> or xc_<column_name> in x, select one column as follows: 
        x.xs_<column_name>                              AS xs_<column_name>     
        x.xc_<column_name>                              AS xc_<column_name>     

    FROM SC_<current entity> c LEFT JOIN SC_EXT_<current entity> x 
                        ON  c.<PK columns 1> = x.<PK columns 1> AND
                            c.<PK columns 2> = x.<PK columns 2> AND 
                            . . .
);  



Else If <current entity>.C_IS_SYSTEM = "F" then: 

ALTER VIEW SUC_V_EXT_<current entity> AS (
    SELECT
        // Calculated column. For each xc_<column_name> in x that has a corresponding <column_name> in c, select one column as follows:   
        COALESCE(x.xc_<column_name>, c.<column_name>)   AS res_<column_name>        (this will exclude PK columns)

        // Main column for imported values. For each <column_name> in c, select one column as follows: 
        c.<column_name>                                 AS <column_name>            (this will include PK columns)

        // Extended column for user/override values. For each xc_<column_name> in x, select one column as follows: 
        x.xc_<column_name>                              AS xc_<column_name>     

    FROM SUC_<current entity> c LEFT JOIN SUC_EXT_<current entity> x 
                        ON  c.<PK columns 1> = x.<PK columns 1> AND
                            c.<PK columns 2> = x.<PK columns 2> AND 
                            . . .
);

Note: This action is mandatory to include newly added properties in the view.

Updates this core profile views:

If <current entity>.C_IS_SYSTEM = "T" then: 

ALTER VIEW SC_V_<current entity> AS
    SELECT SC_<current entity>_P.profile_id, SZ_PROFILE.profile_name, SC_V_EXT_<current entity>.*
    FROM SZ_PROFILE, SC_<current entity>_P, SC_V_EXT_<current entity>
    WHERE SC_<current entity>_P.profile_id = SZ_PROFILE.profile_id  AND 
          SC_<current entity>_P.id         = SC_V_EXT_<current entity>.id


Else If <current entity>.C_IS_SYSTEM = "F" then: 

ALTER VIEW SUC_V_<current entity> AS
    SELECT SUC_<current entity>_P.profile_id, SZ_PROFILE.profile_name, SUC_V_EXT_<current entity>.*
    FROM SZ_PROFILE, SUC_<current entity>_P, SUC_V_EXT_<current entity>
    WHERE SUC_<current entity>_P.profile_id = SZ_PROFILE.profile_id  AND 
          SUC_<current entity>_P.id         = SUC_V_EXT_<current entity>.id

Note: This action is mandatory to include newly added properties in the view.