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

APPS.BSC_MO_LOADER_CONFIG_PKG dependencies on BSC_DB_TABLES

Line 26: from bsc_db_tables tbl,

22:
23: l_origin_name VARCHAR2(100);
24: CURSOR cTableType IS
25: select count(1)
26: from bsc_db_tables tbl,
27: bsc_db_tables_rels rels
28: where
29: rels.table_name = p_table_name
30: and rels.source_table_name = tbl.table_name

Line 27: bsc_db_tables_rels rels

23: l_origin_name VARCHAR2(100);
24: CURSOR cTableType IS
25: select count(1)
26: from bsc_db_tables tbl,
27: bsc_db_tables_rels rels
28: where
29: rels.table_name = p_table_name
30: and rels.source_table_name = tbl.table_name
31: and tbl.table_type = 0;

Line 349: SELECT PROJECT_FLAG FROM BSC_DB_TABLES

345:
346: Function CalcProjectionTable(TableName IN VARCHAR2) return BOOLEAN IS
347:
348: CURSOR C1(p1 VARCHAR2) IS
349: SELECT PROJECT_FLAG FROM BSC_DB_TABLES
350: WHERE TABLE_NAME = p1;
351: l_proj NUMBER ;
352: l_ret boolean;
353: BEGIN

Line 380: -- Return the type of the table according to BSC_DB_TABLES_RELS and BSC_DB_TABLES

376: --****************************************************************************
377: -- GetIndicTableType
378: --
379: -- DESCRIPTION:
380: -- Return the type of the table according to BSC_DB_TABLES_RELS and BSC_DB_TABLES
381: -- arrIndicTables() contains the tables used directly by the indicator
382: -- 0 - Input table
383: -- 1 - Base table
384: -- 2 - Temporal Table

Line 395: FROM BSC_DB_TABLES T, BSC_DB_TABLES_RELS R

391: l_table_name VARCHAR2(100);
392: l_table_type NUMBER;
393: CURSOR c1(p1 VARCHAR2) IS
394: SELECT T.TABLE_NAME, T.TABLE_TYPE
395: FROM BSC_DB_TABLES T, BSC_DB_TABLES_RELS R
396: WHERE T.TABLE_NAME = R.SOURCE_TABLE_NAME
397: AND R.TABLE_NAME = p1;
398: l_return NUMBER;
399: BEGIN

Line 476: l_stmt := 'SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;

472: l_index := arrTables.next(l_index);
473: END LOOP;
474: bsc_mo_helper_pkg.add_value_bulk(1, l_varchar2_list);
475: l_varchar2_list.delete;
476: l_stmt := 'SELECT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS WHERE '|| strWhereInTables;
477: OPEN CV for l_stmt;
478: LOOP
479: FETCH CV INTO l_table;
480: EXIT WHEN CV%NOTFOUND;

Line 518: SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS

514: AND DIM_SET_ID = p2
515: AND TABLE_NAME IS NOT NULL;
516:
517: CURSOR c2 (p1 NUMBER, p2 NUMBER) IS
518: SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
519: WHERE TABLE_NAME IN (
520: SELECT TABLE_NAME
521: FROM BSC_KPI_DATA_TABLES_V
522: WHERE INDICATOR = p1

Line 686: select table_name from bsc_db_Tables_rels

682:
683: l_dummy varchar2(1000);
684:
685: cursor MissingSTables IS
686: select table_name from bsc_db_Tables_rels
687: connect by source_table_name = prior table_name
688: start with source_table_name in
689: (
690: select value_v from bsc_tmp_big_in_cond

Line 695: select table_name from bsc_db_tables_rels

691: where variable_id=10
692: and session_id = userenv('SESSIONID')
693: )
694: union -- add deleted periodicities
695: select table_name from bsc_db_tables_rels
696: where substr(table_name, 1, instr(table_name, '_', -1)) in
697: (select substr(value_v, 1, instr(value_v, '_', -1))
698: from bsc_tmp_big_in_cond
699: where variable_id = 10

Line 770: TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;

766: l_tblrels_table_name dbms_sql.varchar2_table;
767: l_tblrels_src_table_name dbms_sql.varchar2_table;
768: l_tblrels_relation_type dbms_sql.number_table;
769:
770: TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;
771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772: l_tab_rels tab_clsRels;
773:
774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;

Line 771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;

767: l_tblrels_src_table_name dbms_sql.varchar2_table;
768: l_tblrels_relation_type dbms_sql.number_table;
769:
770: TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;
771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772: l_tab_rels tab_clsRels;
773:
774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;
775: l_db_tables_record BSC_DB_TABLES%ROWTYPE ;

Line 774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;

770: TYPE tab_clsRels IS TABLE OF BSC_DB_TABLES_RELS%ROWTYPE index by binary_integer;
771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772: l_tab_rels tab_clsRels;
773:
774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;
775: l_db_tables_record BSC_DB_TABLES%ROWTYPE ;
776: l_tab_db_tables tab_clsDBTables;
777:
778: TYPE tab_clsDBCalculations IS TABLE OF BSC_DB_CALCULATIONS%ROWTYPE index by binary_integer;

Line 775: l_db_tables_record BSC_DB_TABLES%ROWTYPE ;

771: l_rels_record BSC_DB_TABLES_RELS%ROWTYPE ;
772: l_tab_rels tab_clsRels;
773:
774: TYPE tab_clsDBTables IS TABLE OF BSC_DB_TABLES%ROWTYPE index by binary_integer;
775: l_db_tables_record BSC_DB_TABLES%ROWTYPE ;
776: l_tab_db_tables tab_clsDBTables;
777:
778: TYPE tab_clsDBCalculations IS TABLE OF BSC_DB_CALCULATIONS%ROWTYPE index by binary_integer;
779: l_db_calculations_record BSC_DB_CALCULATIONS%ROWTYPE ;

Line 782: TYPE tab_clsDBTablesCols IS TABLE OF BSC_DB_TABLES_COLS%ROWTYPE index by binary_integer;

778: TYPE tab_clsDBCalculations IS TABLE OF BSC_DB_CALCULATIONS%ROWTYPE index by binary_integer;
779: l_db_calculations_record BSC_DB_CALCULATIONS%ROWTYPE ;
780: l_tab_db_calculations tab_clsDBCalculations;
781:
782: TYPE tab_clsDBTablesCols IS TABLE OF BSC_DB_TABLES_COLS%ROWTYPE index by binary_integer;
783: l_db_tables_cols_record BSC_DB_TABLES_COLS%ROWTYPE ;
784: l_tab_db_tables_cols tab_clsDBTablesCols;
785:
786: l_db_calc_1_delete dbms_sql.varchar2_table;

Line 783: l_db_tables_cols_record BSC_DB_TABLES_COLS%ROWTYPE ;

779: l_db_calculations_record BSC_DB_CALCULATIONS%ROWTYPE ;
780: l_tab_db_calculations tab_clsDBCalculations;
781:
782: TYPE tab_clsDBTablesCols IS TABLE OF BSC_DB_TABLES_COLS%ROWTYPE index by binary_integer;
783: l_db_tables_cols_record BSC_DB_TABLES_COLS%ROWTYPE ;
784: l_tab_db_tables_cols tab_clsDBTablesCols;
785:
786: l_db_calc_1_delete dbms_sql.varchar2_table;
787:

Line 835: DELETE FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME = l_tbl_delete(i);

831: --
832: -- this procedure will add any missing S tables to l_tbl_delete
833: add_dependant_tables(l_del_s_tables, l_tbl_delete);
834: FORALL i IN 1..l_tbl_delete.count
835: DELETE FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME = l_tbl_delete(i);
836: FORALL i IN 1..l_tbl_delete.count
837: DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);
838: FORALL i IN 1..l_tbl_delete.count
839: DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);

Line 837: DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);

833: add_dependant_tables(l_del_s_tables, l_tbl_delete);
834: FORALL i IN 1..l_tbl_delete.count
835: DELETE FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME = l_tbl_delete(i);
836: FORALL i IN 1..l_tbl_delete.count
837: DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);
838: FORALL i IN 1..l_tbl_delete.count
839: DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);
840: FORALL i IN 1..l_tbl_delete.count
841: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);

Line 841: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);

837: DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);
838: FORALL i IN 1..l_tbl_delete.count
839: DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);
840: FORALL i IN 1..l_tbl_delete.count
841: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);
842: l_tab_rels.delete;
843: l_index1 := BSC_METADATA_OPTIMIZER_PKG.gTables.first;
844: LOOP
845: Tabla := BSC_METADATA_OPTIMIZER_PKG.gTables(l_index1);

Line 864: --BSC_DB_TABLES_RELS

