MATERIALIZED VIEW: APPS.FII_AR_RCT_AGING_AGRT_MV

Source

SELECT  /*+ use_hash(b) use_hash(c) */
    	  b.time_id                            time_id,
    	  b.period_type_id                     period_type_id,
    	  c.parent_party_id                    parent_party_id,
    	  c.next_level_party_id                party_id,
    	  -2                                   cust_account_id,
    	  b.collector_id                       collector_id,
    	  b.org_id                             org_id,
    	  b.gid                                gid,
          SUM(b.aging_bucket_1_amount_func)    aging_bucket_1_amount_func,
          SUM(b.aging_bucket_1_amount_prim)    aging_bucket_1_amount_prim,
          SUM(b.aging_bucket_1_amount_sec)     aging_bucket_1_amount_sec,
          SUM(b.aging_bucket_1_count)          aging_bucket_1_count,
          SUM(b.aging_bucket_2_amount_func)    aging_bucket_2_amount_func,
          SUM(b.aging_bucket_2_amount_prim)    aging_bucket_2_amount_prim,
          SUM(b.aging_bucket_2_amount_sec)     aging_bucket_2_amount_sec,
          SUM(b.aging_bucket_2_count)          aging_bucket_2_count,
          SUM(b.aging_bucket_3_amount_func)    aging_bucket_3_amount_func,
          SUM(b.aging_bucket_3_amount_prim)    aging_bucket_3_amount_prim,
          SUM(b.aging_bucket_3_amount_sec)     aging_bucket_3_amount_sec,
          SUM(b.aging_bucket_3_count)          aging_bucket_3_count,
          SUM(b.UNID_amount_func)              UNID_amount_func,
          SUM(b.UNID_amount_prim)              UNID_amount_prim,
          SUM(b.UNID_amount_sec)               UNID_amount_sec,
          SUM(b.UNAPP_amount_func)             UNAPP_amount_func,
          SUM(b.UNAPP_amount_prim)             UNAPP_amount_prim,
          SUM(b.UNAPP_amount_sec)              UNAPP_amount_sec,
          SUM(b.total_unapplied_amount_func)   total_unapplied_amount_func,
          SUM(b.total_unapplied_amount_prim)   total_unapplied_amount_prim,
          SUM(b.total_unapplied_amount_sec)    total_unapplied_amount_sec,
          SUM(b.total_unapplied_count)         total_unapplied_count,
          COUNT(b.aging_bucket_1_amount_func)  aging_bucket_1_amt_func_ct,
          COUNT(b.aging_bucket_1_amount_prim)  aging_bucket_1_amt_prim_ct,
          COUNT(b.aging_bucket_1_amount_sec)   aging_bucket_1_amt_sec_ct,
          COUNT(b.aging_bucket_1_count)        aging_bucket_1_count_ct,
          COUNT(b.aging_bucket_2_amount_func)  aging_bucket_2_amt_func_ct,
          COUNT(b.aging_bucket_2_amount_prim)  aging_bucket_2_amt_prim_ct,
          COUNT(b.aging_bucket_2_amount_sec)   aging_bucket_2_amt_sec_ct,
          COUNT(b.aging_bucket_2_count)        aging_bucket_2_count_ct,
          COUNT(b.aging_bucket_3_amount_func)  aging_bucket_3_amt_func_ct,
          COUNT(b.aging_bucket_3_amount_prim)  aging_bucket_3_amt_prim_ct,
          COUNT(b.aging_bucket_3_amount_sec)   aging_bucket_3_amt_sec_ct,
          COUNT(b.aging_bucket_3_count)        aging_bucket_3_count_ct,
          COUNT(b.UNID_amount_func)            UNID_amount_func_ct,
          COUNT(b.UNID_amount_prim)            UNID_amount_prim_ct,
          COUNT(b.UNID_amount_sec)             UNID_amount_sec_ct,
          COUNT(b.UNAPP_amount_func)           UNAPP_amount_func_ct,
          COUNT(b.UNAPP_amount_prim)           UNAPP_amount_prim_ct,
          COUNT(b.UNAPP_amount_sec)            UNAPP_amount_sec_ct,
          COUNT(b.total_unapplied_amount_func) total_unapplied_amt_func_ct,
          COUNT(b.total_unapplied_amount_prim) total_unapplied_amt_prim_ct,
          COUNT(b.total_unapplied_amount_sec)  total_unapplied_amt_sec_ct,
          COUNT(b.total_unapplied_count)       total_unapplied_count_ct,
          COUNT(*)                             ct
  FROM fii_ar_rct_aging_base_mv b,
       FII.FII_CUSTOMER_HIERARCHIES c
  WHERE
       not (c.parent_party_id = c.child_party_id and c.next_level_is_leaf_flag = 'Y') and
       c.child_party_id = b.party_id and
       c.is_hierarchical_flag = 'Y'
  GROUP BY b.time_id,
    	   b.period_type_id,
    	   c.parent_party_id,
    	   c.next_level_party_id,
    	   b.collector_id,
     	   b.org_id,
     	   b.gid