SELECT

  -- Invoice detail.
  IH.CMSInvNo,
  IH.DocumentDate,
  IH.AccountBookType,
  IH.AccountBookID,
  IH.DocumentNumber,
  IH.AccountCode,
  IH.Updated,
  IH.Batch,
  IH.AppointmentID,
  IH.InvoiceType,
  IH.DueDate,
  IH.PaymentDate,
  IH.AmountPaid,
  IH.ExternalID,

  -- Client detail
  IH.ClientID AS CMSClientID,
  IH.TradingName,
  IH.Description,
  IH.TaxTypeID,
  ROUND(IH.TaxRate, 4) AS TaxRate,
  IH.PaymentMethod,
  IH.VATNumber,
  IH.OrderNumber,
  IH.Address1,
  IH.Address2,
  IH.Address3,
  IH.Address4,
  IH.Address5,
  IH.Consultant,
  IH.Contact,
  IH.Postal1,
  IH.Postal2,
  IH.Postal3,
  IH.PostalCode,
  IH.Telephone,
  IH.Fax,

  -- Account Book - Pastel
  PB.DatabaseName AS PastelDatabaseName,
  PB.DebtorsAccountLink,
  PB.VATAccountLink,
  PB.BankAccountLink,

  -- Account Book - Xero
  XE.XeroEntityName,
  CASE IH.TaxTypeID
    WHEN  6 THEN 'ZERORATED'
    WHEN  7 THEN 'EXEMPTOUTPUT'
    WHEN 17 THEN 'BADDEBT'
    ELSE 'OUTPUT3'
  END AS XeroTaxAccount,
  XE.ShortCode AS XeroShortCode,

  -- Account Book - Sage
  SE.SageEntityName,

  -- Account Book - Combined.
  COALESCE(IF(IH.AccountBookType = 'Pastel', PB.DatabaseName,  IF(IH.AccountBookType = 'Xero', XE.XeroEntityName, IF(IH.AccountBookType = 'Sage', SE.SageEntityName, NULL))), CONCAT(IH.AccountBookType, '_', IH.AccountBookID)) AS AccountBookName,
  IF(IH.AccountBookType = 'Pastel', PB.InvoicePrefix, IF(IH.AccountBookType = 'Xero', XE.InvoicePrefix,  IF(IH.AccountBookType = 'Sage', SE.InvoicePrefix,  NULL))) AS InvoicePrefix,

  -- Ad Hoc Only
  BE.ImmediateAdhocInvoices,
  BE.IndividualAdhocInvoices,

  -- Amounts
  IH.InvoiceAmountIncl AS InvTotIncl,
  IH.InvoiceAmountExcl AS InvTotExcl,
  IH.InvoiceAmountExcl * IH.TaxRate AS InvTotTax,
  (SELECT SUM(CN.CreditNoteAmount) FROM taCreditNote AS CN WHERE (CN.InvoiceNumber = IH.DocumentNumber)) AS CreditNoteAmount
FROM
  taInvoiceHeader AS IH
    LEFT JOIN taClients AS BE ON (BE.ClientID = IH.ClientID)
    LEFT JOIN taPastelBook AS PB ON ((PB.PastelBookID = IH.AccountBookID) AND (IH.AccountBookType = 'Pastel'))
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Sage'))
WHERE
  (IH.InvoiceType IN ('A','I','R')) AND
  (IH.AccountCode <> 'NONE')
  @pUpdated
  @pAccountBookID
  @pClientID
  @pCMSInvNo
ORDER BY
  IH.CMSInvNo
@pLimit