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>
399 lines
15 KiB
Transact-SQL
399 lines
15 KiB
Transact-SQL
|
|
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fds__getRequest_details]
|
|
@servicerequestid bigint
|
|
, @mode varchar(3)
|
|
, @authuser varchar(100)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF [dbo].[fis_getModuleAuth]('fds_req', @authuser) < 1
|
|
THROW 60000, N'not authorized', 1;
|
|
|
|
|
|
|
|
DECLARE @today date = GETDATE();
|
|
DECLARE @SReq TABLE (
|
|
[Id] [bigint] NOT NULL,
|
|
[Name] [nvarchar](255) NULL,
|
|
[ExternalId] [nvarchar](255) NULL,
|
|
[InvoiceId] [nvarchar](255) NULL,
|
|
[ClosedAt] [datetime] NULL,
|
|
[ReleasedAt] [datetime] NULL,
|
|
[WorkDoneAt] [datetime] NULL,
|
|
[TargetTimeInMinutes] [bigint] NOT 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) NOT 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] NOT NULL,
|
|
[IsTemplate] [bit] NOT NULL,
|
|
[IsTemplateMobile] [bit] NOT NULL,
|
|
[CreateFromServiceRequestTemplateId] [bigint] NOT NULL,
|
|
[Type] [nvarchar](255) NULL,
|
|
[Summary] nvarchar(255) NULL,
|
|
[isChild] bit NULL,
|
|
[baseorder] int,
|
|
[provision] varchar(255) NULL,
|
|
[open] bit 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 [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
|
|
,[open] = 1
|
|
FROM [dbo].[mfr__servicerequests] as s
|
|
WHERE [id] = @servicerequestid;
|
|
|
|
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] = null
|
|
,[open] = 1
|
|
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])
|
|
;
|
|
|
|
-- need to use CTE, because row_number is not available with update statements
|
|
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];
|
|
|
|
|
|
-- need to use CTE, because row_number is not available with update statements
|
|
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];
|
|
|
|
|
|
DECLARE @inv TABLE ([Id] bigint, [Invoices] varchar(1000), [isOpen] bit);
|
|
With fdsinv as(
|
|
SELECT [Id] = fdsr.[mfr__servicerequest], fdsi.[InvoiceId], fdsi.[InvoiceType], [isCanceled] = ISNULL(fdsi.[isCanceled],0)
|
|
FROM [dbo].[fds__invoice_servicerequests] as fdsr
|
|
JOIN [dbo].[fds__invoices] as fdsi on fdsr.[invid] = fdsi.[id]
|
|
JOIN @SReq as s ON fdsr.[mfr__servicerequest] = s.[id]
|
|
WHERE [isFinal] = 1 AND fdsr.[mfr__servicerequest] is not null
|
|
|
|
), fi as(
|
|
SELECT [Id], [isOpen] = MIN(CASE WHEN ISNULL([invoicetype],'r') = 'r' AND [isCanceled] = 0 THEN 0 ELSE 1 END) FROM fdsinv GROUP BY [Id]
|
|
)
|
|
INSERT INTO @inv
|
|
SELECT [Id]
|
|
, [invoices] = ISNULL(STUFF( (SELECT CHAR(10) + [InvoiceId] + ' (' + [InvoiceTYpe] + ')' FROM fdsinv as f2 WHERE f2.[id] = fi.[Id] FOR XML PATH, TYPE).value('.[1]','nvarchar(max)') ,1,1,'') , '')
|
|
, [isOpen]
|
|
FROM fi;
|
|
|
|
UPDATE s
|
|
SET s.[invoiceid] = [dbo].[fds__fn_InvoiceIDs_mfr](s.[invoiceid], inv.[Invoices])
|
|
,[open] = 1 --CASE WHEN s.[workdoneat] is not null AND [dbo].[fds__fn_InvoiceIDs_mfr](s.[invoiceid],'') = '' AND ISNULL(inv.[isOpen],1) = 1 THEN 1 ELSE 0 END
|
|
FROM @SReq as s LEFT JOIN @inv as inv on s.[id] = inv.[id];
|
|
|
|
|
|
|
|
|
|
DECLARE @step TABLE (
|
|
[Id] [bigint] NOT NULL,
|
|
[MobileId] [bigint] NULL,
|
|
[Version] [int] NOT NULL,
|
|
[Name] [nvarchar](255) NULL,
|
|
[IsDone] [bit] NOT NULL,
|
|
[HasError] [bit] NOT NULL,
|
|
[TrackingId] [nvarchar](255) NULL,
|
|
[Type] [nvarchar](255) NULL,
|
|
[SortOrder] [int] NOT NULL,
|
|
[Data] [nvarchar](max) NULL,
|
|
[DateModifiedOffline] [datetime] NULL,
|
|
[ServiceRequestId] [bigint] NULL,
|
|
[Description] [nvarchar](max) NULL,
|
|
[Comment] [nvarchar](max) NULL,
|
|
[InternalComment] [nvarchar](max) NULL,
|
|
[ServiceObjectId] [bigint] NULL,
|
|
[StepListTemplateId] [bigint] NULL,
|
|
[ParentId] [bigint] NULL,
|
|
[order] int NOT NULL
|
|
);
|
|
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;
|
|
|
|
/** OUTPUT + get other types **/
|
|
|
|
IF @mode in ('r','ov', '','f', 'ful')
|
|
BEGIN
|
|
|
|
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] = MAX (s.customerid)
|
|
, [ParentServiceRequestId] = ISNULL(MIN(CASE WHEN [isChild] = 1 then null else s.[Id] END), MAX([ParentServiceRequestId])) --make sure only one returned
|
|
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 [start] = [end] THEN NULL ELSE [start] END
|
|
, [provisionend] = [end]
|
|
, [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]
|
|
, [search] = @servicerequestid
|
|
, [parent] = app.[ParentServiceRequestId]
|
|
FROM (VALUES(CAST(GETDATE() as date)))base ([today]) CROSS JOIN app;
|
|
|
|
SELECT
|
|
*
|
|
,[order] = ROW_NUMBER() OVER (ORDER BY [baseorder])
|
|
FROM @SReq;
|
|
END
|
|
|
|
IF @mode in ('i','ov')
|
|
BEGIN
|
|
SELECT
|
|
itm.[Id]
|
|
, [net_pos] = CASE WHEN LOWER(itm.[type]) in ('Title','Text') THEN '' ELSE FORMAT( CAST( (ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0) as numeric(7,2)) , '#0.00 €','de') END
|
|
, [bo_pos] = CASE WHEN LOWER(itm.[type]) in ('Title','Text') THEN '' ELSE FORMAT( CAST( ((ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0)) * (1 + (ISNULL(vat.[vat], 19.0) * 0.01)) as numeric(7,2)) , '#0.00 €','de') END
|
|
, [vat] = CASE WHEN LOWER(itm.[type]) in ('Title','Text') THEN null ELSE FORMAT(ISNULL(vat.[vat], 19.0), '#0.0','de') + '%' END
|
|
, itm.[ServiceRequestId]
|
|
, itm.[SortOrder]
|
|
, itm.[Type]
|
|
, itm.[NameOrNumber]
|
|
, itm.[Note]
|
|
, [order] = (s.[baseorder] * 100) + ROW_NUMBER() OVER (PARTITION BY itm.[ServiceRequestId] ORDER BY itm.[SortOrder])
|
|
FROM [dbo].[mfr__items] as itm
|
|
JOIN @SReq as s ON itm.ServiceRequestId = s.[Id]
|
|
LEFT JOIN [dbo].[fds__custom_vat] as vat ON itm.[Id] = vat.[EntityId] and vat.[EntityType] = 'item'
|
|
;
|
|
END
|
|
ELSE IF @mode in ('f', 'ful')
|
|
BEGIN
|
|
SELECT
|
|
itm.[Id]
|
|
, [net] = ISNULL([Price],0)
|
|
, [quantityhours] = ISNULL([quantityhours],1)
|
|
, itm.[UnitString]
|
|
, [net_val] = CAST( (ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0) as numeric(7,2))
|
|
, [bo_val] = CAST( ((ISNULL([Price],0) * ISNULL([quantityhours],1)) - ISNULL([discount],0)) * (1 + (ISNULL(vat.[vat], 19.0) * 0.01)) as numeric(7,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(7,2))
|
|
, itm.[ServiceRequestId]
|
|
, itm.[SortOrder]
|
|
, itm.[Type]
|
|
, itm.[NameOrNumber]
|
|
, itm.[Note]
|
|
, [order] = (s.[baseorder] * 100) + ROW_NUMBER() OVER (PARTITION BY itm.[ServiceRequestId] ORDER BY itm.[SortOrder], itm.[Id])
|
|
FROM [dbo].[mfr__items] as itm
|
|
JOIN @SReq as s ON itm.ServiceRequestId = s.[Id]
|
|
LEFT JOIN [dbo].[fds__custom_vat] as vat ON itm.[Id] = vat.[EntityId] and vat.[EntityType] = 'item'
|
|
;
|
|
END
|
|
|
|
IF @mode in ('f', 'ful')
|
|
BEGIN
|
|
INSERT INTO @step
|
|
SELECT
|
|
stp.*
|
|
, [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
|
|
|
|
|
|
--get invoices
|
|
|
|
--Reminder settings
|
|
DECLARE @stage1 varchar(10), @stage2 varchar(10), @stage3 varchar(10);
|
|
SELECT @stage1 = [stage1], @stage2 = [stage2], @stage3 = [stage3] FROM [dbo].[fds__admin_reminderSettings]();
|
|
With inv as(
|
|
SELECT [id] = CAST(ir.invid as varchar(20)), [isChild] = CAST(MIN(CAST(s.[isChild] as tinyint)) as BIT), [baseorder] = MIN(s.[baseorder]), [sopen] = CAST( MIN( CAST(s.[open] as tinyint) ) as bit)
|
|
FROM [dbo].[fds__invoice_servicerequests] as ir
|
|
JOIN @sreq as s ON ir.[mfr__servicerequest] = s.[id]
|
|
GROUP by ir.invid
|
|
),reminder as (
|
|
SELECT [InvId], [lastReminder] = MAX([DateSent]), [#] = COUNT(*) FROM [dbo].[fds__reminder] WHERE [isSent] = 1 AND [invId] IN (SELECT [Id] FROM inv) GROUP BY [InvId]
|
|
)
|
|
SELECT [Id] = CAST(i.[Id] as varchar(20))
|
|
,i.[DateCreated]
|
|
,[isFinal]
|
|
,[isSent]
|
|
,i.[DocumentName]
|
|
,[invstatus] = CASE WHEN [IsPayed] = 1 AND [isCanceled] = 0 THEN 'pyd'
|
|
WHEN [isCanceled] = 1 OR [dbo].[fds__fn_isInvoiceCancelled](inv.[Id]) = 1 THEN 'cc'
|
|
WHEN [isFinal] = 0 THEN 'dft'
|
|
WHEN [isFinal] = 1 AND [isSent] = 0 THEN 'uns'
|
|
WHEN [DueDate] is null THEN 'op'
|
|
WHEN [invoiceBalance] < 0 THEN ''
|
|
WHEN @today >= [DueDate] THEN 'due'
|
|
WHEN @stage1 <> '' AND ISNULL(rem.[#],0) = 0 AND @today > [dbo].[date_addcustom](ISNULL(DueDate, i.[DateCreated]), @stage1) THEN 'ovd'
|
|
WHEN ISNULL(rem.[#],0) > 0 THEN 'rem'
|
|
END
|
|
,[order] = ROW_NUMBER() OVER ( ORDER BY inv.[isChild], inv.[baseorder], i.[isFinal] DESC, i.[datecreated] ASC )
|
|
FROM [dbo].[fds__invoices] as i
|
|
JOIN inv on i.[Id] = inv.[Id]
|
|
LEFT JOIN reminder as rem on inv.id = rem.[InvId]
|
|
WHERE (inv.[sopen] = 1 OR i.[isfinal] = 1) --- don't show any draft invoices for final tasks;
|
|
;
|
|
END |