Stuff
public text v1 · immutable
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;