SELECT
  'ServiceFee' AS BillingType,
  AP.AppointmentID AS BillingID,
  IF(LEFT(AP.InvoiceNumber, 4) = 'CMS:', SUBSTRING(AP.InvoiceNumber, 5), AP.InvoiceNumber) AS InvoiceNumber,
  AP.ConsultantID,
  U1.FullName AS Consultant,
  U1.EmployeeCode AS EmployeeCode,
  AT.AppointmentType AS BillingDesc,
  AP.ConsultantBranchID AS BranchID,
  BR.BranchName AS BranchName,
  BR.BranchRegion AS BranchRegion,
  AP.BillingDate AS BillingDate,
  CS.ClientID,
  LEFT(REPLACE(REPLACE(CS.GroupName, ',', ''), '''', ''), 40) AS Client,
  SM.ClientGroup,
  CB.ClientBranchID,
  CB.BranchName AS ClientBranchName,
  B3.BranchRegion AS OnBehalfOfBranch,
  AT.ProductCategory AS ProductCategory,
  AP.CoverUsed AS CoverAmount,
  AP.FreeTimeAmount,
  (AP.CoverUsed + AP.FreeTimeAmount) AS ServiceValue,
  AP.InvoiceAmount,
  AP.ServiceValue AS TotalAmount,
  ROUND(AP.TimeTravelled * AP.TimeTravelledRate, 2) AS TravelTime,
  ROUND(AP.DistanceTravelled * AP.DistanceTravelledRate, 2) AS TravelDistance,
  ROUND(AP.TimeTravelled * AP.TimeTravelledRate, 2) + ROUND(AP.DistanceTravelled * AP.DistanceTravelledRate, 2) AS TravelValue,
  ((ROUND(AP.TimeTravelled * AP.TimeTravelledRate, 2) + ROUND(AP.DistanceTravelled * AP.DistanceTravelledRate, 2)) / AP.ServiceValue * 100.0) AS TravelPercentage
FROM
  tbAppointments AS AP
    LEFT JOIN tbAppointmentTypes     AS AT ON (AT.AttendanceTypeID = AP.AttendanceTypeID)
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    LEFT JOIN taClientProducts       AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches       AS CB ON (CB.ClientBranchID = AP.ClientBranchID)
    LEFT JOIN taClients              AS CS ON (CS.ClientID = AP.ClientID)
    LEFT JOIN taClientSummary        AS SM ON (SM.ClientID = AP.ClientID)
    LEFT JOIN taUsers                AS U1 ON (U1.UserID   = AP.ConsultantID)
    LEFT JOIN taBranches             AS BR ON (BR.BranchID = AP.ConsultantBranchID)
    LEFT JOIN taBranches             AS B3 ON (B3.BranchID = CB.LabourNetBranchID),
  taUserBranchMember AS BM
    LEFT JOIN taBranches AS B2 ON (B2.BranchID = BM.BranchID)
WHERE
  (AP.InvoiceNumber <> 'no invoice') AND
  (BM.UserID = AP.ConsultantID) AND
  (BM.FromDate = (SELECT MAX(N.FromDate) FROM taUserBranchMember AS N WHERE (N.UserID = AP.ConsultantID) AND (N.FromDate <= AP.BillingDate))) AND
  (AP.AppointmentStage = 'Authorised') AND
  (AP.ServiceValue IS NOT NULL) AND (AP.ServiceValue > 0.0) AND
  (CS.ClientID IS NOT NULL) AND (CS.ClientID > 0) AND
  (CB.ClientBranchID IS NOT NULL) AND (CB.ClientBranchID > 0) AND
  (U1.UserID IS NOT NULL) AND (U1.UserID > 0) AND
  (AP.BillingDate BETWEEN @aFromDate AND @aToDate)

  @pBranchID
  @pProductID
  @pConsultantID
  @pClientID

  @pBranchRegion
  @pProductCategory
  @pConsultant
  @pClient

  @pCases
  @pProjectID
  @pCaseID
