select cstone_feed_key,
cstone_last_updatetm,
uniq_seq_no,
trans_in,trans_dt,
scb_acct_no,
curr_cd,bai_cd,
raw_am,
bank_ref_no,
free_tx,
case when curr_cd in ('XOF','BRL','XAF','KMF','XPF','JPY') then raw_am
when curr_cd='MRO' then raw_am/10.00
when curr_cd in ('BHD','EGP','IQD','JOD','KWD','LYD','MTL','OMR','SDP','TND','YDD') then raw_am/1000.00
else raw_am/100.00 end as trans_am,
regexp_extract(regexp_extract(regexp_replace(free_tx, '[^a-zA-Z0-9]+', ''),'[0-9]{15}',0),'^(37[0-9]{13}|34[0-9]{13})',0) as cm15,
case when regexp_extract(regexp_extract(regexp_replace(free_tx, '[^a-zA-Z0-9]+', ''),'[0-9]{15}',0),'^(37[0-9]{13}|34[0-9]{13})',0) is NULL
OR(bai_cd < 100 OR( bai_cd > 399 and bai_cd <> 699))
then 'Y' else 'N' end as del_trans_flg,
concat(substr(regexp_extract(regexp_extract(regexp_replace(free_tx, '[^a-zA-Z0-9]+', ''),'[0-9]{15}',0),'^(37[0-9]{13}|34[0-9]{13})',0),1,11),substr(regexp_extract(regexp_extract(regexp_replace(free_tx, '[^a-zA-Z0-9]+', ''),'[0-9]{15}',0),'^(37[0-9]{13}|34[0-9]{13})',0),13,2)) as cm13,
substr(regexp_extract(regexp_extract(regexp_replace(free_tx, '[^a-zA-Z0-9]+', ''),'[0-9]{15}',0),'^(37[0-9]{13}|34[0-9]{13})',0),1,11) as cm11
from scb_acct_trans_dtl;


CASE WHEN TRIM(SUBSTR(TRIM(CAST(REGEXP_REPLACE(browser_localtime,'}','') AS string)),LENGTH(TRIM(CAST(REGEXP_REPLACE(browser_localtime,'}','') AS string))) - 18,20)) NOT IN ('null','xxxx-xx-xx xx:xx:xx') THEN SUBSTR(TRIM(CAST(REGEXP_REPLACE(browser_localtime,'}','') AS string)),1,LENGTH(TRIM(CAST(REGEXP_REPLACE(browser_localtime,'}','') AS string))) - 19) ELSE CAST 0 END as time_offset