VIEW: APPS.HRI_TO_WMV_SEP_V

Source

View Text - Preformatted

SELECT wac.row_indicator row_indicator , wac.loss_sep_indicator loss_sep_indicator , wac.loss_asg_indicator loss_asg_indicator , wac.loss_lta_indicator loss_lta_indicator , NVL(HRI_OLTP_VIEW_WRKFRC.CALC_ABV( wac.assignment_from_id , wac.business_group_from_id , wmt.id , wac.measure_calc_date , wac.primary_from_flag_code ), 0) wmt_metric , wac.active_indicator active_indicator , wac.lta_indicator lta_indicator , wac.emp_indicator emp_indicator , wac.prim_asg_indicator prim_asg_indicator , wac.measure_calc_date measure_calc_date , wac.event_date event_date , wac.separation_category_code separation_category_code , wac.separation_reason_code separation_reason_code , wac.change_reason_code change_reason_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 , wac.supervisor_person_from_id supervisor_person_from_id , wac.location_from_id location_from_id , geof.geo_area_id geo_area_from_code , geof.geo_country_id geo_country_from_code , geof.geo_region_id geo_region_from_code , wac.job_from_id job_from_id , wac.grade_from_id grade_from_id , wac.position_from_id position_from_id , wac.primary_from_flag_code primary_from_flag_code , wac.assignment_type_from_code assignment_type_from_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 , wmt.id wmt_code , wac.low_months_loss_sep low_months_loss_sep , wac.last_change_date last_change_date , wac.hr_security_flag_code hr_security_flag_code , wac.bg_security_flag_code bg_security_flag_code /* fii date foreign keys for event date */ , TO_NUMBER( TO_CHAR( wac.event_date, 'YYYY' ) ) event_fii_time_year_id , DECODE( TRUNC( ( TO_CHAR( wac.event_date, 'MM' ) -1 ) / 6 ) , 0, TO_CHAR( wac.event_date, 'YYYY') || '1' , 1, TO_CHAR( wac.event_date, 'YYYY') || '2' ) event_fii_time_semiyear_id , TO_NUMBER( TO_CHAR( wac.event_date, 'YYYYQ' ) ) event_fii_time_qtr_id , TO_CHAR( TO_CHAR( wac.event_date, 'YYYY' )|| TRUNC( ( TO_NUMBER( TO_CHAR( wac.event_date, 'MM' ) ) + 1 ) / 2 ) ) event_fii_time_bimonth_id , TO_NUMBER( TO_CHAR( wac.event_date, 'YYYYQMM' ) ) event_fii_time_month_id , wac.event_date event_fii_time_day_id FROM hri_mb_wac_loss_entrprs_v wac , hri_cs_geo_lochr_ct geof , hri_cl_wmt_x_v wmt WHERE wac.location_from_id = geof.location_id AND wac.loss_sep_indicator = 1 AND wac.emp_indicator = 1 AND wac.hr_security_flag_code = 'Y' WITH READ ONLY

View Text - HTML Formatted

SELECT WAC.ROW_INDICATOR ROW_INDICATOR
, WAC.LOSS_SEP_INDICATOR LOSS_SEP_INDICATOR
, WAC.LOSS_ASG_INDICATOR LOSS_ASG_INDICATOR
, WAC.LOSS_LTA_INDICATOR LOSS_LTA_INDICATOR
, NVL(HRI_OLTP_VIEW_WRKFRC.CALC_ABV( WAC.ASSIGNMENT_FROM_ID
, WAC.BUSINESS_GROUP_FROM_ID
, WMT.ID
, WAC.MEASURE_CALC_DATE
, WAC.PRIMARY_FROM_FLAG_CODE )
, 0) WMT_METRIC
, WAC.ACTIVE_INDICATOR ACTIVE_INDICATOR
, WAC.LTA_INDICATOR LTA_INDICATOR
, WAC.EMP_INDICATOR EMP_INDICATOR
, WAC.PRIM_ASG_INDICATOR PRIM_ASG_INDICATOR
, WAC.MEASURE_CALC_DATE MEASURE_CALC_DATE
, WAC.EVENT_DATE EVENT_DATE
, WAC.SEPARATION_CATEGORY_CODE SEPARATION_CATEGORY_CODE
, WAC.SEPARATION_REASON_CODE SEPARATION_REASON_CODE
, WAC.CHANGE_REASON_CODE CHANGE_REASON_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
, WAC.SUPERVISOR_PERSON_FROM_ID SUPERVISOR_PERSON_FROM_ID
, WAC.LOCATION_FROM_ID LOCATION_FROM_ID
, GEOF.GEO_AREA_ID GEO_AREA_FROM_CODE
, GEOF.GEO_COUNTRY_ID GEO_COUNTRY_FROM_CODE
, GEOF.GEO_REGION_ID GEO_REGION_FROM_CODE
, WAC.JOB_FROM_ID JOB_FROM_ID
, WAC.GRADE_FROM_ID GRADE_FROM_ID
, WAC.POSITION_FROM_ID POSITION_FROM_ID
, WAC.PRIMARY_FROM_FLAG_CODE PRIMARY_FROM_FLAG_CODE
, WAC.ASSIGNMENT_TYPE_FROM_CODE ASSIGNMENT_TYPE_FROM_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
, WMT.ID WMT_CODE
, WAC.LOW_MONTHS_LOSS_SEP LOW_MONTHS_LOSS_SEP
, WAC.LAST_CHANGE_DATE LAST_CHANGE_DATE
, WAC.HR_SECURITY_FLAG_CODE HR_SECURITY_FLAG_CODE
, WAC.BG_SECURITY_FLAG_CODE BG_SECURITY_FLAG_CODE /* FII DATE FOREIGN KEYS FOR EVENT DATE */
, TO_NUMBER( TO_CHAR( WAC.EVENT_DATE
, 'YYYY' ) ) EVENT_FII_TIME_YEAR_ID
, DECODE( TRUNC( ( TO_CHAR( WAC.EVENT_DATE
, 'MM' ) -1 ) / 6 )
, 0
, TO_CHAR( WAC.EVENT_DATE
, 'YYYY') || '1'
, 1
, TO_CHAR( WAC.EVENT_DATE
, 'YYYY') || '2' ) EVENT_FII_TIME_SEMIYEAR_ID
, TO_NUMBER( TO_CHAR( WAC.EVENT_DATE
, 'YYYYQ' ) ) EVENT_FII_TIME_QTR_ID
, TO_CHAR( TO_CHAR( WAC.EVENT_DATE
, 'YYYY' )|| TRUNC( ( TO_NUMBER( TO_CHAR( WAC.EVENT_DATE
, 'MM' ) ) + 1 ) / 2 ) ) EVENT_FII_TIME_BIMONTH_ID
, TO_NUMBER( TO_CHAR( WAC.EVENT_DATE
, 'YYYYQMM' ) ) EVENT_FII_TIME_MONTH_ID
, WAC.EVENT_DATE EVENT_FII_TIME_DAY_ID
FROM HRI_MB_WAC_LOSS_ENTRPRS_V WAC
, HRI_CS_GEO_LOCHR_CT GEOF
, HRI_CL_WMT_X_V WMT
WHERE WAC.LOCATION_FROM_ID = GEOF.LOCATION_ID
AND WAC.LOSS_SEP_INDICATOR = 1
AND WAC.EMP_INDICATOR = 1
AND WAC.HR_SECURITY_FLAG_CODE = 'Y' WITH READ ONLY