SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP FUNCTION IF EXISTS dbo.ufn_ssERP8_CollectObjects
GO
/* база: select * from sys_element
select * from dbo.ufn_ssERP8_CollectObjects(	 3		-- 1. ID of object type		
												,null	-- 2. ID of source object	
												,null	-- 3. ID of interface form	
												,null	-- 4. ID of section			
												,null	-- 5. ID of element			
												,null	-- 6. ID of table			
												,'bg'	-- 7. Current language		
											)
order by objectable_id
select * from cachereport23
select * from sys_reports

select * from dbo.ufn_ssERP8_CollectObjects(	null,					null,					null,				null,				null,				null,				'bg')
select * from dbo.ufn_ssERP8_CollectObjects(	1,						null,					null,				null,				null,				null,				'bg')
select * from dbo.ufn_ssERP8_CollectObjects(	2,						null,					null,				null,				null,				null,				'bg')
select * from dbo.ufn_ssERP8_CollectObjects(	3,						null,					null,				null,				null,				null,				'bg')

-- списък на документите в системата
select distinct objectable_id,	objectable_lang_name 
		 from dbo.ufn_ssERP8_CollectObjects(	2,						null,					null,				null,				null,				null,				'bg')
order by objectable_id
-- списък на номенклатурите в системата
select distinct objectable_id,	objectable_lang_name 
		 from dbo.ufn_ssERP8_CollectObjects(	1,						null,					null,				null,				null,				null,				'bg')
order by objectable_id

*/

-- =========================================================================================================================
-- Name			
CREATE FUNCTION dbo.ufn_ssERP8_CollectObjects
-- Author		Щерю Щерев																								
-- Create date	05/07/2024
-- Version		
--
-- Serever		MS SQL 2016																										
-- Description	Collection of system objects (documents and nomenclatures) that depend on the passed parameters
-- Returns		json [{ "object_type": <sys_systemObjects.id>, "objectable_id": <objectable_id>, "object_label": <> }]
-- Parameters
(
		 @sys_systemObjects_id	int				-- 1. ID of object type
		,@sys_objectable_id		int				-- 2. ID of source object
		,@sys_form_id			int				-- 3. ID of interface form
		,@sys_section_id		int				-- 4. ID of section
		,@sys_element_id		int				-- 5. ID of element	
		,@sys_table_id			int				-- 6. ID of table
	--	,sys_acl_resources_id	int				-- 7. ID of resource
	--	,is_translatable		bit DEFAULT 0	-- 8. Only objects contained translatebility elements
	--  ,is_exists				bit	DEFAULT 1	-- 9. Only real existings column in tables
	---------------------------------------------   
		,@lang					nvarchar(255)	--  Current language
)
-- =========================================================================================================================
RETURNS TABLE	
/*
manualUpdateFlag: No
*/
AS	RETURN	(
/*
DECLARE  @sys_systemObjects_id int=2, @sys_objectable_id int, @sys_form_id int, @sys_section_id int, @sys_element_id int, @sys_table_id int, @lang nvarchar(255)=N'bg'
--*/
/* SECTION_TYPE =
0=document title
1=document row
3 nomenclature row
4 dynamic nomenclature
5=nomenclature title
6=document map row
*/
-- select * from sys.columns
	SELECT  DISTINCT settings.*
		-- physical table status
			,case when t1.[name] is null then '0' else '1' end			-- exists NMCL/SYS table of element	
			+case when t2.[name] is null then '0' else '1' end			-- exists STATE table of element
			+case when c1.[name] is null then '0' else '1' end			-- exists COLUMN for element in NMCL/SYS table
			+case when c2.[name] is null then '0' else '1' end			-- exists COLUMN for element in STATE table
											as sys_state_element_exists	-- IS teble AND element exists
			,TYPE_NAME(c1.[system_type_id])	as SQL_element_type_sys
			,TYPE_NAME(c2.[system_type_id])	as SQL_element_type_state
			,c1.[is_nullable]				as SQL_element_nullable_sys
			,c2.[is_nullable]				as SQL_element_nullable_state
	FROM 
	( /*DECLARE  @sys_systemObjects_id int=1
				,@sys_objectable_id int=36
				,@sys_form_id int
				,@sys_section_id int
				,@sys_element_id int
				,@sys_table_id int
				,@lang nvarchar(255)=N'bg'
	-- */	
-- СТАТИЧНИ НОМЕНКЛАТУРИ 
-- DECLARE  @sys_systemObjects_id int=1,@sys_objectable_id int, @sys_form_id int, @sys_section_id int, @sys_element_id int, @sys_table_id int, @lang nvarchar(255)=N'bg'
	select
		--	nomenclature OBJECT
			 o.sys_systemObjects_id			as object_type					-- 1.тип на обекта=1	
			,o.objectable_type				as object_type_name				-- 2.Тип на обекта
			,cast(ot.[description]	
					as nvarchar(100))		as object_type_description		-- 3.Описание на типа на обекта
			,o.sys_form_id					as object_interface_form_id		-- 4.ID на интерфейсна форма
			,n.[id]							as objectable_id				-- 5.ID на обекта
			,trn.[value]				  	as objectable_lang_name			-- 6.Име на обекта на текущия език
			,n.[label]						as objectable_label				-- 7.Етикет на обекта
			,trn.[filename]					as objectable_filename			-- 8.Група на обекта в преводната таблица
		-- nmcl table
			,t.id							as objectable_table_id			-- 9.ID на таблицата определено по sys_referenceMap
			,t.[name]						as objectable_table_name	 	-- 10.Име на таблицата  определено по sys_referenceMap
			--,n.tablename						as nomenclature_table_name
		--  nomenclature MAPING
			,rm.id							as rm_id					 	-- 11.ID от sys_referenceMap
			,rm.sys_section_id				as rm_sys_section_id		 	-- 12.ID на секцията от sys_referenceMap
			,rm.sys_element_id				as rm_sys_element_id		 	-- 13.ID на елемента от sys_referenceMap
			,rm.sys_table_id				as rm_sys_table_id				-- 14.ID на таблицата от sys_referenceMap
			,rm.is_translatable				as rm_element_translatable		-- 15.Преводимост на елемента
			,rm.sys_acl_resources_id		as rm_sys_acl_resources_id		-- 16.ID на ресурс	
		-- SECTIONS
			,s.[id]							as section_id					-- 17.ID на секцията от sys_sections
			,trs.[value]					as section_lang_name		 	-- 18.Име на секцията
			,s.[type]						as section_type					-- 19.Тип на секцията
			,s.[label]						as section_label				-- 20.Етикет на секцията		
			,trs.[filename]					as section_filename				-- 21.Група на секцията в преводната таблица
			,s.[UID]						as sys_section_UID				-- 22.UID на секцията
			,s.[position]					as sys_section_position			-- 23.Позиция на секцията
		-- ELEMENTS
			,e.[id]							as element_id					-- 24.ID на елемента от sys_elements
			,rm.element_order				as element_order				-- 25.Поредност от sys_referenceMap
			,e.[label]						as element_label				-- 26.Етикет на елемента от sys_elements
			,e.[name]						as element_name					-- 27.Колона на елемента от sys_elements								
			,tre.[value]					as element_lang_name		 	-- 28.Име на елемента от sys_elements
			,tre.[filename]					as element_filename				-- 29.Група на елемента в преводната таблица
			,e.[type]						as element_type_8v				-- 30.Тип на елемента
			,e.[columnsize]					as element_precision_8v			-- 31.Дължина на елемента 
			,e.[decimalsize]				as element_scale_8v				-- 32.Десетични знаци за елемента
			,e.[defaultValue]				as element_defaultValue			-- 33.Стойност по подразбиране на елемента	
			,cast(e.[options] as nvarchar(MAX))	as [element_options]		-- 34.SQL за елемента
			,rul.[text]						as element_rules			 	-- 35.Правила за елемента
			,rc.[range_element_name]	 	as document_number_field 		-- 36.Номератор на елемента
			,rg.id							as element_number_range_id		-- 37.Диапазон на номератора на елемента
			,0								as element_is_dynamic			-- 39.Динамичен ли е елемента	
			,null							as element_dynamic_value	 	-- 40.Стойност на динамичния елемент (ако е динамичен)
			,N'static nоmenclature column'	as element_description			-- 41.Описание на елемента
			,e.is_creatable					as element_is_creatable			-- 42.Автоматично добавяне в номенклатура
		-- state table 
			,case
			-- 5=nomenclature title	translations
				when s.[type] = 5 AND rm.[is_translatable] = 1	then N'state_form_'+cast(o.sys_form_id as nvarchar(20))+N'_translations'
			-- 5=nomenclature title	
				when s.[type] = 5								then N'state_form_'+cast(o.sys_form_id as nvarchar(20))
			-- 3 nomenclature row translations
				when s.[type] = 3 AND rm.[is_translatable] = 1	then N'state_form_'+cast(o.sys_form_id as nvarchar(20))+N'_grid_'+cast(rm.[sys_section_id] as nvarchar(20))+N'_translations'
			-- 3 nomenclature row 
				when s.[type] = 3								then N'state_form_'+cast(o.sys_form_id as nvarchar(20))+N'_grid_'+cast(rm.[sys_section_id] as nvarchar(20))
			  end							as state_table_name				-- 41.Име на state таблицата
			,case
			-- 3 nomenclature row without translations
				when s.[type] = 3 AND rm.[is_translatable] = 0	then 0	/* 0 is TOP */
				else												 1		
			 end							as object_root					-- 43. Подредба за обработка /processing arrangement
	from dbo.sys_nmcl							as n
		left join [dbo].[sys_languages]			as sl	ON sl.[language]=@lang AND sl.[status]=1
			left join [dbo].[sys_translations]	as trn	ON n.[label] = trn.[label] AND trn.[filename] = N'nomenclature' AND trn.[locale] = sl.[language] AND trn.device = N'web'
		join dbo.sys_objectReference			as o	ON o.[objectable_id] = n.[id]
			left join dbo.sys_ranges_config		as rc	ON rc.objectType = o.sys_systemObjects_id AND rc.id_object = o.[objectable_id] AND rc.enumeration_type = N'automatic' COLLATE database_default
			left join dbo.sys_ranges			as rg	ON rg.id = rc.id_sys_ranges
		join dbo.sys_ReferenceMap				as rm	ON rm.sys_form_id = o.sys_form_id
			left join dbo.sys_table				as t	ON t.id = rm.[sys_table_id]
		join dbo.sys_sections					as s	ON	s.id = rm.sys_section_id
			 left join [dbo].[sys_translations]	as trs	ON	s.[label] = trs.[label] AND trs.[filename] = N'elements' AND trs.[locale] = sl.[language] AND trs.device = N'web'
		join dbo.sys_element					as e	ON	e.id = rm.sys_element_id
			 left join [dbo].[sys_translations]	as tre	ON	e.[label] = tre.[label] AND tre.[filename] = N'elements' AND tre.[locale] = sl.[language] AND tre.device = N'web'
		join dbo.sys_systemObjects				as ot	ON	o.sys_systemObjects_id = ot.id
		cross apply 
		(SELECT STRING_AGG(rules_text.validation_rule, N';') as [text]
		 FROM [dbo].[sys_validation_rules_element_rel]		 as rules_err
				left join sys_validation_rules				 as rules_text	ON rules_err.id_validation_rule=rules_text.id 
		 WHERE rules_err.sys_element_id= e.id )	as rul	

	WHERE (o.sys_systemObjects_id = 1)									-- nomenclature
	  AND (n.[id] = @sys_objectable_id	OR @sys_objectable_id is null )	-- parameter @sys_objectable_id
	  AND (o.sys_form_id = @sys_form_id	OR @sys_form_id is null )	 	-- parameter @sys_form_id
	  AND (s.[id] = @sys_section_id		OR @sys_section_id is null)		-- parameter @sys_section_id
	  AND (e.[id] = @sys_element_id		OR @sys_element_id	 is null)	-- parameter @sys_element_id
	  AND (t.[id] = @sys_table_id		OR @sys_table_id is null)		-- @sys_table_id
	  AND n.active	= 1													-- it is active
	  AND (		s.[type] = 3										 	-- row section
			OR	s.[type] = 5									 		-- title section 
			OR ISNULL(s.[type],0) = 0									-- old type section
		  )
		  
UNION ALL
-- ДИНАМИЧНИ НОМЕНКЛАТУРИ select * from sys_referenceMap_dynamic; select * from sys_element where id=14747
-- DECLARE  @sys_systemObjects_id int=1,@sys_objectable_id int, @sys_form_id int, @sys_section_id int, @sys_element_id int, @sys_table_id int, @lang nvarchar(255)=N'bg'
	select	
		-- nomenclature OBJECT
			 o.sys_systemObjects_id			as object_type					-- 1.тип на обекта=1	
			,o.objectable_type				as object_type_name				-- 2.Тип на обекта
			,cast(ot.[description]	
					as nvarchar(100))		as object_type_description		-- 3.Описание на типа на обекта
			,o.sys_form_id					as object_interface_form_id		-- 4.ID на интерфейсна форма
			,n.[id]							as objectable_id	 			-- 5.ID на обекта
			,trn.[value]					as objectable_lang_name			-- 6.Име на обекта на текущия език	
			,n.[label]						as objectable_label				-- 7.Етикет на обекта
			,trn.[filename]					as objectable_filename			-- 8.Група на обекта в преводната таблица
		-- nmcl table
			,t.id							as objectable_table_id			-- 9.ID на таблицата определено по sys_referenceMap
			,t.[name]						as objectable_table_name 		-- 10.Име на таблицата  определено по sys_referenceMap
			--,n.tablename						as nomenclature_table_name
		-- nomenclature MAPING
			,rm.id							as rm_id					 	-- 11.ID от sys_referenceMap
			,rm.sys_section_id				as rm_sys_section_id		 	-- 12.ID на секцията от sys_referenceMap
			,rm.sys_element_id				as rm_sys_element_id		 	-- 13.ID на елемента от sys_referenceMap
			,rm.sys_table_id				as rm_sys_table_id	 			-- 14.ID на таблицата от sys_referenceMap
			,rm.is_translatable				as rm_element_translatable		-- 15.Преводимост на елемента
			,rm.sys_acl_resources_id		as rm_sys_acl_resources_id		-- 16.ID на ресурс	
		-- SECTIONS
			,s.[id]							as section_id					-- 17.ID на секцията от sys_sections
			,trs.[value]				   	as section_lang_name		  	-- 18.Име на секцията
			,s.[type]						as section_type					-- 19.Тип на секцията
			,s.[label]						as section_label				-- 20.Етикет на секцията
			,trs.[filename]					as section_filename				-- 21.Група на секцията в преводната таблица
			,s.[UID]					  	as sys_section_UID				-- 22.UID на секцията
			,s.[position]					as sys_section_position			-- 23.Позиция на секцията
		-- ELEMENTS
			,e.[id]							as element_id					-- 24.ID на елемента от sys_elements
			,rm.element_order				as element_order			 	-- 25.Поредност от sys_referenceMap
			,e.[label]						as element_label			 	-- 26.Етикет на елемента от sys_elements
			,e.[name]						as element_name					-- 27.Колона на елемента от sys_elements											
			,tre.[value]				  	as element_lang_name		  	-- 28.Име на елемента от sys_elements
			,tre.[filename]					as element_filename				-- 29.Група на елемента в преводната таблица
			,e.[type]						as element_type_8v				-- 30.Тип на елемента
			,e.[columnsize]					as element_precision_8v			-- 31.Дължина на елемента 
			,e.[decimalsize]				as element_scale_8v				-- 32.Десетични знаци за елемента
			,e.[defaultValue]				as element_defaultValue			-- 33.Стойност по подразбиране на елемента	
			,cast(e.[options] as nvarchar(MAX))	as [element_options]		-- 34.SQL за елемента
			,rul.[text]						as element_rules			  	-- 35.Правила за елемента
			,rc.[range_element_name]	  	as element_number_field		  	-- 36.Номератор на елемента
			,rg.id							as element_number_range_id		-- 37.Диапазон на номератора на елемента
			,1								as element_is_dynamic			-- 38.Динамичен ли е елемента	
			,rm.[relation_element_value] 	as element_dynamic_value		-- 39.Стойност на динамичния елемент (ако е динамичен)
			,N'dynamic nоmenclature column'	as element_description			-- 40.Описание на елемента
			,e.is_creatable					as element_is_creatable			-- 41.Автоматично добавяне в номенклатура
		-- state table
			,case
			--	4 dynamic nomenclature
				when s.[type] = 4				then N'state_form_'+cast(o.sys_form_id as nvarchar(20))+N'_dynamic'
			  end							as state_table_name			 	-- 42.Име на state таблицата
			,case	
				when s.[type] = 4 AND rm.[is_translatable] = 0	then 0	/* 0 is TOP */
				else												 1
			 end							as object_root					-- 43. Подредба за обработка /processing arrangement
	from dbo.sys_nmcl							as n
		left join [dbo].[sys_languages]			as sl	ON sl.[language]=@lang AND sl.[status]=1
			left join [dbo].[sys_translations]	as trn	ON n.[label] = trn.[label] AND trn.[filename] = N'nomenclature' AND trn.[locale] = sl.[language] AND trn.device = N'web'
		join dbo.sys_objectReference		 	as o		ON o.[objectable_id] = n.[id]
			left join dbo.sys_ranges_config		as rc	ON rc.objectType = o.sys_systemObjects_id AND rc.id_object = o.[objectable_id] AND rc.enumeration_type = N'automatic' COLLATE database_default
			left join dbo.sys_ranges		 	as rg	ON rg.id = rc.id_sys_ranges
		join dbo.sys_referenceMap_dynamic		as rm	ON rm.sys_form_id = o.sys_form_id
			left join dbo.sys_table				as t		ON t.id = rm.[sys_table_id]
		join dbo.sys_sections					as s	ON	s.id = rm.sys_section_id
			 left join [dbo].[sys_translations]	as trs	ON	s.[label] = trs.[label] AND trs.[filename] = N'elements' AND trs.[locale] = sl.[language] AND trs.device = N'web'
		join dbo.sys_element				 	as e		ON	e.id = rm.sys_element_id
			 left join [dbo].[sys_translations]	as tre	ON	e.[label] = tre.[label] AND tre.[filename] = N'elements' AND tre.[locale] = sl.[language] AND tre.device = N'web'
		join dbo.sys_systemObjects				as ot	ON	o.sys_systemObjects_id = ot.id
		cross apply 
		(SELECT STRING_AGG(rules_text.validation_rule, N';') as [text]
		 FROM [dbo].[sys_validation_rules_element_rel]		 as rules_err
				left join sys_validation_rules				 as rules_text	ON rules_err.id_validation_rule=rules_text.id 
		 WHERE rules_err.sys_element_id= e.id )	as rul	

	WHERE (o.sys_systemObjects_id = 1)									-- nomenclature
	  AND (n.[id] = @sys_objectable_id	OR @sys_objectable_id is null )	-- parameter @sys_objectable_id
	  AND (o.sys_form_id	= @sys_form_id	OR @sys_form_id is null )  	-- parameter @sys_form_id
	  AND (s.[id] = @sys_section_id		OR @sys_section_id is null)		-- parameter @sys_section_id
	  AND (e.[id] = @sys_element_id		OR @sys_element_id	 is null)	-- parameter @sys_element_id
	  AND (t.[id] = @sys_table_id		OR @sys_table_id is null)		-- @sys_table_id
	  AND n.active	= 1													-- it is active
	  AND s.[type] = 4													-- dynamic section

UNION ALL
-- ДОКУМЕНТИ
-- DECLARE  @sys_systemObjects_id int=2,@sys_objectable_id int, @sys_form_id int, @sys_section_id int, @sys_element_id int, @sys_table_id int, @lang nvarchar(255)=N'bg'
	select	
		-- document OBJECT	
			 o.sys_systemObjects_id			as object_type					-- 1.тип на обекта=2
			,o.objectable_type				as object_type_name				-- 2.Тип на обекта
			,cast(ot.[description]	
					as nvarchar(100))		as object_type_description		-- 3.Описание на типа на обекта
			,o.sys_form_id					as object_interface_form_id		-- 4.ID на интерфейсна форма
			,dt.id							as objectable_id			   	-- 5.ID на обекта
			,tr1.[value]				  	as objectable_lang_name			-- 6.Име на обекта на текущия език	
			,dt.[label]						as objectable_label				-- 7.Етикет на обекта
			,tr1.[filename]					as objectable_filename			-- 8.Група на обекта в преводната таблица
		-- document table
			,t.id							as objectable_table_id			-- 9.ID на таблицата определено по sys_referenceMap
			,t.[name]						as objectable_table_name	  	-- 10.Име на таблицата  определено по sys_referenceMap
		-- document MAPING
			,rm.id							as rm_id					  	-- 11.ID от sys_referenceMap
			,rm.sys_section_id				as rm_sys_section_id		  	-- 12.ID на секцията от sys_referenceMap
			,rm.sys_element_id				as rm_sys_element_id		  	-- 13.ID на елемента от sys_referenceMap
			,rm.sys_table_id			  	as rm_sys_table_id				-- 14.ID на таблицата от sys_referenceMap
			,rm.is_translatable				as rm_element_translatable		-- 15.Преводимост на елемента
			,rm.sys_acl_resources_id		as rm_sys_acl_resources_id		-- 16.ID на ресурс	
		-- SECTIONS
			,s.[id]							as section_id					-- 17.ID на секцията от sys_sections
			,tr2.[value]				  	as section_lang_name		   	-- 18.Име на секцията
			,s.[type]						as section_type					-- 19.Тип на секцията
			,s.[label]						as section_label				-- 20.Етикет на секцията
			,tr2.[filename]					as section_filename				-- 21.Група на секцията в преводната таблица
			,s.[UID]					  	as sys_section_UID				-- 22.UID на секцията
			,s.[position]					as sys_section_position			-- 23.Позиция на секцията
		-- ELEMENTS
			,e.[id]							as element_id					-- 24.ID на елемента от sys_elements
			,rm.element_order				as element_order			  	-- 25.Поредност от sys_referenceMap
			,e.[label]						as element_label			  	-- 26.Етикет на елемента от sys_elements
			,e.[name]						as element_name					-- 27.Колона на елемента от sys_elements								
			,tr0.[value]				   	as element_lang_name		   	-- 28.Име на елемента от sys_elements
			,tr0.[filename]					as element_filename				-- 29.Група на елемента в преводната таблица
			,e.[type]						as element_type_8v				-- 30.Тип на елемента
			,e.[columnsize]					as element_precision_8v			-- 31.Дължина на елемента 
			,e.[decimalsize]				as element_scale_8v				-- 32.Десетични знаци за елемента
			,e.[defaultValue]				as element_defaultValue			-- 33.Стойност по подразбиране на елемента	
			,cast(e.[options] as nvarchar(MAX))	as [element_options]	  	-- 34.SQL за елемента
			,rul.[text]						as element_rules			   	-- 35.Правила за елемента
			,rc.[range_element_name]	  	as element_number_field			-- 36.Номератор на елемента
			,rg.id							as element_range_id				-- 37.Диапазон на номератора на елемента
			,0								as element_is_dynamic			-- 38.Динамичен ли е елемента	
			,null							as element_dynamic_value	   	-- 39.Стойност на динамичния елемент (ако е динамичен)
			,case
			 --	0=document title
			 	when s.[type] = 0		then N'document title column'
			 --	1,6=document row
			 	when s.[type] IN (1,6)	then N'document row column'
			 end							as element_description			-- 40.Описание на елемента
			,e.is_creatable					as element_is_creatable			-- 41.Автоматично добавяне в номенклатура
		-- state table
			,case
			-- 	0=document title translations
				when s.[type] = 0 AND rm.[is_translatable] = 1		then N'state_form_'+cast(rm.[sys_form_id] as nvarchar(20))+N'_translations'
			-- 	0=document title
				when s.[type] = 0									then N'state_form_'+cast(rm.[sys_form_id] as nvarchar(20))
			--	1=document row translations
				when s.[type] IN (1,6) AND rm.[is_translatable] = 1	then N'state_form_'+cast(rm.[sys_form_id] as nvarchar(20))+N'_grid_'+cast(rm.[sys_section_id] as nvarchar(20))+N'_translations'
			--	1=document row
				when s.[type] IN (1,6)								then N'state_form_'+cast(rm.[sys_form_id]as nvarchar(20))+N'_grid_'+cast(rm.[sys_section_id] as nvarchar(20))
			  end					    	as state_table_name				-- 42.Име на state таблицата
			,case
				when t.[name] = N'sys_stitle'   then 0	/* 0 is TOP */
				when t.[name] = N'sys_srow'	 	then 1
				else						    	 2
			 end							as object_root					-- 43. Подредба за обработка /processing arrangement	
	from dbo.sys_ReferenceMap 						as rm
			left join [dbo].[sys_languages]			as sl		ON sl.[language]=@lang AND sl.[status]=1
		join dbo.sys_element					  	as e		ON rm.sys_element_id = e.id
			left join [dbo].[sys_translations]		as tr0  	ON e.[label] = tr0.[label] AND tr0.device = N'web' AND tr0.filename = N'elements' AND tr0.[locale] = sl.[language]
		join dbo.sys_table							as t		ON rm.sys_table_id = t.id
		join dbo.sys_objectReference			   	as o		ON rm.sys_form_id = o.sys_form_id
			left join dbo.sys_ranges_config			as rc		ON rc.objectType = o.sys_systemObjects_id AND rc.id_object = o.[objectable_id] AND rc.enumeration_type = N'automatic' COLLATE database_default
			left join dbo.sys_ranges		  		as rg   	ON rg.id = rc.id_sys_ranges
		join dbo.sys_doctype					  	as dt   	ON o.objectable_id = dt.id
			left join [dbo].[sys_translations]		as tr1   	ON dt.[label] = tr1.[label] AND tr1.device = N'web' AND tr1.filename = N'document' AND tr1.[locale] = sl.[language]
		join dbo.sys_sections	 					as s		ON rm.sys_section_id = s.id
			left join [dbo].[sys_translations]		as tr2  	ON s.[label] = tr2.[label] AND tr2.device = N'web' AND tr2.filename = N'elements' AND tr2.[locale] = sl.[language]
		join dbo.sys_systemObjects					as ot   	ON o.sys_systemObjects_id = ot.id
		cross apply 
		(SELECT STRING_AGG(rules_text.validation_rule, N';') as [text]
		 FROM sys_validation_rules_element_rel as rules_err
			left join sys_validation_rules	   as rules_text	ON rules_err.id_validation_rule=rules_text.id 
		 WHERE rules_err.sys_element_id= e.id )		as rul	

	WHERE (o.sys_systemObjects_id = 2)									-- document
	  AND (dt.[id] = @sys_objectable_id	OR @sys_objectable_id is null )	-- parameter @sys_objectable_id
	  AND (o.sys_form_id	= @sys_form_id	OR @sys_form_id is null ) 	-- parameter @sys_form_id
	  AND (s.[id] = @sys_section_id		OR @sys_section_id is null)		-- parameter @sys_section_id
	  AND (e.[id] = @sys_element_id		OR @sys_element_id	 is null)	-- parameter @sys_element_id
	  AND (t.[id] = @sys_table_id		OR @sys_table_id is null)		-- @sys_table_id
	  AND dt.active	= 1						
	  AND (		s.[type] = 0										  	-- header section
			OR	s.[type] = 1										  	-- rows section
			OR	s.[type] = 6		)								  	-- rows map section

UNION ALL

-- ОБИКНОВЕННИ СПРАВКИ
--DECLARE  @sys_systemObjects_id int=3,@sys_objectable_id int, @sys_form_id int, @sys_section_id int, @sys_element_id int, @sys_table_id int, @lang nvarchar(255)=N'bg'
 	select	
		-- report OBJECT	
			 o.sys_systemObjects_id			as object_type					-- 1.тип на обекта=3											
			,o.objectable_type				as object_type_name				-- 2.Тип на обекта												
			,cast(ot.[description]																											
					as nvarchar(100))		as object_type_description		-- 3.Описание на типа на обекта										
			,o.sys_form_id					as object_interface_form_id		-- 4.ID на интерфейсна форма									
			,sr.[id]						as objectable_id			   	-- 5.ID на обекта												
			,tr1.[value]				  	as objectable_lang_name			-- 6.Име на обекта на текущия език								
			,sr.[label]						as objectable_label				-- 7.Етикет на обекта											
			,tr1.[filename]					as objectable_filename			-- 8.Група на обекта в преводната таблица						
		-- report table																														
			,t.id							as objectable_table_id			-- 9.ID на таблицата определено по sys_referenceMap				
			,N'cachereport'+cast(srs.[id] as nvarchar(20))																					
											as objectable_table_name	  	-- 10.Име на таблицата  определено по sys_referenceMap					
		-- report MAPING																													
			,rm.id							as rm_id					  	-- 11.ID от sys_referenceMap									
			,rm.sys_section_id				as rm_sys_section_id		  	-- 12.ID на секцията от sys_referenceMap						
			,rm.sys_element_id				as rm_sys_element_id		  	-- 13.ID на елемента от sys_referenceMap						
			,rm.sys_table_id			  	as rm_sys_table_id				-- 14.ID на таблицата от sys_referenceMap						
			,rm.is_translatable				as rm_element_translatable		-- 15.Преводимост на елемента									
			,rm.sys_acl_resources_id		as rm_sys_acl_resources_id		-- 16.ID на ресурс												
		-- SECTIONS																															
			,s.[id]							as section_id					-- 17.ID на секцията от sys_sections							
			,tr2.[value]				  	as section_lang_name		   	-- 18.Име на секцията											
			,s.[type]						as section_type					-- 19.Тип на секцията
			,s.[label]						as section_label				-- 20.Етикет на секцията
			,tr2.[filename]					as section_filename				-- 21.Група на секцията в преводната таблица					
			,s.[UID]					  	as sys_section_UID				-- 22.UID на секцията											
			,s.[position]					as sys_section_position			-- 23.Позиция на секцията																	
		-- ELEMENTS																															
			,e.[id]							as element_id					-- 24.ID на елемента от sys_elements							
			,rm.element_order				as element_order			  	-- 25.Поредност от sys_referenceMap								
			,e.[label]						as element_label			  	-- 26.Етикет на елемента от sys_elements						
			,report_settings.[name]			as element_name					-- 27.Колона на елемента от sys_elements						
			--,e_rm.[element_name]			as element_name					-- 
			,tr0.[value]				   	as element_lang_name		   	-- 28.Име на елемента от sys_elements							
			,tr0.[filename]					as element_filename				-- 29.Група на елемента в преводната таблица					
			,e.[type]						as element_type_8v				-- 30.Тип на елемента											
			,e.[columnsize]					as element_precision_8v			-- 31.Дължина на елемента 
			,e.[decimalsize]				as element_scale_8v				-- 32.Десетични знаци за елемента
			,e.[defaultValue]				as element_defaultValue			-- 33.Стойност по подразбиране на елемента						
			,cast(e.[options] as nvarchar(MAX))	as [element_options]		-- 34.SQL за елемента											
			,rul.[text]						as element_rules			   	-- 35.Правила за елемента										
			,rc.[range_element_name]	  	as element_number_field			-- 36.Номератор на елемента										
			,rg.id							as element_range_id				-- 37.Диапазон на номератора на елемента						
			,0								as element_is_dynamic			-- 38.Динамичен ли е елемента									
			,null							as element_dynamic_value	   	-- 39.Стойност на динамичния елемент (ако е динамичен)			
			,report_settings.[description]	as element_description			-- 40.Описание на елемента	
			,e.is_creatable					as element_is_creatable			-- 41.Автоматично добавяне в номенклатура
		-- state table
			,N'cachereport'+cast(srs.[id] as nvarchar(20))
										    as state_table_name				-- 42.Име на state таблицата									
			,report_settings.[type]			as object_root					-- 43. Подредба за обработка /processing arrangement			

		FROM [dbo].[sys_reports_settings]				AS srs  -- това са изгледите на справките select * from [dbo].[sys_reports_settings]
			left join [dbo].[sys_languages]				as sl	ON sl.[language]=@lang AND sl.[status]=1
				JOIN [dbo].[sys_objectReference]		AS o    ON (o.[objectable_id]=srs.[id_object] AND o.[sys_systemObjects_id]=3) -- това е нашата справка през обекти
		-- Имена на колони И филтри на справката
			LEFT JOIN	( -- declare @lang nvarchar(255) = N'bg', @sys_objectable_id int=96, @sys_systemObjects_id int=3,@sys_form_id	int,@sys_section_id int,@sys_element_id int,@sys_table_id int
						  -- през обекти	
							SELECT COALESCE(sys_RS.[id_object],sys_CS.[id_object])										as [id_object]
								  ,COALESCE(e_rm.[element_name] COLLATE database_default, sys_CS.[name])				as [name]
								  ,e_rm.[id]																			as [element_id]
								  ,case when sys_CS.[id_object] is null then N'Report filter' else N'Report column' end as [description]
								  ,case when sys_CS.[id_object] is null then 1 else 0 end								as [type]
							FROM [dbo].[sys_reports_settings]		 AS sys_RS  -- това са изгледите на справките select * from [dbo].[sys_reports_settings]
								JOIN [dbo].[sys_objectReference]	 AS o    ON (o.[objectable_id]=sys_RS.[id_object] AND o.[sys_systemObjects_id]=3) -- това е нашата справка през обекти
								LEFT JOIN (	select e.[name] as element_name,rm.* 
											from [dbo].[sys_referenceMap] as rm
											    join [dbo].[sys_element] as e   ON e.[id]=rm.[sys_element_id] 
											) as e_rm 	ON  e_rm.sys_form_id=o.[sys_form_id]
								FULL JOIN ( -- през настройки на справките	-- declare @sys_objectable_id int = 96
											SELECT sys_RS.[id_object],[name] as [name] -- INTO #t2
											FROM [dbo].[sys_reports_settings]		 AS sys_RS  -- това са изгледите на справките select * from [dbo].[sys_reports_settings]
												CROSS APPLY OPENJSON(sys_RS.[settings],'$.colSettings')
																WITH( [name] nvarchar(128) '$.name')
											WHERE sys_RS.objectType=3 AND (sys_RS.[id_object]=@sys_objectable_id OR @sys_objectable_id is null) 
										  )	as sys_CS ON sys_CS.[id_object]=sys_RS.[id_object] AND sys_CS.[name] COLLATE database_default=e_rm.[element_name] COLLATE database_default
							WHERE COALESCE(sys_RS.objectType,3)=3 AND (COALESCE(sys_RS.[id_object],sys_CS.[id_object])=@sys_objectable_id OR @sys_objectable_id is null) 
						)								AS report_settings  ON srs.[id_object]=report_settings.[id_object]
		-- през данни за справката
			LEFT JOIN [dbo].[sys_reports]				AS sr   ON srs.[id_object]=sr.[id]  -- справки -- select * from sys_reports
				LEFT JOIN [dbo].[sys_translations]		AS tr1  ON [sr].[label]=[tr1].[label] AND [tr1].[device]=N'web' AND [tr1].[locale]=sl.[language] AND [tr1].[filename] LIKE N'reports%'
			LEFT JOIN [dbo].[sys_table]					AS t    ON t.[name] = N'cachereport'+cast(srs.[id] as nvarchar(20))
				LEFT JOIN [dbo].[sys_ranges_config]		AS rc   ON rc.objectType = o.sys_systemObjects_id AND rc.id_object = o.[objectable_id] AND rc.enumeration_type = N'automatic' COLLATE database_default
				LEFT JOIN [dbo].[sys_ranges]	  		AS rg   ON rg.id = rc.id_sys_ranges
				LEFT JOIN dbo.sys_systemObjects			AS ot   ON o.sys_systemObjects_id = ot.id
			LEFT JOIN [dbo].[sys_reports_columns]		AS src	ON [src].[columnname] COLLATE database_default=report_settings.[name] COLLATE database_default AND src.[id_object]=srs.[id_object]
		-- през обекти
			LEFT JOIN (	select e.[id] as element_id
							  ,e.[name] as element_name
							  ,rm.[sys_form_id]
						from [dbo].[sys_referenceMap] as rm
						    join [dbo].[sys_element] as e   ON e.[id]=rm.[sys_element_id] 
					  ) as e_rm 
				ON  e_rm.sys_form_id=o.[sys_form_id] AND  e_rm.[element_name] COLLATE database_default=report_settings.[name] COLLATE database_default

			LEFT JOIN [dbo].[sys_element]				AS e	ON e.[id]=e_rm.[element_id]
				LEFT JOIN [dbo].[sys_referenceMap]		AS rm	ON rm.[sys_element_id]=e.[id] AND rm.[sys_form_id]=o.[sys_form_id]

					LEFT JOIN [dbo].[sys_translations]  AS tr0	ON e.[label] = tr0.[label] AND tr0.device = N'web' AND tr0.filename = N'elements'  AND tr0.[locale] = sl.[language]
				LEFT JOIN [dbo].[sys_sections]			AS s	ON rm.sys_section_id = s.id
					LEFT JOIN [dbo].[sys_translations]	AS tr2  ON s.[label] = tr2.[label] AND tr2.device = N'web' AND tr2.filename = N'elements' AND tr2.[locale] = sl.[language]
				cross apply (SELECT STRING_AGG(rules_text.validation_rule, N';') as [text]
							 FROM sys_validation_rules_element_rel as rules_err
							 	left join sys_validation_rules	   as rules_text	ON rules_err.id_validation_rule=rules_text.id 
							 WHERE rules_err.sys_element_id= e.id ) 
														AS rul	
	WHERE (srs.objectType=3)												-- report
	  AND (srs.[id_object]=@sys_objectable_id OR @sys_objectable_id is null)-- parameter @sys_objectable_id.това са изгледите на конкретна справка/или всички
	  AND (o.sys_form_id=@sys_form_id	OR @sys_form_id is null )	 	    -- parameter @sys_form_id
	  AND (s.[id] = @sys_section_id		OR @sys_section_id is null)		    -- parameter @sys_section_id
	  AND (e.[id] = @sys_element_id		OR @sys_element_id	 is null)	    -- parameter @sys_element_id
	  AND (t.[id] = @sys_table_id		OR @sys_table_id is null)		    -- @sys_table_id
	  AND sr.active	= 1													    -- it is active
	  --AND (		s.[type] = 3										 	    -- row section
			--OR	s.[type] = 5									 		    -- title section 
			--OR ISNULL(s.[type],0) = 0											-- old type section

UNION ALL
-- ВЪТРЕШНО-ДОКУМЕНТНИ СПРАВКИ
 -- declare @lang nvarchar(255) = N'bg', @sys_objectable_id int=4, @sys_systemObjects_id int=3
 	select	
		-- report OBJECT	
			 o.sys_systemObjects_id			as object_type					-- 1.тип на обекта=3											
			,o.objectable_type				as object_type_name				-- 2.Тип на обекта												
			,cast(ot.[description]																											
					as nvarchar(100))		as object_type_description		-- 3.Описание на типа на обекта										
			,o.sys_form_id					as object_interface_form_id		-- 4.ID на интерфейсна форма									
			,sr.[id]						as objectable_id			   	-- 5.ID на обекта												
			,tr1.[value]				  	as objectable_lang_name			-- 6.Име на обекта на текущия език								
			,sr.[label]						as objectable_label				-- 7.Етикет на обекта											
			,tr1.[filename]					as objectable_filename			-- 8.Група на обекта в преводната таблица						
		-- report table																														
			,t.id							as objectable_table_id			-- 9.ID на таблицата определено по sys_referenceMap				
			,N'cachereport'+cast(srs.[id] as nvarchar(20))																					
											as objectable_table_name	  	-- 10.Име на таблицата  определено по sys_referenceMap					
		-- report MAPING																													
			,rm.id							as rm_id					  	-- 11.ID от sys_referenceMap									
			,rm.sys_section_id				as rm_sys_section_id		  	-- 12.ID на секцията от sys_referenceMap						
			,rm.sys_element_id				as rm_sys_element_id		  	-- 13.ID на елемента от sys_referenceMap						
			,rm.sys_table_id			  	as rm_sys_table_id				-- 14.ID на таблицата от sys_referenceMap						
			,rm.is_translatable				as rm_element_translatable		-- 15.Преводимост на елемента									
			,rm.sys_acl_resources_id		as rm_sys_acl_resources_id		-- 16.ID на ресурс												
		-- SECTIONS																															
			,s.[id]							as section_id					-- 17.ID на секцията от sys_sections							
			,tr2.[value]				  	as section_lang_name		   	-- 18.Име на секцията											
			,s.[type]						as section_type					-- 19.Тип на секцията
			,s.[label]						as section_label				-- 20.Етикет на секцията			
			,tr2.[filename]					as section_filename				-- 21.Група на секцията в преводната таблица					
			,s.[UID]					  	as sys_section_UID				-- 22.UID на секцията											
			,s.[position]					as sys_section_position			-- 23.Позиция на секцията																	
		-- ELEMENTS																															
			,e.[id]							as element_id					-- 24.ID на елемента от sys_elements							
			,rm.element_order				as element_order			  	-- 25.Поредност от sys_referenceMap								
			,e.[label]						as element_label			  	-- 26.Етикет на елемента от sys_elements						
			,report_settings.[name]			as element_name					-- 27.Колона на елемента от sys_elements						
			--,e_rm.[element_name]			as element_name					-- 
			,tr0.[value]				   	as element_lang_name		   	-- 28.Име на елемента от sys_elements							
			,tr0.[filename]					as element_filename				-- 29.Група на елемента в преводната таблица					
			,e.[type]						as element_type_8v				-- 30.Тип на елемента											
			,e.[columnsize]					as element_precision_8v			-- 31.Дължина на елемента 
			,e.[decimalsize]				as element_scale_8v				-- 32.Десетични знаци за елемента
			,e.[defaultValue]				as element_defaultValue			-- 33.Стойност по подразбиране на елемента						
			,cast(e.[options] as nvarchar(MAX))	as [element_options]		-- 34.SQL за елемента											
			,rul.[text]						as element_rules			   	-- 35.Правила за елемента										
			,rc.[range_element_name]	  	as element_number_field			-- 36.Номератор на елемента										
			,rg.id							as element_range_id				-- 37.Диапазон на номератора на елемента						
			,0								as element_is_dynamic			-- 38.Динамичен ли е елемента									
			,null							as element_dynamic_value	   	-- 39.Стойност на динамичния елемент (ако е динамичен)			
			,report_settings.[description]	as element_description			-- 40.Описание на елемента
			,e.is_creatable					as element_is_creatable			-- 41.Автоматично добавяне в номенклатура	
		-- state table
			,N'cachereport'+cast(srs.[id] as nvarchar(20))
										    as state_table_name				-- 42.Име на state таблицата									
			,report_settings.[type]			as object_root					-- 43. Подредба за обработка /processing arrangement			

		FROM [dbo].[sys_reports_settings]				AS srs  -- това са изгледите на справките select * from [dbo].[sys_reports_settings]
			left join [dbo].[sys_languages]				as sl	ON sl.[language]=@lang AND sl.[status]=1
				JOIN [dbo].[sys_objectReference]		AS o    ON (o.[objectable_id]=srs.[id_object] AND o.[sys_systemObjects_id]=3) -- това е нашата справка през обекти
		-- Имена на колони И филтри на справката
			LEFT JOIN	( -- през настройки на справките	-- declare @sys_objectable_id int	
							SELECT sys_RS.[id_object] as [id_object],[name] as [name],N'Report column' as [description], sys_RS.[id] as [type]
							FROM [dbo].[sys_reports_settings]			 AS sys_RS  -- това са изгледите на справките select * from [dbo].[sys_reports_settings]
								CROSS APPLY OPENJSON(sys_RS.[settings],'$.colSettings')
												WITH( [name] nvarchar(128) '$.name')
							WHERE sys_RS.objectType=3 AND (sys_RS.[id_object]=@sys_objectable_id OR @sys_objectable_id is null) 
						UNION
						  -- през обекти	
							SELECT sys_RS.[id_object] as [id_object],e_rm.[element_name] as [name],N'Report filter' as [description], sys_RS.[id] as [type]
							FROM [dbo].[sys_reports_settings]			 AS sys_RS  -- това са изгледите на справките select * from [dbo].[sys_reports_settings]
								JOIN [dbo].[sys_objectReference]	 AS o    ON (o.[objectable_id]=sys_RS.[id_object] AND o.[sys_systemObjects_id]=3) -- това е нашата справка през обекти
								LEFT JOIN (	select e.[name] as element_name,rm.* 
											from [dbo].[sys_referenceMap] as rm
											    join [dbo].[sys_element] as e   ON e.[id]=rm.[sys_element_id] 
											) as e_rm 	ON  e_rm.sys_form_id=o.[sys_form_id]
							WHERE sys_RS.objectType=3 AND (sys_RS.[id_object]=@sys_objectable_id OR @sys_objectable_id is null) 
						)								AS report_settings  ON srs.[id_object]=report_settings.[id_object]
		-- през данни за справката
			LEFT JOIN [dbo].[sys_reports]				AS sr   ON srs.[id_object]=sr.[id]  -- справки -- select * from sys_reports
				LEFT JOIN [dbo].[sys_translations]		AS tr1  ON [sr].[label]=[tr1].[label] AND [tr1].[device]=N'web' AND [tr1].[locale]=sl.[language] AND [tr1].[filename] LIKE N'reports%'
			LEFT JOIN [dbo].[sys_table]					AS t    ON t.[name] = N'cachereport'+cast(srs.[id] as nvarchar(20))
				LEFT JOIN [dbo].[sys_ranges_config]		AS rc   ON rc.objectType = o.sys_systemObjects_id AND rc.id_object = o.[objectable_id] AND rc.enumeration_type = N'automatic' COLLATE database_default
				LEFT JOIN [dbo].[sys_ranges]	  		AS rg   ON rg.id = rc.id_sys_ranges
				LEFT JOIN dbo.sys_systemObjects			AS ot   ON o.sys_systemObjects_id = ot.id
			LEFT JOIN [dbo].[sys_reports_columns]		AS src	ON [src].[columnname] COLLATE database_default=report_settings.[name] COLLATE database_default AND src.[id_object]=srs.[id_object]
		-- през обекти
			LEFT JOIN (	select e.[id] as element_id
							  ,e.[name] as element_name
							  ,rm.[sys_form_id]
						from [dbo].[sys_referenceMap] as rm
						    join [dbo].[sys_element] as e   ON e.[id]=rm.[sys_element_id] ) as e_rm 
				ON  e_rm.sys_form_id=o.[sys_form_id] AND  e_rm.[element_name] COLLATE database_default=report_settings.[name] COLLATE database_default
			LEFT JOIN [dbo].[sys_element]				AS e	ON e.[id]=e_rm.[element_id]
				LEFT JOIN [dbo].[sys_referenceMap]		AS rm	ON rm.[sys_element_id]=e.[id] AND rm.[sys_form_id]=o.[sys_form_id]

					LEFT JOIN [dbo].[sys_translations]  AS tr0	ON e.[label] = tr0.[label] AND tr0.device = N'web' AND tr0.filename = N'elements'  AND tr0.[locale] = sl.[language]
				LEFT JOIN [dbo].[sys_sections]			AS s	ON rm.sys_section_id = s.id
					LEFT JOIN [dbo].[sys_translations]	AS tr2 ON s.[label] = tr2.[label] AND tr2.filename = N'elements' AND tr2.[locale] = sl.[language]
				cross apply (SELECT STRING_AGG(rules_text.validation_rule, N';') as [text]
							 FROM sys_validation_rules_element_rel as rules_err
							 	left join sys_validation_rules	   as rules_text	ON rules_err.id_validation_rule=rules_text.id 
							 WHERE rules_err.sys_element_id= e.id ) 
														AS rul	
	WHERE (srs.objectType=2)												-- report
	  AND (srs.[id_object]=@sys_objectable_id OR @sys_objectable_id is null)-- parameter @sys_objectable_id.това са изгледите на конкретна справка/или всички
	  AND (o.sys_form_id=@sys_form_id	OR @sys_form_id is null )	 	    -- parameter @sys_form_id
	  AND (s.[id] = @sys_section_id		OR @sys_section_id is null)		    -- parameter @sys_section_id
	  AND (e.[id] = @sys_element_id		OR @sys_element_id	 is null)	    -- parameter @sys_element_id
	  AND (t.[id] = @sys_table_id		OR @sys_table_id is null)		    -- @sys_table_id
	  AND sr.active	= 1													    -- it is active
	  --AND (		s.[type] = 3										 	    -- row section
			--OR	s.[type] = 5									 		    -- title section 
			--OR ISNULL(s.[type],0) = 0											-- old type section


	) as settings
	-- информация през физическа таблица
		LEFT JOIN sys.tables	as t1 ON t1.[name] COLLATE database_default = settings.objectable_table_name COLLATE database_default				-- nomenclature table
		LEFT JOIN sys.tables	as t2 ON t2.[name] COLLATE database_default = settings.state_table_name COLLATE database_default					-- state table
		LEFT JOIN sys.columns	as c1 ON c1.[object_id]	= OBJECT_ID(settings.[objectable_table_name]) AND c1.[name] COLLATE database_default = settings.element_name COLLATE database_default
		LEFT JOIN sys.columns	as c2 ON c2.[object_id]	= OBJECT_ID(settings.[state_table_name]) AND c2.[name] COLLATE database_default = settings.element_name COLLATE database_default
	WHERE (settings.object_type = @sys_systemObjects_id OR @sys_systemObjects_id is null)
	  AND settings.objectable_table_name is not null
	  --AND c1.column_id is not null AND c2.column_id is not null
	  --AND settings.element_name is not null

			) -- end of function			
GO



