BeholdningTilNul.sql

DECLARE @itemNo VARCHAR(13) = '5700384325212'
DECLARE @retailUnitNo VARCHAR(13) = NULL -- NULL = all
DECLARE @username VARCHAR(13) = 'manuel/ph99epp'

BEGIN TRANSACTION;

INSERT INTO [TRIO].[dbo].[ItemLocalInventoryAdjustment] (
	[ItemLocalInventoryAdjustmentId]
	,[ItemLocalId]
	,[ReferenceId]
	,[ReferenceDetailId]
	,[ReferenceTypeId]
	,[AdjustmentQty]
	,[LocallyCreatedBy]
	,[CreatedDateTime]
	,[CreatedBy]
	,[ModifiedDateTime]
	,[ModifiedBy]
	,[Version]
	,[PartitioningKey]
	)
SELECT NEWID()
	,il.ItemLocalId
	,il.ItemLocalId
	,NULL
	,Trio.dbo.GetCommonCodeId('ReferenceType', '13') -- Manuel
	,- il.InventoryQty
	,@username
	,GETDATE()
	,@username
	,GETDATE()
	,@username
	,1
	,1
FROM Trio..Item i
INNER JOIN Trio..ItemLocal il ON il.ItemId = i.ItemId
INNER JOIN Trio..RetailUnit ru ON ru.RetailUnitId = il.RetailUnitId
WHERE i.ItemNo = @itemNo
	AND ru.IsActive = 1
	AND (
		@retailUnitNo IS NULL
		OR ru.RetailUnitNo = @retailUnitNo
		)
	AND il.InventoryQty <> 0;

UPDATE il
SET InventoryQty = 0
	,Version = il.Version + 1
FROM Trio..Item i
INNER JOIN Trio..ItemLocal il ON il.ItemId = i.ItemId
INNER JOIN Trio..RetailUnit ru ON ru.RetailUnitId = il.RetailUnitId
WHERE i.ItemNo = @itemNo
	AND ru.IsActive = 1
	AND (
		@retailUnitNo IS NULL
		OR ru.RetailUnitNo = @retailUnitNo
		)
	AND il.InventoryQty <> 0;

COMMIT;