[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AP_LIA_I_MV
Source
select /* 12.0: bug#4526784 */ f.org_id org_id,
f.supplier_id supplier_id,
fday.ent_year_id,
fday.ent_qtr_id,
fday.ent_period_id,
fday.week_id,
fday.report_date_julian ,
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,
count(*) count_all,
sum(f.unpaid_amt_b) open_amt_b,
count(f.unpaid_amt_b) open_amt_b_cnt,
sum(f.prim_unpaid_amt) open_amt_prim_g,
count(f.prim_unpaid_amt) open_amt_prim_g_cnt,
sum(f.sec_unpaid_amt) open_amt_sec_g,
count(f.sec_unpaid_amt) open_amt_sec_g_cnt,
sum(f.unpaid_count) open_count,
count(f.unpaid_count) open_count_cnt,
sum(f.due_count) open_due_count,
count(f.due_count) open_due_count_cnt,
sum(f.past_due_amt_b) open_past_due_amt_b,
count(f.past_due_amt_b) open_past_due_amt_b_cnt,
sum(f.prim_past_due_amt) open_past_due_amt_prim_g,
count(f.prim_past_due_amt) open_past_due_amt_prim_g_cnt,
sum(f.sec_past_due_amt) open_past_due_amt_sec_g,
count(f.sec_past_due_amt) open_past_due_amt_sec_g_cnt,
sum(f.past_due_count) open_past_due_count,
count(f.past_due_count) open_past_due_count_cnt,
sum(f.payment_amt_b) open_payment_amt_b,
count(f.payment_amt_b) open_payment_amt_b_cnt,
sum(f.prim_payment_amt) open_payment_amt_prim_g,
count(f.prim_payment_amt) open_payment_amt_prim_g_cnt,
sum(f.sec_payment_amt) open_payment_amt_sec_g,
count(f.sec_payment_amt) open_payment_amt_sec_g_cnt,
sum(f.discount_offered_b) open_discount_offered_b,
count(f.discount_offered_b) open_discount_offered_b_cnt,
sum(f.prim_discount_offered) open_discount_offered_prim_g,
count(f.prim_discount_offered) open_dis_offered_prim_g_cnt,
sum(f.sec_discount_offered) open_discount_offered_sec_g,
count(f.sec_discount_offered) open_dis_offered_sec_g_cnt,
sum(f.discount_remaining_b) open_discount_remaining_b,
count(f.discount_remaining_b) open_dis_remaining_b_cnt,
sum(f.prim_discount_remaining) open_discount_remaining_prim_g,
count(f.prim_discount_remaining) open_dis_remaining_prim_g_cnt,
sum(f.sec_discount_remaining) open_discount_remaining_sec_g,
count(f.sec_discount_remaining) open_dis_remaining_sec_g_cnt,
sum(f.discount_taken_b) open_discount_taken_b,
count(f.discount_taken_b) open_dis_taken_b_cnt,
sum(f.prim_discount_taken) open_discount_taken_prim_g,
count(f.prim_discount_taken) open_dis_taken_prim_g_cnt,
sum(f.sec_discount_taken) open_discount_taken_sec_g,
count(f.sec_discount_taken) open_dis_taken_sec_g_cnt,
sum(f.discount_lost_b) open_discount_lost_b,
count(f.discount_lost_b) open_dis_lost_b_cnt,
sum(f.prim_discount_lost) open_discount_lost_prim_g,
count(f.prim_discount_lost) open_dis_lost_prim_g_cnt,
sum(f.sec_discount_lost) open_discount_lost_sec_g,
count(f.sec_discount_lost) open_dis_lost_sec_g_cnt,
sum(f.due_bucket1_b) open_due_bucket1_b,
count(f.due_bucket1_b) open_due_bkt1_b_cnt,
sum(f.prim_due_bucket1) open_due_bucket1_prim_g,
count(f.prim_due_bucket1) open_due_bkt1_prim_g_cnt,
sum(f.sec_due_bucket1) open_due_bucket1_sec_g,
count(f.sec_due_bucket1) open_due_bkt1_sec_g_cnt,
sum(f.due_bucket1_count) open_due_bucket1_count,
count(f.due_bucket1_count) open_due_bkt1_count_cnt,
sum(f.due_bucket2_b) open_due_bucket2_b,
count(f.due_bucket2_b) open_due_bkt2_b_cnt,
sum(f.prim_due_bucket2) open_due_bucket2_prim_g,
count(f.prim_due_bucket2) open_due_bkt2_prim_g_cnt,
sum(f.sec_due_bucket2) open_due_bucket2_sec_g,
count(f.sec_due_bucket2) open_due_bkt2_sec_g_cnt,
sum(f.due_bucket2_count) open_due_bucket2_count,
count(f.due_bucket2_count) open_due_bkt2_count_cnt,
sum(f.due_bucket3_b) open_due_bucket3_b,
count(f.due_bucket3_b) open_due_bkt3_b_cnt,
sum(f.prim_due_bucket3) open_due_bucket3_prim_g,
count(f.prim_due_bucket3) open_due_bkt3_prim_g_cnt,
sum(f.sec_due_bucket3) open_due_bucket3_sec_g,
count(f.sec_due_bucket3) open_due_bkt3_sec_g_cnt,
sum(f.due_bucket3_count) open_due_bucket3_count,
count(f.due_bucket3_count) open_due_bkt3_count_cnt,
sum(f.past_due_bucket1_b) open_past_due_bucket1_b,
count(f.past_due_bucket1_b) open_past_due_bkt1_b_cnt,
sum(f.prim_past_due_bucket1) open_past_due_bucket1_prim_g,
count(f.prim_past_due_bucket1) open_past_due_bkt1_prim_g_cnt,
sum(f.sec_past_due_bucket1) open_past_due_bucket1_sec_g,
count(f.sec_past_due_bucket1) open_past_due_bkt1_sec_g_cnt,
sum(f.past_due_bucket1_count) open_past_due_bucket1_count,
count(f.past_due_bucket1_count) open_past_due_bkt1_count_cnt,
sum(f.past_due_bucket2_b) open_past_due_bucket2_b,
count(f.past_due_bucket2_b) open_past_due_bkt2_b_cnt,
sum(f.prim_past_due_bucket2) open_past_due_bucket2_prim_g,
count(f.prim_past_due_bucket2) open_past_due_bkt2_prim_g_cnt,
sum(f.sec_past_due_bucket2) open_past_due_bucket2_sec_g,
count(f.sec_past_due_bucket2) open_past_due_bkt2_sec_g_cnt,
sum(f.past_due_bucket2_count) open_past_due_bucket2_count,
count(f.past_due_bucket2_count) open_past_due_bkt2_count_cnt,
sum(f.past_due_bucket3_b) open_past_due_bucket3_b,
count(f.past_due_bucket3_b) open_past_due_bkt3_b_cnt,
sum(f.prim_past_due_bucket3) open_past_due_bucket3_prim_g,
count(f.prim_past_due_bucket3) open_past_due_bkt3_prim_g_cnt,
sum(f.sec_past_due_bucket3) open_past_due_bucket3_sec_g,
count(f.sec_past_due_bucket3) open_past_due_bkt3_sec_g_cnt,
sum(f.past_due_bucket3_count) open_past_due_bucket3_count,
count(f.past_due_bucket3_count) open_past_due_bkt3_count_cnt,
sum(f.on_time_payment_amt_b) on_time_payment_amt_b,
count(f.on_time_payment_amt_b) on_time_payment_amt_b_cnt,
sum(f.prim_on_time_payment_amt) on_time_payment_amt_prim_g,
count(f.prim_on_time_payment_amt) on_time_payment_amt_prim_g_cnt,
sum(f.sec_on_time_payment_amt) on_time_payment_amt_sec_g,
count(f.sec_on_time_payment_amt) on_time_payment_amt_sec_g_cnt,
sum(f.late_payment_amt_b) late_payment_amt_b,
count(f.late_payment_amt_b) late_payment_amt_b_cnt,
sum(f.prim_late_payment_amt) late_payment_amt_prim_g,
count(f.prim_late_payment_amt) late_payment_amt_prim_g_cnt,
sum(f.sec_late_payment_amt) late_payment_amt_sec_g,
count(f.sec_late_payment_amt) late_payment_amt_sec_g_cnt,
sum(wt_open_due_amt_b) wt_open_due_amt_b,
count(wt_open_due_amt_b) wt_open_due_amt_b_cnt,
sum(wt_open_due_amt_prim_g) wt_open_due_amt_prim_g,
count(wt_open_due_amt_prim_g) wt_open_due_amt_prim_g_cnt,
sum(wt_open_due_amt_sec_g) wt_open_due_amt_sec_g,
count(wt_open_due_amt_sec_g) wt_open_due_amt_sec_g_cnt,
sum(wt_open_past_due_amt_b) wt_open_past_due_amt_b,
count(wt_open_past_due_amt_b) wt_open_past_due_amt_b_cnt,
sum(wt_open_past_due_amt_prim_g) wt_open_past_due_amt_prim_g,
count(wt_open_past_due_amt_prim_g) wt_op_past_due_amt_prim_g_cnt,
sum(wt_open_past_due_amt_sec_g) wt_open_past_due_amt_sec_g,
count(wt_open_past_due_amt_sec_g) wt_open_past_due_amt_sec_g_cnt,
grouping_id(f.org_id, f.supplier_id, fday.ent_year_id, fday.ent_qtr_id, fday.ent_period_id, fday.week_id, fday.report_date_julian) gid1
from FII_AP_LIA_B_MV 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)