SELECT NVL (LPAD (ca.cm_tax_id_nbr, 14, 0), '00000000000000'),
       NVL (LPAD (cm.ctl1_cd, 2, 0), '00'),
       (CASE
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7 AND ba.active_flag = 'Y'
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11 AND ba.active_flag = 'Y'
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12 AND ba.active_flag = 'Y'
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13 AND ba.active_flag = 'Y'
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN TO_CHAR (bk.dismissed_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN TO_CHAR (bk.dismissed_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN TO_CHAR (bk.dismissed_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN TO_CHAR (bk.dismissed_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
           AND bk.reaffirm_date IS NOT NULL
              THEN TO_CHAR (bk.discharge_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
           AND bk.reaffirm_date IS NULL
              THEN TO_CHAR (bk.discharge_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
              THEN TO_CHAR (bk.discharge_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
              THEN TO_CHAR (bk.discharge_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
              THEN TO_CHAR (bk.discharge_date, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN TO_CHAR (bk.date_filed, 'CCYYMMDD')
           ELSE ('99999999')
        END
       ) event_date,
       LPAD (DECODE (bk.bnkr_type_id, 1, 7, 2, 11, 3, 12, 4, 13, 0), 2, 0),
       (CASE
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7 AND ba.active_flag = 'Y'
              THEN 'A'
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11 AND ba.active_flag = 'Y'
              THEN 'B'
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12 AND ba.active_flag = 'Y'
              THEN 'C'
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13 AND ba.active_flag = 'Y'
              THEN 'D'
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN 'I'
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11
           AND ba.active_flag = 'n'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN 'J'
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12
           AND ba.active_flag = 'n'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN 'J'
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NOT NULL
              THEN 'L'
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
           AND bk.reaffirm_date IS NOT NULL
              THEN 'E'
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
           AND bk.reaffirm_date IS NULL
              THEN 'R'
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
              THEN 'F'
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
              THEN 'G'
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NOT NULL
              THEN 'H'
           WHEN DECODE (bk.bnkr_type_id, 1, 7) = 7
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN 'M'
           WHEN DECODE (bk.bnkr_type_id, 2, 11) = 11
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN 'N'
           WHEN DECODE (bk.bnkr_type_id, 3, 12) = 12
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN 'O'
           WHEN DECODE (bk.bnkr_type_id, 4, 13) = 13
           AND ba.active_flag = 'N'
           AND bk.discharge_date IS NULL
           AND bk.dismissed_date IS NULL
           AND bk.reaffirm_date IS NULL
           AND bk.released_date IS NULL
              THEN 'P'
           ELSE (' ')
        END
       ) cii_cd
FROM loanmgr.vls_cust_acct_rel ca,
     loanmgr.vls_c02_main_d cm,
     fortracs.bankruptcy bk,
     fortracs.business_area_status ba
 WHERE (   TO_CHAR (SUBSTR (ca.cm_tax_id_nbr, -1, 9)) =
                                                 SUBSTR (bk.filedby_ssn, -1,
                                                         9)
        OR TO_CHAR (SUBSTR (ca.cm_tax_id_nbr, -1, 9)) =
                                          SUBSTR (bk.second_filedby_ssn, -1,
                                                  9)
       )
   AND ca.acct_nbr = cm.acct_nbr
   AND ca.customer_nbr = cm.customer_nbr
   AND ca.ctl1_cd = cm.ctl1_cd
   AND ca.cr_rel_type_cd IN ('1', '2', '4')
   AND cm.ctl1_cd = 2
   AND ba.active_flag IN ('Y', 'N')
   AND ba.business_area_status_id = bk.business_area_status_id
   AND cm.ln_acct_stat_cd IN ('AC', 'CL', 'IP', 'PF')
   ; 
-----------------------------------------------------------------------------------------
Now I need to include 3 more conditions in the this query. Conditions are
1. if bk.date_filed and bk.bnkr_type_id same in the records then pick up only one record.
2. if bk.date_filed same in the records but bk.bnkr_type_id different then pick up max(bk.bnkr_type_id) record only.
3. if bk.date_filed different then pick up latest date record only.

where fortracs.bankruptcy bk

I need to do in SQL. How to do it if any idea. Pls let me know if you need any other informations.
Thanks.