629 lines
33 KiB
Transact-SQL
629 lines
33 KiB
Transact-SQL
USE [DD_ECM]
|
|
GO
|
|
|
|
/****** Object: UserDefinedFunction [dbo].[FNCUST_GET_INVOICE_POS_TRANSFER] Script Date: 27.03.2026 14:12:03 ******/
|
|
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_POS_TRANSFER] (
|
|
@pMANDATOR NVARCHAR(5),
|
|
@pIDB_OBJECT_ID BIGINT,
|
|
@pORDER_NR NVARCHAR(25),
|
|
@pDATA_SEPERATOR NVARCHAR(1) = '~',
|
|
@pDATA_SEPERATOR_TAX NVARCHAR(3) = ' - ',
|
|
@pWD_VKR_ACCOUNTING NVARCHAR(25) -- = 'Vektor String 422'
|
|
)
|
|
RETURNS @vTB_INVOICE_POS_TRANSFER TABLE ([GUID] [BIGINT] IDENTITY(1,1) NOT NULL,
|
|
[IDB_OBJECT_ID] [BIGINT] NOT NULL,
|
|
[LineNo] [INT] NULL,
|
|
[attachedToLineNo] [TINYINT] NULL,
|
|
[type] [INT] NULL,
|
|
[no] [NVARCHAR](15) NULL,
|
|
[description] [NVARCHAR](100) NULL,
|
|
[description2] [NVARCHAR](50) NULL,
|
|
[locationCode] [NVARCHAR](10) NULL,
|
|
[variantCode] [NVARCHAR](10) NULL,
|
|
[quantity] [DECIMAL](38,2) NULL,
|
|
[unitOfMeasureCode] [NVARCHAR](50) NULL,
|
|
[directUnitCost] [DECIMAL](38,2) NULL,
|
|
[lineDiscount] [DECIMAL](38,2) NULL,
|
|
[ableToDiscount] [BIT] NULL,
|
|
[discountCalculated] [BIT] NULL,
|
|
[workOrderNo] [NVARCHAR](20) NULL,
|
|
[genBusPostingGroup] [NVARCHAR](20) NULL,
|
|
[genProdPostingGroup] [NVARCHAR](20) NULL,
|
|
[vatBusPostingGroup] [NVARCHAR](20) NULL,
|
|
[vatProdPostingGroup] [NVARCHAR](20) NULL,
|
|
[applToItemEntry] [INT] NULL,
|
|
[postingWithoutApply] [BIT] NULL,
|
|
[anticipatedPayment] [INT] NULL,
|
|
[receiptNo] [NVARCHAR](20) NULL,
|
|
[receiptLineNo] [INT] NULL,
|
|
[irOrderNo] [NVARCHAR](20) NULL,
|
|
[irOrderLineNo] [INT] NULL)
|
|
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @MANDATOR_SHORT_NAME_FALLBACK NVARCHAR(25) = 'SWE',
|
|
@DATA_SEPERATOR_TAX_FALLBACK NVARCHAR(3) = ' - ';
|
|
|
|
-- 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)),
|
|
@DATA_SEPERATOR NVARCHAR(1) = ISNULL(LTRIM(RTRIM(@pDATA_SEPERATOR)),'~'),
|
|
@DATA_SEPERATOR_TAX NVARCHAR(3) = UPPER(ISNULL(@pDATA_SEPERATOR_TAX,@DATA_SEPERATOR_TAX_FALLBACK)),
|
|
@WD_VKR_ACCOUNTING NVARCHAR(25) = LTRIM(RTRIM(@pWD_VKR_ACCOUNTING));
|
|
|
|
-- declare runtime vars
|
|
DECLARE @MY_FUNCTION_NAME NVARCHAR(128) = OBJECT_NAME(@@PROCID);
|
|
DECLARE @VALUE VARCHAR(max) = NULL,
|
|
@POS_TAX_CODE NVARCHAR(25) = NULL,
|
|
@POS_TAX_RATE DECIMAL(20,2) = 0.0;
|
|
|
|
DECLARE @LineNo INT = NULL,
|
|
@attachedToLineNo TINYINT = NULL,
|
|
@type INT = NULL,
|
|
@no NVARCHAR(15) = NULL,
|
|
@description NVARCHAR(100) = NULL,
|
|
@description2 NVARCHAR(50) = NULL,
|
|
@locationCode NVARCHAR(10) = NULL,
|
|
@variantCode NVARCHAR(10) = NULL,
|
|
@quantity DECIMAL(38,2) = NULL,
|
|
@unitOfMeasureCode NVARCHAR(50) = NULL,
|
|
@directUnitCost DECIMAL(38,2) = NULL,
|
|
@lineDiscount DECIMAL(38,2) = NULL,
|
|
@ableToDiscount BIT = NULL,
|
|
@discountCalculated BIT = NULL,
|
|
@workOrderNo NVARCHAR(20) = NULL,
|
|
@genBusPostingGroup NVARCHAR(20) = NULL,
|
|
@genProdPostingGroup NVARCHAR(20) = NULL,
|
|
@vatBusPostingGroup NVARCHAR(20) = NULL,
|
|
@vatProdPostingGroup NVARCHAR(20) = NULL,
|
|
@applToItemEntry INT = NULL,
|
|
@postingWithoutApply BIT = NULL,
|
|
@anticipatedPayment INT = NULL,
|
|
@receiptNo NVARCHAR(20) = NULL,
|
|
@receiptLineNo INT = NULL,
|
|
@irOrderNo NVARCHAR(20) = NULL,
|
|
@irOrderLineNo INT = NULL;
|
|
|
|
IF ((@MANDATOR = 'SWE') AND ((@ORDER_NR <> 'EB9999999') AND (@ORDER_NR IS NOT NULL))) BEGIN -- Mit Bestelldaten aus NAV
|
|
|
|
DECLARE CURSOR_ACCOUNTING_POS CURSOR
|
|
LOCAL FAST_FORWARD FOR
|
|
SELECT [VALUE] FROM [dbo].[FNDD_GET_WINDREAM_INDEX_VALUES](@IDB_OBJECT_ID,@WD_VKR_ACCOUNTING,'; ',0,0);
|
|
|
|
OPEN CURSOR_ACCOUNTING_POS
|
|
FETCH NEXT FROM CURSOR_ACCOUNTING_POS INTO @VALUE
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @LineNo = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 1);
|
|
SET @LineNo = ISNULL(@LineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @attachedToLineNo = 0;
|
|
SET @attachedToLineNo = ISNULL(@attachedToLineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @type = (CASE
|
|
WHEN (SELECT [Item] FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 2) = 'Artikel'
|
|
THEN 2
|
|
ELSE (SELECT [Item] FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 2)
|
|
END);
|
|
SET @type = ISNULL(@type,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @no = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 3);
|
|
SET @no = ISNULL(@no,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @description = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 6);
|
|
SET @description = ISNULL(@description,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @description2 = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 7);
|
|
SET @description2 = ISNULL(@description2,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @locationCode = (SELECT [Location Code]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @locationCode = ISNULL(@locationCode,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @variantCode = (SELECT [Variant Code]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @variantCode = ISNULL(@variantCode,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @quantity = (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.')))
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 9);
|
|
SET @quantity = ISNULL(@quantity,0.0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @unitOfMeasureCode = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 10);
|
|
SET @unitOfMeasureCode = ISNULL(@unitOfMeasureCode,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @directUnitCost = (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.')))
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 11);
|
|
SET @directUnitCost = ISNULL(@directUnitCost,0.0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @lineDiscount = (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.')))
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 16);
|
|
SET @lineDiscount = ISNULL(@lineDiscount,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @ableToDiscount = (SELECT [Able to Discount]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @ableToDiscount = ISNULL(@ableToDiscount,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @discountCalculated = (CASE
|
|
WHEN (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.'))) FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 16) <> 0.0
|
|
THEN 1
|
|
ELSE 0
|
|
END);
|
|
SET @discountCalculated = ISNULL(@discountCalculated,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @workOrderNo = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 5);
|
|
SET @workOrderNo = ISNULL(@workOrderNo,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @POS_TAX_CODE = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 14);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @genBusPostingGroup = (SELECT [Gen_ Bus_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @genBusPostingGroup = ISNULL(@genBusPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @genProdPostingGroup = (SELECT [Gen_ Prod_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @genProdPostingGroup = ISNULL(@genProdPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @vatBusPostingGroup = (SELECT [VAT Bus_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_VAT_LIST](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR_TAX)
|
|
WHERE [CONTENT_CONCAT] = @POS_TAX_CODE);
|
|
SET @vatBusPostingGroup = ISNULL(@vatBusPostingGroup,(SELECT [VAT Bus_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no));
|
|
SET @vatBusPostingGroup = ISNULL(@vatBusPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @vatProdPostingGroup = (SELECT [VAT Prod_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_VAT_LIST](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR_TAX)
|
|
WHERE [CONTENT_CONCAT] = @POS_TAX_CODE);
|
|
SET @vatProdPostingGroup = ISNULL(@vatProdPostingGroup,(SELECT [VAT Prod_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no));
|
|
SET @vatProdPostingGroup = ISNULL(@vatProdPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @applToItemEntry = (SELECT [Appl_-to Item Entry]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @applToItemEntry = ISNULL(@applToItemEntry,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @postingWithoutApply = (SELECT [Posting Without Apply]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @postingWithoutApply = ISNULL(@postingWithoutApply,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @anticipatedPayment = (SELECT [Anticipated Payment]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @receiptNo = (SELECT [Receipt No_]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @receiptNo = ISNULL(@receiptNo,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @receiptLineNo = (SELECT [Receipt Line No_]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @receiptLineNo = ISNULL(@receiptLineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @irOrderNo = (SELECT [IR Order No_]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @irOrderNo = ISNULL(@irOrderNo,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @irOrderLineNo = (SELECT [IR Order Line No_]
|
|
FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR)
|
|
WHERE [Line No_] = @LineNo AND [No_] = @no);
|
|
SET @irOrderLineNo = ISNULL(@irOrderLineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
INSERT INTO @vTB_INVOICE_POS_TRANSFER ([IDB_OBJECT_ID],
|
|
[LineNo],
|
|
[attachedToLineNo],
|
|
[type],
|
|
[no],
|
|
[description],
|
|
[description2],
|
|
[locationCode],
|
|
[variantCode],
|
|
[quantity],
|
|
[unitOfMeasureCode],
|
|
[directUnitCost],
|
|
[lineDiscount],
|
|
[ableToDiscount],
|
|
[discountCalculated],
|
|
[workOrderNo],
|
|
[genBusPostingGroup],
|
|
[genProdPostingGroup],
|
|
[vatBusPostingGroup],
|
|
[vatProdPostingGroup],
|
|
[applToItemEntry],
|
|
[postingWithoutApply],
|
|
[anticipatedPayment],
|
|
[receiptNo],
|
|
[receiptLineNo],
|
|
[irOrderNo],
|
|
[irOrderLineNo])
|
|
VALUES (@IDB_OBJECT_ID,
|
|
@LineNo,
|
|
@attachedToLineNo,
|
|
@type,
|
|
@no,
|
|
@description,
|
|
@description2,
|
|
@locationCode,
|
|
@variantCode,
|
|
@quantity,
|
|
@unitOfMeasureCode,
|
|
@directUnitCost,
|
|
@lineDiscount,
|
|
@ableToDiscount,
|
|
@discountCalculated,
|
|
@workOrderNo,
|
|
@genBusPostingGroup,
|
|
@genProdPostingGroup,
|
|
@vatBusPostingGroup,
|
|
@vatProdPostingGroup,
|
|
@applToItemEntry,
|
|
@postingWithoutApply,
|
|
@anticipatedPayment,
|
|
@receiptNo,
|
|
@receiptLineNo,
|
|
@irOrderNo,
|
|
@irOrderLineNo)
|
|
|
|
FETCH NEXT FROM CURSOR_ACCOUNTING_POS INTO @VALUE
|
|
END
|
|
CLOSE CURSOR_ACCOUNTING_POS
|
|
DEALLOCATE CURSOR_ACCOUNTING_POS
|
|
|
|
|
|
END ELSE IF (@MANDATOR = 'SWE') BEGIN -- Ohne Bestelldaten aus NAV
|
|
|
|
DECLARE CURSOR_ACCOUNTING_POS CURSOR
|
|
LOCAL FAST_FORWARD FOR
|
|
SELECT [VALUE] FROM [dbo].[FNDD_GET_WINDREAM_INDEX_VALUES](@IDB_OBJECT_ID,@WD_VKR_ACCOUNTING,'; ',0,0);
|
|
|
|
OPEN CURSOR_ACCOUNTING_POS
|
|
FETCH NEXT FROM CURSOR_ACCOUNTING_POS INTO @VALUE
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @LineNo = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 1);
|
|
SET @LineNo = ISNULL(@LineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @attachedToLineNo = 0;
|
|
SET @attachedToLineNo = ISNULL(@attachedToLineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @type = (CASE
|
|
WHEN (SELECT [Item] FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 2) = 'Artikel'
|
|
THEN 2
|
|
ELSE (SELECT [Item] FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 2)
|
|
END);
|
|
SET @type = ISNULL(@type,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @no = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 3);
|
|
SET @no = ISNULL(@no,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @description = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 6);
|
|
SET @description = ISNULL(@description,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @description2 = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 7);
|
|
SET @description2 = ISNULL(@description2,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @locationCode = NULL;
|
|
SET @locationCode = ISNULL(@locationCode,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @variantCode = NULL;
|
|
SET @variantCode = ISNULL(@variantCode,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @quantity = (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.')))
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 9);
|
|
SET @quantity = ISNULL(@quantity,0.0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @unitOfMeasureCode = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 10);
|
|
SET @unitOfMeasureCode = ISNULL(@unitOfMeasureCode,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @directUnitCost = (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.')))
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 11);
|
|
SET @directUnitCost = ISNULL(@directUnitCost,0.0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @lineDiscount = (SELECT CONVERT(DECIMAL(38,2),(REPLACE([Item],',','.')))
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 16);
|
|
SET @lineDiscount = ISNULL(@lineDiscount,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @ableToDiscount = NULL;
|
|
SET @ableToDiscount = ISNULL(@ableToDiscount,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @discountCalculated = (CASE
|
|
WHEN (SELECT REPLACE([Item],',','.') FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR) WHERE [GUID] = 16) <> 0.0
|
|
THEN 1
|
|
ELSE 0
|
|
END);
|
|
SET @discountCalculated = ISNULL(@discountCalculated,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @workOrderNo = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 5);
|
|
SET @workOrderNo = ISNULL(@workOrderNo,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @POS_TAX_CODE = (SELECT [Item]
|
|
FROM [FNCUST_SPLIT_STRING_WITH_GUID] (@VALUE,@DATA_SEPERATOR)
|
|
WHERE [GUID] = 14);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @genBusPostingGroup = NULL;
|
|
SET @genBusPostingGroup = ISNULL(@genBusPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @genProdPostingGroup = NULL;
|
|
SET @genProdPostingGroup = ISNULL(@genProdPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @vatBusPostingGroup = (SELECT [VAT Bus_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_VAT_LIST](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR_TAX)
|
|
WHERE [CONTENT_CONCAT] = @POS_TAX_CODE);
|
|
SET @vatBusPostingGroup = ISNULL(@vatBusPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @vatProdPostingGroup = (SELECT [VAT Prod_ Posting Group]
|
|
FROM [FNCUST_GET_MS-DYN365BC_VAT_LIST](@MANDATOR,@ORDER_NR,@DATA_SEPERATOR_TAX)
|
|
WHERE [CONTENT_CONCAT] = @POS_TAX_CODE);
|
|
SET @vatProdPostingGroup = ISNULL(@vatProdPostingGroup,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @applToItemEntry = NULL;
|
|
SET @applToItemEntry = ISNULL(@applToItemEntry,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @postingWithoutApply = NULL;
|
|
SET @postingWithoutApply = ISNULL(@postingWithoutApply,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @anticipatedPayment = NULL;
|
|
SET @anticipatedPayment = ISNULL(@anticipatedPayment,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @receiptNo = NULL;
|
|
SET @receiptNo = ISNULL(@receiptNo,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @receiptLineNo = NULL;
|
|
SET @receiptLineNo = ISNULL(@receiptLineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @irOrderNo = NULL;
|
|
SET @irOrderNo = ISNULL(@irOrderNo,'');
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
SET @irOrderLineNo = NULL;
|
|
SET @irOrderLineNo = ISNULL(@irOrderLineNo,0);
|
|
-------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
INSERT INTO @vTB_INVOICE_POS_TRANSFER ([IDB_OBJECT_ID],
|
|
[LineNo],
|
|
[attachedToLineNo],
|
|
[type],
|
|
[no],
|
|
[description],
|
|
[description2],
|
|
[locationCode],
|
|
[variantCode],
|
|
[quantity],
|
|
[unitOfMeasureCode],
|
|
[directUnitCost],
|
|
[lineDiscount],
|
|
[ableToDiscount],
|
|
[discountCalculated],
|
|
[workOrderNo],
|
|
[genBusPostingGroup],
|
|
[genProdPostingGroup],
|
|
[vatBusPostingGroup],
|
|
[vatProdPostingGroup],
|
|
[applToItemEntry],
|
|
[postingWithoutApply],
|
|
[anticipatedPayment],
|
|
[receiptNo],
|
|
[receiptLineNo],
|
|
[irOrderNo],
|
|
[irOrderLineNo])
|
|
VALUES (@IDB_OBJECT_ID,
|
|
@LineNo,
|
|
@attachedToLineNo,
|
|
@type,
|
|
@no,
|
|
@description,
|
|
@description2,
|
|
@locationCode,
|
|
@variantCode,
|
|
@quantity,
|
|
@unitOfMeasureCode,
|
|
@directUnitCost,
|
|
@lineDiscount,
|
|
@ableToDiscount,
|
|
@discountCalculated,
|
|
@workOrderNo,
|
|
@genBusPostingGroup,
|
|
@genProdPostingGroup,
|
|
@vatBusPostingGroup,
|
|
@vatProdPostingGroup,
|
|
@applToItemEntry,
|
|
@postingWithoutApply,
|
|
@anticipatedPayment,
|
|
@receiptNo,
|
|
@receiptLineNo,
|
|
@irOrderNo,
|
|
@irOrderLineNo)
|
|
|
|
FETCH NEXT FROM CURSOR_ACCOUNTING_POS INTO @VALUE
|
|
END
|
|
CLOSE CURSOR_ACCOUNTING_POS
|
|
DEALLOCATE CURSOR_ACCOUNTING_POS
|
|
|
|
END;
|
|
|
|
RETURN;
|
|
|
|
END;
|
|
|
|
/***
|
|
|
|
-- TEST mit:
|
|
select * from [FNCUST_GET_INVOICE_POS_TRANSFER]('SWE',9229975,'EB00021129','~',' - ','Vektor String 422') -- mit bestellnr
|
|
SELECT * FROM [FNCUST_GET_MS-DYN365BC_VAT_LIST]('SWE','EB00021129',' - ') -- mit bestellnr
|
|
select * from [FNCUST_GET_INVOICE_POS_TRANSFER]('SWE',9229975,'EB9999999','~',' - ','Vektor String 422') -- ohne bestellnr
|
|
|
|
SELECT [VALUE] FROM [dbo].[FNDD_GET_WINDREAM_INDEX_VALUES](9209044,'Vektor String 422','; ',0,0);
|
|
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
|
|
SELECT * FROM [FNCUST_GET_MS-DYN365BC_ORDER_DATA_POS]('SWE','EB00020596','~') WHERE [Line No_] = @attachedToLineNo
|
|
(SELECT [Item] FROM [FNCUST_SPLIT_STRING_WITH_GUID] ((SELECT [VALUE] FROM [dbo].[FNDD_GET_WINDREAM_INDEX_VALUES](9174258,'Vektor String 422','; ',0,0)),'~') WHERE [GUID] = 1);
|
|
|
|
***/
|
|
GO
|
|
|
|
|