SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

Declare @F_BANK_ID int = [[bank_id]];
Declare @dday int = [[cur_dt]];
DECLARE @sprache decimal(4,0) = 1;

DECLARE @cnt_month_with_trns int = 4;
DECLARE @cnt_month_max_kpi int = 12;
DECLARE @min_PAmountCHF decimal(20,4) = 10000;
DECLARE @min_EAmountCHF decimal(20,4) = 50000;
Declare @ddate date;
set @ddate = try_convert(date,cast(@dday as varchar),112);
if @ddate is NULL
	set @ddate = cast(sysdatetime() as date);

DECLARE @NamMonth0 int, --current month
		@NamMonth int,  --previous month
		@NamMonth2 int; --@cnt_month_max_kpi months later
set @NamMonth0 = cast(convert(varchar,@ddate,112) as int)/100;
set @NamMonth = cast(convert(varchar,dateadd(month,-1,@ddate),112) as int)/100;
set @NamMonth2 = cast(convert(varchar,dateadd(month,-@cnt_month_max_kpi,@ddate),112) as int)/100;
Declare @dat0 date,
		@dat2 date;
set @dat0 = convert(date,cast(@NamMonth0*100+1 as varchar),112);
set @dat2 = convert(date,cast(@NamMonth2*100+1 as varchar),112);

exec FAF.Fill_ExchangeRates @F_BANK_ID,@ddate;

with 
	kdkd0 as
		(select userbk_nr,kd_kd_lnr,kd_lnr,kd_lnr_zuw,kd_kd_verb_cd from FAF.FA_MROS_KD_KD where userbk_nr=@F_BANK_ID and isnull(valid_bis_dat,@ddate)>=@ddate
		union all
		select userbk_nr,kd_kd_lnr,kd_lnr_zuw as kd_lnr,kd_lnr as kd_lnr_zuw,kd_kd_verb_cd from FAF.FA_MROS_KD_KD where userbk_nr=@F_BANK_ID and isnull(valid_bis_dat,@ddate)>=@ddate),
	kdkde as
		(select ei.userbk_nr,ei.kd_lnr,ei.EINN_ART_CD,er.ANST_ART_CD
		from
			FAF.FA_MROS_KD_KD_EINN ei
		left join
			FAF.FA_MROS_KD_KD_ERW er
		on er.userbk_nr=ei.userbk_nr and er.kd_kd_einn_lnr=ei.kd_kd_einn_lnr
		where isnull(ei.von_dat_einn,@ddate)<=@ddate 
				and isnull(ei.bis_dat_einn,@ddate)>=@ddate),
	pmnt as 
		(select zv.USERBK_NR,ZV_ZLG_SYS_LNR as ID,ZV_ZLG_SYS_LNR,NULL as KT_BUCH_LNR,NULL as KT_BUCH_DET_LNR,AUF_LNR,AUF_ID,TRAK_REF_LNR,zv.TRAK_REF_TYP,KT_LNR,KT_LNR_B,
			case cast(SH_CD as varchar(1)) 
				when '1' then 'D'
				when '2' then 'C'
			end as SH_CD,
			ZV_EING_AUSG_CD,OM_TEXT_LNR,ZV_ADR_EFF_LNR,AUF_OM_TEXT_LNR,AUF_ZV_ADR_EFF_LNR,AUF_TRAK_REF_TYP,AUF_TRAK_REF_LNR,LAND_LNR_DOM,AUF_LAND_LNR_DOM,
			SITZ_NR_AUSF,SITZ_NR_BEZ,ausf_dt_ist,AUSF_DT_SOLL,AUF_DT_ERT,NULL as BUCH_DAT_A,VALUTA,zv.BUCHTYP_CD,BETRAG_BWRG,BETRAG_ZWRG,NULL as BETRAG_KWRG,
			ISO_WRG_CD,GF_ART,zv.GF_NR,cast('payment' as varchar(50)) as TransactionGroup,
			case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end as special_mark,
			case when cat.MROS_value is not NULL then cat.category_group else rt1.category_group end as category_group,
			case when cat.MROS_value is not NULL then cat.comments else rt1.comments end as comments,
			case when cat.MROS_value is not NULL then cat.MROS_value else rt1.MROS_value end as MROS_value,
			case
				when zv.om_auf_empf_cd = 'TG' then 60
				when zv.om_auf_empf_cd = 'TB' then 70
				when lower(case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end) in ('card_operation','card_purchase') then 90
				when cd_nr.userbk_nr is not NULL then 100
				when zv.trak_cd_e like 'DTB%' or zv.trak_cd_e like 'KTB%' then 50
				when zv.om_auf_empf_cd in ('PE','PG') then 40
				when zv.esr_ref_nr is not NULL then 80
				when zv.gf_nr=211 then 30
				when (zv.zv_eing_ausg_cd='E' and zv.auf_zv_adr_eff_lnr is NULL) or (zv.zv_eing_ausg_cd='A' and zv.zv_adr_eff_lnr is NULL) then 20
				else 10
			end as key_cd,
			kt_id_orig,esr_ref_nr,ext_ref,om_auf_empf_cd
		from 
			FAF.FA_MROS_ZV_ZLG_T zv
		join
			FAF.FA_MROS_REFERENCE_TABLE rt1
		on zv.userbk_nr=rt1.userbk_nr and cast(zv.buchtyp_cd as varchar)=rt1.finnova_value and rt1.ref_type=10 and rt1.activ_data_load=1
		join
			FAF.FA_MROS_REFERENCE_TABLE rt2
		on zv.userbk_nr=rt2.userbk_nr and cast(zv.trak_ref_typ as varchar)=rt2.finnova_value and rt2.ref_type=11 and rt2.activ_data_load=1
		left join
			FAF.FA_MROS_TRX_CATEGORIES_DET cat
		on cat.userbk_nr=zv.userbk_nr and cat.buchtyp_cd=zv.buchtyp_cd and cat.trak_ref_typ=zv.trak_ref_typ and cat.gf_nr=zv.gf_nr
		left join
			FAF.FA_MROS_REFERENCE_TABLE cd_nr
		on cd_nr.userbk_nr=zv.userbk_nr and zv.card_art_nr=cd_nr.Finnova_value and cd_nr.ref_type=22 and cd_nr.activ_data_load=1 and cd_nr.special_mark='BANKCARD'
		where zv.userbk_nr=@F_BANK_ID and ausf_dt_ist>=@ddate and ausf_dt_ist<dateadd(day,1,@ddate)),
	trans as
		(select *,NULL as adr_zeile_1_ez,NULL as adr_zeile_all,NULL as text_ext,NULL as text_zus,0 as [flag] from pmnt
		union all
		select USERBK_NR,ID,ZV_ZLG_SYS_LNR,KT_BUCH_LNR,KT_BUCH_DET_LNR,AUF_LNR,AUF_ID,TRAK_REF_LNR,
			TRAK_REF_TYP,KT_LNR_B as KT_LNR,KT_LNR as KT_LNR_B,SH_CD,ZV_EING_AUSG_CD,OM_TEXT_LNR,ZV_ADR_EFF_LNR,AUF_OM_TEXT_LNR,
			AUF_ZV_ADR_EFF_LNR,AUF_TRAK_REF_TYP,AUF_TRAK_REF_LNR,LAND_LNR_DOM,AUF_LAND_LNR_DOM,SITZ_NR_AUSF,SITZ_NR_BEZ,
			ausf_dt_ist,AUSF_DT_SOLL,AUF_DT_ERT,BUCH_DAT_A,VALUTA,BUCHTYP_CD,BETRAG_BWRG,BETRAG_ZWRG,BETRAG_KWRG,
			ISO_WRG_CD,GF_ART,GF_NR,TransactionGroup,special_mark,category_group,comments,MROS_value,key_cd,kt_id_orig,esr_ref_nr,ext_ref,
			om_auf_empf_cd,NULL as adr_zeile_1_ez,NULL as adr_zeile_all,NULL as text_ext,NULL as text_zus,1 as [flag]
		from pmnt
		where (ZV_EING_AUSG_CD='E' and AUF_ZV_ADR_EFF_LNR is NULL) or (ZV_EING_AUSG_CD='A' and ZV_ADR_EFF_LNR is NULL)
		union all
		select ea.USERBK_NR,ea.kt_buch_lnr as ID,NULL as ZV_ZLG_SYS_LNR,ea.kt_buch_lnr,NULL as KT_BUCH_DET_LNR,NULL as AUF_LNR,NULL as AUF_ID,
			ea.TRAK_REF_LNR,ea.TRAK_REF_TYP,ea.KT_LNR,NULL as KT_LNR_B,ea.SH_CD,NULL as ZV_EING_AUSG_CD,NULL as OM_TEXT_LNR,NULL as ZV_ADR_EFF_LNR,
			NULL as AUF_OM_TEXT_LNR,NULL as AUF_ZV_ADR_EFF_LNR,NULL as AUF_TRAK_REF_TYP,NULL as AUF_TRAK_REF_LNR,
			NULL as LAND_LNR_DOM,NULL as AUF_LAND_LNR_DOM,ea.sitz_nr as SITZ_NR_AUSF,ea.SITZ_NR_BEZ,NULL as ausf_dt_ist,
			NULL as AUSF_DT_SOLL,NULL as AUF_DT_ERT,ea.BUCH_DAT_A,ea.VALUTA,ea.BUCHTYP_CD,ea.BETRAG_BWRG,NULL as BETRAG_ZWRG,ea.BETRAG_KWRG,
			ea.ISO_WRG_CD,NULL as GF_ART,ea.GF_NR,rt1.special_mark as TransactionGroup,
			case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end as special_mark,
			case when cat.MROS_value is not NULL then cat.category_group else rt1.category_group end as category_group,
			case when cat.MROS_value is not NULL then cat.comments else rt1.comments end as comments,
			case when cat.MROS_value is not NULL then cat.MROS_value else rt1.MROS_value end as MROS_value,
			case
				when lower(case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end) = 'cash_operation' then 510
				when lower(case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end) = 'card_operation' then 520
				when lower(case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end) = 'deposit' then 530
				when lower(case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end) = 'withdrawal' then 540
				when lower(case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end) = 'card_purchase' then 550
				when ea.adr_zeile_1_ez is not null then 560
				when case when cat.MROS_value is not NULL then cat.special_mark else rt1.special_mark end is not NULL then 570
				else 500
			end as key_cd,
			NULL as kt_id_orig,NULL as esr_ref_nr,NULL as ext_ref,NULL as om_auf_empf_cd,
			adr_zeile_1_ez,
			ea.adr_zeile_1_ez +
			case
				when ea.adr_zeile_2_ez is not null
				then '; ' + ea.adr_zeile_2_ez
				else  NULL
			end +
			case
				when ea.adr_zeile_3_ez is not null
				then '; ' + ea.adr_zeile_3_ez
				else  NULL
			end +
			case
				when ea.adr_zeile_4_ez is not null
				then '; ' + ea.adr_zeile_4_ez
				else  NULL
			end +
			case
				when ea.adr_zeile_5_ez is not null
				then '; ' + ea.adr_zeile_5_ez
				else  NULL
			end as adr_zeile_all,
			ea.text_ext,ea.text_zus,NULL as [flag]
		from 
			FAF.FA_MROS_KT_BUCH_EA ea
		join
			FAF.FA_MROS_REFERENCE_TABLE rt1
		on ea.userbk_nr=rt1.userbk_nr and cast(ea.buchtyp_cd as varchar)=rt1.finnova_value and rt1.ref_type=10 and rt1.activ_data_load=1
		join
			FAF.FA_MROS_REFERENCE_TABLE rt2
		on ea.userbk_nr=rt2.userbk_nr and cast(ea.trak_ref_typ as varchar)=rt2.finnova_value and rt2.ref_type=11 and rt2.activ_data_load=1
		left join
			FAF.FA_MROS_TRX_CATEGORIES_DET cat
		on cat.userbk_nr=ea.userbk_nr and cat.buchtyp_cd=ea.buchtyp_cd and cat.trak_ref_typ=ea.trak_ref_typ and cat.gf_nr is NULL
		where ea.userbk_nr=@F_BANK_ID and ea.buch_dat_a>=@ddate and ea.buch_dat_a<dateadd(day,1,@ddate)
			and not exists (select * from FAF.FA_MROS_DP_BUCH dp where dp.userbk_nr=ea.userbk_nr and dp.trak_ref_lnr=ea.trak_ref_lnr)
			--can be deleted after datamart correction
			and not exists (select * from FAF.FA_MROS_ZV_AUF au where au.userbk_nr=ea.userbk_nr and au.trak_ref_lnr=ea.trak_ref_lnr)
		union all
		select dp.USERBK_NR,dp.dp_buch_lnr as ID,NULL as ZV_ZLG_SYS_LNR,NULL as KT_BUCH_LNR,NULL as KT_BUCH_DET_LNR,
			NULL as AUF_LNR,NULL as AUF_ID,dp.TRAK_REF_LNR,dp.TRAK_REF_TYP,dp.KT_LNR,NULL as KT_LNR_B,
			--we invert sh_cd for DP_BUCH because in the intersection between DP_BUCH and KT_BUCH they have opposite sh_cd
			case 
				when dp.SH_CD = 'D' then 'C'
				when dp.SH_CD = 'C' then 'D'
				else NULL
			end as sh_cd,
			NULL as ZV_EING_AUSG_CD,NULL as OM_TEXT_LNR,NULL as ZV_ADR_EFF_LNR,
			NULL as AUF_OM_TEXT_LNR,NULL as AUF_ZV_ADR_EFF_LNR,NULL as AUF_TRAK_REF_TYP,NULL as AUF_TRAK_REF_LNR,
			dp.LAND_LNR_DOM,NULL as AUF_LAND_LNR_DOM,dp.sitz_nr as SITZ_NR_AUSF,dp.SITZ_NR_BEZ,NULL as ausf_dt_ist,
			NULL as AUSF_DT_SOLL,NULL as AUF_DT_ERT,dp.BUCH_DAT_A,dp.SETTLE_DAT as VALUTA,dp.BUCHTYP_CD,
			case
				when dp.wrg_lnr=1 then 1
				when exch.rate>0 then exch.rate
				else 0
			end *
			case
				when abs(dp.brut_hwrg)>0 then dp.brut_hwrg
				when abs(dp.menge_buch)>0 and dp.kurs_hwrg>0 then dp.menge_buch * dp.kurs_hwrg
				when abs(dp.menge_ertr)>0 and dp.kurs_hwrg>0 then dp.menge_ertr * dp.kurs_hwrg
				else 0
			end as BETRAG_BWRG,
			NULL as BETRAG_ZWRG,
			case
				when abs(brut_hwrg)>0 then brut_hwrg
				when abs(menge_buch)>0 and kurs_hwrg>0 then menge_buch * kurs_hwrg
				when abs(menge_ertr)>0 and kurs_hwrg>0 then menge_ertr * kurs_hwrg
				else 0
			end as BETRAG_KWRG,
			hwrg.ISO_WRG_CD,NULL as GF_ART,NULL as GF_NR,rt4.special_mark as TransactionGroup,
			rt4.special_mark,rt4.category_group,rt4.comments,rt4.MROS_value,NULL as key_cd,NULL as kt_id_orig,NULL as esr_ref_nr,NULL as ext_ref,
			NULL as om_auf_empf_cd,NULL as adr_zeile_1_ez,NULL as adr_zeile_all,dp.text_ext,dp.text_zus,NULL as [flag]
		from 
			FAF.FA_MROS_DP_BUCH dp
		join
			FAF.FA_MROS_REFERENCE_TABLE rt4
		on dp.userbk_nr=rt4.userbk_nr and cast(dp.buchtyp_cd as varchar)=rt4.finnova_value and rt4.ref_type=10 and rt4.activ_data_load=1
		join
			FAF.FA_MROS_REFERENCE_TABLE rt5
		on dp.userbk_nr=rt5.userbk_nr and cast(dp.trak_ref_typ as varchar)=rt5.finnova_value and rt5.ref_type=11 and rt5.activ_data_load=1
		left join
			(select userbk_nr,wrg_lnr,ExchangeRate_to_local as rate,
				row_number() over (partition by userbk_nr,wrg_lnr order by RateDate desc) as rn
			from FAF.FA_MROS_EXCHANGE_RATES where userbk_nr=@F_BANK_ID and RateDate<=@ddate) exch
		on dp.userbk_nr=exch.userbk_nr and dp.wrg_lnr=exch.wrg_lnr and exch.rn=1
		left join
			(select distinct userbk_nr,wrg_lnr,sprache_cd,
				first_value(ISO_WRG_CD) over (partition by userbk_nr,wrg_lnr,sprache_cd order by VERS desc) as ISO_WRG_CD
			from FAF.FA_MROS_AL1WRG) hwrg
		on hwrg.userbk_nr=dp.userbk_nr and hwrg.wrg_lnr=dp.wrg_lnr and hwrg.sprache_cd=@sprache
		where dp.userbk_nr=@F_BANK_ID and dp.buch_dat_a>=@ddate and dp.buch_dat_a<dateadd(day,1,@ddate) and isnull(dp.buch_ertr_cd,0)<>1
			and not exists (select * from FAF.FA_MROS_KT_BUCH bu where bu.userbk_nr=dp.userbk_nr and bu.trak_ref_lnr=dp.trak_ref_lnr)
			--can be deleted after datamart correction
			and not exists (select * from FAF.FA_MROS_KT_BUCH_EA bua where bua.userbk_nr=dp.userbk_nr and bua.trak_ref_lnr=dp.trak_ref_lnr)
		),
	cntr as
		(SELECT distinct userbk_nr,land_lnr,
			first_value(iso_land_cd) over (partition by userbk_nr,land_lnr order by iso_land_cd desc) as iso_land_cd 
		FROM FAF.FA_MROS_AL1LAND where sprache_cd=@sprache),
	pmnt1 as 
		(select zv.userbk_nr,ac.kd_lnr,ac_b.kd_lnr as kd_lnr_b,zv.betrag_bwrg,zv.sh_cd,zv.ausf_dt_ist as T_Date,
			case
				when zv.zv_eing_ausg_cd='E' and zv.auf_zv_adr_eff_lnr is not NULL then 1
				when zv.zv_eing_ausg_cd='A' and zv.zv_adr_eff_lnr is not NULL then -1
				else 0
			end as pm_flag,
			cast(convert(varchar,zv.ausf_dt_ist,112) as int)/100 as T_Month
		from 
			FAF.FA_MROS_ZV_ZLG_T zv
		join
			FAF.FA_MROS_KT_STAMM ac
		on zv.userbk_nr=ac.userbk_nr and zv.kt_lnr=ac.kt_lnr
		left join
			FAF.FA_MROS_KT_STAMM ac_b
		on zv.userbk_nr=ac_b.userbk_nr and zv.kt_lnr_b=ac_b.kt_lnr
		where zv.userbk_nr=@F_BANK_ID and zv.ausf_dt_ist>=@dat2 and zv.ausf_dt_ist<@dat0
			and ac.kd_lnr<>isnull(ac_b.kd_lnr,0)),
	trans1 as
		(select userbk_nr,kd_lnr,betrag_bwrg,T_Date,T_Month,
			case 
				when pm_flag=1 then 1
				when pm_flag=0 and sh_cd='1' then 1
				else 0
			end as INFLOW,
			case 
				when pm_flag=-1 then 1
				when pm_flag=0 and sh_cd='2' then 1
				else 0
			end as OUTFLOW
		from pmnt1
		union all
		select userbk_nr,kd_lnr_b as kd_lnr,betrag_bwrg,T_Date,T_Month,
			case 
				when sh_cd='2' then 1
				else 0
			end as INFLOW,
			case 
				when sh_cd='1' then 1
				else 0
			end as OUTFLOW
		from pmnt1 where pm_flag=0
		union all
		select ea.userbk_nr,ea.kd_lnr,ea.betrag_bwrg,ea.buch_dat_a as T_Date,cast(convert(varchar,ea.buch_dat_a,112) as int)/100 as T_Month,
			case 
				when ea.sh_cd='C' then 1
				else 0
			end as INFLOW,
			case 
				when ea.sh_cd='D' then 1
				else 0
			end as OUTFLOW
		from 
			FAF.FA_MROS_KT_BUCH_EA ea
		where ea.userbk_nr=@F_BANK_ID and ea.buch_dat_a>=@dat2 and ea.buch_dat_a<@dat0
			and not exists (select * from FAF.FA_MROS_DP_BUCH dp where dp.userbk_nr=ea.userbk_nr and dp.trak_ref_lnr=ea.trak_ref_lnr)
			--can be deleted after datamart correction
			and not exists (select * from FAF.FA_MROS_ZV_AUF au where au.userbk_nr=ea.userbk_nr and au.trak_ref_lnr=ea.trak_ref_lnr)
		union all
		select dp.userbk_nr,dp.kd_lnr,
			case
				when dp.wrg_lnr=1 then 1
				when exch.rate>0 then exch.rate
				else 0
			end *
			case
				when abs(dp.brut_hwrg)>0 then dp.brut_hwrg
				when abs(dp.menge_buch)>0 and dp.kurs_hwrg>0 then dp.menge_buch * dp.kurs_hwrg
				when abs(dp.menge_ertr)>0 and dp.kurs_hwrg>0 then dp.menge_ertr * dp.kurs_hwrg
				else 0
			end as betrag_bwrg,
			dp.buch_dat_a as T_DATE,cast(convert(varchar,dp.buch_dat_a,112) as int)/100 as T_Month,
			--we invert sh_cd for DP_BUCH because in the intersection between DP_BUCH and KT_BUCH they have opposite sh_cd
			case 
				when dp.sh_cd='D' then 1
				else 0
			end as INFLOW,
			case 
				when dp.sh_cd='C' then 1
				else 0
			end as OUTFLOW
		from 
			FAF.FA_MROS_DP_BUCH dp
		join
			FAF.FA_MROS_REFERENCE_TABLE rt4
		on dp.userbk_nr=rt4.userbk_nr and cast(dp.buchtyp_cd as varchar)=rt4.finnova_value and rt4.ref_type=10 and rt4.activ_data_load=1
		join
			FAF.FA_MROS_REFERENCE_TABLE rt5
		on dp.userbk_nr=rt5.userbk_nr and cast(dp.trak_ref_typ as varchar)=rt5.finnova_value and rt5.ref_type=11 and rt5.activ_data_load=1
		left join
			(select dpdt.userbk_nr,dpdt.wrg_lnr,dpdt.buch_dat_a,ex.ExchangeRate_to_local as rate,
				row_number() over (partition by dpdt.userbk_nr,dpdt.wrg_lnr,dpdt.buch_dat_a order by ex.RateDate desc) as rn 
			from
				(select distinct userbk_nr,wrg_lnr,buch_dat_a from FAF.FA_MROS_DP_BUCH where userbk_nr=@F_BANK_ID and buch_dat_a>=@dat2 and buch_dat_a<@dat0) dpdt
			join
				FAF.FA_MROS_EXCHANGE_RATES ex
			on ex.userbk_nr=dpdt.userbk_nr and dpdt.wrg_lnr=ex.wrg_lnr and ex.RateDate<=dpdt.buch_dat_a) exch
		on dp.userbk_nr=exch.userbk_nr and dp.wrg_lnr=exch.wrg_lnr and dp.buch_dat_a=exch.buch_dat_a and exch.rn=1
		where dp.userbk_nr=@F_BANK_ID and dp.buch_dat_a>=@dat2 and dp.buch_dat_a<@dat0 and isnull(dp.buch_ertr_cd,0)<>1
			and not exists (select * from FAF.FA_MROS_KT_BUCH bu where bu.userbk_nr=dp.userbk_nr and bu.trak_ref_lnr=dp.trak_ref_lnr)
			--can be deleted after datamart correction
			and not exists (select * from FAF.FA_MROS_KT_BUCH_EA bua where bua.userbk_nr=dp.userbk_nr and bua.trak_ref_lnr=dp.trak_ref_lnr)),
	pmnt2 as 
		(select zv.userbk_nr,ac.kd_lnr,ac_b.kd_lnr as kd_lnr_b,zv.ausf_dt_ist as T_Date,
			case
				when zv.zv_eing_ausg_cd='E' and zv.auf_zv_adr_eff_lnr is not NULL then 1
				when zv.zv_eing_ausg_cd='A' and zv.zv_adr_eff_lnr is not NULL then -1
				else 0
			end as pm_flag
		from 
			FAF.FA_MROS_ZV_ZLG_T zv
		join
			FAF.FA_MROS_KT_STAMM ac
		on zv.userbk_nr=ac.userbk_nr and zv.kt_lnr=ac.kt_lnr
		left join
			FAF.FA_MROS_KT_STAMM ac_b
		on zv.userbk_nr=ac_b.userbk_nr and zv.kt_lnr_b=ac_b.kt_lnr
		where zv.userbk_nr=@F_BANK_ID and zv.ausf_dt_ist<@ddate and ac.kd_lnr<>isnull(ac_b.kd_lnr,0)),
	trans2 as
		(select userbk_nr,kd_lnr,T_Date from pmnt2
		union all
		select userbk_nr,kd_lnr_b as kd_lnr,T_Date from pmnt2 where pm_flag=0
		union all
		select ea.userbk_nr,ea.kd_lnr,ea.buch_dat_a as T_Date
		from 
			FAF.FA_MROS_KT_BUCH_EA ea
		where ea.userbk_nr=@F_BANK_ID and ea.buch_dat_a<@ddate and ea.buch_dat_a<@dat0
			and not exists (select * from FAF.FA_MROS_DP_BUCH dp where dp.userbk_nr=ea.userbk_nr and dp.trak_ref_lnr=ea.trak_ref_lnr)
		union all
		select dp.userbk_nr,dp.kd_lnr,dp.buch_dat_a as T_DATE
		from 
			FAF.FA_MROS_DP_BUCH dp
		join
			FAF.FA_MROS_REFERENCE_TABLE rt4
		on dp.userbk_nr=rt4.userbk_nr and cast(dp.buchtyp_cd as varchar)=rt4.finnova_value and rt4.ref_type=10 and rt4.activ_data_load=1
		join
			FAF.FA_MROS_REFERENCE_TABLE rt5
		on dp.userbk_nr=rt5.userbk_nr and cast(dp.trak_ref_typ as varchar)=rt5.finnova_value and rt5.ref_type=11 and rt5.activ_data_load=1
		where dp.userbk_nr=@F_BANK_ID and dp.buch_dat_a<@ddate and isnull(dp.buch_ertr_cd,0)<>1
			and not exists (select * from FAF.FA_MROS_KT_BUCH bu where bu.userbk_nr=dp.userbk_nr and bu.trak_ref_lnr=dp.trak_ref_lnr)
			--can be deleted after datamart correction
			and not exists (select * from FAF.FA_MROS_KT_BUCH_EA bua where bua.userbk_nr=dp.userbk_nr and bua.trak_ref_lnr=dp.trak_ref_lnr)),
	d_stats as
		(select userbk_nr,kd_lnr,T_Month,T_Date,
			sum(betrag_bwrg) as sum_D_Amount,
			sum(betrag_bwrg*INFLOW) as sum_D_IN_Amount,
			sum(betrag_bwrg*OUTFLOW) as sum_D_OUT_Amount,
			max(betrag_bwrg) as max_D_Amount,
			count(*) as D_cnt
		from trans1 group by userbk_nr,kd_lnr,T_Month,T_Date),
	aum as
		(select userbk_nr,kd_lnr,aum_bwrg,aum_dat_von,
			row_number() over (partition by userbk_nr,kd_lnr order by aum_dat_von desc) as rn
		from FAF.FA_MROS_KD_AUM where aum_dat_von<@dat0)
