[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AP_PAID_XB_MV
Source
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_amt_b) paid_amt_b,
sum(paid_amt_prim_g) paid_amt_prim_g,
sum(paid_amt_sec_g) paid_amt_sec_g,
sum(invoice_amt_wtd_b) paid_invoice_amt_wtd_b,
sum(invoice_amt_mtd_b) paid_invoice_amt_mtd_b,
sum(invoice_amt_qtd_b) paid_invoice_amt_qtd_b,
sum(invoice_amt_ytd_b) paid_invoice_amt_ytd_b,
sum(prim_invoice_amt_wtd) paid_invoice_amt_wtd_prim_g,
sum(prim_invoice_amt_mtd) paid_invoice_amt_mtd_prim_g,
sum(prim_invoice_amt_qtd) paid_invoice_amt_qtd_prim_g,
sum(prim_invoice_amt_ytd) paid_invoice_amt_ytd_prim_g,
sum(sec_invoice_amt_wtd) paid_invoice_amt_wtd_sec_g,
sum(sec_invoice_amt_mtd) paid_invoice_amt_mtd_sec_g,
sum(sec_invoice_amt_qtd) paid_invoice_amt_qtd_sec_g,
sum(sec_invoice_amt_ytd) paid_invoice_amt_ytd_sec_g,
sum(paid_inv_count_wtd) paid_inv_count_wtd,
sum(paid_inv_count_mtd) paid_inv_count_mtd,
sum(paid_inv_count_qtd) paid_inv_count_qtd,
sum(paid_inv_count_ytd) paid_inv_count_ytd,
sum(payment_count_wtd) payment_count_wtd,
sum(payment_count_mtd) payment_count_mtd,
sum(payment_count_qtd)payment_count_qtd,
sum(payment_count_ytd) payment_count_ytd,
sum(on_time_payment_amt_b) paid_on_time_amt_b,
sum(prim_on_time_payment_amt) paid_on_time_amt_prim_g,
sum(sec_on_time_payment_amt) paid_on_time_amt_sec_g,
sum(late_payment_amt_b) paid_late_amt_b,
sum(prim_late_payment_amt) paid_late_amt_prim_g,
sum(sec_late_payment_amt) paid_late_amt_sec_g,
sum(e_payment_amt_b) e_payment_amt_b,
sum(prim_e_payment_amt) e_payment_amt_prim_g,
sum(sec_e_payment_amt) e_payment_amt_sec_g,
sum(e_payment_count) e_payment_count,
sum(discount_offered_wtd_b) paid_dis_offered_wtd_b,
sum(discount_offered_mtd_b) paid_dis_offered_mtd_b,
sum(discount_offered_qtd_b) paid_dis_offered_qtd_b,
sum(discount_offered_ytd_b) paid_dis_offered_ytd_b,
sum(prim_discount_offered_wtd) paid_dis_offered_wtd_prim_g,
sum(prim_discount_offered_mtd) paid_dis_offered_mtd_prim_g,
sum(prim_discount_offered_qtd) paid_dis_offered_qtd_prim_g,
sum(prim_discount_offered_ytd) paid_dis_offered_ytd_prim_g,
sum(sec_discount_offered_wtd) paid_dis_offered_wtd_sec_g,
sum(sec_discount_offered_mtd) paid_dis_offered_mtd_sec_g,
sum(sec_discount_offered_qtd) paid_dis_offered_qtd_sec_g,
sum(sec_discount_offered_ytd) paid_dis_offered_ytd_sec_g,
sum(discount_taken_b) paid_dis_taken_b,
sum(prim_discount_taken) paid_dis_taken_prim_g,
sum(sec_discount_taken) paid_dis_taken_sec_g,
sum(discount_lost_wtd_b) paid_dis_lost_wtd_b,
sum(discount_lost_mtd_b) paid_dis_lost_mtd_b,
sum(discount_lost_qtd_b) paid_dis_lost_qtd_b,
sum(discount_lost_ytd_b) paid_dis_lost_ytd_b,
sum(prim_discount_lost_wtd) paid_dis_lost_wtd_prim_g,
sum(prim_discount_lost_mtd) paid_dis_lost_mtd_prim_g,
sum(prim_discount_lost_qtd) paid_dis_lost_qtd_prim_g,
sum(prim_discount_lost_ytd) paid_dis_lost_ytd_prim_g,
sum(sec_discount_lost_wtd) paid_dis_lost_wtd_sec_g,
sum(sec_discount_lost_mtd) paid_dis_lost_mtd_sec_g,
sum(sec_discount_lost_qtd) paid_dis_lost_qtd_sec_g,
sum(sec_discount_lost_ytd) paid_dis_lost_ytd_sec_g,
sum(invoice_to_payment_days) invoice_to_payment_days
from
(select f.org_id org_id,
f.supplier_id supplier_id,
decode(grouping_id(time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian),
0, time.report_date_julian, 1, time.week_id, 3, time.ent_period_id, 7, time.ent_qtr_id, 15, time.ent_year_id) time_id,
decode(grouping_id(time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian),
0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id,
sum(f.payment_amount_b) paid_amt_b,
sum(f.prim_payment_amount) paid_amt_prim_g,
sum(f.sec_payment_amount) paid_amt_sec_g,
0 invoice_amt_wtd_b,
0 invoice_amt_mtd_b,
0 invoice_amt_qtd_b,
0 invoice_amt_ytd_b,
0 prim_invoice_amt_wtd,
0 prim_invoice_amt_mtd,
0 prim_invoice_amt_qtd,
0 prim_invoice_amt_ytd,
0 sec_invoice_amt_wtd,
0 sec_invoice_amt_mtd,
0 sec_invoice_amt_qtd,
0 sec_invoice_amt_ytd,
0 paid_inv_count_wtd,
0 paid_inv_count_mtd,
0 paid_inv_count_qtd,
0 paid_inv_count_ytd,
0 payment_count_wtd,
0 payment_count_mtd,
0 payment_count_qtd,
0 payment_count_ytd,
sum(f.on_time_payment_amt_b) on_time_payment_amt_b,
sum(f.prim_on_time_payment_amt) prim_on_time_payment_amt,
sum(f.sec_on_time_payment_amt) sec_on_time_payment_amt,
sum(f.late_payment_amt_b) late_payment_amt_b,
sum(f.prim_late_payment_amt) prim_late_payment_amt,
sum(f.sec_late_payment_amt) sec_late_payment_amt,
sum(decode(f.payment_method,'E',f.payment_amount_b,0)) e_payment_amt_b,
sum(decode(f.payment_method,'E',f.prim_payment_amount,0)) prim_e_payment_amt,
sum(decode(f.payment_method,'E',f.sec_payment_amount,0)) sec_e_payment_amt,
decode(f.payment_method, 'E', count(distinct f.check_id), 0) e_payment_count,
0 discount_offered_wtd_b,
0 discount_offered_mtd_b,
0 discount_offered_qtd_b,
0 discount_offered_ytd_b,
0 prim_discount_offered_wtd,
0 prim_discount_offered_mtd,
0 prim_discount_offered_qtd,
0 prim_discount_offered_ytd,
0 sec_discount_offered_wtd,
0 sec_discount_offered_mtd,
0 sec_discount_offered_qtd,
0 sec_discount_offered_ytd,
sum(f.discount_taken_b) discount_taken_b,
sum(f.prim_discount_taken) prim_discount_taken,
sum(f.sec_discount_taken) sec_discount_taken,
0 discount_lost_wtd_b,
0 discount_lost_mtd_b,
0 discount_lost_qtd_b,
0 discount_lost_ytd_b,
0 prim_discount_lost_wtd,
0 prim_discount_lost_mtd,
0 prim_discount_lost_qtd,
0 prim_discount_lost_ytd,
0 sec_discount_lost_wtd,
0 sec_discount_lost_mtd,
0 sec_discount_lost_qtd,
0 sec_discount_lost_ytd,
0 invoice_to_payment_days
from FII.FII_AP_PAY_SCHED_B f, FII.FII_TIME_DAY time
where f.period_type_id = 1
and f.action in ('PAYMENT')
and f.action_date = time.report_date
group by f.org_id, f.supplier_id, f.payment_method,
rollup (time.ent_year_id, time.ent_qtr_id, time.ent_period_id, time.week_id, time.report_date_julian)
union all
select f.org_id org_id,
f.supplier_id supplier_id,
decode(grouping_id(f.ent_year_id, f.ent_qtr_id, f.ent_period_id, f.week_id, f.report_date_julian),
0, f.report_date_julian, 1, f.week_id, 3, f.ent_period_id, 7, f.ent_qtr_id, 15, f.ent_year_id) time_id,
decode(grouping_id(f.ent_year_id, f.ent_qtr_id, f.ent_period_id, f.week_id, f.report_date_julian),
0, 1, 1, 16, 3, 32, 7, 64, 15, 128) period_type_id,
0 paid_amt_b,
0 paid_amt_prim_g,
0 paid_amt_sec_g,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then base_amount else 0 end), 0) invoice_amt_wtd_b,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then base_amount else 0 end), 0) invoice_amt_mtd_b,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then base_amount else 0 end), 0) invoice_amt_qtd_b,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then base_amount else 0 end), 0) invoice_amt_ytd_b,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then prim_amount else 0 end), 0) prim_invoice_amt_wtd,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then prim_amount else 0 end), 0) prim_invoice_amt_mtd,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then prim_amount else 0 end), 0) prim_invoice_amt_qtd,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then prim_amount else 0 end), 0) prim_invoice_amt_ytd,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then sec_amount else 0 end), 0) sec_invoice_amt_wtd,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then sec_amount else 0 end), 0) sec_invoice_amt_mtd,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then sec_amount else 0 end), 0) sec_invoice_amt_qtd,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then sec_amount else 0 end), 0) sec_invoice_amt_ytd,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then 1 else 0 end), 0) paid_inv_count_wtd,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then 1 else 0 end), 0) paid_inv_count_mtd,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then 1 else 0 end), 0) paid_inv_count_qtd,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then 1 else 0 end), 0) paid_inv_count_ytd,
sum(case when payment_rank_wtd=1 then 1 else 0 end) payment_count_wtd,
sum(case when payment_rank_mtd=1 then 1 else 0 end) payment_count_mtd,
sum(case when payment_rank_qtd=1 then 1 else 0 end) payment_count_qtd,
sum(case when payment_rank_ytd=1 then 1 else 0 end) payment_count_ytd,
0 on_time_payment_amt_b,
0 prim_on_time_payment_amt,
0 sec_on_time_payment_amt,
0 late_payment_amt_b,
0 prim_late_payment_amt,
0 sec_late_payment_amt,
0 e_payment_amt_b,
0 prim_e_payment_amt,
0 sec_e_payment_amt,
0 e_payment_count,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then discount_offered_b else 0 end), 0) discount_offered_wtd_b,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then discount_offered_b else 0 end), 0) discount_offered_mtd_b,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then discount_offered_b else 0 end), 0) discount_offered_qtd_b,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then discount_offered_b else 0 end), 0) discount_offered_ytd_b,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then prim_discount_offered else 0 end), 0) prim_discount_offered_wtd,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then prim_discount_offered else 0 end), 0) prim_discount_offered_mtd,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then prim_discount_offered else 0 end), 0) prim_discount_offered_qtd,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then prim_discount_offered else 0 end), 0) prim_discount_offered_ytd,
decode(f.action, 'PAYMENT', sum(case when wtd_rank=1 then sec_discount_offered else 0 end), 0) sec_discount_offered_wtd,
decode(f.action, 'PAYMENT', sum(case when mtd_rank=1 then sec_discount_offered else 0 end), 0) sec_discount_offered_mtd,
decode(f.action, 'PAYMENT', sum(case when qtd_rank=1 then sec_discount_offered else 0 end), 0) sec_discount_offered_qtd,
decode(f.action, 'PAYMENT', sum(case when ytd_rank=1 then sec_discount_offered else 0 end), 0) sec_discount_offered_ytd,
0 discount_taken_b,
0 prim_discount_taken,
0 sec_discount_taken,
sum(case when same_week=1 then discount_lost_b else 0 end) discount_lost_wtd_b,
sum(case when same_month=1 then discount_lost_b else 0 end) discount_lost_mtd_b,
sum(case when same_qtr=1 then discount_lost_b else 0 end) discount_lost_qtd_b,
sum(case when same_year=1 then discount_lost_b else 0 end) discount_lost_ytd_b,
sum(case when same_week=1 then prim_discount_lost else 0 end) prim_discount_lost_wtd,
sum(case when same_month=1 then prim_discount_lost else 0 end) prim_discount_lost_mtd,
sum(case when same_qtr=1 then prim_discount_lost else 0 end) prim_discount_lost_qtd,
sum(case when same_year=1 then prim_discount_lost else 0 end) prim_discount_lost_ytd,
sum(case when same_week=1 then sec_discount_lost else 0 end) sec_discount_lost_wtd,
sum(case when same_month=1 then sec_discount_lost else 0 end) sec_discount_lost_mtd,
sum(case when same_qtr=1 then sec_discount_lost else 0 end) sec_discount_lost_qtd,
sum(case when same_year=1 then sec_discount_lost else 0 end) sec_discount_lost_ytd,
decode(f.action, 'PAYMENT', sum(wt_invoice_to_payment_days), 0) invoice_to_payment_days
from
(select distinct f.org_id,
f.supplier_id,
f.invoice_id,
f.action,
time.report_date_julian,
time.week_id,
time.ent_period_id,
time.ent_qtr_id,
time.ent_year_id,
f.period_type_id period_type_id,
decode(f.action, 'PAYMENT', rank() over (partition by f.check_id,time.week_id order by f.action_date,f.invoice_id, f.payment_num), 0) payment_rank_wtd,
decode(f.action, 'PAYMENT', rank() over (partition by f.check_id,time.ent_period_id order by f.action_date,f.invoice_id, f.payment_num), 0) payment_rank_mtd,
decode(f.action, 'PAYMENT', rank() over (partition by f.check_id,time.ent_qtr_id order by f.action_date,f.invoice_id, f.payment_num), 0) payment_rank_qtd,
decode(f.action, 'PAYMENT', rank() over (partition by f.check_id,time.ent_year_id order by f.action_date,f.invoice_id, f.payment_num), 0) payment_rank_ytd,
sum(decode(f.action, 'PAYMENT', 1, 0)) over (partition by f.invoice_id) paid_flag,
decode(f.action, 'PAYMENT', rank () over (partition by f.invoice_id, f.action, time.week_id order by f.action_date, f.payment_num, f.check_date, f.check_id), 0) wtd_rank,
decode(f.action, 'PAYMENT', rank () over (partition by f.invoice_id, f.action, time.ent_period_id order by f.action_date, f.payment_num, f.check_date, f.check_id), 0) mtd_rank,
decode(f.action, 'PAYMENT', rank () over (partition by f.invoice_id, f.action, time.ent_qtr_id order by f.action_date, f.payment_num, f.check_date, f.check_id), 0) qtd_rank,
decode(f.action, 'PAYMENT', rank () over (partition by f.invoice_id, f.action, time.ent_year_id order by f.action_date, f.payment_num, f.check_date, f.check_id), 0) ytd_rank,
base.base_amount,
base.prim_amount,
base.sec_amount,
base.discount_offered_b,
base.prim_discount_offered,
base.sec_discount_offered,
case when min(decode(f.action, 'PAYMENT', time.week_id)) over (partition by f.invoice_id, time.week_id)
=nvl(min(decode(f.action, 'DISCOUNT', time.week_id)) over (partition by f.invoice_id, time.week_id),
min(decode(f.action, 'PAYMENT', time.week_id)) over(partition by f.invoice_id, time.week_id))
then 1 else 0 end same_week,
case when min(decode(f.action, 'PAYMENT', time.ent_period_id)) over (partition by f.invoice_id, time.ent_period_id)
=nvl(min(decode(f.action, 'DISCOUNT', time.ent_period_id)) over (partition by f.invoice_id, time.ent_period_id),
min(decode(f.action, 'PAYMENT', time.ent_period_id)) over(partition by f.invoice_id, time.ent_period_id))
then 1 else 0 end same_month,
case when min(decode(f.action, 'PAYMENT', time.ent_qtr_id)) over (partition by f.invoice_id, time.ent_qtr_id)
=nvl(min(decode(f.action, 'DISCOUNT', time.ent_qtr_id)) over (partition by f.invoice_id, time.ent_qtr_id),
min(decode(f.action, 'PAYMENT', time.ent_qtr_id)) over(partition by f.invoice_id, time.ent_qtr_id))
then 1 else 0 end same_qtr,
case when min(decode(f.action, 'PAYMENT', time.ent_year_id)) over (partition by f.invoice_id, time.ent_year_id)
=nvl(min(decode(f.action, 'DISCOUNT', time.ent_year_id)) over (partition by f.invoice_id, time.ent_year_id),
min(decode(f.action, 'PAYMENT', time.ent_year_id)) over(partition by f.invoice_id, time.ent_year_id))
then 1 else 0 end same_year,
f.discount_lost_b,
f.prim_discount_lost,
f.sec_discount_lost,
--f.check_date-base.invoice_date invoice_to_payment_days
(f.check_date-base.invoice_date) * f.payment_amount_b wt_invoice_to_payment_days
from FII.FII_AP_PAY_SCHED_B f, FII.FII_TIME_DAY time, FII.FII_AP_INVOICE_B base
where f.action in ('PAYMENT', 'DISCOUNT')
and f.action_date = time.report_date
and f.invoice_id = base.invoice_id) f
where f.paid_flag > 0
group by f.org_id, f.supplier_id, f.action, f.paid_flag,
rollup (f.ent_year_id, f.ent_qtr_id, f.ent_period_id, f.week_id, f.report_date_julian))
group by grouping sets((org_id, supplier_id, time_id, period_type_id),
(org_id, time_id, period_type_id))