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_ASG_LINK_USAGES_PKG SQL Statements

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

Line: 32


procedure INSERT_ALU (
--
--******************************************************************************
--* Inserts Assignment Link Usages for a new element link.                     *
--******************************************************************************
--
        p_business_group_id     number,
        p_people_group_id       number,
        p_element_link_id       number,
        p_effective_start_date  date,
        p_effective_end_date    date) is
--
v_previous_assignment_id        number;

Line: 64


select id_flex_num,
       segment1, segment2, segment3, segment4, segment5,
       segment6, segment7, segment8, segment9, segment10,
       segment11, segment12, segment13, segment14, segment15,
       segment16, segment17, segment18, segment19, segment20,
       segment21, segment22, segment23, segment24, segment25,
       segment26, segment27, segment28, segment29, segment30
from pay_people_groups link_group
where link_group.people_group_id       = p_people_group_id;

Line: 158


        insert into pay_assignment_link_usages_f
          (assignment_link_usage_id
          ,effective_start_date
          ,effective_end_date
          ,element_link_id
          ,assignment_id)
        values
          (pay_assignment_link_usages_s.nextval
          ,v_start_date_tab(i)
          ,v_end_date_tab(i)
          ,p_element_link_id
          ,v_asg_id_tab(i)
          );

Line: 174


      v_asg_id_tab.delete;

Line: 175


      v_start_date_tab.delete;

Line: 176


      v_end_date_tab.delete;

Line: 229


select  assignment.assignment_id,
        assignment.effective_start_date,
        assignment.effective_end_date
from    per_all_assignments_f ASSIGNMENT,
        pay_people_groups ASSIGNMENT_GROUP
where   assignment.assignment_type       not in (''A'',''O'')
and     assignment.business_group_id + 0 = :p_business_group_id
and     assignment.effective_start_date <= :p_effective_end_date
and     assignment.effective_end_date   >= :p_effective_start_date
and     assignment_group.id_flex_num     = :p_id_flex_num
and     assignment_group.people_group_id = assignment.people_group_id';

Line: 248


   statem := statem || ' for update';

Line: 351


end insert_alu;

Line: 362


        p_delete_mode           varchar2,
        p_effective_start_date  date,
        p_effective_end_date    date,
        p_validation_start_date date,
        p_validation_end_date   date    ) is
--
v_session_date  date;

Line: 372


if p_delete_mode = 'DELETE' then
  --
  v_session_date := p_validation_start_date -1;

Line: 376


  delete
  from  pay_assignment_link_usages_f
  where element_link_id = p_element_link_id
  and   effective_start_date    >= p_validation_start_date;

Line: 381


  if p_delete_mode = 'DELETE' then
    --
    update pay_assignment_link_usages_f
    set         effective_end_date = v_session_date
    where       element_link_id = p_element_link_id
    and         v_session_date between effective_start_date
                                and effective_end_date;

Line: 391


elsif p_delete_mode in ('ZAP', 'DELETE_NEXT_CHANGE') then
  --
  delete
  from pay_assignment_link_usages_f
  where element_link_id = p_element_link_id;

Line: 397


  if p_delete_mode = 'DELETE_NEXT_CHANGE' then
    --
    insert_ALU (
        p_business_group_id,
        p_people_group_id,
        p_element_link_id,
        p_effective_start_date,
        p_effective_end_date    );

Line: 430


  select 1
  from   pay_people_groups el_pg,
         pay_people_groups asg_pg
  where  asg_pg.people_group_id  = p_asg_people_group_id
    and  el_pg.people_group_id   = p_link_people_group_id
    and  el_pg.id_flex_num       = asg_pg.id_flex_num
    and  (el_pg.segment1  is null or el_pg.segment1  = asg_pg.segment1)
    and  (el_pg.segment2  is null or el_pg.segment2  = asg_pg.segment2)
    and  (el_pg.segment3  is null or el_pg.segment3  = asg_pg.segment3)
    and  (el_pg.segment4  is null or el_pg.segment4  = asg_pg.segment4)
    and  (el_pg.segment5  is null or el_pg.segment5  = asg_pg.segment5)
    and  (el_pg.segment6  is null or el_pg.segment6  = asg_pg.segment6)
    and  (el_pg.segment7  is null or el_pg.segment7  = asg_pg.segment7)
    and  (el_pg.segment8  is null or el_pg.segment8  = asg_pg.segment8)
    and  (el_pg.segment9  is null or el_pg.segment9  = asg_pg.segment9)
    and  (el_pg.segment10 is null or el_pg.segment10 = asg_pg.segment10)
    and  (el_pg.segment11 is null or el_pg.segment11 = asg_pg.segment11)
    and  (el_pg.segment12 is null or el_pg.segment12 = asg_pg.segment12)
    and  (el_pg.segment13 is null or el_pg.segment13 = asg_pg.segment13)
    and  (el_pg.segment14 is null or el_pg.segment14 = asg_pg.segment14)
    and  (el_pg.segment15 is null or el_pg.segment15 = asg_pg.segment15)
    and  (el_pg.segment16 is null or el_pg.segment16 = asg_pg.segment16)
    and  (el_pg.segment17 is null or el_pg.segment17 = asg_pg.segment17)
    and  (el_pg.segment18 is null or el_pg.segment18 = asg_pg.segment18)
    and  (el_pg.segment19 is null or el_pg.segment19 = asg_pg.segment19)
    and  (el_pg.segment20 is null or el_pg.segment20 = asg_pg.segment20)
    and  (el_pg.segment21 is null or el_pg.segment21 = asg_pg.segment21)
    and  (el_pg.segment22 is null or el_pg.segment22 = asg_pg.segment22)
    and  (el_pg.segment23 is null or el_pg.segment23 = asg_pg.segment23)
    and  (el_pg.segment24 is null or el_pg.segment24 = asg_pg.segment24)
    and  (el_pg.segment25 is null or el_pg.segment25 = asg_pg.segment25)
    and  (el_pg.segment26 is null or el_pg.segment26 = asg_pg.segment26)
    and  (el_pg.segment27 is null or el_pg.segment27 = asg_pg.segment27)
    and  (el_pg.segment28 is null or el_pg.segment28 = asg_pg.segment28)
    and  (el_pg.segment29 is null or el_pg.segment29 = asg_pg.segment29)
    and  (el_pg.segment30 is null or el_pg.segment30 = asg_pg.segment30)
    ;

Line: 495


  g_alu_asg_hist.delete;

Line: 497


  g_alu_asg_pg_hist.delete;

Line: 515


    select
       people_group_id
      ,effective_start_date
      ,effective_end_date
    from per_all_assignments_f
    where assignment_id = p_assignment_id
    and assignment_type not in ('A','O')
    and people_group_id is not null
    order by effective_start_date;

Line: 588


  g_alu_asg_pg_hist.delete;

Line: 720


  select 1 from pay_assignment_link_usages_f
  where assignment_id = p_assignment_id
  and element_link_id = p_element_link_id;

Line: 736


    select 1
    from per_all_assignments_f
    where assignment_id      = p_asg_id
    and effective_start_date <= p_end_date
    and effective_end_date   >= p_start_date
    for update nowait
    ;

Line: 851


      insert into pay_assignment_link_usages_f
        (assignment_link_usage_id
        ,effective_start_date
        ,effective_end_date
        ,element_link_id
        ,assignment_id)
      values
        (pay_assignment_link_usages_s.nextval
        ,l_alu_start_date_tab(i)
        ,l_alu_end_date_tab(i)
        ,l_alu_link_id_tab(i)
        ,p_assignment_id
        );

Line: 968


      l_link_id_tab.delete;

Line: 969


      l_link_start_date_tab.delete;

Line: 970


      l_link_end_date_tab.delete;

Line: 1044


    select asg.effective_start_date,
           asg.effective_end_date,
           asg.business_group_id,
           asg.people_group_id,
           ppg.id_flex_num
    from   per_all_assignments_f asg,
           pay_people_groups ppg
    where  asg.assignment_id = p_assignment_id
      and  asg.assignment_type not in ('A','O')
      and  asg.people_group_id is not null
      and  ppg.people_group_id = asg.people_group_id
    order by asg.effective_start_date;

