SELECT NVL(RESEARCH_JOB_ID,0) Research_Job,
  NVL(EXCEPTION_FILE_BLOCK,0) Research_Block,
  NVL(EXCEPTION_FILE_SEQ,0) Research_Seq,
  NVL(EXCEPTION_FILE_REL_SEQ,0) Research_Rel_Seq ,
  a.BLK_NUM Block,
  a.SEQ_NUM Sequence,
  a.REL_SEQ_NUM Relative ,
  NVL(ltrim(rtrim(FLD_6)),'0') Serial,
  NVL(FLD_4,'0') Routing,
  NVL(ltrim(rtrim(FLD_3)),'0') Account,
  NVL(ltrim(rtrim(Old_FLD3)),'0') OldAccount,
  NVL(FLD_2,0) TRAN_CD,
  ltrim(TO_CHAR(pls_util.to_num(NVL(NUMERIC_AMT,'0')),'9999999999999.99')) Amount,
  a.EXCEPTION_CD EXCEPTION ,
  NVL(a.BOFD_ID,'') Bank,
  NVL(a.ENDPOINT_ID,0) Endpoint,
  RETURN_RT_NUM ENDPOINT_RT,
  DECODE(PAID_STATUS,'Y','Paid','Return') Paid,
  NVL(d.CODE_DESC,' ') ExceptionReason,
  DECODE(Initial_PAID_STATUS,'Y','Paid','Return') InitialPaidStatus,
  DECODE(RETURN_STATUS,'U','Unable','S','Suspect','R','Completed') Status,
  NVL(action_id,-1) Action,
  NVL(NOTES,' ') Notes,
  NVL(TO_CHAR(EXCEPTION_FILE_DATE,'mmddyyyy'),'') Research_Bus_Date,
  TO_CHAR(NVL(No_POST_Indicator,1)) POST,
  NVL(AMOUNT_ADJUSTMENT,'N') AMOUNT_ADJUSTMENT,
  NVL(CHARGE_ADJUSTMENT,'N') CHARGE_ADJUSTMENT,
  NVL(CREDIT_ADJUSTMENT,'N') CREDIT_ADJUSTMENT,
  NVL(CR_IND,'D') CREDIT_DEBIT ,
  NVL(BATCH_TRACK_NUMBER,0) BATCH_TRACK_NUMBER ,
  NVL(FIMP,0) FIMP,
  NVL(TO_CHAR(OLOD_ACCOUNT_TYPE),'0') ACCOUNT_TYPE ,
  NVL(TO_CHAR(OLOD_ACCOUNT_CLASS),'0') ACCOUNT_CLASS,
  DECODE(UPPER(NVL(OLOD_POSTING_SYSTEM_IND, 'G')),'C','Consumer (CAS)','S','BP Account','2','Custom','3','GPS','G','Citi Gold','H', 'Hogan','L','Balcon','B','BBS','Citi Gold') POSTING_SYSTEM_INDICATOR ,
  DECODE(UPPER(NVL(OLOD_POSTING_SYSTEM_IND, 'G')),'C',0,'S',1,'2',2,'3',3,'G',4,'H', 5,'L',99,'B',100,4) POSTING_SYSTEM_INDICATOR1 ,
  NVL(STOP_INDICATOR,0) STOP_INDICATOR ,
  NVL(c.STOP_PAYEE_NAME,'-') STOP_PAYEE_NAME,
  NVL(c.STOP_AMOUNT,0) STOP_AMOUNT,
  NVL(c.STOP_CHECK_SERIAL_NUMBER,0) STOP_CHECK_SERIAL_NUMBER,
  NVL(REPOST_ACCOUNT_NUMBER,'0') REPOST_ACCOUNT_NUMBER,
  NVL(REPOST_ACCOUNT_CLASS,0) REPOST_ACCOUNT_CLASS ,
  NVL(overDraft_Indicator,0) ODIND,
  NVL(File_Origin_ID,0) FileOrgInd ,
  NVL(trim(DECODE(NVL(trim(upper(PAID_RETURN_DECISIONER)), OLOD_APPROVER_OPERATOR),'DEFAULT',OLOD_APPROVER_OPERATOR,upper(PAID_RETURN_DECISIONER))), upper(PAID_RETURN_DECISIONER)) operator,
  NVL(OLOD_CURRENT_LEDGER_BALANCE,0) LDGR_BLNC ,
  NVL(OLOD_UNCOLLECTED_USED_AMOUNT,0) UNCOLLECTEDUSEDAMT,
  NVL(OLOD_DEBIT_CARD_HOLD_AMOUNT,0) DBCRDHOLDAMT ,
  NVL(OLOD_HARD_HOLD_AMOUNT,0) HRDHOLDAMT,
  NVL(Rej_cd,1) RejCD,
  NVL(POSTING_TRAN_CODE,'0') POSTINGTRANCODE,
  STOP_REQUEST_ID STOPDATE,
  NVL(DECODE(OLOD_RETURN_FLAG, ' ', 'Y',OLOD_RETURN_FLAG), '-') OLODRETURNFLAG,
  NVL(EXCEPTION_CD_LIST,' ') EXCEPTIONCDLIST,
  NVL(STOP_SEQ_NUM,0) STOPSEQNUM,
  to_number(NVL(ltrim(rtrim(FLD_3)),'0')) Account1,
  NVL(FLD_5,'0') Pos44,
  NVL(RAW_SERIAL,' ') RAW_SERIAL,
  NVL(RAW_ROUTING,' ') RAW_ROUTING,
  NVL(RAW_ONUS,' ') RAW_ONUS ,
  DECODE(NVL(SUB_EXCEPTION_DESCRIPTION,' '),' ',' ',NO_POST_REASON_CODE
  || ' - '
  || SUB_EXCEPTION_DESCRIPTION) NoPostReasonDesc ,
  DECODE(NVL(OLOD_INSERTED_ITEM,'N'),'N',NVL(ERETURN_INSERTED_ITEM,'N'),'Y') AddWindowItem,
  NVL(image_flag,'M') ImgFlg
FROM SUB_EXCEPTIONCODE_MASTER s,
  ITEM_11172009_1 a,
  BOFD_MASTER b,
  STOP_PAYMENT c ,
  APPLICATION_MASTER d ,
  endpoint_master e
WHERE (s.SUB_EXCEPTION_CD(+) = a.NO_POST_REASON_CODE
AND a.EXCEPTION_CD           = s.EXCEPTION_CD(+))
AND a.EXCEPTION_CD           =d.CODE(+)
AND a.BOFD_ID                =b.BOFD_ID(+)
AND a.ENDPOINT_ID            = e.ENDPOINT_ID(+)
AND a.BLK_NUM                =13
AND a.BCH_NUM                = 120051
AND FIMP                     = 41
AND a.BLK_NUM                =c.BLK_NUM(+)
AND a.BCH_NUM                =c.BCH_NUM(+)
AND a.SEQ_NUM                =c.SEQ_NUM(+)
AND a.BUS_DATE               = c.BUS_DATE(+)
AND a.REL_SEQ_NUM            = c.REL_SEQ_NUM(+)
AND (c.stop_seq_num         IS NULL
OR c.stop_seq_num            =
  (SELECT MIN(stop_seq_num)
  FROM stop_payment
  WHERE stop_request_id IN
    (SELECT MAX(STOP_REQUEST_ID)
    FROM STOP_PAYMENT d
    WHERE a.BLK_NUM   =d.BLK_NUM(+)
    AND a.BCH_NUM     =d.BCH_NUM(+)
    AND a.SEQ_NUM     =d.SEQ_NUM(+)
    AND a.BUS_DATE    = d.BUS_DATE(+)
    AND a.REL_SEQ_NUM = d.REL_SEQ_NUM(+)
    )
  ))
