How to optimize this MySQL routines
select  ara.ApprovalStatus
    from  `approval_request_approvers` as ara
    where  ara.ApprovalRequestID = rt.ApprovalRequestID
      and  ara.IsDeleted = 0
    order by  ara.ModifiedDate desc
    limit  1


is executed in 7 places.  See if you can make it only once.

And it could use

INDEX(IsDeleted, ApprovalRequestID,  -- in either order for WHERE
      ModifiedDate,                  -- 3rd; for ORDER BY
      ApprovalStatus)                -- for "covering"


You have some LEFT JOINs.  Could the be changed to JOIN?

Change from

or
            (
                select count(*) 
                from ...
            ) &gt; 0


to

or EXISTS ( SELECT * from ... )


For this:

p_createdBy is null or p_createdBy = ''


Consider deciding on NULL or '', then sticking with it.  This gets rid of one OR.

Any place where you use OR, see if you can reformulate the query.

Testing against NULL always fails in inequalities, so

(p_createdDateFrom is not null and p_createdDateFrom &lt;= t.CreatedDate)


-->

p_createdDateFrom &lt;= t.CreatedDate


The main WHERE has at least 11 tests AND'd together.  Are all of them TRUE most of the time?  If not, then rearrange them so that the most likely to fail are first.

Rather than passing in NULL/blank for things you don't want tested, have your client software construct the query with just the things you do want tested.  Then we could discuss other options for indexes.

Please provide EXPLAIN SELECT ... to discuss further.