VIEW: APPS.AST_LM_OPP_EXPANDED_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')