SELECT '1' AS REP_SQ,
       SR.SOURCE_SYS_NM AS SOURCE_NM,
       UR.REINVEST_SQ AS RECORD_ID,
       UR.COLL_ACCT_ID AS COLL_ACCT,
       '' AS TRADE_SEQ,
       UR.SOURCE_CTPY_CD AS SOURCE_CODE,
       NVL(UR.DEAL_CONTRACT_AMT, 0.00) AS TRADE_AMT,
       NVL(UR.SEC_MKT_VAL_AMT, 0.00) AS COLL_AMT,
       '' AS COMMENTS,
       UR.SEC_ID AS SECURITY_ID,
       UR.SEC_ISSUE_CTRY_CD AS SEC_COUNTRY_CODE,
       UR.SEC_CCY_ID AS SEC_CCY_ID,
       UR.SEC_TYPE_CD AS SEC_TYP_CD,
       UR.SEC_INVEST_TYPE_CD AS SEC_INVST_TYP,
       0.00 AS SHR_QUANTITY,
       0.00 As CLEAN_PRICE,
       UR.SEC_DIRTY_PRICE AS DIRTY_PRICE,
       EM.ERROR_ID AS ERROR_ID,
       EM.ERROR_SEVERITY AS ERROR_STATUS,
       EM.ERROR_DESC_TXT AS ERROR_DESC
  FROM CHECKPOINT_ERROR         CE,
       UNFORMATTED_REINVESTMENT UR,
       ERROR_MASTER   EM,
       SOURCE SR
 Where CE.CALENDAR_DT = '27-Aug-2008'
   AND UR.CALENDAR_DT = '27-Aug-2008'
   AND CE.RECORD_SQ = UR.REINVEST_SQ
   AND CE.ERROR_ID = EM.ERROR_ID
   AND SR.SOURCE_SQ = UR.SOURCE_SQ
   AND CE.TABLE_NM_TXT = 'UNFORMATTED_REINVESTMENT'
   AND UR.STATUS_CD <> 'X'
   AND CE.ERROR_ID NOT IN (808, 809, 810, 811)
UNION ALL
SELECT '2' AS REP_SQ,
       SR.SOURCE_SYS_NM AS SOURCE_NM,
       UD.DEAL_SQ AS RECORD_ID,
       UD.COLL_ACCT_ID AS COLL_ACCT,
       UD.TRIPARTY_DEAL_SEQ_NUM AS TRADE_SEQ,
       UD.SOURCE_CTPY_CD AS SOURCE_CODE,
       NVL(UD.DEAL_CONTRACT_AMT, 0.00) AS TRADE_AMT,
       NVL(UD.DEAL_COLL_AMT, 0.00) AS COLL_AMT,
       UD.COLL_ACCT_NM As COMMENTS,
       '' AS SECURITY_ID,
       '' AS SEC_COUNTRY_CODE,
       '' AS SEC_CCY_ID,
       '' AS SEC_TYP_CD,
       '' AS SEC_INVST_TYP,
       0.00 AS SHR_QUANTITY,
       0.00 As CLEAN_PRICE,
       0.00 AS DIRTY_PRICE,
       EM.ERROR_ID AS ERROR_ID,
       EM.ERROR_SEVERITY AS ERROR_STATUS,
       EM.ERROR_DESC_TXT AS ERROR_DESC
  FROM CHECKPOINT_ERROR CE,
       (SELECT RECORD_SQ, COUNT(*) AS REC_COUNT
          FROM CHECKPOINT_ERROR
         WHERE CALENDAR_DT = '15-Aug-2008'
           AND TABLE_NM_TXT = 'UNFORMATTED_DEAL'
         GROUP BY RECORD_SQ) CH,
       UNFORMATTED_DEAL UD,
       ERROR_MASTER EM,
       SOURCE SR
 WHERE CE.CALENDAR_DT = '15-Aug-2008'
   AND UD.CALENDAR_DT = '15-Aug-2008'
   AND CE.RECORD_SQ = CH.RECORD_SQ
   AND CE.RECORD_SQ = UD.DEAL_SQ
   AND CE.TABLE_NM_TXT = 'UNFORMATTED_DEAL'
   AND CE.ERROR_ID = EM.ERROR_ID
   AND CE.ERROR_ID IN
       (DECODE(CH.REC_COUNT,
               1,
               CE.ERROR_ID,
               DECODE(CE.ERROR_ID, 102, '', CE.ERROR_ID)))
   AND CE.ERROR_ID NOT IN (803, 804, 805, 806)
   AND SR.SOURCE_SQ = UD.SOURCE_SQ
   AND UD.STATUS_CD <> 'X'
   ORDER BY REP_SQ,
          RECORD_ID,
          ERROR_ID

SQL> explain plan for select 1 from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1546270724

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

8 rows selected.

SQL> 

   AND CE.ERROR_ID IN
       (DECODE(CH.REC_COUNT,
               1,
               CE.ERROR_ID,
               DECODE(CE.ERROR_ID, 102, '', CE.ERROR_ID)))

   AND (CE.ERROR_ID != 102 OR CH.REC_COUNT = 1)

AND (CE.ERROR_ID != 102 OR CH.REC_COUNT = 1)
AND CE.ERROR_ID IS NOT NULL