eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_UPDATE_COLOR dependencies on BSC_UPDATE_UTIL

Line 8: , p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number

4:
5: FUNCTION Color_Kpi_Measure (
6: p_kpi_measure_id IN NUMBER
7: , p_objective_color_rec IN t_objective_color_rec
8: , p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number
9: , p_num_families IN NUMBER
10: )
11: RETURN BOOLEAN;
12:

Line 96: h_dim_combination BSC_UPDATE_UTIL.t_array_of_number;

92: TYPE t_cursor IS REF CURSOR;
93: h_cursor t_cursor;
94:
95: h_sql VARCHAR2(2000);
96: h_dim_combination BSC_UPDATE_UTIL.t_array_of_number;
97: h_num_families NUMBER;
98: h_last_com_index NUMBER;
99: h_com_index NUMBER;
100: h_family_index NUMBER;

Line 120: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_START');

116: -- We will force the color calculation of all the KPIs under the Objective irrespective of the
117: -- prototype_flag being 7 or not.
118: IF (l_objective_kpis.disable_color IS NULL OR l_objective_kpis.disable_color <> 'T') THEN
119: IF(l_ag_report = FALSE OR (l_ag_report = TRUE and l_objective_kpis.source <> 'CDS')) THEN
120: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_START');
121: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
122: BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
123:
124: -- color per each combination of dimension of different families

Line 121: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));

117: -- prototype_flag being 7 or not.
118: IF (l_objective_kpis.disable_color IS NULL OR l_objective_kpis.disable_color <> 'T') THEN
119: IF(l_ag_report = FALSE OR (l_ag_report = TRUE and l_objective_kpis.source <> 'CDS')) THEN
120: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_START');
121: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
122: BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
123:
124: -- color per each combination of dimension of different families
125: -- in the tab list

Line 168: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_COMPLETE');

164: , h_num_families) THEN
165: RAISE e_unexpected_error;
166: END IF;
167:
168: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_COMPLETE');
169: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
170: BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
171:
172: COMMIT;

Line 169: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));

165: RAISE e_unexpected_error;
166: END IF;
167:
168: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_COMPLETE');
169: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
170: BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
171:
172: COMMIT;
173:

Line 175: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_SKIP');

171:
172: COMMIT;
173:
174: ELSE
175: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_SKIP');
176: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
177: BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
178: END IF;
179: END IF;

Line 176: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));

172: COMMIT;
173:
174: ELSE
175: l_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_KPI_SKIP');
176: l_message := BSC_UPDATE_UTIL.Replace_Token(l_message, 'KPI_MEASURE_ID', TO_CHAR(l_objective_kpis.kpi_measure_id));
177: BSC_UPDATE_LOG.Write_Line_log(l_message, BSC_UPDATE_LOG.OUTPUT);
178: END IF;
179: END IF;
180: END LOOP;

Line 187: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),

183:
184: EXCEPTION
185: WHEN e_unexpected_error THEN
186: ROLLBACK;
187: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
188: x_source => 'BSC_UPDATE_COLOR.Color_Kpis_In_Objective');
189: RETURN FALSE;
190: WHEN OTHERS THEN
191: ROLLBACK;

Line 201: , p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number

197:
198: FUNCTION Color_Kpi_Measure (
199: p_kpi_measure_id IN NUMBER
200: , p_objective_color_rec IN t_objective_color_rec
201: , p_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number
202: , p_num_families IN NUMBER
203: )
204: RETURN BOOLEAN IS
205:

Line 207: l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;

203: )
204: RETURN BOOLEAN IS
205:
206: e_unexpected_error EXCEPTION;
207: l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
208: l_measure_formula bsc_db_color_measures_v.measure_formula%TYPE;
209: l_color_by_total bsc_kpi_measure_props.color_by_total%TYPE;
210: l_apply_color_flag bsc_kpi_measure_props.apply_color_flag%TYPE;
211: l_calc_color_flag BOOLEAN;

Line 216: BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => p_objective_color_rec.objective_id

212:
213: BEGIN
214:
215: -- Get the dim_set_id and comp_level_pk_col for the KPI Measure
216: BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => p_objective_color_rec.objective_id
217: , p_kpi_measure_id => p_kpi_measure_id
218: , x_dim_props_rec => l_kpi_dim_props
219: );
220:

Line 222: l_measure_formula := BSC_UPDATE_UTIL.Get_Measure_Formula ( p_objective_id => p_objective_color_rec.objective_id

218: , x_dim_props_rec => l_kpi_dim_props
219: );
220:
221: -- Get the measure formula
222: l_measure_formula := BSC_UPDATE_UTIL.Get_Measure_Formula ( p_objective_id => p_objective_color_rec.objective_id
223: , p_kpi_measure_id => p_kpi_measure_id
224: , p_sim_objective => p_objective_color_rec.sim_flag
225: );
226:

Line 227: l_measure_formula := BSC_UPDATE_UTIL.Get_Free_Div_Zero_Expression(l_measure_formula);

223: , p_kpi_measure_id => p_kpi_measure_id
224: , p_sim_objective => p_objective_color_rec.sim_flag
225: );
226:
227: l_measure_formula := BSC_UPDATE_UTIL.Get_Free_Div_Zero_Expression(l_measure_formula);
228: IF l_measure_formula IS NULL THEN
229: RAISE e_unexpected_error;
230: END IF;
231:

Line 232: l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => p_objective_color_rec.objective_id

228: IF l_measure_formula IS NULL THEN
229: RAISE e_unexpected_error;
230: END IF;
231:
232: l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => p_objective_color_rec.objective_id
233: , p_kpi_measure_id => p_kpi_measure_id
234: );
235:
236: IF l_color_by_total IS NULL THEN

Line 240: l_apply_color_flag := BSC_UPDATE_UTIL.Get_Apply_Color_Flag ( p_objective_id => p_objective_color_rec.objective_id

236: IF l_color_by_total IS NULL THEN
237: RAISE e_unexpected_error;
238: END IF;
239:
240: l_apply_color_flag := BSC_UPDATE_UTIL.Get_Apply_Color_Flag ( p_objective_id => p_objective_color_rec.objective_id
241: , p_kpi_measure_id => p_kpi_measure_id
242: --, p_sim_objective => p_objective_color_rec.sim_flag
243: );
244:

Line 280: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),

276:
277: EXCEPTION
278: WHEN e_unexpected_error THEN
279: ROLLBACK;
280: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
281: x_source => 'BSC_UPDATE_COLOR.Color_Kpi_Measure');
282: RETURN FALSE;
283: WHEN OTHERS THEN
284: ROLLBACK;

Line 346: l_lst_keys_insert_array BSC_UPDATE_UTIL.t_array_of_varchar2;

342: l_tmp_cumpercent_diff NUMBER;
343: l_num_keys NUMBER;
344: l_mcc_cursor t_cursor;
345: l_mcc_actual_plan_cursor t_cursor;
346: l_lst_keys_insert_array BSC_UPDATE_UTIL.t_array_of_varchar2;
347: l_lst_keys_array BSC_UPDATE_UTIL.t_array_of_varchar2;
348:
349: BEGIN
350:

Line 347: l_lst_keys_array BSC_UPDATE_UTIL.t_array_of_varchar2;

343: l_num_keys NUMBER;
344: l_mcc_cursor t_cursor;
345: l_mcc_actual_plan_cursor t_cursor;
346: l_lst_keys_insert_array BSC_UPDATE_UTIL.t_array_of_varchar2;
347: l_lst_keys_array BSC_UPDATE_UTIL.t_array_of_varchar2;
348:
349: BEGIN
350:
351: -- p_lst_keys_insert can be of the following format:

Line 356: l_num_keys := BSC_UPDATE_UTIL.decompose_varchar2_list(l_lst_keys_insert, l_lst_keys_insert_array, ',');

352: -- KEY1, 0, 0, 0, 0, 0, 0, 0,
353: -- 0, KEY1, 0, 0, 0, 0, 0, 0,
354: -- Remove trailing comma (,)
355: l_lst_keys_insert := SUBSTR(p_lst_keys_insert, 1, instr(p_lst_keys_insert, ',', -1) - 1);
356: l_num_keys := BSC_UPDATE_UTIL.decompose_varchar2_list(l_lst_keys_insert, l_lst_keys_insert_array, ',');
357: FOR l_index IN 1 .. l_lst_keys_insert_array.COUNT LOOP
358: IF (l_lst_keys_insert_array(l_index) = '0') THEN
359: l_lst_keys_insert_array(l_index) := '''$#''';
360: END IF;

Line 362: l_lst_keys_insert := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_lst_keys_insert_array, l_num_keys);

358: IF (l_lst_keys_insert_array(l_index) = '0') THEN
359: l_lst_keys_insert_array(l_index) := '''$#''';
360: END IF;
361: END LOOP;
362: l_lst_keys_insert := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_lst_keys_insert_array, l_num_keys);
363:
364: l_color_method := get_color_method(p_objective_id, p_kpi_measure_id);
365:
366: l_sql_mcc := 'SELECT ' || l_lst_keys_insert || ', period, col_b.color_id FROM ( ' ||

Line 691: x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,

687: x_indic_pl_flag IN BOOLEAN,
688: x_indic_initiatives_flag IN BOOLEAN,
689: x_indic_precalculated_flag IN BOOLEAN,
690: x_tab_id IN NUMBER,
691: x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
692: x_num_families IN NUMBER,
693: x_periodicity_id IN NUMBER,
694: x_comp_level_pk_col IN VARCHAR2,
695: x_dim_set_id IN NUMBER,

Line 720: h_dim_com_keys BSC_UPDATE_UTIL.t_array_of_varchar2;

716: h_family_index NUMBER;
717: h_level_pk_col VARCHAR2(30);
718: h_level_table_name VARCHAR2(30);
719:
720: h_dim_com_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
721:
722: -- same info than h_dim_com_keys but the array is from 1 to x_num_families
723: h_dim_com_keys_1 BSC_UPDATE_UTIL.t_array_of_varchar2;
724:

Line 723: h_dim_com_keys_1 BSC_UPDATE_UTIL.t_array_of_varchar2;

719:
720: h_dim_com_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
721:
722: -- same info than h_dim_com_keys but the array is from 1 to x_num_families
723: h_dim_com_keys_1 BSC_UPDATE_UTIL.t_array_of_varchar2;
724:
725: h_table_name VARCHAR2(30);
726:
727: h_condition VARCHAR2(2000);

Line 730: h_arr_keys BSC_UPDATE_UTIL.t_array_of_varchar2;

726:
727: h_condition VARCHAR2(2000);
728: h_condition_b VARCHAR2(2000);
729:
730: h_arr_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
731: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
732: h_num_keys NUMBER;
733: h_lst_keys VARCHAR2(2000);
734: h_lst_keys_temp VARCHAR2(2000);

Line 731: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;

727: h_condition VARCHAR2(2000);
728: h_condition_b VARCHAR2(2000);
729:
730: h_arr_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
731: h_key_columns_temp BSC_UPDATE_UTIL.t_array_of_varchar2;
732: h_num_keys NUMBER;
733: h_lst_keys VARCHAR2(2000);
734: h_lst_keys_temp VARCHAR2(2000);
735: h_key_index NUMBER;

Line 759: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

755:
756: h_sql_mcc VARCHAR2(32000);
757:
758: -- BSC-BIS-DIMENSIONS: Need to use varchar2 to suppoer NUMBER/VARCHAR2
759: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
760: h_num_bind_vars NUMBER;
761:
762: h_num_bind_vars_1 NUMBER;
763: h_level_comb VARCHAR2(30);

Line 765: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;

761:
762: h_num_bind_vars_1 NUMBER;
763: h_level_comb VARCHAR2(30);
764:
765: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
766: h_num_bind_vars_n NUMBER;
767:
768: CURSOR c_table_columns (p_table_name VARCHAR2, p_column_type VARCHAR2) IS
769: SELECT column_name

Line 810: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_id);

806: h_aw_limit_tbl := BIS_PMV_PAGE_PARAMETER_TBL();
807: h_aw_limit_tbl.delete;
808:
809: -- AW_INTEGRATION: move this line here
810: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_id);
811: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_id);
812:
813: -- Initialize the array h_dim_com_keys: key column names of dimension combinations
814: -- Example: BRACH_CODE|PROD_CODE

Line 811: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_id);

807: h_aw_limit_tbl.delete;
808:
809: -- AW_INTEGRATION: move this line here
810: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(x_periodicity_id);
811: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(x_periodicity_id);
812:
813: -- Initialize the array h_dim_com_keys: key column names of dimension combinations
814: -- Example: BRACH_CODE|PROD_CODE
815:

Line 929: h_sql := 'select min('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||'),'||

925:
926: h_per_parameter_value := h_min_per||'.'||h_min_per||' TO '||h_max_per||'.'||h_max_per;
927: ELSE
928: -- Other periodicity
929: h_sql := 'select min('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||'),'||
930: ' max('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||')'||
931: ' from bsc_db_calendar'||
932: ' where calendar_id = :1 and year = :2';
933: OPEN h_cursor FOR h_sql USING h_calendar_id, x_current_fy;

Line 930: ' max('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||')'||

926: h_per_parameter_value := h_min_per||'.'||h_min_per||' TO '||h_max_per||'.'||h_max_per;
927: ELSE
928: -- Other periodicity
929: h_sql := 'select min('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||'),'||
930: ' max('||BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(x_periodicity_id)||')'||
931: ' from bsc_db_calendar'||
932: ' where calendar_id = :1 and year = :2';
933: OPEN h_cursor FOR h_sql USING h_calendar_id, x_current_fy;
934: FETCH h_cursor INTO h_min_per, h_max_per;

Line 1104: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_arr_keys, h_num_keys);

1100: h_arr_keys(h_num_keys) := x_comp_level_pk_col;
1101: h_key_columns_temp(h_num_keys) := 'KEY'||h_num_keys;
1102: END IF;
1103:
1104: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_arr_keys, h_num_keys);
1105: h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', h_num_keys);
1106: IF h_lst_keys IS NOT NULL THEN
1107: h_lst_keys := h_lst_keys||', ';
1108: h_lst_keys_temp := h_lst_keys_temp||', ';

Line 1105: h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', h_num_keys);

1101: h_key_columns_temp(h_num_keys) := 'KEY'||h_num_keys;
1102: END IF;
1103:
1104: h_lst_keys := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_arr_keys, h_num_keys);
1105: h_lst_keys_temp := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', h_num_keys);
1106: IF h_lst_keys IS NOT NULL THEN
1107: h_lst_keys := h_lst_keys||', ';
1108: h_lst_keys_temp := h_lst_keys_temp||', ';
1109: END IF;

Line 1113: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DATA_COLOR');

1109: END IF;
1110:
1111: -- Insert into temporal table BSC_TMP_DATA_COLOR the base data to calculate the color
1112: -- Clean current records
1113: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DATA_COLOR');
1114:
1115: -- I use h_num_bind_vars_1 because h_num_bind_vars is used in the next query
1116: h_num_bind_vars_1 := h_num_bind_vars;
1117:

Line 1154: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);

1150: h_bind_vars_values(h_num_bind_vars_1) := x_current_fy;
1151:
1152: END IF;
1153:
1154: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
1155: COMMIT;
1156:
1157: -- Get the YTD Flag
1158: l_ytd_flag := BSC_UPDATE_UTIL.get_ytd_flag(x_indic_code, x_kpi_measure_id);

Line 1158: l_ytd_flag := BSC_UPDATE_UTIL.get_ytd_flag(x_indic_code, x_kpi_measure_id);

1154: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
1155: COMMIT;
1156:
1157: -- Get the YTD Flag
1158: l_ytd_flag := BSC_UPDATE_UTIL.get_ytd_flag(x_indic_code, x_kpi_measure_id);
1159:
1160: -- Calculate YTD Data if the indicator enter in YTD calculation
1161: IF l_ytd_flag = 1 THEN
1162: -- Update BSC_TMP_DATA_COLOR with the YTD data

Line 1165: h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B', h_arr_keys, 'A', h_key_columns_temp, h_num_keys, 'AND');

1161: IF l_ytd_flag = 1 THEN
1162: -- Update BSC_TMP_DATA_COLOR with the YTD data
1163: -- Drop table if exits
1164:
1165: h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('B', h_arr_keys, 'A', h_key_columns_temp, h_num_keys, 'AND');
1166:
1167: IF h_num_keys > 0 THEN
1168: h_lst_where := h_lst_where||' AND ';
1169: END IF;

Line 1189: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);

1185: ' AND B.TYPE=A.TYPE AND B.PERIOD<=A.PERIOD'||
1186: ' )';
1187: h_num_bind_vars_1 := h_num_bind_vars_1 + 1;
1188: h_bind_vars_values(h_num_bind_vars_1) := x_current_fy;
1189: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars_1);
1190: commit;
1191: END IF;
1192:
1193: -- Initialize temporal table BSC_TMP_COLORS

Line 1195: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_COLORS');

1191: END IF;
1192:
1193: -- Initialize temporal table BSC_TMP_COLORS
1194: -- Clean current records
1195: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_COLORS');
1196:
1197: h_sql := 'INSERT /*+ append */ INTO BSC_TMP_COLORS ('||h_lst_keys_temp||'PERIOD, VPLAN, VREAL, CUMPERCENT, COLOR)'||
1198: ' SELECT '||h_lst_keys_temp||'BSC_TMP_ALL_PERIODS.PERIOD, '||
1199: ' NULL AS VPLAN, NULL AS VREAL, NULL AS CUMPERCENT, '||GRAY||' AS COLOR'||

Line 1203: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1199: ' NULL AS VPLAN, NULL AS VREAL, NULL AS CUMPERCENT, '||GRAY||' AS COLOR'||
1200: ' FROM BSC_TMP_DATA_COLOR, BSC_TMP_ALL_PERIODS'||
1201: ' GROUP BY '||h_lst_keys_temp||'BSC_TMP_ALL_PERIODS.PERIOD';
1202:
1203: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1204:
1205: commit;
1206:
1207: -- Get plan data

Line 1208: h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('BSC_TMP_COLORS', h_key_columns_temp, 'BSC_TMP_DATA_COLOR', h_key_columns_temp, h_num_keys, 'AND');

1204:
1205: commit;
1206:
1207: -- Get plan data
1208: h_lst_where := BSC_UPDATE_UTIL.Make_Lst_Cond_Join('BSC_TMP_COLORS', h_key_columns_temp, 'BSC_TMP_DATA_COLOR', h_key_columns_temp, h_num_keys, 'AND');
1209:
1210: IF h_num_keys > 0 THEN
1211: h_lst_where := h_lst_where||' AND ';
1212: END IF;

Line 1227: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1223: ' FROM BSC_TMP_DATA_COLOR'||
1224: ' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
1225: ' AND BSC_TMP_DATA_COLOR.TYPE = 1'||
1226: ')';
1227: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1228: COMMIT;
1229:
1230: -- Get real data
1231: h_sql := 'UPDATE BSC_TMP_COLORS'||

Line 1244: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1240: ' FROM BSC_TMP_DATA_COLOR'||
1241: ' WHERE '||h_lst_where||' BSC_TMP_COLORS.PERIOD = BSC_TMP_DATA_COLOR.PERIOD'||
1242: ' AND BSC_TMP_DATA_COLOR.TYPE = 0'||
1243: ')';
1244: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1245: COMMIT;
1246:
1247: -- Calculate the color and store in TEMP table BSC_TMP_COLORS
1248: IF NOT Calculate_KPI_Color

Line 1270: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_level_pk_col, h_arr_keys, h_num_keys) THEN

1266:
1267: OPEN h_cursor FOR h_sql USING x_tab_id;
1268: FETCH h_cursor INTO h_dim_level_index, h_level_pk_col;
1269: WHILE h_cursor%FOUND LOOP
1270: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_level_pk_col, h_arr_keys, h_num_keys) THEN
1271: h_lst_keys_insert := h_lst_keys_insert||'KEY'||h_key_index||', ';
1272: h_key_index := h_key_index + 1;
1273: ELSE
1274: h_lst_keys_insert := h_lst_keys_insert||'0, ';

Line 1308: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_families);

1304: -- Note: I exclude the Initiatives indicator because this indicator has x_comp_level_pk_col = 'PROJECT_CODE'
1305: -- and x_color_by_total = 0 but we dont calculte the minimum color. Instead we put the color of each project
1306: -- directly in BSC_SYS_KPI_COLORS in the last dimension level.
1307:
1308: h_lst_select := BSC_UPDATE_UTIL.Make_Lst_Fixed_Column('KEY', x_num_families);
1309: IF h_lst_select IS NOT NULL THEN
1310: h_lst_select := h_lst_select||', ';
1311: END IF;
1312:

Line 1333: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);

1329: h_bind_vars_values_n(2) := x_indic_code;
1330: h_bind_vars_values_n(3) := x_kpi_measure_id;
1331: h_num_bind_vars_n := 3;
1332:
1333: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1334:
1335: COMMIT;
1336:
1337: IF NOT update_actual_budget_for_mcc( p_objective_id => x_indic_code

Line 1369: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);

1365: h_bind_vars_values_n(2) := x_indic_code;
1366: h_bind_vars_values_n(3) := x_kpi_measure_id;
1367: h_num_bind_vars_n := 3;
1368:
1369: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1370:
1371: COMMIT;
1372: END IF;
1373:

Line 1431: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);

1427: h_bind_vars_values_n.delete;
1428: h_bind_vars_values_n(1) := x_tab_id;
1429: h_bind_vars_values_n(2) := x_indic_code;
1430: h_num_bind_vars_n := 2;
1431: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1432: commit;
1433:
1434: h_dim_level_index_child := h_dim_level_index_parent;
1435: h_level_pk_col_child := h_level_pk_col_parent;

Line 1446: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMDATA_NOT_FOUND'),

1442: RETURN TRUE;
1443:
1444: EXCEPTION
1445: WHEN e_no_data_table_error THEN
1446: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMDATA_NOT_FOUND'),
1447: x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1448: RETURN FALSE;
1449:
1450: WHEN e_unexpected_error THEN

Line 1451: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),

1447: x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1448: RETURN FALSE;
1449:
1450: WHEN e_unexpected_error THEN
1451: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
1452: x_source => 'BSC_UPDATE_COLOR.Color_Indic_Dim_Combination');
1453: RETURN FALSE;
1454:
1455: WHEN OTHERS THEN

Line 1482: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;

1478: RETURN BOOLEAN
1479: IS
1480:
1481: h_sql VARCHAR2(32700);
1482: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
1483: h_num_bind_vars_n NUMBER;
1484: l_color_method NUMBER;
1485: C_ALFA VARCHAR2(10);
1486: C_BETA VARCHAR2(10);

Line 1542: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1538: -- Trunc real and plan to 5 decimal
1539: h_sql := 'UPDATE BSC_TMP_COLORS'||
1540: ' SET VREAL = DECODE(VREAL, NULL, VREAL, TRUNC((VREAL + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||'), '||
1541: ' VPLAN = DECODE(VPLAN, NULL, VPLAN, TRUNC((VPLAN + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||')';
1542: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1543: commit;
1544:
1545: -- Calculate percent of variation
1546: -- 0 Fix Bug# 2580240 Case Plan=Real=0

Line 1550: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1546: -- 0 Fix Bug# 2580240 Case Plan=Real=0
1547: h_sql := 'UPDATE BSC_TMP_COLORS'||
1548: ' SET CUMPERCENT = 100'||
1549: ' WHERE (VREAL = 0) AND (VPLAN = 0)';
1550: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1551: commit;
1552:
1553: -- 1
1554: h_sql := 'UPDATE BSC_TMP_COLORS'||

Line 1557: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1553: -- 1
1554: h_sql := 'UPDATE BSC_TMP_COLORS'||
1555: ' SET CUMPERCENT = TRUNC((((VREAL / VPLAN)*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
1556: ' WHERE (VREAL IS NOT NULL) AND (VPLAN > 0)';
1557: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1558: commit;
1559: -- 2
1560: h_sql := 'UPDATE BSC_TMP_COLORS'||
1561: ' SET CUMPERCENT = TRUNC((((2 + ABS(VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||

