select /* 12.0: bug#4526784 */ org_id org_id,
supplier_id supplier_id,
time_id time_id,
period_type_id period_type_id,
grouping_id(org_id, supplier_id, time_id, period_type_id) gid,
sum(paid_on_time_count_wtd) paid_on_time_count_wtd,
sum(paid_on_time_count_mtd) paid_on_time_count_mtd,
sum(paid_on_time_count_qtd) paid_on_time_count_qtd,
sum(paid_on_time_count_ytd) paid_on_time_count_ytd,
sum(paid_late_count_wtd) paid_late_count_wtd,
sum(paid_late_count_mtd) paid_late_count_mtd,
sum(paid_late_count_qtd) paid_late_count_qtd,
sum(paid_late_count_ytd) paid_late_count_ytd
from
(select f.org_id org_id,
f.supplier_id supplier_id,
decode(grouping_id(fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian),
0, fday.report_date_julian, 1, fday.week_id, 3, fday.ent_period_id, 7, fday.ent_qtr_id, 15, fday.ent_year_id) time_id,
decode(grouping_id(fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian),
0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id,
sum(case when on_time_wtd_rank=1 then 1 else 0 end) paid_on_time_count_wtd,
sum(case when on_time_mtd_rank=1 then 1 else 0 end) paid_on_time_count_mtd,
sum(case when on_time_qtd_rank=1 then 1 else 0 end) paid_on_time_count_qtd,
sum(case when on_time_ytd_rank=1 then 1 else 0 end) paid_on_time_count_ytd,
sum(case when late_wtd_rank=1 then 1 else 0 end) paid_late_count_wtd,
sum(case when late_mtd_rank=1 then 1 else 0 end) paid_late_count_mtd,
sum(case when late_qtd_rank=1 then 1 else 0 end) paid_late_count_qtd,
sum(case when late_ytd_rank=1 then 1 else 0 end) paid_late_count_ytd
from
(select distinct f.org_id,
f.supplier_id,
f.invoice_id,
f.time_id,
case when f.prim_on_time_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.week_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end on_time_wtd_rank,
case when f.prim_on_time_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.ent_period_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end on_time_mtd_rank,
case when f.prim_on_time_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.ent_qtr_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end on_time_qtd_rank,
case when f.prim_on_time_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.ent_year_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end on_time_ytd_rank,
case when f.prim_late_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.week_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end late_wtd_rank,
case when f.prim_late_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.ent_period_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end late_mtd_rank,
case when f.prim_late_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.ent_qtr_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end late_qtd_rank,
case when f.prim_late_payment_amt <> 0 then
rank () over (partition by f.invoice_id, time.ent_year_id, decode(sign(f.due_date-f.action_date),-1,-1,1) order by f.action_date) else 0 end late_ytd_rank
from FII.FII_AP_PAY_SCHED_B f, FII.FII_TIME_DAY time
where f.action in ('PAYMENT')
and f.action_date = time.report_date) f, fii_time_day fday
where f.time_id = fday.report_date_julian
group by f.org_id, f.supplier_id,
rollup (fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian))
group by grouping sets((org_id, supplier_id, time_id, period_type_id), (org_id, time_id, period_type_id))
[Home] [Help]