69 lines
2.5 KiB
Transact-SQL
69 lines
2.5 KiB
Transact-SQL
USE [DD_ECM]
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- MK // 11.12.2025
|
|
|
|
CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_TF_OVERVIEW] (
|
|
@pUSER_ID BIGINT
|
|
)
|
|
RETURNS TABLE
|
|
AS
|
|
RETURN (
|
|
|
|
/*## Following Columns need to be part of sql ##
|
|
TL_STATE Returns the TrafficLight-State for each doc (1=Red;2=Orange;3=Green)
|
|
PROFILE_ID TITLE Profile-Title used for default-grouping
|
|
DOC_ID
|
|
FULL_FILE_PATH
|
|
LAST USE
|
|
LAST EDITED */
|
|
|
|
SELECT T.GUID,
|
|
DATEDIFF(DAY, [T].[DMS_ERSTELLT_DATE],GETDATE()) AS [Days in],
|
|
CASE
|
|
WHEN DATEDIFF(DAY, [T].[DMS_ERSTELLT_DATE],GETDATE()) < 4
|
|
THEN 3
|
|
WHEN DATEDIFF(DAY, [T].[DMS_ERSTELLT_DATE],GETDATE()) = 6
|
|
THEN 2
|
|
ELSE 1 END AS TL_STATE,
|
|
[T].[PROFIL_ID] AS PROFILE_ID,
|
|
[T].[DOC_ID] AS DocID,
|
|
[T].[FILE_PATH] AS FULL_FILE_PATH,
|
|
[TWM_BA].[szLongName] AS Dateiname,
|
|
[TWM_BA].[szText39] AS [Dokumentart],
|
|
CONVERT(DATETIME, convert(varchar(8),[TWM_BA].[dwdate08])) AS [Belegdatum],
|
|
[TWM_BA].[szText25] AS [Mandant],
|
|
[TWM_BA].[szText36] AS [Nr.Intern],
|
|
[TWM_BA].[szText37] AS [Nr.Extern],
|
|
[TWM_BA].[szText20] AS [Prüfer],
|
|
[TWM_BA].[szText35] AS [Personennummer],
|
|
[TWM_BA].[szText34] AS [Name],
|
|
CONVERT(DATETIME, convert(varchar(8),[TWM_BA].[dwdate06])) AS [Fällig am],
|
|
CASE WHEN (([TWM_BA].[lfFloat09] > 0) OR ([TWM_BA].[dwInteger23] > 0))
|
|
THEN concat([TWM_BA].[lfFloat09],'% / ',[TWM_BA].[dwInteger23],' Tage')
|
|
ELSE '---'
|
|
END AS [Skonto],
|
|
--CASE WHEN LEN([TWM_BA].[szText18]) > 0
|
|
-- THEN [TWM_BA].[szText18]
|
|
-- ELSE '---'
|
|
--END AS [Zlg.Bedingung],
|
|
[T].[DMS_ERSTELLT_DATE] AS [Erhalten wann],
|
|
[dbo].[FNPM_PROFILE_GROUP_COLOR] (T.PROFIL_ID) AS 'GROUP_COLOR',
|
|
[dbo].[FNPM_PROFILE_GROUP_TEXT] (T.PROFIL_ID) AS 'GROUP_TEXT'
|
|
|
|
FROM [TBPM_PROFILE_FILES] (NOLOCK) [T]
|
|
INNER JOIN [TBPM_PROFILE] (NOLOCK) [T1] ON [T].[PROFIL_ID] = [T1].[GUID]
|
|
INNER JOIN [windreamDB].[dbo].[BaseAttributes] (NOLOCK) [TWM_BA] ON [T].[DOC_ID] = [TWM_BA].[dwDocID]
|
|
|
|
WHERE [T].[EDIT] = 0
|
|
AND [T].[IN_WORK] = 0
|
|
AND [T1].[ACTIVE] = 1
|
|
AND [T1].[GUID] in (SELECT [PROFILE_ID] FROM [dbo].[FNPM_GET_ACTIVE_PROFILES_USER] (@pUSER_ID))
|
|
|
|
);
|
|
GO |