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_INT_TRAINING_EVENT_ENROLS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name , del.full_name person_name , ett.title event_name , evt.comments event_comments , evt.event_type event_type , evt.timezone time_zone , evt.course_start_date course_start_date , evt.course_start_time course_start_time , evt.course_end_date course_end_date , evt.course_end_time course_end_time , tdb.date_booking_placed date_booking_placed , orgT1.name training_center , tdb.attendance_result attendance_result , DECODE(tdb.failure_reason,NULL,NULL ,hr_bis.bis_decode_lookup ('DELEGATE_FAILURE_REASON',tdb.failure_reason)) failure_reason , DECODE(tdb.successful_attendance_flag ,NULL ,DECODE(ope.completed_date,NULL,hr_bis.bis_decode_lookup('YES_NO','N') ,hr_bis.bis_decode_lookup('YES_NO','Y')) ,hr_bis.bis_decode_lookup('YES_NO',tdb.successful_attendance_flag)) successful_attendance_flag , DECODE(evt.offering_id ,NULL ,TO_CHAR(TRUNC(SYSDATE)+(ope.time)/86400, 'HH24:Mi:SS') ,tdb.total_training_time) total_training_time , NVL(hr_bis.bis_decode_lookup('OTA_CONTENT_PLAYER_STATUS',tdb.content_player_status) ,hr_bis.bis_decode_lookup('OTA_CONTENT_PLAYER_STATUS',ope.lesson_status)) player_status , NVL(tdb.score,DECODE(ope.score, -1000, NULL, ope.score)) test_score , NVL(tdb.completed_content ,(SELECT COUNT(DISTINCT ope1.learning_object_id) FROM ota_performances ope1 , ota_learning_objects olo1 WHERE ope1.learning_object_id = olo1.learning_object_id AND ope1.user_id = tdb.delegate_person_id CONNECT BY prior olo1.learning_object_id = olo1.parent_learning_object_id START WITH olo1.learning_object_id = ofr.learning_object_id)) number_of_modules_completed , NVL(tdb.total_content ,(SELECT COUNT(olo1.learning_object_id) FROM ota_learning_objects olo1 CONNECT BY prior olo1.learning_object_id = olo1.parent_learning_object_id START WITH olo1.learning_object_id = ofr.learning_object_id)) total_modules , DECODE(del.current_employee_flag,NULL,NULL ,hr_bis.bis_decode_lookup('YES_NO',del.current_employee_flag)) current_employee_flag , DECODE(tdb.internal_booking_flag,NULL,NULL ,hr_bis.bis_decode_lookup('YES_NO',tdb.internal_booking_flag)) internal_booking_flag , asg.assignment_number assignment_number , org1T.name assignment_organization , locT.location_code assignment_location , jei.jei_information1 assignment_job_category , job.name assignment_job , btt.name booking_status , tdt.name activity_type , tvt.version_name activity_name , tct.category category_name , ota_utility.get_lang_name(ofr.language_code) language , orgT.name organization_name , tdb.number_of_places number_of_places , (tdb.number_of_places * DECODE(tdb.successful_attendance_flag,'Y',1,'N',0,0)) number_of_successful_places , tdb.comments enrolment_comments , tdb.special_booking_instructions special_requirements , DECODE(tdb.source_of_booking,NULL,NULL ,hr_bis.bis_decode_lookup('BOOKING_SOURCE', tdb.source_of_booking)) source_of_booking_meaning , DECODE(tdb.corespondent,NULL,NULL ,hr_bis.bis_decode_lookup('CORRESPONDENT',tdb.corespondent)) correspondence_meaning , tdb.delegate_contact_phone correspondence_phone , tdb.delegate_contact_fax correspondence_fax , tdb.delegate_contact_email correspondence_email , DECODE(tdb.person_address_type ,'E', addr.address_line1 || DECODE(addr.address_line1,NULL,'',', ') || addr.address_line2 || DECODE(addr.address_line2,NULL,'',', ') || addr.address_line3 || DECODE(addr.address_line3,NULL,'',', ') || addr.town_or_city || DECODE(addr.town_or_city,NULL,'',', ') || addr.region_1 || DECODE(addr.region_1,NULL,'',', ') || addr.region_2 || DECODE(addr.region_2,NULL,'' ,', ') || addr.region_3 || DECODE(addr.region_3,NULL,'',', ') || addr.postal_code || DECODE(addr.postal_code,NULL,'',', ') || addr.country || DECODE(addr.country,NULL,'' ,',') ,'I', DECODE(tdb.corespondent,'S', asg.internal_address_line || DECODE(asg.internal_address_line,NULL,'',', ') || hr_general.hr_lookup_locations(asg.location_id) ,'C', asg2.internal_address_line || DECODE(asg2.internal_address_line,NULL,'',', ') || hr_general.hr_lookup_locations(asg2.location_id),NULL)) correspondence_address , '_DF:OTA:OTA_DELEGATE_BOOKINGS:tdb' , tav.activity_id activity_id , tav.activity_version_id activity_version_id , tdb.delegate_assignment_id assignment_id , tdb.booking_id enrollment_id , tdb.booking_status_type_id booking_status_type_id , tdb.business_group_id business_group_id , evt.event_id event_id , tdb.organization_id organization_id , tdb.delegate_person_id person_id , evt.training_center_id training_center_id , tav.rco_id rco_id , ofr.offering_id offering_id FROM hr_all_organization_units_tl org1T , ota_activity_versions tav , ota_activity_versions_tl tvt , ota_activity_definitions tad , ota_activity_definitions_tl tdt , per_all_assignments_f asg , hr_all_organization_units_tl bgrT , ota_booking_status_types bst , ota_booking_status_types_tl btt , per_all_people_f del , ota_events evt , ota_events_tl ett , ota_offerings ofr , ota_performances ope , hr_all_organization_units_tl orgT , ota_delegate_bookings tdb , hr_locations_all_tl locT , per_jobs job , per_job_extra_info jei , per_addresses addr , per_all_assignments_f asg2 , hr_all_organization_units_tl orgT1 , ota_category_usages tcu , ota_category_usages_tl tct WHERE tdb.booking_status_type_id = bst.booking_status_type_id AND bst.booking_status_type_id = btt.booking_status_type_id AND btt.language = USERENV('LANG') 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 tdb.event_id = evt.event_id AND evt.event_id = ett.event_id AND ett.language = USERENV('LANG') AND evt.parent_offering_id = ofr.offering_id (+) AND ofr.learning_object_id = ope.learning_object_id (+) AND DECODE(ofr.learning_object_id,NULL,'X',tdb.delegate_person_id) = DECODE(ofr.learning_object_id,NULL,'X',ope.user_id) AND ofr.activity_version_id = tav.activity_version_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND DECODE(tdt.activity_id,NULL,'X',tdt.language) = DECODE(tdt.activity_id,NULL,'X',USERENV('LANG')) AND DECODE(tvt.activity_version_id,NULL,'X',tvt.language) = DECODE(tvt.activity_version_id,NULL,'X',USERENV('LANG')) AND tdb.organization_id = orgT.organization_id (+) AND DECODE(orgT.organization_id,NULL,'X',orgT.language) = DECODE(orgT.organization_id,NULL,'X',USERENV('LANG')) AND ofr.delivery_mode_id = tcu.category_usage_id (+) AND tcu.category_usage_id = tct.category_usage_id (+) AND DECODE(tct.category_usage_id,NULL,'X',tct.language) = DECODE(tct.category_usage_id,NULL,'X',USERENV('LANG')) AND (tcu.type = 'DM' OR tcu.type IS NULL) AND tdb.organization_id = orgT.organization_id (+) AND orgT.language (+) = USERENV('LANG') AND tdb.delegate_person_id = del.person_id AND TRUNC(SYSDATE) BETWEEN del.effective_start_date AND del.effective_end_date AND (asg.rowid (+) = ota_tdb_bus.assignment_ok('STUDENT',tdb.delegate_assignment_id,tdb.event_id,tdb.date_booking_placed)) AND asg.organization_id = org1T.organization_id (+) AND DECODE(org1T.organization_id,NULL,'X',org1T.language) = DECODE(org1T.organization_id,NULL,'X',USERENV('LANG')) AND asg.location_id = locT.location_id (+) AND DECODE(locT.location_id,NULL,'X',locT.language) = DECODE(locT.location_id,NULL,'X',USERENV('LANG')) AND asg.job_id = jei.job_id (+) AND asg.job_id = job.job_id (+) AND tdb.business_group_id = bgrT.organization_id AND bgrT.language = USERENV('LANG') AND tdb.business_group_id = NVL(ota_general.get_business_group_id, tdb.business_group_id) AND tdb.person_address_id = addr.address_id (+) AND tdb.sponsor_assignment_id = asg2.assignment_id (+) AND tdb.date_booking_placed BETWEEN asg2.effective_start_date (+) and asg2.effective_end_date (+) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DEL.FULL_NAME PERSON_NAME
, ETT.TITLE EVENT_NAME
, EVT.COMMENTS EVENT_COMMENTS
, EVT.EVENT_TYPE EVENT_TYPE
, EVT.TIMEZONE TIME_ZONE
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_START_TIME COURSE_START_TIME
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.COURSE_END_TIME COURSE_END_TIME
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, ORGT1.NAME TRAINING_CENTER
, TDB.ATTENDANCE_RESULT ATTENDANCE_RESULT
, DECODE(TDB.FAILURE_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP ('DELEGATE_FAILURE_REASON'
, TDB.FAILURE_REASON)) FAILURE_REASON
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, NULL
, DECODE(OPE.COMPLETED_DATE
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'N')
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, 'Y'))
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.SUCCESSFUL_ATTENDANCE_FLAG)) SUCCESSFUL_ATTENDANCE_FLAG
, DECODE(EVT.OFFERING_ID
, NULL
, TO_CHAR(TRUNC(SYSDATE)+(OPE.TIME)/86400
, 'HH24:MI:SS')
, TDB.TOTAL_TRAINING_TIME) TOTAL_TRAINING_TIME
, NVL(HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, OPE.LESSON_STATUS)) PLAYER_STATUS
, NVL(TDB.SCORE
, DECODE(OPE.SCORE
, -1000
, NULL
, OPE.SCORE)) TEST_SCORE
, NVL(TDB.COMPLETED_CONTENT
, (SELECT COUNT(DISTINCT OPE1.LEARNING_OBJECT_ID)
FROM OTA_PERFORMANCES OPE1
, OTA_LEARNING_OBJECTS OLO1
WHERE OPE1.LEARNING_OBJECT_ID = OLO1.LEARNING_OBJECT_ID
AND OPE1.USER_ID = TDB.DELEGATE_PERSON_ID CONNECT BY PRIOR OLO1.LEARNING_OBJECT_ID = OLO1.PARENT_LEARNING_OBJECT_ID START WITH OLO1.LEARNING_OBJECT_ID = OFR.LEARNING_OBJECT_ID)) NUMBER_OF_MODULES_COMPLETED
, NVL(TDB.TOTAL_CONTENT
, (SELECT COUNT(OLO1.LEARNING_OBJECT_ID)
FROM OTA_LEARNING_OBJECTS OLO1 CONNECT BY PRIOR OLO1.LEARNING_OBJECT_ID = OLO1.PARENT_LEARNING_OBJECT_ID START WITH OLO1.LEARNING_OBJECT_ID = OFR.LEARNING_OBJECT_ID)) TOTAL_MODULES
, DECODE(DEL.CURRENT_EMPLOYEE_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DEL.CURRENT_EMPLOYEE_FLAG)) CURRENT_EMPLOYEE_FLAG
, DECODE(TDB.INTERNAL_BOOKING_FLAG
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, TDB.INTERNAL_BOOKING_FLAG)) INTERNAL_BOOKING_FLAG
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ORG1T.NAME ASSIGNMENT_ORGANIZATION
, LOCT.LOCATION_CODE ASSIGNMENT_LOCATION
, JEI.JEI_INFORMATION1 ASSIGNMENT_JOB_CATEGORY
, JOB.NAME ASSIGNMENT_JOB
, BTT.NAME BOOKING_STATUS
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_NAME
, TCT.CATEGORY CATEGORY_NAME
, OTA_UTILITY.GET_LANG_NAME(OFR.LANGUAGE_CODE) LANGUAGE
, ORGT.NAME ORGANIZATION_NAME
, TDB.NUMBER_OF_PLACES NUMBER_OF_PLACES
, (TDB.NUMBER_OF_PLACES * DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, 1
, 'N'
, 0
, 0)) NUMBER_OF_SUCCESSFUL_PLACES
, TDB.COMMENTS ENROLMENT_COMMENTS
, TDB.SPECIAL_BOOKING_INSTRUCTIONS SPECIAL_REQUIREMENTS
, DECODE(TDB.SOURCE_OF_BOOKING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('BOOKING_SOURCE'
, TDB.SOURCE_OF_BOOKING)) SOURCE_OF_BOOKING_MEANING
, DECODE(TDB.CORESPONDENT
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('CORRESPONDENT'
, TDB.CORESPONDENT)) CORRESPONDENCE_MEANING
, TDB.DELEGATE_CONTACT_PHONE CORRESPONDENCE_PHONE
, TDB.DELEGATE_CONTACT_FAX CORRESPONDENCE_FAX
, TDB.DELEGATE_CONTACT_EMAIL CORRESPONDENCE_EMAIL
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE1 || DECODE(ADDR.ADDRESS_LINE1
, NULL
, ''
, '
, ') || ADDR.ADDRESS_LINE2 || DECODE(ADDR.ADDRESS_LINE2
, NULL
, ''
, '
, ') || ADDR.ADDRESS_LINE3 || DECODE(ADDR.ADDRESS_LINE3
, NULL
, ''
, '
, ') || ADDR.TOWN_OR_CITY || DECODE(ADDR.TOWN_OR_CITY
, NULL
, ''
, '
, ') || ADDR.REGION_1 || DECODE(ADDR.REGION_1
, NULL
, ''
, '
, ') || ADDR.REGION_2 || DECODE(ADDR.REGION_2
, NULL
, ''
, '
, ') || ADDR.REGION_3 || DECODE(ADDR.REGION_3
, NULL
, ''
, '
, ') || ADDR.POSTAL_CODE || DECODE(ADDR.POSTAL_CODE
, NULL
, ''
, '
, ') || ADDR.COUNTRY || DECODE(ADDR.COUNTRY
, NULL
, ''
, '
, ')
, 'I'
, DECODE(TDB.CORESPONDENT
, 'S'
, ASG.INTERNAL_ADDRESS_LINE || DECODE(ASG.INTERNAL_ADDRESS_LINE
, NULL
, ''
, '
, ') || HR_GENERAL.HR_LOOKUP_LOCATIONS(ASG.LOCATION_ID)
, 'C'
, ASG2.INTERNAL_ADDRESS_LINE || DECODE(ASG2.INTERNAL_ADDRESS_LINE
, NULL
, ''
, '
, ') || HR_GENERAL.HR_LOOKUP_LOCATIONS(ASG2.LOCATION_ID)
, NULL)) CORRESPONDENCE_ADDRESS
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TAV.ACTIVITY_ID ACTIVITY_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TDB.DELEGATE_ASSIGNMENT_ID ASSIGNMENT_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.EVENT_ID EVENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL ORG1T
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, PER_ALL_ASSIGNMENTS_F ASG
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_TYPES_TL BTT
, PER_ALL_PEOPLE_F DEL
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, OTA_PERFORMANCES OPE
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, OTA_DELEGATE_BOOKINGS TDB
, HR_LOCATIONS_ALL_TL LOCT
, PER_JOBS JOB
, PER_JOB_EXTRA_INFO JEI
, PER_ADDRESSES ADDR
, PER_ALL_ASSIGNMENTS_F ASG2
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, OTA_CATEGORY_USAGES TCU
, OTA_CATEGORY_USAGES_TL TCT
WHERE TDB.BOOKING_STATUS_TYPE_ID = BST.BOOKING_STATUS_TYPE_ID
AND BST.BOOKING_STATUS_TYPE_ID = BTT.BOOKING_STATUS_TYPE_ID
AND BTT.LANGUAGE = USERENV('LANG')
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 TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG')
AND EVT.PARENT_OFFERING_ID = OFR.OFFERING_ID (+)
AND OFR.LEARNING_OBJECT_ID = OPE.LEARNING_OBJECT_ID (+)
AND DECODE(OFR.LEARNING_OBJECT_ID
, NULL
, 'X'
, TDB.DELEGATE_PERSON_ID) = DECODE(OFR.LEARNING_OBJECT_ID
, NULL
, 'X'
, OPE.USER_ID)
AND OFR.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND DECODE(TDT.ACTIVITY_ID
, NULL
, 'X'
, TDT.LANGUAGE) = DECODE(TDT.ACTIVITY_ID
, NULL
, 'X'
, USERENV('LANG'))
AND DECODE(TVT.ACTIVITY_VERSION_ID
, NULL
, 'X'
, TVT.LANGUAGE) = DECODE(TVT.ACTIVITY_VERSION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, ORGT.LANGUAGE) = DECODE(ORGT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND OFR.DELIVERY_MODE_ID = TCU.CATEGORY_USAGE_ID (+)
AND TCU.CATEGORY_USAGE_ID = TCT.CATEGORY_USAGE_ID (+)
AND DECODE(TCT.CATEGORY_USAGE_ID
, NULL
, 'X'
, TCT.LANGUAGE) = DECODE(TCT.CATEGORY_USAGE_ID
, NULL
, 'X'
, USERENV('LANG'))
AND (TCU.TYPE = 'DM' OR TCU.TYPE IS NULL)
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN DEL.EFFECTIVE_START_DATE
AND DEL.EFFECTIVE_END_DATE
AND (ASG.ROWID (+) = OTA_TDB_BUS.ASSIGNMENT_OK('STUDENT'
, TDB.DELEGATE_ASSIGNMENT_ID
, TDB.EVENT_ID
, TDB.DATE_BOOKING_PLACED))
AND ASG.ORGANIZATION_ID = ORG1T.ORGANIZATION_ID (+)
AND DECODE(ORG1T.ORGANIZATION_ID
, NULL
, 'X'
, ORG1T.LANGUAGE) = DECODE(ORG1T.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND ASG.LOCATION_ID = LOCT.LOCATION_ID (+)
AND DECODE(LOCT.LOCATION_ID
, NULL
, 'X'
, LOCT.LANGUAGE) = DECODE(LOCT.LOCATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND ASG.JOB_ID = JEI.JOB_ID (+)
AND ASG.JOB_ID = JOB.JOB_ID (+)
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID)
AND TDB.PERSON_ADDRESS_ID = ADDR.ADDRESS_ID (+)
AND TDB.SPONSOR_ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID (+)
AND TDB.DATE_BOOKING_PLACED BETWEEN ASG2.EFFECTIVE_START_DATE (+)
AND ASG2.EFFECTIVE_END_DATE (+) WITH READ ONLY