MATERIALIZED VIEW: APPS.FII_AR_BILLING_ACT_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,
    	  sum(b.INV_ba_amount_func)    INV_ba_amount_func,
    	  sum(b.INV_ba_amount_prim)    INV_ba_amount_prim,
    	  sum(b.INV_ba_amount_sec)     INV_ba_amount_sec,
    	  sum(b.INV_ba_count)          INV_ba_count,
    	  sum(b.DM_ba_amount_func)     DM_ba_amount_func,
    	  sum(b.DM_ba_amount_prim)     DM_ba_amount_prim,
    	  sum(b.DM_ba_amount_sec)      DM_ba_amount_sec,
    	  sum(b.DM_ba_count)           DM_ba_count,
    	  sum(b.CB_ba_amount_func)     CB_ba_amount_func,
    	  sum(b.CB_ba_amount_prim)     CB_ba_amount_prim,
    	  sum(b.CB_ba_amount_sec)      CB_ba_amount_sec,
    	  sum(b.CB_ba_count)           CB_ba_count,
    	  sum(b.BR_ba_amount_func)     BR_ba_amount_func,
    	  sum(b.BR_ba_amount_prim)     BR_ba_amount_prim,
    	  sum(b.BR_ba_amount_sec)      BR_ba_amount_sec,
    	  sum(b.BR_ba_count)           BR_ba_count,
    	  sum(b.DEP_ba_amount_func)    DEP_ba_amount_func,
    	  sum(b.DEP_ba_amount_prim)    DEP_ba_amount_prim,
    	  sum(b.DEP_ba_amount_sec)     DEP_ba_amount_sec,
    	  sum(b.DEP_ba_count)          DEP_ba_count,
    	  sum(b.CM_ba_amount_func)     CM_ba_amount_func,
    	  sum(b.CM_ba_amount_prim)     CM_ba_amount_prim,
    	  sum(b.CM_ba_amount_sec)      CM_ba_amount_sec,
    	  sum(b.CM_ba_count)           CM_ba_count,
     	  count(b.INV_ba_amount_func)  INV_ba_amount_func_ct,
    	  count(b.INV_ba_amount_prim)  INV_ba_amount_prim_ct,
    	  count(b.INV_ba_amount_sec)   INV_ba_amount_sec_ct,
    	  count(b.INV_ba_count)        INV_ba_count_ct,
    	  count(b.DM_ba_amount_func)   DM_ba_amount_func_ct,
    	  count(b.DM_ba_amount_prim)   DM_ba_amount_prim_ct,
    	  count(b.DM_ba_amount_sec)    DM_ba_amount_sec_ct,
    	  count(b.DM_ba_count)         DM_ba_count_ct,
    	  count(b.CB_ba_amount_func)   CB_ba_amount_func_ct,
    	  count(b.CB_ba_amount_prim)   CB_ba_amount_prim_ct,
    	  count(b.CB_ba_amount_sec)    CB_ba_amount_sec_ct,
    	  count(b.CB_ba_count)         CB_ba_count_ct,
    	  count(b.BR_ba_amount_func)   BR_ba_amount_func_ct,
    	  count(b.BR_ba_amount_prim)   BR_ba_amount_prim_ct,
    	  count(b.BR_ba_amount_sec)    BR_ba_amount_sec_ct,
    	  count(b.BR_ba_count)         BR_ba_count_ct,
    	  count(b.DEP_ba_amount_func)  DEP_ba_amount_func_ct,
    	  count(b.DEP_ba_amount_prim)  DEP_ba_amount_prim_ct,
    	  count(b.DEP_ba_amount_sec)   DEP_ba_amount_sec_ct,
    	  count(b.DEP_ba_count)        DEP_ba_count_ct,
    	  count(b.CM_ba_amount_func)   CM_ba_amount_func_ct,
    	  count(b.CM_ba_amount_prim)   CM_ba_amount_prim_ct,
    	  count(b.CM_ba_amount_sec)    CM_ba_amount_sec_ct,
    	  count(b.CM_ba_count)         CM_ba_count_ct,
          count(*)                     ct
  FROM fii_ar_billing_act_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