Query to get Customer Role in TCA
select a.cust_account_role_id,a.*
from
hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
where
a.role_type = 'CONTACT'
and a.party_id = p_party_id
and a.cust_account_id = p_cust_account_id
and a.cust_acct_site_id = c.cust_acct_site_id
and a.cust_account_id = c.cust_account_id
and c.party_site_id = p_party_site_id
and a.cust_account_role_id = b.cust_account_role_id
and responsibility_type = p_acct_site_type;
