Most folks put in this situation will look for a quick win to buy the time necessary for the complete overhaul which Grant correctly recommends.

I think you have some scope for a quick win here by rewriting the function IF_GetPaymentBizDoc (referenced by the view VIEW_BIZPAYMENT) as an inline table-valued function.

Here's the rewritten function:

CREATE FUNCTION [dbo].[IF_GetPaymentBizDoc]

-- Replaces fn_GetPaymentBizDoc

(@numBizDocsPaymentDetId NUMERIC(8),

@numDomainId INT)

RETURNS TABLE AS

RETURN

WITH SourceData AS (

SELECT

Amount= cast(SUM(monAmount) as varchar(50)),

numCheckNo= isnull( CAST(numCheckNo AS VARCHAR(50)),'No-Chek'),

vcReference = isnull(vcReference,'No-Ref'),

vcMemo= isnull(vcMemo,'No-Memo' )

FROM VIEW_BIZDOCPAYMENT -- no definition in scripts

WHERE numBizDocsPaymentDetId = @numBizDocsPaymentDetId

AND numDomainId = @numDomainId

GROUP BY numCheckNo, vcReference, vcMemo

)

SELECT Narration = STUFF((

SELECT ',' +

'Ref: ' + vcReference +

' Memo: ' + vcMemo +

' Chek No: ' + numCheckNo +

' Amount: ' + Amount

FROM SourceData

ORDER BY numCheckNo, vcReference, vcMemo

FOR XML PATH('')),1,1,'')

Here's the rewritten view which references it:

ALTER VIEW [dbo].[VIEW_BIZPAYMENT]

AS

SELECT

o.numDomainId,

o.numBizDocsPaymentDetId,

x.Narration

FROM OpportunityBizDocsDetails o

CROSS APPLY dbo.IF_GetPaymentBizDoc (numBizDocsPaymentDetId, numDomainId) x

Note that the iTVF itself references a view which doesn't appear in your script. If you can find the view definition then sub it into the iTVF. With this done, you might consider reconstructing the iTVF so that it replaces VIEW_BIZPAYMENT completely.

This part of the query is related to the nested loops left outer join costing 68% of the total, it's well worth tackling.