SELECT
  XE.BeneficiaryAccountNumber,
  'LabourNet Central' AS BeneficiaryName,
  CONCAT(BE.FinNumber, ' ', BE.GroupName) AS BeneficiaryStatementDescription,
  CONCAT('', BE.BankAccountNumber) AS FromAccount,
  '' AS BlankField,
  BE.BankBranchNumber AS FromBranch,
  BE.GroupName AS FromStatementDescription,
  'LABOURNET' AS BeneficiaryName1,
  XE.BeneficiaryReferenceName AS BeneficiaryName2,
  ROUND(IA.InvoiceAmountIncl,2) As Amount
FROM
  taInvoiceAudit AS IA
    LEFT JOIN taClientBranches AS CB ON ((CB.ClientID = IA.ClientID) AND (CB.IsHeadOffice = '1') AND (CB.BranchStatus = '1'))
    LEFT JOIN taClients AS BE ON (BE.ClientID = IA.ClientID)
    LEFT JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = IA.CMSInvNo)
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Xero'))
WHERE
  (IA.InvoiceType = 'Retainer') AND
  (IA.ErrorDescription = 'Success') AND
  (IH.Batch = CONCAT('Batch ', @aBatchNumber)) AND
  (IA.AppointmentID = (YEAR(@aActionDate) * 100 + MONTH(@aActionDate)))

UNION

SELECT
  XE.BeneficiaryAccountNumber,
  'LabourNet Central' AS BeneficiaryName,
  CONCAT(BE.FinNumber, ' ', BE.GroupName) AS BeneficiaryStatementDescription,
  CONCAT('', BE.BankAccountNumber) AS FromAccount,
  '' AS BlankField,
  BE.BankBranchNumber AS FromBranch,
  BE.GroupName AS FromStatementDescription,
  'LABOURNET' AS BeneficiaryName1,
  XE.BeneficiaryReferenceName AS BeneficiaryName2,
  ROUND(IA.InvoiceAmountIncl,2) As Amount
FROM
  cms_archive.taInvoiceAudit AS IA
    LEFT JOIN taClientBranches AS CB ON ((CB.ClientID = IA.ClientID) AND (CB.IsHeadOffice = '1') AND (CB.BranchStatus = '1'))
    LEFT JOIN taClients AS BE ON (BE.ClientID = IA.ClientID)
    LEFT JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = IA.CMSInvNo)
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Xero'))
WHERE
  (IA.InvoiceType = 'Retainer') AND
  (IA.ErrorDescription = 'Success') AND
  (IH.Batch = CONCAT('Batch ', @aBatchNumber)) AND
  (IA.AppointmentID = (YEAR(@aActionDate) * 100 + MONTH(@aActionDate)))
