Can you please help in tunning the attached query as , i am trying to run this query and it runs for several hours and it did not give any output.
Several hours is not all that long. Without an EXPLAIN ANALYZE, we could easily spend several hours scratching our heads and still get nowhere. So unless having this running cripples the rest of your system, please queue up another one and let it go longer. But first, do an ANALYZE (and preferably a VACUUM ANALYZE) on all the tables. If you have a test db which is a recent clone of production, you could do it there so as not to slow down production. The problem is that the row estimates must be way off (otherwise, it shouldn't take long) and if that is the case, we can't use the plan to decide much of anything, since we don't trust it.
In parallel you could start evicting table joins from the query to simplify it until it gets to the point where it will run, so you can then see the actual row counts. To do that it does help if you know what the intent of the query is (or for that matter, the text of the query--you attached the plan twice).