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_GAIN_ENTRPRS_V

Source

View Text - Preformatted


SELECT 1 row_indicator , CASE WHEN ( next_asg.assignment_type = 'E' AND next_ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND next_asg.effective_start_date = pos.date_start AND ( prev_asg.assignment_id IS NULL OR prev_ast.per_system_status = 'ACCEPTED') ) THEN 1 ELSE 0 END gain_hire_indicator , CASE WHEN ( next_asg.assignment_type = 'E' AND next_ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND next_asg.effective_start_date <> pos.date_start AND prev_asg.assignment_id IS NULL ) THEN 1 ELSE 0 END gain_asg_indicator , CASE WHEN ( prev_ast.per_system_status = ('SUSP_ASSIGN') AND next_ast.per_system_status = ('ACTIVE_ASSIGN') ) THEN 1 ELSE 0 END gain_lta_indicator , CASE WHEN ( next_asg.assignment_type = 'E' AND next_ast.per_system_status = 'SUSP_ASSIGN' AND next_asg.effective_start_date = pos.date_start AND prev_asg.assignment_id IS NULL ) THEN 1 ELSE 0 END gain_hire_lta_indicator , CASE WHEN ( next_asg.assignment_type = 'E' AND next_ast.per_system_status = 'SUSP_ASSIGN' AND next_asg.effective_start_date <> pos.date_start AND prev_asg.assignment_id IS NULL ) THEN 1 ELSE 0 END gain_asg_lta_indicator , CASE WHEN next_ast.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK') THEN 1 ELSE 0 END active_indicator , CASE WHEN next_ast.per_system_status IN ('SUSP_ASSIGN','SUSP_CWK_ASG') THEN 1 ELSE 0 END lta_indicator , CASE WHEN next_asg.assignment_type = 'E' THEN 1 ELSE 0 END emp_indicator , CASE WHEN next_asg.assignment_type = 'C' THEN 1 ELSE 0 END cwk_indicator , CASE WHEN next_asg.primary_flag = 'Y' THEN 1 ELSE 0 END prim_asg_indicator , next_asg.effective_start_date measure_calc_date , next_asg.effective_start_date event_date , NVL(pos.leaving_reason,'NA_EDW') hire_reason_code , NVL(next_asg.change_reason,'NA_EDW') change_reason_code , next_asg.person_id person_id , NVL(prev_asg.assignment_id,-1) assignment_from_id , NVL(prev_asg.business_group_id,-1) business_group_from_id , NVL(prev_asg.organization_id,-1) 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 , NVL(prev_asg.assignment_type,'NA_EDW') assignment_type_from_code , NVL(prev_ast.assignment_status_type_id,-1) assignment_status_type_from_id , NVL(prev_ast.per_system_status,'NA_EDW') per_system_status_from_code , NVL(prev_ast.pay_system_status,'NA_EDW') 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 , NVL(next_asg.vacancy_id,-1) vacancy_id , GREATEST(next_asg.last_update_date ,NVL(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' , next_asg.assignment_id , next_asg.person_id , next_asg.assignment_type ) = 'TRUE' ) THEN 'Y' ELSE 'N' END hr_security_flag_code , CASE WHEN next_asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,next_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 WHERE next_asg.assignment_type IN ('E','C') AND ( prev_asg.assignment_type IS NULL OR prev_asg.assignment_type IN ('A','E','C')) AND next_ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN' ,'ACTIVE_CWK','SUSP_CWK_ASG') AND ( prev_ast.per_system_status IS NULL OR prev_ast.per_system_status IN ('ACCEPTED','SUSP_ASSIGN' ,'SUSP_CWK_ASSIGN')) AND ( ( next_ast.per_system_status IN ('SUSP_ASSIGN','SUSP_CWK_ASG') AND ( (prev_ast.per_system_status IS NULL) OR (prev_ast.per_system_status = 'ACCEPTED') ) ) OR next_ast.per_system_status IN ('ACTIVE_ASSIGN','ACTIVE_CWK') ) AND next_asg.assignment_id = prev_asg.assignment_id (+) AND next_asg.effective_start_date - 1 = prev_asg.effective_end_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 next_asg.period_of_service_id = pos.period_of_service_id (+) WITH READ ONLY

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, CASE WHEN ( NEXT_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN')
AND NEXT_ASG.EFFECTIVE_START_DATE = POS.DATE_START
AND ( PREV_ASG.ASSIGNMENT_ID IS NULL OR PREV_AST.PER_SYSTEM_STATUS = 'ACCEPTED') ) THEN 1 ELSE 0 END GAIN_HIRE_INDICATOR
, CASE WHEN ( NEXT_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN')
AND NEXT_ASG.EFFECTIVE_START_DATE <> POS.DATE_START
AND PREV_ASG.ASSIGNMENT_ID IS NULL ) THEN 1 ELSE 0 END GAIN_ASG_INDICATOR
, CASE WHEN ( PREV_AST.PER_SYSTEM_STATUS = ('SUSP_ASSIGN')
AND NEXT_AST.PER_SYSTEM_STATUS = ('ACTIVE_ASSIGN') ) THEN 1 ELSE 0 END GAIN_LTA_INDICATOR
, CASE WHEN ( NEXT_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_AST.PER_SYSTEM_STATUS = 'SUSP_ASSIGN'
AND NEXT_ASG.EFFECTIVE_START_DATE = POS.DATE_START
AND PREV_ASG.ASSIGNMENT_ID IS NULL ) THEN 1 ELSE 0 END GAIN_HIRE_LTA_INDICATOR
, CASE WHEN ( NEXT_ASG.ASSIGNMENT_TYPE = 'E'
AND NEXT_AST.PER_SYSTEM_STATUS = 'SUSP_ASSIGN'
AND NEXT_ASG.EFFECTIVE_START_DATE <> POS.DATE_START
AND PREV_ASG.ASSIGNMENT_ID IS NULL ) THEN 1 ELSE 0 END GAIN_ASG_LTA_INDICATOR
, CASE WHEN NEXT_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') THEN 1 ELSE 0 END ACTIVE_INDICATOR
, CASE WHEN NEXT_AST.PER_SYSTEM_STATUS IN ('SUSP_ASSIGN'
, 'SUSP_CWK_ASG') THEN 1 ELSE 0 END LTA_INDICATOR
, CASE WHEN NEXT_ASG.ASSIGNMENT_TYPE = 'E' THEN 1 ELSE 0 END EMP_INDICATOR
, CASE WHEN NEXT_ASG.ASSIGNMENT_TYPE = 'C' THEN 1 ELSE 0 END CWK_INDICATOR
, CASE WHEN NEXT_ASG.PRIMARY_FLAG = 'Y' THEN 1 ELSE 0 END PRIM_ASG_INDICATOR
, NEXT_ASG.EFFECTIVE_START_DATE MEASURE_CALC_DATE
, NEXT_ASG.EFFECTIVE_START_DATE EVENT_DATE
, NVL(POS.LEAVING_REASON
, 'NA_EDW') HIRE_REASON_CODE
, NVL(NEXT_ASG.CHANGE_REASON
, 'NA_EDW') CHANGE_REASON_CODE
, NEXT_ASG.PERSON_ID PERSON_ID
, NVL(PREV_ASG.ASSIGNMENT_ID
, -1) ASSIGNMENT_FROM_ID
, NVL(PREV_ASG.BUSINESS_GROUP_ID
, -1) BUSINESS_GROUP_FROM_ID
, NVL(PREV_ASG.ORGANIZATION_ID
, -1) 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
, NVL(PREV_ASG.ASSIGNMENT_TYPE
, 'NA_EDW') ASSIGNMENT_TYPE_FROM_CODE
, NVL(PREV_AST.ASSIGNMENT_STATUS_TYPE_ID
, -1) ASSIGNMENT_STATUS_TYPE_FROM_ID
, NVL(PREV_AST.PER_SYSTEM_STATUS
, 'NA_EDW') PER_SYSTEM_STATUS_FROM_CODE
, NVL(PREV_AST.PAY_SYSTEM_STATUS
, 'NA_EDW') 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
, NVL(NEXT_ASG.VACANCY_ID
, -1) VACANCY_ID
, GREATEST(NEXT_ASG.LAST_UPDATE_DATE
, NVL(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'
, NEXT_ASG.ASSIGNMENT_ID
, NEXT_ASG.PERSON_ID
, NEXT_ASG.ASSIGNMENT_TYPE ) = 'TRUE' ) THEN 'Y' ELSE 'N' END HR_SECURITY_FLAG_CODE
, CASE WHEN NEXT_ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, NEXT_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
WHERE NEXT_ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND ( PREV_ASG.ASSIGNMENT_TYPE IS NULL OR PREV_ASG.ASSIGNMENT_TYPE IN ('A'
, 'E'
, 'C'))
AND NEXT_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN'
, 'ACTIVE_CWK'
, 'SUSP_CWK_ASG')
AND ( PREV_AST.PER_SYSTEM_STATUS IS NULL OR PREV_AST.PER_SYSTEM_STATUS IN ('ACCEPTED'
, 'SUSP_ASSIGN'
, 'SUSP_CWK_ASSIGN'))
AND ( ( NEXT_AST.PER_SYSTEM_STATUS IN ('SUSP_ASSIGN'
, 'SUSP_CWK_ASG')
AND ( (PREV_AST.PER_SYSTEM_STATUS IS NULL) OR (PREV_AST.PER_SYSTEM_STATUS = 'ACCEPTED') ) ) OR NEXT_AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'ACTIVE_CWK') )
AND NEXT_ASG.ASSIGNMENT_ID = PREV_ASG.ASSIGNMENT_ID (+)
AND NEXT_ASG.EFFECTIVE_START_DATE - 1 = PREV_ASG.EFFECTIVE_END_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 NEXT_ASG.PERIOD_OF_SERVICE_ID = POS.PERIOD_OF_SERVICE_ID (+) WITH READ ONLY