HR_EE_SC_CLR_VW (SQL View)	Index Back
EE Primary Sec. Clearance View View of an employee's primary security clearance types. Returns the primary clearance type for an employee that is both current and active.

HR_EE_SC_CLR_VW

(SQL View)

EE Primary Sec. Clearance View

View of an employee's primary security clearance types. Returns the primary clearance type for an employee that is both current and active.

SELECT A.EMPLID ,A.SEC_CLEARANCE_TYPE ,A.EFFDT ,A.EFF_STATUS ,A.EXPIRATION_DATE ,A.SEC_CLEARANCE_NUM ,A.SPONSOR FROM PS_HR_EE_SEC_CLR A WHERE A.EFF_STATUS = 'A' AND A.PRIMARY_INDICATOR = 'Y' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_HR_EE_SEC_CLR A1 WHERE A1.EMPLID = A.EMPLID AND A1.SEC_CLEARANCE_TYPE = A.SEC_CLEARANCE_TYPE AND A1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 SEC_CLEARANCE_TYPE Character(3) VARCHAR2(3) NOT NULL Security Clearance Type
Prompt Table: SEC_CLR_TYP_TBL

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

5 EXPIRATION_DATE Date(10) DATE Expiration Date

6 SEC_CLEARANCE_NUM Character(20) VARCHAR2(20) NOT NULL Security Clearance Number

7 SPONSOR Character(50) VARCHAR2(50) NOT NULL Sponsor

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	SEC_CLEARANCE_TYPE	Character(3)	VARCHAR2(3) NOT NULL	Security Clearance Type Prompt Table: SEC_CLR_TYP_TBL
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive
5	EXPIRATION_DATE	Date(10)	DATE	Expiration Date
6	SEC_CLEARANCE_NUM	Character(20)	VARCHAR2(20) NOT NULL	Security Clearance Number
7	SPONSOR	Character(50)	VARCHAR2(50) NOT NULL	Sponsor