View: AST_LS_EVENTS_V

View Text

SELECT EVT.EVENT_OFFER_ID
, EVT.EVENT_OFFER_NAME
, NULL EVENT_HEADER_ID
, EVT.DESCRIPTION
, LNG.NLS_LANGUAGE EVENT_LANGUAGE
, NVL((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' ))
, 0) REG_NUMBER_AVAILABLE
, NULL REG_INVITED_ONLY_FLAG
, NULL EVENT_VENUE_NAME
, EVT.ACTUAL_COST
, HZL.CITY||'
, '|| HZL.STATE ||'
, '|| HZL.COUNTRY EVENT_LOCATION
, HZL.CITY
, HZL.STATE
, HZL.PROVINCE
, HZL.COUNTRY
, LUT.MEANING EVENT_TYPE
, EVT.EVENT_START_DATE
, EVT.EVENT_END_DATE
, AUS.NAME USER_STATUS
, NULL COORDINATOR_PHONE
, EVC.CAMPAIGN_NAME
, EVC.SOURCE_CODE CAMPAIGN_SOURCE_CODE
FROM AMS_EVENT_OFFERS_VL EVT
, AMS_USER_STATUSES_VL AUS
, FND_LANGUAGES_VL LNG
, HZ_LOCATIONS HZL
, AMS_LOOKUPS LUT
, AMS_OBJECT_ASSOCIATIONS CMA
, AMS_CAMPAIGNS_VL EVC
WHERE EVT.EVENT_LEVEL='MAIN'
AND EVT.SYSTEM_STATUS_CODE = 'ACTIVE'
AND EVT.ACTIVE_FLAG = 'Y'
AND EVT.USER_STATUS_ID=AUS.USER_STATUS_ID
AND EVT.EVENT_LOCATION_ID=HZL.LOCATION_ID(+)
AND EVT.EVENT_LANGUAGE_CODE=LNG.LANGUAGE_CODE(+)
AND (TRUNC(NVL(EVT.REG_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(EVT.REG_END_DATE
, SYSDATE)) >= TRUNC(SYSDATE))
AND LUT.LOOKUP_TYPE(+)='AMS_EVENT_TYPE'
AND LUT.LOOKUP_CODE(+)=EVT.EVENT_TYPE_CODE
AND CMA.MASTER_OBJECT_ID (+) = EVT.EVENT_OFFER_ID
AND CMA.MASTER_OBJECT_TYPE(+) = 'EVEO'
AND CMA.USING_OBJECT_ID = EVC.CAMPAIGN_ID(+)
AND CMA.USING_OBJECT_TYPE(+) = 'CAMP'

Columns