--*********************************************
--*ƒf[ƒ^—L–³ƒ`ƒFƒbƒN
--*
--*********************************************
[lselectRecords]
select
	 T2.DTS_YEAR
	,T2.DTS_MONTH
FROM (
	select
		 T.DTS_YEAR
		,T.DTS_MONTH
	from M_GREAD MG ,(
		select
			 DTS_YEAR
			,DTS_MONTH
		from T_MCP
		where DT_MCP >= to_date('&2', 'yyyy/mm/dd')
		and DT_MCP <= to_date('&4', 'yyyy/mm/dd')
		and (('&3' = '') or (CD_GREAD = ('&3' ::text) ::numeric))
		union
		select
			 DTS_YEAR
			,DTS_MONTH
		from T_DERIVERY_PRODUCT
		where DT_DERIVERY_PRODUCT >= to_date('&2', 'yyyy/mm/dd')
		and DT_DERIVERY_PRODUCT <= to_date('&4', 'yyyy/mm/dd')
		and (('&3' = '') or (CD_GREAD = ('&3' ::text) ::numeric))
		union
		select
			 DTS_YEAR
			,DTS_MONTH
		from T_ADJUSTMENT
		where DT_ADJUSTMENT >= to_date('&2', 'yyyy/mm/dd')
		and DT_ADJUSTMENT <= to_date('&4', 'yyyy/mm/dd')
	) T
	GROUP BY T.DTS_YEAR, T.DTS_MONTH
	order by T.DTS_YEAR, T.DTS_MONTH
) T2
where T2.DTS_YEAR is not null
and T2.DTS_MONTH is not null


--*********************************************
--*‘€‹ΖŽžŠΤE‚o‚l‰^“]ŽžŠΤ
--*
--*********************************************
[lDB_TIME_PM]
SELECT PRC_GET_TIMES('&1', '&2', '01', '&6', '&7', '&8')

--*********************************************
--* Žw’θŠϊŠΤ•ͺ‚Μƒf[ƒ^‚πŽζ“Ύ
--*
--*********************************************
[lselectRecords2]
SELECT
	 MCP2.DTS_YEAR
	,MCP2.CD_GREAD
	,MCP2.AVE_MOIST
	,MCP2.DIV_WEIGHT_DRIED
	,trunc((SUM(coalesce(MCP2.GANSUI1, 0)) / 1000) + 0.5) GANSUI
	,trunc((SUM(coalesce(MCP2.FUKAN1, 0)) / 1000) + 0.5) FUKAN
	,SUM(coalesce(MCP2.SUIBUN1, 0)) SUIBUN
	,trunc((SUM(coalesce(MCP2.TANAFU1, 0)) / 1000) + 0.5) TANAFU
FROM (
	select
		 TDP.DTS_YEAR
		,TDP.CD_GREAD
		,MG.AVE_MOIST
		,MD.DIV_WEIGHT_DRIED
		,coalesce(sum(WEIGHT_MOIST), 0) GANSUI1
		,coalesce(sum(WEIGHT_DRY), 0) fukan1
		,coalesce(sum(RATIO_MOIST), 0) SUIBUN1
		,0 tanafu1
	from T_DERIVERY_PRODUCT TDP , M_GREAD MG ,(
		SELECT
			 CD_DERIVERY
			,CD_CUSTOMER
			,DIV_WEIGHT_DRIED
		FROM M_DERIVERY
		WHERE DT_START <= TO_DATE('&5', 'yyyy/mm/dd')
		AND DT_END >= TO_DATE('&5', 'yyyy/mm/dd')
	) MD
	where DT_DERIVERY_PRODUCT >= TO_DATE('&5', 'yyyy/mm/dd')
	AND DT_DERIVERY_PRODUCT <= TO_DATE('&9', 'yyyy/mm/dd')
	AND (('&3' = '') OR (TDP.CD_GREAD = ('&3' ::text) ::numeric))
	AND TDP.CD_GREAD = MG.CD_GRADE
	AND TDP.CD_DERIVERY = MD.CD_DERIVERY
	AND TDP.FLG_DELETE = 0
	group by TDP.DTS_YEAR , TDP.CD_GREAD, MG.AVE_MOIST, MD.DIV_WEIGHT_DRIED
	UNION
	select
		 TA.DTS_YEAR
		,TA.CD_GREAD
		,MG.AVE_MOIST
		,MD.DIV_WEIGHT_DRIED
		,0 GANSUI1
		,0 fukan1
		,0 SUIBUN1
		,sum(coalesce(TA.WEIGHT, 0)) tanafu1
	from T_ADJUSTMENT TA , M_GREAD MG ,(
		SELECT
			 CD_DERIVERY
			,CD_CUSTOMER
			,DIV_WEIGHT_DRIED
		FROM M_DERIVERY
		WHERE DT_START <= TO_DATE('&5', 'yyyy/mm/dd')
		AND DT_END >= TO_DATE('&5', 'yyyy/mm/dd')
	) MD
	where DT_ADJUSTMENT >= TO_DATE('&5', 'yyyy/mm/dd')
	AND DT_ADJUSTMENT <= TO_DATE('&9', 'yyyy/mm/dd')
	AND (('&3' = '') OR (TA.CD_GREAD = ('&3' ::text) ::numeric))
	AND TA.CD_GREAD = MG.CD_GRADE
	AND TA.CD_DERIVERY = MD.CD_DERIVERY
	AND TA.FLG_DELETE = 0
	AND DIV_WP_PRODUCT = 1
	group by TA.DTS_YEAR , TA.CD_GREAD, MG.AVE_MOIST, MD.DIV_WEIGHT_DRIED
) MCP2
GROUP BY MCP2.DTS_YEAR, MCP2.CD_GREAD, MCP2.AVE_MOIST, MCP2.DIV_WEIGHT_DRIED


--*********************************************
--*ŒΜŽ†“Š“όΓή°ΐ’Šo‚r‚p‚k
--*
--*********************************************
[lselectRecords3]
SELECT
	 DTS_YEAR
	,CD_GRADE_PRODUCT
	,trunc((SUM(HARIJURYOU1) / 1000) + 0.5) HARIJURYOU
	,trunc((SUM(TANAWP1) / 1000) + 0.5) TANAWP
FROM (
	SELECT
		 DTS_YEAR
		,CD_GRADE_PRODUCT
		,coalesce(sum(WEIGHT_WP), 0) HARIJURYOU1
		,0 TANAWP1
	FROM T_SHIP_WP TSW
	WHERE DT_SHIPSLIP >= TO_DATE('&5', 'yyyy/mm/dd')
	AND DT_SHIPSLIP <= TO_DATE('&9', 'yyyy/mm/dd')
	AND (('&3' = '') OR (CD_GRADE_PRODUCT = ('&3' ::text) ::numeric))
	AND FLG_DELETE = 0
	GROUP BY DTS_YEAR , CD_GRADE_PRODUCT
	union
	SELECT
		 DTS_YEAR
		,CD_GREAD CD_GRADE_PRODUCT
		,0 HARIJURYOU1
		,coalesce(sum(WEIGHT), 0) TANAWP1
	FROM T_ADJUSTMENT
	WHERE DT_ADJUSTMENT >= TO_DATE('&5', 'yyyy/mm/dd')
	AND DT_ADJUSTMENT <= TO_DATE('&9', 'yyyy/mm/dd')
	AND (('&3' = '') OR (CD_GREAD = ('&3' ::text) ::numeric))
	AND FLG_DELETE = 0
	AND DIV_WP_PRODUCT = 0
	GROUP BY DTS_YEAR, CD_GREAD
) alias22
GROUP BY DTS_YEAR, CD_GRADE_PRODUCT

  
--*********************************************
--*	‚l‚b‚o‘€‹ΖΓή°ΐ’Šo‚r‚p‚k
--*
--*********************************************
[lselectRecords4]
SELECT
	 DTS_YEAR
	,CD_GREAD
	,coalesce(sum(TIME_OPERATE_PLAN), 0) SOUGYOUYOTEI
	,coalesce(sum(TIME_OPERATE), 0) PM
	,trunc((coalesce(sum(WEIGHT_PLAN), 0) / 1000) + 0.5) SEISANYOTEI
	,coalesce(sum(TIME_PM_WORK), 0) SOUGYOU
FROM T_MCP
WHERE DT_MCP >= TO_DATE('&5', 'yyyy/mm/dd')
AND DT_MCP <= TO_DATE('&9', 'yyyy/mm/dd')
AND (('&3' = '') OR (CD_GREAD = ('&3' ::text) ::numeric))
AND FLG_DELETE = 0
GROUP BY DTS_YEAR , CD_GREAD