HGA_MGR_SEC_VW (SQL View)	Index Back
Empl Security Rec for Manager Direct Line Reports + Delegated employeess used in MSS pivot grids.

HGA_MGR_SEC_VW

(SQL View)

Empl Security Rec for Manager

Direct Line Reports + Delegated employeess used in MSS pivot grids.

SELECT DISTINCT DR.EMPLID , DR.EMPL_RCD , DR.JOB_EFFDT , DR.SUPERVISOR_ID , DR.ACCESS_TYPE , 0 , DR.DIRECT_REPORTS , OPR.OPRID FROM PS_HGA_DIRREP_VW DR , PS_GP_INSTALLATION INST , PSOPRDEFN OPR WHERE INST.PG_ACCESS_TYPE = DR.ACCESS_TYPE AND OPR.EMPLID = DR.SUPERVISOR_ID UNION ALL SELECT DISTINCT C.EMPLID , C.EMPL_RCD , C.JOB_EFFDT , DEL.PROXY_ID , C.ACCESS_TYPE , 0 , 'C' , OPR.OPRID FROM PS_HGA_DIRREP_VW C , PS_HCDL_DELEGATION DEL , PSOPRDEFN OPR , PS_GP_INSTALLATION INST WHERE OPR.EMPLID = DEL.PROXY_ID AND C.SUPERVISOR_ID = DEL.DELEGATOR_ID AND DEL.TRANSACTION_NAME = 'GP_ABS_MGRSS_HIST' AND (FROM_DATE <= %CurrentDateIn AND (TO_DATE >= %CurrentDateIn OR TO_DATE IS NULL )) AND DLGT_STATUS = 'A' AND INST.PG_ACCESS_TYPE = C.ACCESS_TYPE AND NOT EXISTS ( SELECT 'Y' FROM PS_HGA_DIRREP_VW C1 , PS_GP_INSTALLATION INST WHERE INST.PG_ACCESS_TYPE = C1.ACCESS_TYPE AND C.EMPLID = C1.EMPLID AND C.EMPL_RCD = C1.EMPL_RCD AND C.ACCESS_TYPE = C1.ACCESS_TYPE AND C1.SUPERVISOR_ID = DEL.PROXY_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 JOB_EFFDT Date(10) DATE Job Effective Date

4 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

5 ACCESS_TYPE Character(1) VARCHAR2(1) NOT NULL Access Type
1=By Dept Security Tree
2=By Supervisor ID
3=By Department Manager ID
4=By Reports To Position
5=By Part Posn Mgmt Supervisor
6=By Part Posn Mgmt Dept Mgr ID
7=By Group ID

6 HR_DR_LEVEL Number(3,0) SMALLINT NOT NULL Number denoting how many levels down the organizational chart the employee is from the supervisor. Direct reports are always level 1.

7 REPORTSTO_TYPE Character(1) VARCHAR2(1) NOT NULL Reports to type: D - Direct I - Indirect C - Delegated P - Other (Pending Approval)
A=All
C=Delegated
D=Direct
I=Indirect
P=Other

8 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	JOB_EFFDT	Date(10)	DATE	Job Effective Date
4	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
5	ACCESS_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Access Type 1=By Dept Security Tree 2=By Supervisor ID 3=By Department Manager ID 4=By Reports To Position 5=By Part Posn Mgmt Supervisor 6=By Part Posn Mgmt Dept Mgr ID 7=By Group ID
6	HR_DR_LEVEL	Number(3,0)	SMALLINT NOT NULL	Number denoting how many levels down the organizational chart the employee is from the supervisor. Direct reports are always level 1.
7	REPORTSTO_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Reports to type: D - Direct I - Indirect C - Delegated P - Other (Pending Approval) A=All C=Delegated D=Direct I=Indirect P=Other
8	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).