SELECT

  --
  -- Appointment details.
  --
  AP.AppointmentID,
  AP.ClientBranchProductID,
  AP.ClientBranchID,
  BP.ClientProductID,
  AP.ClientID,
  AP.ProductID,
  CP.ParentClientProductID,
  PR.UmbrellaCover,
  AP.AttendanceTypeID,
  AP.SubAttendanceType,
  AP.AttendanceDate,
  SUBSTR(AP.AttendanceTime, 12, 8) AS AttendanceTime,
  AP.Duration,
  AP.AppointmentStage,
  AP.DontDeductFromCover,
  AP.TrainingRequired,
  AP.ReferralRequested,
  AP.ProjectID,
  AP.CaseID,
  AP.CreateDateTime,
  AP.LastChangeDate,
  AP.EmployeeParticulars,
  AP.ClientOrderNumber,

  --
  -- IR details.
  --
  AP.IRChargeType,
  AP.IRFinding,
  AP.IRRecommendationID,
  AT.IsClientMatter,

  --
  -- Consultant details.
  --
  AP.BookedByID,
  AP.ConsultantID,
  AP.ConsultantBranchID,
  BR.BranchRegion,
  BG.Rate AS RatePerUnit,
  U1.FullName AS Consultant,

  --
  -- Costing details.
  --
  AP.TimeSpentRate,
  AP.TimeSpent,
  AP.TimeSpentTotal,

  --
  -- Travel time billing details.
  --
  AP.TravelTimeBillable,
  AP.TimeTravelledRate,
  AP.TimeTravelled,
  AP.TimeTravelledTotal,

  --
  -- Travel distance billing details.
  --
  AP.DistanceTravelled,
  AP.DistanceTravelledRate,
  AP.DistanceTravelledTotal,
  AP.TravelMode,
  AP.OtherDestination,
  AP.DestinationName,
  AP.TravelParkingCost,
  AP.TravelTollgatesCost,
  AP.ThresholdDistanceApplied,

  --
  -- Disbursements billing details.
  --
  AP.AmountTelephone,
  AP.AmountPostage,
  AP.AmountCourierFees,
  AP.AmountPhotoCopies,
  AP.AmountCorrespondence,
  AP.AmountOther,
  AP.AmountDetails,
  AP.NoOfDelegates,

  --
  -- Travel billing details.
  --
  AP.AirTravelRequired,
  AP.AirTravelPaidBy,
  AP.AirTravelCost,
  AP.CarRentalRequired,
  AP.CarRentalPaidBy,
  AP.CarRentalCost,
  AP.AccomodationRequired,
  AP.AccomodationPaidBy,
  AP.AccomodationCost,

  --
  -- Appointment costing details.
  --
  AP.ServiceValue,
  AP.InvoiceAmount,
  AP.CoverUsed,
  AP.FreeTimeAmount,
  AP.FreeHoursUsed,
  AP.FreeConsultationUsed,
  AP.InvoiceNumber,
  AP.InvoiceDate,
  AP.ClientCover,
  AP.CoverType,
  AP.BillingDate,
  AP.BillingYearMonth,
  BR.TrackingCategory AS BranchTrackingCategory,
  PC.TrackingCategory AS ProductTrackingCategory,

  --
  -- Attendance type details.
  --
  AT.AppointmentType,
  AT.UseTypeRate,
  AT.IsActive,
  AT.InCoverRate,
  AT.OutOfCoverRate,
  AT.AdhocRate,
  IF(AP.ProductID = 14, 0, AT.BillingRule) AS BillingRule,
  AT.IsService,
  AT.UnitOfMeasureID,
  (
    CASE AT.UnitOfMeasureID
      WHEN 1 THEN 'per Day'
      WHEN 2 THEN 'per Hour'
      WHEN 3 THEN 'per Session'
      WHEN 4 THEN 'per Delegate'
      WHEN 5 THEN 'per Half Day'
      ELSE AT.UnitOfMeasureID
    END
  ) AS PerUnitDesc,
  (
    CASE AT.UnitOfMeasureID
      WHEN 1 THEN 'No of Days'
      WHEN 2 THEN 'No of Hours'
      WHEN 3 THEN 'No of Sessions'
      WHEN 4 THEN 'No of Delegates'
      WHEN 5 THEN 'No of Half Days'
      ELSE AT.UnitOfMeasureID
    END
  ) AS NrOfUnitsDesc,
  AT.ProductCategory AS AttendanceProductCategory,
  ST.OnLine AS SubTypeOnLine,
  PC.ProductCategory,

  --
  -- Client details.
  --
  AP.ClientID,
  CS.OrderCompulsory,
  CS.AdhocAllowed,
  CS.TaxTypeID,
  COALESCE(CS.InternalClient, 0) AS ClientInternalClient,
  AP.InternalClient,
  AP.AccountBookType,
  AP.AccountBookID,
  COALESCE(IF(AP.AccountBookType = 'Xero', XE.XeroEntityName, IF(AP.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(AP.AccountBookType, '_', AP.AccountBookID)) AS AccountBookName,

  --
  -- Client branch details.
  --
  CB.BranchName AS ClientBranchName,
  CB.RoundTripDistance AS RoundTripDistance,
  CB.FreeKiloDistance AS FreeKiloDistance,

  --
  -- Client contact details.
  --
  '' AS ContactTitle,
  '' AS ContactFirstName,
  '' AS ContactSurname,

  --
  -- Calendar details.
  --
  AP.OnLine,
  AP.CalendarID,
  AP.OutlookCalendar,
  AP.OutlookEventID,
  AP.OutlookEventWebLink,

  --
  -- Appointment quality details.
  --
  AP.ClientSatisfactionRating,
  AP.ClientSatisfactionComment,
  AP.ClientHasComplained,
  AP.IsCancelImminent,

  --
  -- Appointment cancellation details.
  --
  AP.CancelledDate,
  AP.CancelledOnDate,
  AP.CancelledByFullname,
  AP.CancelledByReason
FROM
  tbAppointments AS AP
    LEFT JOIN tbAppointmentTypes AS AT ON (AT.AttendanceTypeID = AP.AttendanceTypeID)
    LEFT JOIN taAppointmentSubType AS ST ON (ST.SubTypeID = AP.SubAttendanceType)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = AT.ProductCategory)
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = AP.ClientBranchID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = AP.ClientID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = AP.ConsultantBranchID)
    LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = AP.AccountBookID) AND (AP.AccountBookType = 'Sage'))
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = AP.AccountBookID) AND (AP.AccountBookType = 'Xero'))
    LEFT JOIN taUsers AS U1 ON (U1.UserID = AP.ConsultantID)
    LEFT JOIN taUserBillingGroup AS BG ON (BG.UserBillingGroupID = U1.UserBillingGroupID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientProducts AS CPP ON (CPP.ClientProductID = CP.ParentClientProductID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CPP.ProductID)
WHERE
  (AP.AppointmentID = @nAppointmentID)
