EnVisageOnline/Main-RMO/Database/Scripts/20141001/01_update bou_up_sp.sql

USE [envisage]
GO
/****** Object:  StoredProcedure [dbo].[sp_SetScenarioBottomUpCosts]    Script Date: 10/1/2014 4:34:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[sp_SetScenarioBottomUpCosts] (@aScenarioOID uniqueidentifier) AS
	DECLARE @lBottomUpCosts 		decimal(15,4)
	DECLARE @lBottomUpCosts_LM		decimal(15,4)
	DECLARE @lActuals_BottomUpCosts	decimal(15,4)
	DECLARE @lActuals_BottomUpCosts_LM	decimal(15,4)

	DECLARE @lScenarioStartDate		datetime
	DECLARE @lScenarioEndDate		datetime
	DECLARE @lActualsStartDate		datetime
	DECLARE @lActualsEndDate		datetime
	
	DECLARE @lScenarioType			int
	DECLARE @lActualsId				uniqueidentifier
	DECLARE @lSCUnderShowID			uniqueidentifier
	DECLARE @lGrowthScenario		int

	DECLARE @lIsRevenueGenerating		bit

	BEGIN

		SELECT  @lScenarioType = Type, @lScenarioStartDate = StartDate, @lScenarioEndDate = EndDate, @lGrowthScenario = GrowthScenario
				 FROM Scenario
				WHERE (Id = @aScenarioOID)
	
		BEGIN TRANSACTION updatebottomupdirectcosts

		SELECT  @lIsRevenueGenerating = IsNull(t.IsRevenueGenerating, 0)
				 FROM Scenario s inner join Project p on s.ParentId = p.Id inner join Type t on p.TypeId = t.Id 
				WHERE (s.Id = @aScenarioOID)


		IF ((@lScenarioType = 2) OR (@lScenarioType = 3)) 
		 BEGIN
			SELECT @lBottomUpCosts = SUM (Cost)
					 FROM Scenario_Detail
					WHERE (ParentID = @aScenarioOID);

			SELECT @lBottomUpCosts_LM = SUM (Cost)
					 FROM Scenario_Detail
					WHERE (ParentID = @aScenarioOID)
					  AND (ExpenditureCategoryId IN 
							(SELECT Id FROM VW_Expenditure2Category
									WHERE [Type] IN (1, 2)))

			IF (@lGrowthScenario = 0)
			 BEGIN

				/* calculate actuals buttom up cost which is actuals plus forecast */

				/* retrieve (only) actuals for the same show, there should only be one */
				SELECT @lActualsId = Id, @lActualsStartDate = StartDate, @lActualsEndDate = EndDate 
						 FROM Scenario
						WHERE (Type = 9) 
						  AND (ParentId = (SELECT ParentId FROM Scenario 
											WHERE Id = @aScenarioOID))

				/* sum up buttom up costs from actuals */
				SELECT @lActuals_BottomUpCosts = 0
				SELECT @lActuals_BottomUpCosts_LM = 0


				SELECT @lActuals_BottomUpCosts = SUM (Cost) 
						 FROM Scenario_Detail
						WHERE ((ParentId = @lActualsId)
						  AND  (WeekEndingDate >= @lActualsStartDate)
						  AND  (WeekEndingDate <= @lActualsEndDate))
						   OR ((ParentId = @aScenarioOID)
						  AND  (WeekEndingDate > @lActualsEndDate))

				SELECT @lActuals_BottomUpCosts_LM = SUM (Cost)
						 FROM Scenario_Detail
						WHERE ((ParentID = @lActualsId)
						  AND  (WeekEndingDate >= @lActualsStartDate)
						  AND  (WeekEndingDate <= @lActualsEndDate)
						  AND  (ExpenditureCategoryId IN 
								(SELECT Id FROM VW_Expenditure2Category
										WHERE Type IN (1, 2))))
						   OR ((ParentId = @aScenarioOID)
						  AND  (WeekEndingDate > @lActualsEndDate)
						  AND  (ExpenditureCategoryId IN 
								(SELECT Id FROM VW_Expenditure2Category
										WHERE Type IN (1, 2))))
				IF (@lIsRevenueGenerating = 1)
				UPDATE Scenario
					SET BUDirectCosts = @lBottomUpCosts,  BURevenueShot = (@lBottomUpCosts / Shots), 
						Actuals_BUDirectCosts = @lActuals_BottomUpCosts,
						CalculatedGrossMargin = (ProjectedRevenue - @lBottomUpCosts)  / ProjectedRevenue,
						BUDirectCosts_LM = @lBottomUpCosts_LM,
						Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM,
						BURevenueShot_LM = (@lBottomUpCosts_LM  / Shots), 
						CalculatedGrossMargin_LM = (ProjectedRevenue - @lBottomUpCosts_LM)  / ProjectedRevenue
					WHERE (Id = @aScenarioOID)
				ELSE 
					UPDATE Scenario SET BUDirectCosts = @lBottomUpCosts,  BURevenueShot = (@lBottomUpCosts / Shots), 
						Actuals_BUDirectCosts = @lActuals_BottomUpCosts,
						CalculatedGrossMargin = 0,
						BUDirectCosts_LM = @lBottomUpCosts_LM,
						Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM,
						BURevenueShot_LM = (@lBottomUpCosts_LM  / Shots), 
						CalculatedGrossMargin_LM = 0
					WHERE (Id = @aScenarioOID)
			 END
			ELSE
				IF (@lIsRevenueGenerating = 1)
				UPDATE Scenario
					SET BUDirectCosts = @lBottomUpCosts,  BURevenueShot = (@lBottomUpCosts / Shots), 
						CalculatedGrossMargin = (ProjectedRevenue - @lBottomUpCosts) / ProjectedRevenue,
						BUDirectCosts_LM = @lBottomUpCosts_LM,
						BURevenueShot_LM = (@lBottomUpCosts_LM / (Shots)), 
						CalculatedGrossMargin_LM = (ProjectedRevenue - @lBottomUpCosts_LM) / ProjectedRevenue
					WHERE (Id = @aScenarioOID)
				ELSE
					UPDATE Scenario
					SET BUDirectCosts = @lBottomUpCosts,  BURevenueShot = (@lBottomUpCosts / Shots), 
						CalculatedGrossMargin = 0,
						BUDirectCosts_LM = @lBottomUpCosts_LM,
						BURevenueShot_LM = (@lBottomUpCosts_LM / Shots), 
						CalculatedGrossMargin_LM = 0
					WHERE (Id = @aScenarioOID)
					
		 END
		ELSE IF (@lScenarioType = 9) 
		 BEGIN
			/* recalculate actuals buttom up costs */
			SELECT @lActuals_BottomUpCosts = 0 
			SELECT lActuals_BottomUpCosts_LM = 0

			SELECT  @lActuals_BottomUpCosts = SUM (Cost)
					 FROM Scenario_Detail
					WHERE (ParentId = @aScenarioOID)
					  AND (WeekEndingDate <= @lScenarioEndDate)

			SELECT @lActuals_BottomUpCosts_LM =  SUM (Cost) 
					 FROM Scenario_Detail
					WHERE (ParentId = @aScenarioOID)
					  AND (WeekEndingDate <= @lScenarioEndDate)
					  AND (ExpenditureCategoryId IN 
							(SELECT Id FROM VW_Expenditure2Category						
									WHERE Type IN (1, 2)))

			UPDATE Scenario
				SET BUDirectCosts = @lActuals_BottomUpCosts,  
					Actuals_BUDirectCosts = @lActuals_BottomUpCosts,
					BUDirectCosts_LM = @lActuals_BottomUpCosts_LM,
					Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM
				WHERE (Id = @aScenarioOID)



			/* recalculate buttom up costs of all portfolio, efc, cg scenarios under the same show as the actuals scenario. */
			DECLARE scenarios_undershow CURSOR FOR
				SELECT Id FROM Scenario
						WHERE (ParentId = (SELECT ParentId FROM Scenario
											WHERE Id = @aScenarioOID))
						  AND (Type IN (2, 3))
						  AND (GrowthScenario = 0)

			OPEN scenarios_undershow

			FETCH NEXT FROM scenarios_undershow INTO @lSCUnderShowID

			WHILE (@@fetch_status <> -1)
			 BEGIN

				SELECT @lBottomUpCosts = 0
				SELECT @lBottomUpCosts_LM = 0

				SELECT @lBottomUpCosts = SUM (Cost)
						 FROM Scenario_Detail
						WHERE (ParentId = @lSCUnderShowID)
						  AND (WeekEndingDate > @lScenarioEndDate)

				SELECT @lBottomUpCosts_LM  = SUM (Cost)
						 FROM Scenario_Detail
						WHERE (ParentId = @lSCUnderShowID)
						  AND (WeekEndingDate > @lScenarioEndDate)
						  AND (ExpenditureCategoryId IN 
								(SELECT Id FROM VW_Expenditure2Category
										WHERE Type IN (1, 2)))

				UPDATE Scenario 
					SET Actuals_BUDirectCosts = @lActuals_BottomUpCosts + @lBottomUpCosts,
					    Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM + @lBottomUpCosts_lm
				WHERE (Id = @lSCUnderShowID)

				FETCH NEXT FROM scenarios_undershow INTO @lSCUnderShowID
			 END

			DEALLOCATE scenarios_undershow

		 END

		COMMIT TRANSACTION 

 	END