[Home] [Help]
MATERIALIZED VIEW: APPS.FII_AR_RCT_AGING_INTR_MV
Source
SELECT /*+ use_hash(ag) use_hash(fday) use_hash(acc) use_hash(coll) use_hash(mkt) */
fday.ent_year_id ent_year_id,
fday.ent_qtr_id ent_qtr_id,
fday.ent_period_id ent_period_id,
fday.week_id ent_week_id,
fday.report_date_julian report_date_julian,
GROUPING_ID(mv.party_id,
mv.party_id,
ag.bill_to_customer_id,
mv.collector_id,
ag.org_id,
mv.class_code,
mv.class_category,
fday.ent_year_id,
fday.ent_qtr_id,
fday.ent_period_id,
fday.week_id,
fday.report_date_julian) gid,
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,
mv.party_id parent_party_id,
mv.party_id party_id,
ag.bill_to_customer_id cust_account_id,
mv.collector_id collector_id,
ag.org_id org_id,
mv.class_code class_code,
mv.class_category class_category,
SUM(ag.aging_bucket_1_amount_func) aging_bucket_1_amount_func,
SUM(ag.aging_bucket_1_amount_prim) aging_bucket_1_amount_prim,
SUM(ag.aging_bucket_1_amount_sec) aging_bucket_1_amount_sec,
SUM(ag.aging_bucket_1_count) aging_bucket_1_count,
SUM(ag.aging_bucket_2_amount_func) aging_bucket_2_amount_func,
SUM(ag.aging_bucket_2_amount_prim) aging_bucket_2_amount_prim,
SUM(ag.aging_bucket_2_amount_sec) aging_bucket_2_amount_sec,
SUM(ag.aging_bucket_2_count) aging_bucket_2_count,
SUM(ag.aging_bucket_3_amount_func) aging_bucket_3_amount_func,
SUM(ag.aging_bucket_3_amount_prim) aging_bucket_3_amount_prim,
SUM(ag.aging_bucket_3_amount_sec) aging_bucket_3_amount_sec,
SUM(ag.aging_bucket_3_count) aging_bucket_3_count,
SUM(decode(bill_to_customer_id, -2,
ag.aging_bucket_1_amount_func
+ag.aging_bucket_2_amount_func
+ag.aging_bucket_3_amount_func,
0)) UNID_amount_func,
SUM(decode(bill_to_customer_id, -2,
ag.aging_bucket_1_amount_prim
+ag.aging_bucket_2_amount_prim
+ag.aging_bucket_3_amount_prim,
0)) UNID_amount_prim,
SUM(decode(bill_to_customer_id, -2,
ag.aging_bucket_1_amount_sec
+ag.aging_bucket_2_amount_sec
+ag.aging_bucket_3_amount_sec,
0)) UNID_amount_sec,
SUM(decode(bill_to_customer_id, -2,
0,
ag.aging_bucket_1_amount_func
+ag.aging_bucket_2_amount_func
+ag.aging_bucket_3_amount_func)) UNAPP_amount_func,
SUM(decode(bill_to_customer_id, -2,
0,
ag.aging_bucket_1_amount_prim
+ag.aging_bucket_2_amount_prim
+ag.aging_bucket_3_amount_prim)) UNAPP_amount_prim,
SUM(decode(bill_to_customer_id, -2,
0,
ag.aging_bucket_1_amount_sec
+ag.aging_bucket_2_amount_sec
+ag.aging_bucket_3_amount_sec)) UNAPP_amount_sec,
SUM(ag.aging_bucket_1_amount_func
+ag.aging_bucket_2_amount_func
+ag.aging_bucket_3_amount_func) total_unapplied_amount_func,
SUM(ag.aging_bucket_1_amount_prim
+ag.aging_bucket_2_amount_prim
+ag.aging_bucket_3_amount_prim) total_unapplied_amount_prim,
SUM(ag.aging_bucket_1_amount_sec
+ag.aging_bucket_2_amount_sec
+ag.aging_bucket_3_amount_sec) total_unapplied_amount_sec,
SUM(ag.total_unapplied_count) total_unapplied_count,
COUNT(ag.aging_bucket_1_amount_func) aging_bucket_1_amt_func_ct,
COUNT(ag.aging_bucket_1_amount_prim) aging_bucket_1_amt_prim_ct,
COUNT(ag.aging_bucket_1_amount_sec) aging_bucket_1_amt_sec_ct,
COUNT(ag.aging_bucket_1_count) aging_bucket_1_count_ct,
COUNT(ag.aging_bucket_2_amount_func) aging_bucket_2_amt_func_ct,
COUNT(ag.aging_bucket_2_amount_prim) aging_bucket_2_amt_prim_ct,
COUNT(ag.aging_bucket_2_amount_sec) aging_bucket_2_amt_sec_ct,
COUNT(ag.aging_bucket_2_count) aging_bucket_2_count_ct,
COUNT(ag.aging_bucket_3_amount_func) aging_bucket_3_amt_func_ct,
COUNT(ag.aging_bucket_3_amount_prim) aging_bucket_3_amt_prim_ct,
COUNT(ag.aging_bucket_3_amount_sec) aging_bucket_3_amt_sec_ct,
COUNT(ag.aging_bucket_3_count) aging_bucket_3_count_ct,
COUNT(decode(bill_to_customer_id, -2,
ag.aging_bucket_1_amount_func
+ag.aging_bucket_2_amount_func
+ag.aging_bucket_3_amount_func,
0)) UNID_amt_func_ct,
COUNT(decode(bill_to_customer_id, -2,
ag.aging_bucket_1_amount_prim
+ag.aging_bucket_2_amount_prim
+ag.aging_bucket_3_amount_prim,
0)) UNID_amt_prim_ct,
COUNT(decode(bill_to_customer_id, -2,
ag.aging_bucket_1_amount_sec
+ag.aging_bucket_2_amount_sec
+ag.aging_bucket_3_amount_sec,
0)) UNID_amt_sec_ct,
COUNT(decode(bill_to_customer_id, -2,
0,
ag.aging_bucket_1_amount_func
+ag.aging_bucket_2_amount_func
+ag.aging_bucket_3_amount_func)) UNAPP_amt_func_ct,
COUNT(decode(bill_to_customer_id, -2,
0,
ag.aging_bucket_1_amount_prim
+ag.aging_bucket_2_amount_prim
+ag.aging_bucket_3_amount_prim)) UNAPP_amt_prim_ct,
COUNT(decode(bill_to_customer_id, -2,
0,
ag.aging_bucket_1_amount_sec
+ag.aging_bucket_2_amount_sec
+ag.aging_bucket_3_amount_sec)) UNAPP_amt_sec_ct,
COUNT(ag.aging_bucket_1_amount_func
+ag.aging_bucket_2_amount_func
+ag.aging_bucket_3_amount_func) total_unapplied_amt_func_ct,
COUNT(ag.aging_bucket_1_amount_prim
+ag.aging_bucket_2_amount_prim
+ag.aging_bucket_3_amount_prim) total_unapplied_amt_prim_ct,
COUNT(ag.aging_bucket_1_amount_sec
+ag.aging_bucket_2_amount_sec
+ag.aging_bucket_3_amount_sec) total_unapplied_amt_sec_ct,
COUNT(ag.total_unapplied_count) total_unapplied_count_ct,
COUNT(*) ct
FROM FII.FII_AR_AGING_RECEIPTS ag,
FII.FII_TIME_DAY fday,
APPS.FII_AR_DIMENSIONS_MV mv
WHERE ag.time_id = fday.report_date_julian
AND ag.bill_to_customer_id = mv.cust_account_id
AND ag.bill_to_site_use_id = mv.site_use_id
GROUP BY ag.bill_to_customer_id,
ag.org_id,
mv.party_id,
mv.collector_id,
mv.class_code,
mv.class_category,
ROLLUP (fday.ent_year_id,
fday.ent_qtr_id,
fday.ent_period_id,
fday.week_id,
fday.report_date_julian)
-- HAVING fday.ent_year_id is not null