USE [DD_ECM] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- HE, MK // 04.03.2026 -- 04.03.2026 HE, MK Initial CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_MS-DYN365BC_VENDOR_INFO] ( @pMANDATOR NVARCHAR(15), @pUSE_LIKE_SEARCH BIT, @pVENDOR_NUMBER NVARCHAR(20), @pVENDOR_NAME NVARCHAR(100), @pVENDOR_SEARCH_NAME NVARCHAR(100), @pVENDOR_NAME2 NVARCHAR(50), @pVAT_REGISTRATION_NUMBER NVARCHAR(50), @pIBAN NVARCHAR(50) ) RETURNS @vTB_VENDOR_INFO TABLE ([GUID] [BIGINT] IDENTITY(1,1) NOT NULL, [No_] [NVARCHAR](20) NOT NULL, [Name] [NVARCHAR](100) NOT NULL, [Search Name] [NVARCHAR](100) NULL, [Name 2] [NVARCHAR](50) NULL, [Address] [NVARCHAR](100) NULL, [Address 2] [NVARCHAR](50) NULL, [City] [NVARCHAR](30) NULL, [Contact] [NVARCHAR](100) NULL, [Phone No_] [NVARCHAR](30) NULL, [Our Account No_] [NVARCHAR](20) NULL, [Vendor Posting Group] [NVARCHAR](20) NULL, [Payment Terms Code] [NVARCHAR](10) NULL, [Purchaser Code] [NVARCHAR](20) NULL, [Invoice Disc_ Code] [NVARCHAR](20) NULL, [Country_Region Code] [NVARCHAR](10) NULL, [Blocked] [INT] NOT NULL, [Pay-to Vendor No_] [NVARCHAR](20) NULL, [Payment Method Code] [NVARCHAR](10) NULL, [Prices Including VAT] [TINYINT] NULL, [Fax No_] [NVARCHAR](30) NULL, [VAT Registration No_] [NVARCHAR](20) NULL, [Gen_ Bus_ Posting Group] [NVARCHAR](20) NULL, [GLN] [NVARCHAR](13) NULL, [Post Code] [NVARCHAR](20) NULL, [County] [NVARCHAR](30) NULL, [E-Mail] [NVARCHAR](80) NULL, [Home Page] [NVARCHAR](80) NULL, [No_ Series] [NVARCHAR](20) NULL, [VAT Bus_ Posting Group] [NVARCHAR](20) NULL, [Primary Contact No_] [NVARCHAR](20) NULL, [Document Sending Profile] [NVARCHAR](20) NULL, [IBAN] [NVARCHAR](50) NULL, [Payment Terms Due Date Calculation] [VARCHAR](32) NULL, [Payment Terms Discount Date Calculation] [VARCHAR](32) NULL, [Payment Terms Discount _] [FLOAT] NULL, [Payment Terms Description] [NVARCHAR](100) NULL) AS BEGIN -- declare new vars because of parameter sniffing DECLARE @MANDATOR NVARCHAR(15) = UPPER(LTRIM(RTRIM(@pMANDATOR))), @USE_LIKE_SEARCH BIT = ISNULL(@pUSE_LIKE_SEARCH,0), @VENDOR_NUMBER NVARCHAR(20) = LTRIM(RTRIM(@pVENDOR_NUMBER)), @VENDOR_NAME NVARCHAR(100) = LTRIM(RTRIM(@pVENDOR_NAME)), @VENDOR_SEARCH_NAME NVARCHAR(100) = LTRIM(RTRIM(@pVENDOR_SEARCH_NAME)), @VENDOR_NAME2 NVARCHAR(50) = LTRIM(RTRIM(@pVENDOR_NAME2)), @VAT_REGISTRATION_NUMBER NVARCHAR(50) = LTRIM(RTRIM(@pVAT_REGISTRATION_NUMBER)), @IBAN NVARCHAR(50) = REPLACE(@pIBAN,' ',''); -- declare runtime vars DECLARE @MY_FUNCTION_NAME NVARCHAR(128) = OBJECT_NAME(@@PROCID); DECLARE @FUNCTIONAL_CHARACTER NVARCHAR(1) = char(2); IF ((@MANDATOR = 'SWE') AND (@USE_LIKE_SEARCH = 0)) BEGIN INSERT INTO @vTB_VENDOR_INFO([No_], [Name], [Search Name], [Name 2], [Address], [Address 2], [City], [Contact], [Phone No_], [Our Account No_], [Vendor Posting Group], [Payment Terms Code], [Purchaser Code], [Invoice Disc_ Code], [Country_Region Code], [Blocked], [Pay-to Vendor No_], [Payment Method Code], [Prices Including VAT], [Fax No_], [VAT Registration No_], [Gen_ Bus_ Posting Group], [GLN], [Post Code], [County], [E-Mail], [Home Page], [No_ Series], [VAT Bus_ Posting Group], [Primary Contact No_], [Document Sending Profile], [IBAN], [Payment Terms Due Date Calculation], [Payment Terms Discount Date Calculation], [Payment Terms Discount _], [Payment Terms Description]) SELECT DISTINCT [Vendor].[No_] ,[Vendor].[Name] ,[Vendor].[Search Name] ,[Vendor].[Name 2] ,[Vendor].[Address] ,[Vendor].[Address 2] ,[Vendor].[City] ,[Vendor].[Contact] ,[Vendor].[Phone No_] ,[Vendor].[Our Account No_] ,[Vendor].[Vendor Posting Group] ,[Vendor].[Payment Terms Code] ,[Vendor].[Purchaser Code] ,[Vendor].[Invoice Disc_ Code] ,[Vendor].[Country_Region Code] ,[Vendor].[Blocked] ,[Vendor].[Pay-to Vendor No_] ,[Vendor].[Payment Method Code] ,[Vendor].[Prices Including VAT] ,[Vendor].[Fax No_] ,[Vendor].[VAT Registration No_] ,[Vendor].[Gen_ Bus_ Posting Group] ,[Vendor].[GLN] ,[Vendor].[Post Code] ,[Vendor].[County] ,[Vendor].[E-Mail] ,[Vendor].[Home Page] ,[Vendor].[No_ Series] ,[Vendor].[VAT Bus_ Posting Group] ,[Vendor].[Primary Contact No_] ,[Vendor].[Document Sending Profile] ,CASE WHEN (SELECT COUNT(*) FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] WHERE [Vendor No_] = [Vendor].[No_] AND [WLK Clearing$408c5b32-694e-47bb-96d5-dad7fc8111a7] = 1) = 1 THEN (SELECT [IBAN] FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] WHERE [Vendor No_] = [Vendor].[No_] AND [CODE] = (SELECT [Code] FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] WHERE [Vendor No_] = [Vendor].[No_] AND [WLK Clearing$408c5b32-694e-47bb-96d5-dad7fc8111a7] = 1)) ELSE @IBAN END ,REPLACE([PaymentTerms].[Due Date Calculation],@FUNCTIONAL_CHARACTER,'') ,REPLACE([PaymentTerms].[Discount Date Calculation],@FUNCTIONAL_CHARACTER,'') ,CONVERT(FLOAT,(CONVERT(DECIMAL(3,2),[PaymentTerms].[Discount _]))) ,[PaymentTerms].[Description] FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] as [Vendor] LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] as [VendorBankAccount] ON [Vendor].[No_] = [VendorBankAccount].[Vendor No_] LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Payment Terms$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PaymentTerms] ON [Vendor].[Payment Terms Code] = [PaymentTerms].[Code] WHERE (@VENDOR_NUMBER IS NULL OR [Vendor].[No_] = @VENDOR_NUMBER) AND (@VENDOR_NAME IS NULL OR [Vendor].[Name] = @VENDOR_NAME) AND (@VENDOR_SEARCH_NAME IS NULL OR [Vendor].[Search Name] = @VENDOR_SEARCH_NAME) AND (@VENDOR_NAME2 IS NULL OR [Vendor].[Name 2] = @VENDOR_NAME2) AND (@VAT_REGISTRATION_NUMBER IS NULL OR [Vendor].[VAT Registration No_] = @VAT_REGISTRATION_NUMBER) AND (@IBAN IS NULL OR REPLACE([VendorBankAccount].[IBAN],' ','') = @IBAN); END ELSE IF ((@MANDATOR = 'SWE') AND (@USE_LIKE_SEARCH = 1)) BEGIN INSERT INTO @vTB_VENDOR_INFO([No_], [Name], [Search Name], [Name 2], [Address], [Address 2], [City], [Contact], [Phone No_], [Our Account No_], [Vendor Posting Group], [Payment Terms Code], [Purchaser Code], [Invoice Disc_ Code], [Country_Region Code], [Blocked], [Pay-to Vendor No_], [Payment Method Code], [Prices Including VAT], [Fax No_], [VAT Registration No_], [Gen_ Bus_ Posting Group], [GLN], [Post Code], [County], [E-Mail], [Home Page], [No_ Series], [VAT Bus_ Posting Group], [Primary Contact No_], [Document Sending Profile], [IBAN], [Payment Terms Due Date Calculation], [Payment Terms Discount Date Calculation], [Payment Terms Discount _], [Payment Terms Description]) SELECT DISTINCT [Vendor].[No_] ,[Vendor].[Name] ,[Vendor].[Search Name] ,[Vendor].[Name 2] ,[Vendor].[Address] ,[Vendor].[Address 2] ,[Vendor].[City] ,[Vendor].[Contact] ,[Vendor].[Phone No_] ,[Vendor].[Our Account No_] ,[Vendor].[Vendor Posting Group] ,[Vendor].[Payment Terms Code] ,[Vendor].[Purchaser Code] ,[Vendor].[Invoice Disc_ Code] ,[Vendor].[Country_Region Code] ,[Vendor].[Blocked] ,[Vendor].[Pay-to Vendor No_] ,[Vendor].[Payment Method Code] ,[Vendor].[Prices Including VAT] ,[Vendor].[Fax No_] ,[Vendor].[VAT Registration No_] ,[Vendor].[Gen_ Bus_ Posting Group] ,[Vendor].[GLN] ,[Vendor].[Post Code] ,[Vendor].[County] ,[Vendor].[E-Mail] ,[Vendor].[Home Page] ,[Vendor].[No_ Series] ,[Vendor].[VAT Bus_ Posting Group] ,[Vendor].[Primary Contact No_] ,[Vendor].[Document Sending Profile] ,CASE WHEN (SELECT COUNT(*) FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] WHERE [Vendor No_] = [Vendor].[No_] AND [WLK Clearing$408c5b32-694e-47bb-96d5-dad7fc8111a7] = 1) = 1 THEN (SELECT [IBAN] FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] WHERE [Vendor No_] = [Vendor].[No_] AND [CODE] = (SELECT [Code] FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] WHERE [Vendor No_] = [Vendor].[No_] AND [WLK Clearing$408c5b32-694e-47bb-96d5-dad7fc8111a7] = 1)) ELSE @IBAN END ,REPLACE([PaymentTerms].[Due Date Calculation],@FUNCTIONAL_CHARACTER,'') ,REPLACE([PaymentTerms].[Discount Date Calculation],@FUNCTIONAL_CHARACTER,'') ,CONVERT(FLOAT,(CONVERT(DECIMAL(3,2),[PaymentTerms].[Discount _]))) ,[PaymentTerms].[Description] FROM [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] as [Vendor] LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] as [VendorBankAccount] ON [Vendor].[No_] = [VendorBankAccount].[Vendor No_] LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Payment Terms$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PaymentTerms] ON [Vendor].[Payment Terms Code] = [PaymentTerms].[Code] WHERE (@VENDOR_NUMBER IS NULL OR [Vendor].[No_] LIKE CONCAT('%',@VENDOR_NUMBER,'%')) AND (@VENDOR_NAME IS NULL OR [Vendor].[Name] LIKE CONCAT('%',@VENDOR_NAME,'%')) AND (@VENDOR_SEARCH_NAME IS NULL OR [Vendor].[Search Name] LIKE CONCAT('%',@VENDOR_SEARCH_NAME,'%')) AND (@VENDOR_NAME2 IS NULL OR [Vendor].[Name 2] LIKE CONCAT('%',@VENDOR_NAME2,'%')) AND (@VAT_REGISTRATION_NUMBER IS NULL OR [Vendor].[VAT Registration No_] LIKE CONCAT('%',@VAT_REGISTRATION_NUMBER,'%')) AND (@IBAN IS NULL OR REPLACE([VendorBankAccount].[IBAN],' ','') LIKE CONCAT('%',@IBAN,'%')); END; ------------------------------------------------------------------------------------------------------------------- RETURN; END; /** TEST SELECT * FROM [dbo].[FNCUST_GET_MS-DYN365BC_VENDOR_INFO]('SWE',1,NULL,'Digital Data',NULL,NULL,NULL,NULL) -- mit like SELECT * FROM [dbo].[FNCUST_GET_MS-DYN365BC_VENDOR_INFO]('SWE',1,NULL,NULL,NULL,NULL,NULL,' DE4 45135 002502230 ') -- mit like SELECT * FROM [dbo].[FNCUST_GET_MS-DYN365BC_VENDOR_INFO]('SWE',0,NULL,NULL,NULL,NULL,NULL,'DE 44513500250223017760') -- ohne like SELECT * FROM [dbo].[FNCUST_GET_MS-DYN365BC_VENDOR_INFO]('SWE',0,NULL,'Digital Data GmbH',NULL,NULL,NULL,NULL) -- ohne like SELECT * FROM [dbo].[FNCUST_GET_MS-DYN365BC_VENDOR_INFO]('SWE',0,NULL,NULL,NULL,NULL,NULL,'DE22840550500000026980') **/