title: EBS Query - How to find First and Last name of the people listed in "Contact Information" from an Account Number
body: <p>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"</p>
  
  <p>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"</p>
  
  <p>My half-baked query (that is potentially totally wrong) is as below:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In my broken/incomplete query, 577042 is the account number</p>
  
  <p>How do I complete the query to get the First and the last name of the contact?</p>
  
  <p>Thanks for the help!</p>