eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HRI_MB_WAC_CHANGE_ASG_V

Source

View Text - Preformatted


SELECT 1 , CASE WHEN prev_asg.organization_id <> next_asg.organization_id THEN 1 ELSE 0 END change_org_indicator , CASE WHEN NVL(prev_asg.supervisor_id,-1) <> NVL(next_asg.supervisor_id,-1) THEN 1 ELSE 0 END change_sup_indicator , CASE WHEN NVL(prev_asg.location_id,-1) <> NVL(next_asg.location_id,-1) THEN 1 ELSE 0 END change_loc_indicator , CASE WHEN NVL(prev_asg.job_id,-1) <> NVL(next_asg.job_id,-1) THEN 1 ELSE 0 END change_job_indicator , CASE WHEN NVL(prev_asg.grade_id,-1) <> NVL(next_asg.grade_id,-1) THEN 1 ELSE 0 END change_grd_indicator , CASE WHEN NVL(prev_asg.position_id,-1) <> NVL(next_asg.position_id,-1) THEN 1 ELSE 0 END change_pos_indicator , CASE WHEN prev_asg.primary_flag <> next_asg.primary_flag THEN 1 ELSE 0 END change_prim_asg_indicator , CASE WHEN prev_ast.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK') THEN 1 ELSE 0 END active_from_indicator , CASE WHEN prev_ast.per_system_status IN ('SUSP_ASSIGN','SUSP_CWK_ASG') THEN 1 ELSE 0 END lta_from_indicator , CASE WHEN prev_asg.assignment_type = 'E' THEN 1 ELSE 0 END emp_from_indicator , CASE WHEN prev_asg.assignment_type = 'C' THEN 1 ELSE 0 END cwk_from_indicator , CASE WHEN prev_asg.primary_flag = 'Y' THEN 1 ELSE 0 END prim_asg_from_indicator , CASE WHEN next_ast.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK') THEN 1 ELSE 0 END active_to_indicator , CASE WHEN next_ast.per_system_status IN ('SUSP_ASSIGN','SUSP_CWK_ASG') THEN 1 ELSE 0 END lta_to_indicator , CASE WHEN next_asg.assignment_type = 'E' THEN 1 ELSE 0 END emp_to_indicator , CASE WHEN next_asg.assignment_type = 'C' THEN 1 ELSE 0 END cwk_to_indicator , CASE WHEN next_asg.primary_flag = 'Y' THEN 1 ELSE 0 END prim_asg_to_indicator , next_asg.effective_start_date measure_calc_date , next_asg.effective_start_date event_date , NVL(next_asg.change_reason,'NA_EDW') change_reason_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 , NVL(prev_asg.supervisor_id,-1) supervisor_person_from_id , NVL(prev_asg.location_id,-1) location_from_id , NVL(prev_asg.job_id,-1) job_from_id , NVL(prev_asg.grade_id,-1) grade_from_id , NVL(prev_asg.position_id,-1) position_from_id , prev_asg.primary_flag primary_from_flag_code , prev_asg.assignment_type assignment_type_from_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 , NVL(next_asg.supervisor_id,-1) supervisor_person_to_id , NVL(next_asg.location_id,-1) location_to_id , NVL(next_asg.job_id,-1) job_to_id , NVL(next_asg.grade_id,-1) grade_to_id , NVL(next_asg.position_id,-1) position_to_id , next_asg.primary_flag primary_to_flag_code , next_asg.assignment_type assignment_type_to_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 , CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD('PER_ALL_ASSIGNMENTS_F' , prev_asg.assignment_id , prev_asg.person_id , prev_asg.assignment_type ) = 'TRUE' ) THEN 'Y' ELSE 'N' END hr_security_flag_code , CASE WHEN prev_asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,prev_asg.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code 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 IN ('E','C') AND next_asg.assignment_type IN ('E','C') AND prev_ast.per_system_status <> 'TERM_ASSIGN' AND next_ast.per_system_status <> 'TERM_ASSIGN' WITH READ ONLY

View Text - HTML Formatted

