APPS.PER_CANCEL_HIRE_OR_APL_PKG SQL Statements

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

Line: 30

  select  ppt.system_person_type
  from per_person_type_usages_f ptu, per_person_types ppt
  where ptu.person_type_id = ppt.person_type_id
  and person_id = p_person_id
  and ppt.business_group_id = p_business_group_id
  and ppt.system_person_type <> p_current_person_type
  and p_effective_date between ptu.effective_start_date and ptu.effective_end_date
  and ppt.system_person_type in ('EMP','EX_EMP','APL','EX_APL','CWK','EX_CWK');

Line: 40

  select  ppt.system_person_type
  from per_person_type_usages_f ptu, per_person_types ppt
  where ptu.person_type_id = ppt.person_type_id
  and person_id = p_person_id
  and ppt.business_group_id = p_business_group_id
  and ppt.system_person_type in ('EMP','EX_EMP','APL','EX_APL','CWK','EX_CWK')
  and ptu.effective_end_date =
   (select effective_start_date - 1
   from per_person_type_usages_f ptu1, per_person_types ppt1
   where ptu1.person_type_id = ppt1.person_type_id
   and ptu1.person_id = p_person_id
   and ppt1.system_person_type = p_current_person_type
   and p_effective_date between effective_start_date and effective_end_date)
  order by effective_end_date desc, effective_start_date desc;

Line: 56

  select  ppt.system_person_type
  from per_all_people_f papf, per_person_types ppt
  where papf.person_type_id = ppt.person_type_id
  and person_id = p_person_id
  and ppt.business_group_id = p_business_group_id
  and ppt.system_person_type in
   ('EMP',
    'EX_EMP',
    'APL',
    'EX_APL',
    'EMP_APL',
    'EX_EMP_APL',
    'OTHER')
  and papf.effective_end_date =
   (select effective_start_date - 1
   from per_person_type_usages_f ptu1, per_person_types ppt1
   where ptu1.person_type_id = ppt1.person_type_id
   and ptu1.person_id = p_person_id
   and ppt.business_group_id = p_business_group_id
   and ppt1.system_person_type = p_current_person_type
   and p_effective_date between effective_start_date and effective_end_date)
  order by effective_end_date desc, effective_start_date desc;

Line: 192

    select h.rowid
    from   hr_comments h
    ,      per_assignments_f paf
    where  h.comment_id = paf.comment_id
    and    paf.business_group_id + 0 = p_business_group_id
    and    paf.person_id             = p_person_id
    and    paf.effective_start_date >= p_effective_date
    for    update of h.comment_id;

Line: 204

    select rowid
    from   pay_personal_payment_methods ppm
    where  ppm.business_group_id = p_business_group_id
    and    exists (select 'exists'
                   from per_all_assignments_f paf
                   where paf.business_group_id    +0= p_business_group_id
                   and   paf.person_id            = p_person_id
                   and   paf.assignment_id        = ppm.assignment_id
		   --start bug 5987416
                   --and   ppm.effective_start_date>= p_effective_date
		   --end bug 5987416
		   )
    and   ppm.effective_start_date               >= p_effective_date
    for   update of ppm.assignment_id;

Line: 222

    select pab.rowid
    from   per_assignment_budget_values_f pab
    ,      per_assignments_f paf
    where  pab.business_group_id + 0 = paf.business_group_id + 0
    and    paf.business_group_id + 0 = p_business_group_id
    and    pab.assignment_id         = paf.assignment_id
    and    paf.person_id             = p_person_id
    and    paf.effective_end_date   >=p_effective_date
    and    pab.effective_end_date   >=p_effective_date
    for    update of pab.assignment_id;

