PC_GEOCODE_PROJ

(SQL View)
 Index Back

Project Location Geocodes

This view converts the Project Location data into latitude and longitude geocodes.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.EFFDT , A.LOCATION , A.LOCATION_DESCR , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE , B.LONGITUDE , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_PC_CUR_LOC_VW2 A , PS_GEOCODE_TBL 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.PROJECT_ID , A.EFFDT , A.LOCATION , A.LOCATION_DESCR , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE , B.LONGITUDE , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_PC_CUR_LOC_VW2 A , PS_GEOCODE_TBL B WHERE A.CITY = B.CITY AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND A.POSTAL <> B.POSTAL AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_GEOCODE_TBL X WHERE X.CITY = A.CITY AND X.STATE = A.STATE AND X.COUNTRY = A.COUNTRY) AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = A.CITY AND C.STATE = A.STATE AND C.COUNTRY = A.COUNTRY AND C.POSTAL = A.POSTAL) UNION SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.EFFDT , A.LOCATION , A.LOCATION_DESCR , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , B.LATITUDE , B.LONGITUDE , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_PC_CUR_LOC_VW2 A , PS_GEOCODE_TBL B WHERE A.POSTAL = B.POSTAL AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND A.CITY <> B.CITY AND A.POSTAL <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE (C.CITY = A.CITY AND C.STATE = A.STATE AND C.COUNTRY = A.COUNTRY AND C.POSTAL = A.POSTAL) OR (C.CITY = A.CITY AND C.STATE = A.STATE AND C.COUNTRY = A.COUNTRY AND C.POSTAL <> A.POSTAL))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
3 EFFDT Date(10) DATE Effective Date
4 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
5 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
6 CITY Character(30) VARCHAR2(30) NOT NULL City

Prompt Table: RS_GEOCODE_CITY

7 STATE Character(6) VARCHAR2(6) NOT NULL State
8 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
9 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
10 LATITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Latitude
11 LONGITUDE Signed Number(12,7) DECIMAL(10,7) NOT NULL Longitude
12 GEOCODE Character(30) VARCHAR2(30) NOT NULL The geocode of an address, created by concatenating the address latitude, blank space, longitude.