SELECT A.ORG_CONTACT_ID DQORG_CONTACT_ID , A.PARTY_SITE_ID DQPARTY_SITE_ID , H1.MEANING DQTITLE , H.MEANING DQJOB_TITLE , A.STATUS DQSTATUS , NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('REGISTRY_STATUS' , A.STATUS) , A.STATUS) DQSTATUSM , A.CONTACT_NUMBER DQCONTACT_NUMBER , B.RELATIONSHIP_CODE DQRELATIONSHIP_CODE , B.RELATIONSHIP_ID DQRELATIONSHIP_ID , P.PARTY_NAME DQPARTY_NAME , P.PARTY_ID DQPARTY_ID FROM HZ_ORG_CONTACTS A , HZ_RELATIONSHIPS B , HZ_PARTIES P , AR_LOOKUPS H , AR_LOOKUPS H1 , HZ_MATCHED_CONTACTS_GT G WHERE A.PARTY_SITE_ID IS NOT NULL AND A.PARTY_RELATIONSHIP_ID = B.RELATIONSHIP_ID AND NVL(A.STATUS , 'A') NOT IN ('M') AND B.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND B.DIRECTIONAL_FLAG = 'F' AND B.SUBJECT_ID = P.PARTY_ID AND H.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND H.LOOKUP_CODE(+) = A.JOB_TITLE_CODE AND H1.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND H1.LOOKUP_CODE(+) = A.TITLE AND A.ORG_CONTACT_ID = G.ORG_CONTACT_ID(+) AND G.SEARCH_CONTEXT_ID(+) = ARH_DQM_TREE_HELPER.CTXMAX ORDER BY DECODE(G.SCORE , NULL , -1 , G.SCORE) DESC