View: PN_LOCATIONS_V

Product:	PN - Property Manager
Description:	This view is used as form view for the Locations form PNSULOCN.fmb used for defining space & its attributes
Implementation/DBA Data:	APPS.PN_LOCATIONS_V

View Text

SELECT BLD.ROWID ROW_ID
, BLD.ORG_ID ORG_ID
, BLD.LOCATION_ID LOCATION_ID
, BLD.LOCATION_CODE LOCATION_CODE
, BLD.LOCATION_ALIAS LOCATION_ALIAS
, BLD.BUILDING LOCATION_NAME
, PROP.PROPERTY_ID PROPERTY_ID
, PROP.PROPERTY_NAME PROPERTY_NAME
, BLD.LOCATION_ID BUILDING_ID
, BLD.BUILDING BUILDING
, BLD.LOCATION_CODE BUILDING_LOCATION_CODE
, TO_NUMBER(NULL) FLOOR_ID
, NULL FLOOR
, NULL FLOOR_LOCTION_CODE
, TO_NUMBER(NULL) OFFICE_ID
, NULL OFFICE
, NULL OFFICE_LOCTION_CODE
, NULL OFFICE_COMMON_AREA_FLAG
, BLD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, BLD.LAST_UPDATED_BY LAST_UPDATED_BY
, BLD.CREATION_DATE CREATION_DATE
, BLD.CREATED_BY CREATED_BY
, BLD.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, BLD.LOCATION_PARK_ID LOCATION_PARK_ID
, BLD.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, BLD.LEASE_OR_OWNED LEASE_OR_OWNED
, LOOT.MEANING LEASE_OR_OWNED_TENURE
, BLD.MAX_CAPACITY MAX_CAPACITY
, BLD.OPTIMUM_CAPACITY OPTIMUM_CAPACITY
, BLD.RENTABLE_AREA RENTABLE_AREA
, BLD.USABLE_AREA USABLE_AREA
, BLD.MAX_CAPACITY BUILDING_MAX_CAPACITY
, BLD.OPTIMUM_CAPACITY BUILDING_OPTIMUM_CAPACITY
, BLD.RENTABLE_AREA BUILDING_RENTABLE_AREA
, BLD.USABLE_AREA BUILDING_USABLE_AREA
, TO_NUMBER(NULL) FLOOR_MAX_CAPACITY
, TO_NUMBER(NULL) FLOOR_OPTIMUM_CAPACITY
, TO_NUMBER(NULL) FLOOR_RENTABLE_AREA
, TO_NUMBER(NULL) FLOOR_USABLE_AREA
, BLD.UOM_CODE UOM_CODE
, BLD.STATUS STATUS
, BLD.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, BLD.ATTRIBUTE1 ATTRIBUTE1
, BLD.ATTRIBUTE2 ATTRIBUTE2
, BLD.ATTRIBUTE3 ATTRIBUTE3
, BLD.ATTRIBUTE4 ATTRIBUTE4
, BLD.ATTRIBUTE5 ATTRIBUTE5
, BLD.ATTRIBUTE6 ATTRIBUTE6
, BLD.ATTRIBUTE7 ATTRIBUTE7
, BLD.ATTRIBUTE8 ATTRIBUTE8
, BLD.ATTRIBUTE9 ATTRIBUTE9
, BLD.ATTRIBUTE10 ATTRIBUTE10
, BLD.ATTRIBUTE11 ATTRIBUTE11
, BLD.ATTRIBUTE12 ATTRIBUTE12
, BLD.ATTRIBUTE13 ATTRIBUTE13
, BLD.ATTRIBUTE14 ATTRIBUTE14
, BLD.ATTRIBUTE15 ATTRIBUTE15
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, BLD.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
, FLV.MEANING LOCATION_TYPE
, 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
, BLD.ACTIVE_START_DATE ACTIVE_START_DATE
, TO_DATE(DECODE(TO_CHAR(BLD.ACTIVE_END_DATE
, 'MM/DD/YYYY')
, '12/31/4712'
, NULL
, TO_CHAR(BLD.ACTIVE_END_DATE
, 'MM/DD/YYYY'))
, 'MM/DD/YYYY') ACTIVE_END_DATE
, NVL(BLD.OCCUPANCY_STATUS_CODE
, 'Y') OCCUPANCY_STATUS_CODE
, OCST.MEANING OCCUPANCY_STATUS
, NVL(BLD.ASSIGNABLE_EMP
, 'Y') ASSIGNABLE_EMP
, NVL(BLD.ASSIGNABLE_CC
, 'Y') ASSIGNABLE_CC
, NVL(BLD.ASSIGNABLE_CUST
, 'Y') ASSIGNABLE_CUST
, BLD.DISPOSITION_CODE
, LTRIM(RTRIM(DISP.MEANING)) DISPOSITION
, BLD.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
, PN_PROPERTIES_ALL PROP
, FND_LOOKUPS FLV
, FND_LOOKUPS LOOT
, FND_LOOKUPS OCST
, FND_LOOKUPS DISP
, FND_LOOKUPS ACCT
, PN_LOCATIONS BLD
WHERE BLD.LOCATION_TYPE_LOOKUP_CODE IN ('BUILDING'
, 'LAND')
AND UOM.UOM_CODE (+) = BLD.UOM_CODE
AND ADDR.ADDRESS_ID = BLD.ADDRESS_ID
AND TERR.TERRITORY_CODE = ADDR.COUNTRY
AND FLV.LOOKUP_CODE = BLD.LOCATION_TYPE_LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'PN_LOCATION_TYPE'
AND PLP1.LOCATION_PARK_ID (+) = PROP.LOCATION_PARK_ID
AND PLP1.LANGUAGE (+) = USERENV('LANG')
AND PLP2.LOCATION_PARK_ID (+) = PLP1.PARENT_LOCATION_PARK_ID
AND PLP2.LANGUAGE (+) = USERENV('LANG')
AND BLD.PROPERTY_ID = PROP.PROPERTY_ID (+)
AND LOOT.LOOKUP_CODE (+) = BLD.LEASE_OR_OWNED
AND LOOT.LOOKUP_TYPE(+) = 'PN_LEASED_OR_OWNED'
AND OCST.LOOKUP_CODE(+) = NVL(BLD.OCCUPANCY_STATUS_CODE
, 'Y')
AND OCST.LOOKUP_TYPE(+) = 'PN_OCCUPANCY_STATUS_CODE'
AND DISP.LOOKUP_CODE(+) = BLD.DISPOSITION_CODE
AND DISP.LOOKUP_TYPE(+) = 'PN_DISPOSITION_CODE'
AND ACCT.LOOKUP_CODE(+) = BLD.ACC_TREATMENT_CODE
AND ACCT.LOOKUP_TYPE(+) = 'PN_ACC_TREATMENT_CODE' UNION ALL SELECT FLR.ROWID ROW_ID
, FLR.ORG_ID ORG_ID
, FLR.LOCATION_ID LOCATION_ID
, FLR.LOCATION_CODE LOCATION_CODE
, FLR.LOCATION_ALIAS LOCATION_ALIAS
, FLR.FLOOR LOCATION_NAME
, PROP.PROPERTY_ID PROPERTY_ID
, PROP.PROPERTY_NAME PROPERTY_NAME
, BLD.LOCATION_ID BUILDING_ID
, BLD.BUILDING BUILDING
, BLD.LOCATION_CODE BUILDING_LOCATION_CODE
, FLR.LOCATION_ID FLOOR_ID
, FLR.FLOOR FLOOR
, FLR.LOCATION_CODE FLOOR_LOCATION_CODE
, TO_NUMBER(NULL) OFFICE_ID
, NULL OFFICE
, NULL OFFICE_LOCATION_CODE
, NULL OFFICE_COMMON_AREA_FLAG
, FLR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, FLR.LAST_UPDATED_BY LAST_UPDATED_BY
, FLR.CREATION_DATE CREATION_DATE
, FLR.CREATED_BY CREATED_BY
, FLR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, FLR.LOCATION_PARK_ID LOCATION_PARK_ID
, FLR.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, BLD.LEASE_OR_OWNED LEASE_OR_OWNED
, LOOT.MEANING LEASE_OR_OWNED_TENURE
, FLR.MAX_CAPACITY MAX_CAPACITY
, FLR.OPTIMUM_CAPACITY OPTIMUM_CAPACITY
, FLR.RENTABLE_AREA RENTABLE_AREA
, FLR.USABLE_AREA USABLE_AREA
, TO_NUMBER(NULL) BUILDING_MAX_CAPACITY
, TO_NUMBER(NULL) BUILDING_OPTIMUM_CAPACITY
, TO_NUMBER(NULL) BUILDING_RENTABLE_AREA
, TO_NUMBER(NULL) BUILDING_USABLE_AREA
, FLR.MAX_CAPACITY FLOOR_MAX_CAPACITY
, FLR.OPTIMUM_CAPACITY FLOOR_OPTIMUM_CAPACITY
, FLR.RENTABLE_AREA FLOOR_RENTABLE_AREA
, FLR.USABLE_AREA FLOOR_USABLE_AREA
, FLR.UOM_CODE UOM_CODE
, FLR.STATUS STATUS
, FLR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, FLR.ATTRIBUTE1 ATTRIBUTE1
, FLR.ATTRIBUTE2 ATTRIBUTE2
, FLR.ATTRIBUTE3 ATTRIBUTE3
, FLR.ATTRIBUTE4 ATTRIBUTE4
, FLR.ATTRIBUTE5 ATTRIBUTE5
, FLR.ATTRIBUTE6 ATTRIBUTE6
, FLR.ATTRIBUTE7 ATTRIBUTE7
, FLR.ATTRIBUTE8 ATTRIBUTE8
, FLR.ATTRIBUTE9 ATTRIBUTE9
, FLR.ATTRIBUTE10 ATTRIBUTE10
, FLR.ATTRIBUTE11 ATTRIBUTE11
, FLR.ATTRIBUTE12 ATTRIBUTE12
, FLR.ATTRIBUTE13 ATTRIBUTE13
, FLR.ATTRIBUTE14 ATTRIBUTE14
, FLR.ATTRIBUTE15 ATTRIBUTE15
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, BLD.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
, FLV.MEANING LOCATION_TYPE
, 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
, FLR.ACTIVE_START_DATE ACTIVE_START_DATE
, TO_DATE(DECODE(TO_CHAR(FLR.ACTIVE_END_DATE
, 'MM/DD/YYYY')
, '12/31/4712'
, NULL
, TO_CHAR(FLR.ACTIVE_END_DATE
, 'MM/DD/YYYY'))
, 'MM/DD/YYYY') ACTIVE_END_DATE
, NVL(FLR.OCCUPANCY_STATUS_CODE
, 'Y') OCCUPANCY_STATUS_CODE
, OCST.MEANING OCCUPANCY_STATUS
, NVL(FLR.ASSIGNABLE_EMP
, 'Y') ASSIGNABLE_EMP
, NVL(FLR.ASSIGNABLE_CC
, 'Y') ASSIGNABLE_CC
, NVL(FLR.ASSIGNABLE_CUST
, 'Y') ASSIGNABLE_CUST
, FLR.DISPOSITION_CODE
, LTRIM(RTRIM(DISP.MEANING)) DISPOSITION
, FLR.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
, PN_PROPERTIES_ALL PROP
, FND_LOOKUPS FLV
, FND_LOOKUPS LOOT
, FND_LOOKUPS OCST
, FND_LOOKUPS DISP
, FND_LOOKUPS ACCT
, PN_LOCATIONS_ALL BLD
, PN_LOCATIONS FLR
WHERE FLR.LOCATION_TYPE_LOOKUP_CODE IN ('FLOOR'
, 'PARCEL')
AND FLV.LOOKUP_CODE = FLR.LOCATION_TYPE_LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'PN_LOCATION_TYPE'
AND ADDR.ADDRESS_ID = BLD.ADDRESS_ID
AND TERR.TERRITORY_CODE = ADDR.COUNTRY
AND UOM.UOM_CODE (+) = FLR.UOM_CODE
AND BLD.PROPERTY_ID = PROP.PROPERTY_ID (+)
AND LOOT.LOOKUP_CODE (+) = BLD.LEASE_OR_OWNED
AND PLP1.LOCATION_PARK_ID (+) = PROP.LOCATION_PARK_ID
AND PLP1.LANGUAGE (+) = USERENV('LANG')
AND PLP2.LOCATION_PARK_ID (+) = PLP1.PARENT_LOCATION_PARK_ID
AND PLP2.LANGUAGE (+) = USERENV('LANG')
AND BLD.LOCATION_ID = FLR.PARENT_LOCATION_ID
AND ((FLR.ACTIVE_START_DATE BETWEEN BLD.ACTIVE_START_DATE
AND BLD.ACTIVE_END_DATE) OR (FLR.ACTIVE_END_DATE BETWEEN BLD.ACTIVE_START_DATE
AND BLD.ACTIVE_END_DATE))
AND LOOT.LOOKUP_TYPE(+) = 'PN_LEASED_OR_OWNED'
AND OCST.LOOKUP_CODE(+) = NVL(FLR.OCCUPANCY_STATUS_CODE
, 'Y')
AND OCST.LOOKUP_TYPE(+) = 'PN_OCCUPANCY_STATUS_CODE'
AND DISP.LOOKUP_CODE(+) = FLR.DISPOSITION_CODE
AND DISP.LOOKUP_TYPE(+) = 'PN_DISPOSITION_CODE'
AND ACCT.LOOKUP_CODE(+) = FLR.ACC_TREATMENT_CODE
AND ACCT.LOOKUP_TYPE(+) = 'PN_ACC_TREATMENT_CODE' UNION ALL SELECT OFF.ROWID ROW_ID
, OFF.ORG_ID ORG_ID
, OFF.LOCATION_ID LOCATION_ID
, OFF.LOCATION_CODE LOCATION_CODE
, OFF.LOCATION_ALIAS LOCATION_ALIAS
, OFF.OFFICE LOCATION_NAME
, PROP.PROPERTY_ID PROPERTY_ID
, PROP.PROPERTY_NAME PROPERTY_NAME
, BLD.LOCATION_ID BUILDING_ID
, BLD.BUILDING BUILDING
, BLD.LOCATION_CODE BUILDING_LOCATION_CODE
, FLR.LOCATION_ID FLOOR_ID
, FLR.FLOOR FLOOR
, FLR.LOCATION_CODE FLOOR_LOCATION_CODE
, OFF.LOCATION_ID OFFICE_ID
, OFF.OFFICE OFFICE
, OFF.LOCATION_CODE OFFICE_LOCATION_CODE
, OFF.COMMON_AREA_FLAG OFFICE_COMMON_AREA_FLAG
, OFF.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OFF.LAST_UPDATED_BY LAST_UPDATED_BY
, OFF.CREATION_DATE CREATION_DATE
, OFF.CREATED_BY CREATED_BY
, OFF.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, OFF.LOCATION_PARK_ID LOCATION_PARK_ID
, OFF.LOCATION_TYPE_LOOKUP_CODE LOCATION_TYPE_LOOKUP_CODE
, BLD.LEASE_OR_OWNED LEASE_OR_OWNED
, LOOT.MEANING LEASE_OR_OWNED_TENURE
, OFF.MAX_CAPACITY MAX_CAPACITY
, OFF.OPTIMUM_CAPACITY OPTIMUM_CAPACITY
, OFF.RENTABLE_AREA RENTABLE_AREA
, OFF.USABLE_AREA USABLE_AREA
, TO_NUMBER(NULL) BUILDING_MAX_CAPACITY
, TO_NUMBER(NULL) BUILDING_OPTIMUM_CAPACITY
, TO_NUMBER(NULL) BUILDING_RENTABLE_AREA
, TO_NUMBER(NULL) BUILDING_USABLE_AREA
, TO_NUMBER(NULL) FLOOR_MAX_CAPACITY
, TO_NUMBER(NULL) FLOOR_OPTIMUM_CAPACITY
, TO_NUMBER(NULL) FLOOR_RENTABLE_AREA
, TO_NUMBER(NULL) FLOOR_USABLE_AREA
, OFF.UOM_CODE UOM_CODE
, OFF.STATUS STATUS
, OFF.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, OFF.ATTRIBUTE1 ATTRIBUTE1
, OFF.ATTRIBUTE2 ATTRIBUTE2
, OFF.ATTRIBUTE3 ATTRIBUTE3
, OFF.ATTRIBUTE4 ATTRIBUTE4
, OFF.ATTRIBUTE5 ATTRIBUTE5
, OFF.ATTRIBUTE6 ATTRIBUTE6
, OFF.ATTRIBUTE7 ATTRIBUTE7
, OFF.ATTRIBUTE8 ATTRIBUTE8
, OFF.ATTRIBUTE9 ATTRIBUTE9
, OFF.ATTRIBUTE10 ATTRIBUTE10
, OFF.ATTRIBUTE11 ATTRIBUTE11
, OFF.ATTRIBUTE12 ATTRIBUTE12
, OFF.ATTRIBUTE13 ATTRIBUTE13
, OFF.ATTRIBUTE14 ATTRIBUTE14
, OFF.ATTRIBUTE15 ATTRIBUTE15
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, BLD.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
, FLV.MEANING LOCATION_TYPE
, 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
, OFF.ACTIVE_START_DATE ACTIVE_START_DATE
, TO_DATE(DECODE(TO_CHAR(OFF.ACTIVE_END_DATE
, 'MM/DD/YYYY')
, '12/31/4712'
, NULL
, TO_CHAR(OFF.ACTIVE_END_DATE
, 'MM/DD/YYYY'))
, 'MM/DD/YYYY') ACTIVE_END_DATE
, NVL(OFF.OCCUPANCY_STATUS_CODE
, 'Y') OCCUPANCY_STATUS_CODE
, OCST.MEANING OCCUPANCY_STATUS
, NVL(OFF.ASSIGNABLE_EMP
, 'Y') ASSIGNABLE_EMP
, NVL(OFF.ASSIGNABLE_CC
, 'Y') ASSIGNABLE_CC
, NVL(OFF.ASSIGNABLE_CUST
, 'Y') ASSIGNABLE_CUST
, OFF.DISPOSITION_CODE
, LTRIM(RTRIM(DISP.MEANING)) DISPOSITION
, OFF.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
, PN_PROPERTIES_ALL PROP
, FND_LOOKUPS FLV
, FND_LOOKUPS LOOT
, FND_LOOKUPS OCST
, FND_LOOKUPS DISP
, FND_LOOKUPS ACCT
, PN_LOCATIONS_ALL BLD
, PN_LOCATIONS_ALL FLR
, PN_LOCATIONS OFF
WHERE OFF.LOCATION_TYPE_LOOKUP_CODE IN ('OFFICE'
, 'SECTION')
AND FLV.LOOKUP_CODE = OFF.LOCATION_TYPE_LOOKUP_CODE
AND FLV.LOOKUP_TYPE = 'PN_LOCATION_TYPE'
AND ADDR.ADDRESS_ID = BLD.ADDRESS_ID
AND TERR.TERRITORY_CODE = ADDR.COUNTRY
AND UOM.UOM_CODE (+) = OFF.UOM_CODE
AND BLD.PROPERTY_ID = PROP.PROPERTY_ID (+)
AND LOOT.LOOKUP_CODE (+) = BLD.LEASE_OR_OWNED
AND PLP1.LOCATION_PARK_ID (+) = PROP.LOCATION_PARK_ID
AND PLP1.LANGUAGE (+) = USERENV('LANG')
AND PLP2.LOCATION_PARK_ID (+) = PLP1.PARENT_LOCATION_PARK_ID
AND PLP2.LANGUAGE (+) = USERENV('LANG')
AND FLR.LOCATION_ID = OFF.PARENT_LOCATION_ID
AND BLD.LOCATION_ID = FLR.PARENT_LOCATION_ID
AND ((OFF.ACTIVE_START_DATE BETWEEN FLR.ACTIVE_START_DATE
AND FLR.ACTIVE_END_DATE) OR (OFF.ACTIVE_END_DATE BETWEEN FLR.ACTIVE_START_DATE
AND FLR.ACTIVE_END_DATE))
AND LOOT.LOOKUP_TYPE(+) = 'PN_LEASED_OR_OWNED'
AND OCST.LOOKUP_CODE(+) = NVL(OFF.OCCUPANCY_STATUS_CODE
, 'Y')
AND OCST.LOOKUP_TYPE(+) = 'PN_OCCUPANCY_STATUS_CODE'
AND DISP.LOOKUP_CODE(+) = OFF.DISPOSITION_CODE
AND DISP.LOOKUP_TYPE(+) = 'PN_DISPOSITION_CODE'
AND ACCT.LOOKUP_CODE(+) = OFF.ACC_TREATMENT_CODE
AND ACCT.LOOKUP_TYPE (+) = 'PN_ACC_TREATMENT_CODE'

Columns