VIEW: APPS.HZ_DQM_CS_CPT_V

Source

View Text - Preformatted

SELECT ps.party_site_id dqparty_site_id, c.contact_point_type dqcontact_point_type, c.primary_by_purpose dqprimary_by_purpose, c.primary_flag dqprimary_flag, c.status dqstatus, c.contact_point_id dqcontact_point_id, DECODE(c.contact_point_type, 'EMAIL', c.email_address, 'PHONE', c.raw_phone_number, 'TLX' , c.telex_number, 'WEB' , c.url ) dqcontact_info FROM hz_party_sites ps, hz_contact_points c, hz_matched_cpts_gt g WHERE c.owner_table_name = 'HZ_PARTY_SITES' AND c.owner_table_id = ps.party_site_id AND c.owner_table_id = g.contact_point_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 PS.PARTY_SITE_ID DQPARTY_SITE_ID
, C.CONTACT_POINT_TYPE DQCONTACT_POINT_TYPE
, C.PRIMARY_BY_PURPOSE DQPRIMARY_BY_PURPOSE
, C.PRIMARY_FLAG DQPRIMARY_FLAG
, C.STATUS DQSTATUS
, C.CONTACT_POINT_ID DQCONTACT_POINT_ID
, DECODE(C.CONTACT_POINT_TYPE
, 'EMAIL'
, C.EMAIL_ADDRESS
, 'PHONE'
, C.RAW_PHONE_NUMBER
, 'TLX'
, C.TELEX_NUMBER
, 'WEB'
, C.URL ) DQCONTACT_INFO
FROM HZ_PARTY_SITES PS
, HZ_CONTACT_POINTS C
, HZ_MATCHED_CPTS_GT G
WHERE C.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
AND C.OWNER_TABLE_ID = PS.PARTY_SITE_ID
AND C.OWNER_TABLE_ID = G.CONTACT_POINT_ID(+)
AND G.SEARCH_CONTEXT_ID(+) = ARH_DQM_TREE_HELPER.CTXMAX ORDER BY DECODE(G.SCORE
, NULL
, -1
, G.SCORE) DESC