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'
Thanks a lot for this query..
ReplyDeleteThanks a lot for this query.
ReplyDeleteAny time udhay. Thanks for good words about the post.
ReplyDeleteRegards
eoracleapps team
Thank You
ReplyDeletehi. . 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??
ReplyDeletecan u help me in this..
ORIG_SYSTEM_REFERENCE refer the value from the Third Party/Legacy System .
ReplyDeleteThanks a lot for this query. it help me in R12
ReplyDeleteYou are very welcome
ReplyDeleteThank you for this query.
ReplyDeleteits nice to see this query, when am in need, Thanks a lot for this useful query, looking forward for more help.
ReplyDeleteRaju.
Thanks a lot for your query..
ReplyDeleteSG