VIEW: APPS.AR_MERGE_ADDRESSES_V

Source

View Text - Preformatted

SELECT ADDR.ROWID ROW_ID ,ADDR.CUST_ACCT_SITE_ID ADDRESS_ID, ADDR.CUST_ACCOUNT_ID CUSTOMER_ID ,ADDR.STATUS STATUS , ADDR.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE , LOC.COUNTRY COUNTRY ,TERR.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME , TERR.ADDRESS_STYLE ADDRESS_STYLE ,LOC.ADDRESS1 ADDRESS1 ,LOC.ADDRESS2 ADDRESS2 , LOC.ADDRESS3 ADDRESS3 ,LOC.ADDRESS4 ADDRESS4 ,LOC.CITY CITY , LOC.COUNTY COUNTY ,LOC.STATE STATE ,LOC.PROVINCE PROVINCE , LOC.POSTAL_CODE POSTAL_CODE , ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE,LOC.ADDRESS1,LOC.ADDRESS2, LOC.ADDRESS3,LOC.ADDRESS4,LOC.CITY,LOC.COUNTY,LOC.STATE,LOC.PROVINCE, LOC.POSTAL_CODE,TERR.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS , SU.LOCATION LOCATION, SU.SITE_USE_ID SITE_USE_ID ,SU.SITE_USE_CODE SITE_USE_CODE, SU.PRIMARY_FLAG PRIMARY_FLAG ,LOOK.MEANING SITE_MEANING , LOC.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC, LOC.LOCATION_ID FROM FND_TERRITORIES_VL TERR,HZ_CUST_SITE_USES SU, HZ_CUST_ACCT_SITES ADDR,AR_LOOKUPS LOOK, HZ_PARTY_SITES PSITES, HZ_LOCATIONS LOC WHERE ADDR.PARTY_SITE_ID = PSITES.PARTY_SITE_ID AND PSITES.LOCATION_ID = LOC.LOCATION_ID AND ADDR.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID AND LOOK.LOOKUP_TYPE = 'SITE_USE_CODE' AND LOOK.LOOKUP_CODE = SU.SITE_USE_CODE AND SU.SITE_USE_CODE IN ('BILL_TO','SHIP_TO','MARKET') AND LOC.COUNTRY = TERR.TERRITORY_CODE (+)

View Text - HTML Formatted

SELECT ADDR.ROWID ROW_ID
, ADDR.CUST_ACCT_SITE_ID ADDRESS_ID
, ADDR.CUST_ACCOUNT_ID CUSTOMER_ID
, ADDR.STATUS STATUS
, ADDR.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, LOC.COUNTRY COUNTRY
, TERR.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, TERR.ADDRESS_STYLE ADDRESS_STYLE
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.COUNTY COUNTY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.POSTAL_CODE POSTAL_CODE
, ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
, LOC.ADDRESS1
, LOC.ADDRESS2
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, LOC.POSTAL_CODE
, TERR.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS
, SU.LOCATION LOCATION
, SU.SITE_USE_ID SITE_USE_ID
, SU.SITE_USE_CODE SITE_USE_CODE
, SU.PRIMARY_FLAG PRIMARY_FLAG
, LOOK.MEANING SITE_MEANING
, LOC.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC
, LOC.LOCATION_ID
FROM FND_TERRITORIES_VL TERR
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES ADDR
, AR_LOOKUPS LOOK
, HZ_PARTY_SITES PSITES
, HZ_LOCATIONS LOC
WHERE ADDR.PARTY_SITE_ID = PSITES.PARTY_SITE_ID
AND PSITES.LOCATION_ID = LOC.LOCATION_ID
AND ADDR.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND LOOK.LOOKUP_TYPE = 'SITE_USE_CODE'
AND LOOK.LOOKUP_CODE = SU.SITE_USE_CODE
AND SU.SITE_USE_CODE IN ('BILL_TO'
, 'SHIP_TO'
, 'MARKET')
AND LOC.COUNTRY = TERR.TERRITORY_CODE (+)