VIEW: APPS.HZ_DQM_ORG_CONTACT_INFO_V

Source

View Text - Preformatted

SELECT DISTINCT b.org_contact_id dqorg_contact_id, b.title dqtitle, b.job_title dqjob_title, e.object_id dqpty_id, f.party_id dqparty_id, f.party_name dqtitle_name, g.role_type dqrole_type, c.phone_line_type dqcontact_point_purpose, DECODE(c.contact_point_type,'PHONE',h1.meaning,h.meaning) dqmeaning, c.contact_point_type dqcontact_point_type, c.status dqstatus, NVL(arpt_sql_func_util.get_lookup_meaning ('REGISTRY_STATUS',c.status),c.status) dqstatusm, c.primary_flag dqprimary_flag, c.primary_by_purpose dqprimary_by_purpose, c.contact_point_id dqcontact_point_id, DECODE(c.contact_point_type, 'EMAIL', c.email_address, 'PHONE', DECODE(c.phone_area_code,NULL,NULL,c.phone_area_code||'-')||c.phone_number, 'TLX' , c.telex_number, 'WEB' , c.url ) dqcontact_info FROM hz_org_contacts b, hz_contact_points c, hz_parties d, hz_relationships e, hz_parties f, hz_cust_account_roles g, ar_lookups h, ar_lookups h1 WHERE b.party_relationship_id = e.relationship_id AND e.directional_flag = 'F' AND e.subject_table_name = 'HZ_PARTIES' AND e.subject_id = f.party_id AND e.party_id = c.owner_table_id(+) AND c.owner_table_name(+) = 'HZ_PARTIES' AND e.party_id = d.party_id AND d.party_type = 'PARTY_RELATIONSHIP' AND d.party_id = g.party_id(+) AND h.lookup_type(+) = 'COMMUNICATION_TYPE' AND h.lookup_code(+) = c.contact_point_type AND NVL(c.status,'A') NOT IN ('M') AND h1.lookup_type(+) = 'PHONE_LINE_TYPE' AND h1.lookup_code(+) = c.phone_line_type

View Text - HTML Formatted

SELECT DISTINCT B.ORG_CONTACT_ID DQORG_CONTACT_ID
, B.TITLE DQTITLE
, B.JOB_TITLE DQJOB_TITLE
, E.OBJECT_ID DQPTY_ID
, F.PARTY_ID DQPARTY_ID
, F.PARTY_NAME DQTITLE_NAME
, G.ROLE_TYPE DQROLE_TYPE
, C.PHONE_LINE_TYPE DQCONTACT_POINT_PURPOSE
, DECODE(C.CONTACT_POINT_TYPE
, 'PHONE'
, H1.MEANING
, H.MEANING) DQMEANING
, C.CONTACT_POINT_TYPE DQCONTACT_POINT_TYPE
, C.STATUS DQSTATUS
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('REGISTRY_STATUS'
, C.STATUS)
, C.STATUS) DQSTATUSM
, C.PRIMARY_FLAG DQPRIMARY_FLAG
, C.PRIMARY_BY_PURPOSE DQPRIMARY_BY_PURPOSE
, C.CONTACT_POINT_ID DQCONTACT_POINT_ID
, DECODE(C.CONTACT_POINT_TYPE
, 'EMAIL'
, C.EMAIL_ADDRESS
, 'PHONE'
, DECODE(C.PHONE_AREA_CODE
, NULL
, NULL
, C.PHONE_AREA_CODE||'-')||C.PHONE_NUMBER
, 'TLX'
, C.TELEX_NUMBER
, 'WEB'
, C.URL ) DQCONTACT_INFO
FROM HZ_ORG_CONTACTS B
, HZ_CONTACT_POINTS C
, HZ_PARTIES D
, HZ_RELATIONSHIPS E
, HZ_PARTIES F
, HZ_CUST_ACCOUNT_ROLES G
, AR_LOOKUPS H
, AR_LOOKUPS H1
WHERE B.PARTY_RELATIONSHIP_ID = E.RELATIONSHIP_ID
AND E.DIRECTIONAL_FLAG = 'F'
AND E.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND E.SUBJECT_ID = F.PARTY_ID
AND E.PARTY_ID = C.OWNER_TABLE_ID(+)
AND C.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND E.PARTY_ID = D.PARTY_ID
AND D.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND D.PARTY_ID = G.PARTY_ID(+)
AND H.LOOKUP_TYPE(+) = 'COMMUNICATION_TYPE'
AND H.LOOKUP_CODE(+) = C.CONTACT_POINT_TYPE
AND NVL(C.STATUS
, 'A') NOT IN ('M')
AND H1.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE'
AND H1.LOOKUP_CODE(+) = C.PHONE_LINE_TYPE