[lsaveInsObjSlip]
insert into T_SHIP_WP (
	  NO_SHIPSLIP
	, DT_SHIPSLIP
	, DTS_YEAR
	, DTS_MONTH
	, DTS_DAY
	, TM_SHIP
	, CD_PULPER
	, NO_BATCH
	, NO_LINE
	, CD_STAFF
	, CD_CUSTOMER
	, CD_DERIVERY
	, CD_PRODUCT
	, CD_GRADE_PRODUCT
	, CD_SUPPLIER
	, CD_SHIPMENT
	, CD_WP
	, CD_GRADE
	, UNIT_PRICE_WP
	, WEIGHT_WP
	, MONEY_WP
	, FLG_LOWPLICE
	, FLG_DERIVERY_PRODUCT
	, COMMENT1
	, COMMENT2
	, FLG_DELETE
	, ID_REGIST
	, ID_UPDATE
	, ID_DELETE
	, DT_REGIST
	, DT_UPDATE
	, DT_DELETE
) values (
	  &2
	, to_date( '&3', 'yyyymmdd' )
	, &4
	, &5
	, &6
--	, &7
--	, &27
	,to_timestamp(to_char(&27,'yyyymmddhh24:mi:ss'),'yyyymmddhh24:mi:ss')
	, &8
	, &9
	, &10
	, &11
	, &12
	, &13
	, &14
	, &15
	, &16
	, &17
	, &18
	, &19
	, &20
	, &21
	, &22
	, &23
	, 0
	, '&25'
	, '&26'
	, 0
	, '&1'
	, null
	, null
	,to_timestamp(to_char(&27,'yyyymmddhh24:mi:ss'),'yyyymmddhh24:mi:ss')
--	, &27
	, null
	, null
)

[lsaveResetObjSlip]
delete from T_SHIP_WP
	where NO_SHIPSLIP = &2

[lsaveDelObjSlip]
update T_SHIP_WP set
		  FLG_DELETE = 1
		, ID_DELETE = '&1'
		, DT_DELETE = sysdate
	where NO_SHIPSLIP = &2

[lsaveInsObjHisStock]
insert into T_HIST_PROCESSSTOCK (
	  DT_SHIPSLIP
	, CD_PULPER
	, NO_BATCH
	, CD_GRADE
	, BAL_WEIGHT_STOCK
	, BAL_PRICE_STOCK
	, UNIT_PRICE_WP
	, WEIGHT_WP
	, WEIGHT_STOCK
	, MONEY_STOCK
	, UNIT_PRICE_STOCK
)
select
		  m1.DT_SHIPSLIP
		, m1.CD_PULPER
		, m1.NO_BATCH
		, m1.CD_GRADE
		, 0
		, 0
		, sum( m1.WEIGHT_WP )
		, sum( m1.MONEY_WP )
		, 0 + sum( m1.WEIGHT_WP )
		, 0 + sum( m1.MONEY_WP )
		, round( ( 0 + sum( m1.MONEY_WP ) ) / ( 0 + sum( m1.WEIGHT_WP ) ), 1 )
	from T_SHIP_WP m1
		where m1.NO_SHIPSLIP = &2
		group by 
			  m1.DT_SHIPSLIP
			, m1.CD_PULPER
			, m1.NO_BATCH
			, m1.CD_GRADE

[lsaveExecProcs]
--#24399 2022.06.17 MOD STT
--declare begin NSP_PacMain.changeShipWp( '&1', &2, &3 );end;
select changeShipWp( '&1', &2, &3 )
--#24399 2022.06.17 MOD END

[lloadObject]
select
		  m1.NO_SHIPSLIP
		, m1.DTS_YEAR
		, m1.DTS_MONTH
		, m1.DTS_DAY
		, m1.CD_PULPER
		, m1.NO_BATCH
		, m1.NO_LINE
		, m1.CD_STAFF
		, m1.CD_CUSTOMER
		, m1.CD_DERIVERY
		, m1.CD_PRODUCT
		, m1.CD_GRADE_PRODUCT
		, m1.CD_SUPPLIER
		, m1.CD_SHIPMENT
		, m1.CD_WP
		, coalesce( m1.CD_GRADE, 0 ) as CD_GRADE
		, m1.UNIT_PRICE_WP
		, m1.WEIGHT_WP
		, m1.MONEY_WP
		, coalesce( m1.FLG_LOWPLICE, 0 ) as FLG_LOWPLICE
		, m1.FLG_DERIVERY_PRODUCT
		, m1.FLG_DELETE
		, m4.NAME_SUPPLIER
		, m5.NAME_SHIPMENT
		, m6.NAME_GRADE as NAME_WP
		, m1.COMMENT1
		, m1.COMMENT2
-- k.takahashi add 修正時も投入時刻を引き継ぐ start
	    ,to_char(m1.TM_SHIP,'yyyy/mm/dd hh24:mi:ss') TM_SHIP
