CREATE FUNCTION [dbo].[GetTaxSpecReceipt] (@FolioNumber INT, @PkInvoice INT) 
RETURNS @TaxSpecReceipt TABLE (Amount MONEY, AmountRaw MONEY, TaxPercent MONEY, FkAccountTaxGroup INT, Tax BIT) AS
BEGIN
--nb20230620 Opt using views and simplified CTE
--nb20161228 Created
--Notes: Calculates the Invoice price and Tax amounts 
DECLARE @lPkFolio INT, @lPkInvoice INT
SET @lPkFolio=  @FolioNumber 
SET @lPkInvoice=  @PkInvoice

IF ISNULL(@lPkInvoice, 0) > 0
BEGIN
  DECLARE @lPkFolioByReceipt INT
  SELECT Top 1 @lPkFolioByReceipt = T.FolioNumber FROM Transactions T (NOLOCK) 
    JOIN InvoiceTransactionXTab ITX (NOLOCK) ON T.TransactionNumber = ITX.FkTransaction 
  WHERE ITX.FkInvoice = @lPkInvoice
  SET @lPkfolio = ISNULL(@lPkFolioByReceipt, @lPkfolio)
END

DECLARE @DefaultAccountGroup INT, @LastCurrencyRate MONEY
SELECT TOP 1 @DefaultAccountGroup = PkAccountVatGroup
FROM AccountVatGroup (NOLOCK) WHERE Profile = 0 ORDER BY OrderNumber
SELECT TOP 1 @DefaultAccountGroup = PkAccountVatGroup
FROM AccountVatGroup (NOLOCK) WHERE Profile = 0 ORDER BY OrderNumber
SELECT @LastCurrencyRate = R.LastCurrencyRate FROM Reservations R (NOLOCK) 
  RIGHT JOIN Folios F (NOLOCK) ON F.ReferenceNumber = R.ReferenceNumber AND F.GuestNumber = R.GuestNumber
WHERE F.FolioNumber = @lPkFolio

;WITH gtCte (Gtn) AS (--Statement
  SELECT DISTINCT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH (NOLOCK)
  WHERE TR1.FolioNumber = @lPkFolio AND MovedDeleted=0 AND @lPkInvoice=-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 = @lPkFolio AND MovedDeleted=0 AND ITX1.FkInvoice=@lPkInvoice
  UNION--Prebill
  SELECT TR1.GlobalTransactionNumber FROM Transactions TR1 WITH(NOLOCK)
    LEFT JOIN InvoiceTransactionXtab ITX1 WITH(NOLOCK) ON ITX1.FkTransaction = TR1.TransactionNumber
  WHERE TR1.FolioNumber = @lPkFolio AND MovedDeleted=0 AND @lPkInvoice = 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 = @lPkFolio AND MovedDeleted=0 AND @lPkInvoice = 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 = @lPkFolio AND I1.ReceiptType>0 AND 
    @lPkInvoice=0 AND NOT ITX1.FkTransaction IS NULL AND I1.CancelDate IS NULL)
, TaxCte (Gtn, ResultAccountNumber, LastCurrencyRate) AS (
SELECT T1.GlobalTransactionNumber, MAX(ATR.AccountNumber), @LastCurrencyRate FROM gtCte 
  JOIN Transactions T1 WITH (NOLOCK) ON gtCte.Gtn = T1.GlobalTransactionNumber
  LEFT JOIN Accounts ATR WITH (NOLOCK) ON ATR.AccountNumber = T1.AccountNumber AND ATR.Balance = 1
  LEFT JOIN dbo.Transactions TM (NOLOCK) ON TM.GlobalTransactionNumber = gtCte.GTN AND TM.FolioNumber = @lPkFolio 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.GlobalTransactionNumber
)

INSERT INTO @TaxSpecReceipt
SELECT CAST(SUM(BT.Amount) * 100/CASE LastCurrencyRate WHEN 0 THEN 100 WHEN -1 THEN 100 ELSE ISNULL(LastCurrencyRate, 100) END AS MONEY) AS Amount
, CAST(SUM(BT.Amount) AS MONEY) AS AmountRaw
, BT.TaxPercent, ISNULL(AR.FkAccountVatGroup, @DefaultAccountGroup) AS FkAccountVatGroup, BT.IsTax
FROM TBalanceTax BT (NOLOCK)
  JOIN Transactions TG (NOLOCK) ON TG.TransactionNumber = BT.fkTransaction
  JOIN TaxCte T WITH (NOLOCK) ON T.GTN = TG.GlobalTransactionNumber
  LEFT JOIN Accounts AR WITH (NOLOCK) ON AR.AccountNumber = T.ResultAccountNumber
WHERE BT.fkFolio=@lPkFolio
GROUP BY  BT.IsTax, BT.TaxPercent, ISNULL(AR.FkAccountVatGroup, @DefaultAccountGroup), LastCurrencyRate

  RETURN 
END