SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP PROCEDURE IF EXISTS [dbo].[usp_ssERP8_CompanyANDBranch]
GO
/*

--*/
-- =========================================================================================================================
CREATE PROCEDURE 
-- Name			
				[dbo].[usp_ssERP8_CompanyANDBranch]
-- Author		Щерю Щерев																									
-- Create date	22/02/2025
-- Version		
--
-- Serever		MS SQL 2016																										
-- Description	transfer columns between nmcl_company AND nmcl_companyBranch
-- Returns		0 - fail 1 - success
-- Parameters														 
	    @Source_keyValue_json		 nvarchar(MAX)						 -- 1. JSON with updated rows from nmcl_company
	   ,@lang					     nvarchar(255)	NULL=N'bg'			 -- 2. Current language from sys_language table
	   ,@companyBranch_keyField		 nvarchar(128)	NULL=N'Branch_num'	 -- 3. Field name for branch identifier
	   ,@companyBranch_keyValue		 nvarchar(255)	NULL=N'0'			 -- 4. field value indicating that branch = company
	   ---------------------------------------------
	   ,@ErrMessage				nvarchar(MAX)	NULL=NULL OUTPUT		 -- 5. Text of error/message
	   ,@Return					int				NULL=NULL OUTPUT		 -- 6. Return value	(0 - fail 1 - success)
	   ,@SP_MODE				nvarchar(255)	NULL=N'EXECUTE'			 -- 7. Operation mode
