RS_XS_SO_GEO(SQL View) |
Index Back |
---|---|
SO Geo InformationService 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 |