APPS.HR_PERSON_DELETE SQL Statements

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

Line: 247

      select    'Y',
        fpi.status
      into    p_yes_no,
        p_status
      from    fnd_product_installations    fpi
      where    fpi.status = 'I'
      and    fpi.application_id =
        (select    fa.application_id
         from    fnd_application        fa
          where    fa.application_short_name = P_APPLICATION_SHORT_NAME
        );

Line: 280

    select    count(*)
    into    v_dummy
    from    per_all_people_f    p
    where    p.person_id    = P_PERSON_ID;

Line: 300

      Purge actions. If there are then raise an error and disallow delete.
  */
  --
  PROCEDURE pay_predel_validation (p_person_id    number)
  IS
  --
  v_delete_permitted    varchar2(1);

Line: 311

    select    'Y'
    into    v_delete_permitted
    from    sys.dual
    where    not exists (
        select    null
              from    pay_assignment_actions    paa,
            per_assignments_f    ass,
            pay_payroll_actions    ppa
        where    paa.assignment_id    = ass.assignment_id
        and    ass.person_id        = P_PERSON_ID
        and    ppa.payroll_action_id    = paa.payroll_action_id
        and    ppa.action_type        <> 'Z');

Line: 349

     ben_person_delete.check_ben_rows_before_delete(p_person_id
                                              ,p_effective_date);

Line: 366

  v_delete_permitted    varchar2(1);

Line: 371

    select 'Y'
    into    v_delete_permitted
    from    sys.dual
    where    not exists (
        select    null
        from    fnd_user    aol
        where     aol.employee_id    = P_PERSON_ID
        );

Line: 400

  v_delete_permitted    varchar2(1);

Line: 403

    select    'Y'
    into    v_delete_permitted
    from    sys.dual
    where    not exists (
        select    null
        from    per_assignments_f        ass,
            hr_assignment_set_amendments    asa
        where    asa.assignment_id    = ass.assignment_id
        and    ass.person_id        = P_PERSON_ID
        and    asa.include_or_exclude    = 'I'
        and    not exists (
            select    null
            from    hr_assignment_set_amendments    asa2
            where    asa2.assignment_set_id    = asa.assignment_set_id
            and    asa2.assignment_id    <> asa.assignment_id)
        );

Line: 432

      Check that for any element entries that are about to be deleted, the
      element type is not closed for the duration of that entry. Also check
      that if the assignment is to a payroll, the payroll period is not closed.
      If any of these 2 checks fail, the delete is disallowed.
  */
  --
  PROCEDURE closed_element_entry_check (p_person_id    IN number,
                    p_session_date    IN date)
  IS
  --
  cursor THIS_PERSONS_ELEMENT_ENTRIES is
    select    l.element_type_id,
        e.effective_start_date,
        e.effective_end_date,
        a.assignment_id
    from    pay_element_entries_f    e,
        per_assignments_f    a,
        pay_element_links_f    l
    where    a.person_id        = P_PERSON_ID
    and    a.assignment_id        = e.assignment_id
    and    e.effective_start_date between
            a.effective_start_date and a.effective_end_date
    and    e.element_link_id    = l.element_link_id
    and    e.effective_start_date between
            l.effective_start_date and l.effective_end_date;

Line: 480

      Searches for any contacts of the person being deleted who have
      COBRA Coverage Enrollments which are as a result of the Persons
      Assignments.
  */
  --
  PROCEDURE contact_cobra_validation (p_person_id    number)
  IS
  --
  v_delete_permitted    varchar2(1);

Line: 493

    select    'Y'
    into    v_delete_permitted
    from    sys.dual
    where    not exists (
        select    null
        from    per_assignments_f a
        ,       per_contact_relationships c
        ,       per_cobra_cov_enrollments e
                where   a.person_id = P_PERSON_ID
        and     a.assignment_id = e.assignment_id
        and     c.person_id = P_PERSON_ID
        and     c.contact_relationship_id = e.contact_relationship_id);

Line: 525

  v_delete_permitted    varchar2(1);

Line: 535

     select   null
     into v_delete_permitted
     from     sys.dual
     where not exists(select   null
                  from     per_contracts_f
                  where    person_id = p_person_id);

Line: 555

      Validates whether a person can be deleted from the HR database.
      This is the weak validation performed prior to delete using the
      Delete Person form.
  */
  --
  PROCEDURE weak_predel_validation (p_person_id        IN number,
                                    p_session_date    IN date,
                                    p_dt_delete_mode    IN varchar2) -- 4169275
  IS
  --
  -- DECLARE THE LOCAL VARIABLES
  --
  v_pay_installed    varchar2(1);

Line: 572

  v_delete_permitted    varchar2(1);

Line: 577

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',1);

Line: 579

    hr_person_delete.person_existance_check(P_PERSON_ID);

Line: 581

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',2);

Line: 583

    hr_person_delete.product_installed('PAY', v_pay_status,
    v_pay_installed, v_oracle_id);

Line: 586

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',4);

Line: 592

    if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
       hr_person_delete.aol_predel_validation(P_PERSON_ID);

Line: 597

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',5);

Line: 599

    hr_person_delete.assignment_set_check(P_PERSON_ID);

Line: 601

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',6);

Line: 604

       hr_person_delete.pay_predel_validation(P_PERSON_ID);

Line: 611

       hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);

Line: 613

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',8);

Line: 615

    hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);

Line: 617

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',9);

Line: 619

    hr_person_delete.contact_cobra_validation(P_PERSON_ID);

Line: 621

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',10);

Line: 623

    hr_person_delete.contracts_check(P_PERSON_ID);

Line: 625

    hr_utility.set_location('HR_PERSON_DELETE.WEAK_PREDEL_VALIDATION',11);

Line: 637

      Moderate pre-delete validation called from the Stong_predel_validation
      procedure and HR API's.

*/
  PROCEDURE moderate_predel_validation (p_person_id IN number,
                                      p_session_date IN date,
                                      p_dt_delete_mode    IN varchar2) -- 4169275

  IS
  v_delete_permitted    varchar2(1);

Line: 652

     SELECT        ext_chg_evt_log_id
     FROM          ben_ext_chg_evt_log
     WHERE         person_id = p_person_id
     FOR UPDATE OF ext_chg_evt_log_id;

Line: 662

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 1);

Line: 664

     hr_person_delete.person_existance_check(P_PERSON_ID);

Line: 666

     hr_person_delete.assignment_set_check(P_PERSON_ID);

Line: 673

     if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
        hr_person_delete.aol_predel_validation(P_PERSON_ID);

Line: 679

     hr_person_delete.pay_predel_validation(P_PERSON_ID);

Line: 681

     hr_person_delete.ben_predel_validation(P_PERSON_ID,p_session_date);

Line: 683

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 2);

Line: 688

	 -- Select statement modified to improve performance.

          select    'Y'
	          into    v_delete_permitted
	          from    sys.dual
	          where    not exists (
	          select    null
	          from    per_letter_request_lines r
	          where    r.person_id    = P_PERSON_ID
	                  and     r.date_from >= P_SESSION_DATE );

Line: 699

	  select    'Y'
	  into    v_delete_permitted
	  from    sys.dual
	  where    not exists (
	        select    null
	        from    per_letter_request_lines r
	         where exists (
	         	select  null
	                from    per_assignments_f a
	                where   a.person_id     = P_PERSON_ID
	                and     a.effective_start_date >= P_SESSION_DATE
	                and     a.assignment_id = r.assignment_id));

Line: 713

        /* select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_letter_request_lines r
        where    r.person_id    = P_PERSON_ID
                and     r.date_from >= P_SESSION_DATE
                or    exists (
                        select  null
                        from    per_assignments_f a
                        where   a.person_id     = P_PERSON_ID
                        and     a.effective_start_date >= P_SESSION_DATE
                        and     a.assignment_id = r.assignment_id));*/

Line: 735

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 3);

Line: 738

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_contact_relationships r
        where    r.person_id        = P_PERSON_ID
        or    r.contact_person_id    = P_PERSON_ID);

Line: 752

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 5);

Line: 755

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_events e
        where    e.internal_contact_person_id = P_PERSON_ID);

Line: 768

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 6);

Line: 771

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_bookings b
        where    b.person_id         = P_PERSON_ID);

Line: 784

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 7);

Line: 788

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    1 >= (
        select    count(*)
        from    per_assignments_f a
        where    a.person_id         = P_PERSON_ID
                and     a.effective_start_date > P_SESSION_DATE);-- fix for bug 4238025

Line: 802

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 8);

Line: 805

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_assignments_f a
        where    a.recruiter_id        = P_PERSON_ID
        or    a.supervisor_id        = P_PERSON_ID);

Line: 819

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 9);

Line: 822

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_periods_of_service    p
        where    p.termination_accepted_person_id = P_PERSON_ID);

Line: 835

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 10);

Line: 838

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_person_analyses a
        where    a.person_id         = P_PERSON_ID);

Line: 851

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 11);

Line: 854

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_absence_attendances a
        where    a.person_id         = P_PERSON_ID);

Line: 867

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 12);

Line: 870

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_absence_attendances a
        where    a.authorising_person_id        = P_PERSON_ID
        or    a.replacement_person_id        = P_PERSON_ID);

Line: 884

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 13);

Line: 887

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_recruitment_activities r
        where    r.authorising_person_id        = P_PERSON_ID
        or    r.internal_contact_person_id    = P_PERSON_ID);

Line: 901

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);

Line: 903

        select 'Y'
        into    v_delete_permitted
        from    sys.dual
        where   not exists (
                select  null
                from    per_appraisals apr
                where   apr.appraisee_person_id = P_PERSON_ID
                   or   apr.appraiser_person_id = P_PERSON_ID);

Line: 917

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION',13);

Line: 919

        select 'Y'
        into    v_delete_permitted
        from    sys.dual
        where   not exists (
                select  null
                from    per_participants par
                where   par.person_id = P_PERSON_ID);

Line: 932

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 14);

Line: 935

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_requisitions r
        where    r.person_id         = P_PERSON_ID);

Line: 948

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 15);

Line: 951

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_vacancies v
        where    v.recruiter_id         = P_PERSON_ID);

Line: 964

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 16);

Line: 969

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    pay_element_entries_f    e,
            per_assignments_f    a,
            pay_element_links_f    l
        where    a.person_id         = P_PERSON_ID
        and    a.assignment_id        = e.assignment_id
        and    e.element_link_id    = l.element_link_id
        and    l.standard_link_flag    = 'N');

Line: 987

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 17);

Line: 993

        select  'Y'
        into    v_delete_permitted
        from    sys.dual
        where   not exists (
                select  null
                from    pay_element_entries_f   e,
            per_assignments_f       a
        where   a.person_id             = P_PERSON_ID
                and     a.assignment_id         = e.assignment_id
        and    e.entry_type        <> 'E');

Line: 1009

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 171);

Line: 1013

     hr_person_delete.closed_element_entry_check(P_PERSON_ID, P_SESSION_DATE);

Line: 1015

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 18);

Line: 1018

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_assignment_extra_info i
        where    exists (
            select    null
            from    per_assignments_f a
            where    a.person_id    = P_PERSON_ID
            and    a.assignment_id    = i.assignment_id));

Line: 1035

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 19);

Line: 1038

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_secondary_ass_statuses s
        where    exists (
            select    null
            from    per_assignments_f a
            where    a.person_id    = P_PERSON_ID
            and    a.assignment_id    = s.assignment_id));

Line: 1056

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 20);

Line: 1059

         select    'Y'
         into    v_delete_permitted
         from    sys.dual
         where    not exists (
        select    null
        from    per_events    e
        where    exists (
            select    null
            from    per_assignments_f a
            where    a.person_id    = P_PERSON_ID
            and    a.assignment_id    = e.assignment_id));

Line: 1076

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 21);

Line: 1079

        select  'Y'
        into    v_delete_permitted
        from    sys.dual
        where   not exists (
                select  null
        from    per_spinal_point_placements_f    p
        where    exists  (
                        select  null
                        from    per_assignments_f a
                        where   a.person_id     = P_PERSON_ID
                        and     a.assignment_id = p.assignment_id));

Line: 1096

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 22);

Line: 1099

        select  'Y'
        into    v_delete_permitted
        from    sys.dual
        where   not exists (
                select  null
                from    per_quickpaint_result_text t
                where   exists  (
                        select  null
                        from    per_assignments_f a
                        where   a.person_id     = P_PERSON_ID
                        and     a.assignment_id = t.assignment_id));

Line: 1116

     hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 26);

Line: 1119

        select  'Y'
        into    v_delete_permitted
        from    sys.dual
        where   not exists (
                select  null
                from    per_cobra_cov_enrollments c
        where   exists  (
                        select  null
                        from    per_assignments_f a
                        where   a.person_id     = P_PERSON_ID
                        and     a.assignment_id = c.assignment_id));

Line: 1136

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 27);

Line: 1138

    hr_person_delete.contact_cobra_validation(P_PERSON_ID);

