VIEW: APPS.WSH_CUSTOMER_LOCATIONS_V

Source

View Text - Preformatted

SELECT whzl.wsh_location_id wsh_location_id, whzl.ui_location_code ui_location_code, whzl.location_code || ':' || nvl(whzl.city,substrb(whzl.address1,1,60)) ui_short_location_code, whzl.address1 Address1, whzl.address2 Address2, whzl.address3 Address3, whzl.address4 Address4, whzl.city City, whzl.postal_code Postal_Code, whzl.country Country, whzl.state State, whzl.province Province, whzl.county County, whzl.source_location_id source_location_id, whzl.inactive_date, Hp.duns_number, Hps.party_site_number, hca.CUST_ACCOUNT_ID Customer_ID, substrb(hp.party_name,1,50) customer_name, hca.account_number customer_number, hca.status customer_status, hcas.cust_acct_site_id Address_ID, hcas.tp_header_id TP_Header_ID, hcas.orig_system_reference Orig_System_Reference, hcas.ece_tp_location_code TP_Location_Code_Ext, hcas.org_id, hcas.status cust_acct_site_status, hcsu.site_use_id Site_Use_ID, hcsu.site_use_code site_use_code, hcsu.location Location, hcsu.gsa_indicator, hcsu.status site_use_status, hcsu.primary_flag FROM wsh_locations whzl, hz_parties HP, hz_cust_accounts HCA, HZ_CUST_SITE_USES_ALL hcsu, HZ_CUST_ACCT_SITES_ALL HCAS, HZ_PARTY_SITES HPS WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID AND HCA.PARTY_ID = HP.PARTY_ID And HP.party_id = hps.party_id AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID AND nvl( HCSU.org_id,-999) = nvl( HCAS.org_id,-999) And hps.location_id = whzl.source_location_id AND whzl.location_source_code ='HZ'

View Text - HTML Formatted

SELECT WHZL.WSH_LOCATION_ID WSH_LOCATION_ID
, WHZL.UI_LOCATION_CODE UI_LOCATION_CODE
, WHZL.LOCATION_CODE || ':' || NVL(WHZL.CITY
, SUBSTRB(WHZL.ADDRESS1
, 1
, 60)) UI_SHORT_LOCATION_CODE
, WHZL.ADDRESS1 ADDRESS1
, WHZL.ADDRESS2 ADDRESS2
, WHZL.ADDRESS3 ADDRESS3
, WHZL.ADDRESS4 ADDRESS4
, WHZL.CITY CITY
, WHZL.POSTAL_CODE POSTAL_CODE
, WHZL.COUNTRY COUNTRY
, WHZL.STATE STATE
, WHZL.PROVINCE PROVINCE
, WHZL.COUNTY COUNTY
, WHZL.SOURCE_LOCATION_ID SOURCE_LOCATION_ID
, WHZL.INACTIVE_DATE
, HP.DUNS_NUMBER
, HPS.PARTY_SITE_NUMBER
, HCA.CUST_ACCOUNT_ID CUSTOMER_ID
, SUBSTRB(HP.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, HCA.STATUS CUSTOMER_STATUS
, HCAS.CUST_ACCT_SITE_ID ADDRESS_ID
, HCAS.TP_HEADER_ID TP_HEADER_ID
, HCAS.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, HCAS.ECE_TP_LOCATION_CODE TP_LOCATION_CODE_EXT
, HCAS.ORG_ID
, HCAS.STATUS CUST_ACCT_SITE_STATUS
, HCSU.SITE_USE_ID SITE_USE_ID
, HCSU.SITE_USE_CODE SITE_USE_CODE
, HCSU.LOCATION LOCATION
, HCSU.GSA_INDICATOR
, HCSU.STATUS SITE_USE_STATUS
, HCSU.PRIMARY_FLAG
FROM WSH_LOCATIONS WHZL
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_PARTY_SITES HPS
WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND NVL( HCSU.ORG_ID
, -999) = NVL( HCAS.ORG_ID
, -999)
AND HPS.LOCATION_ID = WHZL.SOURCE_LOCATION_ID
AND WHZL.LOCATION_SOURCE_CODE ='HZ'