APPS.BSC_UPDATE_VAL SQL Statements

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

Line: 31

        SELECT column_name
        FROM bsc_db_tables_cols
        WHERE table_name = p_table AND column_type = p_column_type;

Line: 67

            h_sql := 'DELETE FROM '||h_base_table||
                     ' WHERE '||h_where_cond;

Line: 81

END Delete_Invalid_Zero_Codes;

Line: 97

    SELECT count(*)
    INTO h_count
    FROM (
        SELECT source_table_name
        FROM bsc_db_tables_rels
        START WITH table_name IN (
            SELECT table_name
            FROM bsc_kpi_data_tables t, bsc_kpi_properties p
            WHERE t.indicator = p.indicator AND
                  t.table_name is not null AND
                  p.property_code = h_db_transform AND
                  p.property_value = 0
        )
        CONNECT BY table_name = PRIOR source_table_name
    )
    WHERE source_table_name = x_table;

Line: 135

    c_dim_cols_sql VARCHAR2(2000) := 'SELECT t.column_name, d.level_view_name'||
                                     ' FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d'||
                                     ' WHERE t.table_name = :1 AND t.column_type = :2 AND'||
                                     ' t.column_name = d.level_pk_col'; */

Line: 141

    SELECT t.column_name, d.level_view_name, d.short_name, d.source
    FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d
    WHERE t.table_name = pTableName
    AND t.column_type = pColType
    AND t.column_name = d.level_pk_col ;

Line: 153

    h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;

Line: 177

    c_mn_rels_sql VARCHAR2(2000) := 'SELECT d1.level_view_name as p1_table, d1.level_pk_col as p1_pk_col,'||
                                    ' d2.level_view_name as p2_table, d2.level_pk_col as p2_pk_col,'||
                                    ' r.relation_col as rel_table'||
                                    ' FROM bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b d1,'||
                                    ' bsc_sys_dim_levels_b d2'||
                                    ' WHERE r.relation_type = :1 AND r.dim_level_id = ('||
                                    ' SELECT min(r2.dim_level_id)'||
                                    ' FROM bsc_sys_dim_level_rels r2'||
                                    ' WHERE r.relation_col = r2.relation_col) AND'||
                                    ' r.dim_level_id = d1.dim_level_id AND'||
                                    ' r.parent_dim_level_id = d2.dim_level_id AND'||
                                    ' d1.level_pk_col in ('||
                                    ' SELECT column_name'||
                                    ' FROM bsc_db_tables_cols'||
                                    ' WHERE table_name = :2 AND column_type = :3) AND'||
                                    ' d2.level_pk_col in ('||
                                    ' SELECT column_name'||
                                    ' FROM bsc_db_tables_cols'||
                                    ' WHERE table_name = :4 AND column_type = :5)'; */

Line: 198

       SELECT d1.level_view_name as p1_table, d1.level_pk_col as p1_pk_col,
              d2.level_view_name as p2_table, d2.level_pk_col as p2_pk_col,
              r.relation_col as rel_table
              FROM bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b d1,
                   bsc_sys_dim_levels_b d2
              WHERE r.relation_type = pRelnType
              AND r.dim_level_id = (SELECT min(r2.dim_level_id)
                                    FROM bsc_sys_dim_level_rels r2
                                    WHERE r.relation_col = r2.relation_col)
              AND r.dim_level_id = d1.dim_level_id
              AND r.parent_dim_level_id = d2.dim_level_id
              AND d1.level_pk_col in ( SELECT column_name
                                     FROM bsc_db_tables_cols
                                     WHERE table_name = pTableName
                                     AND column_type = pColType) AND
                                    d2.level_pk_col in (
                                    SELECT column_name
                                    FROM bsc_db_tables_cols
                                    WHERE table_name = pTableName2 AND column_type = pColType2);

Line: 227

    l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;

Line: 236

    h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

Line: 247

    h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);

Line: 250

    DELETE FROM bsc_db_validation
    WHERE input_table_name = x_input_table;

Line: 266

                BSC_UPDATE_DIM.Get_Dbi_Dim_Data(h_level_short_name, h_dbi_dim_data);

Line: 279

        h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                 'SELECT DISTINCT '||
                 ':1, :2, t.'||h_column_name||' '||
                 'FROM '||x_input_table||' t, '||h_level_table_name||' d '||
                 'WHERE t.'||h_column_name||' = d.user_code (+) AND d.user_code IS NULL';

Line: 284

        h_bind_vars_values.delete;

Line: 292

                     'SELECT DISTINCT '||
                     ':3, :4, t.'||h_column_name||' '||
                     'FROM '||x_input_table||' t, '||h_level_table_name||' d '||
                     'WHERE t.'||h_column_name||' = d.user_code AND d.code = :5';

Line: 301

        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,h_num_bind_vars);

Line: 313

        h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                 ' SELECT DISTINCT :1, :2, '||
                 ' t.'||h_p1_pk_col||'||'', ''||t.'||h_p2_pk_col||' '||
                 ' FROM '||x_input_table||' t '||
                 ' WHERE ('||h_p1_pk_col||', '||h_p2_pk_col||') NOT IN ( '||
                 ' SELECT p1.user_code, p2.user_code '||
                 ' FROM '||h_p1_table||' p1, '||h_p2_table||' p2, '||h_rel_table||' r '||
                 ' WHERE r.'||h_p1_pk_col||' = p1.code AND r.'||h_p2_pk_col||' = p2.code)';

Line: 321

        h_bind_vars_values.delete;

Line: 324

        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,2);

Line: 336

    h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_input_table);

Line: 341

    h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);

