[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AP_LIA_B_MV
Source
select /* 12.0: bug#4526784 */ 1 marker, f.rowid row_id, null ps_row_id,
f.org_id org_id,
f.supplier_id supplier_id,
f.time_id time_id,
f.amount_remaining_b unpaid_amt_b,
f.prim_amount_remaining prim_unpaid_amt,
f.sec_amount_remaining sec_unpaid_amt,
0 unpaid_count,
0 due_count,
f.past_due_amount_b past_due_amt_b,
f.prim_past_due_amount prim_past_due_amt,
f.sec_past_due_amount sec_past_due_amt,
0 past_due_count,
f.payment_amount_b payment_amt_b,
f.prim_payment_amount prim_payment_amt,
f.sec_payment_amount sec_payment_amt,
decode(f.action, 'CREATION', f.discount_available_b, 0) discount_offered_b,
decode(f.action, 'CREATION', f.prim_discount_available, 0) prim_discount_offered,
decode(f.action, 'CREATION', f.sec_discount_available, 0) sec_discount_offered,
f.discount_available_b discount_remaining_b,
f.prim_discount_available prim_discount_remaining,
f.sec_discount_available sec_discount_remaining,
f.discount_taken_b discount_taken_b,
f.prim_discount_taken prim_discount_taken,
f.sec_discount_taken sec_discount_taken,
f.discount_lost_b discount_lost_b,
f.prim_discount_lost prim_discount_lost,
f.sec_discount_lost sec_discount_lost,
f.due_bucket1_b due_bucket1_b,
f.prim_due_bucket1 prim_due_bucket1,
f.sec_due_bucket1 sec_due_bucket1,
0 due_bucket1_count,
f.due_bucket2_b due_bucket2_b,
f.prim_due_bucket2 prim_due_bucket2,
f.sec_due_bucket2 sec_due_bucket2,
0 due_bucket2_count,
f.due_bucket3_b due_bucket3_b,
f.prim_due_bucket3 prim_due_bucket3,
f.sec_due_bucket3 sec_due_bucket3,
0 due_bucket3_count,
f.past_due_bucket1_b past_due_bucket1_b,
f.prim_past_due_bucket1 prim_past_due_bucket1,
f.sec_past_due_bucket1 sec_past_due_bucket1,
0 past_due_bucket1_count,
f.past_due_bucket2_b past_due_bucket2_b,
f.prim_past_due_bucket2 prim_past_due_bucket2,
f.sec_past_due_bucket2 sec_past_due_bucket2,
0 past_due_bucket2_count,
f.past_due_bucket3_b past_due_bucket3_b,
f.prim_past_due_bucket3 prim_past_due_bucket3,
f.sec_past_due_bucket3 sec_past_due_bucket3,
0 past_due_bucket3_count,
f.on_time_payment_amt_b on_time_payment_amt_b,
f.prim_on_time_payment_amt prim_on_time_payment_amt,
f.sec_on_time_payment_amt sec_on_time_payment_amt,
f.late_payment_amt_b late_payment_amt_b,
f.prim_late_payment_amt prim_late_payment_amt,
f.sec_late_payment_amt sec_late_payment_amt,
(f.amount_remaining_b - f.past_due_amount_b) * to_number(to_char(f.due_date,'J')) wt_open_due_amt_b,
(f.prim_amount_remaining - f.prim_past_due_amount) * to_number(to_char(f.due_date,'J')) wt_open_due_amt_prim_g,
(f.sec_amount_remaining - f.sec_past_due_amount) * to_number(to_char(f.due_date,'J')) wt_open_due_amt_sec_g,
f.past_due_amount_b * to_number(to_char(f.due_date,'J')) wt_open_past_due_amt_b,
f.prim_past_due_amount * to_number(to_char(f.due_date,'J')) wt_open_past_due_amt_prim_g,
f.sec_past_due_amount * to_number(to_char(f.due_date,'J')) wt_open_past_due_amt_sec_g
from FII.FII_AP_PAY_SCHED_B f
where f.action <> 'PREPAYMENT'
and f.period_type_id = 1
union all
select /* 12.0: bug#4526784 */ 2 marker, f.rowid row_id, null ps_row_id,
f.org_id org_id,
f.supplier_id supplier_id,
to_number(to_char(f.entered_date,'j')) time_id,
0 unpaid_amt_b,
0 prim_unpaid_amt,
0 sec_unpaid_amt,
1 unpaid_count,
0 due_count,
0 past_due_amt_b,
0 prim_past_due_amt,
0 sec_past_due_amt,
0 past_due_count,
0 payment_amt_b,
0 prim_payment_amt,
0 sec_payment_amt,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
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 due_bucket1_b,
0 prim_due_bucket1,
0 sec_due_bucket1,
0 due_bucket1_count,
0 due_bucket2_b,
0 prim_due_bucket2,
0 sec_due_bucket2,
0 due_bucket2_count,
0 due_bucket3_b,
0 prim_due_bucket3,
0 sec_due_bucket3,
0 due_bucket3_count,
0 past_due_bucket1_b,
0 prim_past_due_bucket1,
0 sec_past_due_bucket1,
0 past_due_bucket1_count,
0 past_due_bucket2_b,
0 prim_past_due_bucket2,
0 sec_past_due_bucket2,
0 past_due_bucket2_count,
0 past_due_bucket3_b,
0 prim_past_due_bucket3,
0 sec_past_due_bucket3,
0 past_due_bucket3_count,
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 wt_open_due_amt_b,
0 wt_open_due_amt_prim_g,
0 wt_open_due_amt_sec_g,
0 wt_open_past_due_amt_b,
0 wt_open_past_due_amt_prim_g,
0 wt_open_past_due_amt_sec_g
from FII.FII_AP_INVOICE_B f
where f.invoice_amount <> 0
and f.invoice_type <> 'PREPAYMENT'
union all
select /* 12.0: bug#4526784 */ 3 marker, f.rowid row_id, null ps_row_id,
f.org_id org_id,
f.supplier_id supplier_id,
f.time_id time_id,
0 unpaid_amt_b,
0 prim_unpaid_amt,
0 sec_unpaid_amt,
0 unpaid_count,
f.due_cnt due_count,
0 past_due_amt_b,
0 prim_past_due_amt,
0 sec_past_due_amt,
f.past_due_cnt past_due_count,
0 payment_amt_b,
0 prim_payment_amt,
0 sec_payment_amt,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
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 due_bucket1_b,
0 prim_due_bucket1,
0 sec_due_bucket1,
0 due_bucket1_count,
0 due_bucket2_b,
0 prim_due_bucket2,
0 sec_due_bucket2,
0 due_bucket2_count,
0 due_bucket3_b,
0 prim_due_bucket3,
0 sec_due_bucket3,
0 due_bucket3_count,
0 past_due_bucket1_b,
0 prim_past_due_bucket1,
0 sec_past_due_bucket1,
0 past_due_bucket1_count,
0 past_due_bucket2_b,
0 prim_past_due_bucket2,
0 sec_past_due_bucket2,
0 past_due_bucket2_count,
0 past_due_bucket3_b,
0 prim_past_due_bucket3,
0 sec_past_due_bucket3,
0 past_due_bucket3_count,
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 wt_open_due_amt_b,
0 wt_open_due_amt_prim_g,
0 wt_open_due_amt_sec_g,
0 wt_open_past_due_amt_b,
0 wt_open_past_due_amt_prim_g,
0 wt_open_past_due_amt_sec_g
from FII.FII_AP_DUE_COUNTS_B f
where f.period_type_id = 1
union all
select /* 12.0: bug#4526784 */ 4 marker, f.rowid row_id, null ps_row_id,
f.org_id org_id,
f.supplier_id supplier_id,
f.time_id time_id,
0 unpaid_amt_b,
0 prim_unpaid_amt,
0 sec_unpaid_amt,
0 unpaid_count,
0 due_count,
0 past_due_amt_b,
0 prim_past_due_amt,
0 sec_past_due_amt,
0 past_due_count,
0 payment_amt_b,
0 prim_payment_amt,
0 sec_payment_amt,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
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 due_bucket1_b,
0 prim_due_bucket1,
0 sec_due_bucket1,
f.due_bucket1_cnt due_bucket1_count,
0 due_bucket2_b,
0 prim_due_bucket2,
0 sec_due_bucket2,
f.due_bucket2_cnt due_bucket2_count,
0 due_bucket3_b,
0 prim_due_bucket3,
0 sec_due_bucket3,
f.due_bucket3_cnt due_bucket3_count,
0 past_due_bucket1_b,
0 prim_past_due_bucket1,
0 sec_past_due_bucket1,
f.past_due_bucket1_cnt past_due_bucket1_count,
0 past_due_bucket2_b,
0 prim_past_due_bucket2,
0 sec_past_due_bucket2,
f.past_due_bucket2_cnt past_due_bucket2_count,
0 past_due_bucket3_b,
0 prim_past_due_bucket3,
0 sec_past_due_bucket3,
f.past_due_bucket3_cnt past_due_bucket3_count,
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 wt_open_due_amt_b,
0 wt_open_due_amt_prim_g,
0 wt_open_due_amt_sec_g,
0 wt_open_past_due_amt_b,
0 wt_open_past_due_amt_prim_g,
0 wt_open_past_due_amt_sec_g
from FII.FII_AP_AGING_BKTS_B f
where f.period_type_id = 1
union all
select /* 12.0: bug#4526784 */ 5 marker, f.rowid f_row_id, ps.rowid ps_row_id,
f.org_id org_id,
f.supplier_id supplier_id,
to_number(to_char(f.fully_paid_date,'j')) time_id,
0 unpaid_amt_b,
0 prim_unpaid_amt,
0 sec_unpaid_amt,
0 unpaid_count,
0 due_count,
0 past_due_amt_b,
0 prim_past_due_amt,
0 sec_past_due_amt,
0 past_due_count,
(-1)*ps.payment_amount_b payment_amt_b,
(-1)*ps.prim_payment_amount prim_payment_amt,
(-1)*ps.sec_payment_amount sec_payment_amt,
(-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,
0 discount_remaining_b,
0 prim_discount_remaining,
0 sec_discount_remaining,
(-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 due_bucket1_b,
0 prim_due_bucket1,
0 sec_due_bucket1,
0 due_bucket1_count,
0 due_bucket2_b,
0 prim_due_bucket2,
0 sec_due_bucket2,
0 due_bucket2_count,
0 due_bucket3_b,
0 prim_due_bucket3,
0 sec_due_bucket3,
0 due_bucket3_count,
0 past_due_bucket1_b,
0 prim_past_due_bucket1,
0 sec_past_due_bucket1,
0 past_due_bucket1_count,
0 past_due_bucket2_b,
0 prim_past_due_bucket2,
0 sec_past_due_bucket2,
0 past_due_bucket2_count,
0 past_due_bucket3_b,
0 prim_past_due_bucket3,
0 sec_past_due_bucket3,
0 past_due_bucket3_count,
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 wt_open_due_amt_b,
0 wt_open_due_amt_prim_g,
0 wt_open_due_amt_sec_g,
0 wt_open_past_due_amt_b,
0 wt_open_past_due_amt_prim_g,
0 wt_open_past_due_amt_sec_g
from FII.FII_AP_INVOICE_B f, FII.FII_AP_PAY_SCHED_B ps
where f.fully_paid_date is not null
and f.invoice_type <> 'PREPAYMENT'
and ps.period_type_id = 1
and f.invoice_id = ps.invoice_id
union all
select /* 12.0: bug#4526784 */ 6 marker, f.rowid f_row_id, null ps_row_id,
f.org_id org_id,
f.supplier_id supplier_id,
to_number(to_char(f.fully_paid_date,'j')) time_id,
0 unpaid_amt_b,
0 prim_unpaid_amt,
0 sec_unpaid_amt,
-1 unpaid_count,
0 due_count,
0 past_due_amt_b,
0 prim_past_due_amt,
0 sec_past_due_amt,
0 past_due_count,
0 payment_amt_b,
0 prim_payment_amt,
0 sec_payment_amt,
0 discount_offered_b,
0 prim_discount_offered,
0 sec_discount_offered,
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 due_bucket1_b,
0 prim_due_bucket1,
0 sec_due_bucket1,
0 due_bucket1_count,
0 due_bucket2_b,
0 prim_due_bucket2,
0 sec_due_bucket2,
0 due_bucket2_count,
0 due_bucket3_b,
0 prim_due_bucket3,
0 sec_due_bucket3,
0 due_bucket3_count,
0 past_due_bucket1_b,
0 prim_past_due_bucket1,
0 sec_past_due_bucket1,
0 past_due_bucket1_count,
0 past_due_bucket2_b,
0 prim_past_due_bucket2,
0 sec_past_due_bucket2,
0 past_due_bucket2_count,
0 past_due_bucket3_b,
0 prim_past_due_bucket3,
0 sec_past_due_bucket3,
0 past_due_bucket3_count,
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 wt_open_due_amt_b,
0 wt_open_due_amt_prim_g,
0 wt_open_due_amt_sec_g,
0 wt_open_past_due_amt_b,
0 wt_open_past_due_amt_prim_g,
0 wt_open_past_due_amt_sec_g
from FII.FII_AP_INVOICE_B f
where f.fully_paid_date is not null
and f.invoice_type <> 'PREPAYMENT'