OTA OLM Forum

select
f.forum_id FORUM_ID,
fl.name FORUM_NAME,
fl.description DESCRIPTION,
th.forum_thread_id FORUM_THREAD_ID,
th.subject SUBJECT,
msg.message_body MESSAGE,
fnd_date.date_to_displaydate(msg.creation_date,2) POSTED_DATE,
ota_utility.get_learner_name(msg.person_id, null, msg.contact_id) USER_NAME
from ota_forums_b f,
ota_forums_tl fl,
ota_forum_threads th,
ota_forum_messages msg
where fl.forum_id = f.forum_id
and fl.language = Userenv('LANG')
and f.forum_id = th.forum_id(+)
and th.forum_thread_id = msg.forum_thread_id(+)
and ((:P_FORUM_ID is not null and f.forum_id = :P_FORUM_ID) or :P_FORUM_ID is null)
and (upper(msg.message_body) like upper(:P_MESSAGE||'%') or :P_MESSAGE is null)
and (upper(ota_utility.get_learner_name(msg.person_id, null, msg.contact_id)) like upper('%'||:P_AUTHOR||'%') or :P_AUTHOR is null)
and ( (:P_FROM_DATE is null)
              or
      (trunc(msg.creation_date) >= trunc(:P_FROM_DATE))
     )
and ( (:P_TO_DATE is null)
              or
      (trunc(msg.creation_date) <= trunc(:P_TO_DATE))
     )
and f.business_group_id = ota_general.get_business_group_id
and ota_admin_access_util.admin_can_access_forum(f.forum_id)='Y'
group by f.forum_id,
fl.name,
fl.description,
th.forum_thread_id,
th.subject,
th.last_post_date,
msg.message_body,
msg.creation_date,
ota_utility.get_learner_name(msg.person_id, null, msg.contact_id)
order by
fl.name,
th.subject,
msg.message_body,
msg.creation_date