dbo-PR_T09.sql

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.PR_T09 Script Date: 2/3/2006 4:17:47 PM ******/
ALTER PROCEDURE dbo.PR_T09
(
@YEAR1 char(4) = '2006'
,@QTR1 char(6) = 'Q1'
,@VER1 char(5) = '100'

,@YEAR2 char(4) = '2006'
,@QTR2 char(6) = 'Q2'
,@VER2 char(5) = '100'

,@BU VARCHAR(50) = ''
,@BU_GRP VARCHAR(50) = ''
,@BU_GRP_SUM VARCHAR(50) = 'TCFT-CONS-NOFOX21'

,@PERIOD_START INT = 1
,@PERIOD_END INT = 12
,@AMT BIT = 1
,@PRE_AMT BIT = 1
,@POST_AMT BIT = 1
,@II BIT = 0

)

/*************************************************************************

Created By: Krister Tidics – copied from F221 written by Livia Squires
Created Date:	Feb 2, 2006
Called From:	reporting services TCFT T09
Description: TCFT Operating Profit and Loss PL VARIANCE

Input:	 1] Version info
2] II – switch to set whether Imputed interest is broken out separately

Output:	 PL data pivot for Operating Profit and Loss Reports

Revision History

Date:	 Initials:	 Desc:
**************************************************************************/

AS
BEGIN

SET NOCOUNT ON
/** Test harness
SET	TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DROP TABLE #PROJ
DECLARE
@YEAR1 CHAR(4)
,@QTR1 CHAR(6)
,@VER1 CHAR(5)
,@YEAR2 CHAR(4)
,@QTR2 CHAR(6)
,@VER2 CHAR(5)
,@BU_GRP_SUM VARCHAR(50)
,@BU_GRP VARCHAR(50)
,@BU CHAR(5)
,@II BIT
,@BY_PERIOD BIT
,@AMT INT
,@PRE_AMT INT
,@POST_AMT INT
,@PERIOD_START INT
,@PERIOD_END INT

SET	@YEAR1 = '2006'
SET	@QTR1 = 'Q1'
SET	@VER1 = '100'

SET	@YEAR2 = '2006'
SET	@QTR2 = 'Q2'
SET	@VER2 = '100'

SET	@BU_GRP_SUM = 'TCFT-CONS-NOFOX21'
SET	@BU_GRP = ''
SET	@BU = ''

SET @II = 0
SET @BY_PERIOD = 0
SET	@AMT = 1
SET	@PRE_AMT = 1
SET	@POST_AMT = 1
SET	@PERIOD_START = 1
SET	@PERIOD_END = 12

–*/

DECLARE @BU_NAME VARCHAR(50), @SCALE INT, @VER_ID INT
SET	@SCALE = 5 — no rounding

–DROP TABLE #PROJ

CREATE TABLE #PROJ (
COMPANY CHAR(5)
,PLAN_YEAR CHAR(4)
,FORECAST_QUARTER CHAR(6)
,VERSION_ID CHAR(5)
,BUSINESS_UNIT CHAR(5)
,REPORT_BUSINESS_UNIT CHAR(5)
,PERIOD INT NULL
,BUDGET_CODE CHAR(10)
,BC_DESC VARCHAR(60)
,PROJECT_ID CHAR(6) NOT NULL
,REPORT_PROJECT_ID CHAR(6) –NOT NULL
,SERIES_ID CHAR(6)
,TBD_ID CHAR(6)
,REPORT_SERIES_ID CHAR(6)
,FIRST_SEASON_PROJECT_ID CHAR(6) NULL
,LAST_SEASON_PROJECT_ID CHAR(6) NULL
,SEASON_COUNT CHAR(1) NULL
,PROJECT_GROUP NVARCHAR(15)
,PROJECT_GROUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ORDER INT
,PROJECT_GROUP_ROLLUP NVARCHAR(15)
,PROJECT_GROUP_ROLLUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ROLLUP_ORDER INT
,PROJECT_GROUP_MASTER NVARCHAR(15)
,PROJECT_GROUP_MASTER_NAME NVARCHAR(50)
,PROJECT_GROUP_MASTER_ORDER INT
,PROJECT_DESC VARCHAR (100)
,SERIES_DESC VARCHAR (100)
,INIT_AIR VARCHAR(5)
,AMOUNT MONEY
,PRE_AMOUNT MONEY
,POST_AMOUNT MONEY
,NETWORK MONEY
,DOM_SYN MONEY
,INT_TV MONEY
,DOM_HV MONEY
,INT_HV MONEY
,LM MONEY
,MUSIC MONEY
,PAY_TV MONEY
,OTHER MONEY
,REL_COST MONEY
,SERIES_PROD MONEY
,FINANCING MONEY
,POH MONEY
,RES MONEY
,PAR MONEY
,AMORT_ADJ MONEY
,VER_ID INTEGER NULL
)

