SELECT z.rate_usd
     , b.ref_no
FROM   (SELECT t.rate_usd
             , a.ref_no
             , t.company_id
             , RANK () OVER (PARTITION BY t.ref_code ORDER BY SYSDATE
              , t.rate_usd DESC) rnk1
        FROM   (SELECT purchase_detail.ref_code
                     , rate_usd rate_usd
                     , voucher_date
                     , purchase_detail.company_id
                     , ref_no
                     , RANK () OVER (PARTITION BY purchase_detail.ref_code ORDER BY voucher_date
                      , rate_usd DESC) rnk
                FROM   purchase_detail
                     , purchase_master
                     , ref_master
                WHERE  purchase_master.sr_no = purchase_detail.sr_no
                AND    purchase_master.company_id = purchase_detail.company_id
                AND    purchase_detail.ref_code = ref_master.ref_code
                AND    purchase_detail.company_id = ref_master.company_id
                AND    purchase_master.company_id = 1
                AND    purchase_master.voucher_date < '01-apr-07'
                UNION
                SELECT lot_transfer_to.ref_code
                     , lot_transfer_to.rate_usd rate_usd
                     , voucher_date
                     , lot_transfer_to.company_id
                     , ref_master.ref_no
                     , RANK () OVER (PARTITION BY lot_transfer_to.ref_code ORDER BY voucher_date
                      , lot_transfer_to.rate_usd DESC) rnk
                FROM   lot_transfer_to
                     , lot_mixing
                     , ref_master
                WHERE  lot_mixing.voucher_no = lot_transfer_to.voucher_no
                AND    lot_mixing.company_id = lot_transfer_to.company_id
                AND    lot_transfer_to.ref_code = ref_master.ref_code
                AND    lot_transfer_to.company_id = ref_master.company_id
                AND    lot_mixing.company_id = 1
                AND    lot_mixing.voucher_date < '01-apr-07'
                UNION
                SELECT sales_return_detail.ref_code
                     , sales_return_detail.rate_usd rate_usd
                     , voucher_date
                     , sales_return_detail.company_id
                     , ref_master.ref_no
                     , RANK () OVER (PARTITION BY sales_return_detail.ref_code ORDER BY voucher_date
                      , sales_return_detail.rate_usd DESC) rnk
                FROM   sales_return_detail
                     , sales_return
                     , ref_master
                WHERE  sales_return.sr_no = sales_return_detail.sr_no
                AND    sales_return.company_id =
                                                sales_return_detail.company_id
                AND    sales_return_detail.ref_code = ref_master.ref_code
                AND    sales_return_detail.company_id = ref_master.company_id
                AND    sales_return.company_id = 1
                AND    sales_return.voucher_date < '01-apr-07'
                UNION
                SELECT ref_code
                     , NVL (opening_rate_usd, 0) rate_usd
                     , TO_DATE ('01-apr-06', 'dd-mon-yy') voucher_date
                     , TO_NUMBER (ref_master.company_id) company_id
                     , ref_no
                     , RANK () OVER (PARTITION BY ref_code ORDER BY '01-apr-06' DESC)
                                                                          rnk
                FROM   ref_master
                WHERE  ref_master.company_id = 1) t
             , ref_master a
        WHERE  rnk = 1 AND a.ref_no = t.ref_no AND a.company_id = t.company_id) z
     , ref_master b
WHERE  rnk1 = 1 AND b.ref_no = z.ref_no AND b.company_id = z.company_id