VIEW: APPS.PA_C_RES_DETAILS_V

Source

View Text - Preformatted

SELECT per.person_id resource_source_id, per.full_name resource_name, Decode(per.current_employee_flag,'Y',per.employee_number, per.npw_number) resource_number, per.email_address resource_email, assign.job_id resource_job_id, pj.NAME AS resource_job_name, pa_job_utils.Get_job_level(pj.job_id) resource_job_level, pa_job_utils.Get_job_name(pj.job_id,pj.job_group_id) mapped_job_name, pa_job_utils.Get_job_level(pj.job_id,pj.job_group_id) mapped_job_level, assign.organization_id resource_organization_id, hou.NAME resource_organization_name, pa_schedule_utils.Get_res_calendar_name(NULL,SYSDATE,per.person_id) resource_calendar_name, per1.full_name manager_name, per1.person_id manager_id, pdf.only_current_location work_in_current_location_only, pdf.work_any_location work_in_all_locations, pdf.travel_required travel_domestically, pdf.visit_internationally travel_internationally, hr_person_type_usage_info.Get_user_person_type(SYSDATE,per.person_id) person_type, pa_resource_utils.Get_projected_end_date(per.person_id) projected_end_date, pov.vendor_name supplier_name FROM per_assignments_f assign, per_all_people_f per, per_all_people_f per1, per_jobs pj, hr_all_organization_units hou, per_deployment_factors pdf, po_vendors pov WHERE assign.job_id = pj.job_id (+) AND assign.organization_id = hou.organization_id AND assign.primary_flag = 'Y' AND assign.assignment_type IN ('E','C') AND (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y') AND assign.supervisor_id = per1.person_id (+) AND per.person_id = assign.person_id AND per.person_id = pdf.person_id (+) AND Trunc(SYSDATE) BETWEEN Trunc(per.effective_start_date) AND Trunc(per.effective_end_date) AND Trunc(SYSDATE) BETWEEN Trunc(per1.effective_start_date (+)) AND Trunc(per1.effective_end_date (+)) AND pov.vendor_id (+) = assign.vendor_id AND Trunc(SYSDATE) BETWEEN Trunc(assign.effective_start_date) AND Trunc(assign.effective_end_date) UNION SELECT per.person_id resource_source_id, per.full_name resource_name, Decode(per.current_employee_flag,'Y',per.employee_number, per.npw_number) resource_number, per.email_address resource_email, NULL resource_job_id, NULL AS resource_job_name, NULL resource_job_level, NULL mapped_job_name, NULL mapped_job_level, NULL resource_organization_id, NULL resource_organization_name, NULL resource_calendar_name, NULL manager_name, NULL manager_id, NULL work_in_current_location_only, NULL work_in_all_locations, NULL travel_domestically, NULL travel_internationally, hr_person_type_usage_info.Get_user_person_type(SYSDATE,per.person_id) person_type, NULL projected_end_date, NULL supplier_name FROM per_all_people_f per WHERE ((per.current_employee_flag IS NULL AND per.employee_number IS NOT NULL) OR (per.current_npw_flag IS NULL AND per.npw_number IS NOT NULL)) AND Trunc(SYSDATE) BETWEEN Trunc(per.effective_start_date) AND Trunc(per.effective_end_date)

View Text - HTML Formatted

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, PER.EMAIL_ADDRESS RESOURCE_EMAIL
, ASSIGN.JOB_ID RESOURCE_JOB_ID
, PJ.NAME AS RESOURCE_JOB_NAME
, PA_JOB_UTILS.GET_JOB_LEVEL(PJ.JOB_ID) RESOURCE_JOB_LEVEL
, PA_JOB_UTILS.GET_JOB_NAME(PJ.JOB_ID
, PJ.JOB_GROUP_ID) MAPPED_JOB_NAME
, PA_JOB_UTILS.GET_JOB_LEVEL(PJ.JOB_ID
, PJ.JOB_GROUP_ID) MAPPED_JOB_LEVEL
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, HOU.NAME RESOURCE_ORGANIZATION_NAME
, PA_SCHEDULE_UTILS.GET_RES_CALENDAR_NAME(NULL
, SYSDATE
, PER.PERSON_ID) RESOURCE_CALENDAR_NAME
, PER1.FULL_NAME MANAGER_NAME
, PER1.PERSON_ID MANAGER_ID
, PDF.ONLY_CURRENT_LOCATION WORK_IN_CURRENT_LOCATION_ONLY
, PDF.WORK_ANY_LOCATION WORK_IN_ALL_LOCATIONS
, PDF.TRAVEL_REQUIRED TRAVEL_DOMESTICALLY
, PDF.VISIT_INTERNATIONALLY TRAVEL_INTERNATIONALLY
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE
, PER.PERSON_ID) PERSON_TYPE
, PA_RESOURCE_UTILS.GET_PROJECTED_END_DATE(PER.PERSON_ID) PROJECTED_END_DATE
, POV.VENDOR_NAME SUPPLIER_NAME
FROM PER_ASSIGNMENTS_F ASSIGN
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER1
, PER_JOBS PJ
, HR_ALL_ORGANIZATION_UNITS HOU
, PER_DEPLOYMENT_FACTORS PDF
, PO_VENDORS POV
WHERE ASSIGN.JOB_ID = PJ.JOB_ID (+)
AND ASSIGN.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND (PER.CURRENT_EMPLOYEE_FLAG = 'Y' OR PER.CURRENT_NPW_FLAG = 'Y')
AND ASSIGN.SUPERVISOR_ID = PER1.PERSON_ID (+)
AND PER.PERSON_ID = ASSIGN.PERSON_ID
AND PER.PERSON_ID = PDF.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PER.EFFECTIVE_START_DATE)
AND TRUNC(PER.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PER1.EFFECTIVE_START_DATE (+))
AND TRUNC(PER1.EFFECTIVE_END_DATE (+))
AND POV.VENDOR_ID (+) = ASSIGN.VENDOR_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(ASSIGN.EFFECTIVE_START_DATE)
AND TRUNC(ASSIGN.EFFECTIVE_END_DATE) UNION SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) RESOURCE_NUMBER
, PER.EMAIL_ADDRESS RESOURCE_EMAIL
, NULL RESOURCE_JOB_ID
, NULL AS RESOURCE_JOB_NAME
, NULL RESOURCE_JOB_LEVEL
, NULL MAPPED_JOB_NAME
, NULL MAPPED_JOB_LEVEL
, NULL RESOURCE_ORGANIZATION_ID
, NULL RESOURCE_ORGANIZATION_NAME
, NULL RESOURCE_CALENDAR_NAME
, NULL MANAGER_NAME
, NULL MANAGER_ID
, NULL WORK_IN_CURRENT_LOCATION_ONLY
, NULL WORK_IN_ALL_LOCATIONS
, NULL TRAVEL_DOMESTICALLY
, NULL TRAVEL_INTERNATIONALLY
, HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE
, PER.PERSON_ID) PERSON_TYPE
, NULL PROJECTED_END_DATE
, NULL SUPPLIER_NAME
FROM PER_ALL_PEOPLE_F PER
WHERE ((PER.CURRENT_EMPLOYEE_FLAG IS NULL
AND PER.EMPLOYEE_NUMBER IS NOT NULL) OR (PER.CURRENT_NPW_FLAG IS NULL
AND PER.NPW_NUMBER IS NOT NULL))
AND TRUNC(SYSDATE) BETWEEN TRUNC(PER.EFFECTIVE_START_DATE)
AND TRUNC(PER.EFFECTIVE_END_DATE)