RE_PROPSRCH(SQL View) |
Index Back |
|---|---|
Basic Asset Property InquiryView Record for the Basic Asset Property Inquiry report. |
| SELECT A.ASSET_ID , A.BUSINESS_UNIT , D.LOCATION , B.PROPERTY_ID , B.PROPERTY_NM , B.PROPERTY_CLASS , B.PROPERTY_SUBCLASS , B.PROPERTY_DESCR , B.PARENT_PROPERTY_ID , E.PPTY_USE_AREA , E.PPTY_RENT_AREA , B.TOTAL_AREA , B.SPACE_UOM , B.OWNERSHIP_STATUS , B.OCCUPANCY_STATUS , B.CURRENT_OCCUPANCY , D.COUNTRY , D.STATE , D.CITY , B.SITE_PROPERTY_ID , B.BLDG_PROPERTY_ID ,B.FLOOR_PROPERTY_ID ,B.AREA_PROPERTY_ID ,B.SPACE_PROPERTY_ID FROM PS_ASSET A , PS_ASSET_PROPERTY B , PS_ASSET_LOCATION C , PS_LOCATION_TBL D , PS_RE_LS_PPTY E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.ASSET_ID = C.ASSET_ID AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_ASSET_LOCATION C_ED WHERE C.BUSINESS_UNIT = C_ED.BUSINESS_UNIT AND C.ASSET_ID = C_ED.ASSET_ID AND C_ED.EFFDT <= %CurrentDateIn ) AND C.EFFSEQ = ( SELECT MAX(C_ES.EFFSEQ) FROM PS_ASSET_LOCATION C_ES WHERE C.BUSINESS_UNIT = C_ES.BUSINESS_UNIT AND C.ASSET_ID = C_ES.ASSET_ID AND C.EFFDT = C_ES.EFFDT) AND D.LOCATION = C.LOCATION AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_LOCATION_TBL D_ED WHERE D.SETID = D_ED.SETID AND D.LOCATION = D_ED.LOCATION AND D_ED.EFFDT <= C.EFFDT) AND B.PROPERTY_ID = E.PROPERTY_ID AND D.SETID = ( SELECT F.SETID FROM PS_SET_CNTRL_REC F WHERE F.SETCNTRLVALUE = A.BUSINESS_UNIT AND F.RECNAME = 'LOCATION_TBL' ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  ASSET_ID |
Character(12) | VARCHAR2(12) NOT NULL |
Asset Identification
Default Value: NEXT |
| 2 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BU_RE_NONVW |
| 3 | LOCATION |
Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 4 | PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Property ID |
| 5 | PROPERTY_NM | Character(50) | VARCHAR2(50) NOT NULL | Used to store an Asset Property Name |
| 6 | PROPERTY_CLASS | Character(2) | VARCHAR2(2) NOT NULL |
Property Class
10=Site 20=Building 30=Floor 40=Area 50=Space |
| 7 | PROPERTY_SUBCLASS | Character(20) | VARCHAR2(20) NOT NULL |
Property Subclass
010=Warehouse |
| 8 | PROPERTY_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Property Description |
| 9 | PARENT_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Parent Property ID |
| 10 | PPTY_USE_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Property Usable Area |
| 11 | PPTY_RENT_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Property Rentable Area |
| 12 | TOTAL_AREA | Number(18,3) | DECIMAL(17,3) NOT NULL | Total Area |
| 13 | SPACE_UOM | Character(3) | VARCHAR2(3) NOT NULL |
Space Unit of measure (Sq Feet or Sq Meters)
Prompt Table: UNITS_TBL |
| 14 | OWNERSHIP_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Ownership Status
10=Owned 20=Leased |
| 15 | OCCUPANCY_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Occupancy Status
10=Occupied 20=Vacant |
| 16 | CURRENT_OCCUPANCY | Number(7,0) | INTEGER NOT NULL | Current Occupancy |
| 17 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: COUNTRY_TBL |
| 18 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: %EDIT_STATE |
| 19 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 20 | SITE_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Site ID |
| 21 | BLDG_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Building ID |
| 22 | FLOOR_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Floor ID |
| 23 | AREA_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Area ID |
| 24 | SPACE_PROPERTY_ID | Number(10,0) | DECIMAL(10) NOT NULL | Space ID |