SELECT cr.credit_request_id AS credit_request_id,
       cr.borrower_id AS borrower_id, cr.status_code AS status_code,
       1 AS prev_month_count, 0 AS new_count, 0 AS reinitiated_count,
       0 AS repaid_cancelled_count, 0 AS change_to_existing,
       0 AS prev_year_count, 0 AS fiscal_year_count, 0 AS refer_to_slsb_count,
       cph.cms_portfolio_num AS cms_portfolio_num,
       cph.branch_cluster_transit_num AS branch_transit_num,
       cph.district_bm_transit_num AS district_bm_transit_num,
       cph.district_transit_num AS district_transit_num,
       cph.market_transit_num AS market_transit_num,
       cph.region_transit_num AS region_transit_num,
       cph.total_rm_transit_num AS total_rm_transit_num,
       NVL (cr.authorized_credit_limit_amt,
            cr.proposed_credit_limit_amt
           ) AS prev_month_total_auth_amt,
       ROUND
          ((SELECT /*+ INDEX(wf,indx_o_fac_id) */  SUM (fac_os_bal_cad) AS facility_balance_amt
                FROM mv_rpt_d_wcdw_facility wf, mv_rpt_facility f
               WHERE wf.icap_original_facility_id =
                                   NVL (f.original_facility_id, f.facility_id)
                 AND f.credit_request_id = cr.credit_request_id
                 AND NVL (f.is_deleted, 0) = 0
                 AND wf.snapshot_date =
                        (SELECT MAX (wf1.snapshot_date)
                           FROM mv_rpt_d_wcdw_facility wf1
                          WHERE wf1.icap_original_facility_id =
                                                  wf.icap_original_facility_id))
           + NVL((SELECT SUM (fac_os_bal_cad) AS facility_balance_amt
                                  FROM mv_rpt_d_wcdw_facility wf
                                 WHERE wf.icap_original_facility_id = -99
                                   AND wf.snapshot_date =
                                          (SELECT MAX (wf1.snapshot_date)
                                             FROM mv_rpt_d_wcdw_facility wf1
                                            WHERE wf1.icap_original_facility_id = wf.icap_original_facility_id
                                              AND wf1.icap_original_facility_id = -99)
                                   AND EXISTS (
                                          SELECT 1
                                            FROM mv_rpt_crdt_rqst_participant crp
                                           WHERE (crp.customer_id IN (
                                                     SELECT c.customer_id
                                                       FROM mv_rpt_customer c
                                                      WHERE c.customer_identity_id = wf.icap_cust_identity_id)
                                                 )
                                             AND crp.credit_request_id = cr.credit_request_id
                                             AND crp.credit_request_role_code = 'B')),0)) AS prev_month_os_crdlmt_amt,
       NULL AS new_total_auth_amt, NULL AS new_os_crdlmt_amt,
       NULL AS reinitiate_total_auth_amt, NULL AS reinitiate_os_crdlmt_amt,
       NULL AS repaid_total_auth_amt, NULL AS repaid_os_crdlmt_amt,
       NULL AS change_to_exist_auth_amt,
       NULL AS change_to_exist_os_crdlmt_amt,
       NULL AS prev_year_total_auth_amt, NULL AS prev_year_os_crdlmt_amt,
       NULL AS fiscal_year_total_auth_amt, NULL AS fiscal_year_os_crdlmt_amt,
       NULL AS refer_to_slsb_total_auth_amt,
       NULL AS refer_to_slsb_os_crdlmt_amt,
       cph.branch_cluster_name AS branch_cluster_name,
       cph.district_bm_name AS district_bm_name,
       cph.district_name AS district_name, cph.market_name AS market_name,
       cph.region_name AS region_name
  FROM mv_rpt_credit_request cr,
       cms_portfolio_hierarchy cph,
       mv_rpt_cms_portfolio cp,
       mv_rpt_borrower b
 WHERE cr.borrower_id = b.borrower_id
   AND b.ocp_portfolio_id = cp.ocp_portfolio_id
   AND cph.cms_portfolio_num = cp.cms_portfolio_num
   AND TRUNC (cr.activation_dttm) <= :v_rpt_prev_month_end_date
   AND (   cr.status_code = 'ACTIVE'
        OR (    cr.status_code = 'INACTIVE'
            AND TRUNC (cr.cancel_dttm) > :v_rpt_prev_month_end_date
           )
       )
   AND cr.performing_ind = '1'

