Search from 700 + Posts

Jan 13, 2011

Query to get Contact Assocaited with Party ID and Account ID

SELECT party.party_type,party.party_name Contact_Name,
rel.relationship_code,rel.relationship_id,
--org_cont.*,cont_point.*,
--party.party_id,rel_party.party_id,rel.subject_id,rel.object_id, rel.party_id,
SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME ,
SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME ,
PARTY.CUSTOMER_KEY CONTACT_KEY ,
REL_PARTY.EMAIL_ADDRESS ,
-- PARTY.PARTY_ID ,
ORG_CONT.ORG_CONTACT_ID ,
CONT_POINT.CONTACT_POINT_ID ,
ORG_CONT.CONTACT_NUMBER
FROM HZ_CONTACT_POINTS CONT_POINT,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_PARTIES REL_PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT ,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL_PARTY.PARTY_ID = REL.PARTY_ID
AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG = 'Y'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and ROLE_ACCT.cust_account_id = &Enter_Account_id
and rel.object_id = &Enter_Party_id
AND rel.subject_table_name ='HZ_PARTIES'
AND rel.object_table_name ='HZ_PARTIES'
and party.status ='A'
and rel_party.status ='A'
and rel.status ='A'

11 comments:

  1. Thanks a lot for this query..

    ReplyDelete
  2. Thanks a lot for this query.

    ReplyDelete
  3. Any time udhay. Thanks for good words about the post.


    Regards
    eoracleapps team

    ReplyDelete
  4. hi. . thanks for help. . but for particulat party id. . if we print party.orig_system_ref. . its giving two different values which are not related to that party_id??
    can u help me in this..

    ReplyDelete
  5. ORIG_SYSTEM_REFERENCE refer the value from the Third Party/Legacy System .

    ReplyDelete
  6. Thanks a lot for this query. it help me in R12

    ReplyDelete
  7. its nice to see this query, when am in need, Thanks a lot for this useful query, looking forward for more help.

    Raju.

    ReplyDelete