SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_md_fields_info]
	@fields type_table_ais_name READONLY

as

select	
		fld_table_name, 
		fld_field_name = isnull(name, fld_field_name), -- trebuie sa fie nenul
		fld_l1 = isnull(fld_l1,name),-- trebuie sa fie nenul
		fld_l2, fld_l3, fld_dsc_l1,fld_dsc_l2, fld_dsc_l3,
		cpl_fld_field_name
into #tmp
from @fields f
	left join (
				select fld_table_name, fld_field_name, fld_l1, fld_l2, fld_l3, fld_dsc_l1,fld_dsc_l2, fld_dsc_l3, cpl_fld_field_name = convert(varchar(256),null)
				from md_fields
				
				union 
				
				select	fld_table_name, 
						fld_field_name =	case 
												when fld_table_name = 'act_cpl' then 'acpl_' + fld_field_name
												when fld_table_name = 'ris_cpl' then 'rcpl_' + fld_field_name
												when fld_table_name = 'pol_cpl' then 'pcpl_' + fld_field_name
												when fld_table_name = 'claim_cpl' then 'ccpl_' + fld_field_name
												when fld_table_name = 'claim_actors_cpl' then 'clactcpl_' + fld_field_name
											end,
						fld_l1, fld_l2, fld_l3, fld_dsc_l1,fld_dsc_l2, fld_dsc_l3, cpl_fld_field_name = fld_field_name
				from md_fields_cpl
	
				) as t on t.fld_field_name = f.name


insert into #tmp (
			fld_table_name,		fld_field_name, 
			fld_l1,		fld_l2,		fld_l3,		fld_dsc_l1,		fld_dsc_l2,		fld_dsc_l3)
select		mfc.fld_table_name, case 
									when mfc.fld_table_name = 'act_cpl' then 'acpl_' + mfc.fld_field_name + '#LABEL'
									when mfc.fld_table_name = 'ris_cpl' then 'rcpl_' + mfc.fld_field_name + '#LABEL'
									when mfc.fld_table_name = 'pol_cpl' then 'pcpl_' + mfc.fld_field_name + '#LABEL'
									when mfc.fld_table_name = 'claim_cpl' then 'ccpl_' + mfc.fld_field_name + '#LABEL'
									when mfc.fld_table_name = 'claim_actors_cpl' then 'clactcpl_' + mfc.fld_field_name + '#LABEL'
								end,
			mfc.fld_l1, mfc.fld_l2, mfc.fld_l3, mfc.fld_dsc_l1,	mfc.fld_dsc_l2, mfc.fld_dsc_l3
									
from #tmp t
	inner join md_fields_cpl mfc on		t.fld_table_name = mfc.fld_table_name
									and t.cpl_fld_field_name = mfc.fld_field_name
where isnull(fld_link_table_name,'') <> ''

select *
from #tmp
			

GO
