PY_W4_QUERY_SRC(SQL View) |
Index Back |
|---|
| SELECT A.EMPLID , A.FIRST_NAME , A.MIDDLE_NAME , A.LAST_NAME , B.NATIONAL_ID , C.ADDRESS1 , C.ADDRESS2 , C.ADDRESS3 , C.ADDRESS4 , C.CITY , C.STATE , C.POSTAL , C.COUNTY , D.DESCR , A.NAME_DISPLAY , 0 FROM PS_NAMES A , PS_PERS_NID B , PS_ADDRESSES C , PS_COUNTRY_TBL D WHERE A.NAME_TYPE = 'PRI' AND A.EFF_STATUS = 'A' AND A.Effdt = ( SELECT MAX(AA.Effdt) FROM PS_NAMES AA WHERE AA.EMPLID = A.EMPLID AND AA.NAME_TYPE = 'PRI' AND AA.EFF_STATUS = 'A' AND AA.EFFDT <= %CurrentDateIn ) AND A.EMPLID = B.EMPLID AND B.PRIMARY_NID = 'Y' AND B.EMPLID = C.EMPLID AND C.ADDRESS_TYPE = 'HOME' AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(CC.Effdt) FROM PS_ADDRESSES CC WHERE CC.EMPLID = C.EMPLID AND CC.ADDRESS_TYPE = 'HOME' AND CC.EFF_STATUS = 'A' AND CC.EFFDT <= %CurrentDateIn ) AND D.COUNTRY = C.COUNTRY |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 3 | MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | MIDDLE_NAME |
| 4 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 5 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 6 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 7 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 8 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 9 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 10 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 11 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 12 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 13 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 14 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 15 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 16 | DRIVERS_LIC_NBR | Character(20) | VARCHAR2(20) NOT NULL | Driver's License Nbr |