View: PN_SPACE_ASSIGN_EMP_PUB_V

Product:	PN - Property Manager
Description:	Public view used to view employee assignment information
Implementation/DBA Data:	APPS.PN_SPACE_ASSIGN_EMP_PUB_V

View Text

SELECT PSAE.ROWID ROW_ID
, PSAE.EMP_SPACE_ASSIGN_ID EMP_SPACE_ASSIGN_ID
, PSAE.LOCATION_ID LOCATION_ID
, PL.LOCATION_ID LOCATION_LOCATION_ID
, PL.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, PL.SUITE LOCATION_SUITE_NAME
, PL.LOCATION_CODE LOCATION_CODE
, PL.ACTIVE_START_DATE ACTIVE_START_DATE
, PL.ACTIVE_END_DATE ACTIVE_END_DATE
, PSAE.PERSON_ID PERSON_ID
, PPF.EFFECTIVE_START_DATE EMP_EFFECTIVE_START_DATE
, PPF.EFFECTIVE_END_DATE EMP_EFFECTIVE_END_DATE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PPF.LAST_NAME LAST_NAME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPF.EMAIL_ADDRESS EMAIL_ADDRESS
, PPF.FIRST_NAME FIRST_NAME
, PPF.FULL_NAME FULL_NAME
, PPF.PERSON_TYPE_ID PERSON_TYPE_ID
, PPTTL.USER_PERSON_TYPE EMPLOYEE_TYPE
, PP.PHONE_NUMBER PHONE_NUMBER
, PAF.POSITION_ID POSITION_ID
, HR_GENERAL.DECODE_POSITION_LATEST_NAME(PAF.POSITION_ID) POSITION
, PAF.JOB_ID JOB_ID
, PJ.NAME JOB
, PAF.ORGANIZATION_ID ORGANIZATION_ID
, HOU.NAME ORGANIZATION
, PAF.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY
, FLV.MEANING EMPLOYMENT_CATEGORY_MEANING
, PSAE.PROJECT_ID PROJECT_ID
, PSAE.TASK_ID TASK_ID
, PSAE.EMP_ASSIGN_START_DATE EMP_ASSIGN_START_DATE
, PSAE.EMP_ASSIGN_END_DATE EMP_ASSIGN_END_DATE
, PSAE.COST_CENTER_CODE COST_CENTER_CODE
, PNP_UTIL_FUNC.GET_COST_CENTER(PSAE.PERSON_ID
, NULL
, PL.ORG_ID) CURRENT_COST_CENTER_CODE
, PSAE.ALLOCATED_AREA_PCT ALLOCATED_AREA_PCT
, PSAE.ALLOCATED_AREA ALLOCATED_AREA
, PL.UOM_CODE UOM_CODE
, PSAE.UTILIZED_AREA UTILIZED_AREA
, PSAE.EMP_SPACE_COMMENTS EMP_SPACE_COMMENTS
, PSAE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PSAE.LAST_UPDATED_BY LAST_UPDATED_BY
, PSAE.CREATION_DATE CREATION_DATE
, PSAE.CREATED_BY CREATED_BY
, PSAE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PSAE.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PSAE.ATTRIBUTE1 ATTRIBUTE1
, PSAE.ATTRIBUTE2 ATTRIBUTE2
, PSAE.ATTRIBUTE3 ATTRIBUTE3
, PSAE.ATTRIBUTE4 ATTRIBUTE4
, PSAE.ATTRIBUTE5 ATTRIBUTE5
, PSAE.ATTRIBUTE6 ATTRIBUTE6
, PSAE.ATTRIBUTE7 ATTRIBUTE7
, PSAE.ATTRIBUTE8 ATTRIBUTE8
, PSAE.ATTRIBUTE9 ATTRIBUTE9
, PSAE.ATTRIBUTE10 ATTRIBUTE10
, PSAE.ATTRIBUTE11 ATTRIBUTE11
, PSAE.ATTRIBUTE12 ATTRIBUTE12
, PSAE.ATTRIBUTE13 ATTRIBUTE13
, PSAE.ATTRIBUTE14 ATTRIBUTE14
, PSAE.ATTRIBUTE15 ATTRIBUTE15
, PL.ORG_ID ORG_ID
, PL.SOURCE SOURCE
FROM PER_JOBS PJ
, HR_ORGANIZATION_UNITS HOU
, FND_LOOKUP_VALUES FLV
, PER_PHONES PP
, PER_PERSON_TYPES_TL PPTTL
, PER_ALL_ASSIGNMENTS_F PAF
, PER_ALL_PEOPLE_F PPF
, PN_LOCATIONS_ALL PL
, PN_SPACE_ASSIGN_EMP_ALL PSAE
WHERE NVL(PL.COMMON_AREA_FLAG(+)
, 'N') = 'N'
AND PL.LOCATION_ID(+) = PSAE.LOCATION_ID
AND PNP_UTIL_FUNC.GET_AS_OF_DATE_4_EMP_PUBVIEW BETWEEN PL.ACTIVE_START_DATE(+)
AND PL.ACTIVE_END_DATE(+)
AND PPF.PERSON_ID(+) = PSAE.PERSON_ID
AND PNP_UTIL_FUNC.GET_AS_OF_DATE_4_EMP_PUBVIEW BETWEEN PPF.EFFECTIVE_START_DATE(+)
AND PPF.EFFECTIVE_END_DATE(+)
AND PAF.PERSON_ID(+) = PPF.PERSON_ID
AND PAF.PRIMARY_FLAG(+) = 'Y'
AND PAF.ASSIGNMENT_TYPE(+) <> 'B'
AND PNP_UTIL_FUNC.GET_AS_OF_DATE_4_EMP_PUBVIEW BETWEEN PAF.EFFECTIVE_START_DATE(+)
AND PAF.EFFECTIVE_END_DATE(+)
AND PP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PP.PARENT_ID(+) = PPF.PERSON_ID
AND PP.PHONE_TYPE(+) = 'W1'
AND PNP_UTIL_FUNC.GET_AS_OF_DATE_4_EMP_PUBVIEW BETWEEN PP.DATE_FROM(+)
AND NVL( PP.DATE_TO(+)
, TO_DATE('12/31/4712'
, 'MM/DD/YYYY'))
AND PPTTL.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
AND PPTTL.LANGUAGE = USERENV('LANG')
AND HOU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND PJ.JOB_ID(+) = PAF.JOB_ID
AND FLV.LANGUAGE(+) = USERENV('LANG')
AND FLV.LOOKUP_TYPE(+) = 'EMP_CAT'
AND FLV.LOOKUP_CODE(+) = PAF.EMPLOYMENT_CATEGORY UNION SELECT PSAE.ROWID ROW_ID
, PSAE.EMP_SPACE_ASSIGN_ID EMP_SPACE_ASSIGN_ID
, PSAE.LOCATION_ID LOCATION_ID
, PL.LOCATION_ID LOCATION_LOCATION_ID
, PL.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, PL.SUITE LOCATION_SUITE_NAME
, PL.LOCATION_CODE LOCATION_CODE
, PL.ACTIVE_START_DATE ACTIVE_START_DATE
, PL.ACTIVE_END_DATE ACTIVE_END_DATE
, PSAE.PERSON_ID PERSON_ID
, NULL EMP_EFFECTIVE_START_DATE
, NULL EMP_EFFECTIVE_END_DATE
, NULL ASSIGNMENT_ID
, NULL LAST_NAME
, NULL EMPLOYEE_NUMBER
, NULL EMAIL_ADDRESS
, NULL FIRST_NAME
, NULL FULL_NAME
, NULL PERSON_TYPE_ID
, NULL EMPLOYEE_TYPE
, NULL PHONE_NUMBER
, NULL POSITION_ID
, NULL POSITION
, NULL JOB_ID
, NULL JOB
, NULL ORGANIZATION_ID
, NULL ORGANIZATION
, NULL EMPLOYMENT_CATEGORY
, NULL EMPLOYMENT_CATEGORY_MEANING
, PSAE.PROJECT_ID PROJECT_ID
, PSAE.TASK_ID TASK_ID
, PSAE.EMP_ASSIGN_START_DATE EMP_ASSIGN_START_DATE
, PSAE.EMP_ASSIGN_END_DATE EMP_ASSIGN_END_DATE
, PSAE.COST_CENTER_CODE COST_CENTER_CODE
, NULL CURRENT_COST_CENTER_CODE
, PSAE.ALLOCATED_AREA_PCT ALLOCATED_AREA_PCT
, PSAE.ALLOCATED_AREA ALLOCATED_AREA
, PL.UOM_CODE UOM_CODE
, PSAE.UTILIZED_AREA UTILIZED_AREA
, PSAE.EMP_SPACE_COMMENTS EMP_SPACE_COMMENTS
, PSAE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PSAE.LAST_UPDATED_BY LAST_UPDATED_BY
, PSAE.CREATION_DATE CREATION_DATE
, PSAE.CREATED_BY CREATED_BY
, PSAE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PSAE.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PSAE.ATTRIBUTE1 ATTRIBUTE1
, PSAE.ATTRIBUTE2 ATTRIBUTE2
, PSAE.ATTRIBUTE3 ATTRIBUTE3
, PSAE.ATTRIBUTE4 ATTRIBUTE4
, PSAE.ATTRIBUTE5 ATTRIBUTE5
, PSAE.ATTRIBUTE6 ATTRIBUTE6
, PSAE.ATTRIBUTE7 ATTRIBUTE7
, PSAE.ATTRIBUTE8 ATTRIBUTE8
, PSAE.ATTRIBUTE9 ATTRIBUTE9
, PSAE.ATTRIBUTE10 ATTRIBUTE10
, PSAE.ATTRIBUTE11 ATTRIBUTE11
, PSAE.ATTRIBUTE12 ATTRIBUTE12
, PSAE.ATTRIBUTE13 ATTRIBUTE13
, PSAE.ATTRIBUTE14 ATTRIBUTE14
, PSAE.ATTRIBUTE15 ATTRIBUTE15
, PL.ORG_ID ORG_ID
, PL.SOURCE SOURCE
FROM PN_LOCATIONS_ALL PL
, PN_SPACE_ASSIGN_EMP_ALL PSAE
WHERE PSAE.PERSON_ID IS NULL
AND NVL(PL.COMMON_AREA_FLAG(+)
, 'N') = 'N'
AND PL.LOCATION_ID(+) = PSAE.LOCATION_ID
AND PNP_UTIL_FUNC.GET_AS_OF_DATE_4_EMP_PUBVIEW BETWEEN PL.ACTIVE_START_DATE(+)
AND PL.ACTIVE_END_DATE(+)

Columns