Line 1563: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1559: -- 2
1560: h_sql := 'UPDATE BSC_TMP_COLORS'||
1561: ' SET CUMPERCENT = TRUNC((((2 + ABS(VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
1562: ' WHERE (VREAL > 0) AND (VPLAN < 0)';
1563: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1564: commit;
1565:
1566: -- 3
1567: h_sql := 'UPDATE BSC_TMP_COLORS'||

Line 1570: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1566: -- 3
1567: h_sql := 'UPDATE BSC_TMP_COLORS'||
1568: ' SET CUMPERCENT = TRUNC((((2 - (VREAL / VPLAN))*100) + '||C_ALFA||') * '||C_BETA||') / '||C_BETA||
1569: ' WHERE (VREAL <= 0) AND (VPLAN < 0)';
1570: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1571: commit;
1572:
1573: l_color_method := Get_Color_Method(p_objective_id, p_kpi_measure_id);
1574:

Line 1590: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1586: ' ELSE '||min_perf_color_id||' END '||
1587: ' WHERE (VREAL = 0 OR VPLAN = 0) '||
1588: ' AND VREAL <> VPLAN';
1589:
1590: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1591: commit;
1592: l_comp_op := '<=';
1593:
1594: ELSIF l_color_method = 2 THEN

Line 1601: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1597: ' SET COLOR = CASE WHEN VREAL > VPLAN THEN '||max_perf_color_id||
1598: ' ELSE '||min_perf_color_id||' END '||
1599: ' WHERE (VREAL = 0 OR VPLAN = 0) '||
1600: ' AND VREAL <> VPLAN';
1601: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1602: commit;
1603: l_comp_op := '<';
1604:
1605:

Line 1610: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

1606: ELSIF l_color_method = 3 THEN
1607: h_sql := 'UPDATE BSC_TMP_COLORS'||
1608: ' SET COLOR = '||min_perf_color_id||
1609: ' WHERE ((VREAL = 0) AND (VPLAN <> 0)) OR ((VREAL <> 0) AND (VPLAN = 0))';
1610: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
1611: commit;
1612:
1613: l_comp_op := '<';
1614:

Line 1636: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);

1632:
1633: h_sql := h_sql || ' WHERE ((VREAL = 0 AND VPLAN = 0) OR (VREAL <> 0 AND VPLAN <> 0))';
1634: h_num_bind_vars_n := 0;
1635:
1636: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
1637: commit;
1638: END IF;
1639: END IF;
1640:

Line 1767: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

1763: h_previous_years NUMBER;
1764: h_init_period NUMBER;
1765: h_end_period NUMBER;
1766: h_i NUMBER;
1767: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1768: h_num_bind_vars NUMBER;
1769: -- AW_INTEGRATION: new variables
1770: h_aw_flag BOOLEAN;
1771: l_sim_indicator_flag BOOLEAN;

Line 1786: IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_indic_code) = 2 THEN

1782: h_yearly_flag := 0;
1783: h_edw_flag := 0;
1784:
1785: -- AW_INTEGRATION: Get implementation type
1786: IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(x_indic_code) = 2 THEN
1787: h_aw_flag := TRUE;
1788: ELSE
1789: h_aw_flag := FALSE;
1790: END IF;

Line 1827: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(l_objective_color_props.periodicity_id);

1823: l_sim_indicator_flag := TRUE;
1824: END IF;
1825:
1826: -- Get information about the periodicity
1827: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(l_objective_color_props.periodicity_id);
1828: h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
1829: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);
1830:
1831: -- Get the current fiscal year

Line 1828: h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);

1824: END IF;
1825:
1826: -- Get information about the periodicity
1827: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(l_objective_color_props.periodicity_id);
1828: h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
1829: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);
1830:
1831: -- Get the current fiscal year
1832: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);

Line 1829: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);

1825:
1826: -- Get information about the periodicity
1827: h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(l_objective_color_props.periodicity_id);
1828: h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
1829: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);
1830:
1831: -- Get the current fiscal year
1832: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
1833:

Line 1832: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);

1828: h_calendar_edw_flag := BSC_UPDATE_UTIL.Get_Calendar_EDW_Flag(h_calendar_id);
1829: h_yearly_flag := BSC_UPDATE_UTIL.Get_Periodicity_Yearly_Flag(l_objective_color_props.periodicity_id);
1830:
1831: -- Get the current fiscal year
1832: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(h_calendar_id);
1833:
1834: -- Insert into BSC_TMP_ALL_PERIODS all periods
1835: -- of the periodicity by which the indicator is going to be colored in
1836: -- this tab.

Line 1839: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');

1835: -- of the periodicity by which the indicator is going to be colored in
1836: -- this tab.
1837:
1838: -- Delete current records
1839: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
1840:
1841: IF h_yearly_flag = 1 THEN
1842: -- Get the number of years and previous years of the indicator
1843: IF NOT BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years(x_indic_code,

Line 1843: IF NOT BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years(x_indic_code,

1839: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_ALL_PERIODS');
1840:
1841: IF h_yearly_flag = 1 THEN
1842: -- Get the number of years and previous years of the indicator
1843: IF NOT BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years(x_indic_code,
1844: l_objective_color_props.periodicity_id,
1845: h_num_of_years,
1846: h_previous_years) THEN
1847: RAISE e_unexpected_error;

Line 1860: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);

1856: h_bind_vars_values.delete;
1857: h_bind_vars_values(1) := h_i;
1858: h_num_bind_vars := 1;
1859:
1860: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1861: COMMIT;
1862: END LOOP;
1863:
1864: ELSE

Line 1869: ' SELECT DISTINCT ' || BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(l_objective_color_props.periodicity_id) ||

1865: -- Periodicity different to Annual
1866: IF h_calendar_edw_flag = 0 THEN
1867: -- BSC periodicity
1868: h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS'||
1869: ' SELECT DISTINCT ' || BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(l_objective_color_props.periodicity_id) ||
1870: ' AS PERIOD'||
1871: ' FROM BSC_DB_CALENDAR'||
1872: ' WHERE YEAR = :1 AND CALENDAR_ID = :2';
1873: h_bind_vars_values.delete;

Line 1877: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);

1873: h_bind_vars_values.delete;
1874: h_bind_vars_values(1) := h_current_fy;
1875: h_bind_vars_values(2) := h_calendar_id;
1876: h_num_bind_vars := 2;
1877: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1878: COMMIT;
1879:
1880: ELSE
1881: -- EDW periodicity

Line 1895: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);

1891: h_sql := 'INSERT /*+ append */ INTO BSC_TMP_ALL_PERIODS (PERIOD)'||
1892: ' VALUES (:1)';
1893: h_bind_vars_values(1) := h_i;
1894: h_num_bind_vars := 1;
1895: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
1896: COMMIT;
1897: END LOOP;
1898:
1899: END IF;

Line 1965: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),

1961: IF c_objective_color_props%ISOPEN THEN
1962: CLOSE c_objective_color_props;
1963: END IF;
1964: ROLLBACK;
1965: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
1966: x_source => 'BSC_UPDATE_COLOR.Color_Indicator');
1967: RETURN FALSE;
1968:
1969: WHEN OTHERS THEN

Line 2034: h_num_dimensions_by_family BSC_UPDATE_UTIL.t_array_of_number;

2030: h_tab_id NUMBER;
2031: h_num_dimensions NUMBER;
2032:
2033: -- Array with the number of dimensions of each family
2034: h_num_dimensions_by_family BSC_UPDATE_UTIL.t_array_of_number;
2035: h_max_family_index NUMBER;
2036:
2037: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2038: h_num_bind_vars NUMBER;

Line 2037: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

2033: -- Array with the number of dimensions of each family
2034: h_num_dimensions_by_family BSC_UPDATE_UTIL.t_array_of_number;
2035: h_max_family_index NUMBER;
2036:
2037: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2038: h_num_bind_vars NUMBER;
2039:
2040: h_table_name VARCHAR2(30);
2041: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;

Line 2041: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;

2037: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2038: h_num_bind_vars NUMBER;
2039:
2040: h_table_name VARCHAR2(30);
2041: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
2042: h_num_columns NUMBER;
2043:
2044: BEGIN
2045: h_max_family_index := 0;

Line 2090: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

2086: h_table_columns(h_num_columns).column_name := 'DIM_INDEX';
2087: h_table_columns(h_num_columns).data_type := 'NUMBER';
2088: h_table_columns(h_num_columns).data_size := NULL;
2089: h_table_columns(h_num_columns).add_to_index := 'N';
2090: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2091: RAISE e_unexpected_error;
2092: END IF;
2093:
2094: --Fix bug#4139837

Line 2095: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_DEF');

2091: RAISE e_unexpected_error;
2092: END IF;
2093:
2094: --Fix bug#4139837
2095: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_DEF');
2096:
2097: h_sql := 'INSERT /*+ append */ INTO BSC_TMP_TAB_DEF'||
2098: ' SELECT C.TAB_ID, C.DIM_LEVEL_INDEX,'||
2099: ' D.LEVEL_PK_COL, D.LEVEL_VIEW_NAME,'||

Line 2103: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

2099: ' D.LEVEL_PK_COL, D.LEVEL_VIEW_NAME,'||
2100: ' 0 AS FAMILY_INDEX, 0 AS DIM_INDEX'||
2101: ' FROM BSC_SYS_COM_DIM_LEVELS C, BSC_SYS_DIM_LEVELS_B D'||
2102: ' WHERE C.DIM_LEVEL_ID = D.DIM_LEVEL_ID';
2103: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2104: commit;
2105:
2106: -- Update columns FAMILY_ID and DIM_WITHIN_FAMILY
2107: -- RESTRICTION: The list only can be as following:

Line 2142: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);

2138: h_bind_vars_values(2) := h_dim_index;
2139: h_bind_vars_values(3) := h_tab.tab_id;
2140: h_bind_vars_values(4) := h_tab.dim_level_index;
2141: h_num_bind_vars := 4;
2142: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
2143: commit;
2144:
2145: h_last_tab_id := h_tab.tab_id;
2146:

Line 2194: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

2190: h_table_columns(h_num_columns).column_name := 'DIM_INDEX';
2191: h_table_columns(h_num_columns).data_type := 'NUMBER';
2192: h_table_columns(h_num_columns).data_size := NULL;
2193: h_table_columns(h_num_columns).add_to_index := 'N';
2194: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2195: RAISE e_unexpected_error;
2196: END IF;
2197:
2198: --Fix bug#4139837

Line 2199: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_COM');

2195: RAISE e_unexpected_error;
2196: END IF;
2197:
2198: --Fix bug#4139837
2199: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_TAB_COM');
2200:
2201: -- Insert records
2202: h_sql := 'SELECT TAB_ID, FAMILY_INDEX, COUNT(DIM_INDEX)'||
2203: ' FROM BSC_TMP_TAB_DEF'||

Line 2244: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

2240: h_table_columns(h_num_columns).column_name := 'PERIOD';
2241: h_table_columns(h_num_columns).data_type := 'NUMBER';
2242: h_table_columns(h_num_columns).data_size := 5;
2243: h_table_columns(h_num_columns).add_to_index := 'N';
2244: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2245: RAISE e_unexpected_error;
2246: END IF;
2247:
2248:

Line 2279: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

2275: h_table_columns(h_num_columns).column_name := 'TOTAL';
2276: h_table_columns(h_num_columns).data_type := 'NUMBER';
2277: h_table_columns(h_num_columns).data_size := NULL;
2278: h_table_columns(h_num_columns).add_to_index := 'N';
2279: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2280: RAISE e_unexpected_error;
2281: END IF;
2282:
2283: -- Create temporal table BSC_TMP_COLORS

Line 2323: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

2319: h_table_columns(h_num_columns).column_name := 'COLOR';
2320: h_table_columns(h_num_columns).data_type := 'NUMBER';
2321: h_table_columns(h_num_columns).data_size := NULL;
2322: h_table_columns(h_num_columns).add_to_index := 'N';
2323: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
2324: RAISE e_unexpected_error;
2325: END IF;
2326:
2327: RETURN TRUE;

Line 2331: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TEMP_TABTABLES_FAILED'),

2327: RETURN TRUE;
2328:
2329: EXCEPTION
2330: WHEN e_unexpected_error THEN
2331: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TEMP_TABTABLES_FAILED'),
2332: x_source => 'BSC_UPDATE_COLOR.Create_Temp_Tab_Tables');
2333: RETURN FALSE;
2334:
2335: WHEN OTHERS THEN

Line 2392: x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,

2388: x_indic_pl_flag IN BOOLEAN,
2389: x_indic_precalculated_flag IN BOOLEAN,
2390: x_dim_set_id IN NUMBER,
2391: x_table_name IN VARCHAR2,
2392: x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
2393: x_dim_com_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2394: x_num_families IN NUMBER,
2395: x_comp_level_pk_col IN VARCHAR2,
2396: x_color_by_total IN NUMBER,

Line 2393: x_dim_com_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2389: x_indic_precalculated_flag IN BOOLEAN,
2390: x_dim_set_id IN NUMBER,
2391: x_table_name IN VARCHAR2,
2392: x_dim_combination IN BSC_UPDATE_UTIL.t_array_of_number,
2393: x_dim_com_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2394: x_num_families IN NUMBER,
2395: x_comp_level_pk_col IN VARCHAR2,
2396: x_color_by_total IN NUMBER,
2397: x_condition OUT NOCOPY VARCHAR2,

Line 2398: x_bind_vars_values OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

2394: x_num_families IN NUMBER,
2395: x_comp_level_pk_col IN VARCHAR2,
2396: x_color_by_total IN NUMBER,
2397: x_condition OUT NOCOPY VARCHAR2,
2398: x_bind_vars_values OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2399: x_num_bind_vars OUT NOCOPY NUMBER,
2400: x_aw_limit_tbl IN OUT NOCOPY BIS_PMV_PAGE_PARAMETER_TBL --AW_INTEGRATION: new parameter
2401: ) RETURN BOOLEAN IS
2402:

Line 2500: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(x_comp_level_pk_col);

2496:
2497: --AW_INTEGRATION: This is to limit the dimension before querying the view.
2498: IF x_aw_flag THEN
2499: h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2500: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(x_comp_level_pk_col);
2501: h_aw_limit_rec.parameter_value := h_pl_profit_account;
2502: h_aw_limit_rec.dimension := 'DIMENSION';
2503: x_aw_limit_tbl.extend;
2504: x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;

Line 2536: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);

2532:
2533: --AW_INTEGRATION: Add all the values (including 0 code) of this dimension to the limit table
2534: IF x_aw_flag THEN
2535: h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2536: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2537: h_aw_limit_rec.parameter_value := '^ALL';
2538: h_aw_limit_rec.dimension := 'DIMENSION';
2539: x_aw_limit_tbl.extend;
2540: x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;

Line 2563: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);

2559:
2560: --AW_INTEGRATION: Add all the values of this dimension (including 0 code) to the limit table
2561: IF x_aw_flag THEN
2562: h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2563: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2564: h_aw_limit_rec.parameter_value := '^ALL';
2565: h_aw_limit_rec.dimension := 'DIMENSION';
2566: x_aw_limit_tbl.extend;
2567: x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;

Line 2582: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);

2578:
2579: --AW_INTEGRATION: Add this key item to the limit table
2580: IF x_aw_flag THEN
2581: h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2582: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2583: h_aw_limit_rec.parameter_value := h_default_key_value;
2584: h_aw_limit_rec.dimension := 'DIMENSION';
2585: x_aw_limit_tbl.extend;
2586: x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;

Line 2607: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);

2603:
2604: -- AW_INTEGRATION: Add to the limit table
2605: IF x_aw_flag THEN
2606: h_aw_limit_rec := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
2607: h_aw_limit_rec.parameter_name := BSC_UPDATE_UTIL.Get_Dim_Level_Table_Name(h_table_key);
2608: h_aw_limit_rec.parameter_value := '0';
2609: h_aw_limit_rec.dimension := 'DIMENSION';
2610: x_aw_limit_tbl.extend;
2611: x_aw_limit_tbl(x_aw_limit_tbl.LAST) := h_aw_limit_rec;

Line 2636: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORTABLE_COND_FAILED'),

2632: RETURN TRUE;
2633:
2634: EXCEPTION
2635: WHEN e_unexpected_error THEN
2636: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORTABLE_COND_FAILED'),
2637: x_source => 'BSC_UPDATE_COLOR.Get_Condition_On_Color_Table');
2638: RETURN FALSE;
2639:
2640: WHEN OTHERS THEN

Line 2725: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),

2721: WHEN e_unexpected_error THEN
2722: IF c_indic_tables%ISOPEN THEN
2723: CLOSE c_indic_tables;
2724: END IF;
2725: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),
2726: x_source => 'BSC_UPDATE_COLOR.Get_Table_For_Drill_Comb');
2727: RETURN NULL;
2728:
2729: WHEN OTHERS THEN

Line 2748: x_selected_dim_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,

2744: x_periodicity_id IN NUMBER,
2745: x_dim_set_id IN NUMBER,
2746: x_comp_level_pk_col IN VARCHAR2,
2747: x_color_by_total IN NUMBER,
2748: x_selected_dim_keys IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2749: x_num_selected_dim_keys IN NUMBER,
2750: x_level_comb OUT NOCOPY VARCHAR2
2751: ) RETURN VARCHAR2 IS
2752:

Line 2787: h_selected_drills BSC_UPDATE_UTIL.t_array_of_number;

2783:
2784: h_level_comb VARCHAR2(20);
2785: h_table_name VARCHAR2(30);
2786:
2787: h_selected_drills BSC_UPDATE_UTIL.t_array_of_number;
2788: h_num_selected_drills NUMBER;
2789:
2790: h_drill_comb VARCHAR2(20);
2791:

Line 2837: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,

2833: -- enter in a specific item
2834: OPEN c_key_items (x_indic_code, x_dim_set_id, 2);
2835: FETCH c_key_items INTO h_dim_level_index;
2836: WHILE c_key_items%FOUND LOOP
2837: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,
2838: h_selected_drills,
2839: h_num_selected_drills) THEN
2840: h_num_selected_drills := h_num_selected_drills + 1;
2841: h_selected_drills(h_num_selected_drills) := h_dim_level_index;

