VIEW: APPS.OTA_CUSTOMER_ADDRESSES_V

Source

View Text - Preformatted

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 /* ** Commented for bug 14072624 ** 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 )

View Text - HTML Formatted

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 /* ** COMMENTED FOR BUG 14072624 **
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 )