APPS.HR_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 (business_group_id = p_business_group_id)
      AND (batch_exe_cd = 'HRDM');

Line: 136

  SELECT chunk_size
    FROM ben_batch_parameter
    WHERE (business_group_id = p_business_group_id)
      AND (batch_exe_cd = 'HRDM');

Line: 211

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

Line: 282

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

Line: 361

UPDATE hr_dm_migrations
  SET migration_process = l_text
  WHERE migration_id = p_migration_id;

Line: 493

  SELECT parameter_value
    FROM pay_action_parameters
    WHERE parameter_name = 'HR_DM_DEBUG_PIPE';

Line: 497

  SELECT parameter_value
    FROM pay_action_parameters
    WHERE parameter_name = 'HR_DM_DEBUG_LOG';

Line: 740

  rollback_delete(p_migration_id);

Line: 781

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'R')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 804

    DELETE FROM hr_dm_migration_ranges
      WHERE phase_item_id = l_phase_item_id;

Line: 807

  update_phase_items('NS', l_phase_item_id);

Line: 846

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'DA')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 869

  update_phase_items('NS', l_phase_item_id);

Line: 907

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'UA')
      AND (phi.phase_id = ph.phase_id);

Line: 929

  update_phase_items('NS', l_phase_item_id);

Line: 973

l_call_delete VARCHAR2(200);

Line: 978

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_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: 987

  SELECT tbl.table_name,
         tbl.short_name,
         mr.starting_process_sequence,
         mr.ending_process_sequence,
         mr.range_id
    FROM hr_dm_migration_ranges mr,
         hr_dm_tables tbl,
         hr_dm_phase_items pi
    WHERE (pi.phase_id = l_range_phase_id)
      AND (pi.phase_item_id = mr.phase_item_id)
      AND (pi.table_name = tbl.table_name)
      AND (mr.status IN ('S', 'E'));

Line: 1001

  SELECT pi.batch_id
    FROM hr_dm_migration_ranges mr,
         hr_dm_tables tbl,
         hr_dm_phase_items pi,
         hr_dm_table_groupings tgp
    WHERE (pi.phase_id = l_phase_id)
      AND (pi.group_id = tgp.group_id)
      AND (tbl.table_name = l_table_name)
      AND (tbl.table_id = tgp.table_id);

Line: 1045

    l_call_delete := 'begin hrdmd_' || l_short_name || '.delete_datapump'
                     || '(p_batch_id => ' || l_batch_id
                     || ', p_start_id => ' || l_starting_process_sequence
                     || ', p_end_id => ' || l_ending_process_sequence
                     || ', p_chunk_size => 10'
                     || '); end;';

Line: 1051

    hr_dm_library.run_sql(l_call_delete);

Line: 1055

    update_migration_ranges('NS', l_range_id);

Line: 1061

  update_phase_items('NS',l_phase_item_id);

Line: 1104

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

Line: 1123

  DELETE FROM hr_dm_phase_items
    WHERE phase_id = l_phase_id;

Line: 1127

    DELETE FROM hr_dm_phases
      WHERE phase_id = l_phase_id;

Line: 1134

  update_migrations('S', p_migration_id);

Line: 1171

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_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: 1194

  update_phase_items('NS', l_phase_item_id);

Line: 1232

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'C')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 1255

  update_phase_items('NS', l_phase_item_id);

Line: 1289

PROCEDURE rollback_delete (p_migration_id IN NUMBER) IS
--

l_phase_item_id NUMBER;

Line: 1297

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_dm_phases ph
    WHERE (ph.migration_id = p_migration_id)
      AND (ph.phase_name = 'D')
      AND (phi.phase_id = ph.phase_id)
      AND (phi.status IN ('S', 'E'));

Line: 1306

  SELECT mr.range_id
    FROM hr_dm_migration_ranges mr,
         hr_dm_phase_items pi
    WHERE (pi.phase_id = l_range_phase_id)
      AND (mr.phase_item_id = pi.phase_item_id)
      AND (mr.status IN ('S', 'E'));

Line: 1317

message('ROUT','entry:hr_dm_utility.rollback_delete', 5);

Line: 1329

  update_phase_items('NS', l_phase_item_id);

Line: 1340

    update_migration_ranges('NS', l_range_id);

Line: 1345

message('INFO','Rollback - delete', 15);

Line: 1346

message('SUMM','Rollback - delete', 20);

Line: 1347

message('ROUT','exit:hr_dm_utility.rollback_delete', 25);

Line: 1353

  error(SQLCODE,'hr_dm_utility.rollback_delete','(none)','R');

Line: 1356

END rollback_delete;

Line: 1379

  SELECT phi.phase_item_id
    FROM hr_dm_phase_items phi,
         hr_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: 1402

  update_phase_items('NS', l_phase_item_id);

Line: 1449

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

l_parent_table_id NUMBER(9);

Line: 1461

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

Line: 1470

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

Line: 1483

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

Line: 1500

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

Line: 1511

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

Line: 1512

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

Line: 1513

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

Line: 1519

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

Line: 1523

END update_migrations;

Line: 1546

PROCEDURE update_migration_ranges (p_new_status IN VARCHAR2,
                                   p_id IN NUMBER) IS
--
-- table is hr_dm_migration_ranges
-- parent of (none)
-- child of hr_dm_phase_items

l_parent_table_id NUMBER(9);

Line: 1564

  SELECT ph.migration_id
    FROM hr_dm_migration_ranges mr,
         hr_dm_phase_items pi,
         hr_dm_phases ph
    WHERE p_id = mr.range_id
      AND mr.phase_item_id = pi.phase_item_id
      AND pi.phase_id = ph.phase_id;

Line: 1574

  SELECT pi_par.phase_item_id
    FROM hr_dm_phase_items pi_par,
         hr_dm_tables tbl,
         hr_dm_migration_ranges mr,
         hr_dm_phase_items pi_rg,
         hr_dm_table_groupings tgp
    WHERE p_id = mr.range_id
      AND mr.phase_item_id = pi_rg.phase_item_id
      AND pi_rg.table_name = tbl.table_name
      AND tbl.table_id = tgp.table_id
      AND tgp.group_id = pi_par.group_id
      AND pi_par.phase_id = l_phase_id;

Line: 1590

  SELECT mr.status
    FROM hr_dm_migration_ranges mr,
         hr_dm_phase_items  rg_pi
    WHERE (mr.phase_item_id = rg_pi.phase_item_id)
      AND (rg_pi.group_id = (SELECT rg_pi.group_id
                               FROM hr_dm_phase_items rg_pi,
                                    hr_dm_migration_ranges mr
                               WHERE p_id = mr.range_id
                                 AND mr.phase_item_id = rg_pi.phase_item_id))
      AND (mr.status <> 'C');

Line: 1603

  SELECT status
    FROM hr_dm_phase_items
    WHERE phase_item_id = l_parent_table_id;

Line: 1612

message('ROUT','entry:hr_dm_utility.update_migration_ranges', 5);

Line: 1639

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

Line: 1652

  update_phase_items('E',l_parent_table_id);

Line: 1666

    update_phase_items(p_new_status,l_parent_table_id);

Line: 1684

    update_phase_items('S',l_parent_table_id);

Line: 1691

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

Line: 1692

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

Line: 1693

message('ROUT','exit:hr_dm_utility.update_migration_ranges', 25);

Line: 1699

  error(SQLCODE,'hr_dm_utility.update_migration_ranges','(none)','R');

Line: 1703

END update_migration_ranges;

Line: 1726

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: 1738

  SELECT phase_id
    FROM hr_dm_phase_items
    WHERE phase_item_id = p_id;

Line: 1747

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

Line: 1762

UPDATE hr_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: 1774

  update_phases(p_new_status,l_parent_table_id);

Line: 1777

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

Line: 1778

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

Line: 1779

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

Line: 1785

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

Line: 1789

END update_phase_items;

Line: 1813

PROCEDURE update_phases (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is hr_dm_phases
-- parent of hr_dm_phase_items
-- child of hr_dm_migrations

l_parent_table_id NUMBER(9);

Line: 1826

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

Line: 1833

  SELECT migration_id
    FROM hr_dm_phases
    WHERE phase_id = p_id;

Line: 1842

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

Line: 1856

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

Line: 1873

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

Line: 1890

  update_migrations(l_new_status,l_parent_table_id);

Line: 1894

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

Line: 1895

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

Line: 1896

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

Line: 1902

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

Line: 1906

END update_phases;