View: HZ_MERGE_ADDRESSES_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.HZ_MERGE_ADDRESSES_V

View Text

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
, NVL(SU.SITE_USE_ID
, -99) SITE_USE_ID
, NVL(SU.SITE_USE_CODE
, 'NONE') SITE_USE_CODE
, NVL(SU.PRIMARY_FLAG
, 'N') PRIMARY_FLAG
, NVL(LOOK.MEANING
, SU.SITE_USE_CODE) SITE_MEANING
, LOC.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC
, LOC.LOCATION_ID
, PSITES.PARTY_SITE_NUMBER
, ADDR.ORG_ID
, HR.NAME
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
, HR_OPERATING_UNITS HR
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 SU.SITE_USE_CODE = LOOK.LOOKUP_CODE (+)
AND LOC.COUNTRY = TERR.TERRITORY_CODE (+)
AND ADDR.ORG_ID = HR.ORGANIZATION_ID

Columns