Line: 236

    select rowid
    from   per_assignments_f p
    where  p.recruiter_id = p_person_id
    and    (p.business_group_id = p_business_group_id OR
            nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    for    update of assignment_id;

Line: 246

    select pb.rowid
    from   per_events pe
    ,      per_bookings pb
    where  pe.business_group_id = pb.business_group_id
    and    (pb.business_group_id = p_business_group_id OR
           nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    and    pe.event_id           = pb.event_id
    and    pe.event_or_interview in ('I','E')
    and    pb.person_id          = p_person_id
    for    update of pb.event_id;

Line: 260

    select rowid
    from   per_vacancies pv
    where (pv.business_group_id  = p_business_group_id OR
           nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    and   pv.recruiter_id      = p_person_id
    and   pv.date_from        >= p_effective_date
    for update of pv.vacancy_id;

Line: 271

    select rowid
    from  per_requisitions pr
    where (pr.business_group_id = p_business_group_id OR
           nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    and   pr.person_id         = p_person_id
    for   update of person_id;

Line: 279

    select rowid
    from   per_assignments_f p
    where  (p.business_group_id = p_business_group_id OR
           nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    and    p.supervisor_id = p_person_id
    for    update of assignment_id;

Line: 287

    select ppf.rowid
    from   per_people_f ppf
    where  ppf.person_id = p_person_id
    for update of ppf.person_id;

Line: 295

    select paf.rowid
    from per_assignments_f paf
    where paf.business_group_id + 0 = p_business_group_id
    and   paf.person_id            = p_person_id
    and   paf.assignment_type      = 'C'
    for update of paf.assignment_id;

Line: 305

    select pp.rowid
    from   per_periods_of_placement pp
    where  pp.person_id  = p_person_id
    for update of person_id;

Line: 311

    select pgr.rowid
    from   pay_grade_rules_f pgr
    where  exists (select 'x'
                   from   per_assignments_f paf
                   where  pgr.grade_or_spinal_point_id = paf.assignment_id
                   and    paf.business_group_id + 0    = p_business_group_id
                   and    paf.person_id                = p_person_id)
    for update of pgr.grade_or_spinal_point_id;

Line: 321

    select spp.rowid
    from   per_spinal_point_placements_f spp
    where spp.business_group_id = p_business_group_id and
    exists (select 'x'
                   from   per_assignments_f paf
                   where  spp.assignment_id = paf.assignment_id
                   and    paf.business_group_id + 0    = p_business_group_id
                   and    paf.person_id                = p_person_id)
    for update of spp.assignment_id;

Line: 332

    select pca.rowid
    from   pay_cost_allocations_f pca
    where  exists (select 'x'
                   from   per_assignments_f paf
                   where  pca.assignment_id = paf.assignment_id
                   and    paf.business_group_id + 0    = p_business_group_id
                   and    paf.person_id                = p_person_id)
    for update of pca.assignment_id;

Line: 467

      select ppf.rowid
      from   per_people_f ppf
      where  ppf.person_id = p_person_id
      for update of ppf.person_id;

Line: 475

      select pps.rowid
      from   per_periods_of_service pps
      where  pps.person_id = p_person_id
      for    update of pps.person_id;

Line: 483

      select pap.rowid
      from   per_applications pap
      where  pap.person_id = p_person_id
      for    update of pap.person_id;

Line: 491

select rowid
from   per_assignments_f p
where  (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and    p.supervisor_id = p_person_id
for    update of assignment_id;

Line: 501

select rowid
from   per_assignments_f p
where  p.recruiter_id = p_person_id
and    (p.business_group_id = p_business_group_id OR
   nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
for    update of assignment_id;

Line: 511

select pb.rowid
from   per_events pe
,      per_bookings pb
where  pe.business_group_id = pb.business_group_id
and    (pb.business_group_id = p_business_group_id OR
    nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and    pe.event_id           = pb.event_id
and    pe.event_or_interview = p_type
and    pb.person_id          = p_person_id
for    update of pb.event_id;

Line: 525

select rowid
from   per_vacancies pv
where (pv.business_group_id  = p_business_group_id OR
   nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pv.recruiter_id      = p_person_id
and   pv.date_from        >= p_primary_date
for update of pv.vacancy_id;

Line: 536

select rowid
from  per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
     nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pr.person_id         = p_person_id
for   update of person_id;

Line: 546

select rowid
from   per_absence_attendances paa
where  paa.business_group_id +0 = p_business_group_id
and    paa.person_id         = p_person_id
and    paa.date_start       >= p_primary_date
for    update of person_id;

Line: 556

select rowid
from   pay_personal_payment_methods ppm
where  ppm.business_group_id = p_business_group_id
and    exists (select 'exists'
               from per_all_assignments_f paf
               where paf.business_group_id    +0= p_business_group_id
               and   paf.person_id            = p_person_id
               and   paf.assignment_id        = ppm.assignment_id
               and   paf.period_of_service_id = p_primary_id
	       --start bug 5987416
               --and   ppm.effective_start_date>= p_primary_date
	       --end bug 5987416
              )
and   ppm.effective_start_date               >= p_primary_date
for   update of ppm.assignment_id;

Line: 575

select pab.rowid
from   per_assignment_budget_values_f pab
,      per_assignments_f paf
where  pab.business_group_id + 0    = paf.business_group_id + 0
and    paf.business_group_id + 0    = p_business_group_id
and    pab.assignment_id        = paf.assignment_id
and    paf.person_id            = p_person_id
and    paf.effective_end_date  >=p_primary_date
and    pab.effective_end_date  >=p_primary_date
for    update of pab.assignment_id;

Line: 589

select p.rowid
from   per_letter_request_lines p
,      per_assignments_f paf
where  p.assignment_id = paf.assignment_id
and    paf.business_group_id + 0    = p_business_group_id
and    paf.person_id            = p_person_id
and    paf.application_id       = p_primary_id
and    paf.effective_end_date  >=p_primary_date
for    update of p.assignment_id;

Line: 602

select h.rowid
from   hr_comments h
,      per_assignments_f paf
where  h.comment_id = paf.comment_id
and    paf.business_group_id + 0    = p_business_group_id
and    paf.person_id            = p_person_id
and    paf.application_id       = p_primary_id
and    paf.effective_end_date  >=p_primary_date
for    update of h.comment_id;

Line: 615

select paf.rowid
from per_assignments_f paf
where paf.business_group_id + 0    = p_business_group_id
and   paf.person_id            = p_person_id
and   paf.assignment_type      = 'A'
and   paf.application_id       = p_primary_id
for update of paf.assignment_id;

Line: 626

select paf.rowid
from per_assignments_f paf
where paf.business_group_id + 0    = p_business_group_id
and   paf.person_id            = p_person_id
and   paf.assignment_type      = 'E'
and   paf.application_id       = p_primary_id
for update of paf.assignment_id;

Line: 817

select 'Y'
from   per_all_assignments_f a
where  a.person_id = p_person_id
--
-- 115.51 (START)
--
AND    a.period_of_service_id = p_primary_id
--
-- 115.51 (END)
--
AND    ((a.effective_start_date = p_primary_date
         and a.primary_flag <> 'Y'
         and not exists ( select b.assignment_id
                          from   per_all_assignments_f b
                          where  nvl(b.effective_end_date,hr_api.g_eot)
                                  = (p_primary_date-1)
                          and    b.assignment_id = a.assignment_id) )
         OR  a.effective_start_date > p_primary_date );

Line: 840

select rowid
from   per_assignments_f p
where  (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and    p.supervisor_id = p_person_id
--start changes for bug 9304366
and p.effective_end_date >= p_primary_date
and p.assignment_type in ('E','C')
and not exists (
  select 1
  from per_assignment_status_types past
  where past.assignment_status_type_id = p.assignment_status_type_id
  and past.per_system_status = 'TERM_ASSIGN'
  and (past.business_group_id = p.business_group_id
       or past.business_group_id is NULL));

Line: 860

select rowid
from   per_assignments_f p
where  p.recruiter_id = p_person_id
and    (p.business_group_id = p_business_group_id OR
     nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
--start changes for bug 9304366
and p.effective_end_date >= p_primary_date
and p.assignment_type = 'A';

Line: 873

select 'Events exist'
                from   per_events pe
                ,      per_bookings pb
                where  pe.business_group_id = pb.business_group_id
                and    (pb.business_group_id = p_business_group_id OR
                      nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
                and    pe.event_id           = pb.event_id
                and    pe.event_or_interview = p_type
                and    pb.person_id          = p_person_id;

Line: 886

select 'Interviews exist'
from   per_events pe
where  (pe.business_group_id  = p_business_group_id OR
      nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and    pe.event_or_interview = 'I'
and    pe.internal_contact_person_id = p_person_id;

Line: 896

select rowid
from   per_vacancies pv
where (pv.business_group_id  = p_business_group_id OR
    nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pv.recruiter_id      = p_person_id
and   pv.date_from        >= p_primary_date;

Line: 906

select rowid
from per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
     nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pr.person_id         = p_person_id
and   pr.date_from        >= p_primary_date; -- added for bug 9304366

Line: 916

select rowid
from per_assignment_budget_values_f pab
where pab.business_group_id = p_business_group_id and
exists (select 'budget_values exist'
               from per_all_assignments_f paf
               where  pab.business_group_id    +0= paf.business_group_id + 0
               and    paf.business_group_id    +0= p_business_group_id
               and    pab.assignment_id        = paf.assignment_id
               and    paf.person_id            = p_person_id
               and    paf.period_of_service_id = p_primary_id
               and    paf.effective_end_date  >= p_primary_date
       	       --start bug 5987416
               --and    pab.effective_end_date  >= p_primary_date
	       )
AND   pab.effective_end_date  >= p_primary_date;

Line: 936

select rowid
from pay_personal_payment_methods ppm
where  ppm.business_group_id                  = p_business_group_id
and    exists (select 'exists'
               from per_all_assignments_f paf
               where paf.business_group_id    +0= p_business_group_id
               and   paf.person_id            = p_person_id
               and   paf.assignment_id        = ppm.assignment_id
               and   paf.period_of_service_id = p_primary_id
	       --start bug 5987416
               --and   ppm.effective_start_date>= p_primary_date
	       --end bug 5987416
              )
and   ppm.effective_start_date               >= p_primary_date;

Line: 954

   SELECT null
              FROM   pay_payroll_actions pac,
                     pay_assignment_actions act,
                     per_assignments_f asg
              WHERE  asg.person_id = p_person_id
--
-- 115.51 (START)
--
                AND  asg.period_of_service_id = p_primary_id
--
-- 115.51 (END)
--
                AND  act.assignment_id = asg.assignment_id
                AND  pac.payroll_action_id = act.payroll_action_id
--
--Start Bug 4724223
--
                AND  pac.action_type NOT IN ('X','BEE')
                AND  p_primary_date BETWEEN asg.effective_start_date
                                    AND asg.effective_end_date
--
--End Bug 4724223
--
                AND  pac.effective_date >= p_primary_date;

Line: 983

    SELECT null
       FROM   pay_payroll_actions pac,
      pay_assignment_actions act,
      per_assignments_f asg
       WHERE  asg.person_id = p_person_id
 AND  asg.period_of_service_id = p_primary_id
 AND  act.assignment_id = asg.assignment_id
 AND  pac.payroll_action_id = act.payroll_action_id
 AND  pac.action_type = 'X'
 AND  pac.report_type = 'YREND'
 AND  pac.report_qualifier='FED'
 AND  pac.report_category='RT'
 AND  p_primary_date BETWEEN asg.effective_start_date
     AND asg.effective_end_date
 AND  pac.effective_date >= p_primary_date;

Line: 1003

 select 'exists'
 from per_applications pa
 where pa.business_group_id = business_group_id
  and pa.person_id = p_person_id
  and pa.date_received >= p_primary_date
  and pa.application_id <> nvl(
	(select application_id
	 from per_applications
	 where business_group_id = p_business_group_id
	 and person_id = p_person_id
	 and p_primary_date - 1 between date_received and nvl(date_end,to_date('31/12/4712','dd/mm/yyyy'))
	 ),pa.application_id) ;

Line: 1019

    SELECT ptu.person_type_usage_id
    FROM   per_person_types pt,
           per_person_type_usages_f ptu
    WHERE  pt.business_group_id     = p_business_group_id
    AND    pt.person_type_id        = ptu.person_type_id
    AND    p_primary_date BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date
    AND    ptu.person_id            = p_person_id
    AND    pt.system_person_type = p_system_person_type;

Line: 1406

select rowid
from per_assignment_budget_values_f pab
where pab.business_group_id = p_business_group_id and

exists (select 'budget_values exist'
               from per_all_assignments_f paf
               where  pab.business_group_id    +0= paf.business_group_id + 0
               and    paf.business_group_id    +0= p_business_group_id
               and    pab.assignment_id        = paf.assignment_id
               and    paf.person_id            = p_person_id
               and    paf.period_of_service_id = l_period_of_service_id
               and    paf.effective_end_date  >= p_date_start
	       --for bug 5949102
               --and    pab.effective_end_date  >= p_date_start
	       )
and    pab.effective_end_date  >= p_date_start;

Line: 1426

select rowid
from pay_personal_payment_methods ppm
where  ppm.business_group_id                  = p_business_group_id
and    exists (select 'exists'
               from per_all_assignments_f paf
               where paf.business_group_id    +0= p_business_group_id
               and   paf.person_id            = p_person_id
               and   paf.assignment_id        = ppm.assignment_id
               and   paf.period_of_service_id = l_period_of_service_id
	       --for 5949102
               --and ppm.effective_start_date>= p_date_start
              )
              --end 5949102
and   ppm.effective_start_date               >= p_date_start;

Line: 1444

select rowid, assignment_id, assignment_status_type_id
from per_all_assignments_f paf1
where paf1.business_group_id    +0 = p_business_group_id
and   paf1.person_id             =  p_person_id
and paf1.assignment_type ='A'
and paf1.effective_end_date = p_date_start -1
and exists ( select '1' from
 per_all_assignments_f paf2
 where paf2.business_group_id    +0= p_business_group_id
and   paf2.person_id            = p_person_id
and PAF2.assignment_type ='E'
and paf2.effective_start_date = p_date_start
and paf1.assignment_id = paf2.assignment_id
and   paf2.period_of_service_id = l_period_of_service_id );

Line: 1462

       select iass.assignment_status_id, iass.object_version_number
         from irc_assignment_statuses iass
        where iass.assignment_status_id =
              (select max(iass.assignment_status_id)
                 from irc_assignment_statuses iass
                where iass.assignment_id = l_assignment_id
                  and iass.assignment_status_type_id = l_asg_status_type_id);

Line: 1474

 select assignment_id,effective_start_date
 from   per_all_assignments_f papf1
 where  papf1.person_id = p_person_id
 and    papf1.business_group_id +0 = p_business_group_id
 and    papf1.period_of_service_id is NULL
 and    papf1.assignment_type in ('A')
 and    (p_date_start -1) =( select max(effective_end_date)
                             from per_all_assignments_f papf2
                             where papf1.assignment_id =papf2.assignment_id);

Line: 1485

 select assignment_id,effective_start_date,ROWID
 from   per_all_assignments_f
 where  person_id = p_person_id
 and    business_group_id +0 = p_business_group_id
 and    period_of_service_id is NULL
 and    assignment_type in ('A')
 and    effective_end_date =  p_end_of_time;

Line: 1495

 select assignment_id
 from per_all_assignments_f
 where person_id= p_person_id
 and business_group_id= p_business_group_id
 and assignment_type='E'
 and primary_flag='Y'
 and effective_end_date= p_date_start -1
 and period_of_service_id=l_period_of_service_id;

Line: 1505

select paf.assignment_id, paf.effective_start_date
  from per_all_assignments_f paf
 where paf.person_id = p_person_id
   and paf.business_group_id +0 = p_business_group_id
  and paf.assignment_type not in ('B','O');  -- issue 3 raised .

Line: 1515

select rowid
from per_applications pap
where exists (select 'row exists'
 from   per_all_assignments_f paf
 where  paf.person_id = p_person_id
 and    paf.business_group_id +0 = p_business_group_id
 and    paf.period_of_service_id is NULL
 and    paf.effective_end_date = p_date_start - 1
 and    pap.application_id = paf.application_id);

Line: 1531

      select pps.rowid
      from   per_periods_of_service pps
      where  pps.person_id = p_person_id
      and period_of_service_id = l_period_of_service_id;

Line: 1539

select p.rowid, effective_end_date from per_people_f p
where  p.person_id = p_person_id
and   p.effective_start_date >= p_date_start;

Line: 1546

select p.rowid from per_people_f p
where p.person_id = p_person_id
and   p.effective_end_date = p_date_start -1;

Line: 1552

select ppp.pay_proposal_id, ppp.object_version_number
from per_pay_proposals ppp
where p_assignment_id = ppp.assignment_id;

Line: 1557

  select element_entry_id
  from   pay_element_entries_f
  where  assignment_id = p_assignment_id
  and    creator_type = 'SP'
  and    p_date_start between
         effective_start_date and effective_end_date;

Line: 1566

  select element_entry_id
  from   pay_element_entries_f
  where  assignment_id = p_assignment_id
  and    creator_type = 'SP'
  and    (p_date_start - 1) between
         effective_start_date and effective_end_date;

Line: 1575

    SELECT ptu.person_type_usage_id
    FROM   per_person_types pt,
           per_person_type_usages_f ptu
    WHERE  pt.business_group_id     = p_business_group_id
    AND    pt.person_type_id        = ptu.person_type_id
    AND    p_date_start BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date
    AND    ptu.person_id            = p_person_id
    AND    pt.system_person_type = 'EX_APL';

Line: 1588

select 'Y'
from   per_all_assignments_f a
where  a.person_id = p_person_id

AND    a.period_of_service_id = l_period_of_service_id
AND    ((a.effective_start_date = p_date_start
         and a.primary_flag <> 'Y'
         and not exists ( select b.assignment_id
                          from   per_all_assignments_f b
                          where  nvl(b.effective_end_date,hr_api.g_eot)
                                  = (p_date_start-1)
                          and    b.assignment_id = a.assignment_id) )
         OR  a.effective_start_date > p_date_start );

Line: 1605

   SELECT null
              FROM   pay_payroll_actions pac,
                     pay_assignment_actions act,
                     per_assignments_f asg
              WHERE  asg.person_id = p_person_id
               AND  asg.period_of_service_id = l_period_of_service_id
               AND  act.assignment_id = asg.assignment_id
                AND  pac.payroll_action_id = act.payroll_action_id
                AND  pac.action_type NOT IN ('X','BEE')
                AND  p_date_start BETWEEN asg.effective_start_date
                                    AND asg.effective_end_date
                 AND  pac.effective_date >= p_date_start;

Line: 1633

  select pps.period_of_service_id
  into   v_dummy
  from   per_periods_of_service pps
  where  p_start_date = pps.date_start
  and     pps.person_id = p_person_id;

Line: 1734

  delete from per_spinal_point_placements_f spp
  where spp.assignment_id = l_assignment_id;

Line: 1737

  delete from pay_cost_allocations_f pca
  where pca.assignment_id = l_assignment_id;

Line: 1747

     IRC_ASG_STATUS_API.delete_irc_asg_status
         (p_assignment_status_id  => l_asg_status_id
         ,p_object_version_number => l_asg_status_ovn);

Line: 1760

      hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id  => l_pk_id
                        ,p_business_group_id  => l_business_group_id
                        ,p_object_version_number => l_ovn
                        ,p_validate   => FALSE
                        ,p_salary_warning  =>  l_sal_warning);

Line: 1779

      hr_entry_api.delete_element_entry
        ('DELETE'
        ,p_date_start - 1
        ,l_element_entry_id);

Line: 1785

       hr_entry_api.delete_element_entry
        ('ZAP'
        ,p_date_start
        ,l_element_entry_id);

Line: 1797

  delete from per_all_assignments_f paf
  where paf.assignment_id=l_assignment_id
  and paf.person_id=p_person_id
  and paf.effective_start_date=p_date_start;

Line: 1805

     update per_all_assignments_f
     set effective_end_date = p_end_of_time
     where assignment_id=l_assignment_id
     and  person_id=p_person_id
     and  effective_end_date= p_date_start -1;

Line: 1833

  update per_applications pap
  set pap.date_end = NULL
  where pap.rowid = p_rowid;

Line: 1878

     update per_all_assignments_f
     set effective_end_date = p_end_of_time
     where assignment_id=p_assignment_id
     and  person_id=p_person_id
     and  effective_end_date= p_date_start -1;

Line: 1901

delete from per_all_assignments_f
where person_id= p_person_id
and assignment_id = l_asg
and effective_start_date > = p_date_start;

Line: 1906

update per_all_assignments_f
    set effective_end_date = p_end_of_time

WHERE person_id= p_person_id
and assignment_id = l_asg
AND effective_end_date = p_date_start -1
and primary_flag='Y';

Line: 1921

        delete from per_people_f
        where rowid = p_rowid;

Line: 1940

         update per_people_f
         set effective_end_date = p_end_of_time
         where rowid = p_rowid;

Line: 1980

 update per_applications pap
  set pap.date_end = NULL
  where  application_id in ( select distinct (application_id)
         from  per_all_assignments_f
         where person_id= p_person_id
         and assignment_type = 'A'
         and sysdate between effective_start_date and effective_end_date
         );

Line: 1989

  per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);

Line: 2012

/*Procedure to update the end date person type OTHER
  when cancel placement is done.Added for the bug 6460093*/

procedure upd_person_type_usage_end_date
(
   p_effective_date                 in     date
  ,p_person_id                      in     number
  ,p_system_person_type             in     varchar2


 ) is

   cursor csr_upded_person_type_usages
  (
     p_effective_date                 in     date
    ,p_person_id                      in     number
    ,p_system_person_type             in     varchar2
   ) is
    select ptu.person_type_usage_id
          ,ptu.object_version_number
      from per_person_type_usages_f ptu
     where p_effective_date between ptu.effective_start_date and ptu.effective_end_date
       and ptu.person_id = p_person_id
       and ptu.person_type_id in
             (select ppt.person_type_id
                from per_person_types ppt
               where ((   p_system_person_type = 'OTHER'
                        and ppt.system_person_type = 'OTHER' )));

Line: 2058

       update per_person_type_usages_f ptu
       set effective_end_date = l_effective_end_date
       where ptu.effective_end_date = p_effective_date
       and ptu.person_id             = p_person_id
       and ptu.person_type_usage_id  = l_csr_upd_per_type_usages.person_type_usage_id
       and ptu.object_version_number = l_csr_upd_per_type_usages.object_version_number;

Line: 2100

select rowid
from   per_assignments_f p
where  (p.business_group_id = p_business_group_id OR
nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and    p.supervisor_id = p_person_id
--start changes for bug 9304366
and p.effective_end_date >= p_date_start
and p.assignment_type in ('E','C')
and not exists (
  select 1
  from per_assignment_status_types past
  where past.assignment_status_type_id = p.assignment_status_type_id
  and past.per_system_status = 'TERM_ASSIGN'
  and (past.business_group_id = p.business_group_id
       or past.business_group_id is NULL));

Line: 2120

select rowid
from   per_assignments_f p
where  p.recruiter_id = p_person_id
and    (p.business_group_id = p_business_group_id OR
     nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
--start changes for bug 9304366
and p.effective_end_date >= p_date_start
and p.assignment_type = 'A';

Line: 2133

select rowid
from per_bookings pb
where (pb.business_group_id = p_business_group_id OR
      nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pb.person_id = p_person_id
and   exists ( select 'row exists'
               from per_events pe
               where pe.business_group_id + 0 = p_business_group_id
               and   pe.event_id = pb.event_id
               and   pe.event_or_interview in ('I','E')
               and   pe.date_start >= p_date_start);

Line: 2148

select rowid
from   per_vacancies pv
where (pv.business_group_id = p_business_group_id OR
     nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pv.recruiter_id      = p_person_id
and   pv.date_from        >= p_date_start;

Line: 2158

select rowid
from per_requisitions pr
where (pr.business_group_id = p_business_group_id OR
      nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
and   pr.person_id         = p_person_id
and   pr.date_from        >= p_date_start; -- added for bug 9304366

Line: 2168

select rowid
from per_absence_attendances paa
where  paa.business_group_id +0 = p_business_group_id
and    paa.person_id         = p_person_id
and    paa.date_start       >= p_date_start;

Line: 2177

select rowid
from per_assignment_budget_values_f pab
where pab.business_group_id = p_business_group_id and
exists (select 'budget_values exist'
               from per_all_assignments_f paf
               where  pab.business_group_id    +0= paf.business_group_id + 0
               and    paf.business_group_id    +0= p_business_group_id
               and    pab.assignment_id        = paf.assignment_id
               and    paf.person_id            = p_person_id
               and    paf.period_of_service_id = l_period_of_service_id
               and    paf.effective_end_date  >= p_date_start
	       --start bug 5987416
               --and    pab.effective_end_date  >= p_date_start
	       )
and    pab.effective_end_date  >= p_date_start;

Line: 2197

select rowid
from pay_personal_payment_methods ppm
where  ppm.business_group_id                  = p_business_group_id
and    exists (select 'exists'
               from per_all_assignments_f paf
               where paf.business_group_id    +0= p_business_group_id
               and   paf.person_id            = p_person_id
               and   paf.assignment_id        = ppm.assignment_id
               and   paf.period_of_service_id = l_period_of_service_id
	       --start bug 5987416
               -- and   ppm.effective_start_date>= p_date_start
	       --end bug 5987416
              )
and   ppm.effective_start_date               >= p_date_start;

Line: 2215

select rowid
from hr_comments h
where exists (select 'comments exist'
               from    per_all_assignments_f paf
               where  h.comment_id = paf.comment_id
               and   paf.business_group_id    +0= p_business_group_id
               and   paf.person_id            = p_person_id
               and   paf.period_of_service_id = l_period_of_service_id);

Line: 2228

select rowid, assignment_id, assignment_status_type_id -- Bug 3564129
from per_all_assignments_f paf
where paf.business_group_id    +0= p_business_group_id
and   paf.person_id            = p_person_id
and   paf.period_of_service_id = l_period_of_service_id;

Line: 2237

       select iass.assignment_status_id, iass.object_version_number
         from irc_assignment_statuses iass
        where iass.assignment_status_id =
              (select max(iass.assignment_status_id)
                 from irc_assignment_statuses iass
                where iass.assignment_id = l_assignment_id
                  and iass.assignment_status_type_id = l_asg_status_type_id);

Line: 2251

 select assignment_id,effective_start_date,ROWID
 from   per_all_assignments_f
 where  person_id = p_person_id
 and    business_group_id +0 = p_business_group_id
 and    period_of_service_id is NULL
 and    assignment_type in ('E','A')   -- 3194314
 and    effective_end_date = p_date_start - 1
 for update of effective_end_date;

Line: 2262

 select assignment_id,effective_start_date,ROWID
 from   per_all_assignments_f
 where  person_id = p_person_id
 and    business_group_id +0 = p_business_group_id
 and    period_of_service_id = l_period_of_service_id
 and    assignment_type in ('E');

Line: 2271

select paf.assignment_id, paf.effective_start_date
  from per_all_assignments_f paf
 where paf.person_id = p_person_id
   and paf.business_group_id +0 = p_business_group_id
   and    assignment_type not in ('B','O');                       --modified for bug #6449599 and bug # 7572514

Line: 2280

select rowid
from per_applications pap
where exists (select 'row exists'
 from   per_all_assignments_f paf
 where  paf.person_id = p_person_id
 and    paf.business_group_id +0 = p_business_group_id
 and    paf.period_of_service_id is NULL
 and    paf.effective_end_date = p_date_start - 1
 and    pap.application_id = paf.application_id);

Line: 2296

      select pps.rowid
      from   per_periods_of_service pps
      where  pps.person_id = p_person_id
      and period_of_service_id = l_period_of_service_id;

Line: 2304

select p.rowid, effective_end_date from per_people_f p
where  p.person_id = p_person_id
and   p.effective_start_date >= p_date_start;

Line: 2311

  select ptu.person_type_id
   from per_person_type_usages_f ptu
       ,per_person_types ppt
  where ptu.person_id = p_person_id
    and ptu.effective_start_date = p_date_start
    and ptu.person_type_id = ppt.person_type_id
    and ppt.system_person_type = 'EMP';

Line: 2325

  select ptu.person_type_id
   from per_person_type_usages_f ptu
       ,per_person_types ppt
  where ptu.person_id = p_person_id
    and ptu.effective_end_date = p_date_start - 1
    and ptu.person_type_id = ppt.person_type_id
    and ppt.system_person_type = 'APL';

Line: 2341

  select pp.period_of_placement_id, pp.date_start
    from per_periods_of_placement pp
   where pp.person_id = p_person_id
     and pp.actual_termination_date = cp_date_start - 1;

Line: 2355

select p.rowid from per_people_f p
where p.person_id = p_person_id
and   p.effective_end_date = p_date_start -1;

Line: 2361

select ppp.pay_proposal_id, ppp.object_version_number
from per_pay_proposals ppp
where p_assignment_id = ppp.assignment_id;

Line: 2366

  select element_entry_id
  from   pay_element_entries_f
  where  assignment_id = p_assignment_id
  and    creator_type = 'SP'
  and    p_start_date between
         effective_start_date and effective_end_date;

Line: 2375

  select element_entry_id
  from   pay_element_entries_f
  where  assignment_id = p_assignment_id
  and    creator_type = 'SP'
  and    (p_start_date - 1) between
         effective_start_date and effective_end_date;

Line: 2399

  select pps.period_of_service_id
  into   v_dummy
  from   per_periods_of_service pps
  where  p_start_date = pps.date_start
  and     pps.person_id = p_person_id;

Line: 2432

  update per_all_assignments_f paf
  set    paf.supervisor_id         = NULL
  where  paf.rowid     = p_rowid;

Line: 2453

  update per_all_assignments_f paf
  set    paf.recruiter_id          = NULL
  where  paf.rowid     = p_rowid;

Line: 2472

  delete from per_bookings pb
  where  pb.rowid = p_rowid;

Line: 2494

  update per_all_vacancies pv
  set    pv.recruiter_id      = NULL
  where  pv.rowid = p_rowid;

Line: 2513

  update per_requisitions pr
  set pr.person_id = NULL
  where  pr.rowid = p_rowid;

Line: 2532

  delete from per_absence_attendances paa
  where paa.rowid = p_rowid;

Line: 2549

  delete from per_assignment_budget_values_f pab
  where  pab.rowid = p_rowid;

Line: 2567

  delete from pay_personal_payment_methods ppm
  where  ppm.rowid = p_rowid;

Line: 2585

  delete from hr_comments h
  where  h.rowid = p_rowid;

Line: 2633

  delete from per_spinal_point_placements_f spp
  where spp.assignment_id = l_assignment_id;

Line: 2635

  delete from pay_cost_allocations_f pca
  where pca.assignment_id = l_assignment_id;

Line: 2642

     IRC_ASG_STATUS_API.delete_irc_asg_status
         (p_assignment_status_id  => l_asg_status_id
         ,p_object_version_number => l_asg_status_ovn);

Line: 2654

      hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id  => l_pk_id
                        ,p_business_group_id  => l_business_group_id
                        ,p_object_version_number => l_ovn
                        ,p_validate   => FALSE
                        ,p_salary_warning  =>  l_sal_warning);

Line: 2672

      hr_entry_api.delete_element_entry
        ('DELETE'
        ,p_start_date - 1
        ,l_element_entry_id);

Line: 2678

       hr_entry_api.delete_element_entry
        ('ZAP'
        ,p_start_date
        ,l_element_entry_id);

Line: 2690

  delete from per_all_assignments_f paf
  where paf.rowid = p_rowid;

Line: 2708

  update per_applications pap
  set pap.date_end = NULL
  where pap.rowid = p_rowid;

Line: 2727

  update per_all_assignments_f paf
  set   paf.effective_end_date   = p_end_of_time
  where paf.rowid = p_rowid;

Line: 2760

   delete from per_periods_of_service
   where rowid = p_rowid;

Line: 2806

        select person_type_id into l_person_type_id
        from per_person_types
        where business_group_id = p_business_group_id
        and system_person_type = 'EX_EMP'
        and default_flag = 'Y';

Line: 2815

                select person_type_id into l_person_type_id
                from per_person_types
                where business_group_id = p_business_group_id
                and system_person_type = 'EX_EMP_APL'
                and default_flag = 'Y';

Line: 2826

        update per_people_f
        set person_type_id = l_person_type_id,
            effective_start_date = p_date_start,  -- in case DT updates exist
            current_employee_flag = null,
            current_emp_or_apl_flag = l_apl_flag, -- 3848352 --null,
            current_applicant_flag = l_apl_flag -- 3848352 --null
        where rowid = p_rowid;

Line: 2843

        select person_type_id
        into l_person_type_id
        from per_person_types
        where business_group_id = p_business_group_id
        and system_person_type = 'EX_EMP'
        and default_flag = 'Y';

Line: 2857

        delete from per_people_f
        where rowid = p_rowid;

Line: 2873

       select person_type_id into l_person_type_id
       from per_person_types
       where business_group_id = p_business_group_id
       and system_person_type = 'APL'
       and default_flag = 'Y';

Line: 2888

       /*select person_type_id into l_person_type_id
       from per_person_types
       where business_group_id = p_business_group_id
       and system_person_type = 'OTHER'  -- EX_CWK but this is not maintained
       and default_flag = 'Y'; */

Line: 2902

       update per_people_f
       set person_type_id = l_person_type_id,
        current_employee_flag = null,
        current_emp_or_apl_flag = l_apl_flag, -- 3848352 -- null,
        current_applicant_flag = l_apl_flag, -- 3848352 --null,
        per_information7 = null,
        employee_number = null,
        start_date = l_cwk_date_start,
        effective_start_date = p_date_start, -- p_start_date,   -- in case DT updates exist
        original_date_of_hire = null
       where rowid = p_rowid;

Line: 2918

        delete from per_people_f
        where rowid = p_rowid;

Line: 2926

     delete from per_people_f
     where rowid = p_rowid;

Line: 2960

         update per_people_f
         set effective_end_date = p_end_of_time
         where rowid = p_rowid;

Line: 3015

         ,p_datetrack_delete_mode => 'ZAP'
         );*/

Line: 3037

         ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
         );

Line: 3078

          ,p_datetrack_delete_mode => 'DELETE_NEXT_CHANGE'
          );

Line: 3114

per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);

Line: 3140

select distinct assignment_id
from per_assignments_f
where application_id = p_application_id;

Line: 3148

select pe.event_id
from   per_events pe
where  pe.date_start        >= p_date_received
and    p_assignment_id     = pe.assignment_id;

Line: 3156

select pb.booking_id
from   per_events pe,
       per_bookings pb
where  pb.event_id           = pe.event_id
and    pe.date_start        >= p_date_received
and    pb.person_id          = p_person_id
and    pe.emp_or_apl         = 'A';

Line: 3167

select pab.rowid
from per_assignment_budget_values_f pab
where  pab.assignment_id        = p_assignment_id;

Line: 3174

select p.letter_request_line_id
from per_letter_request_lines p
where  p.assignment_id = p_assignment_id;

Line: 3181

select h.comment_id
from hr_comments h
,    per_assignments_f paf
where  h.comment_id = paf.comment_id
and paf.assignment_id = p_assignment_id;

Line: 3190

select paf.rowid
from per_assignments_f paf
where paf.person_id            = p_person_id
and   paf.application_id       = p_application_id;

Line: 3198

select rowid
from per_applications pap
where pap.date_received = p_date_received
and   pap.person_id = p_person_id;

Line: 3206

select rowid
from per_people_f
where person_id = p_person_id
and effective_start_date >= p_date_received;

Line: 3214

select rowid
from per_people_f
where person_id = p_person_id
and effective_end_date = p_date_received - 1;

Line: 3236

select sas.rowid
from per_secondary_ass_statuses sas
where sas.assignment_id = p_assignment_id;

Line: 3243

select paei.assignment_extra_info_id, paei.object_version_number
from per_assignment_extra_info paei
where p_assignment_id = paei.assignment_id;

Line: 3250

select ppp.pay_proposal_id, ppp.object_version_number
from per_pay_proposals ppp
where p_assignment_id = ppp.assignment_id;

Line: 3257

select pwi.incident_id, pwi.object_version_number
from per_work_incidents pwi
where p_assignment_id = pwi.assignment_id;

Line: 3264

select papa.rowid
from per_assign_proposal_answers papa
where p_assignment_id = papa.assignment_id;

Line: 3281

select distinct h.comment_id --fix for bug 7157204.
from hr_comments h, per_people_f ppf
where ppf.business_group_id = p_business_group_id
and ppf.person_id = p_person_id
and ppf.effective_start_date >= p_date_received
and ppf.comment_id = h.comment_id;

Line: 3294

select effective_start_date
from per_people_f
where person_id = p_person_id
and effective_start_date = hr_general.effective_date;

Line: 3302

select rowid,effective_end_date,effective_start_date,current_applicant_flag
from per_people_f
where person_id = p_person_id
and (effective_start_date > p_date_received
or effective_end_date = hr_general.effective_date - 1);

Line: 3313

  select count(ptu.person_type_id)
   from per_person_type_usages_f ptu
       ,per_person_types ppt
  where ptu.person_id = p_person_id
    and ptu.effective_start_date = p_date_received
    and ptu.person_type_id = ppt.person_type_id
    and ppt.system_person_type <> 'APL';

Line: 3324

select rowid,effective_end_date,effective_start_date,current_applicant_flag
from per_people_f
where person_id = p_person_id
and effective_start_date = p_date_received;

Line: 3332

select rowid,effective_end_date,effective_start_date,current_applicant_flag
from per_people_f
where person_id = p_person_id
and effective_start_date > p_date_received;

Line: 3340

select person_type_id
from per_person_type_usages_f
where person_id = p_person_id
order by EFFECTIVE_END_DATE desc, EFFECTIVE_START_DATE desc;

Line: 3347

select paf.assignment_id, paf.effective_start_date
  from per_all_assignments_f paf
 where paf.person_id = p_person_id
 and paf.assignment_type NOT IN ('B','O'); -- Modified for bug # 9151913

Line: 3357

  select person_type_id
  from per_person_type_usages_f ptu
  where ptu.person_id = p_person_id
  and ptu.effective_end_date = p_date_received - 1
  and exists (
          select ppt.person_type_id
          from per_person_types ppt
          where ppt.system_person_type = 'APL'
          and ppt.business_group_id = p_business_group_id
          and ppt.person_type_id = ptu.person_type_id)
  and exists (select ppt1.person_type_id
              from per_person_types ppt1, per_person_type_usages_f ptu1
              where ptu1.person_type_id = ppt1.person_type_id
               and ppt1.business_group_id = p_business_group_id
               and ptu1.person_id = p_person_id
               and ppt1.system_person_type in ('EMP','CWK')
               and ptu1.effective_start_date = p_date_received);

Line: 3382

    SELECT applicant_number
    FROM   per_all_people_f ppf
    WHERE  ppf.effective_start_date < p_date_received
    AND    ppf.person_id = p_person_id
    AND    ppf.business_group_id +0 = p_business_group_id
    ORDER BY ppf.effective_start_date DESC;

Line: 3430

      delete from per_events pe
      where  pe.event_id = l_pk_id;

Line: 3447

    delete from per_assignment_budget_values_f pab
    where  pab.rowid = l_rowid;

Line: 3464

    delete from hr_comments h
    where  h.comment_id = l_pk_id;

Line: 3481

      delete from per_letter_request_lines plrl
      where plrl.letter_request_line_id = l_pk_id;

Line: 3498

      per_secondary_ass_statuses_pkg.delete_row(l_rowid);

Line: 3508

      hr_assignment_extra_info_api.delete_assignment_extra_info(
                                  p_validate => FALSE
                                 ,p_assignment_extra_info_id => l_pk_id
                                 ,p_object_version_number => l_ovn);

Line: 3521

      hr_maintain_proposal_api.delete_salary_proposal(p_pay_proposal_id  => l_pk_id
                        ,p_business_group_id  => l_business_group_id
                        ,p_object_version_number => l_ovn
                        ,p_validate   => FALSE
                        ,p_salary_warning  =>  l_sal_warning);

Line: 3535

      per_work_incident_api.delete_work_incident(
                                p_validate  => FALSE
                               ,p_incident_id => l_pk_id
                               ,p_object_version_number => l_ovn);

Line: 3548

      delete from per_assign_proposal_answers papa
      where papa.rowid = l_rowid;

Line: 3589

      delete from per_bookings pb
      where  pb.booking_id = l_pk_id;

Line: 3606

      delete from hr_comments h
      where h.comment_id = l_pk_id;

Line: 3625

    delete from per_assignments_f paf
    where paf.rowid = l_rowid;

Line: 3642

    delete from  per_applications pap
    where pap.rowid = l_rowid;

Line: 3672

    delete from per_people_f
    where rowid = l_rowid;

Line: 3695

    update per_people_f
    set effective_end_date = p_end_of_time
    where rowid = l_rowid;

Line: 3739

	-- Get the latest person Type Id to update the person table.
		 --commented for bug 8405711
		 /*open latest_ptid;

Line: 3745

		 /*update per_people_f
		 set PERSON_TYPE_ID=l_person_type_id
		 where rowid = l_rowid;*/

Line: 3749

	    delete from per_people_f
	    where rowid = l_rowid;

Line: 3774

         delete from per_people_f
	    where rowid = l_rowid;

Line: 3797

      delete from per_people_f
      where rowid = l_rowid;

Line: 3808

      update per_people_f
      set applicant_number = l_apl_number,  --null, -- commented for fix of Bug#14156610
          current_applicant_flag=null,
          current_emp_or_apl_flag=DECODE (l_system_person_type,'EMP','Y',null),
          PERSON_TYPE_ID=l_person_type_id,
          effective_end_date=hr_general.end_of_time
      where rowid = l_rowid;

Line: 3825

      update per_people_f
      set applicant_number = null,
          current_applicant_flag=null,
          current_emp_or_apl_flag=DECODE (l_system_person_type,'EMP','Y',null),
          PERSON_TYPE_ID=l_person_type_id
      where rowid = l_rowid;

Line: 3833

      update per_people_f
      set applicant_number = null,
          current_applicant_flag=null,
          current_emp_or_apl_flag=DECODE (l_system_person_type,'EMP','Y',null),
          PERSON_TYPE_ID=l_person_type_id,
          effective_end_date=hr_general.end_of_time
      where rowid = l_rowid;

Line: 3871

      hr_per_type_usage_internal.delete_person_type_usage
                  (p_validate  => FALSE
                  ,p_person_type_usage_id  => l_pk_id
                  ,p_effective_date  => p_date_received
                  ,p_datetrack_mode  => 'ZAP'
                  ,p_object_version_number  => l_ovn
                  ,p_effective_start_date  => l_start_date
                  ,p_effective_end_date  => l_end_date );

Line: 3907

    update per_person_type_usages_f ptu
    set person_type_id = hr_person_type_usage_info.get_default_person_type_id
                         (p_business_group_id, 'EX_APL')
    where ptu.person_id = p_person_id
    and ptu.effective_start_date = p_date_received
    and person_type_id in (
          select ppt.person_type_id
          from per_person_types ppt
          where business_group_id = p_business_group_id
          and ppt.system_person_type = 'APL');

Line: 3922

    delete from per_person_type_usages_f ptu
    where ptu.person_id = p_person_id
    and ptu.effective_start_date >= p_date_received
    and ptu.person_type_id in (
          select ppt.person_type_id
          from per_person_types ppt
          where ppt.system_person_type = 'APL'
	  and business_group_id = p_business_group_id);

Line: 3932

    update per_person_type_usages_f ptu
    set effective_end_date = to_date('31/12/4712','dd/mm/yyyy')
    where ptu.person_id = p_person_id
    and ptu.person_type_id in (
          select ppt.person_type_id
          from per_person_types ppt
          where ppt.system_person_type = 'EX_APL'
	  and business_group_id = p_business_group_id)
    and ptu.effective_end_date = p_date_received - 1;

Line: 3948

per_cancel_hire_or_apl_pkg.update_person_list (p_person_id => p_person_id);

Line: 3960

procedure update_person_list (p_person_id NUMBER) is
begin
--
-- Delete all rows from per_person_list
--
  hr_security_internal.clear_from_person_list(p_person_id);

Line: 3967

end update_person_list;

Line: 4001

    SELECT assignment_id
    FROM   per_assignments_f paf
    WHERE  (paf.business_group_id = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    paf.person_id = p_person_id;

Line: 4010

    SELECT per.person_id,
           per.effective_start_date
    FROM   per_people_f per
    WHERE  per.person_id             = p_person_id
    AND    per.current_npw_flag      = 'Y'
    AND    per.effective_start_date >= p_date_start;

Line: 4020

    SELECT ROWID
    FROM   per_assignments_f p
    WHERE  (p.business_group_id = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    p.supervisor_id      = p_person_id
  --start changes for bug 13014331
    and p.effective_end_date >= p_date_start
    and p.assignment_type in ('E','C')
    and not exists (
    select 1
    from per_assignment_status_types past
    where past.assignment_status_type_id = p.assignment_status_type_id
    and past.per_system_status = 'TERM_ASSIGN'
    and (past.business_group_id = p.business_group_id
         or past.business_group_id is NULL));

Line: 4041

    SELECT ROWID
    FROM   per_assignments_f p
    WHERE  p.recruiter_id       = p_person_id
    AND    (p.business_group_id = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');

Line: 4050

    SELECT 'Events exist'
    FROM   per_events pe,
           per_bookings pb
    WHERE  pe.business_group_id = pb.business_group_id
    AND    (pb.business_group_id = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    pe.event_id           = pb.event_id
    AND    pe.event_or_interview = p_type
    AND    pb.person_id          = p_person_id;

Line: 4063

    SELECT 'Interviews exist'
    FROM   per_events pe
    WHERE  (pe.business_group_id  = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    pe.event_or_interview = 'I'
    AND    pe.internal_contact_person_id = p_person_id;

Line: 4073

    SELECT ROWID
    FROM   per_vacancies pv
    WHERE (pv.business_group_id  = p_business_group_id OR
           NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND   pv.recruiter_id        = p_person_id
    AND   pv.date_from          >= l_effective_date;

Line: 4083

    SELECT ROWID
    FROM   per_requisitions pr
    WHERE (pr.business_group_id = p_business_group_id OR
           NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    pr.person_id         = p_person_id;

Line: 4092

    SELECT ROWID
    FROM per_assignment_budget_values_f pab
    WHERE pab.business_group_id = p_business_group_id and
    EXISTS (SELECT 'budget_values exist'
                   FROM   per_all_assignments_f paf
                   WHERE  pab.business_group_id    +0= paf.business_group_id + 0
                   AND    paf.business_group_id    +0= p_business_group_id
                   AND    pab.assignment_id        = paf.assignment_id
                   AND    paf.person_id            = p_person_id
                   AND    paf.period_of_placement_date_start = p_date_start
                   AND    paf.effective_end_date  >= l_effective_date
		   --START for 5987416
                   --AND    pab.effective_end_date  >= l_effective_date
		   )
     AND    pab.effective_end_date  >= l_effective_date;

Line: 4113

    SELECT ROWID
    FROM pay_personal_payment_methods ppm
    WHERE  ppm.business_group_id                  = p_business_group_id
    AND    EXISTS (SELECT 'exists'
                   FROM per_all_assignments_f paf
                   WHERE paf.business_group_id    +0= p_business_group_id
                   AND   paf.person_id            = p_person_id
                   AND   paf.assignment_id        = ppm.assignment_id
                   AND   paf.period_of_placement_date_start = p_date_start
		   --start bug 5987416
                   --AND   ppm.effective_start_date>= l_effective_date
		   --end bug 5987416
		   )
    AND   ppm.effective_start_date               >= l_effective_date;

Line: 4129

    SELECT ptu.person_type_usage_id
    FROM   per_person_types pt,
           per_person_type_usages_f ptu
    WHERE  pt.business_group_id     = p_business_group_id
    AND    pt.person_type_id        = ptu.person_type_id
    AND    l_effective_date BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date
    AND    ptu.person_id            = p_person_id
    AND    pt.system_person_type = 'CWK';

Line: 4141

   select 'exists'
   from per_applications pa
   where pa.business_group_id = business_group_id
    and pa.person_id = p_person_id
    and pa.date_received >= p_date_start
    and nvl(pa.application_id,0) <> nvl(
	(select application_id
	 from per_applications
	 where business_group_id = p_business_group_id
	 and person_id = p_person_id
	 and p_date_start - 1 between date_received and nvl(date_end,to_date('31/12/4712','dd/mm/yyyy'))
	 ),pa.application_id) ;

Line: 4504

    SELECT ROWID
    FROM   per_bookings pb
    WHERE (pb.business_group_id = p_business_group_id OR
           NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    pb.person_id = p_person_id
    AND    EXISTS (SELECT 'row exists'
                   FROM   per_events pe
                   WHERE  pe.business_group_id + 0 = p_business_group_id
                   AND    pe.event_id = pb.event_id
                   AND    pe.event_or_interview in ('I','E')
                   AND    pe.date_start >= l_date_start);

Line: 4519

    SELECT ROWID
    FROM per_requisitions pr
    WHERE (pr.business_group_id = p_business_group_id OR
           NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND   pr.person_id         = p_person_id;

Line: 4528

    SELECT ROWID
    FROM per_assignment_budget_values_f pab
    WHERE pab.business_group_id = p_business_group_id and
    EXISTS (SELECT 'budget_values exist'
                   FROM   per_all_assignments_f paf
                   WHERE  pab.business_group_id    +0= paf.business_group_id + 0
                   AND    paf.business_group_id    +0= p_business_group_id
                   AND    pab.assignment_id        = paf.assignment_id
                   AND    paf.person_id            = p_person_id
                   AND    paf.period_of_placement_date_start = p_date_start
                   AND    paf.effective_end_date  >= l_effective_date
		   --start for 5987416
                   --AND    pab.effective_end_date  >= l_effective_date
		   )
    AND    pab.effective_end_date  >= l_effective_date;

Line: 4548

    SELECT ROWID
    FROM pay_personal_payment_methods ppm
    WHERE  ppm.business_group_id                  = p_business_group_id
    AND    EXISTS (SELECT 'exists'
                   FROM per_all_assignments_f paf
                   WHERE paf.business_group_id    +0= p_business_group_id
                   AND   paf.person_id            = p_person_id
                   AND   paf.assignment_id        = ppm.assignment_id
                   AND   paf.period_of_placement_date_start = p_date_start
		   --start bug 5987416
                   --AND   ppm.effective_start_date>= l_effective_date
		   --end bug 5987416
		   )
    AND   ppm.effective_start_date               >= l_effective_date;

Line: 4566

    SELECT ROWID
    FROM   per_assignments_f p
    WHERE  (p.business_group_id = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    p.supervisor_id = p_person_id
  --start changes for bug 13014331
    and p.effective_end_date >= p_date_start
    and p.assignment_type in ('E','C')
    and not exists (
    select 1
    from per_assignment_status_types past
    where past.assignment_status_type_id = p.assignment_status_type_id
    and past.per_system_status = 'TERM_ASSIGN'
    and (past.business_group_id = p.business_group_id
         or past.business_group_id is NULL));

Line: 4587

    SELECT ROWID
    FROM   per_assignments_f p
    WHERE  p.recruiter_id = p_person_id
    AND    (p.business_group_id = p_business_group_id OR
            NVL(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y');

Line: 4596

    SELECT ROWID
    FROM   per_vacancies pv
    WHERE (pv.business_group_id = p_business_group_id OR
           nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N')='Y')
    AND    pv.recruiter_id      = p_person_id
    AND    pv.date_from        >= p_date_start;

Line: 4606

    SELECT ROWID
    FROM hr_comments h
    WHERE EXISTS (SELECT 'comments exist'
                  FROM   per_all_assignments_f paf
                  WHERE  h.comment_id = paf.comment_id
                  AND    paf.business_group_id +0 = p_business_group_id
                  AND    paf.person_id            = p_person_id
                  AND    paf.period_of_placement_date_start = p_date_start);

Line: 4620

    SELECT ROWID,
           assignment_id
    FROM   per_all_assignments_f paf
    WHERE  paf.business_group_id +0 = p_business_group_id
    AND    paf.person_id            = p_person_id
    AND    paf.period_of_placement_date_start = p_date_start;

Line: 4630

    SELECT assignment_id,
           effective_start_date,
           ROWID
    FROM   per_all_assignments_f
    WHERE  person_id            = p_person_id
    AND    business_group_id +0 = p_business_group_id
    AND    effective_end_date   = p_date_start - 1
    AND    assignment_type      = 'C'   -- 3194314
    FOR UPDATE OF effective_end_date;

Line: 4643

    SELECT ROWID
    FROM per_applications pap
    WHERE EXISTS (SELECT 'row exists'
                  FROM   per_all_assignments_f paf
                  WHERE  paf.person_id            = p_person_id
                  AND    paf.business_group_id +0 = p_business_group_id
                  AND    paf.effective_end_date   = p_date_start - 1
                  AND    pap.application_id       = paf.application_id);

Line: 4655

    SELECT p.rowid,
           effective_end_date
    FROM   per_people_f p
    WHERE  p.person_id            = p_person_id
    AND    p.effective_start_date >= p_date_start;

Line: 4664

    SELECT p.rowid
    FROM   per_people_f p
    WHERE  p.person_id          = p_person_id
    AND    p.effective_end_date = p_date_start -1;

Line: 4670

    SELECT pgr.rowid
    FROM   pay_grade_rules_f pgr
    WHERE  EXISTS (SELECT 'X'
                   FROM   per_assignments_f paf
                   WHERE  pgr.grade_or_spinal_point_id = paf.assignment_id
                   and    paf.business_group_id + 0    = p_business_group_id
                   AND    paf.person_id                = p_person_id);

Line: 4679

    SELECT pp.rowid
    FROM   per_periods_of_placement pp
    WHERE  pp.person_id  = p_person_id
    AND    pp.date_start = p_date_start;

Line: 4685

    SELECT pp.period_of_placement_id
    FROM   per_periods_of_placement pp
    WHERE  pp.person_id  = p_person_id
    AND    pp.actual_termination_date = p_date_start -1;

Line: 4691

    SELECT ps.period_of_service_id
    FROM   per_periods_of_service ps
    WHERE  ps.person_id  = p_person_id
    AND    ps.actual_termination_date = p_date_start -1;

Line: 4699

  SELECT pop.date_start
    FROM per_periods_of_placement pop
  WHERE pop.person_id = p_person_id
    AND p_effective_date between pop.date_start and
    nvl(pop.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));

Line: 4708

    SELECT person_type_id
    FROM   per_person_types
    WHERE  business_group_id = p_business_group_id
    AND    system_person_type = p_system_person_type
    AND    default_flag = 'Y';

Line: 4719

    SELECT pt.person_type_id
    FROM   per_person_types pt, per_person_type_usages_f ptu
    WHERE  pt.person_type_id = ptu.person_type_id
    AND    pt.business_group_id = p_business_group_id
    AND    pt.system_person_type = p_sys_person_type
    AND    ptu.person_id = p_person_id
    AND    p_effective_date BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date;

Line: 4734

    SELECT ptu.person_type_usage_id,
           ptu.object_version_number
    FROM   per_person_type_usages_f ptu,
           per_person_types pt
    WHERE  ptu.person_id = p_person_id
    AND    pt.person_type_id = ptu.person_type_Id
    AND    pt.system_person_type = 'CWK'
    AND    p_effective_date BETWEEN ptu.effective_start_date
                                AND ptu.effective_end_date;

Line: 4747

    SELECT pt.system_person_type
    FROM   per_person_type_usages_f ptu,
           per_person_types pt
    WHERE  ptu.person_id = p_person_id
    AND    pt.person_type_id = ptu.person_type_Id
    AND    pt.system_person_type in ('EX_APL','APL')
    --AND    p_effective_date =ptu.effective_start_date;  -- Commented for Bug#14166808

Line: 4760

    SELECT ptu.person_type_usage_id
    FROM   per_person_type_usages_f ptu,
           per_person_types pt
    WHERE  ptu.person_id = p_person_id
    AND    pt.person_type_id = ptu.person_type_Id
    AND    pt.system_person_type = 'EX_APL'
    AND    p_effective_date =ptu.effective_start_date;*/

Line: 4776

    SELECT npw_number
    FROM   per_all_people_f ppf
    --WHERE  ppf.effective_start_date < p_effective_date  -- Commented for Bug#14166808
    WHERE  ppf.effective_start_date < p_date_start        -- Added for Bug#14166808
    AND    ppf.person_id = p_person_id
    AND    ppf.business_group_id +0 = p_business_group_id
    ORDER BY ppf.effective_start_date DESC;

Line: 4787

  select paf.assignment_id, paf.effective_start_date
    from per_all_assignments_f paf
   where paf.person_id = p_person_id
     and paf.business_group_id +0 = p_business_group_id
     and paf.assignment_type NOT IN ('B','O'); -- Modified for bug # 9151913

Line: 4809

    UPDATE per_all_assignments_f paf
    SET    paf.supervisor_id = NULL
    WHERE  paf.rowid         = supervisor_rec.rowid;

Line: 4830

    UPDATE per_all_assignments_f paf
    SET    paf.recruiter_id  = NULL
    WHERE  paf.rowid         = recruiter_rec.rowid;

Line: 4851

    UPDATE per_all_vacancies pv
    SET    pv.recruiter_id = NULL
    WHERE  pv.rowid        = vacancies_rec.rowid;

Line: 4872

    DELETE from pay_grade_rules_f pgr
    WHERE  pgr.rowid = assignment_rate_rec.rowid;

Line: 4883

    DELETE FROM hr_comments h
    WHERE  h.rowid = comments_rec.rowid;

Line: 4903

    UPDATE per_requisitions pr
    SET    pr.person_id = NULL
    WHERE  pr.rowid = requisition_rec.rowid;

Line: 4924

    DELETE FROM per_bookings pb
    WHERE  pb.rowid = events_rec.rowid;

Line: 4944

    DELETE FROM per_assignment_budget_values_f pab
    WHERE  pab.rowid = budget_rec.rowid;

Line: 4964

    DELETE FROM pay_personal_payment_methods ppm
    WHERE  ppm.rowid = payment_rec.rowid;

Line: 4984

    DELETE FROM per_spinal_point_placements_f spp
    WHERE spp.assignment_id = assignment1_rec.assignment_id;

Line: 4989

    DELETE FROM pay_cost_allocations_f pca
    WHERE pca.assignment_id = assignment1_rec.assignment_id;

Line: 4994

    DELETE FROM per_all_assignments_f paf
    WHERE paf.rowid = assignment1_rec.rowid;

Line: 5029

        UPDATE per_applications pap
        SET    pap.date_end = NULL
        WHERE  pap.rowid = applicantions_rec.rowid;

Line: 5070

    DELETE FROM per_periods_of_placement
    WHERE rowid = l_rowid;

Line: 5155

      UPDATE per_all_assignments_f paf
      SET   paf.effective_end_date = l_end_of_time
      WHERE paf.rowid = assignment2_rec.rowid;

Line: 5191

        UPDATE per_person_type_usages_f
        SET    person_type_id = l_person_type_id
        WHERE  person_id      = p_person_id
        AND    person_type_id= l_person_type_id1
        AND    p_date_start BETWEEN effective_start_date
                                AND effective_end_date;

Line: 5199

         UPDATE per_people_f   -- 3194314
            SET current_npw_flag = null
               ,effective_start_date = p_date_start   -- in case DT udpates exist
         WHERE rowid = person_rec.rowid;

Line: 5208

        DELETE FROM per_people_f
        WHERE rowid = person_rec.rowid;

Line: 5231

         UPDATE per_people_f
            SET npw_number = l_npw_number, --null  -- Modified for bug#13772471
                effective_start_date = p_date_start,  -- in case DT updates exist
                current_npw_flag = null,
                per_information7 = 'INCL'
         WHERE  rowid = person_rec.rowid;

Line: 5243

        DELETE FROM per_people_f
        WHERE rowid = person_rec.rowid;

Line: 5277

         UPDATE per_people_f
            SET npw_number = l_npw_number, --null  -- Modified for bug#13772471
                effective_start_date = p_date_start,  -- in case DT updates exist
                current_npw_flag = null
         WHERE  rowid = person_rec.rowid;

Line: 5286

	    DELETE FROM per_people_f
  	    WHERE rowid = person_rec.rowid;

Line: 5354

      UPDATE per_people_f
      SET    effective_end_date = l_end_of_time
      WHERE  rowid = new_person_rec.rowid;

Line: 5419

      hr_per_type_usage_internal.delete_person_type_usage
        (p_validate              => FALSE
        ,p_person_type_usage_id  => l_person_type_usage_id
        ,p_effective_date        => p_effective_date
        ,p_datetrack_mode        => hr_api.g_zap
        ,p_object_version_number => l_object_version_number
        ,p_effective_start_date  => l_effective_start_date
        ,p_effective_end_date    => l_effective_end_date);

Line: 5480

      hr_per_type_usage_internal.delete_person_type_usage
        (p_validate              => FALSE
        ,p_person_type_usage_id  => l_person_type_usage_id
        ,p_effective_date        => p_effective_date
        ,p_datetrack_mode        => hr_api.g_zap
        ,p_object_version_number => l_object_version_number
        ,p_effective_start_date  => l_effective_start_date
        ,p_effective_end_date    => l_effective_end_date);

Line: 5514

      hr_per_type_usage_internal.delete_person_type_usage
        (p_validate              => FALSE
        ,p_person_type_usage_id  => l_person_type_usage_id
        ,p_effective_date        => p_effective_date
        ,p_datetrack_mode        => hr_api.g_zap
        ,p_object_version_number => l_object_version_number
        ,p_effective_start_date  => l_effective_start_date
        ,p_effective_end_date    => l_effective_end_date);

Line: 5528

  per_cancel_hire_or_apl_pkg.update_person_list(p_person_id => p_person_id);

Line: 5555

    SELECT pbg.legislation_code
      FROM per_business_groups pbg
         , per_people_f per
     WHERE per.person_id = p_person_id
       AND pbg.business_group_id = per.business_group_id;