SELECT /* 12.0: bug#4526784 */
tab.supervisor_person_id
,tab.effective_start_date
,tab.effective_end_date
,tab.geo_country_code
,tab.geo_region_code
,tab.geo_city_cid
,tab.anl_slry_currency
,tab.total_anl_slry
,tab.total_headcount
,tab.direct_anl_slry
,tab.direct_headcount
FROM
(SELECT
dim_dlt.supervisor_person_id supervisor_person_id
,dim_dlt.effective_date effective_start_date
,NVL((LEAD(dim_dlt.effective_date, 1) OVER
(PARTITION BY dim_dlt.supervisor_person_id,
dim_dlt.geo_country_code,
dim_dlt.geo_region_code,
dim_dlt.geo_city_cid,
dim_dlt.anl_slry_currency
ORDER BY dim_dlt.effective_date)) - 1,
hr_general.end_of_time) effective_end_date
,dim_dlt.geo_country_code
,dim_dlt.geo_region_code
,dim_dlt.geo_city_cid
,dim_dlt.anl_slry_currency anl_slry_currency
,SUM(dim_dlt.anl_slry_adjust) OVER
(PARTITION BY dim_dlt.supervisor_person_id,
dim_dlt.geo_country_code,
dim_dlt.geo_region_code,
dim_dlt.geo_city_cid,
dim_dlt.anl_slry_currency
ORDER BY dim_dlt.effective_date
RANGE UNBOUNDED PRECEDING)
AS total_anl_slry
,SUM(dim_dlt.headcount_adjust) OVER
(PARTITION BY dim_dlt.supervisor_person_id,
dim_dlt.geo_country_code,
dim_dlt.geo_region_code,
dim_dlt.geo_city_cid,
dim_dlt.anl_slry_currency
ORDER BY dim_dlt.effective_date
RANGE UNBOUNDED PRECEDING)
AS total_headcount
,SUM(dim_dlt.dr_anl_slry_adjust) OVER
(PARTITION BY dim_dlt.supervisor_person_id,
dim_dlt.geo_country_code,
dim_dlt.geo_region_code,
dim_dlt.geo_city_cid,
dim_dlt.anl_slry_currency
ORDER BY dim_dlt.effective_date
RANGE UNBOUNDED PRECEDING)
AS direct_anl_slry
,SUM(dim_dlt.dr_headcount_adjust) OVER
(PARTITION BY dim_dlt.supervisor_person_id,
dim_dlt.geo_country_code,
dim_dlt.geo_region_code,
dim_dlt.geo_city_cid,
dim_dlt.anl_slry_currency
ORDER BY dim_dlt.effective_date
RANGE UNBOUNDED PRECEDING)
AS direct_headcount
FROM
(SELECT
dlt.supervisor_person_id supervisor_person_id
,dlt.effective_date effective_date
,dlt.geo_country_code geo_country_code
,dlt.geo_region_code geo_region_code
,dlt.geo_city_cid geo_city_cid
,dlt.anl_slry_currency anl_slry_currency
,SUM(dlt.anl_slry_adjust) anl_slry_adjust
,SUM(dlt.headcount_adjust) headcount_adjust
,SUM(dr_anl_slry_adjust) dr_anl_slry_adjust
,SUM(dr_headcount_adjust) dr_headcount_adjust
FROM hri_map_sup_wrkfc dlt
GROUP BY
dlt.supervisor_person_id
,dlt.effective_date
,dlt.geo_country_code
,dlt.geo_region_code
,dlt.geo_city_cid
,dlt.anl_slry_currency) dim_dlt
WHERE (dim_dlt.anl_slry_adjust <> 0
OR dim_dlt.headcount_adjust <> 0
OR dim_dlt.dr_anl_slry_adjust <> 0
OR dim_dlt.dr_headcount_adjust <> 0)
) tab