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_ENROLLMENT_STATUS_HISTORY

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,bsh.start_date start_date ,btt.name enrollment_status ,hr_bis.bis_decode_lookup('YES_NO',bst.active_flag) status_active ,hr_bis.bis_decode_lookup('YES_NO',bst.default_flag) status_default ,bsh.comments status_history_comments ,per.full_name student_full_name ,orgT.name organization_name ,TO_CHAR(NULL) customer_name ,ett.title event_title ,evt.course_start_date course_start_date ,evt.course_end_date course_end_date ,evt.duration course_duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) course_duration_units ,tdb.date_booking_placed date_booking_placed ,tvt.version_name activity_name ,tdt.name activity_type , 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(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 ,tdb.business_group_id business_group_id ,tdb.delegate_person_id student_id ,tdb.organization_id organization_id ,TO_NUMBER(NULL) customer_id ,evt.event_id event_id ,tav.activity_version_id activity_version_id ,tad.activity_id activity_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM ota_booking_status_histories bsh ,ota_booking_status_types bst ,ota_booking_status_types_tl btt ,ota_delegate_bookings tdb ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,per_all_people_f per ,ota_performances ope ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT WHERE tdb.customer_id IS NULL AND bsh.booking_id = tdb.booking_id AND bsh.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.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 tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') AND tdb.delegate_person_id = per.person_id AND NVL (evt.course_start_date,tdb.date_booking_placed) BETWEEN NVL(per.effective_start_date, NVL(evt.course_start_date,tdb.date_booking_placed)) AND NVL(per.effective_end_date, NVL(evt.course_start_date,tdb.date_booking_placed)) AND tdb.business_group_id = bgrT.organization_id AND bgrT.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) UNION ALL SELECT bgrT.name business_group_name /* Changes made for Bug No:3108567*/ ,NVL(bsh.start_date , tdb.date_booking_placed) start_date /* Changes made for Bug No: 3108567*/ ,btt.name enrollment_status ,hr_bis.bis_decode_lookup('YES_NO',bst.active_flag) status_active ,hr_bis.bis_decode_lookup('YES_NO',bst.default_flag) status_default ,bsh.comments status_history_comments ,party.person_first_name||' '||party.person_last_name student_full_name ,TO_CHAR(NULL) organization_name ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,ett.title event_title ,evt.course_start_date course_start_date ,evt.course_end_date course_end_date ,evt.duration course_duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) course_duration_units ,tdb.date_booking_placed date_booking_placed ,tvt.version_name activity_name ,tdt.name activity_type ,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(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 ,tdb.business_group_id business_group_id ,tdb.delegate_contact_id student_id ,TO_NUMBER(NULL) organization_id ,tdb.customer_id customer_id ,evt.event_id event_id ,tav.activity_version_id activity_version_id ,tad.activity_id activity_id ,tav.rco_id rco_id ,ofr.offering_id offering_id FROM ota_delegate_bookings tdb /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES ACCT_ROLE ,HZ_PARTIES PARTY ,HZ_RELATIONSHIPS REL ,HZ_ORG_CONTACTS ORG_CONT /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,ota_booking_status_types bst ,ota_booking_status_types_tl btt ,ota_booking_status_histories bsh ,ota_activity_versions tav ,ota_activity_definitions tad ,ota_performances ope ,ota_events evt ,ota_offerings ofr ,ota_activity_versions_tl tvt ,ota_activity_definitions_tl tdt ,ota_events_tl ett ,hr_all_organization_units_tl bgrT WHERE tdb.customer_id IS NOT NULL AND tdb.event_id = evt.event_id AND evt.event_id = ett.event_id AND ett.language = userenv('LANG') /* Changes made for Bug No:3108567 */ AND bsh.booking_id(+) = tdb.booking_id AND nvl(bsh.booking_status_type_id,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') /* Changes made for Bug No: 3108567 */ /* replacement of RA_CONTACTS view with table access */ AND tdb.delegate_contact_id = ACCT_ROLE.cust_account_role_id (+) AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+) AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID AND REL.SUBJECT_ID = PARTY.PARTY_ID (+) AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' /* replacement of RA_CUSTOMERS view with table access */ AND tdb.customer_id = CUST_ACCT.cust_account_id AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID AND tdb.business_group_id = bgrT.organization_id AND bgrT.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 tav.activity_id = tad.activity_id (+) AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') 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
, BSH.START_DATE START_DATE
, BTT.NAME ENROLLMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.ACTIVE_FLAG) STATUS_ACTIVE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.DEFAULT_FLAG) STATUS_DEFAULT
, BSH.COMMENTS STATUS_HISTORY_COMMENTS
, PER.FULL_NAME STUDENT_FULL_NAME
, ORGT.NAME ORGANIZATION_NAME
, TO_CHAR(NULL) CUSTOMER_NAME
, ETT.TITLE EVENT_TITLE
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.DURATION COURSE_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) COURSE_DURATION_UNITS
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, TVT.VERSION_NAME ACTIVITY_NAME
, TDT.NAME ACTIVITY_TYPE
, 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(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
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TDB.DELEGATE_PERSON_ID STUDENT_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_BOOKING_STATUS_HISTORIES BSH
, OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_TYPES_TL BTT
, OTA_DELEGATE_BOOKINGS TDB
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, PER_ALL_PEOPLE_F PER
, OTA_PERFORMANCES OPE
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE TDB.CUSTOMER_ID IS NULL
AND BSH.BOOKING_ID = TDB.BOOKING_ID
AND BSH.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.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 TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND TDB.DELEGATE_PERSON_ID = PER.PERSON_ID
AND NVL (EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED) BETWEEN NVL(PER.EFFECTIVE_START_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND NVL(PER.EFFECTIVE_END_DATE
, NVL(EVT.COURSE_START_DATE
, TDB.DATE_BOOKING_PLACED))
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.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) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME /* CHANGES MADE FOR BUG NO:3108567*/
, NVL(BSH.START_DATE
, TDB.DATE_BOOKING_PLACED) START_DATE /* CHANGES MADE FOR BUG NO: 3108567*/
, BTT.NAME ENROLLMENT_STATUS
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.ACTIVE_FLAG) STATUS_ACTIVE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, BST.DEFAULT_FLAG) STATUS_DEFAULT
, BSH.COMMENTS STATUS_HISTORY_COMMENTS
, PARTY.PERSON_FIRST_NAME||' '||PARTY.PERSON_LAST_NAME STUDENT_FULL_NAME
, TO_CHAR(NULL) ORGANIZATION_NAME
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, ETT.TITLE EVENT_TITLE
, EVT.COURSE_START_DATE COURSE_START_DATE
, EVT.COURSE_END_DATE COURSE_END_DATE
, EVT.DURATION COURSE_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) COURSE_DURATION_UNITS
, TDB.DATE_BOOKING_PLACED DATE_BOOKING_PLACED
, TVT.VERSION_NAME ACTIVITY_NAME
, TDT.NAME ACTIVITY_TYPE
, 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(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
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TDB.DELEGATE_CONTACT_ID STUDENT_ID
, TO_NUMBER(NULL) ORGANIZATION_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, EVT.EVENT_ID EVENT_ID
, TAV.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAD.ACTIVITY_ID ACTIVITY_ID
, TAV.RCO_ID RCO_ID
, OFR.OFFERING_ID OFFERING_ID
FROM OTA_DELEGATE_BOOKINGS TDB /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, OTA_BOOKING_STATUS_TYPES BST
, OTA_BOOKING_STATUS_TYPES_TL BTT
, OTA_BOOKING_STATUS_HISTORIES BSH
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_PERFORMANCES OPE
, OTA_EVENTS EVT
, OTA_OFFERINGS OFR
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, OTA_EVENTS_TL ETT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE TDB.CUSTOMER_ID IS NOT NULL
AND TDB.EVENT_ID = EVT.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
AND ETT.LANGUAGE = USERENV('LANG') /* CHANGES MADE FOR BUG NO:3108567 */
AND BSH.BOOKING_ID(+) = TDB.BOOKING_ID
AND NVL(BSH.BOOKING_STATUS_TYPE_ID
, 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') /* CHANGES MADE FOR BUG NO: 3108567 */ /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TDB.DELEGATE_CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID (+)
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID (+) = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID (+)
AND REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' /* REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
AND TDB.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID
AND TDB.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.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 TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY