CREATE FUNCTION [dbo].[GetTotalFrontLegFolio] (@FolioNumber INT, @PkInvoice INT)
RETURNS MONEY
AS
/*
  nb20181010 Created
  Notes:
*/
BEGIN
  DECLARE @Total MONEY

;WITH TaxCte (Gtn) AS (
  SELECT DISTINCT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH(NOLOCK)
    LEFT JOIN InvoiceTransactionXtab ITX1 WITH(NOLOCK) ON ITX1.FkTransaction = TR1.TransactionNumber
    LEFT JOIN Invoice I1 WITH(NOLOCK) ON ITX1.FkInvoice = I1.PkInvoice
  WHERE TR1.FolioNumber = @FolioNumber AND MovedDeleted=0 AND (@PkInvoice=-1 OR
    (@PkInvoice = 0 AND (I1.PkInvoice IS NULL OR NOT I1.PkInvoice IS NULL AND I1.ReceiptType=0 OR (NOT I1.CancelDate IS NULL))) OR ITX1.FkInvoice=@PkInvoice)
  EXCEPT --GTN on written receipts
  SELECT DISTINCT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH(NOLOCK)
    INNER JOIN InvoiceTransactionXtab ITX1 WITH(NOLOCK) ON ITX1.FkTransaction = TR1.TransactionNumber
    INNER JOIN Invoice I1 WITH(NOLOCK) ON ITX1.FkInvoice = I1.PkInvoice
  WHERE TR1.FolioNumber = @FolioNumber AND I1.ReceiptType>0 AND 
    @PkInvoice=0 AND NOT I1.PkInvoice IS NULL AND I1.CancelDate IS NULL
)

SELECT @Total = SUM(
T.PriceOverwrite * CASE T.Debit WHEN 1 THEN 1 ELSE -1 END
)
 * 100/CASE R.LastCurrencyRate WHEN 0 THEN 100 WHEN -1 THEN 100 ELSE ISNULL(R.LastCurrencyRate, 100) END
--, T.Payment, T.Debit, T.ShowonBillFlag--, A.*
FROM Transactions T  WITH (NOLOCK)
  INNER JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber
    AND A.Balance = 2 AND A.AccountType = 5
  INNER JOIN TaxCte TG WITH (NOLOCK) ON TG.GTN = T.GlobalTransactionNumber
  INNER JOIN Folios F  WITH (NOLOCK) ON F.FolioNumber = T.FolioNumber
  LEFT JOIN Reservations R  WITH (NOLOCK) ON R.ReferenceNumber = F.ReferenceNumber AND R.GuestNumber = F.GuestNumber
WHERE T.FolioNumber=@FolioNumber AND T.MovedDeleted=0 AND T.ShowOnBillFlag= 1 
--Order By T.ShowOnBillFlag
GROUP BY LastCurrencyRate
  
  RETURN @Total
END