ÿþUSE [DW]

GO

/****** Object:  View [dbo].[v_FIN_Payroll]    Script Date: 10/13/2006 10:47:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

 Type:  		FIN_Payroll View

 Creation Date:	5/13/2005

 Written by:		knystrom

 Purpose:		Returns record level permissions record set

 Input:		

 Return:		

 Called By:		MS Access app

 Modifications (Date/Author/Purpose):

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

 Create view with CHECK OPTION 

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

CREATE VIEW [dbo].[v_FIN_Payroll]

AS

SELECT     PERS.EDW_PERS_ID, PERS.UIN, PERS.PERS_LNAME + ', ' + PERS.PERS_FNAME + ' ' + ISNULL(PERS.PERS_MNAME, '') AS name_lfm, 

                      PERS.PERS_LNAME, PERS.PERS_FNAME, PERS.PERS_MNAME, PAYDETAIL.PAYR_YR, PAYDETAIL.PAYR_ID, PAYDETAIL.PAYR_NBR, 

                      PAYDETAIL.PAYR_SEQ_NBR, PAYEVENT.PAYR_YR AS PAYR_YR_pe, PAYEVENT.PAYR_ID AS PAYR_ID_pe, 

                      PAYEVENT.PAYR_NBR AS PAYR_NBR_pe, PAYEVENT.PAYR_SEQ_NBR AS PAYR_SEQ_NBR_pe, PAYEVENT.PAYR_EVENT_ID_DESC, 

                      PAYEVENT.PAYR_EVENT_DT, PAYEVENT.PAYR_EVENT_GROSS_AMT, PAYEVENT.PAYR_EVENT_TYPE_DESC, PAYEVENT.PAYR_EVENT_EFF_DT, 

                      PAYDETAIL.PAYR_ACCTG_EARNS_CD, PAYDETAIL.PAYR_ACCTG_EARNS_LONG_DESC, PAYDETAIL.PAYR_ACCTG_BNFT_DED_CD, 

                      PAYDETAIL.PAYR_ACCTG_BNFT_DED_LONG_DESC, PAYDETAIL.PAYR_ACCTG_EVENT_DESC, PAYDETAIL.PAYR_ACCTG_TRAN_SRC_DESC, 

                      PAYDETAIL.PAYR_ACCTG_CATGRY_CD, PAYDETAIL.PAYR_ACCTG_CATGRY_DESC, PAYDETAIL.FY_CD, PAYDETAIL.PAYR_ACCTG_RULE_CLS_CD, 

                      PAYDETAIL.COA_CD, PAYDETAIL.FIN_FUND_CD, PAYDETAIL.ORG_CD, PAYDETAIL.FIN_ACCT_CD, PAYDETAIL.FIN_PGM_CD, PAYDETAIL.GRANT_CD, 

                      PAYDETAIL.PAYR_ACCTG_FUNDING_SRC_DESC, PAYDETAIL.PAYR_ACCTG_TRAN_SIGN_CD, PAYDETAIL.PAYR_ACCTG_TRAN_DT, 

                      PAYDETAIL.PAYR_ACCTG_AMT, PAYDETAIL.PAYR_ACCTG_HOURS, PAYDETAIL.PAYR_ACCTG_EMPEE_CLS_CD, 

                      PAYDETAIL.PAYR_ACCTG_EMPEE_CLS_LONG_DESC, PAYDETAIL.PAYR_ACCTG_EFF_DT, PAYDETAIL.PAYR_ACCTG_DED_TYPE_DESC, 

                      PAYDETAIL.PAYR_ACCTG_EXPS_AMT, PAYDETAIL.PAYR_ACCTG_ENC_AMT, PAYDETAIL.PAYR_ACCTG_TRAN_NBR, ISNULL(PAYDETAIL.PAYR_ID, '') 

                      + '-' + ISNULL(CONVERT(varchar(10), PAYDETAIL.PAYR_NBR), '') AS payr_id_nbr, 

                      PAYDETAIL.FIN_FUND_CD + PAYDETAIL.ORG_CD + PAYDETAIL.FIN_PGM_CD AS cfop, FUND.FIN_FUND_TITLE, ORG.ORG_TITLE, 

                      ORG.COLL_LEVEL_3_CD, ORG.COLL_LEVEL_3_TITLE, ORG.DEPT_LEVEL_5_CD, ORG.DEPT_LEVEL_5_TITLE, ACCT.FIN_ACCT_TITLE, 

                      ACCT.FIN_ACCT_TYPE_CD, PGM.FIN_PGM_TITLE, JOBDETL.JOB_DETL_TITLE, JOBDETL.JOB_DETL_EMPEE_CLS_CD, JOBDETL.JOB_DETL_FTE, 

                      PAYDETAIL.FIN_FUND_CD + '-' + PAYDETAIL.ORG_CD + '-' + PAYDETAIL.FIN_PGM_CD AS fop, PAYDETAIL.POSN_NBR, PAYDETAIL.JOB_SUFFIX, 

                      PAYDETAIL.FIN_ACTV_CD, PAYDETAIL.PAYR_EVENT_TYPE_CD, PAYDETAIL.EDW_FIN_DOC_ID, POSTDOC.POST_DOC_NBR, 

                      ORG.CAMPUS_LEVEL_1_TITLE, FUND.FIN_FUND_EXPEND_END_DT, FUND.FIN_FUND_CD_EFF_DT, FUND.FIN_FUND_CD_TERMN_DT

FROM         dbo.T_FIN_ACCT_CD_HIST AS ACCT RIGHT OUTER JOIN

                      dbo.T_FIN_FUND_CD_HIST AS FUND RIGHT OUTER JOIN

                      dbo.V_PAYR_ACCTG_DETL AS PAYDETAIL INNER JOIN

                      dbo.V_EMPEE_PERS_HIST_1 AS PERS ON PAYDETAIL.EDW_PERS_ID = PERS.EDW_PERS_ID ON FUND.COA_CD = PAYDETAIL.COA_CD AND 

                      FUND.FIN_FUND_CD = PAYDETAIL.FIN_FUND_CD LEFT OUTER JOIN

                      dbo.T_POST_DOC AS POSTDOC ON PAYDETAIL.EDW_FIN_DOC_ID = POSTDOC.EDW_FIN_DOC_ID LEFT OUTER JOIN

                      dbo.V_JOB_DETL_HIST_1 AS JOBDETL ON PAYDETAIL.JOB_SUFFIX = JOBDETL.JOB_SUFFIX AND 

                      PAYDETAIL.POSN_NBR = JOBDETL.POSN_NBR AND PAYDETAIL.EDW_PERS_ID = JOBDETL.EDW_PERS_ID LEFT OUTER JOIN

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

                      dbo.T_FIN_PGM_CD_HIST AS PGM ON PAYDETAIL.FIN_PGM_CD = PGM.FIN_PGM_CD ON ACCT.FIN_ACCT_CD = PAYDETAIL.FIN_ACCT_CD AND 

                      ACCT.COA_CD = PAYDETAIL.COA_CD LEFT OUTER JOIN

                      dbo.V_PAYR_EVENT_1 AS PAYEVENT ON PAYDETAIL.EDW_PERS_ID = PAYEVENT.EDW_PERS_ID AND 

                      PAYDETAIL.PAYR_YR = PAYEVENT.PAYR_YR AND PAYDETAIL.PAYR_ID = PAYEVENT.PAYR_ID AND 

                      PAYDETAIL.PAYR_NBR = PAYEVENT.PAYR_NBR AND PAYDETAIL.PAYR_SEQ_NBR = PAYEVENT.PAYR_SEQ_NBR



GO