Stefan
10ecdfa2e4
Adds the SQL Server Data Tools project for the fuchs_fds database — tables, table types, functions and stored procedures that the backend calls (e.g. fds__getInvoice, fds__merge_bankingtransactions, fds__tt__bankingtransactions, fds__admin_getReportCatalog, fis_* auth). Build/model caches (bin, obj, *.dbmdl, *.jfm, *.user) are git-ignored. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
41 lines
1.4 KiB
Transact-SQL
41 lines
1.4 KiB
Transact-SQL
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fds__maint__updateCancelledStatus]
|
|
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @now datetime = GETUTCDATE();
|
|
DECLARE @log varchar(100) = 'DateCancelled Set: ' + FORMAT(@now,'yyyy-MM-ddTHH:mm:ssZ') + ';'
|
|
DECLARE @out TABLE([InvId] varchar(10), [log] varchar(100));
|
|
|
|
--select [id],[invoiceid],[DocumentName],[IsCanceled],[datefinalized]
|
|
UPDATE inv
|
|
SET inv.[Version] = inv.[version] + 1
|
|
, inv.[DateCancelled] = storno_inv.[DateFinalized]
|
|
, inv.[UserCancelled] = storno_inv.[UserFinalized]
|
|
OUTPUT inserted.id, @log
|
|
INTO @out
|
|
FROM [dbo].[fds__invoices] as inv
|
|
JOIN fds__invoice_details as storno_details on storno_details.[StornoTo_InvId] = inv.[id]
|
|
JOIN fds__invoices as storno_inv on storno_details.[InvId] = storno_inv.[id] and storno_inv.[isFinal] = 1 and storno_inv.[datefinalized] is not null
|
|
WHERE inv.[IsCanceled] = 0
|
|
AND inv.[DateCancelled] is null
|
|
;
|
|
|
|
|
|
MERGE [dbo].[fds__invoice_details] as TARGET
|
|
USING @out as SRC ON TARGET.[InvID] = SRC.[InvId]
|
|
WHEN MATCHED THEN
|
|
UPDATE
|
|
SET TARGET.[log] = ISNULL(TARGET.[log], '') + SRC.[log]
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT ([InvId] ,[log])
|
|
VALUES (SRC.[InvId], SRC.[log])
|
|
;
|
|
|
|
END |