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
  M.DEFINE_NAME = T.DEFINE_NAME
  AND M.VERSION > 0
INNER JOIN
  MS_GENERIC_CSV_DTL D
ON
  D.DEFINE_NO = M.DEFINE_NO
  AND D.VERSION > 0
LEFT OUTER JOIN
  TR_GENERIC_CSV_ITEM I
ON
  I.USER_PATTERN_NO = T.USER_PATTERN_NO
  AND I.ITEM_NAME = D.PHYSICAL_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