-- Constraction 
-- Tables		sys_nmcl, all tables containing data of nomenclature
--=========================================================================================================================
AS BEGIN
/* manualUpdateFlag: No */
SET NOCOUNT ON
BEGIN TRY
	/*

		,@companyBranch_keyField nvarchar(128)=N'Branch_num'
		,@companyBranch_keyValue nvarchar(255)=N'0'
		,@lang					nvarchar(255)=N'bg'
		,@ErrMessage			nvarchar(MAX)	
		,@Return				int				
		,@SP_MODE				nvarchar(255) ='TEST1'	
--	*/
	DECLARE @nRetValue int=1, @ExitCode int, @Status nvarchar(255) = N'0'
	DECLARE @ParmDefinition nvarchar(MAX), @CommandString nvarchar(MAX)
	DECLARE @SQLstr nvarchar(MAX) = cast(N'' as nvarchar(MAX))
	DECLARE @ProcedureName sysname = ISNULL(OBJECT_NAME(@@PROCID),N'usp_ssERP8_CompanyANDBranch'), @POS nvarchar(130) = HOST_NAME()

	DECLARE		/*SELECT*/	@SelectField nvarchar(MAX), @SelectField_tr nvarchar(MAX),
				/*WHERE*/	@Where nvarchar(MAX), @Where_tr nvarchar(MAX),
				/*UPDATE*/	@UpdateField nvarchar(MAX), @UpdateField_tr nvarchar(MAX),
				/*INSERT*/	@InsertField_c nvarchar(MAX), @InsertField_v nvarchar(MAX),
							@InsertField_tr_c nvarchar(MAX), @InsertField_tr_v nvarchar(MAX),
				/* ON */	@On_condition nvarchar(MAX), @On_condition_tr nvarchar(MAX)
	DECLARE @tblServiceFields TABLE ( [ServiceField] nvarchar(130) )
		SET @ErrMessage = cast(N'' as nvarchar(MAX))
	DROP TABLE IF EXISTS #tblOutput
	CREATE TABLE #tblOutput ([table] NVARCHAR(128),[action] NVARCHAR(10),[src_ID] INT,[tgt_ID] INT)

	-- 1. Parameters sniffing
		SET @Status += ',1'
		--	SET @SP_MODE = case when @SP_MODE is null THEN 'EXECUTE' 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
		-- @Source_keyValue_json
		DECLARE @Source_table nvarchar(128),@Target_table nvarchar(128), @grid_state_table nvarchar(128)
		DECLARE @tblSource_keyValue TABLE ( [nmcl] nvarchar(128), [action] nvarchar(10), [inserted_id] int, [deleted_id] int )
			DROP TABLE IF EXISTS #IDs; CREATE TABLE #IDs ( [inserted_id] int, [deleted_id] int )
			IF ISNULL(ISJSON(@Source_keyValue_json),0) = 1	BEGIN
				-- зареждане на таблица от JSON
				INSERT INTO @tblSource_keyValue ( [nmcl], [action], [inserted_id], [deleted_id] )
				SELECT [nmcl], [action], [inserted_id], [deleted_id]
				FROM OPENJSON(@Source_keyValue_json)	WITH (
															     nmcl			nvarchar(128) '$.nmcl'
																,action			nvarchar(10)  '$.action'
																,inserted_id	int			  '$.inserted_id'	
																,deleted_id		int			  '$.deleted_id'
																,is_changed		int			  '$.is_changed'
																,is_new			int			  '$.is_new'
																,is_deleted		int			  '$.is_deleted'
															 )
				WHERE [is_changed] is not null AND [is_new] is not null AND [is_deleted] is not null /*ПО ТОВА СЕ ОПРЕДЕЛЯ state grid table*/
				/* АКО НЕ СТАВА С ГОРНОТО
				SET @grid_state_table =
						(	SELECT TOP(1) [state_table_name] 
							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,5))
							   OR  ([sys_state_element_exists] = N'1100' and [section_type] IN (4))	)
							  AND [section_type]=3 AND [rm_element_translatable]=0
						 )	  --	*/
				-- извличане на source номенклатурата
				DECLARE @nPos int 
				UPDATE @tblSource_keyValue SET  @nPos=case when ISNULL(CHARINDEX(N'->',[nmcl]),0) > 0 then CHARINDEX(N'->',[nmcl]) else 0 end
												,[nmcl]= SUBSTRING([nmcl],@nPos+2,128)
				-- проверка към коя номенклатура е направено прехвърляне (nmcl_company или nmcl_companyBranch) и извличане на засегнатите ID
				SET @Source_table = case when exists ( select 1 from @tblSource_keyValue where [nmcl] = N'nmcl_company')		then N'nmcl_company'
										 when exists ( select 1 from @tblSource_keyValue where [nmcl] = N'nmcl_companyBranch')	then N'nmcl_companyBranch'
									end
					IF @Source_table = 	N'nmcl_company'				
					   INSERT INTO #IDs ([inserted_id], [deleted_id])
							SELECT DISTINCT [inserted_id], [deleted_id]
							FROM @tblSource_keyValue as [js] 	WHERE [nmcl] = N'nmcl_company'
					ELSE IF @Source_table = N'nmcl_companyBranch'				
							INSERT INTO #IDs ([inserted_id], [deleted_id])	
								SELECT DISTINCT [inserted_id], [deleted_id]
								FROM @tblSource_keyValue as js	WHERE [nmcl] = N'nmcl_companyBranch'
				
				-- невалиден JSON
				IF @Source_table is null
					begin SET @ErrMessage += N';Invalid @Source_keyValue_json parameter'; SET @nRetValue = 0 end
				ELSE begin
						SET @Target_table = case when @Source_table=N'nmcl_company' then N'nmcl_companyBranch'
												 when @Source_table=N'nmcl_companyBranch' then N'nmcl_company' end
						
					 end
															END -- IF ISNULL(ISJSON(@Source_keyValue_json),0) = 1	
		-- @companyBranch_keyField AND @companyBranch_keyValue
		DECLARE @companyBranch_keyFieldType nvarchar(32), @r bit
			-- определяне на типа на полето @companyBranch_keyFieldType 
			SELECT @companyBranch_keyFieldType = TYPE_NAME([system_type_id])
			FROM sys.columns (NOLOCK)
			WHERE [name]=@companyBranch_keyField AND [object_id]=OBJECT_ID(N'nmcl_companyBranch',N'U')

			-- проверка дали има такова поле в nmcl_companyBranch и дали е от подходящ тип
			IF @companyBranch_keyFieldType is null
				 begin  SET @ErrMessage += N';Missing @companyBranch_keyField field'; SET @nRetValue = 0 end
			ELSE begin
				SET @ParmDefinition = N'@r bit OUTPUT, @companyBranch_keyValue nvarchar(255)'
				SET @CommandString = N'set @r = case when TRY_CONVERT('+@companyBranch_keyFieldType+N',@companyBranch_keyValue) is null then 0 else 1 end'
					EXEC @ExitCode = sp_executesql @CommandString, @ParmDefinition
												  ,@companyBranch_keyValue=@companyBranch_keyValue
												  ,@r=@r OUTPUT
					IF @r = 0 begin  SET @ErrMessage += N';Invalid @companyBranch_keyValue field type'; SET @nRetValue = 0 end
				 end	
		-- @lang
		-- declare @lang nvarchar(255)=N'bg',@ErrMessage nvarchar(MAX)=N'',@nRetValue int 
			DECLARE @lang_id int = (SELECT [id] FROM sys_languages WHERE [language]=@lang AND [status]=1)
			IF @lang_id is null 
				begin SET @ErrMessage += N';Missing language parameter'; SET @nRetValue = 0 end

		-- required procedures and functions
			--IF NOT EXISTS (select 1 from sys.objects where [name] = N'ufn_ssCollectNomenclElements2' )
			--	begin SET @ErrMessage += N';Missing function dbo.ufn_ssCollectNomenclElements2'; SET @nRetValue = 0 end
	
	-- 2. Logging the data for analysis / Запазване на данни за анализ
		SET @Status += ',2'
		DECLARE @TestSaveParamID int = 0
	IF OBJECT_ID(N'[stepsoft].[dbo].[usp_ssTestSaveParam]',N'P') is not null AND LEFT(@SP_MODE,4)=N'TEST'	
	begin
		DECLARE @PrmString varchar(7168) =	ISNULL(@Source_keyValue_json,'null')+','		-- 1. JSON with updated rows from nmcl_company
										 +	ISNULL(@lang,'null')+','						-- 2. Current language from sys_language table
										 +	ISNULL(@companyBranch_keyField,'null')+','		-- 3. Field name for branch identifier
										 +	ISNULL(@companyBranch_keyValue,'null')+','		-- 4. field value indicating that branch = company
										 +	ISNULL(@SP_MODE,'null')							-- 7. Operation mode
		DECLARE @DBName nvarchar(130) = DB_NAME()
		DECLARE @PrmJson nvarchar(MAX) = ( SELECT  @Source_keyValue_json	 as '@Source_keyValue_json'		-- 1. JSON with updated rows from nmcl_company
												  ,@lang					 as '@lang'						-- 2. Current language from sys_language table
												  ,@companyBranch_keyField	 as '@companyBranch_keyField'	-- 3. Field name for branch identifier
												  ,@companyBranch_keyValue	 as '@companyBranch_keyValue'	-- 4. field value indicating that branch = company
												  ,@ErrMessage				 as '@ErrMessage'				-- 5. Text of error/message
												  ,@Return					 as '@Return'					-- 6. Return value	(0 - fail 1 - success)
												  ,@SP_MODE					 as '@SP_MODE'					-- 7. Operation mode
											FOR JSON PATH, WITHOUT_ARRAY_WRAPPER )							
		 EXEC [stepsoft].[dbo].[usp_ssTestSaveParam] @PrmMessage = @PrmString, @CallingModule = @ProcedureName, @XMLString = null
													,@Separator = ',', @DataBase = @DBName, @RecordID = @TestSaveParamID OUTPUT
													,@PrmJson = @PrmJson
	end -- IF LEFT(@SP_MODE,4)=N'TEST' AND	OBJECT_ID(N'[]stepsoft.[dbo].[usp_ssTestSaveParam]',N'P') is not null
		print @ProcedureName+'->SP_MODE = ' + ISNULL(@SP_MODE,'NULL')+' '+@ProcedureName+'->TestSaveParamID = ' + ISNULL(cast(@TestSaveParamID as varchar(10)),'NULL')

	IF @SP_MODE = N'TEST1'	BEGIN
			select @ProcedureName+N'('+@Status+N')' as  [procedure],'Input parameters' as tbl
				  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
				  ,@Source_keyValue_json as '@Source_keyValue_json'
				  ,@companyBranch_keyField as '@companyBranch_keyField', @companyBranch_keyValue as '@companyBranch_keyValue'
				  ,@grid_state_table as '@grid_state_table'
				  ,@lang as '@lang', @lang_id as '@lang_id',@SP_MODE as '@SP_MODE'
		IF exists (select 1 from #IDs)	 select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Input data in #IDs' as tbl, * from #IDs
		ELSE	begin
				IF exists (select 1 from @tblSource_keyValue )	select  @ProcedureName+N'('+@Status+N')' as  [procedure],'@tblSource_keyValue' as tbl,* from @tblSource_keyValue
				ELSE								select  @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in @tblSource_keyValue' as tbl
													select  @ProcedureName+N'('+@Status+N')' as  [procedure],'No data in #IDs' as tbl
				end
							END -- IF @SP_MODE = N'TEST1'

	-------------------------------------------------------------------------------------------------------------------------------------------
	-- 3. Extract tables and columns / Извличане на таблиците и колоните
	-- select * from sys.columns
		SET @Status += ',3'
	IF @nRetValue = 1	begin
		DROP TABLE IF EXISTS #tblTransfer;
		CREATE TABLE #tblTransfer ( [table] nvarchar(128), [column] nvarchar(128) )
		-- select * from nmcl_company -- определяне на общите полета в nmcl_Company и nmcl_companyBranch
			INSERT INTO #tblTransfer (	[table],[column] )
			SELECT  @source_table as [table]
				   ,src.[name] as [column]
			  FROM sys.columns		(NOLOCK) as src 
				 JOIN sys.columns	(NOLOCK) as trg ON trg.[name] = src.[name] AND src.[system_type_id]=trg.[system_type_id]
			 WHERE src.[object_id] = OBJECT_ID(N'nmcl_company','U')
			   AND trg.[object_id] = OBJECT_ID(N'nmcl_companyBranch','U')
			   AND trg.[is_identity]=0 AND src.[is_identity]=0
			   AND src.[name] NOT IN (N'ID')
		-- select * from nmcl_company_translations -- определяне на общите полета в nmcl_Company_translations и nmcl_companyBranch_translations
			INSERT INTO #tblTransfer (	[table],[column] )
			SELECT  @Source_table+N'_translations' as [table]
				   ,src.[name] as [column]
			  FROM sys.columns		(NOLOCK) as src 
				 JOIN sys.columns	(NOLOCK) as trg ON trg.[name] = src.[name] AND src.[system_type_id]=trg.[system_type_id]
			 WHERE src.[object_id] = OBJECT_ID(N'nmcl_company_translations','U')
			   AND trg.[object_id] = OBJECT_ID(N'nmcl_companyBranch_translations','U')
			   AND trg.[is_identity]=0 AND src.[is_identity]=0
			   AND src.[name] NOT IN (N'ID')
		--;WITH CTE -- изтриване на дублираните по имена елементи в една таблица (изтрива след първия )
		--   AS	(  SELECT [column], ROW_NUMBER() OVER (PARTITION BY table_source,table_target,[column] ORDER BY [column] ) as Duplicate FROM #tblTransfer )
		--DELETE FROM CTE WHERE Duplicate > 1
	IF not exists (select 1 from #tblTransfer )
				begin SET @ErrMessage += N';no columns with the same names for transfer'; SET @nRetValue = 0 end
	IF @SP_MODE = N'TEST1' AND EXISTS (select 1 from #tblTransfer ) select  @ProcedureName+N'('+@Status+N')' as  [procedure],'columns into #tblTransfer' as tbl, * from #tblTransfer
	ELSE IF @SP_MODE = N'TEST1'										select  @ProcedureName+N'('+@Status+N')' as  [procedure],'No rows in #tblTransfer' as tbl
																		   ,@ErrMessage as '@ErrMessage', @nRetValue as '@nRetValue'
						end	-- IF @nRetValue = 1

	-- 4. Create field strings / Създаване на стрингове от полетата
		SET @Status += ',4'
	IF @nRetValue = 1	begin
		-- основните таблици
		SELECT @SelectField = cast( STRING_AGG(N'['+@Source_table+N'].['+[column]+N']',N',')
								    + case when @Source_table = N'nmcl_companyBranch'	then N',['+@Source_table+N'].['+@companyBranch_keyField+N'],['+@Source_table+N'].[id_nmcl_Company]'
										   else N''
									  end															as nvarchar(MAX))
			  ,@Where = cast( case when @Source_table = N'nmcl_companyBranch'			then N'WHERE ['+@Source_table+N'].['+@companyBranch_keyField+N']='''+@companyBranch_keyValue+N'''' 
								   else N''	
							  end																	as nvarchar(MAX))	

			  ,@UpdateField = cast( STRING_AGG([column]+N'=src.['+[column]+N']',N',')				as nvarchar(MAX))
			  ,@InsertField_c = cast( STRING_AGG([column],N',')
								    + case when @Source_table = N'nmcl_company'			then N','+@companyBranch_keyField+N',[id_nmcl_Company]'
										   else N''
									  end															as nvarchar(MAX))
			  ,@InsertField_v = cast( STRING_AGG([column],N',')
								    + case when @Source_table = N'nmcl_company'			then N','''+@companyBranch_keyValue+N''',[id]'
										   else N''
									  end															as nvarchar(MAX))
		FROM #tblTransfer WHERE [table]=@Source_table

		-- таблиците за превод
		SELECT @SelectField_tr = cast( STRING_AGG(N'['+@Source_table+N'_translations].['+[column]+N']',N',')	as nvarchar(MAX))
			  ,@UpdateField_tr = cast( STRING_AGG([column]+N'=src.['+[column]+N']',N',')						as nvarchar(MAX))
			  ,@InsertField_tr_c = cast( STRING_AGG([column],N',')
								   +N',[id_'+@Target_table+N']'													as nvarchar(MAX))
			  ,@InsertField_tr_v = cast( STRING_AGG([column],N',')												
								   +N',[tgt_id]'																as nvarchar(MAX))
		FROM #tblTransfer WHERE [table]=@Source_table+N'_translations'
		-- ON условие
		SET @ON_condition = case when @Source_table=N'nmcl_company'			
									then cast(N'src.[ID]=tgt.[id_nmcl_company] AND tgt.['+@companyBranch_keyField+N']='''+@companyBranch_keyValue+N'''' as nvarchar(MAX))

								 when @Source_table=N'nmcl_companyBranch'	
									-- then cast(N'src.[ID_nmcl_company]=tgt.[ID]' as nvarchar(MAX))
									then cast(N'src.[Bulstat]=tgt.[Bulstat]' as nvarchar(MAX))

								else null
							end
		SET @ON_condition_tr = case when @Source_table=N'nmcl_company'			
									then cast(N'src.[tgt_ID]=tgt.[id_nmcl_companyBranch] AND tgt.[lang]=src.[lang]' as nvarchar(MAX))

								 when @Source_table=N'nmcl_companyBranch'	
									then cast(N'src.[tgt_ID]=tgt.[id_nmcl_company] AND tgt.[lang]=src.[lang]' as nvarchar(MAX))

								else null
							end
						end -- IF @nRetValue = 1
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'PARTS of @SQLstr' as tbl
								  ,@SelectField as '@SelectField',@UpdateField as '@UpdateField',@InsertField_c as '@InsertField_c',@InsertField_v as '@InsertField_v'
								  ,@SelectField_tr as '@SelectField_tr',@UpdateField_tr as '@UpdateField_tr',@InsertField_tr_v as '@InsertField_tr_v'
								  ,@ON_condition as '@ON_condition',@ON_condition_tr as '@ON_condition_tr'

	-- 5. Create transfer script / Създаване на скрипт за трансфер
	-- select * from nmcl_companyBranch
		SET @Status += ',5'
		SET @SQLstr = cast( N'' as nvarchar(MAX))
		-- 5.1. само за тест
			SET @Status += N'a'
			IF @SP_MODE='TEST1'	BEGIN
					SET @SQLstr += cast(N'/*Procedure: '+ISNULL(@ProcedureName,N'null')+N' - for test in SSMS omly*/
							DROP TABLE IF EXISTS #tblOutput
							CREATE TABLE #tblOutput ([table] NVARCHAR(128),[action] NVARCHAR(10),[src_ID] INT,[tgt_ID] INT)
							DROP TABLE IF EXISTS #IDs
							CREATE TABLE #IDs ( [inserted_id] int, [deleted_id] int )'
										as nvarchar(MAX))
					IF exists (select 1 from #IDs )	begin
						SET @SQLstr += cast( N'
							INSERT INTO #IDs ( [inserted_id], [deleted_id] ) /*'+@Source_table+N'->'+@Target_table+N'*/
							VALUES '		as nvarchar(MAX))
						SELECT TOP(10)
			@SQLstr += cast( STRING_AGG( N'('+ISNULL(cast([inserted_id] as nvarchar(20)),N'null')+N','+ISNULL(cast([deleted_id] as nvarchar(20)),N'null')+N')'
										,N',')
							 as nvarchar(MAX))
						FROM #IDs
													end -- IF exists (select 1 from #IDs )
					else /*not exists (select 1 from #IDs )*/ SET @SQLstr += cast(N'
					/*NOT rows in #IDs table*/' as nvarchar(MAX))
								END -- IF @SP_MODE='TEST1'

		-- 5.2. ОСНОВЕН СКРИПТ
-- Company(src)->Branch(tgt): src.id=tgt.id_nmcl_company
-- Branch(src)->Company(tgt): src.id_nmcl_company=tgt.id
		SET @Status += N'b'	
		SET @SQLstr += cast( N'
/* transfering data from '+@Source_table+N' to '+@Target_table+N' */
DECLARE @Output_root TABLE ([action] NVARCHAR(10),[src_ID] INT,[tgt_ID] INT)
	MERGE '+@Target_table+N'					 AS tgt  
	USING	(	SELECT	 ['+@Source_table+N'].[ID]
						,<<SelectField>>
				FROM '+@Source_table+N'
					JOIN (select DISTINCT [inserted_id] from #IDs where [inserted_id] is not null) as js ON js.[inserted_id]=['+@Source_table+N'].[id]
				'+@Where+N'
			)									 AS src

		ON <<ON_condition>>

		WHEN MATCHED THEN UPDATE SET <<UpdateField>>	
		WHEN NOT MATCHED THEN INSERT ( <<InsertField_c>> )
							  VALUES ( <<InsertField_v>> )
	OUTPUT $ACTION,src.[ID],INSERTED.ID INTO @Output_root;
INSERT INTO #tblOutput SELECT '''+@Source_table+N'->'+@Target_table+N''' as [table],[action],[src_ID],[tgt_ID] FROM @Output_root
'
+ case when @Source_table = N'nmcl_companyBranch' AND exists ( select 1 from #IDs where inserted_id is not null AND deleted_id is null)
		then N'
DECLARE @id_nmcl_companyBranch int = ( select TOP(1) [src_ID] from @Output_root ),@id_nmcl_company int = ( select TOP(1) [tgt_ID] from @Output_root )
UPDATE [dbo].[nmcl_companyBranch] SET id_nmcl_company = @id_nmcl_company WHERE @id_nmcl_company is not null AND [id] = @id_nmcl_companyBranch
'
	   else  N'' end	
+N'
/* transfering data from '+@Source_table+N'_translations to '+@Target_table+N'_translations */
DECLARE @Output TABLE ([action] NVARCHAR(10),[src_ID] INT,[tgt_ID] INT)
	MERGE '+@Target_table+N'_translations		 AS tgt  
	USING	(	SELECT	 [OUTPUT].[tgt_id]
						,['+@Source_table+'_translations].[ID] as [src_id]
						,<<SelectField_tr>>
				FROM '+@Source_table+N'_translations
					JOIN '+@Source_table+N' ON ['+@Source_table+N'].[ID] = ['+@Source_table+N'_translations].[id_'+@Source_table+N']
					JOIN @Output_root AS [OUTPUT] ON [OUTPUT].[src_id] = ['+@Source_table+N'].[id]  
			)									 AS src
		ON '+@ON_condition_tr+N'
		WHEN MATCHED THEN UPDATE SET <<UpdateField_tr>>	
		WHEN NOT MATCHED THEN INSERT ( <<InsertField_tr_c>> )
							  VALUES ( <<InsertField_tr_v>> )
	OUTPUT $ACTION,src.src_id,INSERTED.ID INTO @Output;
INSERT INTO #tblOutput SELECT '''+@Source_table+N'_translations'' AS [table],[action],[src_id],[tgt_id] FROM @Output
'								as nvarchar(MAX))

+ case when @Source_table = N'nmcl_company' AND exists ( select 1 from #IDs where inserted_id is null AND deleted_id is not null)
		then cast( N'/* deleteing data for ALL branches from nmcl_companyBranch when company was deleted */
DECLARE @Output_d TABLE ( [action] NVARCHAR(10),[ID] INT	)
	DELETE Branches
		OUTPUT N''DELETE'', DELETED.ID	INTO @Output_d
	FROM (	select [nmcl_companyBranch].* from nmcl_companyBranch
			  join (select DISTINCT [deleted_id] from #IDs where [inserted_id] is null AND [deleted_id] is not null) as js 
					ON js.[deleted_id]=[nmcl_companyBranch].[id_nmcl_company]
		 ) as src
		 JOIN nmcl_companyBranch as Branches ON Branches.ID=src.ID;
INSERT INTO #tblOutput SELECT ''DELETED nmcl_company->nmcl_companyBranch'' as [table],[action],[ID] FROM @Output_d

/* deleteing data for ALL branches from nmcl_companyBranch_translations when company was deleted */
DELETE @Output
	DELETE Branches_translations
		OUTPUT N''DELETE'',DELETED.ID INTO @Output
	FROM ( select [nmcl_companyBranch_translations].* from nmcl_companyBranch_translations
			  join @Output_d as Output_d ON [nmcl_companyBranch_translations].[id_nmcl_companyBranch]=[Output_d].[id] AND [Output_d].[action]=N''DELETE''
		 ) as src
		 JOIN nmcl_companyBranch_translations as Branches_translations ON Branches.ID=src.ID
INSERT INTO #tblOutput SELECT ''DELETED nmcl_companyBranch->nmcl_companyBranch_translations'' AS [table],[action],[ID] FROM @Output 
'				as nvarchar(MAX))
		else CAST(N'' as nvarchar(MAX))
  end

	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'Template transfer SQL script' as tbl
								  ,@nRetValue as '@nRetValue', @ErrMessage as '@ErrMessage'
								  ,@SQLstr as '@SQLstr'


	-- 6. Create final SQL script / Създаване на окончателен скрипт
		SET @Status += ',6'
	IF @nRetValue = 1	begin	
	SET @CommandString = REPLACE(REPLACE( REPLACE( REPLACE( REPLACE ( REPLACE( REPLACE( REPLACE( REPLACE ( @SQLstr
							,N'<<SelectField>>',	 @SelectField )
							,N'<<ON_condition>>',	 @ON_condition)				
							,N'<<UpdateField>>',	 @UpdateField )	
							,N'<<InsertField_c>>',	 @InsertField_c )	
							,N'<<InsertField_v>>',	 @InsertField_v )	
							,N'<<SelectField_tr>>',	 ISNULL(@SelectField_tr,N'')	 )			
							,N'<<UpdateField_tr>>',	 ISNULL(@UpdateField_tr,N'')	 )	
							,N'<<InsertField_tr_c>>',ISNULL(@InsertField_tr_c ,N'')	 )	
							,N'<<InsertField_tr_v>>',ISNULL(@InsertField_tr_v, N'')	 )
	SET @ParmDefinition = N''
						end -- IF @nRetValue = 1
	IF @SP_MODE = N'TEST1' select  @ProcedureName+N'('+@Status+N')' as  [procedure],'FINAL SCRIPT' as tbl
								  ,@companyBranch_keyField as '@companyBranch_keyField', @companyBranch_keyValue as '@companyBranch_keyValue'	
								  ,@CommandString as '@CommandString', @ParmDefinition as '@ParmDefinition'
								  ,@ErrMessage as '@ErrMessage', @nRetValue as '@nRetValue'


	-- 7. Execute transfering string / Изпълнение на стринг за трансфер
		SET @Status += ',7'
	IF @nRetValue = 1 AND @SP_MODE<>N'TEST1' 
	AND	-- Ако няма засегнати id при записа в номенклатурата => няма какво да правим повече
		exists ( select 1 from #IDs )			begin

		EXEC @ExitCode = sp_executesql @CommandString, @ParmDefinition
		IF @ExitCode > 0 begin SET @ErrMessage += N';Transfer error occurred'; SET @nRetValue = 0 end

												end	-- IF @nRetValue = 1 AND @SP_MODE<>'TEST1'	
	-------------------------------------------------------------------------------------------------------------------------------------------
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(130) = ISNULL(ERROR_PROCEDURE(),@ProcedureName)+ISNULL(N'-'+@Status,N'')
		DECLARE @ERROR_MESSAGE nvarchar(2048) = ISNULL(ERROR_MESSAGE(),N'n/a')
		SET @ErrMessage = 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									
							 +N'('+ISNULL('Line: '+cast(ERROR_LINE() as nvarchar(20)),N'')		+N'),'
							 + @ERROR_MESSAGE		
		SET @nRetValue = 0 
END CATCH

	SET @Return = @nRetValue
	DECLARE @Error TABLE ( [Error] nvarchar(2048) ); INSERT INTO @Error SELECT cast([Value] as nvarchar(1024)) as Error FROM string_split(@ErrMessage,';')	WHERE ISNULL([Value],N'') <> N'' 
	DECLARE @Error_json nvarchar(MAX) = (SELECT * FROM @Error FOR JSON PATH , ROOT('Errors'))
	DECLARE @Output_json nvarchar(MAX) = (SELECT * FROM #tblOutput FOR JSON PATH , ROOT('Data'))

	SELECT @Return as [Return], @Error_json as [Error_json], @Output_json as [Output_json]

RETURN @nRetValue
END