ÿþUSE [ED]

GO

/****** Object:  View [dbo].[v_HR_TransactionsView]    Script Date: 10/12/2006 16:11:35 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

 Type:  		HR 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 

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

DROP  VIEW v_HR_Transactions*/

CREATE VIEW [dbo].[v_HR_TransactionsView]

WITH  VIEW_METADATA

AS

SELECT     HRT.trans_id, EDP.uid AS uin, HRT.people_id, EDP.EDW_PERS_ID, EDP.lname, EDP.fname, EDP.mname, HRT.submit_to_univ_date, 

                      HRT.needs_delivery, HRT.delivered_to_univ_date, HRT.job_descriptor, HRT.trans_type, HRT.memo, HRT.internal_notes, HRT.cur_dept, 

                      HRT.cur_eclass, HRT.new_dept, HRT.new_eclass, HRT.cur_totalFTE, HRT.Cur_CheckDist, HRT.Chg_TotalFTE, HRT.Chg_CheckDist, HRT.Cur_Position, 

                      HRT.Cur_ChgDate, HRT.Cur_Title, HRT.Cur_FTE, HRT.Cur_Hourly, HRT.Cur_Monthly, HRT.Cur_Annual, HRT.Cur_Factor, HRT.Cur_TSOrg, 

                      HRT.Cur_EntryMthd, HRT.Cur_JobEndDt, HRT.cur_visatype, HRT.new_visatype, HRT.cur_visaexpire_date, HRT.new_visaexpire_date, 

                      HRT.cur_bdgt_profile, HRT.new_bdgt_profile, HRT.Chg_Title, HRT.Chg_EClass, HRT.Chg_FTE, HRT.Chg_Hourly, HRT.Chg_Monthly, HRT.Chg_Annual, 

                      HRT.Chg_Factor, HRT.Chg_TSOrg, HRT.Chg_EntryMthd, HRT.Chg_JobEndDt, HRT.Chg_Other1, HRT.Chg_Other2, HRT.New_Position, 

                      HRT.New_BeginDt, HRT.New_JobType, HRT.New_Title, HRT.New_PClass, HRT.New_FTE, HRT.New_Hourly, HRT.New_Monthly, HRT.New_Annual, 

                      HRT.New_Factor, HRT.New_TSOrg, HRT.New_EntryMthd, HRT.New_JobEndDt, HRT.FoapalChgDt, HRT.I9, HRT.ExtHelp, HRT.GrpChg, HRT.CV, 

                      HRT.OffLet, HRT.MastRef, HRT.ServExc, HRT.Sep, HRT.ChgFTE, HRT.FlexYr, HRT.Prov, HRT.SumExc, HRT.TmpUpG, HRT.AASum, HRT.online_app, 

                      HRT.newhire_forms, HRT.status, HRT.submitter_org_cd, HRT.dept_head_approved, HRT.submit_to_col_date, HRT.budget_profile, HRT.created_date, 

                      HRT.created_by, HRT.modified_date, HRT.modified_by, HRT.timestamp, HRTF.trans_foapal_id, HRTF.coa, HRTF.index_cd, HRTF.fund, HRTF.org, 

                      HRTF.acct, HRTF.program, HRTF.activity, HRTF.loc, HRTF.amount, HRTF.percentage, HRT.delete_requested, 

                      HRTF.timestamp AS HRTF_timestamp

FROM         dbo.HR_Transactions AS HRT LEFT OUTER JOIN

                      dbo.ED_PEOPLE AS EDP ON HRT.people_id = EDP.people_id LEFT OUTER JOIN

                      dbo.HR_Transaction_x_Foapal AS HRTF ON HRT.trans_id = HRTF.trans_id

WHERE     (IS_MEMBER('UIUC\ED DBS HR ADMIN') = 1) OR

                      EXISTS

                          (SELECT     perm_id, group_name, org_cd, doc_file_path, Created_Date, Created_By, Modified_Date, Modified_By, timestamp

                            FROM          dbo.HR_Group_x_Org AS HRGO

                            WHERE      (HRT.submitter_org_cd = org_cd) AND (dbo.fn_GROUP_IsMember_bln(SYSTEM_USER, group_name) = 'true'))

WITH CHECK OPTION



GO=