[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| The following are the insertion order of tables:
|
| 1. MNAV_SYSTEMS
| 2. MNAV_INTERMEDIATE_GROUPS
| 3. MNAV_INTERMEDIATE_GROUPS_L
| 4. MATRIX
| 5. MATRIX_INFO
| 6. MATRIX_LANGUAGE
| 7. MIND_ANALYSIS
| 8. MIND_CALCULATIONS
| 9. MIND_FIELDS
| 40. MIND_DATA
| 11. MIND_DATA_SERIE
| 12. MIND_DRILLS_CONFIG
| 13. MIND_DRILLS
| 14. MIND_DRILLS_LANGUAGE
| 15. MIND_OPTIONS
| 16. MIND_PERIODS
| 17. MIND_PERIODS_LANGUAGE
| 18. MIND_TABLES_NEW
| 19. MNAV_INDICATORS_BY_SYSTEM
|
+============================================================================*/
Function Create_Tab_Template
Return Boolean
Is
l_top_c BSC_TAB_IND_GROUPS_B.top_position%type;
Select count(*)
Into l_count
From BSC_SYS_PERIODS_TL;
INSERT INTO BSC_SYS_PERIODS_TL
( YEAR,
PERIODICITY_ID,
PERIOD_ID,
MONTH,
LANGUAGE,
SOURCE_LANG,
NAME,
SHORT_NAME)
( SELECT
CA.YEAR,
CA.PERIODICITY_ID,
CA.PERIOD_ID,
1 AS MONTH,
L.LANGUAGE_CODE AS LANGUAGE,
L.LANGUAGE_CODE AS SOURCE_LANG,
CA.NAME,
NULL AS SHORT_NAME
FROM
(SELECT
C.YEAR AS YEAR,
2 AS PERIODICITY_ID,
C.SEMESTER AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
(SELECT
MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C.YEAR AND
C1.SEMESTER = C.SEMESTER
) AND
TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
(SELECT
MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C2.YEAR AND
C1.SEMESTER = C2.SEMESTER)
UNION
SELECT
C.YEAR AS YEAR,
3 AS PERIODICITY_ID,
C.QUARTER AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
(SELECT
MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C.YEAR AND
C1.QUARTER = C.QUARTER
) AND
TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
(SELECT
MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C2.YEAR AND
C1.QUARTER = C2.QUARTER)
UNION
SELECT
C.YEAR AS YEAR,
4 AS PERIODICITY_ID,
C.BIMESTER AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
(SELECT
MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C.YEAR AND
C1.BIMESTER = C.BIMESTER
) AND
TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
(SELECT
MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C2.YEAR AND
C1.BIMESTER = C2.BIMESTER)
UNION
SELECT
C.YEAR AS YEAR,
5 AS PERIODICITY_ID,
C.MONTH AS PERIOD_ID,
TO_CHAR(C.CALENDAR_MONTH) AS NAME
FROM
BSC_DB_CALENDAR C
GROUP BY
C.YEAR,
C.MONTH,
C.CALENDAR_MONTH
UNION
SELECT
C.YEAR AS YEAR,
7 AS PERIODICITY_ID,
C.WEEK52 AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
(SELECT
MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C.YEAR AND
C1.WEEK52 = C.WEEK52)
UNION
SELECT
C.YEAR AS YEAR,
9 AS PERIODICITY_ID,
C.DAY365 AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
Update BSC_SYS_INIT
Set PROPERTY_VALUE = '1',LAST_UPDATED_BY =2,LAST_UPDATE_DATE=SYSDATE
Where PROPERTY_CODE = 'SHOW_TABS';
l_debug_stmt := 'Inserting BSC_TABS_B, i_system = ' ||
to_char(i_system);
Insert Into BSC_TABS_B(
TAB_ID,KPI_MODEL,BSC_MODEL,
CROSS_MODEL,DEFAULT_MODEL,
ZOOM_FACTOR,
CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
Values (
G_Tab_Tbl(i_system).code,
1,
0,
0,
0,
0.8227025,
0,SYSDATE,
0,SYSDATE,
0
);
l_debug_stmt := 'Inserting BSC_TABS_TL, i_system = ' ||
to_char(i_system);
l_sql_stmt := 'INSERT INTO BSC_TABS_TL '||
' (TAB_ID,LANGUAGE,SOURCE_LANG,NAME,HELP) ' ||
'SELECT '||
G_Tab_Tbl(i_system).Code||' AS TAB_ID, '||
'FEM.LANGUAGE AS LANGUAGE, '||
'FEM.SOURCE_LANG AS SOURCE_LANG, '||
'SUBSTR(FEM.MEANING,1,35) AS NAME, '||
'SUBSTR(FEM.MEANING,1,40)||'' ''||SUBSTR(FEM_DESC.MEANING,1,40) AS HELP '||
'FROM '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DESC '||
'WHERE '||
'FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_NAME'' AND '||
'FEM.LOOKUP_CODE = :1 AND '||
'FEM_DESC.LOOKUP_TYPE = ''BSC_UI_COMMON'' AND '||
'FEM_DESC.LOOKUP_CODE = ''DESCRIPTION'' AND '||
'FEM.LANGUAGE = FEM_DESC.LANGUAGE';
l_debug_stmt := 'Inserting BSC_TAB_CSF_B, i_system = ' ||
to_char(i_system);
Insert Into BSC_TAB_CSF_B(
TAB_ID,
CSF_ID,
CSF_TYPE,
INTERMEDIATE_FLAG
)
Values (
G_Tab_Tbl(i_system).Code,
G_Csf_Tbl(0).Code,
G_Csf_Tbl(0).Type,
G_Csf_Tbl(0).Inter_Flag
);
l_debug_stmt := 'Inserting BSC_TAB_CSF_TL, i_system = ' ||
to_char(i_system);
l_sql_stmt := 'INSERT INTO BSC_TAB_CSF_TL '||
' (TAB_ID,CSF_ID,LANGUAGE,SOURCE_LANG,NAME,HELP) ' ||
'SELECT '||
G_Tab_Tbl(i_system).Code||' AS TAB_ID, '||
G_Csf_Tbl(0).Code||' AS CSF_ID, '||
'FEM.LANGUAGE AS LANGUAGE, '||
'FEM.SOURCE_LANG AS SOURCE_LANG, '||
'SUBSTR(FEM.MEANING,1,30) AS NAME, '||
'SUBSTR(FEM.MEANING,1,80) AS HELP '||
'FROM '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM '||
'WHERE '||
'FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_CSF'' AND '||
'FEM.LOOKUP_CODE = :1 ';
l_debug_stmt := 'Inserting BSC_TAB_IND_GROUPS_B, i_system=' ||
to_char(i_system) || ', j_group=' || to_char(j_group) ||
', l_group_ind=' || to_char(l_group_ind);
Insert Into BSC_TAB_IND_GROUPS_B(
TAB_ID,
CSF_ID,
IND_GROUP_ID,
GROUP_TYPE,
NAME_POSITION,
NAME_JUSTIFICATION,
LEFT_POSITION,
TOP_POSITION,
WIDTH,
HEIGHT)
Values (
i_system, -- system_c
0,
G_Group_Tbl(l_group_ind).Code, -- code
0,
1,
0,
l_left_r, -- left_r
l_top_c, -- top_c
l_width, -- width
l_height -- height
);
l_debug_stmt := 'Inserting BSC_TAB_IND_GROUPS_TL, i_system=' ||
to_char(i_system) || ', j_group=' || to_char(j_group) ||
', l_group_ind=' || to_char(l_group_ind);
l_sql_stmt := 'INSERT INTO BSC_TAB_IND_GROUPS_TL '||
' (TAB_ID,CSF_ID,IND_GROUP_ID,LANGUAGE,SOURCE_LANG,NAME,HELP) ' ||
'SELECT '||
i_system||' AS TAB_ID, '||
'0 AS CSF_ID, '||
G_Group_Tbl(l_group_ind).Code||' AS IND_GROUP_ID, '||
'FEM.LANGUAGE AS LANGUAGE, '||
'FEM.SOURCE_LANG AS SOURCE_LANG, '||
'SUBSTR(FEM.MEANING,1,50) AS NAME, '||
'SUBSTR(FEM.MEANING,1,80) AS HELP '||
'FROM '||
BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM '||
'WHERE '||
'FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_IND_GROUPS'' AND '||
'FEM.LOOKUP_CODE = :1 ';
l_debug_stmt := 'Inserting BSC_SYS_LINES, i_system=' ||
to_char(i_system) || ', j_group=' || to_char(j_group);
Insert Into BSC_SYS_LINES (
SOURCE_TYPE,
SOURCE_CODE,
LINE_TYPE,
LINE_ID,
LEFT_POSITION,
TOP_POSITION,
LENGTH,
ARROW)
Values (
1,
i_system, -- system_c
l_gl_type, -- type
l_gl_index_r, -- index_r
l_gl_left_r, -- left_r
l_gl_top_c, -- top_c
l_gl_length, -- length
NULL -- arrow
);
l_debug_stmt := 'Inserting BSC_KPIS_B, indicator=' ||
to_char(G_Ind_Tbl(k_indicator).Indicator) ||
', k_indicator=' || to_char(k_indicator);
Insert Into BSC_KPIS_B (
INDICATOR,
CSF_ID,
IND_GROUP_ID,
DISP_ORDER,
PROTOTYPE_FLAG ,
INDICATOR_TYPE ,
CONFIG_TYPE ,
PERIODICITY_ID,
BM_GROUP_ID,
APPLY_COLOR_FLAG,
PROTOTYPE_COLOR,
SHARE_FLAG,
PUBLISH_FLAG,
CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
EDW_FLAG,
CALENDAR_ID,
COLOR_ROLLUP_TYPE,
PROTOTYPE_COLOR_ID,
WEIGHTED_COLOR_METHOD
)
Values (
G_Ind_Tbl(k_indicator).Indicator, -- indicator
G_Ind_Tbl(k_indicator).Csf, -- intermediate_r
G_Ind_Tbl(k_indicator).Group_r, -- group_r
0, -- position
1, -- prototype
G_Ind_Tbl(k_indicator).type, -- indicator_type
G_Ind_Tbl(k_indicator).config, -- configuration
G_Ind_Tbl(k_indicator).Periodicity, -- panel_periodicity
1,
1,
'G',
1,
1,
0,SYSDATE,
0,SYSDATE,
0,
0,
1,
'DEFAULT_KPI',
24865,
NULL
);
l_debug_stmt := 'Inserting BSC_KPI_DEFAULTS_B, indicator=' ||
to_char(G_Ind_Tbl(k_indicator).Indicator) ||
', k_indicator=' || to_char(k_indicator);
l_sql_defaults_b:= 'Insert Into BSC_KPI_DEFAULTS_B ('||
'TAB_ID,INDICATOR,FORMAT_MASK,COLOR_METHOD,'||
'DIM_SET_ID,DIM_LEVEL1_VALUE,DIM_LEVEL2_VALUE,DIM_LEVEL3_VALUE,DIM_LEVEL4_VALUE,'||
'DIM_LEVEL5_VALUE,DIM_LEVEL6_VALUE,DIM_LEVEL7_VALUE,DIM_LEVEL8_VALUE,'||
'LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)'||
'Values (:1,:2,'||
'''#,###,##0'''||
',1,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,0,SYSDATE,0,NULL)';
l_debug_stmt := 'Inserting BSC_KPI_DEFAULTS_TL, indicator=' ||
to_char(G_Ind_Tbl(k_indicator).Indicator) ||
', k_indicator=' || to_char(k_indicator);
l_sql_stmt := 'INSERT INTO BSC_KPI_DEFAULTS_TL
(TAB_ID,INDICATOR,LANGUAGE,SOURCE_LANG, ANALYSIS_OPTION0_NAME, ANALYSIS_OPTION1_NAME,
ANALYSIS_OPTION2_NAME,PERIOD_NAME,SERIES_NAME,
DIM_LEVEL1_NAME,DIM_LEVEL2_NAME,DIM_LEVEL3_NAME,DIM_LEVEL4_NAME,
DIM_LEVEL5_NAME,DIM_LEVEL6_NAME,DIM_LEVEL7_NAME,DIM_LEVEL8_NAME,
DIM_LEVEL1_TEXT,DIM_LEVEL2_TEXT,DIM_LEVEL3_TEXT,DIM_LEVEL4_TEXT,
DIM_LEVEL5_TEXT,DIM_LEVEL6_TEXT,DIM_LEVEL7_TEXT,DIM_LEVEL8_TEXT)
SELECT '||G_Ind_Tbl(k_indicator).Tab||' AS TAB_ID,'
||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
FEM.LANGUAGE AS LANGUAGE,
FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,30) AS ANALYSIS_OPTION0_NAME,
NULL AS ANALYSIS_OPTION1_NAME,
NULL AS ANALYSIS_OPTION2_NAME,
NULL AS PERIOD_NAME,
SUBSTR(FEM_DS.MEANING,1,30) AS SERIES_NAME,
NULL AS DIM_LEVEL1_NAME,NULL AS DIM_LEVEL2_NAME,NULL AS DIM_LEVEL3_NAME,NULL AS DIM_LEVEL4_NAME,
NULL AS DIM_LEVEL5_NAME,NULL AS DIM_LEVEL6_NAME,NULL AS DIM_LEVEL7_NAME,NULL AS DIM_LEVEL8_NAME,
NULL AS DIM_LEVEL1_TEXT,NULL AS DIM_LEVEL2_TEXT,NULL AS DIM_LEVEL3_TEXT,NULL AS DIM_LEVEL4_TEXT,
NULL AS DIM_LEVEL5_TEXT,NULL AS DIM_LEVEL6_TEXT,NULL AS DIM_LEVEL7_TEXT,NULL AS DIM_LEVEL8_TEXT
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DS
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_AO_NAMES'' AND
FEM.LOOKUP_CODE = ''0'' AND
FEM_DS.LOOKUP_TYPE =''BSC_TPLATE_TAB_DSERIES_NAME'' AND
FEM_DS.LOOKUP_CODE = ''0'' AND
FEM.LANGUAGE = FEM_DS.LANGUAGE';
l_sql_stmt := ' INSERT INTO BSC_KPI_DEFAULTS_TL
(TAB_ID,INDICATOR,LANGUAGE,SOURCE_LANG,
ANALYSIS_OPTION0_NAME,ANALYSIS_OPTION1_NAME,
ANALYSIS_OPTION2_NAME,
PERIOD_NAME,SERIES_NAME,
DIM_LEVEL1_NAME,DIM_LEVEL2_NAME,DIM_LEVEL3_NAME,DIM_LEVEL4_NAME,
DIM_LEVEL5_NAME,DIM_LEVEL6_NAME,DIM_LEVEL7_NAME,DIM_LEVEL8_NAME,
DIM_LEVEL1_TEXT,DIM_LEVEL2_TEXT,DIM_LEVEL3_TEXT,DIM_LEVEL4_TEXT,
DIM_LEVEL5_TEXT,DIM_LEVEL6_TEXT,DIM_LEVEL7_TEXT,DIM_LEVEL8_TEXT)
SELECT '
||G_Ind_Tbl(k_indicator).Tab||' AS TAB_ID,'
||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
FEM.LANGUAGE AS LANGUAGE,
FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,30) AS ANALYSIS_OPTION0_NAME,
NULL AS ANALYSIS_OPTION1_NAME,
NULL AS ANALYSIS_OPTION2_NAME,
NULL AS PERIOD_NAME,
SUBSTR(FEM_DS.MEANING,1,30) AS SERIES_NAME,
SUBSTR(FEM_ATYPE.MEANING,1,80) AS DIM_LEVEL1_NAME,
SUBSTR(FEM_ACCOUNT.MEANING,1,80) AS DIM_LEVEL2_NAME,
SUBSTR(FEM_SUBACCOUNT.MEANING,1,80) AS DIM_LEVEL3_NAME,NULL AS DIM_LEVEL4_NAME,
NULL AS DIM_LEVEL5_NAME,NULL AS DIM_LEVEL6_NAME,NULL AS DIM_LEVEL7_NAME,NULL AS DIM_LEVEL8_NAME,
NULL AS DIM_LEVEL1_TEXT,NULL AS DIM_LEVEL2_TEXT,NULL AS DIM_LEVEL3_TEXT,NULL AS DIM_LEVEL4_TEXT,
NULL AS DIM_LEVEL5_TEXT,NULL AS DIM_LEVEL6_TEXT,NULL AS DIM_LEVEL7_TEXT,NULL AS DIM_LEVEL8_TEXT
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DS, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ATYPE, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ACCOUNT, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_SUBACCOUNT
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_AO_NAMES'' AND
FEM.LOOKUP_CODE = ''1'' AND
FEM_DS.LOOKUP_TYPE =''BSC_TPLATE_TAB_DSERIES_NAME'' AND
FEM_DS.LOOKUP_CODE = ''0'' AND
FEM_ATYPE.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
FEM_ATYPE.LOOKUP_CODE = ''2'' AND
FEM_ACCOUNT.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
FEM_ACCOUNT.LOOKUP_CODE = ''0'' AND
FEM_SUBACCOUNT.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
FEM_SUBACCOUNT.LOOKUP_CODE = ''1'' AND
FEM.LANGUAGE = FEM_DS.LANGUAGE AND
FEM_DS.LANGUAGE = FEM_ATYPE.LANGUAGE AND
FEM_ATYPE.LANGUAGE = FEM_ACCOUNT.LANGUAGE AND
FEM_SUBACCOUNT.LANGUAGE = FEM_ACCOUNT.LANGUAGE';
l_debug_stmt := 'Inserting BSC_KPI_PROPERTIES, k_indicator=' ||
to_char(k_indicator) ||
', l_variable=' || to_char(l_variable) ||
', PROPERTY_CODE=' || G_Var_Tbl(l_variable).Code ||
', PROPERTY_VALUE=' || to_char(G_Var_Tbl(l_variable).Value);
Insert Into BSC_KPI_PROPERTIES (
INDICATOR,
PROPERTY_CODE,
PROPERTY_VALUE,
SECONDARY_VALUE )
Values (
G_Ind_Tbl(k_indicator).Indicator,
G_Var_Tbl(l_variable).code, -- variable
G_Var_Tbl(l_variable).Value, -- value_r
NULL -- secondary_value
);
l_debug_stmt := 'Inserting BSC_KPIS_TL, k_indicator=' ||
to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_stmt := 'INSERT INTO BSC_KPIS_TL
(INDICATOR,LANGUAGE,SOURCE_LANG,NAME,HELP)
SELECT '||G_Ind_Tbl(k_indicator).Indicator|| ' AS INDICATOR,
FEM.LANGUAGE AS LANGUAGE,FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,50) AS NAME,
SUBSTR(FEM.MEANING,1,25)|| '' '' ||SUBSTR(FEM_DESC.MEANING,1,25) AS HELP
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DESC
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_KPIS'' AND
FEM.LOOKUP_CODE = :1 AND
FEM_DESC.LOOKUP_TYPE = :2 AND
FEM_DESC.LOOKUP_CODE = :3 AND
FEM.LANGUAGE = FEM_DESC.LANGUAGE';
l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_GROUPS, Indicator=' ||
to_char(G_Ind_Tbl(k_indicator).Indicator) ||
', k_indicator=' || to_char(k_indicator);
Insert Into BSC_KPI_ANALYSIS_GROUPS (
INDICATOR,
ANALYSIS_GROUP_ID,
NUM_OF_OPTIONS,
DEPENDENCY_FLAG,
PARENT_ANALYSIS_ID,
CHANGE_DIM_SET,
DEFAULT_VALUE )
Values (
G_Ind_Tbl(k_indicator).Indicator,
0, -- analysis
1, -- number_of_options
0, -- dependency
0, -- parent
NULL, -- changes_drill
0 -- default_value
);
l_sql := 'Insert Into BSC_KPI_CALCULATIONS '||
'(INDICATOR, CALCULATION_ID, USER_LEVEL0,'||
'USER_LEVEL1,USER_LEVEL1_DEFAULT, USER_LEVEL2,'||
'USER_LEVEL2_DEFAULT, DEFAULT_VALUE ) values('||
':1,:2,:3,:4,null,null,null,0 )';
l_debug_stmt := 'Inserting BSC_KPI_CALCULATIONS' ||
', k_indicator=' || to_char(k_indicator) ||
', m_calculation=' || to_char(m_calculation) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_KPI_CALCULATIONS (
INDICATOR,
CALCULATION_ID,
USER_LEVEL0,
USER_LEVEL1,
USER_LEVEL1_DEFAULT,
USER_LEVEL2,
USER_LEVEL2_DEFAULT,
DEFAULT_VALUE )
Values (
G_Ind_Tbl(k_indicator).Indicator,
11, -- calculation
2, -- ev0
2, -- ev1
NULL, -- ev1d
NULL, -- ev2
NULL, -- ev2d
0 -- value_r
);
SELECT bsc_kpi_measure_s.NEXTVAL INTO l_kpi_measure_id from dual;
l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_MEASURES_B' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_KPI_ANALYSIS_MEASURES_B (
INDICATOR,
ANALYSIS_OPTION0,
ANALYSIS_OPTION1,
ANALYSIS_OPTION2,
SERIES_ID,
DATASET_ID,
AXIS,
SERIES_TYPE,
STACK_SERIES_ID,
BM_FLAG,
BUDGET_FLAG,
DEFAULT_VALUE,
SERIES_COLOR,
BM_COLOR,
KPI_MEASURE_ID)
Values (
G_Ind_Tbl(k_indicator).Indicator,
0, -- analysis_option0
0, -- analysis_option1
0, -- analysis_option2
0, -- serie
l_data_code, -- data_code
1, -- axis
1, -- series_type
NULL, -- stack
1, -- reference
1, -- plan_series
1, -- default_r,
10053171,
10053171,
l_kpi_measure_id
);
l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_MEASURES_TL' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_stmt := 'INSERT INTO BSC_KPI_ANALYSIS_MEASURES_TL
(INDICATOR,ANALYSIS_OPTION0,ANALYSIS_OPTION1,ANALYSIS_OPTION2,
SERIES_ID,LANGUAGE,SOURCE_LANG,NAME,HELP)
SELECT '
||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
0 AS ANALYSIS_OPTION0,
0 AS ANALYSIS_OPTION1,
0 AS ANALYSIS_OPTION2,
0 AS SERIES_ID,
FEM.LANGUAGE AS LANGUAGE,
FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,20) AS NAME,
SUBSTR(FEM.MEANING,1,80) AS HELP
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DSERIES_NAME'' AND
FEM.LOOKUP_CODE = ''0''';
l_debug_stmt := 'Inserting BSC_KPI_MEASURE_PROPS' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_debug_stmt := 'Inserting BSC_COLOR_TYPE_PROPS and BSC_COLOR_RANGES' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_debug_stmt := 'Inserting BSC_KPI_DIM_SETS_TL' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_stmt := 'INSERT INTO BSC_KPI_DIM_SETS_TL
(INDICATOR,DIM_SET_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
SELECT '||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
0 AS DIM_SET_ID,
FEM.LANGUAGE AS LANGUAGE,
FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,20) AS NAME,
1,SYSDATE,1,SYSDATE
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_SET_NAME'' AND
FEM.LOOKUP_CODE = ''0''';
l_debug_stmt := 'Inserting BSC_KPI_DIM_LEVELS_B' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_KPI_DIM_LEVELS_B (
INDICATOR,
DIM_SET_ID,
DIM_LEVEL_INDEX,
LEVEL_TABLE_NAME,
LEVEL_VIEW_NAME,
FILTER_COLUMN,
FILTER_VALUE,
DEFAULT_VALUE,
DEFAULT_TYPE,
VALUE_ORDER_BY,
COMP_ORDER_BY,
LEVEL_PK_COL,
PARENT_LEVEL_INDEX,
PARENT_LEVEL_REL,
TABLE_RELATION,
PARENT_LEVEL_INDEX2,
PARENT_LEVEL_REL2,
STATUS,
PARENT_IN_TOTAL,
POSITION,
TOTAL0,
LEVEL_DISPLAY,
NO_ITEMS,
DEFAULT_KEY_VALUE,
USER_LEVEL0,
USER_LEVEL1,
USER_LEVEL1_DEFAULT,
USER_LEVEL2,
USER_LEVEL2_DEFAULT
)
Values (
G_Ind_Tbl(k_indicator).Indicator,
0, -- configuration
G_Drill_Tbl(l_drill_ind).Dim_level_index, -- drill
G_Drill_Tbl(l_drill_ind).Table_Name, -- master_T
G_Drill_Tbl(l_drill_ind).Level_View_Name,-- Level View Name
NULL, -- condition_field
G_Drill_Tbl(l_drill_ind).Filter_Val, -- condition_value
G_Drill_Tbl(l_drill_ind).Default_val, -- init
G_Drill_Tbl(l_drill_ind).Default_type, -- init_type
G_Drill_Tbl(l_drill_ind).Value_Order, -- order_r
G_Drill_Tbl(l_drill_ind).Comp_Order, -- order_r
G_Drill_Tbl(l_drill_ind).Level_pk_col, -- field_n
G_Drill_Tbl(l_drill_ind).Parent, -- parent
G_Drill_Tbl(l_drill_ind).Parent_Rel, -- parent_relation
G_Drill_Tbl(l_drill_ind).Table_Rel, -- table_relation
G_Drill_Tbl(l_drill_ind).Parent2, -- parent2
G_Drill_Tbl(l_drill_ind).Parent_Rel2, -- parent_relation2
G_Drill_Tbl(l_drill_ind).Status, -- status
2, -- status_whn_parnt_is_total
G_Drill_Tbl(l_drill_ind).Position, -- position
G_Drill_Tbl(l_drill_ind).Total0, -- total0
0, -- LEVEL_DISPLAY
0, -- No items
NULL, -- Key Value
G_Drill_Tbl(l_drill_ind).Ev0, -- ev0
G_Drill_Tbl(l_drill_ind).Ev0, -- ev1
G_Drill_Tbl(l_drill_ind).Ev1d, -- ev1d
0, -- ev2
0 -- ev2d
);
l_debug_stmt := 'Inserting BSC_KPI_DIM_LEVELS_TL' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_stmt := 'INSERT INTO BSC_KPI_DIM_LEVELS_TL
(INDICATOR,DIM_SET_ID,DIM_LEVEL_INDEX,LANGUAGE,SOURCE_LANG,NAME,
HELP,TOTAL_DISP_NAME,COMP_DISP_NAME)
SELECT '
||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
0 AS DIM_SET_ID,'
||G_Drill_Tbl(l_drill_ind).Dim_level_index||' AS DIM_LEVEL_INDEX,
FEM_ALL.LANGUAGE AS LANGUAGE,
FEM_ALL.SOURCE_LANG AS SOURCE_LANG,
''XXX'' AS NAME,
''XXX'' AS HELP,
SUBSTR(FEM_ALL.MEANING,1,15) AS TOTAL_DISP_NAME,
SUBSTR(FEM_COMP.MEANING,1,15) AS COMP_DISP_NAME
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALL, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_COMP
WHERE FEM_ALL.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
FEM_ALL.LOOKUP_CODE = ''ALL'' AND
FEM_COMP.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
FEM_COMP.LOOKUP_CODE = ''COMPARISON'' AND
FEM_COMP.LANGUAGE = FEM_ALL.LANGUAGE';
l_sql_stmt := 'INSERT INTO BSC_KPI_DIM_LEVELS_TL
(INDICATOR,DIM_SET_ID,DIM_LEVEL_INDEX,LANGUAGE,SOURCE_LANG,NAME,
HELP,TOTAL_DISP_NAME,COMP_DISP_NAME)
SELECT '
||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
0 AS DIM_SET_ID,'
||G_Drill_Tbl(l_drill_ind).Dim_level_index||' AS DIM_LEVEL_INDEX,
FEM.LANGUAGE AS LANGUAGE,
FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,30) AS NAME,
SUBSTR(FEM.MEANING,1,80) AS HELP,
SUBSTR(FEM_ALL.MEANING,1,15) AS TOTAL_DISP_NAME,
SUBSTR(FEM_COMP.MEANING,1,15) AS COMP_DISP_NAME
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALL, '
||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_COMP
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
FEM.LOOKUP_CODE = '''||G_Drill_Tbl(l_drill_ind).dim_level_id||''' AND
FEM_ALL.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
FEM_ALL.LOOKUP_CODE = ''ALL'' AND
FEM_COMP.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
FEM_COMP.LOOKUP_CODE = ''COMPARISON'' AND
FEM.LANGUAGE = FEM_ALL.LANGUAGE AND
FEM_COMP.LANGUAGE = FEM_ALL.LANGUAGE';
l_debug_stmt := 'Inserting BSC_KPI_DIM_GROUPS' ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator) ||
', Family_Code= ' || to_char(G_Drill_Tbl(l_drill_ind).dim_group_id);
Insert Into BSC_KPI_DIM_GROUPS (
INDICATOR,
DIM_SET_ID,
DIM_GROUP_ID,
DIM_GROUP_INDEX )
Values (
G_Ind_Tbl(k_indicator).Indicator,
0,
G_Drill_Tbl(l_drill_ind).dim_group_id,
G_Drill_Tbl(l_drill_ind).dim_group_idx
);
l_debug_stmt := 'Inserting BSC_KPI_DIM_LEVEL_PROPERTIES' ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator) ||
', Entity_Code= ' || to_char(G_Drill_Tbl(l_drill_ind).dim_level_id);
Insert Into BSC_KPI_DIM_LEVEL_PROPERTIES (
INDICATOR,
DIM_SET_ID,
DIM_LEVEL_ID,
POSITION,
TOTAL0,
LEVEL_DISPLAY,
DEFAULT_KEY_VALUE,
USER_LEVEL0,
USER_LEVEL1,
USER_LEVEL1_DEFAULT,
USER_LEVEL2,
USER_LEVEL2_DEFAULT
)
Values (
G_Ind_Tbl(k_indicator).Indicator,
0,
G_Drill_Tbl(l_drill_ind).dim_level_id,
G_Drill_Tbl(l_drill_ind).Position,
G_Drill_Tbl(l_drill_ind).Total0,
G_Drill_Tbl(l_drill_ind).level_display,
NULL,
G_Drill_Tbl(l_drill_ind).Ev0, -- ev0
G_Drill_Tbl(l_drill_ind).Ev0, -- ev1
G_Drill_Tbl(l_drill_ind).Ev1d, -- ev1d
NULL, -- ev2
NULL -- ev2d
);
l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_OPTIONS_B' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_defaults_b := 'Insert Into BSC_KPI_ANALYSIS_OPTIONS_B (INDICATOR,'||
'ANALYSIS_GROUP_ID,OPTION_ID,PARENT_OPTION_ID,GRANDPARENT_OPTION_ID,'||
'DIM_SET_ID,USER_LEVEL0,USER_LEVEL1,USER_LEVEL1_DEFAULT,USER_LEVEL2,'||
'USER_LEVEL2_DEFAULT )Values (:1,0,0,0,0,0,1,1,NULL,NULL,NULL)';
l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_OPTIONS_TL' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_stmt := 'INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_TL
(INDICATOR,ANALYSIS_GROUP_ID,OPTION_ID,PARENT_OPTION_ID,GRANDPARENT_OPTION_ID,
LANGUAGE,SOURCE_LANG,NAME,HELP)
SELECT '
||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
0 AS ANALYSIS_GROUP_ID,
0 AS OPTION_ID,
0 AS PARENT_OPTION_ID,
0 AS GRANDPARENT_OPTION_ID,
FEM.LANGUAGE AS LANGUAGE,
FEM.SOURCE_LANG AS SOURCE_LANG,
SUBSTR(FEM.MEANING,1,25) AS NAME,
SUBSTR(FEM.MEANING,1,80) AS HELP
FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM
WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_AO_NAMES'' AND
FEM.LOOKUP_CODE =:1';
l_debug_stmt := 'Inserting BSC_KPI_PERIODICITIES' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_KPI_PERIODICITIES (
INDICATOR,
PERIODICITY_ID,
DISPLAY_ORDER,
PREVIOUS_YEARS,
NUM_OF_YEARS,
VIEWPORT_FLAG,
VIEWPORT_DEFAULT_SIZE,
USER_LEVEL0,
USER_LEVEL1,
USER_LEVEL1_DEFAULT,
USER_LEVEL2,
USER_LEVEL2_DEFAULT,
CURRENT_PERIOD,
LAST_UPDATE_DATE)
Values (
G_Ind_Tbl(k_indicator).Indicator,
G_Period_Tbl(p_period).Period_Type, -- periodicity_type
l_option_r, -- option_r
G_Period_Tbl(p_period).Prev_Year, -- previous_years
G_Period_Tbl(p_period).Num_Years, -- number_of_years
G_Period_Tbl(p_period).Viewport_flag, -- viewport
G_Period_Tbl(p_period).Viewport_Size, -- viewport_default_size
level_per, -- ev0
level_per, -- ev1
NULL, -- ev1d
NULL, -- ev2
NULL, -- ev2d
1,
SYSDATE
);
l_debug_stmt := 'Inserting BSC_KPI_DATA_TABLES' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
l_sql_defaults_b := 'Insert Into BSC_KPI_DATA_TABLES (INDICATOR,PERIODICITY_ID,'||
'DIM_SET_ID,LEVEL_COMB,TABLE_NAME,FILTER_CONDITION )Values ('||
':1,:2,0,'||
'''?'''||
',NULL,NULL)';
l_debug_stmt := 'Inserting BSC_TAB_INDICATORS' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_TAB_INDICATORS (
TAB_ID,
INDICATOR,
BSC_MODEL_FLAG,
LEFT_POSITION,
TOP_POSITION,
WIDTH,
HEIGHT,
BACKCOLOR )
Values (
G_Ind_Tbl(k_indicator).Tab,
G_Ind_Tbl(k_indicator).Indicator,
0, 0, 0, 0, 0, 0
);
l_debug_stmt := 'Inserting BSC_KPI_PERIODICITIES' ||
', k_indicator=' || to_char(k_indicator) ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_KPI_PERIODICITIES (
INDICATOR,
PERIODICITY_ID,
DISPLAY_ORDER,
PREVIOUS_YEARS,
NUM_OF_YEARS,
VIEWPORT_FLAG,
VIEWPORT_DEFAULT_SIZE,
USER_LEVEL0,
USER_LEVEL1,
USER_LEVEL1_DEFAULT,
USER_LEVEL2,
USER_LEVEL2_DEFAULT,
CURRENT_PERIOD,
LAST_UPDATE_DATE
)
Values (
G_Ind_Tbl(k_indicator).Indicator,
5, -- periodicity_type
0, -- option_r
0, -- previous_years
0, -- number_of_years
0, -- viewport
0, -- viewport_default_size
2, -- ev0
2, -- ev1
NULL, -- ev1d
NULL, -- ev2
NULL, -- ev2d
1,
SYSDATE
);
l_debug_stmt := 'Inserting BSC_SYS_USER_OPTIONS at System Level' ||
', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
Insert Into BSC_SYS_USER_OPTIONS(
SOURCE_TYPE,
SOURCE_CODE,
USER_OPT_ID,
ENABLED_FLAG,
DISPLAY_FLAG)
Values (2,
G_Ind_Tbl(k_indicator).Indicator,
l_user_options (l_variable),
0,
1);
update BSC_KPI_PERIODICITIES
set CURRENT_PERIOD = (
SELECT CURRENT_YEAR
FROM BSC_SYS_CALENDARS_B
WHERE CALENDAR_ID=1
)
where PERIODICITY_ID = 1;
l_debug_stmt := 'Inserting BSC_SYS_USER_OPTIONS at System Level';
Insert Into BSC_SYS_USER_OPTIONS(
SOURCE_TYPE,SOURCE_CODE,USER_OPT_ID,ENABLED_FLAG,DISPLAY_FLAG)
Values (0,0,1,0,1);
Insert Into BSC_SYS_USER_OPTIONS(
SOURCE_TYPE,SOURCE_CODE,USER_OPT_ID,ENABLED_FLAG,DISPLAY_FLAG)
Values (0,0,2,0,1);
l_sql_stmt := 'SELECT NVL(MAX(DIM_LEVEL_ID),0) FROM BSC_SYS_DIM_LEVELS_B';
l_sql_stmt := 'SELECT NVL(MAX(DIM_GROUP_ID),0) FROM BSC_SYS_DIM_GROUPS_TL';
l_sql_stmt := 'SELECT NVL(MAX(DATASET_ID),0) FROM BSC_SYS_DATASETS_B';
l_sql_stmt := 'SELECT NVL(MAX(MEASURE_ID),0) FROM BSC_SYS_MEASURES';