SET ANSI_NULLS ON
SET ANSI_PADDING ON;
SET QUOTED_IDENTIFIER ON
go


IF OBJECT_ID('FAF.FA_MROS_KD_EMAIL_XML', 'U') IS NULL
begin
	create table FAF.FA_MROS_KD_EMAIL_XML (
		[userbk_nr] numeric(4,0) NOT NULL,
		[kd_lnr] [numeric](22, 0) NOT NULL,
		[phones] [xml] NULL	
		CONSTRAINT PK_FA_MROS_KD_EMAIL_XML PRIMARY KEY (userbk_nr,kd_lnr));
end;
go

IF OBJECT_ID('FAF.FA_MROS_KD_ADR_XML', 'U') IS NULL
begin
	create table FAF.FA_MROS_KD_ADR_XML (
		[userbk_nr] numeric(4,0) NOT NULL,
		[kd_lnr] [numeric](22, 0) NOT NULL,
		[addresses] [xml] NULL	
		CONSTRAINT PK_FA_MROS_KD_ADR_XML PRIMARY KEY (userbk_nr,kd_lnr));
end;
go

IF OBJECT_ID('FAF.FA_MROS_KD_VSB_XML', 'U') IS NULL
begin
	create table FAF.FA_MROS_KD_VSB_XML (
		[userbk_nr] numeric(4,0) NOT NULL,
		[kd_lnr] [numeric](22, 0) NOT NULL,
		[identification] [xml] NULL
		CONSTRAINT PK_FA_MROS_KD_VSB_XML PRIMARY KEY (userbk_nr,kd_lnr));
end;
go

if not exists (select 1 from sys.types where is_table_type = 1 and name ='FAF_MROS_numID_t') 
	create type FAF.FAF_MROS_numID_t as table (id int identity(1,1),nID decimal(24,0));
go





--procedure cleans and fills aggregated tables
IF object_id('[FAF].[Fill_AggregatedTables_U]', 'P') IS NOT NULL
	  DROP procedure [FAF].[Fill_AggregatedTables_U]; 
