View: AMS_P_EVENT_OFFERS_V

Product:	AMS - Marketing
Description:	This public view returns the general information about marketing event offerings.
Implementation/DBA Data:	APPS.AMS_P_EVENT_OFFERS_V

View Text

SELECT ''
, ''
, ''
, ''
, ''
, EVO.EVENT_OFFER_ID
, EVO.EVENT_OFFER_NAME
, EVO.DESCRIPTION
, EVO.SOURCE_CODE
, EVO.EVENT_TYPE_CODE
, EVO.INVENTORY_ITEM_ID
, ITEM.CONCATENATED_SEGMENTS
, ''
, ''
, ''
, ''
, ''
, EVO.EVENT_LOCATION_ID
, EVO.TIMEZONE_NAME
, EVO.CITY
, EVO.STATE
, EVO.COUNTRY
, EVO.EVENT_LEVEL
, EVO.USER_STATUS
, EVO.USER_STATUS_ID
, EVO.SYSTEM_STATUS_CODE
, EVO.EVENT_DELIVERY_METHOD_ID
, EVO.EVENT_DELIVERY_METHOD_ID
, EVO.EVENT_DELIVERY_METHOD_CODE
, EVO.EVENT_LANGUAGE_CODE
, EVO.EVENT_LANGUAGE
, EVO.URL
, EVO.COORDINATOR_ID
, JTFR.FULL_NAME
, EVO.PRIORITY_CODE
, EVO.CERTIFICATION_CREDIT_TYPE
, EVO.CERTIFICATION_CREDITS
, EVO.EVENT_DURATION
, EVO.EVENT_DURATION_UOM_CODE
, EVO.EVENT_START_DATE
, EVO.EVENT_START_DATE_TIME
, EVO.EVENT_END_DATE
, EVO.EVENT_END_DATE_TIME
, EVO.REG_START_DATE
, EVO.REG_START_TIME
, EVO.REG_END_DATE
, EVO.REG_END_TIME
, EVO.REG_EFFECTIVE_CAPACITY
, EVO.REG_MINIMUM_CAPACITY
, EVO.REG_MINIMUM_REQ_BY_DATE
, EVO.STREAM_TYPE_CODE
, EVO.EVENT_STANDALONE_FLAG
, EVO.REG_FROZEN_FLAG
, EVO.REG_REQUIRED_FLAG
, EVO.REG_CHARGE_FLAG
, EVO.REG_INVITED_ONLY_FLAG
, EVO.REG_WAITLIST_ALLOWED_FLAG
, EVO.REG_OVERBOOK_ALLOWED_FLAG
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='ENROLLED')
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='WAITLISTED')
, (EVO.REG_EFFECTIVE_CAPACITY-(SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='REGISTERED'))
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='REGISTERED')
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.SYSTEM_STATUS_CODE='CANCELLED')
, (SELECT COUNT(*)
FROM AMS_EVENT_REGISTRATIONS REG
WHERE REG.EVENT_OFFER_ID=EVO.EVENT_OFFER_ID
AND REG.ATTENDED_FLAG ='Y')
FROM AMS_EVENT_OFFERS_ONLY_V EVO
, AMS_JTF_RS_EMP_V JTFR
, MTL_SYSTEM_ITEMS_KFV ITEM
WHERE EVO.EVENT_LEVEL='MAIN'
AND EVO.ACTIVE_FLAG = 'Y'
AND EVO.COORDINATOR_ID= JTFR.RESOURCE_ID(+)
AND EVO.INVENTORY_ITEM_ID=ITEM.INVENTORY_ITEM_ID(+)
AND EVO.ORGANIZATION_ID=ITEM.ORGANIZATION_ID(+)

Columns