Query to find Customer contacts and their roles :

SELECT   oc.JOB_TITLE,
           oc.PARTY_SITE_ID,
           RELATIONSHIP_ID,
           RELATIONSHIP_TYPE,
           hp.PARTY_NAME OBJECT_NAME,                               -- Contact
           OBJECT_ID,
           OBJECT_TYPE,
           OBJECT_TABLE_NAME,
           hr.PARTY_ID,
           RELATIONSHIP_CODE,
           SUBJECT_ID,
           SUBJECT_TYPE,
           SUBJECT_TABLE_NAME,
           oc.TITLE,
           oc.MAIL_STOP,
           oc.CONTACT_KEY,
           ocr.ROLE_TYPE,
           DIRECTIONAL_FLAG
    FROM   HZ_ORG_CONTACT_ROLES ocr,
           HZ_ORG_CONTACTS oc,
           HZ_PARTIES hp,
           HZ_RELATIONSHIPS hr
   WHERE       hr.subject_id = 1004                 --Party id of the customer
           AND hr.object_id = hp.party_id
           AND hr.RELATIONSHIP_ID = oc.PARTY_RELATIONSHIP_ID
           AND oc.ORG_CONTACT_ID = ocr.ORG_CONTACT_ID(+)
ORDER BY   OBJECT_NAME
/




Query to find communication channels for a customer :

SELECT   CONTACT_POINT_ID,
         CONTACT_POINT_TYPE,
         EMAIL_ADDRESS,
         PHONE_NUMBER,
         URL,
         CONTACTS,
         STATUS,
         OWNER_TABLE_NAME,
         OWNER_TABLE_ID,
         PRIMARY_FLAG,
         ORIG_SYSTEM_REFERENCE
  FROM   HZ_CONTACT_POINTS
 WHERE   OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = 1004


Query to find Organization type Party info :


SELECT   hp.PARTY_ID,
           hp.PARTY_NUMBER,
           hp.PARTY_NAME,
           hca.ACCOUNT_NUMBER,
           hca.CUST_ACCOUNT_ID,
           hop.ORGANIZATION_PROFILE_ID,
           hop.EFFECTIVE_START_DATE,
           hop.EFFECTIVE_END_DATE,
           hop.ORGANIZATION_NAME,
           hop.DUNS_NUMBER,
           hop.ENQUIRY_DUNS,
           hop.CEO_NAME,
           hop.CEO_TITLE,
           hop.PRINCIPAL_NAME,
           hop.PRINCIPAL_TITLE,
           hop.LEGAL_STATUS,
           hop.CONTROL_YR,
           hop.EMPLOYEES_TOTAL,
           hop.HQ_BRANCH_IND,
           hop.BRANCH_FLAG,
           hop.OOB_IND,
           hop.LINE_OF_BUSINESS
    FROM   HZ_ORGANIZATION_PROFILES hop, HZ_CUST_ACCOUNTS hca, HZ_PARTIES hp
   WHERE       hp.party_name = 'Hilman and Associates'
           AND hp.PARTY_ID = hca.PARTY_ID
           AND hp.PARTY_ID = hop.PARTY_ID
           AND SYSDATE BETWEEN hop.EFFECTIVE_START_DATE
                           AND  NVL (hop.EFFECTIVE_END_DATE, SYSDATE + 1)
ORDER BY   hca.ACCOUNT_NUMBER