[Oracle]R12:客戶聯絡人

SELECT HP_PER.PARTY_NAME CONTACT_NAME
  FROM HZ_CUST_ACCOUNT_ROLES  ROL,
       HZ_PARTIES             HP_REL,
       HZ_RELATIONSHIPS       REL,
       HZ_PARTIES             HP_PER,
       HZ_CONTACT_POINTS      HCP_PH,
       HZ_CUST_ACCOUNT_ROLES  HCAR,
       HZ_ROLE_RESPONSIBILITY HRR
 WHERE ROL.PARTY_ID = HP_REL.PARTY_ID
   AND HP_REL.PARTY_ID = REL.PARTY_ID
   AND REL.OBJECT_TYPE = 'PERSON'
   AND REL.RELATIONSHIP_CODE = 'CONTACT'
   AND REL.OBJECT_ID = HP_PER.PARTY_ID
   AND HCAR.CUST_ACCOUNT_ROLE_ID = HRR.CUST_ACCOUNT_ROLE_ID
   AND HP_REL.PARTY_ID = HCAR.PARTY_ID
   AND ROL.CUST_ACCOUNT_ID = HCAR.CUST_ACCOUNT_ID
   AND ROL.CUST_ACCOUNT_ID = 5090837 --P_CUST_ACCOUNT_ID
   AND ROL.CUST_ACCT_SITE_ID IS NULL
   AND HCAR.CUST_ACCT_SITE_ID IS NULL
   AND REL.STATUS = 'A'
   AND ROL.STATUS = 'A'
   AND HCP_PH.STATUS = 'A'
      --AND HCP_PH.PRIMARY_FLAG = 'Y'
   AND HCP_PH.CONTACT_POINT_TYPE = 'EMAIL'
   AND HP_REL.PARTY_ID = HCP_PH.OWNER_TABLE_ID
   AND HCP_PH.OWNER_TABLE_NAME = 'HZ_PARTIES'
   AND HRR.RESPONSIBILITY_TYPE = 'STMTS'
 ORDER BY HCP_PH.PRIMARY_FLAG;