HI! As per my understanding posted almost all info. about the problem. Please let me know what exactly you required to solve this problem. I am really eager to have the solution :).
Also please note that I took the SP in SSMS and executed individual queries and found that the Final SIMPLE SELECT statement is taking more than 3 Minutes to list all records.
Please advice.