VIEW: APPS.JTF_RS_EMPLOYEES_VL

Source

View Text - Preformatted

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

View Text - HTML Formatted

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