HR_BADGE_VW

(SQL View)
 Index Back

Badge View

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

SELECT DISTINCT A.EMPLID , D.EMPL_RCD , A.BADGE_TYPE , D.PER_ORG ,C.NAME ,C.LAST_NAME ,C.SECOND_LAST_SRCH ,C.NAME_AC ,A.EFF_STATUS , A.BADGE_NBR , A.EXPIRATION_DATE , A.COMMENTSHORT FROM PS_BADGE_TBL 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 D.EMPL_RCD = A.EMPL_RCD AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_BADGE_TBL AA WHERE AA.EMPLID = A.EMPLID AND AA.BADGE_TYPE = A.BADGE_TYPE AND AA.EFFDT <= %CurrentDateIn ) AND A.EFF_STATUS = 'A' UNION SELECT DISTINCT A1.EMPLID ,0 , A1.BADGE_TYPE ,'POI' ,C1.NAME ,C1.LAST_NAME ,C1.SECOND_LAST_SRCH ,C1.NAME_AC ,A1.EFF_STATUS , A1.BADGE_NBR , A1.EXPIRATION_DATE , A1.COMMENTSHORT FROM PS_BADGE_TBL 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_BADGE_TBL AA1 WHERE AA1.EMPLID = A1.EMPLID AND AA1.BADGE_TYPE = A1.BADGE_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 BADGE_TYPE Character(3) VARCHAR2(3) NOT NULL Pertains to the Type of Badge for an employee. A Badge Type of "CLOCK" corresponds to specific Time and Labor functionality.
    BA=Building Access
    CCA=Computer Center Access
    CLK=Clock
    GB=General Badge
    PA=Parking Access
    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 BADGE_NBR Character(20) VARCHAR2(20) NOT NULL Badge Number
    11 EXPIRATION_DATE Date(10) DATE Expiration Date
    12 COMMENTSHORT Character(30) VARCHAR2(30) NOT NULL Short Comment