VIEW: APPS.HRBV_PEOPLE_ASSIGNMENT

Source

View Text - Preformatted

SELECT 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 ,peo.full_name person_name ,peo.order_name order_name ,peo.employee_number employee_number ,hr_bis.bis_decode_lookup('TITLE',peo.title) title ,peo.middle_names middle_names ,peo.known_as preferred_name ,peo.first_name first_name ,peo.last_name last_name ,peo.pre_name_adjunct prefix ,peo.suffix suffix ,hr_bis.bis_decode_lookup('SEX',peo.sex) gender ,peo.previous_last_name previous_last_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 ,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 ,hr_bis.bis_decode_lookup('WORK_SCHEDULE',peo.work_schedule) work_schedule ,peo.work_telephone work_telephone ,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_emp_or_apl_flag,'N')) current_emp_or_apl_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 ,hr_bis.bis_decode_lookup('YES_NO',peo.background_check_status) background_check_status ,peo.background_date_check background_date_check ,peo.rehire_authorizor rehire_authorizor ,hr_bis.bis_decode_lookup('YES_NO',peo.rehire_recommendation) rehire_recommendation ,peo.rehire_reason rehire_reason ,hr_bis.bis_decode_lookup('BLOOD_TYPE',peo.blood_type) blood_type ,peo.last_medical_test_by last_medical_test_by ,peo.last_medical_test_date last_medical_test_date ,peo.original_date_of_hire original_date_of_hire ,peo.coord_ben_med_pln_no coord_ben_med_pln_no ,peo.coord_ben_no_cvg_flag coord_ben_no_cvg_flag ,peo.coord_ben_med_pl_name coord_ben_med_pl_name ,peo.coord_ben_med_insr_crr_name coord_ben_med_insr_crr_name ,peo.coord_ben_med_insr_crr_ident coord_ben_med_insr_crr_ident ,peo.coord_ben_med_ext_er coord_ben_med_ext_er ,peo.coord_ben_med_cvg_strt_dt coord_ben_med_cvg_strt_dt ,peo.coord_ben_med_cvg_end_dt coord_ben_med_cvg_end_dt ,peo.dpdnt_adoption_date dpdnt_adoption_date ,peo.dpdnt_vlntry_svce_flag dpdnt_vlntry_svce_flag ,peo.receipt_of_death_cert_date receipt_of_death_cert_date ,hr_bis.bis_decode_lookup('TOBACCO_USER',peo.uses_tobacco_flag) uses_tobacco_flag ,'_DF:PER:PER_PEOPLE:peo' ,asg.effective_start_date assignment_start_date ,asg.effective_end_date assignment_end_date ,asg.assignment_number assignment_number ,asg.title assignment_title ,asg.assignment_sequence assignment_sequence ,asg.date_probation_end date_probation_end ,decode(asg.assignment_type ,'E', hr_bis.bis_decode_lookup('EMP_ASSIGN_REASON', asg.change_reason) ,'C', hr_bis.bis_decode_lookup('CWK_ASSIGN_REASON', asg.change_reason)) change_reason ,asg.internal_address_line internal_address_line ,asg.project_title project_title ,asg.time_normal_start time_normal_start ,asg.time_normal_finish time_normal_finish ,asg.notice_period notice_period ,hr_bis.bis_decode_lookup('QUALIFYING_UNITS',asg.notice_period_uom) notice_period_uom ,hr_bis.bis_decode_lookup('YES_NO',asg.work_at_home) work_at_home ,hr_bis.bis_decode_lookup('BARGAINING_UNIT_CODE',asg.bargaining_unit_code) bargaining_unit ,hr_bis.bis_decode_lookup('YES_NO',asg.labour_union_member_flag) labour_union_member_flag ,asg.period_of_placement_date_start period_of_placement_date_start ,asg.probation_period probation_period ,hr_bis.bis_decode_lookup('QUALIFYING_UNITS',asg.probation_unit) probation_unit ,asg.sal_review_period sal_review_period ,hr_bis.bis_decode_lookup('FREQUENCY',asg.sal_review_period_frequency) sal_review_period_frequency ,asg.perf_review_period perf_review_period ,hr_bis.bis_decode_lookup('FREQUENCY',asg.perf_review_period_frequency) perf_review_period_frequency ,hr_bis.bis_decode_lookup('YES_NO',asg.primary_flag) primary_flag ,hr_bis.bis_decode_lookup('YES_NO',asg.manager_flag) manager_flag ,hr_bis.bis_decode_lookup('EMPLOYEE_CATG',asg.employee_category) employee_category ,hr_bis.bis_decode_lookup('EMP_APL',asg.assignment_type) assignment_type ,asg.assignment_category assignment_category ,decode(asg.assignment_type ,'E', hr_bis.bis_decode_lookup('EMP_CAT', asg.employment_category) ,'C', hr_bis.bis_decode_lookup('CWK_ASG_CATEGORY', asg.employment_category)) employment_category ,asg.normal_hours normal_hours ,hr_bis.bis_decode_lookup('FREQUENCY',asg.frequency) normal_hours_frequency ,hr_bis.bis_decode_lookup('REC_TYPE',asg.source_type) source_type ,'_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 ,peo.fast_path_employee fast_path_employee_code ,peo.on_military_service on_military_service_code ,asg.vendor_employee_number vendor_employee_number ,asg.vendor_assignment_number vendor_assignment_number ,asg.job_post_source_name job_post_source_name ,asg.change_reason change_reason_code ,asg.notice_period_uom notice_period_uom_code ,asg.work_at_home work_at_home_code ,asg.bargaining_unit_code bargaining_unit_code ,asg.hourly_salaried_code hourly_salaried_code ,asg.labour_union_member_flag labour_union_member_flag_code ,asg.probation_unit probation_unit_code ,asg.sal_review_period_frequency sal_review_period_freq_code ,asg.perf_review_period_frequency perf_review_period_freq_code ,asg.primary_flag primary_flag_code ,asg.manager_flag manager_flag_code ,asg.employment_category employment_category_code ,asg.assignment_type assignment_type_code ,asg.frequency frequency_code ,asg.source_type source_type_code ,peo.business_group_id peo_business_group_id ,peo.person_id person_id ,peo.global_person_id global_person_id ,peo.party_id party_id ,peo.person_type_id person_type_id ,peo.comment_id peo_comment_id ,peo.vendor_id peo_vendor_id ,peo.benefit_group_id benefit_group_id ,asg.assignment_id assignment_id ,asg.business_group_id asg_business_group_id ,asg.assignment_status_type_id assignment_status_type_id ,asg.grade_id grade_id ,asg.job_id job_id ,asg.location_id location_id ,asg.organization_id organization_id ,asg.payroll_id payroll_id ,asg.period_of_service_id period_of_service_id ,asg.position_id position_id ,asg.people_group_id people_group_id ,asg.soft_coding_keyflex_id soft_coding_keyflex_id ,asg.pay_basis_id pay_basis_id ,asg.supervisor_id supervisor_id ,asg.vacancy_id vacancy_id ,asg.recruiter_id recruiter_id ,asg.person_referred_by_id person_referred_by_id ,asg.recruitment_activity_id recruitment_activity_id ,asg.special_ceiling_step_id special_ceiling_step_id ,asg.source_organization_id source_organization_id ,asg.application_id application_id ,asg.comment_id asg_comment_id ,asg.default_code_comb_id default_code_comb_id ,asg.set_of_books_id set_of_books_id ,asg.contract_id contract_id ,asg.collective_agreement_id collective_agreement_id ,asg.cagr_id_flex_num cagr_id_flex_num ,asg.cagr_grade_def_id cagr_grade_def_id ,asg.establishment_id establishment_id ,asg.vendor_id asg_vendor_id ,peo.last_update_date peo_last_update_date ,peo.last_updated_by peo_last_updated_by ,peo.last_update_login peo_last_update_login ,peo.created_by peo_created_by ,peo.creation_date peo_creation_date ,asg.last_update_date asg_last_update_date ,asg.last_updated_by asg_last_updated_by ,asg.last_update_login asg_last_update_login ,asg.created_by asg_created_by ,asg.creation_date asg_creation_date ,CASE WHEN (asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, asg.business_group_id)) THEN 'Y' ELSE 'N' END bg_security_flag_code FROM per_all_people_f peo ,per_assignments_f asg WHERE asg.person_id = peo.person_id 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 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
, PEO.FULL_NAME PERSON_NAME
, PEO.ORDER_NAME ORDER_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HR_BIS.BIS_DECODE_LOOKUP('TITLE'
, PEO.TITLE) TITLE
, PEO.MIDDLE_NAMES MIDDLE_NAMES
, PEO.KNOWN_AS PREFERRED_NAME
, PEO.FIRST_NAME FIRST_NAME
, PEO.LAST_NAME LAST_NAME
, PEO.PRE_NAME_ADJUNCT PREFIX
, PEO.SUFFIX SUFFIX
, HR_BIS.BIS_DECODE_LOOKUP('SEX'
, PEO.SEX) GENDER
, PEO.PREVIOUS_LAST_NAME PREVIOUS_LAST_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
, 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
, HR_BIS.BIS_DECODE_LOOKUP('WORK_SCHEDULE'
, PEO.WORK_SCHEDULE) WORK_SCHEDULE
, PEO.WORK_TELEPHONE WORK_TELEPHONE
, 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_EMP_OR_APL_FLAG
, 'N')) CURRENT_EMP_OR_APL_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
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.BACKGROUND_CHECK_STATUS) BACKGROUND_CHECK_STATUS
, PEO.BACKGROUND_DATE_CHECK BACKGROUND_DATE_CHECK
, PEO.REHIRE_AUTHORIZOR REHIRE_AUTHORIZOR
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PEO.REHIRE_RECOMMENDATION) REHIRE_RECOMMENDATION
, PEO.REHIRE_REASON REHIRE_REASON
, HR_BIS.BIS_DECODE_LOOKUP('BLOOD_TYPE'
, PEO.BLOOD_TYPE) BLOOD_TYPE
, PEO.LAST_MEDICAL_TEST_BY LAST_MEDICAL_TEST_BY
, PEO.LAST_MEDICAL_TEST_DATE LAST_MEDICAL_TEST_DATE
, PEO.ORIGINAL_DATE_OF_HIRE ORIGINAL_DATE_OF_HIRE
, PEO.COORD_BEN_MED_PLN_NO COORD_BEN_MED_PLN_NO
, PEO.COORD_BEN_NO_CVG_FLAG COORD_BEN_NO_CVG_FLAG
, PEO.COORD_BEN_MED_PL_NAME COORD_BEN_MED_PL_NAME
, PEO.COORD_BEN_MED_INSR_CRR_NAME COORD_BEN_MED_INSR_CRR_NAME
, PEO.COORD_BEN_MED_INSR_CRR_IDENT COORD_BEN_MED_INSR_CRR_IDENT
, PEO.COORD_BEN_MED_EXT_ER COORD_BEN_MED_EXT_ER
, PEO.COORD_BEN_MED_CVG_STRT_DT COORD_BEN_MED_CVG_STRT_DT
, PEO.COORD_BEN_MED_CVG_END_DT COORD_BEN_MED_CVG_END_DT
, PEO.DPDNT_ADOPTION_DATE DPDNT_ADOPTION_DATE
, PEO.DPDNT_VLNTRY_SVCE_FLAG DPDNT_VLNTRY_SVCE_FLAG
, PEO.RECEIPT_OF_DEATH_CERT_DATE RECEIPT_OF_DEATH_CERT_DATE
, HR_BIS.BIS_DECODE_LOOKUP('TOBACCO_USER'
, PEO.USES_TOBACCO_FLAG) USES_TOBACCO_FLAG
, '_DF:PER:PER_PEOPLE:PEO'
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG.TITLE ASSIGNMENT_TITLE
, ASG.ASSIGNMENT_SEQUENCE ASSIGNMENT_SEQUENCE
, ASG.DATE_PROBATION_END DATE_PROBATION_END
, DECODE(ASG.ASSIGNMENT_TYPE
, 'E'
, HR_BIS.BIS_DECODE_LOOKUP('EMP_ASSIGN_REASON'
, ASG.CHANGE_REASON)
, 'C'
, HR_BIS.BIS_DECODE_LOOKUP('CWK_ASSIGN_REASON'
, ASG.CHANGE_REASON)) CHANGE_REASON
, ASG.INTERNAL_ADDRESS_LINE INTERNAL_ADDRESS_LINE
, ASG.PROJECT_TITLE PROJECT_TITLE
, ASG.TIME_NORMAL_START TIME_NORMAL_START
, ASG.TIME_NORMAL_FINISH TIME_NORMAL_FINISH
, ASG.NOTICE_PERIOD NOTICE_PERIOD
, HR_BIS.BIS_DECODE_LOOKUP('QUALIFYING_UNITS'
, ASG.NOTICE_PERIOD_UOM) NOTICE_PERIOD_UOM
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.WORK_AT_HOME) WORK_AT_HOME
, HR_BIS.BIS_DECODE_LOOKUP('BARGAINING_UNIT_CODE'
, ASG.BARGAINING_UNIT_CODE) BARGAINING_UNIT
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.LABOUR_UNION_MEMBER_FLAG) LABOUR_UNION_MEMBER_FLAG
, ASG.PERIOD_OF_PLACEMENT_DATE_START PERIOD_OF_PLACEMENT_DATE_START
, ASG.PROBATION_PERIOD PROBATION_PERIOD
, HR_BIS.BIS_DECODE_LOOKUP('QUALIFYING_UNITS'
, ASG.PROBATION_UNIT) PROBATION_UNIT
, ASG.SAL_REVIEW_PERIOD SAL_REVIEW_PERIOD
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASG.SAL_REVIEW_PERIOD_FREQUENCY) SAL_REVIEW_PERIOD_FREQUENCY
, ASG.PERF_REVIEW_PERIOD PERF_REVIEW_PERIOD
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASG.PERF_REVIEW_PERIOD_FREQUENCY) PERF_REVIEW_PERIOD_FREQUENCY
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.PRIMARY_FLAG) PRIMARY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, ASG.MANAGER_FLAG) MANAGER_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('EMPLOYEE_CATG'
, ASG.EMPLOYEE_CATEGORY) EMPLOYEE_CATEGORY
, HR_BIS.BIS_DECODE_LOOKUP('EMP_APL'
, ASG.ASSIGNMENT_TYPE) ASSIGNMENT_TYPE
, ASG.ASSIGNMENT_CATEGORY ASSIGNMENT_CATEGORY
, DECODE(ASG.ASSIGNMENT_TYPE
, 'E'
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, ASG.EMPLOYMENT_CATEGORY)
, 'C'
, HR_BIS.BIS_DECODE_LOOKUP('CWK_ASG_CATEGORY'
, ASG.EMPLOYMENT_CATEGORY)) EMPLOYMENT_CATEGORY
, ASG.NORMAL_HOURS NORMAL_HOURS
, HR_BIS.BIS_DECODE_LOOKUP('FREQUENCY'
, ASG.FREQUENCY) NORMAL_HOURS_FREQUENCY
, HR_BIS.BIS_DECODE_LOOKUP('REC_TYPE'
, ASG.SOURCE_TYPE) SOURCE_TYPE
, '_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
, PEO.FAST_PATH_EMPLOYEE FAST_PATH_EMPLOYEE_CODE
, PEO.ON_MILITARY_SERVICE ON_MILITARY_SERVICE_CODE
, ASG.VENDOR_EMPLOYEE_NUMBER VENDOR_EMPLOYEE_NUMBER
, ASG.VENDOR_ASSIGNMENT_NUMBER VENDOR_ASSIGNMENT_NUMBER
, ASG.JOB_POST_SOURCE_NAME JOB_POST_SOURCE_NAME
, ASG.CHANGE_REASON CHANGE_REASON_CODE
, ASG.NOTICE_PERIOD_UOM NOTICE_PERIOD_UOM_CODE
, ASG.WORK_AT_HOME WORK_AT_HOME_CODE
, ASG.BARGAINING_UNIT_CODE BARGAINING_UNIT_CODE
, ASG.HOURLY_SALARIED_CODE HOURLY_SALARIED_CODE
, ASG.LABOUR_UNION_MEMBER_FLAG LABOUR_UNION_MEMBER_FLAG_CODE
, ASG.PROBATION_UNIT PROBATION_UNIT_CODE
, ASG.SAL_REVIEW_PERIOD_FREQUENCY SAL_REVIEW_PERIOD_FREQ_CODE
, ASG.PERF_REVIEW_PERIOD_FREQUENCY PERF_REVIEW_PERIOD_FREQ_CODE
, ASG.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASG.MANAGER_FLAG MANAGER_FLAG_CODE
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, ASG.FREQUENCY FREQUENCY_CODE
, ASG.SOURCE_TYPE SOURCE_TYPE_CODE
, PEO.BUSINESS_GROUP_ID PEO_BUSINESS_GROUP_ID
, PEO.PERSON_ID PERSON_ID
, PEO.GLOBAL_PERSON_ID GLOBAL_PERSON_ID
, PEO.PARTY_ID PARTY_ID
, PEO.PERSON_TYPE_ID PERSON_TYPE_ID
, PEO.COMMENT_ID PEO_COMMENT_ID
, PEO.VENDOR_ID PEO_VENDOR_ID
, PEO.BENEFIT_GROUP_ID BENEFIT_GROUP_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID ASG_BUSINESS_GROUP_ID
, ASG.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, ASG.GRADE_ID GRADE_ID
, ASG.JOB_ID JOB_ID
, ASG.LOCATION_ID LOCATION_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.PAYROLL_ID PAYROLL_ID
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASG.POSITION_ID POSITION_ID
, ASG.PEOPLE_GROUP_ID PEOPLE_GROUP_ID
, ASG.SOFT_CODING_KEYFLEX_ID SOFT_CODING_KEYFLEX_ID
, ASG.PAY_BASIS_ID PAY_BASIS_ID
, ASG.SUPERVISOR_ID SUPERVISOR_ID
, ASG.VACANCY_ID VACANCY_ID
, ASG.RECRUITER_ID RECRUITER_ID
, ASG.PERSON_REFERRED_BY_ID PERSON_REFERRED_BY_ID
, ASG.RECRUITMENT_ACTIVITY_ID RECRUITMENT_ACTIVITY_ID
, ASG.SPECIAL_CEILING_STEP_ID SPECIAL_CEILING_STEP_ID
, ASG.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION_ID
, ASG.APPLICATION_ID APPLICATION_ID
, ASG.COMMENT_ID ASG_COMMENT_ID
, ASG.DEFAULT_CODE_COMB_ID DEFAULT_CODE_COMB_ID
, ASG.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, ASG.CONTRACT_ID CONTRACT_ID
, ASG.COLLECTIVE_AGREEMENT_ID COLLECTIVE_AGREEMENT_ID
, ASG.CAGR_ID_FLEX_NUM CAGR_ID_FLEX_NUM
, ASG.CAGR_GRADE_DEF_ID CAGR_GRADE_DEF_ID
, ASG.ESTABLISHMENT_ID ESTABLISHMENT_ID
, ASG.VENDOR_ID ASG_VENDOR_ID
, PEO.LAST_UPDATE_DATE PEO_LAST_UPDATE_DATE
, PEO.LAST_UPDATED_BY PEO_LAST_UPDATED_BY
, PEO.LAST_UPDATE_LOGIN PEO_LAST_UPDATE_LOGIN
, PEO.CREATED_BY PEO_CREATED_BY
, PEO.CREATION_DATE PEO_CREATION_DATE
, ASG.LAST_UPDATE_DATE ASG_LAST_UPDATE_DATE
, ASG.LAST_UPDATED_BY ASG_LAST_UPDATED_BY
, ASG.LAST_UPDATE_LOGIN ASG_LAST_UPDATE_LOGIN
, ASG.CREATED_BY ASG_CREATED_BY
, ASG.CREATION_DATE ASG_CREATION_DATE
, CASE WHEN (ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID)) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_ALL_PEOPLE_F PEO
, PER_ASSIGNMENTS_F ASG
WHERE ASG.PERSON_ID = PEO.PERSON_ID
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