SELECT
  T.ID,
  COUNT(T.ID) OVER(PARTITION BY T.ID) AS NO_COUNT,
  MAX(I.DTL_NO) OVER(PARTITION BY T.ID) AS NO_MAX,
  D.PHYSICAL_ITEM_NAME AS ITEM_VALUE
FROM
  MS_GENERIC_CSV M
  INNER JOIN MS_GENERIC_CSV_DTL D ON
    M.ID = D.ID
    AND 0 < D.VERSION
  INNER JOIN TR_GENERIC_CSV T ON
    M.DEFINE_NAME = T.DEFINE_NAME
    AND 0 < T.VERSION
  LEFT OUTER JOIN TR_GENERIC_CSV_ITEM I ON
    T.ID = I.ID
    AND D.PHYSICAL_ITEM_NAME = I.ITEM_NAME
    AND 0 < I.VERSION
WHERE
  M.DEFINE_NAME = /*:DefineName*/'aaaa'
  AND M.VERSION > 0
ORDER BY
  T.ID, D.DTL_NO
