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_EXT_TRAINING_EVENT_ENROLS

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_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(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 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)) 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 , substrb(HZP.PARTY_NAME,1,50) customer_name , btt.name booking_status , tdt.name activity_type , tvt.version_name activity_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 , cad.address1||decode(cad.address1,null,'',', ')|| cad.address2||decode(cad.address2,null,'',', ')|| cad.address3||decode(cad.address3,null,'',', ')|| cad.address4||decode(cad.address4,null,'',', ')|| cad.city||decode(cad.city,null,'',', ')|| cad.state||decode(cad.state,null,'',', ')|| cad.province||decode(cad.province,null,'',', ')|| cad.county||decode(cad.county,null,'',', ')|| cad.postal_code||decode(cad.postal_code,null,'',', ')|| cad.country correspondence_address , '_DF:OTA:OTA_DELEGATE_BOOKINGS:tdb' , tdb.business_group_id business_group_id , tav.activity_version_id activity_version_id , tdt.activity_id activity_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 , evt.event_id event_id , evt.training_center_id training_center_id , tav.rco_id rco_id , ofr.offering_id offering_id FROM ota_booking_status_types bst , ota_booking_status_types_tl btt , ota_activity_versions tav , ota_activity_versions_tl tvt , ota_activity_definitions_tl tdt , ota_events evt , ota_events_tl ett , ota_offerings ofr , ota_performances ope , ota_delegate_bookings tdb , hr_all_organization_units_tl bgrT , 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 ,HZ_LOCATIONS cad, HZ_CUST_ACCT_SITES acct_site, HZ_PARTY_SITES party_site WHERE evt.event_type IN ('SCHEDULED', 'SELFPACED') 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 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 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 = tdt.activity_id AND tdt.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 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 CUST_ACCT.cust_account_id = tdb.customer_id AND CUST_ACCT.party_id = HZP.party_id AND tdb.business_group_id = NVL(ota_general.get_business_group_id, tdb.business_group_id) 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_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(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 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)) 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
, SUBSTRB(HZP.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, BTT.NAME BOOKING_STATUS
, TDT.NAME ACTIVITY_TYPE
, TVT.VERSION_NAME ACTIVITY_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
, CAD.ADDRESS1||DECODE(CAD.ADDRESS1
, NULL
, ''
, '
, ')|| CAD.ADDRESS2||DECODE(CAD.ADDRESS2
, NULL
, ''
, '
, ')|| CAD.ADDRESS3||DECODE(CAD.ADDRESS3
, NULL
, ''
, '
, ')|| CAD.ADDRESS4||DECODE(CAD.ADDRESS4
, NULL
, ''
, '
, ')|| CAD.CITY||DECODE(CAD.CITY
, NULL
, ''
, '
, ')|| CAD.STATE||DECODE(CAD.STATE
, NULL
, ''
, '
, ')|| CAD.PROVINCE||DECODE(CAD.PROVINCE
, NULL
, ''
, '
, ')|| CAD.COUNTY||DECODE(CAD.COUNTY
, NULL
, ''
, '
, ')|| CAD.POSTAL_CODE||DECODE(CAD.POSTAL_CODE
, NULL
, ''
, '
, ')|| CAD.COUNTRY CORRESPONDENCE_ADDRESS
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TDT.ACTIVITY_ID ACTIVITY_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
, EVT.EVENT_ID EVENT_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_TYPES_TL BTT
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, OTA_PERFORMANCES OPE
, OTA_DELEGATE_BOOKINGS TDB
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, 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
, HZ_LOCATIONS CAD
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
WHERE EVT.EVENT_TYPE IN ('SCHEDULED'
, 'SELFPACED')
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 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 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 = TDT.ACTIVITY_ID
AND TDT.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 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 CUST_ACCT.CUST_ACCOUNT_ID = TDB.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = HZP.PARTY_ID
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY