[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AP_HLIA_I_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(inv_on_hold_amt_b) inv_on_hold_amt_b,
sum(prim_inv_on_hold_amt) inv_on_hold_amt_prim_g,
sum(sec_inv_on_hold_amt) inv_on_hold_amt_sec_g,
sum(inv_on_hold_count) inv_on_hold_count,
sum(due_count) on_hold_due_count,
sum(days_on_hold) days_on_hold,
sum(payment_amount_b) on_hold_payment_amount_b,
sum(prim_payment_amount) on_hold_payment_amount_prim_g,
sum(sec_payment_amount) on_hold_payment_amount_sec_g,
sum(past_due_amt_b) on_hold_past_due_amt_b,
sum(prim_past_due_amt) on_hold_past_due_amt_prim_g,
sum(sec_past_due_amt) on_hold_past_due_amt_sec_g,
sum(past_due_count) on_hold_past_due_count,
sum(discount_remaining_b) on_hold_dis_remaining_b,
sum(prim_discount_remaining) on_hold_dis_remaining_prim_g,
sum(sec_discount_remaining) on_hold_dis_remaining_sec_g,
sum(discount_taken_b) on_hold_dis_taken_b, --open issue:dicount taken seems to be always 0 for the on hold invoice, needs to verify with Susie
sum(prim_discount_taken) on_hold_dis_taken_prim_g, --open issue:dicount taken seems to be always 0 for the on hold invoice, needs to verify with Susie
sum(sec_discount_taken) on_hold_dis_taken_sec_g, --open issue:dicount taken seems to be always 0 for the on hold invoice, needs to verify with Susie
sum(discount_lost_b) on_hold_dis_lost_b,
sum(prim_discount_lost) on_hold_dis_lost_prim_g,
sum(sec_discount_lost) on_hold_dis_lost_sec_g,
sum(hold_count) no_of_holds
from
(select f.org_id org_id,
f.supplier_id supplier_id,
f.time_id time_id,
f.period_type_id period_type_id,
sum(f.on_hold_amount_b) inv_on_hold_amt_b,
sum(f.on_hold_amount_prim_g) prim_inv_on_hold_amt,
sum(f.on_hold_amount_sec_g) sec_inv_on_hold_amt,
0 inv_on_hold_count,
0 due_count,
decode(f.action, 'H', hhist.days_on_hold, 'R', (-1)*hhist.days_on_hold) days_on_hold,
sum(f.on_hold_payment_amount_b) payment_amount_b,
sum(f.on_hold_payment_amount_prim_g) prim_payment_amount,
sum(f.on_hold_payment_amount_sec_g) sec_payment_amount,
sum(f.on_hold_past_due_amount_b) past_due_amt_b,
sum(f.on_hold_past_due_amount_prim_g) prim_past_due_amt,
sum(f.on_hold_past_due_amount_sec_g) sec_past_due_amt,
0 past_due_count,
sum(f.on_hold_dis_available_b) discount_remaining_b,
sum(f.on_hold_dis_available_prim_g) prim_discount_remaining,
sum(f.on_hold_dis_available_sec_g) sec_discount_remaining,
sum(f.on_hold_dis_taken_b) discount_taken_b,
sum(f.on_hold_dis_taken_prim_g) prim_discount_taken,
sum(f.on_hold_dis_taken_sec_g) sec_discount_taken,
sum(f.on_hold_dis_lost_b) discount_lost_b,
sum(f.on_hold_dis_lost_prim_g) prim_discount_lost,
sum(f.on_hold_dis_lost_sec_g) sec_discount_lost,
sum(f.no_of_holds) hold_count
from FII_AP_HLIA_IB_MV f, (select invoice_id invoice_id, sum(days_on_hold) days_on_hold from FII_AP_HHIST_IB_MV group by invoice_id) hhist
where f.invoice_id = hhist.invoice_id
group by f.org_id, f.supplier_id, f.time_id, f.period_type_id, f.invoice_id, f.hold_date, f.release_date, f.action, hhist.days_on_hold
union all
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,
0 inv_on_hold_amt_b,
0 prim_inv_on_hold_amt,
0 sec_inv_on_hold_amt,
sum(f.inv_on_hold_count) inv_on_hold_count,
sum(f.due_count) due_count,
0 days_on_hold,
0 payment_amount_b,
0 prim_payment_amount,
0 sec_payment_amount,
0 past_due_amt_b,
0 prim_past_due_amt,
0 sec_past_due_amt,
sum(f.past_due_count) past_due_count,
0 discount_remaining_b,
0 prim_discount_remaining,
0 sec_discount_remaining,
0 discount_taken_b,
0 prim_discount_taken,
0 sec_discount_taken,
0 discount_lost_b,
0 prim_discount_lost,
0 sec_discount_lost,
0 hold_count
from
(select f.org_id org_id,
f.supplier_id supplier_id,
f.time_id time_id,
decode(f.action, 'H', count(distinct f.invoice_id), 'R', (-1)*count(distinct f.invoice_id), 'DUE', 0) inv_on_hold_count,
decode(f.action, 'H', sum(cnt.due_cnt), 'R', (-1)*sum(cnt.due_cnt),
'DUE', sum(case when cnt.time_id = f.time_id then cnt.due_cnt else 0 end)) due_count,
decode(f.action, 'H', sum(cnt.past_due_cnt), 'R', (-1)*sum(cnt.past_due_cnt),
'DUE', sum(case when cnt.time_id = f.time_id then cnt.past_due_cnt else 0 end)) past_due_count
from (select distinct org_id, supplier_id, time_id, period_type_id, invoice_id, action
from FII_AP_HLIA_IB_MV where action in ('H', 'R', 'DUE') and period_type_id = 1) f, FII.FII_AP_DUE_COUNTS_B cnt,
(select distinct invoice_id,time_id,period_type_id
from FII.FII_AP_PAY_SCHED_B a WHERE action = 'PAYMENT'
) a
where cnt.invoice_id = f.invoice_id
and cnt.period_type_id = f.period_type_id
and cnt.time_id <= f.time_id
and a.invoice_id(+) = cnt.invoice_id
and a.time_id(+) = cnt.time_id
and a.period_type_id(+) = cnt.period_type_id
and (
(a.invoice_id IS NULL)
OR
((cnt.due_cnt >0 or cnt.past_due_cnt > 0 )
AND
a.invoice_id IS NOT NULL
)
)
group by f.org_id, f.supplier_id, f.time_id, f.action) f, FII.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))