Line 2882: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,

2878: -- Create a string with the combination of drills base on the selected drills
2879: h_drill_comb := NULL;
2880:
2881: WHILE c_indic_drills%FOUND LOOP
2882: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_dim_level_index,
2883: h_selected_drills,
2884: h_num_selected_drills) THEN
2885: h_drill_comb := h_drill_comb||'0';
2886: ELSE

Line 2907: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),

2903: RETURN h_table_name;
2904:
2905: EXCEPTION
2906: WHEN e_unexpected_error THEN
2907: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_COLORTABLE_FAILED'),
2908: x_source => 'BSC_UPDATE_COLOR.Get_Table_Used_To_Color');
2909: RETURN NULL;
2910:
2911: WHEN OTHERS THEN

Line 2923: x_num_dimensions_by_family IN BSC_UPDATE_UTIL.t_array_of_number,

2919: | FUNCTION Insert_Tab_Combinations
2920: +============================================================================*/
2921: FUNCTION Insert_Tab_Combinations(
2922: x_tab_id IN NUMBER,
2923: x_num_dimensions_by_family IN BSC_UPDATE_UTIL.t_array_of_number,
2924: x_max_family_index IN NUMBER
2925: ) RETURN BOOLEAN IS
2926:
2927: h_num_combinations NUMBER;

Line 2942: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

2938: h_i_dim NUMBER;
2939:
2940: h_sql VARCHAR2(2000);
2941:
2942: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
2943: h_num_bind_vars NUMBER;
2944:
2945: BEGIN
2946:

Line 2971: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);

2967: h_bind_vars_values(2) := h_i_combination;
2968: h_bind_vars_values(3) := h_i_family;
2969: h_bind_vars_values(4) := h_i_dim;
2970: h_num_bind_vars := 4;
2971: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
2972: commit;
2973:
2974: h_i_combination := h_i_combination + 1;
2975: END LOOP;

Line 3076: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),

3072:
3073: EXCEPTION
3074: WHEN e_unexpected_error THEN
3075: ROLLBACK;
3076: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_KPICOLOR_FAILED'),
3077: x_source => 'BSC_UPDATE_COLOR.Calculate_Objective_Color');
3078: x_color_flag := false;
3079: x_kpi_measure_id := -1;
3080: --RETURN FALSE;

Line 3131: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;

3127: l_obj_color NUMBER;
3128: l_obj_trend NUMBER;
3129: l_calc_trend BOOLEAN := false;
3130: l_rollup_type VARCHAR2(240);
3131: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3132: h_num_bind_vars_n NUMBER;
3133: h_sql VARCHAR2(3000);
3134: h_obj_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3135: h_obj_num_bind_vars_n NUMBER;

Line 3134: h_obj_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;

3130: l_rollup_type VARCHAR2(240);
3131: h_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3132: h_num_bind_vars_n NUMBER;
3133: h_sql VARCHAR2(3000);
3134: h_obj_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3135: h_obj_num_bind_vars_n NUMBER;
3136: h_obj_sql VARCHAR2(3000);
3137: l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
3138: l_color_by_total bsc_kpi_measure_props.color_by_total%TYPE;

Line 3137: l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;

3133: h_sql VARCHAR2(3000);
3134: h_obj_bind_vars_values_n BSC_UPDATE_UTIL.t_array_of_number;
3135: h_obj_num_bind_vars_n NUMBER;
3136: h_obj_sql VARCHAR2(3000);
3137: l_kpi_dim_props BSC_UPDATE_UTIL.t_kpi_dim_props_rec;
3138: l_color_by_total bsc_kpi_measure_props.color_by_total%TYPE;
3139: l_comparison BOOLEAN := false;
3140: l_values_obtained BOOLEAN := false;
3141:

Line 3167: BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => x_indicator

3163: END IF;
3164: --BugFix 6000042 Trend for Comparison Mode
3165: IF ( (l_dim_comb.kpi_trend IS NOT NULL) AND (x_not_pl_not_initiative)
3166: AND (NOT l_values_obtained)) THEN
3167: BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => x_indicator
3168: , p_kpi_measure_id => x_measure_id
3169: , x_dim_props_rec => l_kpi_dim_props
3170: );
3171: l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => x_indicator

Line 3171: l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => x_indicator

3167: BSC_UPDATE_UTIL.Get_Kpi_Dim_Props ( p_objective_id => x_indicator
3168: , p_kpi_measure_id => x_measure_id
3169: , x_dim_props_rec => l_kpi_dim_props
3170: );
3171: l_color_by_total := BSC_UPDATE_UTIL.Get_Color_By_Total ( p_objective_id => x_indicator
3172: , p_kpi_measure_id => x_measure_id
3173: );
3174:
3175: IF ((l_kpi_dim_props.comp_level_pk_col IS NOT NULL) AND (l_color_by_total = 0)) THEN

Line 3199: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);

3195: h_bind_vars_values_n(3) := x_measure_id;
3196: h_bind_vars_values_n(4) := l_dim_comb.period_id;
3197: h_bind_vars_values_n(5) := l_dim_comb.dim_comb;
3198: h_num_bind_vars_n := 5;
3199: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values_n, h_num_bind_vars_n);
3200:
3201: COMMIT;
3202: END IF;
3203:

Line 3254: BSC_UPDATE_UTIL.Execute_Immediate(h_obj_sql, h_obj_bind_vars_values_n, h_obj_num_bind_vars_n);

3250: h_obj_bind_vars_values_n(2) := x_tab_id;
3251: h_obj_bind_vars_values_n(3) := l_dim_comb.period_id;
3252: h_obj_bind_vars_values_n(4) := l_dim_comb.dim_comb;
3253: h_obj_num_bind_vars_n := 4;
3254: BSC_UPDATE_UTIL.Execute_Immediate(h_obj_sql, h_obj_bind_vars_values_n, h_obj_num_bind_vars_n);
3255:
3256: COMMIT;
3257: END IF;
3258: