--*************************************************************************************************************
--*　製品払出データの指定されたデータを取得
--*　(到着予定日付・出荷�ａE出荷日付・グレード・得意先コード・水分��)
--**************************************************************************************************************
[lselectRecords]
SELECT
	 NO_SHIP_PRODUCT_SLIP
	,DT_DUE_ARRIVAL
	,DT_SHIP_PRODUCT
	,CD_CUSTOMER_SEND
	,NO_MOIST
	,CASE WHEN FLG_FCS is null THEN 0 ELSE FLG_FCS END FLG_FCS
FROM T_SHIP_PRODUCT TSP
WHERE (('&2' = '') OR (TSP.NO_MOIST >= ('&2' ::text) ::numeric))
AND (('&3' = '') OR (TSP.NO_MOIST <= ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.DT_SHIP_PRODUCT >= TO_DATE('&4', 'yyyy/mm/dd')))
AND (('&5' = '') OR (TSP.DT_SHIP_PRODUCT <= TO_DATE('&5', 'yyyy/mm/dd')))
AND (('&8' = '') OR (TSP.CD_CUSTOMER_SEND >= ('&8' ::text) ::numeric))
AND (('&9' = '') OR (TSP.CD_CUSTOMER_SEND <= ('&9' ::text) ::numeric))
AND (('&10' = '') OR (TSP.CD_GREAD = ('&10' ::text) ::numeric))
AND TSP.FLG_DELETE = 0
GROUP BY NO_SHIP_PRODUCT_SLIP , DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT , CD_CUSTOMER_SEND , NO_MOIST, FLG_FCS
ORDER BY DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT


--*************************************************************************************************************
--*　製品払出データの指定されたデータを取得（一般用）
--*　(到着予定日付・出荷�ａE出荷日付・グレード・得意先コード・水分��)
--**************************************************************************************************************
[lselectRecordsNormal]
SELECT
	 NO_SHIP_PRODUCT_SLIP
	,DT_DUE_ARRIVAL
	,DT_SHIP_PRODUCT
	,CD_CUSTOMER_SEND
	,NO_MOIST
	,CASE WHEN FLG_FCS is null THEN 0 ELSE FLG_FCS END FLG_FCS
FROM T_SHIP_PRODUCT TSP
WHERE (('&2' = '') OR (TSP.NO_MOIST >= ('&2' ::text) ::numeric))
AND (('&3' = '') OR (TSP.NO_MOIST <= ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.DT_SHIP_PRODUCT >= TO_DATE('&4', 'yyyy/mm/dd')))
AND (('&5' = '') OR (TSP.DT_SHIP_PRODUCT <= TO_DATE('&5', 'yyyy/mm/dd')))
AND TSP.CD_DERIVERY <> 1
AND (('&8' = '') OR (TSP.CD_CUSTOMER_SEND >= ('&8' ::text) ::numeric))
AND (('&9' = '') OR (TSP.CD_CUSTOMER_SEND <= ('&9' ::text) ::numeric))
AND (TSP.CD_CUSTOMER_SEND <> ('13' ::text) ::numeric)
AND (TSP.CD_CUSTOMER_SEND <> ('33' ::text) ::numeric)
AND (('&10' = '') OR (TSP.CD_GREAD = ('&10' ::text) ::numeric))
AND TSP.FLG_DELETE = 0
GROUP BY NO_SHIP_PRODUCT_SLIP , DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT , CD_CUSTOMER_SEND , NO_MOIST , FLG_FCS
ORDER BY DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT


--*************************************************************************************************************
--*　製品払出データの指定されたデータを取得（三菱用）
--*　(到着予定日付・出荷�ａE出荷日付・グレード・得意先コード・水分��)
--**************************************************************************************************************
[lselectRecordsMitsubishi]
SELECT
	 NO_SHIP_PRODUCT_SLIP
	,DT_DUE_ARRIVAL
	,DT_SHIP_PRODUCT
	,CD_CUSTOMER_SEND
	,NO_MOIST
	,CASE WHEN FLG_FCS is null THEN 0 ELSE FLG_FCS END FLG_FCS
FROM T_SHIP_PRODUCT TSP
WHERE (('&2' = '') OR (TSP.NO_MOIST >= ('&2' ::text) ::numeric))
AND (('&3' = '') OR (TSP.NO_MOIST <= ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.DT_SHIP_PRODUCT >= TO_DATE('&4', 'yyyy/mm/dd')))
AND (('&5' = '') OR (TSP.DT_SHIP_PRODUCT <= TO_DATE('&5', 'yyyy/mm/dd')))
AND (TSP.CD_CUSTOMER_SEND = ('13' ::text) ::numeric)
AND (('&10' = '') OR (TSP.CD_GREAD = ('&10' ::text) ::numeric))
AND TSP.FLG_DELETE = 0
GROUP BY NO_SHIP_PRODUCT_SLIP , DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT , CD_CUSTOMER_SEND , NO_MOIST , FLG_FCS
ORDER BY DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT


--************************************************************
--*　指定到着日付分の出荷明細データを取得
--*　(得意先名・納品先・製造日付・ﾊﾟﾚｯﾄ�ａE含水重量・水分率)
--*　(製造日付・ﾊﾟﾚｯﾄ�ａE水分率)  K.Takahashi 2003.07.24 出力内容変更
--************************************************************
[lselectRecords2_MCP]
SELECT
	 F1.RN RN_1
	,F1.DT_DERIVERY_PRODUCT DT_DERIVERY_PRODUCT_1
	,F1.NO_PALLET NO_PALLET_1
	,F1.RATIO_MOIST RATIO_MOIST_1
	,F2.RN RN_2
	,F2.DT_DERIVERY_PRODUCT DT_DERIVERY_PRODUCT_2
	,F2.NO_PALLET NO_PALLET_2
	,F2.RATIO_MOIST RATIO_MOIST_2
	,F2.RN RN_2
	,F3.DT_DERIVERY_PRODUCT DT_DERIVERY_PRODUCT_3
	,F3.NO_PALLET NO_PALLET_3
	,F3.RATIO_MOIST RATIO_MOIST_3
FROM (
	SELECT RN
		,DT_DERIVERY_PRODUCT
		,NO_PALLET
		,RATIO_MOIST
	FROM(
		SELECT
			 row_number() OVER () AS RN
			,DT_DERIVERY_PRODUCT
			,NO_PALLET
			,coalesce(RATIO_MOIST, 0) RATIO_MOIST
		FROM (
			SELECT *
			FROM T_SHIP_PRODUCT
			ORDER BY CD_CUSTOMER_SEND , DT_DERIVERY_PRODUCT , NO_PALLET
		) tsp
		LEFT OUTER JOIN (
			SELECT CD_CUSTOMER
				,NAME_CUSTOMER
			FROM M_CUSTOMER
			WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
			AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
			AND FLG_DELETE = 0
		) mc ON (TSP.CD_CUSTOMER_SEND = MC.CD_CUSTOMER)
		LEFT OUTER JOIN (
			SELECT CD_DERIVERY
				,CD_CUSTOMER
				,NAME_DERIVERY
			FROM M_DERIVERY
			WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
			AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
			AND FLG_DELETE = 0
		) md ON (TSP.CD_DERIVERY_SEND = MD.CD_DERIVERY)
		WHERE (('&2' = '') OR (TSP.DT_DUE_ARRIVAL = TO_DATE('&2', 'yyyy/mm/dd')))
		AND (('&3' = '') OR (TSP.CD_CUSTOMER_SEND = ('&3' ::text) ::numeric))
		AND (('&4' = '') OR (TSP.CD_GREAD = ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (TSP.NO_MOIST = ('&5' ::text) ::numeric))
		AND (('&6' = '') OR (TSP.NO_SHIP_PRODUCT_SLIP = ('&6' ::text) ::numeric))
		AND TSP.FLG_DELETE = 0
		AND ((TSP.CD_DERIVERY_SEND <> 12 AND coalesce(TSP.RATIO_MOIST, 0) <> 0) OR (TSP.CD_DERIVERY_SEND = 12))
 		ORDER BY TSP.CD_CUSTOMER_SEND , DT_DERIVERY_PRODUCT , NO_PALLET
	) alias32
	WHERE MOD(RN - 1, '&7' * 3) < '&7' * 1
) f1
LEFT OUTER JOIN (
	SELECT RN
		,DT_DERIVERY_PRODUCT
		,NO_PALLET
		,RATIO_MOIST
	FROM(
		SELECT row_number() OVER () AS RN
			,DT_DERIVERY_PRODUCT
			,NO_PALLET
			,coalesce(RATIO_MOIST, 0) RATIO_MOIST
		FROM (
			SELECT *
			FROM T_SHIP_PRODUCT
			ORDER BY CD_CUSTOMER_SEND , DT_DERIVERY_PRODUCT , NO_PALLET
		) tsp
		LEFT OUTER JOIN (
			SELECT CD_CUSTOMER
				,NAME_CUSTOMER
			FROM M_CUSTOMER
			WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
			AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
			AND FLG_DELETE = 0
		) mc ON (TSP.CD_CUSTOMER_SEND = MC.CD_CUSTOMER)
		LEFT OUTER JOIN (
			SELECT CD_DERIVERY
				,CD_CUSTOMER
				,NAME_DERIVERY
			FROM M_DERIVERY
			WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
			AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
			AND FLG_DELETE = 0
		) md ON (TSP.CD_DERIVERY_SEND = MD.CD_DERIVERY)
		WHERE (('&2' = '') OR (TSP.DT_DUE_ARRIVAL = TO_DATE('&2', 'yyyy/mm/dd')))
		AND (('&3' = '') OR (TSP.CD_CUSTOMER_SEND = ('&3' ::text) ::numeric))
		AND (('&4' = '') OR (TSP.CD_GREAD = ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (TSP.NO_MOIST = ('&5' ::text) ::numeric))
		AND (('&6' = '') OR (TSP.NO_SHIP_PRODUCT_SLIP = ('&6' ::text) ::numeric))
		AND TSP.FLG_DELETE = 0
		AND ((TSP.CD_DERIVERY_SEND <> 12 AND coalesce(TSP.RATIO_MOIST, 0) <> 0) OR (TSP.CD_DERIVERY_SEND = 12))
		ORDER BY TSP.CD_CUSTOMER_SEND , DT_DERIVERY_PRODUCT , NO_PALLET
	) alias67
	WHERE MOD(RN - 1, '&7' * 3) BETWEEN '&7' * 1 AND '&7' * 2
) f2 ON (F1.RN + ('&7' * 1) = F2.RN)
LEFT OUTER JOIN (
	SELECT RN
		,DT_DERIVERY_PRODUCT
		,NO_PALLET
		,RATIO_MOIST
	FROM (
		SELECT row_number() OVER () AS RN
			,DT_DERIVERY_PRODUCT
			,NO_PALLET
			,coalesce(RATIO_MOIST, 0) RATIO_MOIST
		FROM (
			SELECT *
			FROM T_SHIP_PRODUCT
			ORDER BY CD_CUSTOMER_SEND , DT_DERIVERY_PRODUCT , NO_PALLET
		) tsp
		LEFT OUTER JOIN (
			SELECT CD_CUSTOMER
				,NAME_CUSTOMER
			FROM M_CUSTOMER
			WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
			AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
			AND FLG_DELETE = 0
		) mc ON (TSP.CD_CUSTOMER_SEND = MC.CD_CUSTOMER)
		LEFT OUTER JOIN (
			SELECT CD_DERIVERY
				,CD_CUSTOMER
				,NAME_DERIVERY
			FROM M_DERIVERY
			WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
			AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
			AND FLG_DELETE = 0
		) md ON (TSP.CD_DERIVERY_SEND = MD.CD_DERIVERY)
		WHERE (('&2' = '') OR (TSP.DT_DUE_ARRIVAL = TO_DATE('&2', 'yyyy/mm/dd')))
		AND (('&3' = '') OR (TSP.CD_CUSTOMER_SEND = ('&3' ::text) ::numeric))
		AND (('&4' = '') OR (TSP.CD_GREAD = ('&4' ::text) ::numeric))
		AND (('&5' = '') OR (TSP.NO_MOIST = ('&5' ::text) ::numeric))
		AND (('&6' = '') OR (TSP.NO_SHIP_PRODUCT_SLIP = ('&6' ::text) ::numeric))
		AND TSP.FLG_DELETE = 0
		AND ((TSP.CD_DERIVERY_SEND <> 12 AND coalesce(TSP.RATIO_MOIST, 0) <> 0) OR (TSP.CD_DERIVERY_SEND = 12))
		ORDER BY TSP.CD_CUSTOMER_SEND , DT_DERIVERY_PRODUCT , NO_PALLET
	) alias102
	WHERE MOD(RN - 1, '&7' * 3) BETWEEN '&7' * 2 AND '&7' * 3
) f3 ON (F1.RN + ('&7' * 2) = F3.RN) ORDER BY F1.RN


--************************************************************
--*　指定到着日付分の出荷明細データを取得
--*　(得意先名・納品先・製造日付・ﾊﾟﾚｯﾄ�ａE含水重量・水分率)
--*　(製造日付・ﾊﾟﾚｯﾄ数)  K.Takahashi 2003.07.24 出力内容変更
--************************************************************
[lselectRecords2_SHIPMENT]
SELECT
	 DT_DERIVERY_PRODUCT
	,COUNT(DT_DERIVERY_PRODUCT) CNT_PALLET
FROM t_ship_product tsp
LEFT OUTER JOIN (
	SELECT
		 CD_CUSTOMER
		,NAME_CUSTOMER
	FROM M_CUSTOMER
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) mc ON (TSP.CD_CUSTOMER_SEND = MC.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT
		 CD_DERIVERY
		,CD_CUSTOMER
		,NAME_DERIVERY
	FROM M_DERIVERY
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) md ON (TSP.CD_DERIVERY_SEND = MD.CD_DERIVERY)
WHERE (('&2' = '') OR (TSP.DT_DUE_ARRIVAL = TO_DATE('&2', 'yyyy/mm/dd')))
AND (('&3' = '') OR (TSP.CD_CUSTOMER_SEND = ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.CD_GREAD = ('&4' ::text) ::numeric))
AND (('&5' = '') OR (TSP.NO_MOIST = ('&5' ::text) ::numeric))
AND (('&6' = '') OR (TSP.NO_SHIP_PRODUCT_SLIP = ('&6' ::text) ::numeric))
AND TSP.FLG_DELETE = 0
GROUP BY DT_DERIVERY_PRODUCT
ORDER BY DT_DERIVERY_PRODUCT


--****************************************************************************
--*　指定到着日付分の出荷重量計・平均水分率・風乾重量・得意先名・納品先
--*　運送会社名・車番・水分�ｂﾌデータを取得								2003/05/05 nakamura
--****************************************************************************
[lselectRecords3]
SELECT
	 SUM(WEIGHT_MOIST) TOTALWEIGHT
	,RATIO_MOIST_DISP AVG_RATIO_MOIST
	,WEIGHT_DRY_DISP
	,NAME_TRNSPORT
	,CD_CARNUMBER
	,NAME_CUSTOMER
	,NAME_DERIVERY
	,NO_MOIST
	,NAME_PRODUCT
	,COUNT(NAME_PRODUCT) CNT_PALLET
	,TO_CHAR(statement_timestamp(), 'YYYY/MM/DD') SDATE
	,MCAR.NAME_CARNUMBER
	,SUM(CASE WHEN TSP.CD_DERIVERY_SEND = 12 THEN 1 ELSE CASE WHEN coalesce(TSP.RATIO_MOIST, 0) = 0 THEN 0 ELSE 1 END END) CNT_PALLET_DISP
	,FLG_SLIP
	,CASE WHEN FLG_FCS is null THEN 0 ELSE FLG_FCS END FLG_FCS
	,CASE WHEN FLG_DEL_MOIST is null THEN 0 ELSE FLG_DEL_MOIST END FLG_DEL_MOIST
FROM t_ship_product tsp
LEFT OUTER JOIN (
	SELECT CD_CUSTOMER
		,NAME_CUSTOMER
		,FLG_SLIP
		,FLG_DEL_MOIST
	FROM M_CUSTOMER
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) mcust ON (TSP.CD_CUSTOMER_SEND = MCUST.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT CD_TRANSPORT
		,NAME_TRNSPORT
	FROM M_TRNSPORT
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) mt ON (TSP.CD_TRANSPORT = MT.CD_TRANSPORT)
LEFT OUTER JOIN m_carnumber mcar ON (TSP.CD_TRUCK = MCAR.CD_CARNUMBER AND TSP.CD_TRANSPORT = MCAR.CD_TRANSPORT)
LEFT OUTER JOIN (
	SELECT CD_DERIVERY
		,CD_CUSTOMER
		,NAME_DERIVERY
	FROM M_DERIVERY
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) md ON (TSP.CD_DERIVERY_SEND = MD.CD_DERIVERY AND TSP.CD_CUSTOMER_SEND = MD.CD_CUSTOMER)
LEFT OUTER JOIN (
	SELECT CD_PRODUCT
		,NAME_PRODUCT
	FROM M_PRODUCT
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) mp ON (TSP.CD_PRODUCT_SEND = MP.CD_PRODUCT)
WHERE (('&2' = '') OR (TSP.DT_DUE_ARRIVAL = TO_DATE('&2', 'yyyy/mm/dd')))
AND (('&3' = '') OR (TSP.CD_CUSTOMER_SEND = ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.CD_GREAD = ('&4' ::text) ::numeric))
AND (('&5' = '') OR (TSP.NO_MOIST = ('&5' ::text) ::numeric))
AND (('&6' = '') OR (TSP.NO_SHIP_PRODUCT_SLIP = ('&6' ::text) ::numeric))
AND (('&1' = '') OR (TSP.DT_SHIP_PRODUCT = TO_DATE('&1', 'yyyy/mm/dd')))
AND FLG_DELETE = 0
GROUP BY WEIGHT_DRY_DISP , NAME_TRNSPORT , CD_CARNUMBER , NAME_CUSTOMER , NAME_DERIVERY , NO_MOIST , RATIO_MOIST_DISP, NAME_PRODUCT, TO_CHAR(statement_timestamp(), 'YYYY/MM/DD'), MCAR.NAME_CARNUMBER, FLG_FCS , FLG_SLIP , FLG_DEL_MOIST


--*************************************************************************************************************
--*　製品払出データの指定されたデータを取得（クレシア用）
--*　(到着予定日付・出荷�ａE出荷日付・グレード・得意先コード・水分��)
--**************************************************************************************************************
[lselectRecordsCrecia]
SELECT
	 NO_SHIP_PRODUCT_SLIP
	,DT_DUE_ARRIVAL
	,DT_SHIP_PRODUCT
	,CD_CUSTOMER_SEND
	,NO_MOIST
FROM T_SHIP_PRODUCT TSP
WHERE (('&2' = '') OR (TSP.NO_MOIST >= ('&2' ::text) ::numeric))
AND (('&3' = '') OR (TSP.NO_MOIST <= ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.DT_SHIP_PRODUCT >= TO_DATE('&4', 'yyyy/mm/dd')))
AND (('&5' = '') OR (TSP.DT_SHIP_PRODUCT <= TO_DATE('&5', 'yyyy/mm/dd')))
AND TSP.CD_DERIVERY = 1
AND (TSP.CD_CUSTOMER_SEND <> ('13' ::text) ::numeric)
AND (('&10' = '') OR (TSP.CD_GREAD = ('&10' ::text) ::numeric))
AND TSP.FLG_DELETE = 0
GROUP BY NO_SHIP_PRODUCT_SLIP , DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT , CD_CUSTOMER_SEND , NO_MOIST
ORDER BY DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT


--*************************************************************************************************************
--*　製品払出データの指定されたデータを取得（ケイミュー用）
--*　(到着予定日付・出荷�ａE出荷日付・グレード・得意先コード・水分��)
--**************************************************************************************************************
[lselectRecordsKeimyu]
SELECT
	 NO_SHIP_PRODUCT_SLIP
	,DT_DUE_ARRIVAL
	,DT_SHIP_PRODUCT
	,CD_CUSTOMER_SEND
	,NO_MOIST
FROM T_SHIP_PRODUCT TSP
WHERE (('&2' = '') OR (TSP.NO_MOIST >= ('&2' ::text) ::numeric))
AND (('&3' = '') OR (TSP.NO_MOIST <= ('&3' ::text) ::numeric))
AND (('&4' = '') OR (TSP.DT_SHIP_PRODUCT >= TO_DATE('&4', 'yyyy/mm/dd')))
AND (('&5' = '') OR (TSP.DT_SHIP_PRODUCT <= TO_DATE('&5', 'yyyy/mm/dd')))
AND (TSP.CD_CUSTOMER_SEND = ('33' ::text) ::numeric)
AND (('&10' = '') OR (TSP.CD_GREAD = ('&10' ::text) ::numeric))
AND TSP.FLG_DELETE = 0
GROUP BY NO_SHIP_PRODUCT_SLIP , DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT , CD_CUSTOMER_SEND , NO_MOIST , FLG_FCS
ORDER BY DT_DUE_ARRIVAL , NO_MOIST , DT_SHIP_PRODUCT