USE [BSSW_EU]
GO
/****** Object: StoredProcedure [CustomWorkflows].[setNextOttoRetour] Script Date: 16.05.2023 15:58:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [CustomWorkflows].[setNextOttoRetour] @kLieferschein INT AS
BEGIN
UPDATE tVersand
set kReturnVersandart = 9, cEnclosedReturnIdentCode =
CASE
WHEN CAST(cEnclosedReturnIdentCode AS BIGINT) = 'Deine letze vierstelligeNummer_9999_mitPrüfziffer_4' THEN 'Deine erste vierstelligeNummer_0001_mitPrüfziffer_4'
ELSE
(select top 1
CONCAT(
CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11))
,
CAST(CASE
WHEN
(CAST(LEFT(
LEFT(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),2,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),3,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),4,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),5,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),6,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),7,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),8,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),9,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),10,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),11,1)*4
,2)AS BIGINT)*10+10)
-
CAST(LEFT(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),2,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),3,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),4,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),5,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),6,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),7,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),8,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),9,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),10,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),11,1)*4 AS BIGINT)
=10
THEN 0
ELSE
(CAST(LEFT(
LEFT(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),2,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),3,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),4,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),5,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),6,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),7,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),8,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),9,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),10,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),11,1)*4
,2)AS BIGINT)*10+10)
-
CAST(LEFT(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),2,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),3,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),4,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),5,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),6,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),7,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),8,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),9,1)*4 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),10,1)*9 +
SUBSTRING(CAST(CAST(LEFT(cEnclosedReturnIdentCode,11) AS BIGINT)+1 AS nvarchar(11)),11,1)*4 AS BIGINT)
END AS varchar(1))
)
from tVersand
where cEnclosedReturnIdentCode <> '' AND dVersendet > '2022-11-29T16:47:27.753'
order by cEnclosedReturnIdentCode desc
)
END
WHERE kLieferschein = @kLieferschein
END
GO