APPS.BSC_MO_LOADER_CONFIG_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 25

select count(1)
from bsc_db_tables tbl,
bsc_db_tables_rels rels
where
rels.table_name = p_table_name
and rels.source_table_name = tbl.table_name
and tbl.table_type = 0;

Line: 143

  SELECT DISTINCT DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B
  WHERE INDICATOR = pIndicator
  AND DIM_SET_ID = Configuracion
  AND STATUS <> 2
  ORDER BY DIM_LEVEL_INDEX;

Line: 191

  SELECT PERIODICITY_ID, LEVEL_COMB
  FROM BSC_KPI_DATA_TABLES
  WHERE INDICATOR = pIndicator
  AND DIM_SET_ID = pDimSetID;

Line: 237

                bsc_mo_helper_pkg.writeTmp('UPDATE BSC_KPI_DATA_TABLES SET LEVEL_COMB = '||
                  strDriles||' WHERE INDICATOR =  '|| Indicator.Code||' AND DIM_SET_ID =  '||
                  Configuracion||' AND PERIODICITY_ID =  '|| l_periodicity_id||' AND LEVEL_COMB = '||strDrilesAnt, FND_LOG.LEVEL_STATEMENT);

Line: 242

              UPDATE BSC_KPI_DATA_TABLES SET LEVEL_COMB = strDriles
              WHERE INDICATOR = Indicator.Code AND DIM_SET_ID = Configuracion
              AND PERIODICITY_ID = l_periodicity_id  AND LEVEL_COMB = strDrilesAnt;

Line: 287

  SELECT DISTINCT TABLE_NAME, PERIODICITY_ID
  FROM BSC_KPI_DATA_TABLES_V
  WHERE INDICATOR = indic
  AND TABLE_NAME IS NOT NULL;

Line: 323

    l_stmt := 'CREATE OR REPLACE VIEW '||  uv_name ||' AS SELECT * FROM '||  table_name;

Line: 349

SELECT PROJECT_FLAG FROM BSC_DB_TABLES
WHERE TABLE_NAME = p1;

Line: 394

  SELECT T.TABLE_NAME, T.TABLE_TYPE
  FROM BSC_DB_TABLES T, BSC_DB_TABLES_RELS R
  WHERE T.TABLE_NAME = R.SOURCE_TABLE_NAME
  AND R.TABLE_NAME = p1;

Line: 436

PROCEDURE InsertOriginTables(arrTables IN DBMS_SQL.VARCHAR2_TABLE ,
                  arrOriginTables IN OUT NOCOPY DBMS_SQL.VARCHAR2_TABLE
                  ) IS

  arrTablesAux DBMS_SQL.VARCHAR2_TABLE;

Line: 451

    BSC_MO_HELPER_PKG.writeTmp('Inside InsertOriginTables, arrTables.count = '||arrTables.count);

Line: 456

      BSC_MO_HELPER_PKG.writeTmp('Completed InsertOriginTables, count = 0');

Line: 475

  l_varchar2_list.delete;

Line: 476

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

Line: 485

  InsertOriginTables (arrTablesAux, arrOriginTables);

Line: 487

    BSC_MO_HELPER_PKG.writeTmp('Completed InsertOriginTables, arrOriginTables.count = '||arrOriginTables.count);

Line: 492

    bsc_mo_helper_pkg.writeTmp('Exception in InsertOriginTables:'||sqlerrm, FND_LOG.LEVEL_UNEXPECTED, true);

Line: 512

  SELECT DISTINCT TABLE_NAME FROM BSC_KPI_DATA_TABLES_V
  WHERE INDICATOR = p1
  AND DIM_SET_ID = p2
  AND TABLE_NAME IS NOT NULL;

Line: 518

  SELECT DISTINCT SOURCE_TABLE_NAME FROM BSC_DB_TABLES_RELS
  WHERE TABLE_NAME IN (
  SELECT TABLE_NAME
  FROM BSC_KPI_DATA_TABLES_V
  WHERE INDICATOR = p1
  AND DIM_SET_ID = p2
  AND TABLE_NAME IS NOT NULL)
  AND RELATION_TYPE = 1;

