EBS QUERY TO GET THE PARTY REALTIONS AND ER DIAGRAM RELATED TO IT

ER DIAGRAM:

QUERY FOR REFERENCE:
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;