RS_XS_WRKR_GEO (SQL View)	Index Back
Worker Geo Codes Worker addresses are joined to Geocode tables as follows: 1) City, State, and Postal Code from PERSONAL_DATA matches exactly with RS_GEOCODE_CITY record. 2) City and State from PERSONAL_DATA matches with RS_GEOCODE_CITY where Postal Code does not match. This assumes that Postal Code is more likely than City to be incorrect in the PERSONAL_DATA record. 3) Postal Code and State from PERSONAL_DATA matches with RS_GEOCODE_CITY record where City does not match. This catches errors where the city is spelled differently or incorrectly in the two tables. For example: St. Louis vs Saint Louis.

RS_XS_WRKR_GEO

(SQL View)

Worker Geo Codes

Worker addresses are joined to Geocode tables as follows: 1) City, State, and Postal Code from PERSONAL_DATA matches exactly with RS_GEOCODE_CITY record. 2) City and State from PERSONAL_DATA matches with RS_GEOCODE_CITY where Postal Code does not match. This assumes that Postal Code is more likely than City to be incorrect in the PERSONAL_DATA record. 3) Postal Code and State from PERSONAL_DATA matches with RS_GEOCODE_CITY record where City does not match. This catches errors where the city is spelled differently or incorrectly in the two tables. For example: St. Louis vs Saint Louis.

SELECT A.EMPLID , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_RS_XS_WRKR_ADDR A , PS_RS_GEOCODE_CITY B WHERE A.CITY = B.CITY AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND A.POSTAL = B.POSTAL UNION SELECT A.EMPLID , A.CITY , A.STATE , A.COUNTRY , A.POSTAL %Concat ' -Error' , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_RS_XS_WRKR_ADDR A , PS_RS_GEOCODE_CITY B WHERE A.CITY = B.CITY AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_RS_GEOCODE_CITY X WHERE X.CITY = A.CITY AND X.STATE = A.STATE AND X.COUNTRY = A.COUNTRY) AND NOT EXISTS ( SELECT 'X' FROM PS_RS_GEOCODE_CITY C WHERE C.CITY = B.CITY AND C.STATE = B.STATE AND C.COUNTRY = B.COUNTRY AND C.POSTAL = A.POSTAL) UNION SELECT A.EMPLID , A.CITY %Concat ' -Error' , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_RS_XS_WRKR_ADDR A , PS_RS_GEOCODE_CITY B WHERE A.POSTAL = B.POSTAL AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND A.CITY <> B.CITY AND NOT EXISTS ( SELECT 'X' FROM PS_RS_GEOCODE_CITY C WHERE C.CITY = A.CITY AND C.STATE = A.STATE AND C.COUNTRY = A.COUNTRY)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 CITY Character(30) VARCHAR2(30) NOT NULL City
Prompt Table: RS_GEOCODE_CITY

3 STATE Character(6) VARCHAR2(6) NOT NULL State

4 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

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

6 LATITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude

7 LONGITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	CITY	Character(30)	VARCHAR2(30) NOT NULL	City Prompt Table: RS_GEOCODE_CITY
3	STATE	Character(6)	VARCHAR2(6) NOT NULL	State
4	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country
5	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
6	LATITUDE	Signed Number(12,7)	DECIMAL(10,7) NOT NULL	Latitude
7	LONGITUDE	Signed Number(12,7)	DECIMAL(10,7) NOT NULL	Longitude