CREATE NONCLUSTERED INDEX IX_PROJECT_ID ON #PROJ
(
PROJECT_ID
,PERIOD
)

CREATE NONCLUSTERED INDEX IX_FIRST_SEASON_PROJECT_ID ON #PROJ
(
FIRST_SEASON_PROJECT_ID
)

CREATE NONCLUSTERED INDEX IX_LAST_SEASON_PROJECT_ID ON #PROJ
(
LAST_SEASON_PROJECT_ID
)

CREATE NONCLUSTERED INDEX IX_SERIES_ID ON #PROJ
(
SERIES_ID
)

– GET THE PROJECT DATA
– VER 1
INSERT	#PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
–,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR1
,@QTR1
,@VER1
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 — TEMP TABLE
,1 — VER_ID

– GET THE PROJECT DATA
– VER 2
INSERT	#PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
–,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR2
,@QTR2
,@VER2
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 — TEMP TABLE
,2 — VER_ID

– UPDATE THE TBD PROJECTS IN PRIOR TO MATCH THE PROJECT HEADERS IN THE CURRENT VERSION
UPDATE PRIOR_VER
SET	 PRIOR_VER.REPORT_PROJECT_ID = CURR_VER.REPORT_PROJECT_ID
,PRIOR_VER.REPORT_SERIES_ID = CURR_VER.REPORT_SERIES_ID
,PRIOR_VER.SERIES_DESC = CURR_VER.SERIES_DESC
,PRIOR_VER.PROJECT_DESC = CURR_VER.PROJECT_DESC
–,PRIOR_VER.RELEASE_STATUS = CURR_VER.RELEASE_STATUS
–SELECT *
FROM	#PROJ PRIOR_VER
JOIN
(SELECT REPORT_PROJECT_ID,REPORT_SERIES_ID,TBD_ID,SERIES_DESC,PROJECT_DESC–,RELEASE_STATUS
FROM #PROJ
WHERE	 VER_ID = 1 AND TBD_ID IS NOT NULL)
CURR_VER
ON	 CURR_VER.TBD_ID = PRIOR_VER.PROJECT_ID
WHERE	 PRIOR_VER.VER_ID = 2

/** The next 4 insert queries are to make sure both versions have the same project ids
and budget codes in order for the inner join to work in the final select
**/
–insert any projects in ver2 that dont exist in ver1 into ver1
INSERT	 #PROJ (
VER_ID
–,PERIOD
,BUDGET_CODE
,BC_DESC
,COMPANY
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
–,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT	 1 VER_ID
–,VER2.PERIOD
,VER2.BUDGET_CODE
,VER2.BC_DESC
,VER2.COMPANY
,VER2.PROJECT_ID
,VER2.REPORT_PROJECT_ID
,VER2.SERIES_ID
,VER2.REPORT_SERIES_ID
,VER2.PROJECT_DESC
,VER2.SERIES_DESC
,VER2.BUSINESS_UNIT
,VER2.REPORT_BUSINESS_UNIT
,VER2.TBD_ID
–,VER2.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER2
WHERE	 VER2.VER_ID = 2
AND NOT EXISTS (SELECT	VER1.PROJECT_ID
FROM #PROJ VER1
WHERE	 VER1.VER_ID = 1
AND	 VER2.PROJECT_ID = VER1.PROJECT_ID
)
–projects in ver1 that don't exist in ver2 into ver2
UNION ALL

