VIEW: APPS.HZ_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, 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

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
, 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