CREATE PROCEDURE [dbo].[csp_Build_PivotTableCache]
AS

BEGIN TRANSACTION
	SELECT TOP 0 * FROM tbPivotTableUpdates_Cache WITH ( TABLOCKX )

	TRUNCATE TABLE tbPivotTableUpdates_Cache

	INSERT INTO tbPivotTableUpdates_Cache
	SELECT
		FP.FiscalQuarter,
		SH.Svp,
		SH.SvpName,
		SH.Vp,
		SH.VpName,
		SH.SrDirector,
		SH.SrDirectorName,
		SH.Director,
		SH.DirectorName,
		Team.TeamNumber,
		Team.TeamDescription,
		Customer.CustomerNumber,
		Customer.CustomerDescription,
		ISNULL(Product.BrandDescription, 'NA') AS BrandDescription,
		AllProducts.ProductNumber,
		ISNULL(Product.ProductDescription, 'NA') AS ProductDescription,
		ISNULL(Reference.Volume, 0) AS PriorYearVolume,
		CONVERT(Decimal(30, 8), CASE
			WHEN ISNULL(Allocation.Volume, 0) = 0 THEN 0.0001
			ELSE ISNULL(Allocation.Volume, 0)
			END) AS AllocationVolume,
		ISNULL(SalesPlan.Volume, 0) AS PlanVolume,
		ISNULL(Reference.WorkingTrade, 0) AS PriorYearWorkingTradeRate,
		ISNULL(Allocation.WorkingTrade, 0) AS AllocationWorkingTradeRate,
		ISNULL(SalesPlan.WorkingTrade, 0) AS PlanWorkingTradeRate,
		ISNULL(SalesPlan.Volume, 0) * ISNULL(Allocation.Rate, 0) AS ProjectedFunding,
		ISNULL(Reference.GrossSales, 0) AS PriorYearGrossSales,
		ISNULL(SalesPlan.GrossSales, 0) AS PlanGrossSales,
		ISNULL(Reference.GrossSales - Reference.WorkingTrade, 0) AS PriorYearNetSales,
		ISNULL(SalesPlan.GrossSales - SalesPlan.WorkingTrade, 0) AS PlanNetSales
	FROM
	(
		SELECT DISTINCT
			CustomerNumber
		FROM st_RateAllocation_FY09H2.dbo.tbCustomers
	) UserCustomers
	CROSS JOIN
	(
		SELECT LEFT(EPD.PGNo, 7) AS ProductNumber
		FROM tbEventPlan_Detail_Backup EPD
		LEFT JOIN  tbBrand_Exclude_ForReport BE ON LEFT(EPD.PGNO,2) = BE.BrandId
		WHERE BE.BrandId IS NULL

		UNION

		SELECT LEFT(EPD.PGNo, 7) AS ProductNumber
		FROM vwEventPlan_Reference EPD
		LEFT JOIN  tbBrand_Exclude_ForReport BE ON LEFT(EPD.PGNO,2) = BE.BrandId
		WHERE BE.BrandId IS NULL

		UNION

		SELECT FullProductId AS ProductNumber
		FROM st_RateAllocation_FY09H2.dbo.vwSmoothingExtractByPa
		LEFT JOIN  tbBrand_Exclude_ForReport BE ON LEFT(FullProductId,2) = BE.BrandId
		WHERE BE.BrandId IS NULL
	) AllProducts
	CROSS JOIN
	(
		SELECT
			[Quarter] AS FiscalQuarter,
			MIN(StartDate) AS StartDate,
			MAX(EndDate) AS EndDate
		FROM tbFiscalPeriod FP
		WHERE [Year] = '2009' AND [Quarter] IN ('3', '4')
		GROUP BY [Quarter]
	) FP
	LEFT JOIN
	(
		SELECT
			FiscalQuarter,
			'00' + EP.PANo AS CustomerNumber,
			LEFT(EP.PGNo, 7) AS ProductNumber,
			SUM(EP.TotalVolumeCSU) AS Volume,
			SUM(EP.CAGGrossSalesDollars) AS GrossSales,
			SUM(EP.WorkingTrade) AS WorkingTrade
		FROM vwEventPlan EP
		GROUP BY
			FiscalQuarter,
			'00' + EP.PANo,
			LEFT(EP.PGNo, 7)
	) SalesPlan
		ON FP.FiscalQuarter = SalesPlan.FiscalQuarter
		AND UserCustomers.CustomerNumber = SalesPlan.CustomerNumber
		AND AllProducts.ProductNumber = SalesPlan.ProductNumber
	LEFT JOIN
	(
		SELECT
			3 AS FiscalQuarter,
			R.CustomerNumber,
			R.ProductNumber,
			FLOOR(R.ProfitLossVolume / 2) AS Volume,
			CONVERT(Decimal(30, 8), CASE
				WHEN R.ProfitLossVolume = 0 THEN CONVERT(Decimal(30, 8), R.TotalRate) * CONVERT(Decimal(30, 8), 0.0001)
				ELSE R.TotalRate * R.ProfitLossVolume / 2
				END) AS WorkingTrade,
			R.TotalRate AS Rate
		FROM st_RateAllocation_FY09H2.dbo.tbRates R
		UNION ALL
		SELECT
			4 AS FiscalQuarter,
			R.CustomerNumber,
			R.ProductNumber,
			FLOOR(R.ProfitLossVolume / 2)
				+ CASE
					WHEN R.ProfitLossVolume % 2 = 1 THEN 1
					ELSE 0
					END AS Volume,
			CONVERT(Decimal(30, 8), CASE
				WHEN R.ProfitLossVolume = 0 THEN CONVERT(Decimal(30, 8), R.TotalRate) * CONVERT(Decimal(30, 8), 0.0001)
				ELSE R.TotalRate * R.ProfitLossVolume / 2
				END) AS WorkingTrade,
			R.TotalRate AS Rate
		FROM st_RateAllocation_FY09H2.dbo.tbRates R
	) Allocation
		ON FP.FiscalQuarter = Allocation.FiscalQuarter
		AND UserCustomers.CustomerNumber = Allocation.CustomerNumber
		AND AllProducts.ProductNumber = Allocation.ProductNumber
	LEFT JOIN
	(
		SELECT
			FiscalQuarter,
			'00' + Ref.PANo AS CustomerNumber,
			LEFT(Ref.PGNo, 7) AS ProductNumber,
			SUM(Ref.TotalVolume_CSU) AS Volume,
			SUM(Ref.CAGGrossSalesDollars) AS GrossSales,
			SUM(Ref.WorkingTrade) AS WorkingTrade
		FROM vwEventPlan_Reference Ref
		GROUP BY
			FiscalQuarter,
			'00' + Ref.PANo,
			LEFT(Ref.PGNo, 7)
	) Reference
		ON FP.FiscalQuarter = Reference.FiscalQuarter
		AND UserCustomers.CustomerNumber = Reference.CustomerNumber
		AND AllProducts.ProductNumber = Reference.ProductNumber
	LEFT JOIN
	(
		SELECT
			MAX(LVL1_ID) AS BrandID,
			MAX(RTRIM(LVL1_DESC)) AS BrandDescription,
			LVL3_ID AS ProductNumber,
			MAX(RTRIM(LVL3_DESC)) AS ProductDescription
		FROM st_SharedMaster.dbo.tbSAP_Hierarchy_Product
		GROUP BY
			LVL3_ID
	) Product
		ON AllProducts.ProductNumber = Product.ProductNumber
	JOIN st_RateAllocation_FY09H2.dbo.tbTeamsCustomers TeamsCustomers
		ON UserCustomers.CustomerNumber = TeamsCustomers.CustomerNumber
	JOIN st_RateAllocation_FY09H2.dbo.tbTeams Team
		ON TeamsCustomers.TeamNumber = Team.TeamNumber
	JOIN st_RateAllocation_FY09H2.dbo.tbCustomers Customer
		ON UserCustomers.CustomerNumber = Customer.CustomerNumber
	JOIN
	(
		SELECT DISTINCT
			SH.LVL3_ID AS Svp,
			SH.LVL3_NAME AS SvpName,
			CASE
				WHEN TC.TeamNumber IN(SH.LVL3_ID) THEN '-'
				ELSE SH.LVL4_ID
				END AS Vp,
			CASE
				WHEN TC.TeamNumber IN(SH.LVL3_ID) THEN '-'
				ELSE SH.LVL4_NAME
				END AS VpName,
			CASE
				WHEN TC.TeamNumber IN(SH.LVL3_ID, SH.LVL4_ID) THEN '-'
				ELSE SH.LVL5_ID
				END AS SrDirector,
			CASE
				WHEN TC.TeamNumber IN(SH.LVL3_ID, SH.LVL4_ID) THEN '-'
				ELSE SH.LVL5_NAME
				END AS SrDirectorName,
			CASE
				WHEN TC.TeamNumber IN(SH.LVL3_ID, SH.LVL4_ID, SH.LVL5_ID) THEN '-'
				ELSE SH.LVL6_ID
				END AS Director,
			CASE
				WHEN TC.TeamNumber IN(SH.LVL3_ID, SH.LVL4_ID, SH.LVL5_ID) THEN '-'
				ELSE SH.LVL6_NAME
				END AS DirectorName,
			TC.CustomerNumber
		FROM st_SharedMaster.dbo.tbSAP_Hierarchy_Sales SH
		JOIN st_RateAllocation_FY09H2.dbo.tbTeamsCustomers TC
			ON TC.TeamNumber IN (SH.LVL3_ID, SH.LVL4_ID, SH.LVL5_ID, SH.LVL6_ID)
	) SH
		ON UserCustomers.CustomerNumber = SH.CustomerNumber
	WHERE	ISNULL(SalesPlan.Volume, 0) > 0
		OR	ISNULL(SalesPlan.WorkingTrade, 0) > 0
		OR	ISNULL(Allocation.Volume, 0) > 0
		OR	ISNULL(Reference.Volume, 0) > 0
		OR	ISNULL(Reference.WorkingTrade, 0) > 0


	UPDATE tbPivotTableUpdates_PANo SET CachedDate = GETDATE()
COMMIT
GO