APPS.HR_DM_INIT SQL Statements

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

Line: 23

  SELECT UPPER(parameter_value)
    FROM pay_action_parameters
    WHERE parameter_name = 'HR_DM_CUSTOM_AOL_CODE';

Line: 176

CURSOR csr_select_pi IS
  SELECT DISTINCT tbl.loader_name, tbg.group_id, tbl.table_name
    FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
         all_objects obj, hr_dm_application_groups app,
         hr_dm_migrations mig
    WHERE (tbl.table_id = tbg.table_id)
      AND (app.group_id = tbg.group_id)
      AND (mig.application_id = app.application_id)
      AND (mig.migration_id = r_migration_data.migration_id)
      AND (obj.object_name = tbl.table_name)
      AND (obj.object_type = 'TABLE')
      AND obj.owner in
          (l_apps_owner,
           l_fnd_owner,
           l_ff_owner,
           l_ben_owner,
           l_pay_owner,
           l_per_owner)
      AND (
           (obj.last_ddl_time >= NVL(tbl.last_generated_date,
                                     obj.last_ddl_time))
        OR (l_generator_version <> NVL(tbl.generator_version,
                                       'none'))
        OR (tbl.last_update_date > NVL(tbl.last_generated_date,
                                    tbl.last_update_date))
          )
  UNION
  SELECT DISTINCT tbl.loader_name, tbg.group_id, tbl.table_name
    FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
         all_objects obj, hr_dm_application_groups app,
         hr_dm_migrations mig
    WHERE (tbl.table_id = tbg.table_id)
      AND (app.group_id = tbg.group_id)
      AND (mig.application_id = app.application_id)
      AND (mig.migration_id = r_migration_data.migration_id)
      AND (obj.object_name = tbl.upload_table_name)
      AND (obj.object_type = 'TABLE')
      AND obj.owner in
          (l_apps_owner,
           l_fnd_owner,
           l_ff_owner,
           l_ben_owner,
           l_pay_owner,
           l_per_owner)
      AND (
           (obj.last_ddl_time >= NVL(tbl.last_generated_date,
                                     obj.last_ddl_time))
        OR (l_generator_version <> NVL(tbl.generator_version,
                                       'none'))
        OR (tbl.last_update_date > NVL(tbl.last_generated_date,
                                    tbl.last_update_date))
          )
  UNION
  SELECT DISTINCT tbl.loader_name, tbg.group_id, tbl.table_name
    FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
         hr_dm_application_groups app,
         hr_dm_migrations mig
    WHERE (tbl.table_id = tbg.table_id)
      AND (app.group_id = tbg.group_id)
      AND (mig.application_id = app.application_id)
      AND (mig.migration_id = r_migration_data.migration_id)
      AND (tbl.table_name like 'HR_DMVP%')
      AND (tbl.upload_table_name IS NULL);

Line: 258

UPDATE hr_dm_tables
  SET last_generated_date = NULL
  WHERE table_name = 'FF_FORMULAS_F'
    OR table_name LIKE 'HR_DMV%';

Line: 265

OPEN csr_select_pi;

Line: 267

  FETCH csr_select_pi INTO l_loader_name, l_group_id, l_table_name;

Line: 268

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 270

  INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                                 PHASE_ID,
                                 LOADER_NAME,
                                 BATCH_ID,
                                 GROUP_ID,
                                 TABLE_NAME,
                                 STATUS,
                                 START_TIME,
                                 END_TIME,
                                 CREATED_BY,
                                 CREATION_DATE,
                                 LAST_UPDATED_BY,
                                 LAST_UPDATE_DATE,
                                 LAST_UPDATE_LOGIN)
    SELECT hr_dm_phase_items_s.nextval,
           l_phase_id,
           l_loader_name,
           NULL,
           l_group_id,
           l_table_name,
           'NS',
           NULL,
           NULL,
           1,
           SYSDATE,
           1,
           SYSDATE,
           NULL
      FROM dual
      WHERE NOT EXISTS
        (SELECT NULL FROM hr_dm_phase_items
          WHERE ((phase_id = l_phase_id)
            AND (table_name = l_table_name)));

Line: 309

CLOSE csr_select_pi;

Line: 354

CURSOR csr_select_pi IS
  SELECT tbg.group_id, tbl.table_name
    FROM hr_dm_tables tbl, hr_dm_table_groupings tbg,
         hr_dm_groups grp, hr_dm_application_groups apg
    WHERE ( (tbl.table_id = tbg.table_id)
     AND (tbg.group_id = grp.group_id)
     AND (grp.group_type = 'D')
     AND (grp.group_id = apg.group_id)
     AND (apg.application_id = r_migration_data.application_id)
     AND (apg.migration_type = r_migration_data.migration_type) );

Line: 375

OPEN csr_select_pi;

Line: 377

  FETCH csr_select_pi INTO l_group_id, l_table_name;

Line: 378

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 380

  INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                                 PHASE_ID,
                                 LOADER_NAME,
                                 BATCH_ID,
                                 GROUP_ID,
                                 TABLE_NAME,
                                 STATUS,
                                 START_TIME,
                                 END_TIME,
                                 CREATED_BY,
                                 CREATION_DATE,
                                 LAST_UPDATED_BY,
                                 LAST_UPDATE_DATE,
                                 LAST_UPDATE_LOGIN)
    SELECT hr_dm_phase_items_s.nextval,
           l_phase_id,
           NULL,
           NULL,
           l_group_id,
           l_table_name,
           'NS',
           NULL,
           NULL,
           1,
           SYSDATE,
           1,
           SYSDATE,
           NULL
      FROM dual
      WHERE NOT EXISTS
        (SELECT NULL FROM hr_dm_phase_items
          WHERE ((phase_id = l_phase_id)
            AND (table_name = l_table_name)));

Line: 419

CLOSE csr_select_pi;

Line: 542

SELECT HR_DM_PHASE_ITEMS_S.nextval
  INTO l_phase_item_id_da
  FROM dual;

Line: 545

SELECT HR_DM_PHASE_ITEMS_S.nextval
  INTO l_phase_item_id_ua
  FROM dual;

Line: 549

INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                               PHASE_ID,
                               LOADER_NAME,
                               LOADER_PARAMS_ID,
                               BATCH_ID,
                               GROUP_ID,
                               TABLE_NAME,
                               STATUS,
                               START_TIME,
                               END_TIME)
  SELECT l_phase_item_id_da,
         p_phase_id_da,
         p_param_rec.loader_name,
         p_param_rec.loader_params_id,
         NULL,
         p_param_rec.group_id,
         NULL,
         'NS',
         NULL,
         NULL
    FROM dual;

Line: 571

INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                               PHASE_ID,
                               LOADER_NAME,
                               LOADER_PARAMS_ID,
                               BATCH_ID,
                               GROUP_ID,
                               TABLE_NAME,
                               STATUS,
                               START_TIME,
                               END_TIME)
  SELECT l_phase_item_id_ua,
         p_phase_id_ua,
         p_param_rec.loader_name,
         p_param_rec.loader_params_id,
         NULL,
         p_param_rec.group_id,
         NULL,
         'NS',
         NULL,
         NULL
    FROM dual;

Line: 598

INSERT INTO hr_dm_loader_phase_items (
      loader_phase_item_id,
      da_phase_item_id,
      ua_phase_item_id,
      loader_name,
      loader_conc_program,
      loader_config_file,
      loader_application,
      application_id,
      filename,
      parameter_1,
      parameter_2,
      parameter_3,
      parameter_4,
      parameter_5,
      parameter_6,
      parameter_7,
      parameter_8,
      parameter_9,
      parameter_10,
      custom_code_specified)
  SELECT
      hr_dm_loader_phase_items_s.nextval,
      l_phase_item_id_da,
      l_phase_item_id_ua,
      p_param_rec.loader_name,
      p_param_rec.loader_conc_program,
      p_param_rec.loader_config_file,
      p_param_rec.loader_application,
      p_param_rec.application_id,
      l_filename,
      p_param_rec.parameter1,
      p_param_rec.parameter2,
      p_param_rec.parameter3,
      p_param_rec.parameter4,
      p_param_rec.parameter5,
      p_param_rec.parameter6,
      p_param_rec.parameter7,
      p_param_rec.parameter8,
      p_param_rec.parameter9,
      p_param_rec.parameter10,
      p_custom_code_specified
      FROM dual;

Line: 772

  SELECT tbl.loader_conc_program,
         tbl.loader_config_file,
         tbl.loader_application,
         grp.group_id
    FROM hr_dm_groups grp,
         hr_dm_application_groups apg,
         hr_dm_table_groupings tbg,
         hr_dm_tables tbl
    WHERE tbl.table_id = tbg.table_id
      AND tbg.group_id = grp.group_id
      AND grp.group_type = 'A'
      AND grp.group_id = apg.group_id
      AND apg.application_id = r_migration_data.application_id
      AND apg.migration_type = r_migration_data.migration_type
      AND tbl.loader_name = l_param_rec.loader_name;

Line: 789

  SELECT application_short_name
    FROM fnd_application
    WHERE application_id = r_flexfield_data.application_id;

Line: 850

    l_param_rec.loader_name := 'Desc flexfields (selective)';

Line: 851

    l_param_rec.parameter9 := ':selective';

Line: 932

CURSOR csr_select_pi IS
  SELECT tbl.loader_name,
         tbl.loader_conc_program,
         tbl.loader_config_file,
         tbl.loader_application,
         lp.loader_params_id,
         lp.application_id,
         lp.parameter1,
         lp.parameter2,
         lp.parameter3,
         lp.parameter4,
         lp.parameter5,
         lp.parameter6,
         lp.parameter7,
         lp.parameter8,
         lp.parameter9,
         lp.parameter10,
         grp.group_id
    FROM hr_dm_groups grp,
         hr_dm_application_groups apg,
         hr_dm_table_groupings tbg,
         hr_dm_tables tbl,
         hr_dm_loader_params lp
    WHERE (lp.table_id = tbl.table_id)
      AND (lp.application_id = r_migration_data.application_id)
      AND (tbl.table_id = tbg.table_id)
      AND (tbg.group_id = grp.group_id)
      AND (grp.group_type = 'A')
      AND (grp.group_id = apg.group_id)
      AND (apg.application_id = lp.application_id)
      AND (apg.migration_type = r_migration_data.migration_type);

Line: 965

  SELECT 'D',
         a.application_id,
         NULL,
         NULL,
         fc.descriptive_flexfield_name,
         fc.descriptive_flex_context_code
    FROM fnd_descr_flex_contexts_vl fc,
         fnd_descriptive_flexs_vl f,
         fnd_application a
    WHERE fc.descriptive_flexfield_name =
                            f.descriptive_flexfield_name
      AND f.application_id = a.application_id
      AND a.application_short_name IN ('PER','PAY','BEN','FND')
      AND fc.descriptive_flexfield_name NOT LIKE '$SRS$%';

Line: 981

  SELECT 'D',
         a.application_id,
         NULL,
         NULL,
         fc.descriptive_flexfield_name,
         fc.descriptive_flex_context_code
    FROM fnd_descr_flex_contexts_vl fc,
         fnd_descriptive_flexs_vl f,
         fnd_application a,
         hr_dm_migrations mig
    WHERE fc.descriptive_flexfield_name =
                            f.descriptive_flexfield_name
      AND f.application_id = a.application_id
      AND a.application_short_name IN ('PER','PAY','BEN','FND')
      AND fc.descriptive_flexfield_name NOT LIKE '$SRS$%'
      AND fc.descriptive_flexfield_name = mig.selective_migration_criteria
      AND mig.migration_id = r_migration_data.migration_id;

Line: 1000

  SELECT 'K',
         a.application_id,
         f.id_flex_code,
         f.id_flex_structure_code,
         NULL,
         NULL
    FROM fnd_id_flex_structures_vl f,
         fnd_application a,
         fnd_id_flexs fc
    WHERE f.id_flex_code = fc.id_flex_code
      AND fc.application_id = f.application_id
      AND f.application_id = a.application_id
      AND a.application_short_name IN ('PER','PAY','BEN','FND');

Line: 1030

OPEN csr_select_pi;

Line: 1032

  FETCH csr_select_pi INTO l_param_rec;

Line: 1034

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 1054

                          'Desc flexfields (selective)',
                          'Desc flexfields (lookups)',
                          'Key flexfields')
        )
      ) THEN

      -- process this data later
      -- this is for data where confirmation is required
      NULL;

Line: 1079

CLOSE csr_select_pi;

Line: 1089

    hr_dm_utility.message('INFO','Seeding selective dff migration' , 10);

Line: 1207

CURSOR csr_select_pi IS
  SELECT grp.group_id, grp.description
    FROM hr_dm_groups grp, hr_dm_application_groups apg
    WHERE ((grp.group_type = 'D')
      AND (grp.group_id = apg.group_id)
      AND (apg.application_id = r_migration_data.application_id)
      AND (apg.migration_type = r_migration_data.migration_type) )
    ORDER BY apg.group_order;

Line: 1216

CURSOR csr_select_bg_name IS
  SELECT pbg.name
    FROM per_business_groups pbg, hr_dm_migrations mig
    WHERE ((mig.migration_id = r_migration_data.migration_id)
      AND (mig.business_group_id = pbg.business_group_id));

Line: 1234

hr_dm_copy.delete_datapump_tables;

Line: 1239

OPEN csr_select_pi;

Line: 1241

  FETCH csr_select_pi INTO l_group_id, l_group_text;

Line: 1242

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 1245

  OPEN csr_select_bg_name;

Line: 1247

    FETCH csr_select_bg_name INTO l_business_group_name;

Line: 1248

    EXIT WHEN csr_select_bg_name%NOTFOUND;

Line: 1250

  CLOSE csr_select_bg_name;

Line: 1262

  INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                                 PHASE_ID,
                                 LOADER_NAME,
                                 BATCH_ID,
                                 GROUP_ID,
                                 TABLE_NAME,
                                 STATUS,
                                 START_TIME,
                                 END_TIME,
                                 CREATED_BY,
                                 CREATION_DATE,
                                 LAST_UPDATED_BY,
                                 LAST_UPDATE_DATE,
                                 LAST_UPDATE_LOGIN)
    SELECT hr_dm_phase_items_s.nextval,
           l_phase_id,
           NULL,
           l_batch_id,
           l_group_id,
           NULL,
           'NS',
           NULL,
           NULL,
           1,
           SYSDATE,
           1,
           SYSDATE,
           NULL
      FROM dual
      WHERE NOT EXISTS
        (SELECT NULL FROM hr_dm_phase_items
          WHERE ((phase_id = l_phase_id)
            AND (batch_id = l_batch_id)
            AND (group_id = l_group_id)));

Line: 1303

CLOSE csr_select_pi;

Line: 1410

CURSOR csr_select_pi IS
  SELECT grp.group_id, grp.description
    FROM hr_dm_groups grp, hr_dm_application_groups apg
    WHERE ((grp.group_type = 'D')
     AND (grp.group_id = apg.group_id)
     AND (apg.application_id = r_migration_data.application_id)
     AND (apg.migration_type = r_migration_data.migration_type) )
    ORDER BY apg.group_order;

Line: 1419

CURSOR csr_select_batch_id IS
 SELECT bh.batch_id, bh.batch_name
    FROM hr_pump_batch_headers bh
    WHERE bh.reference = 'HR Data Migrator'
    AND EXISTS (SELECT NULL
                FROM hr_pump_batch_lines bl
                WHERE bh.batch_id = bl.batch_id
                AND ROWNUM < 2);

Line: 1439

OPEN csr_select_pi;

Line: 1441

  FETCH csr_select_pi INTO l_group_id, l_group_text;

