VIEW: APPS.AST_SALES_LEAD_CONTACTS_V

Source

View Text - Preformatted

SELECT LEAD_CONT.ROWID , LEAD_CONT.LEAD_CONTACT_ID , LEAD_CONT.SALES_LEAD_ID , LEAD_CONT.CONTACT_ID , LEAD_CONT.LAST_UPDATE_DATE , LEAD_CONT.LAST_UPDATED_BY , LEAD_CONT.CREATION_DATE , LEAD_CONT.CREATED_BY , LEAD_CONT.LAST_UPDATE_LOGIN , LEAD_CONT.REQUEST_ID , LEAD_CONT.PROGRAM_APPLICATION_ID , LEAD_CONT.PROGRAM_ID , LEAD_CONT.PROGRAM_UPDATE_DATE , LEAD_CONT.ENABLED_FLAG , LEAD_CONT.CUSTOMER_ID , LEAD_CONT.ADDRESS_ID , LEAD_CONT.RANK , LEAD_CONT.PHONE_ID , LEAD_CONT.PRIMARY_CONTACT_FLAG , LEAD_CONT.ATTRIBUTE_CATEGORY , LEAD_CONT.ATTRIBUTE1 , LEAD_CONT.ATTRIBUTE2 , LEAD_CONT.ATTRIBUTE3 , LEAD_CONT.ATTRIBUTE4 , LEAD_CONT.ATTRIBUTE5 , LEAD_CONT.ATTRIBUTE6 , LEAD_CONT.ATTRIBUTE7 , LEAD_CONT.ATTRIBUTE8 , LEAD_CONT.ATTRIBUTE9 , LEAD_CONT.ATTRIBUTE10 , LEAD_CONT.ATTRIBUTE11 , LEAD_CONT.ATTRIBUTE12 , LEAD_CONT.ATTRIBUTE13 , LEAD_CONT.ATTRIBUTE14 , LEAD_CONT.ATTRIBUTE15 , LEAD_CONT.CONTACT_PARTY_ID , LEAD_CONT.CONTACT_ROLE_CODE , ROLE_LKUP.MEANING , PARTY_CONT.PERSON_FIRST_NAME , PARTY_CONT.PERSON_LAST_NAME , ORG_CONT.JOB_TITLE , PHONE2.PHONE_COUNTRY_CODE||DECODE(PHONE2.PHONE_COUNTRY_CODE,NULL,'',' ') ||PHONE2.PHONE_AREA_CODE||DECODE(PHONE2.PHONE_AREA_CODE,NULL,'',' ') ||PHONE2.PHONE_NUMBER||DECODE(PHONE2.PHONE_NUMBER,NULL,'',' ') ||PHONE2.PHONE_EXTENSION , PHONE.PHONE_LINE_TYPE , REL.RELATIONSHIP_ID , REL.RELATIONSHIP_CODE , REL.SUBJECT_ID , REL.OBJECT_ID , AR_LKUP1.MEANING , AR_LKUP2.MEANING , PHONE1.EMAIL_ADDRESS , PHONE2.RAW_PHONE_NUMBER , ORG_CONT.JOB_TITLE_CODE , AR_LKUP3.MEANING FROM AS_SALES_LEAD_CONTACTS LEAD_CONT , HZ_PARTIES PARTY_CONT , HZ_RELATIONSHIPS REL , HZ_ORG_CONTACTS ORG_CONT , HZ_CONTACT_POINTS PHONE , HZ_CONTACT_POINTS PHONE1 , HZ_CONTACT_POINTS PHONE2 , AS_LOOKUPS ROLE_LKUP , AR_LOOKUPS AR_LKUP1 , AR_LOOKUPS AR_LKUP2 , AR_LOOKUPS AR_LKUP3 WHERE LEAD_CONT.CONTACT_PARTY_ID = REL.PARTY_ID AND LEAD_CONT.ADDRESS_ID = PHONE.CONTACT_POINT_ID(+) AND PARTY_CONT.PARTY_ID = REL.SUBJECT_ID AND LEAD_CONT.CUSTOMER_ID = REL.OBJECT_ID AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID AND PARTY_CONT.PARTY_TYPE = 'PERSON' AND LEAD_CONT.CONTACT_PARTY_ID = PHONE1.OWNER_TABLE_ID(+) AND PHONE1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND PHONE1.CONTACT_POINT_TYPE(+) = 'EMAIL' AND PHONE1.PRIMARY_FLAG(+) = 'Y' AND LEAD_CONT.CONTACT_PARTY_ID = PHONE2.OWNER_TABLE_ID(+) AND PHONE2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND PHONE2.CONTACT_POINT_TYPE(+) = 'PHONE' AND PHONE2.PRIMARY_FLAG(+) = 'Y' AND ROLE_LKUP.LOOKUP_TYPE(+) = 'LEAD_CONTACT_ROLE' AND ROLE_LKUP.LOOKUP_CODE(+) = LEAD_CONT.CONTACT_ROLE_CODE AND ROLE_LKUP.ENABLED_FLAG(+) = 'Y' AND AR_LKUP1.LOOKUP_CODE(+) = REL.RELATIONSHIP_CODE AND AR_LKUP1.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE' AND AR_LKUP2.LOOKUP_CODE(+) = PARTY_CONT.PERSON_PRE_NAME_ADJUNCT AND AR_LKUP2.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND AR_LKUP3.LOOKUP_CODE(+) = ORG_CONT.JOB_TITLE_CODE AND AR_LKUP3.LOOKUP_TYPE(+) = 'RESPONSIBILITY'

