VIEW: APPS.JTF_RS_RES_EMP_VL

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'