SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_TransferDataStateNMCL]
GO
/*	-- select * from stepsoft.dbo.PARAMETERS where CallFrom = N'usp_ssERP8_TransferDataStateNMCL' order by id desc
-- select * from sys_nmcl
	DECLARE @em nvarchar(MAX), @r int
	EXEC [dbo].[usp_ssERP8_TransferDataStateNMCL] @sys_nmcl_id=4
											,@lang=N'bg'
											,@id_user=19
											,@internal_number=6
											--,@nmcl_rows_group_field='id_nmcl_stuffGroup'
											--,@nmcl_rows_group_ids='71,157,158,251'
											,@is_empty_grid=0
											,@ErrMessage=@em OUTPUT
											,@Return=@r OUTPUT
											,@SP_MODE=N'TEST1'
	SELECT @r, @em
--*/

-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_TransferDataStateNMCL]
-- Author		Щерю Щерев																									
-- Create date	22/06/2023
-- Version		v.3 21/7/2023
--				v.4 19/8/2023
--				v.5 21/8/2023
--				v.6 10/12/2023
--				v.7 14/2/2024
--				v.8 10/4/2025 - Record was changed by another user
--				v.10 1/9/2025 - Before use is_changed flag
--				v.11 1/9/2025 - AFTER use is_changed flag
--
-- Serever		MS SQL 2016																										
-- Description	Execute SQL script transfer data between State and NMCL tables
-- Returns		0 - fail 1 - success
-- Parameters
	    @sys_nmcl_id			 nvarchar(128)						 -- 1. ID or name of nomenclature in sys_nmcl table
	   ,@lang					 nvarchar(255)	NULL=N'bg'			 -- 2. Current language from sys_language table
	   ,@id_user				 int								 -- 3. Current user from sys_users table
	   ---------------------------------------------
	   ,@internal_number		 int								 -- 4. Value of internal_number for state tables 
	   ,@nmcl_rows_group_field	 nvarchar(130)	NULL=NULL			 -- 5. Name of group token from nmcl_table
	   ,@nmcl_rows_group_ids	 nvarchar(MAX)	NULL=NULL			 -- 6. List of ID's of group token from nmcl_table ( "," delimiter )
	   ,@is_empty_grid			 int	NULL=0						 -- 7. Confirmation flag to delete a group completely
								 									 --		= 0 The state table cannot be empty
								 									 --		= 1 the state table may be empty
								 									 --		= -1 turn off checking for deleted records
	   ,@tablename_extra		 nvarchar(128) NULL=NULL			 -- 8. Name of relation table for @nmcl_rows_group_field
	   ---------------------------------------------				 
	   ,@ErrMessage				 nvarchar(MAX)	NULL=NULL OUTPUT	 -- 9. Text of error/message
	   ,@Return					 int			NULL=NULL OUTPUT	 -- 10. Return value	(0 - fail 1 - success)
	   ,@Output_json			 nvarchar(MAX)  NULL=NULL OUTPUT	 -- 11. JSON with saved data		
	   ,@SP_MODE				 nvarchar(255)	NULL=N'EXECUTE'		 -- 12. Operation mode
-- Constraction 
-- Tables		sys_nmcl, all tables containing data of nomenclature
--=========================================================================================================================
AS BEGIN
/* manualUpdateFlag: No */
SET NOCOUNT ON
BEGIN TRY
/*
DECLARE  @sys_nmcl_id			 nvarchar(128)			=1	
		,@lang					 nvarchar(255)			='bg'
		,@id_user				 int					=1			
		-----------------------------------------
		,@internal_number		 int					=10			
		,@nmcl_rows_group_field	 nvarchar(130)			=N''	
		,@nmcl_rows_group_ids	 nvarchar(MAX)			='0'
		,@is_empty_grid			 int					=0	
		,@tablename_extra		 nvarchar(128)			=N''
		-----------------------------------------
		,@ErrMessage			 nvarchar(MAX)	
		,@Return				 int			
		,@Output_json			 nvarchar(MAX)  
		,@SP_MODE				 nvarchar(255)			=N'TEST1'
--*/

	DECLARE @nRetValue int=1, @ExitCode int, @Status nvarchar(MAX) = cast(N'0' as nvarchar(MAX))
	DECLARE @ParmDefinition nvarchar(MAX), @CommandString nvarchar(MAX), @InfoStr nvarchar(1024)
	DECLARE @SQLstr nvarchar(MAX) = cast(N'' as nvarchar(MAX))
	DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'usp_ssERP8_TransferDataStateNMCL'), @POS nvarchar(128) = HOST_NAME()
	DECLARE @GetDate datetime = GetDate()
	DECLARE @DeepTEST bit = 0, @FinalTestCommand nvarchar(MAX)


	DECLARE			 @nmcl_table_ext nvarchar(128), @state_table nvarchar(128), @section_id int, @section_type int, @order int
					,@Section_UID nvarchar(255), @PartitionStr nvarchar(1024), @comment nvarchar(512)
					,@IsTranslateNomenclature bit = 0, @IsDynamicNomenclature bit=0
	/*SELECT*/		,@SelectString nvarchar(MAX),	@SelectServiceString nvarchar(MAX)
	/*WHERE*/		,@WhereString nvarchar(MAX)
	/*ON_JOIN*/		,@ON_String nvarchar(MAX)
	/*SERVICE*/		,@ServiceField nvarchar(MAX),	@ServiceValue nvarchar(MAX)
	/*UPDATE*/		,@UpdateString nvarchar(MAX),	@InsertString nvarchar(MAX)
	DECLARE @tblServiceFields TABLE ( [ServiceField] nvarchar(128), [ServiceValue] nvarchar(MAX) )
	DECLARE @keeping_data_state int=1 /*ако =0 след прехвърляне данните в state таблицата се изтриват, =1 след прехвърлане само се актуализират флаговете */
		SET @ErrMessage = CAST(N'' as nvarchar(MAX))

	-- 1. Parameters sniffing
		SET @Status += cast(',1' as nvarchar(MAX))
		--	SET @SP_MODE = case when @SP_MODE is null THEN 'TEST' when @SP_MODE = 'TEST' then 'TEST' when @SP_MODE = 'TEST1' then 'TEST1' else 'EXECUTE' end
			SET @SP_MODE = case when @SP_MODE=N'TEST1'																						then N'TEST1' 
								when @SP_MODE=N'TEST'																						then N'TEST' 
								when exists ( select 1 from [dbo].[sys_global_settings] where [name]=N'SP_MODE' AND [value]=N'TEST')		then N'TEST'
								when exists ( select 1 from [dbo].[sys_global_settings] where [name]=N'SP_MODE' AND [value]=@ProcedureName)	then N'TEST'
								else																											 N'EXECUTE' 
							end
		-- @sys_nmcl_id -- select * from sys_nmcl
			DECLARE @nmcl_table nvarchar(130), @nmcl_table_lang nvarchar(1024)
			IF ISNULL(ISNUMERIC(@sys_nmcl_id),0) = 1	-- that is ID
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_table=n.[tablename]
					  ,@nmcl_table_lang = ISNULL(tr.[value],N'Not translated') 
				FROM [dbo].[sys_nmcl] as n
					LEFT JOIN sys_translations as tr ON tr.[label] = n.[label] AND tr.[device]=N'web' AND tr.[filename]=N'nomenclature' AND tr.[locale] = @lang
				WHERE n.[id] = cast(@sys_nmcl_id as int) AND tr.[locale] = @lang 
			ELSE /*@sys_nmcl_id is NON numeric value*/
				SELECT TOP(1)
					   @sys_nmcl_id = n.[id]
					  ,@nmcl_table=n.[tablename]
					  ,@nmcl_table_lang = ISNULL(tr.[value],N'Not translated') 
				FROM [dbo].[sys_nmcl] as n
					LEFT JOIN sys_translations as tr ON tr.[label] = n.[label] AND tr.[device]=N'web' AND tr.[filename]=N'nomenclature' AND tr.[locale] = @lang
				WHERE n.[tablename] COLLATE database_default = @sys_nmcl_id COLLATE database_default
			IF	NULLIF(@nmcl_table,N'') is null 
				begin SET @ErrMessage += cast(N';Missing/invalid nomenclature id parameter '+ISNULL(cast(@sys_nmcl_id as nvarchar(20)),N'null')
												as nvarchar(MAX)); SET @nRetValue = 0 end

		-- @lang
			DECLARE @lang_id int = (SELECT [id] FROM sys_languages WHERE [language]=@lang AND [status]=1)
			IF @lang_id is null 
				begin SET @ErrMessage += cast(N';Missing/invalid language parameter' as nvarchar(MAX)); SET @nRetValue = 0 end
		-- @internal_number
			IF ISNULL(@internal_number,0) <= 0
				begin SET @ErrMessage += cast(N';Missing/invalid internal_number parameter' as nvarchar(MAX)); SET @nRetValue = 0 end
		-- @nmcl_rows_group_field
		DECLARE @insert_backward int = case when ISNULL(@nmcl_rows_group_ids,0)=-1 then 0/*refresh само на новите редове*/ else 1/*refresh на всички редове*/ end
			SET @nmcl_rows_group_field = NULLIF(TRIM(@nmcl_rows_group_field),N'')
			SET @nmcl_rows_group_ids = NULLIF(TRIM(@nmcl_rows_group_ids),N''); SET @nmcl_rows_group_ids = NULLIF(TRIM(@nmcl_rows_group_ids),N'0')
				IF (@nmcl_rows_group_field is not null AND @nmcl_rows_group_ids is null)
				OR (@nmcl_rows_group_field is null AND @nmcl_rows_group_ids is not null)
					begin SET @ErrMessage += cast( N'Missing/invalid rows_group parameters' as nvarchar(MAX))	; SET @nRetValue = 0 end
		-- @tablename_extra
			SET @tablename_extra = NULLIF(@tablename_extra,N'')
			IF @tablename_extra is not null 
				IF COL_LENGTH(@nmcl_table,N'id_'+@tablename_extra) is null
					begin SET @ErrMessage += cast( N'Missing tablename_extra field in nomenclature table' as nvarchar(MAX)); SET @nRetValue = 0 end	

		-- required procedures and functions
			--IF NOT EXISTS (select 1 from sys.objects where [name] = N'ufn_ssCollectNomenclElements2' )
			--	begin SET @ErrMessage = cast(N'Missing function dbo.ufn_ssCollectNomenclElements'
			--								as nvarchar(MAX)); SET @nRetValue = 0 end
			IF NOT EXISTS (select 1 from sys.objects where [name] = N'ufn_ssERP8_CollectObjects' )
				begin SET @ErrMessage += N';Missing function dbo.ufn_ssERP8_CollectObjects'; SET @nRetValue = 0 end


	-- 2. Logging the data for analysis / Запазване на данни за анализ
				SET @Status += cast(',2' as nvarchar(MAX))
			DECLARE @TestSaveParamID int = 0
		IF OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam','P') is not null AND LEFT(@SP_MODE,4) = N'TEST' 
			BEGIN
				DECLARE @PrmString varchar(7168) =	ISNULL(cast(@sys_nmcl_id as varchar(10)),'null')+','					-- 1. ID of nomenclature in sys_nmcl table
												 +	ISNULL(cast(@lang as varchar(10)),'null')+','						-- 2. Current language from sys_language table
												 +	ISNULL(cast(@id_user	 as varchar(10)),'null')+','						-- 3. Current user from sys_users table
												 +	ISNULL(cast(@internal_number as varchar(10)),'null')+','				-- 4. Value of internal_number for state tables 
												 +	ISNULL(cast(@nmcl_rows_group_field as varchar(10)),'null')+','		-- 5. Name of group token from nmcl_table
												 +	ISNULL(cast(@nmcl_rows_group_ids as varchar(10)),'null')+','			-- 6. List of ID's of group token from nmcl_table ( "," delimiter )
												 +	ISNULL(cast(@is_empty_grid as varchar(20)),'null')+','				-- 7. Confirmation flag to delete a group completely
												 +	ISNULL(cast(@tablename_extra as varchar(10)),'null')+','				-- 8. Name of relation table for @nmcl_rows_group_field
												 +	ISNULL(@SP_MODE,'null')												-- 11.Execution mode 
				DECLARE @DBName nvarchar(128) = DB_NAME()
				DECLARE @xml_comple nvarchar(MAX) = null
				DECLARE @PrmJson nvarchar(MAX) = (	SELECT	 @sys_nmcl_id			as [@sys_nmcl_id]					-- 1. ID of nomenclature in sys_nmcl table
															,@lang					as [@lang]							-- 2. Current language from sys_language table
															,@id_user				as [@id_user]						-- 3. Current user from sys_users table
															,@internal_number		as [@internal_number]				-- 4. Value of internal_number for state tables 
															,@nmcl_rows_group_field	as [@nmcl_rows_group_field]			-- 5. Name of group token from nmcl_table
															,@nmcl_rows_group_ids	as [@nmcl_rows_group_ids	]			-- 6. List of ID's of group token from nmcl_table ( "," delimiter )
															,@is_empty_grid			as [@is_empty_grid]					-- 7. Confirmation flag to delete a group completely
															,@tablename_extra		as [@tablename_extra]				-- 8. Name of relation table for @nmcl_rows_group_field
															,@ErrMessage		 	as [@ErrMessage]						-- 9. Text of error/message
															,@Return			 	as [@Return]							-- 10. Return value	(0 - fail 1 - success)
															,@SP_MODE				as [@SP_MODE]						-- 11. Operation mode
													FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )
				EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = @xml_comple
															,@Separator = ',', @DataBase = @DBName, @RecordID = @TestSaveParamID OUTPUT
															,@PrmJson=@PrmJson
			END --IF LEFT(@SP_MODE,4) = N'TEST' AND OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') is not null		
	print @ProcedureName+'->SP_MODE = ' + ISNULL(@SP_MODE,'NULL')+' '+@ProcedureName+'->TestSaveParamID = ' + ISNULL(cast(@TestSaveParamID as varchar(10)),'NULL') + ' '+cast(OBJECT_ID(N'stepsoft.dbo.usp_ssTestSaveParam') as varchar(255))

	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Input parameters' as tbl
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
								  ,@lang as '@lang', @lang_id as '@lang_id'
								  ,@sys_nmcl_id as '@sys_nmcl_id', @nmcl_table as '@nmcl_table', @nmcl_table_lang as '@nmcl_table_lang'
								  ,@tablename_extra as '@tablename_extra'
								  ,@nmcl_rows_group_field as '@nmcl_rows_group_field',@nmcl_rows_group_ids as '@nmcl_rows_group_ids'
								  ,@internal_number as '@internal_number'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Load elements metadata / Зареждане на метаданни за елементите
	/*		0 document title
			1 document row
			2 report
		3 nomenclature row
		4 dynamic row
		5 nomenclature title
			6 document map */
		SET @Status += cast(',3' as nvarchar(MAX))
	--IF @nRetValue = 1	begin
	IF OBJECT_ID('tempdb..#NmclElements') is not null DROP TABLE #NmclElements
		SELECT *
		INTO #NmclElements
		FROM dbo.ufn_ssERP8_CollectObjects( 1, @sys_nmcl_id, null, null, null, null, @lang) 
		WHERE [sys_state_element_exists] = N'1111' and [section_type] IN (3,4,5)
		/* DECLARE @sys_nmcl_id int = 13
			select * from dbo.ufn_ssERP8_CollectObjects(	 1				-- 1. ID of object type		
															,@sys_nmcl_id	-- 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		
														)
		*/
	--					end	-- IF @nRetValue = 1
	IF NOT EXISTS (select 1 from #NmclElements ) begin 
		SET @ErrMessage += cast(N';Missing settings of nomenclature '+ISNULL(@nmcl_table,N'null') as nvarchar(MAX)); SET @nRetValue = 0 
		IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],N'Missing settings of nomenclature '+ISNULL(@nmcl_table,N'null') as tbl
												 end
	ELSE IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'#NmclElements' as tbl,* from #NmclElements
	-- определане на Grid state table
	DECLARE @Grid_state_table nvarchar(128) = (	SELECT TOP(1) [state_table_name] FROM #NmclElements 
												WHERE [section_type] = 3 /*3 nomenclature row*/ 
												AND [rm_element_translatable] = 0 /*not translations*/)
	IF @Grid_state_table is null 
		begin SET @ErrMessage += cast(N'Missing/Invalid state grid table of nomenclature '+ISNULL(@nmcl_table,N'null') as nvarchar(MAX)); SET @nRetValue = 0 end
	IF @SP_MODE=N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],N'GRID STATE TABLE' as tbl,ISNULL(@Grid_state_table,N'null') as '@Grid_state_table'
	IF @DeepTEST = 1 begin
			SET @FinalTestCommand = cast(N'
SELECT * INTO [dbo].[tmp_TEST1] 
FROM [dbo].['+@Grid_state_table+N'] WHERE [internal_number]='+CAST(@internal_number as nvarchar(20))+N'
'											as nvarchar(MAX))
	EXEC (@FinalTestCommand)
					 end	-- IF @DeepTEST = 1
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 4. Check @is_empty_grid flag and root state table internal_number existing / проверка на флага @is_empty_grid и съществуване на @internal_number в главната state таблица
	-- КОНТРОЛ дали трябва да се изтрият всички записи от групата/номенклатурата
			SET @Status += cast(',4' as nvarchar(MAX))
	IF @nRetValue = 1	BEGIN

		-- select * from state_form_386_grid_1995	
		-- определяне на броя записи от state таблицата които са дадени за изтриване
		DECLARE @cnt_all int, @cnt_mrg int, @cnt_del int
			SET @ParmDefinition	= N'@cnt_all int OUTPUT, @cnt_mrg int OUTPUT, @cnt_del int OUTPUT, @internal_number int'
			SET @CommandString	= 
			N'SET @cnt_all = (select COUNT(id) from '+@Grid_state_table+N' where [internal_number]=@internal_number )
			  SET @cnt_mrg = (select COUNT(id) from '+@Grid_state_table+N' where [internal_number]=@internal_number AND ( [is_new]=1 or [is_changed]=1 ))
			  SET @cnt_del = '+ case when COL_LENGTH(@Grid_state_table,N'') is not null then N'(select COUNT(id) from '+@Grid_state_table+N' where [internal_number]=@internal_number AND [is_deleted]=1 )'
									 else N'0' end
				EXEC @ExitCode = sp_executeSQL @CommandString, @ParmDefinition
										  ,@internal_number=@internal_number				-- 1. Value of internal_number for state tables 
										  ,@cnt_all=@cnt_all OUTPUT							-- 2. Number of records in state table for internal_number=@internal_number
										  ,@cnt_mrg=@cnt_mrg OUTPUT							-- 3. Number of records for merging
										  ,@cnt_del=@cnt_del OUTPUT							-- 4. Number of records for deleting

			IF @is_empty_grid=0	-- според параметъра трябва да има неизтрити редове
			-- и няма записи или всички записи са посочени за изтриване
			AND (	@cnt_del=@cnt_all -- всички записи са за изтриване
				 and @cnt_all>0 	
				 ) 
			   begin SET @ErrMessage += cast(
									   N'FALSE of condition @is_empty_grid' 
									 + N'(all:'+ISNULL(cast(@cnt_all as nvarchar(20)),N'null')
									 + N',del:'+ISNULL(cast(@cnt_del as nvarchar(20)),N'null')
									 + N',mrg:'+ISNULL(cast(@cnt_mrg as nvarchar(20)),N'null') + N')'
											as nvarchar(MAX)); SET @nRetValue = 0
				end
			ELSE IF @is_empty_grid=0 -- според параметъра трябва да има неизтрити редове
				 AND (@cnt_all=0	-- няма записи за този @internal_number
				  or  @cnt_mrg=0)	-- няма записи за merge => нищо не е променяно
			   begin SET @ErrMessage += cast(
									   N'no rows to processing' 
									 + N'(all:'+ISNULL(cast(@cnt_all as nvarchar(20)),N'null')
									 + N',del:'+ISNULL(cast(@cnt_del as nvarchar(20)),N'null')
									 + N',mrg:'+ISNULL(cast(@cnt_mrg as nvarchar(20)),N'null') + N')'
											as nvarchar(MAX))
				end

						END	-- IF @nRetValue = 1	BEGIN
	IF @SP_MODE = N'TEST1' select	 @ProcedureName+N'('+@Status+N')' as  [procedure],'CHECK @is_empty_grid' as tbl
									,@is_empty_grid as 'Parameter @is_empty_grid'
									,case when @is_empty_grid=0 then N'The state table cannot be empty'
										  when @is_empty_grid=1	then N'The state table may be empty'
										  when @is_empty_grid=-1 then N'turn off checking for deleted records'
										  else N'Error vslue of @is_empty_grid'
									 end as '@is_empty_grid description'			
									,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
									,@cnt_all as 'count for internal_number',@cnt_del as 'count of internal_number FOR DELETE',@cnt_mrg as 'count of internal_number FOR MERGE'
									,@ParmDefinition as '@ParmDefinition', @CommandString as '@CommandString'
									,@ExitCode as '@ExitCode'

-- STATE -> NMCL =======================================================================================================================================================================
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 5. Create TEMPLATE scripts for transfer / Създаване на темплейт скриптове за трансфер
		SET @Status += cast(',5' as nvarchar(MAX))
	IF @nRetValue = 1	BEGIN
		-- Разрешаване/спиране на актуализацията в зависимост от полето за многопотребителски контрол АКО ТО СЪЩЕСТВУВА В state таблицата
		DECLARE @sys_update_at_condition nvarchar(MAX) = 
				  case when COL_LENGTH( @grid_state_table, N'sys_updated_at') is not null AND COL_LENGTH( @nmcl_table, N'updated_at') is not null 
					-- условие да не се е променила updated_at спрямо sys_updated_at	
						then cast(N' AND [source_table].[sys_updated_at] = [target_table].[updated_at] ' as nvarchar(MAX))
					   else  cast(N'/*missing column sys_updated_at*/' as nvarchar(MAX))		
				  end
		-- Разрешаване/игнориране на изтриване в зависимост от параметъра @is_empty_grid и наличието на поле is_deleted в state таблицата
		DECLARE @delete_condition nvarchar(MAX) = 
				  case when COL_LENGTH( @grid_state_table, N'is_deleted') is not null AND @is_empty_grid<>-1
						then cast(N'WHEN MATCHED AND [source_table].[is_deleted]=1 AND [source_table].[sys_updated_at] = [target_table].[updated_at] THEN DELETE' as nvarchar(MAX))
					   else  cast(N'/* delete condition is false */' as nvarchar(MAX))		
				  end

	-- template scripts
-- АКО @SP_MODE='TEST1'
		DECLARE @SP_MODE_SQL nvarchar(MAX) = cast(
									case when @SP_MODE='TEST1' then N'
		/*Procedure: '+ISNULL(@ProcedureName,N'null')+N' - for test in SSMS only ('+@nmcl_table+N')*/
		DECLARE  @internal_number int = '+ISNULL(cast(@internal_number as nvarchar(20)),N'null')+N'
				,@lang nvarchar(255) = '''+ISNULL(cast(@lang as nvarchar(255)),N'null')+N'''
				,@lang_id int = '+ISNULL(cast(@lang_id as nvarchar(20)),N'null')+N'
				,@id_user int = '+ISNULL(cast(@id_user as nvarchar(20)),N'null')+N'
				,@is_empty_grid int = '+ISNULL(cast(@is_empty_grid as nvarchar(20)),N'null')+N'
		DROP TABLE IF EXISTS #Output 
		CREATE TABLE #Output	( [nmcl] nvarchar(128)
								 ,[action] nvarchar(10)
								 ,[inserted_id] int
								 ,[deleted_id] int
								 ,[id_grid_row] int
								 ,[rows_group] int
								 ,[id_record] int
								 ,[is_changed] int, [is_new] int, [is_deleted] int	)
'										 else /*@SP_MODE<>TEST1*/ N'
'									end		as nvarchar(MAX))

-- ОСНОВЕН СКРИПТ ЗА ПРЕХВЪРЛЯНЕ НА GridTable
		DECLARE @GridQuery nvarchar(MAX) = @SP_MODE_SQL+cast(N'
		DECLARE @Output_root TABLE	( [action] nvarchar(10)
									 ,[inserted_id] int
									 ,[deleted_id] int
									 ,[id_grid_row] int
									 ,[rows_group] int
									 ,[id_record] int
									 ,[is_changed] int, [is_new] int, [is_deleted] int	)
		DECLARE @Output TABLE	( [action] nvarchar(10)
								 ,[inserted_id] int
								 ,[deleted_id] int
								 ,[id_grid_row] int
								 ,[rows_group] int
								 ,[id_record] int
								 ,[is_changed] int, [is_new] int, [is_deleted] int	)
/*Трансфер на РЕДОВЕ: <comment>--*/
		MERGE INTO [dbo].[<nmcl_table>]			as target_table
		USING	(	SELECT 
					<select_string>
					,@internal_number as [internal_number]
					,[<state_table>].[id_record]
					,[<state_table>].[is_changed]
					,[<state_table>].[is_new]
					,[<state_table>].[is_deleted]
					FROM [dbo].[<state_table>]
					WHERE [<state_table>].[internal_number]=@internal_number
				)								as source_table
		ON  <ON_String>
		'+@delete_condition+N'/*physical deletion of marked with is_deleted records FROM NMCL table*/
		WHEN MATCHED AND [source_table].[is_changed]=1 '+@sys_update_at_condition+N' THEN UPDATE SET <update_string>
		WHEN NOT MATCHED AND [source_table].[is_new]=1 THEN INSERT (<insert_string><service_fields>) VALUES (<insert_string><service_values>)
				OUTPUT	 $action
						,INSERTED.ID
						,DELETED.ID
						,[source_table].[id_grid_row]
						,[source_table].[rows_group]
						,[source_table].[id_record]
						,[source_table].[is_changed]
						,[source_table].[is_new]
						,[source_table].[is_deleted]
				INTO @Output_root;
		/*запазване за връщане към викащата процедура*/
		INSERT INTO #Output ( [nmcl], [action], [inserted_id], [deleted_id], [id_grid_row], [rows_group], [id_record], [is_changed], [is_new], [is_deleted] )
		SELECT ''<state_table>-><nmcl_table>'' as nmcl, [action], [inserted_id], [deleted_id], [id_grid_row], [rows_group], [id_record], [is_changed], [is_new], [is_deleted]  
		FROM @Output_root
'																	as nvarchar(MAX))

		DECLARE @NogridQuery nvarchar(MAX) = cast(N'
/*Трансфер на: <comment>--*/
		DELETE @Output
		MERGE INTO [dbo].[<nmcl_table_ext>]			as target_table
		USING	(	SELECT 
					<select_string>
					,@internal_number as [internal_number]
					,[output_tbl].[id_record]
					,[output_tbl].[is_changed]
					,[output_tbl].[is_new]
					,[output_tbl].[is_deleted]
					,[output_tbl].[inserted_id] as [INSERTED_ID]
					,[output_tbl].[action] as [ACTION]
					FROM [dbo].[<state_table>]
						JOIN @Output_root as [output_tbl] ON [output_tbl].[id_grid_row] = [<state_table>].[id_grid_row] 
														  AND ISNULL([output_tbl].[rows_group],0) = ISNULL([<state_table>].[rows_group],0)
					WHERE [<state_table>].[internal_number]=@internal_number
				)								as source_table
		ON  <ON_String>
		WHEN MATCHED AND [source_table].[action]=N''DELETE'' THEN DELETE
		WHEN MATCHED AND [source_table].[is_changed]=1 THEN UPDATE SET <update_string>
		WHEN NOT MATCHED AND [source_table].[is_new]=1 THEN  INSERT (<insert_string><service_fields>) VALUES (<insert_string><service_values>)
				OUTPUT	 $action
						,INSERTED.ID
						,DELETED.ID
						,[source_table].[id_grid_row]
						,[source_table].[rows_group]
						,[source_table].[id_record]
						,null
						,null
						,null
				INTO @Output;
		/*запазване за връщане към викащата процедура*/
		INSERT INTO #Output ( [nmcl], [action], [inserted_id], [deleted_id], [id_grid_row], [rows_group], [id_record], [is_changed], [is_new], [is_deleted] )
		SELECT ''<state_table>-><nmcl_table_ext>'' as nmcl, [action], [inserted_id], [deleted_id], [id_grid_row], [rows_group], [id_record], [is_changed], [is_new], [is_deleted]  
		FROM @Output'
													as nvarchar(MAX))

-- Премахване/актуализация на записите от state таблиците
		DECLARE @UpdateStateChangeFlagQuery nvarchar(MAX) = 
			case
					when @keeping_data_state = 1    then cast(N'
/*ИНИЦИАЛИЗИРАНЕ на флага is_changed за @internal_number В state ТАБЛИЦИТЕ: <comment>--*/
		UPDATE state_table /*update all transferred records 1=>0 */
			SET  [is_changed] = 0
				,[is_new] = 0
				,[id_record] = ISNULL([state_table1].[id_record_old],[state_table1].[id_record])
		FROM (	select [<state_table>].[id] as [id],[output_tbl].[inserted_id] as [id_record]
					  ,[output_tbl].[id_record] as [id_record_old]	
				from [dbo].[<state_table>] 
					 join @Output_root  as [output_tbl] ON [output_tbl].[id_grid_row] = [<state_table>].[id_grid_row]  
													AND ISNULL([output_tbl].[rows_group],0) = ISNULL([<state_table>].[rows_group],0)
				where [output_tbl].[inserted_id] is not null
			 ) as [state_table1]
				JOIN [dbo].[<state_table>] as [state_table] ON [state_table].[id] = [state_table1].[id]
		WHERE [internal_number] = @internal_number
'																							as nvarchar(MAX))
					-- такъв случай не трябва да има
					else								 cast(N'
/*ИЗТРИВАНЕ @internal_number ОТ state ТАБЛИЦИТЕ: <comment>--*/
		DELETE [dbo].[<state_table>] WHERE [internal_number] = @internal_number'
																							as nvarchar(MAX))
			end


-- Обработка на ситуацията с многопотребителска работа
		DECLARE @ChangeByAnotherUser nvarchar(MAX) = cast(N'
		UPDATE [dbo].[<state_table>] SET [is_changed]=0 WHERE [is_changed]=-1 AND [internal_number] = @internal_number /*resolving old conflicts -1=>0 */
		UPDATE [dbo].[<state_table>] SET [is_changed]=-[is_changed] WHERE [internal_number] = @internal_number /*marking the current conflicts 1=>-1 */
'															as nvarchar(MAX))
-- Физическо изтриване на изтритите редове от state
		DECLARE @DeleteByState nvarchar(MAX) = cast(N'
		DELETE [dbo].[<state_table>] /*delete records with is_deleted = 1 AND is_changed <> -1*/
		FROM [dbo].[<state_table>]
			JOIN @Output_root as [output_tbl] ON [output_tbl].[id_grid_row] = [<state_table>].[id_grid_row] 
											  AND ISNULL([output_tbl].[rows_group],0) = ISNULL([<state_table>].[rows_group],0)
		WHERE [<state_table>].[internal_number] = @internal_number AND [output_tbl].[is_deleted]=1 AND [output_tbl].[is_changed]<>-1
'					as nvarchar(MAX))
						END	-- IF @nRetValue = 1
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'General queries' as tbl
								 , @GridQuery as '@GridQuery', @NogridQuery as '@NogridQuery'
								 , @sys_update_at_condition as '@sys_update_at_condition'
								 , @UpdateStateChangeFlagQuery as '@UpdateStateChangeFlagQuery', @ChangeByAnotherUser as '@ChangeByAnotherUser',@DeleteByState as '@DeleteByState'  

	-- ИЗПЪЛНЕНИЕ НА ТРАНСФЕРА =======================================================================================================================================

		DROP TABLE IF EXISTS #Output 
		CREATE TABLE #Output (  [nmcl] nvarchar(128)
							   ,[action] nvarchar(10)
							   ,[inserted_id] int
							   ,[deleted_id] int
							   ,[id_grid_row] int
							   ,[rows_group] int
							   ,[id_record] int
							   ,[is_changed] int, [is_new] int, [is_deleted] int)
	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 6. Cursor for Transfer state table to NMCL table/ Цикъл за Трансфер на state таблици към NMCL таблицата 
			SET @Status += cast(',6' as nvarchar(MAX))
		-- таблица по която върви цикъла
		DECLARE @tblCursor TABLE (	 [id] int identity
									,[objectable_table_name] nvarchar(128)
									,[state_table_name] nvarchar(128)
									,[IsTranslateNomenclature] bit
									,[section_type] int -- 	3=nomenclature row,	4=dynamic row, 5=nomenclature title
									,[section_description] nvarchar(255)
									,[object_root] int
									,[order] int )
		INSERT INTO @tblCursor ( [objectable_table_name], [state_table_name], [IsTranslateNomenclature], [section_type], [section_description], [object_root], [order] )
		SELECT DISTINCT [objectable_table_name]
					   ,[state_table_name]
					   ,[rm_element_translatable]
					   ,[section_type]
					   ,case when [section_type]=3 then N'редове' 
							 when [section_type]=4 then N'динамични редове' 
							 when [section_type]=5 then N'заглавия'  
							 else N'невалиден тип секция'
						end	as [section_description]	
					   ,[object_root]
					   ,case when [rm_element_translatable]=1 then 2 else 1 end
		FROM #NmclElements 
		WHERE [objectable_table_name] is not null AND [state_table_name] is not null
		-- ако няма елементи в grid table => добавяме я изкуствено
		IF not exists (select 1 from @tblCursor where [objectable_table_name]=@nmcl_table AND [state_table_name]=@grid_state_table)
			INSERT INTO @tblCursor ([objectable_table_name], [state_table_name], [IsTranslateNomenclature], [section_type], [object_root] )
			VALUES (@nmcl_table, @grid_state_table, 0, 3, 0)
		-- създаване на допълнително поле за наредба
		UPDATE tblCursor
			SET [Order] = tmp.[order]
		FROM ( select [id], ROW_NUMBER() OVER ( ORDER BY [section_type],[order]) as [order] from @tblCursor ) as tmp
				JOIN @tblCursor as tblCursor ON tblCursor.id=tmp.id
			
	-- ПРЕДВАРИТЕЛНА ИНФОРМАЦИЯ за двойките state и nmcl 
			IF @SP_MODE = N'TEST1' 
				begin
					select  DISTINCT  
									 @ProcedureName+N'('+@Status+N')' as  [procedure],'Pair nmcl->state(#NmclElements)' as tbl
									,[objectable_table_name] as '@nmcl_table_ext' /*nmcl таблица*/
									,[state_table_name] as '@state_table' /*state таблица*/
									,case when [section_type]=3 then N'редове' 
										when [section_type]=4 then N'динамични редове' 
										when [section_type]=5 then N'заглавия'  
										else N'невалиден тип секция'
									  end			as '@section_type_description'	/*тип на секцията*/
									,[section_type] as '@section_type'
									,[section_id],[rm_element_translatable],[sys_section_UID] /*UID на секцията*/
					from #NmclElements 
					--where [objectable_table_name] is not null AND [state_table_name] is not null
					if exists (select 1 from @tblCursor )
							select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblCursor' as tbl, * from @tblCursor order by [order]
					else	select  @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblCursor => NO ROWS' as tbl
				end
				ELSE IF not exists (	select 1 from #NmclElements where [objectable_table_name] is not null 
																						  AND [state_table_name] is not null 
																						  AND [section_type]=3
																						  --AND [rm_element_translatable]=0
									)
					begin 
						  SET @ErrMessage += N'Missing grid section on nomenclature '+ISNULL(@nmcl_table,N'null')
						  SET @nRetValue = 0
						  IF @SP_MODE=N'TEST1' select @ProcedureName+N'('+@Status+N')' as  [procedure],'NO Pair nmcl->GRID state' as tbl
					end

DECLARE table_cursor CURSOR FOR SELECT [objectable_table_name], [state_table_name], [IsTranslateNomenclature], [section_type], [order]
								FROM @tblCursor 
								ORDER BY [order]
	OPEN table_cursor;	FETCH NEXT FROM table_cursor INTO  @nmcl_table_ext ,@state_table, @IsTranslateNomenclature, @section_type, @order 
		WHILE (@@FETCH_STATUS <> -1)	BEGIN
		IF	  (@@FETCH_STATUS <> -2)	BEGIN
		IF			  @nRetValue = 1	BEGIN 
				SET @Status += cast(N',('+@nmcl_table_ext+N'->'+@state_table
							+ N'#'+case when @IsTranslateNomenclature=0 then N'непреводима' when @IsTranslateNomenclature=1 then N'преводима'else N'err' end+N')'
									as nvarchar(MAX))
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.0. initialization
		-- load transfer settings	
			select TOP(1)	 
					 @section_id = [section_id]
					,@IsDynamicNomenclature = [element_is_dynamic]
			from #NmclElements 
			where [objectable_table_name] = @nmcl_table_ext AND [state_table_name] = @state_table AND [section_type] = @section_type
		-- substitution variables
			SELECT	 @SelectString=cast(N'' as nvarchar(MAX)), @SelectServiceString=cast(N'' as nvarchar(MAX))
					,@UpdateString=cast(N'' as nvarchar(MAX)) 
					,@WhereString=cast(N'' as nvarchar(MAX))
					,@InsertString=cast(N'' as nvarchar(MAX))
					,@ON_String=cast(N'' as nvarchar(MAX))  
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------
		-- 6.1. Зареждане на сервизни полета в @tblServiceFields
		DELETE @tblServiceFields -- ( [ServiceField] nvarchar(128), [ServiceValue] nvarchar(MAX) )
		-- required
			--------------------------------------------- специално полета, които се подават директно в template
			-- internal_number
				IF	COL_LENGTH(@state_table,N'internal_number') is null
						begin SET @ErrMessage += N';Missing field internal_number in '+@state_table; SET @nRetValue = 0 end
					--ELSE	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'internal_number', null )
			-- id_record
				IF	COL_LENGTH(@state_table,N'id_record') is null OR COL_LENGTH(@nmcl_table,N'id') is null
						begin SET @ErrMessage += N';Missing field id_record in '+@state_table; SET @nRetValue = 0 end
					--ELSE	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_record', null )	
			-- is_deleted
				IF	@section_type = 3 /*nomenclature rows*/ AND @IsTranslateNomenclature = 0 AND COL_LENGTH(@state_table,N'is_deleted') is null
						begin SET @ErrMessage += N';Missing field is_deleted in '+@state_table; SET @nRetValue = 0 end
					--ELSE IF COL_LENGTH(@state_table,N'is_deleted') is not null
					--	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'is_deleted', null )			
			-- is_new
				IF	@section_type = 3 /*nomenclature rows*/ AND @IsTranslateNomenclature = 0 AND COL_LENGTH(@state_table,N'is_new')	is null
						begin SET @ErrMessage += cast(N';Missing field is_new in '+@state_table as nvarchar(MAX)); SET @nRetValue = 0 end
					--ELSE	
					--	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'is_new', null )		
			-- is_changed
				IF	@section_type = 3 /*nomenclature rows*/ AND @IsTranslateNomenclature = 0 AND COL_LENGTH(@state_table,N'is_changed') is null
						begin SET @ErrMessage += cast(N';Missing field is_changed in '+@state_table as nvarchar(MAX)); SET @nRetValue = 0 end
					--ELSE	
					--	INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'is_changed', null )

			---------------------------------------------
			-- is_grid_row
				IF	COL_LENGTH(@state_table,N'id_grid_row')	is null
						begin SET @ErrMessage += cast(N';Missing field id_grid_row in '+@state_table as nvarchar(MAX)); SET @nRetValue = 0 end
					ELSE	
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_grid_row', null )
			-- rows_group
				IF	COL_LENGTH(@state_table,N'rows_group')	is null
						begin SET @ErrMessage += cast(N';Missing field rows_group in '+@state_table as nvarchar(MAX)); SET @nRetValue = 0 end
					ELSE	
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'rows_group', null )

		-- not required
			-- id_user
				IF	COL_LENGTH(@state_table,N'id_user') is not null AND COL_LENGTH(@nmcl_table_ext,N'id_user') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_user', N'[id_user]' )
				ELSE IF COL_LENGTH(@nmcl_table_ext,N'id_user') is not null 		
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_user', N''''+cast(ISNULL(@id_user,0) as nvarchar(20))+N'''' )
			-- lang
				IF	COL_LENGTH(@state_table,N'lang') is not null AND COL_LENGTH(@nmcl_table_ext,N'lang') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'lang', N'[lang]' )	
			-- created_at
				IF	COL_LENGTH(@state_table,N'created_at')	is not null AND COL_LENGTH(@nmcl_table_ext,N'created_at') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'created_at', N'[created_at]' )
				ELSE IF COL_LENGTH(@nmcl_table_ext,N'created_at') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'created_at', N''''+convert( nvarchar(50),GetDate(),121)+N'''' )
			-- updated_at
				IF	COL_LENGTH(@state_table,N'updated_at')	is not null AND COL_LENGTH(@nmcl_table_ext,N'updated_at')	is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'updated_at', N''''+convert( nvarchar(50),GetDate(),121)+N'''' )
			-- updated_by
				IF	COL_LENGTH(@state_table,N'updated_by')	is not null AND COL_LENGTH(@nmcl_table_ext,N'updated_by') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'updated_by', N'[updated_by]' )			
				ELSE IF	COL_LENGTH(@nmcl_table_ext,N'updated_by') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'updated_by', N''''+cast(ISNULL(@id_user,0) as nvarchar(20))+N'''' )		
			-- created_by
				IF	COL_LENGTH(@state_table,N'created_by') is not null AND COL_LENGTH(@nmcl_table_ext,N'created_by') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'created_by', N'[created_by]' )
				ELSE IF COL_LENGTH(@nmcl_table_ext,N'created_by') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'created_by', N''''+cast(ISNULL(@id_user,0) as nvarchar(20))+N'''' )	
			-- sys_updated_at -- това поле не се прехвърля в nmcl
				IF	COL_LENGTH(@state_table,N'sys_updated_at')	is not null AND COL_LENGTH(@nmcl_table_ext,N'updated_at') is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'sys_updated_at', null )

			-- id_sys_section
				IF	COL_LENGTH(@state_table,N'id_sys_section')	is not null AND COL_LENGTH(@nmcl_table_ext,N'id_sys_section') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_sys_section', N''''+cast(@section_id as nvarchar(20))+N'''' )
			-- id_element_dynamic
				IF	COL_LENGTH(@state_table,N'id_element_dynamic')	is not null AND COL_LENGTH(@nmcl_table_ext,N'id_element_dynamic') is not null 
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( N'id_element_dynamic', N'[id_element_dynamic]' )

			-- INSERTED_ID
				DECLARE @nmclID_field nvarchar(128) = N'id_'+@nmcl_table 
--print @nmcl_table_ext+N' '+@nmclID_field+N': '+ISNULL(cast(COL_LENGTH(@nmcl_table_ext,@nmclID_field) as nvarchar(20)),N'null')
				IF	COL_LENGTH(@nmcl_table_ext,@nmclID_field)	is not null
						INSERT INTO @tblServiceFields ( [ServiceField], [ServiceValue] ) VALUES ( @nmclID_field, N'[INSERTED_ID]' )

		SELECT @ServiceField =ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[ServiceField]+N']' COLLATE database_default), CHAR(44)),N'')
		FROM @tblServiceFields 
		WHERE [ServiceField] is not null AND [ServiceValue] is not null

		SELECT @ServiceValue =ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),ISNULL([ServiceValue],N'NULL') COLLATE database_default), CHAR(44)),N'')
		FROM @tblServiceFields
		WHERE [ServiceField] is not null AND [ServiceValue] is not null
		IF @SP_MODE = N'TEST1' AND exists (select 1 from @tblServiceFields)	select @ProcedureName+N'('+@Status+N')' as  [procedure],'table @tblServiceFields' tbl,* from @tblServiceFields
		ELSE IF @SP_MODE = N'TEST1'											select @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in table @tblServiceFields' tbl
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.2. Зареждане на @comment
			SET @InfoStr = case when @section_type=3 then N'3.редове' when @section_type=4 then N'4.динамична' when @section_type=5 then N'5.заглавия' else ISNULL(cast(@section_type as nvarchar(20)),N'null') end
						 + N' ' + case when @IsTranslateNomenclature is null then N'null' 
									   when @IsTranslateNomenclature=0 then N'непреводима' 
									   else N'преводима' 
								   end
						 + N' ' + case when @IsTranslateNomenclature=0 AND @section_type=3 then N'GridQuery'
									   else N'NogridQuery' 
								   end
				SET @comment = cast(N' '+ISNULL(cast( @order as nvarchar(20)),N'null')+N'.[TABLES: '+ISNULL(@state_table,N'null')+'->'+ISNULL(@nmcl_table_ext,N'null')+N' (section type:'+@InfoStr+N') ]
'									as nvarchar(MAX))

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.3. Зареждане на @SelectString
			IF @section_type <> 4
				SELECT @SelectString +=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+@state_table+N'].['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
				FROM #NmclElements as e
					LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=e.element_name COLLATE database_default
				WHERE e.[state_table_name]=@state_table AND e.[objectable_table_name]=@nmcl_table_ext AND e.[section_type]=@section_type 
				  AND sf.ServiceField is null
				  AND e.[section_type] = @section_type
				  AND e.[rm_element_translatable] = @IsTranslateNomenclature
			--	  AND e.[element_name] NOT IN ( @nmclID_field )

				IF NULLIF(@SelectString,N'') is not null AND NULLIF(@ServiceField,N'') is not null
				   SET @SelectString +=cast(N',' as nvarchar(MAX))

				SELECT @SelectString +=ISNULL(	STRING_AGG(	CONVERT( NVARCHAR(max)
														   ,N'['+@state_table+N'].['+[ServiceField] COLLATE database_default+N']' )
													, CHAR(44))
											   ,N'')
				FROM @tblServiceFields as f
				WHERE f.[ServiceField] NOT IN ( @nmclID_field ) -- Това е полето id_<nmcl_name>, което се зарежда от INSERTED_ID, НО ЛИПСВА в state_table

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.4. Зареждане на @ON_String
		-- според типа на секцията
		SET @ON_String = case
							when @section_type = 3 AND @IsTranslateNomenclature = 0	-- nomenclature grid tables
								then cast(N'[target_table].[id] = [source_table].[id_record] /*AND [source_table].[is_new] = 0*/
'												as nvarchar(MAX))
							when @section_type = 3 AND @IsTranslateNomenclature = 1	-- nomenclature grid tables translations
								then cast(N'[target_table].[id_'+@nmcl_table+N'] = [source_table].[INSERTED_ID]
'												as nvarchar(MAX))
							when @section_type = 4 AND @IsTranslateNomenclature = 0	-- nomenclature dynamic tables
								then cast(	N'[target_table].[id_'+@nmcl_table+N'] = [source_table].[INSERTED_ID] AND [target_table].[id_element_dynamic] = [source_table].[id_element_dynamic]
'												as nvarchar(MAX)) 
							when @section_type = 5 AND @IsTranslateNomenclature = 0	-- nomenclature title tables
								then cast(N'[target_table].[id] = [source_table].[INSERTED_ID]
'												as nvarchar(MAX))
							when @section_type = 5 AND @IsTranslateNomenclature = 1	-- nomenclature title tables translations
								then cast(N'[target_table].[id_'+@nmcl_table+N'] = [source_table].[INSERTED_ID]
'												as nvarchar(MAX))

						 end
		-- добавяне на условие за език	
			IF @IsTranslateNomenclature = 1					-- translations tables	
				SET @ON_String +=cast(N' AND [target_table].[lang] = [source_table].[lang]
'												as nvarchar(MAX))
	
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.5. Зареждане на @InsertString
			IF @section_type <> 4
				SELECT @InsertString=ISNULL(STRING_AGG(CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N']'), CHAR(44)),N'') 
				FROM #NmclElements as e
					LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=e.element_name COLLATE database_default
				WHERE e.[state_table_name]=@state_table AND e.[objectable_table_name]=@nmcl_table_ext AND e.[section_type]=@section_type 
				  AND sf.ServiceField is null
				  AND e.[section_type] = @section_type
				  AND e.[rm_element_translatable] = @IsTranslateNomenclature

				IF NULLIF(@InsertString,N'') is not null AND NULLIF(@ServiceField,N'') is not null
				   SET @InsertString+=cast(N',' as nvarchar(MAX))

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.6. Зареждане на @UpdateString
			IF @section_type <> 4
				SELECT @UpdateString +=ISNULL(STRING_AGG( CONVERT(NVARCHAR(max),N'['+[element_name] COLLATE database_default+N'] = [source_table].['+[element_name] COLLATE database_default+N']')
														, CHAR(44))
											 , N'') 
				FROM #NmclElements as e
					LEFT JOIN @tblServiceFields as sf ON sf.ServiceField COLLATE database_default=e.element_name COLLATE database_default
				WHERE sf.ServiceField is null
				  AND e.[section_type] = @section_type
				  AND e.[rm_element_translatable] = @IsTranslateNomenclature
			  
				IF NULLIF(@UpdateString,N'') is not null AND NULLIF(@ServiceField,N'') is not null
				   SET @UpdateString +=cast(N',' as nvarchar(MAX))

				SELECT @UpdateString +=ISNULL(	STRING_AGG(	CONVERT( NVARCHAR(max)
														   ,N'['+[ServiceField] COLLATE database_default+N'] = '
															+ case when LEFT(ServiceValue,1)=N'[' AND RIGHT(ServiceValue,1)=N']' then N'[source_table].'+[ServiceValue]
																   else [ServiceValue]
															  end	)
													, CHAR(44))
											   ,N'') 
				FROM @tblServiceFields as f
				WHERE [ServiceField] is not null AND [ServiceValue] is not null

		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.7. Зареждане на @WhereString


		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		--  FOR TEST
		IF @SP_MODE = N'TEST1' 
			select @ProcedureName+N'('+@Status+N')' as  [procedure],'Parts of QUERY' tbl
				  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'	
				  ,@nmcl_table_ext as '@nmcl_table_ext'
				  ,@state_table as '@state_table'
				  ,@section_type as '@section_type'
				  ,@section_id as '@section_id'
				  ,@IsTranslateNomenclature as '@IsTranslateNomenclature'  
				  ,@IsDynamicNomenclature as '@IsDynamicNomenclature'
				  ,@comment as '@comment'
				  ,@SelectString as '@SelectString'
				  ,@InsertString as '@InsertString'
				  ,@UpdateString as '@UpdateString'
				  ,@WhereString as '@WhereString'
				  ,@ServiceField as '@ServiceField'
				  ,@ServiceValue as '@ServiceValue'
				  ,@ON_String as '@ON_String'

																				  
		---------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		-- 6.8. replace substitution variables
		IF @section_type = 3 AND @IsTranslateNomenclature=0	-- Grid tables 
					SET @CommandString = cast(
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @GridQuery
									,N'<comment>',@comment)
									,N'<nmcl_table>',@nmcl_table)
									,N'<nmcl_table_ext>',@nmcl_table_ext)
									,N'<state_table>',@state_table)
									,N'<insert_string>',@InsertString)
									,N'<select_string>',@SelectString)
									,N'<update_string>',@UpdateString)
									,N'<service_fields>',@ServiceField)
									,N'<service_values>',@ServiceValue)
									,N'<ON_string>',@ON_String)
									,N'<where_string>',@WhereString)
												as nvarchar(MAX))
			ELSE -- tables translations
						SET @CommandString = cast(N'
'+			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @NoGridQuery
										,N'<comment>',@comment)
										,N'<nmcl_table>',@nmcl_table)
										,N'<nmcl_table_ext>',@nmcl_table_ext)
										,N'<state_table>',@state_table)
										,N'<insert_string>',@InsertString)
										,N'<select_string>',@SelectString)
										,N'<update_string>',@UpdateString)
										,N'<service_fields>',@ServiceField)
										,N'<service_values>',@ServiceValue)
										,N'<ON_string>',@ON_String)
										,N'<where_string>',@WhereString)
													as nvarchar(MAX))
				
				SET @SQLstr += @CommandString+cast(N'
'													 as nvarchar(MAX))

											END -- IF @nRetValue = 1	BEGIN -- ( nmcl->state )
											END -- IF	 (@@FETCH_STATUS <> -2)
						FETCH NEXT FROM table_cursor INTO  @nmcl_table_ext ,@state_table, @IsTranslateNomenclature, @section_type, @order 
											END -- WHILE (@@FETCH_STATUS <> -1)
			CLOSE table_cursor; DEALLOCATE table_cursor

-- STATE -> NMCL =======================================================================================================================================================================
	-------------------------------------------------------------------------------------------------------------------------------------------
	--7. Update @is_changed flag/(delete from state) with internal_number = @internal_number/ Актуализация на is_changed в state таблиците с internal_number = @internal_number
	--  + physical deletion of marked with is_deleted records
		SET @Status += cast(',7' as nvarchar(MAX))
		SET @CommandString = cast(N'' as nvarchar(MAX))

	IF @nRetValue = 1	BEGIN
		SELECT @CommandString += REPLACE(REPLACE(@UpdateStateChangeFlagQuery+case when [root]=1 then @ChangeByAnotherUser else N'' end
												+@DeleteByState /*physical deletion of marked with is_deleted records FROM STATE table*/
											,N'<state_table>',tbl.[state_table_name] COLLATE database_default )
											,N'<comment>', tbl.[state_table_name] COLLATE database_default ) + N'
'		FROM ( SELECT DISTINCT [state_table_name]
							   ,case when [section_type] = 3 /*3 nomenclature row*/ AND [IsTranslateNomenclature] = 0 /*not translations*/	then 1 else 0 end as [root]
							   ,[order]
			   FROM @tblCursor ) as tbl
		ORDER BY tbl.[order]	
						END --8. DELETE record in state tables with internal_number = @internal_number

		IF @SP_MODE = N'TEST1'
			select  @ProcedureName+N'('+@Status+N')' as  [procedure],'DELETE state tables' as tbl, @CommandString as '@CommandString'

		-- добавям изтриването и мултипотребителската обработка
			SET @SQLstr += @CommandString+cast(N'
							'					 as nvarchar(MAX))
	-------------------------------------------------------------------------------------------------------------------------------------------
	--8. IF @SP_MODE='TEST1' only display @SQLstr ELESE executed it
		SET @Status += cast(',8' as nvarchar(MAX))
	IF @SP_MODE = N'TEST1' --само се връща тестов стринг без да се изпълнява
			BEGIN
		SET @FinalTestCommand = case when NULLIF(TRIM(@SQLstr),N'') is null then N'select N''@SQL string not generated'''
								     else N'
select * from #Output	/* Result publication */
SELECT * FROM [dbo].['+@Grid_state_table+N']
	JOIN @Output_root as [output_tbl] ON [output_tbl].[id_grid_row] = ['+@Grid_state_table+N'].[id_grid_row] 
									 AND ISNULL([output_tbl].[rows_group],0) = ISNULL(['+@Grid_state_table+N'].[rows_group],0)
'
												   end
			select  @ProcedureName+N'('+@Status+N')' as  [procedure],'FINAL QUERY' as tbl
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
								  ,TRIM(@SQLstr)+@FinalTestCommand as '@SQLstr'
			END
	ELSE 	BEGIN -- @SP_MODE <> N'TEST1' изпълнява се създадения стринг
			SET @ParmDefinition = N'@internal_number int, @lang nvarchar(255), @lang_id int, @id_user int, @is_empty_grid int'
--print @ParmDefinition
--print substring(@SQLstr, 1   ,4000)
--print substring(@SQLstr, 4001,4000)
--print substring(@SQLstr, 8001,4000)
--print substring(@SQLstr,12001,4000)
			EXEC @ExitCode = sp_executeSQL @SQLstr, @ParmDefinition
									  ,@internal_number=@internal_number				-- 1. Value of internal_number for state tables 
									  ,@lang=@lang										-- 2. Current language from sys_language table
									  ,@lang_id=@lang_id
									  ,@id_user=@id_user								-- 3. Current user from sys_users table
									  ,@is_empty_grid=@is_empty_grid
			SET @Output_json = (SELECT * FROM #Output FOR JSON PATH )
	IF @DeepTEST = 1 begin
			SET @FinalTestCommand = cast(N'
SELECT * INTO [dbo].[tmp_TEST2] 
FROM [dbo].['+@Grid_state_table+N'] WHERE [internal_number]='+CAST(@internal_number as nvarchar(20))+N'
'											as nvarchar(MAX))
	EXEC (@FinalTestCommand)
					 end	-- IF @DeepTEST = 1
			END

	-------------------------------------------------------------------------------------------------------------------------------------------
	--9. Refresh state tables
		SET @Status += cast(',9' as nvarchar(MAX))
	  DECLARE @ConflictOccured_json nvarchar(MAX)
	IF OBJECT_ID('dbo.usp_ssERP8_RefreshDataNMCLState','P') is not null	
	AND COL_LENGTH(@nmcl_table,N'updated_at') is not null
	AND COL_LENGTH(@grid_state_table,N'sys_updated_at') is not null
			BEGIN
		DECLARE @Output_json1 nvarchar(MAX), @prm_ErrMessage nvarchar(MAX)   
					EXEC @ExitCode=[dbo].[usp_ssERP8_RefreshDataNMCLState]
									   @sys_nmcl_id = @sys_nmcl_id						-- 1. ID OR name of nomenclature in sys_nmcl table		
									  ,@lang = @lang									-- 2. Current language from sys_language table
									  ,@id_user = @id_user								-- 3. Current user from sys_users table
									  ,@internal_number = @internal_number				-- 4. Value of internal_number for state tables 
									  ,@nmcl_rows_group_field = @nmcl_rows_group_field	-- 5. Name of group token from nmcl_table 
									  ,@nmcl_rows_group_id = @nmcl_rows_group_ids		-- 6. ID of group token from nmcl_table
									  ,@insert_backward=@insert_backward				-- 7. Flag for backward insert NEW data from nmcl TO STATE 
									  ,@tablename_extra=@tablename_extra				-- 8. Name of relation table for @nmcl_rows_group_field
									  -- 9. JSON with results from search. /*ТОВА касае само процедурата за търсене*/
									  --	{"StringToSearch":"140","TotalResult":2,"Result":[{"rows_group":"0","id_record":86148},{"rows_group":"0","id_record":86149}]}
									  ,@SQLstr=@CommandString OUTPUT					-- 10. Text of refresh script
									  ,@ErrMessage=@prm_ErrMessage OUTPUT				-- 11. Text of error/message
									  ,@Output_json=@Output_json1 OUTPUT				-- 12. Result of procedure
									  -- ,@Return	int	NULL=NULL OUTPUT				-- 13. Return value	(0 - fail 1 - success)
									  ,@SP_MODE=@SP_MODE								-- 14. Operation mode
			IF @prm_ErrMessage is not null SET @ErrMessage += cast(N';'+@prm_ErrMessage	as nvarchar(MAX))
	-------------------------------------------------------------------------------------------------------------------------------------------
	--10. CREATE @ConflictOccured_json FOR: multi-user conflict
		SET @Status += cast(',10' as nvarchar(MAX))
		-- select * from dbo.sys_users
		SET @ConflictOccured_json = CAST(N'' as nvarchar(MAX))
		SET @ExitCode = null
		SET @ParmDefinition = N'@ConflictOccured_json nvarchar(MAX) OUTPUT,@id_user int'
		SET @CommandString = cast(N' SET @ConflictOccured_json =
								(	SELECT	 ['+@grid_state_table+N'].[rows_group]	as [rows_group]
											,['+@grid_state_table+N'].[id_grid_row]	as [id_grid_row]
											,['+@grid_state_table+N'].[updated_by]	as [updated_by]
											,ISNULL([sys_users].[first_name]+'+N'N'' '''+N'+[sys_users].[family_name],N'''') as [user_name]
									FROM [dbo].['+@grid_state_table+N']
										LEFT JOIN [dbo].[sys_users] ON [sys_users].[id]=['+@grid_state_table+N'].[updated_by]
									WHERE ['+@grid_state_table+N'].[is_changed]=-1 AND ISNULL(['+@grid_state_table+N'].[updated_by],0)<>@id_user
									FOR JSON PATH 
								)' as nvarchar(MAX))											

	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'ConflictOccured' as tbl
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
								  ,@CommandString as '@CommandString', @ParmDefinition as '@ParmDefinition'

			EXEC @ExitCode = sp_executeSQL @CommandString, @ParmDefinition
									  ,@ConflictOccured_json=@ConflictOccured_json OUTPUT	-- 1. JSON with conflict id_grid_rows
									  ,@id_user=@id_user									-- 2. Current user from sys_users table
			SET @ConflictOccured_json = case when @ConflictOccured_json is null then (SELECT N'no conflicting rows' as [user_name] FOR JSON PATH )
											 else @ConflictOccured_json
										 end
	IF @DeepTEST = 1 begin
			SET @FinalTestCommand = cast(N'
SELECT * INTO [dbo].[tmp_TEST3] 
FROM [dbo].['+@Grid_state_table+N'] WHERE [internal_number]='+CAST(@internal_number as nvarchar(20))+N'
'											as nvarchar(MAX))
	EXEC (@FinalTestCommand)
					 end	-- IF @DeepTEST = 1
			END -- IF OBJECT_ID('dbo.usp_ssERP8_RefreshDataNMCLState','P') is not null


END TRY
BEGIN CATCH																																																	
-- Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.																																	
		-- close opened transactions
		WHILE @@TRANCOUNT > 0 ROLLBACK TRANSACTION
		-- close opened cursors
			DECLARE @destroyed_cursor_cmd nvarchar(MAX) = N''
			SELECT @destroyed_cursor_cmd += case when [is_open] = 1 then N' CLOSE '+[name]+N';' else N'' end  +  N' DEALLOCATE '+[name]+N';' FROM sys.dm_exec_cursors(@@SPID)
			print @destroyed_cursor_cmd; exec (@destroyed_cursor_cmd)		

		DECLARE @ERROR_PROCEDURE nvarchar(128) = ISNULL(ERROR_PROCEDURE(),@ProcedureName)
		DECLARE @ERROR_MESSAGE nvarchar(2048) = ISNULL(ERROR_MESSAGE(),N'n/a')
		SET @ErrMessage = cast( isnull(@ErrMessage+N',',N'')
							 +N'SQL Msg: ' + ISNULL(cast(ERROR_NUMBER() as nvarchar(20)),N'')	+N','
							 +N' Level ' + ISNULL(cast(ERROR_SEVERITY() as nvarchar(20)),N'')	+N','
							 +N' State ' + ISNULL(cast(ERROR_STATE() as nvarchar(20)),N'')		+N','
							 +N' Proc ' + @ERROR_PROCEDURE+ISNULL(N'-'+@Status,N'')									
							 +N'('+ISNULL('Line: '+cast(ERROR_LINE() as nvarchar(20)),N'')		+N'),'
							 + @ERROR_MESSAGE as nvarchar(MAX))		
		SET @nRetValue = 0 
END CATCH



	-- Запазване на данни за анализ на грешката
	IF @TestSaveParamID > 0 
		UPDATE [StepSoft].[dbo].[PARAMETERS] 
		   SET [PrmXml] = ISNULL(@Output_json,N'null')+N'
===============================================================
'						+ ISNULL(@Output_json1,N'null')+N'
===============================================================
@cnt_all='+ CAST(@cnt_all as nvarchar(20))+N'@cnt_mrg='+ CAST(@cnt_mrg as nvarchar(20))+N'@cnt_del='+ CAST(@cnt_del as nvarchar(20))
	WHERE [ID] = @TestSaveParamID 

	IF @nRetValue = 0 AND @TestSaveParamID > 0 UPDATE [StepSoft].[dbo].[PARAMETERS] SET [ErrMessage] = @ErrMessage WHERE [ID] = @TestSaveParamID 
	DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error SELECT [Value] as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
		SET @ErrMessage = (SELECT * FROM @Error FOR JSON AUTO , ROOT('Errors'))
	-- Определяне на перформънс
	IF @TestSaveParamID > 0 UPDATE [StepSoft].[dbo].[PARAMETERS] SET [EndTime] = GETDATE() WHERE [ID] = @TestSaveParamID 
	-- Връщане на резултат
	SET @Return = @nRetValue
	SELECT @nRetValue as [Return], @ErrMessage as [Error_json],@Output_json as [Output_json], @ConflictOccured_json /*@ErrMessage*/ as [ErrMessage], @Output_json as [Output_message] 

RETURN @nRetValue
END