SELECT 1
, CASE WHEN PREV_ASG.ORGANIZATION_ID <> NEXT_ASG.ORGANIZATION_ID THEN 1 ELSE 0 END CHANGE_ORG_INDICATOR
, CASE WHEN NVL(PREV_ASG.SUPERVISOR_ID
, -1) <> NVL(NEXT_ASG.SUPERVISOR_ID
, -1) THEN 1 ELSE 0 END CHANGE_SUP_INDICATOR
, CASE WHEN NVL(PREV_ASG.LOCATION_ID
, -1) <> NVL(NEXT_ASG.LOCATION_ID
, -1) THEN 1 ELSE 0 END CHANGE_LOC_INDICATOR
, CASE WHEN NVL(PREV_ASG.JOB_ID
, -1) <> NVL(NEXT_ASG.JOB_ID
, -1) THEN 1 ELSE 0 END CHANGE_JOB_INDICATOR
, CASE WHEN NVL(PREV_ASG.GRADE_ID
, -1) <> NVL(NEXT_ASG.GRADE_ID
, -1) THEN 1 ELSE 0 END CHANGE_GRD_INDICATOR
, CASE WHEN NVL(PREV_ASG.POSITION_ID
, -1) <> NVL(NEXT_ASG.POSITION_ID
, -1) THEN 1 ELSE 0 END CHANGE_POS_INDICATOR
, CASE WHEN PREV_ASG.PRIMARY_FLAG <> NEXT_ASG.PRIMARY_FLAG THEN 1 ELSE 0 END CHANGE_PRIM_ASG_INDICATOR
, CASE WHEN PREV_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') THEN 1 ELSE 0 END ACTIVE_FROM_INDICATOR
, CASE WHEN PREV_AST.PER_SYSTEM_STATUS IN ('SUSP_ASSIGN'
, 'SUSP_CWK_ASG') THEN 1 ELSE 0 END LTA_FROM_INDICATOR
, CASE WHEN PREV_ASG.ASSIGNMENT_TYPE = 'E' THEN 1 ELSE 0 END EMP_FROM_INDICATOR
, CASE WHEN PREV_ASG.ASSIGNMENT_TYPE = 'C' THEN 1 ELSE 0 END CWK_FROM_INDICATOR
, CASE WHEN PREV_ASG.PRIMARY_FLAG = 'Y' THEN 1 ELSE 0 END PRIM_ASG_FROM_INDICATOR
, CASE WHEN NEXT_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') THEN 1 ELSE 0 END ACTIVE_TO_INDICATOR
, CASE WHEN NEXT_AST.PER_SYSTEM_STATUS IN ('SUSP_ASSIGN'
, 'SUSP_CWK_ASG') THEN 1 ELSE 0 END LTA_TO_INDICATOR
, CASE WHEN NEXT_ASG.ASSIGNMENT_TYPE = 'E' THEN 1 ELSE 0 END EMP_TO_INDICATOR
, CASE WHEN NEXT_ASG.ASSIGNMENT_TYPE = 'C' THEN 1 ELSE 0 END CWK_TO_INDICATOR
, CASE WHEN NEXT_ASG.PRIMARY_FLAG = 'Y' THEN 1 ELSE 0 END PRIM_ASG_TO_INDICATOR
, NEXT_ASG.EFFECTIVE_START_DATE MEASURE_CALC_DATE
, NEXT_ASG.EFFECTIVE_START_DATE EVENT_DATE
, NVL(NEXT_ASG.CHANGE_REASON
, 'NA_EDW') CHANGE_REASON_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
, NVL(PREV_ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_FROM_ID
, NVL(PREV_ASG.LOCATION_ID
, -1) LOCATION_FROM_ID
, NVL(PREV_ASG.JOB_ID
, -1) JOB_FROM_ID
, NVL(PREV_ASG.GRADE_ID
, -1) GRADE_FROM_ID
, NVL(PREV_ASG.POSITION_ID
, -1) POSITION_FROM_ID
, PREV_ASG.PRIMARY_FLAG PRIMARY_FROM_FLAG_CODE
, PREV_ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_FROM_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
, NVL(NEXT_ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_TO_ID
, NVL(NEXT_ASG.LOCATION_ID
, -1) LOCATION_TO_ID
, NVL(NEXT_ASG.JOB_ID
, -1) JOB_TO_ID
, NVL(NEXT_ASG.GRADE_ID
, -1) GRADE_TO_ID
, NVL(NEXT_ASG.POSITION_ID
, -1) POSITION_TO_ID
, NEXT_ASG.PRIMARY_FLAG PRIMARY_TO_FLAG_CODE
, NEXT_ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_TO_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
, CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD('PER_ALL_ASSIGNMENTS_F'
, PREV_ASG.ASSIGNMENT_ID
, PREV_ASG.PERSON_ID
, PREV_ASG.ASSIGNMENT_TYPE ) = 'TRUE' ) THEN 'Y' ELSE 'N' END HR_SECURITY_FLAG_CODE
, CASE WHEN PREV_ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PREV_ASG.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
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 IN ('E'
, 'C')
AND NEXT_ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND PREV_AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND NEXT_AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN' WITH READ ONLY