VIEW: APPS.PA_NOMINATE_RES_LOV_STAFF_V

Source

View Text - Preformatted

SELECT assign.person_id resource_source_id, per.full_name resource_name, decode(per.current_employee_flag, 'Y', per.employee_number, per.npw_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, sup.person_id staffing_analyst_id, decode(per.current_employee_flag, 'Y', 'EMP', 'CWK') person_type from per_all_people_f per, per_all_people_f sup, hr_all_organization_units hou, per_assignments_f assign, per_job_extra_info jei, per_assignment_status_types ast, fnd_grants fg, fnd_objects fob, wf_roles wfr, (select pa_security_pvt.get_menu_id('PA_PRM_RES_AUTH') menu_id from dual) temp 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 fg.instance_pk1_value = to_char(assign.organization_id) and fg.instance_type = 'INSTANCE' and fg.object_id = fob.object_id and fob.obj_name = 'ORGANIZATION' and fg.grantee_type = 'USER' and fg.grantee_key = wfr.name and wfr.orig_system = 'HZ_PARTY' and sup.party_id = wfr.orig_system_id and sysdate between sup.effective_start_date and sup.effective_end_date and fg.menu_id = temp.menu_id and trunc(SYSDATE) between trunc(fg.start_date) and trunc(NVL(fg.end_date, SYSDATE+1)) 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'

View Text - HTML Formatted

SELECT ASSIGN.PERSON_ID RESOURCE_SOURCE_ID
, PER.FULL_NAME RESOURCE_NAME
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_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
, SUP.PERSON_ID STAFFING_ANALYST_ID
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, 'EMP'
, 'CWK') PERSON_TYPE
FROM PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F SUP
, HR_ALL_ORGANIZATION_UNITS HOU
, PER_ASSIGNMENTS_F ASSIGN
, PER_JOB_EXTRA_INFO JEI
, PER_ASSIGNMENT_STATUS_TYPES AST
, FND_GRANTS FG
, FND_OBJECTS FOB
, WF_ROLES WFR
, (SELECT PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_RES_AUTH') MENU_ID
FROM DUAL) TEMP
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 FG.INSTANCE_PK1_VALUE = TO_CHAR(ASSIGN.ORGANIZATION_ID)
AND FG.INSTANCE_TYPE = 'INSTANCE'
AND FG.OBJECT_ID = FOB.OBJECT_ID
AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.GRANTEE_TYPE = 'USER'
AND FG.GRANTEE_KEY = WFR.NAME
AND WFR.ORIG_SYSTEM = 'HZ_PARTY'
AND SUP.PARTY_ID = WFR.ORIG_SYSTEM_ID
AND SYSDATE BETWEEN SUP.EFFECTIVE_START_DATE
AND SUP.EFFECTIVE_END_DATE
AND FG.MENU_ID = TEMP.MENU_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE
, SYSDATE+1))
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'