APPS.XLE_HISTORY_PUB SQL Statements

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

Line: 11

    l_select_col_stmt VARCHAR2(4000);

Line: 13

    l_select_val_stmt VARCHAR2(4000);

Line: 22

    l_select_col_stmt := 'select col.column_name, col.data_type from user_synonyms syn, ALL_TAB_COLUMNS col'
        || ' where syn.synonym_name = :tab_name and col.owner =  syn.table_owner and col.table_name = syn.table_name order by COL.column_id';

Line: 24

    l_select_val_stmt := 'select';

Line: 26

    DBMS_SQL.PARSE(l_cursor, l_select_col_stmt, DBMS_SQL.V7);

Line: 46

                                  'LAST_UPDATED_BY',
                                  'LAST_UPDATE_DATE',
                                  'LAST_UPDATE_LOGIN',
                                  'OBJECT_VERSION_NUMBER')) THEN
            IF (p_mode = 'PRE') THEN
                G_VALUE_LIST(l_index).column_name := l_column_name;

Line: 62

            IF (length(l_select_val_stmt) = 6) THEN
                l_select_val_stmt := l_select_val_stmt || ' ';

Line: 65

                l_select_val_stmt := l_select_val_stmt || ', ';

Line: 69

                l_select_val_stmt := l_select_val_stmt || l_column_name;

Line: 71

                l_select_val_stmt := l_select_val_stmt || 'to_char(' || l_column_name || ')';

Line: 73

                l_select_val_stmt := l_select_val_stmt || 'to_char(' || l_column_Name || ', ''DD-MON-YYYY HH24:MI:SS'')';

Line: 75

                l_select_val_stmt := l_select_val_stmt || l_column_name;

Line: 84

    IF (length(l_select_val_stmt) > 6) THEN
        l_select_val_stmt := l_select_val_stmt || ' from ' || p_table_name || '
where ';

Line: 87

        l_select_val_stmt := l_select_val_stmt || p_primary_key_name || ' = :p_id';

Line: 91

        DBMS_SQL.PARSE(l_cursor, l_select_val_stmt, DBMS_SQL.V7);

Line: 150

    select effective_from
    from xle_histories
    where  source_id=G_PRIMARY_KEY_ID
    and source_table=G_TABLE_NAME
    and source_column_name=l_column_name
    and effective_to is null;

Line: 184

                select effective_from into l_start_date
                from  xle_registrations
                where registration_id = G_PRIMARY_KEY_ID
                and rownum < 2;

Line: 195

              select '1' into v_chk
              from xle_histories
              where source_id =G_PRIMARY_KEY_ID
              and source_table=G_TABLE_NAME
              and source_column_name=G_VALUE_LIST(i).column_name
              and rownum <2;

Line: 204

                           XLE_Histories_PKG.Insert_Row(
                  x_history_id => l_hist_id,
                  p_source_table => G_TABLE_NAME,
                  p_source_id => G_PRIMARY_KEY_ID,
                  p_source_column_name => G_VALUE_LIST(i).column_name,
                  p_source_column_value => G_VALUE_LIST(i).old_value,
                  p_effective_from => l_start_date,
                  p_effective_to => p_effective_from,
                  p_comment => 'Creation',
                  p_object_version_number => 1);

Line: 218

                  delete from xle_histories
                  where source_id=G_PRIMARY_KEY_ID
                  and source_table=G_TABLE_NAME
                  and source_column_name=G_VALUE_LIST(i).column_name
                  and effective_from > nvl(p_effective_from,sysdate);

Line: 225

                   /*  XLE_Histories_PKG.Insert_Row(
                         x_history_id => l_history_id,
                         p_source_table => G_TABLE_NAME,
                         p_source_id => G_PRIMARY_KEY_ID,
                         p_source_column_name => G_VALUE_LIST(i).column_name,
                         p_source_column_value => G_VALUE_LIST(i).new_value,
                         p_effective_from => l_start_date,
                         p_comment => 'Creation',
                         p_object_version_number => 1);*/

Line: 241

             update XLE_Histories
             set effective_to=decode(trunc(effective_from),trunc(nvl(p_effective_from,sysdate)),(nvl2(p_effective_from,p_effective_from-(1/86400),sysdate-(1/86400))),nvl(p_effective_from,sysdate)-1)
             where source_id=G_PRIMARY_KEY_ID
             and source_table=G_TABLE_NAME
             and source_column_name=G_VALUE_LIST(i).column_name
             and effective_to is null;