860: BSC_MO_HELPER_PKG.writeTmp('Processing gTables('||l_index1||') '||bsc_mo_helper_pkg.get_time);
861: BSC_MO_HELPER_PKG.write_this(Tabla);
862: END IF;
863: bsc_mo_helper_pkg.writeTmp('Going to Delete Metadata tables for '||UPPER(Tabla.Name), FND_LOG.LEVEL_STATEMENT, false);
864: --BSC_DB_TABLES_RELS
865: If Tabla.Type = 1 Then
866: -- Changed to bulk deletes/inserts for better performance - bug 4559323
867: PeriodicityOrigin := 0;
868: OriTableName := null;

Line 916: --BSC_DB_TABLES

912: bsc_mo_helper_pkg.writeTmp('Done checking Soft Relations1');
913: END IF;
914: End If; -- IF TABLA.type = 1
915:
916: --BSC_DB_TABLES
917: get_projection_and_gen_type(Tabla, PeriodicityOrigin, projection, l_generation_type);
918:
919: --EDW Note: Each calendar has his own fiscal year, and range of years
920: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gPeriodicities, Tabla.Periodicity);

Line 1055: --BSC_DB_TABLES_COLS (Key columns)

1051: l_index2 := Tabla_originTable1.next(l_index2);
1052: END LOOP;
1053: END IF;
1054:
1055: --BSC_DB_TABLES_COLS (Key columns)
1056:
1057: IF (Tabla_keyName.count>0) THEN
1058: l_index2 := Tabla_keyName.first;
1059: LOOP

Line 1077: --BSC_DB_TABLES_COLS (Data columns)

1073: l_index2 := Tabla_keyName.next(l_index2);
1074: END LOOP;
1075: END IF;
1076:
1077: --BSC_DB_TABLES_COLS (Data columns)
1078:
1079: IF (Tabla_Data.count >0) THEN
1080: l_index2 := Tabla_Data.first;
1081: LOOP

Line 1147: -- table has been altered, so insert into bsc_db_tables_cols

1143: <>
1144: IF ( Tabla.isProductionTable AND Tabla.isProductionTableAltered) THEN
1145: BSC_MO_HELPER_PKG.writeTmp('Production table altered', FND_LOG.LEVEL_STATEMENT, false);
1146: BSC_MO_HELPER_PKG.write_to_stack('Production table altered');
1147: -- table has been altered, so insert into bsc_db_tables_cols
1148: If Tabla.Type = 1 Then
1149: BSC_MO_HELPER_PKG.write_to_stack('Table Type=1');
1150: PeriodicityOrigin := 0;
1151: Tabla_originTable := BSC_MO_HELPER_PKG.getDecomposedString(Tabla.originTable, ',');

Line 1192: --DELETE FROM BSC_DB_TABLES_COLS WHERE table_name = upper(Tabla_originTable(j)) AND column_type='A' and column_name=l_measure.fieldName;

1188: l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin );
1189: END IF;
1190: l_db_cols_1_delete_table_name(l_db_cols_1_delete_table_name.count+1) := upper(Tabla_originTable(j));
1191: l_db_cols_1_delete_field_name(l_db_cols_1_delete_field_name.count+1) := l_measure.fieldName;
1192: --DELETE FROM BSC_DB_TABLES_COLS WHERE table_name = upper(Tabla_originTable(j)) AND column_type='A' and column_name=l_measure.fieldName;
1193: l_db_tables_cols_record.TABLE_NAME := upper(Tabla_originTable(j));
1194: l_db_tables_cols_record.COLUMN_TYPE := 'A';
1195: l_db_tables_cols_record.COLUMN_NAME := l_measure.fieldName;
1196: l_db_tables_cols_record.SOURCE := l_measure.source;

Line 1215: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME=l_db_cols_1_delete_table_name(i) AND column_name=l_db_cols_1_delete_field_name(i) and column_type='A';

1211: FORALL i IN 1..l_db_calc_1_delete.count
1212: DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_db_calc_1_delete(i) AND CALCULATION_TYPE = 1;
1213:
1214: FORALL i IN 1..l_db_cols_1_delete_table_name.count
1215: DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME=l_db_cols_1_delete_table_name(i) AND column_name=l_db_cols_1_delete_field_name(i) and column_type='A';
1216:
1217:
1218: -- BULK INSERTS
1219: BEGIN

Line 1221: INSERT INTO BSC_DB_TABLES_RELS VALUES l_tab_rels(i);

1217:
1218: -- BULK INSERTS
1219: BEGIN
1220: FORALL i IN 1..l_tab_rels.count
1221: INSERT INTO BSC_DB_TABLES_RELS VALUES l_tab_rels(i);
1222: EXCEPTION
1223: WHEN others THEN
1224: FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP
1225: bsc_mo_helper_pkg.writeTmp('Error ' || i || ' occurred during '||

Line 1252: INSERT INTO BSC_DB_TABLES VALUES l_tab_db_tables(i);

1248: END;
1249:
1250: BEGIN
1251: FORALL i IN 1..l_tab_db_tables.count
1252: INSERT INTO BSC_DB_TABLES VALUES l_tab_db_tables(i);
1253: EXCEPTION
1254: WHEN others THEN
1255:
1256: bsc_mo_helper_pkg.writeTmp('Insert tables: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);

Line 1267: INSERT INTO BSC_DB_TABLES_COLS VALUES l_tab_db_tables_cols(i);

1263: END;
1264:
1265: BEGIN
1266: FORALL i IN 1..l_tab_db_tables_cols.count
1267: INSERT INTO BSC_DB_TABLES_COLS VALUES l_tab_db_tables_cols(i);
1268: EXCEPTION
1269: WHEN others THEN
1270:
1271: bsc_mo_helper_pkg.writeTmp('Insert Cols: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);

Line 1442: FROM BSC_DB_TABLES_COLS

1438: END IF;
1439: -- Bug 4466627
1440: IF (BSC_METADATA_OPTIMIZER_PKG.G_BSC_MV) THEN
1441: SELECT COUNT(1) INTO l_count
1442: FROM BSC_DB_TABLES_COLS
1443: WHERE table_name=l_table_name AND column_name=l_measure.fieldName;
1444: BSC_MO_HELPER_PKG.writeTmp('MV arch, l_count= '||l_count||' for '||l_table_name ||' field='||l_measure.fieldName);
1445: IF (l_count=0) THEN
1446: newColumnFlag := true;

Line 1515: --BSC_DB_TABLES_COLS

1511: FROM BSC_DB_CALCULATIONS
1512: WHERE TABLE_NAME = UPPER(arrTables(i))
1513: AND CALCULATION_TYPE = 5;
1514: End If;
1515: --BSC_DB_TABLES_COLS
1516: IF (l_table_type =3) THEN
1517: --Case 3
1518: --The table is not generated from another indicator table
1519: --This is a base table o the indicator.

Line 1558: INSERT INTO BSC_DB_TABLES_COLS (TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, SOURCE, PROJECTION_ID, SOURCE_FORMULA)

1554: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1555: BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS newcol: table_name='||
1556: arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source_column='||l_source);
1557: END IF;
1558: INSERT INTO BSC_DB_TABLES_COLS (TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, SOURCE, PROJECTION_ID, SOURCE_FORMULA)
1559: VALUES(upper(arrTables(i)), 'A', l_measure.fieldName, l_measure.source, 0, l_source);
1560: Else
1561: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1562: BSC_MO_HELPER_PKG.writeTmp('UPDATE DB_TABLES_COLS newcol: table_name='||

Line 1565: UPDATE BSC_DB_TABLES_COLS

1561: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
1562: BSC_MO_HELPER_PKG.writeTmp('UPDATE DB_TABLES_COLS newcol: table_name='||
1563: arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source='||l_measure.source||', source_column='||l_source);
1564: END IF;
1565: UPDATE BSC_DB_TABLES_COLS
1566: SET SOURCE_FORMULA = l_source
1567: WHERE TABLE_NAME =UPPER(arrTables(i))
1568: AND COLUMN_NAME = l_measure.fieldName
1569: AND SOURCE = l_measure.source

Line 1572: --BSC_DB_TABLES_COLS (Projection method)

1568: AND COLUMN_NAME = l_measure.fieldName
1569: AND SOURCE = l_measure.source
1570: AND COLUMN_TYPE = 'A';
1571: End If;
1572: --BSC_DB_TABLES_COLS (Projection method)
1573: If CalcProjectionTable(arrTables(i)) Then
1574: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1575: UPDATE BSC_DB_TABLES_COLS
1576: SET PROJECTION_ID = BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).PrjMethod

Line 1575: UPDATE BSC_DB_TABLES_COLS

1571: End If;
1572: --BSC_DB_TABLES_COLS (Projection method)
1573: If CalcProjectionTable(arrTables(i)) Then
1574: l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);
1575: UPDATE BSC_DB_TABLES_COLS
1576: SET PROJECTION_ID = BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).PrjMethod
1577: WHERE TABLE_NAME = UPPER(arrTables(i))
1578: AND COLUMN_NAME = l_measure.fieldName
1579: AND SOURCE = l_measure.source