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_ENROLLED_EVENTS

Source

View Text - Preformatted


SELECT bgrT.name business_group_name , DECODE(acct_role.cust_account_role_id,NULL,NULL, party.person_last_name || ', ' || initcap(org_cont.title ) || ' ' || party.person_first_name ) person_name , ett.title event_title , evt.comments event_comments , evt.course_start_date course_start_date , evt.course_end_date course_end_date , 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(ota_lo_utility.get_lo_completion_date(tdb.event_id,nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'c','e')),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 , ota_lo_utility.get_enroll_lo_time( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'c','e') , tdb.event_id, tdb.booking_id) total_training_time , ota_lo_utility.get_enroll_lo_status( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'c','e') , tdb.event_id, tdb.booking_status_type_id, tdb.booking_id,2) player_status , ota_lo_utility.get_enroll_lo_score( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'c','e') , tdb.event_id, tdb.booking_id) test_score /*, 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 , DECODE(evt.offering_id,NULL,hr_bis.bis_decode_lookup('OTA_CONTENT_PLAYER_STATUS',ope.lesson_status), hr_bis.bis_decode_lookup('OTA_CONTENT_PLAYER_STATUS',tdb.content_player_status)) player_status , DECODE(evt.offering_id,NULL,DECODE(ope.score, -1000, NULL, ope.score),tdb.score) test_score */, DECODE(evt.offering_id,NULL, (SELECT COUNT(DISTINCT ope1.learning_object_id) FROM ota_performances ope1 , ota_learning_objects olo1 WHERE olo1.learning_object_id = ope1.learning_object_id AND ope1.user_id = PARTY.party_id and ope1.user_type = 'C' CONNECT BY prior olo1.learning_object_id = olo1.parent_learning_object_id START WITH olo1.learning_object_id = ofr.learning_object_id) ,tdb.completed_content) number_of_modules_completed , DECODE(evt.offering_id,NULL, (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) ,tdb.total_content) total_modules , NULL current_employee_flag , NULL internal_booking_flag , substrb(HZP.PARTY_NAME,1,50) customer_name , NULL assignment_number , NULL assignment_organization , NULL assignment_location , NULL assignment_job_category , NULL assignment_job , btt.name booking_status , bst.type booking_status_type , tvt.version_name activity_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 , DECODE(bst.type,'R',tdb.number_of_places,0) requested_enrollment , DECODE(bst.type,'A',tdb.number_of_places,0) attended_enrollment , DECODE(bst.type,'P',tdb.number_of_places,0) placed_enrollment , DECODE(bst.type,'W',tdb.number_of_places,0) waitlisted_enrollment , 'EXTERNAL' training_target_type , 0 succ_att_int_student_count , DECODE(tdb.customer_id,null,0,DECODE(tdb.successful_attendance_flag,'Y',tdb.number_of_places,0)) succ_att_ext_student_count , 0 non_succ_att_int_student_count , DECODE(tdb.customer_id,null,0,DECODE(tdb.successful_attendance_flag,'N',tdb.number_of_places,0)) non_succ_att_ext_student_count , 0 confirmed_int_student_count , DECODE(tdb.customer_id,null,0,DECODE(bst.type,'A',tdb.number_of_places,'P',tdb.number_of_places,0)) confirmed_ext_student_count , 0 attended_int_student_count , DECODE(tdb.customer_id,null,0,DECODE(bst.type,'A',tdb.number_of_places,0)) attended_ext_student_count , 0 non_attended_int_student_count , DECODE(tdb.customer_id,null,0,DECODE(bst.type,'P',tdb.number_of_places ,'C',tdb.number_of_places,0)) non_attended_ext_student_count , 0 cancelled_int_student_count , DECODE(tdb.customer_id,null,0,DECODE(bst.type,'C',tdb.number_of_places,0)) cancelled_ext_student_count , evt.duration duration , DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) duration_units , evt.duration_units duration_units_code , 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 , cad.address1 address_line_1 , cad.address2 address_line_2 , cad.address3 address_line_3 , cad.address4 address_line_4 , cad.city city , cad.state state , cad.postal_code postal_code , cad.country country , '_DF:OTA:OTA_DELEGATE_BOOKINGS:tdb' , tdb.business_group_id business_group_id , tav.activity_version_id activity_version_id , 0 assignment_id , tdb.booking_status_type_id booking_status_type_id , tdb.customer_id customer_id , tdb.delegate_contact_id delegate_contact_id , tdb.booking_id enrollment_id , tdb.organization_id organization_id , tdb.delegate_person_id person_id , tdb.org_id org_id , tdb.line_id line_id , evt.event_id event_id , evt.training_center_id training_center_id , tav.rco_id rco_id , ofr.offering_id offering_id /*, DECODE(evt.offering_id,NULL,ope.lesson_status,tdb.content_player_status)*/ , ota_lo_utility.get_player_status( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE( tdb.delegate_person_id, null,'C','E') , tdb.event_id, tdb.booking_id) player_status_code FROM ota_booking_status_types bst , ota_booking_status_types_tl btt , ota_activity_versions tav , ota_activity_versions_tl tvt , ota_events evt , ota_events_tl ett , ota_offerings ofr /*, ota_performances ope*/ , hr_all_organization_units_tl bgrT , ota_delegate_bookings tdb , hr_all_organization_units_tl orgT ,HZ_LOCATIONS cad , HZ_CUST_ACCT_SITES acct_site , HZ_PARTY_SITES party_site , hr_all_organization_units_tl orgT1 , HZ_CUST_ACCOUNT_ROLES ACCT_ROLE , HZ_PARTIES PARTY , HZ_RELATIONSHIPS REL , HZ_ORG_CONTACTS ORG_CONT , HZ_CUST_ACCOUNTS ROLE_ACCT , HZ_PARTIES REL_PARTY , HZ_PARTIES HZP , HZ_CUST_ACCOUNTS CUST_ACCT WHERE evt.event_type IN ('SCHEDULED', 'SELFPACED') AND evt.training_center_id = orgT1.organization_id(+) AND orgT1.language(+) = userenv('LANG') AND tdb.business_group_id = bgrT.organization_id AND bgrT.language = 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',ope.user_id) = DECODE(ofr.learning_object_id,NULL,'X',PARTY.party_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 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 tdb.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND tdb.business_group_id = NVL(ota_general.get_business_group_id, tdb.business_group_id) AND acct_role.party_id = rel.party_id(+) AND nvl(acct_role.role_type,'CONTACT') = 'CONTACT' AND org_cont.party_relationship_id(+) = rel.relationship_id AND rel.subject_id = party.party_id(+) AND rel.party_id = rel_party.party_id(+) AND nvl(rel.subject_table_name,'HZ_PARTIES') = 'HZ_PARTIES' AND nvl(rel.object_table_name,'HZ_PARTIES') = 'HZ_PARTIES' AND acct_role.cust_account_id = role_acct.cust_account_id(+) AND nvl(role_acct.party_id,-1) = nvl(rel.object_id,-1) AND acct_role.cust_account_role_id(+) = tdb.delegate_contact_id AND tdb.contact_address_id = ACCT_SITE.cust_acct_site_id(+) AND PARTY_SITE.location_id = cad.location_id(+) AND ACCT_SITE.party_site_id = PARTY_SITE.party_site_id(+) AND CUST_ACCT.cust_account_id = tdb.customer_id AND CUST_ACCT.party_id = HZP.party_id AND tdb.internal_booking_flag = 'N' UNION ALL SELECT bgrT.name business_group_name , del.full_name person_name , ett.title event_name , evt.comments event_comments , evt.course_start_date course_start_date , evt.course_end_date course_end_date , 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(ota_lo_utility.get_lo_completion_date(tdb.event_id,nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'C','E')),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 , ota_lo_utility.get_enroll_lo_time( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'C','E') , tdb.event_id, tdb.booking_id) total_training_time , ota_lo_utility.get_enroll_lo_status( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'C','E') , tdb.event_id, tdb.booking_status_type_id, tdb.booking_id,2) player_status , ota_lo_utility.get_enroll_lo_score( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE(tdb.delegate_person_id, NULL,'C','E') , tdb.event_id, tdb.booking_id) test_score /*, 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 , DECODE(evt.offering_id,NULL ,hr_bis.bis_decode_lookup('OTA_CONTENT_PLAYER_STATUS',ope.lesson_status) ,hr_bis.bis_decode_lookup('OTA_CONTENT_PLAYER_STATUS',tdb.content_player_status)) player_status , DECODE(evt.offering_id,NULL,DECODE(ope.score, -1000, NULL, ope.score),tdb.score) test_score*/ , DECODE(evt.offering_id,NULL, (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 and user_type = 'E' CONNECT BY prior olo1.learning_object_id = olo1.parent_learning_object_id START WITH olo1.learning_object_id = ofr.learning_object_id) ,tdb.completed_content) number_of_modules_completed , DECODE(evt.offering_id,NULL, (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) ,tdb.total_content) 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 , NULL customer_name , 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 , bst.type booking_status_type , tvt.version_name activity_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 , DECODE(bst.type,'R',tdb.number_of_places,0) requested_enrollment , DECODE(bst.type,'A',tdb.number_of_places,0) attended_enrollment , DECODE(bst.type,'P',tdb.number_of_places,0) placed_enrollment , DECODE(bst.type,'W',tdb.number_of_places,0) waitlisted_enrollment , 'INTERNAL' training_target_type , DECODE(tdb.organization_id,null,0 ,DECODE(tdb.successful_attendance_flag,'Y',tdb.number_of_places,0)) succ_att_int_student_count , 0 succ_att_ext_student_count , DECODE(tdb.organization_id,null,0 ,DECODE(tdb.successful_attendance_flag,'N',tdb.number_of_places,0)) non_succ_att_int_student_count , 0 non_succ_att_ext_student_count , DECODE(tdb.organization_id,null,0 ,DECODE( bst.type,'A',tdb.number_of_places,'P',tdb.number_of_places,0)) confirmed_int_student_count , 0 confirmed_ext_student_count , DECODE(tdb.organization_id,null,0 ,DECODE(bst.type,'A',tdb.number_of_places,0)) attended_int_student_count , 0 attended_ext_student_count , DECODE(tdb.organization_id,null, 0 ,DECODE(bst.type,'P',tdb.number_of_places,'C',tdb.number_of_places,0)) non_attended_int_student_count , 0 non_attended_ext_student_count , DECODE(tdb.organization_id,null,0 ,DECODE( bst.type,'C',tdb.number_of_places,0)) cancelled_int_student_count , 0 cancelled_ext_student_count , evt.duration duration , DECODE(evt.duration_units,NULL,NULL ,hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) duration_units , evt.duration_units duration_units_code , 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 ,'I',asg.internal_address_line ,'C',asg2.internal_address_line) address_line_1 , DECODE(tdb.person_address_type,'E',addr.address_line2 ,'I',loc1.address_line_1 ,'C',loc2.address_line_1) address_line_2 , DECODE(tdb.person_address_type,'E',addr.address_line3 ,'I',loc1.address_line_2 ,'C',loc2.address_line_2) address_line_3 , DECODE(tdb.person_address_type,'E',NULL ,'I',loc1.address_line_3 ,'C',loc2.address_line_3) address_line_4 , DECODE(tdb.person_address_type,'E',addr.town_or_city ,'I',loc1.town_or_city ,'C',loc2.town_or_city) city , NULL state , DECODE(tdb.person_address_type,'E',addr.postal_code ,'I',loc1.postal_code ,'C',loc2.postal_code) postal_code , DECODE(tdb.person_address_type,'E',addr.country ,'I',loc1.country ,'C',loc2.country) country , '_DF:OTA:OTA_DELEGATE_BOOKINGS:tdb' , tdb.business_group_id business_group_id , tav.activity_version_id activity_version_id , tdb.delegate_assignment_id assignment_id , tdb.booking_status_type_id booking_status_type_id , tdb.customer_id customer_id , tdb.delegate_contact_id delegate_contact_id , tdb.booking_id enrollment_id , tdb.organization_id organization_id , tdb.delegate_person_id person_id , tdb.org_id org_id , tdb.line_id line_id , evt.event_id event_id , evt.training_center_id training_center_id , tav.rco_id rco_id , ofr.offering_id offering_id /* , DECODE(evt.offering_id,NULL,ope.lesson_status,tdb.content_player_status)*/ , ota_lo_utility.get_player_status( nvl(tdb.delegate_person_id, ota_utility.get_ext_lrnr_party_id(tdb.delegate_contact_id)), DECODE( tdb.delegate_person_id, null,'C','E') , tdb.event_id, tdb.booking_id) player_status_code FROM hr_all_organization_units_tl org1T , ota_activity_versions tav , ota_activity_versions_tl tvt , 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_locations_all loc1 , hr_locations_all loc2 , hr_all_organization_units_tl orgT1 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 orgT1.language (+) = 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 tvt.language (+) = userenv('LANG') AND tdb.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND tdb.delegate_person_id = del.person_id (+) AND TRUNC(SYSDATE) BETWEEN nvl(del.effective_start_date,trunc(sysdate)) AND nvl(del.effective_end_date, trunc(sysdate)) AND tdb.delegate_assignment_id = asg.assignment_id (+) 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 org1T.language (+) = userenv('LANG') AND asg.location_id = locT.location_id (+) AND locT.language (+) = 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 (+) AND asg.location_id = loc1.location_id (+) AND asg2.location_id = loc2.location_id (+) AND tdb.internal_booking_flag = 'Y' WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DECODE(ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, NULL
, NULL
, PARTY.PERSON_LAST_NAME || '
, ' || INITCAP(ORG_CONT.TITLE ) || ' ' || PARTY.PERSON_FIRST_NAME ) PERSON_NAME
, ETT.TITLE EVENT_TITLE
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, 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(OTA_LO_UTILITY.GET_LO_COMPLETION_DATE(TDB.EVENT_ID
, NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E'))
, 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
, OTA_LO_UTILITY.GET_ENROLL_LO_TIME( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_ID) TOTAL_TRAINING_TIME
, OTA_LO_UTILITY.GET_ENROLL_LO_STATUS( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_STATUS_TYPE_ID
, TDB.BOOKING_ID
, 2) PLAYER_STATUS
, OTA_LO_UTILITY.GET_ENROLL_LO_SCORE( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_ID) TEST_SCORE /*
, 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
, DECODE(EVT.OFFERING_ID
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, OPE.LESSON_STATUS)
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)) PLAYER_STATUS
, DECODE(EVT.OFFERING_ID
, NULL
, DECODE(OPE.SCORE
, -1000
, NULL
, OPE.SCORE)
, TDB.SCORE) TEST_SCORE */
, DECODE(EVT.OFFERING_ID
, NULL
, (SELECT COUNT(DISTINCT OPE1.LEARNING_OBJECT_ID)
FROM OTA_PERFORMANCES OPE1
, OTA_LEARNING_OBJECTS OLO1
WHERE OLO1.LEARNING_OBJECT_ID = OPE1.LEARNING_OBJECT_ID
AND OPE1.USER_ID = PARTY.PARTY_ID
AND OPE1.USER_TYPE = 'C' CONNECT BY PRIOR OLO1.LEARNING_OBJECT_ID = OLO1.PARENT_LEARNING_OBJECT_ID START WITH OLO1.LEARNING_OBJECT_ID = OFR.LEARNING_OBJECT_ID)
, TDB.COMPLETED_CONTENT) NUMBER_OF_MODULES_COMPLETED
, DECODE(EVT.OFFERING_ID
, NULL
, (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)
, TDB.TOTAL_CONTENT) TOTAL_MODULES
, NULL CURRENT_EMPLOYEE_FLAG
, NULL INTERNAL_BOOKING_FLAG
, SUBSTRB(HZP.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, NULL ASSIGNMENT_NUMBER
, NULL ASSIGNMENT_ORGANIZATION
, NULL ASSIGNMENT_LOCATION
, NULL ASSIGNMENT_JOB_CATEGORY
, NULL ASSIGNMENT_JOB
, BTT.NAME BOOKING_STATUS
, BST.TYPE BOOKING_STATUS_TYPE
, TVT.VERSION_NAME ACTIVITY_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
, DECODE(BST.TYPE
, 'R'
, TDB.NUMBER_OF_PLACES
, 0) REQUESTED_ENROLLMENT
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0) ATTENDED_ENROLLMENT
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 0) PLACED_ENROLLMENT
, DECODE(BST.TYPE
, 'W'
, TDB.NUMBER_OF_PLACES
, 0) WAITLISTED_ENROLLMENT
, 'EXTERNAL' TRAINING_TARGET_TYPE
, 0 SUCC_ATT_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0)) SUCC_ATT_EXT_STUDENT_COUNT
, 0 NON_SUCC_ATT_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'N'
, TDB.NUMBER_OF_PLACES
, 0)) NON_SUCC_ATT_EXT_STUDENT_COUNT
, 0 CONFIRMED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)) CONFIRMED_EXT_STUDENT_COUNT
, 0 ATTENDED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0)) ATTENDED_EXT_STUDENT_COUNT
, 0 NON_ATTENDED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) NON_ATTENDED_EXT_STUDENT_COUNT
, 0 CANCELLED_INT_STUDENT_COUNT
, DECODE(TDB.CUSTOMER_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) CANCELLED_EXT_STUDENT_COUNT
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.DURATION_UNITS DURATION_UNITS_CODE
, 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
, CAD.ADDRESS1 ADDRESS_LINE_1
, CAD.ADDRESS2 ADDRESS_LINE_2
, CAD.ADDRESS3 ADDRESS_LINE_3
, CAD.ADDRESS4 ADDRESS_LINE_4
, CAD.CITY CITY
, CAD.STATE STATE
, CAD.POSTAL_CODE POSTAL_CODE
, CAD.COUNTRY COUNTRY
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, 0 ASSIGNMENT_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.DELEGATE_CONTACT_ID DELEGATE_CONTACT_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.ORG_ID ORG_ID
, TDB.LINE_ID LINE_ID
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID /*
, DECODE(EVT.OFFERING_ID
, NULL
, OPE.LESSON_STATUS
, TDB.CONTENT_PLAYER_STATUS)*/
, OTA_LO_UTILITY.GET_PLAYER_STATUS( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE( TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_ID) PLAYER_STATUS_CODE
FROM OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_TYPES_TL BTT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR /*
, OTA_PERFORMANCES OPE*/
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_DELEGATE_BOOKINGS TDB
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HZ_LOCATIONS CAD
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_CUST_ACCOUNTS ROLE_ACCT
, HZ_PARTIES REL_PARTY
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE EVT.EVENT_TYPE IN ('SCHEDULED'
, 'SELFPACED')
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID(+)
AND ORGT1.LANGUAGE(+) = USERENV('LANG')
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = 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'
, OPE.USER_ID) = DECODE(OFR.LEARNING_OBJECT_ID
, NULL
, 'X'
, PARTY.PARTY_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 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 TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+)
AND NVL(ACCT_ROLE.ROLE_TYPE
, 'CONTACT') = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID(+) = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID(+)
AND REL.PARTY_ID = REL_PARTY.PARTY_ID(+)
AND NVL(REL.SUBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND NVL(REL.OBJECT_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID(+)
AND NVL(ROLE_ACCT.PARTY_ID
, -1) = NVL(REL.OBJECT_ID
, -1)
AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID(+) = TDB.DELEGATE_CONTACT_ID
AND TDB.CONTACT_ADDRESS_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND PARTY_SITE.LOCATION_ID = CAD.LOCATION_ID(+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND CUST_ACCT.CUST_ACCOUNT_ID = TDB.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = HZP.PARTY_ID
AND TDB.INTERNAL_BOOKING_FLAG = 'N' UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, DEL.FULL_NAME PERSON_NAME
, ETT.TITLE EVENT_NAME
, EVT.COMMENTS EVENT_COMMENTS
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, 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(OTA_LO_UTILITY.GET_LO_COMPLETION_DATE(TDB.EVENT_ID
, NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E'))
, 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
, OTA_LO_UTILITY.GET_ENROLL_LO_TIME( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_ID) TOTAL_TRAINING_TIME
, OTA_LO_UTILITY.GET_ENROLL_LO_STATUS( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_STATUS_TYPE_ID
, TDB.BOOKING_ID
, 2) PLAYER_STATUS
, OTA_LO_UTILITY.GET_ENROLL_LO_SCORE( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE(TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_ID) TEST_SCORE /*
, 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
, DECODE(EVT.OFFERING_ID
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, OPE.LESSON_STATUS)
, HR_BIS.BIS_DECODE_LOOKUP('OTA_CONTENT_PLAYER_STATUS'
, TDB.CONTENT_PLAYER_STATUS)) PLAYER_STATUS
, DECODE(EVT.OFFERING_ID
, NULL
, DECODE(OPE.SCORE
, -1000
, NULL
, OPE.SCORE)
, TDB.SCORE) TEST_SCORE*/
, DECODE(EVT.OFFERING_ID
, NULL
, (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
AND USER_TYPE = 'E' CONNECT BY PRIOR OLO1.LEARNING_OBJECT_ID = OLO1.PARENT_LEARNING_OBJECT_ID START WITH OLO1.LEARNING_OBJECT_ID = OFR.LEARNING_OBJECT_ID)
, TDB.COMPLETED_CONTENT) NUMBER_OF_MODULES_COMPLETED
, DECODE(EVT.OFFERING_ID
, NULL
, (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)
, TDB.TOTAL_CONTENT) 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
, NULL CUSTOMER_NAME
, 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
, BST.TYPE BOOKING_STATUS_TYPE
, TVT.VERSION_NAME ACTIVITY_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
, DECODE(BST.TYPE
, 'R'
, TDB.NUMBER_OF_PLACES
, 0) REQUESTED_ENROLLMENT
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0) ATTENDED_ENROLLMENT
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 0) PLACED_ENROLLMENT
, DECODE(BST.TYPE
, 'W'
, TDB.NUMBER_OF_PLACES
, 0) WAITLISTED_ENROLLMENT
, 'INTERNAL' TRAINING_TARGET_TYPE
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'Y'
, TDB.NUMBER_OF_PLACES
, 0)) SUCC_ATT_INT_STUDENT_COUNT
, 0 SUCC_ATT_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(TDB.SUCCESSFUL_ATTENDANCE_FLAG
, 'N'
, TDB.NUMBER_OF_PLACES
, 0)) NON_SUCC_ATT_INT_STUDENT_COUNT
, 0 NON_SUCC_ATT_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 'P'
, TDB.NUMBER_OF_PLACES
, 0)) CONFIRMED_INT_STUDENT_COUNT
, 0 CONFIRMED_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'A'
, TDB.NUMBER_OF_PLACES
, 0)) ATTENDED_INT_STUDENT_COUNT
, 0 ATTENDED_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE(BST.TYPE
, 'P'
, TDB.NUMBER_OF_PLACES
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) NON_ATTENDED_INT_STUDENT_COUNT
, 0 NON_ATTENDED_EXT_STUDENT_COUNT
, DECODE(TDB.ORGANIZATION_ID
, NULL
, 0
, DECODE( BST.TYPE
, 'C'
, TDB.NUMBER_OF_PLACES
, 0)) CANCELLED_INT_STUDENT_COUNT
, 0 CANCELLED_EXT_STUDENT_COUNT
, EVT.DURATION DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) DURATION_UNITS
, EVT.DURATION_UNITS DURATION_UNITS_CODE
, 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
, 'I'
, ASG.INTERNAL_ADDRESS_LINE
, 'C'
, ASG2.INTERNAL_ADDRESS_LINE) ADDRESS_LINE_1
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE2
, 'I'
, LOC1.ADDRESS_LINE_1
, 'C'
, LOC2.ADDRESS_LINE_1) ADDRESS_LINE_2
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.ADDRESS_LINE3
, 'I'
, LOC1.ADDRESS_LINE_2
, 'C'
, LOC2.ADDRESS_LINE_2) ADDRESS_LINE_3
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, NULL
, 'I'
, LOC1.ADDRESS_LINE_3
, 'C'
, LOC2.ADDRESS_LINE_3) ADDRESS_LINE_4
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.TOWN_OR_CITY
, 'I'
, LOC1.TOWN_OR_CITY
, 'C'
, LOC2.TOWN_OR_CITY) CITY
, NULL STATE
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.POSTAL_CODE
, 'I'
, LOC1.POSTAL_CODE
, 'C'
, LOC2.POSTAL_CODE) POSTAL_CODE
, DECODE(TDB.PERSON_ADDRESS_TYPE
, 'E'
, ADDR.COUNTRY
, 'I'
, LOC1.COUNTRY
, 'C'
, LOC2.COUNTRY) COUNTRY
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TDB.DELEGATE_ASSIGNMENT_ID ASSIGNMENT_ID
, TDB.BOOKING_STATUS_TYPE_ID BOOKING_STATUS_TYPE_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.DELEGATE_CONTACT_ID DELEGATE_CONTACT_ID
, TDB.BOOKING_ID ENROLLMENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.ORG_ID ORG_ID
, TDB.LINE_ID LINE_ID
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID /*
, DECODE(EVT.OFFERING_ID
, NULL
, OPE.LESSON_STATUS
, TDB.CONTENT_PLAYER_STATUS)*/
, OTA_LO_UTILITY.GET_PLAYER_STATUS( NVL(TDB.DELEGATE_PERSON_ID
, OTA_UTILITY.GET_EXT_LRNR_PARTY_ID(TDB.DELEGATE_CONTACT_ID))
, DECODE( TDB.DELEGATE_PERSON_ID
, NULL
, 'C'
, 'E')
, TDB.EVENT_ID
, TDB.BOOKING_ID) PLAYER_STATUS_CODE
FROM HR_ALL_ORGANIZATION_UNITS_TL ORG1T
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, 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_LOCATIONS_ALL LOC1
, HR_LOCATIONS_ALL LOC2
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
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 ORGT1.LANGUAGE (+) = 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 TVT.LANGUAGE (+) = USERENV('LANG')
AND TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TDB.DELEGATE_PERSON_ID = DEL.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN NVL(DEL.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(DEL.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND TDB.DELEGATE_ASSIGNMENT_ID = ASG.ASSIGNMENT_ID (+)
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 ORG1T.LANGUAGE (+) = USERENV('LANG')
AND ASG.LOCATION_ID = LOCT.LOCATION_ID (+)
AND LOCT.LANGUAGE (+) = 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 (+)
AND ASG.LOCATION_ID = LOC1.LOCATION_ID (+)
AND ASG2.LOCATION_ID = LOC2.LOCATION_ID (+)
AND TDB.INTERNAL_BOOKING_FLAG = 'Y' WITH READ ONLY