Order Management - How to get Customer' Ship-to Address contacts (Customer Site Contacts)
I am trying write a SQL to get Customer' Ship-to Address contacts (Customer Site Contacts) from an order.
This is SQL trying to get the ship-to address contact number; however, it shows duplicated data. I cannot find the relationship between hz_relationships, hz_cust_accounts, and hz_cust_acct_sites_all.
SELECT
hca.account_number cust_account_number,
obj.party_name customer_name,
sub.party_name contact_name,
hps.party_site_number,
hcp.contact_point_type,
hcp.phone_number,
hcp.email_address
FROM
hz_cust_accounts hca
JOIN hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id