I don't know, what query actually runs? There is a lot if logic in that code that will dynamically build a wide range of queries based on various other data. Do all these variations take such a long time or only some of them? What does your database tools say takes such a long time? Are you getting any unexpected cartesian joins in all the jumble of code?
What happend if you simplify the query to a single element? Then if that works well, slowely add the extra logic one piece at a time.
When I use the problem_code in (1) on CF side it takes more than a minute to pull 3782 records however using same thing in oracle db takes 433
Two things that I noticed which will slow down your query are:
1. Using functions in the where clause. Specifically
And lower(TICKET_NUMBER) like lower('#attr.tick#%')
2. Not sure why you have a left join to your subquery instead of an inner join.
You also might not be joining to your subquery on enough fields. It would be easier to tell if you did all the joins at the start of your where clause instead of scattered thoughout.