Query: People

select
    ppf.employee_number       "Employee Number",
--
    ppf.last_name             "Last Name",
    ppf.first_name            "First Name",
    ppf.title                 "Title",
    ppf.pre_name_adjunct      "Prefix",
    ppf.suffix                "Suffix",
    ppf.middle_names          "Middle",
    decode(ppf.sex,
           'M','Male',
           'F','Female',
           ppf.sex)             "Gender",
    ppttl.user_person_type      "Person Type",
    ppf.applicant_number        "Applicant Number",
    ppf.national_identifier     "Social Security",
    ppf.effective_start_date    "Effective Date - From",
    ppf.effective_end_date      "Effective Date - To",
    ppf.start_date              "Latest Start Date",
    --
    ppf.date_of_birth         "Personal: Birth Date",
    ppf.town_of_birth         "Personal: Town Of Birth",
    ppf.region_of_birth       "Personal: Region of Birth",
    ppf.country_of_birth      "Personal: Country of Birth",
    trunc((sysdate -
      ppf.date_of_birth)/365) "Personal: Age",
    hl1.meaning               "Personal: Status",                
    ppf.nationality           "Personal: Nationality",
    hl2.meaning               "Personal: Registered Disabled",
    --
    hl5.meaning                         "Employment:Ethnic Origin",
    decode(ppf.per_information11,
        'N','No',
        'Y','Yes',
        ppf.per_information11)          "Employment:Ethnicity Disclosed",
    hl6.meaning                         "Employment:VETS100",
    decode(ppf.per_information9,
        'N','No',
        'Y','Yes',
        ppf.per_information9)           "Employment:Child Support",
    decode(ppf.per_information10,
        'N','No',
        'Y','Yes',
        ppf.per_information10)          "Employment:Opted for Medicare",
    decode(ppf.per_information2,
        'N','No',
        'Y','Yes',
        ppf.per_information2)           "Employment:I-9 Status",
    ppf.per_information3                "Employment:I-9 Expiration",
    hl7.meaning                         "Employment:New Hire",
    hl9.meaning                         "Employment:Exception Reason",
    hl8.meaning                         "Employment:Vets 100A",
    ppf.office_number              "Office Details: Office",
    ppf.internal_location          "Office Details:Location",
    ppf.mailstop                   "Office Details:MailStop",
    ppf.email_address              "Office Details:Email Address",
    hl1.meaning                    "Office Details:Mail To",
    ppf.resume_exists              "Applicant:Resume Exists",
    ppf.resume_last_updated        "Applicant:Last Updated",
    ppf.hold_applicant_date_until  "Hold Application Until",
    ppf.background_check_status    "Background:Checked",
    ppf.background_date_check      "Background:Date Checked",
    ppf.rehire_recommendation      "Rehire:Recommendation",
    ppf.rehire_reason              "Rehire:Reason",
    ppf.honors                     "Further Name:Honors",
    ppf.known_as                   "Further Name:Preferred Name",
    ppf.previous_last_name         "Previuos Last Name",
    hl3.meaning                    "Medical: Blood Type",
    ppf.last_medical_test_date     "Medical:Last Test Date",
    ppf.last_medical_test_by       "Medical:Last Test By",
    ppf.work_schedule              "Other:Work Schedule",
    ppf.fte_capacity               "Other:Full Time Availability",
    ppf.correspondence_language    "Correspondance language",
    ppf.date_of_death              "Date of Death",
    ppf.student_status             "Other:Student Status",
    ppf.date_employee_data_verified  "Other:Date Last Verified",
    ppf.on_military_service          "Other:On Military Service",
    ppf.second_passport_exists       "Other:Second Passport Exist",
    bbg.name                         "Benifits:Benifit Group",
    ppf.uses_tobacco_flag            "Benifits:Uses Tobacco",
    ppf.coord_ben_med_pln_no         "Benifits:Medical Plan",
    ppf.dpdnt_adoption_date          "Benifits:Adoption Date",
    ppf.receipt_of_death_cert_date   "Date Received Death Cert",
    pps.adjusted_svc_date            "Benifits:Adjusted Service Date" ,
    ppf.original_date_of_hire        "Benifits:Original Date Of Hire",
    ppf.coord_ben_no_cvg_flag        "Benifits:Other Coverage",
    ppf.dpdnt_vlntry_svce_flag       "Benifits:Voluntary Service",
    --
    ppf.person_id
from
    per_people_f ppf,
    per_person_types ppt,
    per_person_types_tl ppttl,
    hr_lookups hl1,
    hr_lookups hl2,
    ben_benfts_grp bbg,
    per_periods_of_service pps,
    hr_lookups hl11,
    hr_lookups hl12,
    hr_lookups hl3,
    hr_lookups hl4,
    hr_lookups hl5,
    hr_lookups hl6,
    hr_lookups hl7,
    hr_lookups hl8,
    hr_lookups hl9
where
    1=1
and ppt.person_type_id = ppf.person_type_id
and ppt.person_type_id = ppttl.person_type_id
and bbg.benfts_grp_id(+) = ppf.benefit_group_id
and pps.person_id(+) = ppf.person_id
and hl1.lookup_type(+) = 'STUDENT_STATUS'
and hl1.lookup_code(+) = ppf.student_status
and hl2.lookup_type(+) = 'REGISTERED_DISABLED'
and hl2.lookup_code(+) = ppf.registered_disabled_flag
and hl11.lookup_type(+) = 'HOME_OFFICE'
and hl11.lookup_code(+) = ppf.expense_check_send_to_address
and hl12.lookup_type(+) = 'WORK_SCHEDULE'
and hl12.lookup_code(+) = ppf.work_schedule
and hl3.lookup_type(+) = 'BLOOD_TYPE'
and hl3.lookup_code(+) = ppf.blood_type
and hl4.lookup_type(+) = 'STUDENT_STATUS'
and hl4.lookup_code(+) = ppf.student_status
and hl5.lookup_type(+) = 'US_ETHNIC_GROUP'
and hl5.lookup_code(+) = ppf.per_information1
and hl6.lookup_type(+) = 'US_VETERAN_STATUS'
and hl6.lookup_code(+) = ppf.per_information5
and hl7.lookup_type(+) = 'US_NEW_HIRE_STATUS'
and hl7.lookup_code(+) = ppf.per_information7
and hl8.lookup_type(+) = 'US_VETERAN_STATUS'
and hl8.lookup_code(+) = ppf.per_information25
and hl9.lookup_type(+) = 'US_NEW_HIRE_EXCEPTIONS'
and hl9.lookup_code(+) = ppf.per_information8
and ppttl.language = userenv ('LANG')
--
and ppf.employee_number = '1309'
order by
    1,2,3,4