SELECT
  CP.ClientID,
  CS.ClientStatus,
  CS.TerminationDate AS ClientTerminationDate,
  CP.ProductID,
  CP.ClientProductID,
  CP.Status,
  CP.JoinDate,
  IF(P1.ClientProductID IS NULL, CP.Batch, P1.Batch) AS Batch,
  IF(P1.ClientProductID IS NULL, CONCAT(CP.AccountBookType, '_', CP.AccountBookID), CONCAT(P1.AccountBookType, '_', P1.AccountBookID)) AS AccountBookID,
  IF(P1.ClientProductID IS NULL, COALESCE(CP.BillingCycle, 1), COALESCE(P1.BillingCycle, 1)) AS BillingCycle,
  IF(P1.ClientProductID IS NULL, CP.NextIncreaseDate, P1.NextIncreaseDate) AS NextIncreaseDate,
  IF(P1.ClientProductID IS NULL, COALESCE(CP.DedicatedRetainerInvoice, 0), COALESCE(P1.DedicatedRetainerInvoice, 0)) AS DedicatedRetainerInvoice,
  CP.ParentClientProductID,
  PC.MustHaveParent,
  COALESCE(PC.CoverAllowed, 1) AS CoverAllowed,
  (SELECT COUNT(*) FROM taClientProducts AS CP1 INNER JOIN taProducts AS PR1 USING (ProductID)
   WHERE (CP1.ClientID = CP.ClientID) AND (CP1.ClientProductID <> CP.ClientProductID) AND (CP1.Status = 1) AND (PR1.ProductID = PR.ProductID)
  ) AS MergePossible,
  CP.TerminationDate,
  CP.TerminationReason,
  CP.TerminationNotes,
  fnClientProductRetainer(CP.ClientProductID, NOW()) AS Retainer
FROM
  taClientProducts AS CP
    LEFT JOIN taProducts        AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taClients         AS CS ON (CS.ClientID = CP.ClientID)
    LEFT JOIN taClientProducts  AS P1 ON (P1.ClientProductID = CP.ParentClientProductID)
WHERE
  (CP.ClientProductID = @nClientProductID)