Line: 1442

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 1448

  OPEN csr_select_batch_id;

Line: 1450

    FETCH csr_select_batch_id INTO l_batch_id, l_batch_name;

Line: 1451

    EXIT WHEN (csr_select_batch_id%NOTFOUND);

Line: 1459

    EXIT WHEN (csr_select_batch_id%NOTFOUND);

Line: 1462

      INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                                     PHASE_ID,
                                     LOADER_NAME,
                                     BATCH_ID,
                                     GROUP_ID,
                                     TABLE_NAME,
                                     STATUS,
                                     START_TIME,
                                     END_TIME,
                                     CREATED_BY,
                                     CREATION_DATE,
                                     LAST_UPDATED_BY,
                                     LAST_UPDATE_DATE,
                                     LAST_UPDATE_LOGIN)
        SELECT hr_dm_phase_items_s.nextval,
               l_phase_id,
               NULL,
               l_batch_id,
               l_group_id,
               NULL,
               'NS',
               NULL,
               NULL,
               1,
               SYSDATE,
               1,
               SYSDATE,
               NULL
          FROM dual
          WHERE NOT EXISTS
            (SELECT NULL FROM hr_dm_phase_items
              WHERE ((phase_id = l_phase_id)
                AND (batch_id = l_batch_id)
                AND (group_id = l_group_id)));

Line: 1504

  CLOSE csr_select_batch_id;

Line: 1509

CLOSE csr_select_pi;

Line: 1518

  DELETE FROM hr_dm_resolve_pks
    WHERE source_database_instance = r_migration_data.source_database_instance;

Line: 1526

  DELETE FROM hr_dm_resolve_pks
    WHERE source_database_instance = r_migration_data.source_database_instance
    AND TABLE_NAME = 'HR_NAVIGATION_UNITS';

Line: 1579

CURSOR csr_select_pi IS
  SELECT tbl.loader_name,
         lp.loader_params_id,
         grp.group_id
    FROM hr_dm_groups grp,
         hr_dm_application_groups apg,
         hr_dm_table_groupings tbg,
         hr_dm_tables tbl,
         hr_dm_loader_params lp
    WHERE (lp.table_id = tbl.table_id)
      AND (lp.application_id = r_migration_data.application_id)
      AND (tbl.table_id = tbg.table_id)
      AND (tbg.group_id = grp.group_id)
      AND (grp.group_type = 'A')
      AND (grp.group_id = apg.group_id)
      AND (apg.application_id = r_migration_data.application_id)
      AND (apg.migration_type = r_migration_data.migration_type)
    ORDER BY tbl.table_id;

Line: 1657

CURSOR csr_select_pi IS
  SELECT apg.group_id, grp.description
    FROM hr_dm_application_groups apg,
         hr_dm_groups grp
    WHERE ((apg.application_id = r_migration_data.application_id)
      AND (apg.migration_type = r_migration_data.migration_type)
      AND (apg.group_id = grp.group_id))
    ORDER BY apg.group_order DESC;

Line: 1677

OPEN csr_select_pi;

Line: 1679

  FETCH csr_select_pi INTO l_group_id, l_group_text;

Line: 1680

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 1682

  INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                                 PHASE_ID,
                                 LOADER_NAME,
                                 BATCH_ID,
                                 GROUP_ID,
                                 TABLE_NAME,
                                 STATUS,
                                 START_TIME,
                                 END_TIME,
                                 CREATED_BY,
                                 CREATION_DATE,
                                 LAST_UPDATED_BY,
                                 LAST_UPDATE_DATE,
                                 LAST_UPDATE_LOGIN)
    SELECT hr_dm_phase_items_s.nextval,
           l_phase_id,
           NULL,
           NULL,
           l_group_id,
           NULL,
           'NS',
           NULL,
           NULL,
           1,
           SYSDATE,
           1,
           SYSDATE,
           NULL
      FROM dual
      WHERE NOT EXISTS
        (SELECT NULL FROM hr_dm_phase_items
          WHERE ((phase_id = l_phase_id)
            AND (group_id = l_group_id)));

