RetBeholdningTilFørStatus.sql

USE Trio
go

-- Find status --
DECLARE @retailunitno VARCHAR(13) = '24385';

SELECT TOP 100 (
	SELECT COUNT(*) FROM InventoryCountingItemLocal icil WHERE icil.InventoryCountingId = ic.InventoryCountingId) AS IcilCount
	,(SELECT COUNT(*) FROM InventoryCountingItemLocal icil WHERE icil.InventoryCountingId = ic.InventoryCountingId AND icil.InventoryQty IS NULL) AS NoInvQtyIcilCount
	,ic.*
--ru.RetailUnitNo, ic.InventoryCountingDate
FROM InventoryCounting ic
INNER JOIN RetailUnit ru ON ru.RetailUnitId = ic.RetailUnitId
WHERE InventoryCountingGroupId = 'B617A2A4-3AE6-40C8-ABE0-871238F98455'
	AND InventoryCountingStatusCodeId = 'CA39B890-D137-4ADB-83BA-658D3023D14C'
	AND ic.InventoryCountingDate >= DATEADD(MONTH, - 6, GETDATE())
	AND ru.RetailUnitNo = @retailunitno
ORDER BY ic.InventoryCountingDate DESC

BEGIN TRAN;

-- Ret beholdning for status --
DECLARE @inventoryCountingId UNIQUEIDENTIFIER = '------ IC GUID ------'; -- Indsæt IC fra ovenstående query
DECLARE @user VARCHAR(10) = 'phh';
DECLARE @now DATETIME = GETDATE();

DECLARE @temp TABLE (
	ItemLocalId UNIQUEIDENTIFIER
	,AdjustmentQty INT
	);

WITH cte AS (
	SELECT ilia.ItemLocalId
		,max(ilia.CreatedDateTime) AS DATE
		,SUM(ilia.AdjustmentQty) AS totalAdjustment
	FROM trio..ItemLocalInventoryAdjustment ilia
	WHERE ReferenceId = @inventoryCountingId
	GROUP BY ilia.ItemLocalId
	)
,cte1 AS (
	SELECT ilia.ItemLocalId
		,ilia.AdjustmentQty
		,ccm.Code
	FROM trio..ItemLocalInventoryAdjustment ilia
	INNER JOIN trio..CommonCodeMaster ccm ON ilia.ReferenceTypeId = ccm.CommonCodeMasterId
	INNER JOIN cte ON ilia.ItemLocalId = cte.ItemLocalId
	WHERE ccm.Code IN ('11','10','13')
		AND ilia.CreatedDateTime > cte.DATE
	)
,cte2 AS (
	SELECT cte.ItemLocalId
		,cte.totalAdjustment
		,il.InventoryQty
		,cast(- (cte.totalAdjustment / 3 * 2) AS INT) AS adjust
		,il.InventoryQty + cast(- (cte.totalAdjustment / 3 * 2) AS INT) AS newInventory
	FROM cte
	INNER JOIN trio..ItemLocal il ON cte.ItemLocalId = il.ItemLocalId
	WHERE cte.ItemLocalId NOT IN (
			SELECT ItemLocalId
			FROM cte1
			)
	)
--SELECT * FROM cte2

UPDATE il
SET il.InventoryQty = cte2.newInventory
	,il.Version = il.Version + 1
OUTPUT inserted.ItemLocalId
	,cte2.adjust
INTO @temp(ItemLocalId, AdjustmentQty)
FROM trio..ItemLocal il
INNER JOIN cte2 ON il.ItemLocalId = cte2.ItemLocalId;

INSERT INTO trio..ItemLocalInventoryAdjustment (
	ItemLocalInventoryAdjustmentId
	,ItemLocalId
	,ReferenceId
	,ReferenceDetailId
	,ReferenceTypeId
	,AdjustmentQty
	,LocallyCreatedBy
	,CreatedDateTime
	,CreatedBy
	,ModifiedDateTime
	,ModifiedBy
	,Version
	,PartitioningKey
	)
SELECT NEWID()
	,ItemLocalId
	,@inventoryCountingId
	,NEWID()
	,'E4151D0B-9244-4272-A4A7-3BCAEF32FB14'
	,AdjustmentQty
	,NULL
	,@now
	,@user
	,@now
	,@user
	,0
	,1
FROM @temp

--COMMIT TRAN
ROLLBACK TRAN