SELECT	 2 VER_ID
–,VER1.PERIOD
,VER1.BUDGET_CODE
,VER1.BC_DESC
,VER1.COMPANY
,VER1.PROJECT_ID
,VER1.REPORT_PROJECT_ID
,VER1.SERIES_ID
,VER1.REPORT_SERIES_ID
,VER1.PROJECT_DESC
,VER1.SERIES_DESC
,VER1.BUSINESS_UNIT
,VER1.REPORT_BUSINESS_UNIT
,VER1.TBD_ID
–,VER1.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER1
WHERE	 VER1.VER_ID = 1
AND NOT EXISTS (SELECT	VER2.PROJECT_ID
FROM #PROJ VER2
WHERE	 VER2.VER_ID = 2
AND	 VER2.PROJECT_ID = VER1.PROJECT_ID
)

– This update is to move Amort in the prior version up to the series level
– of the current version on the report. It needs to be done here
– so that the next query for inserting missing budget codes into VER1
– does not miss the amort which we are moving to a different series id
UPDATE	 #PROJ
SET	 PROJECT_ID = TBD_NEW_SERIES.SERIES_ID
–select #FIN.*,TBD_NEW_SERIES.TBD_ID,TBD_NEW_SERIES.SERIES_ID
FROM #PROJ
JOIN	 (
SELECT REPORT_SERIES_ID SERIES_ID,TBD_ID
FROM #PROJ
WHERE	 VER_ID = 1 AND TBD_ID IS NOT NULL
)
TBD_NEW_SERIES	ON TBD_NEW_SERIES.TBD_ID = #PROJ.PROJECT_ID
WHERE #PROJ.VER_ID = 2
AND	 (#PROJ.AMORT_ADJ <> 0 OR #PROJ.PAR <> 0 OR #PROJ.RES <> 0 )

– insert budget codes that exist in ver2 but not ver1 into ver1
– and vice versa
INSERT	 #PROJ (
VER_ID
–,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
–,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT	 1 VER_ID
–,FIN2.PERIOD
,FIN2.BUDGET_CODE
,FIN2.BC_DESC
,FIN2.PROJECT_ID
,FIN2.REPORT_PROJECT_ID
,FIN2.SERIES_ID
,FIN2.REPORT_SERIES_ID
,FIN2.PROJECT_DESC
,FIN2.SERIES_DESC
,FIN2.BUSINESS_UNIT
,FIN2.REPORT_BUSINESS_UNIT
,FIN2.TBD_ID
–,FIN2.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER
,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN2
WHERE	 FIN2.VER_ID = 2
AND NOT EXISTS (SELECT	*
FROM #PROJ FIN1
WHERE	 FIN1.VER_ID = 1
AND	 FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND	 FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)

– budget codes that exist in ver1 but not ver2 into ver2
UNION ALL
SELECT	 2 VER_ID
–,FIN1.PERIOD
,FIN1.BUDGET_CODE
,FIN1.BC_DESC
,FIN1.PROJECT_ID
,FIN1.REPORT_PROJECT_ID
,FIN1.SERIES_ID
,FIN1.REPORT_SERIES_ID
,FIN1.PROJECT_DESC
,FIN1.SERIES_DESC
,FIN1.BUSINESS_UNIT
,FIN1.REPORT_BUSINESS_UNIT
,FIN1.TBD_ID
–,FIN1.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER

,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN1
WHERE	 FIN1.VER_ID = 1
AND NOT EXISTS (SELECT	*
FROM #PROJ FIN2
WHERE	 FIN2.VER_ID = 2
AND	 FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND	 FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)

–UPDATE PROJECT_GROUPING
UPDATE #PROJ
SET	PROJECT_GROUP_NAME = RG3.GROUP_NAME
,PROJECT_GROUP_ORDER = RG3.GROUP_SORT
,PROJECT_GROUP_ROLLUP = RG2.GROUP_CODE
,PROJECT_GROUP_ROLLUP_NAME = RG2.GROUP_NAME
,PROJECT_GROUP_ROLLUP_ORDER = RG2.GROUP_SORT
,PROJECT_GROUP_MASTER = RG1.GROUP_CODE
,PROJECT_GROUP_MASTER_NAME = RG1.GROUP_NAME
,PROJECT_GROUP_MASTER_ORDER = RG1.GROUP_SORT
FROM REPORT R
JOIN REPORT_FRAME F ON R.REPORT_FRAME_ID = F.REPORT_FRAME_ID
JOIN REPORT_GROUP RG1 ON RG1.REPORT_FRAME_ID = F.REPORT_FRAME_ID AND RG1.GROUP_LEVEL = 1
JOIN REPORT_GROUP RG2 ON RG2.PARENT_REPORT_GROUP_ID = RG1.REPORT_GROUP_ID AND RG2.GROUP_LEVEL = 2
JOIN REPORT_GROUP RG3 ON RG3.PARENT_REPORT_GROUP_ID = RG2.REPORT_GROUP_ID AND RG3.GROUP_LEVEL = 3
JOIN #PROJ ON #PROJ.PROJECT_GROUP = RG3.GROUP_CODE
WHERE	R.REPORT_NUMBER = 'T01A'

–SELECT * FROM #PROJ

–ADDED KT – I AM UPDATING THE REPORT_PROJECT_IDs for all those that don't come in
UPDATE #PROJ
SET REPORT_PROJECT_ID = PROJECT_ID
,REPORT_BUSINESS_UNIT = BUSINESS_UNIT
,REPORT_SERIES_ID = SERIES_ID
WHERE REPORT_PROJECT_ID IS NULL

– final select statement
SELECT VER1.SERIES_DESC
,VER1.REPORT_SERIES_ID
,VER1.REPORT_PROJECT_ID
,VER1.PROJECT_DESC
–VER1.RELEASE_STATUS,
–,VER1.PERIOD
,VER1.BUDGET_CODE
–VER1.BC_CAT,
,VER1.BC_DESC
,VER1.PROJECT_GROUP
,VER1.PROJECT_GROUP_NAME
,VER1.PROJECT_GROUP_ORDER
,VER1.PROJECT_GROUP_ROLLUP
,VER1.PROJECT_GROUP_ROLLUP_NAME
,VER1.PROJECT_GROUP_ROLLUP_ORDER
,VER1.PROJECT_GROUP_MASTER
,VER1.PROJECT_GROUP_MASTER_NAME
,VER1.PROJECT_GROUP_MASTER_ORDER
,(VER1.AMOUNT – VER2.AMOUNT) AMOUNT
,(VER1.NETWORK – VER2.NETWORK) NETWORK
,(VER1.DOM_SYN – VER2.DOM_SYN) DOM_SYN
,(VER1.INT_TV – VER2.INT_TV) INT_TV
,(VER1.DOM_HV – VER2.DOM_HV) DOM_HV
,(VER1.INT_HV – VER2.INT_HV) INT_HV
,(VER1.LM – VER2.LM) LM
,(VER1.MUSIC – VER2.MUSIC) MUSIC
,(VER1.PAY_TV – VER2.PAY_TV) PAY_TV
,(VER1.OTHER – VER2.OTHER) OTHER
,(VER1.REL_COST – VER2.REL_COST) REL_COST
,(VER1.SERIES_PROD – VER2.SERIES_PROD) SERIES_PROD
,(VER1.FINANCING – VER2.FINANCING) FINANCING
,(VER1.POH – VER2.POH) POH
,(VER1.RES – VER2.RES) RES
,(VER1.PAR – VER2.PAR) PAR
,(VER1.AMORT_ADJ – VER2.AMORT_ADJ) AMORT_ADJ
FROM	 (
SELECT
–#PROJ.PERIOD,
#PROJ.SERIES_DESC,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
–#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
–#PROJ.BC_CAT,
–#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ

FROM #PROJ
WHERE	#PROJ.VER_ID =1
GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
–,#PROJ.RELEASE_STATUS
–,#PROJ.PERIOD
,#PROJ.BUDGET_CODE
–,#FIN.BC_CAT
,#PROJ.BC_DESC
–ORDER BY #PROJ.RELEASE_ORDER,#PROJ.SERIES_DESC,#PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
–ORDER BY #PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
) VER1
JOIN	 (
SELECT
#PROJ.SERIES_DESC,
–#PROJ.PERIOD,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
–#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
–#PROJ.BC_CAT,
–#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ

FROM #PROJ
WHERE	#PROJ.VER_ID =2

GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
–,#PROJ.PERIOD
–,#PROJ.RELEASE_STATUS
,#PROJ.BUDGET_CODE
–,#PROJ.BC_CAT
,#PROJ.BC_DESC

) VER2
ON VER1.REPORT_PROJECT_ID = VER2.REPORT_PROJECT_ID
AND VER1.BUDGET_CODE = VER2.BUDGET_CODE
ORDER BY VER1.SERIES_DESC
,VER1.REPORT_PROJECT_ID
,VER1.BUDGET_CODE

–select * from #PROJ

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO