VIEW: APPS.HR_ASSIGN_LOV_V

Source

View Text - Preformatted

SELECT distinct peo.full_name FULL_NAME, peo.person_id PERSON_ID, peo.business_group_id BUSINESS_GROUP_ID, peo.effective_start_date EFFECTIVE_START_DATE, peo.effective_end_date EFFECTIVE_END_DATE, peo.employee_number EMPLOYEE_NUMBER, jbt.name JOB_NAME, org.name ORGANIZATION, peo.email_address EMAIL_ADDRESS, bus.name BUSINESS_GROUP, paf.effective_start_date ASN_EFFECTIVE_START_DATE, paf.effective_end_date ASN_EFFECTIVE_END_DATE from per_all_people_f peo, per_all_assignments_f paf, per_jobs_tl jbt, hr_all_organization_units_tl org, hr_all_organization_units_tl bus WHERE peo.person_id in ( select person_id from per_all_assignments_f where assignment_type ='E' and primary_flag = 'Y') and peo.person_type_id in ( select person_type_id from per_person_types where system_person_type in('EMP','EMP_APL')) and peo.person_id = paf.person_id and paf.business_group_id = bus.organization_id and paf.primary_flag = 'Y' and paf.assignment_type = 'E' and bus.language = userenv('LANG') and paf.job_id = jbt.job_id (+) and jbt.language(+) = userenv('LANG') and paf.organization_id = org.organization_id (+) and org.language = userenv('LANG')

View Text - HTML Formatted

SELECT DISTINCT PEO.FULL_NAME FULL_NAME
, PEO.PERSON_ID PERSON_ID
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PEO.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, JBT.NAME JOB_NAME
, ORG.NAME ORGANIZATION
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, BUS.NAME BUSINESS_GROUP
, PAF.EFFECTIVE_START_DATE ASN_EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE ASN_EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PEO
, PER_ALL_ASSIGNMENTS_F PAF
, PER_JOBS_TL JBT
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, HR_ALL_ORGANIZATION_UNITS_TL BUS
WHERE PEO.PERSON_ID IN ( SELECT PERSON_ID
FROM PER_ALL_ASSIGNMENTS_F
WHERE ASSIGNMENT_TYPE ='E'
AND PRIMARY_FLAG = 'Y')
AND PEO.PERSON_TYPE_ID IN ( SELECT PERSON_TYPE_ID
FROM PER_PERSON_TYPES
WHERE SYSTEM_PERSON_TYPE IN('EMP'
, 'EMP_APL'))
AND PEO.PERSON_ID = PAF.PERSON_ID
AND PAF.BUSINESS_GROUP_ID = BUS.ORGANIZATION_ID
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND BUS.LANGUAGE = USERENV('LANG')
AND PAF.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND PAF.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND ORG.LANGUAGE = USERENV('LANG')