VIEW: APPS.HZ_DQM_PS_C_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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