Sage Evolution
Space shortcuts
Page tree

Versions Compared

Old Version 1

changes.mady.by.user Alexander Toufexis

Saved on

compared with

New Version Current

changes.mady.by.user Alexander Toufexis

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Code Block
languagesql
alter procedure _as_FCR_RevalueBank
	@nProfitAmount float
	,@nLossAmount float
	,@nProfitAccount int
	,@nLossAccount int
	,@nBankAccountLink int
	,@nExchangeRateAmount float
	,@nTransactionDate datetime
	,@nPexLex varchar(10)
	,@nReference varchar(1000)
	,@nIsBranch bit
	,@noldDate datetime
	,@nProjectID int

AS
BEGIN

/*usage:
exec _as_FCR_RevalueBank
	@nProfitAmount = 0
	,@nLossAmount = 5654.74
	,@nProfitAccount = 13704
	,@nLossAccount = 13754
	,@nBankAccountLink = 17689
	,@nExchangeRateAmount = 0.041883
	,@nTransactionDate = '2023-06-19'
	,@nPexLex = 'PEX'
	,@nReference = 'FXR0001'
	,@nIsBranch = 0
	,@noldDate = '2023-06-19'
	,@nProjectID = 0

	select top 10 a.master_sub_account,* from postgl 
		left join accounts a on postgl.accountlink = a.accountlink 
		order by postgl.autoidx desc



*/

SET NOCOUNT ON;

--DECLARE @nProfitAmount float
--DECLARE @nLossAmount float
--DECLARE @nProfitAccount int
--DECLARE @nLossAccount int
--DECLARE @nAccountLink int
--DECLARE @nExchangeRateAmount float
--DECLARE @nTransactionDate datetime
--DECLARE @nPexLex varchar(10)
--DECLARE @nReference varchar(1000)
--DECLARE @nIsBranch bit
--DEClARE @noldDate datetime
--DECLARE @nProjectID int

DECLARE @RC int
DECLARE @RC2 int
DECLARE @TxDate datetime
DECLARE @Id varchar(5)
DECLARE @ClientLink int
DECLARE @TrCodeID int
DECLARE @DebitAP float
DECLARE @CreditAP float
DECLARE @DebitGL1 float
DECLARE @CreditGL1 float
DECLARE @DebitGL2 float
DECLARE @CreditGL2 float
DECLARE @CurrencyID int
DECLARE @ExchangeRate float
DECLARE @ForeignDebit float
DECLARE @ForeignCredit float
DECLARE @Description varchar(40)
DECLARE @TaxTypeID int
DECLARE @Reference varchar(20)
DECLARE @Order_No varchar(20)
DECLARE @ExtOrderNum varchar(20)
DECLARE @Audit_No VARCHAR(20)
DECLARE @Tax_Amount float
DECLARE @ForeignTaxAmount float
DECLARE @Project int
DECLARE @Outstanding float
DECLARE @ForeignOutstanding float
DECLARE @InvNumKey int
DECLARE @CRCCheck float
DECLARE @DTStamp datetime
DECLARE @UserName varchar(20)
DECLARE @Reference2 varchar(20)
DECLARE @SettlementTermsID int
DECLARE @Profit float
DECLARE @Loss float
DECLARE @ProfitAccount int
DECLARE @LossAccount int
DECLARE @BankAccLink int
DECLARE @GLContraAccLink int
DECLARE @VenClassID int
DECLARE @DrCrAccount int
DECLARE @Reconciled varchar(2)
DECLARE @JobCodeLink int
DECLARE @MFPID int
DECLARE @cPayeeName varchar(100)
DECLARE @bPrintCheque bit
DECLARE @Period int
DECLARE @AutoIdx bigint
DECLARE @TxBranchID int
DECLARE @RepID int
DECLARE @JCRepCost float
DECLARE @IsJCDocLine bit
DECLARE @IsSTGLDocLine bit
DECLARE @iInvLineID bigint
DECLARE @AutoIdx2 bigint
DECLARE @AutoIdx3 bigint
DECLARE @AuditTemp float
DECLARE @cBankRef varchar(20)
DECLARE @bMaintainBranchID bit
declare @GLTaxAccountID int
declare @cLineUserFields Varchar(max)

declare @LinkAccCode int
declare @TillID int
declare @iMBPropertyID int
declare @iMBPortionID int
declare @iMBServiceID int
declare @iMBMeterID int
declare @iMBPropertyPortionServiceID int

