View: AST_LM_OPP_EXPANDED_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_LM_OPP_EXPANDED_V

View Text

SELECT OPP.LEAD_ID
, OPP.LEAD_NUMBER
, OPP.CUSTOMER_ID
, OPP.ORG_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, PARTY.PARTY_TYPE
, OPP.ADDRESS_ID
, OPP.SALES_STAGE_ID
, ASS.DESCRIPTION SALES_STAGE
, NULL
, OPP.STATUS STATUS_CODE
, AST.MEANING STATUS
, OE.MEANING CHANNEL_CODE
, OPP.TOTAL_AMOUNT REVENUE_AMOUNT
, OPP.WIN_PROBABILITY
, OPP.PARENT_PROJECT
, OPP.TOTAL_AMOUNT
, OPP.CURRENCY_CODE
, OPP.DECISION_DATE
, OPP.LEAD_SOURCE_CODE
, OPP.SOURCE_PROMOTION_ID SOURCE_CODE_ID
, CAMP.SOURCE_CODE
, PARTY1.PARTY_ID
, PARTY1.PERSON_FIRST_NAME CONTACT_FIRST_NAME
, PARTY1.PERSON_LAST_NAME CONTACT_LAST_NAME
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.STATE
, HL.COUNTRY
, HL.POSTAL_CODE
, 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.DESCRIPTION OPPORTUNITY_NAME
, OPP.CREATION_DATE
, OPP.LAST_UPDATE_DATE
, OPP.PRM_REFERRAL_CODE
, HL.LOCATION_ID LOCATION_ID
, HPS.PARTY_SITE_ID PARTY_SITE_ID
, OPP.TOTAL_REVENUE_OPP_FORECAST_AMT
FROM AS_LEADS_ALL OPP
, AS_LEAD_CONTACTS_ALL LEAD_CONTACT
, HZ_PARTIES PARTY
, HZ_PARTIES PARTY1
, HZ_RELATIONSHIPS REL
, AMS_SOURCE_CODES CAMP
, AS_SALES_STAGES_ALL_TL ASS
, AS_STATUSES_TL AST
, OE_LOOKUPS OE
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
WHERE OPP.CUSTOMER_ID = PARTY.PARTY_ID
AND OPP.LEAD_ID = LEAD_CONTACT.LEAD_ID(+)
AND REL.PARTY_ID (+) = LEAD_CONTACT.CONTACT_PARTY_ID
AND PARTY1.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 LEAD_CONTACT.PRIMARY_CONTACT_FLAG (+) = 'Y'
AND OPP.SOURCE_PROMOTION_ID = CAMP.SOURCE_CODE_ID(+)
AND OPP.SALES_STAGE_ID = ASS.SALES_STAGE_ID(+)
AND ASS.LANGUAGE(+) = USERENV('LANG')
AND OPP.STATUS = AST.STATUS_CODE
AND AST.LANGUAGE= USERENV ('LANG')
AND OPP.CHANNEL_CODE = OE.LOOKUP_CODE(+)
AND OE.LOOKUP_TYPE(+)='SALES_CHANNEL'
AND OPP.ADDRESS_ID = HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND PARTY.STATUS IN('A'
, 'I')

Columns