VIEW: APPS.PN_BUILDINGS_V

Source

View Text - Preformatted

SELECT loc.rowid row_id, loc.org_id org_id, loc.location_id location_id, loc.last_update_date last_update_date, loc.last_updated_by last_updated_by, loc.creation_date creation_date, loc.created_by created_by, loc.last_update_login last_update_login, loc.location_park_id location_park_id, loc.location_type_lookup_code location_type_lookup_code, flv.meaning location_type, loc.space_type_lookup_code space_type_lookup_code, loc.function_type_lookup_code function_type_lookup_code, loc.standard_type_lookup_code standard_type_lookup_code, loc.location_code location_code, loc.location_alias location_alias, loc.building building, loc.lease_or_owned lease_or_owned, loot.meaning lease_or_owned_tenure, loc.class class, fndc.meaning class_name, loc.status_type status_type, fnds.meaning status_type_name, loc.gross_area gross_area, loc.uom_code uom_code, loc.status status, loc.property_id property_id, prop.property_name property_name, loc.interface_flag interface_flag, loc.attribute_category attribute_category, loc.attribute1 attribute1, loc.attribute2 attribute2, loc.attribute3 attribute3, loc.attribute4 attribute4, loc.attribute5 attribute5, loc.attribute6 attribute6, loc.attribute7 attribute7, loc.attribute8 attribute8, loc.attribute9 attribute9, loc.attribute10 attribute10, loc.attribute11 attribute11, loc.attribute12 attribute12, loc.attribute13 attribute13, loc.attribute14 attribute14, loc.attribute15 attribute15, uom.unit_of_measure unit_of_measure, loc.address_id address_id, 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, terr.address_style address_style, addr.territory_id territory_id, addr.addr_attribute_category addr_attribute_category, addr.addr_attribute1 addr_attribute1, addr.addr_attribute2 addr_attribute2, addr.addr_attribute3 addr_attribute3, addr.addr_attribute4 addr_attribute4, addr.addr_attribute5 addr_attribute5, addr.addr_attribute6 addr_attribute6, addr.addr_attribute7 addr_attribute7, addr.addr_attribute8 addr_attribute8, addr.addr_attribute9 addr_attribute9, addr.addr_attribute10 addr_attribute10, addr.addr_attribute11 addr_attribute11, addr.addr_attribute12 addr_attribute12, addr.addr_attribute13 addr_attribute13, addr.addr_attribute14 addr_attribute14, addr.addr_attribute15 addr_attribute15, terr.territory_short_name territory_short_name, pnp_util_func.get_concatenated_address( addr.address_style, addr.address_line1, addr.address_line2, addr.address_line3, addr.address_line4, addr.city, addr.county, addr.state, addr.province, addr.zip_code, terr.territory_short_name ) concatenated_address, DECODE(plp1.location_park_type, 'OFFPRK', plp1.location_park_id, NULL) office_park_id, DECODE(plp1.location_park_type, 'OFFPRK', plp1.name, NULL) office_park_name, DECODE(plp1.location_park_type, 'REGION', plp1.location_park_id, plp2.location_park_id) region_id, DECODE(plp1.location_park_type, 'REGION', plp1.name, plp2.name) region_name, pnp_util_func.get_vacant_area(loc.location_id, loc.active_start_date) vacant_area, pnp_util_func.get_load_factor(loc.location_id, loc.active_start_date) load_factor, pnp_util_func.get_vacant_area_percent(loc.location_id, loc.active_start_date) prct_vacant, pnp_util_func.get_utilized_capacity(loc.location_id, loc.active_start_date) utilized_capacity, pnp_util_func.get_vacancy(loc.location_id, loc.active_start_date) vacancy, pnp_util_func.get_occupancy_percent(loc.location_id, loc.active_start_date) occupancy, pnp_util_func.get_area_utilized(loc.location_id, loc.active_start_date) area_utilized, pnp_util_func.get_floors(loc.location_id, loc.active_start_date) floors, pnp_util_func.get_offices(loc.location_id, loc.active_start_date) units, loc.active_start_date active_start_date, TO_DATE(DECODE(TO_CHAR(loc.active_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,TO_CHAR(loc.active_end_date,'MM/DD/YYYY')),'MM/DD/YYYY') active_end_date, NVL(loc.occupancy_status_code,'Y') occupancy_status_code, ocst.meaning occupancy_status, NVL(loc.assignable_emp,'Y') assignable_emp, NVL(loc.assignable_cc,'Y') assignable_cc, NVL(loc.assignable_cust,'Y') assignable_cust, loc.disposition_code disposition_code, LTRIM(RTRIM(disp.meaning)) disposition, loc.acc_treatment_code acc_treatment_code, LTRIM(RTRIM(acct.meaning)) acc_treatment FROM mtl_units_of_measure uom ,pn_addresses_all addr ,fnd_territories_vl terr ,pn_location_parks plp1 ,pn_location_parks plp2 ,fnd_lookups flv ,pn_locations loc ,pn_properties_all prop ,fnd_lookups fndc ,fnd_lookups fnds ,fnd_lookups loot ,fnd_lookups ocst ,fnd_lookups disp ,fnd_lookups acct WHERE loc.location_type_lookup_code IN ('LAND','BUILDING') AND uom.uom_code (+) = loc.uom_code AND addr.address_id (+) = loc.address_id AND terr.territory_code (+) = addr.country AND prop.property_id (+) = loc.property_id AND flv.lookup_code = loc.location_type_lookup_code AND flv.lookup_type = 'PN_LOCATION_TYPE' AND plp1.location_park_id (+) = loc.location_park_id AND plp1.language (+) = USERENV('LANG') AND plp2.location_park_id (+) = plp1.parent_location_park_id AND plp2.language (+) = USERENV('LANG') AND fndc.lookup_code (+) = loc.class AND fndc.lookup_type (+) = 'PN_CLASS_TYPE' AND fnds.lookup_code (+) = loc.status_type AND fnds.lookup_type (+) = 'PN_STATUS_TYPE' AND loot.lookup_code (+) = loc.lease_or_owned AND loot.lookup_type (+) = 'PN_LEASED_OR_OWNED' AND ocst.lookup_code (+) = NVL(loc.occupancy_status_code,'Y') AND ocst.lookup_type (+) = 'PN_OCCUPANCY_STATUS_CODE' AND disp.lookup_code (+) = loc.disposition_code AND disp.lookup_type (+) = 'PN_DISPOSITION_CODE' AND acct.lookup_code (+) = loc.acc_treatment_code AND acct.lookup_type (+) = 'PN_ACC_TREATMENT_CODE'

View Text - HTML Formatted

SELECT LOC.ROWID ROW_ID
, LOC.ORG_ID ORG_ID
, LOC.LOCATION_ID LOCATION_ID
, LOC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LOC.LAST_UPDATED_BY LAST_UPDATED_BY
, LOC.CREATION_DATE CREATION_DATE
, LOC.CREATED_BY CREATED_BY
, LOC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, LOC.LOCATION_PARK_ID LOCATION_PARK_ID
, LOC.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, FLV.MEANING LOCATION_TYPE
, LOC.SPACE_TYPE_LOOKUP_CODE SPACE_TYPE_LOOKUP_CODE
, LOC.FUNCTION_TYPE_LOOKUP_CODE FUNCTION_TYPE_LOOKUP_CODE
, LOC.STANDARD_TYPE_LOOKUP_CODE STANDARD_TYPE_LOOKUP_CODE
, LOC.LOCATION_CODE LOCATION_CODE
, LOC.LOCATION_ALIAS LOCATION_ALIAS
, LOC.BUILDING BUILDING
, LOC.LEASE_OR_OWNED LEASE_OR_OWNED
, LOOT.MEANING LEASE_OR_OWNED_TENURE
, LOC.CLASS CLASS
, FNDC.MEANING CLASS_NAME
, LOC.STATUS_TYPE STATUS_TYPE
, FNDS.MEANING STATUS_TYPE_NAME
, LOC.GROSS_AREA GROSS_AREA
, LOC.UOM_CODE UOM_CODE
, LOC.STATUS STATUS
, LOC.PROPERTY_ID PROPERTY_ID
, PROP.PROPERTY_NAME PROPERTY_NAME
, LOC.INTERFACE_FLAG INTERFACE_FLAG
, LOC.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, LOC.ATTRIBUTE1 ATTRIBUTE1
, LOC.ATTRIBUTE2 ATTRIBUTE2
, LOC.ATTRIBUTE3 ATTRIBUTE3
, LOC.ATTRIBUTE4 ATTRIBUTE4
, LOC.ATTRIBUTE5 ATTRIBUTE5
, LOC.ATTRIBUTE6 ATTRIBUTE6
, LOC.ATTRIBUTE7 ATTRIBUTE7
, LOC.ATTRIBUTE8 ATTRIBUTE8
, LOC.ATTRIBUTE9 ATTRIBUTE9
, LOC.ATTRIBUTE10 ATTRIBUTE10
, LOC.ATTRIBUTE11 ATTRIBUTE11
, LOC.ATTRIBUTE12 ATTRIBUTE12
, LOC.ATTRIBUTE13 ATTRIBUTE13
, LOC.ATTRIBUTE14 ATTRIBUTE14
, LOC.ATTRIBUTE15 ATTRIBUTE15
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, LOC.ADDRESS_ID ADDRESS_ID
, 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
, TERR.ADDRESS_STYLE ADDRESS_STYLE
, ADDR.TERRITORY_ID TERRITORY_ID
, ADDR.ADDR_ATTRIBUTE_CATEGORY ADDR_ATTRIBUTE_CATEGORY
, ADDR.ADDR_ATTRIBUTE1 ADDR_ATTRIBUTE1
, ADDR.ADDR_ATTRIBUTE2 ADDR_ATTRIBUTE2
, ADDR.ADDR_ATTRIBUTE3 ADDR_ATTRIBUTE3
, ADDR.ADDR_ATTRIBUTE4 ADDR_ATTRIBUTE4
, ADDR.ADDR_ATTRIBUTE5 ADDR_ATTRIBUTE5
, ADDR.ADDR_ATTRIBUTE6 ADDR_ATTRIBUTE6
, ADDR.ADDR_ATTRIBUTE7 ADDR_ATTRIBUTE7
, ADDR.ADDR_ATTRIBUTE8 ADDR_ATTRIBUTE8
, ADDR.ADDR_ATTRIBUTE9 ADDR_ATTRIBUTE9
, ADDR.ADDR_ATTRIBUTE10 ADDR_ATTRIBUTE10
, ADDR.ADDR_ATTRIBUTE11 ADDR_ATTRIBUTE11
, ADDR.ADDR_ATTRIBUTE12 ADDR_ATTRIBUTE12
, ADDR.ADDR_ATTRIBUTE13 ADDR_ATTRIBUTE13
, ADDR.ADDR_ATTRIBUTE14 ADDR_ATTRIBUTE14
, ADDR.ADDR_ATTRIBUTE15 ADDR_ATTRIBUTE15
, TERR.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, PNP_UTIL_FUNC.GET_CONCATENATED_ADDRESS( ADDR.ADDRESS_STYLE
, ADDR.ADDRESS_LINE1
, ADDR.ADDRESS_LINE2
, ADDR.ADDRESS_LINE3
, ADDR.ADDRESS_LINE4
, ADDR.CITY
, ADDR.COUNTY
, ADDR.STATE
, ADDR.PROVINCE
, ADDR.ZIP_CODE
, TERR.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS
, DECODE(PLP1.LOCATION_PARK_TYPE
, 'OFFPRK'
, PLP1.LOCATION_PARK_ID
, NULL) OFFICE_PARK_ID
, DECODE(PLP1.LOCATION_PARK_TYPE
, 'OFFPRK'
, PLP1.NAME
, NULL) OFFICE_PARK_NAME
, DECODE(PLP1.LOCATION_PARK_TYPE
, 'REGION'
, PLP1.LOCATION_PARK_ID
, PLP2.LOCATION_PARK_ID) REGION_ID
, DECODE(PLP1.LOCATION_PARK_TYPE
, 'REGION'
, PLP1.NAME
, PLP2.NAME) REGION_NAME
, PNP_UTIL_FUNC.GET_VACANT_AREA(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) VACANT_AREA
, PNP_UTIL_FUNC.GET_LOAD_FACTOR(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) LOAD_FACTOR
, PNP_UTIL_FUNC.GET_VACANT_AREA_PERCENT(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) PRCT_VACANT
, PNP_UTIL_FUNC.GET_UTILIZED_CAPACITY(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) UTILIZED_CAPACITY
, PNP_UTIL_FUNC.GET_VACANCY(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) VACANCY
, PNP_UTIL_FUNC.GET_OCCUPANCY_PERCENT(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) OCCUPANCY
, PNP_UTIL_FUNC.GET_AREA_UTILIZED(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) AREA_UTILIZED
, PNP_UTIL_FUNC.GET_FLOORS(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) FLOORS
, PNP_UTIL_FUNC.GET_OFFICES(LOC.LOCATION_ID
, LOC.ACTIVE_START_DATE) UNITS
, LOC.ACTIVE_START_DATE ACTIVE_START_DATE
, TO_DATE(DECODE(TO_CHAR(LOC.ACTIVE_END_DATE
, 'MM/DD/YYYY')
, '12/31/4712'
, NULL
, TO_CHAR(LOC.ACTIVE_END_DATE
, 'MM/DD/YYYY'))
, 'MM/DD/YYYY') ACTIVE_END_DATE
, NVL(LOC.OCCUPANCY_STATUS_CODE
, 'Y') OCCUPANCY_STATUS_CODE
, OCST.MEANING OCCUPANCY_STATUS
, NVL(LOC.ASSIGNABLE_EMP
, 'Y') ASSIGNABLE_EMP
, NVL(LOC.ASSIGNABLE_CC
, 'Y') ASSIGNABLE_CC
, NVL(LOC.ASSIGNABLE_CUST
, 'Y') ASSIGNABLE_CUST
, LOC.DISPOSITION_CODE DISPOSITION_CODE
, LTRIM(RTRIM(DISP.MEANING)) DISPOSITION
, LOC.ACC_TREATMENT_CODE ACC_TREATMENT_CODE
, LTRIM(RTRIM(ACCT.MEANING)) ACC_TREATMENT
FROM MTL_UNITS_OF_MEASURE UOM
, PN_ADDRESSES_ALL ADDR
, FND_TERRITORIES_VL TERR
, PN_LOCATION_PARKS PLP1
, PN_LOCATION_PARKS PLP2
, FND_LOOKUPS FLV
, PN_LOCATIONS LOC
, PN_PROPERTIES_ALL PROP
, FND_LOOKUPS FNDC
, FND_LOOKUPS FNDS
, FND_LOOKUPS LOOT
, FND_LOOKUPS OCST
, FND_LOOKUPS DISP
, FND_LOOKUPS ACCT
WHERE LOC.LOCATION_TYPE_LOOKUP_CODE IN ('LAND'
, 'BUILDING')
AND UOM.UOM_CODE (+) = LOC.UOM_CODE
AND ADDR.ADDRESS_ID (+) = LOC.ADDRESS_ID
AND TERR.TERRITORY_CODE (+) = ADDR.COUNTRY
AND PROP.PROPERTY_ID (+) = LOC.PROPERTY_ID
AND FLV.LOOKUP_CODE = LOC.LOCATION_TYPE_LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'PN_LOCATION_TYPE'
AND PLP1.LOCATION_PARK_ID (+) = LOC.LOCATION_PARK_ID
AND PLP1.LANGUAGE (+) = USERENV('LANG')
AND PLP2.LOCATION_PARK_ID (+) = PLP1.PARENT_LOCATION_PARK_ID
AND PLP2.LANGUAGE (+) = USERENV('LANG')
AND FNDC.LOOKUP_CODE (+) = LOC.CLASS
AND FNDC.LOOKUP_TYPE (+) = 'PN_CLASS_TYPE'
AND FNDS.LOOKUP_CODE (+) = LOC.STATUS_TYPE
AND FNDS.LOOKUP_TYPE (+) = 'PN_STATUS_TYPE'
AND LOOT.LOOKUP_CODE (+) = LOC.LEASE_OR_OWNED
AND LOOT.LOOKUP_TYPE (+) = 'PN_LEASED_OR_OWNED'
AND OCST.LOOKUP_CODE (+) = NVL(LOC.OCCUPANCY_STATUS_CODE
, 'Y')
AND OCST.LOOKUP_TYPE (+) = 'PN_OCCUPANCY_STATUS_CODE'
AND DISP.LOOKUP_CODE (+) = LOC.DISPOSITION_CODE
AND DISP.LOOKUP_TYPE (+) = 'PN_DISPOSITION_CODE'
AND ACCT.LOOKUP_CODE (+) = LOC.ACC_TREATMENT_CODE
AND ACCT.LOOKUP_TYPE (+) = 'PN_ACC_TREATMENT_CODE'