RE_AMLS_PPTY_VW(SQL View) |
Index Back |
|---|
| SELECT A.BUSINESS_UNIT ,A.ASSET_ID ,%Coalesce(B.PROPERTY_ID ,0) ,A.DESCR ,%Coalesce(B.PROPERTY_CLASS, '10') ,%Coalesce(C.TOTAL_AREA,0) ,%Coalesce(C.SPACE_UOM, ' ') ,C.OCCUPANCY_DT ,%Coalesce(C.PPTY_USE_AREA, 0) ,%Coalesce(C.PPTY_RENT_AREA,0) FROM PS_ASSET A LEFT OUTER JOIN PS_ASSET_PROPERTY B ON (B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.ASSET_ID = A.ASSET_ID) LEFT OUTER JOIN PS_RE_AM_PPTY_TBL C ON (C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.ASSET_ID = A.ASSET_ID) LEFT OUTER JOIN PS_RE_LS_PPTY D ON (D.BUSINESS_UNIT_AM = A.BUSINESS_UNIT AND D.ASSET_ID = A.ASSET_ID) WHERE A.ASSET_TYPE = '050' AND ((B.TOTAL_AREA = 0 OR B.SPACE_UOM = ' ') OR C.DESCR IS NOT NULL OR B.PROPERTY_ID IS NULL) AND D.LS_KEY IS NULL |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 3 | PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Property ID |
| 4 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 5 | PROPERTY_CLASS | Character(2) | VARCHAR2(2) NOT NULL |
Property Class
10=Site 20=Building 30=Floor 40=Area 50=Space |
| 6 | TOTAL_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Total Area |
| 7 | SPACE_UOM | Character(3) | VARCHAR2(3) NOT NULL | Space Unit of measure (Sq Feet or Sq Meters) |
| 8 | OCCUPANCY_DT | Date(10) | DATE | Occupancy Date |
| 9 | PPTY_USE_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Property Usable Area |
| 10 | PPTY_RENT_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Property Rentable Area |