86 lines
3.7 KiB
Transact-SQL
86 lines
3.7 KiB
Transact-SQL
USE [CWLDATEN_MEDP]
|
|
|
|
-- T-SQL Abfrage zur Ermittlung von Lagerstandsdifferenzen (Lagerstand zu Lagerortstruktur)
|
|
-- Stand: MK // 05.11.2021
|
|
-- Stand: TF // 08.11.2021
|
|
|
|
DECLARE
|
|
@ProductCount INT = 0,
|
|
@ProductNumber VARCHAR(30) = 0,
|
|
@ProductStock1 INT = 0,
|
|
@ProductStock2 INT = 0,
|
|
@ProductStockDiff INT = 0,
|
|
@WinLineMandantor VARCHAR(4) = 'MEDP',
|
|
@WinLineYear INT = 1452,
|
|
@ProductName VARCHAR(200) = 0;
|
|
|
|
DECLARE @TABLE as TABLE(ProductNumber VARCHAR(100), ProductName VARCHAR(200))
|
|
DECLARE @TABLE2 as TABLE(ProductNumber VARCHAR(100), ProductName VARCHAR(200), ProductStockDiff INT)
|
|
|
|
INSERT INTO @TABLE (ProductNumber,ProductName)
|
|
select DISTINCT c011,c003
|
|
from v021
|
|
where c014 = 0
|
|
and c011 is not null --LIKE 'PL%'
|
|
and c038 is null
|
|
and mesocomp = @WinLineMandantor and mesoyear = @WinLineYear;
|
|
|
|
Set @ProductCount = (select count(ProductNumber) from @Table)
|
|
|
|
PRINT '--------------------------------------'
|
|
PRINT 'Found ' + convert(varchar(100),@ProductCount) + ' Products!'
|
|
PRINT '--------------------------------------'
|
|
|
|
DECLARE CURSOR_PRODUCT_RESTOCK CURSOR FOR
|
|
SELECT ProductNumber, ProductName
|
|
FROM @TABLE
|
|
OPEN CURSOR_PRODUCT_RESTOCK
|
|
FETCH NEXT FROM CURSOR_PRODUCT_RESTOCK INTO @ProductNumber,@ProductName
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
PRINT 'Processing ProductNumber: ' + @ProductNumber
|
|
SET @ProductStock1 = 0
|
|
SET @ProductStock2 = 0
|
|
SET @ProductStockDiff = 0
|
|
|
|
select @ProductStock1 = ((t030.c008-t030.c009)-t030.c044)
|
|
from t030 -- = Tabellenbezeichnung: Artikel Lagerwerte
|
|
inner join v021 on t030.c015 = v021.c091 and t030.mesocomp = v021.mesocomp and t030.mesoyear = v021.mesoyear
|
|
where t030.c015 = @ProductNumber
|
|
and t030.mesocomp = @WinLineMandantor and t030.mesoyear = @WinLineYear
|
|
and v021.c079 <> 1
|
|
and v021.c178 <> 0
|
|
|
|
--IF (@ProductStock1 < 0)
|
|
-- SET @ProductStock1 = 0;
|
|
|
|
select @ProductStock2 = SUM(t299.c011)
|
|
from t299 -- = Tabellenbezeichnung: Artikelstamm Lagerorte
|
|
inner join v021 on t299.c000 = v021.c002 and t299.mesocomp = v021.mesocomp and t299.mesoyear = v021.mesoyear
|
|
where t299.c000 = @ProductNumber
|
|
and t299.mesocomp = @WinLineMandantor and t299.mesoyear = @WinLineYear
|
|
and v021.c079 <> 1
|
|
and v021.c178 <> 0
|
|
|
|
-- IF (@ProductStock2 < 0)
|
|
-- SET @ProductStock2 = 0;
|
|
|
|
IF (@ProductStock1 <> @ProductStock2)
|
|
BEGIN
|
|
Set @ProductStockDiff = @ProductStock1 - @ProductStock2
|
|
PRINT 'ProductStock1 from T030: ' + convert(varchar(100), @ProductStock1)
|
|
PRINT 'ProductStock2 from T299: ' + convert(varchar(100), @ProductStock2)
|
|
PRINT 'ProductStockDiff: ' + convert(varchar(100), @ProductStockDiff)
|
|
INSERT INTO @TABLE2 (ProductNumber, ProductName, ProductStockDiff) values (@ProductNumber,@ProductName, @ProductStockDiff);
|
|
END
|
|
PRINT '--------------------------------------'
|
|
|
|
FETCH NEXT FROM CURSOR_PRODUCT_RESTOCK INTO @ProductNumber, @ProductName
|
|
|
|
END
|
|
CLOSE CURSOR_PRODUCT_RESTOCK
|
|
DEALLOCATE CURSOR_PRODUCT_RESTOCK
|
|
|
|
SELECT * FROM @TABLE2
|
|
|