VIEW: APPS.HRI_MD_WMV_LOSS_ORG_V

Source

View Text - Preformatted

SELECT DECODE(wac.loss_event_type_code ,'LOSS_SEP',1 ,0) loss_sep_indicator , DECODE(wac.loss_event_type_code ,'LOSS_ASG',1 ,0) loss_asg_indicator , DECODE(wac.loss_event_type_code ,'LOSS_LTA',1 ,0) loss_lta_indicator , DECODE(wac.org_change_type_code ,'ORG_CHANGE',1 ,0) loss_org_indicator , wmv.primary_asg_indicator primary_asg_indicator , wmv.asg_indicator asg_indicator , wmv.fte fte , DECODE(wac.loss_event_type_code ,'LOSS_SEP',(1*wmv.fte) ,0) fte_separations , wmv.head head , DECODE(wac.loss_event_type_code ,'LOSS_SEP',(1*wmv.head) ,0) head_separations , wmv.hours hours , wmv.money money , wmv.pft pft , wmv.user1 user1 , wmv.user2 user2 , wmv.user3 user3 , wmv.user4 user4 , wmv.user5 user5 , wac.event_date event_date , wac.move_event_type_code move_event_type_code , wac.gain_event_type_code gain_event_type_code , wac.loss_event_type_code loss_event_type_code , wac.assignment_start_code assignment_start_code , wac.assignment_end_code assignment_end_code , wac.org_change_type_code org_change_type_code , wac.sup_change_type_code sup_change_type_code , wac.loc_change_type_code loc_change_type_code , wac.job_change_type_code job_change_type_code , wac.grade_change_type_code grade_change_type_code , wac.pos_change_type_code pos_change_type_code , NVL(wac.primary_flag_change_code, 'NA_EDW') primary_flag_change_code , wac.reason_code reason_code , wac.separation_category_code separation_category_code , wac.person_id person_id , wac.assignment_from_id assignment_from_id , wac.business_group_from_id business_group_from_id , wac.organization_from_id organization_from_id , NVL(wac.supervisor_person_from_id, -1) supervisor_person_from_id , NVL(wac.location_from_id,-1) location_from_id , NVL(wac.job_from_id,-1) job_from_id , NVL(wac.grade_from_id,-1) grade_from_id , NVL(wac.position_from_id ,-1) position_from_id , wac.primary_from_flag_code primary_from_flag_code , wac.assignment_status_type_from_id assignment_status_type_from_id , wac.per_system_status_from_code per_system_status_from_code , wac.pay_system_status_from_code pay_system_status_from_code , wac.assignment_to_id assignment_to_id , wac.business_group_to_id business_group_to_id , wac.organization_to_id organization_to_id , NVL(wac.supervisor_person_to_id, -1) supervisor_person_to_id , NVL(wac.location_to_id,-1) location_to_id , NVL(wac.job_to_id,-1) job_to_id , NVL(wac.grade_to_id,-1) grade_to_id , NVL(wac.position_to_id,-1) position_to_id , wac.primary_to_flag_code primary_to_flag_code , wac.assignment_status_type_to_id assignment_status_type_to_id , wac.per_system_status_to_code per_system_status_to_code , wac.pay_system_status_to_code pay_system_status_to_code /* Data currency */ , GREATEST(wmv.last_change_date ,wac.last_change_date) last_change_date FROM hri_mb_wmv wmv , hri_mb_wrk_activity_v wac WHERE (wac.loss_event_type_code <> 'NA_EDW' OR wac.org_change_type_code = 'ORG_CHANGE' ) AND wmv.assignment_id = wac.assignment_from_id AND wac.event_date BETWEEN wmv.effective_start_date AND wmv.effective_end_date AND wac.event_date BETWEEN add_months(TRUNC(sysdate),-1) AND TRUNC(sysdate) WITH READ ONLY

View Text - HTML Formatted

SELECT DECODE(WAC.LOSS_EVENT_TYPE_CODE
, 'LOSS_SEP'
, 1
, 0) LOSS_SEP_INDICATOR
, DECODE(WAC.LOSS_EVENT_TYPE_CODE
, 'LOSS_ASG'
, 1
, 0) LOSS_ASG_INDICATOR
, DECODE(WAC.LOSS_EVENT_TYPE_CODE
, 'LOSS_LTA'
, 1
, 0) LOSS_LTA_INDICATOR
, DECODE(WAC.ORG_CHANGE_TYPE_CODE
, 'ORG_CHANGE'
, 1
, 0) LOSS_ORG_INDICATOR
, WMV.PRIMARY_ASG_INDICATOR PRIMARY_ASG_INDICATOR
, WMV.ASG_INDICATOR ASG_INDICATOR
, WMV.FTE FTE
, DECODE(WAC.LOSS_EVENT_TYPE_CODE
, 'LOSS_SEP'
, (1*WMV.FTE)
, 0) FTE_SEPARATIONS
, WMV.HEAD HEAD
, DECODE(WAC.LOSS_EVENT_TYPE_CODE
, 'LOSS_SEP'
, (1*WMV.HEAD)
, 0) HEAD_SEPARATIONS
, WMV.HOURS HOURS
, WMV.MONEY MONEY
, WMV.PFT PFT
, WMV.USER1 USER1
, WMV.USER2 USER2
, WMV.USER3 USER3
, WMV.USER4 USER4
, WMV.USER5 USER5
, WAC.EVENT_DATE EVENT_DATE
, WAC.MOVE_EVENT_TYPE_CODE MOVE_EVENT_TYPE_CODE
, WAC.GAIN_EVENT_TYPE_CODE GAIN_EVENT_TYPE_CODE
, WAC.LOSS_EVENT_TYPE_CODE LOSS_EVENT_TYPE_CODE
, WAC.ASSIGNMENT_START_CODE ASSIGNMENT_START_CODE
, WAC.ASSIGNMENT_END_CODE ASSIGNMENT_END_CODE
, WAC.ORG_CHANGE_TYPE_CODE ORG_CHANGE_TYPE_CODE
, WAC.SUP_CHANGE_TYPE_CODE SUP_CHANGE_TYPE_CODE
, WAC.LOC_CHANGE_TYPE_CODE LOC_CHANGE_TYPE_CODE
, WAC.JOB_CHANGE_TYPE_CODE JOB_CHANGE_TYPE_CODE
, WAC.GRADE_CHANGE_TYPE_CODE GRADE_CHANGE_TYPE_CODE
, WAC.POS_CHANGE_TYPE_CODE POS_CHANGE_TYPE_CODE
, NVL(WAC.PRIMARY_FLAG_CHANGE_CODE
, 'NA_EDW') PRIMARY_FLAG_CHANGE_CODE
, WAC.REASON_CODE REASON_CODE
, WAC.SEPARATION_CATEGORY_CODE SEPARATION_CATEGORY_CODE
, WAC.PERSON_ID PERSON_ID
, WAC.ASSIGNMENT_FROM_ID ASSIGNMENT_FROM_ID
, WAC.BUSINESS_GROUP_FROM_ID BUSINESS_GROUP_FROM_ID
, WAC.ORGANIZATION_FROM_ID ORGANIZATION_FROM_ID
, NVL(WAC.SUPERVISOR_PERSON_FROM_ID
, -1) SUPERVISOR_PERSON_FROM_ID
, NVL(WAC.LOCATION_FROM_ID
, -1) LOCATION_FROM_ID
, NVL(WAC.JOB_FROM_ID
, -1) JOB_FROM_ID
, NVL(WAC.GRADE_FROM_ID
, -1) GRADE_FROM_ID
, NVL(WAC.POSITION_FROM_ID
, -1) POSITION_FROM_ID
, WAC.PRIMARY_FROM_FLAG_CODE PRIMARY_FROM_FLAG_CODE
, WAC.ASSIGNMENT_STATUS_TYPE_FROM_ID ASSIGNMENT_STATUS_TYPE_FROM_ID
, WAC.PER_SYSTEM_STATUS_FROM_CODE PER_SYSTEM_STATUS_FROM_CODE
, WAC.PAY_SYSTEM_STATUS_FROM_CODE PAY_SYSTEM_STATUS_FROM_CODE
, WAC.ASSIGNMENT_TO_ID ASSIGNMENT_TO_ID
, WAC.BUSINESS_GROUP_TO_ID BUSINESS_GROUP_TO_ID
, WAC.ORGANIZATION_TO_ID ORGANIZATION_TO_ID
, NVL(WAC.SUPERVISOR_PERSON_TO_ID
, -1) SUPERVISOR_PERSON_TO_ID
, NVL(WAC.LOCATION_TO_ID
, -1) LOCATION_TO_ID
, NVL(WAC.JOB_TO_ID
, -1) JOB_TO_ID
, NVL(WAC.GRADE_TO_ID
, -1) GRADE_TO_ID
, NVL(WAC.POSITION_TO_ID
, -1) POSITION_TO_ID
, WAC.PRIMARY_TO_FLAG_CODE PRIMARY_TO_FLAG_CODE
, WAC.ASSIGNMENT_STATUS_TYPE_TO_ID ASSIGNMENT_STATUS_TYPE_TO_ID
, WAC.PER_SYSTEM_STATUS_TO_CODE PER_SYSTEM_STATUS_TO_CODE
, WAC.PAY_SYSTEM_STATUS_TO_CODE PAY_SYSTEM_STATUS_TO_CODE /* DATA CURRENCY */
, GREATEST(WMV.LAST_CHANGE_DATE
, WAC.LAST_CHANGE_DATE) LAST_CHANGE_DATE
FROM HRI_MB_WMV WMV
, HRI_MB_WRK_ACTIVITY_V WAC
WHERE (WAC.LOSS_EVENT_TYPE_CODE <> 'NA_EDW' OR WAC.ORG_CHANGE_TYPE_CODE = 'ORG_CHANGE' )
AND WMV.ASSIGNMENT_ID = WAC.ASSIGNMENT_FROM_ID
AND WAC.EVENT_DATE BETWEEN WMV.EFFECTIVE_START_DATE
AND WMV.EFFECTIVE_END_DATE
AND WAC.EVENT_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE)
, -1)
AND TRUNC(SYSDATE) WITH READ ONLY