select * from (
select
to_number(trim(substr(regexp_substr(accnt_info,':93B::ELIG//FAMT/.*',1,1),length(':93B::ELIG//FAMT/')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') as ELIG_AMT,
CASE WHEN to_number(trim(substr(regexp_substr(accnt_info,':93B::AFFB//FAMT/.*',1,1),length(':93B::AFFB//FAMT/')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') IS NOT NULL THEN to_number(trim(substr(regexp_substr(accnt_info,':93B::AFFB//FAMT/.*',1,1),length(':93B::AFFB//FAMT/')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ')
ELSE to_number(trim(substr(regexp_substr(accnt_info,':93C::AFFB//FAMT/ELIG/.*',1,1),length(':93C::AFFB//FAMT/ELIG/')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') END as AFFB_AMT,
 ACT_N,
case substr(sec_info,1,4)
    when '/US/' then 'CUSIP'
    when '/GB/' then 'SEDOL'
    when 'ISIN' then 'ISIN'
else
    null
end as "SECURITY TYPE",
case substr(sec_info,1,4)
    when  '/US/' then SUBSTR(sec_info,5)
    when '/GB/' then SUBSTR(sec_info,5)
    when 'ISIN' then SUBSTR(sec_info,6)
else
    null
end as "SECURITY NAME",
to_date(substr(payd_info,length(':98A::PAYD//')+1,14),'YYYYMMDDHH24MISS') as "CALL DATE",
CA_ID,
CUSTODIAN_NAME,
CORP,SEME_N,
ISO,
msg_function_c "MSG STATUS CODE",
LOTO_D as "LOTTERY DATE",
CRET_D "MSG DATE",
PUBL_D as "RESULTS DATE",
to_number(trim(substr(regexp_substr(ssb_info,':90B::[[:alnum:]]*//[[:alnum:]]*/USD.*',1,1),length(':93B::AFFB//FAMT/USD')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ')*100 as SSB_AMT, CA_STATUS,
to_number(trim(substr(regexp_substr(accnt_info,':93B::AFFB//UNIT/.*',1,1),length(':93B::AFFB//UNIT/')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') as AFFB_UNIT_AMT,
to_number(trim(substr(regexp_substr(accnt_info,':93C::AFFB//FAMT/ELIG/.*',1,1),length(':93C::AFFB//FAMT/ELIG/')+1)),'999999999999999D999999999999999',' NLS_NUMERIC_CHARACTERS = '',.'' ') as AFFB_FAMT_AMT_ELIG from
  ( select sdsm1.CA_STATUS,
    sdsm1.CA_ID,
    sdsm1.SRC_IP_ID,
    sdsm1.corp,
    sdsm1.iso,
    sdsm1.act_n as ACT_N,
    sdsm2.seme_n,
    sdsm2.CUSTODIAN_NAME,
    regexp_substr(SDSM2.MSG_BODY,'(:16R:ACCTINFO)\W+(:95[[:alnum:]]*)*\W+:97A::SAFE//'||SDSM1.ACT_N||'(.+?):16S:ACCTINFO',1,1,'n') as accnt_info,
    regexp_substr(regexp_substr(SDSM2.MSG_BODY,':16R:USECU\W+:35B:((/US/)|(/GB/)|(ISIN ))([[:alnum:]]+)',1,1,'n'),'((/US/)|(/GB/)|(ISIN ))([[:alnum:]]+)') as     sec_info,
    decode(sdsm2.src_ip_id,203863,regexp_substr(SDSM2.MSG_BODY,':16R:CASHMOVE.*:90B::[[:alnum:]]+//[[:alnum:]]+/USD[[:alnum:],]+',1,1,'n'),null) as ssb_info,
    regexp_substr(SDSM2.MSG_BODY,':98[AC-Z]::PAYD//([[:digit:]]+)',1,1,'n') as payd_info,
    sdsm2.msg_body,
    sdsm2.msg_function_c,
    sdsm2.cret_d,
    sdsm1.PMT_D,
    sdsm1.LOTO_D,
    sdsm1.PUBL_D from (
                      SELECT /*+ ORDERED */ distinct FICA.ROW_STAT_C AS "CA_STATUS",
                      FICA.CA_ID AS "CA_ID",
                      SDSM.SRC_IP_ID,
                      SDSM.CORP_ID AS CORP,
                      SDSM.CAEV_TY_C AS ISO,
                      SDSA.ACT_N,
                      FIRST_VALUE(SDSM.SEME_N) over (partition by SDSM.SRC_IP_ID, SDSM.CORP_ID, SDSM.ACT_N ORDER BY SDSM.CRET_D DESC) AS LAST_SEME,
                      FICD.PMT_D,
                      FICD.LOTO_D,
                      FICD.PUBL_D
                      FROM DATA FICA INNER JOIN TRANSACTION FICD ON FICD.CA_ID = FICA.CA_ID
                      INNER JOIN MESSAGE SDSM ON FICD.SRC_IP_ID = SDSM.SRC_IP_ID
                      AND FICD.VEND_CA_ID = SDSM.CORP_ID AND SDSM.REC_T = '564'
                      AND SDSM.CAEV_TY_C IN ('DRAW','PCAL')
                      and SDSM.CRET_D between (case when trim(to_char(sysdate,'Day')) ='Monday' then trunc(SYSDATE -3) else trunc(SYSDATE -1) end)
                      and SYSDATE +1
                      AND SDSM.msg_function_c <> 'WITH'
                      INNER JOIN ACCOUNTS SDSA ON
                      FICD.SRC_IP_ID = SDSA.SRC_IP_ID
                      AND FICD.VEND_CA_ID = SDSA.CORP_ID
                      WHERE FICA.REQ_C = 'H'
                      ) sdsm1,
                        MESSAGE sdsm2
                        where sdsm2.corp_id = sdsm1.corp
                        and sdsm2.src_ip_id = sdsm1.src_ip_id
                        and sdsm2.seme_n = sdsm1.LAST_SEME
                        and regexp_substr(SDSM2.MSG_BODY,'(:16R:ACCTINFO)\W+(:95[[:alnum:]]*)*\W+:97A::SAFE//'||SDSM1.ACT_N||'(.+?):16S:ACCTINFO',1,1,'n') is not null
    )
) where nvl(affb_amt,0) >= 0 OR nvl(AFFB_UNIT_AMT,0) >= 0