173 lines
12 KiB
Transact-SQL
173 lines
12 KiB
Transact-SQL
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')
|
|
|
|
***/ |