Line: 1067


    select el.element_link_id,
           min(el.effective_start_date) effective_start_date,
           max(el.effective_end_date) effective_end_date
    from   pay_element_links_f el,
           pay_people_groups el_pg,
           pay_people_groups asg_pg
    where  asg_pg.id_flex_num      = p_id_flex_num
      and  asg_pg.people_group_id  = p_people_group_id
      and  el_pg.id_flex_num       = asg_pg.id_flex_num
      and  el.business_group_id + 0    = p_business_group_id
      and  el.effective_start_date <= p_effective_end_date
      and  el.effective_end_date   >= p_effective_start_date
      and  el_pg.people_group_id   = el.people_group_id
      and  (el_pg.segment1  is null or el_pg.segment1  = asg_pg.segment1)
      and  (el_pg.segment2  is null or el_pg.segment2  = asg_pg.segment2)
      and  (el_pg.segment3  is null or el_pg.segment3  = asg_pg.segment3)
      and  (el_pg.segment4  is null or el_pg.segment4  = asg_pg.segment4)
      and  (el_pg.segment5  is null or el_pg.segment5  = asg_pg.segment5)
      and  (el_pg.segment6  is null or el_pg.segment6  = asg_pg.segment6)
      and  (el_pg.segment7  is null or el_pg.segment7  = asg_pg.segment7)
      and  (el_pg.segment8  is null or el_pg.segment8  = asg_pg.segment8)
      and  (el_pg.segment9  is null or el_pg.segment9  = asg_pg.segment9)
      and  (el_pg.segment10 is null or el_pg.segment10 = asg_pg.segment10)
      and  (el_pg.segment11 is null or el_pg.segment11 = asg_pg.segment11)
      and  (el_pg.segment12 is null or el_pg.segment12 = asg_pg.segment12)
      and  (el_pg.segment13 is null or el_pg.segment13 = asg_pg.segment13)
      and  (el_pg.segment14 is null or el_pg.segment14 = asg_pg.segment14)
      and  (el_pg.segment15 is null or el_pg.segment15 = asg_pg.segment15)
      and  (el_pg.segment16 is null or el_pg.segment16 = asg_pg.segment16)
      and  (el_pg.segment17 is null or el_pg.segment17 = asg_pg.segment17)
      and  (el_pg.segment18 is null or el_pg.segment18 = asg_pg.segment18)
      and  (el_pg.segment19 is null or el_pg.segment19 = asg_pg.segment19)
      and  (el_pg.segment20 is null or el_pg.segment20 = asg_pg.segment20)
      and  (el_pg.segment21 is null or el_pg.segment21 = asg_pg.segment21)
      and  (el_pg.segment22 is null or el_pg.segment22 = asg_pg.segment22)
      and  (el_pg.segment23 is null or el_pg.segment23 = asg_pg.segment23)
      and  (el_pg.segment24 is null or el_pg.segment24 = asg_pg.segment24)
      and  (el_pg.segment25 is null or el_pg.segment25 = asg_pg.segment25)
      and  (el_pg.segment26 is null or el_pg.segment26 = asg_pg.segment26)
      and  (el_pg.segment27 is null or el_pg.segment27 = asg_pg.segment27)
      and  (el_pg.segment28 is null or el_pg.segment28 = asg_pg.segment28)
      and  (el_pg.segment29 is null or el_pg.segment29 = asg_pg.segment29)
      and  (el_pg.segment30 is null or el_pg.segment30 = asg_pg.segment30)
    group by el.element_link_id;

Line: 1129


  delete from pay_assignment_link_usages_f alu
  where alu.assignment_id = p_assignment_id;

Line: 1240


          insert into pay_assignment_link_usages_f
          (assignment_link_usage_id,
           effective_start_date,
           effective_end_date,
           element_link_id,
           assignment_id)
          values
          (
           pay_assignment_link_usages_s.nextval,
           v_alu_tab.start_date(i),
           v_alu_tab.end_date(i),
           v_alu_tab.link_id(i),
           p_assignment_id
          );

Line: 1256


      v_alu_tab.start_date.delete;

Line: 1257


      v_alu_tab.end_date.delete;

Line: 1258


      v_alu_tab.link_id.delete;