HR_SEC_CLEAR_VW

(SQL View)
 Index Back

Security Clearance View

This is a view used to fetch Security Clearance information for Expiration Inquiry Page.

SELECT DISTINCT A.EMPLID , B.EMPL_RCD , A.SEC_CLEARANCE_TYPE , D.PER_ORG , C.NAME , C.LAST_NAME , C.SECOND_LAST_SRCH , C.NAME_AC , A.EFF_STATUS , A.SEC_CLEARANCE_NUM , A.EXPIRATION_DATE , A.SPONSOR FROM PS_HR_EE_SEC_CLR A , PS_JOB_EXPIR_NT_VW B , PS_PERSONAL_DATA C , PS_JOB D WHERE A.EMPLID = B.EMPLID AND B.EMPLID = C.EMPLID AND D.EMPLID = A.EMPLID AND D.EMPL_RCD = B.EMPL_RCD AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_HR_EE_SEC_CLR AA WHERE AA.EMPLID = A.EMPLID AND AA.SEC_CLEARANCE_TYPE = A.SEC_CLEARANCE_TYPE AND AA.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS='A' UNION SELECT DISTINCT A1.EMPLID , 0 , A1.SEC_CLEARANCE_TYPE ,'POI' , C1.NAME , C1.LAST_NAME , C1.SECOND_LAST_SRCH , C1.NAME_AC , A1.EFF_STATUS , A1.SEC_CLEARANCE_NUM , A1.EXPIRATION_DATE , A1.SPONSOR FROM PS_HR_EE_SEC_CLR A1 ,PS_PER_POI_ONLY B1 , PS_PERSONAL_DATA C1 WHERE A1.EMPLID = B1.EMPLID AND A1.EMPLID = C1.EMPLID AND A1.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_HR_EE_SEC_CLR AA1 WHERE AA1.EMPLID = A1.EMPLID AND AA1.SEC_CLEARANCE_TYPE = A1.SEC_CLEARANCE_TYPE AND AA1.EFFDT <= %CurrentDateIn)

  • Related Language Record: PERSON_NAME_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 SEC_CLEARANCE_TYPE Character(3) VARCHAR2(3) NOT NULL Security Clearance Type

    Prompt Table: SEC_CLR_TYP_TBL

    4 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
    CWR=Contingent Worker
    EMP=Employee
    POI=Person of Interest

    Default Value: PER_ORG_ASGN.PER_ORG

    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    7 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    8 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
    9 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    10 SEC_CLEARANCE_NUM Character(20) VARCHAR2(20) NOT NULL Security Clearance Number
    11 EXPIRATION_DATE Date(10) DATE Expiration Date
    12 SPONSOR Character(50) VARCHAR2(50) NOT NULL Sponsor