Query To Fetch Customer email address

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=<>;