ÿþUSE [DW]

GO

/****** Object:  View [dbo].[v_FIN_NonPayroll]    Script Date: 10/12/2006 15:59:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

 Type:  v_FIN_NonPayroll

 Creation Date:  3/16/2006

 Written by:  knystrom

 Purpose:  

 Called By:

 Modifications (Date/Author/Purpose):

 

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

CREATE VIEW [dbo].[v_FIN_NonPayroll]

AS

SELECT     dwol.FIN_ACCT_CD, dwol.OL_DETL_EXPS_AMT, dwol.OL_DETL_EDW_ID, dwol.EDW_FIN_DOC_ID, dwol.POST_TRAN_DOC_TYPE_SEQ_NBR, 

                      dwol.POST_TRAN_LINE_ITEM_NBR, dwol.POST_TRAN_SEQ_NBR, dwol.POST_TRAN_SUBMSN_NBR, dwol.POST_TRAN_RVRSL_IND, 

                      dwol.POST_TRAN_SERIAL_NBR, dwol.OL_DETL_DOC_TYPE_CD, dwol.OL_DETL_DOC_TYPE_DESC, dwol.OL_DETL_RULE_CLS_CD, 

                      dwol.OL_DETL_RULE_CLS_DESC, dwol.OL_DETL_RULE_PRCS_CD, dwol.OL_DETL_BNR_RULE_PRCS_CD, dwol.OL_DETL_PERM_ORIG_BUDG_AMT, 

                      dwol.OL_DETL_PERM_BUDG_ADJ_AMT, dwol.OL_DETL_TEMP_BUDG_ADJ_AMT, dwol.OL_DETL_REV_AMT, dwol.OL_DETL_ENC_AMT, 

                      dwol.OL_DETL_RESERV_AMT, dwol.OL_DETL_ACCTED_BUDG_AMT, dwol.OL_DETL_TRAN_DT, dwol.OL_DETL_DESC, 

                      dwol.OL_DETL_USER_DOC_REF_1_NBR, dwol.OL_DETL_USER_DOC_REF_2_NBR, dwol.COA_CD, dwol.FY_CD, dwol.OL_DETL_POST_PERD, 

                      dwol.OL_DETL_BUDG_PERD, dwol.FIN_FUND_CD, dwol.ORG_CD, dwol.FIN_PGM_CD, dwol.FIN_ACTV_CD, dwol.LOCN_CD, 

                      dwol.OL_DETL_FUND_CATGRY_CD, dwol.OL_DETL_ENC_EDW_FIN_DOC_ID, dwol.ENC_DOC_LINE_ITEM, dwol.ENC_DOC_SEQ_NBR, 

                      dwol.OL_DETL_POST_DTTIME, dwol.OL_DETL_EDW_POST_DT, dwol.OL_DETL_EFF_DT, dwol.OL_DETL_USER_ID, 

                      ISNULL({ fn MONTHNAME(dwperd.FISC_PERD_START_DT) }, 'YearEnd') AS month_post_perd, 

                      dwol.FIN_FUND_CD + dwol.ORG_CD + dwol.FIN_PGM_CD AS CFOP, dwfunhist.GRANT_CD, dwfunhist.FIN_FUND_TITLE, 

                      dwfunhist.FIN_FUND_TYPE_CD, dwfunhist.FIN_FUND_LEVEL_NBR, dwfunhist.FIN_FUND_LEVEL_1_TITLE, dwfunhist.FIN_FUND_LEVEL_2_TITLE, 

                      dwaccthist.FIN_ACCT_TITLE, dwaccthist.FIN_ACCT_TYPE_CD, dwaccthist.FIN_ACCT_LEVEL_NBR, dwaccthist.FIN_ACCT_LEVEL_1_TITLE, 

                      dwaccthist.FIN_ACCT_LEVEL_2_TITLE, dwpgmhist.FIN_PGM_TITLE, dwpgmhist.FIN_PGM_LEVEL_NBR, dwpgmhist.NACUBO_FUNC_LEVEL_1_CD, 

                      dwpgmhist.NACUBO_FUNC_LEVEL_1_TITLE, dwpost.POST_DOC_NBR, dworghist.ORG_TITLE, dworghist.COLL_LEVEL_3_CD, 

                      dworghist.COLL_LEVEL_3_TITLE, dworghist.DEPT_LEVEL_5_CD, dworghist.DEPT_LEVEL_5_TITLE, dworghist.CAMPUS_LEVEL_1_TITLE, 

                      dwfunhist.FIN_FUND_CD_EFF_DT, dwfunhist.FIN_FUND_CD_TERMN_DT, dwfunhist.FIN_FUND_EXPEND_END_DT

FROM         dbo.T_OL_DETL AS dwol LEFT OUTER JOIN

                      dbo.T_FIN_PGM_CD_HIST AS dwpgmhist ON dwol.COA_CD = dwpgmhist.COA_CD AND dwol.FIN_PGM_CD = dwpgmhist.FIN_PGM_CD LEFT OUTER JOIN

                      dbo.T_FIN_FUND_CD_HIST AS dwfunhist ON dwol.COA_CD = dwfunhist.COA_CD AND dwol.FIN_FUND_CD = dwfunhist.FIN_FUND_CD LEFT OUTER JOIN

                      dbo.T_ORG_CD_HIST AS dworghist ON dwol.ORG_CD = dworghist.ORG_CD AND dwol.COA_CD = dworghist.COA_CD LEFT OUTER JOIN

                      dbo.T_FIN_ACCT_CD_HIST AS dwaccthist ON dwol.FIN_ACCT_CD = dwaccthist.FIN_ACCT_CD LEFT OUTER JOIN

                      dbo.T_FISC_PERD_CD AS dwperd ON dwol.COA_CD = dwperd.COA_CD AND dwol.FY_CD = dwperd.FY_CD AND 

                      dwol.OL_DETL_POST_PERD = dwperd.FISC_PERD_CD RIGHT OUTER JOIN

                      dbo.T_POST_DOC AS dwpost ON dwol.EDW_FIN_DOC_ID = dwpost.EDW_FIN_DOC_ID

WHERE     (dwol.OL_DETL_EXPS_AMT <> 0) AND (dwol.FIN_ACCT_CD < '20000' OR

                      dwol.FIN_ACCT_CD IN ('219240', '219260'))



GO