ConfigCustom/Stadtwerke_Eschwege
7
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Stadtwerke_Eschwege/SWESQL/SQL-Server/Tabellenwertfunktionen/[FNCUST_GET_INVOICE_HEAD_TRANSFER].sql
KammM 8c1b6c3c51 Projektstand 30.03.2026
2026-03-31 10:55:23 +02:00

552 lines
18 KiB
Transact-SQL
Raw Permalink Blame History

USE [DD_ECM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- HE, MK // 23.03.2026
-- 23.03.2026 HE, MK Initial
CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_INVOICE_HEAD_TRANSFER] (
@pMANDATOR NVARCHAR(5),
@pIDB_OBJECT_ID BIGINT,
@pORDER_NR NVARCHAR(25),
@pWD_IDX_POSTING_DESCRIPTION NVARCHAR(25), -- = 'Beschreibung';
@pWD_VKR_POSTING_DESCRIPTION NVARCHAR(25) -- = 'Vektor String 054';
)
RETURNS @vTB_INVOICE_HEAD_TRANSFER TABLE ([GUID] [BIGINT] IDENTITY(1,1) NOT NULL,
[IDB_OBJECT_ID] [BIGINT] NOT NULL,
[documentType] [INT] NOT NULL,
[no] [NVARCHAR](20) NULL,
[noSeries] [NVARCHAR](20) NOT NULL,
[processIDTransfer] [NVARCHAR](25) NOT NULL,
[buyFromVendorNo] [NVARCHAR](20) NOT NULL,
[payToVendorNo] [NVARCHAR](20) NOT NULL,
[postingDate] [NVARCHAR](10) NULL,
[paymentTermsCode] [NVARCHAR](10) NULL,
[paymentTermsCodeIR] [NVARCHAR](10) NULL,
[dueDate] [DATE] NULL,
[pmtDiscountDate] [DATE] NULL,
[paymentDiscount] [DECIMAL](38,2) NULL,
[paymentDiscountIR] [DECIMAL](38,2) NULL,
[currencyCode] [NVARCHAR](10) NULL,
[invoiceDiscCode] [NVARCHAR](20) NULL,
[postingDescription] [NVARCHAR](100) NULL,
[paymentMethodCode] [NVARCHAR](10) NULL,
[vendorInvoiceNo] [NVARCHAR](35) NOT NULL,
[vendorCrMemoNo] [NVARCHAR](35) NULL,
[phrVendorBankAccountCode] [NVARCHAR](20) NULL,
[phrBankBranchNo] [NVARCHAR](20) NULL,
[phrBankAccountNo] [NVARCHAR](30) NULL,
[phrIBAN] [NVARCHAR](50) NULL,
[phrSWIFTCode] [NVARCHAR](20) NULL,
[phrBankName] [NVARCHAR](100) NULL,
[phrBankAccountEntryPriority] [BIT] NULL,
[phrRMCashDiscountReceived] [BIT] NULL,
[phrRMAmountIncludingVAT] [DECIMAL](38,2) NULL,
[phrRMAmountLessDiscount] [DECIMAL](38,2) NULL)
AS
BEGIN
-- declare new vars because of parameter sniffing
DECLARE @MANDATOR NVARCHAR(5) = UPPER(LTRIM(RTRIM(@pMANDATOR))),
@IDB_OBJECT_ID BIGINT = @pIDB_OBJECT_ID,
@ORDER_NR NVARCHAR(25) = LTRIM(RTRIM(@pORDER_NR)),
@WD_IDX_POSTING_DESCRIPTION NVARCHAR(25) = LTRIM(RTRIM(@pWD_IDX_POSTING_DESCRIPTION)),
@WD_VKR_POSTING_DESCRIPTION NVARCHAR(25) = LTRIM(RTRIM(@pWD_VKR_POSTING_DESCRIPTION));
-- declare runtime vars
DECLARE @MY_FUNCTION_NAME NVARCHAR(128) = OBJECT_NAME(@@PROCID),
@POSTING_DESCRIPTION NVARCHAR(1000) = NULL;
SET @POSTING_DESCRIPTION = (SELECT TOP 1 LTRIM(RTRIM([VALUE])) FROM [FNDD_GET_WINDREAM_INDEX_VALUES](@IDB_OBJECT_ID,@WD_IDX_POSTING_DESCRIPTION,'; ',0,0));
IF (LEN(@POSTING_DESCRIPTION) > 0) BEGIN
SET @POSTING_DESCRIPTION += '; ';
SET @POSTING_DESCRIPTION += (SELECT TOP 1 LTRIM(RTRIM([VALUE])) FROM [FNDD_GET_WINDREAM_INDEX_VALUES](@IDB_OBJECT_ID,@WD_VKR_POSTING_DESCRIPTION,'; ',0,0));
END ELSE BEGIN
SET @POSTING_DESCRIPTION = (SELECT TOP 1 LTRIM(RTRIM([VALUE])) FROM [FNDD_GET_WINDREAM_INDEX_VALUES](@IDB_OBJECT_ID,@WD_VKR_POSTING_DESCRIPTION,'; ',0,0));
END;
-------------------------- Nav Feld ist nur 100 Zeichen groß --------------------------
IF (LEN(@POSTING_DESCRIPTION) > 0) BEGIN
SET @POSTING_DESCRIPTION = Replace(@POSTING_DESCRIPTION,' ',' ');
SET @POSTING_DESCRIPTION = Replace(@POSTING_DESCRIPTION,char(13),' ');
SET @POSTING_DESCRIPTION = LEFT(@POSTING_DESCRIPTION,100);
END;
---------------------------------------------------------------------------------------
IF ((@MANDATOR = 'SWE') AND ((@ORDER_NR <> 'EB9999999') AND (@ORDER_NR IS NOT NULL))) BEGIN -- Mit Bestelldaten aus NAV
INSERT INTO @vTB_INVOICE_HEAD_TRANSFER ([IDB_OBJECT_ID],
[documentType],
[no],
[noSeries],
[processIDTransfer],
[buyFromVendorNo],
[payToVendorNo],
[postingDate],
[paymentTermsCode],
[paymentTermsCodeIR],
[dueDate],
[pmtDiscountDate],
[paymentDiscount],
[paymentDiscountIR],
[currencyCode],
[invoiceDiscCode],
[postingDescription],
[paymentMethodCode],
[vendorInvoiceNo],
[vendorCrMemoNo],
[phrVendorBankAccountCode],
[phrBankBranchNo],
[phrBankAccountNo],
[phrIBAN],
[phrSWIFTCode],
[phrBankName],
[phrBankAccountEntryPriority],
[phrRMCashDiscountReceived],
[phrRMAmountIncludingVAT],
[phrRMAmountLessDiscount])
SELECT @pIDB_OBJECT_ID as 'IDB_OBJECT_ID',
CASE
WHEN 1=1
THEN 2
ELSE 0
END as 'documentType',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'no',
CASE
WHEN 1=1
THEN 'E-RE'
ELSE ''
END as 'noSeries',
CASE
WHEN 1=1
THEN 'TEST'
ELSE ''
END as 'processIDTransfer',
CASE
WHEN LEN(LTRIM(RTRIM([BaseAttributes].[szText35]))) > 0
THEN LTRIM(RTRIM([BaseAttributes].[szText35]))
ELSE ''
END as 'buyFromVendorNo',
CASE
WHEN LEN(LTRIM(RTRIM([BaseAttributes].[szText35]))) > 0
THEN LTRIM(RTRIM([BaseAttributes].[szText35]))
ELSE ''
END as 'payToVendorNo',
CASE
WHEN LEN(CONVERT(nvarchar(25), LEFT([BaseAttributes].[dwDate08],8))) > 0
THEN CONVERT(date, LEFT([BaseAttributes].[dwDate08], 8), 112)
ELSE convert(date,(getdate()))
END as 'postingDate',
CASE
WHEN LEN([PurchaseHeader].[Payment Terms Code]) > 0
THEN [PurchaseHeader].[Payment Terms Code]
ELSE ''
END as 'paymentTermsCode',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'paymentTermsCodeIR',
CASE
WHEN LEN(CONVERT(nvarchar(25), LEFT([BaseAttributes].[dwDate06],8))) > 0
THEN CONVERT(date, LEFT([BaseAttributes].[dwDate06], 8), 112)
ELSE NULL
END as 'dueDate',
CASE
WHEN [BaseAttributes].[dwInteger23] > 0
THEN DATEADD(day,-[BaseAttributes].[dwInteger23],CONVERT(date, LEFT([BaseAttributes].[decCreationTime], 8), 112))
ELSE ''
END as 'pmtDiscountDate',
CASE
WHEN [BaseAttributes].[lfFloat09] > 0 -- Skonto Prozent
THEN [BaseAttributes].[lfFloat09]
ELSE 0
END as 'paymentDiscount',
--CASE
-- WHEN (([BaseAttributes].[dwInteger23] > 0) AND ([BaseAttributes].[lfFloat09] > 0) AND ([BaseAttributes].[lfFloat08] > 0)) -- Skontotage | Skonto Prozent | Skontierbare Betrag
-- THEN [BaseAttributes].[lfFloat08] / 100 * ([BaseAttributes].[lfFloat09])
-- ELSE 0
--END as 'paymentDiscount',
CASE
WHEN 1=1
THEN 0
ELSE 'dummy'
END as 'paymentDiscountIR',
CASE
WHEN LEN([PurchaseHeader].[Currency Code]) > 0
THEN [PurchaseHeader].[Currency Code]
ELSE ''
END as 'currencyCode',
CASE
WHEN LEN([PurchaseHeader].[Invoice Disc_ Code]) > 0
THEN [PurchaseHeader].[Invoice Disc_ Code]
ELSE ''
END as 'invoiceDiscCode',
CASE
WHEN LEN(@POSTING_DESCRIPTION) > 0
THEN @POSTING_DESCRIPTION
ELSE ''
END as 'postingDescription',
CASE
WHEN LEN([PurchaseHeader].[Payment Method Code]) > 0
THEN [PurchaseHeader].[Payment Method Code]
ELSE ''
END as 'paymentMethodCode',
CASE
WHEN LEN([BaseAttributes].[szText37]) > 0
THEN LTRIM(RTRIM([BaseAttributes].[szText37]))
ELSE ''
END as 'vendorInvoiceNo',
CASE
WHEN LEN([PurchaseHeader].[Vendor Cr_ Memo No_]) > 0
THEN [PurchaseHeader].[Vendor Cr_ Memo No_]
ELSE ''
END as 'vendorCrMemoNo',
CASE
WHEN LEN([VendorBankAccount].[Code]) > 0
THEN [VendorBankAccount].[Code]
ELSE ''
END as 'phrVendorBankAccountCode',
CASE
WHEN LEN([VendorBankAccount].[Bank Branch No_]) > 0
THEN [VendorBankAccount].[Bank Branch No_]
ELSE ''
END as 'phrBankBranchNo',
CASE
WHEN LEN([VendorBankAccount].[Bank Account No_]) > 0
THEN [VendorBankAccount].[Bank Account No_]
ELSE ''
END as 'phrBankAccountNo',
CASE
WHEN LEN([VendorBankAccount].[IBAN]) > 0
THEN [VendorBankAccount].[IBAN]
ELSE ''
END as 'phrIBAN',
CASE
WHEN LEN([VendorBankAccount].[SWIFT Code]) > 0
THEN [VendorBankAccount].[SWIFT Code]
ELSE ''
END as 'phrSWIFTCode',
CASE
WHEN LEN([VendorBankAccount].[Name]) > 0
THEN [VendorBankAccount].[Name]
ELSE ''
END as 'phrBankName',
CASE
WHEN 1=1
THEN 0
ELSE 1
END as 'phrBankAccountEntryPriority',
CASE
WHEN 1=1
THEN 0
ELSE 1
END as 'phrRMCashDiscountReceived',
CASE
WHEN LEN(convert(NVARCHAR(25),[BaseAttributes].[lfFloat13])) > 0
THEN convert(decimal(38,2),replace(convert(NVARCHAR(25),[BaseAttributes].[lfFloat13]),',','.'))
ELSE 0
END as 'phrRMAmountIncludingVAT',
CASE
WHEN LEN(convert(NVARCHAR(25),[BaseAttributes].[lfFloat12])) > 0
THEN convert(decimal(38,2),replace(convert(NVARCHAR(25),[BaseAttributes].[lfFloat12]),',','.'))
ELSE 0
END as 'phrRMAmountLessDiscount'
FROM [windreamDB].[dbo].[BaseAttributes] as [BaseAttributes] WITH (NOLOCK)
INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header$437dbf0e-84ff-417a-965d-ed2bb9650972] as [PurchaseHeader] ON [BaseAttributes].[szText36] COLLATE DATABASE_DEFAULT = [PurchaseHeader].[No_] COLLATE DATABASE_DEFAULT
INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] as [Vendor] ON [PurchaseHeader].[Pay-to Vendor No_] = [Vendor].[No_]
INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] as [VendorExt] ON [Vendor].[No_] = [VendorExt].[No_]
LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] as [VendorBankAccountExt] ON [Vendor].[No_] = [VendorBankAccountExt].[Vendor No_] AND [WLK Clearing$408c5b32-694e-47bb-96d5-dad7fc8111a7] = 1
LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] as [VendorBankAccount] ON [Vendor].[No_] = [VendorBankAccount].[Vendor No_] AND [VendorBankAccount].[Code] = [VendorBankAccountExt].[Code]
WHERE [BaseAttributes].[dwDocID] = @IDB_OBJECT_ID; -- test mit 9174258
END ELSE IF (@MANDATOR = 'SWE') BEGIN -- Ohne Bestelldaten aus NAV
INSERT INTO @vTB_INVOICE_HEAD_TRANSFER ([IDB_OBJECT_ID],
[documentType],
[no],
[noSeries],
[processIDTransfer],
[buyFromVendorNo],
[payToVendorNo],
[postingDate],
[paymentTermsCode],
[paymentTermsCodeIR],
[dueDate],
[pmtDiscountDate],
[paymentDiscount],
[paymentDiscountIR],
[currencyCode],
[invoiceDiscCode],
[postingDescription],
[paymentMethodCode],
[vendorInvoiceNo],
[vendorCrMemoNo],
[phrVendorBankAccountCode],
[phrBankBranchNo],
[phrBankAccountNo],
[phrIBAN],
[phrSWIFTCode],
[phrBankName],
[phrBankAccountEntryPriority],
[phrRMCashDiscountReceived],
[phrRMAmountIncludingVAT],
[phrRMAmountLessDiscount])
SELECT @pIDB_OBJECT_ID as 'IDB_OBJECT_ID',
CASE
WHEN 1=1
THEN 2
ELSE 0
END as 'documentType',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'no',
CASE
WHEN 1=1
THEN 'E-RE'
ELSE ''
END as 'noSeries',
CASE
WHEN 1=1
THEN 'TEST'
ELSE ''
END as 'processIDTransfer',
CASE
WHEN LEN(LTRIM(RTRIM([BaseAttributes].[szText35]))) > 0
THEN LTRIM(RTRIM([BaseAttributes].[szText35]))
ELSE ''
END as 'buyFromVendorNo',
CASE
WHEN LEN(LTRIM(RTRIM([BaseAttributes].[szText35]))) > 0
THEN LTRIM(RTRIM([BaseAttributes].[szText35]))
ELSE ''
END as 'payToVendorNo',
CASE
WHEN LEN(CONVERT(nvarchar(25), LEFT([BaseAttributes].[dwDate08],8))) > 0
THEN CONVERT(date, LEFT([BaseAttributes].[dwDate08], 8), 112)
ELSE convert(date,(getdate()))
END as 'postingDate',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'paymentTermsCode',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'paymentTermsCodeIR',
CASE
WHEN LEN(CONVERT(nvarchar(25), LEFT([BaseAttributes].[dwDate06],8))) > 0
THEN CONVERT(date, LEFT([BaseAttributes].[dwDate06], 8), 112)
ELSE ''
END as 'dueDate',
CASE
WHEN [BaseAttributes].[dwInteger23] > 0
THEN DATEADD(day,-[BaseAttributes].[dwInteger23],CONVERT(date, LEFT([BaseAttributes].[decCreationTime], 8), 112))
ELSE ''
END as 'pmtDiscountDate',
CASE
WHEN [BaseAttributes].[lfFloat09] > 0 -- Skonto Prozent
THEN [BaseAttributes].[lfFloat09]
ELSE 0
END as 'paymentDiscount',
--CASE
-- WHEN (([BaseAttributes].[dwInteger23] > 0) AND ([BaseAttributes].[lfFloat09] > 0) AND ([BaseAttributes].[lfFloat08] > 0)) -- Skontotage | Skonto Prozent | Skontierbare Betrag
-- THEN [BaseAttributes].[lfFloat08] / 100 * ([BaseAttributes].[lfFloat09])
-- ELSE 0
--END as 'paymentDiscount',
CASE
WHEN 1=1
THEN 0
ELSE 'dummy'
END as 'paymentDiscountIR',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'currencyCode',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'invoiceDiscCode',
CASE
WHEN LEN(@POSTING_DESCRIPTION) > 0
THEN @POSTING_DESCRIPTION
ELSE ''
END as 'postingDescription',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'paymentMethodCode',
CASE
WHEN LEN([BaseAttributes].[szText37]) > 0
THEN LTRIM(RTRIM([BaseAttributes].[szText37]))
ELSE ''
END as 'vendorInvoiceNo',
CASE
WHEN 1=1
THEN ''
ELSE 'dummy'
END as 'vendorCrMemoNo',
CASE
WHEN LEN([VendorBankAccount].[Code]) > 0
THEN [VendorBankAccount].[Code]
ELSE ''
END as 'phrVendorBankAccountCode',
CASE
WHEN LEN([VendorBankAccount].[Bank Branch No_]) > 0
THEN [VendorBankAccount].[Bank Branch No_]
ELSE ''
END as 'phrBankBranchNo',
CASE
WHEN LEN([VendorBankAccount].[Bank Account No_]) > 0
THEN [VendorBankAccount].[Bank Account No_]
ELSE ''
END as 'phrBankAccountNo',
CASE
WHEN LEN([VendorBankAccount].[IBAN]) > 0
THEN [VendorBankAccount].[IBAN]
ELSE ''
END as 'phrIBAN',
CASE
WHEN LEN([VendorBankAccount].[SWIFT Code]) > 0
THEN [VendorBankAccount].[SWIFT Code]
ELSE ''
END as 'phrSWIFTCode',
CASE
WHEN LEN([VendorBankAccount].[Name]) > 0
THEN [VendorBankAccount].[Name]
ELSE ''
END as 'phrBankName',
CASE
WHEN 1=1
THEN 0
ELSE 1
END as 'phrBankAccountEntryPriority',
CASE
WHEN 1=1
THEN 0
ELSE 1
END as 'phrRMCashDiscountReceived',
CASE
WHEN LEN(convert(NVARCHAR(25),[BaseAttributes].[lfFloat13])) > 0
THEN convert(decimal(38,2),replace(convert(NVARCHAR(25),[BaseAttributes].[lfFloat13]),',','.'))
ELSE 0
END as 'phrRMAmountIncludingVAT',
CASE
WHEN LEN(convert(NVARCHAR(25),[BaseAttributes].[lfFloat12])) > 0
THEN convert(decimal(38,2),replace(convert(NVARCHAR(25),[BaseAttributes].[lfFloat12]),',','.'))
ELSE 0
END as 'phrRMAmountLessDiscount'
FROM [windreamDB].[dbo].[BaseAttributes] as [BaseAttributes] WITH (NOLOCK)
INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] as [Vendor] ON [BaseAttributes].[szText35] COLLATE DATABASE_DEFAULT = [Vendor].[No_] COLLATE DATABASE_DEFAULT
INNER JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] as [VendorExt] ON [Vendor].[No_] = [VendorExt].[No_]
LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] as [VendorBankAccountExt] ON [Vendor].[No_] = [VendorBankAccountExt].[Vendor No_] AND [WLK Clearing$408c5b32-694e-47bb-96d5-dad7fc8111a7] = 1
LEFT JOIN [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] as [VendorBankAccount] ON [Vendor].[No_] = [VendorBankAccount].[Vendor No_] AND [VendorBankAccount].[Code] = [VendorBankAccountExt].[Code]
WHERE [BaseAttributes].[dwDocID] = @IDB_OBJECT_ID; -- test mit 9174258
END;
RETURN;
END;
/***
-- TEST mit:
select * from [FNCUST_GET_INVOICE_HEAD_TRANSFER]('SWE',9208424,'EB00020596','Beschreibung','Vektor String 054') -- mit bestellnr
select * from [FNCUST_GET_INVOICE_HEAD_TRANSFER]('SWE',9209045,'EB9999999','Beschreibung','Vektor String 054') -- ohne bestellnr
select * from [FNCUST_GET_INVOICE_HEAD_TRANSFER]('SWE',9208424,NULL,'Beschreibung','Vektor String 054') -- ohne bestellnr
select * from [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Purchase Header$437dbf0e-84ff-417a-965d-ed2bb9650972] where [No_] = 'EB00020596'
select * from windreamDB.dbo.BaseAttributes where dwdocid = 9174258;
select * from [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor$437dbf0e-84ff-417a-965d-ed2bb9650972] where [No_] = 232102
select * from [SWENAVSQL\NTSSUITE].[NTSsuite].[dbo].[SWE$Vendor Bank Account$437dbf0e-84ff-417a-965d-ed2bb9650972] where [Vendor No_] = 232102
***/
GO
Reference in New Issue View Git Blame Copy Permalink