Line: 556

  InsertOriginTables( arrIndicTables, arrTables);

Line: 582

SELECT DIM_LEVEL_INDEX FROM BSC_KPI_DIM_LEVELS_B
WHERE
INDICATOR = p1 AND
DIM_SET_ID = p2 AND
LEVEL_PK_COL = p3;

Line: 686

select table_name from bsc_db_Tables_rels
connect by source_table_name = prior table_name
start with source_table_name in
(
select value_v from bsc_tmp_big_in_cond
where variable_id=10
and session_id = userenv('SESSIONID')
)
union -- add deleted periodicities
select table_name from bsc_db_tables_rels
where substr(table_name, 1, instr(table_name, '_', -1)) in
   (select substr(value_v, 1, instr(value_v, '_', -1))
      from bsc_tmp_big_in_cond
     where variable_id = 10
       and session_id=userenv('SESSIONID')
   )
;

Line: 765

  l_tbl_delete dbms_sql.varchar2_table;

Line: 786

  l_db_calc_1_delete dbms_sql.varchar2_table;

Line: 788

  l_db_cols_1_delete_table_name dbms_sql.varchar2_table;

Line: 789

  l_db_cols_1_delete_field_name dbms_sql.varchar2_table;

Line: 815

      l_tbl_delete(l_tbl_delete.count+1) := upper(Tabla.Name);

Line: 833

  add_dependant_tables(l_del_s_tables, l_tbl_delete);

Line: 834

  FORALL i IN 1..l_tbl_delete.count
    DELETE FROM BSC_DB_TABLES_RELS WHERE TABLE_NAME = l_tbl_delete(i);

Line: 836

  FORALL i IN 1..l_tbl_delete.count
    DELETE FROM BSC_DB_TABLES WHERE TABLE_NAME = l_tbl_delete(i);

Line: 838

  FORALL i IN 1..l_tbl_delete.count
    DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_tbl_delete(i) AND CALCULATION_TYPE in (l_calc4,l_calc5);

Line: 840

  FORALL i IN 1..l_tbl_delete.count
    DELETE FROM BSC_DB_TABLES_COLS WHERE TABLE_NAME = l_tbl_delete(i) AND COLUMN_TYPE in (l_colP,l_colA);

Line: 842

  l_tab_rels.delete;

Line: 849

    Tabla_keyName.delete;

Line: 851

	Tabla_data.delete;

Line: 853

    Tabla_originTable.delete;

Line: 855

    Tabla_originTable1.delete;

Line: 863

    bsc_mo_helper_pkg.writeTmp('Going to Delete Metadata tables for '||UPPER(Tabla.Name), FND_LOG.LEVEL_STATEMENT, false);

Line: 881

            BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS1:'||Tabla.Name||', '||l_table_origin||', 0');

Line: 901

            BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_RELS2:'||Tabla.Name||','||l_table_origin||',1');

Line: 957

      BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES:'||
          Tabla.Name||', type='|| Tabla.Type||', periodicity='||Tabla.Periodicity||', gen_type='||
          l_generation_type ||', projection = '|| projection||', num_years'|| num_years||', num_prev_years= '||
          num_prev_years||','||Periodo_Act||','|| SubPeriodo_Act||','|| EDW_Flag||',' ||Target_Flag||'  '||bsc_mo_helper_pkg.get_time);

Line: 1006

          BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS1:'||
              Tabla.name||','||arrZeroCodeKeys(i)||','||i||','|| l_measure.fieldName||','||ZeroCodeOrigin||'  '||bsc_mo_helper_pkg.get_time);

Line: 1034

                BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS2:'||
                  Tabla.Name||',5,'|| l_table_origin||','|| l_measure.fieldName);

Line: 1062

          BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS2: table_name='||Tabla.Name||
            ', column_type=P, column_name = '||l_key.keyName||', source_column='||l_key.Origin||'  '||bsc_mo_helper_pkg.get_time);

Line: 1090

          BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS2: Table_name = '||Tabla.Name||', column_type = A, Column Name= '||
					l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin||'  '||bsc_mo_helper_pkg.get_time );

