Customer details

SELECT
cust.ACCOUNT_NUMBER customer_number,
party.party_name BILL_CUST_NAME
,cust.CUSTOMER_CLASS_CODE
, ship.location ||' '||loc.ADDRESS1||' '||loc.ADDRESS2||' '||loc.ADDRESS3||' '||loc.POSTAL_CODE ||' '||loc.CITY ||' '||NVL(loc.STATE , loc.PROVINCE) ||' '||loc.COUNTRY ship_address
FROM hz_cust_accounts cust, hz_cust_acct_sites_all acct, hz_cust_site_uses_all ship,
hz_party_sites party_site ,hz_locations loc,hz_parties party
WHERE cust.cust_account_id = acct.cust_account_id
AND acct.cust_acct_site_id = ship.cust_acct_site_id
AND acct.ORG_ID = ship.ORG_ID
AND ship.SITE_USE_CODE = 'SHIP_TO'
AND cust.status = 'A'
and loc.location_id = party_site.location_id
and acct.party_site_id = party_site.party_site_id
and cust.party_id = party.party_id;
/