Search from 700 + Posts

Jan 24, 2011

Queries to Get Customer Relationship and Contact in Oracle TCA(Part-2)

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

1 comment:

  1. Well as usual , these helps to save lot of time...and i am able to focus more on my Mobile stuff