To get all the customer details like phone no,address,etc... corresponding to a address type

SELECT hzp.party_id


,hzp.person_first_name

,hzp.person_middle_name

,hzp.salutation

,hzp.person_last_name

,hzp.person_pre_name_adjunct

,hzp.attribute1 privacy_consent_indicator

,hzp.attribute7 last_update_date

,ship_loc.city

,ship_loc.province

,ship_loc.country

,ship_loc.postal_code

,ship_loc.address1

,ship_loc.address2

,ship_loc.address3

,ship_su.site_use_code

,DECODE (ship_su.site_use_code, 'BILL_TO', 'Y', 'SHIP_TO', 'N') address_use_type

,cp.phone_number

,cp.phone_line_type

FROM hz_parties hzp

,hz_cust_site_uses_all ship_su

,hz_party_sites ship_ps

,hz_locations ship_loc

,hz_cust_acct_sites_all ship_cas

,hz_contact_points cp

WHERE hzp.party_id = ship_ps.party_id

AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id

AND ship_cas.party_site_id = ship_ps.party_site_id

AND ship_loc.location_id = ship_ps.location_id

-- AND ship_su.SITE_USE_CODE in ('ACK','BOL','DELIVER_TO','INV','SOLD_TO','STMTS')

AND cp.owner_table_name(+) = 'HZ_PARTIES'

AND cp.owner_table_id(+) = ship_ps.party_id

AND cp.status(+) = 'A'

AND cp.primary_flag(+) = 'Y'

AND cp.contact_point_type(+) = 'PHONE'
 
 
 
Output:
PARTY_ID PERSON_FIRST_NAME PERSON_MIDDLE_NAME SALUTATION PERSON_LAST_NAME PERSON_PRE_NAME_ADJUNCT PRIVACY_CONSENT_INDICATOR LAST_UPDATE_DATE CITY PROVINCE COUNTRY POSTAL_CODE ADDRESS1 ADDRESS2 ADDRESS3 SITE_USE_CODE ADDRESS_USE_TYPE PHONE_NUMBER PHONE_LINE_TYPE


70015 H SZALAI MRS. Y BSTFS12 CONCORD ON CA L4K1G8 55 BAY HILL DR SOLD_TO 6694197 HOME:

493570 G HEBB Y DAY,JOSEPH DARTMOUTH NS CA B3B 1K8 5608-10 MORRIS DR DELIVER_TO 4232233 HOME: