APPS.BEN_DM_UTILITY SQL Statements

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

Line: 52

  SELECT thread_cnt_num
    FROM ben_batch_parameter
    WHERE (batch_exe_cd = 'BENDM');

Line: 132

  SELECT status
    FROM ben_dm_phases
    WHERE ((migration_id = p_migration_id)
      AND (phase_name = p_phase));

Line: 203

  SELECT phase_id
    FROM ben_dm_phases
    WHERE ((migration_id = p_migration_id)
      AND (phase_name = p_phase));

Line: 328

  SELECT parameter_value
    FROM pay_action_parameters
    WHERE parameter_name = 'HR_DM_DEBUG_PIPE';

Line: 332

  SELECT parameter_value
    FROM pay_action_parameters
    WHERE parameter_name = 'HR_DM_DEBUG_LOG';

Line: 490

PROCEDURE update_migrations (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is ben_dm_migrations
-- parent of ben_dm_phases
-- child of (none)

l_parent_table_id NUMBER(9);

Line: 502

  SELECT status
    FROM ben_dm_phases
    WHERE ((migration_id = p_id)
      AND (status <> 'C'));

Line: 511

message('ROUT','entry:ben_dm_utility.update_migrations', 5);

Line: 524

  UPDATE ben_dm_migrations
  SET status = p_new_status,
      migration_start_date = NVL(l_start_date, migration_start_date)
  WHERE migration_id = p_id;

Line: 541

    UPDATE ben_dm_migrations
    SET status = p_new_status,
        migration_end_date = sysdate
    WHERE migration_id = p_id;

Line: 550

message('INFO','Update status - update_migrations', 15);

Line: 551

message('SUMM','Update status - update_migrations', 20);

Line: 552

message('ROUT','exit:ben_dm_utility.update_migrations', 25);

Line: 558

  error(SQLCODE,'ben_dm_utility.update_migrations','(none)','R');

Line: 562

END update_migrations;

Line: 583

PROCEDURE update_phase_items (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is hr_dm_phase_items
-- parent of n/a
-- child of hr_dm_phases

l_parent_table_id NUMBER(9);

Line: 595

  SELECT phase_id
    FROM ben_dm_phase_items
    WHERE phase_item_id = p_id;

Line: 604

message('ROUT','entry:ben_dm_utility.update_phase_items', 5);

Line: 619

UPDATE ben_dm_phase_items
  SET status = p_new_status,
      start_time = NVL(l_start_time, start_time),
      end_time = NVL(l_end_time, end_time)
  WHERE phase_item_id = p_id;

Line: 631

  update_phases(p_new_status,l_parent_table_id);

Line: 634

message('INFO','Update status - update_phase_items', 15);

Line: 635

message('SUMM','Update status - update_phase_items', 20);

Line: 636

message('ROUT','exit:ben_dm_utility.update_phase_items', 25);

Line: 642

  error(SQLCODE,'ben_dm_utility.update_phase_items','(none)','R');

Line: 646

END update_phase_items;

Line: 669

PROCEDURE update_phases (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is ben_dm_phases
-- parent of ben_dm_phase_items
-- child of ben_dm_migrations

l_parent_table_id NUMBER(9);

Line: 682

  SELECT status
    FROM ben_dm_phase_items
    WHERE ((phase_id = p_id)
      AND (status <> 'C'));

Line: 689

  SELECT migration_id
    FROM ben_dm_phases
    WHERE phase_id = p_id;

Line: 698

message('ROUT','entry:ben_dm_utility.update_phases', 5);

Line: 712

  UPDATE ben_dm_phases
  SET status = l_new_status,
      start_time = NVL(l_start_time, start_time)
  WHERE phase_id = p_id;

Line: 729

    UPDATE ben_dm_phases
    SET status = l_new_status,
        end_time = sysdate
    WHERE phase_id = p_id;

Line: 746

  update_migrations(l_new_status,l_parent_table_id);

Line: 750

message('INFO','Update status - update_phases', 15);

Line: 751

message('SUMM','Update status - update_phases', 20);

Line: 752

message('ROUT','exit:ben_dm_utility.update_phases', 25);

Line: 758

  error(SQLCODE,'ben_dm_utility.update_phases','(none)','R');

Line: 762

END update_phases;

Line: 865

  SELECT phi.phase_item_id, group_order
    FROM ben_dm_phase_items phi,
         ben_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'DP')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 892

    delete from ben_dm_entity_results
    where group_order = l_group_order;

Line: 899

  update_phase_items('NS',l_phase_item_id);

Line: 939

  SELECT ph.phase_id
    FROM ben_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'I')
      AND (ph.status IN ('S', 'E'));

Line: 958

  DELETE FROM ben_dm_phase_items
    WHERE phase_id = l_phase_id;

Line: 962

    DELETE FROM ben_dm_phases
      WHERE phase_id = l_phase_id;

Line: 969

  update_migrations('S', p_migration_id);

Line: 1007

  SELECT phi.phase_item_id
    FROM ben_dm_phase_items phi,
         ben_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'G')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 1030

  update_phase_items('NS', l_phase_item_id);

Line: 1088

  SELECT phi.phase_item_id
    FROM ben_dm_phase_items phi,
         ben_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'UP')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 1111

  update_phase_items('NS', l_phase_item_id);

Line: 1137

  SELECT table_id
    FROM ben_dm_tables
    WHERE table_name = p_table_name;

Line: 1197

  SELECT column_name, data_type
    FROM sys.all_tab_columns cols1
   WHERE data_type in ('NUMBER','VARCHAR2','DATE')
     AND table_name = p_table_name
     AND column_name not in ('REQUEST_ID','PROGRAM_APPLICATION_ID','PROGRAM_ID','PROGRAM_UPDATE_DATE')
     AND data_length < 2001
     AND not exists (SELECT null
                      FROM BEN_DM_COLUMN_MAPPINGS d1
                     WHERE d1.table_id in (select table_id from ben_dm_tables where table_name = cols1.table_name)
                       AND column_name = cols1.column_name);

Line: 1213

  SELECT column_name
    FROM sys.all_tab_columns
    WHERE data_type = p_data_type
      AND table_name = 'BEN_DM_ENTITY_RESULTS'
      AND column_name not in ('ENTITY_RESULT_ID','MIGRATION_ID','TABLE_NAME','GROUP_ORDER')
      AND column_name not in (SELECT entity_result_column_name
                                FROM ben_dm_column_mappings
                               WHERE table_id = p_table_id)
   ORDER BY column_id asc;

Line: 1243

    INSERT into ben_dm_column_mappings
    (column_mapping_id
    ,table_id
    ,column_name
    ,entity_result_column_name
    ,last_update_date
    ,last_updated_by
    ,last_update_login
    ,created_by
    ,creation_date)
    VALUES
    (ben_dm_column_mappings_s.nextval
    ,l_table_id
    ,column_list.column_name
    ,l_er_column_name
    ,sysdate
    ,1
    ,1
    ,1
    ,sysdate);

Line: 1311

    INSERT into ben_dm_table_order
    (table_order_id
    ,table_id
    ,table_order
    ,last_update_date
    ,last_updated_by
    ,last_update_login
    ,created_by
    ,creation_date)
    VALUES
    (ben_dm_table_order_s.nextval
    ,l_table_id
    ,p_order_no
    ,sysdate
    ,1
    ,1
    ,1
    ,sysdate);

Line: 1392

    insert into ben_dm_hierarchies
               ( hierarchy_id
                ,hierarchy_type
                ,table_id
                ,column_name
                ,parent_table_name
                ,parent_column_name
                ,parent_id_column_name
                ,last_update_date
                ,last_updated_by
                ,last_update_login
                ,created_by
                ,creation_date )
         select  ben_dm_hierarchies_s.nextval
                ,l_hierarchy_type
                ,l_table_id
                ,l_column_name
                ,l_parent_table_name
                ,l_parent_column_name
                ,l_parent_id_column_name
                ,sysdate
                ,1
                ,1
                ,1
                ,sysdate
         from  dual
         where not exists (select 'x'
                           from ben_dm_hierarchies hir
                           where hir.hierarchy_type = l_hierarchy_type
                           and hir.table_id = l_table_id
                           and nvl(hir.column_name,'X') =  nvl(l_column_name,
                                                              'X')
                           and nvl(hir.parent_table_name,-99) = nvl(l_parent_table_name,
                                                                  -99)
                           and nvl(hir.parent_column_name,'X') = nvl(l_parent_column_name,
                                                                    'X')
                           and nvl(hir.parent_id_column_name,'X') = nvl(l_parent_id_column_name,
                                                                       'X')
                          );