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_LOSS_ENTRPRS_V

Source

View Text - Preformatted


SELECT 1 row_indicator , CASE WHEN (prev_asg.effective_end_date = pos.actual_termination_date) THEN 1 ELSE 0 END loss_sep_indicator , CASE WHEN ( ( prev_asg.effective_end_date < pos.actual_termination_date OR pos.actual_termination_date IS NULL) AND ( next_ast.per_system_status IN ('TERM_ASSIGN') OR next_asg.assignment_id IS NULL) ) THEN 1 ELSE 0 END loss_asg_indicator , CASE WHEN (next_ast.per_system_status = ('SUSP_ASSIGN') ) THEN 1 ELSE 0 END loss_lta_indicator , CASE WHEN prev_ast.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK') THEN 1 ELSE 0 END active_indicator , CASE WHEN prev_ast.per_system_status IN ('SUSP_ASSIGN','SUSP_CWK_ASG') THEN 1 ELSE 0 END lta_indicator , CASE WHEN prev_asg.assignment_type = 'E' THEN 1 ELSE 0 END emp_indicator , CASE WHEN prev_asg.assignment_type = 'C' THEN 1 ELSE 0 END cwk_indicator , CASE WHEN prev_asg.primary_flag = 'Y' THEN 1 ELSE 0 END prim_asg_indicator , prev_asg.effective_end_date measure_calc_date , prev_asg.effective_end_date + 1 event_date , NVL(scr.SEPARATION_CATEGORY_CODE,'NA_EDW') separation_category_code , NVL(pos.leaving_reason,'NA_EDW') separation_reason_code , NVL(next_asg.change_reason,'NA_EDW') change_reason_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 , 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 , NVL(prev_asg.primary_flag,'NA_EDW') 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 , NVL(next_asg.assignment_id,-1) assignment_to_id , NVL(next_asg.business_group_id,-1) business_group_to_id , NVL(next_asg.organization_id,-1) 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 , NVL(next_asg.primary_flag,'NA_EDW') primary_to_flag_code , NVL(next_asg.assignment_type, 'NA_EDW') assignment_type_to_code , NVL(next_ast.assignment_status_type_id, -1) assignment_status_type_to_id , NVL(next_ast.per_system_status,'NA_EDW') per_system_status_to_code , NVL(next_ast.pay_system_status,'NA_EDW') pay_system_status_to_code , months_between(pos.actual_termination_date, pos.date_start) low_months_loss_sep , GREATEST(prev_asg.last_update_date ,NVL(next_asg.last_update_date, prev_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' ) AND DECODE(hr_general.get_xbg_profile ,'Y',prev_asg.business_group_id ,hr_bis.get_sec_profile_bg_id) = prev_asg.business_group_id ) 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 , per_periods_of_service pos , hri_cs_sepcr_v scr WHERE prev_asg.assignment_type IN ('E','C') AND ( next_asg.assignment_type IS NULL OR next_asg.assignment_type IN ('E','C')) AND prev_ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN' ,'ACTIVE_CWK','SUSP_CWK_ASG') AND ( next_ast.per_system_status IS NULL OR next_ast.per_system_status IN ('TERM_ASSIGN', 'SUSP_ASSIGN', 'SUSP_CWK_ASSIGN')) AND ( ( prev_ast.per_system_status IN ('SUSP_ASSIGN','SUSP_CWK_ASG') AND ( next_ast.per_system_status IS NULL OR next_ast.per_system_status = ('TERM_ASSIGN') ) ) OR prev_ast.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK') ) AND prev_asg.effective_end_date < hr_general.end_of_time AND 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.period_of_service_id = pos.period_of_service_id AND pos.leaving_reason = scr.separation_reason_code (+) WITH READ ONLY

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, CASE WHEN (PREV_ASG.EFFECTIVE_END_DATE = POS.ACTUAL_TERMINATION_DATE) THEN 1 ELSE 0 END LOSS_SEP_INDICATOR
, CASE WHEN ( ( PREV_ASG.EFFECTIVE_END_DATE < POS.ACTUAL_TERMINATION_DATE OR POS.ACTUAL_TERMINATION_DATE IS NULL)
AND ( NEXT_AST.PER_SYSTEM_STATUS IN ('TERM_ASSIGN') OR NEXT_ASG.ASSIGNMENT_ID IS NULL) ) THEN 1 ELSE 0 END LOSS_ASG_INDICATOR
, CASE WHEN (NEXT_AST.PER_SYSTEM_STATUS = ('SUSP_ASSIGN') ) THEN 1 ELSE 0 END LOSS_LTA_INDICATOR
, CASE WHEN PREV_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') THEN 1 ELSE 0 END ACTIVE_INDICATOR
, CASE WHEN PREV_AST.PER_SYSTEM_STATUS IN ('SUSP_ASSIGN'
, 'SUSP_CWK_ASG') THEN 1 ELSE 0 END LTA_INDICATOR
, CASE WHEN PREV_ASG.ASSIGNMENT_TYPE = 'E' THEN 1 ELSE 0 END EMP_INDICATOR
, CASE WHEN PREV_ASG.ASSIGNMENT_TYPE = 'C' THEN 1 ELSE 0 END CWK_INDICATOR
, CASE WHEN PREV_ASG.PRIMARY_FLAG = 'Y' THEN 1 ELSE 0 END PRIM_ASG_INDICATOR
, PREV_ASG.EFFECTIVE_END_DATE MEASURE_CALC_DATE
, PREV_ASG.EFFECTIVE_END_DATE + 1 EVENT_DATE
, NVL(SCR.SEPARATION_CATEGORY_CODE
, 'NA_EDW') SEPARATION_CATEGORY_CODE
, NVL(POS.LEAVING_REASON
, 'NA_EDW') SEPARATION_REASON_CODE
, NVL(NEXT_ASG.CHANGE_REASON
, 'NA_EDW') CHANGE_REASON_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
, 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
, NVL(PREV_ASG.PRIMARY_FLAG
, 'NA_EDW') 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
, NVL(NEXT_ASG.ASSIGNMENT_ID
, -1) ASSIGNMENT_TO_ID
, NVL(NEXT_ASG.BUSINESS_GROUP_ID
, -1) BUSINESS_GROUP_TO_ID
, NVL(NEXT_ASG.ORGANIZATION_ID
, -1) 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
, NVL(NEXT_ASG.PRIMARY_FLAG
, 'NA_EDW') PRIMARY_TO_FLAG_CODE
, NVL(NEXT_ASG.ASSIGNMENT_TYPE
, 'NA_EDW') ASSIGNMENT_TYPE_TO_CODE
, NVL(NEXT_AST.ASSIGNMENT_STATUS_TYPE_ID
, -1) ASSIGNMENT_STATUS_TYPE_TO_ID
, NVL(NEXT_AST.PER_SYSTEM_STATUS
, 'NA_EDW') PER_SYSTEM_STATUS_TO_CODE
, NVL(NEXT_AST.PAY_SYSTEM_STATUS
, 'NA_EDW') PAY_SYSTEM_STATUS_TO_CODE
, MONTHS_BETWEEN(POS.ACTUAL_TERMINATION_DATE
, POS.DATE_START) LOW_MONTHS_LOSS_SEP
, GREATEST(PREV_ASG.LAST_UPDATE_DATE
, NVL(NEXT_ASG.LAST_UPDATE_DATE
, PREV_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' )
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, PREV_ASG.BUSINESS_GROUP_ID
, HR_BIS.GET_SEC_PROFILE_BG_ID) = PREV_ASG.BUSINESS_GROUP_ID ) 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
, PER_PERIODS_OF_SERVICE POS
, HRI_CS_SEPCR_V SCR
WHERE PREV_ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ( NEXT_ASG.ASSIGNMENT_TYPE IS NULL OR NEXT_ASG.ASSIGNMENT_TYPE IN ('E'
, 'C'))
AND PREV_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN'
, 'ACTIVE_CWK'
, 'SUSP_CWK_ASG')
AND ( NEXT_AST.PER_SYSTEM_STATUS IS NULL OR NEXT_AST.PER_SYSTEM_STATUS IN ('TERM_ASSIGN'
, 'SUSP_ASSIGN'
, 'SUSP_CWK_ASSIGN'))
AND ( ( PREV_AST.PER_SYSTEM_STATUS IN ('SUSP_ASSIGN'
, 'SUSP_CWK_ASG')
AND ( NEXT_AST.PER_SYSTEM_STATUS IS NULL OR NEXT_AST.PER_SYSTEM_STATUS = ('TERM_ASSIGN') ) ) OR PREV_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') )
AND PREV_ASG.EFFECTIVE_END_DATE < HR_GENERAL.END_OF_TIME
AND 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.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID
AND POS.LEAVING_REASON = SCR.SEPARATION_REASON_CODE (+) WITH READ ONLY