Line: 1104

        BSC_MO_HELPER_PKG.writeTmp('Data Cols insertion completed for Table='||Tabla.Name);

Line: 1127

            l_db_calc_1_delete(l_db_calc_1_delete.count+1) := UPPER(Tabla.Name);

Line: 1129

              BSC_MO_HELPER_PKG.writeTmp('INSERT DB_CALCULATIONS3:'||Tabla.Name||',1,'|| Dril1||'  '||bsc_mo_helper_pkg.get_time);

Line: 1176

          IF Tabla.data(i).changeType='NEW' THEN -- new column, insert into db_tables_cols
            If projection = 1 Then
              l_temp := BSC_MO_HELPER_PKG.findIndex(BSC_METADATA_OPTIMIZER_PKG.gLov, l_measure.fieldName, l_measure.source);

Line: 1187

                BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS5: Table_name = '||Tabla_originTable(j)||', column_type = A, Column Name= '||
                    l_measure.fieldName||', source='||l_measure.source||', projection='||l_prj_method||', origin = '||l_measure.Origin );

Line: 1190

              l_db_cols_1_delete_table_name(l_db_cols_1_delete_table_name.count+1) := upper(Tabla_originTable(j));

Line: 1191

              l_db_cols_1_delete_field_name(l_db_cols_1_delete_field_name.count+1) := l_measure.fieldName;

Line: 1211

  FORALL i IN 1..l_db_calc_1_delete.count
    DELETE FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = l_db_calc_1_delete(i) AND CALCULATION_TYPE = 1;

Line: 1214

  FORALL i IN 1..l_db_cols_1_delete_table_name.count
    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';

Line: 1221

    INSERT INTO BSC_DB_TABLES_RELS VALUES l_tab_rels(i);

Line: 1237

    INSERT INTO BSC_DB_CALCULATIONS VALUES l_tab_db_calculations(i);

Line: 1241

   bsc_mo_helper_pkg.writeTmp('Insert calculations: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);

Line: 1252

    INSERT INTO BSC_DB_TABLES VALUES l_tab_db_tables(i);

Line: 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);

Line: 1271

   bsc_mo_helper_pkg.writeTmp('Insert Cols: Number of errors is ' || SQL%BULK_EXCEPTIONS.COUNT, FND_LOG.LEVEL_EXCEPTION, true);

Line: 1290

          BSC_UPDATE.Configure_Periodicity_Calc_VB(tabla.name);

Line: 1291

          BSC_MO_HELPER_PKG.CHeckError('BSC_UPDATE.Configure_Periodicity_Calc_VB');

Line: 1301

    BSC_UPDATE.Configure_Profit_Calc_VB;

Line: 1302

    BSC_MO_HELPER_PKG.CheckError('BSC_UPDATE.Configure_Profit_Calc_VB');

Line: 1356

  SELECT DISTINCT parameter3, parameter5 FROM bsc_db_calculations
  WHERE table_name IN (SELECT table_name
                FROM bsc_kpi_data_tables
                WHERE indicator = pIndicator
                AND dim_set_id = pDimSetID
                AND sql_stmt IS NOT NULL)
                AND CALCULATION_TYPE = 4 ;

Line: 1441

                  SELECT COUNT(1) INTO l_count
                  FROM BSC_DB_TABLES_COLS
                  WHERE table_name=l_table_name AND column_name=l_measure.fieldName;

Line: 1467

                BSC_MO_HELPER_PKG.writeTmp('4 INSERT DB_CALCULATIONS:');

Line: 1469

              l_stmt := 'INSERT INTO BSC_DB_CALCULATIONS (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2 '||
                ', PARAMETER3, PARAMETER4, PARAMETER5) '||
                ' SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2, :1, parameter4, :2 FROM BSC_DB_CALCULATIONS '||
                ' WHERE TABLE_NAME = :3 AND CALCULATION_TYPE = 4';

Line: 1474

              INSERT INTO BSC_DB_CALCULATIONS
                  (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2
                  , PARAMETER3, PARAMETER4, PARAMETER5)
              SELECT DISTINCT
                  TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2,
                  l_measure.fieldName, parameter4, ZeroCodeOrigin
                  FROM BSC_DB_CALCULATIONS
                  WHERE TABLE_NAME = UPPER(arrTables(i)) AND CALCULATION_TYPE = 4;

Line: 1484

                BSC_MO_HELPER_PKG.writeTmp('4 UPDATE DB_CALCULATIONS:');

Line: 1487

              l_stmt :=  'UPDATE BSC_DB_CALCULATIONS  SET PARAMETER5 = :1 WHERE '||
                ' TABLE_NAME = :2 AND PARAMETER3 = :3 AND CALCULATION_TYPE = 4 ';

Line: 1491

              UPDATE BSC_DB_CALCULATIONS
              SET PARAMETER5 = ZeroCodeOrigin
              WHERE
              TABLE_NAME = UPPER(arrTables(i))
              AND PARAMETER3 = l_measure.fieldName
              AND CALCULATION_TYPE = 4 ;

Line: 1503

                BSC_MO_HELPER_PKG.writeTmp('5 INSERT DB_CALCULATIONS:');

Line: 1505

              l_stmt:= 'INSERT INTO BSC_DB_CALCULATIONS (TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2) '||
                    ' SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, :2'||
                    ' FROM BSC_DB_CALCULATIONS WHERE TABLE_NAME = :2 AND CALCULATION_TYPE = 5';

Line: 1509

              INSERT INTO BSC_DB_CALCULATIONS(TABLE_NAME, CALCULATION_TYPE, PARAMETER1, PARAMETER2)
              SELECT DISTINCT TABLE_NAME, CALCULATION_TYPE, PARAMETER1, l_measure.fieldName
              FROM BSC_DB_CALCULATIONS
              WHERE TABLE_NAME = UPPER(arrTables(i))
              AND CALCULATION_TYPE = 5;

Line: 1555

                BSC_MO_HELPER_PKG.writeTmp('INSERT DB_TABLES_COLS newcol: table_name='||
                    arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source_column='||l_source);

Line: 1558

              INSERT INTO BSC_DB_TABLES_COLS (TABLE_NAME, COLUMN_TYPE, COLUMN_NAME, SOURCE, PROJECTION_ID, SOURCE_FORMULA)
              VALUES(upper(arrTables(i)), 'A', l_measure.fieldName, l_measure.source, 0, l_source);

Line: 1562

                BSC_MO_HELPER_PKG.writeTmp('UPDATE DB_TABLES_COLS newcol: table_name='||
                    arrTables(i)||', column_type=A, column_name = '||l_measure.fieldName||', source='||l_measure.source||', source_column='||l_source);

Line: 1565

              UPDATE BSC_DB_TABLES_COLS
              SET SOURCE_FORMULA = l_source
              WHERE TABLE_NAME =UPPER(arrTables(i))
              AND COLUMN_NAME = l_measure.fieldName
              AND SOURCE = l_measure.source
              AND COLUMN_TYPE = 'A';

Line: 1575

              UPDATE BSC_DB_TABLES_COLS
			  SET PROJECTION_ID = BSC_METADATA_OPTIMIZER_PKG.gLov(l_temp).PrjMethod
              WHERE TABLE_NAME = UPPER(arrTables(i))
              AND COLUMN_NAME = l_measure.fieldName
              AND SOURCE = l_measure.source
              AND COLUMN_TYPE = 'A';

Line: 1602

          l_stmt := ' UPDATE bsc_kpi_data_tables '||
                  ' SET sql_stmt = SUBSTR(sql_stmt, 1, INSTR(sql_stmt, ''PERIOD_TYPE_ID'') - 1)||''PERIOD_TYPE_ID''||'''|| zeroCodeDataColumns || '''|| '||
                  ' SUBSTR(sql_stmt, INSTR(sql_stmt, '' FROM ''))'||
                  ' WHERE indicator = :1 ' ||
                  ' AND dim_set_id  = :2 '  ||
                  ' AND sql_stmt IS NOT NULL';