VIEW: APPS.AR_CBA_BANK_BRANCH_CONTACTS_V

Source

View Text - Preformatted

SELECT PersonParty.person_last_name AS last_name, PersonParty.person_first_name AS first_name, PersonParty.person_middle_name AS middle_name, PersonParty.person_title AS title, PersonParty.person_pre_name_adjunct AS prefix, Phone.phone_country_code AS phone_country_code, Phone.phone_area_code AS phone_area_code, Phone.phone_number AS phone_number, Phone.raw_phone_number AS raw_phone_number, Fax.phone_country_code AS fax_country_code, Fax.phone_area_code AS fax_area_code, Fax.phone_number AS fax_number, Fax.raw_phone_number AS raw_fax_number, Email.email_address AS email_address, Web.url AS url, NVL(cca.branch_party_id, cca.bank_party_id) AS bank_or_branch_id FROM hz_parties PersonParty, hz_parties RelParty, hz_contact_points Phone, hz_contact_points Fax, hz_contact_points Email, hz_contact_points Web, hz_relationships BCRel, ce_contact_assignments cca WHERE bcrel.relationship_id = cca.relationship_id AND cca.bank_account_id IS NULL AND BCRel.subject_id = PersonParty.party_id AND BCRel.subject_type = 'PERSON' AND BCRel.subject_table_name = 'HZ_PARTIES' AND BCRel.status = 'A' AND RelParty.party_id = BCRel.party_id AND Phone.owner_table_name(+) = 'HZ_PARTIES' AND Phone.owner_table_id(+) = RelParty.party_id AND Phone.contact_point_type(+) = 'PHONE' AND Phone.phone_line_type(+) = 'GEN' AND Phone.status(+) = 'A' AND Fax.owner_table_name(+) = 'HZ_PARTIES' AND Fax.owner_table_id(+) = RelParty.party_id AND Fax.contact_point_type(+) = 'PHONE' AND Fax.phone_line_type(+) = 'FAX' AND Fax.status(+) = 'A' AND Email.owner_table_name(+) = 'HZ_PARTIES' AND Email.owner_table_id(+) = RelParty.party_id AND Email.contact_point_type(+) = 'EMAIL' AND Email.status(+) = 'A' AND Web.owner_table_name(+) = 'HZ_PARTIES' AND Web.owner_table_id(+) = RelParty.party_id AND Web.contact_point_type(+) = 'WEB' AND Web.status(+) = 'A'

View Text - HTML Formatted

SELECT PERSONPARTY.PERSON_LAST_NAME AS LAST_NAME
, PERSONPARTY.PERSON_FIRST_NAME AS FIRST_NAME
, PERSONPARTY.PERSON_MIDDLE_NAME AS MIDDLE_NAME
, PERSONPARTY.PERSON_TITLE AS TITLE
, PERSONPARTY.PERSON_PRE_NAME_ADJUNCT AS PREFIX
, PHONE.PHONE_COUNTRY_CODE AS PHONE_COUNTRY_CODE
, PHONE.PHONE_AREA_CODE AS PHONE_AREA_CODE
, PHONE.PHONE_NUMBER AS PHONE_NUMBER
, PHONE.RAW_PHONE_NUMBER AS RAW_PHONE_NUMBER
, FAX.PHONE_COUNTRY_CODE AS FAX_COUNTRY_CODE
, FAX.PHONE_AREA_CODE AS FAX_AREA_CODE
, FAX.PHONE_NUMBER AS FAX_NUMBER
, FAX.RAW_PHONE_NUMBER AS RAW_FAX_NUMBER
, EMAIL.EMAIL_ADDRESS AS EMAIL_ADDRESS
, WEB.URL AS URL
, NVL(CCA.BRANCH_PARTY_ID
, CCA.BANK_PARTY_ID) AS BANK_OR_BRANCH_ID
FROM HZ_PARTIES PERSONPARTY
, HZ_PARTIES RELPARTY
, HZ_CONTACT_POINTS PHONE
, HZ_CONTACT_POINTS FAX
, HZ_CONTACT_POINTS EMAIL
, HZ_CONTACT_POINTS WEB
, HZ_RELATIONSHIPS BCREL
, CE_CONTACT_ASSIGNMENTS CCA
WHERE BCREL.RELATIONSHIP_ID = CCA.RELATIONSHIP_ID
AND CCA.BANK_ACCOUNT_ID IS NULL
AND BCREL.SUBJECT_ID = PERSONPARTY.PARTY_ID
AND BCREL.SUBJECT_TYPE = 'PERSON'
AND BCREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BCREL.STATUS = 'A'
AND RELPARTY.PARTY_ID = BCREL.PARTY_ID
AND PHONE.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE.OWNER_TABLE_ID(+) = RELPARTY.PARTY_ID
AND PHONE.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE.PHONE_LINE_TYPE(+) = 'GEN'
AND PHONE.STATUS(+) = 'A'
AND FAX.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND FAX.OWNER_TABLE_ID(+) = RELPARTY.PARTY_ID
AND FAX.CONTACT_POINT_TYPE(+) = 'PHONE'
AND FAX.PHONE_LINE_TYPE(+) = 'FAX'
AND FAX.STATUS(+) = 'A'
AND EMAIL.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND EMAIL.OWNER_TABLE_ID(+) = RELPARTY.PARTY_ID
AND EMAIL.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND EMAIL.STATUS(+) = 'A'
AND WEB.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND WEB.OWNER_TABLE_ID(+) = RELPARTY.PARTY_ID
AND WEB.CONTACT_POINT_TYPE(+) = 'WEB'
AND WEB.STATUS(+) = 'A'