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