
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_UpdateMasterInventoryTotals]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_UpdateMasterInventoryTotals]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	
--		Set InventoryManagement.Available
--		Set InventoryItems Totals
--		Set PartsMasters TotalAvailable
-- =============================================
CREATE PROCEDURE [dbo].[sp_CAPI1_UpdateMasterInventoryTotals] 
AS
BEGIN
	-- Update Inventory Management Items
	UPDATE publ_InventoryManagement SET 
		InventoryManagement_InventoryManagementStockDetails_InventoryManagementAvailable = 
			ISNULL([InventoryManagement_InventoryManagementStockDetails_InventoryManagementOnHand], 0) - ISNULL([InventoryManagement_InventoryManagementStockDetails_InventoryManagementCommitted], 0)
	WHERE isActive = 1

	-- Update Inventory Items
	UPDATE ii SET 
		InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemTotalOnHand = t.sumOnHand
		,InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemTotalCommitted = t.sumCommitted
		,InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemTotalAvailable = t.sumAvailable
		,InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemTotalOnOrder = t.sumOnOrder
	FROM publ_InventoryItems ii
	INNER JOIN
		(
			SELECT im.ParentSurfaceItemId	
				,sumOnHand = SUM(ISNULL(im.[InventoryManagement_InventoryManagementStockDetails_InventoryManagementOnHand], 0))
				,sumCommitted = SUM(ISNULL(im.[InventoryManagement_InventoryManagementStockDetails_InventoryManagementCommitted], 0))
				,sumAvailable = SUM(ISNULL(im.[InventoryManagement_InventoryManagementStockDetails_InventoryManagementAvailable], 0))
				,sumOnOrder = SUM(ISNULL(im.[InventoryManagement_InventoryManagementStockDetails_InventoryManagementOnOrder], 0))
			FROM publ_InventoryManagement im
			WHERE im.isActive = 1
			GROUP BY im.ParentSurfaceItemId
		) t
		ON t.ParentSurfaceItemId = ii.itemID
	WHERE ii.isActive = 1
	AND ii.itemID IN (SELECT ParentSurfaceItemId FROM publ_InventoryManagement)

	-- Update PartsMasters
	UPDATE p
	SET p.InventoryManagement_InventoryManagement_InventoryTotalAvailable = t.sumAvailable
	FROM publ_PartsMaster AS p
	INNER JOIN
		(
			SELECT ii.ParentSurfaceItemId, SUM(ISNULL(ii.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemTotalAvailable, 0)) sumAvailable
			FROM publ_InventoryItems ii
			WHERE ii.isActive = 1
			GROUP BY ii.ParentSurfaceItemId 
		) t
		ON t.ParentSurfaceItemId = p.itemID
	WHERE p.isActive = 1
	AND p.itemID IN (SELECT ParentSurfaceItemId FROM publ_InventoryItems WHERE isActive = 1)

	RETURN 0
END
GO
