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.HRFV_EMPLOYEE_ASSIGNMENTS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,peo.full_name person_name ,peo.pre_name_adjunct prefix ,peo.suffix suffix ,peo.first_name first_name ,peo.last_name last_name ,peo.known_as preferred_name ,peo.middle_names middle_names ,peo.title title ,pca.name collective_agreement_name ,ass.assignment_number assignment_number ,ass.time_normal_start normal_start_time ,ass.time_normal_finish normal_end_time ,hr_bis.bis_decode_lookup('YES_NO',ass.primary_flag) primary_flag ,ass.internal_address_line internal_address ,hr_bis.bis_decode_lookup('YES_NO',ass.manager_flag) manager_flag ,hr_bis.bis_decode_lookup('EMP_CAT',ass.employment_category) employment_category ,ass.assignment_type assignment_type_code ,hr_bis.bis_decode_lookup('EMP_APL',ass.assignment_type) assignment_type ,ass.normal_hours working_hours_amount ,hr_bis.bis_decode_lookup('FREQUENCY',ass.frequency) working_hours_frequency ,peo.employee_number employee_number ,pps.date_start hire_date ,peo.original_date_of_hire original_date_of_hire ,phn.phone_number work_telephone_number ,sup.full_name supervisor_name ,sup.employee_number supervisor_number ,ppb.name salary_basis ,orgT.name organization_name ,pft.name position_name ,jbt.name job_name ,locT.location_code location_name ,gdt.name grade_name ,pgr.group_name people_group ,pay.payroll_name payroll_name ,rac.name recruitment_activity_name ,vac.name vacancy_name ,NVL(astaT.user_status,astT.user_status) user_assignment_status ,hr_bis.bis_decode_lookup('PER_ASS_SYS_STATUS',ast.per_system_status) per_system_assignment_status ,hr_bis.bis_decode_lookup('PAY_ASS_SYS_STATUS',NVL(asta.pay_system_status,ast.pay_system_status)) pay_system_assignment_status ,ass.primary_flag primary_flag_code ,ass.manager_flag manager_flag_code ,ass.employment_category employment_category_code ,ass.frequency frequency_code ,ast.per_system_status per_system_status_code ,NVL(asta.pay_system_status,ast.pay_system_status) pay_system_status_code ,'_KF:PAY:GRP:pgr' ,'_DF:PER:PER_ASSIGNMENTS:ass' ,ass.assignment_id assignment_id ,ass.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,locT.location_id location_id ,ass.organization_id organization_id ,pay.payroll_id payroll_id ,ass.period_of_service_id period_of_service_id ,pgr.people_group_id people_group_id ,ass.person_id person_id ,pft.position_id position_id ,rac.recruitment_activity_id recruitment_activity_id ,ppb.pay_basis_id salary_basis_id ,ass.supervisor_id supervisor_id ,vac.vacancy_id vacancy_id ,pca.collective_agreement_id collective_agreement_id FROM per_all_people_f peo ,per_all_people_f sup ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,per_recruitment_activities rac ,per_all_vacancies vac ,per_pay_bases ppb ,hr_all_positions_f_tl pft ,pay_all_payrolls_f pay ,per_jobs_tl jbt ,hr_locations_all_tl locT ,per_grades_tl gdt ,pay_people_groups pgr ,per_assignments_f ass ,per_assignment_status_types ast ,per_assignment_status_types_tl astT ,per_ass_status_type_amends asta ,per_ass_status_type_amends_tl astaT ,per_phones phn ,per_periods_of_service pps ,per_collective_agreements pca WHERE ass.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND ass.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND ass.payroll_id = pay.payroll_id(+) AND ass.job_id = jbt.job_id(+) AND jbt.language (+) = userenv('LANG') AND ass.location_id = locT.location_id(+) AND locT.language (+) = userenv('LANG') AND ass.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND ass.supervisor_id = sup.person_id(+) AND ass.people_group_id = pgr.people_group_id(+) AND ass.pay_basis_id = ppb.pay_basis_id (+) AND ass.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND ass.person_id = peo.person_id AND peo.current_employee_flag = 'Y' AND ass.vacancy_id = vac.vacancy_id (+) AND ass.recruitment_activity_id = rac.recruitment_activity_id (+) AND ass.assignment_type = 'E' AND ast.assignment_status_type_id = ass.assignment_status_type_id AND ast.assignment_status_type_id = astT.assignment_status_type_id AND astT.language = userenv('LANG') AND ass.assignment_status_type_id = asta.assignment_status_type_id (+) AND ass.business_group_id = asta.business_group_id (+) AND asta.ass_status_type_amend_id = astaT.ass_status_type_amend_id (+) AND NVL(astaT.language,userenv('LANG')) = userenv('LANG') AND ass.person_id = phn.parent_id (+) AND phn.parent_table (+) = 'PER_ALL_PEOPLE_F' AND phn.phone_type (+) = 'W1' AND peo.person_id = pps.person_id (+) AND ass.collective_agreement_id = pca.collective_agreement_id (+) AND ((peo.employee_number is null) or (peo.employee_number is not null and pps.date_start = (select max(pps1.date_start) from per_periods_of_service pps1 where pps1.person_id = peo.person_id and pps1.date_start <= peo.effective_end_date)) ) AND trunc(sysdate) BETWEEN phn.date_from (+) and NVL(phn.date_to (+), hr_general.end_of_time) AND trunc(sysdate) BETWEEN sup.effective_start_date (+) AND sup.effective_end_date (+) AND trunc(sysdate) BETWEEN pay.effective_start_date (+) AND pay.effective_end_date (+) AND trunc(sysdate) BETWEEN peo.effective_start_date AND peo.effective_end_date AND trunc(sysdate) BETWEEN ass.effective_start_date AND ass.effective_end_date AND ass.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, ass.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.TITLE TITLE
, PCA.NAME COLLECTIVE_AGREEMENT_NAME
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASS.TIME_NORMAL_START NORMAL_START_TIME
, ASS.TIME_NORMAL_FINISH NORMAL_END_TIME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASS.PRIMARY_FLAG) PRIMARY_FLAG
, ASS.INTERNAL_ADDRESS_LINE INTERNAL_ADDRESS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASS.MANAGER_FLAG) MANAGER_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASS.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, ASS.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, HR_BIS.BIS_DECODE_LOOKUP('EMP_APL'
, ASS.ASSIGNMENT_TYPE) ASSIGNMENT_TYPE
, ASS.NORMAL_HOURS WORKING_HOURS_AMOUNT
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASS.FREQUENCY) WORKING_HOURS_FREQUENCY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PPS.DATE_START HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PHN.PHONE_NUMBER WORK_TELEPHONE_NUMBER
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, PPB.NAME SALARY_BASIS
, ORGT.NAME ORGANIZATION_NAME
, PFT.NAME POSITION_NAME
, JBT.NAME JOB_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, GDT.NAME GRADE_NAME
, PGR.GROUP_NAME PEOPLE_GROUP
, PAY.PAYROLL_NAME PAYROLL_NAME
, RAC.NAME RECRUITMENT_ACTIVITY_NAME
, VAC.NAME VACANCY_NAME
, NVL(ASTAT.USER_STATUS
, ASTT.USER_STATUS) USER_ASSIGNMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('PER_ASS_SYS_STATUS'
, AST.PER_SYSTEM_STATUS) PER_SYSTEM_ASSIGNMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('PAY_ASS_SYS_STATUS'
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)) PAY_SYSTEM_ASSIGNMENT_STATUS
, ASS.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASS.MANAGER_FLAG MANAGER_FLAG_CODE
, ASS.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASS.FREQUENCY FREQUENCY_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_STATUS_CODE
, '_KF:PAY:GRP:PGR'
, '_DF:PER:PER_ASSIGNMENTS:ASS'
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, ASS.ORGANIZATION_ID ORGANIZATION_ID
, PAY.PAYROLL_ID PAYROLL_ID
, ASS.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASS.PERSON_ID PERSON_ID
, PFT.POSITION_ID POSITION_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, PPB.PAY_BASIS_ID SALARY_BASIS_ID
, ASS.SUPERVISOR_ID SUPERVISOR_ID
, VAC.VACANCY_ID VACANCY_ID
, PCA.COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID
FROM PER_ALL_PEOPLE_F PEO
, PER_ALL_PEOPLE_F SUP
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_RECRUITMENT_ACTIVITIES RAC
, PER_ALL_VACANCIES VAC
, PER_PAY_BASES PPB
, HR_ALL_POSITIONS_F_TL PFT
, PAY_ALL_PAYROLLS_F PAY
, PER_JOBS_TL JBT
, HR_LOCATIONS_ALL_TL LOCT
, PER_GRADES_TL GDT
, PAY_PEOPLE_GROUPS PGR
, PER_ASSIGNMENTS_F ASS
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTT
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTAT
, PER_PHONES PHN
, PER_PERIODS_OF_SERVICE PPS
, PER_COLLECTIVE_AGREEMENTS PCA
WHERE ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASS.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND ASS.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASS.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND ASS.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND ASS.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND ASS.SUPERVISOR_ID = SUP.PERSON_ID(+)
AND ASS.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND ASS.VACANCY_ID = VAC.VACANCY_ID (+)
AND ASS.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID (+)
AND ASS.ASSIGNMENT_TYPE = 'E'
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASS.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTT.ASSIGNMENT_STATUS_TYPE_ID
AND ASTT.LANGUAGE = USERENV('LANG')
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASS.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID (+)
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTAT.ASS_STATUS_TYPE_AMEND_ID (+)
AND NVL(ASTAT.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND ASS.PERSON_ID = PHN.PARENT_ID (+)
AND PHN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND PHN.PHONE_TYPE (+) = 'W1'
AND PEO.PERSON_ID = PPS.PERSON_ID (+)
AND ASS.COLLECTIVE_AGREEMENT_ID = PCA.COLLECTIVE_AGREEMENT_ID (+)
AND ((PEO.EMPLOYEE_NUMBER IS NULL) OR (PEO.EMPLOYEE_NUMBER IS NOT NULL
AND PPS.DATE_START = (SELECT MAX(PPS1.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS1
WHERE PPS1.PERSON_ID = PEO.PERSON_ID
AND PPS1.DATE_START <= PEO.EFFECTIVE_END_DATE)) )
AND TRUNC(SYSDATE) BETWEEN PHN.DATE_FROM (+)
AND NVL(PHN.DATE_TO (+)
, HR_GENERAL.END_OF_TIME)
AND TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE (+)
AND SUP.EFFECTIVE_END_DATE (+)
AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE (+)
AND PAY.EFFECTIVE_END_DATE (+)
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY