VIEW: APPS.PA_NOMINATE_RESOURCE_LOV_V

Source

View Text - Preformatted

SELECT per.person_id resource_source_id, per.full_name resource_name, per.employee_number resource_number, assign.organization_id resource_Organization_id, hou.name resource_organization_name, assign.effective_start_date assignment_start_date, assign.effective_end_date assignment_end_date, assign.supervisor_id supervisor_id, pa_lookups.meaning, per.email_address from per_people_f per, hr_all_organization_units hou, per_assignments_f assign, per_job_extra_info jei, per_assignment_status_types ast, pa_lookups WHERE per.person_id = assign.person_id and assign.organization_id in (select distinct organization_id from pa_all_organizations where pa_org_use_type = 'EXPENDITURES' and inactive_date is NULL and organization_id = assign.organization_id) and assign.primary_flag = 'Y' and assign.assignment_type in ('E', 'C') and assign.job_id is not null and sysdate <= assign.effective_end_date and ast.assignment_status_type_id = assign.assignment_status_type_id and ast.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK') and assign.effective_start_date between per.effective_start_date and per.effective_end_date and assign.organization_id = hou.organization_id and assign.job_id = jei.job_id and information_type = 'Job Category' and jei.jei_information6 = 'Y' and lookup_type = 'PA_PERSON_TYPE' and ((lookup_code = 'EMP' and per.current_employee_flag = 'Y') or (lookup_code = 'CWK' and per.current_employee_flag <> 'Y'))

View Text - HTML Formatted

SELECT PER.PERSON_ID RESOURCE_SOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, PER.EMPLOYEE_NUMBER RESOURCE_NUMBER
, ASSIGN.ORGANIZATION_ID RESOURCE_ORGANIZATION_ID
, HOU.NAME RESOURCE_ORGANIZATION_NAME
, ASSIGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASSIGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, ASSIGN.SUPERVISOR_ID SUPERVISOR_ID
, PA_LOOKUPS.MEANING
, PER.EMAIL_ADDRESS
FROM PER_PEOPLE_F PER
, HR_ALL_ORGANIZATION_UNITS HOU
, PER_ASSIGNMENTS_F ASSIGN
, PER_JOB_EXTRA_INFO JEI
, PER_ASSIGNMENT_STATUS_TYPES AST
, PA_LOOKUPS
WHERE PER.PERSON_ID = ASSIGN.PERSON_ID
AND ASSIGN.ORGANIZATION_ID IN (SELECT DISTINCT ORGANIZATION_ID
FROM PA_ALL_ORGANIZATIONS
WHERE PA_ORG_USE_TYPE = 'EXPENDITURES'
AND INACTIVE_DATE IS NULL
AND ORGANIZATION_ID = ASSIGN.ORGANIZATION_ID)
AND ASSIGN.PRIMARY_FLAG = 'Y'
AND ASSIGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASSIGN.JOB_ID IS NOT NULL
AND SYSDATE <= ASSIGN.EFFECTIVE_END_DATE
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASSIGN.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK')
AND ASSIGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND ASSIGN.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND ASSIGN.JOB_ID = JEI.JOB_ID
AND INFORMATION_TYPE = 'JOB CATEGORY'
AND JEI.JEI_INFORMATION6 = 'Y'
AND LOOKUP_TYPE = 'PA_PERSON_TYPE'
AND ((LOOKUP_CODE = 'EMP'
AND PER.CURRENT_EMPLOYEE_FLAG = 'Y') OR (LOOKUP_CODE = 'CWK'
AND PER.CURRENT_EMPLOYEE_FLAG <> 'Y'))