VIEW: APPS.HR_CURRENT_PEOPLE_LOV_V

Source

View Text - Preformatted

SELECT ppf.full_name full_name, ppf.person_id person_id, ppf.business_group_id, ppf.person_type_id, ppf.effective_start_date, ppf.effective_end_date, ho.name organization, hl.location_code location, hpt.name position, ppf.employee_number employee_number, ho2.name business_group_name, pjt.name job_title, ppf.email_address email_address, ppf.office_number office_number, ppf.current_employee_flag from per_all_people_f ppf, hr_locations_all_tl hl, hr_all_organization_units_tl ho, per_all_assignments_f paf, hr_all_positions_f_tl hpt, hr_all_positions_f hp, hr_all_organization_units_tl ho2, per_jobs_tl pjt, per_periods_of_service ppos WHERE paf.person_id = ppf.person_id AND paf.assignment_type = 'E' AND paf.location_id = hl.location_id (+) AND paf.organization_id = ho.organization_id AND paf.position_id = hp.position_id (+) AND paf.primary_flag = 'Y' AND trunc(sysdate) between paf.effective_start_date AND paf.effective_end_date AND trunc(sysdate) between ppf.effective_start_date AND ppf.effective_end_date AND trunc(sysdate) between nvl(hp.effective_start_date, trunc(sysdate) ) AND nvl(hp.effective_end_date, trunc(sysdate)) AND hpt.position_id(+) = hp.position_id AND hpt.language(+) = userenv('LANG') AND ho.language=USERENV('LANG') AND nvl(hl.language, USERENV('LANG'))= USERENV('LANG') AND paf.business_group_id = ho2.organization_id AND ho2.language = USERENV('LANG') AND paf.job_id = pjt.job_id (+) AND pjt.language(+) = userenv('LANG') AND ppf.person_id = ppos.person_id AND trunc(sysdate) between ppos.date_start AND nvl(ppos.actual_termination_date, trunc(sysdate) )

View Text - HTML Formatted

SELECT PPF.FULL_NAME FULL_NAME
, PPF.PERSON_ID PERSON_ID
, PPF.BUSINESS_GROUP_ID
, PPF.PERSON_TYPE_ID
, PPF.EFFECTIVE_START_DATE
, PPF.EFFECTIVE_END_DATE
, HO.NAME ORGANIZATION
, HL.LOCATION_CODE LOCATION
, HPT.NAME POSITION
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HO2.NAME BUSINESS_GROUP_NAME
, PJT.NAME JOB_TITLE
, PPF.EMAIL_ADDRESS EMAIL_ADDRESS
, PPF.OFFICE_NUMBER OFFICE_NUMBER
, PPF.CURRENT_EMPLOYEE_FLAG
FROM PER_ALL_PEOPLE_F PPF
, HR_LOCATIONS_ALL_TL HL
, HR_ALL_ORGANIZATION_UNITS_TL HO
, PER_ALL_ASSIGNMENTS_F PAF
, HR_ALL_POSITIONS_F_TL HPT
, HR_ALL_POSITIONS_F HP
, HR_ALL_ORGANIZATION_UNITS_TL HO2
, PER_JOBS_TL PJT
, PER_PERIODS_OF_SERVICE PPOS
WHERE PAF.PERSON_ID = PPF.PERSON_ID
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.LOCATION_ID = HL.LOCATION_ID (+)
AND PAF.ORGANIZATION_ID = HO.ORGANIZATION_ID
AND PAF.POSITION_ID = HP.POSITION_ID (+)
AND PAF.PRIMARY_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN NVL(HP.EFFECTIVE_START_DATE
, TRUNC(SYSDATE) )
AND NVL(HP.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND HPT.POSITION_ID(+) = HP.POSITION_ID
AND HPT.LANGUAGE(+) = USERENV('LANG')
AND HO.LANGUAGE=USERENV('LANG')
AND NVL(HL.LANGUAGE
, USERENV('LANG'))= USERENV('LANG')
AND PAF.BUSINESS_GROUP_ID = HO2.ORGANIZATION_ID
AND HO2.LANGUAGE = USERENV('LANG')
AND PAF.JOB_ID = PJT.JOB_ID (+)
AND PJT.LANGUAGE(+) = USERENV('LANG')
AND PPF.PERSON_ID = PPOS.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PPOS.DATE_START
AND NVL(PPOS.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE) )