[Home] [Help]
190:
191: -- Get all the periodicities required in the system tables affected by the base table
192:
193: IF h_num_system_tables > 0 THEN
194: h_lst_where := BSC_APPS.Get_New_Big_In_Cond_Varchar2NU(1, 'table_name');
195: FOR h_i IN 1 .. h_num_system_tables LOOP
196: BSC_APPS.Add_Value_Big_In_Cond(1, h_system_tables(h_i));
197: END LOOP;
198:
192:
193: IF h_num_system_tables > 0 THEN
194: h_lst_where := BSC_APPS.Get_New_Big_In_Cond_Varchar2NU(1, 'table_name');
195: FOR h_i IN 1 .. h_num_system_tables LOOP
196: BSC_APPS.Add_Value_Big_In_Cond(1, h_system_tables(h_i));
197: END LOOP;
198:
199: h_sql := 'SELECT DISTINCT periodicity_id '||
200: 'FROM bsc_db_tables '||
424: h_num_edw_dim_tables := 0;
425:
426: -- Initializes global variables
427:
428: -- Initialize BSC/APPS global variables
429: BSC_APPS.Init_Bsc_Apps;
430:
431: -- Initializes the error message stack
432: g_debug_flag := 'NO';
425:
426: -- Initializes global variables
427:
428: -- Initialize BSC/APPS global variables
429: BSC_APPS.Init_Bsc_Apps;
430:
431: -- Initializes the error message stack
432: g_debug_flag := 'NO';
433: BSC_MESSAGE.Init(g_debug_flag);
432: g_debug_flag := 'NO';
433: BSC_MESSAGE.Init(g_debug_flag);
434:
435: -- Initialize the temporal table used for big 'in' conditions
436: BSC_APPS.Init_Big_In_Cond_Table;
437:
438: -- Check system lock
439: -- This is done in two places:
440: -- 1. At the beginning of the VB loader.
482: RAISE e_no_pending_process;
483: END IF;
484:
485: -- Initiliaze log file
486: IF NOT BSC_APPS.APPS_ENV THEN
487: h_log_file_name := g_schema_name||g_process_id||'.log';
488:
489: IF NOT BSC_UPDATE_LOG.Init_Log_File(h_log_file_name) THEN
490: RAISE e_update_error;
806: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
807: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
808:
809: -- Delete records in the temporal table used for big 'in' conditions
810: BSC_APPS.Init_Big_In_Cond_Table;
811:
812: --Delete the temp table created for storing CODE Column type
813: --LOCKING: Note I review code and the table created in BSC_OLAP_MAIN.create_tmp_col_type_table
814: -- is created with the name BSC_TMP_COL_TYPE_
830:
831: EXCEPTION
832: WHEN e_no_pending_process THEN
833: -- Delete records in the temporal table used for big 'in' conditions
834: BSC_APPS.Init_Big_In_Cond_Table;
835:
836: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_NO_PENDING_PROCESS'),
837: x_source => 'BSC_UPDATE.Execute_Update_Process',
838: x_mode => 'I');
846: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
847:
848: WHEN e_exists_prototype_indicators THEN
849: -- Delete records in the temporal table used for big 'in' conditions
850: BSC_APPS.Init_Big_In_Cond_Table;
851:
852: BSC_MESSAGE.flush;
853: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_METADATA_PEND_CHANGES'),
854: x_source => 'BSC_UPDATE.Execute_Update_Process',
870: WHEN e_update_error THEN
871: ROLLBACK;
872:
873: -- Delete records in the temporal table used for big 'in' conditions
874: BSC_APPS.Init_Big_In_Cond_Table;
875:
876: BSC_MESSAGE.flush;
877: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED'),
878: x_source => 'BSC_UPDATE.Execute_Update_Process',
895: --added 3899523
896: WHEN e_tmp_tbl_create_error THEN
897: ROLLBACK;
898: -- Delete records in the temporal table used for big 'in' conditions
899: BSC_APPS.Init_Big_In_Cond_Table;
900:
901: BSC_MESSAGE.flush;
902: BSC_MESSAGE.Add(x_message => 'BSC_OLAP_MAIN.create_tmp_col_type_table '||h_error_message,
903: x_source => 'BSC_UPDATE.Execute_Update_Process',
920: WHEN e_import_dbi_plans THEN
921: ROLLBACK;
922:
923: -- Delete records in the temporal table used for big 'in' conditions
924: BSC_APPS.Init_Big_In_Cond_Table;
925:
926: BSC_MESSAGE.flush;
927: BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_DIM.Import_Dbi_Plans: '||h_error_message,
928: x_source => 'BSC_UPDATE.Execute_Update_Process',
945: WHEN e_could_not_get_lock THEN
946: ROLLBACK;
947:
948: -- Delete records in the temporal table used for big 'in' conditions
949: BSC_APPS.Init_Big_In_Cond_Table;
950:
951: BSC_MESSAGE.flush;
952:
953: -- Error is already in the log file snet to the log file
971: WHEN OTHERS THEN
972: ROLLBACK;
973:
974: -- Delete records in the temporal table used for big 'in' conditions
975: BSC_APPS.Init_Big_In_Cond_Table;
976:
977: BSC_MESSAGE.flush;
978: BSC_MESSAGE.Add(x_message => SQLERRM,
979: x_source => 'BSC_UPDATE.Execute_Update_Process',
1276: CLOSE c_indicators;
1277:
1278:
1279: -- Delete projected data from tables
1280: IF BSC_APPS.bsc_mv THEN
1281: -- BSC-MV Note: Only delete projection from base tables
1282:
1283: -- The following are base tables. We need to delete the projection
1284: -- from all the periodicities
1473: -- also we refresh all MV affected by the base tables here
1474: -- AW_INTEGRATION: By design there are not PT tables for AW indicators.
1475: -- So no change here. Also remember that the array h_base_tables only
1476: -- has base tables for MV indicators.
1477: IF BSC_APPS.bsc_mv THEN
1478: -- BSC-MV Note: Only delete projection from base tables and tables created
1479: -- to store projection at kpi level
1480:
1481: -- The following are summary tables used to store projection.
1560:
1561: --LOCKING: mode this code here
1562: -- BSC_MV Note: Populate reporting calendar
1563: -- Fix bug#4027813: move load reporting calendar here and pass calendar id
1564: IF BSC_APPS.bsc_mv THEN
1565: -- LOCKING: lock the calendar
1566: IF NOT BSC_UPDATE_LOCK.Lock_Calendar(x_calendar_id) THEN
1567: RAISE e_could_not_get_lock;
1568: END IF;
1860: h_num_system_tables) THEN
1861: RAISE e_unexpected_error;
1862: END IF;
1863:
1864: h_lst_where := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
1865: FOR h_i IN 1 .. x_num_base_tables_to_color LOOP
1866: BSC_APPS.Add_Value_Big_In_Cond(1, x_base_tables_to_color(h_i));
1867: END LOOP;
1868:
1862: END IF;
1863:
1864: h_lst_where := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
1865: FOR h_i IN 1 .. x_num_base_tables_to_color LOOP
1866: BSC_APPS.Add_Value_Big_In_Cond(1, x_base_tables_to_color(h_i));
1867: END LOOP;
1868:
1869: FOR h_i IN 1 .. h_num_system_tables LOOP
1870: BSC_APPS.Add_Value_Big_In_Cond(1, h_system_tables(h_i));
1866: BSC_APPS.Add_Value_Big_In_Cond(1, x_base_tables_to_color(h_i));
1867: END LOOP;
1868:
1869: FOR h_i IN 1 .. h_num_system_tables LOOP
1870: BSC_APPS.Add_Value_Big_In_Cond(1, h_system_tables(h_i));
1871: END LOOP;
1872:
1873: h_sql := 'SELECT DISTINCT indicator '||
1874: 'FROM bsc_kpi_data_tables '||
2154: BEGIN
2155: -- Ref: bug#3482442 In corner cases this query can return more than one
2156: -- row and it will fail. AUDSID is not PK. After meeting with
2157: -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
2158: g_user_id := BSC_APPS.fnd_global_user_id;
2159: g_session_id := USERENV('SESSIONID');
2160: g_schema_name := BSC_APPS.fnd_apps_schema;
2161:
2162: RETURN TRUE;
2156: -- row and it will fail. AUDSID is not PK. After meeting with
2157: -- Vinod and Kris and Venu, we should use FNG_GLOBAL.user_id
2158: g_user_id := BSC_APPS.fnd_global_user_id;
2159: g_session_id := USERENV('SESSIONID');
2160: g_schema_name := BSC_APPS.fnd_apps_schema;
2161:
2162: RETURN TRUE;
2163:
2164: EXCEPTION
2978: RAISE e_unexpected_error;
2979: END IF;
2980:
2981: -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
2982: IF BSC_APPS.bsc_mv THEN
2983: h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
2984: ELSE
2985: h_aw_flag := FALSE;
2986: END IF;
3200: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
3201: ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
3202:
3203: -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
3204: IF BSC_APPS.bsc_mv THEN
3205: h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
3206: ELSE
3207: h_aw_flag := FALSE;
3208: END IF;
3264:
3265: -- AW_INTEGRATION: Initialize the array h_system_tables_aw with the system tables that are affected by
3266: -- the base tables for AW Kpis. By design we know that AW indicators does not share base tables
3267: -- with kpis implemented with MVs. By design AW is never implemented along Summary tables architecture
3268: IF BSC_APPS.bsc_mv THEN
3269: IF NOT Insert_Affected_Tables(h_base_tables_aw, h_num_base_tables_aw, h_system_tables_aw, h_num_system_tables_aw) THEN
3270: RAISE e_unexpected_error;
3271: END IF;
3272: END IF;
3339: BSC_UPDATE_LOG.OUTPUT);
3340:
3341:
3342: -- BSC-MV Note: In new architecture, call Calculate_Sum_Table_MV()
3343: IF BSC_APPS.bsc_mv THEN
3344: -- AW_INTEGRATION: Know if the table is used in a AW indicator
3345: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3346: h_system_tables_aw,
3347: h_num_system_tables_aw) THEN
3421: END LOOP;
3422:
3423: -- AW_INTEGRATION: Refresh AW indicators' cubes. I will use Get_Indicators_To_Color()
3424: -- to get the lost of indicators affected by the base tables of AW indicators
3425: IF BSC_APPS.bsc_mv THEN
3426:
3427: IF NOT Get_Indicators_To_Color(h_base_tables_aw,
3428: h_num_base_tables_aw,
3429: h_indicators_aw,
3601: RAISE e_could_not_get_lock;
3602: END IF;
3603:
3604: UPDATE bsc_kpis_b
3605: SET prototype_flag = 0, last_updated_by = BSC_APPS.fnd_global_user_id, last_update_date = SYSDATE
3606: WHERE indicator = h_color_indicators(h_i) AND prototype_flag IN (6, 7);
3607:
3608: -- Color By KPI: Mark KPIs for color done
3609: UPDATE bsc_kpi_analysis_measures_b
4022: h_sessionid := USERENV('SESSIONID');
4023: -- Bug#4681065
4024: g_warning := FALSE;
4025:
4026: -- Initialize BSC/APPS global variables
4027: BSC_APPS.Init_Bsc_Apps;
4028:
4029: -- LOCKING: call thi api
4030: FND_MSG_PUB.Initialize;
4023: -- Bug#4681065
4024: g_warning := FALSE;
4025:
4026: -- Initialize BSC/APPS global variables
4027: BSC_APPS.Init_Bsc_Apps;
4028:
4029: -- LOCKING: call thi api
4030: FND_MSG_PUB.Initialize;
4031:
4034: BSC_MESSAGE.Init(g_debug_flag);
4035:
4036: -- Check system lock
4037: --LOCKING: Do not use BSC_SECURITY.
4038: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4039: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4040: --SELECT count(*)
4041: --INTO h_count
4042: --FROM bsc_message_logs
4046: --END IF;
4047: --LOCKING: Call this api for backward compatibility
4048: BSC_LOCKS_PUB.Get_System_Lock (
4049: p_program_id => -101,
4050: p_user_id => BSC_APPS.apps_user_id,
4051: p_icx_session_id => null,
4052: x_return_status => h_return_status,
4053: x_msg_count => h_msg_count,
4054: x_msg_data => h_msg_data
4207: h_sessionid := USERENV('SESSIONID');
4208: -- Bug#4681065
4209: g_warning := FALSE;
4210:
4211: -- Initialize BSC/APPS global variables
4212: BSC_APPS.Init_Bsc_Apps;
4213:
4214: -- LOCKING: call thi api
4215: FND_MSG_PUB.Initialize;
4208: -- Bug#4681065
4209: g_warning := FALSE;
4210:
4211: -- Initialize BSC/APPS global variables
4212: BSC_APPS.Init_Bsc_Apps;
4213:
4214: -- LOCKING: call thi api
4215: FND_MSG_PUB.Initialize;
4216:
4219: BSC_MESSAGE.Init(g_debug_flag);
4220:
4221: -- Check system lock
4222: --LOCKING: Do not use BSC_SECURITY.
4223: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4224: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4225: --SELECT count(*)
4226: --INTO h_count
4227: --FROM bsc_message_logs
4231: --END IF;
4232: --LOCKING: Call this api for backward compatibility
4233: BSC_LOCKS_PUB.Get_System_Lock (
4234: p_program_id => -101,
4235: p_user_id => BSC_APPS.apps_user_id,
4236: p_icx_session_id => null,
4237: x_return_status => h_return_status,
4238: x_msg_count => h_msg_count,
4239: x_msg_data => h_msg_data
4268: end if;
4269: --LOCKING: lock again since Load_Indicators_Dims removed the lock
4270: BSC_LOCKS_PUB.Get_System_Lock (
4271: p_program_id => -101,
4272: p_user_id => BSC_APPS.apps_user_id,
4273: p_icx_session_id => null,
4274: x_return_status => h_return_status,
4275: x_msg_count => h_msg_count,
4276: x_msg_data => h_msg_data
4422: h_sessionid := USERENV('SESSIONID');
4423: -- Bug#4681065
4424: g_warning := FALSE;
4425:
4426: -- Initialize BSC/APPS global variables
4427: BSC_APPS.Init_Bsc_Apps;
4428:
4429: -- LOCKING: call thi api
4430: FND_MSG_PUB.Initialize;
4423: -- Bug#4681065
4424: g_warning := FALSE;
4425:
4426: -- Initialize BSC/APPS global variables
4427: BSC_APPS.Init_Bsc_Apps;
4428:
4429: -- LOCKING: call thi api
4430: FND_MSG_PUB.Initialize;
4431:
4434: BSC_MESSAGE.Init(g_debug_flag);
4435:
4436: -- Check system lock
4437: --LOCKING: Do not use BSC_SECURITY.
4438: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4439: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4440: --SELECT count(*)
4441: --INTO h_count
4442: --FROM bsc_message_logs
4446: --END IF;
4447: --LOCKING: Call this api for backward compatibility
4448: BSC_LOCKS_PUB.Get_System_Lock (
4449: p_program_id => -101,
4450: p_user_id => BSC_APPS.apps_user_id,
4451: p_icx_session_id => null,
4452: x_return_status => h_return_status,
4453: x_msg_count => h_msg_count,
4454: x_msg_data => h_msg_data
4590: h_msg_count NUMBER;
4591: h_msg_data VARCHAR2(4000);
4592:
4593: Begin
4594: -- Initialize BSC/APPS global variables
4595: BSC_APPS.Init_Bsc_Apps;
4596:
4597: -- LOCKING: call thi api
4598: FND_MSG_PUB.Initialize;
4591: h_msg_data VARCHAR2(4000);
4592:
4593: Begin
4594: -- Initialize BSC/APPS global variables
4595: BSC_APPS.Init_Bsc_Apps;
4596:
4597: -- LOCKING: call thi api
4598: FND_MSG_PUB.Initialize;
4599:
4612: END IF;
4613:
4614: -- Check system lock
4615: --LOCKING: Do not use BSC_SECURITY.
4616: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4617: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4618: --SELECT count(*)
4619: --INTO h_count
4620: --FROM bsc_message_logs
4624: --END IF;
4625: --LOCKING: Call this api for backward compatibility
4626: BSC_LOCKS_PUB.Get_System_Lock (
4627: p_program_id => -101,
4628: p_user_id => BSC_APPS.apps_user_id,
4629: p_icx_session_id => null,
4630: x_return_status => h_return_status,
4631: x_msg_count => h_msg_count,
4632: x_msg_data => h_msg_data
4768:
4769: -- Bug#4681065
4770: g_warning := FALSE;
4771:
4772: -- Initialize BSC/APPS global variables
4773: BSC_APPS.Init_Bsc_Apps;
4774:
4775: -- LOCKING: call thi api
4776: FND_MSG_PUB.Initialize;
4769: -- Bug#4681065
4770: g_warning := FALSE;
4771:
4772: -- Initialize BSC/APPS global variables
4773: BSC_APPS.Init_Bsc_Apps;
4774:
4775: -- LOCKING: call thi api
4776: FND_MSG_PUB.Initialize;
4777:
4779: g_debug_flag := 'NO';
4780: BSC_MESSAGE.Init(g_debug_flag);
4781:
4782: -- Initialize the temporal table used for big 'in' conditions
4783: BSC_APPS.Init_Big_In_Cond_Table;
4784:
4785: -- Initializes g_session_id, g_user_id and g_schema_name
4786: IF NOT Init_Env_Values THEN
4787: RAISE e_update_error;
4793: END IF;
4794:
4795: -- Check system lock
4796: --LOCKING: Do not use BSC_SECURITY.
4797: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
4798: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
4799: --SELECT count(*)
4800: --INTO h_count
4801: --FROM bsc_message_logs
4805: --END IF;
4806: --LOCKING: Call this api for backward compatibility
4807: BSC_LOCKS_PUB.Get_System_Lock (
4808: p_program_id => -101,
4809: p_user_id => BSC_APPS.apps_user_id,
4810: p_icx_session_id => null,
4811: x_return_status => h_return_status,
4812: x_msg_count => h_msg_count,
4813: x_msg_data => h_msg_data
5056:
5057: -- Bug#4681065
5058: g_warning := FALSE;
5059:
5060: -- Initialize BSC/APPS global variables
5061: BSC_APPS.Init_Bsc_Apps;
5062:
5063: -- LOCKING: call thi api
5064: FND_MSG_PUB.Initialize;
5057: -- Bug#4681065
5058: g_warning := FALSE;
5059:
5060: -- Initialize BSC/APPS global variables
5061: BSC_APPS.Init_Bsc_Apps;
5062:
5063: -- LOCKING: call thi api
5064: FND_MSG_PUB.Initialize;
5065:
5067: g_debug_flag := 'NO';
5068: BSC_MESSAGE.Init(g_debug_flag);
5069:
5070: -- Initialize the temporal table used for big 'in' conditions
5071: BSC_APPS.Init_Big_In_Cond_Table;
5072:
5073: -- Initializes g_session_id, g_user_id and g_schema_name
5074: IF NOT Init_Env_Values THEN
5075: RAISE e_update_error;
5081: END IF;
5082:
5083: -- Check system lock
5084: --LOCKING: Do not use BSC_SECURITY.
5085: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
5086: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
5087: --SELECT count(*)
5088: --INTO h_count
5089: --FROM bsc_message_logs
5093: --END IF;
5094: --LOCKING: Call this api for backward compatibility
5095: BSC_LOCKS_PUB.Get_System_Lock (
5096: p_program_id => -101,
5097: p_user_id => BSC_APPS.apps_user_id,
5098: p_icx_session_id => null,
5099: x_return_status => h_return_status,
5100: x_msg_count => h_msg_count,
5101: x_msg_data => h_msg_data
5157: FOR h_i IN 1..h_num_dbi_dimensions LOOP
5158: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
5159: ' '||h_dbi_dimensions(h_i), BSC_UPDATE_LOG.OUTPUT);
5160:
5161: h_dbi_dim_requests(h_i) := FND_REQUEST.Submit_Request(application => BSC_APPS.bsc_apps_short_name,
5162: program => 'BSC_REFRESH_DBI_DIM',
5163: argument1 => h_dbi_dimensions(h_i));
5164:
5165:
5351: BEGIN
5352: h_num_indicators := 0;
5353: g_keep_input_table_data := x_keep_input_table_data;
5354:
5355: -- Initialize BSC/APPS global variables
5356: BSC_APPS.Init_Bsc_Apps;
5357:
5358: -- LOCKING: call thi api
5359: FND_MSG_PUB.Initialize;
5352: h_num_indicators := 0;
5353: g_keep_input_table_data := x_keep_input_table_data;
5354:
5355: -- Initialize BSC/APPS global variables
5356: BSC_APPS.Init_Bsc_Apps;
5357:
5358: -- LOCKING: call thi api
5359: FND_MSG_PUB.Initialize;
5360:
5362: g_debug_flag := 'NO';
5363: BSC_MESSAGE.Init(g_debug_flag);
5364:
5365: -- Initialize the temporal table used for big 'in' conditions
5366: BSC_APPS.Init_Big_In_Cond_Table;
5367:
5368: -- Initializes g_session_id, g_user_id and g_schema_name
5369: IF NOT Init_Env_Values THEN
5370: RAISE e_update_error;
5371: END IF;
5372:
5373: -- Check system lock
5374: --LOCKING: Do not use BSC_SECURITY.
5375: --BSC_SECURITY.Check_System_Lock(-101, NULL, BSC_APPS.apps_user_id);
5376: --h_source := 'BSC_SECURITY.CHECK_SYSTEM_LOCK';
5377: --SELECT count(*)
5378: --INTO h_count
5379: --FROM bsc_message_logs
5383: --END IF;
5384: --LOCKING: Call this api for backward compatibility
5385: BSC_LOCKS_PUB.Get_System_Lock (
5386: p_program_id => -101,
5387: p_user_id => BSC_APPS.apps_user_id,
5388: p_icx_session_id => null,
5389: x_return_status => h_return_status,
5390: x_msg_count => h_msg_count,
5391: x_msg_data => h_msg_data
5532: h_where_indics := NULL;
5533: h_sql := NULL;
5534: h_num_system_tables := 0;
5535:
5536: h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
5537: FOR h_i IN 1 .. x_num_indicators LOOP
5538: BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
5539: END LOOP;
5540:
5534: h_num_system_tables := 0;
5535:
5536: h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
5537: FOR h_i IN 1 .. x_num_indicators LOOP
5538: BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
5539: END LOOP;
5540:
5541: h_sql := 'SELECT table_name'||
5542: ' FROM bsc_kpi_data_tables'||
5694: h_where_indics := NULL;
5695: h_sql := NULL;
5696: h_source := 'BSC';
5697:
5698: h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'k.indicator');
5699: FOR h_i IN 1 .. x_num_indicators LOOP
5700: BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
5701: END LOOP;
5702:
5696: h_source := 'BSC';
5697:
5698: h_where_indics := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'k.indicator');
5699: FOR h_i IN 1 .. x_num_indicators LOOP
5700: BSC_APPS.Add_Value_Big_In_Cond(1, x_indicators(h_i));
5701: END LOOP;
5702:
5703: h_sql := 'SELECT DISTINCT source_table_name'||
5704: ' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
6344: h_message VARCHAR2(5000);
6345:
6346: BEGIN
6347: IF g_kpi_mode THEN
6348: h_where_cond := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
6349: FOR h_i IN 1 .. g_num_indicators LOOP
6350: BSC_APPS.Add_Value_Big_In_Cond(1, g_indicators(h_i));
6351: END LOOP;
6352:
6346: BEGIN
6347: IF g_kpi_mode THEN
6348: h_where_cond := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
6349: FOR h_i IN 1 .. g_num_indicators LOOP
6350: BSC_APPS.Add_Value_Big_In_Cond(1, g_indicators(h_i));
6351: END LOOP;
6352:
6353: h_sql := 'select indicator, name'||
6354: ' from bsc_kpis_vl'||
6368: g_warning := TRUE;
6369: END LOOP;
6370: CLOSE h_cursor;
6371: ELSE
6372: h_where_cond := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
6373: FOR h_i IN 1 .. x_num_system_tables LOOP
6374: BSC_APPS.Add_Value_Big_In_Cond(1, x_system_tables(h_i));
6375: END LOOP;
6376:
6370: CLOSE h_cursor;
6371: ELSE
6372: h_where_cond := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
6373: FOR h_i IN 1 .. x_num_system_tables LOOP
6374: BSC_APPS.Add_Value_Big_In_Cond(1, x_system_tables(h_i));
6375: END LOOP;
6376:
6377: h_sql := 'select indicator, name'||
6378: ' from bsc_kpis_vl'||