OTA OLM Chat

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)