OTA OLM Chat
Description
Categories: BI Publisher
Application: Learning Management
Source: OLM Chat Report
Short Name: OTACHT
DB package: OTA_CHT_XMLP_PKG
Source: OLM Chat Report
Short Name: OTACHT
DB package: OTA_CHT_XMLP_PKG
SELECT DISTINCT msg.chat_id CHAT_ID, tl.name CHAT_NAME, decode(nvl(trainer.IS_INSTRUCTOR,'N'),'Y','Instructor','N','Active Participant') PERSON_TYPE, ota_utility.get_learner_name(msg.person_id, null, msg.contact_id) FULL_NAME FROM ( select sr.trainer_id as trainer_id, 'Y' as IS_INSTRUCTOR from ota_resource_bookings rb, ota_suppliable_resources sr, OTA_CHATS_TL tl, OTA_CHATS_B ch, OTA_CHAT_MESSAGES msg where rb.supplied_resource_id = sr.supplied_Resource_id and rb.chat_id = ch.chat_id and ch.chat_id = tl.chat_id and ((:P_CHAT_ID is not null and ch.chat_id = :P_CHAT_ID) or :P_CHAT_ID is null) and tl.language = Userenv('LANG') and ch.business_group_id = ota_general.get_business_group_id and ch.chat_id = msg.chat_id and ( (:P_MESSAGE_START_DATE is null) or (trunc(msg.creation_date) >= trunc(:P_MESSAGE_START_DATE)) ) and ( (:P_MESSAGE_END_DATE is null) or (trunc(msg.creation_date) <= trunc(:P_MESSAGE_END_DATE)) ) and msg.person_id = sr.trainer_id group by sr.trainer_id, ch.chat_id ) trainer, OTA_CHATS_TL tl, OTA_CHATS_B ch, OTA_CHAT_MESSAGES msg WHERE ch.chat_id = tl.chat_id and msg.chat_id = ch.chat_id and ((:P_CHAT_ID is not null and ch.chat_id = :P_CHAT_ID) or :P_CHAT_ID is null) and ch.business_group_id = ota_general.get_business_group_id and tl.language = Userenv('LANG') and ( (:P_MESSAGE_START_DATE is null) or (trunc(msg.creation_date) >= trunc(:P_MESSAGE_START_DATE)) ) and ( (:P_MESSAGE_END_DATE is null) or (trunc(msg.creation_date) <= trunc(:P_MESSAGE_END_DATE)) ) and msg.person_id = trainer.trainer_id(+) and (msg.person_id is not null or msg.contact_id is not null) and ota_admin_access_util.admin_can_access_chat(ch.chat_id)='Y' order by tl.name, ota_utility.get_learner_name(msg.person_id, null, msg.contact_id) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Chat Name |
|
LOV Oracle | |
Message Start Date |
|
Date | |
Message End Date |
|
Date | |
Content Type |
|
LOV Oracle |