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) |