VIEW: APPS.HRI_MB_WRK_ACTIVITY_V

Source

View Text - Preformatted

SELECT 1 row_indicator ,DECODE(asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,asg.effective_start_date event_date ,asg.effective_start_date effective_start_date ,asg.effective_end_date effective_to_date /* Ids */ ,'NA_EDW' move_event_type_code ,'GAIN_HIRE' gain_event_type_code ,'NA_EDW' loss_event_type_code ,'Y' assignment_start_code ,'NA_EDW' assignment_end_code ,'ORG_GAIN' org_change_type_code ,'SUP_GAIN' sup_change_type_code ,'LOC_GAIN' loc_change_type_code ,'JOB_GAIN' job_change_type_code ,'GRD_GAIN' grd_change_type_code ,'POS_GAIN' pos_change_type_code ,'NA_EDW' primary_flag_change_code ,NVL(asg.change_reason,'NA_EDW') reason_code ,'NA_EDW' separation_category_code ,asg.person_id person_id ,-1 assignment_from_id ,-1 business_group_from_id ,-1 organization_from_id ,-1 supervisor_from_id ,-1 location_from_id ,-1 job_from_id ,-1 grade_from_id ,-1 position_from_id ,'N' primary_from_flag_code ,-1 assignment_status_type_from_id ,'NA_EDW' per_system_status_from_code ,'NA_EDW' pay_system_status_from_code ,asg.assignment_id assignment_to_id ,asg.business_group_id business_group_to_id ,asg.organization_id organization_to_id ,asg.supervisor_id supervisor_to_id ,asg.location_id location_to_id ,asg.job_id job_to_id ,asg.grade_id grade_to_id ,asg.position_id position_to_id ,asg.primary_flag primary_to_flag_code ,ast.assignment_status_type_id assignment_status_type_to_id ,ast.per_system_status per_system_status_to_code ,ast.pay_system_status pay_system_status_to_code ,GREATEST(asg.last_update_date ,pos.last_update_date) last_change_date FROM per_all_assignments_f asg ,per_periods_of_service pos ,per_assignment_status_types ast WHERE asg.period_of_service_id = pos.period_of_service_id AND asg.assignment_status_type_id = ast.assignment_status_type_id AND asg.assignment_type = 'E' AND ast.per_system_status <> 'TERM_ASSIGN' AND asg.effective_start_date = pos.date_start UNION ALL SELECT 1 row_indicator ,DECODE(asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,asg.effective_end_date event_date ,asg.effective_start_date effective_start_date ,asg.effective_end_date effective_end_date ,'NA_EDW' move_event_type_code ,'NA_EDW' gain_event_type_code ,'LOSS_SEP' loss_event_type_code ,'NA_EDW' assignment_start_code ,'Y' assignment_end_code ,'ORG_LOSS' org_change_type_code ,'SUP_LOSS' sup_change_type_code ,'LOC_LOSS' loc_change_type_code ,'JOB_LOSS' job_change_type_code ,'GRD_LOSS' grd_change_type_code ,'POS_LOSS' pos_change_type_code ,'NA_EDW' primary_flag_change_code ,NVL(pos.leaving_reason,'NA_EDW') reason_code ,scr.separation_category_code separation_category_code ,asg.person_id person_id ,asg.assignment_id assignment_from_id ,asg.business_group_id business_group_from_id ,asg.organization_id organization_from_id ,asg.supervisor_id supervisor_from_id ,asg.location_id location_from_id ,asg.job_id job_from_id ,asg.grade_id grade_from_id ,asg.position_id position_from_id ,asg.primary_flag primary_from_flag_code ,ast.assignment_status_type_id assignment_status_type_from_id ,ast.per_system_status per_system_status_from_code ,ast.pay_system_status pay_system_status_from_code ,-1 assignment_to_id ,-1 business_group_to_id ,-1 organization_to_id ,-1 supervisor_to_id ,-1 location_to_id ,-1 job_to_id ,-1 grade_to_id ,-1 position_to_id ,'N' primary_to_flag_code ,-1 assignment_status_type_to_id ,'NA_EDW' per_system_status_to_code ,'NA_EDW' pay_system_status_to_code ,GREATEST(asg.last_update_date ,pos.last_update_date) last_change_date FROM per_all_assignments_f asg , per_periods_of_service pos , per_assignment_status_types ast , hri_cs_sepcr_v scr WHERE asg.period_of_service_id = pos.period_of_service_id AND ast.assignment_status_type_id = asg.assignment_status_type_id AND NVL(pos.leaving_reason,'NA_EDW') = scr.separation_reason_code AND pos.actual_termination_date <= TRUNC(SYSDATE) AND asg.effective_end_date = pos.actual_termination_date UNION ALL SELECT 1 row_indicator ,DECODE(next_asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,next_asg.effective_start_date event_date ,next_asg.effective_start_date effective_start_date ,next_asg.effective_end_date effective_end_date ,'ASG_CHANGE' move_event_type_code ,'NA_EDW' gain_event_type_code ,'NA_EDW' loss_event_type_code ,'NA_EDW' assignment_start_code ,'NA_EDW' assignment_end_code ,DECODE(prev_asg.organization_id, next_asg.organization_id, 'NA_EDW', 'ORG_CHANGE') org_change_type_code ,DECODE(prev_asg.supervisor_id, next_asg.supervisor_id, 'NA_EDW', 'SUP_CHANGE') sup_change_type_code ,DECODE(prev_asg.location_id, next_asg.location_id, 'NA_EDW', 'LOC_CHANGE') loc_change_type_code ,DECODE(prev_asg.job_id, next_asg.job_id, 'NA_EDW', 'JOB_CHANGE') job_change_type_code ,DECODE(prev_asg.grade_id, next_asg.grade_id, 'NA_EDW', 'GRD_CHANGE') grd_change_type_code ,DECODE(prev_asg.position_id, next_asg.position_id, 'NA_EDW', 'POS_CHANGE') pos_change_type_code ,DECODE(prev_asg.primary_flag, next_asg.primary_flag, NULL, 'Y') primary_flag_change_code ,NVL(next_asg.change_reason,'NA_EDW') reason_code ,'NA_EDW' separation_category_code ,next_asg.person_id person_id ,prev_asg.assignment_id assignment_from_id ,prev_asg.business_group_id business_group_from_id ,prev_asg.organization_id organization_from_id ,prev_asg.supervisor_id supervisor_from_id ,prev_asg.location_id location_from_id ,prev_asg.job_id job_from_id ,prev_asg.grade_id grade_from_id ,prev_asg.position_id position_from_id ,prev_asg.primary_flag primary_from_flag_code ,prev_ast.assignment_status_type_id assignment_status_type_from_id ,prev_ast.per_system_status per_system_status_from_code ,prev_ast.pay_system_status pay_system_status_from_code ,next_asg.assignment_id assignment_to_id ,next_asg.business_group_id business_group_to_id ,next_asg.organization_id organization_to_id ,next_asg.supervisor_id supervisor_to_id ,next_asg.location_id location_to_id ,next_asg.job_id job_to_id ,next_asg.grade_id grade_to_id ,next_asg.position_id position_to_id ,next_asg.primary_flag primary_to_flag_code ,next_ast.assignment_status_type_id assignment_status_type_to_id ,next_ast.per_system_status per_system_status_to_code ,next_ast.pay_system_status pay_system_status_to_code ,GREATEST(prev_asg.last_update_date ,next_asg.last_update_date) last_change_date FROM per_all_assignments_f prev_asg ,per_all_assignments_f next_asg ,per_assignment_status_types prev_ast ,per_assignment_status_types next_ast WHERE prev_asg.assignment_id = next_asg.assignment_id AND prev_asg.effective_end_date + 1 = next_asg.effective_start_date AND next_asg.assignment_status_type_id = next_ast.assignment_status_type_id AND prev_asg.assignment_status_type_id = prev_ast.assignment_status_type_id AND prev_asg.assignment_type = 'E' AND next_asg.assignment_type = 'E' AND next_ast.per_system_status <> 'TERM_ASSIGN' UNION ALL SELECT 1 row_indicator ,DECODE(asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,asg.effective_start_date event_date ,asg.effective_start_date effective_start_date ,asg.effective_end_date effective_end_date ,'NA_EDW' move_event_type_code ,'GAIN_START' gain_event_type_code ,'NA_EDW' loss_event_type_code ,'Y' assignment_start_code ,'NA_EDW' assignment_end_code ,'ORG_GAIN' org_change_type_code ,'SUP_GAIN' sup_change_type_code ,'LOC_GAIN' loc_change_type_code ,'JOB_GAIN' job_change_type_code ,'GRD_GAIN' grd_change_type_code ,'POS_GAIN' pos_change_type_code ,'NA_EDW' primary_flag_change_code ,NVL(asg.change_reason,'NA_EDW') reason_code ,'NA_EDW' separation_category_code ,asg.person_id person_id ,-1 assignment_from_id ,-1 business_group_from_id ,-1 organization_from_id ,-1 supervisor_from_id ,-1 location_from_id ,-1 job_from_id ,-1 grade_from_id ,-1 position_from_id ,'N' primary_from_flag_code ,-1 assignment_status_type_from_id ,'NA_EDW' per_system_status_from_code ,'NA_EDW' pay_system_status_from_code ,asg.assignment_id assignment_to_id ,asg.business_group_id business_group_to_id ,asg.organization_id organization_to_id ,asg.supervisor_id supervisor_to_id ,asg.location_id location_to_id ,asg.job_id job_to_id ,asg.grade_id grade_to_id ,asg.position_id position_to_id ,asg.primary_flag primary_to_flag_code ,ast.assignment_status_type_id assignment_status_type_to_id ,ast.per_system_status per_system_status_to_code ,ast.pay_system_status pay_system_status_to_code ,GREATEST(asg.last_update_date ,pos.last_update_date) last_change_date FROM per_all_assignments_f asg ,per_periods_of_service pos ,per_assignment_status_types ast WHERE asg.period_of_service_id = pos.period_of_service_id AND asg.assignment_status_type_id = ast.assignment_status_type_id AND asg.assignment_type = 'E' AND ast.per_system_status <> 'TERM_ASSIGN' AND asg.primary_flag = 'N' AND asg.effective_start_date > pos.date_start AND asg.effective_start_date = (SELECT MIN(all_asg.effective_start_date) FROM per_all_assignments_f all_asg ,per_assignment_status_types ast WHERE all_asg.assignment_id = asg.assignment_id AND all_asg.assignment_type = 'E' AND ast.assignment_status_type_id = all_asg.assignment_status_type_id AND ast.per_system_status <> 'TERM_ASSIGN' AND all_asg.period_of_service_id = pos.period_of_service_id) UNION ALL SELECT 1 row_indicator ,DECODE(prev_asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,term_asg.effective_start_date event_date ,term_asg.effective_start_date effective_start_date ,term_asg.effective_end_date effective_end_date ,'NA_EDW' move_event_type_code ,'NA_EDW' gain_event_type_code ,'LOSS_ASG' loss_event_type_code ,'NA_EDW' assignment_start_code ,'Y' assignment_end_code ,'ORG_LOSS' org_change_type_code ,'SUP_LOSS' sup_change_type_code ,'LOC_LOSS' loc_change_type_code ,'JOB_LOSS' job_change_type_code ,'GRD_LOSS' grd_change_type_code ,'POS_LOSS' pos_change_type_code ,'NA_EDW' primary_flag_change_code ,NVL(term_asg.change_reason,'NA_EDW') reason_code ,'NA_EDW' separation_category_code ,prev_asg.person_id person_id ,prev_asg.assignment_id assignment_from_id ,prev_asg.business_group_id business_group_from_id ,prev_asg.organization_id organization_from_id ,prev_asg.supervisor_id supervisor_from_id ,prev_asg.location_id location_from_id ,prev_asg.job_id job_from_id ,prev_asg.grade_id grade_from_id ,prev_asg.position_id position_from_id ,prev_asg.primary_flag primary_from_flag_code ,prev_ast.assignment_status_type_id assignment_status_type_from_id ,prev_ast.per_system_status per_system_status_from_code ,prev_ast.pay_system_status pay_system_status_from_code ,-1 assignment_to_id ,-1 business_group_to_id ,-1 organization_to_id ,-1 supervisor_to_id ,-1 location_to_id ,-1 job_to_id ,-1 grade_to_id ,-1 position_to_id ,'N' primary_to_flag_code ,term_asg.assignment_status_type_id assignment_status_type_to_id ,term_ast.per_system_status per_system_status_to_code ,term_ast.pay_system_status pay_system_status_to_code ,GREATEST(prev_asg.last_update_date ,term_asg.last_update_date ,pos.last_update_date) last_change_date FROM per_all_assignments_f prev_asg ,per_all_assignments_f term_asg ,per_periods_of_service pos ,per_assignment_status_types prev_ast ,per_assignment_status_types term_ast WHERE prev_asg.period_of_service_id = pos.period_of_service_id AND prev_asg.assignment_status_type_id = prev_ast.assignment_status_type_id AND term_asg.assignment_status_type_id = term_ast.assignment_status_type_id AND prev_asg.assignment_id = term_asg.assignment_id AND prev_asg.effective_end_date + 1 = term_asg.effective_start_date AND prev_ast.per_system_status <> 'TERM_ASSIGN' AND term_ast.per_system_status = 'TERM_ASSIGN' AND term_asg.effective_start_date <= TRUNC(SYSDATE) AND (pos.actual_termination_date IS NULL OR term_asg.effective_start_date < pos.actual_termination_date) UNION ALL SELECT 1 row_indicator ,DECODE(asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,asg.effective_end_date event_date ,asg.effective_start_date effective_start_date ,asg.effective_end_date effective_end_date /* Ids */ ,'NA_EDW' move_event_type_code ,'NA_EDW' gain_event_type_code ,'LOSS_ASG' loss_event_type_code ,'NA_EDW' assignment_start_code ,'Y' assignment_end_code ,'ORG_LOSS' org_change_type_code ,'SUP_LOSS' sup_change_type_code ,'LOC_LOSS' loc_change_type_code ,'JOB_LOSS' job_change_type_code ,'GRD_LOSS' grd_change_type_code ,'POS_LOSS' pos_change_type_code ,'NA_EDW' primary_flag_change_code ,'NA_EDW' reason_code ,'NA_EDW' separation_category_code ,asg.person_id person_id ,asg.assignment_id assignment_from_id ,asg.business_group_id business_group_from_id ,asg.organization_id organization_from_id ,asg.supervisor_id supervisor_from_id ,asg.location_id location_from_id ,asg.job_id job_from_id ,asg.grade_id grade_from_id ,asg.position_id position_from_id ,asg.primary_flag primary_from_flag_code ,ast.assignment_status_type_id assignment_status_type_to_id ,ast.per_system_status per_system_status_to_code ,ast.pay_system_status pay_system_status_to_code ,-1 assignment_to_id ,-1 business_group_to_id ,-1 organization_to_id ,-1 supervisor_to_id ,-1 location_to_id ,-1 job_to_id ,-1 grade_to_id ,-1 position_to_id ,'N' primary_to_flag_code ,-1 assignment_status_type_to_id ,'NA_EDW' per_system_status_to_code ,'NA_EDW' pay_system_status_to_code ,GREATEST(asg.last_update_date ,pos.last_update_date) last_change_date FROM per_all_assignments_f asg ,per_periods_of_service pos ,per_assignment_status_types ast WHERE asg.period_of_service_id = pos.period_of_service_id AND ast.assignment_status_type_id = asg.assignment_status_type_id AND ast.per_system_status <> 'TERM_ASSIGN' AND asg.primary_flag = 'N' AND asg.effective_end_date <= TRUNC(SYSDATE) AND (pos.actual_termination_date IS NULL OR asg.effective_end_date < pos.actual_termination_date) AND NOT EXISTS (SELECT null FROM per_all_assignments_f next_asg WHERE asg.assignment_id = next_asg.assignment_id AND asg.effective_end_date + 1 = next_asg.effective_start_date AND next_asg.assignment_type = 'E') UNION ALL SELECT 1 row_indicator ,DECODE(next_asg.primary_flag, 'Y', 1, 0) primary_asg_indicator ,next_asg.effective_start_date event_date ,next_asg.effective_start_date effective_start_date ,next_asg.effective_end_date effective_end_date ,'MOVE_PRIM_ASG' move_event_type_code ,'NA_EDW' gain_event_type_code ,'NA_EDW' loss_event_type_code ,'Y' assignment_start_code ,'NA_EDW' assignment_end_code ,DECODE(prev_asg.organization_id, next_asg.organization_id, 'NA_EDW', 'ORG_CHANGE') org_change_type_code ,DECODE(prev_asg.supervisor_id, next_asg.supervisor_id, 'NA_EDW', 'SUP_CHANGE') sup_change_type_code ,DECODE(prev_asg.location_id, next_asg.location_id, 'NA_EDW', 'LOC_CHANGE') loc_change_type_code ,DECODE(prev_asg.job_id, next_asg.job_id, 'NA_EDW', 'JOB_CHANGE') job_change_type_code ,DECODE(prev_asg.grade_id, next_asg.grade_id, 'NA_EDW', 'GRD_CHANGE') grd_change_type_code ,DECODE(prev_asg.position_id, next_asg.position_id, 'NA_EDW', 'POS_CHANGE') pos_change_type_code ,'Y' primary_flag_change_code ,NVL(next_asg.change_reason,'NA_EDW') reason_code ,'NA_EDW' separation_category_code ,next_asg.person_id person_id ,prev_asg.assignment_id assignment_from_id ,prev_asg.business_group_id business_group_from_id ,prev_asg.organization_id organization_from_id ,prev_asg.supervisor_id supervisor_from_id ,prev_asg.job_id job_from_id ,prev_asg.location_id location_from_id ,prev_asg.grade_id grade_from_id ,prev_asg.position_id position_from_id ,next_asg.primary_flag primary_from_flag_code ,prev_ast.assignment_status_type_id assignment_status_type_from_id ,prev_ast.per_system_status per_system_status_from_code ,prev_ast.pay_system_status pay_system_status_from_code ,next_asg.assignment_id assignment_to_id ,next_asg.business_group_id business_group_to_id ,next_asg.organization_id organization_to_id ,next_asg.supervisor_id supervisor_to_id ,next_asg.location_id location_to_id ,next_asg.job_id job_to_id ,next_asg.grade_id grade_to_id ,next_asg.position_id position_to_id ,next_asg.primary_flag primary_to_flag_code ,next_ast.assignment_status_type_id assignment_status_type_to_id ,next_ast.per_system_status per_system_status_to_code ,next_ast.pay_system_status pay_system_status_to_code ,GREATEST(prev_asg.last_update_date ,next_asg.last_update_date) last_change_date FROM per_all_assignments_f prev_asg ,per_all_assignments_f next_asg ,per_assignment_status_types prev_ast ,per_assignment_status_types next_ast WHERE prev_asg.person_id = next_asg.person_id AND prev_asg.effective_end_date + 1 = next_asg.effective_start_date AND next_asg.assignment_status_type_id = next_ast.assignment_status_type_id AND prev_asg.assignment_status_type_id = prev_ast.assignment_status_type_id AND prev_asg.period_of_service_id = next_asg.period_of_service_id AND prev_asg.assignment_id <> next_asg.assignment_id AND prev_asg.primary_flag = 'Y' AND next_asg.primary_flag = 'Y' AND prev_asg.assignment_type = 'E' AND next_asg.assignment_type = 'E' AND next_ast.per_system_status <> 'TERM_ASSIGN' AND NOT EXISTS (SELECT null FROM per_all_assignments_f first WHERE first.assignment_id = next_asg.assignment_id AND first.assignment_type = 'E' AND first.effective_start_date < next_asg.effective_start_date) WITH READ ONLY

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, DECODE(ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, ASG.EFFECTIVE_START_DATE EVENT_DATE
, ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE EFFECTIVE_TO_DATE /* IDS */
, 'NA_EDW' MOVE_EVENT_TYPE_CODE
, 'GAIN_HIRE' GAIN_EVENT_TYPE_CODE
, 'NA_EDW' LOSS_EVENT_TYPE_CODE
, 'Y' ASSIGNMENT_START_CODE
, 'NA_EDW' ASSIGNMENT_END_CODE
, 'ORG_GAIN' ORG_CHANGE_TYPE_CODE
, 'SUP_GAIN' SUP_CHANGE_TYPE_CODE
, 'LOC_GAIN' LOC_CHANGE_TYPE_CODE
, 'JOB_GAIN' JOB_CHANGE_TYPE_CODE
, 'GRD_GAIN' GRD_CHANGE_TYPE_CODE
, 'POS_GAIN' POS_CHANGE_TYPE_CODE
, 'NA_EDW' PRIMARY_FLAG_CHANGE_CODE
, NVL(ASG.CHANGE_REASON
, 'NA_EDW') REASON_CODE
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, ASG.PERSON_ID PERSON_ID
, -1 ASSIGNMENT_FROM_ID
, -1 BUSINESS_GROUP_FROM_ID
, -1 ORGANIZATION_FROM_ID
, -1 SUPERVISOR_FROM_ID
, -1 LOCATION_FROM_ID
, -1 JOB_FROM_ID
, -1 GRADE_FROM_ID
, -1 POSITION_FROM_ID
, 'N' PRIMARY_FROM_FLAG_CODE
, -1 ASSIGNMENT_STATUS_TYPE_FROM_ID
, 'NA_EDW' PER_SYSTEM_STATUS_FROM_CODE
, 'NA_EDW' PAY_SYSTEM_STATUS_FROM_CODE
, ASG.ASSIGNMENT_ID ASSIGNMENT_TO_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_TO_ID
, ASG.ORGANIZATION_ID ORGANIZATION_TO_ID
, ASG.SUPERVISOR_ID SUPERVISOR_TO_ID
, ASG.LOCATION_ID LOCATION_TO_ID
, ASG.JOB_ID JOB_TO_ID
, ASG.GRADE_ID GRADE_TO_ID
, ASG.POSITION_ID POSITION_TO_ID
, ASG.PRIMARY_FLAG PRIMARY_TO_FLAG_CODE
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_TO_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(ASG.LAST_UPDATE_DATE
, POS.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_PERIODS_OF_SERVICE POS
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND ASG.ASSIGNMENT_TYPE = 'E'
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASG.EFFECTIVE_START_DATE = POS.DATE_START UNION ALL SELECT 1 ROW_INDICATOR
, DECODE(ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, ASG.EFFECTIVE_END_DATE EVENT_DATE
, ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'NA_EDW' MOVE_EVENT_TYPE_CODE
, 'NA_EDW' GAIN_EVENT_TYPE_CODE
, 'LOSS_SEP' LOSS_EVENT_TYPE_CODE
, 'NA_EDW' ASSIGNMENT_START_CODE
, 'Y' ASSIGNMENT_END_CODE
, 'ORG_LOSS' ORG_CHANGE_TYPE_CODE
, 'SUP_LOSS' SUP_CHANGE_TYPE_CODE
, 'LOC_LOSS' LOC_CHANGE_TYPE_CODE
, 'JOB_LOSS' JOB_CHANGE_TYPE_CODE
, 'GRD_LOSS' GRD_CHANGE_TYPE_CODE
, 'POS_LOSS' POS_CHANGE_TYPE_CODE
, 'NA_EDW' PRIMARY_FLAG_CHANGE_CODE
, NVL(POS.LEAVING_REASON
, 'NA_EDW') REASON_CODE
, SCR.SEPARATION_CATEGORY_CODE SEPARATION_CATEGORY_CODE
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_FROM_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_FROM_ID
, ASG.ORGANIZATION_ID ORGANIZATION_FROM_ID
, ASG.SUPERVISOR_ID SUPERVISOR_FROM_ID
, ASG.LOCATION_ID LOCATION_FROM_ID
, ASG.JOB_ID JOB_FROM_ID
, ASG.GRADE_ID GRADE_FROM_ID
, ASG.POSITION_ID POSITION_FROM_ID
, ASG.PRIMARY_FLAG PRIMARY_FROM_FLAG_CODE
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_FROM_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_FROM_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_FROM_CODE
, -1 ASSIGNMENT_TO_ID
, -1 BUSINESS_GROUP_TO_ID
, -1 ORGANIZATION_TO_ID
, -1 SUPERVISOR_TO_ID
, -1 LOCATION_TO_ID
, -1 JOB_TO_ID
, -1 GRADE_TO_ID
, -1 POSITION_TO_ID
, 'N' PRIMARY_TO_FLAG_CODE
, -1 ASSIGNMENT_STATUS_TYPE_TO_ID
, 'NA_EDW' PER_SYSTEM_STATUS_TO_CODE
, 'NA_EDW' PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(ASG.LAST_UPDATE_DATE
, POS.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_PERIODS_OF_SERVICE POS
, PER_ASSIGNMENT_STATUS_TYPES AST
, HRI_CS_SEPCR_V SCR
WHERE ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND NVL(POS.LEAVING_REASON
, 'NA_EDW') = SCR.SEPARATION_REASON_CODE
AND POS.ACTUAL_TERMINATION_DATE <= TRUNC(SYSDATE)
AND ASG.EFFECTIVE_END_DATE = POS.ACTUAL_TERMINATION_DATE UNION ALL SELECT 1 ROW_INDICATOR
, DECODE(NEXT_ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, NEXT_ASG.EFFECTIVE_START_DATE EVENT_DATE
, NEXT_ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, NEXT_ASG.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'ASG_CHANGE' MOVE_EVENT_TYPE_CODE
, 'NA_EDW' GAIN_EVENT_TYPE_CODE
, 'NA_EDW' LOSS_EVENT_TYPE_CODE
, 'NA_EDW' ASSIGNMENT_START_CODE
, 'NA_EDW' ASSIGNMENT_END_CODE
, DECODE(PREV_ASG.ORGANIZATION_ID
, NEXT_ASG.ORGANIZATION_ID
, 'NA_EDW'
, 'ORG_CHANGE') ORG_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.SUPERVISOR_ID
, NEXT_ASG.SUPERVISOR_ID
, 'NA_EDW'
, 'SUP_CHANGE') SUP_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.LOCATION_ID
, NEXT_ASG.LOCATION_ID
, 'NA_EDW'
, 'LOC_CHANGE') LOC_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.JOB_ID
, NEXT_ASG.JOB_ID
, 'NA_EDW'
, 'JOB_CHANGE') JOB_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.GRADE_ID
, NEXT_ASG.GRADE_ID
, 'NA_EDW'
, 'GRD_CHANGE') GRD_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.POSITION_ID
, NEXT_ASG.POSITION_ID
, 'NA_EDW'
, 'POS_CHANGE') POS_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.PRIMARY_FLAG
, NEXT_ASG.PRIMARY_FLAG
, NULL
, 'Y') PRIMARY_FLAG_CHANGE_CODE
, NVL(NEXT_ASG.CHANGE_REASON
, 'NA_EDW') REASON_CODE
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, NEXT_ASG.PERSON_ID PERSON_ID
, PREV_ASG.ASSIGNMENT_ID ASSIGNMENT_FROM_ID
, PREV_ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_FROM_ID
, PREV_ASG.ORGANIZATION_ID ORGANIZATION_FROM_ID
, PREV_ASG.SUPERVISOR_ID SUPERVISOR_FROM_ID
, PREV_ASG.LOCATION_ID LOCATION_FROM_ID
, PREV_ASG.JOB_ID JOB_FROM_ID
, PREV_ASG.GRADE_ID GRADE_FROM_ID
, PREV_ASG.POSITION_ID POSITION_FROM_ID
, PREV_ASG.PRIMARY_FLAG PRIMARY_FROM_FLAG_CODE
, PREV_AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_FROM_ID
, PREV_AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_FROM_CODE
, PREV_AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_FROM_CODE
, NEXT_ASG.ASSIGNMENT_ID ASSIGNMENT_TO_ID
, NEXT_ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_TO_ID
, NEXT_ASG.ORGANIZATION_ID ORGANIZATION_TO_ID
, NEXT_ASG.SUPERVISOR_ID SUPERVISOR_TO_ID
, NEXT_ASG.LOCATION_ID LOCATION_TO_ID
, NEXT_ASG.JOB_ID JOB_TO_ID
, NEXT_ASG.GRADE_ID GRADE_TO_ID
, NEXT_ASG.POSITION_ID POSITION_TO_ID
, NEXT_ASG.PRIMARY_FLAG PRIMARY_TO_FLAG_CODE
, NEXT_AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, NEXT_AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_TO_CODE
, NEXT_AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(PREV_ASG.LAST_UPDATE_DATE
, NEXT_ASG.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F PREV_ASG
, PER_ALL_ASSIGNMENTS_F NEXT_ASG
, PER_ASSIGNMENT_STATUS_TYPES PREV_AST
, PER_ASSIGNMENT_STATUS_TYPES NEXT_AST
WHERE PREV_ASG.ASSIGNMENT_ID = NEXT_ASG.ASSIGNMENT_ID
AND PREV_ASG.EFFECTIVE_END_DATE + 1 = NEXT_ASG.EFFECTIVE_START_DATE
AND NEXT_ASG.ASSIGNMENT_STATUS_TYPE_ID = NEXT_AST.ASSIGNMENT_STATUS_TYPE_ID
AND PREV_ASG.ASSIGNMENT_STATUS_TYPE_ID = PREV_AST.ASSIGNMENT_STATUS_TYPE_ID
AND PREV_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN' UNION ALL SELECT 1 ROW_INDICATOR
, DECODE(ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, ASG.EFFECTIVE_START_DATE EVENT_DATE
, ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'NA_EDW' MOVE_EVENT_TYPE_CODE
, 'GAIN_START' GAIN_EVENT_TYPE_CODE
, 'NA_EDW' LOSS_EVENT_TYPE_CODE
, 'Y' ASSIGNMENT_START_CODE
, 'NA_EDW' ASSIGNMENT_END_CODE
, 'ORG_GAIN' ORG_CHANGE_TYPE_CODE
, 'SUP_GAIN' SUP_CHANGE_TYPE_CODE
, 'LOC_GAIN' LOC_CHANGE_TYPE_CODE
, 'JOB_GAIN' JOB_CHANGE_TYPE_CODE
, 'GRD_GAIN' GRD_CHANGE_TYPE_CODE
, 'POS_GAIN' POS_CHANGE_TYPE_CODE
, 'NA_EDW' PRIMARY_FLAG_CHANGE_CODE
, NVL(ASG.CHANGE_REASON
, 'NA_EDW') REASON_CODE
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, ASG.PERSON_ID PERSON_ID
, -1 ASSIGNMENT_FROM_ID
, -1 BUSINESS_GROUP_FROM_ID
, -1 ORGANIZATION_FROM_ID
, -1 SUPERVISOR_FROM_ID
, -1 LOCATION_FROM_ID
, -1 JOB_FROM_ID
, -1 GRADE_FROM_ID
, -1 POSITION_FROM_ID
, 'N' PRIMARY_FROM_FLAG_CODE
, -1 ASSIGNMENT_STATUS_TYPE_FROM_ID
, 'NA_EDW' PER_SYSTEM_STATUS_FROM_CODE
, 'NA_EDW' PAY_SYSTEM_STATUS_FROM_CODE
, ASG.ASSIGNMENT_ID ASSIGNMENT_TO_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_TO_ID
, ASG.ORGANIZATION_ID ORGANIZATION_TO_ID
, ASG.SUPERVISOR_ID SUPERVISOR_TO_ID
, ASG.LOCATION_ID LOCATION_TO_ID
, ASG.JOB_ID JOB_TO_ID
, ASG.GRADE_ID GRADE_TO_ID
, ASG.POSITION_ID POSITION_TO_ID
, ASG.PRIMARY_FLAG PRIMARY_TO_FLAG_CODE
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_TO_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(ASG.LAST_UPDATE_DATE
, POS.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_PERIODS_OF_SERVICE POS
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND ASG.ASSIGNMENT_TYPE = 'E'
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASG.PRIMARY_FLAG = 'N'
AND ASG.EFFECTIVE_START_DATE > POS.DATE_START
AND ASG.EFFECTIVE_START_DATE = (SELECT MIN(ALL_ASG.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ALL_ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE ALL_ASG.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ALL_ASG.ASSIGNMENT_TYPE = 'E'
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ALL_ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ALL_ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID) UNION ALL SELECT 1 ROW_INDICATOR
, DECODE(PREV_ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, TERM_ASG.EFFECTIVE_START_DATE EVENT_DATE
, TERM_ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, TERM_ASG.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'NA_EDW' MOVE_EVENT_TYPE_CODE
, 'NA_EDW' GAIN_EVENT_TYPE_CODE
, 'LOSS_ASG' LOSS_EVENT_TYPE_CODE
, 'NA_EDW' ASSIGNMENT_START_CODE
, 'Y' ASSIGNMENT_END_CODE
, 'ORG_LOSS' ORG_CHANGE_TYPE_CODE
, 'SUP_LOSS' SUP_CHANGE_TYPE_CODE
, 'LOC_LOSS' LOC_CHANGE_TYPE_CODE
, 'JOB_LOSS' JOB_CHANGE_TYPE_CODE
, 'GRD_LOSS' GRD_CHANGE_TYPE_CODE
, 'POS_LOSS' POS_CHANGE_TYPE_CODE
, 'NA_EDW' PRIMARY_FLAG_CHANGE_CODE
, NVL(TERM_ASG.CHANGE_REASON
, 'NA_EDW') REASON_CODE
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, PREV_ASG.PERSON_ID PERSON_ID
, PREV_ASG.ASSIGNMENT_ID ASSIGNMENT_FROM_ID
, PREV_ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_FROM_ID
, PREV_ASG.ORGANIZATION_ID ORGANIZATION_FROM_ID
, PREV_ASG.SUPERVISOR_ID SUPERVISOR_FROM_ID
, PREV_ASG.LOCATION_ID LOCATION_FROM_ID
, PREV_ASG.JOB_ID JOB_FROM_ID
, PREV_ASG.GRADE_ID GRADE_FROM_ID
, PREV_ASG.POSITION_ID POSITION_FROM_ID
, PREV_ASG.PRIMARY_FLAG PRIMARY_FROM_FLAG_CODE
, PREV_AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_FROM_ID
, PREV_AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_FROM_CODE
, PREV_AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_FROM_CODE
, -1 ASSIGNMENT_TO_ID
, -1 BUSINESS_GROUP_TO_ID
, -1 ORGANIZATION_TO_ID
, -1 SUPERVISOR_TO_ID
, -1 LOCATION_TO_ID
, -1 JOB_TO_ID
, -1 GRADE_TO_ID
, -1 POSITION_TO_ID
, 'N' PRIMARY_TO_FLAG_CODE
, TERM_ASG.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, TERM_AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_TO_CODE
, TERM_AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(PREV_ASG.LAST_UPDATE_DATE
, TERM_ASG.LAST_UPDATE_DATE
, POS.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F PREV_ASG
, PER_ALL_ASSIGNMENTS_F TERM_ASG
, PER_PERIODS_OF_SERVICE POS
, PER_ASSIGNMENT_STATUS_TYPES PREV_AST
, PER_ASSIGNMENT_STATUS_TYPES TERM_AST
WHERE PREV_ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID
AND PREV_ASG.ASSIGNMENT_STATUS_TYPE_ID = PREV_AST.ASSIGNMENT_STATUS_TYPE_ID
AND TERM_ASG.ASSIGNMENT_STATUS_TYPE_ID = TERM_AST.ASSIGNMENT_STATUS_TYPE_ID
AND PREV_ASG.ASSIGNMENT_ID = TERM_ASG.ASSIGNMENT_ID
AND PREV_ASG.EFFECTIVE_END_DATE + 1 = TERM_ASG.EFFECTIVE_START_DATE
AND PREV_AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND TERM_AST.PER_SYSTEM_STATUS = 'TERM_ASSIGN'
AND TERM_ASG.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND (POS.ACTUAL_TERMINATION_DATE IS NULL OR TERM_ASG.EFFECTIVE_START_DATE < POS.ACTUAL_TERMINATION_DATE) UNION ALL SELECT 1 ROW_INDICATOR
, DECODE(ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, ASG.EFFECTIVE_END_DATE EVENT_DATE
, ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE EFFECTIVE_END_DATE /* IDS */
, 'NA_EDW' MOVE_EVENT_TYPE_CODE
, 'NA_EDW' GAIN_EVENT_TYPE_CODE
, 'LOSS_ASG' LOSS_EVENT_TYPE_CODE
, 'NA_EDW' ASSIGNMENT_START_CODE
, 'Y' ASSIGNMENT_END_CODE
, 'ORG_LOSS' ORG_CHANGE_TYPE_CODE
, 'SUP_LOSS' SUP_CHANGE_TYPE_CODE
, 'LOC_LOSS' LOC_CHANGE_TYPE_CODE
, 'JOB_LOSS' JOB_CHANGE_TYPE_CODE
, 'GRD_LOSS' GRD_CHANGE_TYPE_CODE
, 'POS_LOSS' POS_CHANGE_TYPE_CODE
, 'NA_EDW' PRIMARY_FLAG_CHANGE_CODE
, 'NA_EDW' REASON_CODE
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_FROM_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_FROM_ID
, ASG.ORGANIZATION_ID ORGANIZATION_FROM_ID
, ASG.SUPERVISOR_ID SUPERVISOR_FROM_ID
, ASG.LOCATION_ID LOCATION_FROM_ID
, ASG.JOB_ID JOB_FROM_ID
, ASG.GRADE_ID GRADE_FROM_ID
, ASG.POSITION_ID POSITION_FROM_ID
, ASG.PRIMARY_FLAG PRIMARY_FROM_FLAG_CODE
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_TO_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_TO_CODE
, -1 ASSIGNMENT_TO_ID
, -1 BUSINESS_GROUP_TO_ID
, -1 ORGANIZATION_TO_ID
, -1 SUPERVISOR_TO_ID
, -1 LOCATION_TO_ID
, -1 JOB_TO_ID
, -1 GRADE_TO_ID
, -1 POSITION_TO_ID
, 'N' PRIMARY_TO_FLAG_CODE
, -1 ASSIGNMENT_STATUS_TYPE_TO_ID
, 'NA_EDW' PER_SYSTEM_STATUS_TO_CODE
, 'NA_EDW' PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(ASG.LAST_UPDATE_DATE
, POS.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_PERIODS_OF_SERVICE POS
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASG.PRIMARY_FLAG = 'N'
AND ASG.EFFECTIVE_END_DATE <= TRUNC(SYSDATE)
AND (POS.ACTUAL_TERMINATION_DATE IS NULL OR ASG.EFFECTIVE_END_DATE < POS.ACTUAL_TERMINATION_DATE)
AND NOT EXISTS (SELECT NULL
FROM PER_ALL_ASSIGNMENTS_F NEXT_ASG
WHERE ASG.ASSIGNMENT_ID = NEXT_ASG.ASSIGNMENT_ID
AND ASG.EFFECTIVE_END_DATE + 1 = NEXT_ASG.EFFECTIVE_START_DATE
AND NEXT_ASG.ASSIGNMENT_TYPE = 'E') UNION ALL SELECT 1 ROW_INDICATOR
, DECODE(NEXT_ASG.PRIMARY_FLAG
, 'Y'
, 1
, 0) PRIMARY_ASG_INDICATOR
, NEXT_ASG.EFFECTIVE_START_DATE EVENT_DATE
, NEXT_ASG.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, NEXT_ASG.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, 'MOVE_PRIM_ASG' MOVE_EVENT_TYPE_CODE
, 'NA_EDW' GAIN_EVENT_TYPE_CODE
, 'NA_EDW' LOSS_EVENT_TYPE_CODE
, 'Y' ASSIGNMENT_START_CODE
, 'NA_EDW' ASSIGNMENT_END_CODE
, DECODE(PREV_ASG.ORGANIZATION_ID
, NEXT_ASG.ORGANIZATION_ID
, 'NA_EDW'
, 'ORG_CHANGE') ORG_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.SUPERVISOR_ID
, NEXT_ASG.SUPERVISOR_ID
, 'NA_EDW'
, 'SUP_CHANGE') SUP_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.LOCATION_ID
, NEXT_ASG.LOCATION_ID
, 'NA_EDW'
, 'LOC_CHANGE') LOC_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.JOB_ID
, NEXT_ASG.JOB_ID
, 'NA_EDW'
, 'JOB_CHANGE') JOB_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.GRADE_ID
, NEXT_ASG.GRADE_ID
, 'NA_EDW'
, 'GRD_CHANGE') GRD_CHANGE_TYPE_CODE
, DECODE(PREV_ASG.POSITION_ID
, NEXT_ASG.POSITION_ID
, 'NA_EDW'
, 'POS_CHANGE') POS_CHANGE_TYPE_CODE
, 'Y' PRIMARY_FLAG_CHANGE_CODE
, NVL(NEXT_ASG.CHANGE_REASON
, 'NA_EDW') REASON_CODE
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, NEXT_ASG.PERSON_ID PERSON_ID
, PREV_ASG.ASSIGNMENT_ID ASSIGNMENT_FROM_ID
, PREV_ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_FROM_ID
, PREV_ASG.ORGANIZATION_ID ORGANIZATION_FROM_ID
, PREV_ASG.SUPERVISOR_ID SUPERVISOR_FROM_ID
, PREV_ASG.JOB_ID JOB_FROM_ID
, PREV_ASG.LOCATION_ID LOCATION_FROM_ID
, PREV_ASG.GRADE_ID GRADE_FROM_ID
, PREV_ASG.POSITION_ID POSITION_FROM_ID
, NEXT_ASG.PRIMARY_FLAG PRIMARY_FROM_FLAG_CODE
, PREV_AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_FROM_ID
, PREV_AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_FROM_CODE
, PREV_AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_FROM_CODE
, NEXT_ASG.ASSIGNMENT_ID ASSIGNMENT_TO_ID
, NEXT_ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_TO_ID
, NEXT_ASG.ORGANIZATION_ID ORGANIZATION_TO_ID
, NEXT_ASG.SUPERVISOR_ID SUPERVISOR_TO_ID
, NEXT_ASG.LOCATION_ID LOCATION_TO_ID
, NEXT_ASG.JOB_ID JOB_TO_ID
, NEXT_ASG.GRADE_ID GRADE_TO_ID
, NEXT_ASG.POSITION_ID POSITION_TO_ID
, NEXT_ASG.PRIMARY_FLAG PRIMARY_TO_FLAG_CODE
, NEXT_AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, NEXT_AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_TO_CODE
, NEXT_AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_TO_CODE
, GREATEST(PREV_ASG.LAST_UPDATE_DATE
, NEXT_ASG.LAST_UPDATE_DATE) LAST_CHANGE_DATE
FROM PER_ALL_ASSIGNMENTS_F PREV_ASG
, PER_ALL_ASSIGNMENTS_F NEXT_ASG
, PER_ASSIGNMENT_STATUS_TYPES PREV_AST
, PER_ASSIGNMENT_STATUS_TYPES NEXT_AST
WHERE PREV_ASG.PERSON_ID = NEXT_ASG.PERSON_ID
AND PREV_ASG.EFFECTIVE_END_DATE + 1 = NEXT_ASG.EFFECTIVE_START_DATE
AND NEXT_ASG.ASSIGNMENT_STATUS_TYPE_ID = NEXT_AST.ASSIGNMENT_STATUS_TYPE_ID
AND PREV_ASG.ASSIGNMENT_STATUS_TYPE_ID = PREV_AST.ASSIGNMENT_STATUS_TYPE_ID
AND PREV_ASG.PERIOD_OF_SERVICE_ID = NEXT_ASG.PERIOD_OF_SERVICE_ID
AND PREV_ASG.ASSIGNMENT_ID <> NEXT_ASG.ASSIGNMENT_ID
AND PREV_ASG.PRIMARY_FLAG = 'Y'
AND NEXT_ASG.PRIMARY_FLAG = 'Y'
AND PREV_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND NOT EXISTS (SELECT NULL
FROM PER_ALL_ASSIGNMENTS_F FIRST
WHERE FIRST.ASSIGNMENT_ID = NEXT_ASG.ASSIGNMENT_ID
AND FIRST.ASSIGNMENT_TYPE = 'E'
AND FIRST.EFFECTIVE_START_DATE < NEXT_ASG.EFFECTIVE_START_DATE) WITH READ ONLY