Line: 1721

CLOSE csr_select_pi;

Line: 1768

CURSOR csr_select_pi IS
  SELECT tbl.loader_name,
         lp.loader_params_id,
         grp.group_id
    FROM hr_dm_groups grp,
         hr_dm_application_groups apg,
         hr_dm_table_groupings tbg,
         hr_dm_tables tbl,
         hr_dm_loader_params lp
    WHERE (lp.table_id = tbl.table_id)
      AND (lp.application_id = r_migration_data.application_id)
      AND (tbl.table_id = tbg.table_id)
      AND (tbg.group_id = grp.group_id)
      AND (grp.group_type = 'C')
      AND (grp.group_id = apg.group_id)
      AND (apg.application_id = r_migration_data.application_id)
      AND (apg.migration_type = r_migration_data.migration_type)
    ORDER BY tbl.table_id;

Line: 1798

OPEN csr_select_pi;

Line: 1800

  FETCH csr_select_pi INTO l_loader_name, l_loader_params_id, l_group_id;

Line: 1801

  EXIT WHEN csr_select_pi%NOTFOUND;

Line: 1803

  INSERT INTO hr_dm_phase_items (PHASE_ITEM_ID,
                                 PHASE_ID,
                                 LOADER_NAME,
                                 LOADER_PARAMS_ID,
                                 BATCH_ID,
                                 GROUP_ID,
                                 TABLE_NAME,
                                 STATUS,
                                 START_TIME,
                                 END_TIME,
                                 CREATED_BY,
                                 CREATION_DATE,
                                 LAST_UPDATED_BY,
                                 LAST_UPDATE_DATE,
                                 LAST_UPDATE_LOGIN)
    SELECT hr_dm_phase_items_s.nextval,
           l_phase_id,
           l_loader_name,
           l_loader_params_id,
           NULL,
           l_group_id,
           NULL,
           'NS',
           NULL,
           NULL,
           1,
           SYSDATE,
           1,
           SYSDATE,
           NULL
      FROM dual
      WHERE NOT EXISTS
        (SELECT NULL FROM hr_dm_phase_items
          WHERE ((phase_id = l_phase_id)
            AND (loader_name = l_loader_name)));

Line: 1844

CLOSE csr_select_pi;

Line: 1964

  SELECT phase_name, previous_phase, next_phase,
         database_location
    FROM hr_dm_phase_rules
    WHERE ((migration_type = r_migration_data.migration_type)
      AND (previous_phase = l_search_phase));

Line: 2041

INSERT INTO hr_dm_phases (PHASE_ID,
                          MIGRATION_ID,
                          PHASE_NAME,
                          STATUS,
                          START_TIME,
                          END_TIME,
                          CREATED_BY,
                          CREATION_DATE,
                          LAST_UPDATED_BY,
                          LAST_UPDATE_DATE,
                          LAST_UPDATE_LOGIN)
  SELECT hr_dm_phases_s.nextval,
         r_migration_data.migration_id,
         p_phase_name,
         'NS',
         NULL,
         NULL,
         1,
         SYSDATE,
         1,
         SYSDATE,
         NULL
    FROM dual
    WHERE NOT EXISTS
      (SELECT NULL FROM hr_dm_phases
        WHERE ((migration_id = r_migration_data.migration_id)
          AND (phase_name = p_phase_name)));

Line: 2112

  SELECT phase_name, database_location
    FROM hr_dm_phase_rules
    WHERE ((migration_type = r_migration_data.migration_type)
      AND (INSTR(database_location,
                 r_migration_data.database_location) >0));

Line: 2204

  hr_dm_utility.update_phases(p_new_status => 'S',
                              p_id => hr_dm_utility.get_phase_id('I',
                              r_migration_data.migration_id));

Line: 2214

  DELETE hr_dm_exp_imps;

Line: 2219

  hr_dm_utility.update_phases(p_new_status => 'C',
                              p_id => hr_dm_utility.get_phase_id('I',
                              r_migration_data.migration_id));