VIEW: APPS.HRI_MD_WAC_LOSS_V

Source

View Text - Preformatted

SELECT /*------------------------------------*/ /* Measures */ los.row_indicator /* Loss Type indicators - mostly additive across the columns */ , los.loss_sep_indicator , los.loss_asg_indicator , los.loss_lta_indicator , 0 loss_org_indicator , 0 loss_sup_indicator , 0 loss_pos_indicator , 0 loss_prim_asg_indicator /* dimension indicators */ , los.active_indicator active_from_indicator , 0 active_to_indicator , los.lta_indicator , los.emp_indicator , los.cwk_indicator , los.prim_asg_indicator /* Dates */ , los.measure_calc_date , los.event_date /* Ids */ /* Workforce Activity Dimension and Levels*/ , los.separation_category_code , los.separation_reason_code , los.change_reason_code /* Workstructure Dimensions */ , los.person_id /* From and To Ids */ , los.assignment_from_id , los.business_group_from_id , los.organization_from_id , los.supervisor_person_from_id , los.location_from_id , los.job_from_id , los.grade_from_id , los.position_from_id , los.primary_from_flag_code , los.assignment_type_from_code , los.assignment_status_type_from_id , los.per_system_status_from_code , los.pay_system_status_from_code , los.assignment_to_id , los.business_group_to_id , los.organization_to_id , los.supervisor_person_to_id , los.location_to_id , los.job_to_id , los.grade_to_id , los.position_to_id , los.primary_to_flag_code , los.assignment_type_to_code , los.assignment_status_type_to_id , los.per_system_status_to_code , los.pay_system_status_to_code /* Length of work FK */ , los.low_months_loss_sep /* Data currency */ , los.last_change_date /* Security */ , los.hr_security_flag_code , los.bg_security_flag_code FROM /*------------------------------------*/ hri_mb_wac_loss_entrprs_v los UNION ALL SELECT /*------------------------------------*/ /* Measures */ row_indicator /* Loss, Gains and Movements Measures*/ , 0 loss_sep_indicator , 0 loss_asg_indicator , 0 loss_lta_indicator , chg.change_org_indicator , chg.change_sup_indicator , chg.change_pos_indicator , chg.change_prim_asg_indicator /* dimension indicators */ , chg.active_from_indicator active_from_indicator , chg.active_to_indicator active_to_indicator , chg.lta_from_indicator lta_indicator , chg.emp_from_indicator emp_indicator , chg.cwk_from_indicator cwk_indicator , chg.prim_asg_from_indicator prim_asg_indicator /* Dates */ , chg.measure_calc_date , chg.event_date /* Ids */ /* Workforce Activity Dimension and Levels*/ , 'NA_EDW' separation_category_code , 'NA_EDW' separation_reason_code , chg.change_reason_code /* Workstructure Dimensions */ , chg.person_id /* From and To Ids */ , chg.assignment_from_id , chg.business_group_from_id , chg.organization_from_id , chg.supervisor_person_from_id , chg.location_from_id , chg.job_from_id , chg.grade_from_id , chg.position_from_id , chg.primary_from_flag_code , chg.assignment_type_from_code , chg.assignment_status_type_from_id , chg.per_system_status_from_code , chg.pay_system_status_from_code , chg.assignment_to_id , chg.business_group_to_id , chg.organization_to_id , chg.supervisor_person_to_id , chg.location_to_id , chg.job_to_id , chg.grade_to_id , chg.position_to_id , chg.primary_to_flag_code , chg.assignment_type_to_code , chg.assignment_status_type_to_id , chg.per_system_status_to_code , chg.pay_system_status_to_code /* Length of work FK */ , to_number(null) low_months_loss_sep /* Data Currency */ , last_change_date /* Security */ , chg.hr_security_flag_code , chg.bg_security_flag_code FROM /*------------------------------------*/ hri_mb_wac_change_asg_v chg WITH READ ONLY

View Text - HTML Formatted

SELECT /*------------------------------------*/ /* MEASURES */ LOS.ROW_INDICATOR /* LOSS TYPE INDICATORS - MOSTLY ADDITIVE ACROSS THE COLUMNS */
, LOS.LOSS_SEP_INDICATOR
, LOS.LOSS_ASG_INDICATOR
, LOS.LOSS_LTA_INDICATOR
, 0 LOSS_ORG_INDICATOR
, 0 LOSS_SUP_INDICATOR
, 0 LOSS_POS_INDICATOR
, 0 LOSS_PRIM_ASG_INDICATOR /* DIMENSION INDICATORS */
, LOS.ACTIVE_INDICATOR ACTIVE_FROM_INDICATOR
, 0 ACTIVE_TO_INDICATOR
, LOS.LTA_INDICATOR
, LOS.EMP_INDICATOR
, LOS.CWK_INDICATOR
, LOS.PRIM_ASG_INDICATOR /* DATES */
, LOS.MEASURE_CALC_DATE
, LOS.EVENT_DATE /* IDS */ /* WORKFORCE ACTIVITY DIMENSION
AND LEVELS*/
, LOS.SEPARATION_CATEGORY_CODE
, LOS.SEPARATION_REASON_CODE
, LOS.CHANGE_REASON_CODE /* WORKSTRUCTURE DIMENSIONS */
, LOS.PERSON_ID /*
FROM AND TO IDS */
, LOS.ASSIGNMENT_FROM_ID
, LOS.BUSINESS_GROUP_FROM_ID
, LOS.ORGANIZATION_FROM_ID
, LOS.SUPERVISOR_PERSON_FROM_ID
, LOS.LOCATION_FROM_ID
, LOS.JOB_FROM_ID
, LOS.GRADE_FROM_ID
, LOS.POSITION_FROM_ID
, LOS.PRIMARY_FROM_FLAG_CODE
, LOS.ASSIGNMENT_TYPE_FROM_CODE
, LOS.ASSIGNMENT_STATUS_TYPE_FROM_ID
, LOS.PER_SYSTEM_STATUS_FROM_CODE
, LOS.PAY_SYSTEM_STATUS_FROM_CODE
, LOS.ASSIGNMENT_TO_ID
, LOS.BUSINESS_GROUP_TO_ID
, LOS.ORGANIZATION_TO_ID
, LOS.SUPERVISOR_PERSON_TO_ID
, LOS.LOCATION_TO_ID
, LOS.JOB_TO_ID
, LOS.GRADE_TO_ID
, LOS.POSITION_TO_ID
, LOS.PRIMARY_TO_FLAG_CODE
, LOS.ASSIGNMENT_TYPE_TO_CODE
, LOS.ASSIGNMENT_STATUS_TYPE_TO_ID
, LOS.PER_SYSTEM_STATUS_TO_CODE
, LOS.PAY_SYSTEM_STATUS_TO_CODE /* LENGTH OF WORK FK */
, LOS.LOW_MONTHS_LOSS_SEP /* DATA CURRENCY */
, LOS.LAST_CHANGE_DATE /* SECURITY */
, LOS.HR_SECURITY_FLAG_CODE
, LOS.BG_SECURITY_FLAG_CODE
FROM /*------------------------------------*/ HRI_MB_WAC_LOSS_ENTRPRS_V LOS UNION ALL SELECT /*------------------------------------*/ /* MEASURES */ ROW_INDICATOR /* LOSS
, GAINS
AND MOVEMENTS MEASURES*/
, 0 LOSS_SEP_INDICATOR
, 0 LOSS_ASG_INDICATOR
, 0 LOSS_LTA_INDICATOR
, CHG.CHANGE_ORG_INDICATOR
, CHG.CHANGE_SUP_INDICATOR
, CHG.CHANGE_POS_INDICATOR
, CHG.CHANGE_PRIM_ASG_INDICATOR /* DIMENSION INDICATORS */
, CHG.ACTIVE_FROM_INDICATOR ACTIVE_FROM_INDICATOR
, CHG.ACTIVE_TO_INDICATOR ACTIVE_TO_INDICATOR
, CHG.LTA_FROM_INDICATOR LTA_INDICATOR
, CHG.EMP_FROM_INDICATOR EMP_INDICATOR
, CHG.CWK_FROM_INDICATOR CWK_INDICATOR
, CHG.PRIM_ASG_FROM_INDICATOR PRIM_ASG_INDICATOR /* DATES */
, CHG.MEASURE_CALC_DATE
, CHG.EVENT_DATE /* IDS */ /* WORKFORCE ACTIVITY DIMENSION
AND LEVELS*/
, 'NA_EDW' SEPARATION_CATEGORY_CODE
, 'NA_EDW' SEPARATION_REASON_CODE
, CHG.CHANGE_REASON_CODE /* WORKSTRUCTURE DIMENSIONS */
, CHG.PERSON_ID /*
FROM AND TO IDS */
, CHG.ASSIGNMENT_FROM_ID
, CHG.BUSINESS_GROUP_FROM_ID
, CHG.ORGANIZATION_FROM_ID
, CHG.SUPERVISOR_PERSON_FROM_ID
, CHG.LOCATION_FROM_ID
, CHG.JOB_FROM_ID
, CHG.GRADE_FROM_ID
, CHG.POSITION_FROM_ID
, CHG.PRIMARY_FROM_FLAG_CODE
, CHG.ASSIGNMENT_TYPE_FROM_CODE
, CHG.ASSIGNMENT_STATUS_TYPE_FROM_ID
, CHG.PER_SYSTEM_STATUS_FROM_CODE
, CHG.PAY_SYSTEM_STATUS_FROM_CODE
, CHG.ASSIGNMENT_TO_ID
, CHG.BUSINESS_GROUP_TO_ID
, CHG.ORGANIZATION_TO_ID
, CHG.SUPERVISOR_PERSON_TO_ID
, CHG.LOCATION_TO_ID
, CHG.JOB_TO_ID
, CHG.GRADE_TO_ID
, CHG.POSITION_TO_ID
, CHG.PRIMARY_TO_FLAG_CODE
, CHG.ASSIGNMENT_TYPE_TO_CODE
, CHG.ASSIGNMENT_STATUS_TYPE_TO_ID
, CHG.PER_SYSTEM_STATUS_TO_CODE
, CHG.PAY_SYSTEM_STATUS_TO_CODE /* LENGTH OF WORK FK */
, TO_NUMBER(NULL) LOW_MONTHS_LOSS_SEP /* DATA CURRENCY */
, LAST_CHANGE_DATE /* SECURITY */
, CHG.HR_SECURITY_FLAG_CODE
, CHG.BG_SECURITY_FLAG_CODE
FROM /*------------------------------------*/ HRI_MB_WAC_CHANGE_ASG_V CHG WITH READ ONLY