Pick a chunk of the query at the bottom of your stored procedure, run it in a query window with a couple of sensible parameters, and have a look at the execution plan. Since there are a few joins involving five or six different columns, and similarly complex WHERE clauses, it would seem likely that the existing indexing strategy isn't helping this query.

Not much else springs to mind except that the derived table SalesInvoiceHeader appears to be aggregated to the same level of granularity as the salesorderheader part of visitinfo. If you were to run the results of derived table SalesInvoiceHeader into a #temp table, then extracting the rows for the salesorderheader part of visitinfo from this #temp table would be a lot cheaper than reading the base tables. Performance (and readability=maintainability) can be significantly improved by breaking up a large query into smaller chunks using indexed #temp tables

Here's what looks to me to be a suitable chunk, reformatted for readability (please read up on table aliases and use them). It's from @RepType = '2', and it's the first query in the UNIONed query set.

-- @RepType = '2', first query in UNIONed query set

SELECT -- #1

VisitInfo.ExecutiveCode ,

VisitInfo.RetailerCode ,

VisitInfo.VisitNumber ,

ISNULL(SUM(InvoiceValue), 0) AS InvoiceValue ,

ISNULL(SUM(CRNValue), 0) AS CRNValue ,

ISNULL(SUM(DiscountValue), 0) AS DiscountValue ,

VisitInfo.VisitDate,'1' AS GroupType

