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

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

    select component_name
      from pay_retro_components
     where retro_component_id = p_retro_component_id
    ;

Line: 155

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

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

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

Line: 593

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

Line: 623

    elsif p_dml_mode = g_delete then
      --
      -- Check to see if any system generated retro entry exists.
      --
      open csr_sys_ent_exists;

Line: 839

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

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

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

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

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

Line: 1255

  select approval_status
  from pay_retro_assignments
  where retro_assignment_id = p_retro_asg_id;

Line: 1312

      ,p_dml_mode                   => g_update
      );

Line: 1446

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

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

  savepoint rts_update_retro_asg;

Line: 1549

    ,p_dml_mode                      => g_update
    );

Line: 1593

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

    rollback to rts_update_retro_asg;

Line: 1608

end update_retro_asg;

Line: 1649

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

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

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

Line: 1706

  select retro_assignment_id
  from pay_retro_assignments
  where superseding_retro_asg_id = p_retro_asg_id
  ;

Line: 1716

  savepoint rts_delete_retro_asg;

Line: 1750

    ,p_dml_mode                      => g_delete
    );

Line: 1757

  delete from pay_retro_entries pre
  where retro_assignment_id = p_retro_assignment_id;

Line: 1768

    update pay_retro_assignments
    set superseding_retro_asg_id = null
    where retro_assignment_id = l_replaced_retro_asg_id;

Line: 1778

  delete from pay_retro_assignments
  where retro_assignment_id = p_retro_assignment_id;

Line: 1792

    rollback to rts_delete_retro_asg;

Line: 1795

end delete_retro_asg;

Line: 1800

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

  savepoint rts_delete_retro_asg_cascade;

Line: 1826

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

    rollback to rts_delete_retro_asg_cascade;

Line: 1843

end delete_retro_asg_cascade;

Line: 1859

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

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

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

  savepoint rts_delete_retro_entry;

Line: 2187

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

Line: 2198

    rollback to rts_delete_retro_entry;

Line: 2201

end delete_retro_entry;

Line: 2207

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

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

Line: 2230

  savepoint rts_update_reprocess_date;

Line: 2279

      ,p_dml_mode                   => g_update
      );

Line: 2319

    rollback to rts_update_reprocess_date;