CREATE FUNCTION [dbo].[GetTaxSpecReceiptAccount] (@FolioNumber INT, @PkInvoice INT) 
RETURNS @TaxSpecReceipt TABLE (Amount MONEY, AmountRaw MONEY, TaxPercent MONEY, AccountNumber INT, FkAccountTaxGroup INT, Tax BIT) AS
BEGIN
/*
nbl20171004: Created
Notes: Calculates the Invoice price and Tax amounts and group by account 
Changes:
*/
DECLARE @DefaultAccountGroup INT

SELECT TOP 1 @DefaultAccountGroup = PkAccountVatGroup
FROM AccountVatGroup (NOLOCK) WHERE Profile = 0 ORDER BY OrderNumber
;WITH TaxCte (Gtn, ResultAccountNumber) AS (
SELECT T1.GlobalTransactionNumber, MAX(ATR.AccountNumber) FROM Transactions T1 WITH (NOLOCK)
  LEFT JOIN Accounts ATR WITH (NOLOCK) ON ATR.AccountNumber = T1.AccountNumber AND ATR.Balance = 1
WHERE T1.GlobalTransactionNumber in (
  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.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)
GROUP BY T1.GlobalTransactionNumber
)

INSERT INTO @TaxSpecReceipt
SELECT SUM(T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END) * 100/CASE LastCurrencyRate WHEN 0 THEN 100 WHEN -1 THEN 100 ELSE ISNULL(LastCurrencyRate, 100) END AS Price
, SUM(T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END) AS PriceRaw
, T.VatValue
, A.AccountNumber
, ISNULL(RA.FkAccountVatGroup, @DefaultAccountGroup), 0
FROM Transactions T  WITH (NOLOCK)
  INNER JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber
    AND (A.Balance = 1 OR (A.Balance = 2 AND T.Payment > 0 AND T.GlobalTransactionNumber IN (
      SELECT TR.GlobalTransactionNumber FROM Transactions TR  WITH (NOLOCK)
        INNER JOIN Ware W  WITH (NOLOCK) ON W.WareNumber = TR.WareNumber AND W.Type = 4
      WHERE TR.FolioNumber = T.FolioNumber)
    ))
  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
  LEFT JOIN Accounts RA WITH (NOLOCK) ON RA.AccountNumber = TG.ResultAccountNumber

WHERE T.FolioNumber=@FolioNumber AND T.MovedDeleted=0 
GROUP BY T.VatValue, ISNULL(RA.FkAccountVatGroup, @DefaultAccountGroup), LastCurrencyRate, A.AccountNumber
UNION--Tax (Non Provision)
SELECT SUM(T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END)* 100/CASE LastCurrencyRate WHEN 0 THEN 100 WHEN -1 THEN 100 ELSE ISNULL(LastCurrencyRate, 100) END AS Price
, SUM(T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END) AS PriceRaw, T.VatValue
, A.AccountNumber
, ISNULL(RA.FkAccountVatGroup, @DefaultAccountGroup), 1
FROM Transactions T  WITH (NOLOCK)
  INNER JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber
    AND A.Balance = 2 AND A.VAT > 1
  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
  INNER JOIN Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
  INNER JOIN Waregroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WaregroupNumber and (WG.Payment <> 5 or T.ShowOnBillFlag = 0) -- excl show on bill provision (incl package)
  INNER JOIN TaxCte TG WITH (NOLOCK) ON TG.GTN = T.GlobalTransactionNumber
  INNER JOIN Accounts RA WITH (NOLOCK) ON RA.AccountNumber = TG.ResultAccountNumber
WHERE T.FolioNumber=@FolioNumber AND T.MovedDeleted=0 
GROUP BY T.VatValue, ISNULL(RA.FkAccountVatGroup, @DefaultAccountGroup), LastCurrencyRate, A.AccountNumber
UNION--Tax (Provision only)
SELECT SUM(T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END)* 100/CASE LastCurrencyRate WHEN 0 THEN 100 WHEN -1 THEN 100 ELSE ISNULL(LastCurrencyRate, 100) END AS Price
, SUM(T.PriceOverwrite * CASE T.Debit WHEN 1 THEN -1 ELSE 1 END) AS PriceRaw, T.VatValue
, A.AccountNumber
, ISNULL(RA.FkAccountVatGroup, @DefaultAccountGroup), 1
FROM Transactions T  WITH (NOLOCK)
  INNER JOIN Accounts A  WITH (NOLOCK) ON A.AccountNumber = T.AccountNumber
  AND A.Balance = 2 AND A.VAT > 1
  INNER JOIN Ware W WITH (NOLOCK) ON W.WareNumber = T.WareNumber
  INNER JOIN Waregroups WG WITH (NOLOCK) ON WG.WaregroupNumber = W.WaregroupNumber and WG.Payment = 5 --provision
  INNER JOIN transactions TR WITH (NOLOCK) ON TR.GlobalTransactionNumber = T.GlobalTransactionNumber AND
    T.WareNumber = TR.WareNumber AND TR.TransactionNumber <> T.TransactionNumber AND
    TR.ShowOnBillFlag = 0 AND T.ShowOnBillFlag = 0 AND T.MovedDeleted=0 AND TR.MovedDeleted=0
  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
  INNER JOIN TaxCte TG WITH (NOLOCK) ON TG.GTN = T.GlobalTransactionNumber
    LEFT JOIN Accounts RA WITH (NOLOCK) ON RA.AccountNumber = TG.ResultAccountNumber
  LEFT JOIN Transactions TR1 with (NOLOCK) ON TR1.GlobalTransactionNumber = T.GlobalTransactionNumber AND TR1.ShowOnBillFlag = 1
WHERE T.FolioNumber=@FolioNumber AND T.MovedDeleted=0 AND T.ShowOnBillFlag = 1 
GROUP BY T.VatValue, ISNULL(RA.FkAccountVatGroup, @DefaultAccountGroup), LastCurrencyRate, A.AccountNumber

  RETURN 
END