VIEW: APPS.OZF_SD_CONTACT_V

Source

View Text - Preformatted

SELECT acct_role.cust_account_role_id, party.person_last_name || decode(party.person_first_name, NULL, NULL, ', ' || party.person_first_name) || decode(arl.meaning, NULL, NULL, ' ' || arl.meaning) name, rel_party.email_address, rel_party.primary_phone_country_code || rel_party.primary_phone_area_code || rel_party.primary_phone_number phone_number FROM hz_cust_account_roles acct_role, hz_parties party, hz_cust_accounts acct, hz_relationships rel, hz_org_contacts org_cont, hz_parties rel_party, ar_lookups arl WHERE acct_role.party_id = rel.party_id AND acct_role.role_type = 'CONTACT' AND org_cont.party_relationship_id = rel.relationship_id AND rel.subject_table_name = 'HZ_PARTIES' AND rel.object_table_name = 'HZ_PARTIES' AND rel.subject_id = party.party_id AND rel.party_id = rel_party.party_id AND arl.lookup_type(+) = 'CONTACT_TITLE' AND arl.lookup_code(+) = party.person_pre_name_adjunct AND rel.object_id = acct.party_id AND acct.cust_account_id = acct_role.cust_account_id

View Text - HTML Formatted

SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, PARTY.PERSON_LAST_NAME || DECODE(PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, ' || PARTY.PERSON_FIRST_NAME) || DECODE(ARL.MEANING
, NULL
, NULL
, ' ' || ARL.MEANING) NAME
, REL_PARTY.EMAIL_ADDRESS
, REL_PARTY.PRIMARY_PHONE_COUNTRY_CODE || REL_PARTY.PRIMARY_PHONE_AREA_CODE || REL_PARTY.PRIMARY_PHONE_NUMBER PHONE_NUMBER
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS ACCT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, AR_LOOKUPS ARL
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND ARL.LOOKUP_CODE(+) = PARTY.PERSON_PRE_NAME_ADJUNCT
AND REL.OBJECT_ID = ACCT.PARTY_ID
AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID