CREATE TRIGGER [dbo].[T_PurchaseInvoices_UpdateQty] 
   ON  [dbo].[PurchaseInvoices]
   FOR UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

	IF UPDATE(LocationID) OR EXISTS(SELECT * FROM deleted)
		BEGIN
			SELECT * INTO #inserted_tmp FROM inserted
			SELECT * INTO #deleted_tmp FROM deleted
		
			EXEC spHeaderUpdateQty 'PurchaseInvoiceDetails', 'PInvoiceID', default, '+', 'InStock'
		END

	SET NOCOUNT OFF

END

CREATE PROCEDURE [dbo].[spHeaderUpdateQty]
    @tablename VARCHAR(100),
    @primarykey VARCHAR(100),
	@locationfield VARCHAR(100) = 'LocationID',
    @addminus CHAR(1),
	@uom VARCHAR(20) = ''
AS

BEGIN
    DECLARE
           @row_count       INT,
           @error_number    INT,
           @error_message   VARCHAR(255),
           @sql VARCHAR(4000)
          
    SET NOCOUNT ON;

    SET @sql = 'SELECT i.ItemID 
				FROM ' + @tablename + ' p INNER JOIN Items i ON p.ItemID = i.ItemID
				INNER JOIN #inserted_tmp t ON p.' + @primarykey + ' = t.' + @primarykey +
				' WHERE KeepStock = 1'

	EXEC (@sql)
             
	SELECT @row_count = @@rowcount

    IF (@row_count > 0)
        BEGIN
			SET @sql = 'UPDATE [dbo].[ItemLocations]
						SET Qty = CASE p.Draft WHEN 1 THEN i.Qty ELSE i.Qty ' + @addminus + '
						ISNULL(dbo.GetQtyByUOM(t.UOMID, s.' + @uom + 'UOMID, t.Qty), t.Qty) END
						FROM [dbo].[ItemLocations] i, [dbo].[' + @tablename + '] t, [#inserted_tmp] p,
							[dbo].[Items] s
						WHERE s.ItemID = t.ItemID AND s.KeepStock = 1 AND 
								p.' + @primarykey + ' = t.' + @primarykey + 
								' AND i.ItemID = t.ItemID AND i.LocationID = p.' + @locationfield

			EXEC (@sql)

			SELECT @row_count = @@rowcount
			IF @row_count = 0
				BEGIN
					SELECT @error_number=50001,
						   @error_message='No item qty is updated.'
						   GOTO error
				END
			ELSE
				PRINT 'Qty updated'
	END
        
	SET @sql = 'SELECT i.ItemID 
				FROM ' + @tablename + ' p INNER JOIN Items i ON p.ItemID = i.ItemID
				INNER JOIN #deleted_tmp t ON p.' + @primarykey + ' = t.' + @primarykey +
				' WHERE KeepStock = 1'
    
	EXEC (@sql)

	SELECT @row_count = @@rowcount

    IF (@row_count > 0)
		BEGIN
			IF (@addminus = '+')
				BEGIN
					SET @addminus = '-'
				END
			ELSE
				BEGIN
					SET @addminus = '+'
				END

			SET @sql = 'UPDATE [dbo].[ItemLocations]
						SET Qty = CASE p.Draft WHEN 1 THEN i.Qty ELSE i.Qty ' + @addminus + ' 
						ISNULL(dbo.GetQtyByUOM(t.UOMID, s.' + @uom + 'UOMID, t.Qty), t.Qty) END
						FROM [dbo].[ItemLocations] i, [dbo].[' + @tablename + '] t, [#deleted_tmp] p,
							[dbo].[Items] s
						WHERE s.ItemID = t.ItemID AND s.KeepStock = 1 AND
								p.' + @primarykey + ' = t.' + @primarykey + 
								' AND i.ItemID = t.ItemID AND i.LocationID = p.' + @locationfield

			EXEC (@sql)

			SELECT @row_count = @@rowcount
			IF @row_count = 0
				BEGIN
					SELECT @error_number=50001,
						   @error_message='No item qty is reverted.'
						   GOTO error
				END
			ELSE
				PRINT N'Qty reverted.'
		END

	SET NOCOUNT OFF

    RETURN

/*  Error Handling  */
error:
      RAISERROR @error_number @error_message
      ROLLBACK TRANSACTION

      SET NOCOUNT OFF

END