ÿþUSE [DW]

GO

/****** Object:  View [dbo].[v_HR_CurrentPositions]    Script Date: 11/15/2006 16:07:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

 Type:  		HR Current Position View

 Creation Date:	5/13/2006

 Written by:		knystrom

 Purpose:		

 Input:		

 Return:		

 Called By:		MS Access app

 Modifications (Date/Author/Purpose):

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

 Create view with CHECK OPTION 

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

 

CREATE VIEW [dbo].[v_HR_CurrentPositions]

WITH  VIEW_METADATA

AS

SELECT     pers.EDW_PERS_ID, pers.PERS_CONFIDENTIALITY_IND, pers.UIN, pers.PERS_LNAME + ', ' + pers.PERS_FNAME + ' ' + ISNULL(pers.PERS_MNAME, 

                      '') AS name_lfm, pers.PERS_LNAME, pers.PERS_FNAME, pers.PERS_MNAME, pers.PERS_NAME_SUFFIX, emp.FIRST_WORK_DT, 

                      emp.LAST_WORK_DT, emp.EMPEE_STATUS_CD, emp.EMPEE_STATUS_DESC, emp.EMPEE_CLS_CD, emp.EMPEE_CLS_LONG_DESC, 

                      emp.EMPEE_GROUP_CD, emp.EMPEE_GROUP_DESC, emp.EMPEE_DATA_EXP_DT, emp.EMPEE_LEAVE_CATGRY_CD, 

                      emp.EMPEE_LEAVE_CATGRY_DESC, emp.BNFT_CATGRY_CD, emp.BNFT_CATGRY_DESC, emp.BNFT_ELIG_IND, emp.FLSA_IND, 

                      emp.ORG_CD AS home_org, emp.EMPEE_COLL_CD, emp.EMPEE_COLL_NAME, emp.EMPEE_DEPT_CD, emp.EMPEE_DEPT_NAME, 

                      emp.WORK_PERD_CD, emp.WORK_PERD_DESC, emp.EMPEE_RET_IND, emp.EMPEE_TERMN_DT, emp.EMPEE_ORG_TITLE, emp.UNIV_SNRTY_DT, 

                      emp.EMPEE_SUB_DEPT_LEVEL_6_CD, emp.EMPEE_SUB_DEPT_LEVEL_6_NAME, emp.EMPEE_SUB_DEPT_LEVEL_7_CD, 

                      emp.EMPEE_SUB_DEPT_LEVEL_7_NAME, emp.FT_PT_STATUS_CD, emp.FT_PT_STATUS_DESC, lbr.POSN_NBR, lbr.JOB_SUFFIX, job.JOB_BGN_DT, 

                      job.JOB_END_DT, lbr.JOB_LBR_DISTR_PCT, lbr.FIN_FUND_CD, lbr.ORG_CD, lbr.FIN_ACCT_CD, lbr.FIN_PGM_CD, lbr.FIN_ACTV_CD, 

                      post.POSN_TITLE, jobdetl.JOB_DETL_TITLE, jobdetl.JOB_DETL_EMPEE_CLS_CD, jobdetl.JOB_DETL_FTE, jobdetl.JOB_DETL_PAY_PERD_SAL, 

                      jobdetl.JOB_DETL_PAY_PERD_SAL AS month_sal, jobdetl.JOB_DETL_ANNL_SAL, jobdetl.JOB_DETL_TIME_ENTRY_TYPE_DESC, 

                      jobdetl.JOB_DETL_EFF_DT, jobdetl.JOB_DETL_STATUS_DESC, post.POSN_EMPEE_CLS_LONG_DESC, post.POSN_CLS_CD, 

                      post.POSN_EMPEE_CLS_CD, post.POSN_BUDG_PRFL_CD, post.PAPE_CIV_SVC_ID, post.POSN_TYPE_DESC, loa.LOA_FROM_DT, loa.LOA_TO_DT, 

                      loa.LOA_NEXT_ELIG_DT, loa.LOA_LEAVE_REASON_CD, loa.LOA_LEAVE_REASON_DESC, loa.LOA_TYPE_CD, loa.LOA_TYPE_DESC, 

                      lbr.JOB_LBR_DISTR_PCT * jobdetl.JOB_DETL_ANNL_SAL AS annual_sal_this_job, 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, 

                      pgm.FIN_PGM_TITLE, acct.FIN_ACCT_TITLE, acct.FIN_ACCT_TYPE_CD, 

                      lbr.COA_CD + '-' + lbr.FIN_FUND_CD + '-' + lbr.ORG_CD + '-' + lbr.FIN_PGM_CD AS cfop, lbr.COA_CD

FROM         dbo.T_FIN_PGM_CD_HIST AS pgm RIGHT OUTER JOIN

                      dbo.T_FIN_FUND_CD_HIST AS fund RIGHT OUTER JOIN

                      dbo.T_ORG_CD_HIST AS org RIGHT OUTER JOIN

                      dbo.T_LOA_HIST AS loa RIGHT OUTER JOIN

                      dbo.V_EMPEE_HIST_1 AS emp INNER JOIN

                      dbo.V_EMPEE_PERS_HIST_1 AS pers ON emp.EDW_PERS_ID = pers.EDW_PERS_ID INNER JOIN

                      dbo.T_JOB_HIST AS job ON emp.EDW_PERS_ID = job.EDW_PERS_ID INNER JOIN

                      dbo.T_POSN_HIST AS post ON job.POSN_NBR = post.POSN_NBR INNER JOIN

                      dbo.T_JOB_LBR_DISTR AS lbr ON job.EDW_PERS_ID = lbr.EDW_PERS_ID AND job.POSN_NBR = lbr.POSN_NBR AND 

                      job.JOB_SUFFIX = lbr.JOB_SUFFIX AND job.COA_CD = lbr.COA_CD INNER JOIN

                      dbo.V_JOB_DETL_HIST_1 AS jobdetl ON job.EDW_PERS_ID = jobdetl.EDW_PERS_ID AND job.POSN_NBR = jobdetl.POSN_NBR AND 

                      job.JOB_SUFFIX = jobdetl.JOB_SUFFIX ON loa.COA_CD = job.COA_CD AND loa.EDW_PERS_ID = job.EDW_PERS_ID AND 

                      loa.POSN_NBR = job.POSN_NBR AND loa.JOB_SUFFIX = job.JOB_SUFFIX ON org.COA_CD = lbr.COA_CD AND org.ORG_CD = lbr.ORG_CD ON 

                      fund.COA_CD = lbr.COA_CD AND fund.FIN_FUND_CD = lbr.FIN_FUND_CD LEFT OUTER JOIN

                      dbo.T_FIN_ACCT_CD_HIST AS acct ON lbr.FIN_ACCT_CD = acct.FIN_ACCT_CD AND lbr.COA_CD = acct.COA_CD ON pgm.COA_CD = lbr.COA_CD AND 

                      pgm.FIN_PGM_CD = lbr.FIN_PGM_CD

WHERE     (jobdetl.JOB_DETL_STATUS_DESC = 'Active')

WITH CHECK OPTION



GO