[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AP_HLIA_IB_MV
Source
SELECT /* 12.0: bug#4526784 */ org_id org_id,
supplier_id supplier_id,
invoice_id invoice_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,
hold_date,
release_date,
action action,
due_date,
SUM(amount_remaining_b) on_hold_amount_b,
SUM(prim_amount_remaining) on_hold_amount_prim_g,
SUM(sec_amount_remaining) on_hold_amount_sec_g,
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_amount_b) on_hold_past_due_amount_b,
SUM(prim_past_due_amount) on_hold_past_due_amount_prim_g,
SUM(sec_past_due_amount) on_hold_past_due_amount_sec_g,
SUM(discount_offered_b) on_hold_dis_offered_b,
SUM(prim_discount_offered) on_hold_dis_offered_prim_g,
SUM(sec_discount_offered) on_hold_dis_offered_sec_g,
SUM(discount_available_b) on_hold_dis_available_b,
SUM(prim_discount_available) on_hold_dis_available_prim_g,
SUM(sec_discount_available) on_hold_dis_available_sec_g,
SUM(discount_taken_b) on_hold_dis_taken_b,
SUM(prim_discount_taken) on_hold_dis_taken_prim_g,
SUM(sec_discount_taken) on_hold_dis_taken_sec_g,
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 hold.org_id org_id,
hold.supplier_id supplier_id,
hold.invoice_id invoice_id,
hold.hold_time_id time_id,
hold.hold_date hold_date,
nvl(hold.release_date,trunc(sysdate)) release_date,
'H' action,
ps.due_date,
ps.amount_remaining_b amount_remaining_b,
ps.prim_amount_remaining prim_amount_remaining,
ps.sec_amount_remaining sec_amount_remaining,
ps.payment_amount_b payment_amount_b,
ps.prim_payment_amount prim_payment_amount,
ps.sec_payment_amount sec_payment_amount,
ps.past_due_amount_b past_due_amount_b,
ps.prim_past_due_amount prim_past_due_amount,
ps.sec_past_due_amount sec_past_due_amount,
DECODE(ps.action, 'CREATION', ps.discount_available_b, 0) discount_offered_b,
DECODE(ps.action, 'CREATION', ps.prim_discount_available, 0) prim_discount_offered,
DECODE(ps.action, 'CREATION', ps.sec_discount_available, 0) sec_discount_offered,
ps.discount_available_b discount_available_b,
ps.prim_discount_available prim_discount_available,
ps.sec_discount_available sec_discount_available,
ps.discount_taken_b discount_taken_b,
ps.prim_discount_taken prim_discount_taken,
ps.sec_discount_taken sec_discount_taken,
ps.discount_lost_b discount_lost_b,
ps.prim_discount_lost prim_discount_lost,
ps.sec_discount_lost sec_discount_lost,
0 hold_count
FROM FII_AP_HHIST_IB_MV hold, FII.FII_AP_PAY_SCHED_B ps
WHERE ps.period_type_id = 1
AND (
ps.time_id < hold.hold_time_id or
(ps.time_id = hold.hold_time_id AND ps.action not in ('PAYMENT', 'PREPAYMENT')) or
(ps.time_id = hold.hold_time_id AND ps.action in ('PAYMENT', 'PREPAYMENT') and
(hold.release_time_id > hold.hold_time_id OR hold.release_time_id IS NULL))
)
AND hold.invoice_id = ps.invoice_id
UNION ALL
SELECT ps.org_id org_id,
ps.supplier_id supplier_id,
ps.invoice_id invoice_id,
ps.time_id time_id,
NULL hold_date,
NULL release_date,
ps.action action,
ps.due_date,
ps.amount_remaining_b amount_remaining_b,
ps.prim_amount_remaining prim_amount_remaining,
ps.sec_amount_remaining sec_amount_remaining,
0 payment_amount_b,
0 prim_payment_amount,
0 sec_payment_amount,
ps.past_due_amount_b past_due_amount_b,
ps.prim_past_due_amount prim_past_due_amount,
ps.sec_past_due_amount sec_past_due_amount,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
ps.discount_available_b discount_available_b,
ps.prim_discount_available prim_discount_available,
ps.sec_discount_available sec_discount_available,
ps.discount_taken_b discount_taken_b,
ps.prim_discount_taken prim_discount_taken,
ps.sec_discount_taken sec_discount_taken,
ps.discount_lost_b discount_lost_b,
ps.prim_discount_lost prim_discount_lost,
ps.sec_discount_lost sec_discount_lost,
0 hold_count
FROM fii_ap_hhist_ib_mv hold, FII.FII_AP_PAY_SCHED_B ps
WHERE ps.period_type_id = 1
AND ps.time_id > hold.hold_time_id
AND (
ps.time_id < nvl(hold.release_time_id, ps.time_id) or
(ps.time_id = nvl(hold.release_time_id, ps.time_id) AND ps.action not in ('PAYMENT', 'PREPAYMENT'))
)
AND ps.invoice_id = hold.invoice_id
UNION ALL
SELECT hold.org_id org_id,
hold.supplier_id supplier_id,
hold.invoice_id invoice_id,
hold.release_time_id time_id,
hold.hold_date hold_date,
hold.release_date release_date,
'R' action,
ps.due_date,
(-1)*ps.amount_remaining_b amount_remaining_b,
(-1)*ps.prim_amount_remaining prim_amount_remaining,
(-1)*ps.sec_amount_remaining sec_amount_remaining,
(-1)*ps.payment_amount_b payment_amount_b,
(-1)*ps.prim_payment_amount prim_payment_amount,
(-1)*ps.sec_payment_amount sec_payment_amount,
(-1)*ps.past_due_amount_b past_due_amount_b,
(-1)*ps.prim_past_due_amount prim_past_due_amount,
(-1)*ps.sec_past_due_amount sec_past_due_amount,
(-1)*DECODE(ps.action, 'CREATION', ps.discount_available_b, 0) discount_offered_b,
(-1)*DECODE(ps.action, 'CREATION', ps.prim_discount_available, 0) prim_discount_offered,
(-1)*DECODE(ps.action, 'CREATION', ps.sec_discount_available, 0) sec_discount_offered,
(-1)*ps.discount_available_b discount_available_b,
(-1)*ps.prim_discount_available prim_discount_available,
(-1)*ps.sec_discount_available sec_discount_available,
(-1)*ps.discount_taken_b discount_taken_b,
(-1)*ps.prim_discount_taken prim_discount_taken,
(-1)*ps.sec_discount_taken sec_discount_taken,
(-1)*ps.discount_lost_b discount_lost_b,
(-1)*ps.prim_discount_lost prim_discount_lost,
(-1)*ps.sec_discount_lost sec_discount_lost,
0 hold_count
FROM FII_AP_HHIST_IB_MV hold, FII.FII_AP_PAY_SCHED_B ps
WHERE ps.period_type_id = 1
AND (
ps.time_id < hold.release_time_id or
(ps.time_id = hold.release_time_id AND ps.action not in ('PAYMENT', 'PREPAYMENT'))
)
AND hold.invoice_id = ps.invoice_id
UNION ALL
SELECT f.org_id org_id,
f.supplier_id supplier_id,
f.invoice_id invoice_id,
f.time_id time_id,
NULL hold_date,
NULL release_date,
NULL action,
NULL due_date,
0 amount_remaining_b,
0 prim_amount_remaining,
0 sec_amount_remaining,
0 payment_amount_b,
0 prim_payment_amount,
0 sec_payment_amount,
0 past_due_amount_b,
0 prim_past_due_amount,
0 sec_past_due_amount,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
0 discount_available_b,
0 prim_discount_available,
0 sec_discount_available,
0 discount_taken_b,
0 prim_discount_taken,
0 sec_discount_taken,
0 discount_lost_b,
0 prim_discount_lost,
0 sec_discount_lost,
1 hold_count
FROM FII.FII_AP_INV_HOLDS_B f
UNION ALL
SELECT f.org_id org_id,
f.supplier_id supplier_id,
f.invoice_id invoice_id,
to_number(to_char(f.release_date, 'j')) time_id,
NULL hold_date,
NULL release_date,
NULL action,
NULL due_date,
0 amount_remaining_b,
0 prim_amount_remaining,
0 sec_amount_remaining,
0 payment_amount_b,
0 prim_payment_amount,
0 sec_payment_amount,
0 past_due_amount_b,
0 prim_past_due_amount,
0 sec_past_due_amount,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
0 discount_available_b,
0 prim_discount_available,
0 sec_discount_available,
0 discount_taken_b,
0 prim_discount_taken,
0 sec_discount_taken,
0 discount_lost_b,
0 prim_discount_lost,
0 sec_discount_lost,
-1 hold_count
FROM FII.FII_AP_INV_HOLDS_B f
WHERE f.release_date IS NOT NULL) f, fii_time_day fday
WHERE f.time_id = fday.report_date_julian
GROUP BY org_id, supplier_id, invoice_id, action, due_date, hold_date, release_date,
rollup (fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian)