SELECT SUBSTR(chr.hier_code, 1, 5),
      tmp_txm.post_acct,
      SUBSTR(tmp_txm.txn_descr, 1, 5),
      SUM
      (
        CASE
          WHEN tmp_txm.txn_code IN ('01', '10') THEN 1
                WHEN tmp_txm.txn_code IN ('02', '20') THEN -1
                ELSE 0
        END
        *
        CASE
          WHEN cr.acct_type IN ('01', '02', '03', '04', '05', '06') THEN 1
                WHEN cr.acct_type IN ('11', '12', '13', '14') THEN -1
                ELSE 0
        END
        *
        cr.signal_revert
        *
        tmp_txm.txn_amount
      )
    FROM
      tac txn
        INNER JOIN
      chr
        ON
          tmp_txm.ent = chr.ent AND
          tmp_txm.sub_ent = chr.sub_ent AND
          tmp_txm.ctr = chr.ctr_detail
        INNER JOIN
      cr
        ON tmp_txm.post_acct = cr.post_acct
        INNER JOIN
      er
        ON
          chr.ent = er.ent AND
          chr.sub_ent = er.sub_ent
    WHERE
      tmp_txm.eff_date BETWEEN TO_DATE('2005'||'01', 'YYYYMM') AND LAST_DAY(TO_DATE('2005'||'01', 'YYYYMM')) AND
      chr.hier_tbl_num = '11111' AND
      chr.sum_flag = 'DEL AND
      chr.ctr_or_hier = chr.ctr_detail AND
      chr.hier_code BETWEEN 'A' AND 'Z' AND
      cr.rel_type = ' ' AND
      cr.acct_id = 'MGT' AND
      cr.acct_lvl = '9' AND
      er.roll_ent = '123' AND
      er.roll_sub_ent = '111'
    GROUP BY
      SUBSTR(chr.hier_code, 1, 5),
      tmp_txm.post_acct,
      SUBSTR(tmp_txm.txn_descr, 1, 5)
    HAVING
      SUM
      (
        CASE
          WHEN tmp_txm.txn_code IN ('01', '10') THEN 1
                WHEN tmp_txm.txn_code IN ('02', '20') THEN -1
                ELSE 0
        END
        *
        CASE
          WHEN cr.acct_type IN ('01', '02', '03', '04', '05', '06') THEN 1
                WHEN cr.acct_type IN ('11', '12', '13', '14') THEN -1
                ELSE 0
        END
        *
        cr.signal_revert
        *
        tmp_txm.txn_amount
      ) <> 0


EXPLAIN PLAN for the query:
--------------------------
SQL> select OBJECT_NAME, operation from plan_table;

OBJECT_NAME                    OPERATION
------------------------------ ------------------------------
                               FILTER
                               SORT
                               SELECT STATEMENT
CR	                       TABLE ACCESS
                               NESTED LOOPS
                               NESTED LOOPS
                               NESTED LOOPS
REL_ER_IDX_PK		        INDEX
REL_CHR_IDX_PK      	 	 INDEX
TAC	                       TABLE ACCESS
REL_TAC_IDX_PK        	 	 INDEX
REL_CR_IDX_01             	 INDEX

12 rows selected.



TABLES & INDEX ARE USED:

CREATE TABLE ER
(
  ROLL_ENT        VARCHAR2(4 BYTE)              NOT NULL,
  ROLL_SUB_ENT    VARCHAR2(3 BYTE)              NOT NULL,
  ROLL_ENT_DESCR  VARCHAR2(50 BYTE),
  ENT             VARCHAR2(4 BYTE)              NOT NULL,
  SUB_ENT         VARCHAR2(3 BYTE)              NOT NULL,
  ENT_DESCR       VARCHAR2(50 BYTE)
);


CREATE UNIQUE INDEX REL_ER_IDX_PK ON ER
(ROLL_ENT, ROLL_SUB_ENT, ENT, SUB_ENT);


ALTER TABLE ER ADD (
  CONSTRAINT REL_ER_IDX_PK
 PRIMARY KEY
 (ROLL_ENT, ROLL_SUB_ENT, ENT, SUB_ENT);



CREATE TABLE CR
(
  ACCT                    VARCHAR2(9 BYTE)      NOT NULL,
  ACCT_LVL                VARCHAR2(2 BYTE)      NOT NULL,
  ACCT_ID                 VARCHAR2(9 BYTE)      NOT NULL,
  REL_TYPE                VARCHAR2(10 BYTE)     NOT NULL,
  ACCT_TYPE               VARCHAR2(2 BYTE)      NOT NULL,
  ACCT_DESCR              VARCHAR2(43 BYTE),
  POST_ACCT               VARCHAR2(9 BYTE)      NOT NULL,
  POST_ACCT_TYPE          VARCHAR2(2 BYTE),
  POST_ACCT_DESCR         VARCHAR2(43 BYTE),
  SIGN_REVRSL             NUMBER
);


CREATE INDEX CR_IDX_01 ON CR
(ACCT_ID, REL_TYPE, POST_ACCT);


CREATE UNIQUE INDEX CR_IDX_PK ON CR
(ACCT_ID, ACCT, REL_TYPE, POST_ACCT);


CREATE TABLE CHR
(
  ENT           VARCHAR2(4 BYTE)                NOT NULL,
  SUB_ENT       VARCHAR2(3 BYTE)                NOT NULL,
  HIER_TBL_NUM  VARCHAR2(3 BYTE)                NOT NULL,
  HIER_ROLL     VARCHAR2(14 BYTE)               NOT NULL,
  HIER_CODE     VARCHAR2(14 BYTE)               NOT NULL,
  SUM_FLAG      VARCHAR2(14 BYTE)               NOT NULL,
  CTR_OR_HIER   VARCHAR2(14 BYTE)               NOT NULL,
  CTR_DETAIL    VARCHAR2(14 BYTE)               NOT NULL,
  CTR_DESCR     VARCHAR2(50 BYTE)
);


CREATE INDEX CHR_IDX_01 ON CHR
(HIER_TBL_NUM, HIER_ROLL, SUM_FLAG);


CREATE UNIQUE INDEX CHR_IDX_PK ON CHR
(ENT, SUB_ENT, HIER_TBL_NUM, HIER_ROLL, SUM_FLAG, 
CTR_DETAIL, CTR_OR_HIER, HIER_CODE);


CREATE INDEX CHR_IDX_02 ON CHR
(ENT, SUB_ENT, HIER_TBL_NUM, CTR_OR_HIER, SUM_FLAG, CTR_DETAIL);


CREATE TABLE TAC
(
  ENT               VARCHAR2(4 BYTE),
  SUB_ENT           VARCHAR2(3 BYTE),
  POST_ACCT         VARCHAR2(9 BYTE),
  CTR               VARCHAR2(7 BYTE),
  POST_DATE         DATE,
  EFF_DATE          DATE,
  TXN_CODE          VARCHAR2(2 BYTE),
  TXN_TYPE          VARCHAR2(1 BYTE),
  TXN_AMOUNT        NUMBER(17,2),
  TXN_DESCR         VARCHAR2(46 BYTE),
  TXN_SOURCE        VARCHAR2(1 BYTE)
);


CREATE INDEX TAC_IDX_01 ON TAC
(ENT, SUB_ENT, POST_ACCT, POST_DATE, EFF_DATE, TXN_AMOUNT);


CREATE INDEX TAC_IDX_PK ON TAC
(ENT, SUB_ENT, CTR, POST_ACCT, POST_DATE, EFF_DATE, TXN_AMOUNT);