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_TRAINING_EVENTS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,ett.title event_title ,evt.comments event_comments ,hr_bis.bis_decode_lookup('TRAINING_EVENT_TYPE',evt.event_type) event_type ,DECODE(evt.event_status,NULL,NULL, hr_bis.bis_decode_lookup('SCHEDULED_EVENT_STATUS',evt.event_status)) event_status ,to_char(evt.course_start_date,'IW') week_number ,evt.course_start_date course_start_date ,evt.course_end_date course_end_date ,evt.course_start_time course_start_time ,evt.course_end_time course_end_time ,evt.timezone timezone ,evt.duration duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) duration_units ,evt.enrolment_start_date enrollment_start_date ,evt.enrolment_end_date enrollment_end_date ,hr_bis.bis_decode_lookup('YES_NO', DECODE(evt.event_type, 'AD HOC', null, substr(ota_evt_shd.resource_booking_flag(evt.event_id),1,1))) resource_booked ,DECODE(evt.public_event_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',evt.public_event_flag)) public_event ,evt.minimum_attendees minimum_attendees ,evt.maximum_attendees maximum_attendees ,evt.maximum_internal_attendees maximum_internal_attendees ,evt.standard_price standard_price ,DECODE(evt.book_independent_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',evt.book_independent_flag)) book_independent ,evt.actual_cost actual_cost ,evt.budget_cost budget_cost ,ota_general.fnd_currency_name(evt.budget_currency_code) budget_cost_currency ,orgT1.name event_center ,DECODE(evt.development_event_type,NULL,NULL, hr_bis.bis_decode_lookup('DEV_EVENT_TYPE',evt.development_event_type)) development_event_type ,DECODE(evt.user_status,NULL,NULL, hr_bis.bis_decode_lookup('EVENT_USER_STATUS',evt.user_status)) user_status ,DECODE(evt.secure_event_flag,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',DECODE(evt.event_type,'DEVELOPMENT',null,'AD HOC',null,evt.secure_event_flag))) secured_event ,ota_general.hr_org_name(evt.organization_id) organization_name ,ota_utility.get_lang_name(ofr.language_code) language ,DECODE(evt.price_basis,NULL,NULL, hr_bis.bis_decode_lookup('EVENT_PRICE_BASIS',evt.price_basis)) price_basis ,DECODE(evt.category_code,NULL,NULL, hr_bis.bis_decode_lookup('ACTIVITY_CATEGORY',evt.category_code)) programme_code ,tvt.version_name activity_name ,tdt.name activity_type ,ota_evt_shd.invoiced_amount_total(evt.event_id) invoice_amount ,ven.vendor_name supplier_name ,ppa.name project_name ,ppa.segment1 project_number ,per.full_name owner_name ,tct.category category_name ,'_DF:OTA:OTA_EVENTS:evt' ,tcu.online_flag online_flag ,evt.business_group_id business_group_id ,evt.event_id event_id ,evt.parent_event_id parent_event_id ,evt.organization_id organization_id ,ofr.language_code language_code ,tav.activity_version_id activity_version_id ,tad.activity_id activity_id ,ven.vendor_id vendor_id ,evt.project_id project_id ,evt.line_id line_id ,evt.org_id org_id ,evt.training_center_id training_center_id ,evt.owner_id owner_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM po_vendors ven ,ota_activity_versions tav ,ota_activity_definitions tad ,ota_events evt ,ota_activity_versions_tl tvt ,ota_activity_definitions_tl tdt ,ota_events_tl ett ,ota_offerings ofr ,pa_projects ppa ,per_all_people_f per ,hr_all_organization_units_tl bgrT ,hr_all_organization_units_tl orgT1 ,ota_category_usages tcu ,ota_category_usages_tl tct WHERE evt.activity_version_id = tav.activity_version_id (+) AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = USERENV('LANG') AND evt.training_center_id = orgT1.organization_id (+) AND DECODE(orgT1.language,null,'X',orgT1.language) = DECODE(orgT1.language,null,'X',userenv('LANG')) AND evt.owner_id = per.person_id (+) AND SYSDATE BETWEEN per.effective_start_date (+) AND per.effective_end_date (+) AND tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language(+) = USERENV('LANG') AND evt.parent_offering_id = ofr.offering_id (+) 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.vendor_id = ven.vendor_id (+) AND evt.project_id = ppa.project_id (+) AND evt.business_group_id = NVL(ota_general.get_business_group_id,evt.business_group_id) AND evt.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ETT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, HR_BIS.BIS_DECODE_LOOKUP('TRAINING_EVENT_TYPE'
, EVT.EVENT_TYPE) EVENT_TYPE
, DECODE(EVT.EVENT_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('SCHEDULED_EVENT_STATUS'
, EVT.EVENT_STATUS)) EVENT_STATUS
, TO_CHAR(EVT.COURSE_START_DATE
, 'IW') WEEK_NUMBER
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.COURSE_START_TIME COURSE_START_TIME
, EVT.COURSE_END_TIME COURSE_END_TIME
, EVT.TIMEZONE TIMEZONE
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.ENROLMENT_START_DATE ENROLLMENT_START_DATE
, EVT.ENROLMENT_END_DATE ENROLLMENT_END_DATE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_TYPE
, 'AD HOC'
, NULL
, SUBSTR(OTA_EVT_SHD.RESOURCE_BOOKING_FLAG(EVT.EVENT_ID)
, 1
, 1))) RESOURCE_BOOKED
, DECODE(EVT.PUBLIC_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.PUBLIC_EVENT_FLAG)) PUBLIC_EVENT
, EVT.MINIMUM_ATTENDEES MINIMUM_ATTENDEES
, EVT.MAXIMUM_ATTENDEES MAXIMUM_ATTENDEES
, EVT.MAXIMUM_INTERNAL_ATTENDEES MAXIMUM_INTERNAL_ATTENDEES
, EVT.STANDARD_PRICE STANDARD_PRICE
, DECODE(EVT.BOOK_INDEPENDENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, EVT.BOOK_INDEPENDENT_FLAG)) BOOK_INDEPENDENT
, EVT.ACTUAL_COST ACTUAL_COST
, EVT.BUDGET_COST BUDGET_COST
, OTA_GENERAL.FND_CURRENCY_NAME(EVT.BUDGET_CURRENCY_CODE) BUDGET_COST_CURRENCY
, ORGT1.NAME EVENT_CENTER
, DECODE(EVT.DEVELOPMENT_EVENT_TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('DEV_EVENT_TYPE'
, EVT.DEVELOPMENT_EVENT_TYPE)) DEVELOPMENT_EVENT_TYPE
, DECODE(EVT.USER_STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_USER_STATUS'
, EVT.USER_STATUS)) USER_STATUS
, DECODE(EVT.SECURE_EVENT_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(EVT.EVENT_TYPE
, 'DEVELOPMENT'
, NULL
, 'AD HOC'
, NULL
, EVT.SECURE_EVENT_FLAG))) SECURED_EVENT
, OTA_GENERAL.HR_ORG_NAME(EVT.ORGANIZATION_ID) ORGANIZATION_NAME
, OTA_UTILITY.GET_LANG_NAME(OFR.LANGUAGE_CODE) LANGUAGE
, DECODE(EVT.PRICE_BASIS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('EVENT_PRICE_BASIS'
, EVT.PRICE_BASIS)) PRICE_BASIS
, DECODE(EVT.CATEGORY_CODE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('ACTIVITY_CATEGORY'
, EVT.CATEGORY_CODE)) PROGRAMME_CODE
, TVT.VERSION_NAME ACTIVITY_NAME
, TDT.NAME ACTIVITY_TYPE
, OTA_EVT_SHD.INVOICED_AMOUNT_TOTAL(EVT.EVENT_ID) INVOICE_AMOUNT
, VEN.VENDOR_NAME SUPPLIER_NAME
, PPA.NAME PROJECT_NAME
, PPA.SEGMENT1 PROJECT_NUMBER
, PER.FULL_NAME OWNER_NAME
, TCT.CATEGORY CATEGORY_NAME
, '_DF:OTA:OTA_EVENTS:EVT'
, TCU.ONLINE_FLAG ONLINE_FLAG
, EVT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, EVT.PARENT_EVENT_ID PARENT_EVENT_ID
, EVT.ORGANIZATION_ID ORGANIZATION_ID
, OFR.LANGUAGE_CODE LANGUAGE_CODE
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, VEN.VENDOR_ID VENDOR_ID
, EVT.PROJECT_ID PROJECT_ID
, EVT.LINE_ID LINE_ID
, EVT.ORG_ID ORG_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, EVT.OWNER_ID OWNER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM PO_VENDORS VEN
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_EVENTS EVT
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, PA_PROJECTS PPA
, PER_ALL_PEOPLE_F PER
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_CATEGORY_USAGES TCU
, OTA_CATEGORY_USAGES_TL TCT
WHERE EVT.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, ORGT1.LANGUAGE) = DECODE(ORGT1.LANGUAGE
, NULL
, 'X'
, USERENV('LANG'))
AND EVT.OWNER_ID = PER.PERSON_ID (+)
AND SYSDATE BETWEEN PER.EFFECTIVE_START_DATE (+)
AND PER.EFFECTIVE_END_DATE (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE(+) = USERENV('LANG')
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID (+)
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.VENDOR_ID = VEN.VENDOR_ID (+)
AND EVT.PROJECT_ID = PPA.PROJECT_ID (+)
AND EVT.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, EVT.BUSINESS_GROUP_ID)
AND EVT.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG') WITH READ ONLY