View Text - HTML Formatted

SELECT LEAD_CONT.ROWID
, LEAD_CONT.LEAD_CONTACT_ID
, LEAD_CONT.SALES_LEAD_ID
, LEAD_CONT.CONTACT_ID
, LEAD_CONT.LAST_UPDATE_DATE
, LEAD_CONT.LAST_UPDATED_BY
, LEAD_CONT.CREATION_DATE
, LEAD_CONT.CREATED_BY
, LEAD_CONT.LAST_UPDATE_LOGIN
, LEAD_CONT.REQUEST_ID
, LEAD_CONT.PROGRAM_APPLICATION_ID
, LEAD_CONT.PROGRAM_ID
, LEAD_CONT.PROGRAM_UPDATE_DATE
, LEAD_CONT.ENABLED_FLAG
, LEAD_CONT.CUSTOMER_ID
, LEAD_CONT.ADDRESS_ID
, LEAD_CONT.RANK
, LEAD_CONT.PHONE_ID
, LEAD_CONT.PRIMARY_CONTACT_FLAG
, LEAD_CONT.ATTRIBUTE_CATEGORY
, LEAD_CONT.ATTRIBUTE1
, LEAD_CONT.ATTRIBUTE2
, LEAD_CONT.ATTRIBUTE3
, LEAD_CONT.ATTRIBUTE4
, LEAD_CONT.ATTRIBUTE5
, LEAD_CONT.ATTRIBUTE6
, LEAD_CONT.ATTRIBUTE7
, LEAD_CONT.ATTRIBUTE8
, LEAD_CONT.ATTRIBUTE9
, LEAD_CONT.ATTRIBUTE10
, LEAD_CONT.ATTRIBUTE11
, LEAD_CONT.ATTRIBUTE12
, LEAD_CONT.ATTRIBUTE13
, LEAD_CONT.ATTRIBUTE14
, LEAD_CONT.ATTRIBUTE15
, LEAD_CONT.CONTACT_PARTY_ID
, LEAD_CONT.CONTACT_ROLE_CODE
, ROLE_LKUP.MEANING
, PARTY_CONT.PERSON_FIRST_NAME
, PARTY_CONT.PERSON_LAST_NAME
, ORG_CONT.JOB_TITLE
, PHONE2.PHONE_COUNTRY_CODE||DECODE(PHONE2.PHONE_COUNTRY_CODE
, NULL
, ''
, ' ') ||PHONE2.PHONE_AREA_CODE||DECODE(PHONE2.PHONE_AREA_CODE
, NULL
, ''
, ' ') ||PHONE2.PHONE_NUMBER||DECODE(PHONE2.PHONE_NUMBER
, NULL
, ''
, ' ') ||PHONE2.PHONE_EXTENSION
, PHONE.PHONE_LINE_TYPE
, REL.RELATIONSHIP_ID
, REL.RELATIONSHIP_CODE
, REL.SUBJECT_ID
, REL.OBJECT_ID
, AR_LKUP1.MEANING
, AR_LKUP2.MEANING
, PHONE1.EMAIL_ADDRESS
, PHONE2.RAW_PHONE_NUMBER
, ORG_CONT.JOB_TITLE_CODE
, AR_LKUP3.MEANING
FROM AS_SALES_LEAD_CONTACTS LEAD_CONT
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CONTACT_POINTS PHONE
, HZ_CONTACT_POINTS PHONE1
, HZ_CONTACT_POINTS PHONE2
, AS_LOOKUPS ROLE_LKUP
, AR_LOOKUPS AR_LKUP1
, AR_LOOKUPS AR_LKUP2
, AR_LOOKUPS AR_LKUP3
WHERE LEAD_CONT.CONTACT_PARTY_ID = REL.PARTY_ID
AND LEAD_CONT.ADDRESS_ID = PHONE.CONTACT_POINT_ID(+)
AND PARTY_CONT.PARTY_ID = REL.SUBJECT_ID
AND LEAD_CONT.CUSTOMER_ID = REL.OBJECT_ID
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND PARTY_CONT.PARTY_TYPE = 'PERSON'
AND LEAD_CONT.CONTACT_PARTY_ID = PHONE1.OWNER_TABLE_ID(+)
AND PHONE1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE1.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND PHONE1.PRIMARY_FLAG(+) = 'Y'
AND LEAD_CONT.CONTACT_PARTY_ID = PHONE2.OWNER_TABLE_ID(+)
AND PHONE2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PHONE2.CONTACT_POINT_TYPE(+) = 'PHONE'
AND PHONE2.PRIMARY_FLAG(+) = 'Y'
AND ROLE_LKUP.LOOKUP_TYPE(+) = 'LEAD_CONTACT_ROLE'
AND ROLE_LKUP.LOOKUP_CODE(+) = LEAD_CONT.CONTACT_ROLE_CODE
AND ROLE_LKUP.ENABLED_FLAG(+) = 'Y'
AND AR_LKUP1.LOOKUP_CODE(+) = REL.RELATIONSHIP_CODE
AND AR_LKUP1.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE'
AND AR_LKUP2.LOOKUP_CODE(+) = PARTY_CONT.PERSON_PRE_NAME_ADJUNCT
AND AR_LKUP2.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND AR_LKUP3.LOOKUP_CODE(+) = ORG_CONT.JOB_TITLE_CODE
AND AR_LKUP3.LOOKUP_TYPE(+) = 'RESPONSIBILITY'