VIEW: APPS.AMS_P_EVENT_OFFERS_V

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)