[Home] [Help]
44: --This API Requires Commit
45: commit;
46: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
47: writeTmp('Exception in BSC_BIS_DIM_OBJ_PUB.Validate_Refresh_BSC_PMF_Views for short_name='
48: ||i.short_name||':'||x_msg_data, FND_LOG.LEVEL_EXCEPTION, true);
49: raise BSC_METADATA_OPTIMIZER_PKG.optimizer_exception;
50: ELSE
51: writeTmp('Successfully generated BSC View '||i.level_view_name, FND_LOG.LEVEL_EXCEPTION, true);
52: END IF;
47: writeTmp('Exception in BSC_BIS_DIM_OBJ_PUB.Validate_Refresh_BSC_PMF_Views for short_name='
48: ||i.short_name||':'||x_msg_data, FND_LOG.LEVEL_EXCEPTION, true);
49: raise BSC_METADATA_OPTIMIZER_PKG.optimizer_exception;
50: ELSE
51: writeTmp('Successfully generated BSC View '||i.level_view_name, FND_LOG.LEVEL_EXCEPTION, true);
52: END IF;
53: END LOOP;
54: commit;
55: return true;
126: and (source_table_name like ''BSC_B%'' or source_table_name like ''BSC_T%'' )';
127:
128: do_ddl(l_stmt, ad_ddl.create_table, TableName);
129: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName);
130: writeTmp('Created '||TableName, FND_LOG.LEVEL_STATEMENT, false);
131: l_stmt := 'create unique index '||TableName||'_u1 on '||TableName||'(indicator, dim_set_id, table_name) TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName;
132: do_ddl(l_stmt, ad_ddl.create_index, TableName||'_U1');
133: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName||'_U1');
134: writeTmp('Created index '||TableName||'_u1', FND_LOG.LEVEL_STATEMENT, false);
130: writeTmp('Created '||TableName, FND_LOG.LEVEL_STATEMENT, false);
131: l_stmt := 'create unique index '||TableName||'_u1 on '||TableName||'(indicator, dim_set_id, table_name) TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName;
132: do_ddl(l_stmt, ad_ddl.create_index, TableName||'_U1');
133: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName||'_U1');
134: writeTmp('Created index '||TableName||'_u1', FND_LOG.LEVEL_STATEMENT, false);
135: l_stmt := 'create index '||TableName||'_n1 on '||TableName||'(table_name) TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName;
136: do_ddl(l_stmt, ad_ddl.create_index, TableName||'_N1');
137: writeTmp('Created index '||TableName||'_n1', FND_LOG.LEVEL_STATEMENT, false);
138: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName||'_N1');
133: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName||'_U1');
134: writeTmp('Created index '||TableName||'_u1', FND_LOG.LEVEL_STATEMENT, false);
135: l_stmt := 'create index '||TableName||'_n1 on '||TableName||'(table_name) TABLESPACE '||BSC_METADATA_OPTIMIZER_PKG.gSummaryIndexTbsName;
136: do_ddl(l_stmt, ad_ddl.create_index, TableName||'_N1');
137: writeTmp('Created index '||TableName||'_n1', FND_LOG.LEVEL_STATEMENT, false);
138: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName||'_N1');
139: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Completed CreateKPIDataTableTmp');
140:
141: exception when others then
138: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Created '||TableName||'_N1');
139: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Completed CreateKPIDataTableTmp');
140:
141: exception when others then
142: writeTmp('Exception in CreateKPIDataTableTmp :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
143: writeTmp('Exception in CreateKPIDataTableTmp stmt= :'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
144: raise;
145: END;
146:
139: bsc_metadata_optimizer_pkg.logProgress('INIT', 'Completed CreateKPIDataTableTmp');
140:
141: exception when others then
142: writeTmp('Exception in CreateKPIDataTableTmp :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
143: writeTmp('Exception in CreateKPIDataTableTmp stmt= :'||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
144: raise;
145: END;
146:
147: -- performance fix, query bsc_kpi_Data_tables_ind instead of bsc_kpi_data_tables_last
180: END LOOP;
181: CLOSE cObjectives;
182: return l_results;
183: exception when others then
184: writeTmp('Exception in find_objectives_for_table_old :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
185: raise;
186: END;
187:
188: FUNCTION find_objectives_for_table_new(p_table IN VARCHAR2) return BSC_METADATA_OPTIMIZER_PKG.tab_clsKPIDimSet IS
209: END LOOP;
210: CLOSE cv;
211: RETURN l_results;
212: exception when others then
213: writeTmp('Exception in find_objectives_for_table_new :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
214: writeTmp('l-stmt was :'||l_stmt||' with bind variable :1='||p_table, FND_LOG.LEVEL_EXCEPTION, true);
215: raise;
216:
217: END;
210: CLOSE cv;
211: RETURN l_results;
212: exception when others then
213: writeTmp('Exception in find_objectives_for_table_new :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
214: writeTmp('l-stmt was :'||l_stmt||' with bind variable :1='||p_table, FND_LOG.LEVEL_EXCEPTION, true);
215: raise;
216:
217: END;
218:
223: ELSE
224: RETURN find_objectives_for_table_new(p_table);
225: END IF;
226: exception when others then
227: writeTmp('Exception in find_objectives_for_table :'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
228: raise;
229:
230: END;
231:
261: ELSE
262: return true;
263: END IF;
264: EXCEPTION WHEN OTHERS THEN
265: bsc_mo_helper_pkg.writeTmp('Exception in is_Base_Table for '||p_table||':'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
266: raise;
267: End ;
268:
269:
311: CLOSE cTableSpace;
312: RETURN l_return_value;
313:
314: EXCEPTION WHEN others then
315: bsc_mo_helper_pkg.writeTmp('EXCEPTION in getTablespaceClauseTbl', FND_LOG.LEVEL_UNEXPECTED, true);
316: RAISE;
317: END;
318:
319: FUNCTION getTablespaceClauseIdx RETURN VARCHAR2 IS
322:
323: cv CurTyp;
324: BEGIN
325: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
326: bsc_mo_helper_pkg.writeTmp('Inside getTablespaceClauseIdx', FND_LOG.LEVEL_PROCEDURE);
327: END IF;
328:
329: l_stmt := 'SELECT BSC_APPS.Get_Tablespace_Clause_Idx FROM DUAL';
330:
332: FETCH cv INTO l_return_value;
333: CLOSE cv;
334:
335: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
336: bsc_mo_helper_pkg.writeTmp('Completed getTablespaceClauseIdx, returning '||l_return_value, FND_LOG.LEVEL_PROCEDURE);
337: END IF;
338:
339: RETURN l_return_value;
340:
338:
339: RETURN l_return_value;
340:
341: EXCEPTION WHEN others then
342: bsc_mo_helper_pkg.writeTmp('EXCEPTION in getTablespaceClauseIdx', FND_LOG.LEVEL_UNEXPECTED, true);
343: RAISE;
344: END;
345:
346: FUNCTION getStorageClause RETURN VARCHAR2 IS
350: SELECT BSC_APPS.Get_Storage_Clause FROM DUAL;
351:
352: BEGIN
353: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
354: bsc_mo_helper_pkg.writeTmp('Inside getStorageClause', FND_LOG.LEVEL_PROCEDURE);
355: END IF;
356:
357: OPEN cStorage;
358: FETCH cStorage INTO l_return_value;
358: FETCH cStorage INTO l_return_value;
359: CLOSE cStorage;
360:
361: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
362: bsc_mo_helper_pkg.writeTmp('Completed getStorageClause, returning '||l_return_value, FND_LOG.LEVEL_PROCEDURE);
363: END IF;
364:
365: RETURN l_return_value;
366:
364:
365: RETURN l_return_value;
366:
367: EXCEPTION WHEN others then
368: bsc_mo_helper_pkg.writeTmp('EXCEPTION in getStorageClause', FND_LOG.LEVEL_UNEXPECTED, true);
369: RAISE;
370: END;
371:
372:
419:
420:
421: PROCEDURE write_this (
422: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV,
423: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
424: pForce IN boolean default false)
425: IS
426: l_count number;
427: BEGIN
427: BEGIN
428: IF BSC_METADATA_OPTIMIZER_PKG.g_log or pForce THEN
429: writeTmp('LevelComb = '||pTable.LevelComb||' , MVName = '||
430: pTable.MVName||', DataSource = '||pTable.MVName||', SqlStmt = '||pTable.SqlStmt,
431: FND_LOG.LEVEL_STATEMENT, pForce);
432: END IF;
433:
434: END;
435:
435:
436:
437: PROCEDURE write_this (
438: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsConfigKpiMV,
439: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
440: pForce IN boolean default false)
441: IS
442: l_count NUMBER;
443: l_table BSC_METADATA_OPTIMIZER_PKG.clsConfigKpiMV;
453: l_count := pTable.next(l_count);
454: END LOOP;
455: END;
456: /*
457: PROCEDURE write_this (pTable IN DBMS_SQL.VARCHAR2_TABLE, pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT, pForce IN boolean default false) IS
458: l_count NUMBER;
459: l_string VARCHAR2(4000);
460: BEGIN
461: IF (pTable.count=0) THEN
485: */
486:
487: PROCEDURE write_this (
488: pTable IN DBMS_SQL.VARCHAR2_TABLE,
489: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
490: pForce IN boolean default false) IS
491: l_count NUMBER;
492: l_string VARCHAR2(4000);
493: BEGIN
509:
510:
511: PROCEDURE write_this (
512: pTable IN DBMS_SQL.NUMBER_TABLE,
513: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
514: pForce IN boolean default false)
515: IS
516: l_count NUMBER;
517: l_string VARCHAR2(3000);
543:
544: PROCEDURE write_this(
545: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsParent,
546: ind IN NUMBER default null,
547: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
548: pForce IN boolean default false)
549: IS
550: BEGIN
551: IF BSC_METADATA_OPTIMIZER_PKG.g_log or pForce THEN
555: END;
556:
557: PROCEDURE write_this(
558: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsParent,
559: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
560: pForce IN boolean default false)
561: IS
562: l_count NUMBER;
563: l_table BSC_METADATA_OPTIMIZER_PKG.clsParent;
577:
578: PROCEDURE write_this(
579: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsMasterTable,
580: ind IN NUMBER default null,
581: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
582: pForce IN boolean default false)
583: IS
584: l_string varchar2(30000);
585: BEGIN
602: END;
603:
604: PROCEDURE write_this(
605: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsMasterTable,
606: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
607: pForce IN boolean default false)
608: IS
609: l_count NUMBER;
610: l_table BSC_METADATA_OPTIMIZER_PKG.clsMasterTable;
623:
624: PROCEDURE write_this(
625: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsRelationMN,
626: ind IN NUMBER default null,
627: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
628: pForce IN boolean default false)
629: IS
630: BEGIN
631: IF BSC_METADATA_OPTIMIZER_PKG.g_log or pForce THEN
637: END;
638:
639: PROCEDURE write_this(
640: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsRelationMN,
641: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
642: pForce IN boolean default false) IS
643: l_count NUMBER;
644: l_table BSC_METADATA_OPTIMIZER_PKG.clsRelationMN;
645: BEGIN
657:
658: PROCEDURE write_this(
659: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsIndicator,
660: ind IN NUMBER default null,
661: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
662: pForce IN boolean default false)
663: IS
664: BEGIN
665: IF BSC_METADATA_OPTIMIZER_PKG.g_log OR pForce THEN
671: END;
672:
673: PROCEDURE write_this(
674: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicator,
675: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
676: pForce IN boolean default false) IS
677: l_count NUMBER;
678: l_table BSC_METADATA_OPTIMIZER_PKG.clsIndicator;
679: BEGIN
690: END;
691: PROCEDURE write_this(
692: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsPeriodicity,
693: ind IN NUMBER default null,
694: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
695: pForce IN boolean default false) IS
696: l_count NUMBER;
697: l_string VARCHAR2(30000);
698: BEGIN
704: END;
705:
706: PROCEDURE write_this(
707: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsPeriodicity ,
708: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
709: pForce IN boolean default false)
710: IS
711: l_count NUMBER;
712: l_table BSC_METADATA_OPTIMIZER_PKG.clsPeriodicity;
725:
726: PROCEDURE write_this(
727: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity,
728: ind IN NUMBER default null,
729: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
730: pForce IN boolean default false)
731: IS
732: l_count NUMBER;
733: l_string VARCHAR2(30000);
737: END IF;
738: END;
739: PROCEDURE write_this(
740: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsIndicPeriodicity ,
741: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
742: pForce IN boolean default false)
743: IS
744: l_count NUMBER;
745: l_table BSC_METADATA_OPTIMIZER_PKG.clsIndicPeriodicity;
758:
759: PROCEDURE write_this(
760: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsCalendar,
761: ind IN NUMBER default null,
762: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
763: pForce IN boolean default false)
764: IS
765: l_count NUMBER;
766: l_string VARCHAR2(30000);
773: END;
774:
775: PROCEDURE write_this(
776: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsCalendar,
777: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
778: pForce IN boolean default false)
779: IS
780: l_count NUMBER;
781: l_table BSC_METADATA_OPTIMIZER_PKG.clsCalendar;
794:
795: PROCEDURE write_this(
796: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsOldBTables,
797: ind IN NUMBER default null,
798: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
799: pForce IN boolean default false)
800: IS
801: l_count NUMBER;
802: l_string VARCHAR2(30000);
809: END;
810:
811: PROCEDURE write_this(
812: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsOldBTables,
813: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
814: pForce IN boolean default false)
815: IS
816: l_count NUMBER;
817: l_table BSC_METADATA_OPTIMIZER_PKG.clsOldBTables;
830:
831: PROCEDURE write_this(
832: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV,
833: ind IN NUMBER default null,
834: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
835: pForce IN boolean default false)
836: IS
837: l_count NUMBER;
838: l_string VARCHAR2(30000);
844: END;
845:
846: PROCEDURE write_this(
847: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsMeasureLOV,
848: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
849: pForce IN boolean default false)
850: IS
851: l_count NUMBER;
852: l_table BSC_METADATA_OPTIMIZER_PKG.clsMeasureLOV;
865:
866: PROCEDURE write_this(
867: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsLevels,
868: ind IN NUMBER default null,
869: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
870: pForce IN boolean default false)
871: IS
872: l_count NUMBER;
873: l_string VARCHAR2(30000);
880: END;
881:
882: PROCEDURE write_this(
883: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsLevels,
884: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
885: pForce IN boolean default false) IS
886: l_count NUMBER;
887: l_table BSC_METADATA_OPTIMIZER_PKG.clsLevels;
888: BEGIN
899: END;
900:
901: PROCEDURE write_this(
902: pTable IN BSC_METADATA_OPTIMIZER_PKG.tabrec_clsLevels,
903: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
904: pForce IN boolean default false)
905: IS
906: l_count NUMBER;
907: BEGIN
913: END;
914:
915: PROCEDURE write_this(
916: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevels,
917: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
918: pForce IN boolean default false)
919: IS
920: l_count NUMBER;
921: l_table BSC_METADATA_OPTIMIZER_PKG.tabrec_clsLevels;
935:
936: PROCEDURE write_this(
937: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsLevelCombinations,
938: ind IN NUMBER default null,
939: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
940: pForce IN boolean default false)
941: IS
942: l_count NUMBER;
943: l_string VARCHAR2(30000);
948: END;
949:
950: PROCEDURE write_this(
951: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsLevelCombinations,
952: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
953: pForce IN boolean default false)
954: IS
955: l_count NUMBER;
956: l_table BSC_METADATA_OPTIMIZER_PKG.clsLevelCombinations;
968: END;
969:
970: PROCEDURE write_this(
971: pTable IN BSC_METADATA_OPTIMIZER_PKG.tabrec_clsLevelCombinations,
972: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
973: pForce IN boolean default false)
974: IS
975: l_count NUMBER;
976: BEGIN
980: END;
981:
982: PROCEDURE write_this(
983: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_clsLevelCombinations,
984: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
985: pForce IN boolean default false)
986: IS
987: l_count NUMBER;
988: l_table BSC_METADATA_OPTIMIZER_PKG.tabrec_clsLevelCombinations;
1003: END;
1004: PROCEDURE write_this(
1005: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsKeyField,
1006: ind IN NUMBER default null,
1007: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1008: pForce IN boolean default false)
1009: IS
1010: l_count NUMBER;
1011: l_string VARCHAR2(300) := null;
1016: ||', calc0Code='||boolean_decode(pTable.calculateCode0)||', FilterView='||pTable.FilterViewName||', dimIndex = '||pTable.dimIndex , pSeverity, pForce);
1017: END IF;
1018: EXCEPTION WHEN OTHERS THEN
1019: l_error := sqlerrm;
1020: writeTmp('Exception in write_this for tab_clsKeyField :' ||l_error, FND_LOG.LEVEL_EXCEPTION, true);
1021: raise;
1022: END;
1023:
1024: PROCEDURE write_this(
1022: END;
1023:
1024: PROCEDURE write_this(
1025: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsKeyField,
1026: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1027: pForce IN boolean default false)
1028: IS
1029: l_count NUMBER;
1030: l_table BSC_METADATA_OPTIMIZER_PKG.clsKeyField;
1041: l_count := pTable.next(l_count);
1042: END LOOP;
1043: EXCEPTION WHEN OTHERS THEN
1044: l_error := sqlerrm;
1045: writeTmp('Exception in write_this for tab_clsKeyField :' ||l_error, FND_LOG.LEVEL_EXCEPTION, true);
1046: raise;
1047: END;
1048:
1049:
1049:
1050: PROCEDURE write_this(
1051: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsDataField,
1052: ind IN NUMBER default null,
1053: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1054: pForce IN boolean default false)
1055: IS
1056: l_count NUMBER;
1057: l_string VARCHAR2(300) := null;
1069: END;
1070:
1071: PROCEDURE write_this(
1072: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField,
1073: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1074: pForce IN boolean default false)
1075: IS
1076: l_count NUMBER;
1077: l_table BSC_METADATA_OPTIMIZER_PKG.clsDataField;
1089: END;
1090: PROCEDURE write_this(
1091: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsBasicTable,
1092: ind IN NUMBER default null,
1093: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1094: pForce IN boolean default false)
1095: IS
1096: l_count NUMBER;
1097: l_string VARCHAR2(30000);
1110: END;
1111:
1112: PROCEDURE write_this(
1113: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsBasicTable,
1114: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1115: pForce IN boolean default false)
1116: IS
1117: l_count NUMBER;
1118: l_table BSC_METADATA_OPTIMIZER_PKG.clsBasicTable;
1131:
1132: PROCEDURE write_this(
1133: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_string,
1134: ind IN NUMBER default null,
1135: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1136: pForce IN boolean default false)
1137: IS
1138: BEGIN
1139: IF BSC_METADATA_OPTIMIZER_PKG.g_log or pForce THEN
1142: END;
1143:
1144: PROCEDURE write_this(
1145: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_tab_string,
1146: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1147: pForce IN boolean default false)
1148: IS
1149: l_count NUMBER;
1150: l_table BSC_METADATA_OPTIMIZER_PKG.tab_string;
1165:
1166: PROCEDURE write_this(
1167: pTable IN BSC_METADATA_OPTIMIZER_PKG.number_table,
1168: ind IN NUMBER default null,
1169: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1170: pForce IN boolean default false)
1171: IS
1172: BEGIN
1173: write_this(pTable.value, pSeverity, pForce);
1175:
1176: PROCEDURE write_this(
1177: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsOriginTable,
1178: ind IN NUMBER default null,
1179: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1180: pForce IN boolean default false)
1181: IS
1182: l_count NUMBER;
1183: l_string VARCHAR2(30000);
1188: END;
1189:
1190: PROCEDURE write_this(
1191: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsOriginTable,
1192: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1193: pForce IN boolean default false)
1194: IS
1195: l_count NUMBER;
1196: l_table BSC_METADATA_OPTIMIZER_PKG.clsOriginTable;
1209:
1210: PROCEDURE write_this(
1211: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsTable,
1212: ind IN NUMBER default null,
1213: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1214: pForce IN boolean default false)
1215: IS
1216: l_count NUMBER;
1217: l_string VARCHAR2(30000);
1241: END;
1242:
1243: PROCEDURE write_this(
1244: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsTable,
1245: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1246: pForce IN boolean default false,
1247: pIgonoreProduction IN boolean default false)
1248: IS
1249: l_count NUMBER;
1258: IF BSC_METADATA_OPTIMIZER_PKG.g_log or pForce THEN
1259: IF pIgonoreProduction AND (l_table.isProductionTable and NOT l_table.isProductionTableAltered) THEN
1260: null;
1261: ELSE
1262: writeTmp(' ', FND_LOG.LEVEL_STATEMENT, pForce);
1263: write_this(l_table, l_count, pSeverity, pForce);
1264: writeTmp(' ', FND_LOG.LEVEL_STATEMENT, pForce);
1265: END IF;
1266: END IF;
1260: null;
1261: ELSE
1262: writeTmp(' ', FND_LOG.LEVEL_STATEMENT, pForce);
1263: write_this(l_table, l_count, pSeverity, pForce);
1264: writeTmp(' ', FND_LOG.LEVEL_STATEMENT, pForce);
1265: END IF;
1266: END IF;
1267: exit when l_count = pTable.last;
1268: l_count := pTable.next(l_count);
1272:
1273: PROCEDURE write_this(
1274: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDisAggField,
1275: ind IN NUMBER default null,
1276: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1277: pForce IN boolean default false)
1278: IS
1279: l_count NUMBER;
1280: l_string VARCHAR2(30000);
1294:
1295: PROCEDURE write_this(
1296: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsDisAggField,
1297: ind IN NUMBER default null,
1298: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1299: pForce IN boolean default false)
1300: IS
1301: l_count NUMBER;
1302: l_string VARCHAR2(30000);
1317: pTableName IN VARCHAR2,
1318: pFieldName IN VARCHAR2,
1319: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsDisAggField,
1320: ind IN NUMBER default null,
1321: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1322: pForce IN boolean default false) IS
1323: l_count NUMBER;
1324: l_string VARCHAR2(30000);
1325: BEGIN
1338: PROCEDURE write_this(
1339: pTableName IN VARCHAR2,
1340: pFieldName IN VARCHAR2,
1341: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDisAggField,
1342: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1343: pForce IN boolean default false)
1344: IS
1345: l_count NUMBER;
1346: l_table BSC_METADATA_OPTIMIZER_PKG.clsDisAggField;
1361: PROCEDURE write_this(
1362: pTableName IN VARCHAR2,
1363: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsUniqueField,
1364: ind IN NUMBER default null,
1365: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1366: pForce IN boolean default false)
1367: IS
1368: l_count NUMBER;
1369: l_string VARCHAR2(30000);
1378:
1379: PROCEDURE write_this(
1380: pTableName IN VARCHAR2,
1381: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsUniqueField,
1382: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1383: pForce IN boolean default false)
1384: IS
1385: l_count NUMBER;
1386: l_table BSC_METADATA_OPTIMIZER_PKG.clsUniqueField;
1400:
1401: PROCEDURE write_this(
1402: pTable IN BSC_METADATA_OPTIMIZER_PKG.clsDBColumn,
1403: ind IN NUMBER default null,
1404: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1405: pForce IN boolean default false)
1406: IS
1407: l_count NUMBER;
1408: l_string VARCHAR2(30000);
1414: END;
1415:
1416: PROCEDURE write_this(
1417: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_clsDBColumn,
1418: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1419: pForce IN boolean default false)
1420: IS
1421: l_count NUMBER;
1422: l_table BSC_METADATA_OPTIMIZER_PKG.clsDBColumn;
1434: END;
1435: PROCEDURE write_this(
1436: pTable IN BSC_METADATA_OPTIMIZER_PKG.TNewITables,
1437: ind IN NUMBER default null,
1438: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1439: pForce IN boolean default false)
1440: IS
1441: l_count NUMBER;
1442: l_string VARCHAR2(30000);
1448: END;
1449:
1450: PROCEDURE write_this(
1451: pTable IN BSC_METADATA_OPTIMIZER_PKG.tab_TNewITables,
1452: pSeverity IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1453: pForce IN boolean default false)
1454: IS
1455: l_count NUMBER;
1456: l_table BSC_METADATA_OPTIMIZER_PKG.TNewITables;
1492: BSC_METADATA_OPTIMIZER_PKG.gTables(l_temp1) := lTable;
1493: l_stack := l_stack ||newline||'Adding gTables('||l_temp1||') = '||lTable.name;
1494: BSC_METADATA_OPTIMIZER_PKG.gTables_counter := BSC_METADATA_OPTIMIZER_PKG.gTables_counter + 1;
1495: EXCEPTION WHEN OTHERS THEN
1496: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1497: BSC_MO_HELPER_PKG.write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1498: writeTmp('l_stack is : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1499: raise;
1500: END;
1493: l_stack := l_stack ||newline||'Adding gTables('||l_temp1||') = '||lTable.name;
1494: BSC_METADATA_OPTIMIZER_PKG.gTables_counter := BSC_METADATA_OPTIMIZER_PKG.gTables_counter + 1;
1495: EXCEPTION WHEN OTHERS THEN
1496: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1497: BSC_MO_HELPER_PKG.write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1498: writeTmp('l_stack is : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1499: raise;
1500: END;
1501:
1494: BSC_METADATA_OPTIMIZER_PKG.gTables_counter := BSC_METADATA_OPTIMIZER_PKG.gTables_counter + 1;
1495: EXCEPTION WHEN OTHERS THEN
1496: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1497: BSC_MO_HELPER_PKG.write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1498: writeTmp('l_stack is : '||l_stack, FND_LOG.LEVEL_UNEXPECTED, true);
1499: raise;
1500: END;
1501:
1502: --****************************************************************************
1524: return;
1525: END IF;
1526: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1527: --BSC_MO_HELPER_PKG.writeTmp('Adding gTables thru PROC '||proc||
1528: -- ', gTables('||BSC_METADATA_OPTIMIZER_PKG.gTables_counter||') is '||lTable.name||' with Data = '||pData.count||', Keys='||pKeyFields.count, FND_LOG.LEVEL_STATEMENT);
1529: null;
1530: END IF;
1531:
1532: lTable.keys := pKeyFields;
1534: BSC_METADATA_OPTIMIZER_PKG.gTables(BSC_METADATA_OPTIMIZER_PKG.gTables_counter) := lTable;
1535: BSC_METADATA_OPTIMIZER_PKG.gTables_counter := BSC_METADATA_OPTIMIZER_PKG.gTables_counter + 1;
1536:
1537: EXCEPTION WHEN OTHERS THEN
1538: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1539: write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1540: writeTmp('keys are :' , FND_LOG.LEVEL_UNEXPECTED, true);
1541: write_this(pKeyFields, FND_LOG.LEVEL_UNEXPECTED, true);
1542: writeTmp('Data :', FND_LOG.LEVEL_UNEXPECTED, true );
1535: BSC_METADATA_OPTIMIZER_PKG.gTables_counter := BSC_METADATA_OPTIMIZER_PKG.gTables_counter + 1;
1536:
1537: EXCEPTION WHEN OTHERS THEN
1538: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1539: write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1540: writeTmp('keys are :' , FND_LOG.LEVEL_UNEXPECTED, true);
1541: write_this(pKeyFields, FND_LOG.LEVEL_UNEXPECTED, true);
1542: writeTmp('Data :', FND_LOG.LEVEL_UNEXPECTED, true );
1543: write_this(pData, FND_LOG.LEVEL_UNEXPECTED, true);
1536:
1537: EXCEPTION WHEN OTHERS THEN
1538: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1539: write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1540: writeTmp('keys are :' , FND_LOG.LEVEL_UNEXPECTED, true);
1541: write_this(pKeyFields, FND_LOG.LEVEL_UNEXPECTED, true);
1542: writeTmp('Data :', FND_LOG.LEVEL_UNEXPECTED, true );
1543: write_this(pData, FND_LOG.LEVEL_UNEXPECTED, true);
1544: raise;
1537: EXCEPTION WHEN OTHERS THEN
1538: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1539: write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1540: writeTmp('keys are :' , FND_LOG.LEVEL_UNEXPECTED, true);
1541: write_this(pKeyFields, FND_LOG.LEVEL_UNEXPECTED, true);
1542: writeTmp('Data :', FND_LOG.LEVEL_UNEXPECTED, true );
1543: write_this(pData, FND_LOG.LEVEL_UNEXPECTED, true);
1544: raise;
1545: END;
1538: writeTmp('Exception in addTable : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
1539: write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1540: writeTmp('keys are :' , FND_LOG.LEVEL_UNEXPECTED, true);
1541: write_this(pKeyFields, FND_LOG.LEVEL_UNEXPECTED, true);
1542: writeTmp('Data :', FND_LOG.LEVEL_UNEXPECTED, true );
1543: write_this(pData, FND_LOG.LEVEL_UNEXPECTED, true);
1544: raise;
1545: END;
1546:
1539: write_this(pTable, 1, FND_LOG.LEVEL_UNEXPECTED, true);
1540: writeTmp('keys are :' , FND_LOG.LEVEL_UNEXPECTED, true);
1541: write_this(pKeyFields, FND_LOG.LEVEL_UNEXPECTED, true);
1542: writeTmp('Data :', FND_LOG.LEVEL_UNEXPECTED, true );
1543: write_this(pData, FND_LOG.LEVEL_UNEXPECTED, true);
1544: raise;
1545: END;
1546:
1547: --****************************************************************************
1578: return l_ret;
1579: EXCEPTION WHEN OTHERS THEN
1580: l_error := sqlerrm;
1581: writeTmp('Exception in CalcProjectionTable for table= '||TableName||', Exception is :' ||l_error,
1582: FND_LOG.LEVEL_EXCEPTION, true);
1583: raise;
1584: End;
1585:
1586:
1623:
1624: EXCEPTION WHEN OTHERS THEN
1625: l_error := sqlerrm;
1626: writeTmp('Exception in WriteInfoMatrix for Indic = '||indic||', variable = '||variable||', value = '||valor||' :'||l_error,
1627: FND_LOG.LEVEL_EXCEPTION, true);
1628: raise;
1629: End ;
1630:
1631: --****************************************************************************
1664:
1665:
1666: EXCEPTION WHEN OTHERS THEN
1667: l_error := sqlerrm;
1668: writeTmp('Exception in SaveOptimizationMode:'||l_error, FND_LOG.LEVEL_EXCEPTION, true);
1669: raise;
1670:
1671: END;
1672:
1688: WHERE TABLE_NAME = p_table AND COLUMN_NAME = upper(p_column) AND OWNER = p3;
1689: l_error VARCHAR2(400);
1690: BEGIN
1691: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1692: BSC_MO_HELPER_PKG.writeTmp('Inside table_column_exists', FND_LOG.LEVEL_STATEMENT);
1693: END IF;
1694:
1695: OPEN C1(UPPER(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema));
1696: FETCH c1 INTO l_table;
1696: FETCH c1 INTO l_table;
1697: IF (c1%FOUND) THEN
1698: CLOSE c1;
1699: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1700: BSC_MO_HELPER_PKG.writeTmp('Completed table_column_exists, returning TRUE', FND_LOG.LEVEL_STATEMENT);
1701: END IF;
1702:
1703: return true;
1704: END IF;
1703: return true;
1704: END IF;
1705: CLOSE c1;
1706: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1707: BSC_MO_HELPER_PKG.writeTmp('Completed table_column_exists, returning FALSE', FND_LOG.LEVEL_STATEMENT);
1708: END IF;
1709:
1710: return false;
1711: EXCEPTION WHEN OTHERS THEN
1709:
1710: return false;
1711: EXCEPTION WHEN OTHERS THEN
1712: l_error := sqlerrm;
1713: writeTmp('Exception in table_column_exists: '||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
1714: raise;
1715: End ;
1716:
1717:
2069: return -1;
2070:
2071: EXCEPTION WHEN OTHERS THEN
2072:
2073: writeTmp('Exception in FindIndex for tab_clsPeriodicity', FND_LOG.LEVEL_STATEMENT, true);
2074: l_error := sqlerrm;
2075: writeTmp('Exception is '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
2076: raise;
2077: End;
2071: EXCEPTION WHEN OTHERS THEN
2072:
2073: writeTmp('Exception in FindIndex for tab_clsPeriodicity', FND_LOG.LEVEL_STATEMENT, true);
2074: l_error := sqlerrm;
2075: writeTmp('Exception is '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
2076: raise;
2077: End;
2078:
2079: Function FindIndex(arrStr IN BSC_METADATA_OPTIMIZER_PKG.tab_clsCalendar, findThis in NUMBER) return NUMBER IS
2307:
2308: PROCEDURE InitArrReservedWords IS
2309: BEGIN
2310: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2311: BSC_MO_HELPER_PKG.writeTmp('Inside InitArrReservedWords', FND_LOG.LEVEL_PROCEDURE);
2312: END IF;
2313:
2314: BSC_METADATA_OPTIMIZER_PKG.gNumArrReservedWords := 556;
2315:
2870: BSC_METADATA_OPTIMIZER_PKG.gArrReservedWords(554) := 'XID';
2871: BSC_METADATA_OPTIMIZER_PKG.gArrReservedWords(555) := 'XOR';
2872:
2873: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2874: BSC_MO_HELPER_PKG.writeTmp('Completed InitArrReservedWords', FND_LOG.LEVEL_PROCEDURE);
2875: END IF;
2876:
2877: End;
2878:
2925: cRow c1%ROWTYPE;
2926:
2927: BEGIN
2928: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2929: BSC_MO_HELPER_PKG.writeTmp('Inside InitLOV', FND_LOG.LEVEL_PROCEDURE);
2930: END IF;
2931: OPEN c1;
2932: LOOP
2933: FETCH c1 INTO cRow;
2953: BSC_METADATA_OPTIMIZER_PKG.gLOV(BSC_METADATA_OPTIMIZER_PKG.gLOV.Count) := FieldLOV;
2954: END Loop;
2955: CLOSE c1;
2956: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
2957: BSC_MO_HELPER_PKG.writeTmp('Completed InitLOV', FND_LOG.LEVEL_PROCEDURE);
2958: END IF;
2959: return;
2960: exception when others then
2961: bsc_mo_helper_pkg.writeTmp('Exception in InitLOV : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
2957: BSC_MO_HELPER_PKG.writeTmp('Completed InitLOV', FND_LOG.LEVEL_PROCEDURE);
2958: END IF;
2959: return;
2960: exception when others then
2961: bsc_mo_helper_pkg.writeTmp('Exception in InitLOV : '||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
2962: TerminateWithError('BSC_VAR_LIST_INIT_FAILED', 'InitLOV');
2963: raise;
2964: End;
2965:
3006:
3007: return def;
3008: EXCEPTION WHEN OTHERS THEN
3009: writeTmp('Exception in getKPIPropertyValue for Indic='||Indic||', Variable='||Variable||', def='||def,
3010: FND_LOG.LEVEL_EXCEPTION, true);
3011: l_error := sqlerrm;
3012: writeTmp('Exception is '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3013:
3014: raise;
3008: EXCEPTION WHEN OTHERS THEN
3009: writeTmp('Exception in getKPIPropertyValue for Indic='||Indic||', Variable='||Variable||', def='||def,
3010: FND_LOG.LEVEL_EXCEPTION, true);
3011: l_error := sqlerrm;
3012: writeTmp('Exception is '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3013:
3014: raise;
3015: End;
3016: --===========================================================================+
3053:
3054: return l_cond;
3055: EXCEPTION WHEN OTHERS THEN
3056: l_error := sqlerrm;
3057: writeTmp( 'Exception in Get_New_Big_In_Cond_Number :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3058: RAISE;
3059: End;
3060:
3061: PROCEDURE Add_Value_BULK(x_variable_id IN NUMBER, x_value IN DBMS_SQL.NUMBER_TABLE) IS
3068: INSERT INTO BSC_TMP_BIG_IN_COND(session_id, variable_id, value_n)
3069: VALUES
3070: (bsc_metadata_optimizer_pkg.g_session_id, x_variable_id, x_value(i));
3071: exception when others then
3072: writeTmp('exception in add_value_bulk for number table', FND_LOG.LEVEL_EXCEPTION, true);
3073: raise;
3074: END;
3075:
3076: PROCEDURE Add_Value_BULK(x_variable_id IN NUMBER, x_value IN DBMS_SQL.VARCHAR2_TABLE) IS
3083: INSERT INTO BSC_TMP_BIG_IN_COND(session_id, variable_id, value_v)
3084: VALUES
3085: (bsc_metadata_optimizer_pkg.g_session_id, x_variable_id, x_value(i));
3086: exception when others then
3087: writeTmp('exception in add_value_bulk for varchar2 table', FND_LOG.LEVEL_EXCEPTION, true);
3088: raise;
3089: END;
3090: --===========================================================================+
3091: -- Name: Add_Value_Big_In_Cond_Number
3101: bsc_apps.Add_Value_Big_In_Cond(x_variable_id , x_value);
3102:
3103: EXCEPTION WHEN OTHERS THEN
3104: l_error := sqlerrm;
3105: writeTmp( 'Exception in Add_Value_Big_In_Cond_Number :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3106: RAISE;
3107: End;
3108:
3109:
3127: ||' AND VARIABLE_ID = '||x_variable_id||')';
3128: return cond;
3129: EXCEPTION WHEN OTHERS THEN
3130: l_error := sqlerrm;
3131: writeTmp( 'Exception in Get_New_Big_In_Cond_Varchar2 :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3132: RAISE;
3133: End;
3134:
3135: --===========================================================================+
3147: bsc_apps.Add_Value_Big_In_Cond(x_variable_id , x_value);
3148:
3149: EXCEPTION WHEN OTHERS THEN
3150: l_error := sqlerrm;
3151: writeTmp( 'Exception in Add_Value_Big_In_Cond_Varchar2 :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3152: RAISE;
3153: End;
3154:
3155:
3182:
3183: l_arr_tables dbms_sql.varchar2_table;
3184: BEGIN
3185: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3186: writeTmp( 'Inside InsertRelatedTables, numTables='||numTables, fnd_log.level_procedure, false);
3187: END IF;
3188: writeTmp('Insert related tables, tables are ', fnd_log.level_statement, false);
3189: write_this(arrNewTables, fnd_log.level_statement, false);
3190:
3184: BEGIN
3185: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3186: writeTmp( 'Inside InsertRelatedTables, numTables='||numTables, fnd_log.level_procedure, false);
3187: END IF;
3188: writeTmp('Insert related tables, tables are ', fnd_log.level_statement, false);
3189: write_this(arrNewTables, fnd_log.level_statement, false);
3190:
3191: numNewTables := 0;
3192:
3185: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3186: writeTmp( 'Inside InsertRelatedTables, numTables='||numTables, fnd_log.level_procedure, false);
3187: END IF;
3188: writeTmp('Insert related tables, tables are ', fnd_log.level_statement, false);
3189: write_this(arrNewTables, fnd_log.level_statement, false);
3190:
3191: numNewTables := 0;
3192:
3193: --BSC-MV Note: Review this procedure. I changed the logic to improve performance
3279: End If;
3280: End If;
3281:
3282: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3283: writeTmp( 'Compl. InsertRelatedTables', fnd_log.level_procedure, false);
3284: END IF;
3285:
3286:
3287: EXCEPTION WHEN OTHERS THEN
3285:
3286:
3287: EXCEPTION WHEN OTHERS THEN
3288: l_error := sqlerrm;
3289: writeTmp( 'Exception in InsertRelatedTables :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3290: RAISE;
3291:
3292: End;
3293:
3334: collIndicadores(0) := indic;
3335: END IF;
3336: EXCEPTION WHEN OTHERS THEN
3337: l_error := sqlerrm;
3338: writeTmp( 'Exception in AddIndicator :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
3339: RAISE;
3340: End;
3341:
3342: /****************************************************************************
3396: END LOOP;
3397: return NumFieldsExpresion;
3398: EXCEPTION WHEN OTHERS THEN
3399: l_error := sqlerrm;
3400: writeTmp('Exception in GetFieldExpression : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3401: raise;
3402:
3403: End;
3404:
3438: l_stack VARCHAR2(32000);
3439: l_indicator NUMBER;
3440: BEGIN
3441: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3442: writeTmp('Inside MarkIndicsForNonStrucChanges', FND_LOG.LEVEL_PROCEDURE);
3443: END IF;
3444: IF BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 <= 0 THEN
3445: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3446: writeTmp('Completed MarkIndicsForNonStrucChanges, count was 0', FND_LOG.LEVEL_PROCEDURE);
3442: writeTmp('Inside MarkIndicsForNonStrucChanges', FND_LOG.LEVEL_PROCEDURE);
3443: END IF;
3444: IF BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 <= 0 THEN
3445: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3446: writeTmp('Completed MarkIndicsForNonStrucChanges, count was 0', FND_LOG.LEVEL_PROCEDURE);
3447: END IF;
3448: return;
3449: END IF;
3450: --Init and array with the measures used by the indicators flagged for
3469: LOOP
3470: FETCH cv INTO l_measureCol, l_source, l_measureID;
3471: EXIT when cv%NOTFOUND;
3472: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3473: writeTmp('Measure = '||l_measureCol, FND_LOG.LEVEL_PROCEDURE);
3474: END IF;
3475: arrMeasuresCols(numMeasures) := l_measureCol;
3476: arrMeasures_src(numMeasures) := l_source;
3477: numMeasures := numMeasures + 1;
3555: CLOSE cv;
3556: l_stack := l_stack ||' Check 5 ';
3557: END IF;
3558: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
3559: writeTmp('Completed MarkIndicsForNonStrucChanges', FND_LOG.LEVEL_PROCEDURE);
3560: END IF;
3561: EXCEPTION WHEN OTHERS THEN
3562: l_error := sqlerrm;
3563: writeTmp('Exception in MarkIndicsForNonStrucChanges : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3559: writeTmp('Completed MarkIndicsForNonStrucChanges', FND_LOG.LEVEL_PROCEDURE);
3560: END IF;
3561: EXCEPTION WHEN OTHERS THEN
3562: l_error := sqlerrm;
3563: writeTmp('Exception in MarkIndicsForNonStrucChanges : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3564: writeTmp('Local Stack dump = '||l_stack, FND_LOG.LEVEL_EXCEPTION, true);
3565: raise;
3566: End;
3567:
3560: END IF;
3561: EXCEPTION WHEN OTHERS THEN
3562: l_error := sqlerrm;
3563: writeTmp('Exception in MarkIndicsForNonStrucChanges : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3564: writeTmp('Local Stack dump = '||l_stack, FND_LOG.LEVEL_EXCEPTION, true);
3565: raise;
3566: End;
3567:
3568:
3588: CLOSE cv;
3589: return l_value;
3590: EXCEPTION WHEN OTHERS THEN
3591: l_error := sqlerrm;
3592: writeTmp('Exception in getInitColumn : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3593: raise;
3594: END;
3595:
3596: --****************************************************************************
3660: END IF;
3661:
3662: EXCEPTION WHEN OTHERS THEN
3663: l_error := sqlerrm;
3664: writeTmp('Exception in decomposeString : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3665: WriteTmp('Parameters were p_string ='||p_string||'and p_separator = '||p_separator, FND_LOG.LEVEL_EXCEPTION, true);
3666: writeTmp('Loop counter = '||l_count||' and Loop stack is '||l_LOOP_stack, FND_LOG.LEVEL_EXCEPTION, true);
3667: raise;
3668:
3661:
3662: EXCEPTION WHEN OTHERS THEN
3663: l_error := sqlerrm;
3664: writeTmp('Exception in decomposeString : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3665: WriteTmp('Parameters were p_string ='||p_string||'and p_separator = '||p_separator, FND_LOG.LEVEL_EXCEPTION, true);
3666: writeTmp('Loop counter = '||l_count||' and Loop stack is '||l_LOOP_stack, FND_LOG.LEVEL_EXCEPTION, true);
3667: raise;
3668:
3669: END;
3662: EXCEPTION WHEN OTHERS THEN
3663: l_error := sqlerrm;
3664: writeTmp('Exception in decomposeString : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3665: WriteTmp('Parameters were p_string ='||p_string||'and p_separator = '||p_separator, FND_LOG.LEVEL_EXCEPTION, true);
3666: writeTmp('Loop counter = '||l_count||' and Loop stack is '||l_LOOP_stack, FND_LOG.LEVEL_EXCEPTION, true);
3667: raise;
3668:
3669: END;
3670:
3709: END IF;
3710: return l_return_array;
3711: EXCEPTION WHEN OTHERS THEN
3712: l_error := sqlerrm;
3713: writeTmp('Exception in decomposeStringtoNumber : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3714: raise;
3715: END;
3716:
3717: --****************************************************************************
3749: forall i in l_origin_list.first..l_origin_list.last
3750: execute immediate l_stmt USING p_periodicity, l_origin_list(i);
3751: end if;
3752: exception when others then
3753: writeTmp('Exception in insert_per:'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
3754: raise;
3755: END;
3756:
3757: PROCEDURE check_circular_dependency IS
3800: CLOSE cv;
3801: EXCEPTION WHEN OTHERS THEN
3802: --IF (SQLCODE = -01436) THEN
3803: BSC_METADATA_OPTIMIZER_PKG.logprogress('ERROR', 'Ciruclar dependency for periodicity_id = '||cPeriodRow.PERIODICITY_ID);
3804: writeTmp('Ciruclar dependency for periodicity_id = '||cPeriodRow.PERIODICITY_ID, FND_LOG.LEVEL_EXCEPTION, true);
3805: terminateWithMsg('Ciruclar dependency for periodicity_id = '||cPeriodRow.PERIODICITY_ID);
3806: --END IF;
3807: raise;
3808: END ;
3810: CLOSE cPeriods;
3811: dropTable(l_table_name);
3812: BSC_METADATA_OPTIMIZER_PKG.logProgress('INIT', 'Completed check_circular_dependency');
3813: exception when others then
3814: writeTmp('Exception in check_circular_dependency:'||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
3815: raise;
3816: END;
3817:
3818:
3971: END LOOP;
3972:
3973: EXCEPTION WHEN OTHERS THEN
3974: l_error := sqlerrm;
3975: writeTmp('Exception in initializePeriodicities : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
3976: raise;
3977: END;
3978:
3979: PROCEDURE InitializeCalendars IS
4048:
4049:
4050: EXCEPTION WHEN OTHERS THEN
4051: l_error := sqlerrm;
4052: writeTmp('Exception in InitializeCalendars : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
4053: TerminateWithError ('BSC_CALENDAR_INIT_FAILED', 'InitializeCalendars');
4054: raise;
4055: END;
4056:
4125: l_DropTables VARCHAR2(1000);
4126: l_varchar2_table dbms_sql.varchar2_table;
4127: BEGIN
4128: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4129: writeTmp( 'Inside MarkTablesForSelectedKPIs', FND_LOG.LEVEL_PROCEDURE, false);
4130: END IF;
4131:
4132: --Initialize the array garrTables the tables used by the indicators in the array garrIndics()
4133: --EDW Integration note:
4149: strWhereNotInIndics := 'NOT ('|| strWhereInIndics ||')';
4150: --Bug 5138449, dont use tmp table, use kpi_data_tables as we need all S tables, not just the lowest level S tables
4151: l_stmt := ' SELECT DISTINCT TABLE_NAME FROM BSC_KPI_DATA_TABLES WHERE ('||
4152: strWhereInIndics|| ') AND TABLE_NAME IS NOT NULL';
4153: writeTmp( 'l_stmt = '||l_stmt, FND_LOG.LEVEL_STATEMENT, false);
4154: OPEN cv for l_stmt;
4155: LOOP
4156: FETCH cv into lTable;
4157: EXIT WHEN cv%NOTFOUND;
4182: -- Step 0 is already taken care of in InitIndicators
4183: ---------------------------------------------------------------
4184: -- Step 1. Find list of tables for 0. going from S to I tables
4185: ---------------------------------------------------------------
4186: writeTmp( 'Calling InsertDirectTables', FND_LOG.LEVEL_STATEMENT, false);
4187: InsertDirectTables(BSC_METADATA_OPTIMIZER_PKG.garrTables, 21);
4188: writeTmp( 'Done with InsertDirectTables', FND_LOG.LEVEL_STATEMENT, false);
4189: strWhereInDirectTables := Get_New_Big_In_Cond_Varchar2( 22, 'TABLE_NAME');
4190: BSC_METADATA_OPTIMIZER_PKG.gnumTables := BSC_METADATA_OPTIMIZER_PKG.garrTables.count;
4184: -- Step 1. Find list of tables for 0. going from S to I tables
4185: ---------------------------------------------------------------
4186: writeTmp( 'Calling InsertDirectTables', FND_LOG.LEVEL_STATEMENT, false);
4187: InsertDirectTables(BSC_METADATA_OPTIMIZER_PKG.garrTables, 21);
4188: writeTmp( 'Done with InsertDirectTables', FND_LOG.LEVEL_STATEMENT, false);
4189: strWhereInDirectTables := Get_New_Big_In_Cond_Varchar2( 22, 'TABLE_NAME');
4190: BSC_METADATA_OPTIMIZER_PKG.gnumTables := BSC_METADATA_OPTIMIZER_PKG.garrTables.count;
4191: Add_Value_Bulk(22, BSC_METADATA_OPTIMIZER_PKG.garrTables);
4192: arrayDirectTables := BSC_METADATA_OPTIMIZER_PKG.garrTables;
4192: arrayDirectTables := BSC_METADATA_OPTIMIZER_PKG.garrTables;
4193: ---------------------------------------------------
4194: -- Step 2. Find list of inter-related tables for 1.
4195: ---------------------------------------------------
4196: writeTmp( 'Calling InsertRelatedTables', FND_LOG.LEVEL_STATEMENT, false);
4197: InsertRelatedTables( arrayDirectTables, BSC_METADATA_OPTIMIZER_PKG.gnumTables);
4198: writeTmp( 'Done InsertRelatedTables', FND_LOG.LEVEL_STATEMENT, false);
4199: --Mark the indicators affected by those tables
4200: strWhereInTables := Get_New_Big_In_Cond_Varchar2( 23, 'TABLE_NAME');
4194: -- Step 2. Find list of inter-related tables for 1.
4195: ---------------------------------------------------
4196: writeTmp( 'Calling InsertRelatedTables', FND_LOG.LEVEL_STATEMENT, false);
4197: InsertRelatedTables( arrayDirectTables, BSC_METADATA_OPTIMIZER_PKG.gnumTables);
4198: writeTmp( 'Done InsertRelatedTables', FND_LOG.LEVEL_STATEMENT, false);
4199: --Mark the indicators affected by those tables
4200: strWhereInTables := Get_New_Big_In_Cond_Varchar2( 23, 'TABLE_NAME');
4201: i:= 0;
4202: writeTmp( 'strWhereInTables='||strWhereInTables, FND_LOG.LEVEL_STATEMENT, false);
4198: writeTmp( 'Done InsertRelatedTables', FND_LOG.LEVEL_STATEMENT, false);
4199: --Mark the indicators affected by those tables
4200: strWhereInTables := Get_New_Big_In_Cond_Varchar2( 23, 'TABLE_NAME');
4201: i:= 0;
4202: writeTmp( 'strWhereInTables='||strWhereInTables, FND_LOG.LEVEL_STATEMENT, false);
4203: writeTmp( 'BSC_METADATA_OPTIMIZER_PKG.garrTables.count='||BSC_METADATA_OPTIMIZER_PKG.garrTables.count, FND_LOG.LEVEL_STATEMENT, false);
4204: l_varchar2_list.delete;
4205: LOOP
4206: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.garrTables.count=0;
4199: --Mark the indicators affected by those tables
4200: strWhereInTables := Get_New_Big_In_Cond_Varchar2( 23, 'TABLE_NAME');
4201: i:= 0;
4202: writeTmp( 'strWhereInTables='||strWhereInTables, FND_LOG.LEVEL_STATEMENT, false);
4203: writeTmp( 'BSC_METADATA_OPTIMIZER_PKG.garrTables.count='||BSC_METADATA_OPTIMIZER_PKG.garrTables.count, FND_LOG.LEVEL_STATEMENT, false);
4204: l_varchar2_list.delete;
4205: LOOP
4206: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.garrTables.count=0;
4207: IF searchStringExists(arrayDirectTables, arrayDirectTables.count, BSC_METADATA_OPTIMIZER_PKG.garrTables(i)) THEN
4213: i := BSC_METADATA_OPTIMIZER_PKG.garrTables.next(i);
4214: END LOOP;
4215: Add_Value_Bulk(23, l_varchar2_list);
4216: l_varchar2_list.delete;
4217: writeTmp( 'Done loop', FND_LOG.LEVEL_STATEMENT, false);
4218: writeTmp( 'strWhereInTables ='||strWhereInTables, FND_LOG.LEVEL_STATEMENT, false);
4219: writeTmp( 'strWhereNotInIndics ='||strWhereNotInIndics, FND_LOG.LEVEL_STATEMENT, false);
4220: ----------------------------------------------------------
4221: --Step 3. Find list of indicators using any table from 2.
4214: END LOOP;
4215: Add_Value_Bulk(23, l_varchar2_list);
4216: l_varchar2_list.delete;
4217: writeTmp( 'Done loop', FND_LOG.LEVEL_STATEMENT, false);
4218: writeTmp( 'strWhereInTables ='||strWhereInTables, FND_LOG.LEVEL_STATEMENT, false);
4219: writeTmp( 'strWhereNotInIndics ='||strWhereNotInIndics, FND_LOG.LEVEL_STATEMENT, false);
4220: ----------------------------------------------------------
4221: --Step 3. Find list of indicators using any table from 2.
4222: ----------------------------------------------------------
4215: Add_Value_Bulk(23, l_varchar2_list);
4216: l_varchar2_list.delete;
4217: writeTmp( 'Done loop', FND_LOG.LEVEL_STATEMENT, false);
4218: writeTmp( 'strWhereInTables ='||strWhereInTables, FND_LOG.LEVEL_STATEMENT, false);
4219: writeTmp( 'strWhereNotInIndics ='||strWhereNotInIndics, FND_LOG.LEVEL_STATEMENT, false);
4220: ----------------------------------------------------------
4221: --Step 3. Find list of indicators using any table from 2.
4222: ----------------------------------------------------------
4223: l_dontProcessIndics := 'SELECT DISTINCT INDICATOR FROM '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' WHERE ('||strWhereInTables|| ')';
4224: --------------------------------------------------
4225: --Step 4. Affected inter-related indicators 3 - 0
4226: --------------------------------------------------
4227: l_dontProcessIndics := l_dontProcessIndics||' AND ('||strWhereNotInIndics||')';
4228: writeTmp( 'l_dontProcessIndics ='||l_dontProcessIndics, FND_LOG.LEVEL_STATEMENT, false);
4229: -- for the indicators that are impacted but are not being processed, we need to preserve
4230: -- the tables. so get the list of tables used by these indicators
4231: ---------------------------------------------------------------------
4232: --Step 5. For indicators in 4, get list of tables going from S to I tables
4233: ---------------------------------------------------------------------
4234: l_dontDropTables := ' select source_table_name from bsc_db_tables_rels
4235: connect by table_name = prior source_table_name start with table_name in
4236: (select table_name from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table||' where indicator in ('||l_dontProcessIndics||') )';
4237: writeTmp( 'l_dontDropTables ='||l_dontDropTables, FND_LOG.LEVEL_STATEMENT, false);
4238: ---------------------------------------------------
4239: --Step 6. Subtract 1-5
4240: ---------------------------------------------------
4241: l_dropTables := 'select table_name from bsc_db_tables where ('||strWhereInDirectTables||') '||' and table_name not in ('||
4239: --Step 6. Subtract 1-5
4240: ---------------------------------------------------
4241: l_dropTables := 'select table_name from bsc_db_tables where ('||strWhereInDirectTables||') '||' and table_name not in ('||
4242: l_dontDropTables||')';
4243: writeTmp( 'l_dropTables ='||l_dropTables, FND_LOG.LEVEL_STATEMENT, false);
4244: BSC_METADATA_OPTIMIZER_PKG.gnumTables := 0;
4245: BSC_METADATA_OPTIMIZER_PKG.garrTables.delete;
4246: l_dropTables := replace(l_dropTables, 'UPPER(TABLE_NAME)', 'TABLE_NAME');
4247: l_dropTables := replace(l_dropTables, 'UPPER(VALUE_V)', 'VALUE_V');
4251: FOR k in 1..l_varchar2_table.count LOOP
4252: lTable := l_varchar2_table(k);
4253: BSC_METADATA_OPTIMIZER_PKG.garrTables(BSC_METADATA_OPTIMIZER_PKG.gnumTables) := lTable;
4254: BSC_METADATA_OPTIMIZER_PKG.gnumTables := BSC_METADATA_OPTIMIZER_PKG.gnumTables + 1;
4255: writeTmp( 'will drop ' || lTable, FND_LOG.LEVEL_STATEMENT, false);
4256: END Loop;
4257: END IF;
4258: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4259: writeTmp( 'Completed MarkTablesForSelectedKPIs', FND_LOG.LEVEL_PROCEDURE, false);
4255: writeTmp( 'will drop ' || lTable, FND_LOG.LEVEL_STATEMENT, false);
4256: END Loop;
4257: END IF;
4258: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4259: writeTmp( 'Completed MarkTablesForSelectedKPIs', FND_LOG.LEVEL_PROCEDURE, false);
4260: END IF;
4261: EXCEPTION WHEN OTHERS THEN
4262: lError := sqlerrm;
4263: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4324: writeTmp( 'Compl. CheckAllIndicsHaveSystem');
4325: END IF;
4326: EXCEPTION WHEN OTHERS THEN
4327: l_error := sqlerrm;
4328: writeTmp('Exception in CheckAllIndicsHaveSystem : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
4329: raise;
4330: End;
4331:
4332: /****************************************************************************
4474:
4475: BEGIN
4476:
4477:
4478: writeTmp( 'Inside InitIndicators, system time is '||get_time, FND_LOG.LEVEL_PROCEDURE, true);
4479:
4480: BSC_METADATA_OPTIMIZER_PKG.garrIndics.delete;
4481: BSC_METADATA_OPTIMIZER_PKG.gnumIndics := 0;
4482:
4516: SELECT COUNT(1) INTO l_total_count FROM BSC_KPIS_VL;
4517: IF (l_objectives_count <> l_total_count) THEN -- there are autogenerated reports, dont process them
4518: BSC_METADATA_OPTIMIZER_PKG.gGAA_RUN_MODE := 1;
4519: ELSE
4520: writeTmp('Entire system consists of objectives, no autogenerated reports found', FND_LOG.LEVEL_PROCEDURE, true);
4521: END IF;
4522: ELSE-- Modified or Selected indicators
4523: l_stmt := l_stmt || ' where prototype_flag in (2,3) and indicator in (SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE process_id = '||BSC_METADATA_OPTIMIZER_PKG.g_processID||')';
4524: END IF;
4522: ELSE-- Modified or Selected indicators
4523: l_stmt := l_stmt || ' where prototype_flag in (2,3) and indicator in (SELECT INDICATOR FROM BSC_TMP_OPT_UI_KPIS WHERE process_id = '||BSC_METADATA_OPTIMIZER_PKG.g_processID||')';
4524: END IF;
4525: l_Stmt := l_stmt || ' ORDER BY INDICATOR ';
4526: writeTmp(l_Stmt, FND_LOG.LEVEL_STATEMENT, false);
4527: open cv for l_stmt;
4528: LOOP
4529: FETCH cv into l_code, l_name, l_action_flag,
4530: l_IndicatorType, l_configType, l_per_inter, l_share_flag,
4566: CLOSE cTables;
4567: ELSE -- incremental mode or selected mode
4568:
4569: MarkTablesForSelectedKPIs;
4570: writeTmp(' # of indicators now = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count, fnd_log.level_statement, false);
4571: BSC_METADATA_OPTIMIZER_PKG.logProgress('INIT', 'Getting Indics with non structural changes ');
4572: --Add indicators with flag = 4 (reconfigure update)
4573: --in the collection gIndicadores
4574: --Of course if the indicator is already in gIndicadores (Structural changes) we do not change it.
4602: END Loop;
4603: CLOSE cv;
4604: BSC_METADATA_OPTIMIZER_PKG.logProgress('INIT', '# of Indics now 2 '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count||', BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 ='||BSC_METADATA_OPTIMIZER_PKG.gnumIndics4);
4605: writeTmp(' # of indicators now 2= '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count||
4606: ', BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 ='||BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 , fnd_log.level_statement, false);
4607: strWhereInIndics := Get_New_Big_In_Cond_Number( 1, 'INDICATOR');
4608: strWhereNotInIndics := null;
4609: IF (BSC_METADATA_OPTIMIZER_PKG.garrIndics.count>0) THEN
4610: /*FOR i IN BSC_METADATA_OPTIMIZER_PKG.garrIndics.first..BSC_METADATA_OPTIMIZER_PKG.garrIndics.last LOOP
4650: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gnumIndics4 > 0) THEN
4651: l_stmt := l_stmt ||' AND (' || strWhereNotInIndics4 || ')';
4652: End IF;
4653: l_stmt := l_stmt ||' ORDER BY INDICATOR';
4654: writeTmp(' Finding indicators for MV level change : '||l_stmt, fnd_log.level_statement, false);
4655: OPEN cv FOR l_stmt using BSC_METADATA_OPTIMIZER_PKG.g_processID;
4656: LOOP
4657: FETCH cv INTO l_code, l_name, l_action_flag, l_indicatorType,
4658: l_configType, l_per_inter, l_share_flag, l_source_indicator, l_edw_flag;
4668: CLOSE CV;
4669: END IF;
4670: END IF; -- mode <>0
4671: BSC_METADATA_OPTIMIZER_PKG.logProgress('INIT', ' # of indicators to process = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count);
4672: writeTmp(' # of indicators to process = '||BSC_METADATA_OPTIMIZER_PKG.gIndicators.count,FND_LOG.LEVEL_STATEMENT, true);
4673:
4674: -- populate the variables in bsc_dbgen_metadata_reader as it needs to consider the
4675: -- objectives in the current run as also production
4676: writeTmp('Checking if AW objectives exists and marking them');
4688: if (l_aw_kpi_list.count>0) then
4689: bsc_dbgen_metadata_reader.mark_facts_in_process(l_aw_kpi_list);
4690: end if;
4691:
4692: writeTmp( 'Completed InitIndicators, system time is '||get_time, FND_LOG.LEVEL_STATEMENT, true);
4693: write_this(BSC_METADATA_OPTIMIZER_PKG.gIndicators, FND_LOG.LEVEL_STATEMENT, false);
4694:
4695: EXCEPTION WHEN OTHERS THEN
4696: l_error := sqlerrm;
4689: bsc_dbgen_metadata_reader.mark_facts_in_process(l_aw_kpi_list);
4690: end if;
4691:
4692: writeTmp( 'Completed InitIndicators, system time is '||get_time, FND_LOG.LEVEL_STATEMENT, true);
4693: write_this(BSC_METADATA_OPTIMIZER_PKG.gIndicators, FND_LOG.LEVEL_STATEMENT, false);
4694:
4695: EXCEPTION WHEN OTHERS THEN
4696: l_error := sqlerrm;
4697: writeTmp('Exception in InitIndicators : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
4693: write_this(BSC_METADATA_OPTIMIZER_PKG.gIndicators, FND_LOG.LEVEL_STATEMENT, false);
4694:
4695: EXCEPTION WHEN OTHERS THEN
4696: l_error := sqlerrm;
4697: writeTmp('Exception in InitIndicators : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
4698: raise;
4699: End;
4700:
4701:
4803: OPEN cv FOR l_stmt;
4804: FETCH cv INTO l_Val;
4805: CLOSE cv;
4806: writeTmp(l_stmt);
4807: writeTmp('# of rows inserted into '||CopyTableName||':'||l_val, FND_LOG.LEVEL_STATEMENT, false);
4808: exception when others then
4809: bsc_metadata_optimizer_pkg.logprogress('INIT', 'Error while creating '||CopyTableName);
4810: writeTmp('Exception in CreateCopyTable : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
4811: writeTmp('Statement executed was : '||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
4806: writeTmp(l_stmt);
4807: writeTmp('# of rows inserted into '||CopyTableName||':'||l_val, FND_LOG.LEVEL_STATEMENT, false);
4808: exception when others then
4809: bsc_metadata_optimizer_pkg.logprogress('INIT', 'Error while creating '||CopyTableName);
4810: writeTmp('Exception in CreateCopyTable : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
4811: writeTmp('Statement executed was : '||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
4812: raise;
4813:
4814: End;
4807: writeTmp('# of rows inserted into '||CopyTableName||':'||l_val, FND_LOG.LEVEL_STATEMENT, false);
4808: exception when others then
4809: bsc_metadata_optimizer_pkg.logprogress('INIT', 'Error while creating '||CopyTableName);
4810: writeTmp('Exception in CreateCopyTable : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
4811: writeTmp('Statement executed was : '||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
4812: raise;
4813:
4814: End;
4815:
4917: END LOOP;
4918: Close cIndex;
4919: exception when others then
4920: bsc_metadata_optimizer_pkg.logprogress('INIT', 'Error while creating index'||IndexName);
4921: writeTmp('Exception in CreateCopyIndexes : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
4922: writeTmp('Statement executed was : '||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
4923: raise;
4924:
4925: End ;
4918: Close cIndex;
4919: exception when others then
4920: bsc_metadata_optimizer_pkg.logprogress('INIT', 'Error while creating index'||IndexName);
4921: writeTmp('Exception in CreateCopyIndexes : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
4922: writeTmp('Statement executed was : '||l_stmt, FND_LOG.LEVEL_EXCEPTION, true);
4923: raise;
4924:
4925: End ;
4926:
5099: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
5100: writeTmp( 'Compl CreateLastTables, time is '||get_time);
5101: END IF;
5102: EXCEPTION WHEN OTHERS THEN
5103: writeTmp('Exception in CreateLastTables '||TableName||' : '||sqlerrm, FND_LOG.LEVEL_EXCEPTION, true);
5104: raise;
5105: End;
5106: /****************************************************************************
5107: DropTable
5263: return false;
5264:
5265: EXCEPTION WHEN OTHERS THEN
5266: l_error := sqlerrm;
5267: writeTmp('Exception in searchStringExists : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
5268: raise;
5269: End;
5270:
5271: --****************************************************************************
5300:
5301:
5302: EXCEPTION WHEN OTHERS THEN
5303: l_error := sqlerrm;
5304: writeTmp('Exception in searchNumberExists : '||l_error, FND_LOG.LEVEL_EXCEPTION, true);
5305: raise;
5306: End;
5307:
5308:
5359: END LOOP;
5360:
5361: EXCEPTION WHEN OTHERS THEN
5362: l_error := sqlerrm;
5363: writeTmp( 'Exception in InsertChildTables_LAST :'||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
5364: RAISE;
5365:
5366: END;
5367:
5521: writeTmp( 'Compl InitInfoOldSystem, time is '||get_time);
5522: END IF;
5523: EXCEPTION WHEN OTHERS THEN
5524: l_ERROR := sqlerrm;
5525: bsc_mo_helper_pkg.writeTmp( 'Exception in InitInfoOldSystem '||l_ERROR, FND_LOG.LEVEL_UNEXPECTED, true);
5526: RAISE;
5527: END;
5528:
5529:
5554: l_drop_list_number DBMS_SQL.NUMBER_TABLE;
5555:
5556: l_child_table VARCHAR2(1000);
5557: BEGIN
5558: writeTmp( 'Inside deletePreviousRunTables, time is '||get_time, FND_LOG.LEVEL_STATEMENT, true);
5559: -- drop UI table used for getRelatedIndicators
5560: begin
5561: bsc_mo_helper_pkg.dropTable('BSC_TMP_OPT_KPI_DATA');
5562: exception when others then
5654: l_stmt := 'UPDATE BSC_KPI_DATA_TABLES
5655: SET TABLE_NAME = NULL, MV_NAME = NULL, DATA_SOURCE = NULL,
5656: SQL_STMT = NULL, PROJECTION_SOURCE = 0, PROJECTION_DATA = NULL
5657: WHERE '|| strWhereInIndics;
5658: writeTmp(l_stmt, fnd_log.level_statement, false);
5659: Execute immediate l_stmt;
5660: END IF;
5661: IF BSC_METADATA_OPTIMIZER_PKG.garrIndics4.count > 0 THEN
5662: --Set the prototype flag to 4 for the indicators that are going to be re-configured
5694: FETCH cv INTO mv_name;
5695: EXIT WHEN cv%NOTFOUND;
5696: BSC_BIA_WRAPPER.Drop_Summary_MV_VB(mv_name);
5697: IF (bsc_metadata_optimizer_pkg.g_log) then
5698: writeTmp('Dropping summary mv '||mv_name, fnd_log.level_statement, false);
5699: END IF;
5700: BSC_MO_HELPER_PKG.CheckError('BSC_BIA_WRAPPER.Drop_Summary_MV_VB');
5701: END LOOP;
5702: CLOSE cv;
5714: LOOP
5715: FETCH cv INTO mv_name;
5716: EXIT WHEN cv%NOTFOUND;
5717: BSC_BIA_WRAPPER.Drop_Summary_MV_VB(mv_name);
5718: writeTmp('Dropping mv '||mv_name, fnd_log.level_statement, false);
5719: BSC_MO_HELPER_PKG.CheckError('BSC_BIA_WRAPPER.Drop_Summary_MV_VB');
5720: END LOOP;
5721: Close cv;
5722: END IF;
5724: --changes those tables are not going to be re-created
5725: END IF;
5726: --Delete all input, base, temporal and summary tables
5727: i:= BSC_METADATA_OPTIMIZER_PKG.garrTables.first;
5728: writeTmp('Dropping Table and related metadata for ', FND_LOG.LEVEL_STATEMENT, true);
5729: LOOP
5730: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.garrTables.count = 0;
5731: IF substr(BSC_METADATA_OPTIMIZER_PKG.garrTables(i), 1, 6)= 'BSC_B_' THEN
5732: IF is_base_table(BSC_METADATA_OPTIMIZER_PKG.garrTables(i)) THEN
5756: DropTable( BSC_METADATA_OPTIMIZER_PKG.garrTables(i));
5757: -- force this to the output.
5758: l_error := l_error || BSC_METADATA_OPTIMIZER_PKG.garrTables(i) ||', ';
5759: IF (i>0 AND mod(i, 10) = 0) THEN
5760: writeTmp(l_error, FND_LOG.LEVEL_STATEMENT, true);
5761: l_error := null;
5762: END IF;
5763: EXIT WHEN i=BSC_METADATA_OPTIMIZER_PKG.garrTables.last;
5764: i := BSC_METADATA_OPTIMIZER_PKG.garrTables.next(i);
5798: null;
5799: END;
5800: END IF;
5801:
5802: writeTmp( 'Compl deletePreviousRunTables, system time is '||get_time, FND_LOG.LEVEL_STATEMENT, true);
5803: EXCEPTION WHEN OTHERS THEN
5804: l_ERROR := sqlerrm;
5805: bsc_mo_helper_pkg.writeTmp( 'Exception in deletePreviousRunTables '||l_ERROR, FND_LOG.LEVEL_UNEXPECTED, true);
5806: RAISE;
5801:
5802: writeTmp( 'Compl deletePreviousRunTables, system time is '||get_time, FND_LOG.LEVEL_STATEMENT, true);
5803: EXCEPTION WHEN OTHERS THEN
5804: l_ERROR := sqlerrm;
5805: bsc_mo_helper_pkg.writeTmp( 'Exception in deletePreviousRunTables '||l_ERROR, FND_LOG.LEVEL_UNEXPECTED, true);
5806: RAISE;
5807: End;
5808:
5809: --****************************************************************************
6034: b_all_columns_used := false;
6035: end if;
6036: return b_all_columns_used ;
6037: EXCEPTION WHEN OTHERS THEN
6038: writeTmp('Exception in all_columns_used:'||sqlerrm||', l_stmt='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
6039: raise;
6040: END;
6041:
6042: PROCEDURE drop_column(p_Table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
6100:
6101: EXCEPTION
6102: WHEN OTHERS THEN
6103: IF (SQLCODE <> -942) THEN -- table doesnt exist, so ignore
6104: writeTmp('Exception in drop_unused_columns_for_table:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6105: writeTmp('drop_unused_columns_for_table, l_stmt='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
6106: raise;
6107: END IF;
6108: END;
6101: EXCEPTION
6102: WHEN OTHERS THEN
6103: IF (SQLCODE <> -942) THEN -- table doesnt exist, so ignore
6104: writeTmp('Exception in drop_unused_columns_for_table:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6105: writeTmp('drop_unused_columns_for_table, l_stmt='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
6106: raise;
6107: END IF;
6108: END;
6109:
6129: AND table_name in
6130: (select distinct table_name
6131: from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last||' )';
6132:
6133: writeTmp('Drop table SQL in drop unused columns is :'||l_stmt, FND_LOG.LEVEL_STATEMENT, FALSE);
6134: writeTmp(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||','|| BSC_METADATA_OPTIMIZER_PKG.gAppsSchema,
6135: FND_LOG.LEVEL_STATEMENT, FALSE);
6136: OPEN cv FOR l_stmt ;
6137: FETCH cv BULK COLLECT INTO l_arr_table_names;
6131: from '||BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last||' )';
6132:
6133: writeTmp('Drop table SQL in drop unused columns is :'||l_stmt, FND_LOG.LEVEL_STATEMENT, FALSE);
6134: writeTmp(BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||','|| BSC_METADATA_OPTIMIZER_PKG.gAppsSchema,
6135: FND_LOG.LEVEL_STATEMENT, FALSE);
6136: OPEN cv FOR l_stmt ;
6137: FETCH cv BULK COLLECT INTO l_arr_table_names;
6138: CLOSE cv;
6139:
6138: CLOSE cv;
6139:
6140: drop_unused_columns_for_Tables(l_arr_Table_names);
6141: EXCEPTION WHEN OTHERS THEN
6142: writeTmp('Exception in drop_unused_columns:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6143: writeTmp('drop_unused_columns:l_stmt='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
6144: --raise;
6145:
6146: END;
6139:
6140: drop_unused_columns_for_Tables(l_arr_Table_names);
6141: EXCEPTION WHEN OTHERS THEN
6142: writeTmp('Exception in drop_unused_columns:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6143: writeTmp('drop_unused_columns:l_stmt='||l_stmt, FND_LOG.LEVEL_UNEXPECTED, true);
6144: --raise;
6145:
6146: END;
6147:
6173: FOR i IN cDropThese LOOP
6174: execute immediate 'drop materialized view log on '||BSC_METADATA_OPTIMIZER_PKG.gBSCSchema||'.'||i.level_table_name;
6175: END LOOP;
6176: EXCEPTION WHEN OTHERS THEN
6177: writeTmp('Exception in drop_unused_mvlogs:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6178: raise;
6179: END;
6180:
6181: --****************************************************************************
6251: l_table_name VARCHAR2(100);
6252: l_owner VARCHAR2(100);
6253: BEGIN
6254: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6255: BSC_MO_HELPER_PKG.writeTmp('Inside cleanDatabase '||get_time, FND_LOG.LEVEL_PROCEDURE);
6256: END IF;
6257: -- Applicable only for MV architecture and non-upgrade scenario
6258: IF (bsc_metadata_optimizer_pkg.g_bsc_mv and bsc_metadata_optimizer_pkg.g_Sum_Level_Change<>1) then
6259: -- Bug 4318566:drop unused MV logs
6259: -- Bug 4318566:drop unused MV logs
6260: drop_unused_mvlogs;
6261: END IF;
6262: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6263: BSC_MO_HELPER_PKG.writeTmp('Dropped unused mv logs '||get_time, FND_LOG.LEVEL_PROCEDURE);
6264: END IF;
6265:
6266: numAllTables := 0;
6267: --Get all the tables used by all indicators
6323: l_drop_these := '(';
6324: --Drop the table from the database
6325: If numNotUsedTables > 0 Then
6326: For i IN 0..numNotUsedTables - 1 LOOP
6327: writeTmp('Dropping unused table = '||arrNotUsedTables(i)||' '||get_time, FND_LOG.LEVEL_STATEMENT, true);
6328: bsc_metadata_optimizer_pkg.logProgress('MISC', 'Dropping unused table = '||arrNotUsedTables(i));
6329: DropTable(arrNotUsedTables(i));
6330: IF (numNotUsedTables>l_drop_threshold) THEN
6331: null;
6368: EXECUTE IMMEDIATE ' DELETE FROM BSC_DB_CALCULATIONS WHERE table_name IN '||l_drop_these;
6369: END IF;
6370: END IF;
6371: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6372: BSC_MO_HELPER_PKG.writeTmp('Deleted invalid entries from metadata tables '||get_time, FND_LOG.LEVEL_PROCEDURE);
6373: END IF;
6374: -- If the system is in summary tables mode, then bsc_kpi_properties.implementation_type should be 1
6375: -- clean up any corruption caused by foll. case
6376: -- System in in Summary tables. User changes MV level to 2. User sets KPI as AW impl.
6378: IF (BSC_METADATA_OPTIMIZER_PKG.g_BSC_mv=false) THEN
6379: UPDATE BSC_KPI_PROPERTIES set property_value = 1 where property_code = BSC_METADATA_OPTIMIZER_PKG.IMPL_TYPE;
6380: END IF;
6381: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6382: BSC_MO_HELPER_PKG.writeTmp('Updated kpi_properties '||get_time, FND_LOG.LEVEL_PROCEDURE);
6383: END IF;
6384: --DropTable(BSC_METADATA_OPTIMIZER_PKG.g_kpi_tmp_table);
6385: DropTable(BSC_METADATA_OPTIMIZER_PKG.g_dbmeasure_tmp_table );
6386: DropTable(BSC_METADATA_OPTIMIZER_PKG.g_period_circ_check);
6390: DropTable(BSC_METADATA_OPTIMIZER_PKG.g_db_table_rels_last);
6391: DropTable(BSC_METADATA_OPTIMIZER_PKG.g_kpi_data_last);
6392: DropTable(BSC_METADATA_OPTIMIZER_PKG.g_db_tables_cols_last);
6393: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6394: BSC_MO_HELPER_PKG.writeTmp('Dropped temp tables '||get_time, FND_LOG.LEVEL_PROCEDURE);
6395: END IF;
6396: OPEN cDropThese;
6397: LOOP
6398: FETCH cDropThese INTO l_table_name, l_owner;
6396: OPEN cDropThese;
6397: LOOP
6398: FETCH cDropThese INTO l_table_name, l_owner;
6399: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6400: BSC_MO_HELPER_PKG.writeTmp('After fetching cursor time is '||get_time, FND_LOG.LEVEL_PROCEDURE);
6401: END IF;
6402: EXIT WHEN cDropThese%NOTFOUND;
6403: IF (l_owner = BSC_METADATA_OPTIMIZER_PKG.gAppsSchema) THEN -- table in apps schema
6404: execute immediate 'drop table '||l_table_name;
6404: execute immediate 'drop table '||l_table_name;
6405: ELSE
6406: DropTable(l_table_name);
6407: END IF;
6408: BSC_MO_HELPER_PKG.writeTmp('Dropped left over table '||l_table_name||' '||get_time, FND_LOG.LEVEL_STATEMENT, false);
6409: END LOOP;
6410: CLOSE cDropThese;
6411: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6412: BSC_MO_HELPER_PKG.writeTmp('Completed cleanDatabase '||get_time, FND_LOG.LEVEL_PROCEDURE);
6408: BSC_MO_HELPER_PKG.writeTmp('Dropped left over table '||l_table_name||' '||get_time, FND_LOG.LEVEL_STATEMENT, false);
6409: END LOOP;
6410: CLOSE cDropThese;
6411: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6412: BSC_MO_HELPER_PKG.writeTmp('Completed cleanDatabase '||get_time, FND_LOG.LEVEL_PROCEDURE);
6413: END IF;
6414: EXCEPTION WHEN OTHERS THEN
6415: writeTmp('Exception in cleanDatabase:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6416: raise;
6411: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
6412: BSC_MO_HELPER_PKG.writeTmp('Completed cleanDatabase '||get_time, FND_LOG.LEVEL_PROCEDURE);
6413: END IF;
6414: EXCEPTION WHEN OTHERS THEN
6415: writeTmp('Exception in cleanDatabase:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6416: raise;
6417: End;
6418:
6419:
6481: l_severity NUMBER;
6482: BEGIN
6483:
6484: IF pSeverity IS NULL THEN
6485: l_severity := FND_LOG.LEVEL_STATEMENT;
6486: ELSE
6487: l_severity := pSeverity;
6488: END IF;
6489: if( l_severity >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6485: l_severity := FND_LOG.LEVEL_STATEMENT;
6486: ELSE
6487: l_severity := pSeverity;
6488: END IF;
6489: if( l_severity >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6490: FND_LOG.STRING(l_severity, 'bsc.pma.opt.optimize', msg);
6491: end if;
6492: EXCEPTION WHEN OTHERS THEN
6493: fnd_file.put_line(FND_FILE.LOG, 'Exception in write_debug:'||sqlerrm);
6486: ELSE
6487: l_severity := pSeverity;
6488: END IF;
6489: if( l_severity >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
6490: FND_LOG.STRING(l_severity, 'bsc.pma.opt.optimize', msg);
6491: end if;
6492: EXCEPTION WHEN OTHERS THEN
6493: fnd_file.put_line(FND_FILE.LOG, 'Exception in write_debug:'||sqlerrm);
6494: raise;
6515: ELSE
6516: l_force_logging := pForce;
6517: END IF;
6518: IF (pSeverity IS NULL) THEN
6519: l_severity := FND_LOG.LEVEL_STATEMENT;
6520: ELSE
6521: l_severity := pSeverity;
6522: END IF;
6523: IF (l_force_logging=false) THEN
6577: i NUMBER;
6578: l_index NUMBER;
6579: BEGIN
6580:
6581: BSC_MO_HELPER_PKG.writeTmp('Inside UpdateFlags '||get_time, FND_LOG.LEVEL_PROCEDURE, true);
6582:
6583: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators.count>0) THEN
6584: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
6585: END IF;
6590: --Case 2
6591: DELETE FROM BSC_KPIS_B WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6592: DELETE FROM BSC_KPIS_TL WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6593: DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6594: writeTmp('Deleting entries for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||', old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6595: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=1 OR BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=3) THEN
6596: --Case 1, 3
6597: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 0,
6598: LAST_UPDATED_BY = BSC_METADATA_OPTIMIZER_PKG.gUserId,
6604: UPDATE bsc_kpi_analysis_measures_b
6605: SET prototype_flag = 0
6606: WHERE indicator = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6607: --Execute IMMEDIATE l_stmt USING BSC_METADATA_OPTIMIZER_PKG.gUserId, BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6608: writeTmp('Updating prototype_flag=0 for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||' from old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6609: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=4) THEN
6610: --Case 4
6611: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 6,
6612: LAST_UPDATED_BY = BSC_METADATA_OPTIMIZER_PKG.gUserId,
6616: UPDATE bsc_kpi_analysis_measures_b
6617: SET prototype_flag = 7
6618: WHERE indicator = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6619: --Execute IMMEDIATE l_stmt USING BSC_METADATA_OPTIMIZER_PKG.gUserId, BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6620: writeTmp('Updating prototype_flag=6 for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||' from old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6621: ELSE
6622: --BSC_MV Note: If summarizarion level changed then update prototype flag to 6
6623: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 Then
6624: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 6,
6628: -- Color By KPI: Mark KPIs for color re-calculation
6629: UPDATE bsc_kpi_analysis_measures_b
6630: SET prototype_flag = 7
6631: WHERE indicator = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6632: writeTmp('Updating prototype_flag=6 for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||' from old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6633: END IF;
6634: END IF;
6635: EXIT WHEN i = BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
6636: i := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(i);
6661: DropAppsTables;
6662: End IF;
6663: execute immediate 'delete bsc_tmp_opt_ui_kpis where process_id=:1' using BSC_METADATA_OPTIMIZER_PKG.g_processID;
6664:
6665: BSC_MO_HELPER_PKG.writeTmp('Completed UpdateFlags'||get_time, FND_LOG.LEVEL_PROCEDURE, true);
6666: EXCEPTION WHEN OTHERS THEN
6667: writeTmp('Exception in UpdateFlags:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6668: raise;
6669:
6663: execute immediate 'delete bsc_tmp_opt_ui_kpis where process_id=:1' using BSC_METADATA_OPTIMIZER_PKG.g_processID;
6664:
6665: BSC_MO_HELPER_PKG.writeTmp('Completed UpdateFlags'||get_time, FND_LOG.LEVEL_PROCEDURE, true);
6666: EXCEPTION WHEN OTHERS THEN
6667: writeTmp('Exception in UpdateFlags:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);
6668: raise;
6669:
6670: END;
6671:
6781: BSC_METADATA_OPTIMIZER_PKG.g_time_getAllKeyFields := BSC_METADATA_OPTIMIZER_PKG.g_time_getAllKeyFields + (sysdate - l_start_time) * 86400;
6782: return keyFields;
6783: EXCEPTION WHEN OTHERS THEN
6784: l_error := sqlerrm;
6785: writeTmp('Exception in getAllKeyFields for table='||pTableName||' : '||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
6786: raise;
6787:
6788: END;
6789:
6832: return dataField;
6833:
6834: EXCEPTION WHEN OTHERS THEN
6835: l_error := sqlerrm;
6836: writeTmp('Exception in getOneDataField for table='||table_name||', field = '||field_name||' : '||l_error, FND_LOG.LEVEL_UNEXPECTED, true);
6837: raise;
6838: END;
6839:
6840: FUNCTION getAllDataFields(pTableName IN VARCHAR2) RETURN BSC_METADATA_OPTIMIZER_PKG.tab_clsDataField IS
6883: return dataFields;
6884:
6885: EXCEPTION WHEN OTHERS THEN
6886: l_error := sqlerrm;
6887: writeTmp('Exception getAllDataFields for table_name ='||pTableName||' : '||l_error, fnd_log.LEVEL_UNEXPECTED, true);
6888: raise;
6889: END;
6890: */
6891:
7171:
7172:
7173:
7174: BEGIN
7175: bsc_mo_helper_pkg.writeTmp('Inside InitializeMasterTables', FND_LOG.LEVEL_PROCEDURE, false);
7176: OPEN c1;
7177: LOOP
7178: FETCH c1 INTO cRow1;
7179: EXIT when c1%NOTFOUND;
7246: BSC_METADATA_OPTIMIZER_PKG.gRelationsMN(bsc_metadata_optimizer_pkg.gRelationsMN.count):= RelMN;
7247: CLOSE C5;
7248: END Loop;
7249: CLOSE c4;
7250: bsc_mo_helper_pkg.writeTmp('Completed InitializeMasterTables', FND_LOG.LEVEL_PROCEDURE, false);
7251: Exception when others then
7252: bsc_mo_helper_pkg.writeTmp('exception in InitializeMasterTables', FND_LOG.LEVEL_UNEXPECTED, true);
7253: bsc_mo_helper_pkg.writeTmp('gMasterTables are ', FND_LOG.LEVEL_EXCEPTION, true);
7254: bsc_mo_helper_pkg.write_this(bsc_metadata_optimizer_pkg.gMasterTable, FND_LOG.LEVEL_EXCEPTION, true);
7248: END Loop;
7249: CLOSE c4;
7250: bsc_mo_helper_pkg.writeTmp('Completed InitializeMasterTables', FND_LOG.LEVEL_PROCEDURE, false);
7251: Exception when others then
7252: bsc_mo_helper_pkg.writeTmp('exception in InitializeMasterTables', FND_LOG.LEVEL_UNEXPECTED, true);
7253: bsc_mo_helper_pkg.writeTmp('gMasterTables are ', FND_LOG.LEVEL_EXCEPTION, true);
7254: bsc_mo_helper_pkg.write_this(bsc_metadata_optimizer_pkg.gMasterTable, FND_LOG.LEVEL_EXCEPTION, true);
7255: raise;
7256:
7249: CLOSE c4;
7250: bsc_mo_helper_pkg.writeTmp('Completed InitializeMasterTables', FND_LOG.LEVEL_PROCEDURE, false);
7251: Exception when others then
7252: bsc_mo_helper_pkg.writeTmp('exception in InitializeMasterTables', FND_LOG.LEVEL_UNEXPECTED, true);
7253: bsc_mo_helper_pkg.writeTmp('gMasterTables are ', FND_LOG.LEVEL_EXCEPTION, true);
7254: bsc_mo_helper_pkg.write_this(bsc_metadata_optimizer_pkg.gMasterTable, FND_LOG.LEVEL_EXCEPTION, true);
7255: raise;
7256:
7257: END;
7250: bsc_mo_helper_pkg.writeTmp('Completed InitializeMasterTables', FND_LOG.LEVEL_PROCEDURE, false);
7251: Exception when others then
7252: bsc_mo_helper_pkg.writeTmp('exception in InitializeMasterTables', FND_LOG.LEVEL_UNEXPECTED, true);
7253: bsc_mo_helper_pkg.writeTmp('gMasterTables are ', FND_LOG.LEVEL_EXCEPTION, true);
7254: bsc_mo_helper_pkg.write_this(bsc_metadata_optimizer_pkg.gMasterTable, FND_LOG.LEVEL_EXCEPTION, true);
7255: raise;
7256:
7257: END;
7258:
7312: ELSE
7313: writeTmp('Attempting to refresh Calendar id '||i.calendar_id ||
7314: ' changed, calling load reporting calendar for this:'||get_time);
7315: IF NOT BSC_BIA_WRAPPER.Load_Reporting_Calendar(i.calendar_id, l_error_message) THEN
7316: writeTmp('Error Loading reporting calendar :'||l_error_message, FND_LOG.LEVEL_UNEXPECTED, true);
7317: BSC_MO_HELPER_PKG.TerminateWithMsg(l_error_message);
7318: raise BSC_METADATA_OPTIMIZER_PKG.optimizer_exception;
7319: END IF;
7320: writeTmp('Done refreshing Calendar id '||i.calendar_id ||' '||get_time);