CREATE FUNCTION [dbo].[GetTotalFrontLegFolio] (@FolioNumber INT, @PkInvoice INT)
RETURNS MONEY
AS
/*
  nb20181010 Created
  Notes:
*/
BEGIN
  DECLARE @Total MONEY, @LastCurrencyRate MONEY
  SELECT @LastCurrencyRate = R.LastCurrencyRate FROM Reservations R (NOLOCK) 
    RIGHT JOIN Folios F (NOLOCK) ON F.ReferenceNumber = R.ReferenceNumber AND F.GuestNumber = R.referenceNumber
  WHERE F.FolioNumber = @FolioNumber

  ;WITH gtCte (Gtn) AS (--Statement
  SELECT DISTINCT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH (NOLOCK)
  WHERE TR1.FolioNumber = @FolioNumber AND MovedDeleted=0 AND @PkInvoice=-1
  UNION--Receipt
  SELECT DISTINCT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH(NOLOCK)
    LEFT JOIN InvoiceTransactionXtab ITX1 WITH(NOLOCK) ON ITX1.FkTransaction = TR1.TransactionNumber
  WHERE TR1.FolioNumber = @FolioNumber AND MovedDeleted=0 AND ITX1.FkInvoice=@PkInvoice
  UNION--Prebill
  SELECT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH(NOLOCK)
    LEFT JOIN InvoiceTransactionXtab ITX1 WITH(NOLOCK) ON ITX1.FkTransaction = TR1.TransactionNumber
  WHERE TR1.FolioNumber = @FolioNumber AND MovedDeleted=0 AND @PkInvoice = 0 AND ITX1.fkInvoice IS NULL 
  UNION--Prebill
  SELECT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH(NOLOCK)
    JOIN InvoiceTransactionXtab ITX1 WITH(NOLOCK) ON ITX1.FkTransaction = TR1.TransactionNumber
    JOIN Invoice I1 WITH(NOLOCK) ON ITX1.FkInvoice = I1.PkInvoice
  WHERE TR1.FolioNumber = @FolioNumber AND MovedDeleted=0 AND @PkInvoice = 0 AND (I1.ReceiptType=0 OR NOT I1.CancelDate IS NULL)
  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 ITX1.FkTransaction IS NULL AND I1.CancelDate IS NULL)
, TaxCte (Gtn, LastCurrencyRate) AS (
SELECT T1.GTN, @LastCurrencyRate FROM gtCte T1
  LEFT JOIN dbo.Transactions TM (NOLOCK) ON TM.GlobalTransactionNumber = T1.GTN AND TM.ShowOnBillFlag = 1
  LEFT JOIN dbo.TransactionTransactionXtab TTX (NOLOCK) ON TTX.FK_TransactionNumber1 = TM.TransactionNumber 
WHERE (TTX.RelationType IS NULL OR TTX.RelationType <> 2) --dont count transfer when in payment role
GROUP BY T1.GTN
)


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