SELECT     v.spr_code       spr_code,
                           v.spr_desc       spr_desc,
                           v.tariff_code    tariff_code,
                           v.tariff_desc    tariff_desc,
                           to_char(v.dates,'YYYY-MM')  month_year,
                           sum(nvl(v.val,0))    total_val,
                           v.typeof
                FROM
                (        /* Get the BILLED REVENUE */
                         SELECT spr.spr_cd spr_code,
                                spr_l.descr50 spr_desc,
                                cbd.cm_tariff tariff_code,
                                crl.descr tariff_desc,
                                sc.accounting_dt dates,
                                cbd.cm_mprn mprn,
                                nvl(cbd.cm_charge, 0) val,
                                'REVENUE' typeof
                           FROM cm_site_calc    sc,
                                cm_billchrg_dwn cbd,
                                ci_rs           crs,
                                ci_rs_l         crl,
                                ci_sa           sa_sup,
                                ci_ft           ft,
                                ci_bill         bill,
                                ci_bseg         cbs,
                                ci_spr          spr,
                                ci_spr_l        spr_l
                          WHERE sc.ft_id = cbd.ft_id
                            AND crs.rs_cd = cbd.cm_tariff
                            AND crs.rs_cd = crl.rs_cd
                            AND spr.sa_id = sa_sup.sa_id
                            AND spr.spr_cd = spr_l.spr_cd
                            AND sa_sup.sa_id = sc.cm_ret_sa_id
                            AND cbd.ft_id = ft.ft_id
                            AND sc.cm_ret_sa_id = sa_sup.sa_id
                            AND sa_sup.acct_id = bill.acct_id
                            AND cbd.cm_invc_nbr = cbs.bill_id
                            AND bill.bill_id = cbs.bill_id
                            AND spr.spr_cd = 'NIE'
                            AND sc.accounting_dt BETWEEN
                                add_months(to_date('2007-05-01', 'yyyy-mm-dd'), -15) + 1 AND
                                last_day(to_date('2007-05-01', 'yyyy-mm-dd'))
                            AND spr_l.language_cd = 'ENG'
                            AND crl.language_cd = 'ENG'
                            AND sa_sup.sa_type_cd = 'SUPPLIER'
                            AND ft.ft_type_flg = 'BS'
                            AND sc.cm_site_calc_stat = 'C'
                            AND bill.bill_stat_flg = 'C'
                            AND cbs.bseg_stat_flg = '50'
                            AND cbd.cm_tariff not in ('T000')
                            AND cbd.cm_tariff = 'T041'
                          UNION ALL                                          /* Get the BILLED CONSUMPTION */
                         SELECT spr.spr_cd spr_code,
                                spr_l.descr50 spr_desc,
                                cbd.cm_tariff tariff_code,
                                crl.descr tariff_desc,
                                sc.accounting_dt dates,
                                cbd.cm_mprn mprn,
                                nvl(cbd.cm_tot_units, 0) val,
                                'CONSUMPTION' typeof
                           FROM cm_site_calc    sc,
                                cm_billchrg_dwn cbd,
                                ci_rs           crs,
                                ci_rs_l         crl,
                                ci_sa           sa_sup,
                                ci_ft           ft,
                                ci_bill         bill,
                                ci_bseg         cbs,
                                ci_spr          spr,
                                ci_spr_l        spr_l
                          WHERE sc.ft_id = cbd.ft_id
                            AND crs.rs_cd = cbd.cm_tariff
                            AND crs.rs_cd = crl.rs_cd
                            AND spr.sa_id = sa_sup.sa_id
                            AND spr.spr_cd = spr_l.spr_cd
                            AND sa_sup.sa_id = sc.cm_ret_sa_id
                            AND cbd.ft_id = ft.ft_id
                            AND sc.cm_ret_sa_id = sa_sup.sa_id
                            AND sa_sup.acct_id = bill.acct_id
                            AND cbd.cm_invc_nbr = cbs.bill_id
                            AND bill.bill_id = cbs.bill_id
                            AND spr.spr_cd = 'NIE'
                            AND sc.accounting_dt BETWEEN
                                add_months(to_date('2007-05-01', 'yyyy-mm-dd'), -15) + 1 AND
                                last_day(to_date('2007-05-01', 'yyyy-mm-dd'))
                            AND spr_l.language_cd = 'ENG'
                            AND crl.language_cd = 'ENG'
                            AND sa_sup.sa_type_cd = 'SUPPLIER'
                            AND ft.ft_type_flg = 'BS'
                            AND sc.cm_site_calc_stat = 'C'
                            AND bill.bill_stat_flg = 'C'
                            AND cbs.bseg_stat_flg = '50'
                            AND cbd.cm_tariff not in ('T000')
                            AND cbd.cm_tariff = 'T041'
                            UNION ALL /* Get all the Billed MPRNs */
                            SELECT spr.spr_cd spr_code,
                                spr_l.descr50 spr_desc,
                                cbd.cm_tariff tariff_code,
                                crl.descr tariff_desc,
                                sc.accounting_dt dates,
                                cbd.cm_mprn mprn,
                                1 val,
                                'BMPRN' typeof
                           FROM cm_site_calc    sc,
                                cm_billchrg_dwn cbd,
                                ci_rs           crs,
                                ci_rs_l         crl,
                                ci_sa           sa_sup,
                                ci_ft           ft,
                                ci_bill         bill,
                                ci_bseg         cbs,
                                ci_spr          spr,
                                ci_spr_l        spr_l
                          WHERE sc.ft_id = cbd.ft_id
                            AND crs.rs_cd = cbd.cm_tariff
                            AND crs.rs_cd = crl.rs_cd
                            AND spr.sa_id = sa_sup.sa_id
                            AND spr.spr_cd = spr_l.spr_cd
                            AND sa_sup.sa_id = sc.cm_ret_sa_id
                            AND cbd.ft_id = ft.ft_id
                            AND sc.cm_ret_sa_id = sa_sup.sa_id
                            AND sa_sup.acct_id = bill.acct_id
                            AND cbd.cm_invc_nbr = cbs.bill_id
                            AND bill.bill_id = cbs.bill_id
                            AND spr.spr_cd = 'NIE'
                            AND sc.accounting_dt BETWEEN
                                add_months(to_date('2007-05-01', 'yyyy-mm-dd'), -15) + 1 AND
                                last_day(to_date('2007-05-01', 'yyyy-mm-dd'))
                            AND spr_l.language_cd = 'ENG'
                            AND crl.language_cd = 'ENG'
                            AND sa_sup.sa_type_cd = 'SUPPLIER'
                            AND ft.ft_type_flg = 'BS'
                            AND sc.cm_site_calc_stat = 'C'
                            AND bill.bill_stat_flg = 'C'
                            AND cbs.bseg_stat_flg = '50'
                            AND cbd.cm_tariff not in ('T000')
                            AND cbd.cm_tariff = 'T041'
                       ) v
                       GROUP BY v.spr_code,
                                v.spr_desc,
                                v.tariff_code,
                                v.tariff_desc,
                                v.typeof,
                                to_char(v.dates,'YYYY-MM')
                       ORDER BY v.spr_code,
                                v.spr_desc,
                                v.tariff_code,
                                v.tariff_desc,
                                v.typeof,
                                to_char(v.dates,'YYYY-MM') desc;