195 lines
8.7 KiB
Transact-SQL
195 lines
8.7 KiB
Transact-SQL
USE [DD_ECM]
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- TSQL Table function for "Kontierungstabelle"
|
|
|
|
-- MK // 24.02.2025
|
|
-- 24.02.2025 Initial
|
|
|
|
CREATE OR ALTER FUNCTION [dbo].[FNCUST_GET_INVOICE_POS] (
|
|
@pEMAIL_MESSAGE_ID NVARCHAR(900),
|
|
@pDATA_SEPERATOR NVARCHAR(1) = '~',
|
|
@pDATA_SEPERATOR_TAX NVARCHAR(3) = ' - '
|
|
)
|
|
RETURNS @vTB_INVOICE_POS TABLE ([GUID] INT IDENTITY(1,1) NOT NULL,
|
|
[CONTENT_CONCAT] NVARCHAR(max) NOT NULL,
|
|
[ORDER_POSITION_ARTICLE] NVARCHAR(50) NULL,
|
|
[INVOICE_POSITION_ARTICLE] NVARCHAR(50) NULL)
|
|
AS
|
|
BEGIN
|
|
|
|
-- declare new vars because of parameter sniffing
|
|
DECLARE @MandatorShortName NVARCHAR(5) = 'SWE',
|
|
@EMAIL_MESSAGE_ID NVARCHAR(900) = UPPER(LTRIM(RTRIM(@pEMAIL_MESSAGE_ID))),
|
|
@DATA_SEPERATOR NVARCHAR(1) = UPPER(LTRIM(RTRIM(@pDATA_SEPERATOR))),
|
|
@DATA_SEPERATOR_TAX NVARCHAR(3) = ISNULL(@pDATA_SEPERATOR_TAX,' - ');
|
|
|
|
DECLARE @INVOICE_POS_COUNTER INT,
|
|
@REFERENCE_GUID NVARCHAR(900),
|
|
@ITEM_DESCRIPTION NVARCHAR(900),
|
|
@ITEM_VALUE NVARCHAR(900),
|
|
@GROUP_COUNTER INT,
|
|
@COUNTER INT = 0,
|
|
@ACCOUNTING VARCHAR(900),
|
|
@ARTICLE_NR_EXT NVARCHAR(50),
|
|
@POS_AMOUNT DECIMAL(20,2),
|
|
@POS_SUM_NETTO DECIMAL(20,2),
|
|
@POS_TAX_CODE NVARCHAR(25),
|
|
@POS_TAX_RATE DECIMAL(20,2),
|
|
@POS_SUM_BRUTTO DECIMAL(20,2);
|
|
|
|
DECLARE @ARTICLE_NR NVARCHAR(50),
|
|
@ORDER_NR NVARCHAR(50),
|
|
@ORDER_NR_FALLBACK NVARCHAR(50) = 'EB9999999', -- Dummy nr für Rechnungen ohne Bestellung
|
|
@WORK_ORDER_NR NVARCHAR(25),
|
|
@WORK_ORDER_NR_FALLBACK NVARCHAR(25) = 'A-99999999', -- Dummy nr für Lagerbestellungen
|
|
@POS_TAX_CODE_NAV_VAT19 NVARCHAR(25) = '19 - INL - 19.00%'; -- Standardwert aus NAV für 19% MwSt Inland
|
|
|
|
SET @INVOICE_POS_COUNTER = (SELECT (MAX([GROUP_COUNTER])-1) as 'INVOICE_POS_COUNTER' FROM [TBEDMI_ITEM_VALUE] (NOLOCK) WHERE [REFERENCE_GUID] = LTRIM(RTRIM(@EMAIL_MESSAGE_ID)))
|
|
IF (@INVOICE_POS_COUNTER > 0) BEGIN
|
|
|
|
SELECT @ORDER_NR = ITEM_VALUE
|
|
FROM dbo.TBEDMI_ITEM_VALUE (NOLOCK)
|
|
WHERE [SPEC_NAME] = 'INVOICE_REFERENCE'
|
|
AND [REFERENCE_GUID] = @EMAIL_MESSAGE_ID;
|
|
|
|
DECLARE CURSOR_INVOICE_POS CURSOR
|
|
LOCAL FAST_FORWARD FOR
|
|
SELECT DISTINCT [REFERENCE_GUID], [GROUP_COUNTER]
|
|
FROM [TBEDMI_ITEM_VALUE] (NOLOCK)
|
|
WHERE [SPEC_NAME] IN ('INVOICE_POSITION_ARTICLE','INVOICE_POSITION_AMOUNT','INVOICE_POSITION_TAX_AMOUNT','INVOICE_TAXPOS_TAX_RATE')
|
|
AND [GROUP_COUNTER] > 1
|
|
AND [REFERENCE_GUID] = @EMAIL_MESSAGE_ID;
|
|
-- OR [REFERENCE_GUID] = 'ERw202512091413384620257svcdigitaldata' -- TEST
|
|
|
|
OPEN CURSOR_INVOICE_POS;
|
|
FETCH NEXT FROM CURSOR_INVOICE_POS INTO @REFERENCE_GUID, @GROUP_COUNTER;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
SET @COUNTER += 1
|
|
SET @ACCOUNTING = NULL;
|
|
SET @POS_AMOUNT = NULL;
|
|
SET @POS_SUM_NETTO = NULL;
|
|
SET @POS_TAX_CODE = NULL;
|
|
SET @POS_SUM_BRUTTO = NULL;
|
|
|
|
SET @ARTICLE_NR = NULL;
|
|
SET @WORK_ORDER_NR = NULL;
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
--==================================================-- PositionsArtikel --==================================================--
|
|
SET @ARTICLE_NR_EXT = ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(nvarchar(200),[ITEM_VALUE])))) = 0
|
|
THEN ''
|
|
ELSE CAST([ITEM_VALUE] AS NVARCHAR(50))
|
|
END as 'ITEM_VALUE'
|
|
FROM [TBEDMI_ITEM_VALUE] (NOLOCK)
|
|
WHERE [REFERENCE_GUID] = @REFERENCE_GUID
|
|
AND [GROUP_COUNTER] = @GROUP_COUNTER
|
|
AND [SPEC_NAME] = 'INVOICE_POSITION_ARTICLE'),'');
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
--===================================================-- PositionsMenge --===================================================--
|
|
SET @POS_AMOUNT = ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(nvarchar(200),[ITEM_VALUE])))) = 0
|
|
THEN 0
|
|
ELSE CAST(REPLACE([ITEM_VALUE],',','.') AS DECIMAL(20,2))
|
|
END as 'ITEM_VALUE'
|
|
FROM [TBEDMI_ITEM_VALUE] (NOLOCK)
|
|
WHERE [REFERENCE_GUID] = @REFERENCE_GUID
|
|
AND [GROUP_COUNTER] = @GROUP_COUNTER
|
|
AND [SPEC_NAME] = 'INVOICE_POSITION_AMOUNT'),0);
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
--===================================================-- PositionsSumme --===================================================--
|
|
SET @POS_SUM_NETTO = ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(nvarchar(200),[ITEM_VALUE])))) = 0
|
|
THEN 0
|
|
ELSE CAST(REPLACE([ITEM_VALUE],',','.') AS DECIMAL(20,2))
|
|
END as 'ITEM_VALUE'
|
|
FROM [TBEDMI_ITEM_VALUE] (NOLOCK)
|
|
WHERE [REFERENCE_GUID] = @REFERENCE_GUID
|
|
AND [GROUP_COUNTER] = @GROUP_COUNTER
|
|
AND [SPEC_NAME] = 'INVOICE_POSITION_TAX_AMOUNT'),0);
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
--=================================================-- PositionsSteuersatz --================================================--
|
|
SET @POS_TAX_RATE = ISNULL((SELECT CASE WHEN LEN(LTRIM(RTRIM(convert(NVARCHAR(200),[ITEM_VALUE])))) = 0
|
|
THEN 0
|
|
ELSE CAST(REPLACE([ITEM_VALUE],',','.') AS DECIMAL(20,2))
|
|
END as 'ITEM_VALUE'
|
|
FROM [TBEDMI_ITEM_VALUE] (NOLOCK)
|
|
WHERE [REFERENCE_GUID] = @REFERENCE_GUID
|
|
AND [GROUP_COUNTER] = @GROUP_COUNTER
|
|
AND [SPEC_NAME] = 'INVOICE_TAXPOS_TAX_RATE'),0);
|
|
|
|
SET @POS_TAX_CODE = CASE WHEN @POS_TAX_RATE = 19.00
|
|
THEN @POS_TAX_CODE_NAV_VAT19
|
|
|
|
ELSE '0'
|
|
|
|
END;
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
--======================================================-- ERP Daten --=====================================================--
|
|
SELECT TOP 1 @ARTICLE_NR = [No_],
|
|
@WORK_ORDER_NR = [Work Order No_]
|
|
FROM [dbo].[FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MandatorShortName,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [GUID] = @COUNTER
|
|
AND ([Description] IS NOT NULL OR [Description 2] IS NOT NULL)
|
|
OR (UPPER([Description]) = @ARTICLE_NR_EXT
|
|
OR UPPER([Description]) like '%' + @ARTICLE_NR_EXT + '%'
|
|
OR UPPER([Description 2]) = @ARTICLE_NR_EXT
|
|
OR UPPER([Description 2]) like '%' + @ARTICLE_NR_EXT + '%');
|
|
--TEST: select * from [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS]('SWE','EB00017529','~')
|
|
|
|
|
|
SELECT TOP 1 @POS_TAX_RATE = [VAT _]
|
|
FROM [dbo].[FNCUST_GET_MS-DYN365BC_VAT_LIST](@MandatorShortName,@ORDER_NR,@DATA_SEPERATOR_TAX)
|
|
WHERE [CONTENT_CONCAT] = @POS_TAX_CODE;
|
|
SET @POS_TAX_RATE = ISNULL(@POS_TAX_RATE,0); -- Failsafe
|
|
-----------------------------------------------------------------------------------------------------------------------------
|
|
|
|
-----------------------------------------------------------------------------------------------------------------
|
|
SET @POS_SUM_BRUTTO = @POS_SUM_NETTO * (100.00 + @POS_TAX_RATE) / 100;
|
|
SET @ACCOUNTING = concat(@COUNTER, @DATA_SEPERATOR,
|
|
ISNULL(@WORK_ORDER_NR,@WORK_ORDER_NR_FALLBACK), @DATA_SEPERATOR,
|
|
NULL, @DATA_SEPERATOR,
|
|
REPLACE(@POS_AMOUNT,',','.'), @DATA_SEPERATOR,
|
|
REPLACE(@POS_SUM_NETTO,',','.'), @DATA_SEPERATOR,
|
|
@POS_TAX_CODE, @DATA_SEPERATOR,
|
|
REPLACE(@POS_SUM_BRUTTO,',','.'), @DATA_SEPERATOR,
|
|
'false', @DATA_SEPERATOR,
|
|
@ARTICLE_NR);
|
|
|
|
IF ((@POS_AMOUNT > 0) AND (@POS_SUM_NETTO > 0)) BEGIN
|
|
|
|
INSERT INTO @vTB_INVOICE_POS([CONTENT_CONCAT],[ORDER_POSITION_ARTICLE],[INVOICE_POSITION_ARTICLE])
|
|
VALUES (@ACCOUNTING, @ARTICLE_NR, @ARTICLE_NR_EXT)
|
|
|
|
END;
|
|
-----------------------------------------------------------------------------------------------------------------
|
|
|
|
FETCH NEXT FROM CURSOR_INVOICE_POS INTO @REFERENCE_GUID, @GROUP_COUNTER;
|
|
END;
|
|
CLOSE CURSOR_INVOICE_POS;
|
|
DEALLOCATE CURSOR_INVOICE_POS;
|
|
|
|
END;
|
|
|
|
RETURN;
|
|
|
|
END;
|
|
|
|
/***
|
|
-- Test
|
|
|
|
SELECT * FROM [FNCUST_GET_INVOICE_POS]('ERw202603031419486748168lfelsburg','~',' - ') --WHERE
|
|
SELECT * FROM [FNCUST_GET_INVOICE_POS]('ERw202603121044356088460lfelsburg','~',' - ') --WHERE
|
|
|
|
***/
|
|
GO |