table structures(DDL):
1) Ibs_work_bankdata;
-- Create table
create table IBS_WORK_BANKDATA
(
  BD_YRQTR  VARCHAR2(50) not null,
  BD_BKCODE VARCHAR2(50) not null,
  BD_FORCD  VARCHAR2(50),
  BD_ALCD   VARCHAR2(50),
  BD_TYPECD VARCHAR2(50),
  BD_CURCD  VARCHAR2(50),
  BD_COUNCD VARCHAR2(50),
  BD_SECTCD VARCHAR2(50),
  BD_MATCD  VARCHAR2(50),
  BD_C_U_CD VARCHAR2(50),
  BD_S_U_CD VARCHAR2(50),
  BD_FC_BAL NUMBER(20,6),
  BD_FC_INT NUMBER(20,6),
  BD_RS_BAL NUMBER(20,6),
  BD_RS_INT NUMBER(20,6),
  BD_TYPE   VARCHAR2(10) default 'O'
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index BD_CURCD_IDX on IBS_WORK_BANKDATA (BD_CURCD)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IBS_WORK_BANKDATA_IDX on IBS_WORK_BANKDATA (BD_YRQTR, BD_BKCODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IBS_WORK_BANKDATA_INDX on IBS_WORK_BANKDATA (BD_ALCD)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IBS_WORK_BANK_COUNT_IDX on IBS_WORK_BANKDATA (BD_COUNCD)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

2) ibs_org_bankdata;
-- Create table
create table IBS_ORG_BANKDATA
(
  BD_YRQTR  VARCHAR2(50) not null,
  BD_BKCODE VARCHAR2(50) not null,
  BD_FORCD  VARCHAR2(50),
  BD_ALCD   VARCHAR2(50),
  BD_TYPECD VARCHAR2(50),
  BD_CURCD  VARCHAR2(50),
  BD_COUNCD VARCHAR2(50),
  BD_SECTCD VARCHAR2(50),
  BD_MATCD  VARCHAR2(50),
  BD_C_U_CD VARCHAR2(50),
  BD_S_U_CD VARCHAR2(50),
  BD_FC_BAL NUMBER(20,6),
  BD_FC_INT NUMBER(20,6),
  BD_RS_BAL NUMBER(20,6),
  BD_RS_INT NUMBER(20,6),
  BD_TYPE   VARCHAR2(10) default 'O'
)
tablespace USERS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index IBS_ORG_BANKDATA_IDX on IBS_ORG_BANKDATA (BD_YRQTR, BD_BKCODE)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

3)ibs_currencymaster:
-- Create table
create table IBS_CURRENCYMASTER
(
  CUR_ID           VARCHAR2(50),
  CUR_CODE         VARCHAR2(50),
  CUR_NAME         VARCHAR2(50),
  CUR_ACTIVE       NUMBER(1) default 1,
  CUR_CREATEDDATE  DATE default sysdate,
  CUR_CHANGEDDATE  DATE default sysdate,
  CUR_CHANGEDBY    VARCHAR2(50),
  CUR_ACCOUNT      NUMBER(1),
  CUR_REPORTS      NUMBER,
  CUR_USD_EXCHANGE NUMBER(10,5) default 0.0
)
tablespace RBI
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

4)ibs_currencyexchnagerate:
-- Create table
create table IBS_CURRENCYEXCHANGERATE
(
  CER_ID           VARCHAR2(50) not null,
  CER_YEARQTR      NUMBER not null,
  CER_CURRENCYID   VARCHAR2(50) not null,
  CER_EXCHANGERATE NUMBER(15,6),
  CER_ACTIVE       NUMBER(1) default 1,
  CER_CREATEDDATE  DATE default sysdate,
  CER_CHANGEDDATE  DATE default sysdate,
  CER_CHANGEDBY    VARCHAR2(50)
)
tablespace RBI
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table IBS_CURRENCYEXCHANGERATE
  add constraint CER_ID primary key (CER_ID)
  using index 
  tablespace RBI
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Records in tables:
SQL> select count(*) from ibs_work_bankdata;

  COUNT(*)
----------
    120351

SQL> select count(*) from ibs_org_bankdata;

  COUNT(*)
----------
    116367

SQL> select count(*) from ibs_currencyexchangerate;

  COUNT(*)
----------
        58

SQL> select count(*) from ibs_currencymaster;

  COUNT(*)
----------
       178



Query:

select trim(wrk.bd_alcd) as ALCD,
                wrk.bd_typecd as TypeCD,
                wrk.bd_forcd as FORCD,
                wrk.bd_curcd as CURCD,
                wrk.bd_councd as COUNCD,
                wrk.bd_sectcd as SECCD,
                wrk.bd_matcd as MATCD,
                wrk.bd_c_u_cd as C_U_CD,
                wrk.bd_s_u_cd as S_U_CD,
                0 as Org_FCBal,
                0 as ORG_Bal,
                case
                  when wrk.bd_type = 'O' then
                   wrk.bd_fc_bal
                  else
                   0
                end as Main_FCBal,
                case
                  when wrk.bd_type = 'O' then
                   (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1))
                  else
                   0
                end as main_Bal,
                wrk.bd_rs_int,
                wrk.bd_rs_bal,
                wrk.bd_fc_int,
                wrk.bd_fc_bal,
                ' ' as TrackChangs
  from ibs_work_bankdata wrk
 inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr
                                and org.bd_bkcode = wrk.bd_bkcode
                                and org.bd_forcd = wrk.bd_forcd
                                and wrk.BD_YRQTR = 20044
                                and wrk.BD_BKCODE = '000'
                                and wrk.BD_ALCD = '51'
                                and wrk.BD_FORCD = 'IN'
                                and wrk.BD_TYPECD = '11'
  left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
  left join ibs_currencyexchangerate exchg on exchg.cer_currencyid =
                                              curmst.cur_id
                                          and exchg.cer_yearqtr = 20051
                                          and exchg.CER_ACTIVE = 1
union
select distinct trim(wrk.bd_alcd) as ALCD,
                wrk.bd_typecd as TypeCD,
                wrk.bd_forcd as FORCD,
                wrk.bd_curcd as CURCD,
                wrk.bd_councd as COUNCD,
                wrk.bd_sectcd as SECCD,
                wrk.bd_matcd as MATCD,
                ' ' as C_U_CD,
                ' ' as S_U_CD,
                0 as Org_FCBal,
                0 as ORG_Bal,
                case
                  when wrk.bd_type = 'O' then
                   wrk.bd_fc_bal
                  else
                   0
                end as Main_FCBal,
                case
                  when wrk.bd_type = 'O' then
                   (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1))
                  else
                   0
                end as main_Bal,
                wrk.bd_rs_int,
                wrk.bd_rs_bal,
                wrk.bd_fc_int,
                wrk.bd_fc_bal,
                ' ' as TrackChangs
  from ibs_work_bankdata wrk
 inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr
                                and org.bd_bkcode = wrk.bd_bkcode
                                and org.bd_forcd = wrk.bd_forcd
                                and wrk.BD_YRQTR = 20044
                                and wrk.BD_BKCODE = '000'
                                and wrk.BD_ALCD = '51'
                                and wrk.BD_FORCD = 'IN'
                                and wrk.BD_TYPECD = '11'
                                and wrk.bd_rs_bal > 0
  left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
  left join ibs_currencyexchangerate exchg on exchg.cer_currencyid =
                                              curmst.cur_id
                                          and exchg.cer_yearqtr = 20051
                                          and exchg.CER_ACTIVE = 1
 order by main_FCBal;
 


Explain Plan for query:

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstar
--------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |    42 |    6K|    137 |
|  SORT UNIQUE              |                    |    42 |    6K|    110 |
|   UNION-ALL               |                    |       |      |        |
|    TABLE ACCESS BY INDEX R|IBS_ORG_BANKDATA    |   204 |    2K|      2 |
|     NESTED LOOPS          |                    |    41 |    6K|     28 |
|      HASH JOIN OUTER      |                    |     1 |  143 |     26 |
|       HASH JOIN OUTER     |                    |     1 |   93 |     24 |
|        TABLE ACCESS BY IND|IBS_WORK_BANKDATA   |     1 |   52 |     22 |
|         INDEX SKIP SCAN   |IBS_WORK_BANKDATA_I |     1 |      |      7 |
|        TABLE ACCESS FULL  |IBS_CURRENCYMASTER  |   178 |    7K|      1 |
|       TABLE ACCESS FULL   |IBS_CURRENCYEXCHANG |    19 |  950 |      1 |
|      INDEX RANGE SCAN     |IBS_ORG_BANKDATA_ID |   204 |      |      1 |
|    TABLE ACCESS BY INDEX R|IBS_ORG_BANKDATA    |   204 |    2K|      2 |
|     NESTED LOOPS          |                    |    41 |    6K|     28 |
|      HASH JOIN OUTER      |                    |     1 |  143 |     26 |
|       HASH JOIN OUTER     |                    |     1 |   93 |     24 |
|        TABLE ACCESS BY IND|IBS_WORK_BANKDATA   |     1 |   52 |     22 |
|         INDEX SKIP SCAN   |IBS_WORK_BANKDATA_I |     1 |      |      7 |

Plan Table
--------------------------------------------------------------------------------
|        TABLE ACCESS FULL  |IBS_CURRENCYMASTER  |   178 |    7K|      1 |
|       TABLE ACCESS FULL   |IBS_CURRENCYEXCHANG |    19 |  950 |      1 |
|      INDEX RANGE SCAN     |IBS_ORG_BANKDATA_ID |   204 |      |      1 |
--------------------------------------------------------------------------------