eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OTFV_SCHEDULED_TRAINING_EVENTS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,ett.title event_name ,evt.comments event_comments ,evt.course_start_date event_start_date ,evt.course_end_date event_end_date ,evt.course_start_time event_start_time ,evt.course_end_time event_end_time ,evt.timezone time_zone ,evt.duration duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) duration_units ,ota_views_pkg.ota_get_places_available(evt.event_id) available_places ,orgT1.name training_center ,evt.enrolment_start_date enrolment_start_date ,evt.enrolment_end_date enrolment_end_date ,DECODE(evt.event_status,NULL,NULL, hr_bis.bis_decode_lookup('SCHEDULED_EVENT_STATUS',evt.event_status)) event_status ,DECODE(evt.user_status,NULL,NULL, hr_bis.bis_decode_lookup('EVENT_USER_STATUS',evt.user_status)) user_status ,evt.minimum_attendees minimum_attendees ,evt.maximum_attendees maximum_attendees ,evt.maximum_internal_attendees maximum_internal_attendees ,evt.standard_price standard_price ,DECODE(evt.secure_event_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO', decode(evt.secure_event_flag,'Y', 'N', 'N', 'Y'))) secure_event_flag ,DECODE(evt.public_event_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO', decode(evt.public_event_flag,'Y', 'N', 'N', 'Y'))) restrictions_apply ,DECODE(evt.event_status,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO', decode(evt.event_status,'F','Y','N'))) event_full_flag ,DECODE(evt.user_status,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',decode(evt.user_status,'PL','Y', 'N'))) event_tentative_flag ,tdt.name activity_type ,tvt.version_name activity_name ,tct.category category_name ,ota_utility.get_lang_name(ofr.language_code) language ,ven.vendor_name supplier_name ,orgT.name sponsoring_organization ,nvl( sum( decode( tdb.organization_id, null, 0, nvl( tdb.number_of_places, 1 ) ) ), 0 ) internal_student_count ,nvl( sum( decode( tdb.customer_id, null, 0, nvl( tdb.number_of_places, 1 ) ) ), 0 ) external_student_count ,sum( decode( tdb.organization_id, null, 0, decode( tdb.successful_attendance_flag,'Y',tdb.number_of_places, 0 ) ) ) successful_int_student_count ,sum( decode( tdb.customer_id,null, 0, decode( tdb.successful_attendance_flag,'Y',tdb.number_of_places, 0 ) ) ) successful_ext_student_count ,sum( decode( tdb.organization_id,null, 0, decode( bst.type, 'A', tdb.number_of_places, 'P', tdb.number_of_places,0 ) ) ) confirmed_int_student_count ,sum( decode( tdb.customer_id, null, 0, decode( bst.type, 'A', tdb.number_of_places, 'P', tdb.number_of_places, 0 ) ) ) confirmed_ext_student_count ,evt.duration_units duration_units_code ,evt.event_status event_status_code ,cur.name currency_code ,tad.activity_id activity_id ,tav.activity_version_id activity_version_id ,evt.business_group_id business_group_id ,evt.event_id event_id ,evt.training_center_id training_center_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM hr_all_organization_units_tl orgT ,ota_booking_status_types bst ,ota_delegate_bookings tdb ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,po_vendors ven ,fnd_currencies_vl cur ,hr_all_organization_units_tl bgrT ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,hr_all_organization_units_tl orgT1 ,ota_category_usages tcu ,ota_category_usages_tl tct WHERE evt.organization_id = orgT.organization_id (+) AND evt.training_center_id = orgT1.organization_id (+) AND decode(orgT1.organization_id,null,'X',orgT1.language)= decode(orgT1.organization_id,null,'X',userenv('LANG')) AND decode(orgT.organization_id, null,'X', orgT.language) = decode(orgT.organization_id, null,'X', userenv('LANG')) AND evt.event_id = tdb.event_id (+) AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') AND tdb.booking_status_type_id = bst.booking_status_type_id (+) AND evt.parent_offering_id = ofr.offering_id AND ofr.activity_version_id = tav.activity_version_id AND tav.activity_version_id = tvt.activity_version_id AND tvt.language = USERENV('LANG') AND tav.activity_id = tad.activity_id AND tad.activity_id = tdt.activity_id AND tdt.language = USERENV('LANG') AND evt.vendor_id = ven.vendor_id(+) AND evt.event_type = 'SCHEDULED' AND ofr.delivery_mode_id = tcu.category_usage_id (+) AND tcu.category_usage_id = tct.category_usage_id (+) AND tct.language (+) = USERENV('LANG') AND (tcu.type = 'DM' OR tcu.type is null) AND evt.currency_code = cur.currency_code(+) AND evt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND evt.business_group_id = NVL(ota_general.get_business_group_id,evt.business_group_id) GROUP BY bgrT.name ,ett.title ,evt.comments ,evt.course_start_date ,evt.course_end_date ,evt.course_start_time ,evt.course_end_time ,evt.timezone ,evt.duration ,evt.duration_units ,orgT1.name ,evt.enrolment_start_date ,evt.enrolment_end_date ,evt.event_status ,evt.user_status ,evt.minimum_attendees ,evt.maximum_attendees ,evt.maximum_internal_attendees ,evt.standard_price ,evt.secure_event_flag ,evt.public_event_flag ,evt.event_status ,evt.user_status ,tdt.name ,tvt.version_name ,tct.category ,cur.name ,ofr.language_code ,ven.vendor_name ,orgT.name ,tad.activity_id ,tav.activity_version_id ,evt.business_group_id ,evt.event_id ,evt.training_center_id ,tav.rco_id ,ofr.offering_id WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ETT.TITLE EVENT_NAME
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE EVENT_START_DATE
, EVT.COURSE_END_DATE EVENT_END_DATE
, EVT.COURSE_START_TIME EVENT_START_TIME
, EVT.COURSE_END_TIME EVENT_END_TIME
, EVT.TIMEZONE TIME_ZONE
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) DURATION_UNITS
, OTA_VIEWS_PKG.OTA_GET_PLACES_AVAILABLE(EVT.EVENT_ID) AVAILABLE_PLACES
, ORGT1.NAME TRAINING_CENTER
, EVT.ENROLMENT_START_DATE ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLMENT_END_DATE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) USER_STATUS
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.SECURE_EVENT_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'))) SECURE_EVENT_FLAG
, DECODE(EVT.PUBLIC_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.PUBLIC_EVENT_FLAG
, 'Y'
, 'N'
, 'N'
, 'Y'))) RESTRICTIONS_APPLY
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_STATUS
, 'F'
, 'Y'
, 'N'))) EVENT_FULL_FLAG
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.USER_STATUS
, 'PL'
, 'Y'
, 'N'))) EVENT_TENTATIVE_FLAG
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_NAME
, TCT.CATEGORY CATEGORY_NAME
, OTA_UTILITY.GET_LANG_NAME(OFR.LANGUAGE_CODE) LANGUAGE
, VEN.VENDOR_NAME SUPPLIER_NAME
, ORGT.NAME SPONSORING_ORGANIZATION
, NVL( SUM( DECODE( TDB.ORGANIZATION_ID
, NULL
, 0
, NVL( TDB.NUMBER_OF_PLACES
, 1 ) ) )
, 0 ) INTERNAL_STUDENT_COUNT
, NVL( SUM( DECODE( TDB.CUSTOMER_ID
, NULL
, 0
, NVL( TDB.NUMBER_OF_PLACES
, 1 ) ) )
, 0 ) EXTERNAL_STUDENT_COUNT
, SUM( DECODE( TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) SUCCESSFUL_INT_STUDENT_COUNT
, SUM( DECODE( TDB.CUSTOMER_ID
, NULL
, 0
, DECODE( TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) SUCCESSFUL_EXT_STUDENT_COUNT
, SUM( DECODE( TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) CONFIRMED_INT_STUDENT_COUNT
, SUM( DECODE( TDB.CUSTOMER_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0 ) ) ) CONFIRMED_EXT_STUDENT_COUNT
, EVT.DURATION_UNITS DURATION_UNITS_CODE
, EVT.EVENT_STATUS EVENT_STATUS_CODE
, CUR.NAME CURRENCY_CODE
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_BOOKING_STATUS_TYPES BST
, OTA_DELEGATE_BOOKINGS TDB
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, PO_VENDORS VEN
, FND_CURRENCIES_VL CUR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_CATEGORY_USAGES TCU
, OTA_CATEGORY_USAGES_TL TCT
WHERE EVT.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.ORGANIZATION_ID
, NULL
, 'X'
, ORGT1.LANGUAGE)= DECODE(ORGT1.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.EVENT_ID = TDB.EVENT_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID (+)
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID
AND TVT.LANGUAGE = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID
AND TDT.LANGUAGE = USERENV('LANG')
AND EVT.VENDOR_ID = VEN.VENDOR_ID(+)
AND EVT.EVENT_TYPE = 'SCHEDULED'
AND OFR.DELIVERY_MODE_ID = TCU.CATEGORY_USAGE_ID (+)
AND TCU.CATEGORY_USAGE_ID = TCT.CATEGORY_USAGE_ID (+)
AND TCT.LANGUAGE (+) = USERENV('LANG')
AND (TCU.TYPE = 'DM' OR TCU.TYPE IS NULL)
AND EVT.CURRENCY_CODE = CUR.CURRENCY_CODE(+)
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID) GROUP BY BGRT.NAME
, ETT.TITLE
, EVT.COMMENTS
, EVT.COURSE_START_DATE
, EVT.COURSE_END_DATE
, EVT.COURSE_START_TIME
, EVT.COURSE_END_TIME
, EVT.TIMEZONE
, EVT.DURATION
, EVT.DURATION_UNITS
, ORGT1.NAME
, EVT.ENROLMENT_START_DATE
, EVT.ENROLMENT_END_DATE
, EVT.EVENT_STATUS
, EVT.USER_STATUS
, EVT.MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE
, EVT.SECURE_EVENT_FLAG
, EVT.PUBLIC_EVENT_FLAG
, EVT.EVENT_STATUS
, EVT.USER_STATUS
, TDT.NAME
, TVT.VERSION_NAME
, TCT.CATEGORY
, CUR.NAME
, OFR.LANGUAGE_CODE
, VEN.VENDOR_NAME
, ORGT.NAME
, TAD.ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID
, EVT.BUSINESS_GROUP_ID
, EVT.EVENT_ID
, EVT.TRAINING_CENTER_ID
, TAV.RCO_ID
, OFR.OFFERING_ID WITH READ ONLY