Line: 1140

    hr_person_delete.contracts_check(P_PERSON_ID);

Line: 1142

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 28);

Line: 1153

      DELETE FROM ben_ext_chg_evt_log
      WHERE  CURRENT OF ben_ext_chg_log;

Line: 1159

    ben_person_delete.perform_ri_check(p_person_id);

Line: 1164

  if upper(p_dt_delete_mode) not in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
    per_ota_predel_validation.ota_predel_per_validation(P_PERSON_ID);

Line: 1171

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 29);

Line: 1177

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 30);

Line: 1183

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 31);

Line: 1189

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 32);

Line: 1195

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 33);

Line: 1201

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 34);

Line: 1207

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 35);

Line: 1211

    hr_utility.set_location('HR_PERSON_DELETE.MODERATE_PREDEL_VALIDATION', 36);

Line: 1228

      If additional data is found then the delete of this person from
      the calling module is invalid as it is beyond its scope. The Delete
      Person form should therefore be used (which only performs
      weak_predel_validation) if a delete really is required.
        p_person_mode  -  'A' check for applicants
                          'E' check for employees
                          'O' check for other types

    NOTE
      No validation is required for security (PER_PERSON_LIST* tables) as
      this is implicit for the person via assignment criteria. The
      rows in these tables can just be deleted.
  */
  PROCEDURE strong_predel_validation (p_person_id    IN number,
                                      p_session_date    IN date,
                                      p_dt_delete_mode    IN varchar2) -- 4169275
  IS
  --
  v_person_types    number;

Line: 1250

     hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);

Line: 1256

     select    count(*)
     into    v_person_types
     from    per_people_f p,
                per_person_types ppt
     where      p.person_id     = P_PERSON_ID
     and        p.effective_end_date >= P_SESSION_DATE
     and        p.person_type_id = ppt.person_type_id
     and    exists
        (select    null
         from    per_people_f p2,
            per_person_types ppt2
         where    p2.person_id    = p.person_id
                 and    p2.effective_end_date >= P_SESSION_DATE
         and    p2.person_type_id = ppt2.person_type_id
         and    ppt2.system_person_type <> ppt.system_person_type
        );

Line: 1278

     hr_utility.set_location('HR_PERSON_DELETE.STRONG_PREDEL_VALIDATION', 1);

Line: 1281

    if p_dt_delete_mode = 'ZAP' then
     hr_person_delete.moderate_predel_validation(p_person_id => p_person_id
                                            ,p_session_date =>p_session_date
					    ,p_dt_delete_mode => p_dt_delete_mode -- 4169275
					    );

Line: 1300

      then delete this contact also. Otherwise do nothing.
    NOTES
      p_person_id        non-contact in relationship
      p_contact_person_id    contact in this relationship - the person
                who the check is performed against.
      p_contact_relationship_id relationship which is currently being
                considered for this contact.
  */
  --
  PROCEDURE check_contact (p_person_id        IN number,
               p_contact_person_id    IN number,
               p_contact_relationship_id IN number,
               p_session_date    IN date)
  IS
  --
  v_contact_elsewhere    varchar2(1);

Line: 1317

  v_delete_contact       varchar2(1);

Line: 1321

    hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 1);

Line: 1323

    hr_person_delete.person_existance_check(P_CONTACT_PERSON_ID);

Line: 1326

        select    'Y'
        into    v_contact_elsewhere
    from    sys.dual
    where    exists (
        select    null
            from    per_contact_relationships r
            where    r.contact_relationship_id <> P_CONTACT_RELATIONSHIP_ID
        and    r.contact_person_id      = P_CONTACT_PERSON_ID);

Line: 1342

    hr_utility.set_location('HR_PERSON_DELETE.CHECK_CONTACT', 2);

Line: 1345

        select    'Y'
        into    v_other_only
        from    sys.dual
        where    not exists
            (select null
             from    per_people_f p
             where    p.person_id        = P_CONTACT_PERSON_ID
             and    p.current_emp_or_apl_flag    = 'Y');

Line: 1364

        v_delete_contact := 'Y';

Line: 1365

            hr_person_delete.strong_predel_validation(P_CONTACT_PERSON_ID,
                            P_SESSION_DATE);

Line: 1369

            v_delete_contact := 'N';

Line: 1372

        if v_delete_contact = 'Y' then
         hr_person_delete.people_default_deletes(P_CONTACT_PERSON_ID,
                                TRUE);

Line: 1386

      delete_a_person
    DESCRIPTION
      Validates whether a person can be deleted from the HR database.
      It is assumed that weak_predel_validation and the other application
      *_delete_person.*_predel_valdation procedures have been successfully
      completed first.
      Cascades are all performed according to the locking ladder.
    NOTE
      P_FORM_CALL is set to 'Y' if this procedure is called from a forms
      module. In this case, the deletes are performed post-delete and a
      row therefore may not exist in per_people_f (for this person_id).
      For this reason the existance check will be ignored.
  */
  --
  PROCEDURE delete_a_person (p_person_id        IN number,
                 p_form_call        IN boolean,
                 p_session_date        IN date)
  IS
  --
  cursor THIS_PERSONS_CONTACTS is
    select    contact_person_id,
        contact_relationship_id
    from    per_contact_relationships
    where    person_id    = P_PERSON_ID;

Line: 1412

    select    person_id
    from    per_people_f
    where    person_id    = P_PERSON_ID
    FOR    UPDATE;

Line: 1418

    select    assignment_id
    from    per_assignments_f
    where    person_id    = P_PERSON_ID
    FOR    UPDATE;

Line: 1423

  cursor DELETE_COMPONENTS is
        select  pp.pay_proposal_id
        from    per_pay_proposals pp,
                per_assignments_f pa
        where   pa.person_id       = P_PERSON_ID
        and     pa.assignment_id   = pp.assignment_id
        FOR     UPDATE;

Line: 1432

    SELECT medical_assessment_id,
           object_version_number
    FROM   per_medical_Assessments pma
    WHERE  pma.person_id = p_person_id;

Line: 1438

          select incident_id, object_version_number
          from per_work_incidents
          where person_id =  p_person_id;

Line: 1443

          select disability_id, object_version_number, effective_start_date, effective_end_date
          from per_disabilities_f
          where person_id = p_person_id;

Line: 1448

        select role_id, object_version_number
        from per_roles
        where person_id= p_person_id;

Line: 1453

       	select   distinct person_type_usage_id
--		,ptu.effective_start_date
--		,ptu.object_version_number
	from 	per_person_type_usages_f ptu
	where 	ptu.person_id = p_person_id
	order by person_type_usage_id;

Line: 1488

	select distinct assignment_id
	from per_assignments_f
	where person_id = p_person_id;

Line: 1498

        hr_person_delete.person_existance_check(P_PERSON_ID);

Line: 1501

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 0);

Line: 1509

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1);

Line: 1520

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 888);

Line: 1523

    update    per_requisitions r
        set    r.person_id    = null
        where    r.person_id    = P_PERSON_ID;

Line: 1528

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 101);

Line: 1531

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2);

Line: 1537

     delete    from per_letter_request_lines l
        where    l.person_id     = P_PERSON_ID;

Line: 1541

    		delete from per_letter_request_lines l
        	where l.assignment_id = l_assignment_id(i);

Line: 1545

    delete    from per_letter_request_lines l
        where    l.person_id     = P_PERSON_ID
    or    exists (
        select  null
                        from    per_assignments_f a
                        where   a.person_id     = P_PERSON_ID
                        and     a.assignment_id = l.assignment_id);*/

Line: 1554

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 201);

Line: 1561

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3);

Line: 1564

    delete    from per_absence_attendances a
        where    a.person_id    = P_PERSON_ID;

Line: 1568

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 301);

Line: 1571

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4);

Line: 1574

        update    per_absence_attendances a
        set    a.authorising_person_id    = null
    where    a.authorising_person_id = P_PERSON_ID;

Line: 1579

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 401);

Line: 1582

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 5);

Line: 1585

    update    per_absence_attendances a
        set    a.replacement_person_id    = null
        where     a.replacement_person_id = P_PERSON_ID;

Line: 1590

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 501);

Line: 1593

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 6);

Line: 1596

    delete    from per_person_analyses a
        where    a.person_id     = P_PERSON_ID;

Line: 1600

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 601);

Line: 1603

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 8);

Line: 1609

    update    per_periods_of_service p
    set    p.termination_accepted_person_id    = null
    where     p.termination_accepted_person_id    = P_PERSON_ID;

Line: 1614

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 801);

Line: 1617

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 9);

Line: 1620

        update    per_recruitment_activities r
    set    r.authorising_person_id    = null
    where    r.authorising_person_id = P_PERSON_ID;

Line: 1625

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 901);

Line: 1628

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 10);

Line: 1631

        update  per_recruitment_activities r
        set     r.internal_contact_person_id    = null
    where    r.internal_contact_person_id    =  P_PERSON_ID;

Line: 1636

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1001);

Line: 1639

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',11);

Line: 1649

begin -- Delete from HR_QUEST_ANSWER_VALUES
  begin -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS
     select 1
     into v_dummy
     from sys.dual
     where exists (
	    select null
	      from per_participants par
	     where par.person_id = P_PERSON_ID);

Line: 1662

        delete from hr_quest_answer_values qsv2
         where qsv2.quest_answer_val_id in
       (select qsv.quest_answer_val_id
          from hr_quest_answer_values qsv
              ,hr_quest_answers qsa
              ,per_participants par
          where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
            and qsa.type_object_id = par.participant_id
            and qsa.type = 'PARTICIPANT'
            and par.person_id = P_PERSON_ID);

Line: 1673

     hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 215); --added for bug 5464252

Line: 1678

      hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 211);

Line: 1680

  end;  -- Delete from HR_QUEST_ANSWER_VALUES: PARTICIPANTS

Line: 1682

  begin -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS
     select 2
     into v_dummy
     from sys.dual
     where exists (
	    select null
		  from per_appraisals apr
		 where (apr.appraiser_person_id = P_PERSON_ID
            or  apr.appraisee_person_id = P_PERSON_ID));

Line: 1695

       delete from hr_quest_answer_values qsv2
         where qsv2.quest_answer_val_id in
       (select qsv.quest_answer_val_id
          from hr_quest_answer_values qsv
              ,hr_quest_answers qsa
              ,per_appraisals apr
         where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
         and   qsa.type_object_id = apr.appraisal_id
         and   qsa.type='APPRAISAL'
         and   (apr.appraisee_person_id = P_PERSON_ID
         or     apr.appraiser_person_id = P_PERSON_ID));

Line: 1711

	     hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 220);

Line: 1713

   end; -- Delete from HR_QUEST_ANSWER_VALUES: APPRAISALS

Line: 1714

end; -- Delete from HR_QUEST_ANSWER_VALUES

Line: 1718

