VIEW: APPS.HRIFV_US_EMPASGDTL_EMPXASG

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,GREATEST(peo.effective_start_date, ass.effective_start_date) record_start_date ,LEAST(peo.effective_end_date, ass.effective_end_date) record_end_date ,peo.effective_start_date employee_start_date ,peo.effective_end_date employee_end_date ,ass.effective_start_date assignment_start_date ,ass.effective_end_date assignment_end_date ,peo.full_name employee_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 ,DECODE(peo.sex, null, null, hr_bis.bis_decode_lookup('SEX',peo.sex)) gender ,peo.previous_last_name previous_last_name ,DECODE(peo.registered_disabled_flag,null, null, hr_bis.bis_decode_lookup('YES_NO',peo.registered_disabled_flag)) registered_disabled ,peo.town_of_birth town_of_birth ,peo.region_of_birth region_of_birth ,peo.country_of_birth country_of_birth ,peo.date_of_birth date_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.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 ,DECODE(peo.current_applicant_flag, null, null, hr_bis.bis_decode_lookup('YES_NO',peo.current_applicant_flag)) current_applicant_flag ,DECODE(peo.current_employee_flag, null, null, hr_bis.bis_decode_lookup('YES_NO',peo.current_employee_flag)) current_employee_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 ,decode(peo.current_employee_flag,'Y',pps.date_start,null) hire_date ,peo.original_date_of_hire original_date_of_hire ,ass.title assignment_title ,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 ,DECODE(ass.employment_category, null, null, hr_bis.bis_decode_lookup('YES_NO',ass.manager_flag)) manager_flag ,DECODE(ass.employment_category, null, null, hr_bis.bis_decode_lookup('EMP_CAT',ass.employment_category)) employment_category ,ass.normal_hours working_hours_amount ,DECODE(ass.frequency, null, null, hr_bis.bis_decode_lookup('FREQUENCY',ass.frequency)) working_hours_frequency ,DECODE(peo.per_information1, null, null, hr_bis.bis_decode_lookup('US_ETHNIC_GROUP',peo.per_information1)) ethnic_origin ,DECODE(peo.per_information2, null, null, hr_bis.bis_decode_lookup('PER_US_I9_STATE',peo.per_information2)) i9 ,fnd_date.canonical_to_date(peo.per_information3) i9_expiry_date ,DECODE(peo.per_information4, null, null, hr_bis.bis_decode_lookup('US_VISA_TYPE',peo.per_information4)) visa_type ,DECODE(peo.per_information5, null, null, hr_bis.bis_decode_lookup('US_VETERAN_STATUS',peo.per_information5)) vietnam_vets_status ,DECODE(peo.per_information6, null, null, hr_bis.bis_decode_lookup('YES_NO',peo.per_information6)) student ,DECODE(peo.per_information7, null, null, hr_bis.bis_decode_lookup('US_NEW_HIRE_STATUS', peo.per_information7)) new_hire_status ,peo.per_information8 exclusion_reason ,DECODE(peo.per_information9, null, null, hr_bis.bis_decode_lookup('YES_NO',peo.per_information9)) child_support_obligation ,peo.national_identifier social_security_number ,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 ,'_DF:PER:PER_ASSIGNMENTS:ass' ,'_DF:PER:PER_PEOPLE:peo' ,peo.sex gender_code ,peo.registered_disabled_flag registered_disabled_code ,peo.marital_status marital_status_code ,peo.nationality nationality_code ,peo.expense_check_send_to_address mail_destination_code ,peo.second_passport_exists second_passport_code ,peo.resume_exists resume_exists_code ,peo.current_applicant_flag current_applicant_code ,peo.current_employee_flag current_employee_code ,peo.fast_path_employee fast_path_employee_code ,peo.on_military_service on_military_service_code ,peo.student_status student_status_code ,ass.primary_flag primary_flag_code ,ass.manager_flag manager_flag_code ,ass.employment_category employment_category_code ,ass.frequency working_hours_frequency_code ,peo.per_information1 ethnic_origin_code ,peo.per_information2 i9_code ,peo.per_information4 visa_type_code ,peo.per_information5 vietnam_vets_status_code ,peo.per_information6 student_code ,peo.per_information7 new_hire_status_code ,peo.per_information9 child_support_obligation_code ,ast.per_system_status per_system_asgn_status_code ,NVL(asta.pay_system_status,ast.pay_system_status) pay_system_asgn_status_code ,peo.person_id person_id ,peo.business_group_id business_group_id ,peo.benefit_group_id benefit_group_id ,peo.person_type_id person_type_id ,peo.comment_id person_comment_id ,ass.assignment_id assignment_id ,ass.grade_id grade_id ,ass.job_id job_id ,ass.location_id location_id ,ass.organization_id organization_id ,ass.payroll_id payroll_id ,ass.period_of_service_id period_of_service_id ,ass.people_group_id people_group_id ,ass.position_id position_id ,ass.recruitment_activity_id recruitment_activity_id ,ass.pay_basis_id salary_basis_id ,ass.supervisor_id supervisor_id ,ass.special_ceiling_step_id special_ceiling_step_id ,ass.comment_id assignment_comment_id ,ass.set_of_books_id set_of_books_id ,ass.collective_agreement_id collective_agreement_id ,ass.contract_id contract_id ,ass.vacancy_id vacancy_id ,ass.recruiter_id recruiter_id ,ass.person_referred_by_id person_referred_by_id ,ass.source_organization_id source_organization_id FROM per_all_people_f peo ,per_business_groups pbg ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,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_periods_of_service pps WHERE ass.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND ass.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND ass.person_id = peo.person_id AND peo.business_group_id = pbg.organization_id AND pbg.legislation_code = 'US' 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.effective_start_date <= peo.effective_start_date AND ass.effective_end_date >= peo.effective_start_date) OR (ass.effective_start_date <= peo.effective_end_date AND ass.effective_end_date >= peo.effective_end_date) OR (ass.effective_start_date >= peo.effective_start_date AND ass.effective_end_date <= peo.effective_end_date) ) AND peo.person_id = pps.person_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 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
, GREATEST(PEO.EFFECTIVE_START_DATE
, ASS.EFFECTIVE_START_DATE) RECORD_START_DATE
, LEAST(PEO.EFFECTIVE_END_DATE
, ASS.EFFECTIVE_END_DATE) RECORD_END_DATE
, PEO.EFFECTIVE_START_DATE EMPLOYEE_START_DATE
, PEO.EFFECTIVE_END_DATE EMPLOYEE_END_DATE
, ASS.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASS.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, PEO.FULL_NAME EMPLOYEE_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
, DECODE(PEO.SEX
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX)) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_NAME
, DECODE(PEO.REGISTERED_DISABLED_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REGISTERED_DISABLED_FLAG)) REGISTERED_DISABLED
, PEO.TOWN_OF_BIRTH TOWN_OF_BIRTH
, PEO.REGION_OF_BIRTH REGION_OF_BIRTH
, PEO.COUNTRY_OF_BIRTH COUNTRY_OF_BIRTH
, PEO.DATE_OF_BIRTH DATE_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.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
, DECODE(PEO.CURRENT_APPLICANT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_APPLICANT_FLAG)) CURRENT_APPLICANT_FLAG
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.CURRENT_EMPLOYEE_FLAG)) CURRENT_EMPLOYEE_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
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, ASS.TITLE ASSIGNMENT_TITLE
, 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
, DECODE(ASS.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASS.MANAGER_FLAG)) MANAGER_FLAG
, DECODE(ASS.EMPLOYMENT_CATEGORY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASS.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY
, ASS.NORMAL_HOURS WORKING_HOURS_AMOUNT
, DECODE(ASS.FREQUENCY
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASS.FREQUENCY)) WORKING_HOURS_FREQUENCY
, DECODE(PEO.PER_INFORMATION1
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_ETHNIC_GROUP'
, PEO.PER_INFORMATION1)) ETHNIC_ORIGIN
, DECODE(PEO.PER_INFORMATION2
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PER_US_I9_STATE'
, PEO.PER_INFORMATION2)) I9
, FND_DATE.CANONICAL_TO_DATE(PEO.PER_INFORMATION3) I9_EXPIRY_DATE
, DECODE(PEO.PER_INFORMATION4
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_VISA_TYPE'
, PEO.PER_INFORMATION4)) VISA_TYPE
, DECODE(PEO.PER_INFORMATION5
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_VETERAN_STATUS'
, PEO.PER_INFORMATION5)) VIETNAM_VETS_STATUS
, DECODE(PEO.PER_INFORMATION6
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION6)) STUDENT
, DECODE(PEO.PER_INFORMATION7
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('US_NEW_HIRE_STATUS'
, PEO.PER_INFORMATION7)) NEW_HIRE_STATUS
, PEO.PER_INFORMATION8 EXCLUSION_REASON
, DECODE(PEO.PER_INFORMATION9
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.PER_INFORMATION9)) CHILD_SUPPORT_OBLIGATION
, PEO.NATIONAL_IDENTIFIER SOCIAL_SECURITY_NUMBER
, 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
, '_DF:PER:PER_ASSIGNMENTS:ASS'
, '_DF:PER:PER_PEOPLE:PEO'
, PEO.SEX GENDER_CODE
, PEO.REGISTERED_DISABLED_FLAG REGISTERED_DISABLED_CODE
, PEO.MARITAL_STATUS MARITAL_STATUS_CODE
, PEO.NATIONALITY NATIONALITY_CODE
, PEO.EXPENSE_CHECK_SEND_TO_ADDRESS MAIL_DESTINATION_CODE
, PEO.SECOND_PASSPORT_EXISTS SECOND_PASSPORT_CODE
, PEO.RESUME_EXISTS RESUME_EXISTS_CODE
, PEO.CURRENT_APPLICANT_FLAG CURRENT_APPLICANT_CODE
, PEO.CURRENT_EMPLOYEE_FLAG CURRENT_EMPLOYEE_CODE
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, PEO.STUDENT_STATUS STUDENT_STATUS_CODE
, ASS.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASS.MANAGER_FLAG MANAGER_FLAG_CODE
, ASS.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASS.FREQUENCY WORKING_HOURS_FREQUENCY_CODE
, PEO.PER_INFORMATION1 ETHNIC_ORIGIN_CODE
, PEO.PER_INFORMATION2 I9_CODE
, PEO.PER_INFORMATION4 VISA_TYPE_CODE
, PEO.PER_INFORMATION5 VIETNAM_VETS_STATUS_CODE
, PEO.PER_INFORMATION6 STUDENT_CODE
, PEO.PER_INFORMATION7 NEW_HIRE_STATUS_CODE
, PEO.PER_INFORMATION9 CHILD_SUPPORT_OBLIGATION_CODE
, AST.PER_SYSTEM_STATUS PER_SYSTEM_ASGN_STATUS_CODE
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS) PAY_SYSTEM_ASGN_STATUS_CODE
, PEO.PERSON_ID PERSON_ID
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEO.BENEFIT_GROUP_ID BENEFIT_GROUP_ID
, PEO.PERSON_TYPE_ID PERSON_TYPE_ID
, PEO.COMMENT_ID PERSON_COMMENT_ID
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ASS.GRADE_ID GRADE_ID
, ASS.JOB_ID JOB_ID
, ASS.LOCATION_ID LOCATION_ID
, ASS.ORGANIZATION_ID ORGANIZATION_ID
, ASS.PAYROLL_ID PAYROLL_ID
, ASS.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASS.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASS.POSITION_ID POSITION_ID
, ASS.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, ASS.PAY_BASIS_ID SALARY_BASIS_ID
, ASS.SUPERVISOR_ID SUPERVISOR_ID
, ASS.SPECIAL_CEILING_STEP_ID SPECIAL_CEILING_STEP_ID
, ASS.COMMENT_ID ASSIGNMENT_COMMENT_ID
, ASS.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, ASS.COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID
, ASS.CONTRACT_ID CONTRACT_ID
, ASS.VACANCY_ID VACANCY_ID
, ASS.RECRUITER_ID RECRUITER_ID
, ASS.PERSON_REFERRED_BY_ID PERSON_REFERRED_BY_ID
, ASS.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
FROM PER_ALL_PEOPLE_F PEO
, PER_BUSINESS_GROUPS PBG
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, 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_PERIODS_OF_SERVICE PPS
WHERE ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASS.PERSON_ID = PEO.PERSON_ID
AND PEO.BUSINESS_GROUP_ID = PBG.ORGANIZATION_ID
AND PBG.LEGISLATION_CODE = 'US'
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.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASS.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASS.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASS.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) )
AND PEO.PERSON_ID = PPS.PERSON_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 ASS.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASS.BUSINESS_GROUP_ID) WITH READ ONLY