[CFAMX-11975] Rewrite fn_ListInventoryDetails - HotSchedules JIRA

Details

Type: Feature Request
Status: Dev Ready (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Performance-EOM
- TechDebt

Description

Rewrite the function/sproc to match what was done to improve performance in the trunk:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'dbo.qry_ListInventoryDetails') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE dbo.qry_ListInventoryDetails
GO
CREATE PROCEDURE dbo.qry_ListInventoryDetails
	@EntityId BIGINT,
	@StartTime DATETIME,
	@EndTime DATETIME,
	@ItemId BIGINT = NULL
AS
BEGIN
	---------------------------------------------------------------------------------------------------------------------------------------------
	/*	This Procedure is a conversion of fn_ListInventoryDetails so that SQL Server can join the output more efficiently.
		Before calling this procedure, the calling proc must create the #qry_ListInventoryDetails temp table.
		It can do this my executing the following code. This SP is then to be executed like INSERT INTO#qry_ListInventoryDetails EXEC dbo.qry_ListInventoryDetails @EntityId, @StartTime, @EndTime
		Then #qry_ListInventoryDetails can be used in place of the table valued function fn_ListInventoryDetails()

		CREATE TABLE #qry_ListInventoryDetails
		(
			 ItemID bigint not null primary key,
			 OpeningValue float,
			 OpeningQty float,
			 ClosingValue float,
			 ClosingQty float,
			 PurchaseValue float,
			 PurchaseQty float,
			 PurchaseExclReturnsValue float,
			 PurchaseExclReturnsQty float,
			 PurchaseReturnsValue float,
			 PurchaseReturnsQty float,
			 TransferInValue float,
			 TransferInQty float,
			 TransferOutValue float,
			 TransferOutQty float,
			 BOMMakeValue float,
			 BOMMakeQty float,
			 BOMUseValue float,
			 BOMUseQty float,
			 SalesValue float,
			 SalesQty float,
			 WasteValue float,
			 WasteQty float,
			 SamplingValue float,
			 SamplingQty float,
			 OtherAdjustValue float,
			 OtherAdjustQty float,
			 DispatchValue float,
			 DispatchQty float,
			 FinishedWasteValue float,
			 FinishedWasteQty float,
			 RawWasteValue float,
			 RawWasteQty float,
			 Expected_Qty float,
			 Expected_Amt float,
			 Variance_Qty float,
			 Variance_Amt float
		); 
	*/
	---------------------------------------------------------------------------------------------------------------------------------------------
	BEGIN TRY
		IF OBJECT_ID('tempdb..#InventoryAdjustments_Opening') IS NOT NULL
			DROP TABLE #InventoryAdjustments_Opening;
		IF OBJECT_ID('tempdb..#InventoryAdjustments_Closing') IS NOT NULL
			DROP TABLE #InventoryAdjustments_Closing;
		IF OBJECT_ID('tempdb..#ApplyDates_Closing') IS NOT NULL
			DROP TABLE #ApplyDates_Closing;
		IF OBJECT_ID('tempdb..#ApplyDates_Opening') IS NOT NULL
			DROP TABLE #ApplyDates_Opening;
		IF OBJECT_ID('tempdb..#OpeningStock') IS NOT NULL
			DROP TABLE #OpeningStock;
		IF OBJECT_ID('tempdb..#ClosingStock') IS NOT NULL
			DROP TABLE #ClosingStock;
		IF OBJECT_ID('tempdb..#tmpInventoryUsage') IS NOT NULL
			DROP TABLE #tmpInventoryUsage;
		IF OBJECT_ID('tempdb..#tmpDailyStockBalance') IS NOT NULL
			DROP TABLE #tmpDailyStockBalance;

		DECLARE @StartBusinessDay DATETIME, @StartBusinessDayOpen DATETIME, @StartBusinessDayClose DATETIME,
						@EndBusinessDay DATETIME, @EndBusinessDayOpen DATETIME, @EndBusinessDayClose DATETIME

		-- get start business day start and end times
		SELECT @StartBusinessDay = CalendarDay, @StartBusinessDayOpen = DayOpen, @StartBusinessDayClose = DayClose
		FROM dbo.fn_GetTradingForTradingDate (@EntityId, @StartTime)
		
		-- get end business day start and end times
		SELECT @EndBusinessDay = CalendarDay, @EndBusinessDayOpen = DayOpen, @EndBusinessDayClose = DayClose
		FROM dbo.fn_GetTradingForTradingDate (@EntityId, @EndTime) 
 
		DECLARE @MinBusinessDay DATETIME, @MinBusinessDayOpen DATETIME, @MinBalanceBusinessDay DATETIME, @MinBalanceBusinessDayOpen DATETIME, @MaxBusinessDay DATETIME, @MaxBusinessDayClose DATETIME
		DECLARE @MinApplyDate DATETIME
		-- get the min and max business day values, this will enforce the outerbounds on all queries on dbo.tbDailyStockBalance and will improve overall performance of this query
		SELECT
			@MinBalanceBusinessDay = MIN(LastBusinessDay),
			@MinBalanceBusinessDayOpen = MIN(LastBusinessDayOpen),
			@MinBusinessDay = MIN(LastBusinessDay),
			@MinBusinessDayOpen = MIN(LastBusinessDayOpen)
		FROM inventory.vwEntityItemDailyStockBalance WITH (NOLOCK)
		WHERE EntityId = @EntityId
 
		DECLARE @PrevStartBusinessDay DateTime = Case WHEN @StartBusinessDay > '1753-01-01' THEN DATEADD(DAY, -1, @StartBusinessDay) ELSE @StartBusinessDay END,
				@PrevEndBusinessDay DateTime = Case WHEN @EndBusinessDay > '1753-01-01' THEN DATEADD(DAY, -1, @EndBusinessDay) ELSE @EndBusinessDay END

	IF @MinBusinessDay >= @StartBusinessDay
		BEGIN
			SELECT
				@MinBusinessDay=CalendarDay,
				@MinBusinessDayOpen=DayOpen
			FROM dbo.tbMxDay WITH (NOLOCK)
			WHERE EntityId = @EntityId
				AND CalendarDay=@PrevStartBusinessDay
		END

		SET @MaxBusinessDay = @EndBusinessDay
		SET @MaxBusinessDayClose = @EndBusinessDayClose

		CREATE TABLE #ApplyDates_Closing
		(	
			EntityId BIGINT NOT NULL, 
			ItemId BIGINT NOT NULL,
			AdjustApplyDate DATETIME,
			BusinessDay DATETIME,
			TotalsApplyDate DATETIME,
			PRIMARY KEY CLUSTERED (EntityId, ItemId)
		)

		SELECT *
		INTO #tmpDailyStockBalance
		FROM tbDailyStockBalance
		WHERE EntityID = @EntityId
			AND BusinessDay BETWEEN @MinBusinessDay AND @EndBusinessDay
			AND ItemId = ISNULL(@ItemId, ItemId)

		INSERT INTO #ApplyDates_Closing
		-- get the latest business day to get opening values from
		-- get the applydate of any item stock counts that have occurred inbetween
		SELECT
			availableItems.EntityId,
			availableItems.ItemId,
			MAX(COALESCE(appliedStockCount.ApplyDate,
			CASE
				WHEN availableItems.LastBusinessDay < @EndBusinessDay THEN availableItems.LastBusinessDayClose
				ELSE @EndBusinessDayOpen
			END)) AS AdjustApplyDate,
			MAX(CASE
					WHEN availableItems.LastBusinessDay <= @PrevEndBusinessDay THEN LastBusinessDay
					ELSE @PrevEndBusinessDay
				END
				) AS BusinessDay,
			MAX(CASE
				WHEN availableItems.LastBusinessDay <= @PrevEndBusinessDay THEN availableItems.LastBusinessDayClose
				ELSE @EndBusinessDayOpen
				END
				) AS TotalsApplyDate
		FROM inventory.vwEntityItemDailyStockBalance availableItems
			LEFT JOIN inventory.vwTransactionStockCountApplyDates appliedStockCount ON availableItems.EntityId=appliedStockCount.EntityId 
				AND availableItems.ItemId = appliedStockCount.ItemId
				AND appliedStockCount.ApplyDate <= @EndTime
				-- add this in to ensure the result set does not continue to grow based on the size of the db
				-- this will improve the overall performance of this query and ensure it doesn't degrade over time
				AND appliedStockCount.ApplyDate >= @MinBusinessDayOpen
				AND (
						(availableItems.LastBusinessDay <= @PrevEndBusinessDay AND appliedStockCount.ApplyDate>=LastBusinessDayClose)
						OR (availableItems.LastBusinessDay > @PrevEndBusinessDay AND appliedStockCount.ApplyDate>=@EndBusinessDayOpen)
					)
		WHERE availableItems.EntityId = @EntityId
			AND availableItems.ItemId = ISNULL(@ItemId, availableItems.ItemId)
		GROUP BY
			availableItems.EntityId,
			availableItems.ItemId

		CREATE TABLE #InventoryAdjustments_Closing
		(
			EntityId BIGINT NOT NULL, 
			ItemId BIGINT NOT NULL,
			StockAmount MONEY,
			StockQty FLOAT,
			BusinessDay DATETIME,
			TotalsApplyDate DATETIME,
			AdjustValue MONEY,
			AdjustQty FLOAT,
			PRIMARY KEY CLUSTERED (EntityId, ItemId)
		)

		SELECT @MinApplyDate = MIN(AdjustApplyDate) FROM #ApplyDates_Closing

		INSERT INTO #InventoryAdjustments_Closing
		SELECT
			applyDates.EntityId,
			applyDates.ItemId,
			stockCount.StockAmount,
			stockCount.StockQty,
			MIN(applyDates.BusinessDay) AS BusinessDay,
			MIN(applyDates.TotalsApplyDate) AS TotalsApplyDate,
			SUM(ISNULL(adjustments.PurchaseExclReturnsValue 
				- adjustments.PurchaseReturnsValue 
				+ adjustments.TransferInValue 
				- adjustments.TransferOutValue 
				+ adjustments.BOMMakeValue 
				- adjustments.BOMUseValue 
				- adjustments.SalesValue 
				- adjustments.WasteValue 
				- adjustments.SamplingValue 
				- adjustments.OtherAdjustValue 
				- adjustments.DispatchValue,0)) AS AdjustValue,
			SUM(ISNULL(adjustments.PurchaseExclReturnsCount 
				- adjustments.PurchaseReturnsCount 
				+ adjustments.TransferInCount 
				- adjustments.TransferOutCount 
				+ adjustments.BOMMakeCount 
				- adjustments.BOMUseCount 
				- adjustments.SalesCount 
				- adjustments.WasteCount 
				- adjustments.SamplingCount 
				- adjustments.OtherAdjustCount 
				- adjustments.DispatchCount,0)) As AdjustQty
		FROM #ApplyDates_Closing applyDates
			-- get any inventory adjustments from the last stock count to the selected end date
			-- this is used to get your inventory usage from your last on hand levels to the selected time
			LEFT JOIN inventory.vwPopulateDailyStockBalance adjustments
				ON adjustments.EntityId=applyDates.EntityId
				AND adjustments.ItemId=applyDates.ItemId
				AND adjustments.ApplyDate>=applyDates.AdjustApplyDate
				AND adjustments.ApplyDate<@EndTime
				-- add this in to ensure the result set does not continue to grow based on the size of the db
				-- this will improve the overall performance of this query and ensure it doesn't degrade over time
				AND adjustments.ApplyDate>=@MinApplyDate
				AND adjustments.EntityId=@EntityId
			LEFT JOIN inventory.vwTransactionStockCountApplyDates stockCount
				ON stockCount.EntityId=applyDates.EntityId
				AND stockCount.ItemId=applyDates.ItemId
				AND stockCount.ApplyDate=applyDates.AdjustApplyDate
				-- add this in to ensure the result set does not continue to grow based on the size of the db
				-- this will improve the overall performance of this query and ensure it doesn't degrade over time
				AND stockCount.ApplyDate>=@MinBusinessDayOpen
				AND stockCount.ApplyDate<=@EndTime
				AND stockCount.EntityId=@EntityId
		GROUP BY
			applyDates.EntityId,
			applyDates.ItemId,
			stockCount.StockAmount,
			stockCount.StockQty

		CREATE TABLE #ApplyDates_Opening
		(
			EntityId BIGINT NOT NULL, 
			ItemId BIGINT NOT NULL,
			AdjustApplyDate DATETIME,
			BusinessDay DATETIME,
			TotalsApplyDate DATETIME,
			PRIMARY KEY CLUSTERED (EntityId, ItemId)
		)

		-- get the latest business day to get opening values from
		-- get the applydate of any item stock counts that have occurred inbetween
		INSERT INTO #ApplyDates_Opening
		SELECT
			availableItems.EntityId,
			availableItems.ItemId,
			MAX(COALESCE(appliedStockCount.ApplyDate,
				CASE
				WHEN availableItems.LastBusinessDay < @StartBusinessDay THEN availableItems.LastBusinessDayClose
				ELSE @StartBusinessDayOpen
				END)) AS AdjustApplyDate,
			MAX(CASE
					WHEN availableItems.LastBusinessDay <= @PrevStartBusinessDay THEN LastBusinessDay  
					ELSE @PrevStartBusinessDay  
				END
				) AS BusinessDay,
			MAX(CASE
					WHEN availableItems.LastBusinessDay <= @PrevStartBusinessDay THEN availableItems.LastBusinessDayClose  
					ELSE @StartBusinessDayOpen
				END
				) AS TotalsApplyDate
		FROM inventory.vwEntityItemDailyStockBalance availableItems
			LEFT JOIN inventory.vwTransactionStockCountApplyDates appliedStockCount
				ON availableItems.EntityId = appliedStockCount.EntityId
				AND availableItems.ItemId = appliedStockCount.ItemId
				AND appliedStockCount.ApplyDate <= @StartTime
				-- add this in to ensure the result set does not continue to grow based on the size of the db
				-- this will improve the overall performance of this query and ensure it doesn't degrade over time
				AND appliedStockCount.ApplyDate >= @MinBusinessDayOpen
				AND (  
						(availableItems.LastBusinessDay <= @PrevStartBusinessDay AND appliedStockCount.ApplyDate >= LastBusinessDayClose)  
						OR (availableItems.LastBusinessDay > @PrevStartBusinessDay AND appliedStockCount.ApplyDate >= @StartBusinessDayOpen)  
					)  
		WHERE availableItems.EntityId = @EntityId
			and availableItems.ItemId = ISNULL(@ItemId, availableItems.ItemId)
		GROUP BY
			availableItems.EntityId,
			availableItems.ItemId

		CREATE TABLE #InventoryAdjustments_Opening
		(
			EntityId BIGINT NOT NULL, 
			ItemId BIGINT NOT NULL,
			StockAmount MONEY,
			StockQty FLOAT,
			BusinessDay DATETIME,
			TotalsApplyDate DATETIME,
			AdjustValue MONEY,
			AdjustQty FLOAT,
			PRIMARY KEY CLUSTERED (EntityId, ItemId)
		)

		SELECT @MinApplyDate = MIN(AdjustApplyDate) FROM #ApplyDates_Opening

		INSERT INTO #InventoryAdjustments_Opening
		SELECT
			applyDates.EntityId,
			applyDates.ItemId,
			stockCount.StockAmount,
			stockCount.StockQty,
			MIN(applyDates.BusinessDay) AS BusinessDay,
			MIN(applyDates.TotalsApplyDate) AS TotalsApplyDate,
			SUM(ISNULL(adjustments.PurchaseExclReturnsValue 
				- adjustments.PurchaseReturnsValue 
				+ adjustments.TransferInValue 
				- adjustments.TransferOutValue 
				+ adjustments.BOMMakeValue 
				- adjustments.BOMUseValue 
				- adjustments.SalesValue 
				- adjustments.WasteValue 
				- adjustments.SamplingValue 
				- adjustments.OtherAdjustValue 
				- adjustments.DispatchValue,0)) AS AdjustValue,
			SUM(ISNULL(adjustments.PurchaseExclReturnsCount 
				- adjustments.PurchaseReturnsCount 
				+ adjustments.TransferInCount 
				- adjustments.TransferOutCount 
				+ adjustments.BOMMakeCount 
				- adjustments.BOMUseCount 
				- adjustments.SalesCount 
				- adjustments.WasteCount 
				- adjustments.SamplingCount 
				- adjustments.OtherAdjustCount 
				- adjustments.DispatchCount,0)) As AdjustQty
		FROM #ApplyDates_Opening applyDates 
			LEFT JOIN inventory.vwPopulateDailyStockBalance adjustments
				ON adjustments.EntityId=applyDates.EntityId
				AND adjustments.ItemId=applyDates.ItemId
				AND adjustments.ApplyDate>=applyDates.AdjustApplyDate
				AND adjustments.ApplyDate<@StartTime 
			   -- add this in to ensure the result set does not continue to grow based on the size of the db  
			   -- this will improve the overall performance of this query and ensure it doesn't degrade over time  
				AND adjustments.ApplyDate>=@MinApplyDate
				AND adjustments.EntityId=@EntityId
			LEFT JOIN inventory.vwTransactionStockCountApplyDates stockCount
				ON stockCount.EntityId=applyDates.EntityId
				AND stockCount.ItemId=applyDates.ItemId
				AND stockCount.ApplyDate=applyDates.AdjustApplyDate
			   -- add this in to ensure the result set does not continue to grow based on the size of the db  
			   -- this will improve the overall performance of this query and ensure it doesn't degrade over time  
				AND stockCount.ApplyDate>=@MinBusinessDayOpen
				AND stockCount.ApplyDate<=@StartTime
				AND stockCount.EntityId=@EntityId
		GROUP BY
			applyDates.EntityId,
			applyDates.ItemId,
			stockCount.StockAmount,
			stockCount.StockQty
     
		-- OpeningStock
		CREATE TABLE #OpeningStock (
			[EntityId] [bigint] NOT NULL,
			[ItemId] [bigint] NOT NULL,
			[BusinessDay] [datetime] NULL,
			[StockValue] [money] NULL,
			[StockQty] [float] NULL,
			[PurchaseExclReturnsValue] [money] NOT NULL,
			[PurchaseExclReturnsQty] [float] NOT NULL,
			[PurchaseReturnsValue] [money] NOT NULL,
			[PurchaseReturnsQty] [float] NOT NULL,
			[TransferInValue] [money] NOT NULL,
			[TransferInQty] [float] NOT NULL,
			[TransferOutValue] [money] NOT NULL,
			[TransferOutQty] [float] NOT NULL,
			[BOMMakeValue] [float] NOT NULL,
			[BOMMakeQty] [float] NOT NULL,
			[BOMUseValue] [float] NOT NULL,
			[BOMUseQty] [float] NOT NULL,
			[SalesValue] [money] NOT NULL,
			[SalesQty] [float] NOT NULL,
			[WasteValue] [money] NOT NULL,
			[WasteQty] [float] NOT NULL,
			[SamplingValue] [money] NOT NULL,
			[SamplingQty] [float] NOT NULL,
			[OtherAdjustValue] [money] NOT NULL,
			[OtherAdjustQty] [float] NOT NULL,
			[DispatchValue] [money] NOT NULL,
			[DispatchQty] [float] NOT NULL,
			[FinishedWasteValue] [money] NOT NULL,
			[FinishedWasteQty] [float] NOT NULL
		)

		SELECT @MinApplyDate = MIN(TotalsApplyDate) FROM #InventoryAdjustments_Opening

		CREATE TABLE #tmpInventoryUsage (EntityID BIGINT, ItemID BIGINT, StockAmount MONEY, StockQty FLOAT, BusinessDay DATETIME, 
										PurchaseExclReturnsValue MONEY, PurchaseExclReturnsQty FLOAT, PurchaseReturnsValue MONEY, PurchaseReturnsQty FLOAT,
										TransferInValue MONEY, TransferInQty FLOAT, TransferOutValue MONEY, TransferOutQty FLOAT, 
										BOMMakeValue FLOAT, BOMMakeQty FLOAT, BOMUseValue FLOAT, BOMUseQty FLOAT, 
										SalesValue MONEY, SalesQty FLOAT, WasteValue MONEY, WasteQty FLOAT, 
										SamplingValue MONEY, SamplingQty FLOAT, OtherAdjustValue MONEY, OtherAdjustQty FLOAT, 
										DispatchValue MONEY, DispatchQty FLOAT,	AdjustValue MONEY, AdjustQty FLOAT, FinishedWasteValue MONEY, FinishedWasteQty FLOAT)

		INSERT INTO #tmpInventoryUsage (EntityID, ItemID, StockAmount, StockQty, BusinessDay, PurchaseExclReturnsValue, PurchaseExclReturnsQty, PurchaseReturnsValue, PurchaseReturnsQty,
										TransferInValue, TransferInQty, TransferOutValue, TransferOutQty, BOMMakeValue, BOMMakeQty, BOMUseValue, BOMUseQty, 
										SalesValue, SalesQty, WasteValue, WasteQty, SamplingValue, SamplingQty, OtherAdjustValue, OtherAdjustQty, DispatchValue, 
										DispatchQty, AdjustValue, AdjustQty, FinishedWasteValue, FinishedWasteQty)
		SELECT	inventoryAdjustments.EntityId,
				inventoryAdjustments.ItemId,
				inventoryAdjustments.StockAmount AS StockAmount,
				inventoryAdjustments.StockQty AS StockQty,
				MIN(inventoryAdjustments.BusinessDay) AS BusinessDay,
				SUM(totals.PurchaseExclReturnsValue) AS PurchaseExclReturnsValue,
				SUM(totals.PurchaseExclReturnsCount) AS PurchaseExclReturnsQty,
				SUM(totals.PurchaseReturnsValue) AS PurchaseReturnsValue,
				SUM(totals.PurchaseReturnsCount) AS PurchaseReturnsQty,
				SUM(totals.TransferInValue) AS TransferInValue,
				SUM(totals.TransferInCount) AS TransferInQty,
				SUM(totals.TransferOutValue) AS TransferOutValue,
				SUM(totals.TransferOutCount) AS TransferOutQty,
				SUM(totals.BOMMakeValue) AS BOMMakeValue,
				SUM(totals.BOMMakeCount) AS BOMMakeQty,
				SUM(totals.BOMUseValue) AS BOMUseValue,
				SUM(totals.BOMUseCount) AS BOMUseQty,
				SUM(totals.SalesValue) AS SalesValue,
				SUM(totals.SalesCount) AS SalesQty,
				SUM(totals.WasteValue) AS WasteValue,
				SUM(totals.WasteCount) AS WasteQty,
				SUM(totals.SamplingValue) AS SamplingValue,
				SUM(totals.SamplingCount) AS SamplingQty,
				SUM(totals.OtherAdjustValue) AS OtherAdjustValue,
				SUM(totals.OtherAdjustCount) AS OtherAdjustQty,
				SUM(totals.DispatchValue) AS DispatchValue,
				SUM(totals.DispatchCount) AS DispatchQty,
				MIN(inventoryAdjustments.AdjustValue) AS AdjustValue,
				MIN(inventoryAdjustments.AdjustQty) AS AdjustQty,
				SUM(totals.FinishedWasteValue) AS FinishedWasteValue,
				SUM(totals.FinishedWasteCount) AS FinishedWasteQty 
		FROM #InventoryAdjustments_Opening inventoryAdjustments
			-- get a total of all usage from start date to end date
			-- this is used to get your total inventory usage
		LEFT OUTER JOIN inventory.vwPopulateDailyStockBalance totals ON totals.EntityId = inventoryAdjustments.EntityId
																	AND totals.ItemId = inventoryAdjustments.ItemId
																	AND totals.ApplyDate >= inventoryAdjustments.TotalsApplyDate
																	AND totals.ApplyDate < @StartTime
			-- add this in to ensure the result set does not continue to grow based on the size of the db
			-- this will improve the overall performance of this query and ensure it doesn't degrade over time
																	AND totals.ApplyDate>=@MinApplyDate
																	AND totals.EntityId=@EntityId
		GROUP BY inventoryAdjustments.EntityId, inventoryAdjustments.ItemId, inventoryAdjustments.StockAmount, inventoryAdjustments.StockQty

	    INSERT #OpeningStock (EntityId,ItemId,BusinessDay,StockValue,StockQty,PurchaseExclReturnsValue,PurchaseExclReturnsQty,PurchaseReturnsValue,PurchaseReturnsQty,TransferInValue,TransferInQty,TransferOutValue,TransferOutQty,BOMMakeValue,BOMMakeQty,BOMUseValue,BOMUseQty,SalesValue,SalesQty,WasteValue,WasteQty,SamplingValue,SamplingQty,OtherAdjustValue,OtherAdjustQty,DispatchValue,DispatchQty,FinishedWasteValue,FinishedWasteQty)
		SELECT	dailyTotals.EntityId,
				dailyTotals.ItemId,
				inventoryUsage.BusinessDay,
  				COALESCE(inventoryUsage.StockAmount, dailyTotals.ClosingStockValue,0) + inventoryUsage.AdjustValue AS StockValue,  
  				COALESCE(inventoryUsage.StockQty, dailyTotals.ClosingStockQty,0) + inventoryUsage.AdjustQty AS StockQty,  
				ISNULL(inventoryUsage.PurchaseExclReturnsValue,0) AS PurchaseExclReturnsValue,
				ISNULL(inventoryUsage.PurchaseExclReturnsQty,0) AS PurchaseExclReturnsQty,
				ISNULL(inventoryUsage.PurchaseReturnsValue,0) AS PurchaseReturnsValue,
				ISNULL(inventoryUsage.PurchaseReturnsQty,0) AS PurchaseReturnsQty,
				ISNULL(inventoryUsage.TransferInValue,0) AS TransferInValue,
				ISNULL(inventoryUsage.TransferInQty,0) AS TransferInQty,
				ISNULL(inventoryUsage.TransferOutValue,0) AS TransferOutValue,
				ISNULL(inventoryUsage.TransferOutQty,0) AS TransferOutQty,
				ISNULL(inventoryUsage.BOMMakeValue,0) AS BOMMakeValue,
				ISNULL(inventoryUsage.BOMMakeQty,0) AS BOMMakeQty,
				ISNULL(inventoryUsage.BOMUseValue,0) AS BOMUseValue,
				ISNULL(inventoryUsage.BOMUseQty,0) AS BOMUseQty,
				ISNULL(inventoryUsage.SalesValue,0) AS SalesValue,
				ISNULL(inventoryUsage.SalesQty,0) AS SalesQty,
				ISNULL(inventoryUsage.WasteValue,0) AS WasteValue,
				ISNULL(inventoryUsage.WasteQty,0) AS WasteQty,
				ISNULL(inventoryUsage.SamplingValue,0) AS SamplingValue,
				ISNULL(inventoryUsage.SamplingQty,0) AS SamplingQty,
				ISNULL(inventoryUsage.OtherAdjustValue,0) AS OtherAdjustValue,
				ISNULL(inventoryUsage.OtherAdjustQty,0) AS OtherAdjustQty,
				ISNULL(inventoryUsage.DispatchValue,0) AS DispatchValue,
				ISNULL(inventoryUsage.DispatchQty,0) AS DispatchQty,
				ISNULL(inventoryUsage.FinishedWasteValue,0) AS FinishedWasteValue,
				ISNULL(inventoryUsage.FinishedWasteQty,0) AS FinishedWasteQty 
		FROM #tmpDailyStockBalance dailyTotals WITH (NOLOCK)
		INNER JOIN #tmpInventoryUsage inventoryUsage ON dailyTotals.EntityID = inventoryUsage.EntityID
													AND dailyTotals.ItemId = inventoryUsage.ItemID
													AND dailyTotals.BusinessDay = inventoryUsage.BusinessDay

		TRUNCATE TABLE #tmpinventoryUsage

		-- ClosingStock
		CREATE TABLE #ClosingStock (
			[EntityId] [bigint] NOT NULL,
			[ItemId] [bigint] NOT NULL,
			[BusinessDay] [datetime] NULL,
			[StockValue] [money] NULL,
			[StockQty] [float] NULL,
			[PurchaseExclReturnsValue] [money] NOT NULL,
			[PurchaseExclReturnsQty] [float] NOT NULL,
			[PurchaseReturnsValue] [money] NOT NULL,
			[PurchaseReturnsQty] [float] NOT NULL,
			[TransferInValue] [money] NOT NULL,
			[TransferInQty] [float] NOT NULL,
			[TransferOutValue] [money] NOT NULL,
			[TransferOutQty] [float] NOT NULL,
			[BOMMakeValue] [float] NOT NULL,
			[BOMMakeQty] [float] NOT NULL,
			[BOMUseValue] [float] NOT NULL,
			[BOMUseQty] [float] NOT NULL,
			[SalesValue] [money] NOT NULL,
			[SalesQty] [float] NOT NULL,
			[WasteValue] [money] NOT NULL,
			[WasteQty] [float] NOT NULL,
			[SamplingValue] [money] NOT NULL,
			[SamplingQty] [float] NOT NULL,
			[OtherAdjustValue] [money] NOT NULL,
			[OtherAdjustQty] [float] NOT NULL,
			[DispatchValue] [money] NOT NULL,
			[DispatchQty] [float] NOT NULL,
			[FinishedWasteValue] [money] NOT NULL,
			[FinishedWasteQty] [float] NOT NULL
		)

		SELECT @MinApplyDate = MIN(TotalsApplyDate) FROM #InventoryAdjustments_Closing

		INSERT INTO #tmpInventoryUsage (EntityID, ItemID, StockAmount, StockQty, BusinessDay, PurchaseExclReturnsValue, PurchaseExclReturnsQty, PurchaseReturnsValue, PurchaseReturnsQty,
						TransferInValue, TransferInQty, TransferOutValue, TransferOutQty, BOMMakeValue, BOMMakeQty, BOMUseValue, BOMUseQty, 
						SalesValue, SalesQty, WasteValue, WasteQty, SamplingValue, SamplingQty, OtherAdjustValue, OtherAdjustQty, DispatchValue, 
						DispatchQty, AdjustValue, AdjustQty, FinishedWasteValue, FinishedWasteQty)
		SELECT		inventoryAdjustments.EntityId,
					inventoryAdjustments.ItemId,
					inventoryAdjustments.StockAmount as StockAmount,
					inventoryAdjustments.StockQty as StockQty,
					MIN(inventoryAdjustments.BusinessDay) AS BusinessDay,
					SUM(totals.PurchaseExclReturnsValue) AS PurchaseExclReturnsValue,
					SUM(totals.PurchaseExclReturnsCount) AS PurchaseExclReturnsQty,
					SUM(totals.PurchaseReturnsValue) AS PurchaseReturnsValue,
					SUM(totals.PurchaseReturnsCount) AS PurchaseReturnsQty,
					SUM(totals.TransferInValue) AS TransferInValue,
					SUM(totals.TransferInCount) AS TransferInQty,
					SUM(totals.TransferOutValue) AS TransferOutValue,
					SUM(totals.TransferOutCount) AS TransferOutQty,
					SUM(totals.BOMMakeValue) AS BOMMakeValue,
					SUM(totals.BOMMakeCount) AS BOMMakeQty,
					SUM(totals.BOMUseValue) AS BOMUseValue,
					SUM(totals.BOMUseCount) AS BOMUseQty,
					SUM(totals.SalesValue) AS SalesValue,
					SUM(totals.SalesCount) AS SalesQty,
					SUM(totals.WasteValue) AS WasteValue,
					SUM(totals.WasteCount) AS WasteQty,
					SUM(totals.SamplingValue) AS SamplingValue,
					SUM(totals.SamplingCount) AS SamplingQty,
					SUM(totals.OtherAdjustValue) AS OtherAdjustValue,
					SUM(totals.OtherAdjustCount) AS OtherAdjustQty,
					SUM(totals.DispatchValue) AS DispatchValue,
					SUM(totals.DispatchCount) AS DispatchQty,
					MIN(inventoryAdjustments.AdjustValue) AS AdjustValue,
					MIN(inventoryAdjustments.AdjustQty) AS AdjustQty,
					SUM(totals.FinishedWasteValue) AS FinishedWasteValue,
					SUM(totals.FinishedWasteCount) AS FinishedWasteQty
		FROM #InventoryAdjustments_Closing inventoryAdjustments
					-- get a total of all usage from start date to end date
					-- this is used to get your total inventory usage
		LEFT OUTER JOIN inventory.vwPopulateDailyStockBalance totals ON totals.EntityId = inventoryAdjustments.EntityId
																	AND totals.ItemId = inventoryAdjustments.ItemId
																	AND totals.ApplyDate >= inventoryAdjustments.TotalsApplyDate
																	AND totals.ApplyDate < @EndTime
					-- add this in to ensure the result set does not continue to grow based on the size of the db
					-- this will improve the overall performance of this query and ensure it doesn't degrade over time
																	AND totals.ApplyDate >= @MinApplyDate
																	AND totals.EntityId = @EntityId
		GROUP BY inventoryAdjustments.EntityId, inventoryAdjustments.ItemId, inventoryAdjustments.StockAmount, inventoryAdjustments.StockQty



	    INSERT #ClosingStock (EntityId,ItemId,BusinessDay,StockValue,StockQty,PurchaseExclReturnsValue,PurchaseExclReturnsQty,PurchaseReturnsValue,PurchaseReturnsQty,TransferInValue,TransferInQty,TransferOutValue,TransferOutQty,BOMMakeValue,BOMMakeQty,BOMUseValue,BOMUseQty,SalesValue,SalesQty,WasteValue,WasteQty,SamplingValue,SamplingQty,OtherAdjustValue,OtherAdjustQty,DispatchValue,DispatchQty,FinishedWasteValue,FinishedWasteQty)
		SELECT	dailyTotals.EntityId,
				dailyTotals.ItemId,
				inventoryUsage.BusinessDay,
				COALESCE(inventoryUsage.StockAmount,dailyTotals.ClosingStockValue,0) + inventoryUsage.AdjustValue AS StockValue,
				COALESCE(inventoryUsage.StockQty,dailyTotals.ClosingStockQty,0) + inventoryUsage.AdjustQty AS StockQty,
				ISNULL(inventoryUsage.PurchaseExclReturnsValue,0) AS PurchaseExclReturnsValue,
				ISNULL(inventoryUsage.PurchaseExclReturnsQty,0) AS PurchaseExclReturnsQty,
				ISNULL(inventoryUsage.PurchaseReturnsValue,0) AS PurchaseReturnsValue,
				ISNULL(inventoryUsage.PurchaseReturnsQty,0) AS PurchaseReturnsQty,
				ISNULL(inventoryUsage.TransferInValue,0) AS TransferInValue,
				ISNULL(inventoryUsage.TransferInQty,0) AS TransferInQty,
				ISNULL(inventoryUsage.TransferOutValue,0) AS TransferOutValue,
				ISNULL(inventoryUsage.TransferOutQty,0) AS TransferOutQty,
				ISNULL(inventoryUsage.BOMMakeValue,0) AS BOMMakeValue,
				ISNULL(inventoryUsage.BOMMakeQty,0) AS BOMMakeQty,
				ISNULL(inventoryUsage.BOMUseValue,0) AS BOMUseValue,
				ISNULL(inventoryUsage.BOMUseQty,0) AS BOMUseQty,
				ISNULL(inventoryUsage.SalesValue,0) AS SalesValue,
				ISNULL(inventoryUsage.SalesQty,0) AS SalesQty,
				ISNULL(inventoryUsage.WasteValue,0) AS WasteValue,
				ISNULL(inventoryUsage.WasteQty,0) AS WasteQty,
				ISNULL(inventoryUsage.SamplingValue,0) AS SamplingValue,
				ISNULL(inventoryUsage.SamplingQty,0) AS SamplingQty,
				ISNULL(inventoryUsage.OtherAdjustValue,0) AS OtherAdjustValue,
				ISNULL(inventoryUsage.OtherAdjustQty,0) AS OtherAdjustQty,
				ISNULL(inventoryUsage.DispatchValue,0) AS DispatchValue,
				ISNULL(inventoryUsage.DispatchQty,0) AS DispatchQty,
				ISNULL(inventoryUsage.FinishedWasteValue,0) AS FinishedWasteValue,
				ISNULL(inventoryUsage.FinishedWasteQty,0) AS FinishedWasteQty 
		FROM #tmpDailyStockBalance dailyTotals WITH (NOLOCK)
		INNER JOIN #tmpInventoryUsage inventoryUsage ON dailyTotals.EntityID=inventoryUsage.EntityID
													AND dailyTotals.ItemId=inventoryUsage.ItemID
													AND dailyTotals.BusinessDay=inventoryUsage.BusinessDay

	 ;WITH CTE_ListInventoryDetails(ItemId, OpeningValue, OpeningQty, ClosingValue, ClosingQty, PurchaseValue, PurchaseQty, PurchaseExclReturnsValue, PurchaseExclReturnsQty
									,PurchaseReturnsValue, PurchaseReturnsQty, TransferInValue, TransferInQty, TransferOutValue, TransferOutQty
									,BOMMakeValue, BOMMakeQty, BOMUseValue, BOMUseQty, SalesValue, SalesQty, WasteValue, WasteQty, SamplingValue, SamplingQty, OtherAdjustValue
									,OtherAdjustQty, DispatchValue, DispatchQty, FinishedWasteValue, FinishedWasteQty, RawWasteValue, RawWasteQty)
      AS
	  (
			SELECT	I.ItemId,
					MIN(ISNULL(O.StockValue,0)) AS OpeningValue,
					MIN(ISNULL(O.StockQty,0)) AS OpeningQty,
					MIN(C.StockValue) AS ClosingValue,
					MIN(C.StockQty) AS ClosingQty,
					SUM(ISNULL(sb.PurchaseExclReturnsValue,0) - ISNULL(sb.PurchaseReturnsValue,0)) - MIN(ISNULL(O.PurchaseExclReturnsValue,0) - ISNULL(O.PurchaseReturnsValue,0)) + MIN(C.PurchaseExclReturnsValue - ISNULL(C.PurchaseReturnsValue,0)) AS PurchaseValue,
					SUM(ISNULL(sb.PurchaseExclReturnsQty,0) - ISNULL(sb.PurchaseReturnsQty,0)) - MIN(ISNULL(O.PurchaseExclReturnsQty,0) - ISNULL(O.PurchaseReturnsQty,0)) + MIN(C.PurchaseExclReturnsQty - ISNULL(C.PurchaseReturnsQty,0)) AS PurchaseQty,
					SUM(ISNULL(sb.PurchaseExclReturnsValue,0)) - MIN(ISNULL(O.PurchaseExclReturnsValue,0)) + MIN(C.PurchaseExclReturnsValue) AS PurchaseExclReturnsValue,
					SUM(ISNULL(sb.PurchaseExclReturnsQty,0)) - MIN(ISNULL(O.PurchaseExclReturnsQty,0)) + MIN(C.PurchaseExclReturnsQty) AS PurchaseExclReturnsQty,
					SUM(ISNULL(sb.PurchaseReturnsValue,0)) - MIN(ISNULL(O.PurchaseReturnsValue,0)) + MIN(C.PurchaseReturnsValue) AS PurchaseReturnsValue,
					SUM(ISNULL(sb.PurchaseReturnsQty,0)) - MIN(ISNULL(O.PurchaseReturnsQty,0)) + MIN(C.PurchaseReturnsQty) AS PurchaseReturnsQty,
					SUM(ISNULL(sb.TransferInValue,0)) - MIN(ISNULL(O.TransferInValue,0)) + MIN(C.TransferInValue) AS TransferInValue,
					SUM(ISNULL(sb.TransferInQty,0)) - MIN(ISNULL(O.TransferInQty,0)) + MIN(C.TransferInQty) AS TransferInQty,
					SUM(ISNULL(sb.TransferOutValue,0)) - MIN(ISNULL(O.TransferOutValue,0)) + MIN(C.TransferOutValue) AS TransferOutValue,
					SUM(ISNULL(sb.TransferOutQty,0)) - MIN(ISNULL(O.TransferOutQty,0)) + MIN(C.TransferOutQty) AS TransferOutQty,
					SUM(ISNULL(sb.BOMMakeValue,0)) - MIN(ISNULL(O.BOMMakeValue,0)) + MIN(C.BOMMakeValue) AS BOMMakeValue,
					SUM(ISNULL(sb.BOMMakeQty,0)) - MIN(ISNULL(O.BOMMakeQty,0)) + MIN(C.BOMMakeQty) AS BOMMakeQty,
					SUM(ISNULL(sb.BOMUseValue,0)) - MIN(ISNULL(O.BOMUseValue,0)) + MIN(C.BOMUseValue) AS BOMUseValue,
					SUM(ISNULL(sb.BOMUseQty,0)) - MIN(ISNULL(O.BOMUseQty,0)) + MIN(C.BOMUseQty) AS BOMUseQty,
					SUM(ISNULL(sb.SalesValue,0)) - MIN(ISNULL(O.SalesValue,0)) + MIN(C.SalesValue) AS SalesValue,
					SUM(ISNULL(sb.SalesQty,0)) - MIN(ISNULL(O.SalesQty,0)) + MIN(C.SalesQty) AS SalesQty,
					SUM(ISNULL(sb.WasteValue,0)) - MIN(ISNULL(O.WasteValue,0)) + MIN(C.WasteValue) AS WasteValue,
					SUM(ISNULL(sb.WasteQty,0)) - MIN(ISNULL(O.WasteQty,0)) + MIN(C.WasteQty) AS WasteQty,
					SUM(ISNULL(sb.SamplingValue,0)) - MIN(ISNULL(O.SamplingValue,0)) + MIN(C.SamplingValue) AS SamplingValue,
					SUM(ISNULL(sb.SamplingQty,0)) - MIN(ISNULL(O.SamplingQty,0)) + MIN(C.SamplingQty) AS SamplingQty,
					SUM(ISNULL(sb.OtherAdjustValue,0)) - MIN(ISNULL(O.OtherAdjustValue,0)) + MIN(C.OtherAdjustValue) AS OtherAdjustValue,
					SUM(ISNULL(sb.OtherAdjustQty,0)) - MIN(ISNULL(O.OtherAdjustQty,0)) + MIN(C.OtherAdjustQty) AS OtherAdjustQty,
					SUM(ISNULL(sb.DispatchValue,0)) - MIN(ISNULL(O.DispatchValue,0)) + MIN(C.DispatchValue) AS DispatchValue,
					SUM(ISNULL(sb.DispatchQty,0)) - MIN(ISNULL(O.DispatchQty,0)) + MIN(C.DispatchQty) AS DispatchQty,
					SUM(ISNULL(sb.FinishedWasteValue,0)) - MIN(ISNULL(O.FinishedWasteValue,0)) + MIN(C.FinishedWasteValue) AS FinishedWasteValue,
					SUM(ISNULL(sb.FinishedWasteQty,0)) - MIN(ISNULL(O.FinishedWasteQty,0)) + MIN(C.FinishedWasteQty) AS FinishedWasteQty,
					(SUM(ISNULL(sb.WasteValue,0)) - MIN(ISNULL(O.WasteValue,0)) + MIN(C.WasteValue)) -
						(SUM(ISNULL(sb.FinishedWasteValue,0)) - MIN(ISNULL(O.FinishedWasteValue,0)) + MIN(C.FinishedWasteValue)) AS RawWasteValue,
					(SUM(ISNULL(sb.WasteQty,0)) - MIN(ISNULL(O.WasteQty,0)) + MIN(C.WasteQty)) -
						(SUM(ISNULL(sb.FinishedWasteQty,0)) - MIN(ISNULL(O.FinishedWasteQty,0)) + MIN(C.FinishedWasteQty)) AS RawWasteQty 
			FROM tbItem I WITH (NOLOCK)
			INNER JOIN #ClosingStock C ON C.ItemId = I.ItemID
			LEFT OUTER JOIN #OpeningStock O ON O.ItemId = I.ItemID
			LEFT OUTER JOIN #tmpDailyStockBalance SB WITH (NOLOCK) ON sb.EntityId = @EntityId
																AND I.ItemID = sb.ItemId
																AND sb.BusinessDay > ISNULL(O.BusinessDay, @StartBusinessDay)
																AND sb.BusinessDay <= C.BusinessDay
				-- add this in to ensure the result set does not continue to grow based on the size of the db
				-- this will improve the overall performance of this query and ensure it doesn't degrade over time
				AND sb.BusinessDay BETWEEN @MinBusinessDay AND @MaxBusinessDay
			WHERE I.ClassCode = 'IV' 
				AND I.ItemId = ISNULL(@ItemId, I.ItemID)
			GROUP BY I.ItemId
		)
		SELECT *
			,Expected_Qty = (OpeningQty + PurchaseQty + TransferINQty + BOMMakeQty - (SalesQty + WasteQty + OtherAdjustQty + TransferOUTQty + SamplingQty + BOMUseQty + DispatchQty))
			,Expected_Amt = (OpeningValue + PurchaseValue + TransferINValue + BOMMakeValue - (SalesValue + WasteValue + OtherAdjustValue + TransferOUTValue + SamplingValue + BOMUseValue + DispatchValue))
			,Variance_Qty = ClosingQty   - (OpeningQty + PurchaseQty + TransferINQty + BOMMakeQty - (SalesQty + WasteQty + OtherAdjustQty + TransferOUTQty + SamplingQty + BOMUseQty + DispatchQty))
			,Variance_Amt = ClosingValue - (OpeningValue + PurchaseValue + TransferINValue + BOMMakeValue - (SalesValue + WasteValue + OtherAdjustValue + TransferOUTValue + SamplingValue + BOMUseValue + DispatchValue))
		FROM CTE_ListInventoryDetails
	END TRY
	BEGIN CATCH
		-- throw error back to caller:
		DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
				@ErrorLine INT = ERROR_LINE(),
				@ErrorProcedure SYSNAME = ERROR_PROCEDURE()

		SET @ErrorMessage = ISNULL(@ErrorMessage, 'n/a') + ' in procedure ' + ISNULL(@ErrorProcedure, 'n/a') + ', line ' + ISNULL(CAST(@ErrorLine AS NVARCHAR(100)), 'n/a') + '.'

		RAISERROR(@ErrorMessage, 16, 1)
	END CATCH
END
GO
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

Attachments

Issue Links

is implemented by

CFAMX-11976 Augment/Create Load Tests

Open

CFAMX-11977 Documentation

Open

CFAMX-12026 Investigate fn_ListInventoryDetails rewrite

Open

Activity

People

Assignee:

Unassigned

Reporter:

Corey Amend (Inactive)

Watchers:

0 Start watching this issue

Dates

Created:

Sep/15/20 7:32 AM

Updated:

Jun/17/22 2:23 PM

PagerDuty

Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.