VIEW: APPS.HRFV_TIME_IN_SERVICE_ANALYSIS

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,ppf.full_name person_name ,ppf.employee_number employee_number ,hr_discoverer.time_in(paf.assignment_id, 'SERVICE') months_in_service ,hr_discoverer.time_in(paf.assignment_id, 'ORGANIZATION') months_in_organization ,hr_discoverer.time_in(paf.assignment_id, 'POSITION') months_in_position ,hr_discoverer.time_in(paf.assignment_id, 'JOB') months_in_job ,hr_discoverer.time_in(paf.assignment_id, 'GRADE') months_in_grade ,hr_discoverer.time_in(paf.assignment_id, 'LOCATION') months_in_location ,paf.assignment_number assignment_number ,houT.name organization_name ,jbt.name job_name ,pft.name position_name ,gdt.name grade_name ,hlT.location_code location_name ,ppos.date_start hire_date ,ppos.actual_termination_date termination_date ,paf.assignment_id assignment_id ,paf.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,hlT.location_id location_id ,paf.organization_id organization_id ,paf.person_id person_id ,pft.position_id position_id FROM hr_all_organization_units_tl houT ,hr_all_organization_units_tl bgrT ,per_grades_tl gdt ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,hr_locations_all_tl hlT ,per_periods_of_service ppos ,per_people_x ppf ,per_all_assignments_f paf WHERE paf.period_of_service_id = ppos.period_of_service_id AND paf.person_id = ppf.person_id AND paf.organization_id = houT.organization_id AND houT.language = userenv('LANG') AND paf.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND paf.job_id = jbt.job_id(+) AND jbt.language (+) = userenv('LANG') AND paf.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND paf.location_id = hlT.location_id(+) AND hlT.language (+) = userenv('LANG') AND paf.assignment_type = 'E' AND ((TRUNC(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date AND (ppos.actual_termination_date > paf.effective_start_date OR ppos.actual_termination_date IS NULL)) OR (paf.effective_end_date = ppos.actual_termination_date AND ppos.actual_termination_date < TRUNC(sysdate))) AND paf.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND paf.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, paf.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PPF.FULL_NAME PERSON_NAME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HR_DISCOVERER.TIME_IN(PAF.ASSIGNMENT_ID
, 'SERVICE') MONTHS_IN_SERVICE
, HR_DISCOVERER.TIME_IN(PAF.ASSIGNMENT_ID
, 'ORGANIZATION') MONTHS_IN_ORGANIZATION
, HR_DISCOVERER.TIME_IN(PAF.ASSIGNMENT_ID
, 'POSITION') MONTHS_IN_POSITION
, HR_DISCOVERER.TIME_IN(PAF.ASSIGNMENT_ID
, 'JOB') MONTHS_IN_JOB
, HR_DISCOVERER.TIME_IN(PAF.ASSIGNMENT_ID
, 'GRADE') MONTHS_IN_GRADE
, HR_DISCOVERER.TIME_IN(PAF.ASSIGNMENT_ID
, 'LOCATION') MONTHS_IN_LOCATION
, PAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, HOUT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, HLT.LOCATION_CODE LOCATION_NAME
, PPOS.DATE_START HIRE_DATE
, PPOS.ACTUAL_TERMINATION_DATE TERMINATION_DATE
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, HLT.LOCATION_ID LOCATION_ID
, PAF.ORGANIZATION_ID ORGANIZATION_ID
, PAF.PERSON_ID PERSON_ID
, PFT.POSITION_ID POSITION_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL HOUT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, HR_LOCATIONS_ALL_TL HLT
, PER_PERIODS_OF_SERVICE PPOS
, PER_PEOPLE_X PPF
, PER_ALL_ASSIGNMENTS_F PAF
WHERE PAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PAF.ORGANIZATION_ID = HOUT.ORGANIZATION_ID
AND HOUT.LANGUAGE = USERENV('LANG')
AND PAF.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND PAF.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND PAF.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND PAF.LOCATION_ID = HLT.LOCATION_ID(+)
AND HLT.LANGUAGE (+) = USERENV('LANG')
AND PAF.ASSIGNMENT_TYPE = 'E'
AND ((TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND (PPOS.ACTUAL_TERMINATION_DATE > PAF.EFFECTIVE_START_DATE OR PPOS.ACTUAL_TERMINATION_DATE IS NULL)) OR (PAF.EFFECTIVE_END_DATE = PPOS.ACTUAL_TERMINATION_DATE
AND PPOS.ACTUAL_TERMINATION_DATE < TRUNC(SYSDATE)))
AND PAF.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND PAF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PAF.BUSINESS_GROUP_ID) WITH READ ONLY