View: JTF_RS_RES_EMP_VL

Product:	JTF - CRM Foundation
Description:	This view shows a few attributes of a Resource of type Employee
Implementation/DBA Data:	APPS.JTF_RS_RES_EMP_VL

View Text

SELECT RSC.RESOURCE_ID
, RSC.USER_ID
, PPL.PERSON_ID EMPLOYEE_ID
, PPL.FIRST_NAME FIRST_NAME
, PPL.LAST_NAME LAST_NAME
, PPL.MIDDLE_NAMES MIDDLE_NAME
, PPL.FULL_NAME FULL_NAME
, PPL.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPL.WORK_TELEPHONE PHONE
, PPL.EMAIL_ADDRESS EMAIL
FROM JTF_RS_RESOURCE_EXTNS RSC
, PER_PEOPLE_F PPL
, PER_ASSIGNMENTS_F ASG
, PER_PERIODS_OF_SERVICE PSR
WHERE RSC.CATEGORY = 'EMPLOYEE'
AND PPL.PERSON_ID = RSC.SOURCE_ID
AND ASG.PERSON_ID = PPL.PERSON_ID
AND ASG.PERIOD_OF_SERVICE_ID = PSR.PERIOD_OF_SERVICE_ID
AND TRUNC(SYSDATE) BETWEEN PPL.EFFECTIVE_START_DATE
AND PPL.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_START_DATE <= (SELECT MAX(NVL(F2.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)))
FROM PER_PERIODS_OF_SERVICE F2
WHERE F2.PERSON_ID = ASG.PERSON_ID
AND F2.DATE_START <= TRUNC(SYSDATE))
AND ASG.EFFECTIVE_END_DATE >= (SELECT MAX(NVL(F2.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)))
FROM PER_PERIODS_OF_SERVICE F2
WHERE F2.PERSON_ID = ASG.PERSON_ID
AND F2.DATE_START <= TRUNC(SYSDATE))
AND PPL.EMPLOYEE_NUMBER IS NOT NULL
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE = 'E'

Columns