ÿþUSE [DW]

GO

/****** Object:  View [dbo].[V_T_OL_SUM]    Script Date: 10/12/2006 16:08:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/* =============================================

 Type:  		View

 Creation Date: 	4/2/200

 Written by: 		knystrom

 Purpose:		t_ol_sum decode

 Called By:		Access

 Modifications (Date/Author/Purpose):

 

 =============================================

select * from dw.dbo.v_T_OL_SUM*/

CREATE VIEW [dbo].[V_T_OL_SUM]

AS

SELECT     OL.COA_CD, OL.FY_CD, OL.FISC_PERD_CD, OL.FIN_FUND_CD, OL.ORG_CD, OL.FIN_ACCT_CD, OL.FIN_PGM_CD, OL.FIN_ACTV_CD, OL.LOCN_CD, 

                      OL.PERM_ORIG_BUDG_PERD_TOT, OL.PERM_BUDG_ADJ_PERD_TOT, OL.TEMP_BUDG_ADJ_PERD_TOT, OL.REV_PERD_TOT, OL.EXPS_PERD_TOT, 

                      OL.ENC_PERD_TOT, OL.RESERV_PERD_TOT, OL.ACCTED_BUDG_PERD_TOT, OL.PERM_ORIG_BUDG_YTD_BAL, OL.PERM_BUDG_ADJ_YTD_BAL, 

                      OL.TEMP_BUDG_ADJ_YTD_BAL, OL.REV_YTD_BAL, OL.EXPS_YTD_BAL, OL.ENC_YTD_BAL, OL.RESERV_YTD_BAL, OL.ACCTED_BUDG_YTD_BAL, 

                      OL.PERM_BUDG, OL.OP_BUDG, OL.BBA, OL.UNENC_BBA, OL.OL_SUM_EFF_DT, OL.OL_SUM_POST_DT, PERD.FISC_PERD_START_DT, 

                      PERD.FISC_PERD_END_DT, ISNULL({ fn MONTHNAME(PERD.FISC_PERD_START_DT) }, 'YearEnd') AS fisc_perd_month, 

                      OL.ENC_YTD_BAL + OL.RESERV_YTD_BAL AS total_enc, OL.COA_CD + '-' + OL.FIN_FUND_CD + '-' + OL.ORG_CD + '-' + OL.FIN_PGM_CD AS cfop, 

                      FUND.FIN_FUND_TITLE, ORG.ORG_TITLE, ORG.CAMPUS_LEVEL_1_TITLE, ORG.COLL_LEVEL_3_TITLE, ORG.SCHOOL_SUB_COLL_LEVEL_4_TITLE, 

                      ORG.DEPT_LEVEL_5_TITLE, ACCT_TYPE.FIN_ACCT_TYPE_TITLE, ACCT.FIN_ACCT_TITLE, ACCT.FIN_ACCT_LEVEL_1_CD, 

                      ACCT.FIN_ACCT_LEVEL_1_TITLE, ACCT.FIN_ACCT_LEVEL_2_CD, ACCT.FIN_ACCT_LEVEL_2_TITLE, ACCT.FIN_ACCT_LEVEL_3_CD, 

                      ACCT.FIN_ACCT_LEVEL_3_TITLE, ACCT.FIN_ACCT_CD_NORM_BAL, ACCT.FIN_ACCT_TYPE_CD, PGM.FIN_PGM_TITLE

FROM         dbo.T_FIN_FUND_CD_HIST AS FUND RIGHT OUTER JOIN

                      dbo.T_OL_SUM AS OL LEFT OUTER JOIN

                      dbo.T_FIN_PGM_CD_HIST AS PGM ON OL.COA_CD = PGM.COA_CD AND OL.FIN_PGM_CD = PGM.FIN_PGM_CD LEFT OUTER JOIN

                      dbo.T_ORG_CD_HIST AS ORG ON OL.COA_CD = ORG.COA_CD AND OL.ORG_CD = ORG.ORG_CD LEFT OUTER JOIN

                      dbo.T_FIN_ACCT_TYPE_CD_HIST AS ACCT_TYPE ON OL.COA_CD = ACCT_TYPE.COA_CD AND LEFT(OL.FIN_ACCT_CD, 1) 

                      = ACCT_TYPE.FIN_ACCT_TYPE_CD LEFT OUTER JOIN

                      dbo.T_FISC_PERD_CD AS PERD ON OL.COA_CD = PERD.COA_CD AND OL.FISC_PERD_CD = PERD.FISC_PERD_CD AND 

                      OL.FY_CD = PERD.FY_CD LEFT OUTER JOIN

                      dbo.T_FIN_ACCT_CD_HIST AS ACCT ON OL.COA_CD = ACCT.COA_CD AND OL.FIN_ACCT_CD = ACCT.FIN_ACCT_CD ON FUND.COA_CD = OL.COA_CD AND

                       FUND.FIN_FUND_CD = OL.FIN_FUND_CD



GO