Declare @From float=Convert(float,Convert(DateTime,'01 Apr 2000'))
Declare @To float=Convert(float,Convert(DateTime,'08 Jul 2022'))
Declare @Tab Table(ID bigint Identity(1,1),VoucherNo Nvarchar(500),Mth Nvarchar(500),DocDate DateTime,Job Nvarchar(500),ProjectName Nvarchar(500),TypeofProject Nvarchar(500),PartyName Nvarchar(500),GrpName Nvarchar(500),ExecutiveName Nvarchar(500),ClientExeName Nvarchar(500),DeptName Nvarchar(500),DeptHead Nvarchar(500),LCBillAmt float,ExchgRate float,FXBillAmt float,XchgAmt float,RcptNo Nvarchar(500),RcptDt Nvarchar(500),RcptExRt Nvarchar(500),ExRateDiff Nvarchar(500),DiffFX float)
Insert into @Tab
Select *,ExchgRate-RcptExRt ExRateDiff,(ExchgRate-RcptExRt)*FXBillAmt DiffFX from(
--Sales Type
Select IDD.VoucherNo,Left(DateName(Month,Convert(DateTime,IDD.DocDate)),3) + '-' + Right(DateName(YY,Convert(DateTime,IDD.DocDate)),2) Mth
,Convert(DateTime,IDD.DocDate) DocDate,C11.ccAlpha13 Job,C11.Name ProjectName,Lkup.Name TypeofProject,Acc.AccountName PartyName,Grp.AccountName GrpName
,Sls.Name ExecutiveName,C11.ccAlpha3 ClientExeName,Dep.Name DeptName,C15.Name DeptHead,CDN.dcNum11 LCBillAmt,IDD.ExchangeRate ExchgRate,IDD.GrossFC FXBillAmt
,IDD.GrossFC*IDD.ExchangeRate XchgAmt
,Stuff((Select ',' +  DocNo from Com_Billwise with(nolock) Where RefDocNo=IDD.VoucherNo Order by DocNo for XML PATH('')),1,1,'') RcptNo
,Stuff((Select ',' +  Convert(Varchar,Convert(DateTime,CB.DocDate),106) from Com_Billwise CB with(nolock) Where RefDocNo=IDD.VoucherNo Order by DocNo for XML PATH('')),1,1,'') RcptDt
,(Select ExchangeRate from Acc_DocDetails with(nolock) Where VoucherNo IN(Stuff((Select ',' +  DocNo from Com_Billwise with(nolock) Where RefDocNo=IDD.VoucherNo Order by DocNo for XML PATH('')),1,1,'')) AND ExchangeRate>1) RcptExRt
from Inv_DocDetails IDD with(nolock)
Join ACC_DocDetails ACD with(nolock) ON IDD.InvDocDetailsID=ACD.InvDocDetailsID
left join COM_DocCCData CDC with(nolock) on IDD.InvDocDetailsID=CDC.InvDocDetailsID
left join COM_DocNumData CDN with(nolock) on IDD.InvDocDetailsID=CDN.InvDocDetailsID
left join COM_CC50011 C11 with(nolock) on CDC.dcCCNID11=C11.NodeID
Left Join COM_Lookup Lkup with(nolock) ON C11.ccAlpha19=Lkup.NodeID AND IsNumeric(C11.ccAlpha19)=1 
Left Join COM_Salesman Sls with(nolock) ON CDC.dcCCNID5=Sls.NodeID
Left Join COM_Department Dep with(nolock) ON CDC.dcCCNID4=Dep.NodeID
left join COM_CCCCDATA CCD with(nolock) on Dep.NodeID=CCD.NodeID AND CCD.CostCenterID=50004
left join COM_CC50015 C15 with(nolock) on CCD.CCNID15=C15.NodeID
left Join Acc_Accounts Acc with(nolock) ON IDD.DebitAccount=Acc.AccountID 
left Join Acc_Accounts Grp with(nolock) ON Acc.ParentID=Grp.AccountID
Where IDD.VoucherType=-1 AND IDD.ExchangeRate>1 AND IDD.DocDate Between @From AND @To 
UNION ALL --Purchase Type
Select IDD.VoucherNo,Left(DateName(Month,Convert(DateTime,IDD.DocDate)),3) + '-' + Right(DateName(YY,Convert(DateTime,IDD.DocDate)),2) Mth
,Convert(DateTime,IDD.DocDate) DocDate,C11.ccAlpha13 Job,C11.Name ProjectName,Lkup.Name TypeofProject,Acc.AccountName PartyName,Grp.AccountName GrpName
,Sls.Name ExecutiveName,C11.ccAlpha3 ClientExeName,Dep.Name DeptName,C15.Name DeptHead,CDN.dcNum11 LCBillAmt,IDD.ExchangeRate ExchgRate,IDD.GrossFC FXBillAmt
,IDD.GrossFC*IDD.ExchangeRate XchgAmt
,Stuff((Select ',' +  DocNo from Com_Billwise with(nolock) Where RefDocNo=IDD.VoucherNo Order by DocNo for XML PATH('')),1,1,'') RcptNo
,Stuff((Select ',' +  Convert(Varchar,Convert(DateTime,CB.DocDate),106) from Com_Billwise CB with(nolock) Where RefDocNo=IDD.VoucherNo Order by DocNo for XML PATH('')),1,1,'') RcptDt
,(Select ExchangeRate from Acc_DocDetails with(nolock) Where VoucherNo IN(Stuff((Select ',' +  DocNo from Com_Billwise with(nolock) Where RefDocNo=IDD.VoucherNo Order by DocNo for XML PATH('')),1,1,'')) AND ExchangeRate>1) RcptExRt
from Inv_DocDetails IDD with(nolock)
Join ACC_DocDetails ACD with(nolock) ON IDD.InvDocDetailsID=ACD.InvDocDetailsID
left join COM_DocCCData CDC with(nolock) on IDD.InvDocDetailsID=CDC.InvDocDetailsID
left join COM_DocNumData CDN with(nolock) on IDD.InvDocDetailsID=CDN.InvDocDetailsID
left join COM_CC50011 C11 with(nolock) on CDC.dcCCNID11=C11.NodeID
Left Join COM_Lookup Lkup with(nolock) ON C11.ccAlpha19=Lkup.NodeID AND IsNumeric(C11.ccAlpha19)=1 
Left Join COM_Salesman Sls with(nolock) ON CDC.dcCCNID5=Sls.NodeID
Left Join COM_Department Dep with(nolock) ON CDC.dcCCNID4=Dep.NodeID
left join COM_CCCCDATA CCD with(nolock) on Dep.NodeID=CCD.NodeID AND CCD.CostCenterID=50004
left join COM_CC50015 C15 with(nolock) on CCD.CCNID15=C15.NodeID
left Join Acc_Accounts Acc with(nolock) ON IDD.CreditAccount=Acc.AccountID 
left Join Acc_Accounts Grp with(nolock) ON Acc.ParentID=Grp.AccountID
Where IDD.VoucherType=1 AND IDD.ExchangeRate>1 AND IDD.DocDate Between @From AND @To 
UNION ALL --Opening Bal
Select ACD.VoucherNo,Left(DateName(Month,Convert(DateTime,ACD.DocDate)),3) + '-' + Right(DateName(YY,Convert(DateTime,ACD.DocDate)),2) Mth
,Convert(DateTime,ACD.DocDate) DocDate,C11.ccAlpha13 Job,C11.Name ProjectName,Lkup.Name TypeofProject
,Case When Acc.AccountID=-100 Then Acc1.AccountName Else Acc.AccountName End PartyName
,Case When Acc.AccountID=-100 Then Grp1.AccountName Else Grp.AccountName End GrpName
,Sls.Name ExecutiveName,C11.ccAlpha3 ClientExeName,Dep.Name DeptName,C15.Name DeptHead,CDN.dcNum11 LCBillAmt,ACD.ExchangeRate ExchgRate,ACD.AmountFC FXBillAmt
,ACD.AmountFC*ACD.ExchangeRate XchgAmt
,Stuff((Select ',' +  DocNo from Com_Billwise with(nolock) Where RefDocNo=ACD.VoucherNo Order by DocNo for XML PATH('')),1,1,'') RcptNo
,Stuff((Select ',' +  Convert(Varchar,Convert(DateTime,CB.DocDate),106) from Com_Billwise CB with(nolock) Where RefDocNo=ACD.VoucherNo Order by DocNo for XML PATH('')),1,1,'') RcptDt
,(Select ExchangeRate from Acc_DocDetails with(nolock) Where VoucherNo IN(Stuff((Select ',' +  DocNo from Com_Billwise with(nolock) Where RefDocNo=ACD.VoucherNo Order by DocNo for XML PATH('')),1,1,'')) AND ExchangeRate>1) RcptExRt
from ACC_DocDetails ACD with(nolock) 
left join COM_DocCCData CDC with(nolock) on ACD.AccDocDetailsID=CDC.AccDocDetailsID
left join COM_DocNumData CDN with(nolock) on ACD.AccDocDetailsID=CDN.AccDocDetailsID
left join COM_CC50011 C11 with(nolock) on CDC.dcCCNID11=C11.NodeID
Left Join COM_Lookup Lkup with(nolock) ON C11.ccAlpha19=Lkup.NodeID AND IsNumeric(C11.ccAlpha19)=1 
Left Join COM_Salesman Sls with(nolock) ON CDC.dcCCNID5=Sls.NodeID
Left Join COM_Department Dep with(nolock) ON CDC.dcCCNID4=Dep.NodeID
left join COM_CCCCDATA CCD with(nolock) on Dep.NodeID=CCD.NodeID AND CCD.CostCenterID=50004
left join COM_CC50015 C15 with(nolock) on CCD.CCNID15=C15.NodeID
left Join Acc_Accounts Acc with(nolock) ON ACD.DebitAccount=Acc.AccountID 
left Join Acc_Accounts Acc1 with(nolock) ON ACD.CreditAccount=Acc1.AccountID
left Join Acc_Accounts Grp with(nolock) ON Acc.ParentID=Grp.AccountID 
left Join Acc_Accounts Grp1 with(nolock) ON Acc1.ParentID=Grp1.AccountID 
Where ACD.CostCenterID=40016 AND ACD.ExchangeRate>1 AND ACD.DocDate Between @From AND @To 
) As T
Where RcptNo Is Not Null
Order by RcptNo

Declare @Tab2 Table(ID bigint Identity(1,1),VoucherNo Nvarchar(500),FXBillAmt float,ExchangeRate float,ExRateDiff float,DiffFX float)
Insert into @Tab2
Select T.VoucherNo,T.FXBillAmt,ACD.ExchangeRate,T.ExchgRate-ACD.ExchangeRate,CDN.dcNum5
from @Tab T
Left Join COM_Billwise CB with(nolock) ON T.VoucherNo=CB.RefDocNo
Left Join Acc_DocDetails ACD with(nolock) ON CB.DocNo=ACD.VoucherNo AND ACD.ExchangeRate>1
Left Join Com_DocNumData CDN with(nolock) ON ACD.AccDocDetailsID=CDN.AccDocDetailsID
Where RcptNo like '%,%'
Group by T.VoucherNo,T.FXBillAmt,ACD.ExchangeRate,T.ExchgRate,CDN.dcNum5

Declare @Count bigint,@i bigint=1,@VoucherNo Nvarchar(500)
Select @Count=Count(*) from @Tab2
While(@i<=@Count)
Begin
	Select @VoucherNo=VoucherNo from @Tab2 Where ID=@i
	Update @Tab Set RcptExRt=Stuff((Select ', ' +  Convert(Varchar,ExchangeRate) from @Tab2 Where VoucherNo=@VoucherNo for XML PATH('')),1,1,'') 
	,ExRateDiff=Stuff((Select ', ' + Convert(Varchar,ExRateDiff) from @Tab2 Where VoucherNo=@VoucherNo for XML PATH('')),1,1,'')
	,DiffFX=(Select Sum(DiffFX) from @Tab2 Where VoucherNo=@VoucherNo)
	Where VoucherNo=@VoucherNo

	Set @i=@i +1 
End

Select VoucherNo,DocDate,Job,ProjectName,TypeofProject,PartyName,GrpName,ExecutiveName,ClientExeName,DeptName,ExchgRate
,DeptHead,LCBillAmt,ExchgRate,FXBillAmt,XchgAmt,RcptNo,RcptDt,RcptExRt,ExRateDiff,DiffFX from @Tab
Group by  VoucherNo,DocDate,Job,ProjectName,TypeofProject,PartyName,GrpName,ExecutiveName,ClientExeName,DeptName,ExchgRate
,DeptHead,LCBillAmt,ExchgRate,FXBillAmt,XchgAmt,RcptNo,RcptDt,RcptExRt,ExRateDiff,DiffFX