EBS Query - How to find First and Last name of the people listed in "Contact Information" from an Account Number

Question

In the oracle apps (version 11) web page, there is "Customer" then "Customer Account" then you can choose to open "Account Contact" screen to see the details of the "Contact Information". I see names and "Contact Number" there. I can could find the "Contact Number" with the following query but I cannot figure out how to access the Prefix, First name, Middle name, Last name, Suffix and Job Title of the "Contact Information"

I am trying to write a little SQL statement to pull the First names and last names of the "Contact Information" that is associated to an Account by specifying single HZ_ACCOUNT_CONTACT.ACCOUNT_NUMBER value. So input is a single long value and the return is List of First + Last names of the "Contacts"

My half-baked query (that is potentially totally wrong) is as below:

SELECT
  CA1.CUST_ACCOUNT_ID,
  CA1.ACCOUNT_NUMBER, ----------------------------- The Input
  P1.PARTY_ID   AS P1_PARTY_ID,
  P1.PARTY_NAME AS P1_PARTY_NAME,
  OC2.CONTACT_NUMBER,
  R2.CUST_ACCOUNT_ROLE_ID
FROM
  HZ_CUST_ACCOUNTS           CA1
  JOIN HZ_PARTIES            P1  ON P1.PARTY_ID  = CA1.PARTY_ID
  JOIN HZ_RELATIONSHIPS      R   ON R.SUBJECT_ID = P1.PARTY_ID
  JOIN HZ_PARTIES            P2  ON P2.PARTY_ID  = R.PARTY_ID 
  JOIN HZ_ORG_CONTACTS       OC2 ON OC2.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
  JOIN HZ_CUST_ACCOUNT_ROLES R2  ON R2.PARTY_ID = P2.PARTY_ID
  -- What else do I need to join to get to "contact infomation"?
WHERE
  R.RELATIONSHIP_CODE = 'CONTACT'
  AND R.OBJECT_TABLE_NAME = 'HZ_PARTIES'
  AND P2.PARTY_TYPE = 'PARTY_RELATIONSHIP'
  AND CA1.ACCOUNT_NUMBER = '577042' ; -------------- The Input

In my broken/incomplete query, 577042 is the account number

How do I complete the query to get the First and the last name of the contact?

Thanks for the help!

Answer 1

The approach with Oracle's trading community architecture (tca) is to drive this query with the ar.hz_relationships table. Here is an ERD:

You have to identify the subject and object of this relationship (organization and a person) as seen below.

All the nice contact attributes are inhz_parties alias, cont, in the query below:

SELECT cacct.account_number cust_account,
      cust.party_name customer_name,
      crole.role_type contact_role_type,
      cont.person_last_name contact_last_name,
      cont.person_first_name contact_first_name,
      cont.person_name_suffix contact_suffix,
      cont.party_name contact_name,
      rparty.party_name relationship_party_name
    FROM ar.hz_relationships rel
    JOIN ar.hz_parties cust
    ON rel.subject_id = cust.party_id
    JOIN ar.hz_parties cont
    ON rel.object_id = cont.party_id
    JOIN ar.hz_parties rparty
    ON rel.party_id = rparty.party_id
    JOIN ar.hz_cust_accounts cacct
    ON rel.subject_id = cacct.party_id
    JOIN ar.hz_cust_account_roles crole
    ON cacct.cust_account_id    = crole.cust_account_id
    AND rel.party_id            = crole.party_id
    WHERE rel.relationship_code = 'CONTACT'
    AND rel.object_table_name   = 'HZ_PARTIES'
    AND rel.subject_table_name  = 'HZ_PARTIES'
    AND rel.subject_type        = 'ORGANIZATION'
    AND rel.object_type         = 'PERSON'
    AND rel.directional_flag    = 'B'
    AND rel.relationship_type   = 'CONTACT'
    AND crole.role_type         = 'CONTACT'
    AND rparty.party_type       = 'PARTY_RELATIONSHIP'
    ORDER BY 1,4,5

This does not address statuses of the contacts/relationship.