CUSTOM Create Data Definition

SELECT 
 LOC.LOCATION_CODE LOCATION_CODE
, PROP.PROPERTY_NAME PROPERTY_NAME
, ADDR.ADDRESS_LINE1 ADDRESS_LINE1
, ADDR.ADDRESS_LINE2 ADDRESS_LINE2
, ADDR.CITY CITY
, ADDR.STATE STATE
, ADDR.ZIP_CODE ZIP_CODE
, ADDR.COUNTRY COUNTRY
, 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
, LOC.BUILDING BUILDING
FROM 
 PN_ADDRESSES_ALL ADDR
, FND_TERRITORIES_VL TERR
, PN_LOCATIONS_ALL LOC
, PN_PROPERTIES_ALL PROP
WHERE LOC.LOCATION_TYPE_LOOKUP_CODE IN ('BUILDING')
AND PROP.ATTRIBUTE1 in ('Yes', 'Y')
AND ADDR.ADDRESS_ID (+) = LOC.ADDRESS_ID
AND TERR.TERRITORY_CODE (+) = ADDR.COUNTRY
AND PROP.PROPERTY_ID (+) = LOC.PROPERTY_ID

SELECT LOC.LOCATION_CODE LOCATION_CODE , PROP.PROPERTY_NAME PROPERTY_NAME , ADDR.ADDRESS_LINE1 ADDRESS_LINE1 , ADDR.ADDRESS_LINE2 ADDRESS_LINE2 , ADDR.CITY CITY , ADDR.STATE STATE , ADDR.ZIP_CODE ZIP_CODE , ADDR.COUNTRY COUNTRY , 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 , LOC.BUILDING BUILDING FROM PN_ADDRESSES_ALL ADDR , FND_TERRITORIES_VL TERR , PN_LOCATIONS_ALL LOC , PN_PROPERTIES_ALL PROP WHERE LOC.LOCATION_TYPE_LOOKUP_CODE IN ('BUILDING') AND PROP.ATTRIBUTE1 in ('Yes', 'Y') AND ADDR.ADDRESS_ID (+) = LOC.ADDRESS_ID AND TERR.TERRITORY_CODE (+) = ADDR.COUNTRY AND PROP.PROPERTY_ID (+) = LOC.PROPERTY_ID