--**************************************************************************
--*　故紙受入データの指定された月・グレード取得
--*　(受入日付・グレード)
--**************************************************************************
[lselectRecords]
-- --故紙受入データ
SELECT  TRW.CD_GRADE ,MG.NAME_GREAD
FROM T_RECEIVE_WP TRW ,M_GREAD MG
WHERE (('&2' = '') OR (TRW.DT_RECEIVESLIP >= TO_DATE('&2', 'yyyy/mm/dd')))
AND (('&3' = '') OR (TRW.DT_RECEIVESLIP <= TO_DATE('&3', 'yyyy/mm/dd')))
AND (('&4' = '') OR (TRW.CD_SUPPLIER >= ('&4' ::text) ::numeric))
AND (('&5' = '') OR (TRW.CD_SUPPLIER <= ('&5' ::text) ::numeric))
AND (('&6' = '') OR (TRW.CD_SHIPMENT >= ('&6' ::text) ::numeric))
AND (('&7' = '') OR (TRW.CD_SHIPMENT <= ('&7' ::text) ::numeric))
AND (('&8' = '') OR (TRW.CD_WP >= ('&8' ::text) ::numeric))
AND (('&9' = '') OR (TRW.CD_WP <= ('&9' ::text) ::numeric))
AND (('&10' = '') OR (TRW.CD_GRADE = ('&10' ::text) ::numeric))
AND TRW.CD_GRADE = MG.CD_GRADE
AND TRW.FLG_DELETE = 0
GROUP BY TRW.CD_GRADE , MG.NAME_GREAD

--故紙払出データ
UNION
SELECT   TSW.CD_GRADE ,MG.NAME_GREAD
FROM T_SHIP_WP TSW ,M_GREAD MG
WHERE (('&2' = '') OR (TSW.DT_SHIPSLIP >= TO_DATE('&2', 'yyyy/mm/dd')))
AND (('&3' = '') OR (TSW.DT_SHIPSLIP <= TO_DATE('&3', 'yyyy/mm/dd')))
AND (('&4' = '')	OR (TSW.CD_SUPPLIER >= ('&4' ::text) ::numeric))
AND (('&5' = '') OR (TSW.CD_SUPPLIER <= ('&5' ::text) ::numeric))
AND (('&6' = '') OR (TSW.CD_SHIPMENT >= ('&6' ::text) ::numeric))
AND (('&7' = '') OR (TSW.CD_SHIPMENT <= ('&7' ::text) ::numeric))
AND (('&8' = '') OR (TSW.CD_WP >= ('&8' ::text) ::numeric))
AND (('&9' = '') OR (TSW.CD_WP <= ('&9' ::text) ::numeric))
AND (('&10' = '') OR (TSW.CD_GRADE = ('&10' ::text) ::numeric))
AND TSW.CD_GRADE = MG.CD_GRADE
AND TSW.FLG_DELETE = 0
GROUP BY TSW.CD_GRADE , MG.NAME_GREAD

-- 棚卸データ
UNION
SELECT TA.CD_GREAD CD_GRADE ,MG.NAME_GREAD
FROM T_ADJUSTMENT TA ,M_GREAD MG
WHERE (('&2' = '') OR (TA.DT_ADJUSTMENT >= TO_DATE('&2', 'yyyy/mm/dd')))
AND (('&3' = '') OR (TA.DT_ADJUSTMENT <= TO_DATE('&3', 'yyyy/mm/dd')))
AND (('&4' = '') OR (TA.CD_SUPPLIER >= ('&4' ::text) ::numeric))
AND (('&5' = '') OR (TA.CD_SUPPLIER <= ('&5' ::text) ::numeric))
AND (('&6' = '') OR (TA.CD_SHIPMENT >= ('&6' ::text) ::numeric))
AND (('&7' = '') OR (TA.CD_SHIPMENT <= ('&7' ::text) ::numeric))
AND (('&8' = '') OR (TA.CD_WP >= ('&8' ::text) ::numeric))
AND (('&9' = '') OR (TA.CD_WP <= ('&9' ::text) ::numeric))
AND (('&10' = '') OR (TA.CD_GREAD = ('&10' ::text) ::numeric))
AND TA.CD_GREAD = MG.CD_GRADE
AND TA.FLG_DELETE = 0
AND TA.DIV_WP_PRODUCT = 0
GROUP BY TA.CD_GREAD , MG.NAME_GREAD
 
