VIEW: APPS.AST_SALESOPP_CODE_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.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_SOURCE_CODES 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.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_SOURCE_CODES 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'