VIEW: APPS.PN_SPACE_ASSIGN_EMP_PUB_V

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)