[loadOriginal]
select fnc_load_basv0520(:s_id, ':hin_cd', ':login_id')

[deleteHacyuWork]
delete from w_basv0220_load_m where s_id = :s_id

[insertHacyuWork]
insert into w_basv0220_load_m (s_id, row_no, hin_cd, hin_nm, suryo, hacyu_su, del_flg, cre_id, cre_time, upd_id, upd_time)
values (:s_id, 0, ':hin_cd', null, null, :hacyu_su, 0, ':login_id', now(), ':login_id', now())

[load_grdList]
select
  ww.lv
, ww.row_no
, ww.hin_cd
, ww.hin_nm
, ww.koumoku1
, ww.now_ztanka as stanka
, ww.now_stock
, coalesce(ww.j_count,0) as j_count
, coalesce(ww.s_count,0) as s_count
, coalesce(ww.now_stock,0) + coalesce(ww.s_count,0) - coalesce(ww.j_count,0) as new_stock
, ww.nyotei_ymd as ymd
, ww.den_no
, ww.regist_su
--, sh.sir_nm
, ms.sir_rnm as sir_nm
from (
select
  w.view_level as lv
, w.sort_key as row_no
, w.hin_cd
, repeat('　',(w.view_level - 1))||m.hin_nm as hin_nm
, mh.han_name as koumoku1
, m.stanka
, st.now_stock
, st.now_ztanka
, (
	select sum(COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0))
	from t_sir uh
	inner join t_sir_m um on (um.den_no = uh.den_no)
	WHERE 1 = 1
	and uh.hacyu_flg = 1
	and uh.del_flg = 0
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
	and um.hin_cd = w.hin_cd
	and ((':nyuka_yotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyuka_yotei_ymd'))
) as s_count
, (
	select sum(w.syotei_su)
	from (
		--受注残
		select (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) as syotei_su
		from t_uri uh
		inner join t_uri_m um on (um.den_no = uh.den_no)
		WHERE 1 = 1
		and uh.jucyu_flg = 1
		and uh.del_flg = 0
		and (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) > 0
		and um.hin_cd = w.hin_cd
		and ((':nonyu_ymd' = '') or (um.nonyu_ymd <= ':nonyu_ymd'))
		--支給残
		union all
		select ceil((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) / COALESCE(mh.set_iri_su,1)) * ms.set_su as syotei_su
		from t_sir uh
		inner join t_sir_m um on (um.den_no = uh.den_no)
		inner join m_hin mh on (mh.hin_cd = um.hin_cd)
		inner join m_sethin ms on (ms.hin_cd = um.hin_cd)
		WHERE 1 = 1
		and uh.hacyu_flg = 1
		and uh.del_flg = 0
		and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
		and ms.chin_cd = w.hin_cd
		and ((':nyuka_yotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyuka_yotei_ymd'))
		and COALESCE(m.non_stock_flg,0) = 0
	) w
) as j_count
, (
	select um.nyuka_yotei_ymd
	from t_sir uh
	inner join t_sir_m um on (um.den_no = uh.den_no)
	WHERE 1 = 1
	and uh.hacyu_flg = 1
	and uh.del_flg = 0
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
	and um.hin_cd = w.hin_cd
	and ((':nyuka_yotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyuka_yotei_ymd'))
	order by um.nyuka_yotei_ymd, um.den_no
	limit 1
) as nyotei_ymd
, (
	select um.den_no
	from t_sir uh
	inner join t_sir_m um on (um.den_no = uh.den_no)
	WHERE 1 = 1
	and uh.hacyu_flg = 1
	and uh.del_flg = 0
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
	and um.hin_cd = w.hin_cd
	and ((':nyuka_yotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyuka_yotei_ymd'))
	order by um.nyuka_yotei_ymd, um.den_no
	limit 1
) as den_no
, (
	select count(*)
	from t_sir uh
	inner join t_sir_m um on (um.den_no = uh.den_no)
	WHERE 1 = 1
	and uh.hacyu_flg = 1
	and uh.del_flg = 0
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
	and um.hin_cd = w.hin_cd
	and ((':nyuka_yotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyuka_yotei_ymd'))
) as regist_su
from w_basv0520 w
inner join m_hin m on (m.hin_cd = w.hin_cd)
left join m_stock st on (st.hin_cd = w.hin_cd)
left join m_hanyo mh on (mh.mst_kbn = '04' and mh.han_cd = m.hin_sts)
where w.s_id = :s_id
) ww
left join t_sir sh on (sh.den_no = ww.den_no)
left join m_sir ms on (ms.sir_cd = sh.sir_cd)
order by row_no




[load_grdList2]
select
  um.den_no
, um.row_no
, um.hin_cd
, um.hin_nm
, (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) as hacyu_zan
, um.tanka as tankaf2
, round((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) * um.tanka, 0) as kingaku
, uh.syori_ymd
, um.nyuka_yotei_ymd as nyuka_yotei_ymd
, uh.sir_cd
--, uh.sir_nm
, ms.sir_rnm as sir_nm
, uh.biko1
, um.meisai_biko as biko2 --明細行の備考欄
from t_sir uh
inner join t_sir_m um on (um.den_no = uh.den_no)
inner join m_hin hin on (um.hin_cd = hin.hin_cd)
left join m_sir ms on (ms.sir_cd = uh.sir_cd)
WHERE 1 = 1
and uh.hacyu_flg = 1
and uh.del_flg = 0
and um.hin_cd = ':hin_cd'
and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
and (('' = ':nyuka_yotei_ymd') or (um.nyuka_yotei_ymd <= ':nyuka_yotei_ymd'))
order by um.nyuka_yotei_ymd, uh.syori_ymd, um.den_no, um.row_no