[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AP_IVATY_B_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,
sum(f.base_amount) invoice_amt_b,
count(f.base_amount) invoice_amt_bc,
sum(f.prim_amount) prim_invoice_amt,
count(f.prim_amount) prim_invoice_amtc,
sum(f.sec_amount) sec_invoice_amt,
count(f.sec_amount) sec_invoice_amtc,
count(*) invoice_entered_count,
sum(f.dist_count) distribution_count,
count(f.dist_count) distribution_countc,
sum(decode(f.e_invoices_flag, 'Y',f.base_amount,0)) e_invoice_amt_b,
count(decode(f.e_invoices_flag, 'Y',f.base_amount,0)) e_invoice_amt_bc,
sum(decode(f.e_invoices_flag, 'Y',f.prim_amount,0)) prim_e_invoice_amt,
count(decode(f.e_invoices_flag, 'Y',f.prim_amount,0)) prim_e_invoice_amtc,
sum(decode(f.e_invoices_flag, 'Y',f.sec_amount,0)) sec_e_invoice_amt,
count(decode(f.e_invoices_flag, 'Y',f.sec_amount,0)) sec_e_invoice_amtc,
sum(decode(f.e_invoices_flag, 'Y',1,0)) e_invoice_count,
count(decode(f.e_invoices_flag, 'Y',1,0)) e_invoice_countc,
sum(decode(f.e_invoices_flag, 'Y', f.dist_count, 0)) e_distribution_count,
count(decode(f.e_invoices_flag, 'Y', f.dist_count, 0)) e_distribution_countc,
sum(decode(f.source,'XML GATEWAY',1,0)) xml_count,
count(decode(f.source,'XML GATEWAY',1,0)) xml_countc,
sum(decode(f.source,'EDI GATEWAY',1,0)) edi_count,
count(decode(f.source,'EDI GATEWAY',1,0)) edi_countc,
sum(decode(f.source,'ERS',1,0)) ers_count,
count(decode(f.source,'ERS',1,0)) ers_countc,
sum(decode(f.source,'ISP',1,0)) isp_count,
count(decode(f.source,'ISP',1,0)) isp_countc,
sum(decode(f.source,'ASBN',1,0)) ASBN_count,
count(decode(f.source,'ASBN',1,0)) ASBN_countc,
sum(decode(f.source,'Manual Invoice Entry',0,'INVOICE GATEWAY',0,'RECURRING INVOICE',0,'XML GATEWAY',0,
'EDI GATEWAY',0,'ERS',0,'ISP',0,'ASBN',0,1)) other_integrated_count,
count(decode(f.source,'Manual Invoice Entry',0,'INVOICE GATEWAY',0,'RECURRING INVOICE',0,'XML GATEWAY',0,
'EDI GATEWAY',0,'ERS',0,'ISP',0,'ASBN',0,1)) other_integrated_countc,
sum(decode(f.invoice_type,'STANDARD',1,0)) standard_count,
count(decode(f.invoice_type,'STANDARD',1,0)) standard_countc,
sum(decode(f.invoice_type,'AWT',1,0)) withholding_count,
count(decode(f.invoice_type,'AWT',1,0)) withholding_countc,
sum(decode(f.invoice_type,'PREPAYMENT',1,0)) prepayment_count,
count(decode(f.invoice_type,'PREPAYMENT',1,0)) prepayment_countc,
sum(decode(f.invoice_type,'CREDIT',1,0)) credit_count,
count(decode(f.invoice_type,'CREDIT',1,0)) credit_countc,
sum(decode(f.invoice_type,'DEBIT',1,0)) debit_count,
count(decode(f.invoice_type,'DEBIT',1,0)) debit_countc,
sum(decode(f.invoice_type,'MIXED',1,0)) mixed_count,
count(decode(f.invoice_type,'MIXED',1,0)) mixed_countc,
sum(decode(f.invoice_type,'INTEREST',1,0)) interest_count,
count(decode(f.invoice_type,'INTEREST',1,0)) interest_countc,
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.FII_AP_INVOICE_B f, FII.FII_TIME_DAY fday
where f.cancel_flag = 'N'
and f.entered_date = fday.report_date
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)