/*        delete from hr_quest_answer_values qsv2
        where qsv2.quest_answer_val_id in (
        select qsv.quest_answer_val_id
          from hr_quest_answer_values qsv
             , hr_quest_answers qsa
             , per_appraisals apr
             , per_participants par
         where qsv.questionnaire_answer_id = qsa.questionnaire_answer_id
           and (qsa.type_object_id = apr.appraisal_id
                    and qsa.type='APPRAISAL'
                    and (apr.appraisee_person_id = P_PERSON_ID
                         or  apr.appraiser_person_id = P_PERSON_ID))
            or (qsa.type_object_id = par.participant_id
                    and qsa.type='PARTICIPANT'
                    and  par.person_id = P_PERSON_ID)
             ); -- Fix 3619599

Line: 1736

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1101);

Line: 1744

   DELETE
   FROM   per_objectives
   WHERE  scorecard_id IN (SELECT scorecard_id
                           FROM   per_personal_scorecards
                           WHERE  person_id = P_PERSON_ID)
    OR    appraisal_id in (SELECT appraisal_id
                           FROM per_appraisals
                           WHERE appraisee_person_id = p_person_id) ;

Line: 1753

   DELETE
   FROM  per_scorecard_sharing
   WHERE scorecard_id IN (SELECT scorecard_id
                          FROM   per_personal_scorecards
                          WHERE  person_id = P_PERSON_ID);

Line: 1758

   DELETE
   FROM   hr_api_transaction_steps
   WHERE  transaction_id IN (SELECT a.transaction_id
                             FROM   hr_api_transactions a,
                                    per_personal_scorecards sc
                             WHERE  a.transaction_ref_table = 'PER_PERSONAL_SCORECARDS'
                             AND    a.transaction_ref_id = sc.scorecard_id
                             AND    sc.person_id = P_PERSON_ID);

Line: 1767

 DELETE
 FROM   hr_api_transactions
 WHERE  transaction_id IN (SELECT transaction_id
                           FROM   hr_api_transactions a,
                                  per_personal_scorecards sc
                           WHERE  a.transaction_ref_table =  'PER_PERSONAL_SCORECARDS'
                           AND    a.transaction_ref_id = sc.scorecard_id
                           AND    sc.person_id = P_PERSON_ID);

Line: 1775

   DELETE
   FROM   per_personal_scorecards
   WHERE  person_id =  P_PERSON_ID;

Line: 1781

     hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1111);

Line: 1785

    DELETE
    FROM per_competence_elements
    WHERE assessment_id  IN (SELECT assessment_id
                             FROM   per_assessments
                             WHERE  appraisal_id  IN (SELECT appraisal_id
                                                      FROM per_appraisals
                                                      WHERE  appraisee_person_id = P_PERSON_ID));

Line: 1792

    DELETE
    FROM   per_performance_ratings
    WHERE  appraisal_id  IN (SELECT appraisal_id
                             FROM per_appraisals
                             WHERE  appraisee_person_id = P_PERSON_ID);

Line: 1797

    DELETE
    FROM   per_assessments
    WHERE  appraisal_id  IN (SELECT appraisal_id
                             FROM per_appraisals
                             WHERE  appraisee_person_id = P_PERSON_ID);

Line: 1805

     hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1112);

Line: 1810

        delete from hr_quest_answers qsa2
         where qsa2.questionnaire_answer_id in (
         select qsa.questionnaire_answer_id
           from hr_quest_answers qsa
              , per_participants par
              , per_appraisals apr
          where (qsa.type_object_id = apr.appraisal_id
                      and qsa.type='APPRAISAL'
                     and (apr.appraiser_person_id = P_PERSON_ID
                          or  apr.appraisee_person_id = P_PERSON_ID))
             or (qsa.type_object_id = par.participant_id
                 and qsa.type='PARTICIPANT'
                 and  par.person_id = P_PERSON_ID)
            ); -- Fix 3619599

Line: 1826

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1102);

Line: 1832

    DELETE
    FROM per_participants par2
    WHERE par2.person_id = P_PERSON_ID
    OR
    (
        par2.participation_in_column = 'APPRAISAL_ID'
        AND par2.participation_in_table = 'PER_APPRAISALS'
        AND par2.participation_in_id in
        (
        SELECT
            apr.appraisal_id
        FROM per_appraisals apr
        WHERE
            (
                apr.appraisee_person_id = P_PERSON_ID
                OR apr.appraiser_person_id = P_PERSON_ID
            )
        )
    );

Line: 1853

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1103);

Line: 1859

       delete from per_appraisals apr
        where --apr.appraiser_person_id = P_PERSON_ID  or    -- changed as part of bug#8265994
        apr.appraisee_person_id = P_PERSON_ID;

Line: 1864

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1104);

Line: 1868

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 12);

Line: 1870

    hr_security.delete_per_from_list(P_PERSON_ID);

Line: 1872

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 14);

Line: 1875

        update    per_vacancies v
    set    v.recruiter_id    = null
    where    v.recruiter_id    = P_PERSON_ID;

Line: 1880

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1401);

Line: 1883

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 15);

Line: 1886

    update    per_assignments_f ass
        set    ass.person_referred_by_id = null
        where    ass.person_referred_by_id = P_PERSON_ID;

Line: 1891

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1501);

Line: 1894

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 16);

Line: 1897

    update    per_assignments_f a
        set    a.recruiter_id        = null
        where    a.recruiter_id        = P_PERSON_ID;

Line: 1902

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1601);

Line: 1905

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 17);

Line: 1908

    update    per_assignments_f a
        set    a.supervisor_id        = null
        where    a.supervisor_id         = P_PERSON_ID;

Line: 1913

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1701);

Line: 1916

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 18);

Line: 1923

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 185);

Line: 1932

        open DELETE_COMPONENTS;

Line: 1934

           FETCH DELETE_COMPONENTS INTO v_proposal_id;

Line: 1935

           EXIT WHEN DELETE_COMPONENTS%NOTFOUND;

Line: 1936

           DELETE FROM per_pay_proposal_components
              WHERE pay_proposal_id = v_proposal_id;

Line: 1939

        close DELETE_COMPONENTS;

Line: 1943

       delete  from per_pay_proposals p
        where   exists (
                select  null
                from    per_assignments_f ass
                where   ass.assignment_id       = p.assignment_id
                and     ass.person_id           = P_PERSON_ID);

Line: 1951

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1801);

Line: 1954

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 19);

Line: 1957

        delete  from pay_personal_payment_methods_f m
        where   m.assignment_id in (
                select ass.assignment_id
                from   per_assignments_f ass
                where  ass.person_id  = P_PERSON_ID);

Line: 1964

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 1901);

Line: 1967

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 20);

Line: 1970

        delete    from per_assignment_budget_values_f a
        where   a.assignment_id in (
        select    ass.assignment_id
        from    per_assignments_f ass
        where   ass.person_id    = P_PERSON_ID);

Line: 1977

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2001);

Line: 1980

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 21);

Line: 1983

        delete    from per_assignment_extra_info a
        where   a.assignment_id in (
        select    ass.assignment_id
        from    per_assignments_f ass
        where    ass.person_id        = P_PERSON_ID);

Line: 1990

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2101);

Line: 1993

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 22);

Line: 1996

        delete    from per_secondary_ass_statuses a
        where   a.assignment_id in (
        select    ass.assignment_id
        from    per_assignments_f ass
        where    ass.person_id        = P_PERSON_ID);

Line: 2003

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2201);

Line: 2006

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23);

Line: 2013

        delete  from per_cobra_coverage_benefits c2
        where   c2.cobra_coverage_enrollment_id in (
        select    c.cobra_coverage_enrollment_id
        from    per_cobra_cov_enrollments c
        where    exists (
            select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = P_PERSON_ID)
        );

Line: 2025

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2301);

Line: 2028

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 230);

Line: 2031

        delete  from per_cobra_coverage_benefits c2
        where   c2.cobra_coverage_enrollment_id in (
        select    c.cobra_coverage_enrollment_id
        from    per_cobra_cov_enrollments c
        ,       per_contact_relationships r
        where    r.contact_person_id = P_PERSON_ID
        and     c.contact_relationship_id = r.contact_relationship_id
        and     exists (
            select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = r.person_id)
        );

Line: 2046

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23001);

Line: 2049

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 231);

Line: 2052

        delete  from per_cobra_coverage_statuses c2
        where   c2.cobra_coverage_enrollment_id in (
        select    c.cobra_coverage_enrollment_id
        from    per_cobra_cov_enrollments c
        where    exists (
            select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = P_PERSON_ID)
        );

Line: 2064

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23101);

Line: 2067

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 232);

Line: 2070

        delete  from per_cobra_coverage_statuses c2
        where   c2.cobra_coverage_enrollment_id in (
        select    c.cobra_coverage_enrollment_id
        from    per_cobra_cov_enrollments c
        ,       per_contact_relationships r
        where    r.contact_person_id = P_PERSON_ID
        and     c.contact_relationship_id = r.contact_relationship_id
        and     exists (
            select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = r.person_id)
        );

Line: 2085

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23201);

Line: 2088

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 233);

Line: 2091

        delete  from per_sched_cobra_payments c2
        where   c2.cobra_coverage_enrollment_id in (
        select    c.cobra_coverage_enrollment_id
        from    per_cobra_cov_enrollments c
        where    exists (
            select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = P_PERSON_ID)
        );

Line: 2103

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23301);

Line: 2106

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 234);

Line: 2109

        delete  from per_sched_cobra_payments c2
        where   c2.cobra_coverage_enrollment_id in (
        select    c.cobra_coverage_enrollment_id
        from    per_cobra_cov_enrollments c
        ,       per_contact_relationships r
        where    r.contact_person_id = P_PERSON_ID
        and     c.contact_relationship_id = r.contact_relationship_id
        and     exists (
            select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = r.person_id)
        );

Line: 2124

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23401);

Line: 2127

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 235);

Line: 2130

        delete  from per_cobra_cov_enrollments c
    where    c.assignment_id in  (
                        select  ass.assignment_id
                        from    per_assignments_f ass
                        where   ass.person_id           = P_PERSON_ID);

Line: 2137

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23501);

Line: 2140

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 236);

Line: 2143

        delete  from per_cobra_cov_enrollments c
    where    exists
           (select null
        from   per_contact_relationships r
        where   r.contact_person_id = P_PERSON_ID
        and     c.contact_relationship_id = r.contact_relationship_id
        and exists (
                        select  null
                        from    per_assignments_f ass
                        where   ass.assignment_id       = c.assignment_id
                        and     ass.person_id           = r.person_id)
               );

Line: 2157

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23601);

Line: 2165

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);

Line: 2168

        delete  from ben_covered_dependents_f c
    	where c.contact_relationship_id in (
    	    select r.contact_relationship_id
    	    from per_contact_relationships r
    	    where r.contact_person_id = p_person_id
        );

Line: 2176

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);

Line: 2192

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 237);

Line: 2195

    select    count(*)
    into     v_dummy
    from    per_contact_relationships r
    where    r.person_id        = P_PERSON_ID;

Line: 2203

        delete    from per_contact_relationships r
        where    (r.person_id = P_PERSON_ID
        and    r.contact_person_id = EACH_CONTACT.CONTACT_PERSON_ID)
        or    (r.person_id = EACH_CONTACT.CONTACT_PERSON_ID
        and    r.contact_person_id = P_PERSON_ID);

Line: 2209

             hr_person_delete.check_contact(P_PERSON_ID,
                    EACH_CONTACT.CONTACT_PERSON_ID,
                    EACH_CONTACT.CONTACT_RELATIONSHIP_ID,
                    P_SESSION_DATE);

Line: 2218

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 23701);

Line: 2221

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 24);

Line: 2224

        delete    from per_contact_relationships r
        where    r.contact_person_id    = P_PERSON_ID;

Line: 2228

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2401);

Line: 2231

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25);

Line: 2234

        delete    from per_addresses a
        where    a.person_id    = P_PERSON_ID;

Line: 2238

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2501);

Line: 2241

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25.5);

Line: 2244

        delete    from per_phones a
        where    a.parent_id    = P_PERSON_ID
                and a.parent_table = 'PER_ALL_PEOPLE_F';

Line: 2249

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 25501);

Line: 2252

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26);

Line: 2260

        dbms_sql.parse(v_review_cursor,'DELETE from PER_PERFORMANCE_REVIEWS
                                        where person_id=:x',dbms_sql.v7);

Line: 2274

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 261);

Line: 2282

	-- Delete statement modified to improve performance.

	forall i in 1..l_assignment_id.count
		delete  from per_bookings b
		where    b.event_id in (select  e.event_id
         				from    per_events e
         				where    e.assignment_id = l_assignment_id(i));

Line: 2290

        /*delete  from per_bookings b
        where    b.event_id in
        (select    e.event_id
         from    per_events e
         where    exists (
            select    null
            from    per_assignments_f ass
            where    ass.assignment_id    = e.assignment_id
            and    ass.person_id         = P_PERSON_ID)
        );*/

Line: 2302

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26101);

Line: 2305

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 262);

Line: 2309

    	-- Delete statement modified to improve performance.

    	forall i in 1..l_assignment_id.count
    		delete    from per_events e
        	where    e.assignment_id = l_assignment_id(i);

Line: 2315

       /* delete    from per_events e
        where    e.assignment_id in (
                    select ass.assignment_id
                    from   per_assignments_f ass
                    where  ass.person_id           = P_PERSON_ID);*/

Line: 2322

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 26201);

Line: 2325

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 27);

Line: 2328

        update    per_events e
        set    e.internal_contact_person_id    = null
        where    e.internal_contact_person_id    = P_PERSON_ID;

Line: 2333

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2701);

Line: 2336

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 28);

Line: 2339

        delete    from per_bookings b
        where    b.person_id    = P_PERSON_ID;

Line: 2343

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2801);

Line: 2346

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 29);

Line: 2349

        delete  from per_quickpaint_result_text q
        where   q.assignment_id in  (
                    select  ass.assignment_id
                    from    per_assignments_f ass
                    where   ass.person_id           = P_PERSON_ID);

Line: 2356

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 2901);

Line: 2359

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 30);

Line: 2365

        delete    from hr_assignment_set_amendments h
        where  h.assignment_id in     (
                    select  ass.assignment_id
                    from    per_assignments_f ass
                    where   ass.person_id           = P_PERSON_ID);

Line: 2372

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3001);

Line: 2375

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 31);

Line: 2378

        delete  from pay_cost_allocations_f a
        where   a.assignment_id in (
                select  ass.assignment_id
                from    per_assignments_f ass
                where   ass.person_id           = P_PERSON_ID);

Line: 2385

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3101);

Line: 2388

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 32);

Line: 2391

        delete  from per_spinal_point_placements_f p
    where    p.assignment_id in (
        select  ass.assignment_id
        from    per_assignments_f ass
        where   ass.person_id           = P_PERSON_ID);

Line: 2398

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3201);

Line: 2401

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 33);

Line: 2407

        delete    from pay_assignment_actions a
        where    exists  (
                    select  null
                    from    per_assignments_f ass
                    where   ass.person_id     = P_PERSON_ID
                    and     ass.assignment_id = a.assignment_id);

Line: 2415

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3301);

Line: 2418

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 34);

Line: 2421

        delete    from pay_assignment_latest_balances b
        where   b.assignment_id in  (
                    select  ass.assignment_id
                    from    per_assignments_f ass
                    where   ass.person_id           = P_PERSON_ID);

Line: 2428

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3401);

Line: 2431

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 35);

Line: 2438

        	delete  from pay_assignment_link_usages_f u
        		where  u.assignment_id	= l_assignment_id(i);

Line: 2441

        /*delete  from pay_assignment_link_usages_f u
        where
        u.assignment_id in (
                   select ass.assignment_id
                   from per_assignments_f ass
                   where ass.person_id = P_PERSON_ID); */

Line: 2449

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3501);

Line: 2452

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 36);

Line: 2455

    delete    from pay_element_entry_values_f v
    where    v.element_entry_id in (
        select    e.element_entry_id
        from    pay_element_entries_f e
        where    exists (
            select    null
            from    per_assignments_f ass
            where    ass.assignment_id    = e.assignment_id
            and    ass.person_id        = P_PERSON_ID)
        );

Line: 2467

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3601);

Line: 2470

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 37);

Line: 2473

    delete    from pay_run_results r
    where    r.source_type    = 'E'
    and    r.source_id    in (
        select    e.element_entry_id
        from    pay_element_entries_f e
        where    exists (
            select    null
            from    per_assignments_f ass
            where    ass.assignment_id    = e.assignment_id
            and    ass.person_id        = P_PERSON_ID)
        );

Line: 2486

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3701);

Line: 2489

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 38);

Line: 2492

        delete    from pay_element_entries_f e
        where    e.assignment_id in (
                    select  ass.assignment_id
                    from    per_assignments_f ass
                    where   ass.person_id           = P_PERSON_ID);

Line: 2499

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 3801);

Line: 2502

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 39);

Line: 2511

             Delete  pay_us_emp_fed_tax_rules_f peft
             Where   peft.assignment_id   in (
                     select ass.assignment_id
                     from per_assignments_f ass
                     where ass.person_id    = p_person_id );

Line: 2518

          hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3802);

Line: 2523

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 40);

Line: 2525

             Delete  pay_us_emp_state_tax_rules_f pest
             Where   pest.assignment_id   in (
                     select ass.assignment_id
                     from per_assignments_f ass
                     where ass.person_id    = p_person_id );

Line: 2532

          hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3803);

Line: 2537

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 41);

Line: 2539

             Delete  pay_us_emp_county_tax_rules_f pect
             Where   pect.assignment_id   in (
                     select ass.assignment_id
                     from per_assignments_f ass
                     where ass.person_id    = p_person_id );

Line: 2546

          hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3804);

Line: 2551

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 42);

Line: 2553

             Delete  pay_us_emp_city_tax_rules_f pecit
             Where   pecit.assignment_id   in (
                     select ass.assignment_id
                     from per_assignments_f ass
                     where ass.person_id    = p_person_id );

Line: 2560

          hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',3805);

Line: 2567

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 43);

Line: 2570

        delete    from per_all_assignments_f a
        where    a.person_id     = P_PERSON_ID;

Line: 2574

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4001);

Line: 2577

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 44);

Line: 2580

    delete    from per_periods_of_service p
        where    p.person_id     = P_PERSON_ID;

Line: 2584

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4101);

Line: 2587

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 45);

Line: 2590

    delete    from per_applications a
        where    a.person_id     = P_PERSON_ID;

Line: 2594

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4201);

Line: 2599

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 46);

Line: 2602

        delete  from per_people_extra_info  e
        where   e.person_id     = P_PERSON_ID;

Line: 2606

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4301);

Line: 2614

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON',47);

Line: 2618

 	select 	min(ptu1.effective_start_date)
	into	l_effective_date
	from 	per_person_type_usages_f ptu1
	where 	ptu1.person_type_usage_id = ptu_rec.person_type_usage_id;

Line: 2623

 	select 	ptu2.object_version_number
	into	l_object_version_number
	from 	per_person_type_usages_f ptu2
	where 	ptu2.person_type_usage_id = ptu_rec.person_type_usage_id
	and	ptu2.effective_start_date = l_effective_date;

Line: 2644

       hr_per_type_usage_internal.delete_person_type_usage
		(p_person_type_usage_id  => ptu_rec.person_type_usage_id
		,p_effective_date	 => l_effective_date
		,p_datetrack_mode 	 => '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: 2659

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 48);

Line: 2662

       delete from per_person_dlvry_methods
       where person_id = P_PERSON_ID;

Line: 2670

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 49);

Line: 2672

       delete from per_checklist_items
       where person_id = P_PERSON_ID;

Line: 2681

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 50);

Line: 2686

        delete from per_subjects_taken_tl st
         where st.subjects_taken_id IN ( select s.subjects_taken_id
                                           from per_subjects_taken s
                                              , per_qualifications q
                                          where q.person_id = P_PERSON_ID
                                            and s.qualification_id = q.qualification_id
                                       );

Line: 2694

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4698);

Line: 2697

        delete from per_subjects_taken s
        where s.qualification_id in ( select qualification_id
                                      from per_qualifications
                                      where person_id = P_PERSON_ID );

Line: 2702

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4699);

Line: 2705

        delete from per_qualifications_tl  qt
         where qt.qualification_id in ( select q.qualification_id
                                          from per_qualifications q
                                         where q.person_id = P_PERSON_ID
                                      );

Line: 2711

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4700);

Line: 2713

        delete  from per_qualifications  q
        where   q.person_id     = P_PERSON_ID;

Line: 2718

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4701);

Line: 2724

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 99);

Line: 2727

    delete    from per_all_people_f
        where    person_id = P_PERSON_ID;

Line: 2731

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4401);

Line: 2741

   hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 100);

Line: 2747

     per_medical_assessment_api.delete_medical_assessment
       (FALSE
       ,mea_rec.medical_assessment_id
       ,mea_rec.object_version_number);

Line: 2754

   hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 110);

Line: 2763

          per_disability_api.delete_disability(false,p_session_date ,'ZAP',v_disability_id, v_object_version_no, v_effective_start_date, v_effective_end_date);

Line: 2766

   hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 120);

Line: 2775

        per_work_incident_api.delete_work_incident(false,v_incident_id, v_object_version_number);

Line: 2778

   hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 130);