-- TODO: Set parameter values here.
SELECT @Profit = @nProfitAmount
SELECT @Loss = @nLossAmount
SELECT @ProfitAccount = @nProfitAccount
SELECT @LossAccount = @nLossAccount
SELECT @ExchangeRate = @nExchangeRateAmount
SELECT @TxDate = @nTransactionDate
SELECT @TrCodeID = (SELECT idTrCodes FROM TrCodes WHERE Code = @nPexLex AND iModule = 4)--6=AP, 5=AR, 4=GL

select @AutoIdx = 0
select @TxBranchID = 0
select @RepID = 0
select @JCRepCost = 0
select @IsJCDocLine = 0
select @IsSTGLDocLine = 0
select @iInvLineID = 0

select @LinkAccCode = 0
select @TillID = 0
select @iMBPropertyID = 0
select @iMBPortionID = 0
select @iMBServiceID = 0
select @iMBMeterID = 0
select @iMBPropertyPortionServiceID = 0

SELECT @Reference = @nReference

SELECT @DrCrAccount = @nBankAccountLink
SELECT @Reconciled = ''
SELECT @JobCodeLink = 0
SELECT @MFPID = 0
SELECT @cPayeeName = ''
SELECT @bPrintCheque = 0
SELECT @cBankRef = ''
SELECT @bMaintainBranchID = 1

-- **** EARLY BREAK IF AMOUNT IS 0****
IF ((ROUND(@Profit + @Loss,2)) = 0)
 begin 
 SELECT 0 
 Return
end
--NOW: WE SET THE CONTRA ACCOUNT, AND THE DEBIT/CREDIT AMOUNT
IF (ABS(@Profit) > 0)
BEGIN
 --PROFIT > 0
 SELECT @GLContraAccLink = @ProfitAccount
 IF (@Profit > 0) --if profit, then:
   BEGIN
	--Profit is positive, so DEBIT AP Account (Credit GL Contra)
    SELECT @DebitAP = @Profit
	SELECT @CreditAP = 0
	SELECT @DebitGL1 = @Profit
	SELECT @CreditGL1 = 0
	SELECT @DebitGL2 = 0
	SELECT @CreditGL2 = @Profit
   END
 ELSE
	BEGIN
	 --Profit is negative, so this is REVERSE TRANSACTION
	 SELECT @DebitAP = 0
	 SELECT @CreditAP = -@Profit
	SELECT @DebitGL1 = 0
	SELECT @CreditGL1 = -@Profit
	SELECT @DebitGL2 = -@Profit
	SELECT @CreditGL2 = 0
	END
END
ELSE
 BEGIN
 --Loss > 0
 SELECT @GLContraAccLink = @LossAccount
 IF (@Loss > 0) --if loss, then:
   BEGIN
	--loss is positive, so CREDIT AP Account (Debit GL Contra)
    SELECT @DebitAP = 0
	SELECT @CreditAP = @Loss
	SELECT @DebitGL1 = 0
	SELECT @CreditGL1 = @Loss
	SELECT @DebitGL2 = @Loss
	SELECT @CreditGL2 = 0
   END
 ELSE
	BEGIN
	 --Loss is negative, so this is REVERSE TRANSACTION
	 SELECT @DebitAP = -@Loss
	 SELECT @CreditAP = 0
	SELECT @DebitGL1 = -@Loss
	SELECT @CreditGL1 = 0
	SELECT @DebitGL2 = 0
	SELECT @CreditGL2 = -@Loss
	END
 END


SELECT @Id = 'CB'

SELECT @CurrencyID = 0

SELECT @ForeignDebit = 0
SELECT @ForeignCredit = 0
SELECT @Description = 'FX Revaluation @ ' + CAST(ROUND(@ExchangeRate,6) AS VARCHAR(10))
SELECT @TaxTypeID = 0

SELECT @Order_No = ''
SELECT @ExtOrderNum = ''
select @cLineUserFields = ''
select @GLTaxAccountID = 0

EXEC @AuditTemp = _bspNextAuditNo;

select @Audit_No = CASE 
	WHEN @nIsBranch = 1 THEN Cast(@TxBranchID as varchar(20)) + '.' + CAST(@AuditTemp as varchar(10)) + '.0001'
	ELSE CAST(@AuditTemp as varchar(10)) + '.0001' END; --'GET NEXT AUDIT NO!!!!' --Check SP _bspNextAuditNo

SELECT @Tax_Amount = 0
SELECT @ForeignTaxAmount = 0
SELECT @Project = @nProjectID
SELECT @Outstanding = 0 --MMMM CHECK!
SELECT @ForeignOutstanding = 0
SELECT @InvNumKey = 0
SELECT @CRCCheck = 0
SELECT @DTStamp = GETDATE()
SELECT @UserName = 'Asamco' --misschien nog veranderen?
SELECT @Reference2 = 'FX Reval ' + Convert(varchar(25), @noldDate, 112) -- (YYYYMMDD)
SELECT @SettlementTermsID = 0
Select @Period = (Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @TxDate)

BEGIN TRY
BEGIN TRANSACTION

--NOW EXECUTE LINE 1 OF GL Tx
EXECUTE @RC = [_bspPostGLTrans]

@AutoIdx2 OUTPUT
  ,@TxDate
  ,@Id
  ,@nBankAccountLink
  ,@TrCodeID
  ,@DebitGL1
  ,@CreditGL1
  ,@CurrencyID
  ,@ExchangeRate
  ,@ForeignDebit
  ,@ForeignCredit
  ,@Description
  ,@TaxTypeID
  ,@Reference
  ,@Order_No
  ,@ExtOrderNum
  ,@Audit_No
  ,@Tax_Amount
  ,@ForeignTaxAmount
  ,@Project
  ,@Period
  ,@Reconciled
  ,@DrCrAccount
  ,@JobCodeLink
  ,@MFPID
  ,@UserName
  ,@cPayeeName
  ,@bPrintCheque
  ,@Reference2
,@TxBranchID
,@RepID
,@JCRepCost
,@IsJCDocLine
,@IsSTGLDocLine
,@iInvLineID
,@cBankRef
,@bMaintainBranchID
  ,@GLTaxAccountID
  ,@cLineUserFields

--NOW EXECUTE LINE 2 OF GL Tx
EXECUTE @RC = [_bspPostGLTrans]

@AutoIdx3 OUTPUT
  ,@TxDate
  ,@Id
  ,@GLContraAccLink
  ,@TrCodeID
  ,@DebitGL2
  ,@CreditGL2
  ,@CurrencyID
  ,@ExchangeRate
  ,@ForeignDebit
  ,@ForeignCredit
  ,@Description
  ,@TaxTypeID
  ,@Reference
  ,@Order_No
  ,@ExtOrderNum
  ,@Audit_No
  ,@Tax_Amount
  ,@ForeignTaxAmount
  ,@Project
  ,@Period
  ,@Reconciled
  ,@DrCrAccount
  ,@JobCodeLink
  ,@MFPID
  ,@UserName
  ,@cPayeeName
  ,@bPrintCheque
  ,@Reference2           
,@TxBranchID 
,@RepID
,@JCRepCost 
,@IsJCDocLine 
,@IsSTGLDocLine 
,@iInvLineID
,@cBankRef 
,@bMaintainBranchID 
  ,@GLTaxAccountID
  ,@cLineUserFields
Select @AutoIdx;

COMMIT TRANSACTION
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	THROW;
END CATCH

END -- sproc



Code Block
languagesql
titleCalculate FX Revaluation on Bank
collapsetrue
SET ARITHABORT ON
DECLARE @RevalueDate datetime
SELECT @RevalueDate = '2023-06-19'

--select * from TrCodes where Code = 'PEX'
--select * from [Gender Links (Online DB)].dbo.TrCodes where code = 'PEX'
--insert into TrCodes (iModule,code,linkid, Description,DebitTrans,tax,rep,Account1Link,Account2Link,TaxAccountLink,GLPrompt,TaxTypeID,SplitTr,bSalesFilter,bAllowSubAccTrans,bSettlementDisc,iDtTaxGroupID,iCtTaxGroupID,iTaxGroupID,iMBServiceID,TrCodes_iBranchID)
--	(select iModule,'PEX',linkid, 'PEX-LEX on CCE',DebitTrans,tax,rep,Account1Link,Account2Link,TaxAccountLink,GLPrompt,TaxTypeID,SplitTr,bSalesFilter,bAllowSubAccTrans,bSettlementDisc,iDtTaxGroupID,iCtTaxGroupID,iTaxGroupID,iMBServiceID,TrCodes_iBranchID from TrCodes where idTrCodes = 2)

DECLARE @PeriodID bigint
SELECT @PeriodID = (SELECT max(idPeriod) FROM _etblPeriod WHERE dPeriodDate <= @RevalueDate )

