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, lookup.meaning 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_people_f per, per_all_people_f per1, per_jobs pj, hr_all_organization_units hou, per_deployment_factors pdf, hr_lookups lookup, po_vendors pov WHERE assign.job_id = pj.job_id (+) AND assign.organization_id = hou.organization_id AND trunc(SYSDATE) BETWEEN trunc(assign.effective_start_date) AND trunc(assign.effective_end_date) AND assign.primary_flag = 'Y' AND assign.assignment_type in ('E', 'C') AND per.person_id = assign.person_id AND assign.supervisor_id = per1.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 lookup.lookup_type = 'PERSON_TYPE' AND lookup.lookup_code = decode(per.current_employee_flag, 'Y', 'EMP', 'EX_EMP') AND pov.vendor_id (+) = assign.vendor_id

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
, LOOKUP.MEANING 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_PEOPLE_F PER
, PER_ALL_PEOPLE_F PER1
, PER_JOBS PJ
, HR_ALL_ORGANIZATION_UNITS HOU
, PER_DEPLOYMENT_FACTORS PDF
, HR_LOOKUPS LOOKUP
, PO_VENDORS POV
WHERE ASSIGN.JOB_ID = PJ.JOB_ID (+)
AND ASSIGN.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(ASSIGN.EFFECTIVE_START_DATE)
AND TRUNC(ASSIGN.EFFECTIVE_END_DATE)
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND PER.PERSON_ID = ASSIGN.PERSON_ID
AND ASSIGN.SUPERVISOR_ID = PER1.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 LOOKUP.LOOKUP_TYPE = 'PERSON_TYPE'
AND LOOKUP.LOOKUP_CODE = DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'EX_EMP')
AND POV.VENDOR_ID (+) = ASSIGN.VENDOR_ID