Line: 342

    h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);

Line: 343

    h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(h_periodicity);

Line: 347

        IF NOT BSC_UPDATE_UTIL.Get_Period_Cols_Names(h_periodicity, h_period_col_name, h_subperiod_col_name) THEN
            RAISE e_unexpected_error;

Line: 353

            l_bind_vars_values.delete;

Line: 354

            h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                     'SELECT DISTINCT :1, :2, '||
                     't.year||'', ''||t.'||h_period_col_name||' '||
                     'FROM '||x_input_table||' t, bsc_db_calendar d '||
                     'WHERE d.calendar_id (+) = :3'||' AND t.year = d.year (+) '||
                     ' AND (d.year IS NULL OR d.calendar_id IS NULL) '||
                     'UNION '||
                     'SELECT DISTINCT :4, :5, '||
                     't.year||'', ''||t.'||h_period_col_name||' '||
                     'FROM '||x_input_table||' t '||
                     'WHERE t.'||h_period_col_name||' <> :6';

Line: 365

            h_bind_vars_values.delete;

Line: 372

            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,6);

Line: 376

            l_bind_vars_values.delete;

Line: 377

            h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                     'SELECT DISTINCT :1, :2, '||
                     't.year||'', ''||t.'||h_period_col_name||'||'', ''||t.'||h_subperiod_col_name||' '||
                     'FROM '||x_input_table||' t, bsc_db_week_maps d '||
                     'WHERE d.calendar_id (+) = :3'||' AND t.year = d.year (+) '||
                     'AND t.'||h_period_col_name||' = d.month (+) '||
                     'AND t.'||h_subperiod_col_name||' = d.week (+) '||
                     'AND (d.calendar_id IS NULL OR d.year IS NULL OR d.month IS NULL OR d.week IS NULL)';

Line: 385

            h_bind_vars_values.delete;

Line: 389

            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);

Line: 393

            l_bind_vars_values.delete;

Line: 394

            h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                     'SELECT DISTINCT :1, :2, '||
                     't.year||'', ''||t.'||h_period_col_name||'||'', ''||t.'||h_subperiod_col_name||' '||
                     'FROM '||x_input_table||' t, bsc_db_calendar d '||
                     'WHERE d.calendar_id (+) = :3'|| ' AND t.year = d.year (+) '||
                     'AND t.'||h_period_col_name||' = d.month (+) '||
                     'AND t.'||h_subperiod_col_name||' = d.day30 (+) '||
                     'AND (d.calendar_id IS NULL OR d.year IS NULL OR d.month IS NULL OR d.day30 IS NULL)';

Line: 402

            h_bind_vars_values.delete;

Line: 406

            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);

Line: 410

            l_bind_vars_values.delete;

Line: 411

            h_db_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(h_periodicity);

Line: 412

            h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                     'SELECT DISTINCT :1, :2, '||
                     't.year||'', ''||t.'||h_period_col_name||' '||
                     'FROM '||x_input_table||' t, bsc_db_calendar d '||
                     'WHERE d.calendar_id (+) = :3'||' AND t.year = d.year (+) '||
                     'AND t.'||h_period_col_name||' = d.'||h_db_calendar_col_name||' (+) '||
                     'AND (d.calendar_id IS NULL OR d.year IS NULL OR d.'||h_db_calendar_col_name||' IS NULL)';

Line: 419

            h_bind_vars_values.delete;

Line: 423

            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);

Line: 432

            l_bind_vars_values.delete;

Line: 433

            h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                     'SELECT DISTINCT :1, :2, TO_CHAR(time_fk) '||
                     'FROM '||x_input_table||' '||
                     'WHERE TRUNC(time_fk) NOT IN ( '||
                     ' SELECT TRUNC(TO_DATE(time_fk,:3)) '||
                     ' FROM bsc_sys_periods '||
                     ' WHERE periodicity_id = :4 '||
                     ')';

Line: 441

            h_bind_vars_values.delete;

Line: 446

            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,4);

Line: 449

            l_bind_vars_values.delete;

Line: 450

            h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                     'SELECT DISTINCT :1, :2, i.time_fk '||
                     'FROM '||x_input_table||' i, bsc_sys_periods p '||
                     'WHERE p.periodicity_id (+) = :3 AND i.time_fk = p.time_fk (+) '||
                     'AND (p.periodicity_id IS NULL OR p.time_fk IS NULL)';

Line: 455

            h_bind_vars_values.delete;

Line: 459

            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);

Line: 466

    h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
             'SELECT DISTINCT :1, :2, TO_CHAR(TYPE) '||
             'FROM '||x_input_table||' i, bsc_sys_benchmarks_b b '||
             'WHERE i.type = b.data_type (+) and i.type <> :3 and b.data_type is null';

Line: 470

    h_bind_vars_values.delete;

Line: 474

    BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);

Line: 478

        l_bind_vars_values.delete;

Line: 479

        h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
                 'SELECT DISTINCT :1, :2, TO_CHAR(TYPE) '||
                 'FROM '||x_input_table||' '||
                 'WHERE TYPE = :3';

Line: 483

        h_bind_vars_values.delete;

Line: 487

        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);

Line: 493

    SELECT COUNT(*)
    INTO h_num_rows
    FROM BSC_DB_VALIDATION
    WHERE ROWNUM < 2 AND INPUT_TABLE_NAME = x_input_table;

Line: 508

      BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_VALID_FAILED'),
                      x_source => 'BSC_UPDATE_VAL.Validate_Codes');

Line: 514

                      x_source => 'BSC_UPDATE_VAL.Validate_Codes');