SELECT
Accounts.AccountLink
,Master_Sub_Account as Account
,Accounts.Description as Name
--,ROUND(COALESCE((SELECT (Actual_Deb00-Actual_Cred00) FROM _etblAccBlnc where MasterSubLink = PostGL.AccountLink),0)+SUM(PostGL.Debit - PostGL.Credit),2) as HomeBalance
--,ROUND(COALESCE((SELECT (Actual_ForeignDeb00-Actual_ForeignCred00) FROM _etblAccBlnc where MasterSubLink = PostGL.AccountLink),0)+SUM(PostGL.fForeignDebit - PostGL.fForeignCredit),2) as ForeignBalance
,HomeBalance = ROUND(
	COALESCE((SELECT SUM([fBFDebit]-[fBFCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(Debit-Credit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0),2)
,ForeignBalance = ROUND(
	COALESCE((SELECT SUM([fBFForeignDebit]-[fBFForeignCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualForeignDebit - BLC.fActualForeignCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID < @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(fForeignDebit-fForeignCredit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period >= @PeriodID AND P2.TxDate <= @RevalueDate),0),2)
,Currency.CurrencyCode as Currency
,COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0) as RevalueRate
,NewLocalBalance = ROUND((COALESCE((SELECT SUM([fBFForeignDebit]-[fBFForeignCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualForeignDebit - BLC.fActualForeignCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(fForeignDebit-fForeignCredit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0))
    * COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0),2)
,Profit =
  CASE
   WHEN
    (
    --New Local Balance
    ROUND((COALESCE((SELECT SUM([fBFForeignDebit]-[fBFForeignCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualForeignDebit - BLC.fActualForeignCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(fForeignDebit-fForeignCredit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0))
    * COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0),2)
    --MINUS Current Home Balance
    -
    ROUND(COALESCE((SELECT SUM([fBFDebit]-[fBFCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(Debit-Credit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0),2)
    ) > 0
    AND COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0) > 0
   THEN
    ROUND((COALESCE((SELECT SUM([fBFForeignDebit]-[fBFForeignCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualForeignDebit - BLC.fActualForeignCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(fForeignDebit-fForeignCredit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0))
    * COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0),2)
    --MINUS Current Home Balance
    -
    ROUND(COALESCE((SELECT SUM([fBFDebit]-[fBFCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(Debit-Credit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0),2)
   ELSE
    0
   END
,Loss = CASE
   WHEN
    (
    --New Local Balance
    ROUND((COALESCE((SELECT SUM([fBFForeignDebit]-[fBFForeignCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualForeignDebit - BLC.fActualForeignCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(fForeignDebit-fForeignCredit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0))
    * COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0),2)
    --MINUS Current Home Balance
    -
    ROUND(COALESCE((SELECT SUM([fBFDebit]-[fBFCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(Debit-Credit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0),2)
    ) < 0
    AND COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0) > 0
   THEN
    -(
    ROUND((COALESCE((SELECT SUM([fBFForeignDebit]-[fBFForeignCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualForeignDebit - BLC.fActualForeignCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(fForeignDebit-fForeignCredit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0))
    * COALESCE((SELECT fBuyRate FROM CurrencyHist WHERE CurrencyHist.iCurrencyID = COALESCE(Accounts.iForeignBankCurrencyID,0) AND dRateDate = @RevalueDate),0),2)
    --MINUS Current Home Balance
    -
    ROUND(COALESCE((SELECT SUM([fBFDebit]-[fBFCredit]) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
	+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID <= @PeriodID AND BLC.iAccBlncPeriodID > 0),0)
    + COALESCE((SELECT SUM(Debit-Credit) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period > @PeriodID AND P2.TxDate <= @RevalueDate),0),2)
    )
   ELSE
    0
   END

,CAST(1 as bit) as Post

FROM Accounts
LEFT JOIN Currency ON COALESCE(Accounts.iForeignBankCurrencyID,0) = CurrencyLink
left join _etblGLAccountTypes on iAccountType = idGLAccountType
WHERE
cAccountTypeDescription = 'Cash and Cash Equivalents' 
AND AccountLevel IN (0,2)
AND COALESCE(Accounts.iForeignBankCurrencyID,0) > 0
GROUP BY
Accounts.Master_Sub_Account, Accounts.Description, COALESCE(Accounts.iForeignBankCurrencyID,0), Currency.CurrencyCode, Accounts.AccountLink

ORDER BY Accounts.Master_Sub_Account

OPTION (RECOMPILE)