Plan hash value: 1854681316
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                              |     1 |   301 |  5345   (2)| 00:01:05 |
|   1 |  SORT AGGREGATE                       |                              |     1 |    73 |            |          |
|*  2 |   FILTER                              |                              |       |       |            |          |
|   3 |    MAT_VIEW ACCESS BY INDEX ROWID     | MV_RPT_D_WCDW_FACILITY       |     3 |    84 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                              |   196 | 14308 |  3581   (3)| 00:00:43 |
|*  5 |      MAT_VIEW ACCESS FULL             | MV_RPT_FACILITY              |    57 |  2565 |  3410   (3)| 00:00:41 |
|*  6 |      INDEX RANGE SCAN                 | INDX_O_FAC_ID                |     3 |       |     0   (0)| 00:00:01 |
|   7 |    SORT AGGREGATE                     |                              |     1 |    15 |            |          |
|   8 |     MAT_VIEW ACCESS BY INDEX ROWID    | MV_RPT_D_WCDW_FACILITY       |     3 |    45 |     4   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                 | INDX_O_FAC_ID                |     3 |       |     1   (0)| 00:00:01 |
|  10 |      SORT AGGREGATE                   |                              |     1 |    62 |            |          |
|* 11 |       FILTER                          |                              |       |       |            |          |
|* 12 |        HASH JOIN                      |                              |  2572K|   152M|   696   (8)| 00:00:09 |
|  13 |         VIEW                          | VW_SQ_1                      | 16038 |   344K|   329   (3)| 00:00:04 |
|  14 |          SORT GROUP BY                |                              | 16038 |   328K|   329   (3)| 00:00:04 |
|* 15 |           MAT_VIEW ACCESS FULL        | MV_RPT_D_WCDW_FACILITY       | 16038 |   328K|   329   (3)| 00:00:04 |
|* 16 |         MAT_VIEW ACCESS FULL          | MV_RPT_D_WCDW_FACILITY       | 16038 |   626K|   329   (3)| 00:00:04 |
|  17 |        NESTED LOOPS                   |                              |     1 |    33 |     5   (0)| 00:00:01 |
|* 18 |         MAT_VIEW ACCESS BY INDEX ROWID| MV_RPT_CRDT_RQST_PARTICIPANT |     1 |    14 |     4   (0)| 00:00:01 |
|* 19 |          INDEX RANGE SCAN             | XPK_CREDIT_REQUEST_PARTICIPA |     2 |       |     3   (0)| 00:00:01 |
|* 20 |         MAT_VIEW ACCESS BY INDEX ROWID| MV_RPT_CUSTOMER              |     1 |    19 |     1   (0)| 00:00:01 |
|* 21 |          INDEX UNIQUE SCAN            | XPK_CUSTOMER                 |     1 |       |     0   (0)| 00:00:01 |
|* 22 |  HASH JOIN                            |                              |     1 |   301 |  5345   (2)| 00:01:05 |
|  23 |   MAT_VIEW ACCESS FULL                | MV_RPT_CMS_PORTFOLIO         |  3578 | 93028 |    13   (0)| 00:00:01 |
|  24 |   MERGE JOIN CARTESIAN                |                              | 17240 |  4629K|  5331   (2)| 00:01:04 |
|  25 |    NESTED LOOPS                       |                              |     1 |   111 |  5180   (2)| 00:01:03 |
|* 26 |     MAT_VIEW ACCESS FULL              | MV_RPT_CREDIT_REQUEST        |     1 |    92 |  5179   (2)| 00:01:03 |
|  27 |     MAT_VIEW ACCESS BY INDEX ROWID    | MV_RPT_BORROWER              |     1 |    19 |     1   (0)| 00:00:01 |
|* 28 |      INDEX UNIQUE SCAN                | XPK_BORROWER                 |     1 |       |     0   (0)| 00:00:01 |
|  29 |    BUFFER SORT                        |                              | 19622 |  3142K|  5330   (2)| 00:01:04 |
|  30 |     TABLE ACCESS FULL                 | CMS_PORTFOLIO_HIERARCHY      | 19622 |  3142K|   151   (2)| 00:00:02 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("WF"."SNAPSHOT_DATE"= (SELECT /*+ */ MAX("WF1"."SNAPSHOT_DATE") FROM "MV_RPT_D_WCDW_FACILITY" 
              "WF1" WHERE "WF1"."ICAP_ORIGINAL_FACILITY_ID"=:B1))
   5 - filter("F"."CREDIT_REQUEST_ID"=:B1 AND NVL("F"."IS_DELETED",0)=0)
   6 - access("WF"."ICAP_ORIGINAL_FACILITY_ID"=NVL("F"."ORIGINAL_FACILITY_ID","F"."FACILITY_ID"))
   9 - access("WF1"."ICAP_ORIGINAL_FACILITY_ID"=:B1)
  11 - filter( EXISTS (SELECT /*+ */ 0 FROM "MV_RPT_CRDT_RQST_PARTICIPANT" "CRP","MV_RPT_CUSTOMER" "C" WHERE 
              "CRP"."CUSTOMER_ID"="C"."CUSTOMER_ID" AND "C"."CUSTOMER_IDENTITY_ID"=TO_NUMBER(:B1) AND 
              "CRP"."CREDIT_REQUEST_ID"=:B2 AND "CRP"."CREDIT_REQUEST_ROLE_CODE"='B'))
  12 - access("WF"."SNAPSHOT_DATE"="VW_COL_1" AND "ICAP_ORIGINAL_FACILITY_ID"="WF"."ICAP_ORIGINAL_FACILITY_ID"
              )
  15 - filter("WF1"."ICAP_ORIGINAL_FACILITY_ID"=(-99))
  16 - filter("WF"."ICAP_ORIGINAL_FACILITY_ID"=(-99))
  18 - filter("CRP"."CREDIT_REQUEST_ROLE_CODE"='B')
  19 - access("CRP"."CREDIT_REQUEST_ID"=:B1)
  20 - filter("C"."CUSTOMER_IDENTITY_ID"=TO_NUMBER(:B1))
  21 - access("CRP"."CUSTOMER_ID"="C"."CUSTOMER_ID")
  22 - access("B"."OCP_PORTFOLIO_ID"="CP"."OCP_PORTFOLIO_ID" AND 
              "CP"."CMS_PORTFOLIO_NUM"=TO_NUMBER("CPH"."CMS_PORTFOLIO_NUM"))
  26 - filter("CR"."PERFORMING_IND"='1' AND ("CR"."STATUS_CODE"='ACTIVE' OR "CR"."STATUS_CODE"='INACTIVE' AND 
              TRUNC(INTERNAL_FUNCTION("CR"."CANCEL_DTTM"))>:V_RPT_PREV_MONTH_END_DATE) AND 
              TRUNC(INTERNAL_FUNCTION("CR"."ACTIVATION_DTTM"))<=:V_RPT_PREV_MONTH_END_DATE)
  28 - access("CR"."BORROWER_ID"="B"."BORROWER_ID")