How to optimize query performance?
PAYMENT.iTransaction_num IN (SELECT SID FROM dbo.UF_CSVToTable(@sTransaction_num)
This usually performs better even if iTransaction_num needs to be cast to varchar:
CHARINDEX(','+ PAYMENT.iTransaction_num + ',', ','+ @sTransaction_num +',') > 0
That change may not affect the duration very much but, i still follow that pattern unless the amount of data is too large then i dump it to a temp table with a PK or something. And of course 'too large' depends because too large for one query may not be too large for another.
As for the rest, what is inside of 'bms_vw_Invoice_details'? The plan makes it look very complicated. Many of its JOINs operate as scans. Do you need all of its output columns? Can you refactor its contents into your query and leave out some tables? Is there a date on an index that could be used in a predicate that would limit the data?
If you broke this statement into logical pieces (dumping the results to temp tables) and then put it back together at the end you would more easily find what is making it slow and then recombine as you can with out losing the performance changes.