VIEW: APPS.HRFV_PERSON_ASSIGNMENT_HISTORY

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,GREATEST(peo.effective_start_date, asg.effective_start_date) record_start_date ,LEAST(peo.effective_end_date, asg.effective_end_date) record_end_date ,peo.effective_start_date person_start_date ,peo.effective_end_date person_end_date ,asg.effective_start_date assignment_start_date ,asg.effective_end_date assignment_end_date ,peo.full_name person_name ,peo.employee_number employee_number ,peo.middle_names middle_names ,peo.known_as preferred_name ,peo.first_name first_name ,peo.last_name last_name ,peo.title title ,peo.pre_name_adjunct prefix ,peo.suffix suffix ,hr_bis.bis_decode_lookup('SEX',peo.sex) gender ,peo.previous_last_name previous_last_name ,pca.name collective_agreement_name ,hr_bis.bis_decode_lookup('REGISTERED_DISABLED',peo.registered_disabled_flag) registered_disabled ,peo.date_of_birth date_of_birth ,peo.town_of_birth town_of_birth ,peo.region_of_birth region_of_birth ,ftrT.TERRITORY_SHORT_NAME country_of_birth ,DECODE(peo.marital_status, NULL, NULL, hr_bis.bis_decode_lookup('MAR_STATUS',peo.marital_status)) marital_status ,DECODE(peo.nationality, NULL, NULL, hr_bis.bis_decode_lookup('NATIONALITY',peo.nationality)) nationality ,peo.national_identifier national_identifier ,peo.applicant_number applicant_number ,peo.npw_number contingent_number ,peo.email_address email_address ,DECODE(peo.expense_check_send_to_address, NULL, NULL, hr_bis.bis_decode_lookup('HOME_OFFICE', peo.expense_check_send_to_address)) mail_destination ,DECODE(peo.second_passport_exists, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.second_passport_exists)) second_passport ,peo.office_number office_number ,peo.internal_location internal_location ,peo.mailstop mailstop ,peo.work_schedule work_schedule ,peo.fte_capacity fte_capacity ,DECODE(peo.resume_exists, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.resume_exists)) resume_exists ,peo.resume_last_updated resume_last_updated ,hr_bis.bis_decode_lookup('YES_NO',NVL(peo.current_applicant_flag,'N')) current_applicant_flag ,hr_bis.bis_decode_lookup('YES_NO',NVL(peo.current_employee_flag,'N')) current_employee_flag ,hr_bis.bis_decode_lookup('YES_NO',NVL(peo.current_npw_flag,'N')) current_contingent_flag ,peo.date_employee_data_verified date_employee_data_verified ,peo.date_of_death date_of_death ,DECODE(peo.fast_path_employee, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.fast_path_employee)) fast_path_employee ,peo.hold_applicant_date_until hold_application_until ,peo.honors honors ,DECODE(peo.on_military_service, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',peo.on_military_service)) on_military_service ,peo.projected_start_date projected_start_date ,DECODE(peo.student_status, NULL, NULL, hr_bis.bis_decode_lookup('STUDENT_STATUS',peo.student_status)) student_status ,pptT.user_person_type person_type ,HRI_BPL_PERSON_TYPE.get_emp_user_person_type (GREATEST(peo.effective_start_date,asg.effective_start_date) ,peo.person_id) employee_person_type ,HRI_BPL_PERSON_TYPE.get_apl_user_person_type (GREATEST(peo.effective_start_date,asg.effective_start_date) ,peo.person_id) applicant_person_type ,HRI_BPL_PERSON_TYPE.get_cwk_user_person_type (GREATEST(peo.effective_start_date,asg.effective_start_date) ,peo.person_id) contingent_person_type ,HRI_BPL_PERSON_TYPE.get_concat_user_person_type (GREATEST(peo.effective_start_date,asg.effective_start_date) ,peo.person_id) user_concat_person_type ,CASE WHEN peo.current_employee_flag = 'Y' THEN (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) ELSE CASE WHEN peo.current_npw_flag = 'Y' THEN (SELECT MAX(ppp1.date_start) FROM per_periods_of_placement ppp1 WHERE ppp1.person_id = peo.person_id AND ppp1.date_start <= peo.effective_end_date) END END hire_date ,peo.original_date_of_hire original_date_of_hire ,phn.phone_number work_telelphone_number ,'_DF:PER:PER_PEOPLE:peo' ,asg.assignment_number assignment_number ,asg.time_normal_start normal_start_time ,asg.time_normal_finish normal_end_time ,hr_bis.bis_decode_lookup('YES_NO',asg.primary_flag) primary_flag ,asg.internal_address_line internal_address ,hr_bis.bis_decode_lookup('EMP_APL',asg.assignment_type) assignment_type ,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 ,DECODE(asg.manager_flag, NULL, NULL, hr_bis.bis_decode_lookup('YES_NO',asg.manager_flag)) manager_flag ,DECODE(asg.employment_category, NULL, NULL, hr_bis.bis_decode_lookup('EMP_CAT',asg.employment_category)) employment_category ,asg.normal_hours working_hours_amount ,hr_bis.bis_decode_lookup('FREQUENCY',asg.frequency) working_hours_frequency ,asg.change_reason change_reason ,DECODE(asg.source_type, NULL, NULL, hr_bis.bis_decode_lookup('REC_TYPE',asg.source_type)) source_type ,sup.full_name supervisor_name ,sup.employee_number supervisor_number ,ppb.pay_basis salary_basis ,orgT.name assignment_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 ,'_KF:PAY:GRP:pgr' ,'_DF:PER:PER_ASSIGNMENTS:asg' ,peo.sex gender_code ,peo.registered_disabled_flag registered_disabled_flag_code ,peo.marital_status marital_status_code ,peo.nationality nationality_code ,peo.expense_check_send_to_address expense_check_addr_code ,peo.second_passport_exists second_passport_exists_code ,peo.resume_exists resume_exists_code ,peo.current_applicant_flag current_applicant_flag_code ,peo.current_employee_flag current_employee_flag_code ,peo.current_npw_flag current_contingent_flag_code ,peo.student_status student_status_code ,asg.primary_flag primary_flag_code ,asg.assignment_type assignment_type_code ,ast.per_system_status per_system_status_code ,NVL(asta.pay_system_status,ast.pay_system_status) pay_system_status_code ,asg.manager_flag manager_flag_code ,asg.employment_category employment_category_code ,peo.fast_path_employee fast_path_employee_code ,peo.on_military_service on_military_service_code ,asg.frequency frequency_code ,asg.source_type source_type_code ,ppt.system_person_type system_person_type_code ,asg.application_id application_id ,asg.assignment_id assignment_id ,orgT.organization_id assignment_organization_id ,asg.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,locT.location_id location_id ,pay.payroll_id payroll_id ,asg.period_of_service_id period_of_service_id ,pgr.people_group_id people_group_id ,asg.person_id person_id ,peo.person_type_id person_type_id ,pft.position_id position_id ,rac.recruitment_activity_id recruitment_activity_id ,ppb.pay_basis_id salary_basis_id ,asg.supervisor_id supervisor_id ,vac.vacancy_id vacancy_id ,pca. collective_agreement_id collective_agreement_id ,peo.last_update_date peo_last_update_date ,asg.last_update_date asg_last_update_date ,peo.last_updated_by peo_last_updated_by ,asg.last_updated_by asg_last_updated_by 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 asg ,per_person_types ppt ,per_person_types_tl pptT ,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 ,fnd_territories_tl ftrT ,per_collective_agreements pca WHERE asg.organization_id = orgT.organization_id AND asg.pay_basis_id = ppb.pay_basis_id (+) AND asg.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND asg.payroll_id = pay.payroll_id(+) AND asg.job_id = jbt.job_id(+) AND jbt.language (+) = userenv('LANG') AND asg.location_id = locT.location_id(+) AND asg.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND asg.supervisor_id = sup.person_id(+) AND asg.people_group_id = pgr.people_group_id(+) AND asg.business_group_id = bgrT.organization_id AND asg.person_id = peo.person_id AND asg.vacancy_id = vac.vacancy_id (+) AND asg.recruitment_activity_id = rac.recruitment_activity_id (+) AND peo.person_type_id = ppt.person_type_id AND ppt.person_type_id = pptT.person_type_id AND ast.assignment_status_type_id = asg.assignment_status_type_id AND ast.assignment_status_type_id = astT.assignment_status_type_id AND astT.language = userenv('LANG') AND asg.assignment_status_type_id = asta.assignment_status_type_id (+) AND asg.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 asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, asg.business_group_id) AND locT.language (+) = userenv('LANG') AND orgT.language = userenv('LANG') AND bgrT.language = userenv('LANG') AND pptT.language = userenv('LANG') AND peo.country_of_birth = ftrT.TERRITORY_CODE(+) AND ftrT.language(+) = userenv('LANG') AND ppt.active_flag = 'Y' AND asg.person_id = phn.parent_id (+) AND phn.parent_table (+) = 'PER_ALL_PEOPLE_F' AND phn.phone_type (+) = 'W1' AND asg.effective_start_date BETWEEN phn.date_from (+) AND NVL(phn.date_to (+), hr_general.end_of_time) AND asg.collective_agreement_id = pca.collective_agreement_id (+) AND asg.effective_start_date BETWEEN sup.effective_start_date (+) AND sup.effective_end_date (+) AND asg.effective_start_date BETWEEN pay.effective_start_date (+) AND pay.effective_end_date (+) AND ( (asg.effective_start_date <= peo.effective_start_date AND asg.effective_end_date >= peo.effective_start_date) OR (asg.effective_start_date <= peo.effective_end_date AND asg.effective_end_date >= peo.effective_end_date) OR (asg.effective_start_date >= peo.effective_start_date AND asg.effective_end_date <= peo.effective_end_date) ) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, GREATEST(PEO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE) RECORD_START_DATE
, LEAST(PEO.EFFECTIVE_END_DATE
, ASG.EFFECTIVE_END_DATE) RECORD_END_DATE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.TITLE TITLE
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, PCA.NAME COLLECTIVE_AGREEMENT_NAME
, HR_BIS.BIS_DECODE_LOOKUP('REGISTERED_DISABLED'
, PEO.REGISTERED_DISABLED_FLAG) REGISTERED_DISABLED
, PEO.DATE_OF_BIRTH DATE_OF_BIRTH
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, FTRT.TERRITORY_SHORT_NAME COUNTRY_OF_BIRTH
, DECODE(PEO.MARITAL_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('MAR_STATUS'
, PEO.MARITAL_STATUS)) MARITAL_STATUS
, DECODE(PEO.NATIONALITY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('NATIONALITY'
, PEO.NATIONALITY)) NATIONALITY
, PEO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PEO.APPLICANT_NUMBER APPLICANT_NUMBER
, PEO.NPW_NUMBER CONTINGENT_NUMBER
, PEO.EMAIL_ADDRESS EMAIL_ADDRESS
, DECODE(PEO.EXPENSE_CHECK_SEND_TO_ADDRESS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('HOME_OFFICE'
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS)) MAIL_DESTINATION
, DECODE(PEO.SECOND_PASSPORT_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.SECOND_PASSPORT_EXISTS)) SECOND_PASSPORT
, PEO.OFFICE_NUMBER OFFICE_NUMBER
, PEO.INTERNAL_LOCATION INTERNAL_LOCATION
, PEO.MAILSTOP MAILSTOP
, PEO.WORK_SCHEDULE WORK_SCHEDULE
, PEO.FTE_CAPACITY FTE_CAPACITY
, DECODE(PEO.RESUME_EXISTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.RESUME_EXISTS)) RESUME_EXISTS
, PEO.RESUME_LAST_UPDATED RESUME_LAST_UPDATED
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_APPLICANT_FLAG
, 'N')) CURRENT_APPLICANT_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_EMPLOYEE_FLAG
, 'N')) CURRENT_EMPLOYEE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, NVL(PEO.CURRENT_NPW_FLAG
, 'N')) CURRENT_CONTINGENT_FLAG
, PEO.DATE_EMPLOYEE_DATA_VERIFIED DATE_EMPLOYEE_DATA_VERIFIED
, PEO.DATE_OF_DEATH DATE_OF_DEATH
, DECODE(PEO.FAST_PATH_EMPLOYEE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.FAST_PATH_EMPLOYEE)) FAST_PATH_EMPLOYEE
, PEO.HOLD_APPLICANT_DATE_UNTIL HOLD_APPLICATION_UNTIL
, PEO.HONORS HONORS
, DECODE(PEO.ON_MILITARY_SERVICE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.ON_MILITARY_SERVICE)) ON_MILITARY_SERVICE
, PEO.PROJECTED_START_DATE PROJECTED_START_DATE
, DECODE(PEO.STUDENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('STUDENT_STATUS'
, PEO.STUDENT_STATUS)) STUDENT_STATUS
, PPTT.USER_PERSON_TYPE PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_EMP_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) EMPLOYEE_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_APL_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) APPLICANT_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_CWK_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) CONTINGENT_PERSON_TYPE
, HRI_BPL_PERSON_TYPE.GET_CONCAT_USER_PERSON_TYPE (GREATEST(PEO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE)
, PEO.PERSON_ID) USER_CONCAT_PERSON_TYPE
, CASE WHEN PEO.CURRENT_EMPLOYEE_FLAG = 'Y' THEN (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) ELSE CASE WHEN PEO.CURRENT_NPW_FLAG = 'Y' THEN (SELECT MAX(PPP1.DATE_START)
FROM PER_PERIODS_OF_PLACEMENT PPP1
WHERE PPP1.PERSON_ID = PEO.PERSON_ID
AND PPP1.DATE_START <= PEO.EFFECTIVE_END_DATE) END END HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PHN.PHONE_NUMBER WORK_TELELPHONE_NUMBER
, '_DF:PER:PER_PEOPLE:PEO'
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG.TIME_NORMAL_START NORMAL_START_TIME
, ASG.TIME_NORMAL_FINISH NORMAL_END_TIME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.PRIMARY_FLAG) PRIMARY_FLAG
, ASG.INTERNAL_ADDRESS_LINE INTERNAL_ADDRESS
, HR_BIS.BIS_DECODE_LOOKUP('EMP_APL'
, ASG.ASSIGNMENT_TYPE) ASSIGNMENT_TYPE
, 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
, DECODE(ASG.MANAGER_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.MANAGER_FLAG)) MANAGER_FLAG
, DECODE(ASG.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASG.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASG.FREQUENCY) WORKING_HOURS_FREQUENCY
, ASG.CHANGE_REASON CHANGE_REASON
, DECODE(ASG.SOURCE_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('REC_TYPE'
, ASG.SOURCE_TYPE)) SOURCE_TYPE
, SUP.FULL_NAME SUPERVISOR_NAME
, SUP.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, PPB.PAY_BASIS SALARY_BASIS
, ORGT.NAME ASSIGNMENT_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
, '_KF:PAY:GRP:PGR'
, '_DF:PER:PER_ASSIGNMENTS:ASG'
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_FLAG_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS EXPENSE_CHECK_ADDR_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_EXISTS_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_FLAG_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_FLAG_CODE
, PEO.CURRENT_NPW_FLAG CURRENT_CONTINGENT_FLAG_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, ASG.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_STATUS_CODE
, ASG.MANAGER_FLAG MANAGER_FLAG_CODE
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, ASG.FREQUENCY FREQUENCY_CODE
, ASG.SOURCE_TYPE SOURCE_TYPE_CODE
, PPT.SYSTEM_PERSON_TYPE SYSTEM_PERSON_TYPE_CODE
, ASG.APPLICATION_ID APPLICATION_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ORGT.ORGANIZATION_ID ASSIGNMENT_ORGANIZATION_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, LOCT.LOCATION_ID LOCATION_ID
, PAY.PAYROLL_ID PAYROLL_ID
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, PGR.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASG.PERSON_ID PERSON_ID
, PEO.PERSON_TYPE_ID PERSON_TYPE_ID
, PFT.POSITION_ID POSITION_ID
, RAC.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, PPB.PAY_BASIS_ID SALARY_BASIS_ID
, ASG.SUPERVISOR_ID SUPERVISOR_ID
, VAC.VACANCY_ID VACANCY_ID
, PCA. COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID
, PEO.LAST_UPDATE_DATE PEO_LAST_UPDATE_DATE
, ASG.LAST_UPDATE_DATE ASG_LAST_UPDATE_DATE
, PEO.LAST_UPDATED_BY PEO_LAST_UPDATED_BY
, ASG.LAST_UPDATED_BY ASG_LAST_UPDATED_BY
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 ASG
, PER_PERSON_TYPES PPT
, PER_PERSON_TYPES_TL PPTT
, 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
, FND_TERRITORIES_TL FTRT
, PER_COLLECTIVE_AGREEMENTS PCA
WHERE ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND ASG.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND ASG.LOCATION_ID = LOCT.LOCATION_ID(+)
AND ASG.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND ASG.SUPERVISOR_ID = SUP.PERSON_ID(+)
AND ASG.PEOPLE_GROUP_ID = PGR.PEOPLE_GROUP_ID(+)
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND ASG.VACANCY_ID = VAC.VACANCY_ID (+)
AND ASG.RECRUITMENT_ACTIVITY_ID = RAC.RECRUITMENT_ACTIVITY_ID (+)
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTT.ASSIGNMENT_STATUS_TYPE_ID
AND ASTT.LANGUAGE = USERENV('LANG')
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASG.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 ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND ORGT.LANGUAGE = USERENV('LANG')
AND BGRT.LANGUAGE = USERENV('LANG')
AND PPTT.LANGUAGE = USERENV('LANG')
AND PEO.COUNTRY_OF_BIRTH = FTRT.TERRITORY_CODE(+)
AND FTRT.LANGUAGE(+) = USERENV('LANG')
AND PPT.ACTIVE_FLAG = 'Y'
AND ASG.PERSON_ID = PHN.PARENT_ID (+)
AND PHN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND PHN.PHONE_TYPE (+) = 'W1'
AND ASG.EFFECTIVE_START_DATE BETWEEN PHN.DATE_FROM (+)
AND NVL(PHN.DATE_TO (+)
, HR_GENERAL.END_OF_TIME)
AND ASG.COLLECTIVE_AGREEMENT_ID = PCA.COLLECTIVE_AGREEMENT_ID (+)
AND ASG.EFFECTIVE_START_DATE BETWEEN SUP.EFFECTIVE_START_DATE (+)
AND SUP.EFFECTIVE_END_DATE (+)
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE (+)
AND PAY.EFFECTIVE_END_DATE (+)
AND ( (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASG.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) ) WITH READ ONLY