FROM (

SELECT -- #2 -- Derived table visitinfo

A.BusinessUnit ,

A.RetailerCode ,

A.VisitNumber ,

A.ExecutiveCode ,

A.VisitDate

FROM RD.Retailer

OUTER APPLY (

SELECT TOP 4 * -- #3 Derived table A

FROM (

SELECT -- #4 Derived table AA

SalesInvoiceHeader.BusinessUnit ,

SalesInvoiceHeader.RetailerCode ,

SalesInvoiceHeader.VisitNumber ,

SalesInvoiceHeader.ExecutiveCode ,

ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate

FROM RD.SalesInvoiceHeader WITH (NOLOCK)

--INNER JOIN XA.fn_Territories(@BusinessUnit,

-- @User,

-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit

-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue

LEFT OUTER JOIN RD.SalesOrderHeader

ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit

AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode

AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode

AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode

AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode

AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber

WHERE

SalesInvoiceHeader.BusinessUnit = @BusinessUnit -- ### parameter

AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode

AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <= @AsAtDate -- ### parameter

UNION

SELECT -- #4 Derived table AA

VisitDetail.BusinessUnit ,

RetailerCode,

VisitDetail.VisitNumber ,

VisitDetail.ExecutiveCode ,

VisitHeader.VisitDate

FROM RD.VisitDetail WITH(NOLOCK)

INNER JOIN RD.VisitHeader

ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit

AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode

AND VisitDetail.VisitNumber = VisitHeader.VisitNumber

--INNER JOIN XA.fn_Territories(@BusinessUnit,

--@User,

--@PowerUser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit

--AND VisitDetail.TerritoryCode = Territory.MasterGroupValue

WHERE

VisitDetail.BusinessUnit = @BusinessUnit -- ### parameter

AND Retailer.BusinessUnit = VisitDetail.BusinessUnit

AND Retailer.RetailerCode = VisitDetail.RetailerCode

AND VisitDetail.Status <> '2'

AND VisitDate <= @AsAtDate -- ### parameter

) AS AA

ORDER BY

RetailerCode,

VisitDate DESC,

VisitNumber DESC

) AS A

) AS VisitInfo

OUTER APPLY (

SELECT -- #2 Derived table TotalInfo

SalesInvoiceHeader.BusinessUnit,

SalesInvoiceHeader.ExecutiveCode ,

SalesInvoiceHeader.RetailerCode ,

SalesInvoiceHeader.VisitNumber ,

SalesInvoiceHeader.TotalGoodsValue AS InvoiceValue ,

ISNULL(CreditNoteHeader.GoodsValue, 0)

- ISNULL(LineDiscountTotal, 0)

- ISNULL(HeaderDiscountTotal, 0) AS CRNValue ,

TotalHeaderDiscountValue

+ TotalLineDiscountValue

+ TotalSpecialDiscountValue

+ TotalOtherDiscountValue AS DiscountValue

FROM (

SELECT -- #3 Derived table SalesInvoiceHeader

SalesInvoiceHeader.BusinessUnit,

SalesInvoiceHeader.TerritoryCode,

SalesInvoiceHeader.SalesCategoryCode,

ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS OrderDate,

SalesInvoiceHeader.RetailerCode,

SalesInvoiceHeader.ExecutiveCode,

SalesInvoiceHeader.VisitNumber,

SUM(SalesInvoiceHeader.TotalGoodsValue) AS TotalGoodsValue,

SUM(SalesInvoiceHeader.TotalHeaderDiscountValue) AS TotalHeaderDiscountValue,

SUM(SalesInvoiceHeader.TotalLineDiscountValue) AS TotalLineDiscountValue,

SUM(SalesInvoiceHeader.TotalSpecialDiscountValue) AS TotalSpecialDiscountValue,

SUM(SalesInvoiceHeader.TotalOtherDiscountValue) AS TotalOtherDiscountValue

FROM RD.SalesInvoiceHeader WITH(NOLOCK)

LEFT OUTER JOIN RD.SalesOrderHeader soh

ON RD.SalesInvoiceHeader.BusinessUnit = RD.SalesOrderHeader.BusinessUnit

AND RD.SalesInvoiceHeader.TerritoryCode = RD.SalesOrderHeader.TerritoryCode

AND RD.SalesInvoiceHeader.ExecutiveCode = RD.SalesOrderHeader.ExecutiveCode

AND RD.SalesInvoiceHeader.SalesCategoryCode = RD.SalesOrderHeader.SalesCategoryCode

AND RD.SalesInvoiceHeader.RetailerCode = RD.SalesOrderHeader.RetailerCode

AND RD.SalesInvoiceHeader.VisitNumber = RD.SalesOrderHeader.VisitNumber

WHERE SalesInvoiceHeader.Status = '1' -- why is this filter missing from VisitInfo?

GROUP BY

SalesInvoiceHeader.BusinessUnit,

SalesInvoiceHeader.TerritoryCode,

SalesInvoiceHeader.SalesCategoryCode,

ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate),

SalesInvoiceHeader.RetailerCode,

SalesInvoiceHeader.ExecutiveCode,

SalesInvoiceHeader.VisitNumber

) AS SalesInvoiceHeader

LEFT OUTER JOIN (

SELECT -- #3 Derived table CreditNoteHeader

BusinessUnit,

TerritoryCode,

SalesCategoryCode,

CreditDate,

RetailerCode,

SUM(CreditNoteHeader.GoodsValue) AS GoodsValue,

SUM(LineDiscountTotal) AS LineDiscountTotal,

SUM(HeaderDiscountTotal) AS HeaderDiscountTotal

FROM RD.CreditNoteHeader WITH(NOLOCK)

WHERE Status = '1'

GROUP BY BusinessUnit, TerritoryCode, SalesCategoryCode, CreditDate, RetailerCode

) AS CreditNoteHeader

ON SalesInvoiceHeader.BusinessUnit = CreditNoteHeader.BusinessUnit

AND SalesInvoiceHeader.TerritoryCode = CreditNoteHeader.TerritoryCode

AND SalesInvoiceHeader.SalesCategoryCode = CreditNoteHeader.SalesCategoryCode

AND SalesInvoiceHeader.OrderDate = CreditNoteHeader.CreditDate

AND SalesInvoiceHeader.RetailerCode = CreditNoteHeader.RetailerCode

WHERE VisitInfo.BusinessUnit = SalesInvoiceHeader.BusinessUnit

AND VisitInfo.RetailerCode = SalesInvoiceHeader.RetailerCode

AND VisitInfo.VisitNumber = SalesInvoiceHeader.VisitNumber

AND VisitInfo.VisitDate = SalesInvoiceHeader.OrderDate

) AS TotalInfo

INNER JOIN #VRRPT127_Executive AS Executive ON VisitInfo.ExecutiveCode = Executive.ExecutiveCode

INNER JOIN #VRRPT127_RETAILER AS Retailer ON VisitInfo.RetailerCode = Retailer.RetailerCode

WHERE VisitInfo.BusinessUnit = @BusinessUnit

GROUP BY

VisitInfo.ExecutiveCode,

VisitInfo.RetailerCode ,

VisitInfo.VisitNumber ,

VisitInfo.VisitDate