View: OTA_CUSTOMER_ADDRESSES_V

Product:	OTA - Learning Management
Description:	View to list all Addresses for a Customer.
Implementation/DBA Data:	APPS.OTA_CUSTOMER_ADDRESSES_V

View Text

SELECT ACCT_SITE.ROWID
, ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID
, ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
, LOC.COUNTRY COUNTRY
, LOC.CITY CITY
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.COUNTY COUNTY
, ACCT_SITE.STATUS STATUS
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.ADDRESS1||DECODE(LOC.ADDRESS1
, NULL
, ''
, '
, ')|| LOC.ADDRESS2|| DECODE(LOC.ADDRESS2
, NULL
, ''
, '
, ')|| LOC.ADDRESS3|| DECODE(LOC.ADDRESS3
, NULL
, ''
, '
, ')|| LOC.ADDRESS4|| DECODE(LOC.ADDRESS4
, NULL
, ''
, '
, ')|| LOC.CITY|| DECODE(LOC.CITY
, NULL
, ''
, '
, ')|| LOC.STATE||DECODE(LOC.STATE
, NULL
, ''
, '
, ')|| LOC.PROVINCE||DECODE(LOC.PROVINCE
, NULL
, ''
, '
, ')|| LOC.COUNTY|| DECODE(LOC.COUNTY
, NULL
, ''
, '
, ')|| LOC.POSTAL_CODE|| DECODE(LOC.POSTAL_CODE
, NULL
, ''
, '
, ')|| LOC.COUNTRY
FROM HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND NVL(ACCT_SITE.ORG_ID
, NVL( TO_NUMBER( DECODE( SUBSTRB( USERENV( 'CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(ACCT_SITE.ORG_ID (+)
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)))
, 1 ) = DECODE(FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID')
, NULL
, (NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99))
, 1 )

Columns