SELECT 		
	cast(tr.USERBK_NR as varchar(50)) as [ZV_ZLG_T/USERBK_NR], 
	tr.TransactionGroup, --new
	cast(tr.ID as varchar(50)) as ID,  --new
	cast(tr.ZV_ZLG_SYS_LNR as varchar(50)) as [ZV_ZLG_T/ZV_ZLG_SYS_LNR],
	cast(tr.KT_BUCH_LNR as varchar(50)) as [KT_BUCH/KT_BUCH_LNR],
	cast(tr.KT_BUCH_DET_LNR as varchar(50)) as [KT_BUCH_DET/KT_BUCH_DET_LNR],
	cast(tr.AUF_LNR as varchar(50)) as [ZV_ZLG_T/AUF_LNR],
	tr.AUF_ID as [OM_AUF/AUF_ID],
	cast(tr.TRAK_REF_LNR as varchar(50)) as [ZV_ZLG_T/TRAK_REF_LNR],
	tr.TRAK_REF_TYP as [ZV_ZLG_T/TRAK_REF_TYP],
	cast(tr.KT_LNR_B as varchar(50)) as [ZV_ZLG_T/KT_LNR_B],
	case
		when tr.[flag] is not null
		then
			case 
				when tr.ZV_EING_AUSG_CD='A' and tr.ZV_ADR_EFF_LNR is NOT NULL then 1
				when tr.[flag]=0 and tr.sh_cd='C' then 1
				when tr.[flag]=1 and tr.sh_cd='D' then 1
				else 0
			end
		else
			case when tr.sh_cd='D' then 1 else 0 end
	end as [DEBIT],
	case
		when tr.[flag] is not null
		then
			case 
				when tr.ZV_EING_AUSG_CD='E' and tr.AUF_ZV_ADR_EFF_LNR is NOT NULL then 1
				when tr.[flag]=0 and tr.sh_cd='D' then 1
				when tr.[flag]=1 and tr.sh_cd='C' then 1
				else 0
			end
		else
			case when tr.sh_cd='C' then 1 else 0 end
	end as [CREDIT],
	tr.ZV_EING_AUSG_CD as [ZV_AUF_T/ZV_EING_AUSG_CD],
	cast(tr.OM_TEXT_LNR as varchar(50)) as [ZV_ZLG_T/OM_TEXT_LNR],
	cast(tr.ZV_ADR_EFF_LNR as varchar(50)) as [ZV_ZLG_T/ZV_ADR_EFF_LNR],
	cast(tr.AUF_OM_TEXT_LNR as varchar(50)) as [OM_AUF/OM_TEXT_LNR],
	cast(tr.AUF_ZV_ADR_EFF_LNR as varchar(50)) as [ZV_AUF_T/ZV_ADR_EFF_LNR],
	tr.AUF_TRAK_REF_TYP as [ZV_AUF_T/TRAK_REF_TYP],
	cast(tr.AUF_TRAK_REF_LNR as varchar(50)) as [ZV_AUF_T/TRAK_REF_LNR],
	tr.LAND_LNR_DOM as [ZV_ZLG_T/LAND_LNR_DOM],
	cn1.iso_land_cd as [ZV_ZLG_T/LAND_LNR_DOM_ISO],
	tr.AUF_LAND_LNR_DOM as [OM_AUF/LAND_LNR_DOM],
	cn2.iso_land_cd as [OM_AUF/LAND_LNR_DOM_ISO],
	case
		when tr.[flag] is not null
		then
			case
				when ac.kd_lnr<>isnull(ac_b.kd_lnr,0)
				then
					case 
						when tr.ZV_EING_AUSG_CD='E' and tr.AUF_ZV_ADR_EFF_LNR is NOT NULL then 1
						when tr.[flag]=0 and tr.sh_cd='D' then 1
						when tr.[flag]=1 and tr.sh_cd='C' then 1
						else 0
					end
				else 0
			end
		else 
			case when tr.sh_cd='C' then 1 else 0 end
	end as [INFLOW],
	case
		when tr.[flag] is not null
		then
			case
				when ac.kd_lnr<>isnull(ac_b.kd_lnr,0)
				then
					case 
						when tr.ZV_EING_AUSG_CD='A' and tr.ZV_ADR_EFF_LNR is NOT NULL then 1
						when tr.[flag]=0 and tr.sh_cd='C' then 1
						when tr.[flag]=1 and tr.sh_cd='D' then 1
						else 0
					end 
				else 0
			end
		else 
			case when tr.sh_cd='D' then 1 else 0 end
	end as [OUTFLOW],
	case 
		when tr.ZV_EING_AUSG_CD='E' and tr.AUF_ZV_ADR_EFF_LNR is NOT NULL then 1
		else 0
	end as [INFLOW_EXT_BANK],
	case 
		when tr.ZV_EING_AUSG_CD='A' and tr.ZV_ADR_EFF_LNR is NOT NULL then 1 
		else 0
	end as [OUTFLOW_EXT_BANK],
	tr.SITZ_NR_AUSF as [OM_AUF/SITZ_NR_AUSF],
	tr.SITZ_NR_BEZ as [OM_AUF/SITZ_NR_BEZ],
	tr.ausf_dt_ist as [OM_AUF/AUSF_DT_IST],
	tr.AUSF_DT_SOLL as [OM_AUF/AUSF_DT_SOLL],
	tr.AUF_DT_ERT as [OM_AUF/AUF_DT_ERT],
	tr.BUCH_DAT_A as [KT_BUCH/BUCH_DAT_A], --new
	tr.VALUTA as [ZV_ZLG_T/VALUTA],
	case
		when tr.[flag] is not null
		then convert(varchar,tr.ausf_dt_ist,112)
		else convert(varchar,tr.BUCH_DAT_A,112)
	end as [T_DATE],
	case
		when tr.[flag] is not null
		then replace(convert(varchar,tr.ausf_dt_ist,108),':','')
		else replace(convert(varchar,tr.BUCH_DAT_A,108),':','')
	end as [T_Time],
	tr.BUCHTYP_CD as [ZV_ZLG_T/BUCHTYP_CD], --TRANSACTION_TYPE
	tr.BETRAG_BWRG as [ZV_ZLG_T/BETRAG_BWRG], --AMOUNT_CHF
	tr.BETRAG_ZWRG as [ZV_ZLG_T/BETRAG_ZWRG],
	tr.BETRAG_KWRG as [KT_BUCH/BETRAG_KWRG], --new
	tr.ISO_WRG_CD as [ZV_ZLG_T/ISO_WRG_CD], --ACC_CURRENCY
	tr.GF_ART as [ZV_ZLG_T/GF_ART],
	tr.GF_NR as [ZV_ZLG_T/GF_NR],
	cast(ac.KT_LNR as varchar(50)) as [KT_STAMM/KT_LNR], --F_ACCOUNT_ID
	ac.EDIT_KT_NR as [KT_STAMM/EDIT_KT_NR], --F_ACCOUNT_NR,
	ac.KT_ART_NR as [KT_STAMM/KT_ART_NR],--ACCOUNTTYPE 	
	ac.EROEFF_DAT as [KT_STAMM/EROEFF_DAT],
	datediff(day,convert(date,cast(ac.EROEFF_DAT as varchar),112),@ddate)  as [KT_DURATION],
	ac.iban_id as [KT_STAMM/IBAN_ID],
	sld0.SALDO_BWRG as [KT_SALDO/SALDO_BWRG],
	sld0.SALDO_KWRG as [KT_SALDO/SALDO_KWRG],
	isnull(sldall.KD_TOTAL_SALDO,0) as [KD_TOTAL_SALDO],
	cast(cl.KD_LNR as varchar(50)) as [KD_STAMM/KD_LNR], 
	cl.EDIT_KD_NR as [KD_STAMM/EDIT_KD_NR],
	cl.KD_HTYP_CD as [KD_STAMM/KD_HTYP_CD],
	isnull(cl.BANK_FLAG,0) as [KD_STAMM/BANK_FLAG],
	cl.GESCHLECHT_CD as [KD_STAMM/GESCHLECHT_CD],
	cl.VORNAME as [KD_STAMM/VORNAME],
	cl.NAME_1 as [KD_STAMM/NAME_1],
	cl.KD_BEZ as [KD_STAMM/KD_BEZ],
	cl.KD_RSTAT_CD as [KD_STAMM/KD_RSTAT_CD],
	cl.KD_RSTAT_BEZ as [KD_STAMM/KD_RSTAT_BEZ],
	cl.KD_RSTAT_AUSP_CD as [KD_STAMM/KD_RSTAT_AUSP_CD],
	cl.KD_RSTAT_AUSP_BEZ as [KD_STAMM/KD_RSTAT_AUSP_BEZ],
	cl.KDKAT_CD as [KD_STAMM/KDKAT_CD],
	cl.KDKAT_BEZ as [KD_STAMM/KDKAT_BEZ],
	cl.KD_SEGM_CD as [KD_STAMM/KD_SEGM_CD],
	cl.KD_SEGM_BEZ as [KD_STAMM/KD_SEGM_BEZ],
	cl.RFORM_CD as [KD_STAMM/RFORM_CD],
	cl.RFORM_BEZ as [KD_STAMM/RFORM_BEZ],
	cl.UID_NR as [KD_STAMM/UID_NR],
	cl.GEBURT_DAT as [KD_STAMM/GEBURT_DAT],
	cl.GEBURT_JAHR as [KD_STAMM/GEBURT_JAHR],
	cl.ALTER_JAHR as [KD_STAMM/ALTER_JAHR],
	cl.TOD_DAT as [KD_STAMM/TOD_DAT],
	cl.TOD_JAHR as [KD_STAMM/TOD_JAHR],
	cl.KD_SITZ_GSCH_CD as [KD_STAMM/KD_SITZ_GSCH_CD],
	cl.TITEL as [KD_STAMM/TITEL],
	cl.GEBURT_ORT as [KD_STAMM/GEBURT_ORT],
	cl.NOGA_CD as [KD_STAMM/NOGA_CD],
	cl.FIN_VOLLM_CD as [KD_STAMM/FIN_VOLLM_CD],
	cl.NOGA_BEZ as [KD_STAMM/NOGA_BEZ],
	cl.BERUF_CD as [KD_STAMM/BERUF_CD],
	cl.BERUF_BEZ as [KD_STAMM/BERUF_BEZ],
	cl.KAP as [KD_STAMM/KAP],
	cl.EMAIL_ADR as [KD_STAMM/EMAIL_ADR],
	cl.KD_VIP_CD as [KD_STAMM/KD_VIP_CD],
	isnull(cl.KD_PEP_CD,0) as [KD_STAMM/KD_PEP_CD],
	isnull(cl.KD_PEP_CD_AUTO,0) as [KD_STAMM/KD_PEP_CD_AUTO],
	cl.KD_PEP_DT_AUTO as [KD_STAMM/KD_PEP_DT_AUTO],
	case when kdkd1.userbk_nr is not null then 1 else 0 end as [PEP_RELATED_01],
	cl.KD_GRP_ART_CD as [KD_STAMM/KD_GRP_ART_CD],
	cl.LAND_LNR_NAT as [KD_STAMM/LAND_LNR_NAT],
	cl.LAND_LNR_NAT_BEZ as [KD_STAMM/LAND_LNR_NAT_BEZ],
	cl.ISO_LAND_CODE_NAT as [KD_STAMM/ISO_LAND_CODE_NAT],
	cl.LAND_LNR_NAT_2 as [KD_STAMM/LAND_LNR_NAT_2],
	cl.LAND_LNR_NAT_BEZ_2 as [KD_STAMM/LAND_LNR_NAT_BEZ_2],
	cl.ISO_LAND_CODE_NAT_2 as [KD_STAMM/ISO_LAND_CODE_NAT_2],
	cl.LAND_LNR_AUSL as [KD_STAMM/LAND_LNR_AUSL],
	cl.LAND_LNR_AUSL_BEZ as [KD_STAMM/LAND_LNR_AUSL_BEZ],
	cl.ISO_CODE_AUSL as [KD_STAMM/ISO_CODE_AUSL],
	cl.EROEFF_DAT_REL as [KD_STAMM/EROEFF_DAT],
	datediff(day,convert(date,cast(cl.EROEFF_DAT_REL as varchar),112),@ddate) as [KD_DURATION],
	cl.KD_STAT_CD as [KD_STAMM/KD_STAT_CD],
	cl.KD_GWV_CD as [KD_GWV_STUF/KD_GWV_CD],
	cl.KD_GW_SYS_DT as [KD_GWV_STUF/SYS_DT],
	cl.KD_GWV_CODE_BEZ as [KD_GWV_STUF/KD_GWV_CODE_BEZ],
	cl.KD_VSB_STAT_CD as [KD_STAMM/KD_VSB_STAT_CD],
	cl.KD_VSB_STAT_BEZ as [KD_STAMM/KD_VSB_STAT_BEZ],
	cl.VSB_STAT_DAT as [KD_STAMM/VSB_STAT_DAT], 
	cl.EB_LEGAL_KAT_CD as [KD_STAMM/EB_LEGAL_KAT_CD],
	cl.BERFIRMA_NR as [KD_STAMM/BERFIRMA_NR], 
	cl.KD_KOMPL_STRKT as [KD_STAMM/KD_KOMPL_STRKT],
	cl.INTER_CMPY_CD as [KD_STAMM/INTER_CMPY_CD],
	cl.KD_VERM_STAT_CD as [KD_STAMM/KD_VERM_STAT_CD],
	kd_zus.LAND_LNR_GEBURT as [KD_STAMM_ZUS/LAND_LNR_GEBURT],
	kd_zus.KD_VERB_ZWECK_CD as [KD_STAMM_ZUS/KD_VERB_ZWECK_CD],
	tx_kd.TX_FATCA_STAT_CD as [TX_KD_PROF/TX_FATCA_STAT_CD],
	kd_vsb.KD_CHK_CD as [KD_VSB/KD_CHK_CD], 
	cast(erw.ANST_ART_CD_LIST as varchar(4000)) as [KD_KD_ERW/ANST_ART_CD_LIST],
	cast(einn.EINN_ART_CD_LIST as varchar(4000)) as [KD_KD_EINN/EINN_ART_CD_LIST],
	datediff(day,cl.mut_von,@ddate) as [KD_LAST_CHANGE],
	adr.STRASSE as [KD_ADR/STRASSE],
	adr.ADR_ORT_BEZ as [KD_ADR/ADR_ORT_BEZ],
	adr.REGION_CD as [KD_ADR/REGION_CD],
	adr.ADR_PLZ as [KD_ADR/ADR_PLZ],
	adr.ORT_ORT_BEZ as [KD_ADR/ORT_ORT_BEZ],
	adr.ORT_PLZ as [KD_ADR/ORT_PLZ],
	adr.ISO_LAND_CD as [KD_ADR/ISO_LAND_CD],
	adr.LAND_BEZ as [KD_ADR/LAND_BEZ],
	case when kdkd2.userbk_nr is not null then 1 else 0 end as [KD_KD_01],
	cast(kdkd2.KD_LNR_ZUW_LIST as varchar(4000)) as KD_LNR_ZUW_LIST,
	case when vt.userbk_nr is not null then 1 else 0 end as [VT_STAMM_01],
	cast(vt.VT_TYP_CD_LIST as varchar(4000)) as VT_TYP_CD_LIST,
	case when sr.sr_feld_bez is not null then 1 else 0 end as [SR_FELD_WERT_01],
	sr.sr_feld_bez as [SR_FELD_WERT/SR_FELD_BEZ],
	sr.feld_wert as [SR_FELD_WERT/FELD_WERT],
	isnull(st3.MAX_DAY_SUM_AMOUNT_1Y,0) as MAX_DAY_SUM_AMOUNT_1Y,
	isnull(st3.MAX_CNT_MONTH_TRANS_1Y,0) as MAX_CNT_MONTH_TRANS_1Y,
	isnull(
		cast(st3.CNT_TRANS_1Y as numeric(10,2))/
		case
			when cast(convert(varchar,sld.min_acc_dat,112) as int)/100>@NamMonth2
			then
				case
					when @NamMonth0-cast(convert(varchar,sld.min_acc_dat,112) as int)/100>12
					then @NamMonth0-cast(convert(varchar,sld.min_acc_dat,112) as int)/100-88
					else @NamMonth0-cast(convert(varchar,sld.min_acc_dat,112) as int)/100
				end
			else 12
		end
	,0) as AVG_CNT_MONTH_TRANS_1Y,
	isnull(st3.CNT_MONTH_WITH_TRANS_1Y,0) as CNT_MONTH_WITH_TRANS_1Y,
	isnull(st3.MAX_TRANSACTION_1Y,0) as MAX_TRANSACTION_1Y,
	isnull(st3.MAX_MONTH_SUM_AMOUNT_1Y,0) as MAX_MONTH_SUM_AMOUNT_1Y,
	isnull(st3.MAX_MONTH_SUM_INFLOW_1Y,0) as MAX_MONTH_SUM_INFLOW_1Y,
	isnull(st3.MAX_MONTH_SUM_OUTFLOW_1Y,0) as MAX_MONTH_SUM_OUTFLOW_1Y,
	isnull(
		case 
			when sld.min_acc_dat>=@dat2 then sld.SUM_KD_SALDO_1Y/datediff(day,sld.min_acc_dat,@dat0)
			when sld.min_acc_dat<@dat2 then sld.SUM_KD_SALDO_1Y/datediff(day,@dat2,@dat0)
			else NULL
		end
	,0) as AVG_KD_SALDO_1Y,
	isnull(sld.MAX_KD_SALDO_1Y,0) as MAX_KD_SALDO_1Y,
	--scoring
	cl_r.Risk as KD_SCORING_RISK,
	cl_r.Risk_Class as KD_SCORING_RISKCLASS,
	cl_r.RiskSpillover as KD_SCORING_RISK_SPILLOVER,
	cl_r.RiskSpillover_Class as KD_SCORING_RISKCLASS_SPILLOVER,
	--addon 02.12.2021
	isnull(datediff(day,maxdt.T_Date,@ddate),-1) as DAYS_FROM_LAST_TRANSACTION,
	--addon 01.11.2024
	substring(
		case
			when
				patindex('%[0-9]%',
					replace(replace(replace(
						case 
							when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
							then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
							when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
							then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
							when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
							then ac_b.iban_id
							else NULL
						end
					,' ',''),'-',''),'.','')
				)=3	and
				len(
					replace(replace(replace(
						case 
							when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
							then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
							when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
							then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
							when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
							then ac_b.iban_id
							else NULL
						end
					,' ',''),'-',''),'.','')
				)>=15 and
				left(
					replace(replace(replace(
						case 
							when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
							then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
							when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
							then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
							when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
							then ac_b.iban_id
							else NULL
						end
					,' ',''),'-',''),'.','')
				,2) in (select iso_land_cd from cntr)
			then
				replace(replace(replace(
						case 
							when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
							then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
							when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
							then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
							when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
							then ac_b.iban_id
							else NULL
						end
					,' ',''),'-',''),'.','')
			else NULL
		end
	,1,255) as COUNTERPARTY_IBAN,
	case
		when
			patindex('%[0-9]%',
				replace(replace(replace(
					case 
						when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
						then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
						when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
						then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
						when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
						then ac_b.iban_id
						else NULL
					end
				,' ',''),'-',''),'.','')
			)=3	and
			len(
				replace(replace(replace(
					case 
						when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
						then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
						when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
						then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
						when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
						then ac_b.iban_id
						else NULL
					end
				,' ',''),'-',''),'.','')
			)>=15 and
			left(
				replace(replace(replace(
					case 
						when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL 
						then coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
						when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL 
						then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
						when (tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NULL or tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NULL ) and isnull(cl_b.bank_flag,0)=0
						then ac_b.iban_id
						else NULL
					end
				,' ',''),'-',''),'.','')
			,2) in (select iso_land_cd from cntr)
		then 1
		else 0
	end as [COUNTERPARTY_IBAN_01],
	ltrim(rtrim(
		replace(replace(
			case
				when dscr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NOT NULL 
				then
					case 
						when charindex('(21): ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('(21): ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),charindex('; ',substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),len(s20.text_rec))
							end
						when charindex('21: ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('21: ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),charindex('; ',substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),len(s20.text_rec))
							end
						when charindex('(1): ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('(1): ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex('; ',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec))
							end
						when charindex('1: ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('1: ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex('; ',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec))
							end
						else NULL
					end
				when dscr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NOT NULL 
				then 
					case 
						when charindex('1/',ss1.adr_zeilen)>0
						then 
							case
								when charindex('; ',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen))>0
								then substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),charindex('; ',substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),len(ss1.adr_zeilen)))-1)
								else substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),len(ss1.adr_zeilen))
							end
						else ss1.adr_zeilen
					end
				else NULL
			end
		,char(13),''),char(10),char(13)+char(10))
	)) as CounterpartyDetailsText,
	substring(
		case
			when dscr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NOT NULL 
			then 
				case 
					when ss1.bic is NULL or len(ss1.bic)<=3
					then ss1.kt_id
					else ss1.bic
				end
			when dscr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NOT NULL 
			then 
				case
					when ss1.EMPF_ART_CD=1
					then 
						isnull(
							case 
								when ss1.auf_bic is NULL or len(ss1.auf_bic)<=3
								then ss1.auf_kt_id
								else ss1.auf_bic
							end
						,ss1.bic)
					else 
						case 
							when ss1.bic is NULL or len(ss1.bic)<=3
							then ss1.kt_id
							else ss1.bic
						end
				end
			when dscr.key_cd = 20
			then bn.SWIFT
			else NULL
		end
	,1,50) as CounterpartyBankSwift,
	substring(
		case
			when tr.[flag] is not null
			then
				case
					when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NOT NULL 
					then
						case 
							when charindex('(21): ',s20.text_rec)>0
							then 
								case
									when charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))>len('(21): ')
									then substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),charindex(char(10),substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),len(s20.text_rec)))-1)
									else substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),len(s20.text_rec))
								end
							when charindex('21: ',s20.text_rec)>0
							then 
								case
									when charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))>len('21: ')
									then substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),charindex(char(10),substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),len(s20.text_rec)))-1)
									else substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),len(s20.text_rec))
								end
							when charindex('(1): ',s20.text_rec)>0
							then 
								case
									when charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))>0
									then 
										case
											when patindex('%[0-9]%',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex(char(10),substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1))>0 
											then 
												case
													when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)>0
													then 
														case
															when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
															then NULL
															else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-1)
														end
													else 
														case
															when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))>0
															then NULL
															else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec))
														end
												end
											else substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex(char(10),substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1)
										end
									else 
										case
											when patindex('%[0-9]%',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))>0 
											then NULL
											else substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec))
										end
								end
							when charindex('1: ',s20.text_rec)>0
							then 
								case
									when charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))>0
									then 
										case
											when patindex('%[0-9]%',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex(char(10),substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1))>0 
											then 
												case
													when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)>0
													then 
														case
															when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
															then NULL
															else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-1)
														end
													else 
														case
															when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))>0
															then NULL
															else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec))
														end
												end
											else substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex(char(10),substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1)
										end
									else 
										case
											when patindex('%[0-9]%',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))>0 
											then NULL
											else substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec))
										end
								end
							else NULL
						end
					when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NOT NULL 
					then 
						case 
							when charindex('1/',ss1.adr_zeilen)>0
							then
								case
									when charindex(char(10),ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen))>0
									then substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),charindex(char(10),substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),len(ss1.adr_zeilen)))-1)
									else substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),len(ss1.adr_zeilen))
								end
							when substring(ltrim(replace(replace(ss1.adr_zeilen,char(10),''),char(13),'')),1,1)='/'
							then
								case
									when charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))>0
									then
										case
											when charindex(char(10),ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1)>0
											then substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1,charindex(char(10),substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1,len(ss1.adr_zeilen)))-1)
											else substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1,len(ss1.adr_zeilen))
										end
									else NULL
								end
							when charindex(char(10),ss1.adr_zeilen)>0
							then substring(ss1.adr_zeilen,1,charindex(char(10),ss1.adr_zeilen)-1)
							else ss1.adr_zeilen
						end
					else isnull(cl_b.kd_bez,cl_b.Vorname + ' ' + cl_b.Name_1)
				end
			else NULL
		end
	,1,255) as CounterpartyName,
	case
		when tr.[flag] is not null
		then
			case
				when tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is NOT NULL 
				then
					case 
						when charindex('(21): ',s20.text_rec)>0
						then 
							case
								when charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))>len('(21): ')
								then 
									case
										when charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec)))>0
										then 
											case
												when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1)<charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec)))
												then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1,charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec)))))))
												else NULL
											end
										else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1,len(s20.text_rec))
									end
								else NULL
							end
						when charindex('21: ',s20.text_rec)>0
						then 
							case
								when charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))>len('21: ')
								then 
									case
										when charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec)))>0
										then 
											case
												when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1)<charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec)))
												then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1,charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec)))))))
												else NULL
											end
										else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1,len(s20.text_rec))
									end
								else NULL
							end
						when charindex('(1): ',s20.text_rec)>0
						then 
							case
								when charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))>0
								then 
									case
										when patindex('%[0-9]%',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex(char(10),substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1))>0 
										then 
											case
												when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)>0
												then 
													case
														when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
														then NULL
														else 
															case
																when charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)>0
																then
																	case
																		when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)<charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)
																		then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1,charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)))))
																		else NULL
																	end
																else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1,len(s20.text_rec))
															end
													end
												else NULL
											end
										else 
											case
												when charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec)))>0
												then
													case
														when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)<charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)
														then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec)))))))
														else NULL
													end
												else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec))
											end
									end
								else NULL
							end
						when charindex('1: ',s20.text_rec)>0
						then 
							case
								when charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))>0
								then 
									case
										when patindex('%[0-9]%',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex(char(10),substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1))>0 
										then 
											case
												when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)>0
												then 
													case
														when charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
														then NULL
														else 
															case
																when charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)>0
																then
																	case
																		when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)<charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)
																		then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1,charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)))))
																		else NULL
																	end
																else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1,len(s20.text_rec))
															end
													end
												else NULL
											end
										else 
											case
												when charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec)))>0
												then
													case
														when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)<charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)
														then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec)))))))
														else NULL
													end
												else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec))
											end
									end
								else NULL
							end
						else NULL
					end
				when tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is NOT NULL 
				then 
					case 
						when charindex('1/',ss1.adr_zeilen)>0
						then
							case
								when charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1)>0
								then
									replace(
										case
											when charindex(char(10),ss1.adr_zeilen,len(ss1.adr_zeilen)-charindex('/1',reverse(ss1.adr_zeilen)))>0
											then substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1),charindex(char(10),ss1.adr_zeilen,len(ss1.adr_zeilen)-charindex('/1',reverse(ss1.adr_zeilen)))-charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1))
											else substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1),len(ss1.adr_zeilen))
										end
									,'1/','')
								else NULL
							end
						when substring(ltrim(replace(replace(ss1.adr_zeilen,char(10),''),char(13),'')),1,1)='/'
						then
							case
								when charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))>0
								then 
									case
										when charindex(char(10),ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1)>0
										then substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1)+1,len(ss1.adr_zeilen))
										else NULL
									end
								else NULL
							end
						when charindex(char(10),ss1.adr_zeilen)>0
						then substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen)+1,len(ss1.adr_zeilen))
						else NULL
					end
				else adr_b.Strasse + ' ' + coalesce(adr_b.ort_ort_bez,adr_b.adr_ort_bez,'')
			end
		else NULL
	end as CounterpartyAddress,
	substring(
		case 
			when charindex('(3): ',s20.text_rec)>0
			then '; '+
				ltrim(replace(replace(
					case
						when charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(3): ',s20.text_rec))))>0
						then
							case
								when charindex('; ',s20.text_rec,charindex('(3): ',s20.text_rec))>0
								then substring(s20.text_rec,
									charindex('(3): ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(3): ',s20.text_rec))))+2,
									charindex('; ',s20.text_rec,charindex('(3): ',s20.text_rec))-charindex('(3): ',s20.text_rec)+charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(3): ',s20.text_rec))))-2
									)
								else substring(s20.text_rec,
									charindex('(3): ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(3): ',s20.text_rec))))+2,
									len(s20.text_rec)
									)
							end
						else
							case
								when charindex('; ',s20.text_rec,charindex('(3): ',s20.text_rec))>0
								then substring(s20.text_rec,1,charindex('; ',s20.text_rec,charindex('(3): ',s20.text_rec))-1)
								else s20.text_rec
							end
					end
				,char(10),' '),char(13),' '))
			else
				case 
					when charindex('3: ',s20.text_rec)>0
					then '; '+
						ltrim(replace(replace(
							case
								when charindex(' ;',reverse(substring(s20.text_rec,1,charindex('3: ',s20.text_rec))))>0
								then
									case
										when charindex('; ',s20.text_rec,charindex('3: ',s20.text_rec))>0
										then substring(s20.text_rec,
											charindex('3: ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('3: ',s20.text_rec))))+2,
											charindex('; ',s20.text_rec,charindex('3: ',s20.text_rec))-charindex('3: ',s20.text_rec)+charindex(' ;',reverse(substring(s20.text_rec,1,charindex('3: ',s20.text_rec))))-2
											)
										else substring(s20.text_rec,
											charindex('3: ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('3: ',s20.text_rec))))+2,
											len(s20.text_rec)
											)
									end
								else
									case
										when charindex('; ',s20.text_rec,charindex('3: ',s20.text_rec))>0
										then substring(s20.text_rec,1,charindex('; ',s20.text_rec,charindex('3: ',s20.text_rec))-1)
										else s20.text_rec
									end
							end
						,char(10),' '),char(13),' '))
					else ''
				end
		end + 
		case 
			when charindex('(8): ',s20.text_rec)>0
			then '; '+
				ltrim(replace(replace(
					case
						when charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(8): ',s20.text_rec))))>0
						then
							case
								when charindex('; ',s20.text_rec,charindex('(8): ',s20.text_rec))>0
								then substring(s20.text_rec,
									charindex('(8): ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(8): ',s20.text_rec))))+2,
									charindex('; ',s20.text_rec,charindex('(8): ',s20.text_rec))-charindex('(8): ',s20.text_rec)+charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(8): ',s20.text_rec))))-2
									)
								else substring(s20.text_rec,
									charindex('(8): ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(8): ',s20.text_rec))))+2,
									len(s20.text_rec)
									)
							end
						else
							case
								when charindex('; ',s20.text_rec,charindex('(8): ',s20.text_rec))>0
								then substring(s20.text_rec,1,charindex('; ',s20.text_rec,charindex('(8): ',s20.text_rec))-1)
								else s20.text_rec
							end
					end
				,char(10),' '),char(13),' '))
			else
				case 
					when charindex('8: ',s20.text_rec)>0
					then '; '+
						ltrim(replace(replace(
							case
								when charindex(' ;',reverse(substring(s20.text_rec,1,charindex('8: ',s20.text_rec))))>0
								then
									case
										when charindex('; ',s20.text_rec,charindex('8: ',s20.text_rec))>0
										then substring(s20.text_rec,
											charindex('8: ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('8: ',s20.text_rec))))+2,
											charindex('; ',s20.text_rec,charindex('8: ',s20.text_rec))-charindex('8: ',s20.text_rec)+charindex(' ;',reverse(substring(s20.text_rec,1,charindex('8: ',s20.text_rec))))-2
											)
										else substring(s20.text_rec,
											charindex('8: ',s20.text_rec)-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('8: ',s20.text_rec))))+2,
											len(s20.text_rec)
											)
									end
								else
									case
										when charindex('; ',s20.text_rec,charindex('8: ',s20.text_rec))>0
										then substring(s20.text_rec,1,charindex('; ',s20.text_rec,charindex('8: ',s20.text_rec))-1)
										else s20.text_rec
									end
							end
						,char(10),' '),char(13),' '))
					else ''
				end
		end
	,3,len(s20.text_rec)) as CounterpartyMessage,
	substring(
		case
			when dscr.key_cd = 30
			then 'Post ' 
			when dscr.key_cd = 80
			then 'ESR '
			else ''
		end + 
		isnull(
			case
				when dscr.key_cd = 30 or dscr.key_cd = 80
				then
					case
						when patindex('%[0-9]%',coalesce(s25.kt_nr, s25.iban_id, s25.qr_iban_id,s20.iban_txt))>0
						then
							replace(replace(replace(replace(replace(
								coalesce(s25.kt_nr, s25.iban_id, s25.qr_iban_id,s20.iban_txt,tr.kt_id_orig)
							,char(10),''),char(9),''),char(13),''),' ',''),';','')
						when patindex('%[0-9]%',tr.kt_id_orig)>0
						then tr.kt_id_orig
						else ''
					end
				else ''
			end
		,'') +
		case
			when dscr.key_cd = 30
			then ';' + char(13) + char(10)
			when dscr.key_cd = 80
			then '; Referenz nummer ' + rtrim(substring(tr.esr_ref_nr,1,2) + ' ' + substring(tr.esr_ref_nr,3,5) + ' ' + substring(tr.esr_ref_nr,8,5) + ' ' + substring(tr.esr_ref_nr,13,5) + ' ' + substring(tr.esr_ref_nr,18,5) + ' ' + substring(tr.esr_ref_nr,23,5)) + ';' + char(13) + char(10)
			else ''
		end + 
		isnull(
			case
				when dscr.transaction_description = '<comments>' 
				then tr.comments
				when dscr.transaction_description = '<special_mark>'
				then tr.special_mark
				else dscr.transaction_description
			end
		,'') + 
		isnull(
			case 
				when dscr.key_cd=70
					or (dscr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='C') 
					or (dscr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='D')
					or (dscr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
					or (dscr.key_cd=20 and tr.sh_cd='D')
					or (dscr.key_cd in (500,510,520,530,540,550,560,570) and tr.sh_cd='D')
				then ' ' + dscr.debit_text
				when dscr.key_cd=60
					or (dscr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='D') 
					or (dscr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='C') 
					or (dscr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
					or (dscr.key_cd=20 and tr.sh_cd='C')
					or (dscr.key_cd in (500,510,520,530,540,550,560,570) and tr.sh_cd='C')
				then ' ' + dscr.credit_text
				else ''
			end
		,'') + ';' + char(13) + char(10) + 
		isnull(
			case
				when charindex('(1): ',s20.text_rec)>0 and charindex('(90): ',s20.text_rec)>0 
				then 
					case
						when charindex('; ',s20.text_rec)+2<len(substring(s20.text_rec,1,charindex('(90): ',s20.text_rec)))-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(90): ',s20.text_rec))))-1
						then substring(s20.text_rec,charindex('; ',s20.text_rec)+2,len(substring(s20.text_rec,1,charindex('(90): ',s20.text_rec)))-charindex(' ;',reverse(substring(s20.text_rec,1,charindex('(90): ',s20.text_rec))))-charindex('; ',s20.text_rec)-2)
						else ''
					end
				when charindex('(1): ',s20.text_rec)>0 and charindex('(90): ',s20.text_rec)=0 
				then 
					case
						when charindex('; ',s20.text_rec,charindex('(1): ',s20.text_rec))>0
						then substring(s20.text_rec,charindex('; ',s20.text_rec,charindex('(1): ',s20.text_rec))+2,len(s20.text_rec))
						else ''
					end
				when charindex('(1): ',s20.text_rec)=0 and charindex('(90): ',s20.text_rec)>0 
				then 
					case
						when charindex(' ;',s20.text_rec,charindex(' :(09)',reverse(s20.text_rec)))>0
						then substring(s20.text_rec,1,len(s20.text_rec)-charindex(' ;',s20.text_rec,charindex(' :(09)',reverse(s20.text_rec)))-1)
						else ''
					end
				else s20.text_rec
			end
		,'') + 
		case
			when tr.text_ext is not null and tr.text_zus is not NULL
			then 'Text: ' + tr.text_ext + ';' + char(13) + char(10) + tr.text_zus
			when tr.text_ext is not null
			then 'Text: ' + tr.text_ext
			when tr.text_zus is not NULL
			then 'Text: ' + tr.text_zus
			else ''
		end
	,1,4000) as TransactionDescription,
	case
		when tr.key_cd=70
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)+isnull(cl_b.bank_flag,0)=1)
			or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
			or tr.key_cd=20
			or tr.key_cd in (510,520,530,540,550)
			or (tr.key_cd in (500,570) and tr.sh_cd='1')
		then 1
		when tr.key_cd=60 
			or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL) 
			or tr.key_cd=560
			or (tr.key_cd in (500,570) and tr.sh_cd='2')
		then 0
		else NULL
	end as SenderMyClient,
	substring(
		case
			when tr.key_cd=70 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1)
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
				or (tr.key_cd=20 and tr.sh_cd='2')
				or tr.key_cd in (510,520,530,540,550)
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then 
				case
					when cl.kd_htyp_cd=1 and cl.vorname is not NULL and cl.name_1 is not NULL
					then cl.vorname + ' ' + cl.name_1
					when cl.kd_htyp_cd=2 and cl.vorname is not NULL and cl.name_1 is not NULL and clp.vorname is not NULL 
					then 
						case
							when cl.name_1=clp.name_1 or clp.name_1 is NULL
							then cl.vorname + ' and ' + clp.vorname + ' ' + cl.name_1
							else cl.vorname + ' ' + cl.name_1 + ' and ' + clp.vorname + ' ' + clp.name_1
						end
					else cl.kd_bez
				end
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0)
				or (tr.key_cd=20 and tr.sh_cd='1')
			then
				case
					when cl_b.kd_htyp_cd=1 and cl_b.vorname is not NULL and cl_b.name_1 is not NULL
					then cl_b.vorname + ' ' + cl_b.name_1
					when cl_b.kd_htyp_cd=2 and cl_b.vorname is not NULL and cl_b.name_1 is not NULL and clp_b.vorname is not NULL
					then 
						case
							when cl_b.name_1=clp_b.name_1 or clp_b.name_1 is NULL
							then cl_b.vorname + ' and ' + clp_b.vorname + ' ' + cl_b.name_1
							else cl_b.vorname + ' ' + cl_b.name_1 + ' and ' + clp_b.vorname + ' ' + clp_b.name_1
						end
					else cl_b.kd_bez
				end
			when tr.key_cd=560 
			then tr.adr_zeile_1_ez
			when tr.key_cd in (500,570) and tr.sh_cd='2'
			then bn.institution_name
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
			then 
				case 
					when charindex('(21): ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))>len('(21): ')
							then substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),charindex(char(10),substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),len(s20.text_rec)))-1)
							else substring(s20.text_rec,charindex('(21): ',s20.text_rec)+len('(21): '),len(s20.text_rec))
						end
					when charindex('21: ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))>len('21: ')
							then substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),charindex(char(10),substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),len(s20.text_rec)))-1)
							else substring(s20.text_rec,charindex('21: ',s20.text_rec)+len('21: '),len(s20.text_rec))
						end
					when charindex('(1): ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))>0
							then 
								case
									when patindex('%[0-9]%',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex(char(10),substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1))>0 
									then 
										case
											when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)>0
											then 
												case
													when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
													then NULL
													else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-1)
												end
											else 
												case
													when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))>0
													then NULL
													else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec))
												end
										end
									else substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex(char(10),substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1)
								end
							else 
								case
									when patindex('%[0-9]%',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))>0 
									then NULL
									else substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec))
								end
						end
					when charindex('1: ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))>0
							then 
								case
									when patindex('%[0-9]%',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex(char(10),substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1))>0 
									then 
										case
											when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)>0
											then 
												case
													when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
													then NULL
													else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-1)
												end
											else 
												case
													when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))>0
													then NULL
													else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec))
												end
										end
									else substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex(char(10),substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1)
								end
							else 
								case
									when patindex('%[0-9]%',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))>0 
									then NULL
									else substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec))
								end
						end
					else NULL
				end
			when tr.key_cd=60 
			then NULL
			else NULL
		end
	,1,255) as SenderName,
	substring(
		case
			when tr.key_cd=70 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1)
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
				or (tr.key_cd=20 and tr.sh_cd='2')
				or tr.key_cd in (510,520,530,540,550)
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then
				case 
					when adr.strasse is not NULL 
					then adr.strasse + ', '
					else ''
				end +
				case 
					when coalesce(adr.ort_plz,adr.adr_plz,adr.ort_ort_bez,adr.adr_ort_bez) is not NULL 
					then isnull(adr.ort_plz,adr.adr_plz) + ' ' + isnull(adr.ort_ort_bez,adr.adr_ort_bez) + ', '
					else ''
				end + isnull(adr.land_bez,'')
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0)
				or (tr.key_cd=20 and tr.sh_cd='1')
			then
				case 
					when adr_b.strasse is not NULL 
					then adr_b.strasse + ', '
					else ''
				end +
				case 
					when coalesce(adr_b.ort_plz,adr_b.adr_plz,adr_b.ort_ort_bez,adr_b.adr_ort_bez) is not NULL 
					then isnull(adr_b.ort_plz,adr_b.adr_plz) + ' ' + isnull(adr_b.ort_ort_bez,adr_b.adr_ort_bez) + ', '
					else ''
				end + isnull(adr_b.land_bez,'')
			when tr.key_cd in (500,570) and tr.sh_cd='2'
			then bn.address + ', ' + isnull(bn.zip,'') + ' ' + isnull(bn.city,'')
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
			then
				case 
					when charindex('(21): ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))>len('(21): ')
							then 
								case
									when charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec)))>0
									then 
										case
											when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1)<charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec)))
											then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1,charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec)))))))
											else NULL
										end
									else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(21): ',s20.text_rec))+1,len(s20.text_rec))
								end
							else NULL
						end
					when charindex('21: ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))>len('21: ')
							then 
								case
									when charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec)))>0
									then 
										case
											when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1)<charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec)))
											then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1,charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec)))))))
											else NULL
										end
									else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('21: ',s20.text_rec))+1,len(s20.text_rec))
								end
							else NULL
						end
					when charindex('(1): ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))>0
							then 
								case
									when patindex('%[0-9]%',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex(char(10),substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1))>0 
									then 
										case
											when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)>0
											then 
												case
													when charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
													then NULL
													else 
														case
															when charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)>0
															then
																case
																	when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)<charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)
																	then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1,charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1)))))
																	else NULL
																end
															else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)+1,len(s20.text_rec))
														end
												end
											else NULL
										end
									else 
										case
											when charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec)))>0
											then
												case
													when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)<charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1)
													then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,charindex('): ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex('): ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec)))))))
													else NULL
												end
											else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('(1): ',s20.text_rec))+1,len(s20.text_rec))
										end
								end
							else NULL
						end
					when charindex('1: ',s20.text_rec)>0
					then 
						case
							when charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))>0
							then 
								case
									when patindex('%[0-9]%',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex(char(10),substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1))>0 
									then 
										case
											when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)>0
											then 
												case
													when charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(char(10),substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-1))>0
													then NULL
													else 
														case
															when charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)>0
															then
																case
																	when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)<charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)
																	then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1,charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1)))))
																	else NULL
																end
															else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)+1,len(s20.text_rec))
														end
												end
											else NULL
										end
									else 
										case
											when charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec)))>0
											then
												case
													when charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)>0 and charindex(char(10),s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)<charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1)
													then substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,charindex(': ',substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec)))-charindex(char(10),reverse(substring(s20.text_rec,1,charindex(': ',s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec)))))))
													else NULL
												end
											else substring(s20.text_rec,charindex(char(10),s20.text_rec,charindex('1: ',s20.text_rec))+1,len(s20.text_rec))
										end
								end
							else NULL
						end
					else NULL
				end
			when tr.key_cd=60 or tr.key_cd=560
			then NULL
			else NULL
		end
	,1,4000) as SenderAddress,
	substring(
		case
			when tr.key_cd=70 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2')
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
				or (tr.key_cd=20 and tr.sh_cd='2')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then ac.iban_id
			when tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1' 
				or (tr.key_cd=20 and tr.sh_cd='1')
			then ac_b.iban_id
			when tr.key_cd=60 then tr.ext_ref
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
			then
				case
					when patindex('%[0-9]%',coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt))>0
					then
						replace(replace(replace(
							coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
						,' ',''),'-',''),'.','')
					else NULL
				end
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1') 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
				or tr.key_cd=560
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then NULL
			else NULL
		end
	,1,255) as SenderIban,
	substring(
		case
			when tr.key_cd=70 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2')
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
				or tr.key_cd=20
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then bn.institution_name
			when tr.key_cd=60 then 'Twint AG'
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
			then
				isnull(
					case
						when s25.bk_name is not null then s25.bk_name
						when charindex(char(10),s25.adr_zeilen)>0
						then substring(s25.adr_zeilen,1,charindex(char(10),s25.adr_zeilen)-1)
						else s25.adr_zeilen
					end
				,case when tr.key_cd=30 then 'Post' when tr.key_cd=80 then 'PostESR' else NULL end)
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1')
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
				or tr.key_cd=560
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then NULL
			else NULL
		end
	,1,255) as SenderBank,
	substring(
		case
			when tr.key_cd=70 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2')
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
				or tr.key_cd=20
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then bn.swift
			when tr.key_cd=60 then 'TWINTBIC'
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
			then
				isnull(
					case 
						when len(s25.bic_cp)>1 then s25.bic_cp
						when len(s25.bic)>1 then s25.bic
						when len(s25.kt_id)>1 then s25.kt_id
						else NULL
					end
				,case when tr.key_cd=30 then 'Post' when tr.key_cd=80 then 'PostESR' else NULL end)
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1' )
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
				or tr.key_cd=560
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then NULL
			else NULL
		end
	,1,50) as SenderSwift,
	case
		when tr.key_cd=70 
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2')
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
			or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
			or tr.key_cd=20
			or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
			or (tr.key_cd in (500,570) and tr.sh_cd='1')
		then 
			case 
				when len(ltrim(rtrim(bn.swift))) in (8,11) 
				then substring(ltrim(rtrim(bn.swift)),5,2)
				else NULL
			end
		when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
		then
			case 
				when len(ltrim(rtrim(s25.bic_cp))) in (8,11)
					and substring(ltrim(rtrim(s25.bic_cp)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(s25.bic_cp)),5,2)
				when len(ltrim(rtrim(s25.bic))) in (8,11) 
					and substring(ltrim(rtrim(s25.bic)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(s25.bic)),5,2)
				when len(ltrim(rtrim(s25.kt_id))) in (8,11) 
					and substring(ltrim(rtrim(s25.kt_id)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(s25.kt_id)),5,2)
				else
					case
						when 
							len(
								replace(replace(replace(
									coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
								,' ',''),'-',''),'.','')
							)>=15
							and
							patindex('%[0-9]%',
								replace(replace(replace(
									coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
								,' ',''),'-',''),'.','')
							)>0
							and
							substring(
								replace(replace(replace(
									coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
								,' ',''),'-',''),'.','')
							,1,2) in (select iso_land_cd from cntr)
						then
							substring(
								replace(replace(replace(
									coalesce(s25.KT_NR, s25.IBAN_ID, s25.QR_IBAN_ID,s20.iban_txt)
								,' ',''),'-',''),'.','')
							,1,2)
						else NULL
					end
			end
		when tr.key_cd = 20
		then substring(bn.swift,5,2)
		when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1' )
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
			or tr.key_cd=60
			or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
			or tr.key_cd=560
			or (tr.key_cd in (500,570) and tr.sh_cd='2')
		then NULL
		else NULL
	end as SenderBankCountry,

	case
		when tr.key_cd=60 
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)+isnull(cl_b.bank_flag,0)=1)
			or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
			or tr.key_cd=20
			or tr.key_cd in (510,520,530,540,550,560)
			or (tr.key_cd in (500,570) and tr.sh_cd='2')
		then 1
		when tr.key_cd=70 
			or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL)
			or (tr.key_cd in (500,570) and tr.sh_cd='1')
		then 0
		else NULL
	end as ReceiverMyClient,
	substring(
		case
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1)
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
				or (tr.key_cd=20 and tr.sh_cd='1')
				or tr.key_cd in (510,520,530,540,550,560)
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then
				case
					when cl.kd_htyp_cd=1 and cl.vorname is not NULL and cl.name_1 is not NULL
					then cl.vorname + ' ' + cl.name_1
					when cl.kd_htyp_cd=2 and cl.vorname is not NULL and cl.name_1 is not NULL and clp.vorname is not NULL
					then 
						case
							when cl.name_1=clp.name_1 or clp.name_1 is NULL
							then cl.vorname + ' and ' + clp.vorname + ' ' + cl.name_1
							else cl.vorname + ' ' + cl.name_1 + ' and ' + clp.vorname + ' ' + clp.name_1
						end
					else cl.kd_bez
				end
			when tr.key_cd=60 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0)
				or (tr.key_cd=20 and tr.sh_cd='2')
			then 
				case
					when cl_b.kd_htyp_cd=1 and cl_b.vorname is not NULL and cl_b.name_1 is not NULL
					then cl_b.vorname + ' ' + cl_b.name_1
					when cl_b.kd_htyp_cd=2 and cl_b.vorname is not NULL and cl_b.name_1 is not NULL and clp_b.vorname is not NULL
					then 
						case
							when cl_b.name_1=clp_b.name_1 or clp_b.name_1 is NULL
							then cl_b.vorname + ' and ' + clp_b.vorname + ' ' + cl_b.name_1
							else cl_b.vorname + ' ' + cl_b.name_1 + ' and ' + clp_b.vorname + ' ' + clp_b.name_1
						end
					else cl_b.kd_bez
				end
			when tr.key_cd in (500,570) and tr.sh_cd='1'
			then bn.institution_name
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
			then 
				case 
					when charindex('1/',ss1.adr_zeilen)>0
					then
						case
							when charindex(char(10),ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen))>0
							then substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),charindex(char(10),substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),len(ss1.adr_zeilen)))-1)
							else substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+len('1/'),len(ss1.adr_zeilen))
						end
					when substring(ltrim(replace(replace(ss1.adr_zeilen,char(10),''),char(13),'')),1,1)='/'
					then
						case
							when charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))>0
							then
								case
									when charindex(char(10),ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1)>0
									then substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1,charindex(char(10),substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1,len(ss1.adr_zeilen)))-1)
									else substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1,len(ss1.adr_zeilen))
								end
							else NULL
						end
					when charindex(char(10),ss1.adr_zeilen)>0
					then substring(ss1.adr_zeilen,1,charindex(char(10),ss1.adr_zeilen)-1)
					else ss1.adr_zeilen
				end
			when tr.key_cd=70 
			then NULL
			else NULL
		end
	,1,255) as ReceiverName,
	substring(
		case
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1)
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
				or (tr.key_cd=20 and tr.sh_cd='1')
				or tr.key_cd in (510,520,530,540,550,560)
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then
				case 
					when adr.strasse is not NULL 
					then adr.strasse + ', '
					else ''
				end +
				case 
					when coalesce(adr.ort_plz,adr.adr_plz,adr.ort_ort_bez,adr.adr_ort_bez) is not NULL 
					then isnull(adr.ort_plz,adr.adr_plz) + ' ' + isnull(adr.ort_ort_bez,adr.adr_ort_bez) + ', '
					else ''
				end + isnull(adr.land_bez,'')
			when tr.key_cd=60 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0)
				or (tr.key_cd=20 and tr.sh_cd='2')
			then
				case 
					when adr_b.strasse is not NULL 
					then adr_b.strasse + ', '
					else ''
				end +
				case 
					when coalesce(adr_b.ort_plz,adr_b.adr_plz,adr_b.ort_ort_bez,adr_b.adr_ort_bez) is not NULL 
					then isnull(adr_b.ort_plz,adr_b.adr_plz) + ' ' + isnull(adr_b.ort_ort_bez,adr_b.adr_ort_bez) + ', '
					else ''
				end + isnull(adr_b.land_bez,'')
			when tr.key_cd in (500,570) and tr.sh_cd='1'
			then bn.address + ', ' + isnull(bn.zip,'') + ' ' + isnull(bn.city,'')
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
			then
				case 
					when charindex('1/',ss1.adr_zeilen)>0
					then
						case
							when charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1)>0
							then
								replace(
									case
										when charindex(char(10),ss1.adr_zeilen,len(ss1.adr_zeilen)-charindex('/1',reverse(ss1.adr_zeilen)))>0
										then substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1),charindex(char(10),ss1.adr_zeilen,len(ss1.adr_zeilen)-charindex('/1',reverse(ss1.adr_zeilen)))-charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1))
										else substring(ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen,charindex('1/',ss1.adr_zeilen)+1),len(ss1.adr_zeilen))
									end
								,'1/','')
							else NULL
						end
					when substring(ltrim(replace(replace(ss1.adr_zeilen,char(10),''),char(13),'')),1,1)='/'
					then
						case
							when charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))>0
							then 
								case
									when charindex(char(10),ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1)>0
									then substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen,charindex('/',ss1.adr_zeilen))+1)+1,len(ss1.adr_zeilen))
									else NULL
								end
							else NULL
						end
					when charindex(char(10),ss1.adr_zeilen)>0
					then substring(ss1.adr_zeilen,charindex(char(10),ss1.adr_zeilen)+1,len(ss1.adr_zeilen))
					else NULL
				end
			when tr.key_cd=70
			then NULL
			else NULL
		end
	,1,1000) as ReceiverAddress,
	substring(
		case
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1')
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
				or (tr.key_cd=20 and tr.sh_cd='1')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
				or tr.key_cd=560
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then ac.iban_id
			when tr.key_cd=60 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
				or (tr.key_cd=20 and tr.sh_cd='2')
			then ac_b.iban_id
			when tr.key_cd=70 then tr.ext_ref
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
			then coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr)
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2') 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then NULL
			else NULL
		end
	,1,50) as ReceiverIban,
	substring(
		case
			when tr.key_cd=60 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1')
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
				or tr.key_cd=20
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
				or tr.key_cd=560
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then bn.institution_name
			when tr.key_cd=70 then 'Twint AG'
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
			then
				isnull(
					case
						when ss1.bk_name is not null then ss1.bk_name
						when ss1.empf_art_cd=1
						then
							case
								when charindex(char(10),ss1.auf_adr_zeilen)>0
								then substring(ss1.auf_adr_zeilen,1,charindex(char(10),ss1.auf_adr_zeilen)-1)
								else ss1.auf_adr_zeilen
							end
						else
							case
								when charindex(char(10),ss1.adr_zeilen)>0
								then substring(ss1.adr_zeilen,1,charindex(char(10),ss1.adr_zeilen)-1)
								else ss1.adr_zeilen
							end
					end
				,case when tr.key_cd=30 then 'Post' when tr.key_cd=80 then 'PostESR' else NULL end)
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2') 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then NULL
			else NULL
		end
	,1,255) as ReceiverBank,
	substring(
		case
			when tr.key_cd=60 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1')
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
				or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
				or tr.key_cd=20
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
				or tr.key_cd=560
				or (tr.key_cd in (500,570) and tr.sh_cd='2')
			then bn.swift
			when tr.key_cd=70 then 'TWINTBIC'
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
			then
				isnull(
					case
						when len(ss1.bic_cp)>1 then ss1.bic_cp
						when ss1.empf_art_cd=1
						then 
							case 
								when len(ss1.auf_bic)>1 then ss1.auf_bic
								when len(ss1.auf_kt_id)>1 then ss1.auf_kt_id
								when len(ss1.bic)>1 then ss1.bic
								else NULL
							end
						else 
							case 
								when len(ss1.bic)>1 then ss1.bic
								when (tr.key_cd=10 or isnull(ss1.kt_id_cd,0) not in (1,3) or len(ss1.ext_kt_nr)>1) and len(ss1.kt_id)>1 then ss1.kt_id
								else NULL
							end
					end
				,case when tr.key_cd=30 then 'Post' when tr.key_cd=80 then 'PostESR' else NULL end)
			when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2') 
				or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
				or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
				or (tr.key_cd in (500,570) and tr.sh_cd='1')
			then NULL
			else NULL
		end
	,1,50) as ReceiverSwift,
	case
		when tr.key_cd=60 
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='1')
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='2')
			or (tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL)
			or tr.key_cd=20
			or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='2')
			or tr.key_cd=560
			or (tr.key_cd in (500,570) and tr.sh_cd='2')
		then substring(bn.swift,5,2)
		when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
		then
			case
				when len(ltrim(rtrim(ss1.bic_cp))) in (8,11) 
					and substring(ltrim(rtrim(ss1.bic_cp)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(ss1.bic_cp)),5,2)
				when ss1.empf_art_cd=1 
					and len(ltrim(rtrim(ss1.auf_bic))) in (8,11) 
					and substring(ltrim(rtrim(ss1.auf_bic)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(ss1.auf_bic)),5,2)
				when ss1.empf_art_cd=1 
					and len(ltrim(rtrim(ss1.auf_kt_id))) in (8,11) 
					and substring(ltrim(rtrim(ss1.auf_kt_id)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(ss1.auf_kt_id)),5,2)
				when len(ltrim(rtrim(ss1.bic))) in (8,11) 
					and substring(ltrim(rtrim(ss1.bic)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(ss1.bic)),5,2)
				when (tr.key_cd=10 or isnull(ss1.kt_id_cd,0) not in (1,3) or len(ss1.ext_kt_nr)>1) 
					and len(ltrim(rtrim(ss1.kt_id))) in (8,11) 
					and substring(ltrim(rtrim(ss1.kt_id)),5,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(ss1.kt_id)),5,2)
				when len(ltrim(rtrim(coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr))))>=15
					and patindex('%[0-9]%',ltrim(rtrim(coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr))))=3
					and substring(ltrim(rtrim(coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr))),1,2) in (select iso_land_cd from cntr)
				then substring(ltrim(rtrim(coalesce(ss1.KT_NR, ss1.IBAN_ID, ss1.QR_IBAN_ID,ss1.ext_kt_nr))),1,2)
				else NULL
			end
		when (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=0 and isnull(cl_b.bank_flag,0)=1 and tr.sh_cd='2') 
			or (tr.key_cd in (40,50,90,100) and isnull(cl.bank_flag,0)=1 and isnull(cl_b.bank_flag,0)=0 and tr.sh_cd='1')
			or tr.key_cd=70
			or (tr.key_cd in (510,520,530,540,550) and tr.sh_cd='1')
			or (tr.key_cd in (500,570) and tr.sh_cd='1')
		then NULL
		else NULL
	end as ReceiverBankCountry,
	substring(
		case 
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='E' and tr.auf_zv_adr_eff_lnr is not NULL
			then 
				isnull(s25.counterparty_details,
					case
						when charindex('(1): ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('(1): ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),charindex('; ',substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('(1): ',s20.text_rec)+len('(1): '),len(s20.text_rec))
							end + ';' + char(13) + char(10)
						when charindex('1: ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('1: ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),charindex('; ',substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('1: ',s20.text_rec)+len('1: '),len(s20.text_rec))
							end + ';' + char(13) + char(10)
						else ''
					end +
					case
						when charindex('(90): ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('(90): ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('(90): ',s20.text_rec)+len('(90): '),charindex('; ',substring(s20.text_rec,charindex('(90): ',s20.text_rec)+len('(90): '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('(90): ',s20.text_rec)+len('(90): '),len(s20.text_rec))
							end + ';' + char(13) + char(10)
						when charindex('90: ',s20.text_rec)>0
						then 
							case
								when charindex('; ',s20.text_rec,charindex('90: ',s20.text_rec))>0
								then substring(s20.text_rec,charindex('90: ',s20.text_rec)+len('90: '),charindex('; ',substring(s20.text_rec,charindex('90: ',s20.text_rec)+len('90: '),len(s20.text_rec)))-1)
								else substring(s20.text_rec,charindex('90: ',s20.text_rec)+len('90: '),len(s20.text_rec))
							end + ';' + char(13) + char(10)
						else ''
					end)
			when tr.key_cd in (10,30,80) and tr.zv_eing_ausg_cd='A' and tr.zv_adr_eff_lnr is not NULL
			then isnull(ss1.counterparty_details,ss1.adr_zeilen)
			when tr.key_cd=560 then adr_zeile_all 
			else NULL
		end
	,1,255) as Comments
from
	trans tr
join
	FAF.FA_MROS_KT_STAMM ac
on tr.userbk_nr=ac.userbk_nr and tr.kt_lnr=ac.kt_lnr
join
	FAF.FA_MROS_KD_STAMM cl
on ac.userbk_nr=cl.userbk_nr and ac.kd_lnr=cl.kd_lnr and cl.partner_flag=0
left join
	FAF.FA_MROS_KD_STAMM clp
on ac.userbk_nr=clp.userbk_nr and ac.kd_lnr=clp.kd_lnr and clp.partner_flag=1
join
	FAF.FA_MROS_INT_PERS_CHARGE bn
on tr.userbk_nr=bn.userbk_nr_home
left join
	FAF.FA_MROS_KT_SALDO sld0
on ac.kt_lnr=sld0.kt_lnr and ac.userbk_nr=sld0.userbk_nr
left join
	FAF.FA_MROS_KT_STAMM ac_b
on tr.userbk_nr=ac_b.userbk_nr and tr.kt_lnr_b=ac_b.kt_lnr
left join
	FAF.FA_MROS_KD_STAMM cl_b
on ac_b.userbk_nr=cl_b.userbk_nr and ac_b.kd_lnr=cl_b.kd_lnr and cl_b.partner_flag=0
left join
	FAF.FA_MROS_KD_STAMM clp_b
on ac_b.userbk_nr=clp_b.userbk_nr and ac_b.kd_lnr=clp_b.kd_lnr and clp_b.partner_flag=1
left join
	(select ad.*,
		row_number() over (partition by ad.userbk_nr,ad.kd_lnr order by isnull(rt_ln.Country_Risk,3) desc,ad.iso_land_cd desc,ad.strasse) as rn
	from
		FAF.FA_MROS_KD_ADR ad
	left join
		FAF.FA_MROS_LAENDERKAT rt_ln
	on rt_ln.userbk_nr=ad.userbk_nr and rt_ln.iso_land_cd=ad.iso_land_cd) adr_b
on adr_b.userbk_nr=cl_b.userbk_nr and adr_b.kd_lnr=cl_b.kd_lnr and adr_b.rn=1
--left join
--	(select userbk_nr,kd_lnr,aum_bwrg,aum_dat_von,
--		row_number() over (partition by userbk_nr,kd_lnr order by aum_dat_von desc) as rn
--	from FAF.FA_MROS_KD_AUM where aum_dat_von<@ddate) aum
--on aum.userbk_nr=cl.userbk_nr and aum.kd_lnr=cl.kd_lnr and aum.rn=1
left join
	(select kt.userbk_nr,kt.kd_lnr,sum(isnull(kts.saldo_bwrg,0)) as KD_TOTAL_SALDO
	from
		FAF.FA_MROS_KT_STAMM kt
	left join
		FAF.FA_MROS_KT_SALDO kts
	on kt.userbk_nr=kts.userbk_nr and kt.kt_lnr=kts.kt_lnr
	group by kt.userbk_nr,kt.kd_lnr) sldall
on sldall.userbk_nr=cl.userbk_nr and sldall.kd_lnr=cl.kd_lnr
left join
	(select ad.*,row_number() over (partition by userbk_nr,kd_lnr order by iso_land_cd desc,strasse) as rn from FAF.FA_MROS_KD_ADR ad) adr
on adr.userbk_nr=cl.userbk_nr and adr.kd_lnr=cl.kd_lnr and adr.rn=1
left join
	(select distinct kdkd0.userbk_nr,kdkd0.kd_lnr 
	from
		kdkd0
	join
		FAF.FA_MROS_KD_STAMM kd
	on kd.userbk_nr=kdkd0.userbk_nr and kd.kd_lnr=kdkd0.kd_lnr_zuw and kd.partner_flag=0 
	where kd.userbk_nr=@F_BANK_ID and isnull(kd.KD_PEP_CD,'0')<>'0') kdkd1
on kdkd1.userbk_nr=cl.userbk_nr and kdkd1.kd_lnr=cl.kd_lnr
left join
	(select t0.userbk_nr,t0.kd_lnr,
		cast(substring(string_agg(convert(varchar(max),'{"kd_lnr_zuw":' + isnull(cast(t0.kd_lnr_zuw as varchar(4000)),'null') + ',"kd_kd_verb_cd":' + isnull(cast(t0.kd_kd_verb_cd as varchar(4000)),'null') + '}'),','),1,4000) as varchar(4000)) as KD_LNR_ZUW_LIST
	from 
		(select distinct userbk_nr,kd_lnr,kd_lnr_zuw,kd_kd_verb_cd from kdkd0 where kd_lnr is not NULL) t0
	group by t0.userbk_nr,t0.kd_lnr) kdkd2
on cl.userbk_nr=kdkd2.userbk_nr and cl.kd_lnr=kdkd2.kd_lnr
left join
	(select s0.userbk_nr,s0.kd_lnr,
		cast(substring(string_agg(convert(varchar(max),'{"kd_lnr_zuw":' + isnull(cast(s0.kd_lnr_zuw as varchar(4000)),'null') + ',"vt_typ_cd":' + isnull(cast(s0.vt_typ_cd as varchar(4000)),'null') + '}'),','),1,4000) as varchar(4000)) as VT_TYP_CD_LIST
	from 
		(select vts.userbk_nr,vts.kd_lnr,vtz.kd_lnr as kd_lnr_zuw,vts.vt_typ_cd
		from 
			FAF.FA_MROS_VT_STAMM vts
		left join
			FAF.FA_MROS_VT_ZUORD vtz
		on vts.userbk_nr=vtz.userbk_nr and vts.vt_lnr=vtz.vt_lnr
		where vts.userbk_nr=@F_BANK_ID and isnull(vts.ende_dat,@ddate)>=@ddate and vts.kd_lnr is not NULL
		union
		select vts.userbk_nr,vtz.kd_lnr,vts.kd_lnr as kd_lnr_zuw,vts.vt_typ_cd
		from 
			FAF.FA_MROS_VT_STAMM vts
		left join
			FAF.FA_MROS_VT_ZUORD vtz
		on vts.userbk_nr=vtz.userbk_nr and vts.vt_lnr=vtz.vt_lnr
		where vts.userbk_nr=@F_BANK_ID and isnull(vts.ende_dat,@ddate)>=@ddate and vtz.kd_lnr is not NULL) s0
	group by s0.userbk_nr,s0.kd_lnr) vt
on cl.userbk_nr=vt.userbk_nr and cl.kd_lnr=vt.kd_lnr
left join
	(select distinct userbk_nr,kd_lnr,
		first_value(sr_feld_bez) over (partition by userbk_nr,kd_lnr order by sr_feld_zus_lnr desc) as sr_feld_bez,
		first_value(Feld_wert) over (partition by userbk_nr,kd_lnr order by sr_feld_zus_lnr desc) as Feld_wert
	from FAF.FA_MROS_SR_FELD_WERT) sr
on sr.userbk_nr=cl.userbk_nr and sr.kd_lnr=cl.kd_lnr
left join
	cntr cn1
on cn1.userbk_nr=tr.userbk_nr and cn1.land_lnr=tr.land_lnr_dom
left join
	cntr cn2
on cn2.userbk_nr=tr.userbk_nr and cn2.land_lnr=tr.auf_land_lnr_dom
left join
	FAF.FA_MROS_KD_STAMM_ZUS kd_zus
on kd_zus.userbk_nr=cl.userbk_nr and kd_zus.kd_lnr=cl.kd_lnr
left join
	(select distinct userbk_nr,kd_lnr,
		first_value(KD_CHK_CD) over (partition by userbk_nr,kd_lnr order by IDDOC_CD) as KD_CHK_CD
	from FAF.FA_MROS_KD_VSB) kd_vsb
on kd_vsb.userbk_nr=cl.userbk_nr and kd_vsb.kd_lnr=cl.kd_lnr
left join
	(select distinct userbk_nr,kd_lnr,
		first_value(TX_FATCA_STAT_CD) over (partition by userbk_nr,kd_lnr order by TX_KD_PROF_LNR desc) as TX_FATCA_STAT_CD
	from FAF.FA_MROS_TX_KD_PROF) tx_kd
on tx_kd.userbk_nr=cl.userbk_nr and tx_kd.kd_lnr=cl.kd_lnr
left join
	(select s1.userbk_nr,s1.kd_lnr,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(s1.ANST_ART_CD as varchar(4000)) + '}'),','),1,4000) as varchar(4000)) as ANST_ART_CD_LIST
	from 
		(select distinct userbk_nr,kd_lnr,ANST_ART_CD from kdkde where kd_lnr is not NULL and ANST_ART_CD is not null) s1
	group by s1.userbk_nr,s1.kd_lnr) erw
on erw.userbk_nr=cl.userbk_nr and erw.kd_lnr=cl.kd_lnr
left join
	(select q0.userbk_nr,q0.kd_lnr,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(q0.EINN_ART_CD as varchar(4000)) + '}'),','),1,4000) as varchar(4000)) as EINN_ART_CD_LIST
	from 
		(select distinct userbk_nr,kd_lnr,EINN_ART_CD from kdkde where kd_lnr is not NULL and EINN_ART_CD is not null) q0
	group by q0.userbk_nr,q0.kd_lnr) einn
on einn.userbk_nr=cl.userbk_nr and einn.kd_lnr=cl.kd_lnr
left join
	(select userbk_nr,om_text_lnr,text_rec,
		replace(replace(
			case 
				when charindex('(90): ',text_rec)>0 
				then 
					case
						when charindex(char(10),substring(text_rec,charindex('(90): ',text_rec)+len('(90): '),len(text_rec)))>0
						then substring(text_rec,charindex('(90): ',text_rec)+len('(90): '),charindex(char(10),substring(text_rec,charindex('(90): ',text_rec)+len('(90): '),len(text_rec)))-1)
						else substring(text_rec,charindex('(90): ',text_rec)+len('(90): '),len(text_rec))
					end
				when charindex('90: ',text_rec)>0 
				then 
					case
						when charindex(char(10),substring(text_rec,charindex('90: ',text_rec)+len('90: '),len(text_rec)))>0
						then substring(text_rec,charindex('90: ',text_rec)+len('90: '),charindex(char(10),substring(text_rec,charindex('90: ',text_rec)+len('90: '),len(text_rec)))-1)
						else substring(text_rec,charindex('90: ',text_rec)+len('90: '),len(text_rec))
					end
				when charindex(char(10)+'6/',text_rec)>0 
				then 
					case
						when charindex(char(10),substring(text_rec,charindex(char(10)+'6/',text_rec)+len(char(10)+'6/'),len(text_rec)))>0
						then substring(text_rec,charindex(char(10)+'6/',text_rec)+len(char(10)+'6/'),charindex(char(10),substring(text_rec,charindex(char(10)+'6/',text_rec)+len(char(10)+'6/'),len(text_rec)))-1)
						else substring(text_rec,charindex(char(10)+'6/',text_rec)+len(char(10)+'6/'),len(text_rec))
					end
				when charindex('(1): ',text_rec)>0 
				then 
					case
						when charindex(char(10),substring(text_rec,charindex('(1): ',text_rec)+len('(1): '),len(text_rec)))>0
						then substring(text_rec,charindex('(1): ',text_rec)+len('(1): '),charindex(char(10),substring(text_rec,charindex('(1): ',text_rec)+len('(1): '),len(text_rec)))-1)
						else substring(text_rec,charindex('(1): ',text_rec)+len('(1): '),len(text_rec))
					end
				when charindex('1: ',text_rec)>0 
				then 
					case
						when charindex(char(10),substring(text_rec,charindex('1: ',text_rec)+len('1: '),len(text_rec)))>0
						then substring(text_rec,charindex('1: ',text_rec)+len('1: '),charindex(char(10),substring(text_rec,charindex('1: ',text_rec)+len('1: '),len(text_rec)))-1)
						else substring(text_rec,charindex('1: ',text_rec)+len('1: '),len(text_rec))
					end
				else NULL
			end
		,'/C/',''),'/','') as iban_txt
	from FAF.FA_MROS_OM_TEXT) s20 
on tr.userbk_nr=s20.userbk_nr and tr.om_text_lnr=s20.om_text_lnr
left join
	FAF.FA_MROS_ZV_ADR_EFF s25 
on tr.userbk_nr=s25.userbk_nr and tr.auf_zv_adr_eff_lnr=s25.zv_adr_eff_lnr
left join
	FAF.FA_MROS_ZV_ADR_EFF ss1 
on tr.userbk_nr=ss1.userbk_nr and tr.zv_adr_eff_lnr=ss1.zv_adr_eff_lnr
left join
	FAF.TXM_CLIENT_RISK_table cl_r
on cl_r.UserBank_id=cl.userbk_nr and cl_r.Customer_id=cl.kd_lnr
left join
	(select userbk_nr,kd_lnr,
		max(max_Dsum_Amount) as MAX_DAY_SUM_AMOUNT_1Y,
		max(M_cnt) as MAX_CNT_MONTH_TRANS_1Y,
		sum(M_cnt) as CNT_TRANS_1Y,
		count(*) as CNT_MONTH_WITH_TRANS_1Y,
		max(max_M_Amount) as MAX_TRANSACTION_1Y,
		max(sum_M_Amount) as MAX_MONTH_SUM_AMOUNT_1Y,
		max(sum_M_IN_Amount) as MAX_MONTH_SUM_INFLOW_1Y,
		max(sum_M_OUT_Amount) as MAX_MONTH_SUM_OUTFLOW_1Y
	from
		(select userbk_nr,kd_lnr,T_Month,
			sum(sum_D_Amount) as sum_M_Amount,
			sum(sum_D_IN_Amount) as sum_M_IN_Amount,
			sum(sum_D_OUT_Amount) as sum_M_OUT_Amount,
			max(sum_D_Amount) as max_Dsum_Amount,
			max(max_D_Amount) as max_M_Amount,
			sum(D_cnt) as M_cnt
		from
			d_stats st1
		group by userbk_nr,kd_lnr,T_Month) st2
	group by userbk_nr,kd_lnr) st3
on cl.userbk_nr=st3.userbk_nr and cl.kd_lnr=st3.kd_lnr
left join
	(select sl1.userbk_nr,sl1.kd_lnr,
		max(
			case
				when sl2.aum_dat_von<@dat2 
				then NULL
				else sl1.aum_bwrg
			end
		) as MAX_KD_SALDO_1Y,
		sum(
			case
				when sl2.aum_dat_von<@dat2 then 0
				else sl1.aum_bwrg*
					datediff(day,
						case 
							when sl1.aum_dat_von>=@dat2
							then sl1.aum_dat_von
							else @dat2
						end,
						case 
							when sl2.aum_dat_von<=@dat0
							then sl2.aum_dat_von
							else @dat0
						end)
			end
		) as SUM_KD_SALDO_1Y,
		min(sl1.aum_dat_von) as min_acc_dat
	from 
		aum sl1
	left join
		aum sl2
	on sl1.userbk_nr=sl2.userbk_nr and sl1.kd_lnr=sl2.kd_lnr and sl1.rn=sl2.rn+1
	group by sl1.userbk_nr,sl1.kd_lnr) sld
on cl.userbk_nr=sld.userbk_nr and cl.kd_lnr=sld.kd_lnr
left join
	(select userbk_nr,kd_lnr,max(T_DATE) as T_DATE from trans2 group by userbk_nr,kd_lnr) maxdt
on cl.userbk_nr=maxdt.userbk_nr and cl.kd_lnr=maxdt.kd_lnr
left join
	FAF.FA_MROS_TRANSACTION_DESCR dscr
on dscr.userbk_nr=tr.userbk_nr and tr.key_cd=dscr.key_cd
where ac.kd_lnr<>isnull(ac_b.kd_lnr,0) and isnull(cl.bank_flag,0)=0
order by tr.ID,tr.kt_lnr;


