AM_ED_LOCATION(SQL View) |
Index Back |
|---|---|
ETL Asset Location |
| SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LOCATION , A.DESCR , A.AREA_ID , A.ADDRESS1 , A.CITY , A.STATE , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_ASSET_LOC_VW2 A , PS_GEOCODE_TBL B WHERE %Upper(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.ASSET_ID , A.LOCATION , A.DESCR , A.AREA_ID , A.ADDRESS1 , A.CITY , A.STATE , A.COUNTRY , A.POSTAL %Concat ' -Error' , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_ASSET_LOC_VW2 A , PS_GEOCODE_TBL B WHERE %Upper(A.CITY) = B.CITY AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_GEOCODE_TBL X WHERE X.CITY = B.CITY AND X.STATE = B.STATE AND X.COUNTRY = B.COUNTRY) AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = B.CITY AND C.STATE = B.STATE AND C.COUNTRY = B.COUNTRY AND C.POSTAL = A.POSTAL) UNION SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LOCATION , A.DESCR , A.AREA_ID , A.ADDRESS1 , A.CITY %Concat ' -Error' , A.STATE , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_ASSET_LOC_VW2 A , PS_GEOCODE_TBL B WHERE A.POSTAL = B.POSTAL AND A.STATE = B.STATE AND A.COUNTRY = B.COUNTRY AND %Upper(A.CITY) <> B.CITY AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = %Upper(A.CITY) AND C.STATE = A.STATE AND C.COUNTRY = A.COUNTRY) UNION SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LOCATION , A.DESCR , A.AREA_ID , A.ADDRESS1 , A.CITY , A.STATE %Concat ' -Error' , A.COUNTRY , A.POSTAL , %NumToChar(B.LATITUDE) %Concat ' ' %Concat %NumToChar(B.LONGITUDE) FROM PS_ASSET_LOC_VW2 A , PS_GEOCODE_TBL B WHERE %Upper(A.CITY) = B.CITY AND A.STATE <> B.STATE AND A.COUNTRY = B.COUNTRY AND B.LATITUDE = ( SELECT MIN(X.LATITUDE) FROM PS_GEOCODE_TBL X WHERE X.CITY = B.CITY AND X.COUNTRY = B.COUNTRY) AND NOT EXISTS ( SELECT 'X' FROM PS_GEOCODE_TBL C WHERE C.CITY = B.CITY AND C.STATE = A.STATE AND C.COUNTRY = B.COUNTRY) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_AM_NONVW |
| 2 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 3 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 4 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location |
| 5 | AREA_ID | Character(10) | VARCHAR2(10) NOT NULL | Area ID |
| 6 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 7 | CITY | Character(30) | VARCHAR2(30) NOT NULL |
City
Prompt Table: RS_GEOCODE_CITY |
| 8 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 9 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 10 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 11 | GEOCODE | Character(30) | VARCHAR2(30) NOT NULL | The geocode of an address, created by concatenating the address latitude, blank space, longitude. |