Line: 2787

        per_supplementary_role_api.delete_supplementary_role(false, v_role_id, v_ovn_roles);

Line: 2790

   hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 150);

Line: 2794

    delete    from per_periods_of_placement p
        where    p.person_id     = P_PERSON_ID;

Line: 2798

        hr_utility.set_location('HR_PERSON_DELETE.DELETE_A_PERSON', 4501);

Line: 2803

  END delete_a_person;

Line: 2809

      people_default_deletes
    DESCRIPTION
      Delete routine for deleting information set up as default when people
      are created. Used primarily for delete on PERPEEPI (Enter Person).
      The strong_predel_validation should first be performed to ensure that
      no additional info (apart from default) has been entered.
    NOTE
      See delete_a_person for p_form_call details. Further, p_form_call is
      set to TRUE when this procedure is called from check_contact as
      there is no need to check the existance of the contact.
  */
  --
  PROCEDURE people_default_deletes (p_person_id    IN number,
                    p_form_call    IN boolean)
  IS
  --
  v_assignment_id    number(15);

Line: 2828

        select  person_id
        from    per_people_f
        where   person_id       = P_PERSON_ID
        FOR     UPDATE;

Line: 2837

    hr_person_delete.person_existance_check(P_PERSON_ID);

Line: 2840

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 1);

Line: 2850

    ben_person_delete.delete_ben_rows(p_person_id);

Line: 2854

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 2);

Line: 2856

    hr_security.delete_per_from_list(P_PERSON_ID);

Line: 2858

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 4);

Line: 2866

        select    ass.assignment_id
    into    v_assignment_id
    from    per_assignments_f ass
    where    ass.person_id    = P_PERSON_ID
    FOR UPDATE;

Line: 2875

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 5);

Line: 2878

        delete  from pay_personal_payment_methods p
    where    p.assignment_id = V_ASSIGNMENT_ID;

Line: 2884

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 6);

Line: 2887

        delete  from per_assignment_budget_values_f v
        where   v.assignment_id = V_ASSIGNMENT_ID;

Line: 2893

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 7);

Line: 2896

        delete  from per_addresses a
    where    a.person_id    = P_PERSON_ID;

Line: 2902

    hr_utility.set_location('HR_PERSON_DELETE.DELETE_DEFAULT_DELETES', 7.5);

Line: 2905

        delete  from per_phones a
        where   a.parent_id     = P_PERSON_ID
                and a.parent_table = 'PER_ALL_PEOPLE_F';

Line: 2912

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 8);

Line: 2915

        delete  from pay_cost_allocations_f a
    where    a.assignment_id = V_ASSIGNMENT_ID;

Line: 2921

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 9);

Line: 2924

    delete    from pay_element_entry_values_f v
    where   exists (
            select   null
            from     pay_element_entries_f e
            where    e.assignment_id = V_ASSIGNMENT_ID
            and      e.element_entry_id = v.element_entry_id);

Line: 2934

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 10);

Line: 2937

    delete    from pay_run_results r
    where    r.source_type    = 'E'
    and    EXISTS (
            select null
            from    pay_element_entries_f e
            where    e.assignment_id = V_ASSIGNMENT_ID
                 and e.element_entry_id = r.source_id);

Line: 2948

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 11);

Line: 2951

        delete  from pay_element_entries_f e
        where   e.assignment_id = V_ASSIGNMENT_ID;

Line: 2957

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 12);

Line: 2962

    delete     from per_assignments_f ass
    where    ass.assignment_id = V_ASSIGNMENT_ID;

Line: 2966

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 13);

Line: 2969

    delete    from per_periods_of_service p
    where    p.person_id = P_PERSON_ID;

Line: 2975

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 14);

Line: 2978

        delete  from per_applications a
        where    a.person_id = P_PERSON_ID;

Line: 2986

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 17);

Line: 2988

       delete from per_checklist_items
       where person_id = P_PERSON_ID;

Line: 2997

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 15);

Line: 3001

    hr_utility.set_location('HR_PERSON_DELETE.PEOPLE_DEFAULT_DELETES', 16);

Line: 3004

        delete      from per_people_f
        where       person_id = P_PERSON_ID;

Line: 3011

    delete    from per_periods_of_placement p
    where    p.person_id = P_PERSON_ID;

Line: 3018

  END people_default_deletes;

Line: 3024

      applicant_default_deletes
    DESCRIPTION
      Delete routine for deleting information set up as default when
      applicants are entered.  Used primarily for delete on PERREAQE
      (Applicant Quick Entry). The strong_predel_validation should first be
      performed to ensure that no additional info (apart from default) has
      been entered.
    NOTE
      See delete_a_person for p_form_call details.
  */
  --
  PROCEDURE applicant_default_deletes (p_person_id IN number,
                       p_form_call IN boolean)
  IS
  --
  v_assignment_id       number(15);

Line: 3042

        select  person_id
        from    per_people_f
        where   person_id       = P_PERSON_ID
        FOR     UPDATE;

Line: 3051

    hr_person_delete.person_existance_check(P_PERSON_ID);

Line: 3054

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 1);

Line: 3060

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 3);

Line: 3063

        delete  from per_person_list l
        where    l.person_id = P_PERSON_ID;

Line: 3069

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 5);

Line: 3075

        select    ass.assignment_id
    into    v_assignment_id
    from    per_assignments_f ass
    where    ass.person_id    = P_PERSON_ID
    FOR UPDATE;

Line: 3082

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6);

Line: 3085

        delete  from per_addresses a
        where   a.person_id     = P_PERSON_ID;

Line: 3091

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 6.5);

Line: 3094

        delete  from per_phones a
        where   a.parent_id     = P_PERSON_ID
                and a.parent_table = 'PER_ALL_PEOPLE_F';

Line: 3101

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 7);

Line: 3104

    delete     from per_assignments_f ass
    where    ass.assignment_id = V_ASSIGNMENT_ID;

Line: 3108

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 8);

Line: 3111

        delete  from per_applications a
        where    a.person_id = P_PERSON_ID;

Line: 3119

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 15);

Line: 3121

       delete from per_checklist_items
       where person_id = P_PERSON_ID;

Line: 3133

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 16);

Line: 3135

       delete from per_person_type_usages_f
       where person_id = P_PERSON_ID;

Line: 3144

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);

Line: 3146

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 9);

Line: 3150

    hr_utility.set_location('HR_PERSON_DELETE.APPLICANT_DEFAULT_DELETES', 10);

Line: 3153

        delete      from per_people_f
        where       person_id = P_PERSON_ID;

Line: 3159

  END applicant_default_deletes;