ORDER BY POSTING_SYSTEM_INDICATOR1 ASC,
  Account1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1435219534

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                          |     2 |   722 |   102   (3)| 00:00:02 |
|   1 |  SORT ORDER BY                     |                          |     2 |   722 |   102   (3)| 00:00:02 |
|*  2 |   FILTER                           |                          |       |       |            |       |
|*  3 |    HASH JOIN OUTER                 |                          |     2 |   722 |   101   (2)| 00:00:02 |
|*  4 |     HASH JOIN OUTER                |                          |     2 |   640 |    97   (2)| 00:00:02 |
|*  5 |      HASH JOIN OUTER               |                          |     2 |   586 |    94   (2)| 00:00:02 |
|   6 |       NESTED LOOPS OUTER           |                          |     2 |   580 |    90   (0)| 00:00:02 |
|*  7 |        HASH JOIN RIGHT OUTER       |                          |     2 |   574 |    90   (0)| 00:00:02 |
|   8 |         TABLE ACCESS BY INDEX ROWID| STOP_PAYMENT             |    47 |  3102 |    78   (0)| 00:00:01 |
|*  9 |          INDEX SKIP SCAN           | STOP_PAYMENTINDX         |    47 |       |    44   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| ITEM_11172009_1          |    65 | 14365 |    12   (0)| 00:00:01 |
|* 11 |          INDEX SKIP SCAN           | ITEM_INDEX3              |    66 |       |     8   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN           | PK_BOFD_MASTER           |     1 |     3 |     0   (0)| 00:00:01 |
|  13 |       TABLE ACCESS FULL            | ENDPOINT_MASTER          |     6 |    18 |     3   (0)| 00:00:01 |
|  14 |      TABLE ACCESS FULL             | APPLICATION_MASTER       |    17 |   459 |     3   (0)| 00:00:01 |
|  15 |     TABLE ACCESS FULL              | SUB_EXCEPTIONCODE_MASTER |    39 |  1599 |     3   (0)| 00:00:01 |
|  16 |    SORT AGGREGATE                  |                          |     1 |    16 |            |       |
|* 17 |     FILTER                         |                          |       |       |            |       |
|  18 |      TABLE ACCESS FULL             | STOP_PAYMENT             |   100K|  1572K|   222   (1)| 00:00:03 |
|  19 |      SORT AGGREGATE                |                          |     1 |    35 |            |       |
|* 20 |       TABLE ACCESS BY INDEX ROWID  | STOP_PAYMENT             |     1 |    35 |     4   (0)| 00:00:01 |
|* 21 |        INDEX RANGE SCAN            | STOP_PAYMENTINDX         |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C"."STOP_SEQ_NUM" IS NULL OR "C"."STOP_SEQ_NUM"= (SELECT MIN("STOP_SEQ_NUM") FROM
              "STOP_PAYMENT" "STOP_PAYMENT" WHERE "STOP_REQUEST_ID"= (SELECT MAX("STOP_REQUEST_ID") FROM
              "STOP_PAYMENT" "D" WHERE "D"."SEQ_NUM"(+)=:B1 AND "D"."BCH_NUM"(+)=:B2 AND "D"."BLK_NUM"(+)=:B3 AND
              "D"."BUS_DATE"(+)=:B4 AND "D"."REL_SEQ_NUM"(+)=:B5)))
   3 - access("A"."NO_POST_REASON_CODE"=TO_NUMBER("S"."SUB_EXCEPTION_CD"(+)) AND
              "A"."EXCEPTION_CD"=TO_NUMBER("S"."EXCEPTION_CD"(+)))
   4 - access("A"."EXCEPTION_CD"="D"."CODE"(+))
   5 - access("A"."ENDPOINT_ID"="E"."ENDPOINT_ID"(+))
   7 - access("A"."BLK_NUM"="C"."BLK_NUM"(+) AND "A"."BCH_NUM"="C"."BCH_NUM"(+) AND
              "A"."SEQ_NUM"="C"."SEQ_NUM"(+) AND "A"."BUS_DATE"="C"."BUS_DATE"(+) AND
              "A"."REL_SEQ_NUM"="C"."REL_SEQ_NUM"(+))
   9 - access("C"."BLK_NUM"(+)=13 AND "C"."BCH_NUM"(+)=120051)
       filter("C"."BCH_NUM"(+)=120051 AND "C"."BLK_NUM"(+)=13)
  10 - filter("FIMP"=41)
  11 - access("A"."BLK_NUM"=13 AND "A"."BCH_NUM"=120051)
       filter("A"."BCH_NUM"=120051 AND "A"."BLK_NUM"=13)
  12 - access("A"."BOFD_ID"="B"."BOFD_ID"(+))
  17 - filter("STOP_REQUEST_ID"= (SELECT MAX("STOP_REQUEST_ID") FROM "STOP_PAYMENT" "D" WHERE
              "D"."SEQ_NUM"(+)=:B1 AND "D"."BCH_NUM"(+)=:B2 AND "D"."BLK_NUM"(+)=:B3 AND "D"."BUS_DATE"(+)=:B4 AND
              "D"."REL_SEQ_NUM"(+)=:B5))
  20 - filter("D"."REL_SEQ_NUM"(+)=:B1)
  21 - access("D"."BUS_DATE"(+)=:B1 AND "D"."BLK_NUM"(+)=:B2 AND "D"."BCH_NUM"(+)=:B3 AND
              "D"."SEQ_NUM"(+)=:B4)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      79352  consistent gets
          0  physical reads
          0  redo size
       7589  bytes sent via SQL*Net to client
       2468  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         75  rows processed