--********************************************************
--*　故紙投入日報より指定された日付とパルパー�ｂ�取得
--********************************************************
[lselectRecords5]
SELECT
	 TSW.DT_SHIPSLIP
	,TSW.DTS_YEAR
	,TSW.DTS_MONTH
	,TSW.DTS_DAY
FROM T_SHIP_WP TSW
WHERE TSW.DT_SHIPSLIP >= TO_DATE('&10', 'yyyy/mm/dd')
AND TSW.DT_SHIPSLIP <= TO_DATE('&11', 'yyyy/mm/dd')
AND TSW.FLG_DELETE = 0
AND (('&8' = '') OR (TSW.CD_PULPER = ('&8'::text) ::numeric))
AND (('&9' = '') OR (TSW.CD_GRADE_PRODUCT = ('&9'::text) ::numeric))
GROUP BY TSW.DT_SHIPSLIP , TSW.DTS_YEAR , TSW.DTS_MONTH , TSW.DTS_DAY
ORDER BY TSW.DT_SHIPSLIP


--************************************************************************
--*　故紙投入日報の指定された日数分の明細を取得
--*　（日付・運賃込平均単価(日計・低単価日計・AG月計・BG月計・CG月計・月計)・
--*　　　  　数量(日計・低単価日計・AG月計・BG月計・CG月計・月計)・
--*　　　  　合計(日計・低単価日計・AG月計・BG月計・CG月計・月計)）
--*************************************************************************
[lselectRecords]
SELECT
	 TSW.DT_SHIPSLIP
	,TSW.DTS_YEAR
	,TSW.DTS_MONTH
	,TSW.DTS_DAY
	,coalesce(NITI.NTANKA, 0) NTANKA
	,coalesce(NITI.NSUURYOU, 0) NSUURYOU
	,coalesce(NITI.NKINGAKU, 0) NKINGAKU
	,coalesce(LOW.LTANKA, 0) LTANKA
	,coalesce(LOW.LSUURYOU, 0) LSUURYOU
	,coalesce(LOW.LKINGAKU, 0) LKINGAKU
	,coalesce(AG.AGTANKA, 0) AGTANKA
	,coalesce(AG.AGSUURYOU, 0) AGSUURYOU
	,coalesce(AG.AGKINGAKU, 0) AGKINGAKU
	,coalesce(BG.BGTANKA, 0) BGTANKA
	,coalesce(BG.BGSUURYOU, 0) BGSUURYOU
	,coalesce(BG.BGKINGAKU, 0) BGKINGAKU
	,coalesce(CG.CGTANKA, 0) CGTANKA
	,coalesce(CG.CGSUURYOU, 0) CGSUURYOU
	,coalesce(CG.CGKINGAKU, 0) CGKINGAKU
	,coalesce(GETUKEI.GETUTANKA, 0) GETUTANKA
	,coalesce(GETUKEI.GETUSUURYOU, 0) GETUSUURYOU
	,coalesce(GETUKEI.GETUKINGAKU, 0) GETUKINGAKU
	,AG_SEIHIN.A_SEIHINKIJYUN
	,AG_SEIHIN.A_SEIHINTANKA
	,AG_SEIHIN.A_SEIHINKAZU
	,AG_SEIHIN.A_SEIHINGAKU
	,AG_SEIHIN.A_STD_MONEY_PRODUCT
	,BG_SEIHIN.B_SEIHINKIJYUN
	,BG_SEIHIN.B_SEIHINTANKA
	,BG_SEIHIN.B_SEIHINKAZU
	,BG_SEIHIN.B_SEIHINGAKU
	,BG_SEIHIN.B_STD_MONEY_PRODUCT
	,CG_SEIHIN.C_SEIHINKIJYUN
	,CG_SEIHIN.C_SEIHINTANKA
	,CG_SEIHIN.C_SEIHINKAZU
	,CG_SEIHIN.C_SEIHINGAKU
	,CG_SEIHIN.C_STD_MONEY_PRODUCT
	,SEIHINGETUKEI.SEIHINKIJYUN
	,SEIHINGETUKEI.SEIHINTANKA
	,SEIHINGETUKEI.SEIHINKAZU
	,SEIHINGETUKEI.SEIHINGAKU
	,SEIHINGETUKEI.STD_MONEY_PRODUCT
FROM t_ship_wp tsw
LEFT OUTER JOIN (
	SELECT
		 DT_SHIPSLIP
		,ROUND((SUM(MONEY_WP) / SUM(WEIGHT_WP)), 1) NTANKA
		,SUM(WEIGHT_WP) NSUURYOU
		,SUM(MONEY_WP) NKINGAKU
	FROM T_SHIP_WP
	WHERE DT_SHIPSLIP = TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
	AND (('&2' = '') OR (CD_PULPER = ('&2' ::text) ::numeric))
	GROUP BY DT_SHIPSLIP
) niti ON (TSW.DT_SHIPSLIP = NITI.DT_SHIPSLIP AND TSW.DT_SHIPSLIP = NITI.DT_SHIPSLIP)
LEFT OUTER JOIN (
	SELECT
		 DT_SHIPSLIP
		,ROUND((SUM(MONEY_WP) / SUM(WEIGHT_WP)), 1) LTANKA
		,SUM(WEIGHT_WP) LSUURYOU
		,SUM(MONEY_WP) LKINGAKU
	FROM T_SHIP_WP
	WHERE DT_SHIPSLIP = TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_LOWPLICE = 1
	AND FLG_DELETE = 0
	AND (('&2' = '') OR (CD_PULPER = ('&2' ::text) ::numeric))
	GROUP BY DT_SHIPSLIP
) low ON (TSW.DT_SHIPSLIP = LOW.DT_SHIPSLIP AND TSW.DT_SHIPSLIP = LOW.DT_SHIPSLIP)
LEFT OUTER JOIN (
	SELECT
		 DTS_YEAR
		,DTS_MONTH
		,ROUND((SUM(MONEY_WP) / SUM(WEIGHT_WP)), 1) AGTANKA
		,SUM(WEIGHT_WP) AGSUURYOU
		,SUM(MONEY_WP) AGKINGAKU
	FROM T_SHIP_WP
	WHERE (('&10' = '') OR (DTS_YEAR = ('&10' ::text) ::numeric))
	AND (('&11' = '') OR (DTS_MONTH = ('&11' ::text) ::numeric))
	AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
	AND (('&2' = '') OR (CD_PULPER = ('&2' ::text) ::numeric))
	AND CD_GRADE = 1
	AND FLG_DELETE = 0
	GROUP BY DTS_YEAR , DTS_MONTH
) ag ON (TSW.DTS_YEAR = AG.DTS_YEAR AND TSW.DTS_MONTH = AG.DTS_MONTH)
LEFT OUTER JOIN (
	SELECT
		 DTS_YEAR
		,DTS_MONTH
		,ROUND((SUM(MONEY_WP) / SUM(WEIGHT_WP)), 1) BGTANKA
		,SUM(WEIGHT_WP) BGSUURYOU
		,SUM(MONEY_WP) BGKINGAKU
	FROM T_SHIP_WP
	WHERE (('&10' = '') OR (DTS_YEAR = ('&10' ::text) ::numeric))
	AND (('&11' = '') OR (DTS_MONTH = ('&11' ::text) ::numeric))
	AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
	AND (('&2' = '') OR (CD_PULPER = ('&2' ::text) ::numeric))
	AND CD_GRADE = 2
	AND FLG_DELETE = 0
	GROUP BY DTS_YEAR , DTS_MONTH
) bg ON (TSW.DTS_YEAR = BG.DTS_YEAR AND TSW.DTS_MONTH = BG.DTS_MONTH)
LEFT OUTER JOIN (
	SELECT
		 DTS_YEAR
		,DTS_MONTH
		,ROUND((SUM(MONEY_WP) / SUM(WEIGHT_WP)), 1) CGTANKA
		,SUM(WEIGHT_WP) CGSUURYOU
		,SUM(MONEY_WP) CGKINGAKU
	FROM T_SHIP_WP
	WHERE (('&10' = '') OR (DTS_YEAR = ('&10' ::text) ::numeric))
	AND (('&11' = '') OR (DTS_MONTH = ('&11' ::text) ::numeric))
	AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
	AND (('&2' = '') OR (CD_PULPER = ('&2' ::text) ::numeric))
	AND CD_GRADE = 3
	AND FLG_DELETE = 0
	GROUP BY DTS_YEAR , DTS_MONTH
) cg ON (TSW.DTS_YEAR = CG.DTS_YEAR AND TSW.DTS_MONTH = CG.DTS_MONTH)
LEFT OUTER JOIN (
	SELECT
		 DTS_YEAR
		,DTS_MONTH
		,ROUND((SUM(MONEY_WP) / SUM(WEIGHT_WP)), 1) GETUTANKA
		,SUM(WEIGHT_WP) GETUSUURYOU
		,SUM(MONEY_WP) GETUKINGAKU
	FROM T_SHIP_WP
	WHERE (('&10' = '') OR (DTS_YEAR = ('&10' ::text) ::numeric))
	AND (('&11' = '') OR (DTS_MONTH = ('&11' ::text) ::numeric))
	AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
	AND (('&2' = '') OR (CD_PULPER = ('&2' ::text) ::numeric))
	AND FLG_DELETE = 0
	GROUP BY DTS_YEAR , DTS_MONTH
) getukei ON (TSW.DTS_YEAR = GETUKEI.DTS_YEAR AND TSW.DTS_MONTH = GETUKEI.DTS_MONTH)
LEFT OUTER JOIN (
	select
		 sum(DTS_YEAR) DTS_YEAR
		,sum(DTS_MONTH) DTS_MONTH
		,sum(SEIHINTANKA) A_SEIHINTANKA
		,sum(SEIHINKAZU) A_SEIHINKAZU
		,sum(SEIHINGAKU) A_SEIHINGAKU
		,sum(STD_MONEY_PRODUCT) A_STD_MONEY_PRODUCT
		,round((sum(STD_MONEY_PRODUCT) / sum(SEIHINKAZU)), 1) A_SEIHINKIJYUN
	from (
		select
			 TSW.DTS_YEAR
			,TSW.DTS_MONTH
			,round((sum(MONEY_WP) / sum(WEIGHT_WP)), 1) SEIHINTANKA
			,sum(WEIGHT_WP) SEIHINKAZU
			,sum(MONEY_WP) SEIHINGAKU
			,0 STD_MONEY_PRODUCT
			,0 DTS_DAY
			,0 NO_BATCH
		FROM T_SHIP_WP TSW , M_DERIVERY_PRODUCT MDP
		WHERE (('&10' = '') OR (TSW.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW.CD_GRADE_PRODUCT = 1
		AND TSW.FLG_DELETE = 0
		AND TSW.CD_DERIVERY = MDP.CD_DERIVERY
		AND TSW.CD_PRODUCT = MDP.CD_PRODUCT
		GROUP BY TSW.DTS_YEAR , TSW.DTS_MONTH
		union
		select
			 0
			,0
			,0
			,0
			,0
			,STD_MONEY_PRODUCT
			,DTS_DAY
			,NO_BATCH
		from T_SHIP_WP TSW2
		WHERE (('&10' = '') OR (TSW2.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW2.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW2.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW2.CD_GRADE_PRODUCT = 1
		AND TSW2.FLG_DELETE = 0
		group by STD_MONEY_PRODUCT, DTS_DAY , NO_BATCH
	) alias182
) ag_seihin ON (TSW.DTS_YEAR = AG_SEIHIN.DTS_YEAR AND TSW.DTS_MONTH = AG_SEIHIN.DTS_MONTH)
LEFT OUTER JOIN (
	select
		 sum(DTS_YEAR) DTS_YEAR
		,sum(DTS_MONTH) DTS_MONTH
		,sum(SEIHINTANKA) B_SEIHINTANKA
		,sum(SEIHINKAZU) B_SEIHINKAZU
		,sum(SEIHINGAKU) B_SEIHINGAKU
		,sum(STD_MONEY_PRODUCT) B_STD_MONEY_PRODUCT
		,round((sum(STD_MONEY_PRODUCT) / sum(SEIHINKAZU)), 1) B_SEIHINKIJYUN
	from (
		select
			 TSW.DTS_YEAR
			,TSW.DTS_MONTH
			,round((sum(MONEY_WP) / sum(WEIGHT_WP)), 1) SEIHINTANKA
			,sum(WEIGHT_WP) SEIHINKAZU
			,sum(MONEY_WP) SEIHINGAKU
			,0 STD_MONEY_PRODUCT
			,0 DTS_DAY
			,0 NO_BATCH
		FROM T_SHIP_WP TSW , M_DERIVERY_PRODUCT MDP
		WHERE (('&10' = '') OR (TSW.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW.CD_GRADE_PRODUCT = 2
		AND TSW.FLG_DELETE = 0
		AND TSW.CD_DERIVERY = MDP.CD_DERIVERY
		AND TSW.CD_PRODUCT = MDP.CD_PRODUCT
		GROUP BY TSW.DTS_YEAR , TSW.DTS_MONTH
		union
		select
			 0
			,0
			,0
			,0
			,0
			,STD_MONEY_PRODUCT
			,DTS_DAY
			,NO_BATCH
		from T_SHIP_WP TSW2
		WHERE (('&10' = '') OR (TSW2.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW2.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW2.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW2.CD_GRADE_PRODUCT = 2
		AND TSW2.FLG_DELETE = 0
		group by STD_MONEY_PRODUCT, DTS_DAY , NO_BATCH
	) alias232
) bg_seihin ON (TSW.DTS_YEAR = BG_SEIHIN.DTS_YEAR AND TSW.DTS_MONTH = BG_SEIHIN.DTS_MONTH)
LEFT OUTER JOIN (
	select
		 sum(DTS_YEAR) DTS_YEAR
		,sum(DTS_MONTH) DTS_MONTH
		,sum(SEIHINTANKA) C_SEIHINTANKA
		,sum(SEIHINKAZU) C_SEIHINKAZU
		,sum(SEIHINGAKU) C_SEIHINGAKU
		,sum(STD_MONEY_PRODUCT) C_STD_MONEY_PRODUCT
		,round((sum(STD_MONEY_PRODUCT) / sum(SEIHINKAZU)), 1) C_SEIHINKIJYUN
	from (
		select
			 TSW.DTS_YEAR
			,TSW.DTS_MONTH
			,round((sum(MONEY_WP) / sum(WEIGHT_WP)), 1) SEIHINTANKA
			,sum(WEIGHT_WP) SEIHINKAZU
			,sum(MONEY_WP) SEIHINGAKU
			,0 STD_MONEY_PRODUCT
			,0 DTS_DAY
			,0 NO_BATCH
		FROM T_SHIP_WP TSW , M_DERIVERY_PRODUCT MDP
		WHERE (('&10' = '') OR (TSW.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW.CD_GRADE_PRODUCT = 3
		AND TSW.FLG_DELETE = 0
		AND TSW.CD_DERIVERY = MDP.CD_DERIVERY
		AND TSW.CD_PRODUCT = MDP.CD_PRODUCT
		GROUP BY TSW.DTS_YEAR , TSW.DTS_MONTH
		union
		select
			 0
			,0
			,0
			,0
			,0
			,STD_MONEY_PRODUCT
			,DTS_DAY
			,NO_BATCH
		from T_SHIP_WP TSW2
		WHERE (('&10' = '') OR (TSW2.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW2.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW2.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW2.CD_GRADE_PRODUCT = 3
		AND TSW2.FLG_DELETE = 0
		group by STD_MONEY_PRODUCT, DTS_DAY , NO_BATCH
	) alias282
) cg_seihin ON (TSW.DTS_YEAR = CG_SEIHIN.DTS_YEAR AND TSW.DTS_MONTH = CG_SEIHIN.DTS_MONTH)
LEFT OUTER JOIN (
	select
		 sum(DTS_YEAR) DTS_YEAR
		,sum(DTS_MONTH) DTS_MONTH
		,sum(SEIHINTANKA) SEIHINTANKA
		,sum(SEIHINKAZU) SEIHINKAZU
		,sum(SEIHINGAKU) SEIHINGAKU
		,sum(STD_MONEY_PRODUCT) STD_MONEY_PRODUCT
		,round((sum(STD_MONEY_PRODUCT) / sum(SEIHINKAZU)), 1) SEIHINKIJYUN
	from (
		select
			 TSW.DTS_YEAR
			,TSW.DTS_MONTH
			,round((sum(MONEY_WP) / sum(WEIGHT_WP)), 1) SEIHINTANKA
			,sum(WEIGHT_WP) SEIHINKAZU
			,sum(MONEY_WP) SEIHINGAKU
			,0 STD_MONEY_PRODUCT
			,0 DTS_DAY
			,0 NO_BATCH
		FROM T_SHIP_WP TSW , M_DERIVERY_PRODUCT MDP
		WHERE (('&10' = '') OR (TSW.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW.FLG_DELETE = 0
		AND TSW.CD_DERIVERY = MDP.CD_DERIVERY
		AND TSW.CD_PRODUCT = MDP.CD_PRODUCT
		GROUP BY TSW.DTS_YEAR , TSW.DTS_MONTH
		union
		select
			 0
			,0
			,0
			,0
			,0
			,STD_MONEY_PRODUCT
			,DTS_DAY
			,NO_BATCH
		from T_SHIP_WP TSW2
		WHERE (('&10' = '') OR (TSW2.DTS_YEAR = ('&10' ::text) ::numeric))
		AND (('&11' = '') OR (TSW2.DTS_MONTH = ('&11' ::text) ::numeric))
		AND (('&12' = '') OR (DTS_DAY <= ('&12' ::text) ::numeric))
		AND (('&2' = '') OR (TSW2.CD_PULPER = ('&2' ::text) ::numeric))
		AND TSW2.FLG_DELETE = 0
		group by STD_MONEY_PRODUCT, DTS_DAY , NO_BATCH
	) alias332
) seihingetukei ON (TSW.DTS_YEAR = SEIHINGETUKEI.DTS_YEAR AND TSW.DTS_MONTH = SEIHINGETUKEI.DTS_MONTH)
WHERE TSW.DT_SHIPSLIP = TO_DATE('&1' ,'yyyy/mm/dd')
AND (('&2' = '') OR (TSW.CD_PULPER = ('&2' ::text) ::numeric))
AND (('&9' = '') OR (TSW.CD_GRADE_PRODUCT = ('&9' ::text) ::numeric))
AND TSW.FLG_DELETE = 0
GROUP BY TSW.DT_SHIPSLIP , TSW.DTS_YEAR , TSW.DTS_MONTH , TSW.DTS_DAY , NITI.NTANKA , NITI.NSUURYOU , NITI.NKINGAKU , LOW.LTANKA , LOW.LSUURYOU , LOW.LKINGAKU , AG.AGTANKA , AG.AGSUURYOU , AG.AGKINGAKU , BG.BGTANKA , BG.BGSUURYOU , BG.BGKINGAKU , CG.CGTANKA , CG.CGSUURYOU , CG.CGKINGAKU , GETUKEI.GETUTANKA , GETUKEI.GETUSUURYOU , GETUKEI.GETUKINGAKU , AG_SEIHIN.A_SEIHINKIJYUN , AG_SEIHIN.A_SEIHINTANKA , AG_SEIHIN.A_SEIHINKAZU , AG_SEIHIN.A_SEIHINGAKU , AG_SEIHIN.A_STD_MONEY_PRODUCT , BG_SEIHIN.B_SEIHINKIJYUN , BG_SEIHIN.B_SEIHINTANKA , BG_SEIHIN.B_SEIHINKAZU , BG_SEIHIN.B_SEIHINGAKU , BG_SEIHIN.B_STD_MONEY_PRODUCT , CG_SEIHIN.C_SEIHINKIJYUN , CG_SEIHIN.C_SEIHINTANKA , CG_SEIHIN.C_SEIHINKAZU , CG_SEIHIN.C_SEIHINGAKU , CG_SEIHIN.C_STD_MONEY_PRODUCT , SEIHINGETUKEI.SEIHINKIJYUN , SEIHINGETUKEI.SEIHINTANKA , SEIHINGETUKEI.SEIHINKAZU , SEIHINGETUKEI.SEIHINGAKU , SEIHINGETUKEI.STD_MONEY_PRODUCT
ORDER BY TSW.DT_SHIPSLIP


--*******************************
--* パルパー別のバッチ�ｂ�取得
--*　　(1日分)
--*******************************
[lselectRecords2]
SELECT
	 DT_SHIPSLIP
	,CD_PULPER
	,NO_BATCH
FROM T_SHIP_WP
WHERE DT_SHIPSLIP = TO_DATE('&1', 'yyyy/mm/dd')
AND (('&2' = '') OR (CD_PULPER = ('&2'::text) ::numeric))
AND (('&9' = '') OR (CD_GRADE_PRODUCT = ('&9'::text) ::numeric))
AND FLG_DELETE = 0
GROUP BY DT_SHIPSLIP , CD_PULPER , NO_BATCH
ORDER BY CD_PULPER , NO_BATCH


--*****************************************************************************************
--* パルパー別の投入データを取得　(1日分(1パルパー、1バッチ分))
--*　(得意先名・納品先・製品・製品グレード・仕入先・荷送先・銘柄・運賃込単価・数量・金額)
--*****************************************************************************************
[lselectRecords3]
SELECT
	 TSW.CD_PULPER
	,TSW.NO_BATCH
	,(SELECT NAME_STAFF FROM M_STAFF WHERE CD_STAFF = TSW.CD_STAFF) NAME_STAFF
	,round((coalesce(MDP.UNIT_PRICE_PRODUCT, 0) ::text) ::numeric, 1) UNIT_PRICE_PRODUCT
	,TO_CHAR(TSW.TM_SHIP, 'YYYY/MM/DD HH24:MI:SS') TM_SHIP
	,NAME_CUSTOMER
	,NAME_DERIVERY
	,NAME_PRODUCT
	,MG.NAME_GREAD SEIHINGREAD
	,NAME_SUPPLIER
	,NAME_SHIPMENT
	,MW.NAME_GRADE
	,UNIT_PRICE_WP
	,WEIGHT_WP
	,MONEY_WP
	,coalesce(FLG_LOWPLICE, 0) FLG_LOWPLICE
	,round((coalesce(MDP.UNIT_PRICE_PRODUCT, 0) - coalesce(MDP.STD_PRICE_PRODUCT, 0)), 1) KIJYUNGAKU
	,COMMENT1
	,COMMENT2
FROM t_ship_wp tsw
LEFT OUTER JOIN m_customer mc ON (TSW.CD_CUSTOMER = MC.CD_CUSTOMER)
LEFT OUTER JOIN m_derivery md ON (TSW.CD_DERIVERY = MD.CD_DERIVERY)
LEFT OUTER JOIN m_product mp ON (TSW.CD_PRODUCT = MP.CD_PRODUCT)
LEFT OUTER JOIN m_gread mg ON (TSW.CD_GRADE_PRODUCT = MG.CD_GRADE)
LEFT OUTER JOIN (
	SELECT
		 CD_SUPPLIER
		,NAME_SUPPLIER
	FROM M_SUPPLIER
	WHERE DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
	AND DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
	AND FLG_DELETE = 0
) ms ON (TSW.CD_SUPPLIER = MS.CD_SUPPLIER)
LEFT OUTER JOIN m_shipment msip ON (TSW.CD_SHIPMENT = MSIP.CD_SHIPMENT)
LEFT OUTER JOIN m_wp mw ON (TSW.CD_WP = MW.CD_WP)
LEFT OUTER JOIN m_derivery_product mdp ON (TSW.CD_DERIVERY = MDP.CD_DERIVERY AND TSW.CD_PRODUCT = MDP.CD_PRODUCT)
WHERE TSW.DT_SHIPSLIP = TO_DATE('&1', 'yyyy/mm/dd')
AND (('&2' = '') OR (TSW.CD_PULPER = ('&2' ::text) ::numeric))
AND (('&3' = '') OR (TSW.CD_GRADE_PRODUCT = ('&3' ::text) ::numeric))
AND TSW.FLG_DELETE = 0
AND MSIP.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MSIP.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MSIP.FLG_DELETE = 0
AND MW.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MW.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MW.FLG_DELETE = 0
AND MC.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MC.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MC.FLG_DELETE = 0
AND MD.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MD.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MD.FLG_DELETE = 0
AND MP.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MP.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MP.FLG_DELETE = 0
AND MDP.DT_START <= TO_DATE('&1', 'yyyy/mm/dd')
AND MDP.DT_END >= TO_DATE('&1', 'yyyy/mm/dd')
AND MDP.FLG_DELETE = 0
ORDER BY TM_SHIP, TSW.CD_PULPER, TSW.NO_BATCH , TSW.CD_GRADE , TSW.NO_LINE


--****************************************************************
--* パルパー別の勤務者名を取得　　(1日分)
--*　早番：(7：00以降)　遅番：(15：00以降)　深夜番：(23：00以降)
--****************************************************************
[lselectRecords4]
SELECT
	 CASE WHEN coalesce(H.NAME_STAFF, '') = '' THEN '' ELSE H.NAME_STAFF END HAYABAN
	,CASE WHEN coalesce(O.NAME_STAFF, '') = '' THEN '' ELSE O.NAME_STAFF END OSOBAN
	,CASE WHEN coalesce(S.NAME_STAFF, '') = '' THEN '' ELSE S.NAME_STAFF END SINYABAN
FROM (
	SELECT 'A' ::text C1
) m
LEFT OUTER JOIN (
	SELECT
		 'A' ::text C1
		,coalesce(TSW.CD_STAFF, 0) CD_STAFF
		,NAME_STAFF
	FROM T_SHIP_WP TSW , M_STAFF MS
	WHERE TM_SHIP = (
		SELECT MIN(TM_SHIP)
		FROM T_SHIP_WP
		WHERE TM_SHIP >= ('&3' ::timestamp)
		AND TM_SHIP <= ('&4' ::timestamp)
		AND CD_PULPER = ('&14' ::text) ::numeric
		AND FLG_DELETE = 0)
	AND TSW.DT_SHIPSLIP = TO_DATE('&1', 'yyyy/mm/dd')
	AND TSW.CD_PULPER = ('&14' ::text) ::numeric
	AND TSW.FLG_DELETE = 0
	AND TSW.CD_STAFF = MS.CD_STAFF
	GROUP BY TSW.CD_STAFF , NAME_STAFF
) h ON (M.C1 = H.C1)
LEFT OUTER JOIN (
	SELECT
		 'A' ::text C1
		,coalesce(TSW.CD_STAFF, 0) CD_STAFF
		,NAME_STAFF
	FROM T_SHIP_WP TSW , M_STAFF MS
	WHERE TM_SHIP = (
		SELECT MIN(TM_SHIP)
		FROM T_SHIP_WP
		WHERE TM_SHIP >= ('&5' ::timestamp)
		AND TM_SHIP <= ('&6' ::timestamp)
		AND CD_PULPER = ('&14' ::text) ::numeric
		AND FLG_DELETE = 0)
	AND TSW.DT_SHIPSLIP = TO_DATE('&1', 'yyyy/mm/dd')
	AND TSW.CD_PULPER = ('&14' ::text) ::numeric
	AND TSW.FLG_DELETE = 0
	AND TSW.CD_STAFF = MS.CD_STAFF
	GROUP BY TSW.CD_STAFF , NAME_STAFF
) o ON (M.C1 = O.C1)
LEFT OUTER JOIN (
	SELECT
		 'A' ::text C1
		,coalesce(TSW.CD_STAFF, 0) CD_STAFF
		,NAME_STAFF
	FROM T_SHIP_WP TSW , M_STAFF MS
	WHERE TM_SHIP = (
		SELECT MIN(TM_SHIP)
		FROM T_SHIP_WP
		WHERE TM_SHIP >= ('&7' ::timestamp)
		AND TM_SHIP <= ('&8' ::timestamp)
		AND CD_PULPER = ('&14' ::text) ::numeric
		AND FLG_DELETE = 0)
	AND TSW.DT_SHIPSLIP >= TO_DATE('&1', 'yyyy/mm/dd')
	AND TSW.DT_SHIPSLIP <= TO_DATE('&13', 'yyyy/mm/dd')
	AND TSW.CD_PULPER = ('&14' ::text) ::numeric
	AND TSW.FLG_DELETE = 0
	AND TSW.CD_STAFF = MS.CD_STAFF
	GROUP BY TSW.CD_STAFF , NAME_STAFF
) s ON (M.C1 = S.C1)