eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRIFV_PERIOD_OF_WORK

Source

View Text - Preformatted


SELECT bgrT.name business_group_name /* employee assignment information */ ,peo.full_name person_name ,peo.employee_number employee_number ,asg.assignment_number assignment_number ,peo.applicant_number applicant_number ,peo.first_name first_name ,peo.last_name last_name ,peo.previous_last_name previous_last_name ,peo.middle_names middle_names ,peo.known_as preferred_name ,peo.title title ,peo.pre_name_adjunct prefix ,peo.suffix suffix ,DECODE(asg.employment_category, NULL, NULL, hr_bis.bis_decode_lookup('EMP_CAT',asg.employment_category)) employment_category ,peo.rehire_reason rehire_reason ,DECODE(peo.rehire_recommendation, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.rehire_recommendation)) ,peo.rehire_authorizor rehire_authorizor ,DECODE(peo.sex, NULL, NULL, hr_bis.bis_decode_lookup('SEX',peo.sex)) gender ,peo.email_address email_address ,peo.original_date_of_hire original_date_of_hire ,peo.effective_start_date person_start_date ,DECODE(peo.effective_end_date, hr_general.end_of_time, NULL,peo.effective_end_date ) person_end_date ,asg.effective_start_date assignment_start_date ,DECODE(asg.effective_end_date, hr_general.end_of_time, NULL,asg.effective_end_date ) assignment_end_date ,peo.date_of_birth date_of_birth ,DECODE(peo.date_of_birth, NULL, NULL, ROUND((NVL(ppos.actual_termination_date, ppos.date_start) - peo.date_of_birth ),0)) age_in_days ,DECODE(peo.date_of_birth, NULL, NULL, FLOOR(MONTHS_BETWEEN( NVL(ppos.actual_termination_date, ppos.date_start) ,peo.date_of_birth )/12)) age_in_years ,DECODE(peo.date_of_birth, NULL, NULL, ROUND((TRUNC(SYSDATE) - peo.date_of_birth ),0)) current_age_in_days ,DECODE(peo.date_of_birth, NULL, NULL, FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),peo.date_of_birth )/12)) current_age_in_years /* period of service information */ ,ppos.date_start hire_date ,ROUND( NVL(ppos.actual_termination_date,TRUNC(SYSDATE)) - ppos.date_start ,0) period_of_work_days ,ROUND( MONTHS_BETWEEN(NVL(ppos.actual_termination_date,TRUNC(SYSDATE)), ppos.date_start)/12 ,2) period_of_work_years ,ppos.actual_termination_date actual_separation_date ,hr_bis.bis_decode_lookup('LEAV_REAS',ppos.leaving_reason) separation_reason ,ppos.comments separation_comments ,ppos.accepted_termination_date accepted_separation_date ,ppos.final_process_date final_process_date ,ppos.last_standard_process_date last_standard_process_date ,ppos.notified_termination_date notified_separation_date ,ppos.projected_termination_date projected_separation_date /* system codes */ ,ppos.leaving_reason separation_reason_code ,asg.employment_category employment_category_code ,peo.sex gender_code ,peo.rehire_recommendation rehire_recommendation_code /* not null dates used for date joins */ ,peo.effective_start_date person_start_date_nn ,peo.effective_end_date person_end_date_nn ,asg.effective_start_date assignment_start_date_nn ,asg.effective_end_date assignment_end_date_nn ,ppos.date_start period_of_service_start_date ,NVL(ppos.actual_termination_date, hr_general.end_of_time) period_of_service_end_date_nn /* flexfields */ ,'_DF:PER:PER_PEOPLE:peo' ,'_DF:PER:PER_ASSIGNMENTS:asg' ,'_DF:PER:PER_PDS_DEVELOPER_DF:ppos' ,'_DF:PER:PER_PERIODS_OF_SERVICE:ppos' /* ids */ ,asg.business_group_id business_group_id ,asg.assignment_id assignment_id ,asg.person_id person_id ,asg.grade_id grade_id ,asg.job_id job_id ,asg.organization_id organization_id ,asg.position_id position_id ,asg.location_id location_id ,asg.period_of_service_id period_of_service_id ,asg.recruiter_id recruiter_id ,asg.payroll_id payroll_id ,asg.person_referred_by_id person_referred_by_id ,asg.supervisor_id supervisor_id ,asg.recruitment_activity_id recruitment_activity_id ,asg.source_organization_id source_organization_id ,asg.people_group_id people_group_id ,asg.vacancy_id vacancy_id ,asg.pay_basis_id pay_basis_id ,asg.application_id application_id ,asg.set_of_books_id set_of_books_id ,asg.contract_id contract_id ,asg.collective_agreement_id collective_agreement_id ,peo.person_type_id person_type_id ,peo.benefit_group_id benefit_group_id ,peo.comment_id person_comment_id ,ppos.termination_accepted_person_id termination_accepted_person_id FROM hr_all_organization_units_tl bgrT ,per_assignments_f asg /* HR secured by assignment */ ,per_all_people_f peo ,per_periods_of_service ppos WHERE asg.person_id = peo.person_id AND asg.effective_start_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg.period_of_service_id = ppos.period_of_service_id AND NVL(ppos.actual_termination_date, ppos.date_start) BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND asg.assignment_type = 'E' AND asg.primary_flag = 'Y' AND asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,asg.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME /* EMPLOYEE ASSIGNMENT INFORMATION */
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.TITLE TITLE
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, DECODE(ASG.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASG.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY
, PEO.REHIRE_REASON REHIRE_REASON
, DECODE(PEO.REHIRE_RECOMMENDATION
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REHIRE_RECOMMENDATION))
, PEO.REHIRE_AUTHORIZOR REHIRE_AUTHORIZOR
, DECODE(PEO.SEX
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX)) GENDER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, DECODE(PEO.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME
, NULL
, PEO.EFFECTIVE_END_DATE ) PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, DECODE(ASG.EFFECTIVE_END_DATE
, HR_GENERAL.END_OF_TIME
, NULL
, ASG.EFFECTIVE_END_DATE ) ASSIGNMENT_END_DATE
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, DECODE(PEO.DATE_OF_BIRTH
, NULL
, NULL
, ROUND((NVL(PPOS.ACTUAL_TERMINATION_DATE
, PPOS.DATE_START) - PEO.DATE_OF_BIRTH )
, 0)) AGE_IN_DAYS
, DECODE(PEO.DATE_OF_BIRTH
, NULL
, NULL
, FLOOR(MONTHS_BETWEEN( NVL(PPOS.ACTUAL_TERMINATION_DATE
, PPOS.DATE_START)
, PEO.DATE_OF_BIRTH )/12)) AGE_IN_YEARS
, DECODE(PEO.DATE_OF_BIRTH
, NULL
, NULL
, ROUND((TRUNC(SYSDATE) - PEO.DATE_OF_BIRTH )
, 0)) CURRENT_AGE_IN_DAYS
, DECODE(PEO.DATE_OF_BIRTH
, NULL
, NULL
, FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE)
, PEO.DATE_OF_BIRTH )/12)) CURRENT_AGE_IN_YEARS /* PERIOD OF SERVICE INFORMATION */
, PPOS.DATE_START HIRE_DATE
, ROUND( NVL(PPOS.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE)) - PPOS.DATE_START
, 0) PERIOD_OF_WORK_DAYS
, ROUND( MONTHS_BETWEEN(NVL(PPOS.ACTUAL_TERMINATION_DATE
, TRUNC(SYSDATE))
, PPOS.DATE_START)/12
, 2) PERIOD_OF_WORK_YEARS
, PPOS.ACTUAL_TERMINATION_DATE ACTUAL_SEPARATION_DATE
, HR_BIS.BIS_DECODE_LOOKUP('LEAV_REAS'
, PPOS.LEAVING_REASON) SEPARATION_REASON
, PPOS.COMMENTS SEPARATION_COMMENTS
, PPOS.ACCEPTED_TERMINATION_DATE ACCEPTED_SEPARATION_DATE
, PPOS.FINAL_PROCESS_DATE FINAL_PROCESS_DATE
, PPOS.LAST_STANDARD_PROCESS_DATE LAST_STANDARD_PROCESS_DATE
, PPOS.NOTIFIED_TERMINATION_DATE NOTIFIED_SEPARATION_DATE
, PPOS.PROJECTED_TERMINATION_DATE PROJECTED_SEPARATION_DATE /* SYSTEM CODES */
, PPOS.LEAVING_REASON SEPARATION_REASON_CODE
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, PEO.SEX GENDER_CODE
, PEO.REHIRE_RECOMMENDATION REHIRE_RECOMMENDATION_CODE /* NOT NULL DATES USED FOR DATE JOINS */
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE_NN
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE_NN
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE_NN
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE_NN
, PPOS.DATE_START PERIOD_OF_SERVICE_START_DATE
, NVL(PPOS.ACTUAL_TERMINATION_DATE
, HR_GENERAL.END_OF_TIME) PERIOD_OF_SERVICE_END_DATE_NN /* FLEXFIELDS */
, '_DF:PER:PER_PEOPLE:PEO'
, '_DF:PER:PER_ASSIGNMENTS:ASG'
, '_DF:PER:PER_PDS_DEVELOPER_DF:PPOS'
, '_DF:PER:PER_PERIODS_OF_SERVICE:PPOS' /* IDS */
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.PERSON_ID PERSON_ID
, ASG.GRADE_ID GRADE_ID
, ASG.JOB_ID JOB_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.POSITION_ID POSITION_ID
, ASG.LOCATION_ID LOCATION_ID
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASG.RECRUITER_ID RECRUITER_ID
, ASG.PAYROLL_ID PAYROLL_ID
, ASG.PERSON_REFERRED_BY_ID PERSON_REFERRED_BY_ID
, ASG.SUPERVISOR_ID SUPERVISOR_ID
, ASG.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, ASG.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, ASG.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASG.VACANCY_ID VACANCY_ID
, ASG.PAY_BASIS_ID PAY_BASIS_ID
, ASG.APPLICATION_ID APPLICATION_ID
, ASG.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, ASG.CONTRACT_ID CONTRACT_ID
, ASG.COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID
, PEO.PERSON_TYPE_ID PERSON_TYPE_ID
, PEO.BENEFIT_GROUP_ID BENEFIT_GROUP_ID
, PEO.COMMENT_ID PERSON_COMMENT_ID
, PPOS.TERMINATION_ACCEPTED_PERSON_ID TERMINATION_ACCEPTED_PERSON_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ASSIGNMENTS_F ASG /* HR SECURED BY ASSIGNMENT */
, PER_ALL_PEOPLE_F PEO
, PER_PERIODS_OF_SERVICE PPOS
WHERE ASG.PERSON_ID = PEO.PERSON_ID
AND ASG.EFFECTIVE_START_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND NVL(PPOS.ACTUAL_TERMINATION_DATE
, PPOS.DATE_START) BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.ASSIGNMENT_TYPE = 'E'
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY