APPS.HR_CANCEL_HIRE_API SQL Statements

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

Line: 73

  SELECT DISTINCT
         paa.business_group_id
        ,pos.period_of_service_id
        ,pos.date_start
  FROM   per_periods_of_service pos
        ,per_all_assignments_f paa
  WHERE  paa.person_id = p_person_id
  AND    p_effective_date BETWEEN
         paa.effective_start_date AND paa.effective_end_date
  AND    paa.person_id = pos.person_id
--
-- 115.3 (START)
--
  AND    pos.date_start IN
         (SELECT MAX(pos2.date_start)
          FROM   per_periods_of_service pos2
          WHERE  pos2.person_id = p_person_id)
--
-- 115.3 (END)
--
 AND    paa.period_of_service_id = pos.period_of_service_id;

Line: 97

   SELECT DISTINCT
          paa.business_group_id
         ,pos.period_of_service_id
         ,pos.date_start
   FROM   per_periods_of_service pos
         ,per_all_assignments_f paa
   WHERE  paa.person_id = p_person_id
   AND    p_effective_date BETWEEN
          paa.effective_start_date AND paa.effective_end_date
   AND    paa.person_id = pos.person_id
   AND    paa.period_of_service_id = pos.period_of_service_id;

Line: 133

select max(effective_start_date)
from per_person_type_usages_f ppf1
where ppf1.person_id =  p_person_id

and ppf1.effective_start_date >
    ( select max(ppf2.effective_start_date)
    from per_person_type_usages_f ppf2 , per_person_types ppt2
    where ppf2.person_id = p_person_id
         and ppt2.person_type_id= ppf2.person_type_id
        and ppt2.system_person_type='APL'
        and ppt2.business_group_id = l_business_group_id )

and exists (
select '1' from
per_person_types ppt
where ppt.person_type_id= ppf1.person_type_id
and ppt.system_person_type='EX_APL'
and ppt.business_group_id = l_business_group_id )

and not exists (
select '1' from
per_person_type_usages_f ppf2
where ppf2.person_id = p_person_id
and ppf2.effective_start_date > ppf1.effective_start_date
and ppf2.person_type_id <> ppf1.person_type_id ); -- fix for 9587362

Line: 160

select max(effective_start_date)
from per_person_type_usages_f pptf
where person_id = p_person_id
and exists (
select '1' from
per_person_types ppt
where ppt.person_type_id= pptf.person_type_id
and ppt.system_person_type='EMP'
and ppt.business_group_id = l_business_group_id );

Line: 171

select max(effective_start_date)
from per_person_type_usages_f pptf
where person_id = p_person_id
and exists (
select '1' from
per_person_types ppt
where ppt.person_type_id= pptf.person_type_id
and ppt.system_person_type='APL'
and ppt.business_group_id = l_business_group_id );

Line: 187

    SELECT typ.system_person_type
      FROM  per_person_types typ
          ,per_person_type_usages_f ptu
     WHERE
        typ.system_person_type IN ('APL','EMP','EX_APL','EX_EMP','CWK','EX_CWK','OTHER')
       AND typ.person_type_id = ptu.person_type_id
       AND p_effective_date BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date
       AND ptu.person_id = p_person_id
  ORDER BY DECODE(typ.system_person_type
                 ,'EMP'   ,1
                 ,'CWK'   ,2
                 ,'APL'   ,3
                 ,'EX_EMP',4
                 ,'EX_CWK',5
                 ,'EX_APL',6
                          ,7
                 );

Line: 207

select '1'
from per_person_type_usages_f where
person_id=p_person_id and
effective_start_date > l_date;

Line: 215

  select 'exist'
  from   per_pay_proposals
  where  assignment_id  in (
          select distinct (assignment_id)
          from per_all_assignments_f
          where person_id= p_person_id
          and assignment_type ='E'
          and l_efd -1 between effective_start_date and effective_end_date
          and primary_flag='Y'
          )
          and change_date >= l_efd;

Line: 244

select 'X' from
per_periods_of_service
where person_id = p_person_id
and actual_termination_date = p_effective_date;

Line: 641

                            p_adjustment_type      => 'DELETE');