VIEW: APPS.PN_LOCATIONS_PUB_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'