CREATE PROCEDURE PRPTBUMONTHLY
(
	@year VARCHAR(8)
	,@year1 VARCHAR(8)
	,@costcenter VARCHAR(20)
	,@scenarioid VARCHAR(20)
	,@employeeid VARCHAR(20)
	,@costobjecttype VARCHAR(20)
)
AS

--DECLARE @year VARCHAR(8) = '2023'
--DECLARE @year1 VARCHAR(8) = '2022'
--DECLARE @costcenter VARCHAR(20) = ''
--DECLARE @scenarioid VARCHAR(20)  = '0000000004'
--DECLARE @employeeid VARCHAR(20) = ''
--DECLARE @costobjecttype VARCHAR(20) = ''

DECLARE @year2 VARCHAR(4)
SET @year2 = CAST((CAST(@year1 AS INT) - 1) AS VARCHAR(4))

SELECT 
	dat.year2,
	eg.ObjectDescription AS ce_group_desc,
	ce.ObjectDescription AS ce_desc,
	dat.ce_group, dat.CostElement,
	SUM(dat.amount_2) amount2,
	SUM(dat.amount_1) amount1,
	SUM(dat.total_budget) total_budget,
	SUM(dat.amount01) amount01,
	SUM(dat.amount02) amount02,
	SUM(dat.amount03) amount03,
	SUM(dat.amount04) amount04,
	SUM(dat.amount05) amount05,
	SUM(dat.amount06) amount06,
	SUM(dat.amount07) amount07,
	SUM(dat.amount08) amount08,
	SUM(dat.amount09) amount09,
	SUM(dat.amount10) amount10,
	SUM(dat.amount11) amount11,
	SUM(dat.amount12) amount12
FROM dbo.Product_GetBudgetResultCompare(@year, @year1, @year2 ,@scenarioid, @costcenter, @employeeid, @costobjecttype) dat
LEFT JOIN dbo.PHROM0001 AS eg
	ON eg.ObjectClass = 'EG' AND eg.ObjectID = dat.ce_group
	AND eg.StartDate <= (@year + '0101') AND eg.EndDate >= (@year + '0101') 
LEFT JOIN dbo.PHROM0001 AS ce
	ON ce.ObjectClass = 'CE' AND ce.ObjectID = dat.CostElement
	AND ce.StartDate <= (@year + '0101') AND ce.EndDate >= (@year + '0101') 
WHERE (dat.CostCenter = @costcenter OR @costcenter = '')
GROUP BY dat.year2, dat.ce_group, dat.cc_group, dat.CostElement, eg.ObjectDescription, ce.ObjectDescription