Thanks for posting this. The first cursor loop (ARTransaction_Cursor) can be easily converted to a set-based query as follows;

INSERT INTO TemplateARTransactions (

TransDate

,GLDate

,HeaderAttribute

,Reference

,LinkToLine

,Currency

,Source

,Type

,CustCode

,CustName

,SalesPerson

,MemoLine

,Quantity

,UnitPrice

,Amount

,TaxCode

,PONumber

,TransDescription

,LineType

,PaymentTerms

,Organisation

,Location

,LocationCode

,RecordNumber

,BusinessDate

,VersionNumber

,LastModifiedDate

)

SELECT

@BusinessDate

, Convert(Datetime,@BusinessDate,105)

,@LocationCode+cast(cn.FiscalYear as varchar(5)) + 'CN'+cast(cn.DocNo as Varchar(20))

,@LocationCode+cast(cn.FiscalYear as varchar(5)) +'CN'+cast(cn.DocNo as Varchar(20))+'/'+CAST(LineItemNo as Varchar(5))

, Null

,'INR'

,'EPOSS',

@LocationCode+'GH'

,Null,

@LocationCode+'GH

',@LocationCode

,'GH Closure'

,'1'

,cn.Amount

,cn.Amount

,Null

,'GH'+cast(RefGHAccountDocNo as varchar (20))

,@LocationCode

,'Line'

,'D001'

,'B'+@LocationCode

,'B'+@LocationCode

,@LocationCode

,MaxRecordNo

,@BusinessDate

,Null

,@BusinessDate

FROM (

SELECT

cn.Amount, -- @Amount

cn.FiscalYear, -- @FiscalYear

RefGHAccountDocNo, -- @AccountNo

cn.DocNo, -- @CNNo

cn.DocDate, -- @BusinessDateTemp

LineItemNo = ROW_NUMBER() OVER(ORDER BY GH.DocNo), -- check that this is the correct sort order

MaxRecordNo = x.iMaxRecordNo + ROW_NUMBER() OVER(ORDER BY GH.DocNo) -- and this

FROM GH

INNER Join GHDetails d

ON GH.DocNo = d.RefGHAccountDocNo

and GH.FiscalYear = d.REFAccountCodeFiscalYear

and GH.LocationCode = d.LocationCode

INNER Join CreditNote cn

ON d.DocNo = cn.RefDocNo

and d.FiscalYear = cn.RefFiscalYear

and d.Locationcode = cn.Locationcode

INNER join LocationMaster lm

ON cn.LocationCode = lm.LocationCOde

CROSS APPLY (

SELECT iMaxRecordNo = MAX(RecordNumber) + 1

FROM TemplateARTransactions

WHERE LocationCode = @LocationCode

) x

WHERE cn.DocDate = @BusinessDate -- # EOD Data Fetch 15-Jul-08

AND OwnerInfo <> 'Level 3'

AND GH.Status <> 0

AND GH.LocationCode = @LocationCode -- parameter

And CreditNoteType = 'GH'

AND RefDocType = 'GH'

and cn.DocNo > 0 --CNNo>0

) d

Untested - no sample data.

You can see that it's very simple to do. This should perform far faster than the cursor equivalent.