DBA Data[Home] [Help]

APPS.HR_JP_DATA_MIGRATION_PKG SQL Statements

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

Line: 63

    select  hjp1.parameter_value  iv_mode,
        hjp2.parameter_name   iv_name,
        hjp2.parameter_value  iv_id_to,
        hjp3.parameter_value  iv_id_from
    from  hr_jp_parameters hjp1,
        hr_jp_parameters hjp2,
        hr_jp_parameters hjp3
    where hjp1.owner = p_parameter_name
    and   hjp2.owner = hjp1.parameter_value
    and   hjp2.parameter_name = hjp1.parameter_name
    and   hjp3.owner(+) = 'IV_COPY_FROM'
    and   hjp3.parameter_name(+) = hjp2.parameter_name;
Line: 89

      insert into pay_run_results (
        RUN_RESULT_ID,
        ELEMENT_TYPE_ID,
        ASSIGNMENT_ACTION_ID,
        ENTRY_TYPE,
        SOURCE_ID,
        SOURCE_TYPE,
        STATUS)
      select  /*+ INDEX(PRR_FROM PAY_RUN_RESULTS_N1) */
        pay_run_results_s.nextval,
        l_element_type_id_to,
        prr_from.assignment_action_id,
        prr_from.entry_type,
        prr_from.source_id,
        prr_from.source_type,
        prr_from.status
      from  pay_run_results prr_from
      where prr_from.element_type_id = l_element_type_id_from
      and not exists(
          select  /*+ INDEX(PRR_TO PAY_RUN_RESULTS_N50) */
            NULL
          from  pay_run_results prr_to
          where prr_to.assignment_action_id = prr_from.assignment_action_id
          and prr_to.element_type_id = l_element_type_id_to);
Line: 120

          insert into pay_run_result_values (
              INPUT_VALUE_ID,
              RUN_RESULT_ID,
              RESULT_VALUE)
          select  /*+ ORDERED
            INDEX(FROM_ELE_PRR PAY_RUN_RESULTS_N1)
            INDEX(PRRV_FROM PAY_RUN_RESULT_VALUES_PK)
            INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N50)
            USE_NL(prrv_from to_ele_prr) */
            rec_related_iv.iv_id_to,
            to_ele_prr.run_result_id,
            prrv_from.result_value
          from  pay_run_results   from_ele_prr,
            pay_run_result_values prrv_from,
            pay_run_results   to_ele_prr
          where from_ele_prr.element_type_id = l_element_type_id_from
          and prrv_from.run_result_id = from_ele_prr.run_result_id
          and prrv_from.input_value_id = rec_related_iv.iv_id_from
          and to_ele_prr.assignment_action_id = from_ele_prr.assignment_action_id
          and to_ele_prr.element_type_id = l_element_type_id_to
          and not exists(
              select  NULL
              from  pay_run_result_values prrv_to
              where prrv_to.run_result_id = to_ele_prr.run_result_id
              and prrv_to.input_value_id = rec_related_iv.iv_id_to);
Line: 148

          insert into pay_run_result_values (
            INPUT_VALUE_ID,
            RUN_RESULT_ID,
            RESULT_VALUE)
          select  /*+ INDEX(TO_ELE_PRR PAY_RUN_RESULTS_N1) */
            rec_related_iv.iv_id_to,
            to_ele_prr.run_result_id,
            NULL
          from  pay_run_results to_ele_prr
          where to_ele_prr.element_type_id = l_element_type_id_to
          and not exists(
              select  NULL
              from  pay_run_result_values prrv_to
              where prrv_to.run_result_id = to_ele_prr.run_result_id
              and prrv_to.input_value_id = rec_related_iv.iv_id_to);
Line: 185

    select  /*+ ORDERED
                    INDEX(PIV PAY_INPUT_VALUES_F_PK)
                    INDEX(PET PAY_ELEMENT_TYPES_F_PK)
                    INDEX(PRR PAY_RUN_RESULTS_N1) */
            prr.run_result_id   run_result_id
    from  pay_input_values_f    piv,
                pay_element_types_f   pet,
        pay_run_results     prr
    where piv.input_value_id = p_parameter_value
    and   pet.element_type_id = piv.element_type_id
    and   piv.effective_start_date
        between pet.effective_start_date and pet.effective_end_date
    and   prr.element_type_id = pet.element_type_id
    and not exists(
        select  /*+ INDEX(PRRV PAY_RUN_RESULT_VALUES_PK) */
                        NULL
        from  pay_run_result_values prrv
        where prrv.run_result_id=prr.run_result_id
        and   prrv.input_value_id=l_input_value_id_to);
Line: 206

  select  /*+ ORDERED
                INDEX(PIV PAY_INPUT_VALUES_F_PK)
                INDEX(PET PAY_ELEMENT_TYPES_F_PK)
                INDEX(PEL PAY_ELEMENT_LINKS_F_N7) */
        pel.rowid       row_id,
      pel.element_link_id   element_link_id,
      pel.costable_type   costable_type,
      piv.name        input_value_name,
      piv.effective_start_date      effective_start_date,
      piv.effective_end_date        effective_end_date,
      piv.default_value   default_value,
      piv.max_value     max_value,
      piv.min_value     min_value,
      piv.warning_or_error  warning_or_error
  from  pay_input_values_f  piv,
            pay_element_types_f pet,
      pay_element_links_f pel
  where piv.input_value_id = p_parameter_value
  and   pet.element_type_id = piv.element_type_id
  and   piv.effective_start_date
      between pet.effective_start_date and pet.effective_end_date
  and   pel.element_type_id = pet.element_type_id
  and   pel.effective_start_date    <= piv.effective_end_date
  and   pel.effective_end_date      >= piv.effective_start_date;
Line: 235

    select  /*+ INDEX(PEE PAY_ELEMENT_ENTRIES_F_N4) */
        pee.rowid   row_id,
        pee.element_entry_id  element_entry_id
    from  pay_element_entries_f pee
    where pee.element_link_id = p_element_link_id
    and not exists(select /*+ INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
                        NULL
        from  pay_element_entry_values_f peev
        where peev.element_entry_id = pee.element_entry_id
        and   peev.input_value_id = p_input_value_id
        and   peev.effective_start_date = pee.effective_start_date
        and   peev.effective_end_date = pee.effective_end_date);
Line: 264

      insert  into  pay_link_input_values_f
        (LINK_INPUT_VALUE_ID,
         EFFECTIVE_START_DATE,
         EFFECTIVE_END_DATE,
         ELEMENT_LINK_ID,
         INPUT_VALUE_ID,
         COSTED_FLAG,
         DEFAULT_VALUE,
         MAX_VALUE,
         MIN_VALUE,
         WARNING_OR_ERROR,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         LAST_UPDATE_LOGIN,
         CREATED_BY,
         CREATION_DATE)
       select PAY_LINK_INPUT_VALUES_S.nextval,
        greatest(pel.effective_start_date,rec_element_link.effective_start_date),
        least(pel.effective_end_date,rec_element_link.effective_end_date),
        rec_element_link.element_link_id,
        l_input_value_id_to,
        l_costed_flag,
        rec_element_link.default_value,
        rec_element_link.max_value,
        rec_element_link.min_value,
        rec_element_link.warning_or_error,
        pel.last_update_date,
        pel.last_updated_by,
        pel.last_update_login,
        NULL,
        pel.creation_date
      from  pay_element_links_f pel
      where pel.rowid=rec_element_link.row_id
      and not exists(
          select  null
          from  pay_link_input_values_f
          where element_link_id = rec_element_link.element_link_id
          and   input_value_id = l_input_value_id_to);
Line: 305

        insert into pay_element_entry_values_f(
          ELEMENT_ENTRY_VALUE_ID,
          EFFECTIVE_START_DATE,
          EFFECTIVE_END_DATE,
          INPUT_VALUE_ID,
          ELEMENT_ENTRY_ID,
          SCREEN_ENTRY_VALUE)
        select  pay_element_entry_values_s.nextval,
          pee.effective_start_date,
          pee.effective_end_date,
          l_input_value_id_to,
          pee.element_entry_id,
          NULL
        from  pay_element_entries_f pee
        where pee.rowid=rec_element_entry.row_id;
Line: 337

      insert into pay_run_result_values (
          INPUT_VALUE_ID,
          RUN_RESULT_ID,
          RESULT_VALUE)
      values(l_input_value_id_to,
          rec_run_result_id.run_result_id,
          NULL);
Line: 372

  l_delete_mode     VARCHAR2(10);
Line: 378

    select  element_link_id
    from  pay_element_links_f
    where element_type_id=l_element_type_id;
Line: 386

    select  pee.element_entry_id,
        pee.effective_start_date
    from  pay_element_entries_f pee
    where pee.element_link_id=p_element_link_id
    and   p_session_date
Line: 396

    select  pee.element_entry_id
    from  pay_element_entries_f pee
    where pee.element_entry_id=p_element_entry_id
    and p_session_date
      between pee.effective_start_date and pee.effective_end_date;
Line: 414

            l_delete_mode := 'ZAP';
Line: 417

            l_delete_mode := 'DELETE';
Line: 420

          hr_entry_api.delete_element_entry(l_delete_mode,l_target_date,rec_element_entry.element_entry_id);
Line: 451

  select /*+ ORDERED
             INDEX(PA PER_ASSIGNMENTS_F_PK) */
         pbg.business_group_id bg_id,
         pbg.name bg_name,
         pa.assignment_number ass_num
  from   per_all_assignments_f pa,
         per_business_groups_perf pbg
  where  pa.assignment_id = p_assignment_id
  and    p_effective_date
         between pa.effective_start_date and pa.effective_end_date
  and    pbg.business_group_id = pa.business_group_id;
Line: 622

procedure insert_session(
            p_effective_date in date)
is
--
  l_rowid rowid;
Line: 630

  select rowid
  from   fnd_sessions
  where  session_id = userenv('sessionid')
  for update nowait;
Line: 642

      insert into fnd_sessions(
        session_id,
        effective_date)
      values(
        userenv('sessionid'),
        p_effective_date);
Line: 651

      update fnd_sessions
      set    effective_date = p_effective_date
      where rowid = l_rowid;
Line: 659

end insert_session;
Line: 664

procedure delete_session
is
begin
--
  delete
  from  fnd_sessions
  where session_id = userenv('sessionid');
Line: 672

end delete_session;
Line: 765

    g_upd_mode     := 'UPDATE';
Line: 791

  p_valid_delete        in out nocopy varchar2)
is
--
  l_proc varchar2(80) := c_package||'val_mig_smr_assact';
Line: 803

  l_valid_delete varchar2(1) := 'N';
Line: 807

  select /*+ ORDERED
             USE_NL(PLIV, PEE)
             INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
             INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
         pee.effective_start_date,
         pee.updating_action_id
  from   pay_link_input_values_f pliv,
         pay_element_entries_f pee
  where  pliv.input_value_id = c_smr_iv_id
  and    p_session_date
         between pliv.effective_start_date and pliv.effective_end_date
  and    pee.element_link_id = pliv.element_link_id
  and    pee.assignment_id = p_assignment_id
  and    p_session_date
         between pee.effective_start_date and pee.effective_end_date;
Line: 825

  select /*+ ORDERED
             USE_NL(PLIV, PEE)
             INDEX(PLIV PAY_LINK_INPUT_VALUES_F_N2)
             INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51) */
         pee.effective_start_date
  from   pay_link_input_values_f pliv,
         pay_element_entries_f pee
  where  pliv.input_value_id = c_smr_iv_id
  and    p_session_date
         between pliv.effective_start_date and pliv.effective_end_date
  and    pee.element_link_id = pliv.element_link_id
  and    pee.assignment_id = p_assignment_id
  and    pee.effective_start_date > p_session_date;
Line: 846

  if p_valid_delete = 'N' then
  --
  -- skip ee not exist.
  -- skip already updated (manual update)
  --
    open csr_ee_esd;
Line: 857

      l_valid_delete := 'Y';
Line: 867

        l_valid_delete := 'N';
Line: 889

      hr_utility.trace('skip manual upd : l_valid_delete : '||l_valid_delete);
Line: 894

    if l_valid_delete = 'Y' then
    --
      open csr_ft_ee;
Line: 902

        l_valid_delete := 'N';
Line: 924

      hr_utility.trace('skip future entry : l_valid_delete : '||l_valid_delete);
Line: 929

    if l_valid_delete = 'Y'
    and g_upd_mode <> 'OVERRIDE' then
    --
      l_am_eev := pay_jp_balance_pkg.get_entry_value_char(
                     p_input_value_id => c_am_iv_id,
                     p_assignment_id  => p_assignment_id,
                     p_effective_date => p_session_date);
Line: 943

          l_valid_delete := 'N';
Line: 963

        l_valid_delete := 'N';
Line: 985

      hr_utility.trace('skip applied month in future : l_valid_delete : '||l_valid_delete);
Line: 991

    if l_valid_delete = 'Y' then
    --
      l_mr_eev := pay_jp_balance_pkg.get_entry_value_char(
                     p_input_value_id => c_mr_iv_id,
                     p_assignment_id  => p_assignment_id,
                     p_effective_date => p_session_date);
Line: 1000

        l_valid_delete := 'N';
Line: 1023

            l_valid_delete := 'N';
Line: 1035

      hr_utility.trace('skip mr is null or out range : l_valid_delete : '||l_valid_delete);
Line: 1038

    if l_valid_delete = 'Y' then
    --
      p_valid_delete := 'Y';
Line: 1091

  select /*+ ORDERED
             USE_NL(PEL, PEE, PEEV)
             INDEX(PEL PAY_ELEMENT_LINKS_F_N7)
             INDEX(PEE PAY_ELEMENT_ENTRIES_F_N51)
             INDEX(PEEV PAY_ELEMENT_ENTRY_VALUES_F_N50) */
         pee.element_entry_id,
         pee.effective_start_date,
         pee.effective_end_date,
         pee.object_version_number,
         peev.input_value_id,
         peev.screen_entry_value
  from   pay_element_links_f        pel,
         pay_element_entries_f      pee,
         pay_element_entry_values_f peev
  where  pel.element_type_id = c_com_hi_smr_elm_id
  and    pel.business_group_id + 0 = p_business_group_id
  and    p_session_date
         between pel.effective_start_date and pel.effective_end_date
  and    pee.assignment_id = p_assignment_id
  and    pee.element_link_id = pel.element_link_id
  and    p_session_date
         between pee.effective_start_date and pee.effective_end_date
  and    pee.entry_type = 'E'
  and    peev.element_entry_id = pee.element_entry_id
  and    peev.effective_start_date = pee.effective_start_date
  and    peev.effective_end_date = pee.effective_end_date
  for update of peev.element_entry_value_id nowait;
Line: 1215

      pay_element_entry_api.update_element_entry(
        p_validate              => false,
        p_effective_date        => p_session_date,
        p_business_group_id     => null, -- not used
        p_datetrack_update_mode => 'UPDATE',
        p_element_entry_id      => l_csr_entry.element_entry_id,
        p_object_version_number => l_ovn,
        p_input_value_id1       => c_am_iv_id,
        p_input_value_id2       => c_smr_iv_id,
        p_input_value_id3       => c_smr_o_iv_id,
        p_input_value_id4       => c_at_iv_id,
        p_input_value_id5       => c_mr_iv_id,
        p_input_value_id6       => c_mr_o_iv_id,
        p_entry_value1          => to_char(p_session_date,'YYYYMM'),
        p_entry_value2          => l_exp_smr_eev,
        p_entry_value3          => l_exp_smr_o_eev,
        p_entry_value4          => 'O',
        p_entry_value5          => l_exp_mr_eev,
        p_entry_value6          => l_exp_mr_o_eev,
        p_effective_start_date  => l_esd,
        p_effective_end_date    => l_eed,
        p_update_warning        => l_warning);
Line: 1340

  l_qualify_valid_update varchar2(1) := 'N';
Line: 1347

  select /*+ ORDERED
             INDEX(PA PER_ASSIGNMENTS_F_FK1) */
         pbg.business_group_id,
         pbg.name bg_name,
         pa.assignment_id,
         pa.assignment_number
  from   per_business_groups_perf pbg,
         per_all_assignments_f pa
  where  pbg.legislation_code = g_legislation_code
--and pbg.business_group_id = 3101
--and pa.assignment_number
--    between 1000 and 1200
  and    pa.business_group_id = pbg.business_group_id
  and    pa.effective_start_date = (
           select /*+ INDEX(PA2 PER_ASSIGNMENTS_F_PK) */
                  max(pa2.effective_start_date)
           from   per_all_assignments_f pa2
           where  pa2.assignment_id = pa.assignment_id);
Line: 1390

    g_upd_mode := 'UPDATE';
Line: 1402

    g_range_ass_hi_smr_tbl.delete;
Line: 1408

  g_qualify_hi_smr_ass_tbl.delete;
Line: 1415

  insert_session(g_mig_date);
Line: 1497

          p_valid_delete        => g_range_ass_hi_smr_tbl(j).del_done);
Line: 1499

        if l_qualify_valid_update = 'N'
        and g_range_ass_hi_smr_tbl(j).del_done = 'Y' then
          l_qualify_valid_update := 'Y';
Line: 1510

    l_qualify_valid_update := 'Y';
Line: 1514

  if l_qualify_valid_update = 'Y' then
  --
    if g_range_ass_hi_smr_tbl.count > 0 then
    --
      for k in 0..g_range_ass_hi_smr_tbl.count - 1 loop
      --
        if g_range_ass_hi_smr_tbl(k).del_done = 'Y'
        or g_skip_qualify = 'Y' then
        --
          g_qualify_hi_smr_ass_tbl(l_qualify_ass_tbl_cnt).bg_id    := g_range_ass_hi_smr_tbl(k).bg_id;
Line: 1577

  if l_qualify_valid_update = 'Y' then
  --
    if g_qualify_hi_smr_ass_tbl.count > 0 then
    --
      for m in 0..g_qualify_hi_smr_ass_tbl.count - 1 loop
      --
        mig_smr_assact(
          p_business_group_id   => g_qualify_hi_smr_ass_tbl(m).bg_id,
          p_business_group_name => g_qualify_hi_smr_ass_tbl(m).bg_name,
          p_assignment_id       => g_qualify_hi_smr_ass_tbl(m).ass_id,
          p_assignment_number   => g_qualify_hi_smr_ass_tbl(m).ass_num,
          p_session_date        => g_mig_date,
          p_hi_mr               => g_qualify_hi_smr_ass_tbl(m).hi_mr);
Line: 1625

  delete_session;
Line: 1627

  if l_qualify_valid_update = 'Y' then
  --
    commit;