SELECT A.CUST_ACCOUNT_ID DQCUST_ACCOUNT_ID , A.CUST_ACCOUNT_ROLE_ID DQCUST_ACCOUNT_ROLE_ID , A.CUST_ACCT_SITE_ID DQCUST_ACCT_SITE_ID , C.RELATIONSHIP_ID DQRELATIONSHIP_ID , E.ORG_CONTACT_ID DQORG_CONTACT_ID , D.PARTY_NAME DQPARTY_NAME , H1.MEANING DQTITLE , H.MEANING DQJOB_TITLE , E.CONTACT_NUMBER DQCONTACT_NUMBER , A.STATUS DQSTATUS , NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('REGISTRY_STATUS' , A.STATUS) , A.STATUS) DQSTATUSM FROM HZ_CUST_ACCOUNT_ROLES A , HZ_PARTIES B , HZ_RELATIONSHIPS C , HZ_PARTIES D , HZ_ORG_CONTACTS E , AR_LOOKUPS H , AR_LOOKUPS H1 , HZ_MATCHED_CONTACTS_GT G WHERE A.CUST_ACCT_SITE_ID IS NOT NULL AND A.PARTY_ID = B.PARTY_ID AND NVL(A.STATUS , 'A') NOT IN ('M') AND B.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND B.PARTY_ID = C.PARTY_ID AND C.RELATIONSHIP_ID = E.PARTY_RELATIONSHIP_ID AND C.DIRECTIONAL_FLAG = 'F' AND C.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND C.SUBJECT_ID = D.PARTY_ID AND -A.CUST_ACCOUNT_ROLE_ID = G.ORG_CONTACT_ID(+) AND G.SEARCH_CONTEXT_ID(+) = ARH_DQM_TREE_HELPER.CTXMAX AND H.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND H.LOOKUP_CODE(+) = E.JOB_TITLE_CODE AND H1.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND H1.LOOKUP_CODE(+) = E.TITLE ORDER BY DECODE(G.SCORE , NULL , -1 , -G.SCORE) DESC