HR_BADGE_VW(SQL View) |
Index Back |
|---|---|
Badge ViewThis 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 ) |
| # | 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 Rcd Nbr |
| 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 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 |