Line: 250

             XLE_Histories_PKG.Insert_Row(
                 x_history_id => l_history_id,
                 p_source_table => G_TABLE_NAME,
                 p_source_id => G_PRIMARY_KEY_ID,
                 p_source_column_name => G_VALUE_LIST(i).column_name,
                 p_source_column_value => G_VALUE_LIST(i).new_value,
                 p_effective_from => p_effective_from,
                 p_comment => p_comment,
                 p_object_version_number => 1);

Line: 267

           update  xle_histories
           SET effective_to =to_date(to_char(to_date(l_eff, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MM-YYYY'), 'DD-MM-YYYY')
           where source_id=G_PRIMARY_KEY_ID
           and source_table=G_TABLE_NAME
           and effective_to is null;

Line: 282

             Delete from xle_histories
             where source_id=G_PRIMARY_KEY_ID
             and source_table=G_TABLE_NAME
             and source_column_name=l_column_name
             and effective_from >  p_effective_from ;

Line: 301

    G_VALUE_LIST.delete;

Line: 364

    select source_column_name
    from xle_history_columns_b;

Line: 380

    G_VALUE_LIST.delete;

Line: 393

  'select ' ||
                                  G_VALUE_LIST(1).column_name ||','
                               || G_VALUE_LIST(2).column_name ||','
                               || G_VALUE_LIST(3).column_name ||','
                               || G_VALUE_LIST(4).column_name ||','
                               || G_VALUE_LIST(5).column_name ||','
                               || G_VALUE_LIST(6).column_name ||','
                               || G_VALUE_LIST(7).column_name ||','
                               || G_VALUE_LIST(8).column_name ||','
                               || G_VALUE_LIST(9).column_name ||','
                               || G_VALUE_LIST(10).column_name ||','
                               || G_VALUE_LIST(11).column_name ||','
                               || G_VALUE_LIST(12).column_name ||','
                               || G_VALUE_LIST(13).column_name ||','
                               || G_VALUE_LIST(14).column_name ||','
                               || G_VALUE_LIST(15).column_name ||','
                               || G_VALUE_LIST(16).column_name ||','
                               || G_VALUE_LIST(17).column_name ||','
                               || G_VALUE_LIST(18).column_name ||','
                               || G_VALUE_LIST(19).column_name ||','
                               || G_VALUE_LIST(20).column_name ||','
                               || G_VALUE_LIST(21).column_name ||','
                               || G_VALUE_LIST(22).column_name ||','
                               || G_VALUE_LIST(23).column_name ||','
                               || G_VALUE_LIST(24).column_name ||','
                               || G_VALUE_LIST(25).column_name ||','
                               || G_VALUE_LIST(26).column_name ||','
                               || G_VALUE_LIST(27).column_name ||','
                               || G_VALUE_LIST(28).column_name ||','
                               || G_VALUE_LIST(29).column_name ||'
                     from XLE_REGISTRATIONS where REGISTRATION_ID='||p_id

                     INTO         G_VALUE_LIST(1).new_value
                                , G_VALUE_LIST(2).new_value
                                , G_VALUE_LIST(3).new_value
                                , G_VALUE_LIST(4).new_value
                                , G_VALUE_LIST(5).new_value
                                , G_VALUE_LIST(6).new_value
                                , G_VALUE_LIST(7).new_value
                                , G_VALUE_LIST(8).new_value
                                , G_VALUE_LIST(9).new_value
                                , G_VALUE_LIST(10).new_value
                                , G_VALUE_LIST(11).new_value
                                , G_VALUE_LIST(12).new_value
                                , G_VALUE_LIST(13).new_value
                                , G_VALUE_LIST(14).new_value
                                , G_VALUE_LIST(15).new_value
                                , G_VALUE_LIST(16).new_value
                                , G_VALUE_LIST(17).new_value
                                , G_VALUE_LIST(18).new_value
                                , G_VALUE_LIST(19).new_value
                                , G_VALUE_LIST(20).new_value
                                , G_VALUE_LIST(21).new_value
                                , G_VALUE_LIST(22).new_value
                                , G_VALUE_LIST(23).new_value
                                , G_VALUE_LIST(24).new_value
                                , G_VALUE_LIST(25).new_value
                                , G_VALUE_LIST(26).new_value
                                , G_VALUE_LIST(27).new_value
                                , G_VALUE_LIST(28).new_value
                                , G_VALUE_LIST(29).new_value;

Line: 466

            XLE_Histories_PKG.Insert_Row(
                x_history_id => l_history_id,
                p_source_table => G_TABLE_NAME,
                p_source_id => G_PRIMARY_KEY_ID,
                p_source_column_name => G_VALUE_LIST(i).column_name,
                p_source_column_value => G_VALUE_LIST(i).new_value,
                p_effective_from => p_effective_from,
                p_comment => p_comment,
                p_object_version_number => 1);