RE_PROPERTY_RPT(SQL View) |
Index Back |
|---|---|
Property PS Query ViewProperty PS Query View |
| SELECT W.SETID , X.BUSINESS_UNIT , X.PROPERTY_ID , X.PROPERTY_CLASS , X.PROPERTY_NM AS NAME , W.LOCATION , W.COUNTRY , W.STATE , W.CITY , X.SITE_PROPERTY_ID , X.BLDG_PROPERTY_ID , X.FLOOR_PROPERTY_ID , X.AREA_PROPERTY_ID , X.SPACE_PROPERTY_ID , X.TOTAL_AREA , X.ASSET_ID , X.PROPERTY_DESCR , X.PROPERTY_SUBCLASS , X.PARENT_PROPERTY_ID , X.PARCEL_NUMBER , X.LOT , X.BLOCK , X.SPACE_UOM , X.CURRENT_OCCUPANCY , X.OCCUPANCY_STATUS , X.OWNERSHIP_STATUS , X.IS_LEASED AS IS_LEASED FROM PS_RE_ACTIVE_PPTY X , PS_RE_ASSET_LOC_VW B LEFT OUTER JOIN PS_RE_PROP_LOC_VW W ON (B.LOCATION2 = W.LOCATION) WHERE X.BUSINESS_UNIT = B.BUSINESS_UNIT_AM AND X.ASSET_ID = B.ASSET_ID AND ((W.SETID IS NULL) OR W.SETID = ( SELECT SET_CNTRL_REC.SETID FROM PS_SET_CNTRL_REC SET_CNTRL_REC WHERE SET_CNTRL_REC.SETCNTRLVALUE = X.BUSINESS_UNIT AND SET_CNTRL_REC.RECNAME = 'LOCATION_TBL') ) AND %Coalesce(W.EFFDT, %CurrentDateIn) = CASE WHEN W.EFFDT IS NOT NULL THEN ( SELECT MAX(W_ED.EFFDT) FROM PS_LOCATION_TBL W_ED WHERE W_ED.SETID = W.SETID AND W_ED.LOCATION = W.LOCATION AND W_ED.EFF_STATUS='A' AND W_ED.EFFDT <= B.LOCATION_EFFDT) ELSE %CurrentDateIn END |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Property ID |
| 4 | PROPERTY_CLASS | Character(2) | VARCHAR2(2) NOT NULL |
Property Class
10=Site 20=Building 30=Floor 40=Area 50=Space |
| 5 | PROPERTY_NM | Character(50) | VARCHAR2(50) NOT NULL | Used to store an Asset Property Name |
| 6 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 7 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 8 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 9 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 10 | SITE_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Site ID |
| 11 | BLDG_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Building ID |
| 12 | FLOOR_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Floor ID |
| 13 | AREA_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Area ID |
| 14 | SPACE_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Space ID |
| 15 | TOTAL_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Total Area |
| 16 | ASSET_ID | Character(12) | VARCHAR2(12) NOT NULL | Asset Identification |
| 17 | PROPERTY_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Property Description |
| 18 | PROPERTY_SUBCLASS | Character(20) | VARCHAR2(20) NOT NULL |
Property Subclass
010=Warehouse |
| 19 | PARENT_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Parent Property ID |
| 20 | PARCEL_NUMBER | Character(30) | VARCHAR2(30) NOT NULL | Parcel Number |
| 21 | LOT | Character(20) | VARCHAR2(20) NOT NULL | Lot Number |
| 22 | BLOCK | Character(20) | VARCHAR2(20) NOT NULL | Block Number |
| 23 | SPACE_UOM | Character(3) | VARCHAR2(3) NOT NULL | Space Unit of measure (Sq Feet or Sq Meters) |
| 24 | CURRENT_OCCUPANCY | Number(7,0) | INTEGER NOT NULL | Current Occupancy |
| 25 | OCCUPANCY_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Occupancy Status
10=Occupied 20=Vacant |
| 26 | OWNERSHIP_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Ownership Status
10=Owned 20=Leased |
| 27 | IS_LEASED | Number(1,0) | SMALLINT NOT NULL | Indicator that the property is on a lease in RE Lease Admin application. |