USE [DD_ECM] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- HE, MK // 05.11.2025 -- 05.03.2025 HE, MK Initial -- 05.11.2025 HE, MK Anpassungen wegen NAV Update CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_MS-DYN365BC_ORDER_DATA] ( @pMandatorShortName NVARCHAR(250), -- Default SWE @pOrderNr NVARCHAR(100)) RETURNS @ORDER_DATA TABLE ([GUID] [INT] IDENTITY(1,1) NOT NULL, [MandantenKurzname] [NVARCHAR](25) NOT NULL, [Version] [TINYINT] NOT NULL, [BestellNr] [NVARCHAR](100) NOT NULL, [LieferantenNr] [NVARCHAR](50) NOT NULL, [LieferantenName] [NVARCHAR](100) NOT NULL, [BestellReferenz] [NVARCHAR](100) NULL, [BestellDatum] [datetime2] NULL, [Buchungstext] [NVARCHAR](200) NULL, [Zlg.-Bedingung] [NVARCHAR](200) NULL, [BestellerName_DMSSync] [NVARCHAR](50) NULL, [SkontoProzent] [FLOAT] NULL, [SkontoTage] [TINYINT] NULL, [BestellWertNetto] [DECIMAL](20,2) NULL, [BestellWertBrutto] [DECIMAL](20,2) NULL) AS BEGIN -- declare new vars because of parameter sniffing DECLARE @MandatorShortName NVARCHAR(250) = UPPER(LTRIM(RTRIM(@pMandatorShortName))), @OrderNr NVARCHAR(100) = UPPER(LTRIM(RTRIM(@pOrderNr))), @VersionNo TINYINT = 0; IF (@MandatorShortName like 'SWE%') OR (@MandatorShortName like '%SWE%') OR (@MandatorShortName like '%SWE') BEGIN INSERT INTO @ORDER_DATA([MandantenKurzname],[Version],[BestellNr],[LieferantenNr],[LieferantenName],[BestellReferenz],[BestellDatum],[Buchungstext],[Zlg.-Bedingung],[BestellerName_DMSSync],[SkontoProzent],[SkontoTage],[BestellWertNetto],[BestellWertBrutto]) SELECT TOP 10000 @MandatorShortName as 'MandantenKurzname', 0 as 'Version', UPPER([PurchaseHeader].[No_]) as 'BestellNr', LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor No_])) as 'LieferantenNr', CASE WHEN LEN(LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor Name 2]))) > 0 THEN CONCAT((LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor Name]))),', ',(LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor Name 2])))) ELSE (LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor Name]))) END as 'LieferantenName', CASE WHEN LEN(TRIM([PurchaseHeader].[Your Reference])) > 0 THEN TRIM([PurchaseHeader].[Your Reference]) ELSE NULL END as 'BestellReferenz', [PurchaseHeader].[Order Date] as 'BestellDatum', LTRIM(RTRIM([PurchaseHeader].[Posting Description])) as 'Buchungstext', LTRIM(RTRIM([PaymentTerms].[Description])) as 'Zlg.-Bedingung', CASE WHEN (SELECT count([USERNAME]) FROM [TBDD_USER] WHERE [USERNAME] IN (SELECT [USER_NAME] FROM [FNCUST_GET_MS-DYN365BC_USER_DATA](@MandatorShortName,[PurchaseHeader$ext].[WLK Resource Requester$408c5b32-694e-47bb-96d5-dad7fc8111a7]))) > 0 THEN (SELECT [USER_NAME] FROM [FNCUST_GET_MS-DYN365BC_USER_DATA](@MandatorShortName,[PurchaseHeader$ext].[WLK Resource Requester$408c5b32-694e-47bb-96d5-dad7fc8111a7])) ELSE NULL END as 'BestellerName_DMSSync', convert(FLOAT,[PurchaseHeader].[Payment Discount _]) as 'SkontoProzent', --CASE WHEN ((DATEDIFF(DAY,[PurchaseHeader].[Posting Date],[PurchaseHeader].[Pmt_ Discount Date])) > 0) and ((DATEDIFF(DAY,[PurchaseHeader].[Posting Date],[PurchaseHeader].[Pmt_ Discount Date])) < 100) -- THEN DATEDIFF(DAY,[PurchaseHeader].[Posting Date],[PurchaseHeader].[Pmt_ Discount Date]) -- ELSE 0 --END as 'SkontoTage', CASE WHEN [PurchaseHeader].[Prepmt_ Payment Terms Code] like '%/%/%' THEN PARSENAME(REPLACE([PurchaseHeader].[Prepmt_ Payment Terms Code], '/', '.'), 2) ELSE 0 END as 'SkontoTage', convert(decimal(20,2),SUM([PurchaseLine].[Amount])) as 'BestellWertNetto', convert(decimal(20,2),SUM([PurchaseLine].[Amount Including VAT])) as 'BestellWertBrutto' FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PurchaseHeader] INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] as [PurchaseHeader$ext] ON [PurchaseHeader].[Document Type] = [PurchaseHeader$ext].[Document Type] AND [PurchaseHeader].[No_] = [PurchaseHeader$ext].[No_] INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Line$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PurchaseLine] ON [PurchaseHeader].[Document Type] = [PurchaseLine].[Document Type] AND [PurchaseHeader].[No_] = [PurchaseLine].[Document No_] AND [PurchaseLine].[Type] = 2 -- = Artikel LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Payment Terms$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PaymentTerms] ON [PurchaseHeader].[Payment Terms Code] = [PaymentTerms].[Code] WHERE [PurchaseHeader].[No_ Series] IN ('EK-BED','EK-BESTELL') AND UPPER([PurchaseHeader].[No_]) = @OrderNr GROUP BY UPPER([PurchaseHeader].[No_]), LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor No_])), LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor Name])), LTRIM(RTRIM([PurchaseHeader].[Buy-from Vendor Name 2])), TRIM([PurchaseHeader].[Your Reference]), [PurchaseHeader].[Order Date], LTRIM(RTRIM([PurchaseHeader].[Posting Description])), LTRIM(RTRIM([PaymentTerms].[Description])), [PurchaseHeader$ext].[WLK Resource Requester$408c5b32-694e-47bb-96d5-dad7fc8111a7], convert(FLOAT,[PurchaseHeader].[Payment Discount _]), [PurchaseHeader].[Posting Date], [PurchaseHeader].[Prepmt_ Payment Terms Code] IF ((SELECT count(*) FROM @ORDER_DATA) = 0 ) BEGIN SET @VersionNo = (SELECT MAX([Version No_]) FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header Archive$437dbf0e-84ff-417a-965d-ed2bb9650972] WHERE [No_ Series] IN ('EK-BED','EK-BESTELL') AND UPPER([No_]) = @OrderNr); INSERT INTO @ORDER_DATA([MandantenKurzname],[Version],[BestellNr],[LieferantenNr],[LieferantenName],[BestellReferenz],[BestellDatum],[Buchungstext],[Zlg.-Bedingung],[BestellerName_DMSSync],[SkontoProzent],[SkontoTage],[BestellWertNetto],[BestellWertBrutto]) SELECT TOP 10000 @MandatorShortName as 'MandantenKurzname', [PurchaseHeaderArchive].[Version No_] as 'Version', UPPER([PurchaseHeaderArchive].[No_]) as 'BestellNr', LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor No_])) as 'LieferantenNr', CASE WHEN LEN(LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor Name 2]))) > 0 THEN CONCAT((LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor Name]))),', ',(LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor Name 2])))) ELSE (LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor Name]))) END as 'LieferantenName', CASE WHEN LEN(TRIM([PurchaseHeaderArchive].[Your Reference])) > 0 THEN TRIM([PurchaseHeaderArchive].[Your Reference]) ELSE NULL END as 'BestellReferenz', [PurchaseHeaderArchive].[Order Date] as 'BestellDatum', LTRIM(RTRIM([PurchaseHeaderArchive].[Posting Description])) as 'Buchungstext', LTRIM(RTRIM([PaymentTerms].[Description])) as 'Zlg.-Bedingung', CASE WHEN (SELECT count([USERNAME]) FROM [TBDD_USER] WHERE [USERNAME] IN (SELECT [USER_NAME] FROM [FNCUST_GET_MS-DYN365BC_USER_DATA](@MandatorShortName,[PurchaseHeaderArchive$ext].[WLK Resource Requester$408c5b32-694e-47bb-96d5-dad7fc8111a7]))) > 0 THEN (SELECT [USER_NAME] FROM [FNCUST_GET_MS-DYN365BC_USER_DATA](@MandatorShortName,[PurchaseHeaderArchive$ext].[WLK Resource Requester$408c5b32-694e-47bb-96d5-dad7fc8111a7])) ELSE NULL END as 'BestellerName_DMSSync', convert(FLOAT,[PurchaseHeaderArchive].[Payment Discount _]) as 'SkontoProzent', --CASE WHEN ((DATEDIFF(DAY,[PurchaseHeaderArchive].[Posting Date],[PurchaseHeaderArchive].[Pmt_ Discount Date])) > 0) and ((DATEDIFF(DAY,[PurchaseHeaderArchive].[Posting Date],[PurchaseHeaderArchive].[Pmt_ Discount Date])) < 100) -- THEN DATEDIFF(DAY,[PurchaseHeaderArchive].[Posting Date],[PurchaseHeaderArchive].[Pmt_ Discount Date]) -- ELSE 0 --END as 'SkontoTage', CASE WHEN [PurchaseHeaderArchive].[Payment Terms Code] like '%/%/%' THEN PARSENAME(REPLACE([PurchaseHeaderArchive].[Payment Terms Code], '/', '.'), 2) ELSE 0 END as 'SkontoTage', convert(decimal(20,2),SUM([PurchaseLineArchive].[Amount])) as 'BestellWertNetto', convert(decimal(20,2),SUM([PurchaseLineArchive].[Amount Including VAT])) as 'BestellWertBrutto' FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header Archive$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PurchaseHeaderArchive] INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header Archive$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] as [PurchaseHeaderArchive$ext] ON [PurchaseHeaderArchive].[Document Type] = [PurchaseHeaderArchive$ext].[Document Type] AND [PurchaseHeaderArchive].[No_] = [PurchaseHeaderArchive$ext].[No_] INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Line Archive$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PurchaseLineArchive] ON [PurchaseHeaderArchive].[Document Type] = [PurchaseLineArchive].[Document Type] AND [PurchaseHeaderArchive].[No_] = [PurchaseLineArchive].[Document No_] AND [PurchaseLineArchive].[Type] = 2 LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Payment Terms$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PaymentTerms] ON [PurchaseHeaderArchive].[Payment Terms Code] = [PaymentTerms].[Code] WHERE [PurchaseHeaderArchive].[No_ Series] IN ('EK-BED','EK-BESTELL') AND UPPER([PurchaseHeaderArchive].[No_]) = @OrderNr AND [PurchaseHeaderArchive].[Version No_] = @VersionNo GROUP BY [PurchaseHeaderArchive].[Version No_], UPPER([PurchaseHeaderArchive].[No_]), LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor No_])), LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor Name])), LTRIM(RTRIM([PurchaseHeaderArchive].[Buy-from Vendor Name 2])), TRIM([PurchaseHeaderArchive].[Your Reference]), [PurchaseHeaderArchive].[Order Date], LTRIM(RTRIM([PurchaseHeaderArchive].[Posting Description])), LTRIM(RTRIM([PaymentTerms].[Description])), [PurchaseHeaderArchive$ext].[WLK Resource Requester$408c5b32-694e-47bb-96d5-dad7fc8111a7], convert(FLOAT,[PurchaseHeaderArchive].[Payment Discount _]), [PurchaseHeaderArchive].[Posting Date], [PurchaseHeaderArchive].[Payment Terms Code] END; --IF (@UserID > 0) BEGIN -- SELECT @BestellerName = CASE WHEN LTRIM(RTRIM([User Name])) like '%\%' -- THEN SUBSTRING(LTRIM(RTRIM([User Name])), CHARINDEX('\', LTRIM(RTRIM([User Name]))) + 1, LEN(LTRIM(RTRIM([User Name]))) - CHARINDEX('\', LTRIM(RTRIM([User Name])))) -- ELSE LTRIM(RTRIM([User Name])) -- END -- FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Employee$437dbf0e-84ff-417a-965d-ed2bb9650972] as [Employee] -- INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[User] as [User] ON [Employee].[$systemId] = [User].[$systemId] -- WHERE [Employee].[no_] = @UserID --END; ELSE BEGIN -- SET @BestellerName = NULL --END; END; RETURN; END; GO /*** -- Test select * FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PurchaseHeader] where [PurchaseHeader].[No_] in ('ERM0000052','ERM0000053') ***/