VIEW: APPS.AST_LM_EVENTS_SRC_V

Source

View Text - Preformatted

SELECT EVT.EVENT_OFFER_ID , EVT.EVENT_OFFER_NAME , EVT.EVENT_HEADER_ID , EVH.EVENT_HEADER_NAME , EVT.DESCRIPTION , EVT.EVENT_LANGUAGE_CODE , LNG.NLS_LANGUAGE EVENT_LANGUAGE , EVT.REG_EFFECTIVE_CAPACITY , ( EVT.REG_EFFECTIVE_CAPACITY - ( SELECT COUNT(*) FROM AMS_EVENT_REGISTRATIONS REG WHERE REG.EVENT_OFFER_ID=EVT.EVENT_OFFER_ID AND REG.SYSTEM_STATUS_CODE='REGISTERED' )) REG_NUMBER_AVAILABLE , EVT.REG_INVITED_ONLY_FLAG , EVT.REG_REQUIRED_FLAG , EVT.EVENT_VENUE_ID , HZP.PARTY_NAME EVENT_VENUE_NAME, EVT.FORECASTED_REVENUE, EVT.FORECASTED_COST , EVT.ACTUAL_REVENUE , EVT.ACTUAL_COST , EVT.EVENT_LOCATION_ID , HZL.CITY||','|| HZL.STATE ||','|| HZL.COUNTRY EVENT_LOCATION, HZL.CITY , HZL.STATE , HZL.PROVINCE , HZL.COUNTRY , EVT.EVENT_TYPE_CODE , LUT.MEANING EVENT_TYPE , EVT.EVENT_START_DATE , EVT.EVENT_START_DATE_TIME, EVT.EVENT_END_DATE , EVT.EVENT_END_DATE_TIME , EVT.USER_STATUS_ID , AUS.NAME USER_STATUS , EVT.URL , EVT.PHONE , EVT.EMAIL , EVT.OWNER_USER_ID COORDINATOR_ID, EVT.ACTIVE_FLAG , RES.FULL_NAME COORDINATOR_NAME , RES.PHONE COORDINATOR_PHONE , evc.source_code_id CAMPAIGN_ID, evc.name CAMPAIGN_NAME , evc.source_code CAMPAIGN_SOURCE_CODE , EVT.ATTRIBUTE_CATEGORY , EVT.ATTRIBUTE1, EVT.ATTRIBUTE2 , EVT.ATTRIBUTE3 , EVT.ATTRIBUTE4 , EVT.ATTRIBUTE5 , EVT.ATTRIBUTE6 , EVT.ATTRIBUTE7 , EVT.ATTRIBUTE8 , EVT.ATTRIBUTE9 , EVT.ATTRIBUTE10 , EVT.ATTRIBUTE11 , EVT.ATTRIBUTE12 , EVT.ATTRIBUTE13 , EVT.ATTRIBUTE14 , EVT.ATTRIBUTE15 FROM AMS_EVENT_OFFERS_VL EVT, AMS_EVENT_HEADERS_VL EVH , AMS_USER_STATUSES_VL AUS , FND_LANGUAGES_VL LNG , HZ_LOCATIONS HZL , AMS_VENUES_V AVN , HZ_PARTIES HZP , AMS_LOOKUPS LUT , JTF_RS_RES_EMP_VL RES , ams_p_source_codes_v evc WHERE EVT.EVENT_LEVEL='MAIN' AND EVT.SYSTEM_STATUS_CODE='ACTIVE' AND EVT.ACTIVE_FLAG='Y' AND EVT.EVENT_HEADER_ID=EVH.EVENT_HEADER_ID(+) AND EVT.USER_STATUS_ID=AUS.USER_STATUS_ID AND EVT.EVENT_LANGUAGE_CODE=LNG.LANGUAGE_CODE(+) AND LUT.LOOKUP_TYPE(+)='AMS_EVENT_TYPE' AND LUT.LOOKUP_CODE(+)=EVT.EVENT_TYPE_CODE AND EVT.EVENT_LOCATION_ID=HZL.LOCATION_ID(+) AND EVT.EVENT_VENUE_ID=AVN.VENUE_ID(+) AND avn.party_id = hzp.party_id(+) AND ((evc.related_source_id = evt.event_offer_id AND evc.related_source_object IN('EVEO','EONE') AND evc.SOURCE_TYPE = 'CAMP') or (evc.related_source_id = evt.event_header_id AND evc.related_source_object = 'EVEH' AND evc.SOURCE_TYPE = 'CAMP') or (evc.OBJECT_ID = evt.event_offer_id AND evc.SOURCE_TYPE = 'EVEO') or (evc.OBJECT_ID = evt.event_offer_id AND evc.SOURCE_TYPE = 'EONE') or (evc.OBJECT_ID = evt.event_header_id AND evc.SOURCE_TYPE = 'EVEH')) AND evc.status in ('ACTIVE','ON_HOLD','ONHOLD','COMPLETED') AND evc.source_type != 'OFFR' AND RES.RESOURCE_ID (+) = EVT.OWNER_USER_ID

View Text - HTML Formatted

SELECT EVT.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, EVT.EVENT_HEADER_ID
, EVH.EVENT_HEADER_NAME
, EVT.DESCRIPTION
, EVT.EVENT_LANGUAGE_CODE
, LNG.NLS_LANGUAGE EVENT_LANGUAGE
, EVT.REG_EFFECTIVE_CAPACITY
, ( EVT.REG_EFFECTIVE_CAPACITY - ( SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVT.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='REGISTERED' )) REG_NUMBER_AVAILABLE
, EVT.REG_INVITED_ONLY_FLAG
, EVT.REG_REQUIRED_FLAG
, EVT.EVENT_VENUE_ID
, HZP.PARTY_NAME EVENT_VENUE_NAME
, EVT.FORECASTED_REVENUE
, EVT.FORECASTED_COST
, EVT.ACTUAL_REVENUE
, EVT.ACTUAL_COST
, EVT.EVENT_LOCATION_ID
, HZL.CITY||'
, '|| HZL.STATE ||'
, '|| HZL.COUNTRY EVENT_LOCATION
, HZL.CITY
, HZL.STATE
, HZL.PROVINCE
, HZL.COUNTRY
, EVT.EVENT_TYPE_CODE
, LUT.MEANING EVENT_TYPE
, EVT.EVENT_START_DATE
, EVT.EVENT_START_DATE_TIME
, EVT.EVENT_END_DATE
, EVT.EVENT_END_DATE_TIME
, EVT.USER_STATUS_ID
, AUS.NAME USER_STATUS
, EVT.URL
, EVT.PHONE
, EVT.EMAIL
, EVT.OWNER_USER_ID COORDINATOR_ID
, EVT.ACTIVE_FLAG
, RES.FULL_NAME COORDINATOR_NAME
, RES.PHONE COORDINATOR_PHONE
, EVC.SOURCE_CODE_ID CAMPAIGN_ID
, EVC.NAME CAMPAIGN_NAME
, EVC.SOURCE_CODE CAMPAIGN_SOURCE_CODE
, EVT.ATTRIBUTE_CATEGORY
, EVT.ATTRIBUTE1
, EVT.ATTRIBUTE2
, EVT.ATTRIBUTE3
, EVT.ATTRIBUTE4
, EVT.ATTRIBUTE5
, EVT.ATTRIBUTE6
, EVT.ATTRIBUTE7
, EVT.ATTRIBUTE8
, EVT.ATTRIBUTE9
, EVT.ATTRIBUTE10
, EVT.ATTRIBUTE11
, EVT.ATTRIBUTE12
, EVT.ATTRIBUTE13
, EVT.ATTRIBUTE14
, EVT.ATTRIBUTE15
FROM AMS_EVENT_OFFERS_VL EVT
, AMS_EVENT_HEADERS_VL EVH
, AMS_USER_STATUSES_VL AUS
, FND_LANGUAGES_VL LNG
, HZ_LOCATIONS HZL
, AMS_VENUES_V AVN
, HZ_PARTIES HZP
, AMS_LOOKUPS LUT
, JTF_RS_RES_EMP_VL RES
, AMS_P_SOURCE_CODES_V EVC
WHERE EVT.EVENT_LEVEL='MAIN'
AND EVT.SYSTEM_STATUS_CODE='ACTIVE'
AND EVT.ACTIVE_FLAG='Y'
AND EVT.EVENT_HEADER_ID=EVH.EVENT_HEADER_ID(+)
AND EVT.USER_STATUS_ID=AUS.USER_STATUS_ID
AND EVT.EVENT_LANGUAGE_CODE=LNG.LANGUAGE_CODE(+)
AND LUT.LOOKUP_TYPE(+)='AMS_EVENT_TYPE'
AND LUT.LOOKUP_CODE(+)=EVT.EVENT_TYPE_CODE
AND EVT.EVENT_LOCATION_ID=HZL.LOCATION_ID(+)
AND EVT.EVENT_VENUE_ID=AVN.VENUE_ID(+)
AND AVN.PARTY_ID = HZP.PARTY_ID(+)
AND ((EVC.RELATED_SOURCE_ID = EVT.EVENT_OFFER_ID
AND EVC.RELATED_SOURCE_OBJECT IN('EVEO'
, 'EONE')
AND EVC.SOURCE_TYPE = 'CAMP') OR (EVC.RELATED_SOURCE_ID = EVT.EVENT_HEADER_ID
AND EVC.RELATED_SOURCE_OBJECT = 'EVEH'
AND EVC.SOURCE_TYPE = 'CAMP') OR (EVC.OBJECT_ID = EVT.EVENT_OFFER_ID
AND EVC.SOURCE_TYPE = 'EVEO') OR (EVC.OBJECT_ID = EVT.EVENT_OFFER_ID
AND EVC.SOURCE_TYPE = 'EONE') OR (EVC.OBJECT_ID = EVT.EVENT_HEADER_ID
AND EVC.SOURCE_TYPE = 'EVEH'))
AND EVC.STATUS IN ('ACTIVE'
, 'ON_HOLD'
, 'ONHOLD'
, 'COMPLETED')
AND EVC.SOURCE_TYPE != 'OFFR'
AND RES.RESOURCE_ID (+) = EVT.OWNER_USER_ID