Fuchs_Intranet/Fuchs_Database/dbo/Stored Procedures/mfr__updt__servicerequests.sql

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[mfr__updt__servicerequests]
	@tblname as nvarchar(50)
	, @referencetable nvarchar(50)
	, @tgtid bigint
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRY

		DECLARE @now datetime = GETUTCDATE();
		DECLARE @tmp [dbo].[mfr__tt__servicerequests];

		DECLARE @tmp_cmd nvarchar(1000) = N'SELECT DISTINCT * FROM ' + @tblname;
		INSERT INTO @tmp EXECUTE [sp_executesql] @tmp_cmd;

		BEGIN TRANSACTION;

		IF @referencetable = 'mfr__servicerequests'
			BEGIN
				DELETE p
					OUTput deleted.*, @now INTO [dbo].[mfr__d_*PartnerSet]
					FROM [dbo].[mfr__*PartnerSet] as p 
					WHERE p.Property like 'ServiceRequest[:]%' AND EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = p.[EntityId]);
				DELETE cv
					OUTput deleted.*, @now INTO [dbo].[mfr__d_#customvalues]
					FROM [dbo].[mfr__#customvalues] as cv 
					WHERE cv.Property like 'ServiceRequest[:]%' AND EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = cv.[EntityId]);
				DELETE lc
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_#locations]
					FROM [dbo].[mfr__#locations] as lc 
					WHERE lc.Property like 'ServiceRequest[:]%' AND EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = lc.[EntityId]);
				DELETE stp
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_steps]
					FROM [dbo].[mfr__steps] as stp
					WHERE EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = stp.[ServiceRequestId] and ISNULL(stp.[StepListTemplateId],0) = 0);
				DELETE app
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_appointments]
					FROM [dbo].[mfr__appointments] as app
					WHERE EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = app.[ServiceRequestId]);
				DELETE itm
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_items]
					FROM [dbo].[mfr__items] as itm
					WHERE EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = itm.[ServiceRequestId] and ISNULL(itm.[ServiceobjectId],0) = 0);
				DELETE te
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_timeevents]
					FROM [dbo].[mfr__timeevents] as te
					WHERE EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = te.[ServiceRequestId]);
				DELETE sm
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_stockmovements]
					FROM [dbo].[mfr__stockmovements] as sm
					WHERE EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = sm.[ServiceRequestId]);
				DELETE cm
					OUTPUT deleted.*, @now INTO [dbo].[mfr__d_comments]
					FROM [dbo].[mfr__comments] as cm
					WHERE EXISTS (SELECT * FROM @tmp as tmp where tmp.[id] = cm.[ServiceRequestId] AND ISNULL(cm.[CompanyId],0) = 0);
			END


		MERGE [dbo].[mfr__servicerequests] as TARGET
			USING @tmp as SOURCE ON TARGET.[Id] = SOURCE.[Id]
		WHEN MATCHED AND (TARGET.[Version] <= SOURCE.[Version] OR TARGET.[DateModified] <= SOURCE.[DateModified]  OR TARGET.[DateOfCreation] <= SOURCE.[DateOfCreation] )  THEN
			UPDATE
				SET [Id] = SOURCE.[Id]
					  ,[Name] = SOURCE.[Name]
					  ,[ExternalId] = SOURCE.[ExternalId]
					  ,[InvoiceId] = SOURCE.[InvoiceId]
					  ,[ClosedAt] = SOURCE.[ClosedAt]
					  ,[ReleasedAt] = SOURCE.[ReleasedAt]
					  ,[WorkDoneAt] = SOURCE.[WorkDoneAt]
					  ,[TargetTimeInMinutes] = SOURCE.[TargetTimeInMinutes]
					  ,[DateModified] = SOURCE.[DateModified]
					  ,[DateOfCreation] = SOURCE.[DateOfCreation]
					  ,[DueDateRangeStart] = SOURCE.[DueDateRangeStart]
					  ,[DueDateRangeEnd] = SOURCE.[DueDateRangeEnd]
					  ,[PortalLink] = SOURCE.[PortalLink]
					  ,[CostCenterId] = SOURCE.[CostCenterId]
					  ,[Description] = SOURCE.[Description]
					  ,[State] = SOURCE.[State]
					  ,[CustomValues] = SOURCE.[CustomValues]
					  ,[CurrentOwnerId] = SOURCE.[CurrentOwnerId]
					  ,[CustomerId] = SOURCE.[CustomerId]
					  ,[ParentServiceRequestId] = SOURCE.[ParentServiceRequestId]
					  ,[Location#ID] = SOURCE.[Location#ID]
					  ,[Version] = SOURCE.[Version]
					  ,[IsTemplate] = SOURCE.[IsTemplate]
					  ,[IsTemplateMobile] = SOURCE.[IsTemplateMobile]
					  ,[CreateFromServiceRequestTemplateId] = SOURCE.[CreateFromServiceRequestTemplateId]
					  ,[Type] = SOURCE.[Type]
		WHEN NOT MATCHED BY TARGET THEN
			INSERT
				   ([Id]
					  ,[Name]
					  ,[ExternalId]
					  ,[InvoiceId]
					  ,[ClosedAt]
					  ,[ReleasedAt]
					  ,[WorkDoneAt]
					  ,[TargetTimeInMinutes]
					  ,[DateModified]
					  ,[DateOfCreation]
					  ,[DueDateRangeStart]
					  ,[DueDateRangeEnd]
					  ,[PortalLink]
					  ,[CostCenterId]
					  ,[Description]
					  ,[State]
					  ,[CustomValues]
					  ,[CurrentOwnerId]
					  ,[CustomerId]
					  ,[ParentServiceRequestId]
					  ,[Location#ID]
					  ,[Version]
					  ,[IsTemplate]
					  ,[IsTemplateMobile]
					  ,[CreateFromServiceRequestTemplateId]
					  ,[Type])
			 VALUES
					(SOURCE.[Id]
					  ,SOURCE.[Name]
					  ,SOURCE.[ExternalId]
					  ,SOURCE.[InvoiceId]
					  ,SOURCE.[ClosedAt]
					  ,SOURCE.[ReleasedAt]
					  ,SOURCE.[WorkDoneAt]
					  ,SOURCE.[TargetTimeInMinutes]
					  ,SOURCE.[DateModified]
					  ,SOURCE.[DateOfCreation]
					  ,SOURCE.[DueDateRangeStart]
					  ,SOURCE.[DueDateRangeEnd]
					  ,SOURCE.[PortalLink]
					  ,SOURCE.[CostCenterId]
					  ,SOURCE.[Description]
					  ,SOURCE.[State]
					  ,SOURCE.[CustomValues]
					  ,SOURCE.[CurrentOwnerId]
					  ,SOURCE.[CustomerId]
					  ,SOURCE.[ParentServiceRequestId]
					  ,SOURCE.[Location#ID]
					  ,SOURCE.[Version]
					  ,SOURCE.[IsTemplate]
					  ,SOURCE.[IsTemplateMobile]
					  ,SOURCE.[CreateFromServiceRequestTemplateId]
					  ,SOURCE.[Type]);
		
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		INSERT INTO dbo.[fds__admin_dberrors]
		VALUES (SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE());
		
		-- Transaction uncommittable
			--IF (XACT_STATE()) = -1
			  ROLLBACK TRANSACTION;
 
		-- Transaction committable
			--IF (XACT_STATE()) = 1
			  --COMMIT TRANSACTION;


		DECLARE @Message varchar(MAX) = ERROR_MESSAGE(), @Severity int = ERROR_SEVERITY(), @State smallint = ERROR_STATE();
		RAISERROR (@Message, @Severity, @State);

	END CATCH;
END