SELECT /*+ ORDERED USE_HASH(sup) USE_HASH(sum1) PARALLEL(sup) PARALLEL(sum1) */ sum2.supervisor_id supervisor_id , sum2.effective_start_date effective_date , SUM(sum1.sum_information2) budget_value , 'TOTAL_WMV_BUDGET' count_type FROM (SELECT /*+ NO_MERGE USE_HASH(sup) USE_HASH(sum3) */ /* 12.0: bug#4526784 */ DISTINCT sup.sup_person_id supervisor_id , sum3.effective_date effective_start_date FROM HR.HR_PTL_SUMMARY_DATA sum3 , hri_cs_suph_v sup WHERE sum3.sum_information_category = 'HRI_DBI_WMV_BUDGET' AND sup.sub_person_id = sum3.summary_context_id AND sup.sub_invalid_flag_code = 'N' AND sum3.effective_date BETWEEN sup.effective_start_date AND sup.effective_end_date ) sum2 , hri_cs_suph_v sup , HR.HR_PTL_SUMMARY_DATA sum1 WHERE sum1.sum_information_category = 'HRI_DBI_WMV_BUDGET' AND sum2.supervisor_id = sup.sup_person_id AND sum1.summary_context_id = sup.sub_person_id AND sup.sub_invalid_flag_code = 'N' AND TRUNC(sum2.effective_start_date) BETWEEN TRUNC(sum1.effective_date) AND TRUNC(sum1.effective_end_date) AND sum2.effective_start_date BETWEEN TRUNC(sup.effective_start_date) AND TRUNC(sup.effective_end_date) GROUP BY sum2.supervisor_id , sum2.effective_start_date UNION ALL SELECT sum2.supervisor_id supervisor_id , sum2.effective_date effective_date , SUM(sum1.sum_information2) budget_value , 'DR_WMV_BUDGET' count_type FROM HR.HR_PTL_SUMMARY_DATA sum1 , (SELECT DISTINCT sum3.summary_context_id supervisor_id , sum3.effective_date effective_date FROM HR.HR_PTL_SUMMARY_DATA sum3 WHERE sum3.sum_information_category = 'HRI_DBI_WMV_BUDGET') sum2 WHERE sum1.sum_information_category = 'HRI_DBI_WMV_BUDGET' AND sum2.supervisor_id = sum1.summary_context_id AND sum2.effective_date BETWEEN TRUNC(sum1.effective_date) AND TRUNC(sum1.effective_end_date) GROUP BY sum2.supervisor_id , sum2.effective_date
[Home] [Help]