eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_BATCH_LINK_PROCESS_PKG SQL Statements

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

Line: 94


  select sysdate into l_current_time
  from dual;

Line: 154


  select *
  from pay_batch_element_links
  where batch_element_link_id = p_batch_element_link_id;

Line: 242


    update pay_element_links_f
    set comment_id                 = l_bat_rec.comment_id
    where
        element_link_id      = l_link_rec.element_link_id
    and effective_start_date = l_link_rec.effective_start_date
    and effective_end_date   = l_link_rec.effective_end_date
    ;

Line: 279


  update pay_batch_element_links
  set element_link_id = l_link_rec.element_link_id
  where batch_element_link_id = p_batch_element_link_id;

Line: 316


  select
    payroll_action_id
   ,business_group_id
   ,effective_date
   ,legislative_parameters
   ,pay_core_utils.get_parameter
      ('GEN_LINK_TYPE', legislative_parameters) gen_link_type
   ,pay_core_utils.get_parameter
      ('ELE_LINK_ID', legislative_parameters) ele_link_id
   ,pay_core_utils.get_parameter
      ('BAT_LINK_ID', legislative_parameters) bat_link_id
  from pay_payroll_actions ppa
  where payroll_action_id = p_payroll_action_id;

Line: 413


  select
    bat.batch_element_link_id
  from
    pay_batch_element_links bat
  where
      bat.business_group_id = p_bgid
  and (bat.batch_element_link_id = p_batlink_id
       or (    p_gen_link_type = 'A'
           and p_batlink_id is null
           and nvl(pay_batch_object_status_pkg.get_status
                     ('BEL',bat.batch_element_link_id)
                  ,'U') <> 'C')
      )
  and bat.element_link_id is null
  --
  -- Ensure element type exists.
  --
  and exists
        (select 1 from pay_element_types_f pet
         where pet.element_type_id = bat.element_type_id)
  order by
    bat.element_type_id
   ,bat.effective_date
  for update nowait
  ;

Line: 447


  g_batch_links.delete;

Line: 520


  select
    pel.element_link_id
   ,pel.element_type_id
   ,min(pel.effective_start_date) effective_start_date
   ,max(pel.effective_end_date)   effective_end_date
   ,pel.link_to_all_payrolls_flag
   ,pel.payroll_id
   ,pel.job_id
   ,pel.grade_id
   ,pel.position_id
   ,pel.organization_id
   ,pel.location_id
   ,pel.pay_basis_id
   ,pel.employment_category
   ,pel.people_group_id
   ,pel.standard_link_flag
   --
   ,bos.payroll_action_id
   ,bos.object_status
  from
    pay_element_links_f     pel
   ,pay_batch_object_status bos
  where
      p_phase = 1
  and (   pel.element_link_id = p_elelink_id
       or (    p_gen_link_type = 'A'
           and bos.object_status <> 'C'))
  and pel.business_group_id = p_bgid
  and (pel.standard_link_flag = 'Y'
       or pel.people_group_id is not null)
  and bos.object_type = 'EL'
  and bos.object_id   = pel.element_link_id
  -- not processed by this payroll action
  and nvl(bos.payroll_action_id, -999) <> p_pact_id
  and (nvl(bos.object_status, 'C') <> 'P'
       or not exists
            (select null from pay_payroll_actions
             where payroll_action_id = bos.payroll_action_id))
  group by
    pel.element_link_id
   ,pel.element_type_id
   ,pel.link_to_all_payrolls_flag
   ,pel.payroll_id
   ,pel.job_id
   ,pel.grade_id
   ,pel.position_id
   ,pel.organization_id
   ,pel.location_id
   ,pel.pay_basis_id
   ,pel.employment_category
   ,pel.people_group_id
   ,pel.standard_link_flag
   --
   ,bos.payroll_action_id
   ,bos.object_status
  --
  UNION ALL
  --
  -- element links being processed
  --
  select
    pel.element_link_id
   ,pel.element_type_id
   ,min(pel.effective_start_date) effective_start_date
   ,max(pel.effective_end_date)   effective_end_date
   ,pel.link_to_all_payrolls_flag
   ,pel.payroll_id
   ,pel.job_id
   ,pel.grade_id
   ,pel.position_id
   ,pel.organization_id
   ,pel.location_id
   ,pel.pay_basis_id
   ,pel.employment_category
   ,pel.people_group_id
   ,pel.standard_link_flag
   --
   ,bos.payroll_action_id
   ,bos.object_status
  from
    pay_batch_object_status bos
   ,pay_element_links_f     pel
  where
      bos.payroll_action_id = p_pact_id
  and bos.object_type = 'EL'
  and pel.element_link_id = bos.object_id
  and pel.business_group_id = p_bgid
  and (pel.standard_link_flag = 'Y'
       or pel.people_group_id is not null)
  group by
    pel.element_link_id
   ,pel.element_type_id
   ,pel.link_to_all_payrolls_flag
   ,pel.payroll_id
   ,pel.job_id
   ,pel.grade_id
   ,pel.position_id
   ,pel.organization_id
   ,pel.location_id
   ,pel.pay_basis_id
   ,pel.employment_category
   ,pel.people_group_id
   ,pel.standard_link_flag
   --
   ,bos.payroll_action_id
   ,bos.object_status
  order by
    people_group_id
   ,element_link_id
  ;

