APPS.HR_APPROVAL_CUSTOM SQL Statements

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

Line: 75

SELECT paf.person_id
FROM per_all_assignments_f paf START WITH paf.person_id = p_person_id
 AND paf.primary_flag = 'Y'
 AND paf.assignment_type IN('E',   'C')
 AND l_effective_date BETWEEN paf.effective_start_date
 AND paf.effective_end_date
 CONNECT BY PRIOR paf.supervisor_id = paf.person_id
 AND paf.primary_flag = 'Y'
 AND paf.assignment_type IN('E',   'C')
 AND l_effective_date BETWEEN paf.effective_start_date
 AND paf.effective_end_date;

Line: 95

     select  ppf.person_id
     into l_employee
     from per_all_people_f ppf
     where ppf.person_id = p_person_id
     and  trunc(sysdate) between ppf.effective_start_date and  ppf.effective_end_date
     and  (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y') ;

Line: 115

      select  ppf.person_id
      into l_employee
      from per_all_people_f ppf
      where ppf.person_id = l_person_id
      and  trunc(sysdate) between ppf.effective_start_date and  ppf.effective_end_date
      and  (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y');

Line: 135

select nvl(max(ppf.EFFECTIVE_END_DATE),sysdate+10)
into ed_date
from per_all_people_f ppf where ppf.person_id in (
select paf1.supervisor_id
     from per_all_assignments_f paf1
     where paf1.primary_flag = 'Y'
     and paf1.assignment_type in ('E','C')
     and paf1.person_id = l_person_id
     and paf1.supervisor_id is not null
     and paf1.EFFECTIVE_END_DATE = ( select max(paf.EFFECTIVE_END_DATE)
                                       from per_all_assignments_f paf
                                       where paf.primary_flag = 'Y'
                                       and paf.assignment_type in ('E','C')
                                       and paf.person_id = l_person_id
     )
)
and  (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y');

Line: 155

      select distinct paf1.supervisor_id
      into l_disp_person_id
      from per_all_assignments_f paf1
      where paf1.primary_flag = 'Y'
      and paf1.assignment_type in ('E','C')
      and paf1.person_id = l_person_id
      and paf1.supervisor_id is not null
      and paf1.EFFECTIVE_END_DATE = ( select max(paf.EFFECTIVE_END_DATE)
                                        from per_all_assignments_f paf
                                        where paf.primary_flag = 'Y'
                                        and paf.assignment_type in ('E','C')
                                        and paf.person_id = l_person_id
      );

Line: 212

    select  ppf.person_id
    from    per_all_assignments_f paf
           -- fix for bug # 1677216
           --,per_people_f      ppf
           ,per_all_people_f      ppf
    where   paf.person_id             = l_in_person_id
    and     paf.primary_flag          = 'Y'
    and     l_effective_date
    between paf.effective_start_date
    and     paf.effective_end_date
    and     ppf.person_id             = paf.supervisor_id
    and     ppf.current_employee_flag = 'Y'
    and     l_effective_date
    between ppf.effective_start_date
    and     ppf.effective_end_date;

Line: 230

    select  ppf.person_id
    from    per_all_assignments_f paf
           ,per_all_people_f      ppf
    where   paf.person_id             = l_in_person_id
    and      paf.primary_flag = 'Y'
       and     trunc(sysdate)
       between paf.effective_start_date
       and     paf.effective_end_date
       and     paf.assignment_type in ('E','C')
       and     paf.assignment_status_type_id not in
                                 (select assignment_status_type_id
                                  from per_assignment_status_types
                                where per_system_status = 'TERM_ASSIGN' and business_group_id=paf.business_group_id)
    and     ppf.person_id  = paf.supervisor_id
    and     (ppf.current_employee_flag = 'Y' Or ppf.current_npw_flag = 'Y')
    and     trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;