RS_XS_SO_GEO

(SQL View)
 Index Back

SO Geo Information

Service Order addresses are joined to Geocode table as follows: 1) City, State, Country, and Postal Code from CUST_ADDRESS matches exactly with RS_GEOCODE_CITY record. 2) City, State, and Country from CUST_ADDRESS matches with RS_GEOCODE_CITY where Postal Code does not match. This assumes that City is more likely than Postal Code to be correct in the PERSONAL_DATA record. 3) Postal Code, State, and Country from CUST_ADDRESS 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.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_RS_XS_SO_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.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY , A.STATE , A.COUNTRY , A.POSTAL %Concat ' -Error' , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_RS_XS_SO_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 G WHERE G.CITY = A.CITY AND G.STATE = A.STATE AND G.COUNTRY = A.COUNTRY AND G.POSTAL = A.POSTAL) UNION SELECT A.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , A.CITY %Concat ' -Error' , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE + 360 , B.LONGITUDE + 360 FROM PS_RS_XS_SO_ADDR A , PS_RS_GEOCODE_CITY B WHERE A.POSTAL = B.POSTAL AND A.COUNTRY = B.COUNTRY AND (A.CITY <> B.CITY OR A.STATE <> B.STATE) AND NOT EXISTS ( SELECT 'X' FROM PS_RS_GEOCODE_CITY G WHERE G.CITY = A.CITY AND G.STATE = A.STATE AND G.COUNTRY = A.COUNTRY)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 SO_ID Character(15) VARCHAR2(15) NOT NULL Service Order ID
3 SO_LINE Number(3,0) SMALLINT NOT NULL Line Number
4 CITY Character(30) VARCHAR2(30) NOT NULL City
5 STATE Character(6) VARCHAR2(6) NOT NULL State
6 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
7 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
8 LATITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
9 LONGITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude