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_COMBINED_TRAINING_HISTORY

Source

View Text - Preformatted


SELECT bgrT.name business_group_name ,tnh.trng_title training_title ,tnh.completion_date training_completion_date ,DECODE(tnh.type,NULL,NULL, hr_bis.bis_decode_lookup('OTA_TRAINING_TYPES',tnh.type)) training_type ,tnh.centre training_center ,tnh.provider training_provider ,tnh.award training_award ,tnh.rating training_rating ,tnh.duration training_duration ,DECODE(tnh.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',tnh.duration_units)) training_duration_units ,DECODE(tnh.status,NULL,NULL, hr_bis.bis_decode_lookup('OTA_TRAINING_STATUSES',tnh.status)) training_status ,to_char(null) total_training_time ,to_char(null) player_status ,to_number(null) test_score ,to_number(null) number_of_modules_completed ,to_number(null) total_modules ,orgT.name organization_name ,NULL customer_name ,per.full_name person_name ,con.full_name contact_name ,tvt.version_name ota_equi_activity_name ,tdt.name ota_equi_activity_type ,'NON_OTA' data_source ,'_DF:OTA:OTA_NOTRNG_HISTORIES:tnh' ,'_DF:_DUMMY:OTA:OTA_DELEGATE_BOOKINGS:tdb' ,tnh.activity_version_id activity_version_id ,tav.activity_id activity_id ,tnh.contact_id contact_id ,tnh.person_id person_id ,tnh.customer_id customer_id ,tnh.organization_id organization_id ,tnh.business_group_id business_group_id ,to_number(null) training_center_id ,to_number(null) roc_id FROM ota_notrng_histories tnh ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,per_all_people_f per ,per_all_people_f con ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt WHERE tnh.organization_id IS NOT NULL AND tnh.organization_id = orgT.organization_id(+) AND orgT.language = userenv('LANG') AND tnh.business_group_id = bgrT.organization_id AND bgrt.language = userenv('LANG') AND tnh.person_id = per.person_id AND tnh.completion_date BETWEEN per.effective_start_date AND per.effective_end_date AND tnh.contact_id = con.person_id(+) AND tnh.completion_date BETWEEN con.effective_start_date(+) AND con.effective_end_date(+) AND tnh.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 tnh.business_group_id = NVL(ota_general.get_business_group_id ,tnh.business_group_id) UNION SELECT bgrT.name business_group_name ,tnh.trng_title training_title ,tnh.completion_date training_completion_date ,DECODE(tnh.type,NULL,NULL, hr_bis.bis_decode_lookup('OTA_TRAINING_TYPES',tnh.type)) training_type ,tnh.centre training_center ,tnh.provider training_provider ,tnh.award training_award ,tnh.rating training_rating ,tnh.duration training_duration ,DECODE(tnh.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',tnh.duration_units)) training_duration_units ,DECODE(tnh.status,NULL,NULL, hr_bis.bis_decode_lookup('OTA_TRAINING_STATUSES',tnh.status)) training_status ,to_char(null) total_training_time ,to_char(null) player_status ,to_number(null) test_score ,to_number(null) number_of_modules_completed ,to_number(null) total_modules ,orgT.name organization_name ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,per_party.person_first_name||' ' ||per_party.person_last_name person_name ,con_party.person_first_name||' '||con_party.person_last_name contact_name ,tvt.version_name ota_equi_activity_name ,tdt.name ota_equi_activity_name ,'NON_OTA' data_source ,'_DF:OTA:OTA_NOTRNG_HISTORIES:tnh' ,'_DF:_DUMMY:OTA:OTA_DELEGATE_BOOKINGS:tdb' ,tnh.activity_version_id activity_version_id ,tav.activity_id activity_id ,tnh.contact_id contact_id ,tnh.person_id person_id ,tnh.customer_id customer_id ,tnh.organization_id organization_id ,tnh.business_group_id business_group_id ,to_number(null) training_center_id ,tav.rco_id rco_id FROM ota_notrng_histories tnh ,hr_all_organization_units_tl bgrT ,hr_all_organization_units_tl orgT /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES PER_ACCT_ROLE ,HZ_PARTIES PER_PARTY ,HZ_RELATIONSHIPS PER_REL /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES CON_ACCT_ROLE ,HZ_PARTIES CON_PARTY ,HZ_RELATIONSHIPS CON_REL /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt WHERE tnh.customer_id IS NOT NULL /* replacement of RA_CUSTOMERS view with table access */ AND tnh.customer_id = CUST_ACCT.cust_account_id AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID AND tnh.business_group_id = bgrT.organization_id AND bgrt.language = userenv('LANG') AND tnh.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND tnh.activity_version_id = tav.activity_version_id (+) AND tav.activity_id = tad.activity_id (+) AND tav.activity_version_id = tvt.activity_version_id (+) AND tvt.language (+) = userenv('LANG') AND tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') /* replacement of RA_CONTACTS view with table access */ AND tnh.person_id = PER_ACCT_ROLE.cust_account_role_id AND PER_ACCT_ROLE.PARTY_ID = PER_REL.PARTY_ID AND PER_ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND PER_REL.SUBJECT_ID = PER_PARTY.PARTY_ID AND PER_REL.RELATIONSHIP_CODE = 'CONTACT_OF' /* replacement of RA_CONTACTS view with table access */ AND tnh.contact_id = CON_ACCT_ROLE.cust_account_role_id (+) AND CON_ACCT_ROLE.PARTY_ID = CON_REL.PARTY_ID (+) AND CON_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND CON_REL.SUBJECT_ID = CON_PARTY.PARTY_ID (+) AND CON_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' AND tnh.business_group_id = NVL(ota_general.get_business_group_id ,tnh.business_group_id) UNION SELECT bgrT.name business_group_name ,ett.title training_title ,evt.course_end_date training_completion_date ,null training_type ,orgT1.name training_center ,ven.vendor_name training_provider ,null training_award ,null training_rating ,evt.duration training_duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) training_duration_units ,null training_status , 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 ,orgT.name organization_name ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,per.first_name||' '||per.last_name person_name ,con.first_name||' '||con.last_name contact_name ,tvt.version_name ota_equi_activity_name ,tdt.name ota_equi_activity_name ,'OTA' data_source ,'_DF:_DUMMY:OTA:OTA_NOTRNG_HISTORIES:tnh' ,'_DF:OTA:OTA_DELEGATE_BOOKINGS:tdb' ,evt.activity_version_id activity_version_id ,tav.activity_id activity_id ,tdb.contact_id contact_id ,tdb.delegate_person_id person_id ,tdb.customer_id customer_id ,tdb.organization_id organization_id ,tdb.business_group_id business_group_id ,evt.training_center_id training_center_id ,tav.rco_id rco_id FROM hr_all_organization_units_tl bgrT ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,hr_all_organization_units_tl orgT ,po_vendors ven ,ota_delegate_bookings tdb ,ota_activity_versions tav ,ota_performances ope ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt ,per_all_people_f per ,per_all_people_f con /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,hr_all_organization_units_tl orgT1 WHERE tdb.organization_id IS NOT NULL 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.event_id = evt.event_id AND evt.event_id = ett.event_id 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 ett.language = userenv('LANG') AND evt.training_center_id = orgT1.organization_id(+) AND orgT1.language (+) = userenv('LANG') AND evt.vendor_id = ven.vendor_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 tdb.date_booking_placed BETWEEN per.effective_start_date(+) AND per.effective_end_date (+) AND tdb.contact_id = con.person_id(+) AND tdb.date_booking_placed BETWEEN con.effective_start_date(+) AND con.effective_end_date(+) /* 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 = NVL(ota_general.get_business_group_id ,tdb.business_group_id) UNION SELECT bgrT.name business_group_name ,ett.title training_title ,evt.course_end_date training_completion_date ,null training_type ,orgT1.name training_center ,ven.vendor_name training_provider ,null training_award ,null training_rating ,evt.duration training_duration ,DECODE(evt.duration_units,NULL,NULL, hr_bis.bis_decode_lookup('OTA_DURATION_UNITS',evt.duration_units)) training_duration_units ,null training_status , 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 ,orgT.name organization_name ,substrb(CUST_PARTY.PARTY_NAME,1,50) customer_name ,per_party.person_first_name||' '||per_party.person_last_name person_name ,con_party.person_first_name||' '||con_party.person_last_name contact_name ,tvt.version_name ota_equi_activity_name ,tdt.name ota_equi_activity_name ,'OTA' data_source ,'_DF:_DUMMY:OTA:OTA_NOTRNG_HISTORIES:tnh' ,'_DF:OTA:OTA_DELEGATE_BOOKINGS:tdb' ,evt.activity_version_id activity_version_id ,tav.activity_id activity_id ,tdb.contact_id contact_id ,tdb.delegate_person_id person_id ,tdb.customer_id customer_id ,tdb.organization_id organization_id ,tdb.business_group_id business_group_id ,evt.training_center_id training_center_id ,tav.rco_id rco_id FROM hr_all_organization_units_tl bgrT ,ota_events evt ,ota_events_tl ett ,ota_offerings ofr ,hr_all_organization_units_tl orgT ,po_vendors ven ,ota_delegate_bookings tdb ,ota_performances ope ,ota_activity_versions tav ,ota_activity_versions_tl tvt ,ota_activity_definitions tad ,ota_activity_definitions_tl tdt /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES PER_ACCT_ROLE ,HZ_PARTIES PER_PARTY ,HZ_RELATIONSHIPS PER_REL /* Start replacement of RA_CONTACTS view with table access */ ,HZ_CUST_ACCOUNT_ROLES CON_ACCT_ROLE ,HZ_PARTIES CON_PARTY ,HZ_RELATIONSHIPS CON_REL /* Start replacement of RA_CUSTOMERS view with table access */ ,HZ_CUST_ACCOUNTS CUST_ACCT ,HZ_PARTIES CUST_PARTY ,hr_all_organization_units_tl orgT1 WHERE tdb.customer_id IS NOT NULL /* 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 tdb.organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND tdb.event_id = evt.event_id (+) AND evt.event_id = ett.event_id (+) 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 ett.language (+) = userenv('LANG') AND evt.training_center_id = orgT1.organization_id (+) AND orgT1.language (+) = userenv('LANG') AND evt.vendor_id = ven.vendor_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 tad.activity_id = tdt.activity_id (+) AND tdt.language (+) = userenv('LANG') AND tav.activity_id = tad.activity_id (+) /* replacement of RA_CONTACTS view with table access */ AND tdb.delegate_person_id = PER_ACCT_ROLE.cust_account_role_id (+) AND PER_ACCT_ROLE.PARTY_ID = PER_REL.PARTY_ID (+) AND PER_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND PER_REL.SUBJECT_ID = PER_PARTY.PARTY_ID (+) AND PER_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' /* replacement of RA_CONTACTS view with table access */ AND tdb.contact_id = CON_ACCT_ROLE.cust_account_role_id (+) AND CON_ACCT_ROLE.PARTY_ID = CON_REL.PARTY_ID (+) AND CON_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT' AND CON_REL.SUBJECT_ID = CON_PARTY.PARTY_ID (+) AND CON_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' 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
, TNH.TRNG_TITLE TRAINING_TITLE
, TNH.COMPLETION_DATE TRAINING_COMPLETION_DATE
, DECODE(TNH.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_TYPES'
, TNH.TYPE)) TRAINING_TYPE
, TNH.CENTRE TRAINING_CENTER
, TNH.PROVIDER TRAINING_PROVIDER
, TNH.AWARD TRAINING_AWARD
, TNH.RATING TRAINING_RATING
, TNH.DURATION TRAINING_DURATION
, DECODE(TNH.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, TNH.DURATION_UNITS)) TRAINING_DURATION_UNITS
, DECODE(TNH.STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_STATUSES'
, TNH.STATUS)) TRAINING_STATUS
, TO_CHAR(NULL) TOTAL_TRAINING_TIME
, TO_CHAR(NULL) PLAYER_STATUS
, TO_NUMBER(NULL) TEST_SCORE
, TO_NUMBER(NULL) NUMBER_OF_MODULES_COMPLETED
, TO_NUMBER(NULL) TOTAL_MODULES
, ORGT.NAME ORGANIZATION_NAME
, NULL CUSTOMER_NAME
, PER.FULL_NAME PERSON_NAME
, CON.FULL_NAME CONTACT_NAME
, TVT.VERSION_NAME OTA_EQUI_ACTIVITY_NAME
, TDT.NAME OTA_EQUI_ACTIVITY_TYPE
, 'NON_OTA' DATA_SOURCE
, '_DF:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:_DUMMY:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TNH.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TNH.CONTACT_ID CONTACT_ID
, TNH.PERSON_ID PERSON_ID
, TNH.CUSTOMER_ID CUSTOMER_ID
, TNH.ORGANIZATION_ID ORGANIZATION_ID
, TNH.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) TRAINING_CENTER_ID
, TO_NUMBER(NULL) ROC_ID
FROM OTA_NOTRNG_HISTORIES TNH
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F CON
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
WHERE TNH.ORGANIZATION_ID IS NOT NULL
AND TNH.ORGANIZATION_ID = ORGT.ORGANIZATION_ID(+)
AND ORGT.LANGUAGE = USERENV('LANG')
AND TNH.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TNH.PERSON_ID = PER.PERSON_ID
AND TNH.COMPLETION_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND TNH.CONTACT_ID = CON.PERSON_ID(+)
AND TNH.COMPLETION_DATE BETWEEN CON.EFFECTIVE_START_DATE(+)
AND CON.EFFECTIVE_END_DATE(+)
AND TNH.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 TNH.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TNH.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, TNH.TRNG_TITLE TRAINING_TITLE
, TNH.COMPLETION_DATE TRAINING_COMPLETION_DATE
, DECODE(TNH.TYPE
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_TYPES'
, TNH.TYPE)) TRAINING_TYPE
, TNH.CENTRE TRAINING_CENTER
, TNH.PROVIDER TRAINING_PROVIDER
, TNH.AWARD TRAINING_AWARD
, TNH.RATING TRAINING_RATING
, TNH.DURATION TRAINING_DURATION
, DECODE(TNH.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, TNH.DURATION_UNITS)) TRAINING_DURATION_UNITS
, DECODE(TNH.STATUS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_TRAINING_STATUSES'
, TNH.STATUS)) TRAINING_STATUS
, TO_CHAR(NULL) TOTAL_TRAINING_TIME
, TO_CHAR(NULL) PLAYER_STATUS
, TO_NUMBER(NULL) TEST_SCORE
, TO_NUMBER(NULL) NUMBER_OF_MODULES_COMPLETED
, TO_NUMBER(NULL) TOTAL_MODULES
, ORGT.NAME ORGANIZATION_NAME
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PER_PARTY.PERSON_FIRST_NAME||' ' ||PER_PARTY.PERSON_LAST_NAME PERSON_NAME
, CON_PARTY.PERSON_FIRST_NAME||' '||CON_PARTY.PERSON_LAST_NAME CONTACT_NAME
, TVT.VERSION_NAME OTA_EQUI_ACTIVITY_NAME
, TDT.NAME OTA_EQUI_ACTIVITY_NAME
, 'NON_OTA' DATA_SOURCE
, '_DF:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:_DUMMY:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, TNH.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TNH.CONTACT_ID CONTACT_ID
, TNH.PERSON_ID PERSON_ID
, TNH.CUSTOMER_ID CUSTOMER_ID
, TNH.ORGANIZATION_ID ORGANIZATION_ID
, TNH.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
FROM OTA_NOTRNG_HISTORIES TNH
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES PER_ACCT_ROLE
, HZ_PARTIES PER_PARTY
, HZ_RELATIONSHIPS PER_REL /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES CON_ACCT_ROLE
, HZ_PARTIES CON_PARTY
, HZ_RELATIONSHIPS CON_REL /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
WHERE TNH.CUSTOMER_ID IS NOT NULL /* REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
AND TNH.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = CUST_PARTY.PARTY_ID
AND TNH.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND TNH.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TNH.ACTIVITY_VERSION_ID = TAV.ACTIVITY_VERSION_ID (+)
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+)
AND TAV.ACTIVITY_VERSION_ID = TVT.ACTIVITY_VERSION_ID (+)
AND TVT.LANGUAGE (+) = USERENV('LANG')
AND TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG') /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TNH.PERSON_ID = PER_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND PER_ACCT_ROLE.PARTY_ID = PER_REL.PARTY_ID
AND PER_ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND PER_REL.SUBJECT_ID = PER_PARTY.PARTY_ID
AND PER_REL.RELATIONSHIP_CODE = 'CONTACT_OF' /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TNH.CONTACT_ID = CON_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND CON_ACCT_ROLE.PARTY_ID = CON_REL.PARTY_ID (+)
AND CON_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND CON_REL.SUBJECT_ID = CON_PARTY.PARTY_ID (+)
AND CON_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF'
AND TNH.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TNH.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ETT.TITLE TRAINING_TITLE
, EVT.COURSE_END_DATE TRAINING_COMPLETION_DATE
, NULL TRAINING_TYPE
, ORGT1.NAME TRAINING_CENTER
, VEN.VENDOR_NAME TRAINING_PROVIDER
, NULL TRAINING_AWARD
, NULL TRAINING_RATING
, EVT.DURATION TRAINING_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) TRAINING_DURATION_UNITS
, NULL TRAINING_STATUS
, 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
, ORGT.NAME ORGANIZATION_NAME
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PER.FIRST_NAME||' '||PER.LAST_NAME PERSON_NAME
, CON.FIRST_NAME||' '||CON.LAST_NAME CONTACT_NAME
, TVT.VERSION_NAME OTA_EQUI_ACTIVITY_NAME
, TDT.NAME OTA_EQUI_ACTIVITY_NAME
, 'OTA' DATA_SOURCE
, '_DF:_DUMMY:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, EVT.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TDB.CONTACT_ID CONTACT_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PO_VENDORS VEN
, OTA_DELEGATE_BOOKINGS TDB
, OTA_ACTIVITY_VERSIONS TAV
, OTA_PERFORMANCES OPE
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT
, PER_ALL_PEOPLE_F PER
, PER_ALL_PEOPLE_F CON /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE TDB.ORGANIZATION_ID IS NOT NULL
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.EVENT_ID = EVT.EVENT_ID
AND EVT.EVENT_ID = ETT.EVENT_ID
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 ETT.LANGUAGE = USERENV('LANG')
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID(+)
AND ORGT1.LANGUAGE (+) = USERENV('LANG')
AND EVT.VENDOR_ID = VEN.VENDOR_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 TDB.DATE_BOOKING_PLACED BETWEEN PER.EFFECTIVE_START_DATE(+)
AND PER.EFFECTIVE_END_DATE (+)
AND TDB.CONTACT_ID = CON.PERSON_ID(+)
AND TDB.DATE_BOOKING_PLACED BETWEEN CON.EFFECTIVE_START_DATE(+)
AND CON.EFFECTIVE_END_DATE(+) /* 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 = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) UNION SELECT BGRT.NAME BUSINESS_GROUP_NAME
, ETT.TITLE TRAINING_TITLE
, EVT.COURSE_END_DATE TRAINING_COMPLETION_DATE
, NULL TRAINING_TYPE
, ORGT1.NAME TRAINING_CENTER
, VEN.VENDOR_NAME TRAINING_PROVIDER
, NULL TRAINING_AWARD
, NULL TRAINING_RATING
, EVT.DURATION TRAINING_DURATION
, DECODE(EVT.DURATION_UNITS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('OTA_DURATION_UNITS'
, EVT.DURATION_UNITS)) TRAINING_DURATION_UNITS
, NULL TRAINING_STATUS
, 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
, ORGT.NAME ORGANIZATION_NAME
, SUBSTRB(CUST_PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, PER_PARTY.PERSON_FIRST_NAME||' '||PER_PARTY.PERSON_LAST_NAME PERSON_NAME
, CON_PARTY.PERSON_FIRST_NAME||' '||CON_PARTY.PERSON_LAST_NAME CONTACT_NAME
, TVT.VERSION_NAME OTA_EQUI_ACTIVITY_NAME
, TDT.NAME OTA_EQUI_ACTIVITY_NAME
, 'OTA' DATA_SOURCE
, '_DF:_DUMMY:OTA:OTA_NOTRNG_HISTORIES:TNH'
, '_DF:OTA:OTA_DELEGATE_BOOKINGS:TDB'
, EVT.ACTIVITY_VERSION_ID ACTIVITY_VERSION_ID
, TAV.ACTIVITY_ID ACTIVITY_ID
, TDB.CONTACT_ID CONTACT_ID
, TDB.DELEGATE_PERSON_ID PERSON_ID
, TDB.CUSTOMER_ID CUSTOMER_ID
, TDB.ORGANIZATION_ID ORGANIZATION_ID
, TDB.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EVT.TRAINING_CENTER_ID TRAINING_CENTER_ID
, TAV.RCO_ID RCO_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, OTA_EVENTS EVT
, OTA_EVENTS_TL ETT
, OTA_OFFERINGS OFR
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PO_VENDORS VEN
, OTA_DELEGATE_BOOKINGS TDB
, OTA_PERFORMANCES OPE
, OTA_ACTIVITY_VERSIONS TAV
, OTA_ACTIVITY_VERSIONS_TL TVT
, OTA_ACTIVITY_DEFINITIONS TAD
, OTA_ACTIVITY_DEFINITIONS_TL TDT /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES PER_ACCT_ROLE
, HZ_PARTIES PER_PARTY
, HZ_RELATIONSHIPS PER_REL /* START REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNT_ROLES CON_ACCT_ROLE
, HZ_PARTIES CON_PARTY
, HZ_RELATIONSHIPS CON_REL /* START REPLACEMENT OF RA_CUSTOMERS VIEW WITH TABLE ACCESS */
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES CUST_PARTY
, HR_ALL_ORGANIZATION_UNITS_TL ORGT1
WHERE TDB.CUSTOMER_ID IS NOT NULL /* 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 TDB.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND TDB.EVENT_ID = EVT.EVENT_ID (+)
AND EVT.EVENT_ID = ETT.EVENT_ID (+)
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 ETT.LANGUAGE (+) = USERENV('LANG')
AND EVT.TRAINING_CENTER_ID = ORGT1.ORGANIZATION_ID (+)
AND ORGT1.LANGUAGE (+) = USERENV('LANG')
AND EVT.VENDOR_ID = VEN.VENDOR_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 TAD.ACTIVITY_ID = TDT.ACTIVITY_ID (+)
AND TDT.LANGUAGE (+) = USERENV('LANG')
AND TAV.ACTIVITY_ID = TAD.ACTIVITY_ID (+) /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TDB.DELEGATE_PERSON_ID = PER_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND PER_ACCT_ROLE.PARTY_ID = PER_REL.PARTY_ID (+)
AND PER_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND PER_REL.SUBJECT_ID = PER_PARTY.PARTY_ID (+)
AND PER_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF' /* REPLACEMENT OF RA_CONTACTS VIEW WITH TABLE ACCESS */
AND TDB.CONTACT_ID = CON_ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND CON_ACCT_ROLE.PARTY_ID = CON_REL.PARTY_ID (+)
AND CON_ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND CON_REL.SUBJECT_ID = CON_PARTY.PARTY_ID (+)
AND CON_REL.RELATIONSHIP_CODE (+) = 'CONTACT_OF'
AND TDB.BUSINESS_GROUP_ID = NVL(OTA_GENERAL.GET_BUSINESS_GROUP_ID
, TDB.BUSINESS_GROUP_ID) WITH READ ONLY