VIEW: APPS.IGS_PE_ADDR_V

Source

View Text - Preformatted

SELECT PS.PARTY_ID PERSON_ID, PS.PARTY_SITE_ID PARTY_SITE_ID, PS.LOCATION_ID LOCATION_ID, TO_DATE(NULL) WH_UPDATE_DATE, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, PS.ATTRIBUTE1 ATTRIBUTE1, PS.ATTRIBUTE2 ATTRIBUTE2, PS.ATTRIBUTE3 ATTRIBUTE3, PS.ATTRIBUTE4 ATTRIBUTE4, PS.ATTRIBUTE5 ATTRIBUTE5, PS.ATTRIBUTE6 ATTRIBUTE6, PS.ATTRIBUTE7 ATTRIBUTE7, PS.ATTRIBUTE8 ATTRIBUTE8, PS.ATTRIBUTE9 ATTRIBUTE9, PS.ATTRIBUTE10 ATTRIBUTE10, PS.ATTRIBUTE11 ATTRIBUTE11, PS.ATTRIBUTE12 ATTRIBUTE12, PS.ATTRIBUTE13 ATTRIBUTE13, PS.ATTRIBUTE14 ATTRIBUTE14, PS.ATTRIBUTE15 ATTRIBUTE15, PS.ATTRIBUTE16 ATTRIBUTE16, PS.ATTRIBUTE17 ATTRIBUTE17, PS.ATTRIBUTE18 ATTRIBUTE18, PS.ATTRIBUTE19 ATTRIBUTE19, PS.ATTRIBUTE20 ATTRIBUTE20, PS.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE, L.ADDRESS_EFFECTIVE_DATE ADDRESS_EFFECTIVE_DATE, L.ADDRESS_EXPIRATION_DATE ADDRESS_EXPIRATION_DATE, PS.MAILSTOP MAILSTOP, PS.IDENTIFYING_ADDRESS_FLAG IDENTIFYING_ADDRESS_FLAG, PS.LANGUAGE PARTY_SITE_LANGUAGE, PS.STATUS STATUS, PS.PARTY_SITE_NAME PARTY_SITE_NAME, L.ADDRESS_KEY ADDRESS_KEY, FT.ADDRESS_STYLE ADDRESS_STYLE, L.VALIDATED_FLAG VALIDATED_FLAG, L.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC, L.PO_BOX_NUMBER PO_BOX_NUMBER, L.HOUSE_NUMBER HOUSE_NUMBER, L.STREET_SUFFIX STREET_SUFFIX, L.STREET STREET, L.STREET_NUMBER STREET_NUMBER, L.FLOOR FLOOR, L.SUITE SUITE, L.POSTAL_PLUS4_CODE POSTAL_PLUS4_CODE, L.POSITION POSITION, L.LOCATION_DIRECTIONS LOCATION_DIRECTIONS, L.CLLI_CODE CLLI_CODE, L.LANGUAGE LOCATION_LANGUAGE, L.SHORT_DESCRIPTION SHORT_DESCRIPTION, L.DESCRIPTION DESCRIPTION, L.CONTENT_SOURCE_TYPE LOCATION_CONTENT_SOURCE_TYPE, IGSPS.START_DATE START_DT, IGSPS.END_DATE END_DT, TO_DATE(NULL) DATE_LAST_VERIFIED, PS.IDENTIFYING_ADDRESS_FLAG CORRESPONDENCE, L.ADDRESS1 ADDR_LINE_1, L.ADDRESS2 ADDR_LINE_2, L.ADDRESS3 ADDR_LINE_3, L.ADDRESS4 ADDR_LINE_4, L.CITY CITY, L.STATE STATE, L.PROVINCE PROVINCE, L.COUNTY COUNTY, L.COUNTRY COUNTRY_CD, FT.TERRITORY_SHORT_NAME COUNTRY, L.POSTAL_CODE POSTAL_CODE, L.DELIVERY_POINT_CODE DELIVERY_POINT_CODE, NULL OTHER_DETAILS_1, NULL OTHER_DETAILS_2, NULL OTHER_DETAILS_3, PS.CREATED_BY CREATED_BY, PS.CREATION_DATE CREATION_DATE, PS.LAST_UPDATED_BY LAST_UPDATED_BY, PS.LAST_UPDATE_DATE LAST_UPDATE_DATE, PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, PS.REQUEST_ID REQUEST_ID, PS.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, PS.PROGRAM_ID PROGRAM_ID, PS.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, L.OBJECT_VERSION_NUMBER LOCATION_OVN, PS.OBJECT_VERSION_NUMBER PARTY_SITE_OVN FROM HZ_PARTIES P, HZ_PARTY_SITES PS, HZ_LOCATIONS L, IGS_PE_HZ_PTY_SITES IGSPS, FND_TERRITORIES_VL FT WHERE P.PARTY_ID = PS.PARTY_ID AND PS.LOCATION_ID = L.LOCATION_ID AND PS.PARTY_SITE_ID = IGSPS.PARTY_SITE_ID (+) AND FT.TERRITORY_CODE = L.COUNTRY

View Text - HTML Formatted

SELECT PS.PARTY_ID PERSON_ID
, PS.PARTY_SITE_ID PARTY_SITE_ID
, PS.LOCATION_ID LOCATION_ID
, TO_DATE(NULL) WH_UPDATE_DATE
, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1 ATTRIBUTE1
, PS.ATTRIBUTE2 ATTRIBUTE2
, PS.ATTRIBUTE3 ATTRIBUTE3
, PS.ATTRIBUTE4 ATTRIBUTE4
, PS.ATTRIBUTE5 ATTRIBUTE5
, PS.ATTRIBUTE6 ATTRIBUTE6
, PS.ATTRIBUTE7 ATTRIBUTE7
, PS.ATTRIBUTE8 ATTRIBUTE8
, PS.ATTRIBUTE9 ATTRIBUTE9
, PS.ATTRIBUTE10 ATTRIBUTE10
, PS.ATTRIBUTE11 ATTRIBUTE11
, PS.ATTRIBUTE12 ATTRIBUTE12
, PS.ATTRIBUTE13 ATTRIBUTE13
, PS.ATTRIBUTE14 ATTRIBUTE14
, PS.ATTRIBUTE15 ATTRIBUTE15
, PS.ATTRIBUTE16 ATTRIBUTE16
, PS.ATTRIBUTE17 ATTRIBUTE17
, PS.ATTRIBUTE18 ATTRIBUTE18
, PS.ATTRIBUTE19 ATTRIBUTE19
, PS.ATTRIBUTE20 ATTRIBUTE20
, PS.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, L.ADDRESS_EFFECTIVE_DATE ADDRESS_EFFECTIVE_DATE
, L.ADDRESS_EXPIRATION_DATE ADDRESS_EXPIRATION_DATE
, PS.MAILSTOP MAILSTOP
, PS.IDENTIFYING_ADDRESS_FLAG IDENTIFYING_ADDRESS_FLAG
, PS.LANGUAGE PARTY_SITE_LANGUAGE
, PS.STATUS STATUS
, PS.PARTY_SITE_NAME PARTY_SITE_NAME
, L.ADDRESS_KEY ADDRESS_KEY
, FT.ADDRESS_STYLE ADDRESS_STYLE
, L.VALIDATED_FLAG VALIDATED_FLAG
, L.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC
, L.PO_BOX_NUMBER PO_BOX_NUMBER
, L.HOUSE_NUMBER HOUSE_NUMBER
, L.STREET_SUFFIX STREET_SUFFIX
, L.STREET STREET
, L.STREET_NUMBER STREET_NUMBER
, L.FLOOR FLOOR
, L.SUITE SUITE
, L.POSTAL_PLUS4_CODE POSTAL_PLUS4_CODE
, L.POSITION POSITION
, L.LOCATION_DIRECTIONS LOCATION_DIRECTIONS
, L.CLLI_CODE CLLI_CODE
, L.LANGUAGE LOCATION_LANGUAGE
, L.SHORT_DESCRIPTION SHORT_DESCRIPTION
, L.DESCRIPTION DESCRIPTION
, L.CONTENT_SOURCE_TYPE LOCATION_CONTENT_SOURCE_TYPE
, IGSPS.START_DATE START_DT
, IGSPS.END_DATE END_DT
, TO_DATE(NULL) DATE_LAST_VERIFIED
, PS.IDENTIFYING_ADDRESS_FLAG CORRESPONDENCE
, L.ADDRESS1 ADDR_LINE_1
, L.ADDRESS2 ADDR_LINE_2
, L.ADDRESS3 ADDR_LINE_3
, L.ADDRESS4 ADDR_LINE_4
, L.CITY CITY
, L.STATE STATE
, L.PROVINCE PROVINCE
, L.COUNTY COUNTY
, L.COUNTRY COUNTRY_CD
, FT.TERRITORY_SHORT_NAME COUNTRY
, L.POSTAL_CODE POSTAL_CODE
, L.DELIVERY_POINT_CODE DELIVERY_POINT_CODE
, NULL OTHER_DETAILS_1
, NULL OTHER_DETAILS_2
, NULL OTHER_DETAILS_3
, PS.CREATED_BY CREATED_BY
, PS.CREATION_DATE CREATION_DATE
, PS.LAST_UPDATED_BY LAST_UPDATED_BY
, PS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.REQUEST_ID REQUEST_ID
, PS.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PS.PROGRAM_ID PROGRAM_ID
, PS.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, L.OBJECT_VERSION_NUMBER LOCATION_OVN
, PS.OBJECT_VERSION_NUMBER PARTY_SITE_OVN
FROM HZ_PARTIES P
, HZ_PARTY_SITES PS
, HZ_LOCATIONS L
, IGS_PE_HZ_PTY_SITES IGSPS
, FND_TERRITORIES_VL FT
WHERE P.PARTY_ID = PS.PARTY_ID
AND PS.LOCATION_ID = L.LOCATION_ID
AND PS.PARTY_SITE_ID = IGSPS.PARTY_SITE_ID (+)
AND FT.TERRITORY_CODE = L.COUNTRY