Customer Contact Info from Header Level.

Customer Contact Info from Header Level.

select (select party_name from hz_parties where party_id=hca.party_id) customer_name,
hca.account_number,
hp.person_last_name
, hoc.contact_number,
 hp1.email_address
FROM hz_cust_account_roles hcar
, hz_parties hp
, hz_relationships hr
, hz_org_contacts hoc
, hz_parties hp1
, hz_cust_accounts hca
WHERE hcar.party_id = hr.party_id
AND hcar.role_type = 'CONTACT'
AND hoc.party_relationship_id= hr.relationship_id
AND hr.subject_id = hp.party_id
AND hr.party_id = hp1.party_id
AND hr.subject_table_name = 'HZ_PARTIES'
AND hr.object_table_name = 'HZ_PARTIES'
AND hcar.cust_account_id = hca.cust_account_id
AND hca.party_id = hr.object_id
-- AND hcar.cust_account_role_id = v_ship_contact_id;
order by 2