go
create procedure FAF.Fill_AggregatedTables_U
as
begin
	declare @BANKS FAF.FAF_MROS_numID_t;
	declare @F_Bank_id int;
	declare @ii int;
	declare @nn int;

	truncate table [FAF].[FA_MROS_KD_EMAIL_XML]; 
	truncate table [FAF].[FA_MROS_KD_ADR_XML];
	truncate table [FAF].[FA_MROS_KD_VSB_XML];

	insert into @BANKS
	select distinct userbk_nr from FAF.FA_MROS_KD_STAMM;
	set @nn = (select count(*) from @BANKS);
	set @ii=1
	while @ii<=@nn
	begin
		select @F_Bank_id = cast(nID as int) from @BANKS where id=@ii;
		
		insert into FAF.FA_MROS_KD_EMAIL_XML
		select userbk_nr,kd_lnr,
			(select 
				FAF.GoAML_char_process(cnt.MROS_value,FAF.GoAML_get_paramV(@F_Bank_id,'tph__tph_contact_type'),FAF.GoAML_get_paramL(@F_Bank_id,'tph__tph_contact_type')) as "tph_contact_type",
				FAF.GoAML_char_process(com.MROS_value,FAF.GoAML_get_paramV(@F_Bank_id,'tph__tph_communication_type'),FAF.GoAML_get_paramL(@F_Bank_id,'tph__tph_communication_type')) as "tph_communication_type",
				FAF.GoAML_char_process(s1.EMAIL_ADR,FAF.GoAML_get_paramV(@F_Bank_id,'tph__tph_number'),FAF.GoAML_get_paramL(@F_Bank_id,'tph__tph_number')) as "tph_number",
				FAF.GoAML_char_process(
					case
						when FAF.GoAML_get_paramS(@F_Bank_id,112,1)='ok'
						then
							case 
								when FAF.GoAML_char_process(cnt.MROS_value,FAF.GoAML_get_paramV(@F_Bank_id,'tph__tph_contact_type'),FAF.GoAML_get_paramL(@F_Bank_id,'tph__tph_contact_type'))=FAF.GoAML_get_paramS(@F_Bank_id,10,1)
								then FAF.GoAML_char_process(cnt.comments,FAF.GoAML_get_paramS(@F_Bank_id,10,2),0) + '; '
								else ''
							end +
							case 
								when FAF.GoAML_char_process(com.MROS_value,FAF.GoAML_get_paramV(@F_Bank_id,'tph__tph_communication_type'),FAF.GoAML_get_paramL(@F_Bank_id,'tph__tph_communication_type'))=FAF.GoAML_get_paramS(@F_Bank_id,12,1)
								then FAF.GoAML_char_process(com.comments,FAF.GoAML_get_paramS(@F_Bank_id,12,2),0)
								else ''
							end
						else NULL
					end
				,FAF.GoAML_get_paramV(@F_Bank_id,'tph__comments'),FAF.GoAML_get_paramL(@F_Bank_id,'tph__comments'))as "comments"
			from 
				FAF.FA_MROS_KD_EMAIL s1
			left join
				FAF.FA_MROS_REFERENCE_TABLE cnt
			on s1.userbk_nr=cnt.userbk_nr and cast(s1.mail_art_cd as varchar)=cnt.finnova_value and cnt.ref_type=6
			left join
				FAF.FA_MROS_REFERENCE_TABLE com
			on s1.userbk_nr=com.userbk_nr and cast(s1.mail_art_cd as varchar)=com.finnova_value and com.ref_type=7
			where s.kd_lnr=s1.kd_lnr and s.userbk_nr=s1.userbk_nr 
				and isnull(com.MROS_value,'-') <> case when FAF.GoAML_get_paramS(@F_Bank_id,121,1)='ok' then FAF.GoAML_get_paramS(@F_Bank_id,121,2) else '' end
				and isnull(s1.mail_art_cd,'-') <> case when FAF.GoAML_get_paramS(@F_Bank_id,122,1)='ok' then FAF.GoAML_get_paramS(@F_Bank_id,122,2) else '' end
			FOR XML PATH ('phone'),type, ELEMENTS) as phones
		from
			(select distinct userbk_nr,kd_lnr from FAF.FA_MROS_KD_EMAIL where userbk_nr=@F_Bank_id) s;

		insert into FAF.FA_MROS_KD_ADR_XML
		select userbk_nr,kd_lnr,
			(select 
				FAF.GoAML_char_process(
					case
						when cast(kd.kd_htyp_cd as varchar)=FAF.GoAML_get_paramS(@F_Bank_id,8,1) then FAF.GoAML_get_paramS(@F_Bank_id,8,2)
						when cast(kd.kd_htyp_cd as varchar)=FAF.GoAML_get_paramS(@F_Bank_id,9,1) then FAF.GoAML_get_paramS(@F_Bank_id,9,2)
						when cast(kd.kd_htyp_cd as varchar)=FAF.GoAML_get_paramS(@F_Bank_id,7,1) then FAF.GoAML_get_paramS(@F_Bank_id,7,2)
						else NULL
					end
				,FAF.GoAML_get_paramV(@F_Bank_id,'tadr__address_type'),FAF.GoAML_get_paramL(@F_Bank_id,'tadr__address_type')) as "address_type",
				FAF.GoAML_char_process(s1.strasse,FAF.GoAML_get_paramV(@F_Bank_id,'tadr__address'),FAF.GoAML_get_paramL(@F_Bank_id,'tadr__address')) as "address",
				FAF.GoAML_char_process(coalesce(s1.ort_ort_bez,s1.adr_ort_bez),FAF.GoAML_get_paramV(@F_Bank_id,'tadr__city'),FAF.GoAML_get_paramL(@F_Bank_id,'tadr__city')) as "city",
				FAF.GoAML_char_process(coalesce(cast(s1.adr_plz as varchar),cast(s1.ort_plz as varchar)),FAF.GoAML_get_paramV(@F_Bank_id,'tadr__zip'),FAF.GoAML_get_paramL(@F_Bank_id,'tadr__zip')) as "zip",
				FAF.GoAML_char_process(cntr.iso_land_cd,FAF.GoAML_get_paramV(@F_Bank_id,'tadr__country_code'),FAF.GoAML_get_paramL(@F_Bank_id,'tadr__country_code')) as "country_code",
				FAF.GoAML_char_process(
					case
						when FAF.GoAML_get_paramS(@F_Bank_id,46,1)='ok'
						then st.comments
						else st.MROS_value
					end
				,FAF.GoAML_get_paramV(@F_Bank_id,'tadr__state'),FAF.GoAML_get_paramL(@F_Bank_id,'tadr__state')) as "state"
			from 
				FAF.FA_MROS_KD_ADR s1
			join
				FAF.FA_MROS_KD_STAMM kd
			on s1.userbk_nr=kd.userbk_nr and s1.kd_lnr=kd.kd_lnr and kd.partner_flag=0
			left join
				FAF.FA_MROS_INT_LAND cntr
			on cntr.iso_land_cd=s1.iso_land_cd and cntr.userbk_nr=s1.userbk_nr
			left join
				FAF.FA_MROS_REFERENCE_TABLE st
			on s1.userbk_nr=st.userbk_nr and cast(s1.region_cd as varchar)=st.finnova_value and st.ref_type=2
			where s.kd_lnr=s1.kd_lnr and s.userbk_nr=s1.userbk_nr
			FOR XML PATH ('address'),type, ELEMENTS) as addresses
		from
			(select distinct userbk_nr,kd_lnr from FAF.FA_MROS_KD_ADR where userbk_nr=@F_Bank_id) s;

		insert into FAF.FA_MROS_KD_VSB_XML
		select userbk_nr,kd_lnr,
			(select 
				FAF.GoAML_char_process(id.MROS_value,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__type'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__type')) as "type",
				FAF.GoAML_char_process(s1.KD_AUSW_NR,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__number'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__number')) as "number",
				FAF.GoAML_char_process(
					case 
						when s1.AUSSTELL_DAT is not null and s1.AUSSTELL_DAT >= convert(date,'17530101',112) and s1.AUSSTELL_DAT<=cast(sysdatetime() as date)
						then left(convert(varchar,cast(s1.AUSSTELL_DAT as datetime),126),19)
						else NULL
					end 
				,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__issue_date'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__issue_date')) as "issue_date",
				FAF.GoAML_char_process(
					case 
						when s1.ABL_DAT is not null and s1.ABL_DAT >= convert(date,'17530101',112) and s1.ABL_DAT >= coalesce(s1.AUSSTELL_DAT,convert(date,'17530101',112))
						then left(convert(varchar,cast(s1.ABL_DAT as datetime),126),19)
						else NULL
					end 
				,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__expiry_date'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__expiry_date')) as "expiry_date",
				FAF.GoAML_char_process(s1.KD_VSB_BEGLAUB,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__issued_by'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__issued_by')) as "issued_by", 
				FAF.GoAML_char_process(cntr.iso_land_cd,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__issue_country'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__issue_country')) as "issue_country",
				FAF.GoAML_char_process(
					case 
						when FAF.GoAML_char_process(id.MROS_value,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__type'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__type'))=FAF.GoAML_get_paramS(@F_Bank_id,6,1)
						then coalesce(id.comments,FAF.GoAML_get_paramS(@F_Bank_id,6,2))
						else NULL
					end 
				,FAF.GoAML_get_paramV(@F_Bank_id,'tpi__comments'),FAF.GoAML_get_paramL(@F_Bank_id,'tpi__comments')) as "comments"
			from 
				FAF.FA_MROS_KD_VSB s1
			left join
				FAF.FA_MROS_INT_LAND cntr
			on cntr.iso_land_cd=s1.iso_land_cd and cntr.userbk_nr=s1.userbk_nr
			left join
				FAF.FA_MROS_REFERENCE_TABLE id
			on s1.userbk_nr=id.userbk_nr and cast(s1.iddoc_cd as varchar)=id.finnova_value and id.ref_type=5
			where s1.kd_lnr=s.kd_lnr and s1.userbk_nr=s.userbk_nr
			FOR XML PATH ('identification'),type, ELEMENTS) as identification
		from
			(select distinct userbk_nr,kd_lnr from FAF.FA_MROS_KD_VSB where userbk_nr=@F_Bank_id) s;

		set @ii = @ii+1;
	end;

end;
go



--example
exec FAF.Fill_AggregatedTables_U;
go
