SELECT
  BE.ClientID,
  BE.FinNumber AS AccountCode,
  BE.ClientStatus,
  COALESCE(X.AccountBookType, 'Xero') AS AccountBookType,
  COALESCE(X.AccountBookID, 1) AS AccountBookID,
  COALESCE(BE.GroupName, '') AS TradingName,
  COALESCE(BE.EmailAddress, '') AS EmailAddress,
  COALESCE(BE.VATNumber, '') AS VATNumber,
  COALESCE(BE.CompanyRegNumber, '') AS CompanyRegNumber,
  COALESCE(BE.Postal1, '') AS Postal1,
  COALESCE(BE.Postal2, '') AS Postal2,
  COALESCE(BE.Postal3, '') AS Postal3,
  COALESCE(BE.PostalCode, '') AS PostalCode,
  COALESCE(BE.BankSortCode, '') AS BankSortCode,
  COALESCE(BE.BankAccountNumber, '') AS BankAccountNumber,
  COALESCE(HO.Address1, '') AS Address1,
  COALESCE(HO.Address2, '') AS Address2,
  COALESCE(HO.Address3, '') AS Address3,
  COALESCE(HO.Address4, '') AS Address4,
  COALESCE(HO.Address5, '') AS Address5,
  COALESCE(HO.TelephoneNo, '') AS TelephoneNo,
  COALESCE(HO.TelephoneAreaCode, '') AS TelephoneAreaCode,
  COALESCE(HO.FaxNo, '') AS FaxNo,
  COALESCE(HO.FaxAreaCode, '') AS FaxAreaCode,
  COALESCE(HO.OtherTelNo, '') AS OtherTelNo,
  COALESCE(HO.OtherAreaCode, '') AS OtherAreaCode
FROM
  taClients AS BE
    INNER JOIN taClientBranches AS HO ON ((HO.ClientID = BE.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = 1))
    INNER JOIN (

      SELECT
        BE.ClientID,
        BM.AccountBookType,
        BM.AccountBookID
      FROM
        taClients AS BE
          INNER JOIN taAccountBookMember AS BM ON (BM.ClientID = BE.ClientID)
          INNER JOIN taXeroEntity AS XE ON (XE.XeroEntityID = BM.AccountBookID)
      WHERE
        (BE.ClientStatus = 1) AND
        (BE.FinNumber <> 'NONE') AND
        (BM.AccountBookType = 'Xero') AND
        (XE.Enabled = 1)
        @pBookIDMember
        @pClientID

      UNION

      SELECT
        BE.ClientID,
        CP.AccountBookType,
        CP.AccountBookID
      FROM
        taClients AS BE
          INNER JOIN taClientProducts AS CP ON (CP.ClientID = BE.ClientID)
          INNER JOIN taXeroEntity AS XE ON (XE.XeroEntityID = CP.AccountBookID)
      WHERE
        (BE.ClientStatus = 1) AND
        (BE.FinNumber <> 'NONE') AND
        (CP.Status = 1) AND
        (CP.AccountBookType = 'Xero') AND
        (XE.Enabled = 1)
        @pBookIDProduct
        @pClientID

    ) AS X ON (X.ClientID = BE.ClientID)
ORDER BY AccountBookType, AccountBookID, AccountCode
