VIEW: APPS.HZ_DQM_CS_C_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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