VIEW: APPS.AS_OPPORTUNITY_CONTACTS_V

Source

View Text - Preformatted

SELECT LEAD_CONTACT.ROWID ROW_ID, LEAD_CONTACT.LEAD_CONTACT_ID, LEAD_CONTACT.LEAD_ID, LEAD_CONTACT.CONTACT_ID, LEAD_CONTACT.LAST_UPDATE_DATE, LEAD_CONTACT.LAST_UPDATED_BY, LEAD_CONTACT.CREATION_DATE, LEAD_CONTACT.CREATED_BY, LEAD_CONTACT.LAST_UPDATE_LOGIN, LEAD_CONTACT.REQUEST_ID, LEAD_CONTACT.PROGRAM_APPLICATION_ID, LEAD_CONTACT.PROGRAM_ID, LEAD_CONTACT.PROGRAM_UPDATE_DATE, LEAD_CONTACT.ENABLED_FLAG, LEAD_CONTACT.CUSTOMER_ID, LEAD_CONTACT.ADDRESS_ID, LEAD_CONTACT.RANK, LEAD_CONTACT.PHONE_ID, LEAD_CONTACT.ATTRIBUTE_CATEGORY, LEAD_CONTACT.ATTRIBUTE1, LEAD_CONTACT.ATTRIBUTE2, LEAD_CONTACT.ATTRIBUTE3, LEAD_CONTACT.ATTRIBUTE4, LEAD_CONTACT.ATTRIBUTE5, LEAD_CONTACT.ATTRIBUTE6, LEAD_CONTACT.ATTRIBUTE7, LEAD_CONTACT.ATTRIBUTE8, LEAD_CONTACT.ATTRIBUTE9, LEAD_CONTACT.ATTRIBUTE10, LEAD_CONTACT.ATTRIBUTE11, LEAD_CONTACT.ATTRIBUTE12, LEAD_CONTACT.ATTRIBUTE13, LEAD_CONTACT.ATTRIBUTE14, LEAD_CONTACT.ATTRIBUTE15, LEAD_CONTACT.CONTACT_PARTY_ID, PARTY_CONTACTS.ROWID CONTACT_ROW_ID, PARTY_CONTACTS.PERSON_FIRST_NAME FIRST_NAME, PARTY_CONTACTS.PERSON_LAST_NAME LAST_NAME, PARTY_CONTACTS.PERSON_PRE_NAME_ADJUNCT, PARTY_CONTACTS.PERSON_TITLE, REL_PARTY.EMAIL_ADDRESS, CONTACT_PHONES.ROWID PHONE_ROW_ID, CONTACT_PHONES.PHONE_COUNTRY_CODE, CONTACT_PHONES.PHONE_AREA_CODE AREA_CODE, CONTACT_PHONES.PHONE_NUMBER, CONTACT_PHONES.PHONE_EXTENSION EXTENSION, CONTACT_PHONES.PHONE_LINE_TYPE PHONE_TYPE_CODE, ARL.MEANING PHONE_TYPE, CONTACT_PHONES.PRIMARY_FLAG, RT.FORWARD_REL_CODE, RT.ROLE, REL.RELATIONSHIP_ID, REL.SUBJECT_ID SUBJECT_PARTY_ID, REL.OBJECT_ID OBJECT_PARTY_ID, LEAD_CONTACT.PRIMARY_CONTACT_FLAG FROM AS_LEAD_CONTACTS_ALL LEAD_CONTACT, HZ_PARTIES PARTY_CONTACTS, HZ_RELATIONSHIPS REL, HZ_RELATIONSHIP_TYPES RT, HZ_CONTACT_POINTS CONTACT_PHONES, AR_LOOKUPS ARL, HZ_PARTIES REL_PARTY WHERE REL.PARTY_ID = LEAD_CONTACT.CONTACT_PARTY_ID AND PARTY_CONTACTS.PARTY_ID = REL.SUBJECT_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.RELATIONSHIP_CODE = RT.FORWARD_REL_CODE AND REL.RELATIONSHIP_TYPE = RT.RELATIONSHIP_TYPE AND REL.SUBJECT_TYPE = RT.SUBJECT_TYPE AND REL.OBJECT_TYPE = RT.OBJECT_TYPE AND REL.OBJECT_ID = LEAD_CONTACT.CUSTOMER_ID AND CONTACT_PHONES.OWNER_TABLE_NAME (+) = 'HZ_PARTIES' AND CONTACT_PHONES.OWNER_TABLE_ID (+) = LEAD_CONTACT.CONTACT_PARTY_ID AND CONTACT_PHONES.PRIMARY_FLAG (+) = 'Y' AND CONTACT_PHONES.CONTACT_POINT_TYPE (+) = 'PHONE' AND ARL.LOOKUP_CODE (+) = CONTACT_PHONES.PHONE_LINE_TYPE AND ARL.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE' AND REL_PARTY.PARTY_ID = LEAD_CONTACT.CONTACT_PARTY_ID

