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 @cnt_month_with_trns int = 4;
DECLARE @cnt_month_max_kpi int = 12;
DECLARE @min_PAmountCHF numeric(15,2) = 10000;
DECLARE @min_EAmountCHF numeric(15,2) = 50000;

DECLARE @dt date;
set @dt = convert(date,cast(@dday as varchar),112);

DECLARE @NamMonth int, --previous month
		@NamMonth2 int; --@cnt_month_max_kpi months later
set @NamMonth = cast(convert(varchar,dateadd(month,-1,@dt),112) as int);
set @NamMonth = (@NamMonth-(@NamMonth % 100))/100;
set @NamMonth2 = cast(convert(varchar,dateadd(month,-@cnt_month_max_kpi,@dt),112) as int);
set @NamMonth2 = (@NamMonth2-(@NamMonth2 % 100))/100;

with
	links0 as
		(select * from FAF.FA_LINKS
		where UserBank_id=@F_BANK_ID 
			and isnull(LinkOpenDate,convert(date,'19010101',112))<=@dt 
			and isnull(LinkExpiryDate,convert(date,'20991231',112))>=@dt),
	saldo as
		(select * from
			(select *,
				row_number() over (partition by UserBank_id,Account_id order by SaldoDate desc) as rn
			from FAF.FA_SALDO_HISTORY where UserBank_id=@F_BANK_ID and SaldoDate<=@dt) s0
		where rn=1)
SELECT
	-- bank-specific variables definition
	case when tr.TransactionCountry_cd<>'AT' then 1 else 0 end as FOREIGN_flag,
	case when tr.AmountLocalCurrency<=50 then 1 else 0 end as SMALL_flag,
	case when tr.TransactionType_cd = 'IZVRUECK' then 1 else 0 end as ReturnDebit_flag,
	case
		when (select top 1 charindex(dr.[Name],tr.TransactionDescription) as chi from FAF.DubiousReason_Wuestenrot dr order by charindex(dr.[Name],tr.TransactionDescription) desc)>0
		then 1 
		else 0
	end as DubiousReason_flag,
	case
		when (select top 1 charindex(cr.[Name],tr.TransactionDescription) as chi from FAF.CryptoRelated_Wuestenrot cr order by charindex(cr.[Name],tr.TransactionDescription) desc)>0
		then 1 
		else 0
	end as CryptoRelated_flag,
	case when tr.TransactionType_cd in (select TransactionType_cd from FAF.CashTransactions_Wuestenrot) then 1 else 0 end as Cash_flag,
	case when ac.AccountType_cd = 'SPAR' then 1 else 0 end as SavingsAccount_flag,
	case when tr.TransactionType_cd in ('ECSCHECK','SCHCKEIN','SCHCKRZB','SCHECK') then 1 else 0 end as ChequeDeposit_flag,
	case when charindex(cl.CustomerLastName,tr.CounterpartyName)=0 then 1 else 0 end as DifCounterparties_flag,
	case when charindex(cl.CustomerLastName,tr.IntendendRecipient)=0 then 1 else 0 end as MismatchRecipient_flag,
	case when actp.[Group] in ('SAVINGS','LOAN','CREDITCARD','BUILDINGSAVINGS','CAPITAL','LIFEINSURANCE','NONLIFEINSURANCE') then 1 else 0 end as Non_Current_Account_flag,
	case when actp.[Group] = 'LOAN' then 1 else 0 end as Loan_flag,
	case 
		when (select top 1 charindex(gm.[Name],tr.TransactionDescription) as chi from FAF.Gambling_Wuestenrot gm order by charindex(gm.[Name],tr.TransactionDescription) desc)>0 
		then 1 
		else 0 
	end as Gambling_flag,
	actp.[Group] as AccountType_group,
	case when rc.DELVO_risk='1' then 1 else 0 end as TrCountryDELVO_flag,
	case when tr.TransactionMode_cd='POS' and tr.AmountLocalCurrency>0 then 1 else 0 end as POS_Debit_positive_flag,
	case when tr.TransactionMode_cd in ('POSSTO', 'POSGUT') and tr.AmountLocalCurrency>0 then 1 else 0 end as POS_Credit_positive_flag,
	case when tr.TransactionMode_cd='ATM' and tr.AmountLocalCurrency>0 then 1 else 0 end as ATM_Debit_positive_flag,
	case when tr.TransactionMode_cd in ('ATMSTO', 'ATMGUT') and tr.AmountLocalCurrency>0 then 1 else 0 end as ATM_Credit_positive_flag,
	case when tr.TransactionMode_cd='POS' and tr.AmountLocalCurrency<0 then 1 else 0 end as POS_Debit_negative_flag,
	case when tr.TransactionMode_cd in ('POSSTO', 'POSGUT') and tr.AmountLocalCurrency<0 then 1 else 0 end as POS_Credit_negative_flag,
	case when tr.TransactionMode_cd='ATM' and tr.AmountLocalCurrency<0 then 1 else 0 end as ATM_Debit_negative_flag,
	case when tr.TransactionMode_cd in ('ATMSTO', 'ATMGUT') and tr.AmountLocalCurrency<0 then 1 else 0 end as ATM_Credit_negative_flag,
	-- END of the bank-specific variables definition

	tr.UserBank_id,
	tr.Order_id,
	tr.Order_nr,
	tr.Transaction_id,
	tr.Transaction_nr,
	tr.Account_id,
	tr.Account_nr,
	tr.CustomerGroup_id,
	tr.CustomerGroup_nr,
	tr.Branch_id, 
	tr.External_flag, 
	tr.TransactionGroup_cd, 
	tr.TransactionType_cd, 
	tr.TransactionCategory_cd, 
	tr.TransactionMode_cd, 
	tr.InflowOutflow, 
	' '+replace(rtrim(ltrim(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(tr.TransactionDescription,char(10),' '),char(9),' '),char(13),' '),',',' '),'.',' '),'_',' '),':',' '),';',' '),'/',' '),'|',' '),'(',' '),')',' '))),'  ',' ')+' ' as TransactionDescription, 
	tr.TransactionDate,
	tr.TransactionValueDate, 
	tr.AmountAccountCurrency, 
	tr.AmountTransactionCurrency, 
	tr.AmountLocalCurrency, 
	tr.TransactionCurrency_cd, 
	tr.TransactionCountry_cd, 
	replace(rtrim(ltrim(replace(replace(replace(tr.TransactionLocation,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as TransactionLocation, 
	tr.FundsType_cd, 
	tr.PaymentSystemType_cd, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyBankName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyBankName, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyBankSWIFT,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyBankSWIFT, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyBankAddress,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyBankAddress, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyBankID,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyBankID, 
	tr.CounterpartyBankIDType_cd, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyID,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyID, 
	tr.CounterpartyType_cd, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CorrespondentBankName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CorrespondentBankName, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CorrespondentBankAddress,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CorrespondentBankAddress, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CorrespondentBankSWIFT,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CorrespondentBankSWIFT, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyIBAN,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyIBAN, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyExtID,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyExtID, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyAccountNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyAccountNumber, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyName, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyAddress,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyAddress, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyDetailsText,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyDetailsText, 
	tr.CounterpartyBankCountry_cd, 
	tr.CounterpartyCountry_cd, 
	replace(rtrim(ltrim(replace(replace(replace(tr.CounterpartyMessage,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CounterpartyMessage, 
	tr.CounterpartyAccount_id, 
	tr.CounterpartyCustomerGroup_id, 
	tr.CounterpartyFundsType_cd, 
	tr.TransactionConductor_id, 
	replace(rtrim(ltrim(replace(replace(replace(tr.TransactionAuthorizedBy,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as TransactionAuthorizedBy, 
	tr.TransactionPostingDate, 
	tr.TransactionExchangeRate, 
	tr.CounterpartyExchangeRate, 
	replace(rtrim(ltrim(replace(replace(replace(tr.IntendendRecipient,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as IntendendRecipient,
	replace(rtrim(ltrim(replace(replace(replace(tr.TransactionComment,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as TransactionComment, 

	replace(rtrim(ltrim(replace(replace(replace(ac.AccountIBAN,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountIBAN, 
	replace(rtrim(ltrim(replace(replace(replace(ac.AccountLocation,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountLocation, 
	ac.AccountCurrency_cd, 
	ac.AccountOpenDate, 
	ac.AccountCloseDate, 
	ac.AccountStatus_cd, 
	ac.AccountType_cd, 
	replace(rtrim(ltrim(replace(replace(replace(ac.AccountDescription,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountDescription, 
	ac.AccountPayAmountNonEU_cd,
	replace(rtrim(ltrim(replace(replace(replace(ac.AccountPayDestNonEU,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountPayDestNonEU,
	ac.AccountSavingPeriod_cd,
	ac.AccountSavingMonthAmount_cd,
	ac.AccountAmountInitial_cd,
	ac.AccountSourceOfFunds_cd,
	ac.AccountAmountPerMonth_cd,
	replace(rtrim(ltrim(replace(replace(replace(ac.AccountRefAccountHolder,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountRefAccountHolder,
	replace(rtrim(ltrim(replace(replace(replace(ac.AccountRefIBAN,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountRefIBAN,
	replace(rtrim(ltrim(replace(replace(replace(ac.AccountComment,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AccountComment, 

	sld.SaldoLocal, 
	sld.SaldoAccount,
	sld.SaldoDate,

	cl.Customer_id,
	cl.Customer_nr,
	cl.Branch_id as CustomerBranch_id,
	cl.CustomerType_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerFirstName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerFirstName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerLastName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerLastName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerFullName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerFullName,
	cl.CustomerOpeningDate,
	cl.CustomerStatus_cd,
	cl.CustomerDateOfBirth,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPlaceOfBirth,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerPlaceOfBirth,
	cl.CustomerCountryOfBirth_cd,
	cl.CustomerDateOfDeath,
	cl.CustomerCitizenship1_cd,
	cl.CustomerCitizenship2_cd,
	cl.CustomerGender_cd,
	cl.CustomerTitle_cd,
	cl.CustomerOccupation_cd,
	cl.CustomerTaxCountry_cd,
	cl.CustomerDomicile_flag,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerIncorporationNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerIncorporationNumber,
	cl.CustomerBuisinesCategory_cd,
	cl.CustomerLegalForm_cd,
	cl.CustomerParentCompanyResid_cd,
	cl.CustomerLegalCategory_cd,
	cl.CustomerFATCAStatus_cd,
	cl.CustomerBusinessRelation_cd,
	cl.CustomerIDCheckType_cd,
	cl.CustomerReferredEAM_cd,
	cl.CustomerComplexStructure_flag,
	cl.CustomerGroupType_cd,
	cl.CustomerSizeCategory_cd,
	cl.CustomerLastChangeDate,
	cl.CustomerCivilStatus_cd,
	cl.CustomerRiskCategory_cd,
	cl.CustomerSegment_cd,
	cl.CustomerVip_flag,
	cl.CustomerCategory_cd,
	cl.CustomerCapital,
	isnull(cl.Bank_flag,0) as Bank_flag,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerCommercialName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerCommercialName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerEmail,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerEmail,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerUrl,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerUrl,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerTaxNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerTaxNumber,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerTaxRegNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerTaxRegNumber,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerMiddleName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerMiddleName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPrefixName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerPrefixName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerMotherName,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerMotherName,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerAlias,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerAlias,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerSSN,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerSSN,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPassportNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerPassportNumber,
	cl.CustomerPassportCountry_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerIDNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerIDNumber,
	cl.CustomerCitizenship3_cd,
	cl.CustomerResidence_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerSourceOfWealth,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerSourceOfWealth,
	cl.CustomerSalary_flag,
	cl.CustomerEducationLevel_cd,
	cl.CustomerNetIncome,
	cl.CustomerAddIncome,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerPhoneNumber,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as CustomerPhoneNumber,
	cl.SAR_flag,
	cl.CustomerPepStatus_cd,
	replace(rtrim(ltrim(replace(replace(replace(cl.PEP_Description,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as PEP_Description,
	cl.PEP_Country_cd,
	cl.CustomerPEPCategory_cd,
	cl.CustomerSanctionStatus_flag,
	cl.CustomerCrimeStatus_flag,
	cl.CustomerInternStatus_flag,
	cl.CustomerPepStatusOnboard_flag,
	replace(rtrim(ltrim(replace(replace(replace(cl.CustomerComment,char(10),' '),char(9),' '),char(13),'; '))),' ',' ') as CustomerComment, 

	replace(rtrim(ltrim(replace(replace(replace(adr.AddressStreet,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AddressStreet, 
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressTown,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AddressTown, 
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressCity,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AddressCity, 
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressRegion,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AddressRegion, 
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressPostCode,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AddressPostCode, 
	adr.AddressCountry_cd, 
	adr.AddressType_cd, 
	adr.AddressOpenDate, 
	adr.AddressExpiryDate, 
	replace(rtrim(ltrim(replace(replace(replace(adr.AddressComment,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as AddressComment, 
	
	case
		when tr.InflowOutflow='O'
		then 1
		else 0
	end as [DEBIT],
	case
		when tr.InflowOutflow='I'
		then 1
		else 0
	end as [CREDIT],
	case
		when tr.CustomerGroup_id<>isnull(tr.CounterpartyCustomerGroup_id,'')
		then
			case
				when tr.InflowOutflow='I'
				then 1
				else 0
			end
		else 0
	end as [INFLOW],
	case
		when tr.CustomerGroup_id<>isnull(tr.CounterpartyCustomerGroup_id,'')
		then
			case
				when tr.InflowOutflow='O'
				then 1
				else 0
			end
		else 0
	end as [OUTFLOW],
	case 
		when tr.External_flag>0 and tr.InflowOutflow='I'
		then 1
		else 0
	end as [INFLOW_EXT_BANK],
	case 
		when tr.External_flag>0 and tr.InflowOutflow='O'
		then 1 
		else 0
	end as [OUTFLOW_EXT_BANK],
	case 
		when len(tr.CounterpartyIBAN)>=15
		then 1
		else 0
	end as [COUNTERPARTY_IBAN_01],
	isnull(ac_all.TOTAL_SALDO,0) as [KD_TOTAL_SALDO],
	datediff(day,ac.AccountOpenDate,@dt)  as [KT_DURATION],
	datediff(day,AccountOpenDate,AccountCloseDate) as KT_Duration_Closed,
	datediff(year,cl.CustomerDateOfBirth,@dt) as [KD_AGE],
	datediff(day,cl.CustomerOpeningDate,@dt) as [KD_DURATION],
	datediff(day,cl.CustomerLastChangeDate,@dt) as [KD_LAST_CHANGE],
	case when pepr.UserBank_id is not null then 1 else 0 end as [PEP_RELATED_01],
	lnk.LinkType_list,
	kdkd.CustomerB_id_list,
	emp.EmploymentType_list,
	fnd.FundType_list,
	convert(varchar,tr.TransactionDate,112) as [T_DATE],
	replace(convert(varchar,TransactionDate,108),':','') as T_Time,
	NULL as [W_AMOUNTCHF],
	NULL as [KD_BHVCLUSTER],
	NULL as [KD_BHV_THRESHOLD], 
	NULL as [KD_BHV_RISK],
	NULL as [MAX_DAY_SUM_AMOUNT_1Y],
	NULL as [MAX_CNT_MONTH_TRANS_1Y],
	NULL as [CNT_MONTH_WITH_TRANS_1Y],
	NULL as [MAX_TRANSACTION_1Y],
	NULL as [MAX_MONTH_SUM_AMOUNT_1Y],
	NULL as [MAX_MONTH_SUM_INFLOW_1Y],
	NULL as [MAX_MONTH_SUM_OUTFLOW_1Y],
	NULL as [AVG_KD_SALDO_1Y],
	NULL as [MAX_KD_SALDO_1Y],
	NULL as [KD_SCORING_RISK],
	NULL as [KD_SCORING_RISKCLASS],
	NULL as [KD_SCORING_RISK_SPILLOVER],
	NULL as [KD_SCORING_RISKCLASS_SPILLOVER],
	tr.ProductCode_cd,
	prcd.Description as ProductCode,
	ac.Duration as ProductDuration,
	ac.ProductChannel_cd,
	prch.Description as ProductChannel,
	ac.ActuarialReserve,
	ac.AccountBlockType_cd,
	abt.Description as AccountBlockType,
	ac.CountOwnerChange,
	isnull(ac_all.SUM_CURRENT_SALDO,0) as SUM_KD_CURRENT_SALDO,
	isnull(ac_all.SUM_SAVINGS_SALDO,0) as SUM_KD_SAVINGS_SALDO,
	isnull(ac_all.SUM_LOAN_SALDO,0) as SUM_KD_LOAN_SALDO,
	isnull(ac_all.SUM_CREDITCARD_SALDO,0) as SUM_KD_CREDITCARD_SALDO,
	isnull(ac_all.SUM_BUILDINGSAVINGS_SALDO,0) as SUM_KD_BUILDINGSAVINGS_SALDO,
	isnull(ac_all.SUM_CAPITAL_SALDO,0) as SUM_KD_CAPITAL_SALDO,
	isnull(ac_all.SUM_LIFEINSURANCE_SALDO,0) as SUM_KD_LIFEINSURANCE_SALDO,
	isnull(ac_all.SUM_NONLIFEINSURANCE_SALDO,0) as SUM_KD_NONLIFEINSURANCE_SALDO,
	isnull(ac_all.CNT_CURRENT_SALDO,0) as CNT_KD_CURRENT_SALDO,
	isnull(ac_all.CNT_SAVINGS_SALDO,0) as CNT_KD_SAVINGS_SALDO,
	isnull(ac_all.CNT_LOAN_SALDO,0) as CNT_KD_LOAN_SALDO,
	isnull(ac_all.CNT_CREDITCARD_SALDO,0) as CNT_KD_CREDITCARD_SALDO,
	isnull(ac_all.CNT_BUILDINGSAVINGS_SALDO,0) as CNT_KD_BUILDINGSAVINGS_SALDO,
	isnull(ac_all.CNT_CAPITAL_SALDO,0) as CNT_KD_CAPITAL_SALDO,
	isnull(ac_all.CNT_LIFEINSURANCE_SALDO,0) as CNT_KD_LIFEINSURANCE_SALDO,
	isnull(ac_all.CNT_NONLIFEINSURANCE_SALDO,0) as CNT_KD_NONLIFEINSURANCE_SALDO,
	case when isnull(cl.CustomerCategory_cd,'')<>'V' and agnt.UserBank_id is not NULL then 1 else 0 end as AgentContact_flag,
	case
		when tr.InflowOutflow='O' then 1
		when tr.InflowOutflow='I' and tr.CounterpartyCustomerGroup_id is not NULL then 1
		else 0
	end as SenderMyClient,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='O' then cl.CustomerFullName
			when tr.InflowOutflow='I' then tr.CounterpartyName
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as SenderName,
	substring(replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='O' then 
				adr.AddressStreet
				+case when len(adr.AddressTown)>0 then ', ' + adr.AddressTown else '' end
				+case when len(adr.AddressCity)>0 then ', ' + adr.AddressCity else '' end
				+case when len(adr.AddressRegion)>0 then ', ' + adr.AddressRegion else '' end
				+case when len(adr.AddressPostCode)>0 then ', ' + adr.AddressPostCode else '' end
				+case when len(adr.AddressCountry_cd)>0 then ', ' + adr.AddressCountry_cd else '' end
			when tr.InflowOutflow='I' then tr.CounterpartyAddress
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' '),1,4000) as SenderAddress,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='O' then ac.AccountIBAN
			when tr.InflowOutflow='I' then tr.CounterpartyIBAN
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as SenderIban,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='O' then bn.SourceInstitutionName
			when tr.InflowOutflow='I' then tr.CounterpartyBankName
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as SenderBank,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='O' then bn.SourceSWIFT
			when tr.InflowOutflow='I' then tr.CounterpartyBankSWIFT
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as SenderSwift,
	isnull(
		substring(replace(rtrim(ltrim(replace(replace(replace(
			case
				when tr.InflowOutflow='O' then bn.SourceSWIFT
				when tr.InflowOutflow='I' then tr.CounterpartyBankSWIFT
				else NULL
			end
		,char(10),' '),char(9),' '),char(13),'; '))),'  ',' '),5,2),
		substring(replace(rtrim(ltrim(replace(replace(replace(
			case
				when tr.InflowOutflow='O' then ac.AccountIBAN
				when tr.InflowOutflow='I' then tr.CounterpartyIBAN
				else NULL
			end
		,char(10),' '),char(9),' '),char(13),'; '))),'  ',' '),1,2)
	) as SenderBankCountry,
	case
		when tr.InflowOutflow='I' then 1
		when tr.InflowOutflow='O' and tr.CounterpartyCustomerGroup_id is not NULL then 1
		else 0
	end as ReceiverMyClient,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='I' then cl.CustomerFullName
			when tr.InflowOutflow='O' then tr.CounterpartyName
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as ReceiverName,
	substring(replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='I' then 
				adr.AddressStreet
				+case when len(adr.AddressTown)>0 then ', ' + adr.AddressTown else '' end
				+case when len(adr.AddressCity)>0 then ', ' + adr.AddressCity else '' end
				+case when len(adr.AddressRegion)>0 then ', ' + adr.AddressRegion else '' end
				+case when len(adr.AddressPostCode)>0 then ', ' + adr.AddressPostCode else '' end
				+case when len(adr.AddressCountry_cd)>0 then ', ' + adr.AddressCountry_cd else '' end
			when tr.InflowOutflow='O' then tr.CounterpartyAddress
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' '),1,4000) as ReceiverAddress,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='I' then ac.AccountIBAN
			when tr.InflowOutflow='O' then tr.CounterpartyIBAN
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as ReceiverIban,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='I' then bn.SourceInstitutionName
			when tr.InflowOutflow='O' then tr.CounterpartyBankName
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as ReceiverBank,
	replace(rtrim(ltrim(replace(replace(replace(
		case
			when tr.InflowOutflow='I' then bn.SourceSWIFT
			when tr.InflowOutflow='O' then tr.CounterpartyBankSWIFT
			else NULL
		end
	,char(10),' '),char(9),' '),char(13),'; '))),'  ',' ') as ReceiverSwift,
	isnull(
		substring(replace(rtrim(ltrim(replace(replace(replace(
			case
				when tr.InflowOutflow='I' then bn.SourceSWIFT
				when tr.InflowOutflow='O' then tr.CounterpartyBankSWIFT
				else NULL
			end
		,char(10),' '),char(9),' '),char(13),'; '))),'  ',' '),5,2),
		substring(replace(rtrim(ltrim(replace(replace(replace(
			case
				when tr.InflowOutflow='I' then ac.AccountIBAN
				when tr.InflowOutflow='O' then tr.CounterpartyIBAN
				else NULL
			end
		,char(10),' '),char(9),' '),char(13),'; '))),'  ',' '),1,2)
	) as ReceiverBankCountry,
	tr.CounterpartyDetailsText as Comments

	/*,
	--BHV
	case 
		when abs(tr.AmountLocalCurrency)>=(case when cl.CustomerType_cd='3' then @min_EAmountCHF else @min_PAmountCHF end)
		then  abs(tr.AmountLocalCurrency)*
			(case 
				when opcd.OperationCode is null 
				then 1.1 
				else 
					(case 
						when opcd.mean_perc>=0.25 then 0.9
						when opcd.mean_perc>=0.15 then 0.95 
						when opcd.mean_perc<=0.001 then 1.1 
						when opcd.mean_perc<=0.01 then 1.05 
						else 1 
					end)
			end)*
			exp((convert(numeric(15,2),3)*3 --tr.TransactionRisk)*tr.TransactionRisk
				-1)/36) 
		else abs(tr.AmountLocalCurrency)
	end as [W_AMOUNTCHF],
	isnull(clst1.prev_BHVCluster,0) as [KD_BHVCLUSTER],
	case 
		when thr.thr_q75_meantop_W_AmountCHF>(case when cl.CustomerType_cd='3' then @min_EAmountCHF else @min_PAmountCHF end)
		then thr.thr_q75_meantop_W_AmountCHF 
		else (case when cl.CustomerType_cd='3' then @min_EAmountCHF else @min_PAmountCHF end)
	end as [KD_BHV_THRESHOLD], 
	isnull(clst1.Risk,0) as [KD_BHV_RISK],
	isnull(clst1.prev_max_day_sum_Amount,0) as [MAX_DAY_SUM_AMOUNT_1Y],
	isnull(clst1.prev_max_N_month_trans,0) as [MAX_CNT_MONTH_TRANS_1Y],
	isnull(clst1.cnt_month,0) as [CNT_MONTH_WITH_TRANS_1Y],
	isnull(clst1.max_Amount,0) as [MAX_TRANSACTION_1Y],
	isnull(clst1.prev_max_sum_Amount,0)  as [MAX_MONTH_SUM_AMOUNT_1Y],
	isnull(clst1.prev_max_sum_Inflow,0)  as [MAX_MONTH_SUM_INFLOW_1Y],
	isnull(clst1.prev_max_sum_Outflow,0)  as [MAX_MONTH_SUM_OUTFLOW_1Y],
	isnull(sld1.avg_Saldo,0)  as [AVG_KD_SALDO_1Y],
	isnull(sld1.max_Saldo,0)  as [MAX_KD_SALDO_1Y],
	--scoring
	cl_r.Risk as [KD_SCORING_RISK],
	cl_r.RiskClass_N as [KD_SCORING_RISKCLASS],
	cl_r.RiskSpillover as [KD_SCORING_RISK_SPILLOVER],
	cl_r.RiskClass_N_Spillover as [KD_SCORING_RISKCLASS_SPILLOVER]
	*/
from
	FAF.FA_TRANSACTIONS tr
left join
	FAF.FA_REFERENCE_TABLE rt1
on tr.UserBank_id=rt1.UserBank_id and tr.TransactionType_cd=rt1.ValueBank and rt1.ReferenceType=10 and rt1.ActivDataLoad=1
left join
	FAF.FA_REFERENCE_TABLE rt2
on tr.UserBank_id=rt2.UserBank_id and tr.TransactionCategory_cd=rt2.ValueBank and rt2.ReferenceType=11 and rt2.ActivDataLoad=1
join
	FAF.FA_ACCOUNTS ac
on tr.UserBank_id=ac.UserBank_id and tr.Account_id=ac.Account_id
join
	FAF.FA_CUSTOMERS_GROUPS grp
on ac.UserBank_id=grp.UserBank_id and ac.CustomerGroup_id=grp.CustomerGroup_id
join
	FAF.FA_CUSTOMERS cl
on grp.UserBank_id=cl.UserBank_id and grp.Customer_id=cl.Customer_id and cl.Profile_code=0 and isnull(cl.Bank_flag,0)=0
left join
	saldo sld
on ac.Account_id=sld.Account_id and ac.UserBank_id=sld.UserBank_id
left join
	FAF.AccountTypes_Wuestenrot actp
on ac.AccountType_cd=actp.AccountType_cd
left join
	(select UserBank_id,Customer_id,
		sum(case when SaldoLocal>0 then SaldoLocal else 0 end) as TOTAL_SALDO,
		sum(case when [Group]='CURRENT' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_CURRENT_SALDO,
		sum(case when [Group]='SAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_SAVINGS_SALDO,
		sum(case when [Group]='LOAN' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_LOAN_SALDO,
		sum(case when [Group]='CREDITCARD' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_CREDITCARD_SALDO,
		sum(case when [Group]='BUILDINGSAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_BUILDINGSAVINGS_SALDO,
		sum(case when [Group]='CAPITAL' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_CAPITAL_SALDO,
		sum(case when [Group]='LIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_LIFEINSURANCE_SALDO,
		sum(case when [Group]='NONLIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then SaldoLocal else 0 end) as SUM_NONLIFEINSURANCE_SALDO,
		sum(case when [Group]='CURRENT' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_CURRENT_SALDO,
		sum(case when [Group]='SAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_SAVINGS_SALDO,
		sum(case when [Group]='LOAN' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_LOAN_SALDO,
		sum(case when [Group]='CREDITCARD' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_CREDITCARD_SALDO,
		sum(case when [Group]='BUILDINGSAVINGS' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_BUILDINGSAVINGS_SALDO,
		sum(case when [Group]='CAPITAL' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_CAPITAL_SALDO,
		sum(case when [Group]='LIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_LIFEINSURANCE_SALDO,
		sum(case when [Group]='NONLIFEINSURANCE' and isnull(AccountOpenDate,@dt)<=@dt and isnull(AccountCloseDate,dateadd(day,1,@dt))>@dt then 1 else 0 end) as CNT_NONLIFEINSURANCE_SALDO
	from
		(select kt.UserBank_id,grp.Customer_id,kt.Account_id,kts.SaldoDate,kt.AccountOpenDate,kt.AccountCloseDate,kt.AccountType_cd,
			case when kts.SaldoLocal>0 then kts.SaldoLocal else 0 end as SaldoLocal,act.[Group],
			row_number() over (partition by kt.UserBank_id,grp.Customer_id,kt.Account_id order by kts.SaldoDate desc) as rn 
		from 
			FAF.FA_ACCOUNTS kt
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=kt.UserBank_id and grp.CustomerGroup_id=kt.CustomerGroup_id
		left join
			FAF.FA_SALDO_HISTORY kts
		on kt.UserBank_id=kts.UserBank_id and kt.Account_id=kts.Account_id
		left join
			FAF.AccountTypes_Wuestenrot act
		on kt.AccountType_cd=act.AccountType_cd
		where kts.SaldoDate<=@dt) ss
	where rn=1
	group by UserBank_id,Customer_id) ac_all
on cl.UserBank_id=ac_all.UserBank_id and cl.Customer_id=ac_all.Customer_id
left join
	(select ad.*,
		row_number() over (partition by ad.UserBank_id,ad.Customer_id order by isnull(cntr1.RiskCategory,7) desc) as rn
	from
		FAF.FA_ADDRESSES ad
	left join
		FAF.FA_RT_COUNTRY cntr1
	on cntr1.UserBank_id=ad.UserBank_id and cntr1.Country_cd=ad.AddressCountry_cd
	where ad.UserBank_id=@F_BANK_ID 
		and isnull(ad.AddressOpenDate,convert(date,'19010101',112))<=@dt 
		and isnull(ad.AddressExpiryDate,convert(date,'20991231',112))>=@dt
		and ad.Profile_code=0) adr
on adr.UserBank_id=cl.UserBank_id and adr.Customer_id=cl.Customer_id and adr.rn=1
left join
	(select distinct links0.UserBank_id,links0.CustomerA_id 
	from
		links0
	join
		FAF.FA_CUSTOMERS_GROUPS grp
	on grp.UserBank_id=links0.UserBank_id and grp.CustomerGroup_id=links0.CustomerGroupB_id
	join
		FAF.FA_CUSTOMERS kd
	on kd.UserBank_id=grp.UserBank_id and kd.Customer_id=grp.Customer_id
	where links0.CustomerA_id<>kd.Customer_id and isnull(kd.CustomerPepStatus_cd,'0')<>'0') pepr -- bank-specific constant
on pepr.UserBank_id=cl.UserBank_id and pepr.CustomerA_id=cl.Customer_id
left join
	(select t0.UserBank_id,t0.CustomerA_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(t0.LinkType_cd as varchar(4000)) + '}'),',') within group (order by t0.LinkType_cd),1,4000) as varchar(4000)) as LinkType_list
	from 
		(select distinct UserBank_id,CustomerA_id,LinkType_cd from links0 where LinkType_cd is not NULL) t0
	group by t0.UserBank_id,t0.CustomerA_id) lnk
on cl.UserBank_id=lnk.UserBank_id and cl.Customer_id=lnk.CustomerA_id
left join
	(select t1.UserBank_id,t1.CustomerA_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(t1.CustomerB_id as varchar(4000)) + '}'),',') within group (order by t1.CustomerB_id),1,4000) as varchar(4000)) as CustomerB_id_list
	from 
		(select distinct links0.UserBank_id,links0.CustomerA_id,grp.Customer_id as CustomerB_id 
		from 
			links0
		join
			FAF.FA_CUSTOMERS_GROUPS grp
		on grp.UserBank_id=links0.UserBank_id and grp.CustomerGroup_id=links0.CustomerGroupB_id
		where links0.CustomerA_id<>grp.Customer_id) t1
	group by t1.UserBank_id,t1.CustomerA_id) kdkd
on cl.UserBank_id=kdkd.UserBank_id and cl.Customer_id=kdkd.CustomerA_id
left join
	(select s0.UserBank_id,s0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(s0.EmploymentType_cd as varchar(4000)) + '}'),',') within group (order by s0.EmploymentType_cd),1,4000) as varchar(4000)) as EmploymentType_list
	from 
		(select distinct UserBank_id,Customer_id,EmploymentType_cd 
		from FAF.FA_EMPLOYMENTS 
		where UserBank_id=@F_BANK_ID and EmploymentType_cd is not NULL
			and isnull(EmploymentOpenDate,convert(date,'19010101',112))<=@dt
			and isnull(EmploymentExpiryDate,convert(date,'20991231',112))>=@dt 			
			and Profile_code=0) s0
	group by s0.UserBank_id,s0.Customer_id) emp
on emp.UserBank_id=cl.UserBank_id and emp.Customer_id=cl.Customer_id
left join
	(select q0.UserBank_id,q0.Customer_id,
		cast(substring(string_agg(convert(varchar(max),'{' + cast(q0.FundType_cd as varchar(4000)) + '}'),',') within group (order by q0.FundType_cd),1,4000) as varchar(4000)) as FundType_list
	from 
		(select distinct UserBank_id,Customer_id,FundType_cd 
		from FAF.FA_FUNDS 
		where UserBank_id=@F_BANK_ID and FundType_cd is not NULL
			and isnull(FundOpenDate,convert(date,'19010101',112))<=@dt
			and isnull(FundExpiryDate,convert(date,'20991231',112))>=@dt) q0
	group by q0.UserBank_id,q0.Customer_id) fnd
on fnd.UserBank_id=cl.UserBank_id and fnd.Customer_id=cl.Customer_id
/*
left join
	FAF.FA_BHV_ClientStat clst
on clst.UserBank_id=cl.UserBank_id and clst.Customer_id=cl.Customer_id and clst.NameMonth = @NamMonth
left join
	(select UserBank_id,Customer_id,count(*) as cnt_month,max(meantop_W_AmountCHF) as max_meantop_W_AmountCHF,max(Risk) as Risk,
		max(BHVCluster) as prev_BHVCluster,max(max_day_sum_Amount) as prev_max_day_sum_Amount,
		max(N_month_trans) as prev_max_N_month_trans,max(sum_Amount) as prev_max_sum_Amount,
		max(sum_Inflow) as prev_max_sum_Inflow,max(sum_Outflow) as prev_max_sum_Outflow,
		max(max_Amount) as max_Amount
	from FAF.FA_BHV_ClientStat where UserBank_id=@F_BANK_ID and NameMonth>=@NamMonth2 and NameMonth<=@NamMonth 
	group by UserBank_id,Customer_id) as clst1
on clst1.UserBank_id=cl.UserBank_id and clst1.Customer_id=cl.Customer_id
left join
	(select UserBank_id,Customer_id,max(max_Saldo) as max_Saldo,
		sum(sum_Saldo/cnt_days)/count(*) as avg_Saldo
	from FAF.FA_BHV_ClientStat_d 
	where UserBank_id=@F_BANK_ID and NameMonth>=@NamMonth2 and NameMonth<=@NamMonth 
	group by UserBank_id,Customer_id) as sld1
on sld1.UserBank_id=cl.UserBank_id and sld1.Customer_id=cl.Customer_id
left join
	(select UserBank_id,CustomerType_cd,ClusterID,OperationCode,sum(perc)/count(*) as mean_perc 
	from FAF.FA_BHV_OperCode where NameMonth>=@NamMonth2 and NameMonth<=@NamMonth
	group by UserBank_id,CustomerType_cd,ClusterID,OperationCode) as opcd
on opcd.UserBank_id=clst.UserBank_id and opcd.ClusterID=clst.ClusterID and opcd.CustomerType_cd=clst.CustomerType_cd and opcd.OperationCode=tr.TransactionType_cd
left join
	FAF.FA_BHV_Thresholds thr
on thr.UserBank_id=clst.UserBank_id and thr.CustomerType_cd=clst.CustomerType_cd and thr.ClusterID=clst.ClusterID and thr.BHVCluster=clst1.prev_BHVCluster and thr.NameMonth = @NamMonth
left join
	FAF.TXM_CLIENT_RISK_Mercantil cl_r
on cl_r.UserBank_id=cl.UserBank_id and cl_r.Customer_id=cl.Customer_id
*/
left join
	FAF.FA_RT_ProductCode prcd
on prcd.UserBank_id=tr.UserBank_id and prcd.ProductCode_cd=tr.ProductCode_cd
left join
	FAF.FA_RT_ProductChannel prch
on prch.UserBank_id=ac.UserBank_id and prch.ProductChannel_cd=ac.ProductChannel_cd
left join
	FAF.FA_RT_AccountBlockType abt
on abt.UserBank_id=ac.UserBank_id and abt.AccountBlockType_cd=ac.AccountBlockType_cd
left join
	FAF.Wuestenrot_Risk_Country_List rc
on tr.TransactionCountry_cd=rc.ISO2
left join
	FAF.FA_CUSTOMERS agnt
on cl.UserBank_id=agnt.UserBank_id and agnt.CustomerCategory_cd='V' 
	and (cl.CustomerEMail=agnt.CustomerEMail or cl.CustomerPhoneNumber=agnt.CustomerPhoneNumber)
left join
	FAF.FA_BANKS bn
on bn.UserBank_id=tr.UserBank_id
where tr.UserBank_id=@F_BANK_ID and tr.TransactionDate>=@dt and tr.TransactionDate<dateadd(day,1,@dt)
order by tr.TransactionDate,tr.Transaction_id;

