SELECT

  -- Invoice detail.
  IH.CMSInvNo,
  IH.DocumentNumber AS InvNumber,
  LEFT(NOW(), 10) AS DocumentDate,
  '' AS CMSUserName,
  0 AS AgentID,
  IH.AccountBookType,
  IH.AccountBookID,
  IH.DocumentNumber,
  0 AS PastelPeriod,
  IH.AccountCode,
  'Combined Invoice' AS Description,
  IH.Updated,
  0 AS BatchNumber,
  0 AS RunNumber,
  IH.AppointmentID,
  IH.InvoiceType,
  NULL AS PaymentDate,
  NULL AS AmountPaid,

  -- Client detail
  IH.ClientID AS CMSClientID,
  IH.TradingName,
  BE.GroupName,
  IH.TaxTypeID,
  ROUND(IH.TaxRate, 4) AS TaxRate,
  IH.PaymentMethod,
  BE.VATNumber,
  BE.ContractNumber AS OrderNumber,
  CB.BranchName,
  CB.Address1,
  CB.Address2,
  CB.Address3,
  CB.Address4,
  CB.Address5,
  BE.Postal1,
  BE.Postal2,
  BE.Postal3,
  BE.PostalCode,
  TRIM(CONCAT(CB.TelephoneAreaCode, ' ', CB.TelephoneNo)) AS Telephone,
  TRIM(CONCAT(CB.FaxAreaCode, ' ', CB.FaxNo)) AS Fax,
  U1.FullName AS Consultant,
  BE.EmailAddress AS Contact,
  AP.ConsultantBranchID,

  -- 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,

  -- Account Book - Sage
  SE.SageEntityName,
  CASE IH.TaxTypeID
    WHEN  6 THEN 3
    WHEN  7 THEN 5
    WHEN 17 THEN 8
    ELSE 1
  END AS SageTaxAccount,

  -- 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,  ''))) AS InvoicePrefix,

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

  -- Amounts
  0.0 AS InvTotIncl,
  0.0 AS InvTotExcl,
  0.0 AS InvTotTax,

  IH.ExternalID,
  '' AS ErrorDescription
FROM
  taInvoiceHeader AS IH
    LEFT JOIN taClients AS BE ON (BE.ClientID = IH.ClientID)
    LEFT JOIN taClientBranches AS CB ON ((CB.ClientID = IH.ClientID) AND (CB.IsHeadOffice = '1') AND (CB.BranchStatus = '1'))
    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'))
    LEFT JOIN tbAppointments AS AP ON (AP.AppointmentID = IH.AppointmentID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = AP.ConsultantID)
WHERE
  (BE.IndividualAdhocInvoices <> 1)
  @pUpdated
  @pClientID
  @pInvoiceNumber
ORDER BY
  IH.CMSInvNo