VIEW: APPS.AST_SALESOPP_NAME_UWQ_V

Source

View Text - Preformatted

SELECT /*+ FIRST_ROWS */ 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 LEAD_ID, OPP.DESCRIPTION DESCRIPTION, OPP.TOTAL_AMOUNT TOTAL_AMOUNT, OPP.LAST_UPDATE_DATE LAST_UPDATE_DATE, OPP.LAST_UPDATED_BY LAST_UPDATED_BY, OPP.CUSTOMER_ID PARTY_ID , PARTY.PARTY_NAME PARTY_NAME, OPP.ORG_ID ORG_ID, OPP.SALES_STAGE_ID SALES_STAGE_ID, SS.NAME SALES_STAGE_NAME , OPP.LEAD_NUMBER LEAD_NUMBER, OPP.STATUS STATUS_CODE , STT.MEANING STATUS_MEANING, OPP.WIN_PROBABILITY WIN_PROBABILITY, FP.MEANING PROBABILITY_MEANING, v1.SALESFORCE_ID RESOURCE_ID, 'OPPORTUNITY' OBJECT_CODE, 'RS_INDIVIDUAL' RESOURCE_TYPE, OPP.CUSTOMER_BUDGET CUSTOMER_BUDGET, OPP.DECISION_DATE DECISION_DATE, OPP.CREATION_DATE CREATION_DATE, OPP.CHANNEL_CODE CHANNEL_CODE, ASOCHANNELS.MEANING SALES_CHANNEL, OPP.CURRENCY_CODE CURRENCY_CODE, OPP.PARENT_PROJECT PARENT_PROJECT, OPP.DECISION_TIMEFRAME_CODE 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 CONTACT_FIRST_NAME, PARTY_CONTACTS.PERSON_LAST_NAME CONTACT_LAST_NAME, PARTY_CONTACTS.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME, PARTY_CONTACTS.PERSON_FIRST_NAME || '  ' || PARTY_CONTACTS.PERSON_MIDDLE_NAME || '  ' || PARTY_CONTACTS.PERSON_LAST_NAME CONTACT_FULL_NAME, PARTY_CONTACTS.SALUTATION SALUTATION, REL_PARTY.EMAIL_ADDRESS CONTACT_EMAIL_ADDRESS, ORG_CONT.JOB_TITLE CONTACT_JOB_TITLE, LEAD_CONTACT.CONTACT_PARTY_ID CONTACT_PARTY_ID, LEAD_CONTACT.PHONE_ID 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 CONTACT_PHONE_NUMBER, OPP. SALES_METHODOLOGY_ID SALES_METHODOLOGY_ID, SM.SALES_METHODOLOGY_NAME SALES_METHODOLOGY_NAME, OPP.VEHICLE_RESPONSE_CODE VEHICLE_RESPONSE_CODE, LOK1.MEANING VEHICLE_RESPONSE_MEANING, OPP.SOURCE_PROMOTION_ID SOURCE_PROMOTION_ID, OPP.ADDRESS_ID ADDRESS_ID, LOCATION.ADDRESS1 ADDRESS1, LOCATION.CITY CITY, LOCATION.STATE STATE, LOCATION.COUNTRY COUNTRY, LOCATION.PROVINCE PROVINCE, LOCATION.POSTAL_CODE POSTAL_CODE, LOCATION.ADDRESS1||' '||LOCATION.CITY||' '||LOCATION.STATE||' '|| LOCATION.COUNTRY||' '||LOCATION.POSTAL_CODE LOCATION, OPP.CLOSE_REASON CLOSE_REASON, OPP.CLOSE_COMPETITOR_ID CLOSE_COMPETITOR_ID, OPP.CLOSE_COMPETITOR_CODE CLOSE_COMPETITOR_CODE, OPP.CLOSE_COMPETITOR CLOSE_COMPETITOR, LOK3.MEANING CLOSE_COMMENT, SC.NAME SOURCE_NAME, SC.SOURCE_CODE SOURCE_CODE, OPP.TOTAL_REVENUE_OPP_FORECAST_AMT FORECAST_AMOUNT 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, HZ_PARTIES PARTY, AS_STATUSES_TL STT, HZ_LOCATIONS LOCATION, HZ_PARTY_SITES SITES, HZ_PARTIES REL_PARTY, ( select /*+ NO_MERGE */ distinct scd.lead_id, acc.salesforce_id from AS_ACCESSES_ALL acc , AS_SALES_CREDITS SCD where acc.OPEN_FLAG = 'Y' AND acc.salesforce_id = scd.salesforce_id AND SCD.LEAD_ID = ACC.LEAD_ID ) v1 , 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 = STT.STATUS_CODE AND STT.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 SS.LANGUAGE(+) = USERENV('LANG') AND RES.USER_ID(+) = OPP.LAST_UPDATED_BY 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 = v1.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 LOK1.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE' AND LOK3.LOOKUP_CODE(+) = OPP.CLOSE_REASON AND LOK3.LOOKUP_TYPE(+) = 'CLOSE_REASON'

View Text - HTML Formatted

