Thanks for your reply.

I tuned it to better than earlier, since earlier query was bit critical with joins.

But I am not that much proficient to minimize the query and tune it.

I created the recommended Index, but now its in Index scan.

Please find the attached execution plan and suggest me.