SELECT M3.IDHINMOKU
      ,BT010.TXHINBAN
      ,BT010.TXHINMEI
      ,BT010.KBHINSYU
      ,BT010.KBUNIT
      ,MAX(M3.HIZUKE) as HIZUKE
      ,MAX(M3.KBN) as KBN
      ,ifnull(M3.QTGENZAIKO,0) + SUM(M3.SURYO) as RUIKEI
FROM (
SELECT
 M1.*
 ,(SELECT SUM(QTGENZAIKO) FROM FT010 WHERE FT010.IDHINMOKU = M1.IDHINMOKU) QTGENZAIKO

FROM (
-- 製造入庫予定
SELECT
   JT010.QTSEIZO AS SURYO
  ,JT010.IDHINMOKU
  ,JT030.DTTHRU AS HIZUKE
  ,'060' AS KBN -- 完成入庫
FROM JT010
LEFT OUTER JOIN (SELECT
                   JT030.NRSEIZO
                  ,JT030.SQKOUTEI
                  ,SUB.SQLIST
                  ,CASE WHEN JT030.DTTHRU IS NULL THEN TLN_SYSDATE() ELSE JT030.DTTHRU END AS DTTHRU
                 FROM JT030
                 INNER JOIN  (SELECT NRSEIZO
                                    ,MAX(SQLIST) AS SQLIST
                                FROM JT030
                               GROUP BY NRSEIZO) SUB ON JT030.NRSEIZO = SUB.NRSEIZO  AND JT030.SQLIST = SUB.SQLIST
                 ) JT030 ON JT030.NRSEIZO = JT010.NRSEIZO
WHERE JT010.STSEIZO < 40
 AND JT030.DTTHRU <= /**%HIZUKE%**/'9999/12/31'
UNION ALL
-- 仕入入庫予定
SELECT
   HT011.QTHATTYU * BT010.VLKANZAN AS SURYO
  ,HT011.IDHINMOKU
  ,HT011.DTNOUKI AS HIZUKE
  ,'030' AS KBN -- 仕入入庫
FROM HT011
LEFT JOIN HT020 ON HT020.NRNYUKA = HT011.NRNYUKA
LEFT JOIN BT010 ON BT010.IDHINMOKU = HT011.IDHINMOKU
WHERE ( HT011.FGNYUKA IS NULL OR HT020.STNYUKA = '10')
AND HT011.DTNOUKI <= /**%HIZUKE%**/'9999/12/31'
UNION ALL
-- 売上出庫
SELECT
   JT010.QTJYUTYU * -1 AS SURYO
  ,JT010.IDHINMOKU
  ,JT010.DTNOUKI AS HIZUKE
  ,'130' AS KBN -- 売上出庫
FROM JT010
WHERE JT010.STSEIZO < 50
  AND JT010.QTJYUTYU > 0
  AND JT010.DTNOUKI <= /**%HIZUKE%**/'9999/12/31'
UNION ALL
-- 材料出庫
SELECT
   JT040.QTTOUNYU * -1 AS SURYO
  ,JT040.IDHINMOKU
  ,CASE WHEN JT030.DTFROM IS NULL THEN TLN_SYSDATE() ELSE JT030.DTFROM END AS HIZUKE
  ,'160' AS KBN -- 材料出庫
FROM JT010
INNER JOIN JT040 ON JT040.NRSEIZO = JT010.NRSEIZO
LEFT  JOIN JT030 ON JT030.NRSEIZO = JT040.NRSEIZO AND JT030.SQKOUTEI = JT040.SQKOUTEI
WHERE JT010.STSEIZO < 30
  AND (CASE WHEN JT030.DTFROM IS NULL THEN TLN_SYSDATE() ELSE JT030.DTFROM END) <= /**%HIZUKE%**/'9999/12/31'
  ) M1
ORDER BY M1.IDHINMOKU,M1.HIZUKE) M3
LEFT JOIN BT010 ON M3.IDHINMOKU = BT010.IDHINMOKU
GROUP BY M3.IDHINMOKU
      ,BT010.TXHINBAN
      ,BT010.TXHINMEI
      ,BT010.KBHINSYU
      ,BT010.KBUNIT