111 lines
3.8 KiB
Transact-SQL
111 lines
3.8 KiB
Transact-SQL
-- dbo.ef_Vyroba_VratNovyPaletovyList
|
||
CREATE FUNCTION dbo.ef_Vyroba_VratNovyPaletovyList (@typ TINYINT=1, @radaVPr NVARCHAR(10)=N'', @cisloOrg INT=NULL, @idKmenZbozi INT=NULL)
|
||
RETURNS NVARCHAR(50)
|
||
AS
|
||
BEGIN
|
||
|
||
-- funkci neupravujte, stejne bude prepsana pri spusteni pluginu pro Planovani nebo Windows sluzbou HDC API
|
||
|
||
DECLARE
|
||
@retVal NVARCHAR(15),
|
||
@bi BIGINT,
|
||
@maska NVARCHAR(100)=NULL,
|
||
@delka TINYINT,
|
||
@sz NVARCHAR(3)
|
||
|
||
DECLARE
|
||
@skladNestd NVARCHAR(30)=N'211',
|
||
@radaPrijemNestd NVARCHAR(3)='270' -- vyrobky PEK
|
||
|
||
|
||
SET @bi = 1 + ISNULL( (SELECT MAX(TRY_PARSE(VyrCislo AS BIGINT)) FROM dbo.TabVyrCisPrikaz
|
||
WHERE VyrCislo LIKE N'5[2-9]%' AND LEN(VyrCislo)=7 AND ISNUMERIC(VyrCislo)=1 AND TRY_CAST(VyrCislo AS BIGINT) IS NOT NULL ), 0)
|
||
IF (@bi = 1)
|
||
SET @bi = 5200001
|
||
SET @retVal = CONVERT(nvarchar, @bi)
|
||
|
||
-- IF EXISTS(SELECT 1 FROM Information_schema.Routines WHERE Specific_schema=N'dbo' AND specific_name=N'ef_Vyroba_VratNovyPaletovyList1' AND Routine_Type=N'FUNCTION')
|
||
IF EXISTS(SELECT 1 FROM sys.objects o JOIN sys.schemas s ON (s.schema_id=o.schema_id) WHERE o.name=N'ef_Vyroba_VratNovyPaletovyList1' AND s.name=N'dbo' AND o.type='FN')
|
||
SET @retVal = dbo.ef_Vyroba_VratNovyPaletovyList1 (@typ, @radaVPr, @cisloOrg, @idKmenZbozi)
|
||
|
||
|
||
|
||
/*
|
||
|
||
IF (@typ=2)
|
||
BEGIN
|
||
SET @delka = 0
|
||
IF (@radaVPr<>N'')
|
||
BEGIN
|
||
SET @maska=(SELECT MaskaProGenVyrCis FROM dbo.TabRadyPrikazu WHERE Rada=@radaVPr)
|
||
IF (@maska IS NOT NULL)
|
||
IF (@maska LIKE N'#%C')
|
||
SET @delka = CONVERT(tinyint, LEFT(SUBSTRING(@maska, 2, 50), CHARINDEX(N'C', SUBSTRING(@maska, 2, 50))-1))
|
||
END
|
||
|
||
SELECT @bi = 1 + MAX(ISNULL(TRY_PARSE(VyrCislo AS BIGINT), 0)) FROM dbo.TabVyrCisPrikaz
|
||
SET @retVal = CONVERT(nvarchar, @bi)
|
||
|
||
IF (@maska IS NULL) AND (@typ=2)
|
||
BEGIN
|
||
SELECT @bi = 1 + MAX(ISNULL(TRY_PARSE(VyrCislo AS BIGINT), 23000000)) FROM dbo.TabVyrCisPrikaz
|
||
SET @retVal = CONVERT(nvarchar, @bi)
|
||
END
|
||
ELSE
|
||
IF (@delka>0)
|
||
SET @retVal = REPLICATE(N'0', @delka - LEN(@retVal)) + @retVal
|
||
END
|
||
|
||
|
||
IF (@typ=3)
|
||
BEGIN
|
||
SET @bi = 1 + ISNULL( (SELECT MAX(TRY_PARSE(CisloPalety AS BIGINT)) FROM dbo._TabVyroba_Palety WHERE Typ=1), 23000000)
|
||
SET @retVal = CONVERT(nvarchar, @bi)
|
||
END
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
IF (@typ=1) -- prijem nestandard
|
||
BEGIN
|
||
SELECT @bi = 1 + ISNULL(MAX(x.num), 8200000)
|
||
FROM (SELECT TRY_PARSE(vcs.Nazev1 AS BIGINT) AS num FROM dbo.TabPohybyZbozi p
|
||
INNER JOIN dbo.TabDokladyZbozi d ON (d.ID=p.IDDoklad)
|
||
INNER JOIN dbo.TabStavSkladu s ON (s.ID=p.IDZboSklad) INNER JOIN dbo.TabKmenZbozi k ON (k.ID=s.IDKmenZbozi)
|
||
INNER JOIN dbo.TabVyrCP vcp ON (vcp.IDPolozkaDokladu=p.ID) INNER JOIN dbo.TabVyrCS vcs ON (vcs.IDStavSkladu=p.IDZboSklad)
|
||
WHERE k.Nazev1 LIKE N'%nestandart%' AND CONVERT(nvarchar, p.Poznamka)=N'P<><50>jem nestandard' AND d.DruhPohybuZbo=0
|
||
AND d.IDSklad=@skladNestd AND ISNUMERIC(vcs.Nazev1)=1 AND d.RadaDokladu=@radaPrijemNestd
|
||
) x
|
||
SET @retVal = CONVERT(nvarchar, @bi)
|
||
END
|
||
|
||
|
||
IF (@idKmenZbozi IS NOT NULL) AND (@typ<>1)
|
||
BEGIN
|
||
SELECT @sz=SkupZbo FROM dbo.TabKmenZbozi WHERE ID=@idKmenZbozi
|
||
|
||
IF (@sz LIKE N'7%' AND @sz NOT LIKE N'70%')
|
||
SET @bi = 1 + ISNULL( (SELECT MAX(TRY_PARSE(CisloPalety AS BIGINT)) FROM dbo._TabVyroba_Palety WHERE Typ=1), 5200000)
|
||
|
||
IF (@sz LIKE N'6%')
|
||
SET @bi = 1 + ISNULL( (SELECT MAX(TRY_PARSE(CisloPalety AS BIGINT)) FROM dbo._TabVyroba_Palety WHERE Typ=2), 6200000)
|
||
|
||
IF (@sz LIKE N'1%')
|
||
SET @bi = 1 + ISNULL( (SELECT MAX(TRY_PARSE(CisloPalety AS BIGINT)) FROM dbo._TabVyroba_Palety WHERE Typ=3), 7200000)
|
||
|
||
IF (@sz LIKE N'60%')
|
||
SET @bi = 1 + ISNULL( (SELECT MAX(TRY_PARSE(CisloPalety AS BIGINT)) FROM dbo._TabVyroba_Palety WHERE Typ=4), 8200000)
|
||
|
||
SET @retVal = CONVERT(nvarchar, @bi)
|
||
END
|
||
|
||
*/
|
||
|
||
RETURN @retVal
|
||
|
||
|
||
END
|