VIEW: APPS.AST_MYTEAM_SALESOPP_NAME_UWQ_V

Source

View Text - Preformatted

SELECT OBJB.object_function IEU_OBJECT_FUNCTION, OBJB.object_parameters IEU_OBJECT_PARAMETERS, '' IEU_MEDIA_TYPE_UUID, 'LEAD_ID' IEU_PARAM_PK_COL, to_char(OPP.lead_id) IEU_PARAM_PK_VALUE, OPP.LEAD_ID , OPP.DESCRIPTION , OPP.TOTAL_AMOUNT , OPP.LAST_UPDATE_DATE , OPP.LAST_UPDATED_BY , OPP.CUSTOMER_ID PARTY_ID , PARTY.PARTY_NAME , OPP.ORG_ID, OPP.SALES_STAGE_ID , SS.NAME SALES_STAGE_NAME , OPP.LEAD_NUMBER , OPP.STATUS STATUS_CODE , STB.MEANING STATUS_MEANING , OPP.WIN_PROBABILITY , FP.MEANING PROBABILITY_MEANING , ACC.SALESFORCE_ID RESOURCE_ID , 'OPPORTUNITY' OBJECT_CODE , 'RS_INDIVIDUAL' RESOURCE_TYPE , OPP.CUSTOMER_BUDGET , OPP.DECISION_DATE , OPP.CREATION_DATE , OPP.CHANNEL_CODE , ASOCHANNELS.MEANING SALES_CHANNEL, OPP.CURRENCY_CODE, OPP.PARENT_PROJECT, OPP.DECISION_TIMEFRAME_CODE, LOK2.MEANING DECISION_TIMEFRAME, DECODE(RES.SOURCE_NAME,NULL,NVL((SELECT USER_NAME FROM FND_USER WHERE USER_ID = OPP.LAST_UPDATED_BY),'-'),RES.SOURCE_NAME) LAST_UPDATED_BYNAME, PARTY_CONTACTS.PERSON_FIRST_NAME, PARTY_CONTACTS. PERSON_LAST_NAME, PARTY_CONTACTS.PERSON_MIDDLE_NAME, PARTY_CONTACTS.PERSON_FIRST_NAME || '  ' || PARTY_CONTACTS.PERSON_MIDDLE_NAME || '  ' || PARTY_CONTACTS.PERSON_LAST_NAME, PARTY_CONTACTS.SALUTATION, REL_PARTY.EMAIL_ADDRESS, ORG_CONT.JOB_TITLE, LEAD_CONTACT.CONTACT_PARTY_ID, LEAD_CONTACT.PHONE_ID, CONTACT_PHONES.PHONE_COUNTRY_CODE|| DECODE(CONTACT_PHONES.PHONE_COUNTRY_CODE,NULL,'',' ')|| CONTACT_PHONES.PHONE_AREA_CODE|| DECODE(CONTACT_PHONES.PHONE_AREA_CODE,NULL,'',' ')|| CONTACT_PHONES.PHONE_NUMBER|| DECODE(CONTACT_PHONES.PHONE_NUMBER,NULL,'',' ')|| CONTACT_PHONES.PHONE_EXTENSION , OPP. SALES_METHODOLOGY_ID, SM.SALES_METHODOLOGY_NAME, OPP.VEHICLE_RESPONSE_CODE, LOK1.MEANING, OPP.SOURCE_PROMOTION_ID, OPP.ADDRESS_ID, LOCATION.ADDRESS1, LOCATION.CITY, LOCATION.STATE, LOCATION.COUNTRY, LOCATION.PROVINCE, LOCATION.POSTAL_CODE, LOCATION.ADDRESS1||' '||LOCATION.CITY||' '|| LOCATION.STATE||' '||LOCATION.COUNTRY||' '||LOCATION.POSTAL_CODE, OPP.CLOSE_REASON, OPP.CLOSE_COMPETITOR_ID, OPP.CLOSE_COMPETITOR_CODE, OPP.CLOSE_COMPETITOR, OPP.CLOSE_COMMENT, ACC.OPEN_FLAG, SC.NAME , SC.SOURCE_CODE , OPP.TOTAL_REVENUE_OPP_FORECAST_AMT FROM AS_LEADS_ALL OPP, AS_SALES_METHODOLOGY_TL SM, AS_LEAD_CONTACTS_ALL LEAD_CONTACT, HZ_PARTIES PARTY_CONTACTS, HZ_RELATIONSHIPS REL, HZ_CONTACT_POINTS CONTACT_PHONES, HZ_ORG_CONTACTS ORG_CONT , JTF_RS_RESOURCE_EXTNS RES, AS_SALES_STAGES_ALL_TL SS, AS_FORECAST_PROB_ALL_TL FP, AS_STATUSES_TL STB, HZ_LOCATIONS LOCATION, HZ_PARTY_SITES SITES, HZ_PARTIES REL_PARTY, AS_ACCESSES_ALL ACC, HZ_PARTIES PARTY, JTF_OBJECTS_B OBJB, AMS_P_SOURCE_CODES_V SC, AS_LOOKUPS LOK1, AS_LOOKUPS LOK2, AS_LOOKUPS LOK3, OE_LOOKUPS ASOCHANNELS WHERE LOK2.LOOKUP_CODE(+) = OPP.DECISION_TIMEFRAME_CODE AND LOK2.LOOKUP_TYPE(+) = 'DECISION_TIMEFRAME' AND OPP.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE(+) AND ASOCHANNELS.LOOKUP_TYPE(+) = 'SALES_CHANNEL' AND OPP.STATUS = STB.STATUS_CODE AND STB.LANGUAGE(+) = USERENV('LANG') AND OPP.SALES_METHODOLOGY_ID = SM.SALES_METHODOLOGY_ID(+) AND SM.LANGUAGE(+) = USERENV('LANG') AND OPP.CUSTOMER_ID = PARTY.PARTY_ID AND OPP.LEAD_ID = LEAD_CONTACT.LEAD_ID(+) AND LEAD_CONTACT.PRIMARY_CONTACT_FLAG (+) = 'Y' AND 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.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 REL_PARTY.PARTY_ID (+) = LEAD_CONTACT.CONTACT_PARTY_ID AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID AND OPP.SALES_STAGE_ID = SS.SALES_STAGE_ID(+) AND RES.USER_ID(+) = OPP.LAST_UPDATED_BY AND SS.LANGUAGE(+) = USERENV('LANG') AND OPP.WIN_PROBABILITY = FP.PROBABILITY_VALUE AND FP.LANGUAGE = USERENV('LANG') AND LOCATION.LOCATION_ID (+) = SITES.LOCATION_ID AND OPP.ADDRESS_ID = SITES.PARTY_SITE_ID(+) AND OPP.LEAD_ID = ACC.LEAD_ID AND OPP.SOURCE_PROMOTION_ID = SC.SOURCE_CODE_ID(+) AND OBJB.OBJECT_CODE = 'OPPORTUNITY' AND LOK1.LOOKUP_CODE(+) = OPP.VEHICLE_RESPONSE_CODE AND ACC.OPEN_FLAG='Y' AND LOK1.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE' AND LOK3.LOOKUP_CODE(+) = OPP.CLOSE_REASON AND LOK3.LOOKUP_TYPE(+) = 'CLOSE_REASON'

View Text - HTML Formatted

SELECT OBJB.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJB.OBJECT_PARAMETERS IEU_OBJECT_PARAMETERS
, '' IEU_MEDIA_TYPE_UUID
, 'LEAD_ID' IEU_PARAM_PK_COL
, TO_CHAR(OPP.LEAD_ID) IEU_PARAM_PK_VALUE
, OPP.LEAD_ID
, OPP.DESCRIPTION
, OPP.TOTAL_AMOUNT
, OPP.LAST_UPDATE_DATE
, OPP.LAST_UPDATED_BY
, OPP.CUSTOMER_ID PARTY_ID
, PARTY.PARTY_NAME
, OPP.ORG_ID
, OPP.SALES_STAGE_ID
, SS.NAME SALES_STAGE_NAME
, OPP.LEAD_NUMBER
, OPP.STATUS STATUS_CODE
, STB.MEANING STATUS_MEANING
, OPP.WIN_PROBABILITY
, FP.MEANING PROBABILITY_MEANING
, ACC.SALESFORCE_ID RESOURCE_ID
, 'OPPORTUNITY' OBJECT_CODE
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, OPP.CUSTOMER_BUDGET
, OPP.DECISION_DATE
, OPP.CREATION_DATE
, OPP.CHANNEL_CODE
, ASOCHANNELS.MEANING SALES_CHANNEL
, OPP.CURRENCY_CODE
, OPP.PARENT_PROJECT
, OPP.DECISION_TIMEFRAME_CODE
, LOK2.MEANING DECISION_TIMEFRAME
, DECODE(RES.SOURCE_NAME
, NULL
, NVL((SELECT USER_NAME
FROM FND_USER
WHERE USER_ID = OPP.LAST_UPDATED_BY)
, '-')
, RES.SOURCE_NAME) LAST_UPDATED_BYNAME
, PARTY_CONTACTS.PERSON_FIRST_NAME
, PARTY_CONTACTS. PERSON_LAST_NAME
, PARTY_CONTACTS.PERSON_MIDDLE_NAME
, PARTY_CONTACTS.PERSON_FIRST_NAME || ' ' || PARTY_CONTACTS.PERSON_MIDDLE_NAME || ' ' || PARTY_CONTACTS.PERSON_LAST_NAME
, PARTY_CONTACTS.SALUTATION
, REL_PARTY.EMAIL_ADDRESS
, ORG_CONT.JOB_TITLE
, LEAD_CONTACT.CONTACT_PARTY_ID
, LEAD_CONTACT.PHONE_ID
, CONTACT_PHONES.PHONE_COUNTRY_CODE|| DECODE(CONTACT_PHONES.PHONE_COUNTRY_CODE
, NULL
, ''
, ' ')|| CONTACT_PHONES.PHONE_AREA_CODE|| DECODE(CONTACT_PHONES.PHONE_AREA_CODE
, NULL
, ''
, ' ')|| CONTACT_PHONES.PHONE_NUMBER|| DECODE(CONTACT_PHONES.PHONE_NUMBER
, NULL
, ''
, ' ')|| CONTACT_PHONES.PHONE_EXTENSION
, OPP. SALES_METHODOLOGY_ID
, SM.SALES_METHODOLOGY_NAME
, OPP.VEHICLE_RESPONSE_CODE
, LOK1.MEANING
, OPP.SOURCE_PROMOTION_ID
, OPP.ADDRESS_ID
, LOCATION.ADDRESS1
, LOCATION.CITY
, LOCATION.STATE
, LOCATION.COUNTRY
, LOCATION.PROVINCE
, LOCATION.POSTAL_CODE
, LOCATION.ADDRESS1||' '||LOCATION.CITY||' '|| LOCATION.STATE||' '||LOCATION.COUNTRY||' '||LOCATION.POSTAL_CODE
, OPP.CLOSE_REASON
, OPP.CLOSE_COMPETITOR_ID
, OPP.CLOSE_COMPETITOR_CODE
, OPP.CLOSE_COMPETITOR
, OPP.CLOSE_COMMENT
, ACC.OPEN_FLAG
, SC.NAME
, SC.SOURCE_CODE
, OPP.TOTAL_REVENUE_OPP_FORECAST_AMT
FROM AS_LEADS_ALL OPP
, AS_SALES_METHODOLOGY_TL SM
, AS_LEAD_CONTACTS_ALL LEAD_CONTACT
, HZ_PARTIES PARTY_CONTACTS
, HZ_RELATIONSHIPS REL
, HZ_CONTACT_POINTS CONTACT_PHONES
, HZ_ORG_CONTACTS ORG_CONT
, JTF_RS_RESOURCE_EXTNS RES
, AS_SALES_STAGES_ALL_TL SS
, AS_FORECAST_PROB_ALL_TL FP
, AS_STATUSES_TL STB
, HZ_LOCATIONS LOCATION
, HZ_PARTY_SITES SITES
, HZ_PARTIES REL_PARTY
, AS_ACCESSES_ALL ACC
, HZ_PARTIES PARTY
, JTF_OBJECTS_B OBJB
, AMS_P_SOURCE_CODES_V SC
, AS_LOOKUPS LOK1
, AS_LOOKUPS LOK2
, AS_LOOKUPS LOK3
, OE_LOOKUPS ASOCHANNELS
WHERE LOK2.LOOKUP_CODE(+) = OPP.DECISION_TIMEFRAME_CODE
AND LOK2.LOOKUP_TYPE(+) = 'DECISION_TIMEFRAME'
AND OPP.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE(+)
AND ASOCHANNELS.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND OPP.STATUS = STB.STATUS_CODE
AND STB.LANGUAGE(+) = USERENV('LANG')
AND OPP.SALES_METHODOLOGY_ID = SM.SALES_METHODOLOGY_ID(+)
AND SM.LANGUAGE(+) = USERENV('LANG')
AND OPP.CUSTOMER_ID = PARTY.PARTY_ID
AND OPP.LEAD_ID = LEAD_CONTACT.LEAD_ID(+)
AND LEAD_CONTACT.PRIMARY_CONTACT_FLAG (+) = 'Y'
AND 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.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 REL_PARTY.PARTY_ID (+) = LEAD_CONTACT.CONTACT_PARTY_ID
AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID
AND OPP.SALES_STAGE_ID = SS.SALES_STAGE_ID(+)
AND RES.USER_ID(+) = OPP.LAST_UPDATED_BY
AND SS.LANGUAGE(+) = USERENV('LANG')
AND OPP.WIN_PROBABILITY = FP.PROBABILITY_VALUE
AND FP.LANGUAGE = USERENV('LANG')
AND LOCATION.LOCATION_ID (+) = SITES.LOCATION_ID
AND OPP.ADDRESS_ID = SITES.PARTY_SITE_ID(+)
AND OPP.LEAD_ID = ACC.LEAD_ID
AND OPP.SOURCE_PROMOTION_ID = SC.SOURCE_CODE_ID(+)
AND OBJB.OBJECT_CODE = 'OPPORTUNITY'
AND LOK1.LOOKUP_CODE(+) = OPP.VEHICLE_RESPONSE_CODE
AND ACC.OPEN_FLAG='Y'
AND LOK1.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE'
AND LOK3.LOOKUP_CODE(+) = OPP.CLOSE_REASON
AND LOK3.LOOKUP_TYPE(+) = 'CLOSE_REASON'