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>
470 lines
18 KiB
Transact-SQL
470 lines
18 KiB
Transact-SQL
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fds__prepInvoice]
|
|
@servicerequestid bigint
|
|
, @mode varchar(3)
|
|
, @authuser varchar(100)
|
|
, @type char(1)
|
|
, @sel varchar(1000)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF [dbo].[fis_getModuleAuth]('fds_inv', @authuser) < 2
|
|
THROW 60000, N'not authorized', 1;
|
|
|
|
INSERT INTO [dbo].[fds__admin_activity] ([activity] ,[authuser] ,[info])
|
|
VALUES ('fds__prepInvoice' ,@authuser , '');
|
|
|
|
|
|
DECLARE @today date = GETDATE();
|
|
|
|
SET @type = LOWER(ISNULL(@type, 'r'));
|
|
|
|
DECLARE @selReq as TABLE ([id] bigint);
|
|
IF ISNULL(@sel,'') = '' AND @servicerequestid is not null
|
|
SET @sel = CAST(@servicerequestid as varchar(1000));
|
|
INSERT INTO @selReq SELECT try_cast(t.[value] as bigint) FROM string_split(ISNULL(@sel,''),',') as t WHERE try_cast(t.[value] as bigint) is not null;
|
|
|
|
DECLARE @SReq TABLE (
|
|
[Id] [bigint] NULL,
|
|
[Name] [nvarchar](255) NULL,
|
|
[ExternalId] [nvarchar](255) NULL,
|
|
[InvoiceId] [nvarchar](255) NULL,
|
|
[ClosedAt] [datetime] NULL,
|
|
[ReleasedAt] [datetime] NULL,
|
|
[WorkDoneAt] [datetime] NULL,
|
|
[TargetTimeInMinutes] [bigint] NULL,
|
|
[DateModified] [datetime] NULL,
|
|
[DateOfCreation] [datetime] NULL,
|
|
[DueDateRangeStart] [datetime] NULL,
|
|
[DueDateRangeEnd] [datetime] NULL,
|
|
[PortalLink] [nvarchar](255) NULL,
|
|
[CostCenterId] [bigint] NULL,
|
|
[Description] [nvarchar](max) NULL,
|
|
[State] [nvarchar](255) NULL,
|
|
[CustomValues] [nvarchar](max) NULL,
|
|
[CurrentOwnerId] [bigint] NULL,
|
|
[CustomerId] [bigint] NULL,
|
|
[ParentServiceRequestId] [bigint] NULL,
|
|
[ParentExtenalId] [nvarchar](255) NULL,
|
|
[ParentWorkDoneAt] [date] NULL,
|
|
[ParentInvoiceId] [nvarchar](255) NULL,
|
|
[Location#ID] [bigint] NULL,
|
|
[Version] [int] NULL,
|
|
[IsTemplate] [bit] NULL,
|
|
[IsTemplateMobile] [bit] NULL,
|
|
[CreateFromServiceRequestTemplateId] [bigint] NULL,
|
|
[Type] [nvarchar](255) NULL,
|
|
[Summary] nvarchar(255) NULL,
|
|
[isChild] bit NULL,
|
|
[baseorder] int,
|
|
[provision] varchar(255) NULL
|
|
);
|
|
insert into @SReq
|
|
SELECT
|
|
[Id]
|
|
,[Name]
|
|
,[ExternalId]
|
|
,[InvoiceId]
|
|
,[ClosedAt]
|
|
,[ReleasedAt]
|
|
,[WorkDoneAt]
|
|
,[TargetTimeInMinutes]
|
|
,[DateModified]
|
|
,[DateOfCreation]
|
|
,[DueDateRangeStart]
|
|
,[DueDateRangeEnd]
|
|
,[PortalLink]
|
|
,[CostCenterId]
|
|
,[Description]
|
|
,[State]
|
|
,[CustomValues]
|
|
,[CurrentOwnerId]
|
|
,[CustomerId]
|
|
,[ParentServiceRequestId] = CASE WHEN ISNULL(s.[ParentServiceRequestId],0) = 0 then [id] ELSE s.[ParentServiceRequestId] END
|
|
,[ParentExtenalId] = cast(CASE WHEN ISNULL(s.[ParentServiceRequestId],0) = 0 then null ELSE '?' END as nvarchar(255))
|
|
,[ParentWorkDoneAt] = cast(NULL as datetime)
|
|
,[ParentInvoiceId] = cast(NULL as nvarchar(255))
|
|
,[Location#ID]
|
|
,[Version]
|
|
,[IsTemplate]
|
|
,[IsTemplateMobile]
|
|
,[CreateFromServiceRequestTemplateId]
|
|
,[Type]
|
|
,[Summary] = NULL
|
|
,[isChild] = CASE WHEN ISNULL(s.[ParentServiceRequestId],0) = 0 then 0 ELSE 1 END
|
|
,[baseorder] = 0
|
|
,[provision] = NULL
|
|
FROM [dbo].[mfr__servicerequests] as s
|
|
WHERE [id] = @servicerequestid;
|
|
|
|
IF @type <> 'i' -- for interim invoices, only one selected servicerequest is allowed -> childs and parents are ignored
|
|
BEGIN
|
|
INSERT INTO @SReq
|
|
SELECT s.[Id]
|
|
,s.[Name]
|
|
,s.[ExternalId]
|
|
,s.[InvoiceId]
|
|
,s.[ClosedAt]
|
|
,s.[ReleasedAt]
|
|
,s.[WorkDoneAt]
|
|
,s.[TargetTimeInMinutes]
|
|
,s.[DateModified]
|
|
,s.[DateOfCreation]
|
|
,s.[DueDateRangeStart]
|
|
,s.[DueDateRangeEnd]
|
|
,s.[PortalLink]
|
|
,s.[CostCenterId]
|
|
,s.[Description]
|
|
,s.[State]
|
|
,s.[CustomValues]
|
|
,s.[CurrentOwnerId]
|
|
,s.[CustomerId]
|
|
,s.[ParentServiceRequestId]
|
|
,[ParentExtenalId] = CASE WHEN s.[ParentServiceRequestId] = q.[id] THEN q.[ExternalId] ELSE NULL END
|
|
,[ParentWorkDoneAt] = CASE WHEN s.[ParentServiceRequestId] = q.[id] THEN q.[WorkDoneAt] ELSE NULL END
|
|
,[ParentInvoiceId] = CASE WHEN s.[ParentServiceRequestId] = q.[id] THEN q.[InvoiceId] ELSE NULL END
|
|
,s.[Location#ID]
|
|
,s.[Version]
|
|
,s.[IsTemplate]
|
|
,s.[IsTemplateMobile]
|
|
,s.[CreateFromServiceRequestTemplateId]
|
|
,s.[Type]
|
|
,[Summary] = NULL
|
|
,[isChild] = CASE WHEN ISNULL(s.[ParentServiceRequestId],0) = 0 then 0 ELSE 1 END
|
|
,[baseorder] = 0
|
|
, [provision_start] = null
|
|
from [dbo].[mfr__servicerequests] as s JOIN @SReq as q ON (s.[ParentServiceRequestId] = q.[id] OR q.[ParentServiceRequestId] = s.[id]) --parents or childs
|
|
WHERE NOT EXISTS (SELECT * FROM @SReq as s2 WHERE s2.[id] = s.[Id])
|
|
END;
|
|
|
|
|
|
|
|
WITH q as (
|
|
SELECT [id], [baseorder] = (ROW_NUMBER() OVER (ORDER BY ISNULL([ParentWorkDoneAt], [WorkDoneAt]), ISNULL([ParentServiceRequestId],[id])) * 100)
|
|
FROM @SReq as s
|
|
WHERE [ischild] = 0
|
|
)
|
|
UPDATE s SET
|
|
[baseorder] = q.baseorder
|
|
FROM @SReq as s JOIN q ON s.[id] = q.[id];
|
|
|
|
|
|
WITH q as (
|
|
SELECT s.[id]
|
|
, q.[ExternalId], q.[WorkDoneAt], q.[InvoiceId]
|
|
, [baseorder] = q.[baseorder] + ROW_NUMBER() OVER (PARTITION BY q.[id] ORDER BY s.[dateofcreation])
|
|
FROM @SReq as s JOIN @SReq as q ON s.[ParentServiceRequestId] = q.[id]
|
|
WHERE s.[ischild] = 1
|
|
)
|
|
UPDATE s SET
|
|
[ParentExtenalId] = q.[ExternalId]
|
|
,[ParentWorkDoneAt] = q.[WorkDoneAt]
|
|
,[ParentInvoiceId] = q.[InvoiceId]
|
|
,[baseorder] = q.[baseorder]
|
|
FROM @SReq as s JOIN q ON s.[Id] = q.[id];
|
|
|
|
IF @type <> 'i'
|
|
BEGIN
|
|
DELETE s FROM @SReq as s WHERE NOT EXISTS(SELECT * FROM @selReq as sel where sel.[id] = s.[Id])
|
|
END
|
|
|
|
|
|
|
|
DECLARE @step [dbo].[fds__tt__mfr_steps];
|
|
DECLARE @itm [dbo].[fds__tt__mfr_items];
|
|
DECLARE @company TABLE (
|
|
[Id] [bigint] NOT NULL,
|
|
[Location#ID] [bigint] NULL,
|
|
[Version] [int] NOT NULL,
|
|
[IsPhysicalPerson] [bit] NOT NULL,
|
|
[IsOwner] [bit] NOT NULL,
|
|
[IsEmailInvoicingActive] [bit] NOT NULL,
|
|
[IsSupplier] [bit] NOT NULL,
|
|
[MappingId] [nvarchar](255) NULL,
|
|
[ExternalId] [nvarchar](255) NULL,
|
|
[Name] [nvarchar](255) NULL,
|
|
[Note] [nvarchar](255) NULL,
|
|
[SupportTelephone] [nvarchar](255) NULL,
|
|
[SupportFax] [nvarchar](255) NULL,
|
|
[SupportMail] [nvarchar](255) NULL,
|
|
[QuickSearch] [nvarchar](255) NULL,
|
|
[DateModified] [datetime] NOT NULL,
|
|
[CustomValues] [nvarchar](max) NOT NULL,
|
|
[MainContactId] [bigint] NOT NULL,
|
|
[address] nvarchar(1000) null
|
|
);
|
|
INSERT INTO @company SELECT DISTINCT cy.*, [address] = [dbo].[fds__getCompanyAddress](cy.[id]) FROM [mfr__companies] as cy JOIN @SReq as s on cy.id = s.[CustomerId];
|
|
DECLARE @companyids [dbo].[fds__tt__idlist];
|
|
INSERT INTO @companyids SELECT DISTINCT [id] FROM @company;
|
|
|
|
|
|
INSERT INTO @itm
|
|
SELECT
|
|
itm.*
|
|
, [baseorder] = s.[baseorder], [order] = ROW_NUMBER() OVER (PARTITION BY [ServiceRequestId] ORDER BY [SortOrder], itm.[Id])
|
|
from [dbo].[mfr__items] as itm JOIN @SReq as s ON itm.ServiceRequestId = s.[Id]
|
|
where itm.[Type] <> 'PartialInvoice';
|
|
|
|
DECLARE @NUM_of_int_Invoices int = 0;
|
|
|
|
IF @type <> 'i'
|
|
BEGIN
|
|
|
|
|
|
-- Add virtual items for intermediate invoices
|
|
With associated_interims as (
|
|
SELECT inv.InvoiceVAT_1
|
|
,inv.InvoiceBalance_net
|
|
,inv.DocumentName
|
|
,inv.DateCreated
|
|
,inv.[dateFinalized]
|
|
,s.[baseorder]
|
|
,srq.[mfr__servicerequest]
|
|
,[payed] = ISNULL([dbo].[fds__fn_InvoicePaymentAmount](inv.Id),0.0)
|
|
,[open_amount] = CAST( ISNULL(inv.InvoiceBalance,0.0) - ISNULL([dbo].[fds__fn_InvoicePaymentAmount](inv.Id),0.0) as numeric(10,2))
|
|
,[open_amount_net] = CAST( ISNULL(inv.InvoiceBalance,0.0) - ISNULL([dbo].[fds__fn_InvoicePaymentAmount](inv.Id),0.0) as numeric(10,2)) * (100.0 / (100.0 + inv.[InvoiceVAT_1])) --interim invoices can only have one vat !!
|
|
from [dbo].[fds__invoices] as inv
|
|
JOIN [dbo].[fds__invoice_servicerequests] as srq ON inv.[Id] = srq.[InvId] AND inv.[InvoiceType] = 'i' --important
|
|
JOIN @SReq as s ON srq.[mfr__servicerequest] = s.[Id]
|
|
WHERE inv.[InvoiceType] = 'i' --important;
|
|
AND ISNULL(inv.[isFinal],0) = 1 AND ISNULL(inv.[IsCanceled],0) = 0
|
|
AND [dbo].[fds__fn_IntermediateIsAllocatedToOther](inv.[id],null) = 0 --inonly those intermediates that are not already allocated to any other
|
|
)
|
|
INSERT INTO @itm
|
|
SELECT
|
|
[Id] = -2
|
|
,[Version] = 0
|
|
,[QuantityHours] = 1
|
|
,[PlannedQuantityHours] = 1
|
|
,[ItemTypeId] = NULL
|
|
,[ItemNumber] = NULL
|
|
,[TrackingId] = NULL
|
|
,[Manufacture] = NULL
|
|
,[Price] = ai.InvoiceBalance_net * -1.0 --without VAT
|
|
,[Costs] = ai.InvoiceBalance_net * -1.0 --without VAT
|
|
,[NameOrNumber] = REPLACE(ai.DocumentName, '.pdf','')
|
|
,[CustomValues] = NULL
|
|
,[Note] = '<p>' + 'Anrechnung der ' + REPLACE(ai.DocumentName, '.pdf','') + ' vom ' + FORMAT(ai.[dateFinalized], 'dd.MM.yyyy', 'de-de') + '</p>'
|
|
,[ExternalId] = NULL
|
|
,[Discount] = NULL
|
|
,[VAT] = ai.InvoiceVAT_1
|
|
,[IsManual] = 1
|
|
,[SortOrder] = ROW_NUMBER() OVER (PARTITION BY ai.[mfr__servicerequest] ORDER BY ai.[dateFinalized], ai.DateCreated)
|
|
,[Type] = 'IntInvoiceAllocation'
|
|
,[ServiceRequestId] = -2 --s.[id]
|
|
,[ServiceObjectId] = Null
|
|
,[CreatorId] = NULL
|
|
,[UnitId] = 15825600513
|
|
,[UnitString] = 'Pauschal'
|
|
,[baseorder] = ai.[baseorder]
|
|
, [order] = 10000 + (ROW_NUMBER() OVER (PARTITION BY ai.[mfr__servicerequest] ORDER BY ai.[dateFinalized], ai.DateCreated) * 10)
|
|
FROM associated_interims as ai
|
|
UNION
|
|
SELECT
|
|
[Id] = -3
|
|
,[Version] = 0
|
|
,[QuantityHours] = 1
|
|
,[PlannedQuantityHours] = 1
|
|
,[ItemTypeId] = NULL
|
|
,[ItemNumber] = NULL
|
|
,[TrackingId] = NULL
|
|
,[Manufacture] = NULL
|
|
,[Price] = ai.open_amount_net --without VAT ! positive, because it's a charge
|
|
,[Costs] = ai.open_amount_net --without VAT ! positive, because it's a charge
|
|
,[NameOrNumber] = 'offener Betrag aus ' + REPLACE(ai.DocumentName, '.pdf','')
|
|
,[CustomValues] = NULL
|
|
,[Note] = '<p>' + 'Berechnung des offenen Betrags aus ' + REPLACE(ai.DocumentName, '.pdf','') + ' vom ' + FORMAT(ai.[dateFinalized], 'dd.MM.yyyy', 'de-de') + '</p><p>bereits gezahlt (brutto): ' + FORMAT([payed], '0.00', 'de') + '€.</p>'
|
|
,[ExternalId] = NULL
|
|
,[Discount] = NULL
|
|
,[VAT] = ai.InvoiceVAT_1
|
|
,[IsManual] = 1
|
|
,[SortOrder] = ROW_NUMBER() OVER (PARTITION BY ai.[mfr__servicerequest] ORDER BY ai.[dateFinalized], ai.DateCreated)
|
|
,[Type] = 'IntInvoiceAllocationDiff'
|
|
,[ServiceRequestId] = -2 --s.[id]
|
|
,[ServiceObjectId] = Null
|
|
,[CreatorId] = NULL
|
|
,[UnitId] = 15825600513
|
|
,[UnitString] = 'Pauschal'
|
|
,[baseorder] = ai.[baseorder]
|
|
, [order] = 10001 + (ROW_NUMBER() OVER (PARTITION BY ai.[mfr__servicerequest] ORDER BY ai.[dateFinalized], ai.DateCreated) * 10)
|
|
FROM associated_interims as ai
|
|
WHERE ISNULL(ai.open_amount,0.0) > 0.0;
|
|
|
|
IF EXISTS (SELECT * FROM @itm WHERE [ServiceRequestId] = -2)
|
|
BEGIN
|
|
-- Add virtual request
|
|
INSERT INTO @SReq ([Id]
|
|
,[Name]
|
|
,[Type]
|
|
,[isChild]
|
|
,[baseorder])
|
|
VALUES(-2
|
|
,'Abschlagszahlungen'
|
|
,'vrii'
|
|
,0
|
|
,999);
|
|
|
|
SET @type = 'f';
|
|
|
|
END
|
|
|
|
END;
|
|
ELSE
|
|
BEGIN
|
|
SET @NUM_of_int_Invoices = ISNULL( (SELECT COUNT(*) FROM (SELECT DISTINCT inv.id FROM [dbo].[fds__invoices] as inv
|
|
JOIN [dbo].[fds__invoice_servicerequests] as srq ON inv.[Id] = srq.[InvId] AND inv.[InvoiceType] = 'i' --important
|
|
JOIN @SReq as s ON srq.[mfr__servicerequest] = s.[Id]
|
|
WHERE inv.[InvoiceType] = 'i' --important;
|
|
AND [dbo].[fds__fn_isInvoiceCancelled](inv.id) = 0
|
|
AND inv.[isFinal] = 1)y
|
|
) , 0);
|
|
END;
|
|
|
|
|
|
/** OUTPUT + get other types **/
|
|
|
|
|
|
WITH app as(
|
|
SELECT [start] = MIN(CAST(CASE WHEN a.[StartDateTime] is null THEN s.[workdoneat] WHEN CAST(a.[StartDateTime] as date) < s.[workdoneat] THEN CAST(a.[StartDateTime] as date) ELSE s.[workdoneat] END as date))
|
|
, [end] = MAX(CAST(CASE WHEN a.[EndDateTime] is null THEN s.[workdoneat] WHEN CAST(a.[EndDateTime] as date) < s.[workdoneat] THEN CAST(a.[EndDateTime] as date) ELSE s.[workdoneat] END as date))
|
|
, [CustomerId] = MIN (s.customerid)
|
|
FROM [dbo].[mfr__appointments] as a
|
|
RIGHT JOIN @SReq as s on a.[ServiceRequestId] = s.[Id] AND a.[State] in ('IsWorkDone')
|
|
)
|
|
SELECT TOP(1)
|
|
[today]
|
|
, [provisionstart] = CASe WHEN @type = 'i' THEN NULL WHEN [start] = [end] THEN NULL ELSE [start] END
|
|
, [provisionend] = CASe WHEN @type = 'i' THEN @today ELSE [end] END -- Abschlagsrechnungen immer mit Tagesdatum
|
|
, [provisionlocation] = N''
|
|
, [sender] = N'Sebastian Fuchs GmbH & Co. KG ● Germaniastraße 15 ● 40223 Düsseldorf'
|
|
, [paymentterms] = N'10wd'
|
|
, [invoiceemail] = (SELECT TOP(1) [SupportMail] FROM @company where IsEmailInvoicingActive = 1)
|
|
, [invoiceaddress] = (SELECT TOP(1) CONCAT([name], CHAR(10), [address]) FROM @company ORDER BY IsEmailInvoicingActive DESC)
|
|
, [tax_servicerefund] = 0.2
|
|
, [CustomerId] = [CustomerId]
|
|
, [invoicetitle] = CASE WHEN @type = 'i' THEN (CASE WHEN @NUM_of_int_Invoices > 0 THEN CAST((@NUM_of_int_Invoices + 1) as varchar(3)) + '. ' ELSE '' END) + 'Abschlagsrechnung'
|
|
WHEN @type = 'f' THEN 'Schlussrechnung' WHEN @type = 'c' THEN 'Stornorechnung' ELSE 'Rechnung' END
|
|
, [type] = @type
|
|
FROM (VALUES(CAST(GETDATE() as date)))base ([today]) CROSS JOIN app;
|
|
|
|
SELECT
|
|
*
|
|
,[order] = ROW_NUMBER() OVER (ORDER BY [baseorder])
|
|
FROM @SReq;
|
|
|
|
|
|
|
|
IF @type <> 'i'
|
|
BEGIN
|
|
SELECT
|
|
itm.[Id]
|
|
, [net] = ISNULL([Price],0)
|
|
, [quantityhours] = ISNULL([quantityhours],1)
|
|
, itm.[UnitString]
|
|
, [net_val] = [dbo].[net_val]([Price],[quantityhours], [discount])
|
|
, [bo_val] = [dbo].[bo_val]([Price],[quantityhours], [discount], vat.[vat])
|
|
, [vat] = CASE WHEN LOWER(itm.[type]) in ('Title','Text') THEN null ELSE FORMAT(ISNULL(vat.[vat], 19.0), '#0.0','de') + '%' END
|
|
, [vat_val] = [dbo].[vat_val]([Price],[quantityhours],[discount],vat.[vat])
|
|
--, [net_val] = CAST( (ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0) as numeric(10,2))
|
|
--, [bo_val] = CAST( ((ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0)) * (1 + (ISNULL(vat.[vat], 19.0) * 0.01)) as numeric(10,2))
|
|
--, [vat] = CASE WHEN LOWER(itm.[type]) in ('Title','Text') THEN null ELSE FORMAT(ISNULL(vat.[vat], 19.0), '#0.0','de') + '%' END
|
|
--, [vat_val] = CAST( ((ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0)) * ((ISNULL(vat.[vat], 19.0) * 0.01)) as numeric(10,2))
|
|
, itm.[ServiceRequestId]
|
|
, itm.[SortOrder]
|
|
, itm.[Type]
|
|
, itm.[NameOrNumber]
|
|
, [Note] = IIF(ISNULL([Discount], 0) > 0, '<b>Rabatt '+ FORMAT([discount]*0.01,'#.0%','de-de') + '</b>', '') + ISNULL(itm.[Note],'')
|
|
, [order] = (itm.[baseorder] * 100) + itm.[order]
|
|
FROM @itm as itm
|
|
LEFT JOIN [dbo].[fds__custom_vat] as vat ON itm.[Id] = vat.[EntityId] and vat.[EntityType] = 'item';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
WITH citm as (
|
|
SELECT itm.[ServiceRequestId], [net_val] = SUM( [dbo].[net_val]([Price],[quantityhours],[discount]) )
|
|
FROM @itm as itm
|
|
WHERE itm.[Type] = 'Material'
|
|
GROUP BY itm.[ServiceRequestId]
|
|
)
|
|
SELECT
|
|
[Id] = -1
|
|
, [net] = 1000.0
|
|
, [quantityhours] = 1
|
|
, [UnitString] = 'Pauschal'
|
|
, [net_val] = CAST( 1000.0 as numeric(10,2))
|
|
, [bo_val] = CAST( 1000.0 * 1.19 as numeric(10,2))
|
|
, [vat] = FORMAT( 19.0, '#0.0','de') + '%'
|
|
, [vat_val] = CAST( 1000.0 * 0.19 as numeric(10,2))
|
|
, [ServiceRequestId] = s.[id]
|
|
, [SortOrder] = 0
|
|
, [Type] = 'PartialInvoice'
|
|
, [NameOrNumber] = 'Abschlagsbetrag für bereits erbrachte Arbeiten, Dienstleistungen, Materiallieferungen und getätigte Bestellvorgänge zum oben genannten Bauvorhaben'
|
|
, [Note] = ''
|
|
, [order] = 1
|
|
FROM @SReq as s;
|
|
|
|
END
|
|
|
|
|
|
|
|
INSERT INTO @step
|
|
SELECT
|
|
stp.*
|
|
, s.[baseorder]
|
|
, [order] = (s.[baseorder] * 100) + ROW_NUMBER() OVER (PARTITION BY stp.[ServiceRequestId] ORDER BY stp.[SortOrder], stp.[Id])
|
|
FROM [dbo].[mfr__steps] as stp JOIN @SReq as s ON stp.ServiceRequestId = s.[Id];
|
|
SELECT * from @step;
|
|
|
|
|
|
|
|
SELECT
|
|
cc.[Id]
|
|
, [CompanyName] = cc.[name]
|
|
, ct.[Gender]
|
|
, ct.[FirstName]
|
|
, ct.[LastName]
|
|
, ct.[Email]
|
|
, cc.[address]
|
|
FROM [dbo].[fds__getCompanysContacts](@companyids) as ct join @company as cc on cc.[Id] = ct.[CyId];
|
|
|
|
|
|
|
|
SELECT DISTINCT
|
|
[ref] = 'Einsatzort ' + so.[name]
|
|
,[address] = [dbo].[fds__fn_combineAddress] (so.[Name], loc.[AddressString], null
|
|
,loc.[Postal]
|
|
,loc.[City]
|
|
,loc.[State]
|
|
,loc.[Country])
|
|
FROM @SReq as s
|
|
LEFT JOIN [mfr__*PartnerSet] as p ON p.property = 'ServiceRequest:ServiceObjects' and p.[EntityId] = s.[id]
|
|
LEFT JOIN [mfr__serviceobjects] as so ON so.id = p.[PartnerId]
|
|
LEFT JOIN [mfr__*PartnerSet] as po ON p.property = 'ServiceObject:Location' and po.[EntityId] = so.[Id]
|
|
LEFT join [dbo].[mfr__#locations] as loc ON (loc.[id] = so.[Location#ID] OR po.[PartnerId] = loc.[Id] OR (so.[Id] = loc.[EntityId] and loc.Property = 'ServiceObject:Location'))
|
|
WHERE so.id is not null and loc.[id] is not null
|
|
UNION
|
|
SELECT DISTINCT
|
|
[ref] = 'Auftragsort ' + [name]
|
|
,[address] = [dbo].[fds__fn_combineAddress] (loc.[AddressString], null, null
|
|
,loc.[Postal]
|
|
,loc.[City]
|
|
,loc.[State]
|
|
,loc.[Country])
|
|
FROM @SReq as s
|
|
LEFT JOIN [mfr__*PartnerSet] as p ON p.property = 'ServiceRequest:Location' and p.[EntityId] = s.[id]
|
|
join [dbo].[mfr__#locations] as loc ON (s.[Location#ID] = loc.[id] OR p.[PartnerId] = loc.[Id] OR (s.[Id] = loc.[EntityId] and loc.Property = 'ServiceRequest:Location'))
|
|
WHERE loc.[id] is not null
|
|
UNION
|
|
SELECT DISTINCT [ref] = 'Kunde'
|
|
, [address]
|
|
FROM @company;
|
|
|
|
|
|
|
|
END |