Queries to drive Customer Relation Ship , Role , Account Info in Oracle TCA(Part-1).

Some Interesting TCA Queries

Query #1 
select a.object_id
from
hz_relationships a, hz_cust_accounts  b
where  a.party_id = l_party_id
and a.object_id = b.party_id
and b.cust_account_id = p_cust_account_id
and a.status = 'A'
and (sysdate between nvl(a.start_date, sysdate)
and nvl(a.end_date, sysdate))
AND b.status = 'A'
AND (sysdate BETWEEN NVL(b.account_activation_date, sysdate) AND
                     NVL(b.account_termination_date, sysdate));

Query #2
  select a.cust_account_id
    from hz_cust_accounts a, hz_cust_account_roles b
    where a.cust_account_id = b.cust_account_id
    and b.party_id = p_party_id
    and b.role_type = 'ACCOUNT_USER'
    AND b.status = 'A'
    AND a.status = 'A'
    AND (sysdate BETWEEN NVL(a.account_activation_date, sysdate) AND
                         NVL(a.account_termination_date, sysdate));