View Text - HTML Formatted

SELECT LEAD_CONTACT.ROWID ROW_ID
, LEAD_CONTACT.LEAD_CONTACT_ID
, LEAD_CONTACT.LEAD_ID
, LEAD_CONTACT.CONTACT_ID
, LEAD_CONTACT.LAST_UPDATE_DATE
, LEAD_CONTACT.LAST_UPDATED_BY
, LEAD_CONTACT.CREATION_DATE
, LEAD_CONTACT.CREATED_BY
, LEAD_CONTACT.LAST_UPDATE_LOGIN
, LEAD_CONTACT.REQUEST_ID
, LEAD_CONTACT.PROGRAM_APPLICATION_ID
, LEAD_CONTACT.PROGRAM_ID
, LEAD_CONTACT.PROGRAM_UPDATE_DATE
, LEAD_CONTACT.ENABLED_FLAG
, LEAD_CONTACT.CUSTOMER_ID
, LEAD_CONTACT.ADDRESS_ID
, LEAD_CONTACT.RANK
, LEAD_CONTACT.PHONE_ID
, LEAD_CONTACT.ATTRIBUTE_CATEGORY
, LEAD_CONTACT.ATTRIBUTE1
, LEAD_CONTACT.ATTRIBUTE2
, LEAD_CONTACT.ATTRIBUTE3
, LEAD_CONTACT.ATTRIBUTE4
, LEAD_CONTACT.ATTRIBUTE5
, LEAD_CONTACT.ATTRIBUTE6
, LEAD_CONTACT.ATTRIBUTE7
, LEAD_CONTACT.ATTRIBUTE8
, LEAD_CONTACT.ATTRIBUTE9
, LEAD_CONTACT.ATTRIBUTE10
, LEAD_CONTACT.ATTRIBUTE11
, LEAD_CONTACT.ATTRIBUTE12
, LEAD_CONTACT.ATTRIBUTE13
, LEAD_CONTACT.ATTRIBUTE14
, LEAD_CONTACT.ATTRIBUTE15
, LEAD_CONTACT.CONTACT_PARTY_ID
, PARTY_CONTACTS.ROWID CONTACT_ROW_ID
, PARTY_CONTACTS.PERSON_FIRST_NAME FIRST_NAME
, PARTY_CONTACTS.PERSON_LAST_NAME LAST_NAME
, PARTY_CONTACTS.PERSON_PRE_NAME_ADJUNCT
, PARTY_CONTACTS.PERSON_TITLE
, REL_PARTY.EMAIL_ADDRESS
, CONTACT_PHONES.ROWID PHONE_ROW_ID
, CONTACT_PHONES.PHONE_COUNTRY_CODE
, CONTACT_PHONES.PHONE_AREA_CODE AREA_CODE
, CONTACT_PHONES.PHONE_NUMBER
, CONTACT_PHONES.PHONE_EXTENSION EXTENSION
, CONTACT_PHONES.PHONE_LINE_TYPE PHONE_TYPE_CODE
, ARL.MEANING PHONE_TYPE
, CONTACT_PHONES.PRIMARY_FLAG
, RT.FORWARD_REL_CODE
, RT.ROLE
, REL.RELATIONSHIP_ID
, REL.SUBJECT_ID SUBJECT_PARTY_ID
, REL.OBJECT_ID OBJECT_PARTY_ID
, LEAD_CONTACT.PRIMARY_CONTACT_FLAG
FROM AS_LEAD_CONTACTS_ALL LEAD_CONTACT
, HZ_PARTIES PARTY_CONTACTS
, HZ_RELATIONSHIPS REL
, HZ_RELATIONSHIP_TYPES RT
, HZ_CONTACT_POINTS CONTACT_PHONES
, AR_LOOKUPS ARL
, HZ_PARTIES REL_PARTY
WHERE REL.PARTY_ID = LEAD_CONTACT.CONTACT_PARTY_ID
AND PARTY_CONTACTS.PARTY_ID = REL.SUBJECT_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.RELATIONSHIP_CODE = RT.FORWARD_REL_CODE
AND REL.RELATIONSHIP_TYPE = RT.RELATIONSHIP_TYPE
AND REL.SUBJECT_TYPE = RT.SUBJECT_TYPE
AND REL.OBJECT_TYPE = RT.OBJECT_TYPE
AND REL.OBJECT_ID = LEAD_CONTACT.CUSTOMER_ID
AND CONTACT_PHONES.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CONTACT_PHONES.OWNER_TABLE_ID (+) = LEAD_CONTACT.CONTACT_PARTY_ID
AND CONTACT_PHONES.PRIMARY_FLAG (+) = 'Y'
AND CONTACT_PHONES.CONTACT_POINT_TYPE (+) = 'PHONE'
AND ARL.LOOKUP_CODE (+) = CONTACT_PHONES.PHONE_LINE_TYPE
AND ARL.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE'
AND REL_PARTY.PARTY_ID = LEAD_CONTACT.CONTACT_PARTY_ID