VIEW: APPS.POR_EMPLOYEE_LOV_V

Source

View Text - Preformatted

SELECT PAPF.FULL_NAME, PAPF.EMPLOYEE_NUMBER, PAPF.PERSON_ID, NULL, PBGP.NAME, PAPF.BUSINESS_GROUP_ID FROM PER_ALL_PEOPLE_F PAPF, PER_BUSINESS_GROUPS_PERF PBGP, PER_ALL_ASSIGNMENTS_F ASGN WHERE PAPF.BUSINESS_GROUP_ID = PBGP.BUSINESS_GROUP_ID AND PAPF.EMPLOYEE_NUMBER IS NOT NULL AND PAPF.EFFECTIVE_END_DATE IN (SELECT MAX(C.EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F C WHERE PAPF.PERSON_ID=C.PERSON_ID) AND ASGN.PERSON_ID = PAPF.PERSON_ID AND ASGN.PRIMARY_FLAG = 'Y' AND ASGN.ASSIGNMENT_TYPE = 'E' AND ASGN.EFFECTIVE_END_DATE IN (SELECT MAX(B.EFFECTIVE_END_DATE) FROM PER_ALL_ASSIGNMENTS_F B WHERE ASGN.PERSON_ID=B.PERSON_ID AND B.PRIMARY_FLAG = 'Y' AND B.ASSIGNMENT_TYPE = 'E' ) AND ASGN.LOCATION_ID IS NULL UNION ALL SELECT PAPF.FULL_NAME, PAPF.EMPLOYEE_NUMBER, PAPF.PERSON_ID, HRLT.LOCATION_CODE, PBGP.NAME, PAPF.BUSINESS_GROUP_ID FROM PER_ALL_PEOPLE_F PAPF, HR_LOCATIONS_ALL HRL, HR_LOCATIONS_ALL_TL HRLT, PER_ALL_ASSIGNMENTS_F ASGN, PER_BUSINESS_GROUPS_PERF PBGP WHERE PAPF.BUSINESS_GROUP_ID = PBGP.BUSINESS_GROUP_ID AND PAPF.EMPLOYEE_NUMBER IS NOT NULL AND PAPF.EFFECTIVE_END_DATE IN (SELECT MAX(C.EFFECTIVE_END_DATE) FROM PER_ALL_PEOPLE_F C WHERE PAPF.PERSON_ID=C.PERSON_ID) AND ASGN.PERSON_ID = PAPF.PERSON_ID AND ASGN.PRIMARY_FLAG = 'Y' AND ASGN.ASSIGNMENT_TYPE = 'E' AND ASGN.EFFECTIVE_END_DATE IN (SELECT MAX(D.EFFECTIVE_END_DATE) FROM PER_ALL_ASSIGNMENTS_F D WHERE ASGN.PERSON_ID=D.PERSON_ID AND D.PRIMARY_FLAG = 'Y' AND D.ASSIGNMENT_TYPE = 'E') AND ASGN.LOCATION_ID IS NOT NULL AND ASGN.LOCATION_ID = HRL.LOCATION_ID AND HRL.LOCATION_ID = HRLT.LOCATION_ID AND HRLT.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE

View Text - HTML Formatted

SELECT PAPF.FULL_NAME
, PAPF.EMPLOYEE_NUMBER
, PAPF.PERSON_ID
, NULL
, PBGP.NAME
, PAPF.BUSINESS_GROUP_ID
FROM PER_ALL_PEOPLE_F PAPF
, PER_BUSINESS_GROUPS_PERF PBGP
, PER_ALL_ASSIGNMENTS_F ASGN
WHERE PAPF.BUSINESS_GROUP_ID = PBGP.BUSINESS_GROUP_ID
AND PAPF.EMPLOYEE_NUMBER IS NOT NULL
AND PAPF.EFFECTIVE_END_DATE IN (SELECT MAX(C.EFFECTIVE_END_DATE)
FROM PER_ALL_PEOPLE_F C
WHERE PAPF.PERSON_ID=C.PERSON_ID)
AND ASGN.PERSON_ID = PAPF.PERSON_ID
AND ASGN.PRIMARY_FLAG = 'Y'
AND ASGN.ASSIGNMENT_TYPE = 'E'
AND ASGN.EFFECTIVE_END_DATE IN (SELECT MAX(B.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F B
WHERE ASGN.PERSON_ID=B.PERSON_ID
AND B.PRIMARY_FLAG = 'Y'
AND B.ASSIGNMENT_TYPE = 'E' )
AND ASGN.LOCATION_ID IS NULL UNION ALL SELECT PAPF.FULL_NAME
, PAPF.EMPLOYEE_NUMBER
, PAPF.PERSON_ID
, HRLT.LOCATION_CODE
, PBGP.NAME
, PAPF.BUSINESS_GROUP_ID
FROM PER_ALL_PEOPLE_F PAPF
, HR_LOCATIONS_ALL HRL
, HR_LOCATIONS_ALL_TL HRLT
, PER_ALL_ASSIGNMENTS_F ASGN
, PER_BUSINESS_GROUPS_PERF PBGP
WHERE PAPF.BUSINESS_GROUP_ID = PBGP.BUSINESS_GROUP_ID
AND PAPF.EMPLOYEE_NUMBER IS NOT NULL
AND PAPF.EFFECTIVE_END_DATE IN (SELECT MAX(C.EFFECTIVE_END_DATE)
FROM PER_ALL_PEOPLE_F C
WHERE PAPF.PERSON_ID=C.PERSON_ID)
AND ASGN.PERSON_ID = PAPF.PERSON_ID
AND ASGN.PRIMARY_FLAG = 'Y'
AND ASGN.ASSIGNMENT_TYPE = 'E'
AND ASGN.EFFECTIVE_END_DATE IN (SELECT MAX(D.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F D
WHERE ASGN.PERSON_ID=D.PERSON_ID
AND D.PRIMARY_FLAG = 'Y'
AND D.ASSIGNMENT_TYPE = 'E')
AND ASGN.LOCATION_ID IS NOT NULL
AND ASGN.LOCATION_ID = HRL.LOCATION_ID
AND HRL.LOCATION_ID = HRLT.LOCATION_ID
AND HRLT.LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE