--Delete Blacklisted Allocations Table if it exists
IF EXISTS (SELECT * FROM sysobjects
            WHERE id = OBJECT_ID(N'[dbo].[_backup_tbAllocations]')
              AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[_backup_tbAllocations]

--Backup Allocations Table
Select * INTO _backup_tbAllocations FROM TBALLOCATIONS
-------------------------------------------------------------------


--Delete Blacklisted Allocations Table if it exists
IF EXISTS (SELECT * FROM sysobjects
            WHERE id = OBJECT_ID(N'[dbo].[tbAllocations_Blacklist]')
              AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[tbAllocations_Blacklist]
--------------------------------------------------------------------

--Create table of Blacklisted Allocations
SELECT
	A.TimePeriodID,
	A.CustomerNumber,
	PB.ProductNumber as ProductNumber,
	Funding,
	Volume
Into tbAllocations_Blacklist
FROM
	tbAllocations A
Join
	tbProducts_Blacklist PB
on
	A.ProductNumber = PB.ProductNumber
-------------------------------------------------------------

--Delete Blacklisted Allocations from Allocation Table
DELETE
	A
FROM
	tbAllocations A
join
	tbAllocations_Blacklist AB
ON
	AB.ProductNumber = A.ProductNumber and
	AB.TimePeriodID= A.TimePeriodID and
	AB.CustomerNumber = A.CustomerNumber

--------------------------------------------------------------


--Delete Exception Table if exists
IF EXISTS (SELECT * FROM sysobjects
            WHERE id = OBJECT_ID(N'[dbo].[tbVP_Blacklist_Exceptions]')
              AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].[tbVP_Blacklist_Exceptions]
-------------------------------------------------------------------


--Find records that will be orphaned at the BD level and insert into exception table
SELECT
	AB.CustomerNumber,
	AB.ProductNumber,
	AB.FUNDING,
	AB.VOLUME
INTO
	tbVP_Blacklist_Exceptions
FROM
	tbAllocations_Blacklist AB
Join
	vwCustomersHierarchy ch
On
	ch.TimePeriodID=AB.TimePeriodID and
	ch.CustomerNumber=AB.CustomerNumber
LEFT Join
	tbAllocations A
ON
	LEFT(AB.ProductNumber,7) = LEFT(A.ProductNumber,7) and
	AB.TimePeriodID= A.TimePeriodID and
	AB.CustomerNumber = A.CustomerNumber
Where
	CH.CustomerHierarchyLevel='2' AND
	A.ProductNumber is Null
ORDER BY FUNDING DESC

--------------------------------------------------------------------------------------

DECLARE @DATA VARCHAR(max)
DECLARE @XML XML

SET @DATA ='<Allocations>'




SELECT
	A.TimeperiodID,
	A.Customernumber,
	A.ProductNumber,
	isnull(A.Volume/nullif(L3.Volume,0),0) as VolSharePerc
INTO #VolShareAll
From
	tbAllocations A
JOIN
	(Select
		a.TimePeriodID,
		a.Customernumber,
		left(a.ProductNumber,7) as Level3,
		Sum(a.Volume) as Volume
	From
		tbAllocations A
	Group By
		a.TimePeriodID,
		a.CustomerNumber,
		Left(a.Productnumber,7)) L3
ON
	L3.TimePeriodID=A.TimePeriodID and
	L3.CustomerNumber = A.CustomerNumber and
	L3.Level3= Left(A.productnumber,7)


SELECT DISTINCT
	A.TimePeriodID,
	A.CustomerNumber,
	LEFT(A.ProductNumber, 7) AS Level3,
	VolShare.ProductNumber
INTO #HighestVol
FROM tbAllocations A
CROSS APPLY
(
	SELECT TOP 1
		ProductNumber
	FROM #VolShareAll
	WHERE	TimePeriodID = A.TimePeriodID
		AND	CustomerNumber = A.CustomerNumber
		AND	LEFT(ProductNumber, 7) = LEFT(A.ProductNumber, 7)
	ORDER BY VolSharePerc
) VolShare



Select  @DATA = @DATA+  '<allocation>		 <timePeriodId>'+CONVERT(VARCHAR(10),HV.TimePeriodID)+'</timePeriodId>
												<customer>'+HV.CustomerNumber+'</customer>
												<product>'+HV.ProductNumber+'</product>
												<funding>'+CONVERT(VARCHAR(MAX),HV.Funding+A.Funding)+'</funding>
												<volume>'+CONVERT(VARCHAR(MAX),HV.Volume + A.Volume)+'</volume>
											</allocation>'
FROM tbAllocations A
JOIN
(
Select DISTINCT
CONVERT(VARCHAR(10),HV.CustomerNumber) AS CustomerNumber,
HV.TimePeriodID,
HV.ProductNumber,
SUM(AB.Funding) as Funding,
SUM(AB.Volume) AS Volume
FROM
	#HighestVol HV
JOIN
	tbAllocations_Blacklist AB
ON
	AB.TimeperiodID=HV.TimePeriodID AND
	AB.CustomerNumber = CONVERT(VARCHAR(10),HV.CustomerNumber) AND
	LEFT(AB.ProductNumber,7)=Left(HV.ProductNumber,7)
JOIN vwCustomersHierarchy CH
on
	Ch.TimePeriodID=HV.TimePeriodID AND
	CH.CustomerNumber= CONVERT(VARCHAR(10),HV.CustomerNumber)

Where
	CH.CustomerHierarchyLevel='2' and
	AB.Volume>1
group by
	HV.CustomerNumber,
	HV.TimePeriodID,
	HV.ProductNumber
)HV
ON
A.TIMEPERIODID=HV.TimePeriodID and
A.CustomerNumber=HV.CustomerNumber and
A.ProductNumber = HV.ProductNumber

DROP TABLE #VolShareAll
DROP TABLE #HighestVol



SET @DATA =@DATA +'</Allocations>'
SET @XML =@DATA

EXEC dbo.csp_Allocations_Save @DATA=@XML, @USERID='DISCOPROMOGROUP'