-- 原料在庫
UNION
SELECT TS.CD_GRADE , MG.NAME_GREAD
FROM T_STOCK_WP TS ,M_GREAD MG
WHERE (('&2' = '') OR (TS.DTS_YM >= TO_CHAR(TO_DATE('&2', 'YYYY/MM/DD') + - 1 * '1 month'::interval, 'YYYYMM')))
AND (('&3' = '') OR (TS.DTS_YM <= TO_CHAR(TO_DATE('&3', 'YYYY/MM/DD') + - 1 * '1 month'::interval, 'YYYYMM')))
AND (('&4' = '') OR (TS.CD_SUPPLIER >= ('&4' ::text) ::numeric))
AND (('&5' = '') OR (TS.CD_SUPPLIER <= ('&5' ::text) ::numeric))
AND (('&6' = '') OR (TS.CD_SHIPMENT >= ('&6' ::text) ::numeric))
AND (('&7' = '') OR (TS.CD_SHIPMENT <= ('&7' ::text) ::numeric))
AND (('&8' = '') OR (TS.CD_WP >= ('&8' ::text) ::numeric))
AND (('&9' = '') OR (TS.CD_WP <= ('&9' ::text) ::numeric))
AND (('&10' = '') OR (TS.CD_GRADE = ('&10' ::text) ::numeric))
AND TS.CD_GRADE = MG.CD_GRADE
AND TS.FLG_DELETE = 0
AND TS.WEIGHT_STOCK <> 0
GROUP BY TS.CD_GRADE , MG.NAME_GREAD
ORDER BY  CD_GRADE


--*************************************************************************************
--*　故紙受入データ、故紙払い出しデータ、故紙在庫データより指定された月分の明細を取得
--*　(数量・金額・前月在庫量)
--*************************************************************************************
[lselectRecords2]
SELECT
	 CD_WP --銘柄コード
	,CD_GRADE --グレード
	,NAME_GRADE --銘柄名
	,CD_SUPPLIER --仕入先コード
	,NAME_SUPPLIER --仕入先名
	,CD_SHIPMENT --荷送り先コード
	,NAME_SHIPMENT --荷送り先名
	,SUM(BFOZAIKAZU) BFOZAIKAZU --前月在庫数量
	,SUM(BFOZAIGAKU) BFOZAIGAKU --前月在庫金額
	,SUM(UKEIREKAZU) UKEIREKAZU --受入数量
	,SUM(UKEIREGAKU) UKEIREGAKU --受入金額
	,SUM(HARAIKAZU) HARAIKAZU --払出数量
	,SUM(HARAIGAKU) HARAIGAKU --払出金額
	,SUM(TANAKAZU) TANAKAZU --棚卸数量
	,SUM(TANAGAKU) TANAGAKU --棚卸金額
FROM(
	--開始日付前日の在庫量取得
	SELECT
		 bfozaiko.CD_WP --銘柄コード
		,bfozaiko.CD_GRADE --グレード
		,MW.NAME_GRADE --銘柄名
		,bfozaiko.CD_SUPPLIER --仕入先コード
		,MS.NAME_SUPPLIER --仕入先名
		,bfozaiko.CD_SHIPMENT --荷送り先コード
		,MSIP.NAME_SHIPMENT --荷送り先名
		,coalesce(bfozaiko.WEIGHT_STOCK, 0) + coalesce(bfozaiko.GTRWKAZU, 0) - coalesce(bfozaiko.GTSWKAZU, 0) + coalesce(bfozaiko.GTTAKAZU, 0) BFOZAIKAZU --前月在庫数量
		,coalesce(bfozaiko.MONEY_STOCK, 0) + coalesce(bfozaiko.GTRWGAKU, 0) - coalesce(bfozaiko.GTSWGAKU, 0) + coalesce(bfozaiko.GTTAGAKU, 0) BFOZAIGAKU --前月在庫金額
		,0 UKEIREKAZU --受入数量
		,0 UKEIREGAKU --受入金額
		,0 HARAIKAZU --払出数量
		,0 HARAIGAKU --払出金額
		,0 TANAKAZU --棚卸数量
		,0 TANAGAKU --棚卸金額
	FROM (
		SELECT CD_WP,CD_GRADE ,NAME_GRADE
		FROM M_WP
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
		AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
		AND FLG_DELETE = 0
	) mw, ( 
		SELECT CD_GRADE
			,CD_SUPPLIER
			,CD_SHIPMENT
			,CD_WP
			,WEIGHT_STOCK
			,MONEY_STOCK
			,0 GTRWKAZU
			,0 GTRWGAKU
			,0 GTSWKAZU
			,0 GTSWGAKU
			,0 GTTAKAZU
			,0 GTTAGAKU
		FROM T_STOCK_WP TSTOC
		WHERE TSTOC.DTS_YM = '&10'
		AND (('&1' = '') OR (TSTOC.CD_GRADE = ('&1' ::text) ::numeric))
		AND TSTOC.FLG_DELETE = 0

		union
		SELECT CD_GRADE
			,CD_SUPPLIER
			,CD_SHIPMENT
			,CD_WP
			,0
			,0
			,SUM(ROUND(WEIGHT_WP)) GTRWKAZU
			,SUM(ROUND(MONEY_WP)) GTRWGAKU
			,0
			,0
			,0
			,0
		FROM T_RECEIVE_WP TRW
		WHERE DT_RECEIVESLIP > TO_DATE('&11', 'YYYY/MM/DD')
		AND DT_RECEIVESLIP < TO_DATE('&2', 'YYYY/MM/DD')
		AND (('&1' = '') OR (CD_GRADE = ('&1' ::text) ::numeric))
		AND FLG_DELETE = 0
		GROUP BY CD_GRADE, CD_SUPPLIER , CD_SHIPMENT , CD_WP

		union
		SELECT CD_GRADE
			,CD_SUPPLIER
			,CD_SHIPMENT
			,CD_WP
			,0
			,0
			,0
			,0
			,SUM(ROUND(WEIGHT_WP)) GTSWKAZU
			,SUM(ROUND(MONEY_WP)) GTSWGAKU
			,0
			,0
		FROM T_SHIP_WP
		WHERE DT_SHIPSLIP > TO_DATE('&11', 'YYYY/MM/DD')
		AND DT_SHIPSLIP < TO_DATE('&2', 'YYYY/MM/DD')
		AND (('&1' = '') OR (CD_GRADE = ('&1' ::text) ::numeric))
		AND FLG_DELETE = 0
		GROUP BY CD_GRADE , CD_SUPPLIER , CD_SHIPMENT , CD_WP

		union
		SELECT CD_GREAD
			,CD_SUPPLIER
			,CD_SHIPMENT
			,CD_WP
			,0
			,0
			,0
			,0
			,0
			,0
			,SUM(ROUND(WEIGHT)) GTTAKAZU
			,SUM(ROUND(MONEY)) GTTAGAKU
		FROM T_ADJUSTMENT
		WHERE DT_ADJUSTMENT > TO_DATE('&11', 'YYYY/MM/DD')
		AND DT_ADJUSTMENT < TO_DATE('&2', 'YYYY/MM/DD')
		AND (('&1' = '') OR (CD_GREAD = ('&1' ::text) ::numeric))
		AND DIV_WP_PRODUCT = 0
		AND FLG_DELETE = 0
		GROUP BY CD_GREAD , CD_SUPPLIER , CD_SHIPMENT , CD_WP
	 ) bfozaiko
	LEFT OUTER JOIN (
		SELECT CD_SUPPLIER ,NAME_SUPPLIER
		FROM M_SUPPLIER
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
		AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
		AND FLG_DELETE = 0
	) ms ON (bfozaiko.CD_SUPPLIER = MS.CD_SUPPLIER)
	LEFT OUTER JOIN (
		SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
		FROM M_SHIPMENT
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
		AND FLG_DELETE = 0
	) msip ON (bfozaiko.CD_SHIPMENT = MSIP.CD_SHIPMENT)
	WHERE bfozaiko.CD_WP = MW.CD_WP
	GROUP BY bfozaiko.CD_SUPPLIER , MS.NAME_SUPPLIER , bfozaiko.CD_SHIPMENT , MSIP.NAME_SHIPMENT
		, bfozaiko.CD_WP , bfozaiko.CD_GRADE , MW.CD_WP , MW.NAME_GRADE , MONEY_STOCK
		, WEIGHT_STOCK , GTRWKAZU , GTSWKAZU , GTTAKAZU , GTRWGAKU , GTSWGAKU , GTTAGAKU

	--故紙受入データ取得
	UNION
	SELECT
		 TRW.CD_WP --銘柄コード
		,TRW.CD_GRADE --グレード
		,MW.NAME_GRADE --銘柄名
		,TRW.CD_SUPPLIER --仕入先コード
		,MS.NAME_SUPPLIER --仕入先名
		,TRW.CD_SHIPMENT --荷送り先コード
		,MSIP.NAME_SHIPMENT --荷送り先名
		,0 --前月在庫数量
		,0 --前月在庫金額		
		,SUM(ROUND(TRW.WEIGHT_WP)) UKEIREKAZU --受入数量
		,SUM(ROUND(TRW.MONEY_WP)) UKEIREGAKU --受入金額
		,0 --払出数量
		,0 --払出金額
		,0 --棚卸数量
		,0 --棚卸金額
	FROM (
		SELECT CD_WP,CD_GRADE,NAME_GRADE
		FROM M_WP
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
		AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
		AND FLG_DELETE = 0
	) mw, t_receive_wp trw
	LEFT OUTER JOIN (
		SELECT CD_SUPPLIER ,NAME_SUPPLIER
		FROM M_SUPPLIER
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
		AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
		AND FLG_DELETE = 0
	) ms ON (TRW.CD_SUPPLIER = MS.CD_SUPPLIER)
	LEFT OUTER JOIN (
		SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
		FROM M_SHIPMENT
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
		AND FLG_DELETE = 0
	) msip ON (TRW.CD_SHIPMENT = MSIP.CD_SHIPMENT)
	WHERE TRW.CD_WP = MW.CD_WP
	AND (('&1' = '') OR (TRW.CD_GRADE = ('&1' ::text) ::numeric))
	AND TRW.DT_RECEIVESLIP >= TO_DATE('&2', 'YYYY/MM/DD')
	AND TRW.DT_RECEIVESLIP <= TO_DATE('&3', 'YYYY/MM/DD')
	AND TRW.FLG_DELETE = 0
	GROUP BY TRW.CD_SUPPLIER , MS.NAME_SUPPLIER , TRW.CD_SHIPMENT , MSIP.NAME_SHIPMENT , TRW.CD_WP , TRW.CD_GRADE , MW.NAME_GRADE

	--故紙払出データ取得
	UNION
	SELECT
		 TSW.CD_WP --銘柄コード
		,TSW.CD_GRADE --グレード
		,MW.NAME_GRADE --銘柄名
		,TSW.CD_SUPPLIER --仕入先コード
		,MS.NAME_SUPPLIER --仕入先名
		,TSW.CD_SHIPMENT --荷送り先コード
		,MSIP.NAME_SHIPMENT --荷送り先名
		,0 --前月在庫数量
		,0 --前月在庫金額
		,0 --受入数量
		,0 --受入金額
		,SUM(ROUND(TSW.WEIGHT_WP)) HARAIKAZU --払出数量
		,SUM(ROUND(TSW.MONEY_WP)) HARAIGAKU --払出金額
		,0 --棚卸数量
		,0 --棚卸金額
	FROM (
		SELECT CD_WP,CD_GRADE ,NAME_GRADE
		FROM M_WP
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
		AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
		AND FLG_DELETE = 0
	) mw, t_ship_wp tsw
	LEFT OUTER JOIN (
		SELECT CD_SUPPLIER ,NAME_SUPPLIER
		FROM M_SUPPLIER
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
		AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
		AND FLG_DELETE = 0
	) ms ON (TSW.CD_SUPPLIER = MS.CD_SUPPLIER)
	LEFT OUTER JOIN (
		SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
		FROM M_SHIPMENT
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
		AND FLG_DELETE = 0
	) msip ON (TSW.CD_SHIPMENT = MSIP.CD_SHIPMENT)
	WHERE TSW.CD_WP = MW.CD_WP
	AND (('&1' = '') OR (TSW.CD_GRADE = ('&1' ::text) ::numeric))
	AND TSW.DT_SHIPSLIP >= TO_DATE('&2', 'YYYY/MM/DD')
	AND TSW.DT_SHIPSLIP <= TO_DATE('&3', 'YYYY/MM/DD')
	AND TSW.FLG_DELETE = 0
	GROUP BY TSW.CD_SUPPLIER , MS.NAME_SUPPLIER , TSW.CD_SHIPMENT , MSIP.NAME_SHIPMENT , TSW.CD_WP , TSW.CD_GRADE , MW.NAME_GRADE

	--棚卸データ取得
	UNION
	SELECT
		 TADJ.CD_WP --銘柄コード
		,TADJ.CD_GREAD --グレード
		,MW.NAME_GRADE --銘柄名
		,TADJ.CD_SUPPLIER --仕入先コード
		,MS.NAME_SUPPLIER --仕入先名
		,TADJ.CD_SHIPMENT --荷送り先コード
		,MSIP.NAME_SHIPMENT --荷送り先名
		,0 --前月在庫数量
		,0 --前月在庫金額
		,0 --受入数量
		,0 --受入金額
		,0 --払出数量
		,0 --払出金額
		,SUM(ROUND(WEIGHT)) TANAKAZU --棚卸数量
		,SUM(ROUND(MONEY)) TANAGAKU --棚卸金額
	FROM(
		SELECT CD_SUPPLIER ,NAME_SUPPLIER
		FROM M_SUPPLIER
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
		AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
		AND FLG_DELETE = 0
	) MS,(
		SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
		FROM M_SHIPMENT
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
		AND FLG_DELETE = 0
	) MSIP,(
		SELECT CD_WP,CD_GRADE ,NAME_GRADE
		FROM M_WP
		WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
		AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
		AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
		AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
		AND FLG_DELETE = 0
	) MW,T_ADJUSTMENT TADJ
	WHERE TADJ.CD_SUPPLIER = MS.CD_SUPPLIER
	AND TADJ.CD_SHIPMENT = MSIP.CD_SHIPMENT
	AND TADJ.CD_WP = MW.CD_WP
	AND (('&1' = '') OR (TADJ.CD_GREAD = ('&1' ::text) ::numeric))
	AND TADJ.DT_ADJUSTMENT >= TO_DATE('&2', 'YYYY/MM/DD')
	AND TADJ.DT_ADJUSTMENT <= TO_DATE('&3', 'YYYY/MM/DD')
	AND TADJ.DIV_WP_PRODUCT = 0
	AND FLG_DELETE = 0
	GROUP BY TADJ.CD_SUPPLIER , MS.NAME_SUPPLIER , TADJ.CD_SHIPMENT , MSIP.NAME_SHIPMENT, TADJ.CD_WP , TADJ.CD_GREAD , MW.NAME_GRADE
) foo
WHERE NAME_SUPPLIER IS NOT NULL
AND NAME_SHIPMENT IS NOT NULL
GROUP BY CD_SUPPLIER , NAME_SUPPLIER , CD_SHIPMENT , NAME_SHIPMENT , CD_WP , CD_GRADE , NAME_GRADE
ORDER BY CD_GRADE , CD_WP , CD_SUPPLIER , CD_SHIPMENT


--**************************************************************************
--*　指定された期間のグレード別のデータを所得
--*
--**************************************************************************
[lselectRecords3]
SELECT
	 CD_GRADE CD_GRADE
	,NAME_GREAD NAME_GREAD
	,SUM(BFOGTZAIKAZU) BFOGTZAIKAZU
	,SUM(BFOGTZAIGAKU) BFOGTZAIGAKU
	,CASE WHEN SUM(GTUKEKAZU) = 0 THEN 0 ELSE SUM(GTUKEGAKU) / SUM(GTUKEKAZU) END TANKA
	,SUM(GTUKEKAZU) GTUKEKAZU
	,SUM(GTUKEGAKU) GTUKEGAKU
	,SUM(GTHARAIKAZU) GTHARAIKAZU
	,SUM(GTHARAIGAKU) GTHARAIGAKU
	,SUM(GTTANAKAZU) GTTANAKAZU
	,SUM(GTTANAGAKU) GTTANAGAKU
	,SUM(GTZAIKAZU) GTZAIKAZU
	,SUM(GTZAIGAKU) GTZAIGAKU
FROM(
	SELECT
		 MST.CD_GRADE
		,MG.NAME_GREAD
		,MST.CD_WP
		,MST.CD_SUPPLIER
		,MST.CD_SHIPMENT
		,SUM(MST.BFOZAIKAZU) BFOGTZAIKAZU
		,CASE WHEN SUM(MST.BFOZAIKAZU) = 0 THEN 0 ELSE SUM(MST.BFOZAIGAKU) END BFOGTZAIGAKU
		,SUM(MST.UKEIREKAZU) GTUKEKAZU
		,SUM(MST.UKEIREGAKU) GTUKEGAKU
		,SUM(MST.HARAIKAZU) GTHARAIKAZU
		,SUM(MST.HARAIGAKU) GTHARAIGAKU
		,SUM(MST.TANAKAZU) GTTANAKAZU
		,SUM(MST.TANAGAKU) GTTANAGAKU
		,SUM(MST.BFOZAIKAZU) + SUM(MST.UKEIREKAZU) - SUM(MST.HARAIKAZU) + SUM(MST.TANAKAZU) GTZAIKAZU
		,CASE WHEN(SUM(MST.BFOZAIKAZU) + SUM(MST.UKEIREKAZU) - SUM(MST.HARAIKAZU) + SUM(MST.TANAKAZU)) = 0 THEN 0 ELSE (CASE WHEN SUM(MST.BFOZAIKAZU) = 0 THEN 0 ELSE SUM(MST.BFOZAIGAKU) END + SUM(MST.UKEIREGAKU) - SUM(MST.HARAIGAKU) + SUM(MST.TANAGAKU)) END GTZAIGAKU
	FROM(
		--開始日付前日の在庫量取得
		SELECT
			 bfozaiko.CD_WP --銘柄コード
			,bfozaiko.CD_GRADE --グレード
			,MW.NAME_GRADE --銘柄名
			,bfozaiko.CD_SUPPLIER --仕入先コード
			,MS.NAME_SUPPLIER --仕入先名
			,bfozaiko.CD_SHIPMENT --荷送り先コード
			,MSIP.NAME_SHIPMENT --荷送り先名
			,coalesce(bfozaiko.WEIGHT_STOCK, 0) + coalesce(bfozaiko.GTRWKAZU, 0) - coalesce(bfozaiko.GTSWKAZU, 0) + coalesce(bfozaiko.GTTAKAZU, 0) BFOZAIKAZU --前月在庫数量
			,coalesce(bfozaiko.MONEY_STOCK, 0) + coalesce(bfozaiko.GTRWGAKU, 0) - coalesce(bfozaiko.GTSWGAKU, 0) + coalesce(bfozaiko.GTTAGAKU, 0) BFOZAIGAKU --前月在庫金額
			,0 UKEIREKAZU --受入数量
			,0 UKEIREGAKU --受入金額
			,0 HARAIKAZU --払出数量
			,0 HARAIGAKU --払出金額
			,0 TANAKAZU --棚卸数量
			,0 TANAGAKU --棚卸金額
		FROM (
			SELECT CD_WP,CD_GRADE ,NAME_GRADE
			FROM M_WP
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
			AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
			AND FLG_DELETE = 0
		) mw,(
			SELECT CD_GRADE,CD_SUPPLIER  ,CD_SHIPMENT ,CD_WP    ,WEIGHT_STOCK  ,MONEY_STOCK ,0 GTRWKAZU ,0 GTRWGAKU ,0 GTSWKAZU,0 GTSWGAKU ,0 GTTAKAZU ,0 GTTAGAKU
			FROM T_STOCK_WP TSTOC
			WHERE TSTOC.DTS_YM = '&10'
			AND TSTOC.FLG_DELETE = 0

			union
			SELECT CD_GRADE,CD_SUPPLIER  ,CD_SHIPMENT ,CD_WP ,0,0,SUM(ROUND(WEIGHT_WP)) GTRWKAZU ,SUM(ROUND(MONEY_WP)) GTRWGAKU ,0 ,0 ,0  ,0 
			FROM T_RECEIVE_WP TRW
			WHERE DT_RECEIVESLIP > TO_DATE('&11', 'YYYY/MM/DD')
			AND DT_RECEIVESLIP < TO_DATE('&2', 'YYYY/MM/DD')
			AND FLG_DELETE = 0
			GROUP BY CD_GRADE, CD_SUPPLIER , CD_SHIPMENT , CD_WP

			union
			SELECT CD_GRADE,CD_SUPPLIER  ,CD_SHIPMENT  ,CD_WP ,0 ,0,0 ,0 ,SUM(ROUND(WEIGHT_WP)) GTSWKAZU ,SUM(ROUND(MONEY_WP)) GTSWGAKU  ,0 ,0
			FROM T_SHIP_WP
			WHERE DT_SHIPSLIP > TO_DATE('&11', 'YYYY/MM/DD')
			AND DT_SHIPSLIP < TO_DATE('&2', 'YYYY/MM/DD')
			AND FLG_DELETE = 0
			GROUP BY CD_GRADE , CD_SUPPLIER , CD_SHIPMENT , CD_WP

			union
			SELECT CD_GREAD,CD_SUPPLIER  ,CD_SHIPMENT ,CD_WP  ,0 ,0,0 ,0,0,0 ,SUM(ROUND(WEIGHT)) GTTAKAZU ,SUM(ROUND(MONEY)) GTTAGAKU
			FROM T_ADJUSTMENT
			WHERE DT_ADJUSTMENT > TO_DATE('&11', 'YYYY/MM/DD')
			AND DT_ADJUSTMENT < TO_DATE('&2', 'YYYY/MM/DD')
			AND DIV_WP_PRODUCT = 0
			AND FLG_DELETE = 0
			GROUP BY CD_GREAD , CD_SUPPLIER , CD_SHIPMENT , CD_WP
		 ) bfozaiko
		LEFT OUTER JOIN (
			SELECT CD_SUPPLIER ,NAME_SUPPLIER
			FROM M_SUPPLIER
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
			AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
			AND FLG_DELETE = 0
		) ms ON (bfozaiko.CD_SUPPLIER = MS.CD_SUPPLIER)
		LEFT OUTER JOIN (
			SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
			FROM M_SHIPMENT
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
			AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
			AND FLG_DELETE = 0
		) msip ON (bfozaiko.CD_SHIPMENT = MSIP.CD_SHIPMENT)
		WHERE bfozaiko.CD_WP = MW.CD_WP
		GROUP BY bfozaiko.CD_SUPPLIER , MS.NAME_SUPPLIER , bfozaiko.CD_SHIPMENT , MSIP.NAME_SHIPMENT
			, bfozaiko.CD_WP , bfozaiko.CD_GRADE , MW.CD_WP , MW.NAME_GRADE , MONEY_STOCK
			, WEIGHT_STOCK , GTRWKAZU , GTSWKAZU , GTTAKAZU , GTRWGAKU , GTSWGAKU , GTTAGAKU

		--故紙受入データ取得
		UNION
		SELECT
			 TRW.CD_WP --銘柄コード
			,TRW.CD_GRADE --グレード
			,MW.NAME_GRADE --銘柄名
			,TRW.CD_SUPPLIER --仕入先コード
			,MS.NAME_SUPPLIER --仕入先名
			,TRW.CD_SHIPMENT --荷送り先コード
			,MSIP.NAME_SHIPMENT --荷送り先名
			,0 --前月在庫数量
			,0 --前月在庫金額
			,SUM(ROUND(coalesce(TRW.WEIGHT_WP, 0))) UKEIREKAZU --受入数量
			,SUM(ROUND(coalesce(TRW.MONEY_WP, 0))) UKEIREGAKU --受入金額
			,0 --払出数量
			,0 --払出金額
			,0 --棚卸数量
			,0 --棚卸金額
		FROM (
			SELECT CD_WP,CD_GRADE ,NAME_GRADE
			FROM M_WP
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
			AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
			AND FLG_DELETE = 0
		) mw, t_receive_wp trw
		LEFT OUTER JOIN (
			SELECT CD_SUPPLIER ,NAME_SUPPLIER
			FROM M_SUPPLIER
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
			AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
			AND FLG_DELETE = 0
		) ms ON (TRW.CD_SUPPLIER = MS.CD_SUPPLIER)
		LEFT OUTER JOIN (
			SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
			FROM M_SHIPMENT
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
			AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
			AND FLG_DELETE = 0
		) msip ON (TRW.CD_SHIPMENT = MSIP.CD_SHIPMENT)
		WHERE TRW.CD_WP = MW.CD_WP
		AND TRW.DT_RECEIVESLIP >= TO_DATE('&2', 'YYYY/MM/DD')
		AND TRW.DT_RECEIVESLIP <= TO_DATE('&3', 'YYYY/MM/DD')
		AND TRW.FLG_DELETE = 0
		GROUP BY TRW.CD_SUPPLIER , MS.NAME_SUPPLIER , TRW.CD_SHIPMENT , MSIP.NAME_SHIPMENT , TRW.CD_WP , TRW.CD_GRADE , MW.NAME_GRADE

		--故紙払出データ取得
		UNION
		SELECT
			 TSW.CD_WP --銘柄コード
			,TSW.CD_GRADE --グレード
			,MW.NAME_GRADE --銘柄名
			,TSW.CD_SUPPLIER --仕入先コード
			,MS.NAME_SUPPLIER --仕入先名
			,TSW.CD_SHIPMENT --荷送り先コード
			,MSIP.NAME_SHIPMENT --荷送り先名
			,0 --前月在庫数量
			,0 --前月在庫金額
			,0 --受入数量　
			,0 --受入金額　
			,SUM(ROUND(coalesce(TSW.WEIGHT_WP, 0))) HARAIKAZU --払出数量
			,SUM(ROUND(coalesce(TSW.MONEY_WP, 0))) HARAIGAKU --払出金額
			,0 --棚卸数量
			,0 --棚卸金額
		FROM (
			SELECT CD_WP,CD_GRADE ,NAME_GRADE
			FROM M_WP
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
			AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
			AND FLG_DELETE = 0
		) mw, t_ship_wp tsw
		LEFT OUTER JOIN (
			SELECT CD_SUPPLIER ,NAME_SUPPLIER
			FROM M_SUPPLIER
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
			AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
			AND FLG_DELETE = 0
		) ms ON (TSW.CD_SUPPLIER = MS.CD_SUPPLIER)
		LEFT OUTER JOIN (
			SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
			FROM M_SHIPMENT
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
			AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
			AND FLG_DELETE = 0
		) msip ON (TSW.CD_SHIPMENT = MSIP.CD_SHIPMENT)
		WHERE TSW.CD_WP = MW.CD_WP
		AND TSW.DT_SHIPSLIP >= TO_DATE('&2', 'YYYY/MM/DD')
		AND TSW.DT_SHIPSLIP <= TO_DATE('&3', 'YYYY/MM/DD')
		AND TSW.FLG_DELETE = 0
		GROUP BY TSW.CD_SUPPLIER , MS.NAME_SUPPLIER , TSW.CD_SHIPMENT , MSIP.NAME_SHIPMENT , TSW.CD_WP , TSW.CD_GRADE , MW.NAME_GRADE

		--棚卸データ取得
		UNION
		SELECT
			 TADJ.CD_WP --銘柄コード
			,TADJ.CD_GREAD --グレード
			,MW.NAME_GRADE --銘柄名
			,TADJ.CD_SUPPLIER --仕入先コード
			,MS.NAME_SUPPLIER --仕入先名
			,TADJ.CD_SHIPMENT --荷送り先コード
			,MSIP.NAME_SHIPMENT --荷送り先名
			,0 --前月在庫数量
			,0 --前月在庫金額
			,0 --受入数量
			,0 --受入金額
			,0 --払出数量
			,0 --払出金額
			,SUM(ROUND(coalesce(WEIGHT, 0))) TANAKAZU --棚卸数量
			,SUM(ROUND(coalesce(MONEY, 0))) TANAGAKU --棚卸金額
		FROM(
			SELECT CD_SUPPLIER ,NAME_SUPPLIER
			FROM M_SUPPLIER
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&8' = '') OR (CD_SUPPLIER >= ('&8' ::text) ::numeric))
			AND (('&9' = '') OR (CD_SUPPLIER <= ('&9' ::text) ::numeric))
			AND FLG_DELETE = 0
		) MS,(
			SELECT CD_SHIPMENT ,NAME_SHIPMENT,CD_SUPPLIER
			FROM M_SHIPMENT
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&4' = '') OR (CD_SHIPMENT >= ('&4' ::text) ::numeric))
			AND (('&5' = '') OR (CD_SHIPMENT <= ('&5' ::text) ::numeric))
			AND FLG_DELETE = 0 
		) MSIP,(
			SELECT CD_WP,CD_GRADE ,NAME_GRADE
			FROM M_WP
			WHERE DT_START <= TO_DATE('&3', 'YYYY/MM/DD')
			AND DT_END >= TO_DATE('&3', 'YYYY/MM/DD')
			AND (('&6' = '') OR (CD_WP >= ('&6' ::text) ::numeric))
			AND (('&7' = '') OR (CD_WP <= ('&7' ::text) ::numeric))
			AND FLG_DELETE = 0
		) MW,T_ADJUSTMENT TADJ
		WHERE TADJ.CD_SUPPLIER = MS.CD_SUPPLIER
		AND TADJ.CD_SHIPMENT = MSIP.CD_SHIPMENT
		AND TADJ.CD_WP = MW.CD_WP
		AND TADJ.DT_ADJUSTMENT >= TO_DATE('&2', 'YYYY/MM/DD')
		AND TADJ.DT_ADJUSTMENT <= TO_DATE('&3', 'YYYY/MM/DD')
		AND TADJ.DIV_WP_PRODUCT = 0
		AND FLG_DELETE = 0
		GROUP BY TADJ.CD_SUPPLIER , MS.NAME_SUPPLIER , TADJ.CD_SHIPMENT , MSIP.NAME_SHIPMENT , TADJ.CD_WP , TADJ.CD_GREAD , MW.NAME_GRADE
	)MST,M_GREAD MG
	WHERE MST.CD_GRADE = MG.CD_GRADE
	GROUP BY MST.CD_GRADE , MG.NAME_GREAD , MST.CD_WP , MST.CD_SUPPLIER , MST.CD_SHIPMENT
) alias225
GROUP BY CD_GRADE , NAME_GREAD
ORDER BY CD_GRADE