[Home] [Help]
1: PACKAGE BODY BSC_UPDATE_UTIL AS
2: /* $Header: BSCDUTIB.pls 120.10 2007/03/15 09:46:11 ankgoel ship $ */
3:
4:
5: /*===========================================================================+
34: WHERE CALENDAR_ID = p_calendar_id AND PERIODICITY_TYPE = p_periodicity_type;
35:
36: h_periodicity_id NUMBER;
37: h_source VARCHAR2(200);
38: h_tmp_array BSC_UPDATE_UTIL.t_array_of_number;
39: h_count NUMBER;
40: h_i NUMBER;
41: h_new_per_id NUMBER;
42: h_new_source VARCHAR2(200);
40: h_i NUMBER;
41: h_new_per_id NUMBER;
42: h_new_source VARCHAR2(200);
43:
44: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
45: h_num_bind_vars NUMBER;
46:
47: BEGIN
48: -- Init BSC/APPS global variables
69: h_bind_vars_values.delete;
70: h_bind_vars_values(1) := x_calendar_id;
71: h_bind_vars_values(2) := h_periodicity_id;
72: h_num_bind_vars := 2;
73: BSC_UPDATE_UTIL.Execute_Immediate(sql_stmt, h_bind_vars_values, h_num_bind_vars);
74: */
75: INSERT INTO BSC_SYS_PERIODICITIES (
76: PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,PERIOD_COL_NAME,
77: SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
92: FETCH c_new_per INTO h_periodicity_id,h_source;
93: WHILE c_new_per%FOUND LOOP
94: h_new_source := '';
95: IF h_source IS NOT NULL THEN
96: h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(h_source,h_tmp_array,',');
97: FOR h_i IN 1.. h_count LOOP
98: -- Get New Value
99: h_periodicity_type := h_tmp_array(h_i);
100: --OPEN c_get_per FOR c_get_per_sql USING x_calendar_id, h_periodicity_type;
124:
125: EXCEPTION
126: WHEN OTHERS THEN
127: BSC_MESSAGE.Add(x_message => SQLERRM,
128: x_source => 'BSC_UPDATE_UTIL.CloneBSCPeriodicitybyCalendar',
129: x_mode => 'I');
130: COMMIT;
131: END CloneBSCPeriodicitybyCalendar;
132:
196:
197: EXCEPTION
198: WHEN OTHERS THEN
199: BSC_MESSAGE.Add(x_message => SQLERRM,
200: x_source => 'BSC_UPDATE_UTIL.Create_Unique_Index');
201: RETURN FALSE;
202:
203: END Create_Unique_Index;
204:
222: | FUNCTION Create_Global_Temp_Table
223: +============================================================================*/
224: FUNCTION Create_Global_Temp_Table(
225: x_table_name IN VARCHAR2,
226: x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
227: x_num_columns IN NUMBER
228: ) RETURN BOOLEAN IS
229:
230: h_sql VARCHAR2(32700);
336: exception
337: when others then
338: if h_lst_cols_index is null then
339: BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
340: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
341: BSC_MESSAGE.Add(x_message => 'l_index_count=0.Unable to create Index on any Key columns',
342: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
343: RETURN FALSE;
344: end if;
338: if h_lst_cols_index is null then
339: BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
340: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
341: BSC_MESSAGE.Add(x_message => 'l_index_count=0.Unable to create Index on any Key columns',
342: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
343: RETURN FALSE;
344: end if;
345: h_lst_cols_index:=getSmallerColumnList(h_lst_cols_index);
346: end;
379:
380: EXCEPTION
381: WHEN OTHERS THEN
382: BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
383: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
384: BSC_MESSAGE.Add(x_message => SQLERRM,
385: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
386: RETURN FALSE;
387:
381: WHEN OTHERS THEN
382: BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
383: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
384: BSC_MESSAGE.Add(x_message => SQLERRM,
385: x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
386: RETURN FALSE;
387:
388: END Create_Global_Temp_Table;
389:
392: | FUNCTION Create_Permanent_Table
393: +============================================================================*/
394: FUNCTION Create_Permanent_Table(
395: x_table_name IN VARCHAR2,
396: x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
397: x_num_columns IN NUMBER,
398: x_tablespace IN VARCHAR2,
399: x_idx_tablespace IN VARCHAR2
400: ) RETURN BOOLEAN IS
511:
512: EXCEPTION
513: WHEN OTHERS THEN
514: BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
515: x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
516: BSC_MESSAGE.Add(x_message => SQLERRM,
517: x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
518: RETURN FALSE;
519:
513: WHEN OTHERS THEN
514: BSC_MESSAGE.Add(x_message => 'x_table_name='||x_table_name,
515: x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
516: BSC_MESSAGE.Add(x_message => SQLERRM,
517: x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
518: RETURN FALSE;
519:
520: END Create_Permanent_Table;
521:
668:
669: EXCEPTION
670: WHEN OTHERS THEN
671: BSC_MESSAGE.Add(x_message => SQLERRM,
672: x_source => 'BSC_UPDATE_UTIL.Drop_Index');
673: RETURN FALSE;
674: END Drop_Index;
675:
676:
697:
698: EXCEPTION
699: WHEN OTHERS THEN
700: BSC_MESSAGE.Add(x_message => SQLERRM,
701: x_source => 'BSC_UPDATE_UTIL.Drop_Table');
702: RETURN FALSE;
703: END Drop_Table;
704:
705:
782: | PROCEDURE Execute_Immediate
783: +============================================================================*/
784: PROCEDURE Execute_Immediate(
785: x_sql IN VARCHAR2,
786: x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
787: x_num_bind_vars IN NUMBER
788: ) IS
789:
790: l_sql VARCHAR2(32700);
920: | PROCEDURE Execute_Immediate
921: +============================================================================*/
922: PROCEDURE Execute_Immediate(
923: x_sql IN clob,
924: x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
925: x_num_bind_vars IN NUMBER
926: ) IS
927:
928: l_sql VARCHAR2(32700);
1152: | FUNCTION Execute_Immediate
1153: +============================================================================*/
1154: FUNCTION Execute_Immediate(
1155: x_sql IN VARCHAR2,
1156: x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1157: x_num_bind_vars IN NUMBER
1158: ) RETURN NUMBER IS
1159:
1160: l_sql VARCHAR2(32700);
1291: | PROCEDURE Execute_Immediate
1292: +============================================================================*/
1293: PROCEDURE Execute_Immediate(
1294: x_sql IN VARCHAR2,
1295: x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
1296: x_num_bind_vars IN NUMBER
1297: ) IS
1298:
1299: l_sql VARCHAR2(32700);
1429: | PROCEDURE Execute_Immediate
1430: +============================================================================*/
1431: PROCEDURE Execute_Immediate(
1432: x_sql IN clob,
1433: x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
1434: x_num_bind_vars IN NUMBER
1435: ) IS
1436:
1437: l_sql VARCHAR2(32700);
1661: | FUNCTION Execute_Immediate
1662: +============================================================================*/
1663: FUNCTION Execute_Immediate(
1664: x_sql IN VARCHAR2,
1665: x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
1666: x_num_bind_vars IN NUMBER
1667: ) RETURN NUMBER IS
1668:
1669: l_sql VARCHAR2(32700);
2068:
2069: EXCEPTION
2070: WHEN OTHERS THEN
2071: BSC_MESSAGE.Add(x_message => SQLERRM,
2072: x_source => 'BSC_UPDATE_UTIL.Get_Calendar_Start_Date');
2073: RETURN FALSE;
2074: END Get_Calendar_Start_Date;
2075:
2076:
2325:
2326: EXCEPTION
2327: WHEN OTHERS THEN
2328: BSC_MESSAGE.Add(x_message => SQLERRM,
2329: x_source => 'BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years');
2330: RETURN FALSE;
2331:
2332: END Get_Indic_Range_Of_Years;
2333:
2336: | FUNCTION Get_Information_Data_Columns
2337: +============================================================================*/
2338: FUNCTION Get_Information_Data_Columns(
2339: x_table IN VARCHAR2,
2340: x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2341: x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2342: x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2343: x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2344: x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2337: +============================================================================*/
2338: FUNCTION Get_Information_Data_Columns(
2339: x_table IN VARCHAR2,
2340: x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2341: x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2342: x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2343: x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2344: x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2345:
2338: FUNCTION Get_Information_Data_Columns(
2339: x_table IN VARCHAR2,
2340: x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2341: x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2342: x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2343: x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2344: x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2345:
2346: -- BSC-BIS-DIMENSIONS Note: From thsi implemetation we can run loader when
2339: x_table IN VARCHAR2,
2340: x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2341: x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2342: x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2343: x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
2344: x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2345:
2346: -- BSC-BIS-DIMENSIONS Note: From thsi implemetation we can run loader when
2347: -- there are indicators in prototype. This open a case where a measure is
2398:
2399: EXCEPTION
2400: WHEN OTHERS THEN
2401: BSC_MESSAGE.Add(x_message => SQLERRM,
2402: x_source => 'BSC_UPDATE_UTIL.Get_Information_Data_Columns');
2403: RETURN FALSE;
2404:
2405: END Get_Information_Data_Columns;
2406:
2409: | FUNCTION Get_Information_Key_Columns
2410: +============================================================================*/
2411: FUNCTION Get_Information_Key_Columns(
2412: x_table IN VARCHAR2,
2413: x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2414: x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2415: x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2416: x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2417: x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2410: +============================================================================*/
2411: FUNCTION Get_Information_Key_Columns(
2412: x_table IN VARCHAR2,
2413: x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2414: x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2415: x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2416: x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2417: x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2418:
2411: FUNCTION Get_Information_Key_Columns(
2412: x_table IN VARCHAR2,
2413: x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2414: x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2415: x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2416: x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2417: x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2418:
2419: TYPE t_cursor IS REF CURSOR;
2412: x_table IN VARCHAR2,
2413: x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2414: x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2415: x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2416: x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2417: x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
2418:
2419: TYPE t_cursor IS REF CURSOR;
2420:
2465:
2466: EXCEPTION
2467: WHEN OTHERS THEN
2468: BSC_MESSAGE.Add(x_message => SQLERRM,
2469: x_source => 'BSC_UPDATE_UTIL.Get_Information_Key_Columns');
2470: RETURN FALSE;
2471:
2472: END Get_Information_Key_Columns;
2473:
2495: /*
2496: OPEN c_init FOR c_init_sql USING x_variable_name;
2497: FETCH c_init INTO x_variable_value;
2498: IF c_init%NOTFOUND THEN
2499: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2500: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2501: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2502: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2503: x_variable_value := NULL;
2496: OPEN c_init FOR c_init_sql USING x_variable_name;
2497: FETCH c_init INTO x_variable_value;
2498: IF c_init%NOTFOUND THEN
2499: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2500: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2501: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2502: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2503: x_variable_value := NULL;
2504: END IF;
2497: FETCH c_init INTO x_variable_value;
2498: IF c_init%NOTFOUND THEN
2499: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2500: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2501: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2502: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2503: x_variable_value := NULL;
2504: END IF;
2505: CLOSE c_init;
2498: IF c_init%NOTFOUND THEN
2499: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2500: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2501: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2502: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2503: x_variable_value := NULL;
2504: END IF;
2505: CLOSE c_init;
2506: */
2510: FROM bsc_sys_init
2511: WHERE property_code = x_variable_name;
2512: EXCEPTION
2513: WHEN NO_DATA_FOUND THEN
2514: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2515: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2516: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2517: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2518: x_variable_value := NULL;
2511: WHERE property_code = x_variable_name;
2512: EXCEPTION
2513: WHEN NO_DATA_FOUND THEN
2514: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2515: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2516: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2517: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2518: x_variable_value := NULL;
2519: END;
2512: EXCEPTION
2513: WHEN NO_DATA_FOUND THEN
2514: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2515: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2516: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2517: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2518: x_variable_value := NULL;
2519: END;
2520:
2513: WHEN NO_DATA_FOUND THEN
2514: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
2515: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
2516: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
2517: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2518: x_variable_value := NULL;
2519: END;
2520:
2521: RETURN TRUE;
2522:
2523: EXCEPTION
2524: WHEN OTHERS THEN
2525: BSC_MESSAGE.Add(x_message => SQLERRM,
2526: x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
2527: RETURN FALSE;
2528:
2529: END Get_Init_Variable_Value;
2530:
2558:
2559: EXCEPTION
2560: WHEN OTHERS THEN
2561: BSC_MESSAGE.Add(x_message => SQLERRM,
2562: x_source => 'BSC_UPDATE_UTIL.Get_Input_Table_Source');
2563: RETURN FALSE;
2564: END Get_Input_Table_Source;
2565:
2566:
2567: /*===========================================================================+
2568: | FUNCTION Get_Installed_Languages
2569: +============================================================================*/
2570: FUNCTION Get_Installed_Languages(
2571: x_languages IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2572: ) RETURN NUMBER IS
2573:
2574: TYPE t_cursor IS REF CURSOR;
2575:
2610:
2611: EXCEPTION
2612: WHEN OTHERS THEN
2613: BSC_MESSAGE.Add(x_message => SQLERRM,
2614: x_source => 'BSC_UPDATE_UTIL.Get_Installed_Languages');
2615: RETURN -1;
2616:
2617: END Get_Installed_Languages;
2618:
2642: /*
2643: OPEN c_lookup_value FOR c_lookup_value_sql USING x_lookup_type, x_lookup_code;
2644: FETCH c_lookup_value INTO h_lookup_value;
2645: IF c_lookup_value%NOTFOUND THEN
2646: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2647: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2648: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2649: BSC_MESSAGE.Add(x_message => h_message,
2650: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2643: OPEN c_lookup_value FOR c_lookup_value_sql USING x_lookup_type, x_lookup_code;
2644: FETCH c_lookup_value INTO h_lookup_value;
2645: IF c_lookup_value%NOTFOUND THEN
2646: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2647: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2648: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2649: BSC_MESSAGE.Add(x_message => h_message,
2650: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2651: h_lookup_value := NULL;
2644: FETCH c_lookup_value INTO h_lookup_value;
2645: IF c_lookup_value%NOTFOUND THEN
2646: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2647: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2648: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2649: BSC_MESSAGE.Add(x_message => h_message,
2650: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2651: h_lookup_value := NULL;
2652: END IF;
2646: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2647: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2648: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2649: BSC_MESSAGE.Add(x_message => h_message,
2650: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2651: h_lookup_value := NULL;
2652: END IF;
2653: CLOSE c_lookup_value;
2654: */
2658: FROM bsc_lookups
2659: WHERE lookup_type = x_lookup_type AND lookup_code = x_lookup_code;
2660: EXCEPTION
2661: WHEN NO_DATA_FOUND THEN
2662: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2663: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2664: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2665: BSC_MESSAGE.Add(x_message => h_message,
2666: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2659: WHERE lookup_type = x_lookup_type AND lookup_code = x_lookup_code;
2660: EXCEPTION
2661: WHEN NO_DATA_FOUND THEN
2662: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2663: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2664: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2665: BSC_MESSAGE.Add(x_message => h_message,
2666: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2667: h_lookup_value := NULL;
2660: EXCEPTION
2661: WHEN NO_DATA_FOUND THEN
2662: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2663: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2664: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2665: BSC_MESSAGE.Add(x_message => h_message,
2666: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2667: h_lookup_value := NULL;
2668: END;
2662: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
2663: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
2664: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
2665: BSC_MESSAGE.Add(x_message => h_message,
2666: x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
2667: h_lookup_value := NULL;
2668: END;
2669:
2670: RETURN h_lookup_value;
2768:
2769: EXCEPTION
2770: WHEN OTHERS THEN
2771: BSC_MESSAGE.Add(x_message => SQLERRM,
2772: x_source => 'BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity');
2773: RETURN NULL;
2774:
2775: END Get_Num_Periods_Periodicity;
2776:
2828:
2829: EXCEPTION
2830: WHEN OTHERS THEN
2831: BSC_MESSAGE.Add(x_message => SQLERRM,
2832: x_source => 'BSC_UPDATE_UTIL.Get_Period_Cols_Names');
2833: RETURN FALSE;
2834:
2835: END Get_Period_Cols_Names;
2836:
2861:
2862: IF p_yearly_flag = 1 THEN
2863: h_period := p_current_fy;
2864: ELSE
2865: h_source_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_source_periodicity_id);
2866: h_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_periodicity_id);
2867:
2868: h_sql := 'SELECT DISTINCT '||h_col_name||
2869: ' FROM bsc_db_calendar'||
2862: IF p_yearly_flag = 1 THEN
2863: h_period := p_current_fy;
2864: ELSE
2865: h_source_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_source_periodicity_id);
2866: h_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_periodicity_id);
2867:
2868: h_sql := 'SELECT DISTINCT '||h_col_name||
2869: ' FROM bsc_db_calendar'||
2870: ' WHERE calendar_id = :1 AND year = :2'||
3006: | FUNCTION Get_Source_Periodicities
3007: +============================================================================*/
3008: FUNCTION Get_Source_Periodicities(
3009: x_periodicity_id IN NUMBER,
3010: x_source_periodicities IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
3011: ) RETURN NUMBER IS
3012:
3013: h_num_source_periodicities NUMBER;
3014: h_i NUMBER;
3085:
3086: EXCEPTION
3087: WHEN OTHERS THEN
3088: BSC_MESSAGE.Add(x_message => SQLERRM,
3089: x_source => 'BSC_UPDATE_UTIL.Get_Table_Generation_Type');
3090: RETURN NULL;
3091:
3092: END Get_Table_Generation_Type;
3093:
3113:
3114: EXCEPTION
3115: WHEN OTHERS THEN
3116: BSC_MESSAGE.Add(x_message => SQLERRM,
3117: x_source => 'BSC_UPDATE_UTIL.Get_Table_Type');
3118: RETURN NULL;
3119:
3120: END Get_Table_Type;
3121:
3163:
3164: EXCEPTION
3165: WHEN OTHERS THEN
3166: BSC_MESSAGE.Add(x_message => SQLERRM,
3167: x_source => 'BSC_UPDATE_UTIL.Get_Table_Periodicity');
3168: RETURN NULL;
3169:
3170: END Get_Table_Periodicity;
3171:
3203:
3204: EXCEPTION
3205: WHEN OTHERS THEN
3206: BSC_MESSAGE.Add(x_message => SQLERRM,
3207: x_source => 'BSC_UPDATE_UTIL.Get_Table_Range_Of_Years');
3208: RETURN FALSE;
3209:
3210: END Get_Table_Range_Of_Years;
3211:
3361: h_periodicity_id NUMBER;
3362: h_custom_code NUMBER;
3363: h_source_periodicity NUMBER;
3364:
3365: h_calculated_pers BSC_UPDATE_UTIL.t_array_of_number;
3366: h_num_calculated_pers NUMBER;
3367:
3368: TYPE t_custom_per IS RECORD (
3369: periodicity_id NUMBER,
3522: RETURN TRUE;
3523:
3524: EXCEPTION
3525: WHEN e_unexpected_error THEN
3526: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
3527: x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3528: RETURN FALSE;
3529:
3530: WHEN OTHERS THEN
3523:
3524: EXCEPTION
3525: WHEN e_unexpected_error THEN
3526: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
3527: x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3528: RETURN FALSE;
3529:
3530: WHEN OTHERS THEN
3531: BSC_MESSAGE.Add(x_message => SQLERRM,
3528: RETURN FALSE;
3529:
3530: WHEN OTHERS THEN
3531: BSC_MESSAGE.Add(x_message => SQLERRM,
3532: x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3533: RETURN FALSE;
3534:
3535: END Init_Calendar_Tables;
3536:
3552: RETURN TRUE;
3553:
3554: EXCEPTION
3555: WHEN e_unexpected_error THEN
3556: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
3557: x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3558: RETURN FALSE;
3559:
3560: WHEN OTHERS THEN
3553:
3554: EXCEPTION
3555: WHEN e_unexpected_error THEN
3556: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
3557: x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3558: RETURN FALSE;
3559:
3560: WHEN OTHERS THEN
3561: BSC_MESSAGE.Add(x_message => SQLERRM,
3558: RETURN FALSE;
3559:
3560: WHEN OTHERS THEN
3561: BSC_MESSAGE.Add(x_message => SQLERRM,
3562: x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
3563: RETURN FALSE;
3564:
3565: END Init_Calendar_Tables;
3566:
3742: h_source VARCHAR2(500);
3743:
3744: h_index NUMBER;
3745:
3746: h_sources BSC_UPDATE_UTIL.t_array_of_number;
3747: h_num_sources NUMBER;
3748: h_i NUMBER;
3749: h_j NUMBER;
3750: h_source_periodicity_id NUMBER;
3748: h_i NUMBER;
3749: h_j NUMBER;
3750: h_source_periodicity_id NUMBER;
3751:
3752: h_arr_new_periodicity_rels BSC_UPDATE_UTIL.t_array_periodicity_rels;
3753: h_num_new_periodicity_rels NUMBER;
3754:
3755: BEGIN
3756: h_sql := NULL;
3812:
3813: EXCEPTION
3814: WHEN OTHERS THEN
3815: BSC_MESSAGE.Add(x_message => SQLERRM,
3816: x_source => 'BSC_UPDATE_UTIL.Load_Periodicity_Rels');
3817: RETURN FALSE;
3818:
3819: END Load_Periodicity_Rels;
3820:
4121:
4122:
4123: h_message VARCHAR2(4000);
4124:
4125: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
4126: h_num_bind_vars NUMBER;
4127:
4128: BEGIN
4129: /* Get the range of years the system tables need. */
4193: h_bind_vars_values(11) := h_weekly4;
4194: h_bind_vars_values(12) := h_daily365;
4195: h_bind_vars_values(13) := h_daily30;
4196: h_num_bind_vars := 13;
4197: BSC_UPDATE_UTIL.Execute_Immediate(sql_stmt, h_bind_vars_values, h_num_bind_vars);
4198: */
4199:
4200: INSERT INTO bsc_db_calendar (calendar_id, calendar_year, calendar_month, calendar_day,
4201: year, semester, quarter, bimester, month, week52, week4, day365, day30)
4246: EXCEPTION
4247: WHEN OTHERS THEN
4248: BSC_MESSAGE.Add(
4249: X_Message => SQLERRM,
4250: X_Source => 'BSC_UPDATE_UTIL.Populate_Bsc_Db_Calendar');
4251: RETURN FALSE;
4252:
4253: END Populate_Bsc_Db_Calendar;
4254:
4290: EXCEPTION
4291: WHEN OTHERS THEN
4292: BSC_MESSAGE.Add(
4293: x_message => SQLERRM,
4294: x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Db_Week_Maps');
4295: RETURN FALSE;
4296:
4297: END Populate_Bsc_Db_Week_Maps;
4298:
4316:
4317: h_periodicity_id NUMBER;
4318: h_db_column_name VARCHAR2(50);
4319:
4320: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
4321: h_num_bind_vars NUMBER;
4322:
4323: BEGIN
4324: -- Delete all rows from bsc_sys_periods_tl
4513: h_bind_vars_values.delete;
4514: h_bind_vars_values(1) := h_periodicity_id;
4515: h_bind_vars_values(2) := x_calendar_id;
4516: h_num_bind_vars := 2;
4517: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
4518:
4519: FETCH c_custom_pers INTO h_periodicity_id, h_db_column_name;
4520: END LOOP;
4521: CLOSE c_custom_pers;
4526: EXCEPTION
4527: WHEN OTHERS THEN
4528: BSC_MESSAGE.Add(
4529: x_message => SQLERRM,
4530: x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Sys_Periods_Tl');
4531: RETURN FALSE;
4532:
4533: END Populate_Bsc_Sys_Periods_Tl;
4534:
4549: -- Init BSC/APPS global variables
4550: BSC_APPS.Init_Bsc_Apps;
4551:
4552: -- Init calendar tables
4553: IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, x_action) THEN
4554: RAISE e_error;
4555: END IF;
4556:
4557: -- BSC_MV Note: Populate reporting calendar
4580:
4581: EXCEPTION
4582: WHEN e_error THEN
4583: BSC_MESSAGE.flush;
4584: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED'),
4585: x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4586: x_mode => 'I');
4587: COMMIT;
4588:
4581: EXCEPTION
4582: WHEN e_error THEN
4583: BSC_MESSAGE.flush;
4584: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED'),
4585: x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4586: x_mode => 'I');
4587: COMMIT;
4588:
4589: WHEN e_error_load_rpt_cal THEN
4588:
4589: WHEN e_error_load_rpt_cal THEN
4590: BSC_MESSAGE.flush;
4591: BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Load_Reporting_Calendar: '||h_error_message,
4592: x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4593: x_mode => 'I');
4594: COMMIT;
4595:
4596: WHEN OTHERS THEN
4594: COMMIT;
4595:
4596: WHEN OTHERS THEN
4597: BSC_MESSAGE.Add(x_message => SQLERRM,
4598: x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4599: x_mode => 'I');
4600: COMMIT;
4601: END Populate_Calendar_Tables;
4602:
4613:
4614: EXCEPTION
4615: WHEN OTHERS THEN
4616: BSC_MESSAGE.Add(x_message => SQLERRM,
4617: x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
4618: x_mode => 'I');
4619: COMMIT;
4620: END Populate_Calendar_Tables;
4621:
4644: -- Init BSC/APPS global variables
4645: BSC_APPS.Init_Bsc_Apps;
4646:
4647: -- Init calendar tables
4648: IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(p_calendar_id) THEN
4649: RAISE e_error;
4650: END IF;
4651:
4652: IF (p_commit = FND_API.G_TRUE) THEN
4658: ROLLBACK TO BscUpdateUtilPopCalTables;
4659: FND_MSG_PUB.Add_Exc_Msg(
4660: G_PKG_NAME,
4661: l_api_name,
4662: BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED')
4663: );
4664: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4665: FND_MSG_PUB.Count_And_Get(
4666: p_encoded => 'F'
4761: EXCEPTION
4762: WHEN OTHERS THEN
4763: BSC_MESSAGE.Add(
4764: X_Message => SQLERRM,
4765: X_Source => 'BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year');
4766: RETURN FALSE;
4767:
4768: END Set_Calendar_Fiscal_Year;
4769:
4867: EXCEPTION
4868: WHEN OTHERS THEN
4869: BSC_MESSAGE.Add(
4870: X_Message => SQLERRM,
4871: X_Source => 'BSC_UPDATE_UTIL.Table_Has_Any_Row');
4872: RETURN NULL;
4873:
4874: END Table_Has_Any_Row;
4875:
4890: EXCEPTION
4891: WHEN OTHERS THEN
4892: BSC_MESSAGE.Add(
4893: X_Message => SQLERRM,
4894: X_Source => 'BSC_UPDATE_UTIL.Table_Has_Any_Row');
4895: RETURN NULL;
4896:
4897: END Table_Has_Any_Row;
4898:
4958: WHERE CALENDAR_ID = p_calendar_id AND PERIODICITY_TYPE = p_periodicity_type
4959: ORDER BY PERIODICITY_ID;
4960:
4961: h_source VARCHAR2(200);
4962: h_tmp_array BSC_UPDATE_UTIL.t_array_of_number;
4963: h_count NUMBER;
4964: h_i NUMBER;
4965: h_new_per_id NUMBER;
4966: h_new_source VARCHAR2(200);
4978: IF c_new_per%FOUND THEN
4979: h_new_source := '';
4980: x_exist := FALSE;
4981: IF h_source IS NOT NULL THEN
4982: h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(h_source,h_tmp_array,',');
4983: FOR h_i IN 1.. h_count LOOP
4984: -- Get New Value
4985: h_new_per_id := h_tmp_array(h_i);
4986: --Delete
5035:
5036: EXCEPTION
5037: WHEN OTHERS THEN
5038: BSC_MESSAGE.Add(x_message => SQLERRM,
5039: x_source => 'BSC_UPDATE_UTIL.UpdAnualPeriodicitySrc',
5040: x_mode => 'I');
5041: COMMIT;
5042: END Update_AnualPeriodicity_Src;
5043:
5055:
5056: TYPE t_cursor IS REF CURSOR;
5057: h_cursor t_cursor;
5058:
5059: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5060: h_num_bind_vars NUMBER;
5061:
5062: BEGIN
5063: -- Get Kpi EDW Flag
5162: EXCEPTION
5163: WHEN OTHERS THEN
5164: BSC_MESSAGE.Add(
5165: X_Message => SQLERRM,
5166: X_Source => 'BSC_UPDATE_UTIL.Update_Kpi_Period_Name');
5167: RETURN FALSE;
5168:
5169: END Update_Kpi_Period_Name;
5170:
5181: TYPE t_cursor IS REF CURSOR;
5182: h_cursor t_cursor;
5183: h_sql VARCHAR2(32000);
5184:
5185: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5186: h_num_bind_vars NUMBER;
5187:
5188: BEGIN
5189: -- Ref: bug#3482442 In corner cases this query can return more than one
5228: h_sql VARCHAR2(32000);
5229:
5230: h_sessionid NUMBER;
5231:
5232: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5233: h_num_bind_vars NUMBER;
5234:
5235: BEGIN
5236: h_sessionid := USERENV('SESSIONID');
5269: h_sql VARCHAR2(32000);
5270:
5271: h_sessionid NUMBER;
5272:
5273: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5274: h_num_bind_vars NUMBER;
5275:
5276: BEGIN
5277: h_sessionid := USERENV('SESSIONID');
5324: h_sql VARCHAR2(32000);
5325:
5326: h_sessionid NUMBER;
5327:
5328: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5329: h_num_bind_vars NUMBER;
5330:
5331: BEGIN
5332: h_sessionid := USERENV('SESSIONID');
5507:
5508: h_fix_overlap_period NUMBER;
5509: h_distance NUMBER;
5510:
5511: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5512: h_num_bind_vars NUMBER;
5513:
5514: BEGIN
5515: -- Check whether the current fical year exists in BSC_SYS_PERIODS
5593: h_bind_vars_values(5) := h_model_year;
5594: h_bind_vars_values(6) := x_periodicity_id;
5595: h_bind_vars_values(7) := h_model_year;
5596: h_num_bind_vars := 7;
5597: BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
5598: */
5599: INSERT INTO bsc_sys_periods (
5600: periodicity_id,
5601: year,
5911: RETURN TRUE;
5912: EXCEPTION
5913: WHEN OTHERS THEN
5914: BSC_MESSAGE.Add(x_message => SQLERRM,
5915: x_source => 'BSC_UPDATE_UTIL.Verify_Custom_Periodicity');
5916: RETURN FALSE;
5917: END Verify_Custom_Periodicity;
5918:
5919:
5932: h_sql VARCHAR2(32000);
5933:
5934: h_sessionid NUMBER;
5935:
5936: h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
5937: h_num_bind_vars NUMBER;
5938:
5939: BEGIN
5940: h_sessionid := USERENV('SESSIONID');
5974:
5975: EXCEPTION
5976: WHEN OTHERS THEN
5977: BSC_MESSAGE.Add(x_message => SQLERRM,
5978: x_source => 'BSC_UPDATE_UTIL.Write_Init_Variable_Value');
5979: RETURN FALSE;
5980:
5981: END Write_Init_Variable_Value;
5982:
5990: return g_parallel;
5991: EXCEPTION
5992: WHEN OTHERS THEN
5993: BSC_MESSAGE.Add(x_message => SQLERRM,
5994: x_source => 'BSC_UPDATE_UTIL.is_parallel');
5995: RETURN FALSE;
5996: END is_parallel;
5997:
5998:
6008: BEGIN
6009: l_variable := NULL;
6010: l_version := NULL;
6011:
6012: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6013: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6009: l_variable := NULL;
6010: l_version := NULL;
6011:
6012: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6013: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6010: l_version := NULL;
6011:
6012: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6013: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6011:
6012: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6013: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6012: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6013: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6020: END IF;
6013: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6020: END IF;
6021: IF (BSC_UPDATE_UTIL.Get_Init_Variable_Value(
6014: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6020: END IF;
6021: IF (BSC_UPDATE_UTIL.Get_Init_Variable_Value(
6022: x_variable_name => l_variable
6015: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6016: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6020: END IF;
6021: IF (BSC_UPDATE_UTIL.Get_Init_Variable_Value(
6022: x_variable_name => l_variable
6023: , x_variable_value => l_version)) THEN
6017: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6018: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6019: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6020: END IF;
6021: IF (BSC_UPDATE_UTIL.Get_Init_Variable_Value(
6022: x_variable_name => l_variable
6023: , x_variable_value => l_version)) THEN
6024: RETURN l_version;
6025: ELSE
6028: EXCEPTION
6029: WHEN OTHERS THEN
6030: BSC_MESSAGE.Add
6031: ( x_message => SQLERRM
6032: , x_source => 'BSC_UPDATE_UTIL.get_Product_Version'
6033: );
6034: RETURN NULL;
6035: END get_Product_Version;
6036:
6045: l_variable BSC_SYS_INIT.Property_Code%TYPE;
6046: BEGIN
6047: l_variable := NULL;
6048:
6049: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6050: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6046: BEGIN
6047: l_variable := NULL;
6048:
6049: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6050: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6047: l_variable := NULL;
6048:
6049: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6050: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6048:
6049: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6050: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6049: IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
6050: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6057: END IF;
6050: l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6057: END IF;
6058: IF(l_variable IS NOT NULL) THEN
6051: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6057: END IF;
6058: IF(l_variable IS NOT NULL) THEN
6059: RETURN BSC_UPDATE_UTIL.Write_Init_Variable_Value
6052: l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
6053: ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
6054: l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6057: END IF;
6058: IF(l_variable IS NOT NULL) THEN
6059: RETURN BSC_UPDATE_UTIL.Write_Init_Variable_Value
6060: ( x_variable_name => l_variable
6055: ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
6056: l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
6057: END IF;
6058: IF(l_variable IS NOT NULL) THEN
6059: RETURN BSC_UPDATE_UTIL.Write_Init_Variable_Value
6060: ( x_variable_name => l_variable
6061: , x_variable_value => p_Version
6062: );
6063: ELSE
6066: EXCEPTION
6067: WHEN OTHERS THEN
6068: BSC_MESSAGE.Add
6069: ( x_message => SQLERRM
6070: , x_source => 'BSC_UPDATE_UTIL.set_Product_Version'
6071: );
6072: RETURN FALSE;
6073: END set_Product_Version;
6074:
6208:
6209: PROCEDURE Get_Kpi_Dim_Props (
6210: p_objective_id IN NUMBER
6211: , p_kpi_measure_id IN NUMBER
6212: , x_dim_props_rec OUT NOCOPY BSC_UPDATE_UTIL.t_kpi_dim_props_rec
6213: )
6214: IS
6215: CURSOR c_dim_props(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
6216: SELECT ds.dim_set_id dim_set_id,
6244: IF c_dim_props%ISOPEN THEN
6245: CLOSE c_dim_props;
6246: END IF;
6247: BSC_MESSAGE.Add(x_message => SQLERRM,
6248: x_source => 'BSC_UPDATE_UTIL.Get_Kpi_Dim_Props');
6249: RAISE;
6250: END Get_Kpi_Dim_Props;
6251:
6252:
6297: IF c_measure_formula%ISOPEN THEN
6298: CLOSE c_measure_formula;
6299: END IF;
6300: BSC_MESSAGE.Add(x_message => SQLERRM,
6301: x_source => 'BSC_UPDATE_UTIL.get_kpi_measure_formula');
6302: RETURN NULL;
6303: END get_kpi_measure_formula;
6304:
6305:
6354:
6355: EXCEPTION
6356: WHEN OTHERS THEN
6357: BSC_MESSAGE.Add(x_message => SQLERRM,
6358: x_source => 'BSC_UPDATE_UTIL.Get_SimObj_Color_Formula');
6359: RETURN NULL;
6360: END Get_SimObj_Color_Formula;
6361:
6362:
6387:
6388: EXCEPTION
6389: WHEN OTHERS THEN
6390: BSC_MESSAGE.Add(x_message => SQLERRM,
6391: x_source => 'BSC_UPDATE_UTIL.Get_Measure_Formula');
6392: RETURN NULL;
6393: END Get_Measure_Formula;
6394:
6395:
6428: IF c_color_by_total%ISOPEN THEN
6429: CLOSE c_color_by_total;
6430: END IF;
6431: BSC_MESSAGE.Add(x_message => SQLERRM,
6432: x_source => 'BSC_UPDATE_UTIL.Get_Color_By_Total');
6433: RETURN NULL;
6434: END Get_Color_By_Total;
6435:
6436:
6470: IF c_ytd_flag%ISOPEN THEN
6471: CLOSE c_ytd_flag;
6472: END IF;
6473: BSC_MESSAGE.Add(x_message => SQLERRM,
6474: x_source => 'BSC_UPDATE_UTIL.get_ytd_flag');
6475: RETURN l_ytd_flag;
6476: END get_ytd_flag;
6477:
6478:
6511: IF c_apply_color_flag%ISOPEN THEN
6512: CLOSE c_apply_color_flag;
6513: END IF;
6514: BSC_MESSAGE.Add(x_message => SQLERRM,
6515: x_source => 'BSC_UPDATE_UTIL.Get_Apply_Color_Flag');
6516: RETURN NULL;
6517: END Get_Apply_Color_Flag;
6518:
6519:
6516: RETURN NULL;
6517: END Get_Apply_Color_Flag;
6518:
6519:
6520: END BSC_UPDATE_UTIL;