Line: 671


  g_standard_links.delete;

Line: 672


  g_pg_links.delete;

Line: 910


    l_sql := 'select nvl(1,:payroll_action_id) from dual where 1 = 0';

Line: 915


'select distinct asg.person_id
 from
   pay_payroll_actions    ppa
  ,per_all_assignments_f  asg
 where
     ppa.payroll_action_id = :payroll_action_id
 and asg.business_group_id = ppa.business_group_id
 and (asg.assignment_type = ''E''
      or (asg.people_group_id is not null
          and asg.assignment_type not in (''A'',''O'')))
 order by asg.person_id';

Line: 929


'select distinct asg.person_id
 from
   pay_payroll_actions    ppa
  ,per_all_assignments_f  asg
  ,per_periods_of_service pos
 where
     ppa.payroll_action_id = :payroll_action_id
 and asg.business_group_id = ppa.business_group_id
 and pos.person_id         = asg.person_id
 and pos.period_of_service_id = asg.period_of_service_id
 and pos.business_group_id = ppa.business_group_id
 order by asg.person_id';

Line: 976


  select distinct
    paf.assignment_id
   ,paf.person_id
  from
    per_periods_of_service     pos
   ,per_all_assignments_f      paf
  where
      pos.person_id between p_stperson and p_endperson
  and pos.business_group_id = p_bgid
  and paf.period_of_service_id = pos.period_of_service_id
  and p_creating_alu_flag = 'N'
  --
  union all
  -- We should handle all assignment types for ALUs.
  select distinct
    paf.assignment_id
   ,paf.person_id
  from
    per_all_assignments_f      paf
  where
      paf.person_id between p_stperson and p_endperson
  and paf.business_group_id = p_bgid
  and (paf.assignment_type = 'E'
       or (paf.people_group_id is not null
           and paf.assignment_type not in ('A','O')))
  and p_creating_alu_flag = 'Y'
  order by 1, 2;

Line: 1034


    select pay_assignment_actions_s.nextval into l_asgact_id
    from dual;

Line: 1136


  select assignment_id into l_asgid
  from pay_assignment_actions
  where assignment_action_id = p_assactid;

Line: 1200


  select 'I'
  from dual
  where exists
          (select null
           from pay_assignment_actions paa
           where paa.payroll_action_id = p_payroll_action_id
           and paa.action_status <> 'C')
  union all
  select ppa.action_status
  from pay_payroll_actions ppa
  where ppa.payroll_action_id = p_payroll_action_id
  ;

Line: 1223


  select
    pay_core_utils.get_parameter
      ('REMOVE_ACT', legislative_parameters) remove_act
  into l_remove_act
  from pay_payroll_actions
  where payroll_action_id = p_payroll_action_id
  ;

Line: 1285


    delete from pay_batch_object_status
    where payroll_action_id = p_payroll_action_id
    and object_status in ('P', 'C')
    ;

Line: 1292


    update pay_batch_object_status
    set payroll_action_id = null
    where payroll_action_id = p_payroll_action_id
    ;

Line: 1300


    update pay_batch_object_status
    set object_status = l_status
    where payroll_action_id = p_payroll_action_id
    and object_status = 'P'
    ;

Line: 1309


    select count(1) into l_count
    from pay_batch_object_status
    where payroll_action_id = p_payroll_action_id
    and object_status = 'P'
    ;