SELECT hp.person_first_name
' '
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;
and 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;
and hca.account_number=<>;