HR_RWWRKADDR_VW

(SQL View)

SELECT R.EMPLID ,R.EMPL_RCD ,R.LOCATION2 , A.EFFDT , A.EFF_STATUS ,R.SETID2 , A.DESCR , A.DESCRSHORT , A.BUILDING , %subrec(ADDRESS_SBR,A) ,CASE WHEN A.LASTUPDDTTM IS NOT NULL AND A.LASTUPDDTTM >=R.LASTUPDDTTM THEN A.LASTUPDDTTM WHEN R.LASTUPDDTTM IS NOT NULL THEN R.LASTUPDDTTM ELSE %CurrentDateTimeIn END FROM PS_HR_RWORK_VW R ,PS_LOCATION_TBL A WHERE R.HR_RW_LOC_TYPE='W' AND R.LOCATION2=A.LOCATION AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_LOCATION_TBL B WHERE a.LOCATION=B.LOCATION AND B.EFF_STATUS = A.EFF_STATUS AND R.SETID2 =B.SETID AND ( B.EFFDT <= %CurrentDateIn )) AND (R.START_DATE IS NULL OR R.START_DATE=( SELECT MAX(START_DATE) FROM PS_HR_RWORK_TBL R1 WHERE R.EMPLID=R1.EMPLID AND R.EMPL_RCD=R1.EMPL_RCD AND R1.START_DATE <= %CurrentDateIn) ) AND (R.END_DATE IS NULL OR R.END_DATE >= %CurrentDateIn )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

4 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive
Default Value: A

6 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

7 DESCR Character(30) VARCHAR2(30) NOT NULL Description

8 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

9 BUILDING Character(10) VARCHAR2(10) NOT NULL Building Nbr

10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

11 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

12 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

13 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

14 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

15 CITY Character(30) VARCHAR2(30) NOT NULL City

16 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1

17 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2

18 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference

19 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1

20 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2

21 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3

22 COUNTY Character(30) VARCHAR2(30) NOT NULL County

23 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: STATE_TBL

24 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

25 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code

26 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit
Y/N Table Edit

27 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
4	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
5	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive Default Value: A
6	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
7	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
8	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
9	BUILDING	Character(10)	VARCHAR2(10) NOT NULL	Building Nbr
10	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
11	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
12	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
13	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
14	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
15	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
16	NUM1	Character(6)	VARCHAR2(6) NOT NULL	Number 1
17	NUM2	Character(6)	VARCHAR2(6) NOT NULL	Number 2
18	HOUSE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	House Type AB=House Boat Reference WW=House Trailer Reference
19	ADDR_FIELD1	Character(2)	VARCHAR2(2) NOT NULL	Address Field 1
20	ADDR_FIELD2	Character(4)	VARCHAR2(4) NOT NULL	Address Field 2
21	ADDR_FIELD3	Character(4)	VARCHAR2(4) NOT NULL	Address Field 3
22	COUNTY	Character(30)	VARCHAR2(30) NOT NULL	County
23	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: STATE_TBL
24	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
25	GEO_CODE	Character(11)	VARCHAR2(11) NOT NULL	Geo Code
26	IN_CITY_LIMIT	Character(1)	VARCHAR2(1) NOT NULL	In City Limit Y/N Table Edit
27	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.