View: JTF_RS_EMPLOYEES_VL

Product:	JTF - CRM Foundation
Description:	This view has the list of all Employees that can be importedor is already present in Resource Manager
Implementation/DBA Data:	APPS.JTF_RS_EMPLOYEES_VL

View Text

SELECT PPL.PERSON_ID PERSON_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
, NULL JOB_TITLE
, ASG.SUPERVISOR_ID MANAGER_PERSON_ID
, EMP.FULL_NAME MANAGER_NAME
, PPL.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PPL.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, NULL EMP_CATEGORY
, NULL EMP_STATUS
, PPL.BUSINESS_GROUP_ID ORG_ID
, ORG.NAME ORG_NAME
FROM PER_PEOPLE_F PPL
, PER_ASSIGNMENTS_F ASG
, PER_EMPLOYEES_X EMP
, PER_PERIODS_OF_SERVICE PSR
, HR_ALL_ORGANIZATION_UNITS ORG
WHERE 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.SUPERVISOR_ID = EMP.EMPLOYEE_ID(+)
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE = 'E'
AND PPL.BUSINESS_GROUP_ID = ORG.ORGANIZATION_ID

Columns