SELECT /*+ FIRST_ROWS */ 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 LEAD_ID
, OPP.DESCRIPTION DESCRIPTION
, OPP.TOTAL_AMOUNT TOTAL_AMOUNT
, OPP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OPP.LAST_UPDATED_BY LAST_UPDATED_BY
, OPP.CUSTOMER_ID PARTY_ID
, PARTY.PARTY_NAME PARTY_NAME
, OPP.ORG_ID ORG_ID
, OPP.SALES_STAGE_ID SALES_STAGE_ID
, SS.NAME SALES_STAGE_NAME
, OPP.LEAD_NUMBER LEAD_NUMBER
, OPP.STATUS STATUS_CODE
, STT.MEANING STATUS_MEANING
, OPP.WIN_PROBABILITY WIN_PROBABILITY
, FP.MEANING PROBABILITY_MEANING
, V1.SALESFORCE_ID RESOURCE_ID
, 'OPPORTUNITY' OBJECT_CODE
, 'RS_INDIVIDUAL' RESOURCE_TYPE
, OPP.CUSTOMER_BUDGET CUSTOMER_BUDGET
, OPP.DECISION_DATE DECISION_DATE
, OPP.CREATION_DATE CREATION_DATE
, OPP.CHANNEL_CODE CHANNEL_CODE
, ASOCHANNELS.MEANING SALES_CHANNEL
, OPP.CURRENCY_CODE CURRENCY_CODE
, OPP.PARENT_PROJECT PARENT_PROJECT
, OPP.DECISION_TIMEFRAME_CODE 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 CONTACT_FIRST_NAME
, PARTY_CONTACTS.PERSON_LAST_NAME CONTACT_LAST_NAME
, PARTY_CONTACTS.PERSON_MIDDLE_NAME PERSON_MIDDLE_NAME
, PARTY_CONTACTS.PERSON_FIRST_NAME || ' ' || PARTY_CONTACTS.PERSON_MIDDLE_NAME || ' ' || PARTY_CONTACTS.PERSON_LAST_NAME CONTACT_FULL_NAME
, PARTY_CONTACTS.SALUTATION SALUTATION
, REL_PARTY.EMAIL_ADDRESS CONTACT_EMAIL_ADDRESS
, ORG_CONT.JOB_TITLE CONTACT_JOB_TITLE
, LEAD_CONTACT.CONTACT_PARTY_ID CONTACT_PARTY_ID
, LEAD_CONTACT.PHONE_ID 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 CONTACT_PHONE_NUMBER
, OPP. SALES_METHODOLOGY_ID SALES_METHODOLOGY_ID
, SM.SALES_METHODOLOGY_NAME SALES_METHODOLOGY_NAME
, OPP.VEHICLE_RESPONSE_CODE VEHICLE_RESPONSE_CODE
, LOK1.MEANING VEHICLE_RESPONSE_MEANING
, OPP.SOURCE_PROMOTION_ID SOURCE_PROMOTION_ID
, OPP.ADDRESS_ID ADDRESS_ID
, LOCATION.ADDRESS1 ADDRESS1
, LOCATION.CITY CITY
, LOCATION.STATE STATE
, LOCATION.COUNTRY COUNTRY
, LOCATION.PROVINCE PROVINCE
, LOCATION.POSTAL_CODE POSTAL_CODE
, LOCATION.ADDRESS1||' '||LOCATION.CITY||' '||LOCATION.STATE||' '|| LOCATION.COUNTRY||' '||LOCATION.POSTAL_CODE LOCATION
, OPP.CLOSE_REASON CLOSE_REASON
, OPP.CLOSE_COMPETITOR_ID CLOSE_COMPETITOR_ID
, OPP.CLOSE_COMPETITOR_CODE CLOSE_COMPETITOR_CODE
, OPP.CLOSE_COMPETITOR CLOSE_COMPETITOR
, LOK3.MEANING CLOSE_COMMENT
, SC.NAME SOURCE_NAME
, SC.SOURCE_CODE SOURCE_CODE
, OPP.TOTAL_REVENUE_OPP_FORECAST_AMT FORECAST_AMOUNT
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
, HZ_PARTIES PARTY
, AS_STATUSES_TL STT
, HZ_LOCATIONS LOCATION
, HZ_PARTY_SITES SITES
, HZ_PARTIES REL_PARTY
, ( SELECT /*+ NO_MERGE */ DISTINCT SCD.LEAD_ID
, ACC.SALESFORCE_ID
FROM AS_ACCESSES_ALL ACC
, AS_SALES_CREDITS SCD
WHERE ACC.OPEN_FLAG = 'Y'
AND ACC.SALESFORCE_ID = SCD.SALESFORCE_ID
AND SCD.LEAD_ID = ACC.LEAD_ID ) V1
, 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 = STT.STATUS_CODE
AND STT.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 SS.LANGUAGE(+) = USERENV('LANG')
AND RES.USER_ID(+) = OPP.LAST_UPDATED_BY
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 = V1.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 LOK1.LOOKUP_TYPE(+) = 'VEHICLE_RESPONSE_CODE'
AND LOK3.LOOKUP_CODE(+) = OPP.CLOSE_REASON
AND LOK3.LOOKUP_TYPE(+) = 'CLOSE_REASON'