IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE [Name] = N'IDWew' AND Object_ID = Object_ID(N'SSCommon.STContractors'))
BEGIN
    ALTER TABLE [SSCommon].[STContractors] ADD [IDWew] NVARCHAR(5) NULL;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE [Name] = N'IDWew' AND Object_ID = Object_ID(N'SSCommon.STContractorsRep'))
BEGIN
    ALTER TABLE [SSCommon].[STContractorsRep] ADD [IDWew] NVARCHAR(5) NULL;
END
GO

CREATE OR ALTER TRIGGER [SSCommon].[Contractors_TH_IOU] 
   ON [SSCommon].[STContractors] 
   INSTEAD OF UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @CI                varchar(128)     = context_info();
    DECLARE @st_modified_by    uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_replication_id uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_state          int              = 3;
    DECLARE @st_server         varchar(100)     = RTRIM(CONVERT(char(100), SERVERPROPERTY('servername')));
    DECLARE @st_last_modified  datetime         = GETDATE();
    DECLARE @is_mirror_db_guid char(1)          = '0'
    DECLARE @skip_history      char(1)          = '0';
    DECLARE @st_db_guid        uniqueidentifier = '00000000-0000-0000-0000-000000000000';    
    DECLARE @endpos            int              = CHARINDEX('*', @CI);
    
    if (@endpos > 0)
    BEGIN
	    DECLARE @divpos1 int = CHARINDEX('@', @CI);
	    DECLARE @divpos2 int = CHARINDEX('$', @CI);
	    DECLARE @divpos6 int = CHARINDEX('|', @CI);
	    DECLARE @divpos7 int = CHARINDEX('#', @CI);	    	    

        SET @st_modified_by    = SUBSTRING(@CI, 1,            @divpos1 - 1);
	    SET @st_replication_id = SUBSTRING(@CI, @divpos1 + 1, @divpos2 - @divpos1 - 1);
	    SET @is_mirror_db_guid = SUBSTRING(@CI, @divpos6 + 1, @divpos7 - @divpos6 - 1);
  	    SET @skip_history =		 SUBSTRING(@CI, @divpos7+1,   LEN(@CI) - @divpos7);

	    IF @is_mirror_db_guid = '1'
	    BEGIN			
			SET @st_db_guid = @st_replication_id;
			SET @st_replication_id = '00000000-0000-0000-0000-000000000000';
		END
    END;

    UPDATE SSCommon.[STContractors]
    SET 
    [Guid] = inserted1.[Guid], [Shortcut] = inserted1.[Shortcut], [Name] = inserted1.[Name], [NaturalPerson] = inserted1.[NaturalPerson], [NIP] = inserted1.[NIP], [PESEL] = inserted1.[PESEL], [REGON] = inserted1.[REGON], [NIP_UE] = inserted1.[NIP_UE], [Note] = inserted1.[Note], [ContactGuid] = inserted1.[ContactGuid], [BankingInfoGuid] = inserted1.[BankingInfoGuid], [LimitFlag] = inserted1.[LimitFlag], [LimitAmount] = inserted1.[LimitAmount], [LimitCurrency] = inserted1.[LimitCurrency], [VIES] = inserted1.[VIES], [Negotiation] = inserted1.[Negotiation], [VATPayerActive] = inserted1.[VATPayerActive], [LinkedUnit] = inserted1.[LinkedUnit], [MainElement] = inserted1.[MainElement], [MainPerson] = inserted1.[MainPerson], [VATGroup] = inserted1.[VATGroup], [JST] = inserted1.[JST], [IDWew] = inserted1.[IDWew],
    st_last_modified = @st_last_modified 
	 FROM SSCommon.[STContractors] as Contractors0 INNER JOIN inserted as inserted1 ON (inserted1.[Id]=Contractors0.[Id]) ;

	
	
 	IF(@skip_history <> '1')
	BEGIN
		INSERT INTO SSCommon.[STContractorsRep]
		([Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata , st_state, st_modified_by, st_host, st_client, st_server, st_replication_id, st_db_guid)
		SELECT 
		    [Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
		    @st_last_modified,
		    NULL,
		    @st_state,
		    @st_modified_by,
		    HOST_NAME(),
		    APP_NAME(),
		    @st_server,
		    @st_replication_id, 
		    @st_db_guid
		FROM inserted;
	END
  

END;
GO

ALTER TABLE [SSCommon].[STContractors] ENABLE TRIGGER [Contractors_TH_IOU]
GO

CREATE OR ALTER TRIGGER [SSCommon].[Contractors_TH_IOI] 
   ON [SSCommon].[STContractors] 
   INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @CI                varchar(128)     = context_info();
    DECLARE @st_modified_by    uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_replication_id uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_state          int              = 2;
    DECLARE @st_server         varchar(100)     = RTRIM(CONVERT(char(100), SERVERPROPERTY('servername')));
    DECLARE @st_last_modified  datetime         = GETDATE();
    DECLARE @ID_INS            char(1)          = '0';
    DECLARE @is_mirror_db_guid char(1)          = '0';
    DECLARE @skip_history      char(1)          = '0';
    DECLARE @st_db_guid        uniqueidentifier = '00000000-0000-0000-0000-000000000000';    
    DECLARE @endpos            int              = CHARINDEX('*', @CI);
    
    if (@endpos > 0)
    BEGIN
	    DECLARE @divpos1 int = CHARINDEX('@', @CI);
	    DECLARE @divpos2 int = CHARINDEX('$', @CI);
	    DECLARE @divpos4 int = CHARINDEX('^', @CI);
	    DECLARE @divpos5 int = CHARINDEX('&', @CI);
	    DECLARE @divpos6 int = CHARINDEX('|', @CI);
	    DECLARE @divpos7 int = CHARINDEX('#', @CI);	    	    

        SET @st_modified_by    = SUBSTRING(@CI, 1,            @divpos1 - 1);
	    SET @st_replication_id = SUBSTRING(@CI, @divpos1 + 1, @divpos2 - @divpos1 - 1);
	    SET @ID_INS            = SUBSTRING(@CI, @divpos4 + 1, @divpos5 - @divpos4 - 1);
	    SET @is_mirror_db_guid = SUBSTRING(@CI, @divpos6 + 1, @divpos7 - @divpos6 - 1);
   	    SET @skip_history =		 SUBSTRING(@CI, @divpos7+1,   LEN(@CI) - @divpos7);
	    
	    IF @is_mirror_db_guid = '1'
	    BEGIN			
			SET @st_db_guid = @st_replication_id;
			SET @st_replication_id = '00000000-0000-0000-0000-000000000000';
		END
    END;

 	IF(@skip_history <> '1')
	BEGIN
		INSERT INTO SSCommon.[STContractorsRep]
		([Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata, st_state, st_modified_by, st_host, st_client, st_server, st_replication_id, st_db_guid)
		SELECT
			[Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
			@st_last_modified,
			NULL,
			@st_state,
			@st_modified_by,
			HOST_NAME(),
			APP_NAME(),
			@st_server,
			@st_replication_id, 
			@st_db_guid
		FROM inserted;
	END
	
	SET NOCOUNT OFF;
    
    IF @ID_INS = '1'   
		INSERT INTO SSCommon.[STContractors] 
		    ([Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata) 
			SELECT [Id], [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], @st_last_modified, NULL
			FROM inserted;
	ELSE
		INSERT INTO SSCommon.[STContractors] 
		([Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], st_last_modified, st_shadowdata)
			SELECT [Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew], @st_last_modified, NULL
			FROM inserted;

	SET NOCOUNT ON;

	

END;
GO

ALTER TABLE [SSCommon].[STContractors] ENABLE TRIGGER [Contractors_TH_IOI]
GO


CREATE OR ALTER TRIGGER [SSCommon].[Contractors_TH_AD] 
   ON [SSCommon].[STContractors] 
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

    DECLARE @CI                varchar(128)     = context_info();
    DECLARE @st_modified_by    uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_replication_id uniqueidentifier = '00000000-0000-0000-0000-000000000000';
    DECLARE @st_state          int              = 1;
    DECLARE @st_server         varchar(100)     = RTRIM(CONVERT(char(100), SERVERPROPERTY('servername')));
    DECLARE @st_last_modified  datetime         = GETDATE();
    DECLARE @is_mirror_db_guid char(1)          = '0'
    DECLARE @skip_history      char(1)          = '0'       
    DECLARE @st_db_guid        uniqueidentifier = '00000000-0000-0000-0000-000000000000';    
    DECLARE @endpos            int               = CHARINDEX('*', @CI);
    
    if (@endpos > 0)
    BEGIN
	    DECLARE @divpos1 int = CHARINDEX('@', @CI);
	    DECLARE @divpos2 int = CHARINDEX('$', @CI);
	    DECLARE @divpos6 int = CHARINDEX('|', @CI);
	    DECLARE @divpos7 int = CHARINDEX('#', @CI);	    	    

        SET @st_modified_by    = SUBSTRING(@CI, 1,            @divpos1 - 1);
	    SET @st_replication_id = SUBSTRING(@CI, @divpos1 + 1, @divpos2 - @divpos1 - 1);
	    SET @is_mirror_db_guid = SUBSTRING(@CI, @divpos6 + 1, @divpos7 - @divpos6 - 1);
		SET @skip_history =		 SUBSTRING(@CI, @divpos7+1,   LEN(@CI) - @divpos7);

	    IF @is_mirror_db_guid = '1'
	    BEGIN			
			SET @st_db_guid = @st_replication_id;
			SET @st_replication_id = '00000000-0000-0000-0000-000000000000';
		END
    END;

	IF(@skip_history = '1')
		RETURN

	DECLARE @oldrecid int;
	DECLARE @Id int;
	DECLARE deletedrows CURSOR LOCAL FAST_FORWARD FOR  		
		SELECT DISTINCT MAX(c.st_seq_id) as st_seq_id, d.[Id] 
		FROM SSCommon.[STContractorsRep] c
		join deleted d ON c.Guid = d.Guid
		GROUP BY d.[Id] ;

	OPEN deletedrows;
	FETCH NEXT FROM deletedrows INTO @oldrecid, @Id;

	WHILE @@FETCH_STATUS = 0
	BEGIN
			
		INSERT INTO SSCommon.[STContractorsRep]
		(
			[Id], 
			[Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
			st_last_modified,
			st_shadowdata,
			st_state,
			st_modified_by,
			st_host,
			st_client,
			st_server,
			st_replication_id, 
			st_db_guid
		)
		SELECT 
			@Id,
			[Guid], [Shortcut], [Name], [NaturalPerson], [NIP], [PESEL], [REGON], [NIP_UE], [Note], [ContactGuid], [BankingInfoGuid], [LimitFlag], [LimitAmount], [LimitCurrency], [VIES], [Negotiation], [VATPayerActive], [LinkedUnit], [MainElement], [MainPerson], [VATGroup], [JST], [IDWew],
			st_last_modified,
			st_shadowdata,
			st_state,
			st_modified_by,
			st_host,
			st_client,
			st_server,
			st_replication_id, 
			st_db_guid
		FROM SSCommon.[STContractorsRep] WHERE st_seq_id = @oldrecid;

		DECLARE @newrecid int;
		SET @newrecid = SCOPE_IDENTITY();

		UPDATE SSCommon.[STContractorsRep]  
		SET
			st_last_modified = @st_last_modified,
			st_state = @st_state,
			st_modified_by = @st_modified_by,
			st_host = HOST_NAME(),
			st_client = APP_NAME(),
			st_server = @st_server,
			st_replication_id = @st_replication_id,
			st_db_guid = @st_db_guid
		WHERE st_seq_id = @newrecid;

		FETCH NEXT FROM deletedrows INTO @oldrecid, @Id;
	END;

	CLOSE deletedrows;
	DEALLOCATE deletedrows;    

END;
GO

ALTER TABLE [SSCommon].[STContractors] ENABLE TRIGGER [Contractors_TH_AD]
GO


-- Procedura umożliwiająca pobranie danych kontrahenta w postaci XML
CREATE OR ALTER PROCEDURE [Common].[ReadContractorAsXML] (@contractorId INT, @moduleCode NVARCHAR(255), @userid int)
AS
BEGIN
	BEGIN TRY
		DECLARE @RightsKHElementID INT;
		SELECT @RightsKHElementID=ElementKindId FROM SSCommon.STDictionaries WHERE guid='DEBA7687-E550-4CBC-9FE9-8175984F1AD4'

		DECLARE @classificationTableGuid UNIQUEIDENTIFIER
		DECLARE @classificationTableName NVARCHAR(255)

		SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
		FROM [SSCommon].[STClasifications] 
		WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = @moduleCode

		IF @classificationTableName IS NULL -- jeżeli nie pobrano na podstawie kodu modułu to ustaw nazwę wspólnej tabeli kontrahentów
		BEGIN
			IF @moduleCode = 'FKF'
			BEGIN
				-- dla FKF ta sama klasyfikacja kontrahentów co dla HMF
				SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
				FROM [SSCommon].[STClasifications] 
				WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = 'HMF'
			END
			ELSE
				SET @classificationTableName = 'STContractorsClassification'
		END

		CREATE TABLE #customFieldsDefinitions(
					[ColumnName] NVARCHAR(128) COLLATE database_default,
					[Symbol] NVARCHAR(128) COLLATE database_default,
					[Type] NVARCHAR(250) COLLATE database_default
					)

		INSERT INTO #customFieldsDefinitions
		SELECT
			[Common].[GetNormalizedColumnNameForCustomField](dim.[Symbol]),
			[Common].[GetNormalizedSymbolForCustomField](dim.[Symbol]),
			dim.[DimCLRType]
		from SSCommon.STClasifications clas
		INNER JOIN [SSCommon].[STClasificationDims] clasDim ON clasDim.[ClasificationsDefId] = clas.[ClasificationsDefId]
		INNER JOIN [SSCommon].[STDimensions] dim ON dim.[Id] = clasDim.[DimensionsId]
		where clas.[Guid] like @classificationTableGuid

		DECLARE @valueStatement NVARCHAR(MAX)
		CREATE TABLE #customFields(xmlData XML)
		IF EXISTS(SELECT * FROM #customFieldsDefinitions)
		BEGIN
			SET @valueStatement = (SELECT (CASE WHEN (cf.[Type] = 'System.Boolean, mscorlib')
				THEN (SELECT CONCAT('(CASE ', cf.[ColumnName],' WHEN 0 THEN ''false'' WHEN 1 THEN ''true'' ELSE NULL END) AS [' + cf.[Symbol] + '],'))
				ELSE (SELECT CONCAT('[', CAST(cf.[ColumnName] AS NVARCHAR(128)) + '] AS [' + cf.[Symbol] + '],'))
				END)
			FROM #customFieldsDefinitions cf
			FOR XML PATH(''), ELEMENTS XSINIL)
			SET @valueStatement = CONCAT('SELECT ', STUFF(@valueStatement, LEN(@valueStatement), 1, ''),' INTO #customFields FROM [SSCommon].[',@classificationTableName,'] WHERE [ElementId] = ',@contractorId,'; (SELECT * FROM #customFields) FOR XML PATH(''''), ELEMENTS XSINIL, TYPE')
			
			INSERT INTO #customFields
			EXECUTE (@valueStatement)
		END
		
		IF (OBJECT_ID('tempdb..#customFieldsDefinitions') IS NOT NULL) DROP TABLE #customFieldsDefinitions

		DECLARE @isUsedOnDocuments INT		
		exec @isUsedOnDocuments = HM.hm_Validate_KH_Delete @contractorId

		-- Prawo do wszystkich kontaktów
		DECLARE @cmf1000 bit = iif(EXISTS(SELECT TOP 1 Id
			FROM SSCommon.STEffPerm perm
			JOIN SSCommon.STEKOperations oper ON oper.OperationId = perm.OperId AND oper.Shortcut = 'CMF1000'
			WHERE UserId IS NOT NULL AND UserId = @userid), 1, 0);
		
        DECLARE @contractorNip NVARCHAR(20) = (
            SELECT [Common].ClearNonNumericCharacters([NIP]) 
            FROM [SSCommon].[STContractors] 
            WHERE [Id] = @contractorId);
		DECLARE @wlCheckStatus NVARCHAR(16);
		DECLARE @wlValidAt DATETIME2;
		SELECT TOP(1) @wlCheckStatus = [CheckStatus], @wlValidAt=Convert(date, [ValidAt]) 
			FROM [Common].[TaxPayersWLChecks] 
            WHERE [ContractorsNip] = @contractorNip OR [ContractorsNip] IS NULL AND [ContractorId] = @contractorId 
            ORDER BY [Id] DESC;

		DECLARE @resultXml XML = 
		(
			SELECT
				ctr.[Id] AS [Id],
				ctr.[Guid] AS [Guid],
				(CASE WHEN ctr.[NaturalPerson] = 1 THEN 'Osoba fizyczna (firma)' ELSE (CASE WHEN ctr.[NaturalPerson] = 2 THEN 'Osoba fizyczna' ELSE 'Firma' END) END) AS [Subject],
				ctr.[Name] AS [Name],
				ctr.[Shortcut] AS [Shortcut],
				IIF( @isUsedOnDocuments != 0,'true','false') AS [IsUsedOnDocuments],
				(
				SELECT
					--Dla firmy nie uzupełniamy imienia i nazwiska - będą one przy osobie kontaktowej
					CASE WHEN ctr.[MainPerson] IS NULL OR ctr.[NaturalPerson] = 0 THEN '' ELSE p.[FirstName] END AS [FirstName],
					CASE WHEN ctr.[MainPerson] IS NULL OR ctr.[NaturalPerson] = 0 THEN '' ELSE p.[Surname] END AS [SurName]
				FROM [SSCommon].[STContractors] ctr1
				LEFT OUTER JOIN [SSCommon].[STPersons] p ON ctr1.[MainPerson] = p.[Guid]
				WHERE ctr.[Id] = ctr1.[Id]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				),
				ctr.[NIP] AS [Nip],
				ctr.[NIP_UE] AS [NipUE],
				ctr.[PESEL] AS [Pesel],
				ctr.[REGON] AS [Regon],
                ISNULL(ctr.[IDWew], '') AS [IDWew],
				ctr.[Note] AS [Note],
				CASE WHEN ctr.[VIES] = 0 THEN 'false' ELSE 'true' END AS [Vies],
				CASE WHEN ctr.[VATPayerActive] = 0 THEN 'false' ELSE 'true' END  AS [VatPayerActive],
				ISNULL(@wlCheckStatus,'') AS [WLCheckStatus],
				ISNULL(CONVERT(VARCHAR(10), @wlValidAt),'') AS [WLValidAt],
				CASE WHEN ctr.[LinkedUnit] = 0 THEN 'false' ELSE 'true' END AS [LinkedUnit],
				CASE WHEN ctr.[VATGroup] = 0 THEN 'false' ELSE 'true' END AS [VatGroup],
				CASE WHEN ctr.[JST] = 0 THEN 'false' ELSE 'true' END AS [Jst],
				(
				SELECT
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Id] END AS [Id],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE CAST(aMain.[Guid] AS NVARCHAR(36)) END AS [Guid],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[AddressName] END AS [Name],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[PostOffice] END AS [Post],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[PostCode] END AS [PostalCode],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Place] END AS [Place],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Street] END AS [Street],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[HouseNo] END AS [HouseNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[ApartmentNo] END AS [ApartmentNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Province] END AS [Province],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aMain.[Country] END AS [Country]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				) AS [MainAddress],
				(
				SELECT
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Id] END AS [Id],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE CAST(aCorsp.[Guid] AS NVARCHAR(36)) END AS [Guid],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[AddressName] END AS [Name],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[PostOffice] END AS [Post],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[PostCode] END AS [PostalCode],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Place] END AS [Place],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Street] END AS [Street],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[HouseNo] END AS [HouseNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[ApartmentNo] END AS [ApartmentNo],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Province] END AS [Province],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE aCorsp.[Country] END AS [Country]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				) AS [CorespondenceAddress],
				(
					ISNULL((SELECT
						acc.[Id] AS [Id],
						acc.[Guid] AS [Guid],
						acc.[AccountName] AS [AccountName],
						acc.[AccountNo] AS [AccountNo],
						acc.[BankName] AS [BankName],
						acc.[SWIFT_BIC] AS [SwiftBicNumber],
						CASE WHEN acc.[Active] = 1 THEN 'true' ELSE 'false' END as [IsActive],
						CASE WHEN acc.[Guid] = bi.[MainAccount] THEN 'true' ELSE 'false' END as [IsDefault],
						CASE WHEN acc.[ElementId] IS NULL THEN 'False' ELSE (SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] WHERE [ElementId] = acc.[ElementId] AND [Name] LIKE 'Rachunek VAT') END AS [IsVatAccount],
						CASE WHEN acc.[ElementId] IS NULL THEN 'False' ELSE (SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] WHERE [ElementId] = acc.[ElementId] AND [Name] LIKE 'Elektroniczny') END AS [IsElectronic],
						CASE WHEN acc.[WhiteList] IS NULL THEN '' ELSE (CASE WHEN acc.[WhiteList] = 1 THEN 'true' ELSE 'false' END) END as [IsInTaxPayerRegistry],
						CASE WHEN acc.[WhiteListDate] IS NULL THEN 'None' 
							ELSE (CASE WHEN acc.[WhiteList] = 1 THEN 
									(CASE WHEN DATEDIFF(dd, cast(acc.WhiteListDate as DATE), cast(GetDate() as date)) = 0 THEN 'Valid' ELSE 'ValidOverdue' END) 
								  ELSE 'Invalid' END) END as [IsInTaxPayerRegistryStatus],
						acc.[WhiteListDate] as [VerificationDate]
					FROM [SSCommon].[STBankingInfos] bi
					INNER JOIN [SSCommon].[STAccounts] acc ON acc.[BankingInfosGuid] = bi.[Guid]
					WHERE bi.[Guid] = ctr.[BankingInfoGuid]
					ORDER BY acc.[Id]
					FOR XML PATH('BankAccount'), ELEMENTS XSINIL, TYPE), '')
				) AS [BankAccounts],
				(
				SELECT
					CASE WHEN ctr.[MainPerson] IS NULL THEN '' ELSE p.[FirstName] END AS [FirstName],
					CASE WHEN ctr.[MainPerson] IS NULL THEN '' ELSE p.[Surname] END AS [SurName],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Facebook] END AS [Facebook],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Fax] END AS [Fax],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Telephone1] END AS [Telephone1],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Telephone2] END AS [Telephone2],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Telex] END AS [Tlf],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[WWW] END AS [Website],
					CASE WHEN ctr.[ContactGuid] IS NULL THEN '' ELSE t.[Email1] END AS [Email]
				FROM [SSCommon].[STContractors] ctr1
				LEFT OUTER JOIN [SSCommon].[STPersons] p ON ctr1.[MainPerson] = p.[Guid]
				LEFT OUTER JOIN [SSCommon].[STContacts] c ON ctr1.[ContactGuid] = c.[Guid]
				LEFT OUTER JOIN [SSCommon].[STTeleItContacts] t ON c.[MainTeleITContact] = t.[Guid]
				WHERE ctr.[Guid] = ctr1.[Guid] AND ctr.[Id] = @contractorId
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				) AS [ContactPerson],
				CASE WHEN ctr.[LimitFlag] = 0 THEN 'false' ELSE 'true' END AS [IsLimitActive],
				ctr.[LimitCurrency] AS [LimitCurrency],
				CAST(ctr.[LimitAmount] AS MONEY) AS [LimitValue],
				CASE WHEN ctr.[Negotiation] = 0 THEN 'false' ELSE 'true' END AS [PriceNegotiation],
				(
					SELECT
						CASE WHEN elem.[Active] = 0 THEN 'Nieaktywny' ELSE 'Aktywny' END AS [Status], -- 0 - nieaktywny, 1 - aktywny
						ISNULL((SELECT CASE WHEN [StringValue] = 2 THEN 'Wymagaj' ELSE (CASE WHEN [StringValue] = 1 THEN 'Blokada' ELSE '' END) END FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'splitPayment'), '') AS [RequestMppTransfers],
						(SELECT (CASE WHEN [StringValue] = '' THEN NULL ELSE [StringValue] END) FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'idFK') AS [IdFk],
						CASE WHEN [Position] <= 0 THEN 0 ELSE [Position] END AS [PositionFk],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'parametrFK'), '') AS [AccountantParameter],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'typCeny'), '') AS [PriceTypeId],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'rodzajCeny'), '') AS [SaleKindId],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'rejestr'), '') AS [PayRegistryId],
						ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'formaPlatnosci'), '') AS [PaymentFormId],
						ISNULL((SELECT [long] FROM [HM].[PaymentForm] pf WHERE pf.id = ISNULL((SELECT [StringValue] FROM [SSCommon].[STElementExAttributes] ex WHERE ex.[ElementId] = elem.[ElementId] AND ex.[Name] LIKE 'formaPlatnosci'), '')), 0) AS [PaymentFormDeadline]
					FROM [SSCommon].[STElements] elem
					WHERE ctr.[MainElement] = elem.[Guid] AND ctr.[Id] = @contractorId
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE
				),
				(SELECT (ISNULL((SELECT elem.[ElementId] FROM [SSCommon].[STContractorsClassification] cmnCtrClas
					INNER JOIN [SSCommon].[STElements] elem ON elem.[Guid] = cmnCtrClas.[CDim_ZnacznikKh]
					WHERE cmnCtrClas.[ElementId] = ctr.[Guid]), ''))
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [MarkerId],
				(SELECT
					(SELECT ex.StringValue FROM [SSCommon].[STElementExAttributes] ex
					INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.ElementId AND ex.[Name] = 'znacznik'
					WHERE elem.[Guid] = ctr.[MainElement] )
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [FkMarkerId],
				(SELECT (ISNULL((SELECT elem.[Shortcut] FROM [SSCommon].[STContractorsClassification] cmnCtrClas
					INNER JOIN [SSCommon].[STElements] elem ON elem.[Guid] = cmnCtrClas.[CDim_KatalogKh]
					WHERE cmnCtrClas.[ElementId] = ctr.[Guid]), ''))
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [CatalogId],
				(SELECT (ISNULL((SELECT elem.[ElementId] FROM [SSCommon].[STContractorsClassification] cmnCtrClas
					INNER JOIN [SSCommon].[STElements] elem ON elem.[Guid] = cmnCtrClas.[CDim_RodzajKh]
					WHERE cmnCtrClas.[ElementId] = ctr.[Guid]), ''))
					FOR XML PATH(''), ELEMENTS XSINIL, TYPE) as [ContractorKindId],
		-- dane handlowe
				(SELECT(CAST(ISNULL((SELECT [Discount] FROM [HM].[ContractorDiscounts] WHERE [ContractorId] = ctr.[Id]), 0)AS MONEY)) FOR XML PATH(''), ELEMENTS XSINIL, TYPE) AS [DiscountPercent],
				(SELECT
					-CAST([Receivables] AS MONEY) AS [Receivables],
					-CAST([Liabilities] AS MONEY) AS [Liabilities]
				FROM [HM].[ContractorLiabilitiesAndReceivables]
				WHERE [ContractorId] = ctr.[Id]
				FOR XML PATH(''), ELEMENTS XSINIL, TYPE),
				
		--role kontrahenta
				(
			
				SELECT 
				CASE isnull(grupy.ContractorId,0)
				WHEN 0 THEN 'false' 
				ELSE 'true' END AS HasRight,
				elementy.Shortcut AS Name, elementy.ElementId
				FROM  SSCommon.STUserRightsGroups urg 
				INNER JOIN SSCommon.STElements elementy ON elementy.ElementId=urg.ContractorRightGroup
				LEFT JOIN SSCommon.STContractorsRightsGroups grupy ON grupy.ContractorRightGroup = elementy.ElementId AND grupy.ContractorId=@contractorId
				WHERE elementy.ElementKindId=@RightsKHElementID AND urg.UserId=@userid
				FOR XML PATH('ContractorsGroupPermission'),ELEMENTS XSINIL, TYPE
				) AS [ContractorsGroupPermissions],
	
		-- kontakty
				ISNULL((
					SELECT cp.[Id], [FirstName], [SurName], [Role],
					ISNULL(cp_phone.[Content], '') AS [Phone],
					ISNULL(cp_email.[Content], '') AS [Email],
					[Tags],
					ISNULL(cp_email.[DocumentTypes], '') AS [DocumentTypes],
					[IsActive]
					FROM [Common].[ContactPersons] cp
					LEFT JOIN [Common].[ContactDetails] cp_phone ON (cp.[Id] = cp_phone.[ContactId] AND cp_phone.[IsDefault] = 1 AND cp_phone.[Type] IN (1, 2)) -- ConnectionType.BusinessPhone, ConnectionType.PrivatePhone
					LEFT JOIN [Common].[ContactDetails] cp_email ON (cp.[Id] = cp_email.[ContactId] AND cp_email.[IsDefault] = 1 AND cp_email.[Type] = 3) -- ConnectionType.Email
					WHERE [ContractorId] = ctr.[Id] AND @userid = IIF(@cmf1000 = 1, @userid, cp.[SupervisorId])
					FOR XML PATH('ContractorContactPerson'), ELEMENTS XSINIL, TYPE
				), '') AS [ContactPersonList],

		-- pola własne
				(SELECT * FROM #customFields)				
			FROM [SSCommon].[STContractors] ctr
			LEFT OUTER JOIN [SSCommon].[STContacts] c ON ctr.[ContactGuid] = c.[Guid]
			LEFT OUTER JOIN [SSCommon].[STPostOfficeAddresses] aMain ON c.[MainPostOfficeAddress] = aMain.[Guid]
			LEFT OUTER JOIN [SSCommon].[STPostOfficeAddresses] aCorsp ON c.[Guid] = aCorsp.[ContactGuid] AND aCorsp.[AddressName] LIKE 'adres korespondencyjny'
			WHERE ctr.[Id] = @contractorId
			FOR XML PATH(''), ROOT('Contractor')
		)
		IF (OBJECT_ID('tempdb..#customFields') IS NOT NULL) DROP TABLE #customFields

		EXEC [Common].[ReadContractorRIAsXML] @contractorId, @resultXml, @moduleCode
	END TRY
	BEGIN CATCH
		THROW;
	END CATCH
END

GO

-- Procedura umożliwiająca zapisanie danych kontrahenta z postaci XML
CREATE OR ALTER PROCEDURE [Common].[UpdateContractorFromXML] (@contractorData XML, @moduleCode NVARCHAR(255), @contextInfo VARCHAR(128), @contextInfoP VARCHAR(128), @contextInfoA VARCHAR(128), @userid int)
AS
BEGIN
	SET NOCOUNT ON; -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
	SET XACT_ABORT ON; -- Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

	--Ustawienie CONTEXT_INFO z danymi potrzebnymi trigerom do zapisu historii zmian w tabelach z sufiksem 'Rep'
	declare @ContextInfoBinary as varbinary(128)
	SET @ContextInfoBinary = convert(varbinary(128), @contextInfo)
	SET CONTEXT_INFO @ContextInfoBinary;
	declare @ContextInfoBinaryP as varbinary(128)
	SET @ContextInfoBinaryP = convert(varbinary(128), @contextInfoP)
	declare @ContextInfoBinaryA as varbinary(128)
	SET @ContextInfoBinaryA = convert(varbinary(128), @contextInfoA)

	BEGIN TRY
		BEGIN TRANSACTION;
		
		-- tabela tymczasowa na dane kontrahenta z xml
		CREATE TABLE [#tblContractor]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(150) COLLATE database_default,
			[Shortcut] NVARCHAR(50) COLLATE database_default,
			[Nip] NVARCHAR(20) COLLATE database_default,
			[NipUE] NVARCHAR(20) COLLATE database_default,
			[Pesel] NVARCHAR(15) COLLATE database_default,
			[Regon] NVARCHAR(20) COLLATE database_default,
            [IDWew] NVARCHAR(5) COLLATE database_default,
			[LinkedUnit] BIT,
			[VatGroup] BIT,
			[Jst] BIT,
			[PersonKind] TINYINT,
			[VATPayerActive] BIT,
			[VIES] BIT,
				-- exAttribute
			[RequestMppTransfers] NVARCHAR(10) COLLATE database_default, -- splitPayment
			[PaymentFormId] INT, -- formaPlatnosci
			[PayRegistryId] INT, -- rejestr
			[SaleKindId] INT, -- rodzajCeny
			[PriceTypeId] INT, -- typCeny
			[AccountantParameter] NVARCHAR(40) COLLATE database_default, -- parametrFK
			[IdFk] NVARCHAR(1024), -- idFK
			[PositionFk] INT, -- pozycja FK
			-- additional data
			[CatalogId] NVARCHAR(100) COLLATE database_default,
			[ContractorKindId] INT,
			[MarkerId] INT,
			[FkMarkerId] INT,
			[Status] BIT,
			[Note] NVARCHAR(max) COLLATE database_default,
			-- sale conditions
			[IsLimitActive] BIT,
			[LimitCurrency] NVARCHAR(20) COLLATE database_default,
			[LimitValue] FLOAT,
			[PriceNegotiation] BIT,
			-- main person
			[FirstName] NVARCHAR(50) COLLATE database_default,
			[Surname] NVARCHAR(50) COLLATE database_default
		)
		-- tabela tymczasowa na dane kontaktowe kontrahenta z xml
		CREATE TABLE [#tblContact]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[FirstName] NVARCHAR(50) COLLATE database_default,
			[Surname] NVARCHAR(50) COLLATE database_default,
			[Email] NVARCHAR(100) COLLATE database_default,
			[Facebook] NVARCHAR(100) COLLATE database_default,
			[Fax] NVARCHAR(50) COLLATE database_default,
			[Telephone1] NVARCHAR(50) COLLATE database_default,
			[Telephone2] NVARCHAR(50) COLLATE database_default,
			[Telex] NVARCHAR(50) COLLATE database_default,
			[Website] NVARCHAR(100) COLLATE database_default
		)
		-- tabela tymczasowa na dane adresu domyślnego kontrahenta z xml
		CREATE TABLE [#tblMainAddress]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(100) COLLATE database_default,
			[Post] NVARCHAR(50) COLLATE database_default,
			[PostalCode] NVARCHAR(10) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[Street] NVARCHAR(50) COLLATE database_default,
			[HouseNo] NVARCHAR(15) COLLATE database_default,
			[ApartmentNo] NVARCHAR(15) COLLATE database_default,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default
		)
		-- tabela tymczasowa na dane adresu korespondencyjnego kontrahenta z xml
		CREATE TABLE [#tblCorespondenceAddress]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(100) COLLATE database_default,
			[Post] NVARCHAR(50) COLLATE database_default,
			[PostalCode] NVARCHAR(10) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[Street] NVARCHAR(50) COLLATE database_default,
			[HouseNo] NVARCHAR(15) COLLATE database_default,
			[ApartmentNo] NVARCHAR(15) COLLATE database_default,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default
		)
		-- tabela tymczasowa na dane handlowe kontrahenta z xml
		CREATE TABLE [#tblTradeData]
		(
			[DiscountPercent] FLOAT,
			[Receivables] FLOAT,
			[Liabilities] FLOAT
		)
		-- tabela tymczasowa na pola z rachunkami bankowymi
		CREATE TABLE [#tblBankAccounts]
		(
			[Id] INT,
			[Guid] UNIQUEIDENTIFIER,
			[AccountNo] NVARCHAR(50) COLLATE database_default,
			[AccountName] NVARCHAR(100) COLLATE database_default,
			[IsDefault] BIT,
			[IsActive] BIT,
			[WhiteList] BIT,
			[WhiteListDate] DATETIME2,
			[SWIFT_BIC] VARCHAR(11) COLLATE database_default,
			[BankName] NVARCHAR(100) COLLATE database_default,
			-- extended attributes
			[IsElectronic] BIT,
			[IsVatAccount] BIT
		)
		-- tabela tymczasowa na pola z rachunkami bankowymi
		CREATE TABLE [#tblCustomFields]
		(
			[FieldName] NVARCHAR(100) COLLATE database_default,
			[Value] NVARCHAR(MAX) COLLATE database_default,
			[ColumnName] NVARCHAR(128) COLLATE database_default,
			[Type] NVARCHAR(250) COLLATE database_default
		)
		
		-- tabela tymczasowa na ElementExAttributes
		CREATE TABLE [#ExtAttributes]
		(
			[ElementId] INT,
			[Name] NVARCHAR(50) COLLATE database_default,
			[StringValue] NVARCHAR(1024) COLLATE database_default,
			[OriginalCLRType] NVARCHAR(400) COLLATE database_default,
			[ElementExAttrId] INT IDENTITY(1,1),
			[Guid] UNIQUEIDENTIFIER,
			[Title] NVARCHAR(200) COLLATE database_default,
			[AccessFlag] BIT,
			[InheritedFlag] BIT
		)
		
		--tabela tymczasowa na grupy kontrahentów
		CREATE TABLE [#ContractorsGroupPermissions]
		(
			[HasRight] BIT,
			[ElementId] INT,
			[Name] NVARCHAR(100) COLLATE database_default
		)

		DECLARE @extAttElementId int
		DECLARE @extAttTitle nvarchar (200)
		DECLARE @extAttActive bit
		DECLARE @extAttAccountNo nvarchar (40)
		DECLARE @extAttDescription nvarchar (4000)
		DECLARE @extAttShortcut nvarchar (100)
		DECLARE @extAttPosition int
		DECLARE @extAttExtAttributes nvarchar(max)
		DECLARE @extAttExtObjVer int
		DECLARE @extAttExtWriteAction int
		DECLARE @extAttExtOtherThenOriginal smallint

		-- przygotowanie xml
		DECLARE @handle INT, @prepareXmlStatus INT
		EXEC @prepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @contractorData

		-- deserializacja danych kontaktowych z xml
		INSERT INTO [#tblContact]
		SELECT 
			ISNULL([Id], 0), CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
			[FirstName], [SurName], [Email], [Facebook], [Fax], [Telephone1], [Telephone2], [Telex], [Website]
		FROM 
			OPENXML(@handle, '/Contractor/ContactPerson')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[FirstName] NVARCHAR(50) 'FirstName',
				[Surname] NVARCHAR(50) 'SurName',
				[Email] NVARCHAR(100) 'Email',
				[Facebook] NVARCHAR(100) 'Facebook',
				[Fax] NVARCHAR(50) 'Fax',
				[Telephone1] NVARCHAR(50) 'Telephone1',
				[Telephone2] NVARCHAR(50) 'Telephone2',
				[Telex] NVARCHAR(50) 'Tlf',
				[Website] NVARCHAR(100) 'Website'
			)
		-- wyczyszczenie pustych elementów
		DELETE FROM [#tblContact]
		WHERE [FirstName] = '' AND [Surname] = '' AND [Email] = '' AND [Facebook] = '' AND [Fax] = '' AND [Telephone1] = '' AND [Telephone2] = '' AND [Telex] = '' AND [Website] = ''
			AND [Guid] = '00000000-0000-0000-0000-000000000000' --dla nowych kontaktów, dla istniejących chcemy wyczyścić pola w bazie
		--select * from #tblContact

		-- deserializacja danych adresu domyślnego z xml
		INSERT INTO [#tblMainAddress]
		SELECT 
			ISNULL([Id], 0),
			CASE WHEN [Guid] IS NULL OR [Guid] = '' THEN '00000000-0000-0000-0000-000000000000' ELSE [GUID] END,
			[Name], [Post], [PostalCode], [Place], [Street], [HouseNo], [ApartmentNo], [Province], [Country]
		FROM
			OPENXML(@handle, '/Contractor/MainAddress')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[Name] NVARCHAR(100) 'Name',
				[Post] NVARCHAR(50) 'Post',
				[PostalCode] NVARCHAR(10) 'PostalCode',
				[Place] NVARCHAR(50) 'Place',
				[Street] NVARCHAR(50) 'Street',
				[HouseNo] NVARCHAR(15) 'HouseNo',
				[ApartmentNo] NVARCHAR(15) 'ApartmentNo',
				[Province] NVARCHAR(50) 'Province',
				[Country] NVARCHAR(50) 'Country'
			)
		-- wyczyszczenie pustych elementów
		DELETE FROM [#tblMainAddress]
		WHERE [Name] = '' AND [Post] = '' AND [PostalCode] = '' AND [Place] = '' AND [Street] = '' AND [HouseNo] = '' AND [ApartmentNo] = '' AND [Province] = '' AND [Country] = ''
		--select * from [#tblMainAddress]
		
		-- deserializacja danych adresu korespondencyjnego z xml
		INSERT INTO [#tblCorespondenceAddress]
		SELECT 
			ISNULL([Id], 0), CASE WHEN [Guid] IS NULL OR [Guid] = '' THEN '00000000-0000-0000-0000-000000000000' ELSE [GUID] END,
			[Name], [Post], [PostalCode], [Place], [Street], [HouseNo], [ApartmentNo], [Province], [Country]
		FROM 
			OPENXML(@handle, '/Contractor/CorespondenceAddress')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[Name] NVARCHAR(100) 'Name',
				[Post] NVARCHAR(50) 'Post',
				[PostalCode] NVARCHAR(10) 'PostalCode',
				[Place] NVARCHAR(50) 'Place',
				[Street] NVARCHAR(50) 'Street',
				[HouseNo] NVARCHAR(15) 'HouseNo',
				[ApartmentNo] NVARCHAR(15) 'ApartmentNo',
				[Province] NVARCHAR(50) 'Province',
				[Country] NVARCHAR(50) 'Country'
			)
		-- wyczyszczenie pustych elementów
		DELETE FROM [#tblCorespondenceAddress]
		WHERE [Name] = '' AND [Post] = '' AND [PostalCode] = '' AND [Place] = '' AND [Street] = '' AND [HouseNo] = '' AND [ApartmentNo] = '' AND [Province] = '' AND [Country] = ''
		--select * from [#tblCorespondenceAddress]
		
		-- deserializacja danych handlowych z xml
		INSERT INTO [#tblTradeData]
		SELECT 
			[DiscountPercent], [Receivables], [Liabilities]
		FROM 
			OPENXML(@handle, '/Contractor')
			WITH
			(
				[DiscountPercent] FLOAT 'DiscountPercent',
				[Receivables] FLOAT 'Receivables',
				[Liabilities] FLOAT 'Liabilities'
			)
		DELETE FROM [#tblTradeData]
		WHERE [DiscountPercent] = 0 AND [Receivables] = 0 AND [Liabilities] = 0
		--select * from [#tblTradeData]
		
		-- deserializacja danych rachunków bankowych z xml
		INSERT INTO [#tblBankAccounts]
		SELECT 
			[Id],CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
			[AccountNo], [AccountName], [IsDefault], [IsActive],
			CASE WHEN [WhiteList] IS NULL OR [WhiteList] = '' THEN NULL ELSE (CASE WHEN [WhiteList] = 'true' THEN 1 ELSE 0 END) END [WhiteList],
			CASE WHEN [WhiteListDate] IS NULL OR [WhiteListDate] = '' THEN NULL ELSE cast([WhiteListDate] as DATETIME2) END [WhiteListDate],
			[SWIFT_BIC], [BankName],
			-- extended attributes
			[IsElectronic], [IsVatAccount]
		FROM 
			OPENXML(@handle, '/Contractor/BankAccounts/BankAccount')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[AccountNo] NVARCHAR(50) 'AccountNo',
				[AccountName] NVARCHAR(100) 'AccountName',
				[IsDefault] BIT 'IsDefault',
				[IsActive] BIT 'IsActive',
				[WhiteList] NVARCHAR(5) 'IsInTaxPayerRegistry',
				[WhiteListDate] NVARCHAR(40) 'VerificationDate',
				[SWIFT_BIC] VARCHAR(11) 'SwiftBicNumber',
				[BankName] NVARCHAR(100) 'BankName',
				-- extended attributes
				[IsElectronic] BIT 'IsElectronic',
				[IsVatAccount] BIT 'IsVatAccount'
			)
			
		DELETE FROM [#tblBankAccounts]
		WHERE ([AccountNo] IS NULL OR [AccountNo] = '') AND ([AccountName] IS NULL OR [AccountName] = '') AND ([IsDefault] IS NULL OR [IsDefault] = 0) AND ([IsActive] IS NULL OR [IsActive] = 0)
			AND [WhiteList] = NULL AND [WhiteListDate] = NULL AND ([SWIFT_BIC] IS NULL OR [SWIFT_BIC] = '') AND ([BankName] IS NULL OR [BankName] = '')
			AND ([IsElectronic] IS NULL OR [IsElectronic] = 0)AND ([IsVatAccount] IS NULL OR [IsVatAccount] = 0)
		--select * from [#tblBankAccounts]

		-- deserializacja danych kontrahenta z xml
		INSERT INTO [#tblContractor]
		SELECT 
			[Id], CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
			[Name], [Shortcut], [Nip], [NipUE], [Pesel], [Regon], [IDWew], [LinkedUnit], [VatGroup], [Jst],
			(CASE WHEN [PersonKind] LIKE 'Osoba fizyczna (firma)' THEN 1 ELSE (CASE WHEN [PersonKind] = 'Osoba fizyczna' THEN 2 ELSE 0 END) END) AS [Subject],
			[VATPayerActive], [VIES],
			-- exAttribute
			[RequestMppTransfers], [PaymentFormId], [PayRegistryId], [SaleKindId], [PriceTypeId], [AccountantParameter], [IdFk], [PositionFk],
			-- additional data
			[CatalogId], [ContractorKindId], [MarkerId], [FkMarkerId], (CASE WHEN [Status] IS NULL OR [Status] LIKE '' OR [Status] LIKE 'Aktywny' THEN 1 ELSE 0 END) AS [Status], [Note],
			-- sale conditions
			[IsLimitActive], [LimitCurrency], [LimitValue], [PriceNegotiation],
			-- main person
			[FirstName], [Surname]
		FROM 
			OPENXML(@handle, '/Contractor')
			WITH
			(
				[Id] INT 'Id',
				[Guid] NVARCHAR(36) 'Guid',
				[Name] NVARCHAR(150) 'Name',
				[Shortcut] NVARCHAR(50) 'Shortcut',
				[Nip] NVARCHAR(20) 'Nip',
				[NipUE] NVARCHAR(20) 'NipUE',
				[Pesel] NVARCHAR(15) 'Pesel',
				[Regon] NVARCHAR(20) 'Regon',
                [IDWew] NVARCHAR(5) 'IDWew',
				[LinkedUnit] BIT 'LinkedUnit',
				[VatGroup] BIT 'VatGroup',
				[Jst] BIT 'Jst',
				[PersonKind] NVARCHAR(100) 'Subject',
				[VATPayerActive] BIT 'VatPayerActive',
				[VIES] BIT 'Vies',
				-- exAttribute
				[RequestMppTransfers] NVARCHAR(10) 'RequestMppTransfers', -- splitPayment
				[PaymentFormId] INT 'PaymentFormId', -- formaPlatnosci
				[PayRegistryId] INT 'PayRegistryId', -- rejestr
				[SaleKindId] INT 'SaleKindId', -- rodzajCeny
				[PriceTypeId] INT 'PriceTypeId', -- typCeny
				[AccountantParameter] NVARCHAR(40) 'AccountantParameter', -- parametrFK
				[IdFk] NVARCHAR(1024) 'IdFk', -- idFK
				[PositionFk] INT 'PositionFk', -- pozycja FK
				-- additional data
				[CatalogId] NVARCHAR(100) 'CatalogId',
				[ContractorKindId] INT 'ContractorKindId',
				[MarkerId] INT 'MarkerId',
				[FKMarkerId] INT 'FkMarkerId',
				[Status] NVARCHAR(10) 'Status',
				[Note] NVARCHAR(max) 'Note',
				-- sale conditions
				[IsLimitActive] BIT 'IsLimitActive',
				[LimitCurrency] NVARCHAR(20) 'LimitCurrency',
				[LimitValue] FLOAT 'LimitValue',
				[PriceNegotiation] BIT 'PriceNegotiation',
				-- main person
				[FirstName] NVARCHAR(50) 'FirstName',
				[Surname] NVARCHAR(50) 'SurName'
			)
		--select * from [#tblContractor]

		-- ustawienie domyślnego znacznika; wpp. bug 195961
		UPDATE [#tblContractor]
		   SET [MarkerId] = (SELECT [ElementId] FROM [SSCommon].[STElements] WHERE [Guid] = 'D205CFCC-1AF4-4E6B-919C-9AE3A2192F81')
		 WHERE 0 = ISNULL([MarkerId], 0);

		-- deserializacja pól własnych kontrahenta z xml
		DECLARE @classificationTableGuid UNIQUEIDENTIFIER
		DECLARE @classificationTableName NVARCHAR(255)

		SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
		FROM [SSCommon].[STClasifications] 
		WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = @moduleCode

		IF @classificationTableName IS NULL -- jeżeli nie pobrano na podstawie kodu modułu to ustaw nazwę wspólnej tabeli kontrahentów
		BEGIN
			IF @moduleCode = 'FKF'
			BEGIN
				-- dla FKF ta sama klasyfikacja kontrahentów co dla HMF
				SELECT @classificationTableName = ISNULL([TableNamePrefix], '') + [TableName], @classificationTableGuid = [Guid]
				FROM [SSCommon].[STClasifications] 
				WHERE [TypeTable] LIKE 'Contractors' AND [Owner] = 'HMF'
			END
			ELSE
				SET @classificationTableName = 'STContractorsClassification'
		END

		DECLARE @customFieldsDefinitions TABLE([ColumnName] NVARCHAR(128) COLLATE database_default,[Symbol] NVARCHAR(128) COLLATE database_default,[Type] NVARCHAR(250) COLLATE database_default)
		INSERT INTO @customFieldsDefinitions
		SELECT
			[Common].[GetNormalizedColumnNameForCustomField](dim.[Symbol]),
			[Common].[GetNormalizedSymbolForCustomField](dim.[Symbol]),
			dim.[DimCLRType]
		from [SSCommon].[STClasifications] clas
		INNER JOIN [SSCommon].[STClasificationDims] clasDim ON clasDim.[ClasificationsDefId] = clas.[ClasificationsDefId]
		INNER JOIN [SSCommon].[STDimensions] dim ON dim.[Id] = clasDim.[DimensionsId]
		where clas.[Guid] like @classificationTableGuid
		--SELECT * FROM @customFieldsDefinitions

		-- czy są zdefiniowane pola własne
		IF EXISTS (SELECT * FROM @customFieldsDefinitions)
		BEGIN
			SELECT * INTO #temporaryContractorData
			FROM OPENXML(@handle, '/Contractor')
			;WITH documentFields as (
				SELECT c0.[parentid] AS [Id], c0.[localname] AS [FieldName], c1.[text] AS [Value]
				FROM #temporaryContractorData c0
				INNER JOIN #temporaryContractorData c1 ON c0.[id] = c1.[parentid]
				WHERE c0.[parentid] = 0 AND c0.[nodetype] = 1 AND c1.[nodetype] = 3 -- nodetype	int	Identifies the node type. 1 = Element node, 2 = Attribute node, 3 = Text node
			)
			INSERT INTO [#tblCustomFields]
			SELECT customFieldDefinitions.[Symbol] AS [FieldName], documentFields.[Value] AS [Value], customFieldDefinitions.[ColumnName] AS [ColumnName], customFieldDefinitions.[Type] AS [Type]
			FROM documentFields
			RIGHT OUTER JOIN @customFieldsDefinitions customFieldDefinitions ON customFieldDefinitions.[Symbol] = documentFields.[FieldName]

			IF (OBJECT_ID('tempdb..#temporaryContractorData') IS NOT NULL) DROP TABLE #temporaryContractorData
			--SELECT * FROM [#tblCustomFields]
		END

		DECLARE @contractorId INT, @contractorGuid UNIQUEIDENTIFIER
		SELECT @contractorId = [Id], @contractorGuid = CASE WHEN [Guid] IS NULL OR [Guid] LIKE '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [Guid] END FROM [#tblContractor]
		DECLARE @contactId INT
		DECLARE @contactGuid UNIQUEIDENTIFIER
		SET @contactGuid = NULL
		DECLARE @bankingInfoGuid UNIQUEIDENTIFIER
		SET @bankingInfoGuid = NULL
		IF ((@contactId IS NULL OR @contactId = 0) AND @contractorId IS NOT NULL AND @contractorId <> 0)
		BEGIN
			SELECT
				@contactGuid = ctr.[ContactGuid],
				@contactId = ct.[Id],
				@bankingInfoGuid = [BankingInfoGuid]
			FROM [SSCommon].[STContractors] ctr
			INNER JOIN [SSCommon].[STContacts] ct ON ct.[Guid] = ctr.[ContactGuid]
			WHERE ctr.[Id] = @contractorId
		END

-- zapis informacji do tabeli z danymi kontaktowymi
		-- tabela tymczasowa na TeleItContacts
		CREATE TABLE [#TeleItContacts]
		(
			[Id] INT IDENTITY(1,1),
			[Guid] UNIQUEIDENTIFIER,
			[Name] NVARCHAR(50) COLLATE database_default,
			[ContactGuid] UNIQUEIDENTIFIER,
			[Telephone1] NVARCHAR(50) COLLATE database_default,
			[Telephone2] NVARCHAR(50) COLLATE database_default,
			[Telephone3] NVARCHAR(50) COLLATE database_default,
			[Fax] NVARCHAR(50) COLLATE database_default,
			[Fax2] NVARCHAR(50) COLLATE database_default,
			[Telex] NVARCHAR(50) COLLATE database_default,
			[Email1] NVARCHAR(100) COLLATE database_default,
			[Email2] NVARCHAR(100) COLLATE database_default,
			[WWW] NVARCHAR(100) COLLATE database_default,
			[Communicator1] NVARCHAR(50) COLLATE database_default,
			[Communicator2] NVARCHAR(50) COLLATE database_default,
			[Description] NVARCHAR(3000) COLLATE database_default,
			[Facebook] NVARCHAR(100) COLLATE database_default
		)

		-- tabela tymczasowa na PostOfficeAddresses
		CREATE TABLE [#PostOfficeAddresses]
		(
			[Id] INT IDENTITY(1,1),
			[AddressName] NVARCHAR(64) COLLATE database_default,
			[Guid] UNIQUEIDENTIFIER,
			[Street] NVARCHAR(50) COLLATE database_default,
			[Commune] NVARCHAR(50) COLLATE database_default,
			[District] NVARCHAR(50) COLLATE database_default,
			[Place] NVARCHAR(50) COLLATE database_default,
			[PostCode] NVARCHAR(10) COLLATE database_default,
			[PostOffice] NVARCHAR(50) COLLATE database_default,
			[ApartmentNo] NVARCHAR(15) COLLATE database_default,
			[HouseNo] NVARCHAR(15) COLLATE database_default,
			[ContactGuid] UNIQUEIDENTIFIER,
			[Province] NVARCHAR(50) COLLATE database_default,
			[Country] NVARCHAR(50) COLLATE database_default,
			[PostOfficeBox] NVARCHAR(50) COLLATE database_default
		)
		
		DECLARE @contactName NVARCHAR(100) = ''
		DECLARE @contactMainPostOfficeAddress UNIQUEIDENTIFIER
		DECLARE @contactMainTeleITContact UNIQUEIDENTIFIER
		DECLARE @contactPostOfficeAddresses NVARCHAR(max)
		DECLARE @contactTeleItContacts NVARCHAR(max)
		DECLARE @contactWriteAction INT = 0

		IF EXISTS (SELECT * FROM [#tblContact])
		BEGIN
			
			IF (@contactId IS NULL OR @contactId = 0) -- nowy kontakt
			BEGIN
				IF (@contactGuid IS NULL OR @contactGuid LIKE '00000000-0000-0000-0000-000000000000')
					SELECT @contactGuid = CASE WHEN [Guid] IS NOT NULL AND [Guid] NOT LIKE '00000000-0000-0000-0000-000000000000' THEN [Guid] ELSE NEWID() END FROM [#tblContact]

				SELECT 
					@contactName = '',
					@contactMainTeleITContact = NEWID(),
					@contactWriteAction = 1
			END
			ELSE -- istniejący kontakt
			BEGIN
				SELECT 
					@contactName = [Name], 
					@contactWriteAction = 2
				FROM [SSCommon].[STContacts] 
				WHERE [Guid] = @contactGuid

				SELECT @contactMainTeleITContact = [Guid] FROM [SSCommon].[STTeleItContacts] WHERE [ContactGuid] LIKE @contactGuid
				IF (@contactMainTeleITContact IS NULL)
					SET @contactMainTeleITContact = NEWID()
			END
			INSERT INTO [#TeleItContacts]([Guid], [Name], [ContactGuid], [Telephone1], [Telephone2], [Telephone3], [Fax], [Fax2], [Email1], [Email2], [WWW],
				[Communicator1], [Communicator2], [Description], [Telex], [Facebook])
			SELECT @contactMainTeleITContact, 'domyślny adres IT', @contactGuid, ISNULL(c.[Telephone1], ''), ISNULL(c.[Telephone2], ''), '', ISNULL(c.[Fax], ''), '', ISNULL(c.[Email], ''), '', ISNULL(c.[Website], ''),
				'', '', '', ISNULL(c.[Telex], ''), ISNULL(c.[Facebook], '')
			FROM [#tblContact] c
		END

		-- zapis informacji do tabeli z danymi adresu domyślnego
		IF EXISTS (SELECT * FROM [#tblMainAddress])
		BEGIN
			IF (@contactGuid IS NULL)
				SET @contactGuid = NEWID()

			DECLARE @postOfficeAddressName NVARCHAR(64)
			SELECT @contactMainPostOfficeAddress = [Guid], @postOfficeAddressName = [Name] FROM [#tblMainAddress]

			IF (@contactId IS NULL OR @contactId = 0) -- nowy kontakt
			BEGIN
				SELECT @contactId = [Id] FROM [SSCommon].[STContacts] WHERE [Guid] LIKE @contactGuid
				IF (@contactMainPostOfficeAddress IS NULL OR @contactMainPostOfficeAddress LIKE '00000000-0000-0000-0000-000000000000')
					SET @contactMainPostOfficeAddress = NEWID()
			END
			ELSE -- istniejący kontakt
			BEGIN
				IF EXISTS(SELECT * FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @postOfficeAddressName)
					SELECT @contactMainPostOfficeAddress = [Guid] FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @postOfficeAddressName
				ELSE
					IF (@contactMainPostOfficeAddress IS NULL OR @contactMainPostOfficeAddress LIKE '00000000-0000-0000-0000-000000000000')
						SET @contactMainPostOfficeAddress = NEWID()
			END
			INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
				[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
			SELECT CASE WHEN a.[Name] IS NULL OR a.[Name] = '' THEN 'adres domyślny' ELSE a.[Name] END, @contactMainPostOfficeAddress, a.[Street], '', '', a.[Place], a.[PostalCode], a.[Post], a.[ApartmentNo],
				a.[HouseNo], @contactGuid, a.[Province], a.[Country], ''
			FROM [#tblMainAddress] a
		END	

		-- zapis informacji do tabeli z danymi adresu korespondencyjnego
		IF EXISTS (SELECT * FROM [#tblCorespondenceAddress])
		BEGIN
			DECLARE @corespondenceAddressId INT
			DECLARE @corespondenceAddressGuid UNIQUEIDENTIFIER
			DECLARE @corespondenceAddressName NVARCHAR(64)
			SELECT  @corespondenceAddressId = [Id], @corespondenceAddressGuid = [Guid], @corespondenceAddressName = [Name] FROM [#tblCorespondenceAddress]

			IF (@corespondenceAddressId IS NULL OR @corespondenceAddressId = 0) -- nowy adres korespondencyjny
				IF (@corespondenceAddressGuid IS NULL OR @corespondenceAddressGuid LIKE '00000000-0000-0000-0000-000000000000')
					SET @corespondenceAddressGuid = NEWID()	
			ELSE
			BEGIN
				IF EXISTS (SELECT * FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @corespondenceAddressName)
					SELECT @corespondenceAddressGuid = [Guid] FROM [SSCommon].[STPostOfficeAddresses] WHERE [ContactGuid] LIKE @contactGuid AND [AddressName] LIKE @corespondenceAddressName
				ELSE
					IF (@corespondenceAddressGuid IS NULL OR @corespondenceAddressGuid LIKE '00000000-0000-0000-0000-000000000000')
						SET @corespondenceAddressGuid = NEWID()
			END
			INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
				[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
			SELECT CASE WHEN a.[Name] IS NULL OR a.[Name] = '' THEN 'adres korespondencyjny' ELSE a.[Name] END, @corespondenceAddressGuid, a.[Street], '', '', a.[Place], a.[PostalCode], a.[Post], a.[ApartmentNo],
				a.[HouseNo], @contactGuid, a.[Province], a.[Country], ''
			FROM [#tblCorespondenceAddress] a
		END

		-- odczytanie pozostałych adresów podpietych pod kontakt (np. adresy dostaw)
		INSERT INTO [#PostOfficeAddresses]([AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
			[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox])
		SELECT [AddressName], [Guid], [Street], [Commune], [District], [Place], [PostCode], [PostOffice], [ApartmentNo],
			[HouseNo], [ContactGuid], [Province], [Country], [PostOfficeBox]
		FROM [SSCommon].[STPostOfficeAddresses]
		WHERE [ContactGuid] = @contactGuid AND [AddressName] NOT LIKE 'adres domyślny' AND [AddressName] NOT LIKE 'adres korespondencyjny'


		SET @contactTeleItContacts = (SELECT * FROM #TeleItContacts FOR XML PATH('TeleItContacts'), root ('DocumentElement'))
		SET @contactPostOfficeAddresses = (SELECT * FROM #PostOfficeAddresses FOR XML PATH('PostOfficeAddresses'), root ('DocumentElement'))
		
		EXEC [SSCommon].[sp_SaveContactWithDetails] 
					@contactId,
					@contactGuid,
					@contactName,
					@contactMainPostOfficeAddress,
					@contactMainTeleITContact,
					@contactPostOfficeAddresses,
					@contactTeleItContacts,
					@contactWriteAction,
					@contextInfo

-- zapis informacji do tabeli z danymi bankowymi
-- TODO wykorzystać procedurę sp_InsertBankingInfoAndAccounts, sp_UpdateBankingInfoAndAccounts, sp_InsertElementAndAttrs, sp_SaveElementAndAttrs, sp_UpdateElementAndAttrs jeżeli jest taka możliwość
		SET CONTEXT_INFO @ContextInfoBinaryA;

		DECLARE @removingElementsTable TABLE([ElementId] INT, [AccountId] INT) -- tymczasowa tabela na elementy do usunięcia
		IF EXISTS (SELECT * FROM [#tblBankAccounts])
		BEGIN	
			-- tabela tymczasowa na Rachunki bankowe
			CREATE TABLE [#Accounts]
			(
				[Id] INT IDENTITY(1,1),
				[Guid] UNIQUEIDENTIFIER,
				[BankingInfosGuid] UNIQUEIDENTIFIER,
				[AccountNo] NVARCHAR(50) COLLATE database_default,
				[BankName] NVARCHAR(100) COLLATE database_default,
				[AccountName] NVARCHAR(100) COLLATE database_default,
				[ElementId] INT,
				[MainElementGuid] UNIQUEIDENTIFIER,
				[SWIFT_BIC] NVARCHAR(11) COLLATE database_default,
				[Active] BIT,
				[WhiteList] BIT,
				[WhiteListDate] DATETIME2
			)

			DECLARE @bankAccountsEKId INT
			DECLARE @bankAccountsEKGuid UNIQUEIDENTIFIER = '4849512E-9024-4B7C-BD06-737BF4FF3A77'
			SELECT @bankAccountsEKId = [ElementKindId] FROM [SSCommon].[STElementKinds] WHERE [Guid] LIKE @bankAccountsEKGuid
			DECLARE @minBankAccountsPosition INT
			DECLARE @bankingId INT
			DECLARE @bankingMainElement UNIQUEIDENTIFIER
			DECLARE @bankingAccounts NVARCHAR(MAX)

			-- sprawdzenie czy kontrahent ma BankingInfo
			IF (@bankingInfoGuid IS NULL) -- nowe banking info
			BEGIN			
				SET @bankingInfoGuid = NEWID()
				EXEC [SSCommon].[sp_InsertBankingInfoAndAccounts] 
					0,
					@bankingInfoGuid,
					'',
					'<DocumentElement />',
					@contextInfoA,
					NULL			
			END
			
			-- usunięcie kont bankowych
			INSERT INTO @removingElementsTable
			SELECT cmnElements.[ElementId], cmnAccounts.[Id] FROM [SSCommon].[STElements] cmnElements
			INNER JOIN [SSCommon].[STAccounts] cmnAccounts ON cmnAccounts.[MainElementGuid] = cmnElements.[Guid]
			LEFT OUTER JOIN [#tblBankAccounts] tAccount ON tAccount.[Id] = cmnAccounts.[Id]
			WHERE tAccount.[Id] IS NULL AND cmnAccounts.[BankingInfosGuid] LIKE @bankingInfoGuid

			IF EXISTS(SELECT * FROM @removingElementsTable)
			BEGIN
				DELETE FROM [SSCommon].[STElementExAttributes]
				WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)

				UPDATE [SSCommon].[STBankingInfos]
				SET [MainAccount] = NULL
				FROM [SSCommon].[STBankingInfos] bi
				INNER JOIN #tblBankAccounts tba ON bi.[MainAccount] = tba.[Guid]
				WHERE bi.[Guid] = @bankingInfoGuid
			
				BEGIN TRY
					DELETE FROM [SSCommon].[STAccounts]
					WHERE [Id] IN (SELECT [AccountId] FROM @removingElementsTable)
			
					DELETE FROM [SSCommon].[STElements]
					WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
				END TRY
				BEGIN CATCH
					IF (ERROR_NUMBER() = 547)
						THROW 50000, N'Nie można usunąć rachunku bankowego. Istnieją dokumenty powiązane z danym rachunkiem.', 1
					ELSE
						THROW 50000, N'Nie powiodło się usuwanie rachunków bankowych z bazy danych', 1
				END CATCH
			END		
			-- aktualizacja rachunków bankowych
			SELECT @minBankAccountsPosition = ISNULL(MIN([Position]), 0) FROM [SSCommon].[STElements] WHERE [ElementKindId] = @bankAccountsEKId
		
			BEGIN TRY
				--aktualizacja STElements
				SELECT
						@extAttAccountNo = '',
						@extAttDescription = '',
						@extAttExtAttributes = '<DocumentElement />',					
						@extAttExtOtherThenOriginal=0			
				
				DECLARE @counter INT = 0
				
				DECLARE @accountToHandle NVARCHAR(50)
				SELECT @accountToHandle = [Guid]
				FROM #tblBankAccounts
				ORDER BY [Id] ASC
				OFFSET @counter ROWS
				FETCH NEXT 1 ROWS ONLY
				
				WHILE @accountToHandle IS NOT NULL
				BEGIN
					SELECT
						@extAttElementId = CASE WHEN elem.[ElementId] IS NULL OR elem.[ElementId] = 0 THEN -1 ELSE elem.[ElementId] END,
						@extAttTitle = tAcc.[Guid],
						@extAttActive = tAcc.[IsActive],
						@extAttShortcut = tAcc.[Guid],
						@extAttPosition = @minBankAccountsPosition - 1,
						@minBankAccountsPosition = @minBankAccountsPosition - 1,
						@extAttExtWriteAction = CASE WHEN elem.[ObjVer] IS NULL THEN 1 ELSE 2 END,
						@extAttExtObjVer = CASE WHEN elem.[ElementId] IS NULL OR elem.[ElementId] = 0 THEN 0 ELSE elem.[ObjVer] + 1 END
					FROM #tblBankAccounts tAcc
					LEFT OUTER JOIN [SSCommon].[STElements] elem ON tAcc.[Guid] = elem.[Guid]
					WHERE tAcc.[Guid] = @accountToHandle

					EXEC [SSCommon].[sp_SaveElementAndAttrs] 
							@extAttElementId , 
							@accountToHandle, 
							@extAttTitle,
							@extAttActive,
							@extAttAccountNo,
							@extAttDescription,
							@bankAccountsEKId,
							@extAttShortcut,
							@extAttPosition,
							@extAttExtObjVer,
							@extAttExtAttributes,
							@bankAccountsEKGuid,
							@extAttExtWriteAction,
							@extAttExtOtherThenOriginal,
							@contextInfoA
					
					SET @accountToHandle = NULL
					SELECT @accountToHandle = [Guid],
						@counter = @counter + 1
					FROM #tblBankAccounts
					ORDER BY [Id] ASC
					OFFSET @counter ROWS
					FETCH NEXT 1 ROWS ONLY
				END

				SELECT @bankingMainElement = [Guid]
				FROM #tblBankAccounts
				WHERE [IsDefault] = 1
				--aktualizacja danych konkretnych rachunków bankowych
				INSERT INTO #Accounts ([Guid], [BankingInfosGuid], [AccountNo], [BankName],
					[Active], [SWIFT_BIC], [WhiteList], [WhiteListDate])
				SELECT 
					CASE WHEN tAccount.[Guid] IS NULL OR tAccount.[Guid] LIKE '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE tAccount.[Guid] END,
					@bankingInfoGuid, tAccount.[AccountNo], ISNULL(tAccount.[BankName], ''),
					ISNULL(tAccount.[IsActive], 1), ISNULL(tAccount.[SWIFT_BIC], ''),
					tAccount.[WhiteList], tAccount.[WhiteListDate]
				FROM #tblBankAccounts tAccount
				LEFT OUTER JOIN [SSCommon].[STAccounts] sa ON tAccount.[Guid] = sa.[Guid]
				
				UPDATE #Accounts
				SET [AccountName] = [Guid],
					[MainElementGuid] = [Guid]
				FROM #Accounts
				
				SET @bankingAccounts = (SELECT * FROM #Accounts FOR XML PATH('Accounts'), root ('DocumentElement'))
			
				EXEC [SSCommon].[sp_UpdateBankingInfoAndAccounts] 
					@bankingId,
					@bankingInfoGuid,
					'',
					@bankingAccounts,
					@contextInfoA,
					@bankingMainElement
			END TRY
			BEGIN CATCH
				THROW 50000, N'Nie powiodła się aktualizacja rachunków bankowych', 1
			END CATCH
		END
		ELSE -- jeżeli istniały rachunki bankowe to zostały usunięte (UWAGA: przypisane rachunki bankowe np. do dokumentów nie zostaną usunięte)
		BEGIN 
			IF (@bankingInfoGuid IS NOT NULL AND @bankingInfoGuid NOT LIKE '00000000-0000-0000-0000-000000000000')
			BEGIN
				BEGIN TRY
				-- usunięcie kont bankowych
				INSERT INTO @removingElementsTable
				SELECT cmnElements.[ElementId], cmnAccounts.[Id] FROM [SSCommon].[STElements] cmnElements
				INNER JOIN [SSCommon].[STAccounts] cmnAccounts ON cmnAccounts.[MainElementGuid] = cmnElements.[Guid]
				LEFT OUTER JOIN [#tblBankAccounts] tAccount ON tAccount.[Id] = cmnAccounts.[Id]
				WHERE tAccount.[Id] IS NULL AND cmnAccounts.[BankingInfosGuid] LIKE @bankingInfoGuid
				
				IF EXISTS(SELECT * FROM @removingElementsTable)
				BEGIN
					DELETE FROM [SSCommon].[STElementExAttributes]
					WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)

					UPDATE [SSCommon].[STBankingInfos]
					SET [MainAccount] = NULL
					WHERE [Guid] = @bankingInfoGuid
				
					DELETE FROM [SSCommon].[STAccounts]
					WHERE [Id] IN (SELECT [AccountId] FROM @removingElementsTable)
				
					DELETE FROM [SSCommon].[STElements]
					WHERE [ElementId] IN (SELECT [ElementId] FROM @removingElementsTable)
		
					UPDATE [SSCommon].[STContractors]
					SET [BankingInfoGuid] = NULL
					WHERE [Id] = @contractorId

					DELETE FROM [SSCommon].[STBankingInfos]
					WHERE [Guid] LIKE @bankingInfoGuid

					SET @bankingInfoGuid = NULL
				END
				END TRY
				BEGIN CATCH
					IF (ERROR_NUMBER() = 547)
						THROW 50000, N'Nie można usunąć rachunku bankowego. Istnieją dokumenty powiązane z danym rachunkiem.', 1
					ELSE
						THROW 50000, N'Nie powiodło się usuwanie rachunków bankowych z bazy danych', 1
				END CATCH
			END
		END

		SET CONTEXT_INFO @ContextInfoBinary;

		IF EXISTS (SELECT * FROM [#tblContractor])
		BEGIN
			DECLARE @contractorMainElement UNIQUEIDENTIFIER
			DECLARE @mainPersonGuid UNIQUEIDENTIFIER
			SET @mainPersonGuid = NULL

			DECLARE @contractorElementKindGuid UNIQUEIDENTIFIER = 'BC090993-0768-4487-BE6F-E44E9501734C'
			DECLARE @contractorElementKindId INT = NULL

			DECLARE @firstname NVARCHAR(50), @surname NVARCHAR(50)
			DECLARE @maxStringIdentLenght INT = 100
			SELECT @firstname = [FirstName], @surname = [Surname] FROM [#tblContractor]
			IF (@firstname IS NULL OR @firstname LIKE '')
				SELECT @firstname = [FirstName] FROM [#tblContact]
			IF (@surname IS NULL OR @surname LIKE '')
				SELECT @surname = [Surname] FROM [#tblContact]
			
			IF (@contractorId IS NULL OR @contractorId = 0)
			BEGIN
				SET @contractorMainElement = NEWID()

				IF EXISTS(SELECT TOP 1 ElementId FROM [SSCommon].[STElements] elem INNER JOIN [#tblContractor] tContractor ON elem.[Shortcut] LIKE tContractor.[Shortcut] INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] WHERE ekinds.[Name] LIKE 'Kontrahenci')
					THROW 50000, N'Kontrahent o podanym skrócie istnieje w bazie danych', 1

                IF @moduleCode = 'FKF'
					SET @extAttPosition = (SELECT [PositionFk] FROM #tblContractor WHERE [Id] = @contractorId)
                ELSE
                    SET @extAttPosition = NULL

                IF EXISTS(SELECT TOP 1 ElementId FROM [SSCommon].[STElements] elem INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] WHERE elem.Position = @extAttPosition AND ekinds.[Name] = 'Kontrahenci')
					THROW 50000, N'Kontrahent o podanej pozycji istnieje w bazie danych', 1

				BEGIN TRY	
					SELECT 
						@extAttElementId = NULL,
						@extAttAccountNo =  CASE WHEN @moduleCode = 'FKF' THEN 'COMDA#GENERATEVALIDACCOUNTNO2024' ELSE 'COMDA#RESETVALIDACCOUNTNO2008' END,
						@extAttDescription = '',
						@extAttExtObjVer = 0,
						@extAttExtWriteAction = 1,
						@extAttExtOtherThenOriginal = 0

					SELECT @extAttTitle = tContractor.[Name], 
						@extAttActive = ISNULL(tContractor.[Status], 1), 						
						@extAttShortcut = ISNULL(tContractor.[Shortcut], '')
					FROM [#tblContractor] tContractor
					
					-- exAttributes
					-- splitPayment
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
						'System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'splitPayment', 0, 0
					-- idFK
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'idFK', (SELECT [IdFk] FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'idFK', 0, 0						
					-- parametrFK
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'parametrFK', 0, 0
					-- typCeny
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR  [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'typCeny', 0, 0
					-- rodzajCeny
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rodzajCeny', 0, 0
					-- rejestr
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rejestr', 0, 0
					-- formaPlatnosci
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'formaPlatnosci', 0, 0
					--	znacznik kontrahenta w FK
					INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
						[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
					SELECT @extAttElementId, 'znacznik', (SELECT ISNULL([FkMarkerId], 0) FROM [#tblContractor]),
						'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'znacznik', 0, 0
  
					SET @extAttExtAttributes = (SELECT * FROM #ExtAttributes FOR XML PATH('ElementExAttributes'), root ('DocumentElement'))
					
					EXEC [SSCommon].[sp_SaveElementAndAttrs] 
						@extAttElementId , 
						@contractorMainElement, 
						@extAttTitle,
						@extAttActive,
						@extAttAccountNo,
						@extAttDescription,
						@contractorElementKindId,
						@extAttShortcut,
						@extAttPosition,
						@extAttExtObjVer,
						@extAttExtAttributes,
						@contractorElementKindGuid,
						@extAttExtWriteAction,
						@extAttExtOtherThenOriginal,
						@contextInfo

					IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
					BEGIN
						SET CONTEXT_INFO @ContextInfoBinaryP;
						SET @mainPersonGuid = NEWID()
						INSERT INTO [SSCommon].[STPersons]([Firstname],[SecondName],[Surname],[BirthDate],[Guid],[StringIdent],
							[DualStringIdent1],[DualStringIdent2],[Note],[ContactGuid],[BankingInfoGuid],[DataSourceName],[DataSourceAddress],[DataProcessingAgree],[DataMarketingProcessingAgree],
							[MainElement],[ExcludedFromAnonymization],[Anonymized])
						SELECT ISNULL(@firstname, ''), '', ISNULL(@surname, ''), CAST('1753-01-01 00:00:00.000' AS DATETIME2), @mainPersonGuid, LEFT(ISNULL(tContractor.[Name], ''), @maxStringIdentLenght),
							ISNULL(tContractor.[Pesel],''), ISNULL(tContractor.[Nip],''), '', @contactGuid, @bankingInfoGuid, '', '', 0, 0,
							@contractorMainElement, 0, 0
						FROM [#tblContractor] tContractor

						INSERT INTO [SSCommon].[STPersonsClassification]([Guid], [ElementId], [CDim_UserPerson], [CDim_EmployeePerson], [CDim_ContractorContactPerson])
							VALUES(NEWID(), @mainPersonGuid, 0, 0, 1)
						SET CONTEXT_INFO @ContextInfoBinary;
					END

					INSERT INTO [SSCommon].[STContractors]([Guid],
						[Shortcut],[Name],[NIP],[PESEL],[REGON],[NIP_UE],[Note],
						[ContactGuid],[BankingInfoGuid],[LimitFlag],[LimitAmount],[LimitCurrency],
						[VIES],[Negotiation],[MainElement],[MainPerson],[VATPayerActive],
						[NaturalPerson],[LinkedUnit], [VATGroup], [JST], [IDWew])
					SELECT
						@contractorGuid, tContractor.[Shortcut], tContractor.[Name], ISNULL(tContractor.[Nip], ''), ISNULL(tContractor.[Pesel], ''), ISNULL(tContractor.[Regon], ''), ISNULL(tContractor.[NipUE], ''),
						ISNULL(tContractor.[Note], ''),@contactGuid, @bankingInfoGuid, ISNULL(tContractor.[IsLimitActive], 0), ISNULL(tContractor.[LimitValue], 0), ISNULL(tContractor.[LimitCurrency], ''),
						ISNULL(tContractor.[VIES], 0), ISNULL(tContractor.[PriceNegotiation], 0), @contractorMainElement, @mainPersonGuid, ISNULL(tContractor.[VATPayerActive], 0),
						ISNULL(tContractor.[PersonKind], 0), ISNULL(tContractor.[LinkedUnit], 0), ISNULL(tContractor.[VatGroup], 0), ISNULL(tContractor.[Jst], 0), ISNULL(tContractor.[IDWew], '')
					FROM [#tblContractor] tContractor

					SELECT @contractorId = [Id] FROM [SSCommon].[STContractors] WHERE Shortcut = @extAttShortcut

					IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
					BEGIN
						SET CONTEXT_INFO @ContextInfoBinaryP;
						INSERT INTO [SSCommon].[STPersonsContractors]([PersonGuid],[ContractorGuid],[PersonOrder],[Guid])
							VALUES(@mainPersonGuid, @contractorGuid, 0, NEWID())
						SET CONTEXT_INFO @ContextInfoBinary;
					END

				END TRY
				BEGIN CATCH	
					THROW 50000, N'Nie powiodło się dodawanie kontrahenta', 1
				END CATCH
			END
			ELSE
			BEGIN
			IF @moduleCode LIKE 'FKF'
				SET @extAttPosition = (SELECT [PositionFk] FROM #tblContractor WHERE [Id] = @contractorId)
            ELSE
                SET @extAttPosition = NULL
			 
			 IF EXISTS(SELECT TOP 1 ElementId FROM [SSCommon].[STElements] elem 
                        INNER JOIN [SSCommon].[STElementKinds] ekinds ON elem.[ElementKindId] = ekinds.[ElementKindId] 
                        INNER JOIN [SSCommon].[STContractors] ctr ON elem.[Guid] = ctr.[MainElement]
                        WHERE elem.Position = @extAttPosition AND ekinds.[Name] = 'Kontrahenci' AND ctr.[Id] <> @contractorId)
				THROW 50000, N'Kontrahent o podanej pozycji istnieje w bazie danych', 1
	
            BEGIN TRY
					SELECT @mainPersonGuid = [MainPerson], @contractorMainElement = [MainElement], @contractorGuid = [Guid] FROM [SSCommon].[STContractors] WHERE [Id] = @contractorId
					
					SET CONTEXT_INFO @ContextInfoBinaryP;

					IF ((@surname IS NOT NULL AND @surname NOT LIKE '') OR (@firstname IS NOT NULL AND @firstname NOT LIKE ''))
					BEGIN
						IF (@mainPersonGuid IS NULL)
						BEGIN
							SET @mainPersonGuid = NEWID()

							INSERT INTO [SSCommon].[STPersons]([Firstname],[SecondName],[Surname],[BirthDate],[Guid],[StringIdent],
								[DualStringIdent1],[DualStringIdent2],[Note],[ContactGuid],[BankingInfoGuid],[DataSourceName],[DataSourceAddress],[DataProcessingAgree],[DataMarketingProcessingAgree],
								[MainElement],[ExcludedFromAnonymization],[Anonymized])
							SELECT ISNULL(@firstname, ''), '', ISNULL(@surname, ''), CAST('1753-01-01 00:00:00.000' AS DATETIME2), @mainPersonGuid, LEFT(ISNULL(tContractor.[Name], ''), @maxStringIdentLenght),
								ISNULL(tContractor.[Pesel],''), ISNULL(tContractor.[Nip],''), '', @contactGuid, @bankingInfoGuid, '', '', 0, 0,
								@contractorMainElement, 0, 0
							FROM [#tblContractor] tContractor
														
							INSERT INTO [SSCommon].[STPersonsClassification]([Guid], [ElementId], [CDim_UserPerson], [CDim_EmployeePerson], [CDim_ContractorContactPerson])
								VALUES(NEWID(), @mainPersonGuid, 0, 0, 1)

							INSERT INTO [SSCommon].[STPersonsContractors]([PersonGuid],[ContractorGuid],[PersonOrder],[Guid])
								VALUES(@mainPersonGuid, @contractorGuid, 0, NEWID())
						END
						ELSE
						BEGIN
							UPDATE [SSCommon].[STPersons]
							SET [Firstname] = ISNULL(@firstname, ''), [Surname] = ISNULL(@surname, '')
							FROM [#tblContractor] tContractor
							WHERE [SSCommon].[STPersons].Guid LIKE @mainPersonGuid
						END
					END
					ELSE
					BEGIN
						UPDATE [SSCommon].[STContractors]
						SET [MainPerson] = NULL
						WHERE [Id] = @contractorId

						DELETE FROM [SSCommon].[STPersons]
						WHERE [Guid] LIKE @mainPersonGuid

						SET @mainPersonGuid = NULL
					END			
					
					SET CONTEXT_INFO @ContextInfoBinary;

					UPDATE [SSCommon].[STContractors]
					SET
						[Shortcut] = ISNULL(tContractor.[Shortcut], ''),
						[Name] = ISNULL(tContractor.[Name], ''),
						[NIP] = ISNULL(tContractor.[Nip], ''),
						[PESEL] = ISNULL(tContractor.[Pesel], ''),
						[REGON] = ISNULL(tContractor.[Regon], ''),
						[NIP_UE] = ISNULL(tContractor.NipUE, ''),
						[Note] = ISNULL(tContractor.Note, ''),
						[ContactGuid] = @contactGuid,
						[BankingInfoGuid] = @bankingInfoGuid,
						[LimitFlag] = ISNULL(tContractor.[IsLimitActive], 0),
						[LimitAmount] = ISNULL(tContractor.[LimitValue], 0),
						[LimitCurrency] = ISNULL(tContractor.[LimitCurrency], ''),
						[VIES] = ISNULL(tContractor.[VIES], 0),
						[Negotiation] = ISNULL(tContractor.[PriceNegotiation], 0),
						[MainPerson] = @mainPersonGuid,
						[VATPayerActive] = ISNULL(tContractor.[VATPayerActive], 0),
						[NaturalPerson] = ISNULL(tContractor.[PersonKind], 0),
						[LinkedUnit] = ISNULL(tContractor.[LinkedUnit], 0),
						[VatGroup] = ISNULL(tContractor.[VatGroup], 0),
						[Jst] = ISNULL(tContractor.[Jst], 0),
                        [IDWew] = ISNULL(tContractor.[IDWew], '')
					FROM [SSCommon].[STContractors] cmnCtrs
					INNER JOIN [#tblContractor] tContractor ON tContractor.[Id] = cmnCtrs.[Id]
					WHERE cmnCtrs.[Id] = @contractorId 
												
					SELECT @extAttElementId = elem.[ElementId]
					FROM [SSCommon].[STElements] elem
					WHERE elem.[Guid] = @contractorMainElement
					
					SELECT 
					    @extAttAccountNo = CASE WHEN @moduleCode = 'FKF' THEN 'COMDA#UPDATEVALIDACCOUNTNO2024' ELSE 'COMDA#UPDATEVALIDACCOUNTNO2008' END, 
						@extAttDescription = [Description],
						@extAttExtObjVer = [ObjVer] + 1,
						@extAttExtWriteAction = 2,
						@extAttExtOtherThenOriginal = 0
					FROM [SSCommon].[STElements] 
					WHERE [ElementId] = @extAttElementId					
				
					SELECT @extAttTitle = ISNULL(tContractor.[Name], ''), 
						@extAttActive = ISNULL(tContractor.[Status], 1), 						
						@extAttShortcut = ISNULL(tContractor.[Shortcut], '')
					FROM [#tblContractor] tContractor

					-- exAttributes
					DECLARE @exAttributeId INT
					DECLARE @exAttributeGuid UNIQUEIDENTIFIER
					DECLARE @exAttributeCLRType NVARCHAR(400)
					DECLARE @exAttributeTitle NVARCHAR(200)
					DECLARE @exAttributeAccessFlag BIT
					DECLARE @exAttributeInheritedFlag BIT

					--	splitPayment
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'splitPayment'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN					
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'splitPayment', (SELECT CASE WHEN [RequestMppTransfers] LIKE 'Wymagaj' THEN 2 ELSE (CASE WHEN [RequestMppTransfers] LIKE 'Blokada' THEN 1 ELSE 0 END) END FROM [#tblContractor]),
						'System.Int16, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'splitPayment', 0, 0
					END
					--	idFK
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'idFK'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'idFK', (SELECT [IdFk] FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'idFK', (SELECT [IdFk] FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'idFK', 0, 0
					END
					--	parametrFK
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'parametrFK'
					IF (@exAttributeId IS NOT NULL)
					BEGIN					
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'parametrFK', (SELECT ISNULL([AccountantParameter], '') FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'parametrFK', 0, 0
					END
					--	typCeny
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'typCeny'
					IF (@exAttributeId IS NOT NULL)
					BEGIN						
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR  [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END  FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'typCeny', (SELECT CASE WHEN [PriceTypeId] IS NULL OR  [PriceTypeId] < 0 THEN 0 ELSE [PriceTypeId] END  FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'typCeny', 0, 0
					END
					--	rodzajCeny
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'rodzajCeny'
					IF (@exAttributeId IS NOT NULL)
					BEGIN						
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'rodzajCeny', (SELECT ISNULL([SaleKindId],0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rodzajCeny', 0, 0
					END
					--	rejestr
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'rejestr'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'rejestr', (SELECT ISNULL([PayRegistryId], 0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'rejestr', 0, 0
					END
					--	formaPlatnosci
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'formaPlatnosci'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'formaPlatnosci', (SELECT ISNULL([PaymentFormId], 0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'formaPlatnosci', 0, 0
					END
					--	znacznik kontrahenta w FK
					SET @exAttributeId = NULL
					SELECT @exAttributeId = ex.[ElementExAttrId], @exAttributeGuid = ex.[Guid], @exAttributeCLRType = ex.[OriginalCLRType],
							@exAttributeTitle = ex.[Title], @exAttributeAccessFlag = ex.[AccessFlag], @exAttributeInheritedFlag = ex.[InheritedFlag]
						FROM [SSCommon].[STElementExAttributes] ex
						INNER JOIN [SSCommon].[STElements] elem ON elem.[ElementId] = ex.[ElementId]
						INNER JOIN [SSCommon].[STContractors] ctrs ON ctrs.[MainElement] = elem.[Guid]
						WHERE ctrs.[Id] = @contractorId AND ex.[Name] LIKE 'znacznik'
					IF (@exAttributeId IS NOT NULL)
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue], 
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @exAttributeId, 'znacznik', (SELECT ISNULL([FkMarkerId], 0) FROM [#tblContractor]),
							@exAttributeCLRType, @exAttributeGuid, @exAttributeTitle, @exAttributeInheritedFlag, @exAttributeAccessFlag
					END
					ELSE
					BEGIN
						INSERT INTO #ExtAttributes([ElementId], [Name], [StringValue],
							[OriginalCLRType], [Guid], [Title], [InheritedFlag], [AccessFlag])
						SELECT @extAttElementId, 'znacznik', (SELECT ISNULL([FkMarkerId], 0) FROM [#tblContractor]),
							'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089', NEWID(), 'znacznik', 0, 1
					END

					SET @extAttExtAttributes = (SELECT * FROM #ExtAttributes FOR XML PATH('ElementExAttributes'), root ('DocumentElement'))
					
					EXEC [SSCommon].[sp_SaveElementAndAttrs] 
						@extAttElementId , 
						@contractorMainElement, 
						@extAttTitle,
						@extAttActive,
						@extAttAccountNo,
						@extAttDescription,
						@contractorElementKindId,
						@extAttShortcut,
						@extAttPosition,
						@extAttExtObjVer,
						@extAttExtAttributes,
						@contractorElementKindGuid,
						@extAttExtWriteAction,
						@extAttExtOtherThenOriginal,
						@contextInfo
				END TRY
				BEGIN CATCH
					THROW 50000, N'Nie powiodła się aktualizacja kontrahenta', 1
				END CATCH
			END
		END
		
		IF EXISTS (SELECT * FROM [#tblTradeData])
		BEGIN
			DECLARE @discountPercent FLOAT
			SELECT @discountPercent = [DiscountPercent] FROM [#tblTradeData]
			IF (@discountPercent IS NOT NULL AND @discountPercent <> 0)
			BEGIN
				IF EXISTS (SELECT * FROM [HM].[ContractorDiscounts] WHERE [ContractorId] = @contractorId)
				BEGIN
					UPDATE [HM].[CN] 
					SET [cena] = @discountPercent
					WHERE [typ] = 15 and [idpd] = @contractorId
				END
				ELSE
				BEGIN
					INSERT INTO [HM].[CN]([idpd], [cena], [typ], [flag], [subtyp], [idpm], [typpd], [idck], [typpar], [parametr], [typceny], [waluta], [aktywna], [flagcen], [datastart], [dataend], [createdBy])
					VALUES (@contractorId, @discountPercent, 15, 0, 0, NULL, 0, NULL, 0, 0, 0, '%', 0, 0, NULL, NULL, NULL)
				END
			END
			ELSE
			BEGIN
				DELETE FROM [HM].[CN]
				WHERE [idpd] = @contractorId AND [typ] = 15
			END
		END

		DECLARE @markerId INT		
		DECLARE @contractorKindId INT
		DECLARE @catalogId NVARCHAR(100)
		SELECT @catalogId = [CatalogId], @contractorKindId = ISNULL([ContractorKindId], 0), @markerId = ISNULL([MarkerId], 0)
			FROM [#tblContractor]

		DECLARE @contractorKindGuid UNIQUEIDENTIFIER
		IF (@contractorKindId <> 0)
			SELECT @contractorKindGuid = [Guid] FROM [SSCommon].[STElements] WHERE [ElementId] = @contractorKindId
		
		DECLARE @catalogGuid UNIQUEIDENTIFIER
		IF (@catalogId IS NOT NULL AND @catalogId NOT LIKE '')
			SELECT @catalogGuid = elem.[Guid] 
			FROM [SSCommon].[STElements] elem
			JOIN [SSCommon].[STElementKinds] elemKind ON elem.[ElementKindId] = elemKind.[ElementKindId] AND elemKind.Shortcut ='KatalogKh'
			WHERE elem.[Shortcut] = @catalogId

		DECLARE @markerGuid UNIQUEIDENTIFIER
		IF (@markerId IS NOT NULL AND @markerId <> 0)
			SELECT @markerGuid = [Guid] FROM [SSCommon].[STElements] WHERE [ElementId] = @markerId

		IF EXISTS (SELECT * FROM [#tblCustomFields])
		BEGIN
			--SELECT * FROM [#tblCustomFields]
			DECLARE @valueStatement NVARCHAR(MAX)
			DECLARE @newLine NVARCHAR(10)
			SET @newLine = char(13)+char(10)

			DECLARE @setStatement NVARCHAR(MAX)
			SELECT @setStatement = COALESCE(@setStatement + ', ', '') +
					'[' + [ColumnName] + '] = ' + 
					CASE WHEN [Value] IS NULL THEN 'NULL' 
					ELSE (CASE WHEN ([Type] = 'System.Boolean, mscorlib') THEN (CASE WHEN [Value] = 'True' THEN '1' ELSE '0' END)
						ELSE (CASE WHEN ([Type] = 'System.DateTime, mscorlib') THEN 'CAST(''' + [Value] + '''AS datetime2)'
							ELSE (CASE WHEN ([Type] = 'System.String, mscorlib') THEN '''' + [Value] + ''''
								ELSE [Value] END) END) END) END
			FROM [#tblCustomFields]
			--SELECT @setStatement

			DECLARE @insertColumnsStatement NVARCHAR(MAX)
			DECLARE @insertValuesStatement NVARCHAR(MAX)
			SELECT @insertColumnsStatement = COALESCE(@insertColumnsStatement + ', ', '') + '[' + [ColumnName] + ']',
				@insertValuesStatement = COALESCE(@insertValuesStatement + ', ', '') +
					CASE WHEN [Value] IS NULL THEN 'NULL' 
					ELSE (CASE WHEN ([Type] = 'System.Boolean, mscorlib') THEN (CASE WHEN [Value] = 'True' THEN '1' ELSE '0' END)
						ELSE (CASE WHEN ([Type] = 'System.DateTime, mscorlib') THEN 'CAST(''' + [Value] + '''AS datetime2)'
							ELSE (CASE WHEN ([Type] = 'System.String, mscorlib') THEN '''' + [Value] + ''''
								ELSE [Value] END) END) END) END
			FROM [#tblCustomFields]
			--SELECT @insertColumnsStatement
			--SELECT @insertValuesStatement
			
			SET @valueStatement = CONCAT('IF EXISTS(SELECT * FROM [SSCommon].[',@classificationTableName,'] WHERE [ElementId] = ',@contractorId,')',
				@newLine,'BEGIN',
					@newLine,'UPDATE [SSCommon].[',@classificationTableName,']',
					@newLine,'SET ',
					@newLine,@setStatement,
					@newLine,'WHERE [ElementId] = ',@contractorId,
				@newLine,'END',
				@newLine,'ELSE',
				@newLine,'BEGIN',
					@newLine,'INSERT INTO [SSCommon].[',@classificationTableName,']([ElementId], [Guid],',@insertColumnsStatement,')',
					@newLine,'VALUES(',@contractorId,',''',NEWID(),''',',@insertValuesStatement,')',
				@newLine,'END')

			--SELECT @valueStatement
			EXEC (@valueStatement)
		END
		ELSE
		BEGIN
			DECLARE @deleteStatement NVARCHAR(MAX)

			SET @deleteStatement = CONCAT('DELETE FROM [SSCommon].[', @classificationTableName,'] WHERE [ElementId] = ''', @contractorId, '''')
			--SELECT @deleteStatement
			EXEC (@deleteStatement)
		END

		IF NOT EXISTS(SELECT * FROM [SSCommon].[STContractorsClassification] WHERE [ElementId] LIKE @contractorGuid)
		BEGIN
			INSERT INTO [SSCommon].[STContractorsClassification]([Guid],[ElementId],[CDim_ZnacznikKh],[CDim_RodzajKh],[CDim_KatalogKh])
			VALUES (NEWID(), @contractorGuid, @markerGuid, @contractorKindGuid, @catalogGuid)
		END
		ELSE
		BEGIN
			UPDATE [SSCommon].[STContractorsClassification]
			SET [CDim_ZnacznikKh] = @markerGuid, [CDim_RodzajKh] = @contractorKindGuid, [CDim_KatalogKh] = @catalogGuid
			WHERE [ElementId] LIKE @contractorGuid
		END

		--uprawnienia do grup 
		--deserializacja z xml
		INSERT INTO [#ContractorsGroupPermissions]
		SELECT 
			HasRight,ElementId,Name		
		FROM 
			OPENXML(@handle, '/Contractor/ContractorsGroupPermissions/ContractorsGroupPermission')
			WITH
			(
				[HasRight] BIT 'HasRight',
				[Name] NVARCHAR(100) 'Name',
				[ElementId] INT 'ElementId'
				
			)
			
		-- dopisanie kontrahenta do grupy
		DELETE crg FROM [SSCommon].[STContractorsRightsGroups] crg
		JOIN [SSCommon].[STUserRightsGroups] urg on urg.ContractorRightGroup = crg.ContractorRightGroup
		WHERE ContractorId=@contractorId AND UserId = @userid;

		INSERT INTO [SSCommon].[STContractorsRightsGroups](ContractorId,ContractorRightGroup)
		SELECT @contractorId, ElementId FROM #ContractorsGroupPermissions WHERE HasRight=1	


		--czyścimy śmieci
		IF (OBJECT_ID('tempdb..[#tblContractor]') IS NOT NULL) DROP TABLE [#tblContractor]
		IF (OBJECT_ID('tempdb..[#tblContact]') IS NOT NULL) DROP TABLE [#tblContact]
		IF (OBJECT_ID('tempdb..[#tblMainAddress]') IS NOT NULL) DROP TABLE [#tblMainAddress]
		IF (OBJECT_ID('tempdb..[#tblCorespondenceAddress]') IS NOT NULL) DROP TABLE [#tblCorespondenceAddress]
		IF (OBJECT_ID('tempdb..[#tblTradeData]') IS NOT NULL) DROP TABLE [#tblTradeData]
		IF (OBJECT_ID('tempdb..[#tblBankAccounts]') IS NOT NULL) DROP TABLE [#tblBankAccounts]
		IF (OBJECT_ID('tempdb..[#tblCustomFields]') IS NOT NULL) DROP TABLE [#tblCustomFields]
		IF (OBJECT_ID('tempdb..[#ContractorsGroupPermissions]') IS NOT NULL) DROP TABLE [#ContractorsGroupPermissions]
		
		EXEC [Common].[UpdateContractorRIFromXML] @contractorId, @contractorData, @moduleCode , @contextInfo, @contextInfoP

		COMMIT TRANSACTION;

		exec [Common].[ReadContractorAsXML] @contractorId, @moduleCode, @userid

	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

GO

CREATE OR ALTER FUNCTION HM.fnGetTaxCountryFromVatId
(
    @VatId NVARCHAR(20)
)
RETURNS CHAR(2)
AS
BEGIN
    -- Clean the VatId from dashes, spaces and dots
    DECLARE @NormalizedNip NVARCHAR(20) = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@VatId, '-', ''), ' ', ''), '.', '')))
    -- Set default country to Poland
    DECLARE @TaxCountry CHAR(2) = 'PL'
    -- VatId's frefix is the country code
    SELECT @TaxCountry = SUBSTRING(@NormalizedNip, 1, 2) WHERE @NormalizedNip LIKE '[a-zA-Z][a-zA-Z]%'

    RETURN @TaxCountry
END 

GO

CREATE OR ALTER PROCEDURE HM.UpdateKsefRelatedPartiesBranch
    @documentId INT,
    @receiverAddressId INT,
    @contractorAddressId INT,
    @subtype INT = NULL
AS
BEGIN
    BEGIN TRY

        IF @documentId = 0
            RETURN;

        --sprawdzamy czy dokument jest dokumentem KSeF
        IF (HM.fnIsKsefDocumentType(@subtype) = 0)
            RETURN;

    BEGIN TRANSACTION

        -- Aktualizacja (usunięcie/dodanie) podmiotów powiązanych dla sprzedawcy wystawiającego fakturę w imieniu (Jednostki Samorządu Terytorialnego)
        EXEC HM.UpdateKsefRelatedPartiesJstSeller
            @documentId = @documentId,
            @subtype = @subtype

        -- odział w podmiotach powiazanych
        -- (id wpisu, id oddziału, dodany przez uzytkownika w oknie podmioty powiazane)
        DECLARE @branches TABLE 
        (
            Id int, 
            BranchId INT, 
            AddedByUser BIT, 
            [Role] INT, 
            IdentifierType INT,
            PRIMARY KEY CLUSTERED (Id),
            INDEX IX_BranchId_Role_AddedByUser_IdentifierType NONCLUSTERED (BranchId, [Role], AddedByUser, IdentifierType)
         )

        INSERT INTO @branches (Id, BranchId, AddedByUser, [Role], IdentifierType)
        SELECT Id, ISNULL(BranchId, -1), AddedByUser, [Role], IdentifierType
        FROM HM.Ksef_OtherParties
         --    biezacy dokument             oddział        odbiorca/JST/GV      
        WHERE DocumentId = @documentId AND PartyType = 1 AND [Role] IN (2, 8, 10)

        DECLARE @receiverId INT
        DECLARE @branchId INT
        DECLARE @adrName VARCHAR(150)
        DECLARE @receiverIDWew VARCHAR(5)
        DECLARE @email NVARCHAR(100) 
        DECLARE @phone NVARCHAR(50)

        --pobranie id odbiorcy oraz numeru oddziału KSeF
        SELECT TOP 1 @receiverId = idkh, @adrName = nazwaAdr, @receiverIDWew = LTRIM(RTRIM(REPLACE(REPLACE(ad.KsefDepartmentValue, '-', ''), ' ', ''))),
                    @email = ISNULL(ad.EmailThirdParty, ''), @phone = ISNULL(IIF(ISNULL(LEN(TRIM(ad.TelThirdParty)), 0) > 16, NULL, TRIM(ad.TelThirdParty)), '')
        FROM HM.AD WHERE Id = @receiverAddressId

        DECLARE @BranchToDeleteIdList HM.IntListTypeUnique

        IF (ISNULL(@receiverAddressId, 0) = ISNULL(@contractorAddressId, 0))
        BEGIN
            
            INSERT INTO @BranchToDeleteIdList (Id)
            SELECT DISTINCT Id 
            FROM @branches p
            WHERE p.BranchId IS NOT NULL AND p.AddedByUser = 0

            -- usuwanie wpisów z podmiotów powiązanych
            EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList
            
            COMMIT TRANSACTION
            RETURN;
        END

        -- pobranie id odziału KSeF
        SELECT TOP 1 @branchId = s.Id
        FROM SSCommon.STContractors AS Contractors0
        INNER JOIN SSCommon.STContacts contact ON contact.Guid = Contractors0.ContactGuid
        INNER JOIN SSCommon.STPostOfficeAddresses s ON s.ContactGuid = contact.Guid
            AND s.AddressName = @adrName
        WHERE Contractors0.Id = @receiverId

        --pobranie podatkowego kraju odbiorcy
        DECLARE @receiverCountry CHAR(2)
        SELECT @receiverCountry = HM.fnGetTaxCountryFromVatId(IIF(TRIM(NIP_UE) <> '', NIP_UE, NIP))
        FROM SSCommon.STContractors
        WHERE Id = @receiverId

        -- brak może wynikać z usunięcia adresu dostawy (oddziału ksef)
        SET @branchId = ISNULL(@branchId, 0)

        --pobranie id kontrahenta, czy jest członkiem Grupy VAT lub Jednostką Samorządu Terytorialnego (JST). 
        DECLARE @Buyer_JST BIT = 0
        DECLARE @Buyer_VG BIT = 0
        DECLARE @contractorId INT
        SELECT 
            @contractorId = a.idkh,
            @Buyer_JST = c.JST,
            @Buyer_VG = c.VATGroup
        FROM HM.AD a
        INNER JOIN SSCommon.STContractors c ON c.Id = a.idkh
        WHERE a.Id = @contractorAddressId

        DECLARE @InsertThirdPartySetting INT
        SELECT TOP 1 @InsertThirdPartySetting = SettingSelector
        FROM HM.KsefSettings_Additional
        WHERE SettingType = 24      -- ustawienie: Odbiorca w podmiotach powiązanych = 24

		-- Nie dodawaj odbiorcy do Podmiotów powiązanych (opcja domyślna)
		SET @InsertThirdPartySetting = ISNULL(@InsertThirdPartySetting, 0)

        --Czy aktualne ustawienie Ksef ma wstawiać identyfikator (nip, nipUe, wewnętrzny, zewnętrzny)
        DECLARE @HasIdentifierType INT = IIF(@InsertThirdPartySetting = 2, 1, 0) -- 2 oznacza dodaj z indentyfikatorem

        -- odbiorca jest z IDWew lub Oddziałem KSeF
		SET @receiverIDWew = ISNULL(@receiverIDWew, '')
        DECLARE @InsertReceiverIDWew BIT = IIF(@receiverIDWew != '' AND @InsertThirdPartySetting = 0, 1, 0)

        --sprawdzenie czy wygenerowany stan jest aktualny w stosunku do JST / GV / IDWew oraz ustawień KSeF tak by można było skończyć przetwarzanie
        DECLARE @Role__2_OK BIT = 0
        DECLARE @Role__8_OK BIT = 0
        DECLARE @Role_10_OK BIT = 0

        DECLARE @InsertRole_2 BIT = IIF(@Buyer_JST = 0 AND @Buyer_VG = 0 AND (@InsertThirdPartySetting != 0 OR @InsertReceiverIDWew = 1), 1, 0)
        IF @InsertRole_2 = 1 AND EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 2 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1))) OR
           @InsertRole_2 = 0 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 2 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1) OR @InsertThirdPartySetting = 0))
        BEGIN
            SET @Role__2_OK = 1 -- automatyczne wpisy dotyczące roli 2 są prawidłowe
        END

        IF @Buyer_JST = 1 AND EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 8 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1))) OR
           @Buyer_JST = 0 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 8 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1)))
        BEGIN
            SET @Role__8_OK = 1 -- automatyczne wpisy dotyczące roli 8 są prawidłowe
        END

        IF @Buyer_VG = 1 AND EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 10 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1))) OR
           @Buyer_VG = 0 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 10 AND ((IdentifierType = 0 AND @HasIdentifierType = 0) OR (IdentifierType <> 0 AND @HasIdentifierType = 1)))
        BEGIN
            SET @Role_10_OK = 1 -- automatyczne wpisy dotyczące roli 10 są prawidłowe
        END

        IF NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId != @branchId AND AddedByUser = 0) AND	-- zmieniono adres => zmienił się @branchId => należy usunąć poprzednie automatyczne wpisy
			(@Role__2_OK = 1 AND @Role__8_OK = 1 AND @Role_10_OK = 1 OR										-- wpisy z rolami 2/8/10 ok
            NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE AddedByUser = 0) AND @branchId = 0)				-- dokument nie ma odziału w podmiotach powiazanych i nie wskazano odziału
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        -- z podmiotow powiazanych usuwamy oddziały inne niz wskazany ?? Lub gdy JST/GV nie pasuje do nabywcy i IDWew dla odbiorcy
        INSERT INTO @BranchToDeleteIdList (Id)
        SELECT DISTINCT p.Id
        FROM @branches AS p
        WHERE 
            (p.BranchId <> @branchId AND p.AddedByUser = 0)
            OR
            (
                p.BranchId = @branchId AND 
                p.AddedByUser = 0 AND
                (
                    (
                        (p.[Role] = 8 AND @Buyer_JST = 0) 
                        OR 
                        (p.[Role] = 10 AND @Buyer_VG = 0) 
                        OR 
                        (
                            (@Buyer_JST = 1 OR @Buyer_VG = 1 OR @InsertReceiverIDWew = 0) 
                            AND 
                            p.[Role] = 2
                        )
                    )
                    OR
                    (
                        (p.IdentifierType = 0 AND @HasIdentifierType = 1) 
                        OR 
                        (p.IdentifierType <> 0 AND @HasIdentifierType = 0)
                    )
                ) 
            )

        -- usuwanie wpisów z podmiotów powiązanych
        EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList

        DELETE br
        FROM @branches br
        INNER JOIN @BranchToDeleteIdList ids ON ids.Id = br.Id

        -- ===== OBSŁUGA WSTAWIANIA ODBIORCY DO PODMIOTÓW POWIĄZANYCH ===== --

        --Obsługa ustawienia Odbiorca - czy dodawać odbiorcę do podmiotów powiązanych
        IF @InsertThirdPartySetting = 0 AND @Buyer_JST = 0 AND @Buyer_VG = 0 AND @InsertReceiverIDWew = 0 -- Nie dodawaj odbiorcy do podmiotów powiązanych
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        IF @branchId = 0 --brak id odd
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        -- odczytujemy dane odziału
        DECLARE @name NVARCHAR(512),
                @nip NVARCHAR(20),
                @nip_ue NVARCHAR(20), 
                @country CHAR(2), 
                @vies BIT, 
                @address NVARCHAR(512),
                @gln NVARCHAR(13),
                @eori NVARCHAR(256);
            
        DECLARE @IsContractorNameForBranchName BIT = 0 -- 0 - Nazwa oddziału KSef (domyślna), 1 - Nazwa kontrahenta
        SELECT TOP 1 @IsContractorNameForBranchName = SettingSelector
        FROM HM.KsefSettings_Additional
        WHERE SettingType = 27      -- ustawienie: Nazwa Oddziału/Kontrahenta w Podmiocie3 = 27

        IF EXISTS (SELECT TOP 1 1 FROM HM.KSef_Departments AS d WHERE d.STPostOfficeAddressesId = @branchId)
        BEGIN
            --Pobieranie danych oddziału
            SELECT 
                    @name = CASE WHEN @IsContractorNameForBranchName = 1 THEN TRIM(c.Name) ELSE TRIM(a.AddressName) END, -- wysyłamy nazwę kontrahenta
                    @nip = TRIM(c.NIP), 
                    @nip_ue = TRIM(c.NIP_UE), 
                    @country = SUBSTRING(TRIM(a.Country), 1, 2), 
                    @vies = c.VIES,
                    @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place),
                    @gln = TRIM(d.GLN),
                    @eori = TRIM(d.EORI),
                    @email = d.EmailThirdParty,
                    @phone = IIF(ISNULL(LEN(TRIM(d.TelThirdParty)), 0) > 16, NULL, TRIM(d.TelThirdParty))
            FROM HM.KSef_Departments AS d
            INNER JOIN SSCommon.STPostOfficeAddresses a ON a.Id = d.STPostOfficeAddressesId
            INNER JOIN SSCommon.STContacts ct ON ct.Guid = a.ContactGuid
            INNER JOIN SSCommon.STContractors AS c ON c.ContactGuid = ct.Guid
            WHERE d.STPostOfficeAddressesId = @branchId 
        END
        ELSE
        BEGIN
            -- EORI
            EXEC HM.GetContractorEori
                    @contractorId = @receiverId,
                    @value        = @eori OUTPUT;
                 
            -- GLN
            EXEC HM.GetContractorGln
                    @contractorId = @receiverId,
                    @value        = @gln OUTPUT;

            --Pobieranie danych dla adresu domyślnego
            SELECT 
                    @name = TRIM(c.Name), -- Tutaj brak oddziału KSeF, więc zawsze nazwa kontrahenta
                    @nip = TRIM(c.NIP), 
                    @nip_ue = TRIM(c.NIP_UE), 
                    @country = SUBSTRING(TRIM(a.Country), 1, 2), 
                    @vies = c.VIES,
                    @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place)
            FROM SSCommon.STPostOfficeAddresses a 
            INNER JOIN SSCommon.STContacts ct ON ct.Guid = a.ContactGuid
            INNER JOIN SSCommon.STContractors AS c ON c.ContactGuid = ct.Guid
            WHERE a.Id = @branchId
        END

        -- okreslamy typ identyfikatora ( NIP / VAT-UE / Brak )
        DECLARE @identyfierType INT = 0     -- Brak
        DECLARE @normalizedNipUE NVARCHAR(20) = HM.fnNormalizeNip(@nip_ue, @receiverCountry, @vies, 1, 1);
        DECLARE @normalizedNip NVARCHAR(20) = HM.fnNormalizeNip(@nip, @receiverCountry, @vies, 0, 1);
        DECLARE @otherTaxId NVARCHAR(100)

        DECLARE @InternalId VARCHAR(40)
        IF ISNULL(LEN(TRIM(@normalizedNip)), 0) > 0 AND ISNULL(LEN(TRIM(@receiverIDWew)), 0) > 0
            SET @InternalId = TRIM(@normalizedNip) + '-' + TRIM(@receiverIDWew)

		DECLARE @isCountryFromEu INT
		SELECT @isCountryFromEu = IsFromUe FROM HM.fnIsCountryFromUe(@receiverCountry)

        IF ISNULL(LEN(TRIM(@InternalId)), 0) > 0
            SET @identyfierType = 4 -- Wewnętrzny
        ELSE IF ISNULL(LEN(TRIM(@normalizedNip)), 0) > 0 AND @receiverCountry = 'PL'
            SET @identyfierType = 1 -- NIP
        ELSE IF ISNULL(LEN(TRIM(@normalizedNipUE)), 0) > 0 AND @receiverCountry <> 'PL' AND @vies = 1 AND @isCountryFromEu = 1
            SET @identyfierType = 2 -- VatUE
        ELSE IF ISNULL(LEN(TRIM(@nip)), 0) > 0 
        BEGIN
            SET @identyfierType = 3 -- Identyfikator podatkowy inny.
            IF (LEFT(@nip, 2) = @receiverCountry)
                SET @otherTaxId = SUBSTRING(@nip, 3, LEN(@nip) - 2);
            ELSE
                SET @otherTaxId = @nip
        END
        ELSE
            SET @identyfierType = 0 -- Nieokreślony (Brak)

        IF @InsertThirdPartySetting = 1 -- Dodaj Odbiorcę dokumentu do Podmiotów powiązanych bez numeru NIP
        BEGIN
            SET @identyfierType = 0 -- Nieokreślony (Brak)
        END

        IF @Buyer_JST = 0 AND @Buyer_VG = 0 AND (@InsertThirdPartySetting != 0 OR @InsertReceiverIDWew != 0)
        BEGIN
            -- brak odziału w podmiotach powiazanych - dodajemy wskazanego
            INSERT INTO HM.Ksef_OtherParties (
                DocumentId,
                PartyType,
                BranchId,
                ContractorId,
                Name,
                Role,
                InternalId,
                IdentifierType,
                Nip,
                NipUE,
                OtherTaxId,
                Eori,
                CountryCode,
                Address,
                Gln,
                Email,
                Phone,
                AddedByUser
            )
            VALUES (
                @documentId,
                1,
                @branchId,
                @receiverId,
                @name,
                2,
                @InternalId,
                @identyfierType,
                @normalizedNip,
                @normalizedNipUE,
                @otherTaxId,
                @eori,
                @country,
                @address,
                @gln,
                @email,
                @phone,
                0
            );
        END
        ELSE
        BEGIN
            IF @Buyer_JST = 1 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 8)
            BEGIN
                -- brak odbiorcy w podmiotach powiazanych - dodajemy wskazanego
                INSERT INTO HM.Ksef_OtherParties (
                    DocumentId,
                    PartyType,
                    BranchId,
                    Name,
                    Role,
                    InternalId,
                    IdentifierType,
                    Nip,
                    NipUE,
                    OtherTaxId,
                    Eori,
                    CountryCode,
                    Address,
                    Gln,
                    Email,
                    Phone,
                    AddedByUser
                )
                VALUES (
                    @documentId,
                    1,
                    @branchId,
                    @name,
                    8,
                    @InternalId,
                    @identyfierType,
                    @normalizedNip,
                    @normalizedNipUE,
                    @otherTaxId,
                    @eori,
                    @country,
                    @address,
                    @gln,
                    @email,
                    @phone,
                    0
                );
            END

            IF @Buyer_VG = 1 AND NOT EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0 AND [Role] = 10)
            BEGIN
                -- brak odbiorcy w podmiotach powiazanych - dodajemy wskazanego
                INSERT INTO HM.Ksef_OtherParties (
                    DocumentId,
                    PartyType,
                    BranchId,
                    Name,
                    Role,
                    InternalId,
                    IdentifierType,
                    Nip,
                    NipUE,
                    OtherTaxId,
                    Eori,
                    CountryCode,
                    Address,
                    Gln,
                    Email,
                    Phone,
                    AddedByUser
                )
                VALUES (
                    @documentId,
                    1,
                    @branchId,
                    @name,
                    10,
                    @InternalId,
                    @identyfierType,
                    @normalizedNip,
                    @normalizedNipUE,
                    @otherTaxId,
                    @eori,
                    @country,
                    @address,
                    @gln,
                    @email,
                    @phone,
                    0
                );
            END
        END

    	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

GO

CREATE OR ALTER PROCEDURE HM.UpdateKsefRelatedPartiesPayer
    @documentId INT,
    @payerId INT
AS
BEGIN
    IF @documentId = 0
        RETURN

    -- platnik w podmiotach powiazanych
    -- (id wpisu, id kontrahenta, dodany przez uzytkownika w oknie podmioty powiazane)
    DECLARE @payers TABLE (Id INT, ContractorId INT, AddedByUser BIT)

    INSERT INTO @payers (Id, ContractorId, AddedByUser)
    SELECT Id, ISNULL(ContractorId, -1), AddedByUser
      FROM HM.Ksef_OtherParties
     --    biezacy dokument             kontrahent        platnik      
     WHERE DocumentId = @documentId AND PartyType = 2 AND Role = 6

    -- istnieje juz wlasciwy wpis platnika w podmiotach powiazanych
    IF (SELECT COUNT(Id) FROM @payers WHERE ContractorId = @payerId AND AddedByUser = 0) <> 0
        RETURN

    -- dokument nie ma platnika w podmiotach powiazanych i nie wskazano platnika
    IF (SELECT COUNT(Id) FROM @payers WHERE AddedByUser = 0) = 0 AND @payerId = 0
        RETURN

    BEGIN TRY
	    BEGIN TRANSACTION

        -- z podmiotow powiazanych usuwamy platnikow innych niz wskazany
        DELETE
          FROM kop
          FROM HM.Ksef_OtherParties AS kop
               INNER JOIN @payers AS p ON p.Id = kop.Id
         WHERE p.ContractorId <> @payerId AND p.AddedByUser = 0

        DELETE
          FROM @payers
         WHERE ContractorId <> @payerId AND AddedByUser = 0

        IF @payerId <> 0 AND NOT EXISTS (SELECT 1 FROM @payers WHERE ContractorId = @payerId)
        BEGIN
            -- odczytujemy dane platnika
            DECLARE
                @name NVARCHAR(512),
                @nip NVARCHAR(20),
                @nip_ue NVARCHAR(20),
                @country CHAR(2),
                @taxCountry CHAR(2),
                @vies BIT,
                @address NVARCHAR(512),
                @email NVARCHAR(100),
                @phone NVARCHAR(50),
                @eori NVARCHAR(256),
                @gln NVARCHAR(13);

            SELECT
                @name = TRIM(c.Name),
                @nip = TRIM(c.NIP),
                @nip_ue = TRIM(c.NIP_UE),
                @country = SUBSTRING(TRIM(a.Country), 1, 2),
                @taxCountry = HM.fnGetTaxCountryFromVatId(IIF(TRIM(c.NIP_UE) <> '', c.NIP_UE, c.NIP)),
                @vies = c.VIES,
                @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place),
                @email = t.Email1,
                @phone = IIF(ISNULL(LEN(TRIM(t.Telephone1)), 0) > 16, NULL, TRIM(t.Telephone1))
            FROM SSCommon.STContractors AS c
            LEFT OUTER JOIN SSCommon.STContacts AS ct ON ct.Guid = c.ContactGuid
            LEFT OUTER JOIN SSCommon.STPostOfficeAddresses AS a ON a.Guid = ct.MainPostOfficeAddress
            LEFT OUTER JOIN SSCommon.STTeleItContacts AS t ON t.Guid = ct.MainTeleITContact
            WHERE c.Id = @payerId

            -- okreslamy typ identyfikatora ( NIP / VAT-UE / Inny / Brak )
            DECLARE @identyfierType INT = 0     -- Brak
            DECLARE @normalizedNip NVARCHAR(20) = HM.fnNormalizeNip(@nip, @taxCountry, @vies, 0, 1);
            DECLARE @normalizedNipUE NVARCHAR(20) = HM.fnNormalizeNip(@nip_ue, @taxCountry, @vies, 1, 1);
            DECLARE @otherTaxId NVARCHAR(50) = NULL
            IF ISNULL(@normalizedNipUE, '') <> ''
                SET @identyfierType = 2         -- VAT-UE
            ELSE
            BEGIN
                IF ISNULL(@normalizedNip, '') <> ''
                    SET @identyfierType = 1     -- NIP
                ELSE
                BEGIN
                    SET @nip_ue = ISNULL(TRIM(REPLACE(REPLACE(REPLACE(@nip_ue, '-', ''), ' ', ''), '.', '')), '')
                    SET @nip    = ISNULL(TRIM(REPLACE(REPLACE(REPLACE(@nip   , '-', ''), ' ', ''), '.', '')), '')
                    DECLARE @normalizedOtherTaxId NVARCHAR(50) = IIF(@nip_ue <> '', @nip_ue, @nip)
                    IF @normalizedOtherTaxId <> ''
                        SELECT @otherTaxId = @normalizedOtherTaxId,
                               @identyfierType = 3  -- Inny
                END
            END

            -- EORI
            EXEC HM.GetContractorEori
                 @contractorId = @payerId,
                 @value        = @eori OUTPUT;
                 
            -- GLN
            EXEC HM.GetContractorGln
                 @contractorId = @payerId,
                 @value        = @gln OUTPUT;

            -- brak platnika w podmiotach powiazanych - dodajemy wskazanego
            INSERT INTO HM.Ksef_OtherParties (
                DocumentId,
                PartyType,
                ContractorId,
                Name,
                Role,
                IdentifierType,
                Nip,
                NipUE,
                OtherTaxId,
                Eori,
                CountryCode,
                Address,
                Gln,
                Email,
                Phone,
                AddedByUser
            )
            VALUES (
                @documentId,
                2,
                @payerId,
                @name,
                6,
                @identyfierType,
                @normalizedNip,
                @normalizedNipUE,
                @otherTaxId,
                @eori,
                @country,
                @address,
                @gln,
                @email,
                @phone,
                0
            )
        END

    	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		THROW
	END CATCH
END
  
GO

SET IDENTITY_INSERT HM.KsefSettings_Additional ON;

--Nazwa Oddziału/Kontrahenta w Podmiocie3
IF NOT EXISTS (SELECT 1 FROM HM.KsefSettings_Additional WHERE Id = 27)
BEGIN
	INSERT INTO HM.KsefSettings_Additional (Id, SettingType, SettingSelector, SettingUserValue, SettingUserValue2, SettingBoolUserValue, CustomStringUserValue, CustomStringUserValue2, SettingDateTimeUserValue) 
	VALUES (27, 27, 0, NULL, NULL, 0, NULL, NULL, NULL)
END

--Unikalny identyfikator pozycji
IF NOT EXISTS (SELECT 1 FROM HM.KsefSettings_Additional WHERE Id = 28)
BEGIN
	INSERT INTO HM.KsefSettings_Additional (Id, SettingType, SettingSelector, SettingUserValue, SettingUserValue2, SettingBoolUserValue, CustomStringUserValue, CustomStringUserValue2, SettingDateTimeUserValue) 
	VALUES (28, 28, 0, NULL, NULL, 0, NULL, NULL, NULL)
END

SET IDENTITY_INSERT HM.KsefSettings_Additional OFF;

GO

-- Zwiekszenie rozmiaru SymbolToKsef do 256 zgodnie z KSeF FA3
ALTER TABLE HM.KSefSettings_STClasificationDims
ALTER COLUMN [SymbolToKsef] NVARCHAR(256) NOT NULL

GO

CREATE OR ALTER PROCEDURE HM.stdhm_getrec_tw
@id int
AS
SELECT id,flag,aktywny,subtyp,typ,znacznik,rodzaj,katalog,info,kod,nazwa,kodpaskowy,vatsp,sww,zaokrag,idlongname,kontofk,metoda,przelicz,szablon,nazwaFiskal,jmdod1,przeljmdod1,jmdod2,przeljmdod2,stanmin,stanmax,typks,jm,jmdef,jmdom,przeljmdom,negoc,super,createdBy,createdDate,modifiedBy,modifiedDate, pcn, przelkg, przeluz,przelit,jmuzup,fundusz,odwrotneObc,vat50,subtypext,mpp,JPK_V7,kaucja,BarcodeSendToKSeF
FROM TW
WHERE id = @id
ORDER BY id ASC

GO

CREATE OR ALTER PROCEDURE HM.stdhm_insert_tw
@id int out, @flag smallint, @aktywny smallint, @subtyp smallint, @typ smallint,
@znacznik smallint, @rodzaj int, @katalog int, @info smallint, @kod varchar(41),
@nazwa varchar(101), @kodpaskowy varchar(151), @vatsp int, @sww varchar(21), @zaokrag smallint,
@idlongname int, @kontofk varchar(25), @metoda smallint, @przelicz smallint,
@szablon varchar(101), @nazwaFiskal varchar(41), @jmdod1 varchar(9), @przeljmdod1 float, @jmdod2 varchar(9),
@przeljmdod2 float, @stanmin float, @stanmax float, @typks varchar(41), @jm varchar(9),
@jmdef smallint, @jmdom varchar(9) out, @przeljmdom float out, @negoc smallint, @super int, @createdBy int, @createdDate datetime, @modifiedBy int,
@modifiedDate datetime, @pcn varchar(10), @przelkg float, @przeluz float, @przelit smallint, @jmuzup varchar(9), @fundusz int, @odwrotneObc smallint, @vat50 smallint, @subtypext smallint,
@mpp bit, @JPK_V7 varchar(10), @kaucja varchar(41), @BarcodeSendToKSeF bit
AS
SET @createdDate = GETDATE()


SET @przeljmdom = 1.0
IF @jmdef = 253 and @przeljmdod1 <> 0.0 SET @przeljmdom = @przeljmdod1
IF @jmdef = 252 and @przeljmdod2 <> 0.0 SET @przeljmdom = @przeljmdod2
SET @jmdom = CASE WHEN @jmdef = 253 THEN @jmdod1 WHEN @jmdef = 252 THEN @jmdod2 ELSE @jm END

IF @id=0 BEGIN
	INSERT INTO TW (flag, aktywny, subtyp, typ, znacznik,
		rodzaj, katalog, info, kod, nazwa,
		kodpaskowy, vatsp, sww, zaokrag,
		idlongname, kontofk, metoda, przelicz, szablon,
		nazwaFiskal, jmdod1, przeljmdod1, jmdod2, przeljmdod2,
		stanmin, stanmax, typks, jm, jmdef, jmdom, przeljmdom,
		negoc, super, createdBy, createdDate, pcn, przelkg, przeluz, przelit, jmuzup, fundusz, odwrotneObc, vat50, subtypext, mpp, JPK_V7, kaucja, BarcodeSendToKSeF)
	VALUES (@flag, @aktywny, @subtyp, @typ, @znacznik,
		@rodzaj, @katalog, @info, @kod, @nazwa,
		@kodpaskowy, @vatsp, @sww, @zaokrag,
		@idlongname, @kontofk, @metoda, @przelicz, @szablon,
		@nazwaFiskal, @jmdod1, @przeljmdod1, @jmdod2, @przeljmdod2,
		@stanmin, @stanmax, @typks, @jm, @jmdef, @jmdom, @przeljmdom,
		@negoc, @super, @createdBy, @createdDate, @pcn, @przelkg, @przeluz, @przelit, @jmuzup, @fundusz, @odwrotneObc, @vat50, @subtypext,@mpp,
		@JPK_V7, @kaucja, @BarcodeSendToKSeF)
	SET @id = SCOPE_IDENTITY()
	INSERT INTO MF.ProductionItemAttributes (HMID) VALUES (@id)
	BEGIN
		INSERT INTO ProductsKinds (KindId, kod)
		VALUES (@id, @kod)
	END
END ELSE BEGIN
	RAISERROR( '***IDENTITY_INSERT*** Próba dodania rekordu o identyfikatorze %ld do tabeli TW!', 16, 1, @id )
END
return @id

GO

CREATE OR ALTER PROCEDURE HM.stdhm_update_tw
@id int, @flag smallint, @aktywny smallint, @subtyp smallint, @typ smallint,
@znacznik smallint, @rodzaj int, @katalog int, @info smallint, @kod varchar(41),
@nazwa varchar(101), @kodpaskowy varchar(151), @vatsp int, @sww varchar(21), @zaokrag smallint,
@idlongname int, @kontofk varchar(25), @metoda smallint, @przelicz smallint,
@szablon varchar(101), @nazwaFiskal varchar(41), @jmdod1 varchar(9), @przeljmdod1 float, @jmdod2 varchar(9),
@przeljmdod2 float, @stanmin float, @stanmax float, @typks varchar(41), @jm varchar(9),
@jmdef smallint, @jmdom varchar(9) out, @przeljmdom float out, @negoc smallint, @super int, @createdBy int, @createdDate datetime, @modifiedBy int,
@modifiedDate datetime, @pcn varchar(10), @przelkg float, @przeluz float, @przelit smallint, @jmuzup varchar(9), @fundusz int, @odwrotneObc smallint, @vat50 smallint, @subtypext smallint,
@mpp bit, @JPK_V7 varchar(10), @kaucja varchar(41), @BarcodeSendToKSeF bit
AS
SET @modifiedDate = GETDATE()


SET @przeljmdom = 1.0
IF @jmdef = 253 and @przeljmdod1 <> 0.0 SET @przeljmdom = @przeljmdod1
IF @jmdef = 252 and @przeljmdod2 <> 0.0 SET @przeljmdom = @przeljmdod2
SET @jmdom = CASE WHEN @jmdef = 253 THEN @jmdod1 WHEN @jmdef = 252 THEN @jmdod2 ELSE @jm END

UPDATE TW
SET flag = @flag, aktywny = @aktywny, subtyp = @subtyp, typ = @typ, znacznik = @znacznik,
rodzaj = @rodzaj, katalog = @katalog, info = @info, kod = @kod, nazwa = @nazwa,
kodpaskowy = @kodpaskowy, vatsp = @vatsp, sww = @sww, zaokrag = @zaokrag,
idlongname = @idlongname, kontofk = @kontofk, metoda = @metoda, przelicz = @przelicz, szablon = @szablon,
nazwaFiskal = @nazwaFiskal, jmdod1 = @jmdod1, przeljmdod1 = @przeljmdod1, jmdod2 = @jmdod2, przeljmdod2 = @przeljmdod2,
stanmin = @stanmin, stanmax = @stanmax, typks = @typks, jm = @jm, jmdef = @jmdef, jmdom = @jmdom, przeljmdom = @przeljmdom,
negoc = @negoc, super = @super, modifiedBy = @modifiedBy, modifiedDate = @modifiedDate, pcn = @pcn,
przelkg = @przelkg, przeluz = @przeluz, przelit = @przelit, jmuzup = @jmuzup, fundusz = @fundusz, odwrotneObc = @odwrotneObc, vat50 = @vat50, subtypext=@subtypext,
	mpp = @mpp, JPK_V7 = @JPK_V7, kaucja = @kaucja, BarcodeSendToKSeF = @BarcodeSendToKSeF
WHERE @id = id
IF @typ = 110
	BEGIN
		UPDATE ProductsKinds SET kod = @kod WHERE KindId = @id
	END 

GO

CREATE OR ALTER VIEW [HM].[Ksef_ContractorJstSeller_View]
AS
    SELECT
	id,
	aktywny as IsInvoiceInNameOfSeller,
	CAST(subtyp AS INT) AS RoleId,
	CAST(CASE WHEN subtyp IN (5, 7, 9) THEN 1 ELSE 0 END AS BIT) AS IsCorrectRoleId,
	nazwa as SellerName,
	nip AS SellerNip,
	typks as SellerNipUe,
	regon as SellerRegon,
	pesel as SellerPesel,
	statusUE as IsSellerVies,
	'PL' as SellerCountryCode,
	ulica as SellerAddressStreet,
	dom as SellerAddressHouseNo,
	lokal as SellerAddressApartmentNo,
	kodpocz as SellerAddressPostCode,
	miejscowosc as SellerAddressCity,
    www as IDWew
FROM HM.KH
WHERE typ = 109 AND kod = 'HMF_SPRZEDAWCA' -- pobieranie po indeksie unikalnym
    -- pobieramy rekord tylko jeśli spełnione są wszystkie warunki, inaczej rekord nie jest pobrany, bo ten wpis w bazie zawsze istnieje
    AND CASE WHEN aktywny = 1 AND CASE WHEN subtyp IN (5, 7, 9) THEN 1 ELSE 0 END = 1 THEN 1 ELSE 0 END = 1
    AND NULLIF(TRIM(nip), '') IS NOT NULL -- NIP nie może być pusty

GO

CREATE OR ALTER PROCEDURE HM.UpdateKsefRelatedPartiesJstSeller
    @documentId INT,
    @subtype INT = NULL
AS
BEGIN
    BEGIN TRY
        IF @documentId = 0
            RETURN;

        --sprawdzamy czy dokument jest dokumentem KSeF
        IF (HM.fnIsKsefDocumentType(@subtype) = 0)
            RETURN;

        BEGIN TRANSACTION

        -- sprzedawca w imieniu którego wystawiany jest dokument w podmiotach powiązanych
        DECLARE @JstSellers TABLE (Id INT PRIMARY KEY, AddedByUser BIT)

        INSERT INTO @JstSellers (Id, AddedByUser)
        SELECT Id, AddedByUser
        FROM HM.Ksef_OtherParties
        WHERE DocumentId = @documentId AND PartyType = 0 -- typ nieznany

        DECLARE @isJSTSeller BIT = 0
        DECLARE @Role INT
        DECLARE @idWew NVARCHAR(5)
        IF EXISTS (SELECT TOP 1 1 FROM [HM].[Ksef_ContractorJstSeller_View])
        BEGIN
            SET @isJSTSeller = 1
            SELECT @Role = RoleId, @idWew = TRIM(IDWew) FROM [HM].[Ksef_ContractorJstSeller_View]
        END
        ELSE
        BEGIN

            DECLARE @BranchToDeleteIdList HM.IntListTypeUnique

            INSERT INTO @BranchToDeleteIdList (Id)
            SELECT DISTINCT p.Id 
            FROM HM.Ksef_OtherParties AS kop
            INNER JOIN @JstSellers AS p ON p.Id = kop.Id
            WHERE p.AddedByUser = 0

            -- usuwanie wpisów z podmiotów powiązanych
            EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList
        END

        IF @isJSTSeller = 0
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        --Pobieranie danych firmy i Wstawianie do podmiotów powiązanych JST sprzedawca, o ile już ich tam nie ma
        IF @isJSTSeller = 1 AND @Role IS NOT NULL AND @Role IN (5, 7, 9) AND NOT EXISTS (
            SELECT TOP 1 1 
            FROM HM.Ksef_OtherParties 
            WHERE DocumentId = @documentId AND PartyType = 0
        )
        BEGIN
        
            DECLARE @name NVARCHAR(512),
                    @nip NVARCHAR(20),
                    @vies BIT, 
                    @address NVARCHAR(512);

            DECLARE @StartIssueDate DATETIME
            -- Znany jest scenariusz, że ktoś zmieni date wystawienia, a dane firmy są zapisane między datami obowiązywania
            SELECT @StartIssueDate = d.[data] FROM HM.DK d WHERE d.Id = @documentId
            IF @StartIssueDate IS NULL
            BEGIN
               COMMIT TRANSACTION
               RETURN;
            END

            IF (@idWew IS NOT NULL AND @idWew <> '')
            BEGIN
                SELECT
                    @name = TRIM(SellerName),
                    @nip = TRIM(SellerNip),
                    @vies = IsSellerVies,
                    @address = HM.GetFullAddress(
                        TRIM(SellerAddressCity) + IIF(TRIM(SellerAddressCity) <> '', ', ' ,'') + SellerAddressStreet, 
                        SellerAddressHouseNo, 
                        SellerAddressApartmentNo, 
                        SellerAddressPostCode,
                        '')
                FROM [HM].[Ksef_ContractorJstSeller_View]
            END
            ELSE
            BEGIN
                SELECT TOP 1 
                    @name = TRIM(CompanyData1.[Name]),
                    @nip = TRIM(CompanyData1.[NIP]),
                    @vies = CompanyData1.[Vies],
                    @address = HM.GetFullAddress(
                        TRIM(PostOfficeAddresses5.[Place]) + IIF(TRIM(PostOfficeAddresses5.[Place]) <> '', ', ' ,'') + PostOfficeAddresses5.[Street], 
                        PostOfficeAddresses5.[HouseNo], 
                        PostOfficeAddresses5.[ApartmentNo], 
                        PostOfficeAddresses5.[PostCode],
                        PostOfficeAddresses5.[PostOffice])
                FROM SSCommon.[STCompany] as Company0 
                INNER JOIN SSCommon.[STCompanyData] as CompanyData1 
	                ON (CompanyData1.[CompanyGuid]=Company0.[Guid])  
                LEFT OUTER JOIN SSCommon.[STContacts] as Contacts3 
	                ON (Contacts3.[Guid]=CompanyData1.[ContactGuid])  
                LEFT OUTER JOIN SSCommon.[STPostOfficeAddresses] as PostOfficeAddresses5 
	                ON (PostOfficeAddresses5.[Guid]=Contacts3.[MainPostOfficeAddress])  
                WHERE  CompanyData1.[BeginDate] <= @StartIssueDate 
                AND CompanyData1.[EndDate] >= @StartIssueDate
                ORDER BY CompanyData1.[BeginDate] ASC
            END

            DECLARE @country CHAR(2) = 'PL'
            DECLARE @normalizedNip NVARCHAR(20) = HM.fnNormalizeNip(@nip, @country, @vies, 0, 1);

            -- Firma musi posiadać polski NIP
            IF @normalizedNip IS NULL OR @normalizedNip = ''
            BEGIN
                COMMIT TRANSACTION
                RETURN
            END

            DECLARE @identyfierType INT = 1     -- NIP
            DECLARE @InternalId VARCHAR(40)

            IF (@idWew IS NOT NULL AND @idWew <> '')
            BEGIN
                SET @identyfierType = 4         -- Wewnętrzny
                SET @InternalId = @normalizedNip + '-' + @idWew
            END

            -- brak wpisu w podmiotach powiazanych - dodajemy wskazanego
            INSERT INTO HM.Ksef_OtherParties (
                DocumentId,
                PartyType,
                Name,
                Role,
                InternalId,
                IdentifierType,
                Nip,
                CountryCode,
                Address,
                AddedByUser
            )
            VALUES (
                @documentId,
                0,
                @name,
                @Role,
                @InternalId,
                @identyfierType,
                @normalizedNip,
                @country,
                @address,
                0
            );
        END

    	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

GO

-- Dodanie wierszy do [HM].[KSef_Departments] dla starszych adresów dostaw
INSERT INTO [HM].[KSef_Departments] ([STPostOfficeAddressesId])
	SELECT adr.[Id]
	FROM [SSCommon].[STContractors] ctr
	INNER JOIN [SSCommon].[STPostOfficeAddresses] adr on ctr.[ContactGuid] = adr.[ContactGuid]
	LEFT JOIN [HM].[KSef_Departments] ksef ON adr.[Id] = ksef.[STPostOfficeAddressesId]
	WHERE adr.[AddressName] NOT IN ('adres domyślny', 'adres korespondencyjny') AND ksef.[STPostOfficeAddressesId] IS NULL
GO

CREATE OR ALTER FUNCTION [HM].[ft_AdvanceInvoiceSettlementPosition] ( 
	@positionId INT	-- pozycja dokumentu sprzedażowego do którego są zaliczki
	)
RETURNS TABLE
AS
RETURN (
		SELECT
			dp.lp																								AS SaleOrdinalNumber,
			dp.Id																								AS SalePositionId,
			dp.kod																								as SalePositionProductShortcut,
			vr.Name																								as SalePositionVatRateName,
			CAST((quantities.quantity_on_inventory_docs * 
				HM.DecimalPriceRound(CASE WHEN dp.grupacen = 0 
				THEN CAST(dp.cenawal AS DECIMAL(35, 15)) 
				ELSE CAST(dp.cenawal AS DECIMAL(35, 15)) * (1 + dp.wartstvat / 10000.0) END, 5)) 
				AS DECIMAL(24,2))																				AS SalePositionReleaseValueGross,
			relations.zpid																						AS ForeignOrderPositionId,
			dpz.id																								AS Id,
			dkz.id																								AS DkzId,
			dkz.kod																								AS DocumentNumber,
			dkz.data																							AS DocumentDate,
			(SELECT top 1 ISNULL(CAST(dpx.walBrutto AS DECIMAL(24,2)), 0)
				FROM HM.DP dpx
				WHERE dpx.id = r.dpId OR (dpx.idpozkoryg = r.dpId AND kompensata = 0)
				ORDER BY id DESC)																				AS AdvanceInvoiceAmount,
			settlement.Amount																					AS ForSettlementAmount,
			CAST(ISNULL(s.ValueGross, 0) AS DECIMAL(24,2))														AS SettlementAmount,
			CAST(settlement.Amount - ISNULL(s.ValueGross, 0) AS DECIMAL(24,2))									AS RemainingAmount,
			IIF(settlement.Amount > 0, 'false', 'true')															AS IsSettled,
			CAST(ISNULL(dkz.kurs, 0) AS DECIMAL(24,6))															AS ExchangeRate,
            IIF(dp.flag & 0x4000 = 0, 'false', 'true')                                                          AS IsElementOfSet
		FROM HM.DP dp
		JOIN HM.VatRate vr ON vr.id = dp.stvat
		CROSS APPLY HM.fnGetForeignOrderPositionRelatedToSalePosition(dp.id, dp.lp, dp.super, dp.kompensata, 
					dp.idpozkoryg) relations
		JOIN HM.AdvanceInvoiceSettlementForeignOrder r ON r.ZpId = relations.zpid				-- wiązanie pozycji zamówienia z pozycjami zaliczek
		JOIN HM.DP dpz ON dpz.id = r.DpId														-- pozycje zaliczek
		JOIN HM.DK dkz ON dkz.id = dpz.super AND dkz.anulowany <> 1 AND dkz.bufor = 0			-- dokumenty zaliczek
		CROSS APPLY HM.fnGetQuantityOnRelatedDocuments(relations.zpid) quantities
		CROSS APPLY HM.fnGetForSettlementAmount(dp.id, dp.super) settlement
		LEFT JOIN HM.AdvanceInvoiceSettlementSaleDocument s ON s.AdvanceInvoiceDpId = dpz.id AND s.SaleDocumentDpId = dp.id
		WHERE dp.id = @positionId
		)

GO