-- k.takahashi add 修正時も投入時刻を引き継ぐ   end
	from T_SHIP_WP m1
--#24303 2022.06.17 MOD STT
--		,M_SUPPLIER m4
--		,M_SHIPMENT m5
--		,M_WP m6
		inner join M_SUPPLIER m4 on (
		      m4.CD_SUPPLIER = m1.CD_SUPPLIER
		  and m4.DT_START <= m1.DT_SHIPSLIP
		  and m4.DT_END >= m1.DT_SHIPSLIP
		)
		inner join M_SHIPMENT m5 on (
		      m5.CD_SHIPMENT = m1.CD_SHIPMENT
		  and m5.DT_START <= m1.DT_SHIPSLIP
		  and m5.DT_END >= m1.DT_SHIPSLIP
		)
		left join M_WP m6 on (
		      m6.CD_WP = m1.CD_WP
		)
--#24303 2022.06.17 MOD END
		where m1.NO_SHIPSLIP = &1
		  and m1.FLG_DELETE = 0
--#24303 2022.06.17 MOD STT
--		  and m4.CD_SUPPLIER = m1.CD_SUPPLIER
--		  and m4.DT_START <= m1.DT_SHIPSLIP
--		  and m4.DT_END >= m1.DT_SHIPSLIP
--		  and m5.CD_SHIPMENT = m1.CD_SHIPMENT
--		  and m5.DT_START <= m1.DT_SHIPSLIP
--		  and m5.DT_END >= m1.DT_SHIPSLIP
--		  and m6.CD_WP (+)= m1.CD_WP
--#24303 2022.06.17 MOD END
		order by NO_LINE

[lloadObjects]
select
		  m1.NO_SHIPSLIP
		, to_char( m1.DT_SHIPSLIP, 'yyyy/mm/dd' ) as DT_SHIPSLIP
		, coalesce( m1.CD_PULPER, 0 ) as CD_PULPER
		, coalesce( m1.NO_BATCH, 0 ) as NO_BATCH
		, m2.NAME_STAFF
		, m3.NAME_PULPER
		, m4.NAME_DERIVERY
		, m5.NAME_PRODUCT
		, m6.NAME_GREAD
		, m7.NAME_CUSTOMER
	from T_SHIP_WP m1
--#24303 2022.06.17 MOD STT
--		,M_STAFF m2
--		,M_PULPER m3
--		,M_DERIVERY m4
--		,M_PRODUCT m5
--		,M_GREAD m6
--		,M_CUSTOMER m7
		left join M_STAFF m2 on (
		      m2.CD_STAFF = m1.CD_STAFF
		)
		left join M_PULPER m3 on (
		      m3.CD_PULPER = m1.CD_PULPER
		)
		inner join M_DERIVERY m4 on (
		      m4.CD_DERIVERY = m1.CD_DERIVERY 
		  and m4.DT_START <= m1.DT_SHIPSLIP
		  and m4.DT_END >= m1.DT_SHIPSLIP
		)
		inner join M_PRODUCT m5 on (
		      m5.CD_PRODUCT = m1.CD_PRODUCT
		  and m5.DT_START <= m1.DT_SHIPSLIP
		  and m5.DT_END >= m1.DT_SHIPSLIP
		)
		left join M_GREAD m6 on (
		      m6.CD_GRADE = m5.CD_GREAD
		)
		inner join M_CUSTOMER m7 on (
		      m7.CD_CUSTOMER = m1.CD_CUSTOMER
		  and m7.DT_START <= m1.DT_SHIPSLIP
		  and m7.DT_END >= m1.DT_SHIPSLIP
		)
--#24303 2022.06.17 MOD END
		where ( ( &1 is null ) or ( m1.DTS_YEAR  = &1 ) )
		  and ( ( &2 is null ) or ( m1.DTS_MONTH = &2 ) )
		  and ( ( &3 is null ) or ( m1.DTS_DAY   = &3 ) )
		  and ( ( &4 is null ) or ( m1.CD_STAFF  = &4 ) )
		  and ( ( &5 is null ) or ( m1.CD_PULPER = &5 ) )
		  and ( ( &6 is null ) or ( m1.NO_BATCH  = &6 ) )
		  and ( ( &7 is null ) or ( m1.CD_CUSTOMER  = &7 ) )
		  and ( ( &8 is null ) or ( m1.CD_DERIVERY = &8 ) )
		  and ( ( &9 is null ) or ( m1.CD_PRODUCT  = &9 ) )
		  and m1.FLG_DELETE = 0
--#24303 2022.06.17 MOD STT
--		  and m2.CD_STAFF (+)= m1.CD_STAFF
--		  and m3.CD_PULPER (+)= m1.CD_PULPER
--		  and m4.CD_DERIVERY = m1.CD_DERIVERY 
--		  and m4.DT_START <= m1.DT_SHIPSLIP
--		  and m4.DT_END >= m1.DT_SHIPSLIP
--		  and m5.CD_PRODUCT = m1.CD_PRODUCT
--		  and m5.DT_START <= m1.DT_SHIPSLIP
--		  and m5.DT_END >= m1.DT_SHIPSLIP
--		  and m6.CD_GRADE (+)= m5.CD_GREAD
--		  and m7.CD_CUSTOMER = m1.CD_CUSTOMER
--		  and m7.DT_START <= m1.DT_SHIPSLIP
--		  and m7.DT_END >= m1.DT_SHIPSLIP
--#24303 2022.06.17 MOD END
		group by 
			  m1.NO_SHIPSLIP
			, to_char( m1.DT_SHIPSLIP, 'yyyy/mm/dd' )
			, coalesce( m1.CD_PULPER, 0 )
			, coalesce( m1.NO_BATCH, 0 )
			, m2.NAME_STAFF
			, m3.NAME_PULPER
			, m4.NAME_DERIVERY
			, m5.NAME_PRODUCT
			, m6.NAME_GREAD
			, m7.NAME_CUSTOMER
		order by CD_PULPER, DT_SHIPSLIP desc, NO_BATCH desc

[lloadHantei]
select
		coalesce( UNIT_PRICE_PRODUCT, 0 ) - coalesce( STD_PRICE_PRODUCT, 0 ) as UNIT_PRICE
	from M_DERIVERY_PRODUCT
		where FLG_DELETE = 0
		  and CD_DERIVERY = &1
		  and CD_PRODUCT = &2
		  and DT_START   <= TO_DATE( '&3', 'yyyymmdd' )
		  and DT_END     >= TO_DATE( '&3', 'yyyymmdd' )

[lloadInsBatch]
select
		  max( coalesce( m1.NO_BATCH, 0 ) ) as NO_BATCH
	from T_SHIP_WP m1
		where m1.DT_SHIPSLIP = to_date( '&1', 'yyyymmdd' )
		  and m1.CD_PULPER = to_number( '&2' )

[lloadUpdBatch]
select
		  coalesce( m1.NO_BATCH, 0 ) as NO_BATCH
	from T_SHIP_WP m1
		where m1.DT_SHIPSLIP = to_date( '&1', 'yyyymmdd' )
		  and m1.CD_PULPER = to_number( '&2' )
		  and m1.FLG_DELETE = 0
		group by coalesce( m1.NO_BATCH, 0 )
		order by NO_BATCH

[lloadCheckRest]
select
	count(*) cnt
from
(select
	count(ts.NO_SHIPSLIP) CNT_SHIP
	from
	(select
		NO_SHIPSLIP
	from T_SHIP_WP
	where DT_SHIPSLIP = to_date('&1','yyyymmdd')
	and FLG_DELETE = '0'
	group by DT_SHIPSLIP, NO_SHIPSLIP) ts
) t1
,(select
	count(NO_PULPER_DAIRY) CNT_PUL
from T_PULPER_DAIRY
where DT_PULPER_DAIRY =  to_date('&1','yyyymmdd')) t2
where t1.CNT_SHIP <> t2.CNT_PUL

[lloadSearchObjects]
--2017.08.23 M.Inoue add
select
	sup.CD_SUPPLIER
	,sup.NAME_SUPPLIER
	,shp.CD_SHIPMENT
	,shp.NAME_SHIPMENT
	,wp.CD_WP
	,wp.NAME_GRADE
	,wp.CD_GRADE
	,gr.NAME_GREAD
from M_SHIPMENT_WP swp
inner join M_WP wp on swp.CD_WP = wp.CD_WP
inner join M_SHIPMENT shp on swp.CD_SHIPMENT = shp.CD_SHIPMENT
inner join M_SUPPLIER sup on shp.CD_SUPPLIER = sup.CD_SUPPLIER
left join M_GREAD gr on wp.CD_GRADE = gr.CD_GRADE
left join (select CD_WP, count(CD_WP) wp_cnt from T_SHIP_WP
			where DT_SHIPSLIP <= to_date('&2','yyyymmdd')
				and DT_SHIPSLIP >= add_months(to_date('&2','yyyymmdd'),-&3)
			group by cd_wp) st on wp.CD_WP = st.CD_WP
where swp.FLG_DELETE = '0'
	and wp.FLG_DELETE = '0'
	and shp.FLG_DELETE = '0'
	and sup.FLG_DELETE = '0'

	and swp.DT_START <= to_date('&2','yyyymmdd')
	and swp.DT_END >= to_date('&2','yyyymmdd')
	and wp.DT_START <= to_date('&2','yyyymmdd')
	and wp.DT_END >= to_date('&2','yyyymmdd')
	and shp.DT_START <= to_date('&2','yyyymmdd')
	and shp.DT_END >= to_date('&2','yyyymmdd')
	and sup.DT_START <= to_date('&2','yyyymmdd')
	and sup.DT_END >= to_date('&2','yyyymmdd')

	and ( ( &1 is null ) or ( sup.CD_SUPPLIER  = &1 ) )

order by coalesce(st.WP_CNT,0) desc