title: SQL - edit of report (returning initials from previous field)
body: <p>Admittedly, my SQL knowledge is not amazing, and I need to do some edits to a SQL report within our Oracle system. Below is the report code. basically, the report below (I have edited the names to Manager 1/ Manager 2 ETC ETC). Can I add a column to the end of this that says if Executive_Member = 'Manager 2' return 'Manager 2' and the initials of the manager below Manager 2. For example if Manager 2 was John, and Manager 3 was Paul James, return John - PJ</p>
  
  <p>Sorry to attach my whole report to this file, I wasnt sure what I could leave cut out and what I had to include in order for this to make sense.</p>
  
  <p>so to confirm, I want to be able to add a new field at the end of this report, which can return if execo_member = Manager 2, then return Manager 2 and initials of Manager 3. </p>
  
  <pre><code>SELECT papf.person_number,
    ppnf.first_name ,
    ppnf.last_name ,
    --
    pa.address_line_1,
    pa.address_line_2,
    pa.town_or_city,
    pa.postal_code,
    pa.country,
    --
    pph.search_phone_number AS phone,
    pea.email_address       AS worker_email_address,
    TRUNC(pp.date_of_birth) AS date_of_birth,
    --
    (
    SELECT hl.meaning
    FROM hcm_lookups hl
    WHERE hl.lookup_type = 'SEX'
    AND hl.lookup_code   = pplf.sex
    ) AS gender,
    --
    pni.national_identifier_number,
    TRUNC(pp.start_date)              AS start_date,
    TRUNC(ppos.original_date_of_hire) AS original_date_of_hire,
    --
    pjf.job_code,
    pjft.name             AS job_name,
    pjfft.job_family_name AS job_family,
    pgft.name             AS grade_name,
    --
    pptl.user_person_type AS person_type,
    --
    (
    SELECT hl.meaning
    FROM hcm_lookups hl
    WHERE hl.lookup_type = 'HOURLY_SALARIED_CODE'
    AND hl.lookup_code   = paam.hourly_salaried_code
    ) AS hourly_salaried_code,
    --
    (
    SELECT hl.meaning
    FROM hcm_lookups hl
    WHERE hl.lookup_type = 'EMPLOYEE_CATG'
    AND hl.lookup_code   = paam.employee_category
    ) AS employee_category,
    --
    (
    SELECT hl.meaning
    FROM hcm_lookups hl
    WHERE hl.lookup_type = 'YES_NO'
    AND hl.lookup_code   = paam.manager_flag
    ) AS is_manager,
    --
    paam.normal_hours,
    --
    (
    SELECT hl.meaning
    FROM hcm_lookups hl
    WHERE hl.lookup_type = 'FREQUENCY'
    AND hl.lookup_code   = paam.frequency
    ) AS frequency,
    --
    ppg.segment1 AS people_group,
    --
    (
    SELECT organizationunittranslationpeo.name
    FROM hr_all_organization_units_f organizationunitpeo,
      hr_org_unit_classifications_f orgunitclassificationpeo,
      hr_organization_units_f_tl organizationunittranslationpeo
    WHERE organizationunitpeo.organization_id    = orgunitclassificationpeo.organization_id(+)
    AND organizationunitpeo.organization_id      = organizationunittranslationpeo.organization_id(+)
    AND organizationunitpeo.effective_start_date = organizationunittranslationpeo.effective_start_date(+)
    AND organizationunitpeo.effective_end_date   = organizationunittranslationpeo.effective_end_date(+)
    AND :EFFECTIVE_DATE BETWEEN organizationunitpeo.effective_start_date AND organizationunitpeo.effective_end_date
    AND :EFFECTIVE_DATE BETWEEN orgunitclassificationpeo.effective_start_date(+) AND orgunitclassificationpeo.effective_end_date(+)
    AND :EFFECTIVE_DATE BETWEEN organizationunittranslationpeo.effective_start_date(+) AND organizationunittranslationpeo.effective_end_date(+)
    AND organizationunittranslationpeo.LANGUAGE      = USERENV('LANG')
    AND orgunitclassificationpeo.classification_code = 'DEPARTMENT'
    AND organizationunitpeo.organization_id          = paam.organization_id
    ) AS department,
    --
    (
    SELECT organizationunittranslationpeo.name
    FROM hr_all_organization_units_f organizationunitpeo,
      hr_org_unit_classifications_f orgunitclassificationpeo,
      hr_organization_units_f_tl organizationunittranslationpeo
    WHERE organizationunitpeo.organization_id    = orgunitclassificationpeo.organization_id(+)
    AND organizationunitpeo.organization_id      = organizationunittranslationpeo.organization_id(+)
    AND organizationunitpeo.effective_start_date = organizationunittranslationpeo.effective_start_date(+)
    AND organizationunitpeo.effective_end_date   = organizationunittranslationpeo.effective_end_date(+)
    AND :EFFECTIVE_DATE BETWEEN organizationunitpeo.effective_start_date AND organizationunitpeo.effective_end_date
    AND :EFFECTIVE_DATE BETWEEN orgunitclassificationpeo.effective_start_date(+) AND orgunitclassificationpeo.effective_end_date(+)
    AND :EFFECTIVE_DATE BETWEEN organizationunittranslationpeo.effective_start_date(+) AND organizationunittranslationpeo.effective_end_date(+)
    AND organizationunittranslationpeo.LANGUAGE      = USERENV('LANG')
    AND orgunitclassificationpeo.classification_code = 'HCM_LEMP'
    AND organizationunitpeo.organization_id          = paam.legal_entity_id
    ) AS legal_employer, --
    (SELECT organizationunittranslationpeo.name
    FROM hr_all_organization_units_f organizationunitpeo,
      hr_org_unit_classifications_f orgunitclassificationpeo,
      hr_organization_units_f_tl organizationunittranslationpeo
    WHERE organizationunitpeo.organization_id    = orgunitclassificationpeo.organization_id(+)
    AND organizationunitpeo.organization_id      = organizationunittranslationpeo.organization_id(+)
    AND organizationunitpeo.effective_start_date = organizationunittranslationpeo.effective_start_date(+)
    AND organizationunitpeo.effective_end_date   = organizationunittranslationpeo.effective_end_date(+)
    AND :EFFECTIVE_DATE BETWEEN organizationunitpeo.effective_start_date AND organizationunitpeo.effective_end_date
    AND :EFFECTIVE_DATE BETWEEN orgunitclassificationpeo.effective_start_date(+) AND orgunitclassificationpeo.effective_end_date(+)
    AND :EFFECTIVE_DATE BETWEEN organizationunittranslationpeo.effective_start_date(+) AND organizationunittranslationpeo.effective_end_date(+)
    AND organizationunittranslationpeo.LANGUAGE      = USERENV('LANG')
    AND orgunitclassificationpeo.classification_code = 'FUN_BUSINESS_UNIT'
    AND organizationunitpeo.organization_id          = paam.business_unit_id
    ) AS business_unit,
    --
    (
    SELECT locationdetailspeo.location_name
    FROM per_location_details_f_vl locationdetailspeo,
      per_locations locationpeo
    WHERE locationdetailspeo.location_id = locationpeo.location_id
    AND :EFFECTIVE_DATE BETWEEN locationdetailspeo.effective_start_date AND locationdetailspeo.effective_end_date
    AND locationdetailspeo.location_id = paam.location_id
    ) AS location_name,
    --
    ppnf1.display_name AS manager_name_1,
    --
    ppnf2.display_name AS manager_name_2,
    --
    ppnf3.display_name AS manager_name_3,
    --
    ppnf4.display_name AS manager_name_4,
    --
    ppnf5.display_name AS manager_name_5,
    --
    ppnf6.display_name AS manager_name_6,
    --
    ppnf7.display_name AS manager_name_7,
    --
    ppnf8.display_name AS manager_name_8,
    --
    ppnf9.display_name AS manager_name_9,
    --
    ppnf10.display_name AS manager_name_10,
    --
    ppnf11.display_name AS manager_name_11,
    --
    ppnf12.display_name AS manager_name_12,
    --
    ppnf13.display_name AS manager_name_13,
    --
    ppnf14.display_name AS manager_name_14,
    --
    ppnf15.display_name AS manager_name_15,
    --
    (
    CASE 
    WHEN ppnf1.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf1.display_name 
    WHEN ppnf2.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf2.display_name 
    WHEN ppnf3.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf3.display_name 
    WHEN ppnf4.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf4.display_name 
    WHEN ppnf5.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf5.display_name 
    WHEN ppnf6.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf6.display_name 
    WHEN ppnf7.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf7.display_name 
    WHEN ppnf8.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf8.display_name 
    WHEN ppnf9.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf9.display_name 
    WHEN ppnf10.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf10.display_name 
    WHEN ppnf11.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf11.display_name 
    WHEN ppnf12.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf12.display_name 
    WHEN ppnf13.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf13.display_name 
    WHEN ppnf14.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf14.display_name 
    WHEN ppnf15.display_name IN ('Manager 1', 'Manager 2', 'Manager 3', 'Manager 4', 'Manager 5')
    THEN ppnf15.display_name 
    ELSE NULL
    END
    ) executive_member
  FROM fusion.per_person_names_f ppnf,
    fusion.per_all_people_f papf,
    fusion.per_all_assignments_m paam,
    fusion.per_phones pph,
    fusion.per_jobs_f pjf,
    fusion.per_jobs_f_tl pjft,
    fusion.per_job_family_f pjff,
    fusion.per_job_family_f_tl pjfft,
    fusion.per_grades_f pgf,
    fusion.per_grades_f_tl pgft,
    fusion.per_person_types ppt,
    fusion.per_person_types_tl pptl,
    fusion.per_people_groups ppg,
    fusion.per_addresses_f pa,
    fusion.per_persons pp,
    fusion.per_email_addresses pea,
    fusion.per_people_legislative_f pplf,
    fusion.per_national_identifiers pni,
    fusion.per_periods_of_service ppos,
    fusion.per_assignment_supervisors_f pasf,
    --
    fusion.per_person_names_f ppnf1,
    fusion.per_all_assignments_m paam1,
    fusion.per_assignment_supervisors_f pasf1,
    --
    fusion.per_person_names_f ppnf2,
    fusion.per_all_assignments_m paam2,
    fusion.per_assignment_supervisors_f pasf2,
    --
    fusion.per_person_names_f ppnf3,
    fusion.per_all_assignments_m paam3,
    fusion.per_assignment_supervisors_f pasf3,
    --
    fusion.per_person_names_f ppnf4,
    fusion.per_all_assignments_m paam4,
    fusion.per_assignment_supervisors_f pasf4,
    --
    fusion.per_person_names_f ppnf5,
    fusion.per_all_assignments_m paam5,
    fusion.per_assignment_supervisors_f pasf5,
    --
    fusion.per_person_names_f ppnf6,
    fusion.per_all_assignments_m paam6,
    fusion.per_assignment_supervisors_f pasf6,
    --
    fusion.per_person_names_f ppnf7,
    fusion.per_all_assignments_m paam7,
    fusion.per_assignment_supervisors_f pasf7,
    --
    fusion.per_person_names_f ppnf8,
    fusion.per_all_assignments_m paam8,
    fusion.per_assignment_supervisors_f pasf8,
    --
    fusion.per_person_names_f ppnf9,
    fusion.per_all_assignments_m paam9,
    fusion.per_assignment_supervisors_f pasf9,
    --
    fusion.per_person_names_f ppnf10,
    fusion.per_all_assignments_m paam10,
    fusion.per_assignment_supervisors_f pasf10,
    --
    fusion.per_person_names_f ppnf11,
    fusion.per_all_assignments_m paam11,
    fusion.per_assignment_supervisors_f pasf11,
    --
    fusion.per_person_names_f ppnf12,
    fusion.per_all_assignments_m paam12,
    fusion.per_assignment_supervisors_f pasf12,
    --
    fusion.per_person_names_f ppnf13,
    fusion.per_all_assignments_m paam13,
    fusion.per_assignment_supervisors_f pasf13,
    --
    fusion.per_person_names_f ppnf14,
    fusion.per_all_assignments_m paam14,
    fusion.per_assignment_supervisors_f pasf14,
    --
    fusion.per_person_names_f ppnf15,
    fusion.per_all_assignments_m paam15
  WHERE ppnf.person_id = papf.person_id
  AND :EFFECTIVE_DATE BETWEEN papf.effective_start_date(+) AND papf.effective_end_date(+)
    --
  AND papf.person_id                  = paam.person_id(+)
  AND paam.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam.effective_latest_change(+) = 'Y'
  AND paam.primary_flag(+)            = 'Y'
  AND paam.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam.effective_start_date(+) AND paam.effective_end_date(+)
    --
  AND papf.person_id        = pph.person_id(+)
  AND papf.primary_phone_id = pph.phone_id(+)
    --
  AND paam.job_id        = pjf.job_id(+)
  AND pjf.job_id         = pjft.job_id(+)
  AND pjf.job_family_id  = pjff.job_family_id(+)
  AND pjff.job_family_id = pjfft.job_family_id(+)
  AND pjft.LANGUAGE(+)   = USERENV('LANG')
  AND pjfft.LANGUAGE(+)  = USERENV('LANG')
  AND :EFFECTIVE_DATE BETWEEN pjf.effective_start_date(+) AND pjf.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pjff.effective_start_date(+) AND pjff.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pjfft.effective_start_date(+) AND pjfft.effective_end_date(+)
    --
  AND paam.grade_id    = pgf.grade_id(+)
  AND pgf.grade_id     = pgft.grade_id(+)
  AND pgft.LANGUAGE(+) = USERENV('LANG')
  AND :EFFECTIVE_DATE BETWEEN pgf.effective_start_date(+) AND pgf.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pgft.effective_start_date(+) AND pgft.effective_end_date(+)
    --
  AND paam.person_type_id = ppt.person_type_id
  AND ppt.person_type_id  = pptl.person_type_id
  AND pptl.LANGUAGE       = USERENV('LANG')
    --
  AND paam.people_group_id = ppg.people_group_id(+)
    --
  AND papf.mailing_address_id = pa.address_id(+)
  AND :EFFECTIVE_DATE BETWEEN pa.effective_start_date(+) AND pa.effective_end_date(+)
    --
  AND ppnf.person_id = pp.person_id(+)
    --
  AND papf.person_id = pea.person_id(+)
  AND :EFFECTIVE_DATE BETWEEN pea.date_from(+) AND NVL(pea.date_to, SYSDATE)
  AND pea.email_type(+) = 'W1'
    --
  AND paam.person_id        = pplf.person_id(+)
  AND paam.legislation_code = pplf.legislation_code(+)
  AND :EFFECTIVE_DATE BETWEEN pplf.effective_start_date(+) AND pplf.effective_end_date(+)
    --
  AND papf.person_id      = pni.person_id(+)
  AND papf.primary_nid_id = pni.national_identifier_id(+)
    --
  AND papf.person_id   = ppos.person_id(+)
  AND (ppos.date_start =
    (SELECT MAX(ppos1.date_start)
    FROM fusion.per_periods_of_service ppos1
    WHERE ppos1.person_id = ppos.person_id
    )
  OR ppos.date_start IS NULL)
    --
    --AND ppnf.person_id           = pasf.person_id(+)
  AND paam.assignment_id       = pasf.assignment_id(+)
  AND UPPER(ppnf.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf.effective_start_date(+) AND ppnf.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf.effective_start_date(+) AND pasf.effective_end_date(+)
    --
  AND pasf.manager_id           = ppnf1.person_id(+)
  AND ppnf1.person_id           = pasf1.person_id(+)
  AND UPPER(ppnf1.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf1.effective_start_date(+) AND ppnf1.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf1.effective_start_date(+) AND pasf1.effective_end_date(+)
  AND ppnf1.person_id                  = paam1.person_id (+)
  AND paam1.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam1.effective_latest_change(+) = 'Y'
  AND paam1.primary_flag(+)            = 'Y'
  AND paam1.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam1.effective_start_date(+) AND paam1.effective_end_date(+)
    --
  AND pasf1.manager_id          = ppnf2.person_id(+)
  AND ppnf2.person_id           = pasf2.person_id(+)
  AND UPPER(ppnf2.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf2.effective_start_date(+) AND ppnf2.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf2.effective_start_date(+) AND pasf2.effective_end_date(+)
  AND ppnf2.person_id                  = paam2.person_id (+)
  AND paam2.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam2.effective_latest_change(+) = 'Y'
  AND paam2.primary_flag(+)            = 'Y'
  AND paam2.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam2.effective_start_date(+) AND paam2.effective_end_date(+)
    --
  AND pasf2.manager_id          = ppnf3.person_id(+)
  AND ppnf3.person_id           = pasf3.person_id(+)
  AND UPPER(ppnf3.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf3.effective_start_date(+) AND ppnf3.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf3.effective_start_date(+) AND pasf3.effective_end_date(+)
  AND ppnf3.person_id                  = paam3.person_id (+)
  AND paam3.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam3.effective_latest_change(+) = 'Y'
  AND paam3.primary_flag(+)            = 'Y'
  AND paam3.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam3.effective_start_date(+) AND paam3.effective_end_date(+)
    --
  AND pasf3.manager_id          = ppnf4.person_id(+)
  AND ppnf4.person_id           = pasf4.person_id(+)
  AND UPPER(ppnf4.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf4.effective_start_date(+) AND ppnf4.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf4.effective_start_date(+) AND pasf4.effective_end_date(+)
  AND ppnf4.person_id                  = paam4.person_id (+)
  AND paam4.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam4.effective_latest_change(+) = 'Y'
  AND paam4.primary_flag(+)            = 'Y'
  AND paam4.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam4.effective_start_date(+) AND paam4.effective_end_date(+)
    --
  AND pasf4.manager_id          = ppnf5.person_id(+)
  AND ppnf5.person_id           = pasf5.person_id(+)
  AND UPPER(ppnf5.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf5.effective_start_date(+) AND ppnf5.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf5.effective_start_date(+) AND pasf5.effective_end_date(+)
  AND ppnf5.person_id                  = paam5.person_id (+)
  AND paam5.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam5.effective_latest_change(+) = 'Y'
  AND paam5.primary_flag(+)            = 'Y'
  AND paam5.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam5.effective_start_date(+) AND paam5.effective_end_date(+)
    --
  AND pasf5.manager_id          = ppnf6.person_id(+)
  AND ppnf6.person_id           = pasf6.person_id(+)
  AND UPPER(ppnf6.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf6.effective_start_date(+) AND ppnf6.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf6.effective_start_date(+) AND pasf6.effective_end_date(+)
  AND ppnf6.person_id                  = paam6.person_id (+)
  AND paam6.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam6.effective_latest_change(+) = 'Y'
  AND paam6.primary_flag(+)            = 'Y'
  AND paam6.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam6.effective_start_date(+) AND paam6.effective_end_date(+)
    --
  AND pasf6.manager_id          = ppnf7.person_id(+)
  AND ppnf7.person_id           = pasf7.person_id(+)
  AND UPPER(ppnf7.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf7.effective_start_date(+) AND ppnf7.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf7.effective_start_date(+) AND pasf7.effective_end_date(+)
  AND ppnf7.person_id                  = paam7.person_id (+)
  AND paam7.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam7.effective_latest_change(+) = 'Y'
  AND paam7.primary_flag(+)            = 'Y'
  AND paam7.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam7.effective_start_date(+) AND paam7.effective_end_date(+)
    --
  AND pasf7.manager_id          = ppnf8.person_id(+)
  AND ppnf8.person_id           = pasf8.person_id(+)
  AND UPPER(ppnf8.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf8.effective_start_date(+) AND ppnf8.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf8.effective_start_date(+) AND pasf8.effective_end_date(+)
  AND ppnf8.person_id                  = paam8.person_id (+)
  AND paam8.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam8.effective_latest_change(+) = 'Y'
  AND paam8.primary_flag(+)            = 'Y'
  AND paam8.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam8.effective_start_date(+) AND paam8.effective_end_date(+)
    --
  AND pasf8.manager_id          = ppnf9.person_id(+)
  AND ppnf9.person_id           = pasf9.person_id(+)
  AND UPPER(ppnf9.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf9.effective_start_date(+) AND ppnf9.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf9.effective_start_date(+) AND pasf9.effective_end_date(+)
  AND ppnf9.person_id                  = paam9.person_id (+)
  AND paam9.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam9.effective_latest_change(+) = 'Y'
  AND paam9.primary_flag(+)            = 'Y'
  AND paam9.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam9.effective_start_date(+) AND paam9.effective_end_date(+)
    --
  AND pasf9.manager_id           = ppnf10.person_id(+)
  AND ppnf10.person_id           = pasf10.person_id(+)
  AND UPPER(ppnf10.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf10.effective_start_date(+) AND ppnf10.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf10.effective_start_date(+) AND pasf10.effective_end_date(+)
  AND ppnf10.person_id                  = paam10.person_id (+)
  AND paam10.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam10.effective_latest_change(+) = 'Y'
  AND paam10.primary_flag(+)            = 'Y'
  AND paam10.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam10.effective_start_date(+) AND paam10.effective_end_date(+)
    --
  AND pasf10.manager_id          = ppnf11.person_id(+)
  AND ppnf11.person_id           = pasf11.person_id(+)
  AND UPPER(ppnf11.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf11.effective_start_date(+) AND ppnf11.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf11.effective_start_date(+) AND pasf11.effective_end_date(+)
  AND ppnf11.person_id                  = paam11.person_id (+)
  AND paam11.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam11.effective_latest_change(+) = 'Y'
  AND paam11.primary_flag(+)            = 'Y'
  AND paam11.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam11.effective_start_date(+) AND paam11.effective_end_date(+)
    --
  AND pasf11.manager_id          = ppnf12.person_id(+)
  AND ppnf12.person_id           = pasf12.person_id(+)
  AND UPPER(ppnf12.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf12.effective_start_date(+) AND ppnf12.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf12.effective_start_date(+) AND pasf12.effective_end_date(+)
  AND ppnf12.person_id                  = paam12.person_id (+)
  AND paam12.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam12.effective_latest_change(+) = 'Y'
  AND paam12.primary_flag(+)            = 'Y'
  AND paam12.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam12.effective_start_date(+) AND paam12.effective_end_date(+)
    --
  AND pasf12.manager_id          = ppnf13.person_id(+)
  AND ppnf13.person_id           = pasf13.person_id(+)
  AND UPPER(ppnf13.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf13.effective_start_date(+) AND ppnf13.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf13.effective_start_date(+) AND pasf13.effective_end_date(+)
  AND ppnf13.person_id                  = paam13.person_id (+)
  AND paam13.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam13.effective_latest_change(+) = 'Y'
  AND paam13.primary_flag(+)            = 'Y'
  AND paam13.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam13.effective_start_date(+) AND paam13.effective_end_date(+)
    --
  AND pasf13.manager_id          = ppnf14.person_id(+)
  AND ppnf14.person_id           = pasf14.person_id(+)
  AND UPPER(ppnf14.name_type(+)) = 'GLOBAL'
  AND :EFFECTIVE_DATE BETWEEN ppnf14.effective_start_date(+) AND ppnf14.effective_end_date(+)
  AND :EFFECTIVE_DATE BETWEEN pasf14.effective_start_date(+) AND pasf14.effective_end_date(+)
  AND ppnf14.person_id                  = paam14.person_id (+)
  AND paam14.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam14.effective_latest_change(+) = 'Y'
  AND paam14.primary_flag(+)            = 'Y'
  AND paam14.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam14.effective_start_date(+) AND paam14.effective_end_date(+)
    --
  AND pasf14.manager_id                 = ppnf15.person_id(+)
  AND UPPER(ppnf15.name_type(+))        = 'GLOBAL'
  AND ppnf15.person_id                  = paam15.person_id (+)
  AND paam15.assignment_type(+)        IN ( 'E', 'C', 'N', 'P' )
  AND paam15.effective_latest_change(+) = 'Y'
  AND paam15.primary_flag(+)            = 'Y'
  AND paam15.assignment_status_type(+)  = 'ACTIVE'
  AND :EFFECTIVE_DATE BETWEEN paam15.effective_start_date(+) AND paam15.effective_end_date(+)
    --
  AND paam.hourly_salaried_code      = 'S'
  AND pp.start_date                  &lt; :EFFECTIVE_DATE
  AND (ppos.actual_termination_date IS NULL
  OR ppos.actual_termination_date    &gt; :EFFECTIVE_DATE)
  AND pptl.user_person_type NOT IN ('Contingent Worker', 'Nonworker', 'Nonworker Paid')
    -- AND papf.person_number             = '3'
  </code></pre>