Some More Interesting TCA Queries .
Query #1
SELECT cust_account_role_id, status
FROM hz_cust_account_roles
WHERE party_id = p_party_id
AND cust_account_id = p_cust_account_id
AND role_type ='CONTACT'
AND cust_acct_site_id is null
and status = 'A';
Query #2
SELECT par.party_id
FROM hz_relationships par,
hz_org_contacts org ,
hz_cust_accounts acc
WHERE org.party_relationship_id = par.relationship_id
AND org.org_contact_id = p_org_contact_id
and par.status = 'A'
and (sysdate between nvl(par.start_date, sysdate) and nvl(par.end_date, sysdate))
AND acc.cust_account_id = p_cust_account_id
AND par.object_id = acc.party_id
AND acc.status = 'A'
AND (sysdate BETWEEN NVL(acc.account_activation_date, sysdate) AND
NVL(acc.account_termination_date, sysdate));
