MATERIALIZED VIEW: APPS.FII_AR_DISPUTES_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.current_dispute_amount_func)    current_dispute_amount_func,
      	  SUM(b.current_dispute_amount_prim)    current_dispute_amount_prim,
      	  SUM(b.current_dispute_amount_sec)     current_dispute_amount_sec,
      	  SUM(b.current_dispute_count)          current_dispute_count,
      	  SUM(b.past_due_dispute_amount_func)   past_due_dispute_amount_func,
      	  SUM(b.past_due_dispute_amount_prim)   past_due_dispute_amount_prim,
      	  SUM(b.past_due_dispute_amount_sec)    past_due_dispute_amount_sec,
      	  SUM(b.past_due_dispute_count)         past_due_dispute_count,
      	  SUM(b.total_dispute_amount_func)      total_dispute_amount_func,
      	  SUM(b.total_dispute_amount_prim)      total_dispute_amount_prim,
      	  SUM(b.total_dispute_amount_sec)       total_dispute_amount_sec,
      	  COUNT(b.current_dispute_amount_func)  current_dispute_amt_func_ct,
      	  COUNT(b.current_dispute_amount_prim)  current_dispute_amt_prim_ct,
      	  COUNT(b.current_dispute_amount_sec)   current_dispute_amt_sec_ct,
      	  COUNT(b.current_dispute_count)        current_dispute_count_ct,
      	  COUNT(b.past_due_dispute_amount_func) past_due_dispute_amt_func_ct,
      	  COUNT(b.past_due_dispute_amount_prim) past_due_dispute_amt_prim_ct,
      	  COUNT(b.past_due_dispute_amount_sec)  past_due_dispute_amt_sec_ct,
      	  COUNT(b.past_due_dispute_count)       past_due_dispute_count_ct,
      	  COUNT(b.total_dispute_amount_func)    total_dispute_amt_func_ct,
      	  COUNT(b.total_dispute_amount_prim)    total_dispute_amt_prim_ct,
      	  COUNT(b.total_dispute_amount_sec)     total_dispute_amt_sec_ct,
          COUNT(*)                              ct
  FROM fii_ar_disputes_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