View: PN_LOCATIONS_PUB_V

Product:	PN - Property Manager
Description:	Public view used to view location information.
Implementation/DBA Data:	APPS.PN_LOCATIONS_PUB_V

View Text

SELECT PLO.ROWID ROW_ID
, PLO.LOCATION_ID
, PLO.LOCATION_CODE
, PLO.LOCATION_TYPE_LOOKUP_CODE
, PLO.ACTIVE_START_DATE
, PLO.ACTIVE_END_DATE
, PLO.LOCATION_ALIAS
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, NULL
, 'LAND'
, NULL
, PLF.LOCATION_CODE) PARENT_LOCATION_CODE
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'OFFICE'
, PLB.LOCATION_CODE
, 'SECTION'
, PLB.LOCATION_CODE
, NULL) GPARENT_LOCATION_CODE
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'OFFICE'
, PLO.OFFICE
, 'SECTION'
, PLO.OFFICE
, 'FLOOR'
, PLO.FLOOR
, 'PARCEL'
, PLO.FLOOR
, 'BUILDING'
, PLO.BUILDING
, 'LAND'
, PLO.BUILDING) LOCATION_NAME
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'OFFICE'
, PLF.FLOOR
, 'SECTION'
, PLF.FLOOR
, 'FLOOR'
, PLB.BUILDING
, 'PARCEL'
, PLB.BUILDING
, NULL) PARENT_LOCATION_NAME
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'OFFICE'
, PLB.BUILDING
, 'SECTION'
, PLB.BUILDING
, NULL) GPARENT_LOCATION_NAME
, PLO.SUITE
, PLO.UOM_CODE
, PLO.SPACE_TYPE_LOOKUP_CODE SPACE_TYPE_LOOKUP_CODE
, SPC.MEANING SPACE_TYPE
, PLO.FUNCTION_TYPE_LOOKUP_CODE FUNCTION_TYPE_LOOKUP_CODE
, FUN.MEANING FUNCTION_TYPE
, PLO.STANDARD_TYPE_LOOKUP_CODE STANDARD_TYPE_LOOKUP_CODE
, STD.MEANING STANDARD_TYPE
, PLO.STATUS_TYPE STATUS_TYPE
, STYP.MEANING STATUS_TYPE_NAME
, PLO.MAX_CAPACITY
, PLO.OPTIMUM_CAPACITY
, PLO.RENTABLE_AREA
, PLO.USABLE_AREA
, PLO.GROSS_AREA
, PLO.ASSIGNABLE_AREA
, PLO.COMMON_AREA
, PNP_UTIL_FUNC.GET_VACANT_AREA(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE))) VACANT_AREA
, ROUND(DECODE(PLO.USABLE_AREA
, 0
, 0
, (1 - PLO.RENTABLE_AREA/PLO.USABLE_AREA))
, 2) VACANT_AREA_PERCENT
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, PNP_UTIL_FUNC.GET_FLOORS(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'LAND'
, PNP_UTIL_FUNC.GET_FLOORS(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'FLOOR'
, PNP_UTIL_FUNC.GET_OFFICES(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'PARCEL'
, PNP_UTIL_FUNC.GET_OFFICES(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 1) LEVELS
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, PNP_UTIL_FUNC.GET_OFFICES(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'LAND'
, PNP_UTIL_FUNC.GET_OFFICES(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, NULL) UNITS
, PNP_UTIL_FUNC.GET_UTILIZED_CAPACITY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE))) UTILIZED_CAPACITY
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, PNP_UTIL_FUNC.GET_LOAD_FACTOR(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'LAND'
, PNP_UTIL_FUNC.GET_LOAD_FACTOR(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, NULL) LOAD_FACTOR
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, PNP_UTIL_FUNC.GET_AREA_UTILIZED(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'LAND'
, PNP_UTIL_FUNC.GET_AREA_UTILIZED(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, NULL) UTILIZED_AREA
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, PNP_UTIL_FUNC.GET_VACANCY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'LAND'
, PNP_UTIL_FUNC.GET_VACANCY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'FLOOR'
, PNP_UTIL_FUNC.GET_FLOOR_VACANCY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'PARCEL'
, PNP_UTIL_FUNC.GET_FLOOR_VACANCY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'OFFICE'
, PNP_UTIL_FUNC.GET_OFFICE_VACANCY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, 'SECTION'
, PNP_UTIL_FUNC.GET_OFFICE_VACANCY(PLO.LOCATION_ID
, NVL( PNP_UTIL_FUNC.GET_AS_OF_DATE_4_LOC_PUBVIEW
, LEAST(SYSDATE
, PLO.ACTIVE_END_DATE)))
, NULL) VACANCY
, DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'FLOOR'
, PNP_UTIL_FUNC.GET_FLOOR_SECONDARY_AREA(PLO.LOCATION_ID)
, 'PARCEL'
, PNP_UTIL_FUNC.GET_FLOOR_SECONDARY_AREA(PLO.LOCATION_ID)
, 'OFFICE'
, PNP_UTIL_FUNC.GET_OFFICE_SECONDARY_AREA(PLO.LOCATION_ID)
, 'SECTION'
, PNP_UTIL_FUNC.GET_OFFICE_SECONDARY_AREA(PLO.LOCATION_ID)
, NULL) SECONDARY_CIRCULATION
, PLO.COMMON_AREA_FLAG
, PP.PROPERTY_ID
, PP.PROPERTY_NAME
, PP.PROPERTY_CODE
, PLB.LEASE_OR_OWNED TENURE_CODE
, LOOT.MEANING TENURE
, PLB.CLASS
, FL1.MEANING CLASS_MEANING
, PLO.STATUS
, PLF.STATUS PARENT_STATUS
, PLB.STATUS GPARENT_STATUS
, ADDR.ADDRESS_LINE1 ADDRESS_LINE1
, ADDR.ADDRESS_LINE2 ADDRESS_LINE2
, ADDR.ADDRESS_LINE3 ADDRESS_LINE3
, ADDR.ADDRESS_LINE4 ADDRESS_LINE4
, ADDR.COUNTY COUNTY
, ADDR.CITY CITY
, ADDR.STATE STATE
, ADDR.PROVINCE PROVINCE
, ADDR.ZIP_CODE ZIP_CODE
, ADDR.COUNTRY COUNTRY
, ADDR.TERRITORY_ID TERRITORY_ID
, ADDR.ATTRIBUTE_CATEGORY ADDR_ATTRIBUTE_CATEGORY
, ADDR.ATTRIBUTE1 ADDR_ATTRIBUTE1
, ADDR.ATTRIBUTE2 ADDR_ATTRIBUTE2
, ADDR.ATTRIBUTE3 ADDR_ATTRIBUTE3
, ADDR.ATTRIBUTE4 ADDR_ATTRIBUTE4
, ADDR.ATTRIBUTE5 ADDR_ATTRIBUTE5
, ADDR.ATTRIBUTE6 ADDR_ATTRIBUTE6
, ADDR.ATTRIBUTE7 ADDR_ATTRIBUTE7
, ADDR.ATTRIBUTE8 ADDR_ATTRIBUTE8
, ADDR.ATTRIBUTE9 ADDR_ATTRIBUTE9
, ADDR.ATTRIBUTE10 ADDR_ATTRIBUTE10
, ADDR.ATTRIBUTE11 ADDR_ATTRIBUTE11
, ADDR.ATTRIBUTE12 ADDR_ATTRIBUTE12
, ADDR.ATTRIBUTE13 ADDR_ATTRIBUTE13
, ADDR.ATTRIBUTE14 ADDR_ATTRIBUTE14
, ADDR.ATTRIBUTE15 ADDR_ATTRIBUTE15
, TERR.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, DECODE(PLPO.LOCATION_PARK_TYPE
, 'OFFPRK'
, PLPO.NAME
, NULL) OFFICE_PARK
, DECODE(PLPO.LOCATION_PARK_TYPE
, 'REGION'
, PLPO.NAME
, PLPR.NAME) REGION
, PLO.LAST_UPDATE_DATE
, PLO.LAST_UPDATED_BY
, PLO.CREATION_DATE
, PLO.CREATED_BY
, PLO.LAST_UPDATE_LOGIN
, PLO.ATTRIBUTE_CATEGORY
, PLO.ATTRIBUTE1
, PLO.ATTRIBUTE2
, PLO.ATTRIBUTE3
, PLO.ATTRIBUTE4
, PLO.ATTRIBUTE5
, PLO.ATTRIBUTE6
, PLO.ATTRIBUTE7
, PLO.ATTRIBUTE8
, PLO.ATTRIBUTE9
, PLO.ATTRIBUTE10
, PLO.ATTRIBUTE11
, PLO.ATTRIBUTE12
, PLO.ATTRIBUTE13
, PLO.ATTRIBUTE14
, PLO.ATTRIBUTE15
, PLO.ORG_ID
, PLO.BOOKABLE_FLAG
, NVL(PLO.OCCUPANCY_STATUS_CODE
, 'Y') OCCUPANCY_STATUS_CODE
, OCST.MEANING OCCUPANCY_STATUS
, NVL(PLO.ASSIGNABLE_EMP
, 'Y') ASSIGNABLE_EMP
, NVL(PLO.ASSIGNABLE_CC
, 'Y') ASSIGNABLE_CC
, NVL(PLO.ASSIGNABLE_CUST
, 'Y') ASSIGNABLE_CUST
, PLO.DISPOSITION_CODE DISPOSITION_CODE
, LTRIM(RTRIM(DISP.MEANING)) DISPOSITION
, PLO.ACC_TREATMENT_CODE ACC_TREATMENT_CODE
, LTRIM(RTRIM(ACCT.MEANING)) ACC_TREATMENT
FROM PN_LOCATIONS_ALL PLO
, PN_LOCATIONS_ALL PLF
, PN_LOCATIONS_ALL PLB
, PN_ADDRESSES_ALL ADDR
, FND_TERRITORIES_VL TERR
, PN_PROPERTIES_ALL PP
, FND_LOOKUPS LOOT
, FND_LOOKUPS FL1
, FND_LOOKUPS SPC
, FND_LOOKUPS FUN
, FND_LOOKUPS STD
, FND_LOOKUPS STYP
, FND_LOOKUPS OCST
, FND_LOOKUPS DISP
, FND_LOOKUPS ACCT
, PN_LOCATION_PARKS PLPO
, PN_LOCATION_PARKS PLPR
WHERE PLF.LOCATION_ID = DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'OFFICE'
, PLO.PARENT_LOCATION_ID
, 'SECTION'
, PLO.PARENT_LOCATION_ID
, 'FLOOR'
, PLO.PARENT_LOCATION_ID
, 'PARCEL'
, PLO.PARENT_LOCATION_ID
, 'BUILDING'
, PLO.LOCATION_ID
, 'LAND'
, PLO.LOCATION_ID)
AND PLO.ACTIVE_START_DATE BETWEEN PLF.ACTIVE_START_DATE
AND PLF.ACTIVE_END_DATE
AND PLB.LOCATION_ID = DECODE(PLF.LOCATION_TYPE_LOOKUP_CODE
, 'FLOOR'
, PLF.PARENT_LOCATION_ID
, 'PARCEL'
, PLF.PARENT_LOCATION_ID
, 'BUILDING'
, PLF.LOCATION_ID
, 'LAND'
, PLF.LOCATION_ID)
AND PLF.ACTIVE_START_DATE BETWEEN PLB.ACTIVE_START_DATE
AND PLB.ACTIVE_END_DATE
AND PP.PROPERTY_ID(+) = PLB.PROPERTY_ID
AND PLPO.LOCATION_PARK_ID(+) = PP.LOCATION_PARK_ID
AND PLPO.LANGUAGE(+) = USERENV('LANG')
AND PLPR.LOCATION_PARK_ID(+) = PLPO.PARENT_LOCATION_PARK_ID
AND PLPR.LANGUAGE(+) = USERENV('LANG')
AND LOOT.LOOKUP_CODE(+) = PLB.LEASE_OR_OWNED
AND LOOT.LOOKUP_TYPE(+) = 'PN_LEASED_OR_OWNED'
AND FL1.LOOKUP_CODE(+) = PLB.LEASE_OR_OWNED
AND FL1.LOOKUP_TYPE(+) = 'PN_CLASS_TYPE'
AND ADDR.ADDRESS_ID = PLB.ADDRESS_ID
AND TERR.TERRITORY_CODE = ADDR.COUNTRY
AND SPC.LOOKUP_CODE (+) = PLO.SPACE_TYPE_LOOKUP_CODE
AND SPC.LOOKUP_TYPE (+) = DECODE(PLO.LOCATION_TYPE_LOOKUP_CODE
, 'BUILDING'
, 'PN_SPACE_TYPE'
, 'FLOOR'
, 'PN_SPACE_TYPE'
, 'OFFICE'
, 'PN_SPACE_TYPE'
, 'LAND'
, 'PN_PARCEL_TYPE'
, 'PARCEL'
, 'PN_PARCEL_TYPE'
, 'SECTION'
, 'PN_PARCEL_TYPE')
AND FUN.LOOKUP_CODE (+) = PLO.FUNCTION_TYPE_LOOKUP_CODE
AND FUN.LOOKUP_TYPE (+) = 'PN_FUNCTION_TYPE'
AND STD.LOOKUP_CODE (+) = PLO.STANDARD_TYPE_LOOKUP_CODE
AND STD.LOOKUP_TYPE (+) = 'PN_STANDARD_TYPE'
AND STYP.LOOKUP_CODE(+) = PLO.STATUS_TYPE
AND STYP.LOOKUP_TYPE(+) = 'PN_STATUS_TYPE'
AND OCST.LOOKUP_CODE(+) = NVL(PLO.OCCUPANCY_STATUS_CODE
, 'Y')
AND OCST.LOOKUP_TYPE(+) = 'PN_OCCUPANCY_STATUS_CODE'
AND DISP.LOOKUP_CODE(+) = PLO.DISPOSITION_CODE
AND DISP.LOOKUP_TYPE(+) = 'PN_DISPOSITION_CODE'
AND ACCT.LOOKUP_CODE(+) = PLO.ACC_TREATMENT_CODE
AND ACCT.LOOKUP_TYPE(+) = 'PN_ACC_TREATMENT_CODE'

Columns