View: PA_R_PROJECT_RESOURCES_IND_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_R_PROJECT_RESOURCES_IND_V

View Text

SELECT ASGN.PERSON_ID PERSON_ID
, SUBSTR(PER.FULL_NAME
, 1
, 60) NAME
, ASGN.ORGANIZATION_ID ORGANIZATION_ID
, ASGN.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASGN.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, SYSDATE START_DATE
, PO.ACTUAL_TERMINATION_DATE TERMINATION_DATE
, PTYPE.SYSTEM_PERSON_TYPE P_TYPE
, PTYPE.USER_PERSON_TYPE USER_TYPE
, ASGN.LOCATION_ID
, PER.EFFECTIVE_START_DATE PER_START_DATE
, PER.EFFECTIVE_END_DATE PER_END_DATE
, DECODE(PER.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER) PER_EMP_NUMBER
, PER.EMAIL_ADDRESS PER_EMAIL
, PER.WORK_TELEPHONE PER_WORK_PHONE
, PER.BUSINESS_GROUP_ID PER_BUSINESS_GROUP_ID
, PER.FIRST_NAME PER_FIRST_NAME
, PER.LAST_NAME PER_LAST_NAME
, PER.MIDDLE_NAMES PER_MIDDLE_NAME
, JOB.NAME JOB_NAME
, ASGN.SUPERVISOR_ID SUPERVISOR_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(PER.BUSINESS_GROUP_ID) ORG_NAME
, 'EMPLOYEE' RESOURCE_TYPE
, ASGN.JOB_ID
, JOB.JOB_GROUP_ID JOB_GROUP_ID
FROM PER_ALL_ASSIGNMENTS_F ASGN
, (SELECT PERSON_ID
, ACTUAL_TERMINATION_DATE
FROM PER_PERIODS_OF_SERVICE UNION ALL SELECT PERSON_ID
, ACTUAL_TERMINATION_DATE
FROM PER_PERIODS_OF_PLACEMENT) PO
, PER_ALL_PEOPLE_F PER
, PER_PERSON_TYPES PTYPE
, PER_PERSON_TYPE_USAGES_F PTU
, PER_ASSIGNMENT_STATUS_TYPES STATUS
, PER_JOBS JOB
WHERE ASGN.PERSON_ID = PER.PERSON_ID
AND ASGN.ASSIGNMENT_STATUS_TYPE_ID = STATUS.ASSIGNMENT_STATUS_TYPE_ID
AND STATUS.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK')
AND PER.PERSON_ID = PTU.PERSON_ID
AND ASGN.EFFECTIVE_START_DATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE
AND PTU.PERSON_TYPE_ID = PTYPE.PERSON_TYPE_ID
AND PTYPE.SYSTEM_PERSON_TYPE IN ('EMP'
, 'CWK')
AND (PER.CURRENT_EMPLOYEE_FLAG = 'Y' OR PER.CURRENT_NPW_FLAG = 'Y')
AND ASGN.PRIMARY_FLAG = 'Y'
AND ASGN.PERSON_ID = PO.PERSON_ID
AND NVL(PO.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND ASGN.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ASGN.JOB_ID IS NOT NULL
AND ASGN.JOB_ID = JOB.JOB_ID
AND ASGN.EFFECTIVE_START_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE

Columns