VIEW: APPS.AST_OPPORTUNITY_CONTACT_V

Source

View Text - Preformatted

SELECT opp.ROWID, opp.lead_id, opp.last_update_date, opp.last_updated_by, opp.creation_date, opp.created_by, opp.last_update_login, opp.request_id, opp.program_application_id, opp.program_id, opp.program_update_date, opp.lead_number, opp.org_id, opp.status, opp.customer_id, opp.address_id, opp.auto_assignment_type, opp.prm_assignment_type, opp.decision_timeframe_code, opp.vehicle_response_code, opp.budget_status_code, opp.prm_ind_classification_code, opp.prm_lead_type, opp.close_competitor_code, opp.deleted_flag, opp.customer_budget, opp.original_lead_id, opp.incumbent_partner_resource_id, opp.incumbent_partner_party_id, opp.followup_date, opp.prm_exec_sponsor_flag, opp.prm_prj_lead_in_place_flag, opp.methodology_code, opp.offer_id, opp.lead_source_code, opp.orig_system_reference, opp.sales_stage_id, opp.channel_code, opp.total_amount, opp.currency_code, opp.decision_date, opp.win_probability, opp.close_reason, opp.close_competitor, opp.close_competitor_id, opp.close_comment, opp.description, opp.RANK, opp.end_user_customer_name, opp.source_promotion_id, opp.end_user_customer_id, opp.end_user_address_id, opp.attribute_category, opp.attribute1, opp.attribute2, opp.attribute3, opp.attribute4, opp.attribute5, opp.attribute6, opp.attribute7, opp.attribute8, opp.attribute9, opp.attribute10, opp.attribute11, opp.attribute12, opp.attribute13, opp.attribute14, opp.attribute15, opp.parent_project, opp.no_opp_allowed_flag, opp.delete_allowed_flag, opp.price_list_id, party.party_name, party.person_first_name, party.person_last_name, party.party_type, party.category_code, party.party_number, party.validated_flag, asochannels.meaning, stgtl.name, asstatuses.meaning, opp.freeze_flag, party_contacts.party_name primary_contact, party_contacts.party_id primary_contact_party_id, contact_phones.phone_country_code || ' ' || contact_phones.phone_area_code || ' ' || contact_phones.phone_number || ' ' || contact_phones.phone_extension phone, contacts.job_title, lead_contact.lead_contact_id, lead_contact.contact_id, lead_contact.contact_party_id, lead_contact.phone_id, lead_contact.RANK role FROM as_leads_all opp, hz_parties party, oe_lookups asochannels, as_sales_stages_all_tl stgtl, as_statuses_tl asstatuses, as_lead_contacts_all lead_contact, hz_parties party_contacts, hz_relationships rel, hz_contact_points contact_phones, hz_org_contacts contacts, ar_lookups arl, hz_parties rel_party WHERE opp.customer_id = party.party_id AND opp.channel_code = asochannels.lookup_code AND asochannels.lookup_type = 'SALES_CHANNEL' AND opp.sales_stage_id = stgtl.sales_stage_id AND stgtl.language = USERENV ('LANG') AND opp.status = asstatuses.status_code AND asstatuses.language = USERENV ('LANG') AND opp.lead_id = lead_contact.lead_id(+) AND rel.party_id(+) = lead_contact.contact_party_id AND lead_contact.primary_contact_flag(+) = 'Y' AND lead_contact.contact_id = contacts.org_contact_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.directional_flag(+) = 'F' 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 OPP.ROWID
, OPP.LEAD_ID
, OPP.LAST_UPDATE_DATE
, OPP.LAST_UPDATED_BY
, OPP.CREATION_DATE
, OPP.CREATED_BY
, OPP.LAST_UPDATE_LOGIN
, OPP.REQUEST_ID
, OPP.PROGRAM_APPLICATION_ID
, OPP.PROGRAM_ID
, OPP.PROGRAM_UPDATE_DATE
, OPP.LEAD_NUMBER
, OPP.ORG_ID
, OPP.STATUS
, OPP.CUSTOMER_ID
, OPP.ADDRESS_ID
, OPP.AUTO_ASSIGNMENT_TYPE
, OPP.PRM_ASSIGNMENT_TYPE
, OPP.DECISION_TIMEFRAME_CODE
, OPP.VEHICLE_RESPONSE_CODE
, OPP.BUDGET_STATUS_CODE
, OPP.PRM_IND_CLASSIFICATION_CODE
, OPP.PRM_LEAD_TYPE
, OPP.CLOSE_COMPETITOR_CODE
, OPP.DELETED_FLAG
, OPP.CUSTOMER_BUDGET
, OPP.ORIGINAL_LEAD_ID
, OPP.INCUMBENT_PARTNER_RESOURCE_ID
, OPP.INCUMBENT_PARTNER_PARTY_ID
, OPP.FOLLOWUP_DATE
, OPP.PRM_EXEC_SPONSOR_FLAG
, OPP.PRM_PRJ_LEAD_IN_PLACE_FLAG
, OPP.METHODOLOGY_CODE
, OPP.OFFER_ID
, OPP.LEAD_SOURCE_CODE
, OPP.ORIG_SYSTEM_REFERENCE
, OPP.SALES_STAGE_ID
, OPP.CHANNEL_CODE
, OPP.TOTAL_AMOUNT
, OPP.CURRENCY_CODE
, OPP.DECISION_DATE
, OPP.WIN_PROBABILITY
, OPP.CLOSE_REASON
, OPP.CLOSE_COMPETITOR
, OPP.CLOSE_COMPETITOR_ID
, OPP.CLOSE_COMMENT
, OPP.DESCRIPTION
, OPP.RANK
, OPP.END_USER_CUSTOMER_NAME
, OPP.SOURCE_PROMOTION_ID
, OPP.END_USER_CUSTOMER_ID
, OPP.END_USER_ADDRESS_ID
, OPP.ATTRIBUTE_CATEGORY
, OPP.ATTRIBUTE1
, OPP.ATTRIBUTE2
, OPP.ATTRIBUTE3
, OPP.ATTRIBUTE4
, OPP.ATTRIBUTE5
, OPP.ATTRIBUTE6
, OPP.ATTRIBUTE7
, OPP.ATTRIBUTE8
, OPP.ATTRIBUTE9
, OPP.ATTRIBUTE10
, OPP.ATTRIBUTE11
, OPP.ATTRIBUTE12
, OPP.ATTRIBUTE13
, OPP.ATTRIBUTE14
, OPP.ATTRIBUTE15
, OPP.PARENT_PROJECT
, OPP.NO_OPP_ALLOWED_FLAG
, OPP.DELETE_ALLOWED_FLAG
, OPP.PRICE_LIST_ID
, PARTY.PARTY_NAME
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_LAST_NAME
, PARTY.PARTY_TYPE
, PARTY.CATEGORY_CODE
, PARTY.PARTY_NUMBER
, PARTY.VALIDATED_FLAG
, ASOCHANNELS.MEANING
, STGTL.NAME
, ASSTATUSES.MEANING
, OPP.FREEZE_FLAG
, PARTY_CONTACTS.PARTY_NAME PRIMARY_CONTACT
, PARTY_CONTACTS.PARTY_ID PRIMARY_CONTACT_PARTY_ID
, CONTACT_PHONES.PHONE_COUNTRY_CODE || ' ' || CONTACT_PHONES.PHONE_AREA_CODE || ' ' || CONTACT_PHONES.PHONE_NUMBER || ' ' || CONTACT_PHONES.PHONE_EXTENSION PHONE
, CONTACTS.JOB_TITLE
, LEAD_CONTACT.LEAD_CONTACT_ID
, LEAD_CONTACT.CONTACT_ID
, LEAD_CONTACT.CONTACT_PARTY_ID
, LEAD_CONTACT.PHONE_ID
, LEAD_CONTACT.RANK ROLE
FROM AS_LEADS_ALL OPP
, HZ_PARTIES PARTY
, OE_LOOKUPS ASOCHANNELS
, AS_SALES_STAGES_ALL_TL STGTL
, AS_STATUSES_TL ASSTATUSES
, AS_LEAD_CONTACTS_ALL LEAD_CONTACT
, HZ_PARTIES PARTY_CONTACTS
, HZ_RELATIONSHIPS REL
, HZ_CONTACT_POINTS CONTACT_PHONES
, HZ_ORG_CONTACTS CONTACTS
, AR_LOOKUPS ARL
, HZ_PARTIES REL_PARTY
WHERE OPP.CUSTOMER_ID = PARTY.PARTY_ID
AND OPP.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE
AND ASOCHANNELS.LOOKUP_TYPE = 'SALES_CHANNEL'
AND OPP.SALES_STAGE_ID = STGTL.SALES_STAGE_ID
AND STGTL.LANGUAGE = USERENV ('LANG')
AND OPP.STATUS = ASSTATUSES.STATUS_CODE
AND ASSTATUSES.LANGUAGE = USERENV ('LANG')
AND OPP.LEAD_ID = LEAD_CONTACT.LEAD_ID(+)
AND REL.PARTY_ID(+) = LEAD_CONTACT.CONTACT_PARTY_ID
AND LEAD_CONTACT.PRIMARY_CONTACT_FLAG(+) = 'Y'
AND LEAD_CONTACT.CONTACT_ID = CONTACTS.ORG_CONTACT_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.DIRECTIONAL_FLAG(+) = 'F'
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