select 0 UMARKER, 'SALES REP' GRP_MARKER, fact.agreement_type_id AGREEMENT_TYPE_ID, fact.customer_id CUSTOMER_ID, cc.class_code CLASS_CODE, fact.salesrep_id RESOURCE_ID, fact.sales_grp_id SALES_GRP_ID, fact.sales_grp_id PARENT_GRP_ID, decode(grouping_id( fact.agreement_type_id, cc.class_code, fact.customer_id),0,0,4,1,1,2,5,3,3,4,7,5) AGG_LEVEL, grouping_id( fact.agreement_type_id, cc.class_code, fact.customer_id, fact.salesrep_id, fact.sales_grp_id, cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian) GRP_ID, cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian, decode(grouping_id(cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian), 30, cal.report_date_julian, 29, cal.week_id, 27, cal.ent_period_id, 23, cal.ent_qtr_id, 15, cal.ent_year_id) TIME_ID, sum(fact.commit_prorated_amt_g) COMMIT_AMT_G, sum(fact.commit_prorated_amt_g1) COMMIT_AMT_G1, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g else 0 end) FULFILL_F_EE_AMT_G, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g1 else 0 end) FULFILL_F_EE_AMT_G1, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g else 0 end) FULFILL_OUT_F_EE_AMT_G, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g1 else 0 end) FULFILL_OUT_F_EE_AMT_G1, count(fact.commit_prorated_amt_g) C_COMMIT_AMT_G, count(fact.commit_prorated_amt_g1) C_COMMIT_AMT_G1, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g else 0 end) C_FULFILL_F_EE_AMT_G, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g1 else 0 end) C_FULFILL_F_EE_AMT_G1, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g else 0 end) C_FULFILL_OUT_F_EE_AMT_G, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g1 else 0 end) C_FULFILL_OUT_F_EE_AMT_G1, count(*) C_TOTAL from ISC.ISC_DBI_BSA_ORDER_LINES_F fact, FII.FII_PARTY_MKT_CLASS cc, FII.FII_TIME_DAY cal where fact.time_effective_end_date_id = cal.report_date and fact.customer_id = cc.party_id and fact.transaction_phase_code = 'F' and fact.commit_prorated_amt_g is not null and fact.blanket_line_id is not null and nvl(fact.time_termination_date_id, fact.time_activation_date_id + 1) >= fact.time_activation_date_id group by rollup(fact.agreement_type_id), fact.salesrep_id, fact.sales_grp_id, rollup(cc.class_code, fact.customer_id), grouping sets(cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian) union all select 1 UMARKER, grp.umarker GRP_MARKER, fact.agreement_type_id AGREEMENT_TYPE_ID, fact.customer_id CUSTOMER_ID, cc.class_code CLASS_CODE, null RESOURCE_ID, grp.prg_id SALES_GRP_ID, grp.parent_prg_id PARENT_GRP_ID, decode(grouping_id( fact.agreement_type_id, cc.class_code, fact.customer_id),0,0,4,1,1,2,5,3,3,4,7,5) AGG_LEVEL, grouping_id( fact.agreement_type_id, cc.class_code, fact.customer_id, grp.umarker, grp.prg_id, grp.parent_prg_id, cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian) GRP_ID, cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian, decode(grouping_id(cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian), 30, cal.report_date_julian, 29, cal.week_id, 27, cal.ent_period_id, 23, cal.ent_qtr_id, 15, cal.ent_year_id) TIME_ID, sum(fact.commit_prorated_amt_g) COMMIT_AMT_G, sum(fact.commit_prorated_amt_g1) COMMIT_AMT_G1, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g else 0 end) FULFILL_F_EE_AMT_G, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g1 else 0 end) FULFILL_F_EE_AMT_G1, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g else 0 end) FULFILL_OUT_F_EE_AMT_G, sum(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g1 else 0 end) FULFILL_OUT_F_EE_AMT_G1, count(fact.commit_prorated_amt_g) C_COMMIT_AMT_G, count(fact.commit_prorated_amt_g1) C_COMMIT_AMT_G1, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g else 0 end) C_FULFILL_F_EE_AMT_G, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_amt_g1 else 0 end) C_FULFILL_F_EE_AMT_G1, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g else 0 end) C_FULFILL_OUT_F_EE_AMT_G, count(case when fact.time_fulfilled_date_id is not null and fact.time_fulfilled_date_id <= fact.time_effective_end_date_id then fact.fulfilled_outstand_amt_g1 else 0 end) C_FULFILL_OUT_F_EE_AMT_G1, count(*) C_TOTAL from ISC.ISC_DBI_BSA_ORDER_LINES_F fact, FII.FII_PARTY_MKT_CLASS cc, oki_rs_group_mv grp, FII.FII_TIME_DAY cal where fact.time_effective_end_date_id = cal.report_date and fact.customer_id = cc.party_id and fact.sales_grp_id = grp.rg_id and fact.transaction_phase_code = 'F' and fact.commit_prorated_amt_g is not null and fact.blanket_line_id is not null and nvl(fact.time_termination_date_id, fact.time_activation_date_id + 1) >= fact.time_activation_date_id group by rollup(fact.agreement_type_id), grp.umarker, grp.prg_id, grp.parent_prg_id, rollup(cc.class_code, fact.customer_id), grouping sets(cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian)