SELECT
  I.DTL_NO,
  D.LOGICAL_ITEM_NAME AS ITEM_LABEL,
  D.PHYSICAL_ITEM_NAME AS ITEM_VALUE,
  I.OUTPUT_FLG,
  I.AGGREGATION_KBN,
  I.GROUP_FLG,
  I.BREAK_FLG,
  I.ORDER_SORT,
  I.ORDER_KBN,
  I.ORDER_KBN AS ORDER_FLG,
  CASE WHEN I.OUTPUT_FLG = '1' THEN D.PHYSICAL_ITEM_NAME ELSE NULL END AS OUTPUT,
  CASE WHEN I.GROUP_FLG = '1' THEN D.PHYSICAL_ITEM_NAME ELSE NULL END AS GROUPING,
  CASE WHEN I.BREAK_FLG = '1' THEN D.PHYSICAL_ITEM_NAME ELSE NULL END AS PAGE_BREAK,
  CASE WHEN I.OUTPUT_FLG = '1' AND I.BREAK_FLG = '1' THEN
    SUM(CASE WHEN I.OUTPUT_FLG = '1' AND I.BREAK_FLG = '1' THEN 1 ELSE 0 END) OVER (ORDER BY I.DTL_NO, D.DTL_NO) - 1
  ELSE NULL END AS BREAK_INDEX
FROM
  TR_GENERIC_CSV T
  INNER JOIN MS_GENERIC_CSV M ON
    T.DEFINE_NAME = M.DEFINE_NAME
    AND 0 < M.VERSION
  INNER JOIN MS_GENERIC_CSV_DTL D ON
    M.DEFINE_NO = D.DEFINE_NO
    AND 0 < D.VERSION
  LEFT OUTER JOIN TR_GENERIC_CSV_ITEM I ON
    T.USER_PATTERN_NO = I.USER_PATTERN_NO
    AND D.PHYSICAL_ITEM_NAME = I.ITEM_NAME
WHERE
  T.ID = /*:Id*/7
  AND T.VERSION > 0
  AND (I.VERSION IS NULL OR I.VERSION > 0)
ORDER BY
  I.DTL_NO, D.DTL_NO
