APPS.PAY_RETRO_STATUS_INTERNAL SQL Statements

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

Line: 8

g_update   varchar2(6) := 'UPDATE';

Line: 9

g_delete   varchar2(6) := 'DELETE';

Line: 30

    select pra.retro_assignment_id
    from   pay_retro_assignments  pra
    where  pra.assignment_id = p_assignment_id
    and    pra.retro_assignment_action_id is null
    and    pra.superseding_retro_asg_id is null
    and    approval_status in ('P','A','D');

Line: 65

  select
    pettl.element_name
  from
    pay_element_entries_f      pee
   ,pay_element_links_f        pel
   ,pay_element_types_f_tl     pettl
  where
      pee.element_entry_id = p_element_entry_id
  and pel.element_link_id = pee.element_link_id
  and pee.effective_start_date between pel.effective_start_date
                                   and pel.effective_end_date
  and pettl.element_type_id = pel.element_type_id
  and pettl.language = userenv('lang')
  ;

Line: 99

    select component_name
      from pay_retro_components
     where retro_component_id = p_retro_component_id
    ;

Line: 156

  select sum(distinct decode(owner_type,'S',1, 'U',2, 'M',4, 1)) owntype_sum
  from pay_retro_entries
  where retro_assignment_id = p_retro_assignment_id
  ;

Line: 282

  select
    paf.business_group_id
   ,paf.payroll_id
  from
    per_all_assignments_f      paf
   ,per_periods_of_service     prd
  where
      paf.assignment_id = p_assignment_id
  and paf.payroll_id is not null
  and p_reprocess_date between paf.effective_start_date
                           and paf.effective_end_date
  and prd.period_of_service_id = paf.period_of_service_id;

Line: 348

  ,p_insert_or_update              in     varchar2
  )
is
  l_inv_app_status      boolean:= false;

Line: 362

      if p_insert_or_update = 'I' then
         /*If creation , then only two statuses A and D are allowed*/
         hr_utility.set_location(l_proc, 30);

Line: 478

    select 1
    from pay_retro_entries
    where
        retro_assignment_id = p_retro_assignment_id
    and reprocess_date < p_reprocess_date;

Line: 548

  ,p_delete_sys_retro_asg          in     varchar2 default 'N'
  )
is
  l_proc                varchar2(72) := g_package||'chk_retro_asg_updatable';

Line: 556

  select 1 from pay_retro_entries
  where retro_assignment_id = p_retro_assignment_id
  and nvl(owner_type, g_system) <> g_user;

Line: 588

    elsif p_dml_mode = g_delete then
      --
      -- Check to see if any system generated retro entry exists.
      -- Checking it only when the user doesn't want to delete the system
      -- created retro assignments. Bug#6892796.
      if (nvl(p_delete_sys_retro_asg,'N') <> 'Y') then
        open csr_sys_ent_exists;

Line: 807

  select
    pel.element_type_id
  from
    pay_element_entries_f      pee
   ,pay_element_links_f        pel
   ,pay_element_types_f_tl     pettl
  where
      pee.element_entry_id = p_element_entry_id
  and pee.assignment_id = nvl(p_assignment_id, pee.assignment_id)
  and pee.creator_type in ('A', 'F', 'H', 'Q', 'SP', 'UT', 'M', 'S')
  and pel.element_link_id = pee.element_link_id
  and pee.effective_start_date between pel.effective_start_date
                                   and pel.effective_end_date
  and pettl.element_type_id = pel.element_type_id
  and pettl.language = userenv('lang')
  ;

Line: 972

    select 1
      from pay_retro_components
     where retro_component_id = p_retro_component_id
       and nvl(legislation_code, l_legislation_code) = l_legislation_code
    ;

Line: 1046

  select 1
  from
    pay_retro_component_usages rcu
  where
      rcu.creator_id = p_element_type_id
  and rcu.creator_type = 'ET'
  and rcu.retro_component_id = p_retro_component_id
  ;

Line: 1057

  select
    pettl.element_name
  from
    pay_element_types_f_tl     pettl
  where
      pettl.element_type_id = p_element_type_id
  and pettl.language = userenv('lang')
  ;

Line: 1103

  select *
  from pay_retro_assignments
  where retro_assignment_id = p_retro_assignment_id
  for update nowait;

Line: 1223

  select approval_status
  from pay_retro_assignments
  where retro_assignment_id = p_retro_asg_id;

Line: 1280

      ,p_dml_mode                   => g_update
      );

Line: 1314

      ,p_insert_or_update              => 'I');

Line: 1412

      update pay_retro_assignments
      set
        reprocess_date = nvl(l_new_reprocess_date, reprocess_date)
       ,start_date = nvl(l_new_start_date, start_date)
       ,approval_status = nvl(l_new_approval_status, approval_status)
      where retro_assignment_id = l_retro_assignment_id;

Line: 1442

procedure update_retro_asg
  (p_retro_assignment_id           in     number
  ,p_reprocess_date                in     date     default hr_api.g_date
  ,p_start_date                    in     date     default hr_api.g_date
  ,p_approval_status               in     varchar2 default hr_api.g_varchar2
  ,p_owner_type                    in     varchar2 default g_user
  )
is
  l_proc                varchar2(72) := g_package||'update_retro_asg';

Line: 1461

  select 1 from pay_retro_entries
  where retro_assignment_id = p_retro_assignment_id
  and nvl(owner_type, g_system) <> g_user;

Line: 1470

  savepoint rts_update_retro_asg;

Line: 1525

    ,p_dml_mode                      => g_update
    );

Line: 1539

      ,p_insert_or_update              => 'U');

Line: 1567

  /* Bug 13696751, Allow update status of 'F', only for system generated retro
 * assignments */
  if l_new_rec.approval_status <> 'F'
  then
 --
     hr_utility.set_location(l_proc, 65);

Line: 1573

      update pay_retro_assignments
      set reprocess_date  = l_new_rec.reprocess_date
       ,start_date      = l_new_rec.start_date
       ,approval_status = l_new_rec.approval_status
      where retro_assignment_id = p_retro_assignment_id;

Line: 1586

	 		     update pay_retro_assignments
			      set reprocess_date  = l_new_rec.reprocess_date
			       ,start_date      = l_new_rec.start_date
			       ,approval_status = l_new_rec.approval_status
			      where retro_assignment_id = p_retro_assignment_id;

Line: 1608

    rollback to rts_update_retro_asg;

Line: 1611

end update_retro_asg;

Line: 1652

  select
    min(reprocess_date)
   ,min(effective_date)
  into
    l_reprocess_date
   ,l_start_date
  from pay_retro_entries
  where
      retro_assignment_id = p_retro_assignment_id
  ;

Line: 1682

      update pay_retro_assignments
      set reprocess_date = least(l_reprocess_date, l_old_rec.reprocess_date)
         ,start_date     = least(l_start_date, l_old_rec.start_date)
      where retro_assignment_id = p_retro_assignment_id;

Line: 1697

reference. May need to consider to delete them in future.*/
procedure delete_sys_retro_asg
  (p_retro_assignment_id   in number) is
  l_proc        varchar2(72) := g_package||'delete_sys_retro_asg';

Line: 1704

  select null
  from pay_retro_assignments
  where retro_assignment_id = p_retro_assignment_id
  and   retro_assignment_action_id is null
  for update nowait;

Line: 1716

    update pay_retro_assignments
    set    retro_assignment_action_id = -1,
           approval_status = 'F'
    where  current of csr_sys_retro_asgs;

Line: 1728

procedure delete_retro_asg
  (p_retro_assignment_id           in     number
  ,p_owner_type                    in     varchar2 default g_user
  ,p_delete_sys_retro_asg          in     varchar2 default 'N'
  ,p_replaced_retro_asg_id            out nocopy   number
  )
is
  l_proc                varchar2(72) := g_package||'delete_retro_asg';

Line: 1743

  select retro_assignment_id
  from pay_retro_assignments
  where superseding_retro_asg_id = p_retro_asg_id
  ;

Line: 1753

  savepoint rts_delete_retro_asg;

Line: 1787

    ,p_dml_mode                      => g_delete
    ,p_delete_sys_retro_asg          => p_delete_sys_retro_asg
    );

Line: 1794

  if (nvl(p_delete_sys_retro_asg,'N') = 'Y') then
    hr_utility.set_location('Deleting system created retro assignments : '||l_proc, 60);

Line: 1796

    delete_sys_retro_asg(p_retro_assignment_id => p_retro_assignment_id);

Line: 1797

    hr_utility.set_location('Deleted system created retro assignments : '||l_proc, 70);

Line: 1803

    delete from pay_retro_entries pre
    where retro_assignment_id = p_retro_assignment_id;

Line: 1814

        update pay_retro_assignments
        set superseding_retro_asg_id = null
        where retro_assignment_id = l_replaced_retro_asg_id;

Line: 1823

    delete from pay_retro_assignments
    where retro_assignment_id = p_retro_assignment_id;

Line: 1837

    rollback to rts_delete_retro_asg;

Line: 1840

  end delete_retro_asg;

Line: 1845

procedure delete_retro_asg_cascade
  (p_retro_assignment_id           in     number
  ,p_owner_type                    in     varchar2 default g_user
  )
is
  l_proc                varchar2(72) := g_package||'delete_retro_asg_cascade';

Line: 1859

  savepoint rts_delete_retro_asg_cascade;

Line: 1871

    delete_retro_asg
      (p_retro_assignment_id           => l_retro_assignment_id
      ,p_owner_type                    => p_owner_type
      ,p_replaced_retro_asg_id         => l_replaced_retro_asg_id
      );

Line: 1885

    rollback to rts_delete_retro_asg_cascade;

Line: 1888

end delete_retro_asg_cascade;

Line: 1904

  select *
  from pay_retro_entries
  where retro_assignment_id = p_retro_assignment_id
  and element_entry_id = p_element_entry_id
  for update nowait;

Line: 2139

        update pay_retro_entries
        set reprocess_date = p_reprocess_date
           ,effective_date = p_reprocess_date
        where
            retro_assignment_id = p_retro_assignment_id
        and element_entry_id = p_element_entry_id;

Line: 2169

procedure delete_retro_entry
  (p_retro_assignment_id           in     number
  ,p_element_entry_id              in     number
  ,p_owner_type                    in     varchar2 default g_user
  )
is
  l_proc                varchar2(72) := g_package||'delete_retro_entry';

Line: 2183

  savepoint rts_delete_retro_entry;

Line: 2232

  delete from pay_retro_entries
  where retro_assignment_id = p_retro_assignment_id
  and element_entry_id = p_element_entry_id
  ;

Line: 2243

    rollback to rts_delete_retro_entry;

Line: 2246

end delete_retro_entry;

Line: 2252

procedure update_reprocess_date(
p_assignment_id in number
,p_reprocess_date in date
,p_owner_type in varchar2 default g_user
,p_retro_asg_id out nocopy number) is

  l_retro_assignment_id number;

Line: 2268

  l_proc                varchar2(72) := g_package||'update_reprocess_date';

Line: 2275

  savepoint rts_update_reprocess_date;

Line: 2324

      ,p_dml_mode                   => g_update
      );

Line: 2364

    rollback to rts_update_reprocess_date;