MATERIALIZED VIEW: APPS.HRI_MDP_SUP_WCNT_TERM_ASG_MV

Source

SELECT  /* 12.0: bug#4526784 */
     dt.supervisor_person_id      supervisor_person_id
    ,dt.direct_supervisor_person_id
                                   direct_supervisor_person_id
    ,dt.effective_date            effective_date
    ,dt.wkth_wktyp_sk_fk          wkth_wktyp_sk_fk
    ,dt.pow_start_date            pow_start_date
    ,SUM(MONTHS_BETWEEN(dt.effective_date, dt.pow_start_date) * dt.primary_flag_ind)
                                  pow_months
    ,dt.assignment_id             assignment_id
    ,dt.person_id                 person_id
    ,dt.geo_area_code             geo_area_code
    ,dt.geo_country_code          geo_country_code
    ,dt.job_fmly_code             job_fmly_code
    ,dt.job_fnctn_code            job_fnctn_code
    ,dt.job_id                    job_id
    ,dt.leaving_reason_code       leaving_reason_code
    ,dt.separation_category       separation_category
    ,dt.organization_id           organization_id
    ,dt.location_id               location_id
    ,dt.pow_band_sk_fk            pow_band_sk_fk
    ,dt.perf_band                 perf_band
    ,dt.direct_ind                direct_ind
    ,SUM(dt.headcount_value)      separation_hdc
    ,count(dt.headcount_value)    admin_sprtn_hdc_count
    ,count(*)                     admin_row_count
FROM HRI.HRI_MAP_SUP_WRKFC_ASG  dt
WHERE dt.worker_term_ind = 1
AND dt.metric_adjust_multiplier = -1
GROUP BY
     dt.supervisor_person_id
    ,dt.direct_supervisor_person_id
    ,dt.effective_date
    ,dt.wkth_wktyp_sk_fk
    ,dt.pow_start_date
    ,dt.assignment_id
    ,dt.person_id
    ,dt.geo_area_code
    ,dt.geo_country_code
    ,dt.job_fmly_code
    ,dt.job_fnctn_code
    ,dt.job_id
    ,dt.leaving_reason_code
    ,dt.separation_category
    ,dt.organization_id
    ,dt.location_id
    ,dt.pow_band_sk_fk
    ,dt.perf_band
    ,dt.direct_ind