[load_grdList]
select
  w.hin_cd
, w.hin_nm
, w.hin_rnm
, w.jan_cd
, w.brand_cd
, w.brand_nm
, w.htanka
, w.tanka
, w.now_stock
, w.cat_cd
, w.cat_nm
, w.htanka_f2
, w.tanka_f2
, w.nyotei_su
, w.syotei_su
, COALESCE(w.now_stock,0) + COALESCE(w.nyotei_su,0) - COALESCE(w.syotei_su,0) as yotei_stock
, w.hacyu_irai_su
from (
SELECT
  m.hin_cd
, m.hin_nm
, m.hin_rnm
, m.jan_cd
, m.brand_cd
, h1.han_name as brand_nm
, m.htanka
, m.tanka
, st.now_stock
, m.cat_cd
, h2.han_name as cat_nm
, m.htanka as htanka_f2
, m.tanka as tanka_f2
, (
	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 = m.hin_cd
	and ((':nyotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyotei_ymd'))
) as nyotei_su
,(
	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 = m.hin_cd
		and ((':syotei_ymd' = '') or (um.nonyu_ymd <= ':syotei_ymd'))
		--支給残
		union all
--		select (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) * ms.set_su as syotei_su
		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 = m.hin_cd
		and ((':nyotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyotei_ymd'))
		and COALESCE(m.non_stock_flg,0) = 0
	) w
) as syotei_su
, m.hacyu_irai_su
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
LEFT JOIN m_hanyo h1 on (h1.mst_kbn = '06' and h1.han_cd = m.brand_cd)
LEFT JOIN m_hanyo h2 on (h2.mst_kbn = '05' and h2.han_cd = m.cat_cd)
LEFT JOIN m_sethin ohin on (ohin.hin_cd = m.hin_cd and row_no = 0)
WHERE 1 = 1
AND ((m.hin_cd LIKE ':hin_cd%' or m.jan_cd LIKE ':hin_cd%' or m.jan_cd LIKE '%/ :hin_cd%' or m.jan_cd LIKE '%/:hin_cd%') or (':hin_cd' = ''))
AND ((m.hin_nm LIKE '%:hin_nm%' or m.hin_rnm LIKE '%:hin_nm%') or (':hin_nm' = ''))
AND ((m.brand_cd = ':brand_cd') or (':brand_cd' = ''))
AND ((h2.han_cd is not null and h2.koumoku1 = ':dcat_cd') or (':dcat_cd' = ''))
AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
AND ((':st_cond' != '1') or (st.now_stock > 0))
and m.del_flg = 0
) w
where ((':yotei_cond' != '1') or ((COALESCE(w.now_stock,0) + COALESCE(w.nyotei_su,0) - COALESCE(w.syotei_su,0)) < 0))
order by brand_cd, hin_cd

[load_grdList_sir]
select
  w.sir_cd
, w.sir_nm
, w.hacyu_zan
, w.nyuka_yotei_ymd
from (
select
  m.sir_cd
, m.sir_rnm as sir_nm
, (
	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)
	inner join m_hin hin on (um.hin_cd = hin.hin_cd)
	where uh.sir_cd = m.sir_cd
	and uh.del_flg = 0
	and uh.hacyu_flg = 1
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
) as hacyu_zan
, (
	select min(um.nyuka_yotei_ymd)
	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)
	where uh.sir_cd = m.sir_cd
	and uh.del_flg = 0
	and uh.hacyu_flg = 1
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
) as nyuka_yotei_ymd
from m_sir m
where m.del_flg = 0
) w
where w.hacyu_zan != 0
order by sir_cd

[load_grdList_zan]
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
, uh.biko1
, um.meisai_biko as biko2 --明細行の備考欄
, hin.jan_cd
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)
WHERE 1 = 1
and uh.del_flg = 0
and uh.hacyu_flg = 1
and uh.sir_cd = ':w_sir_cd'
and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
order by um.nyuka_yotei_ymd, uh.syori_ymd, um.den_no, um.row_no

[changeHacyuIraiSu]
update m_hin set
  hacyu_irai_su = :w_hacyu_irai_su
, upd_id = ':login_id'
, upd_time = now()
, hacyu_irai_ymd =  to_char( now() ,'yyyymmdd')
, hacyu_irai_tan_cd = ':login_id'
where hin_cd = ':w_hin_cd'




[exportList]
SELECT
  m.hin_cd
, m.sethin_id
, m.hin_nm
, m.hin_rnm
, m.jan_cd
, m.brand_cd
, h1.han_name as brand_nm
, m.htanka
, m.tanka
, COALESCE(st.now_stock,0) + (case when(':hin-renkei' = '1')then(COALESCE(other_stock,0))else(0)end) as now_stock
, m.htanka + fnc_get_zei(m.htanka,to_char(now(),'yyyymmdd')) as htanka_komi
, vs.stanka1
, vs.stanka2
, vs.stanka3
, vs.stanka4
, vs.stanka5
, vu.utanka1
, vu.utanka2
, vu.utanka3
, vu.utanka4
, vu.utanka5
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
left join v_hin_stanka_rireki vs on (vs.hin_cd = m.hin_cd)
left join v_hin_utanka_rireki vu on (vu.hin_cd = m.hin_cd and vu.tok_cd = ':tok_cd')
LEFT JOIN m_hanyo h1 on (h1.mst_kbn = '06' and h1.han_cd = m.brand_cd)
LEFT JOIN m_hanyo h2 on (h2.mst_kbn = '05' and h2.han_cd = m.cat_cd)
WHERE 1 = 1
--AND ((m.jan_cd LIKE '%:jan_cd' or m.jan_cd LIKE '%:jan_cd /%' or m.jan_cd LIKE '%:jan_cd/%') or (':jan_cd' = ''))
AND ((m.jan_cd LIKE '%:jan_cd%') or (':jan_cd' = ''))
--AND ((m.hin_cd LIKE '%:hin_cd' or m.jan_cd LIKE '%:hin_cd' or m.jan_cd LIKE '%:hin_cd /%' or m.jan_cd LIKE '%:hin_cd/%') or (':hin_cd' = ''))
AND ((m.hin_cd LIKE '%:hin_cd%' or m.jan_cd LIKE '%:hin_cd%') or (':hin_cd' = ''))
AND ((m.hin_nm LIKE '%:hin_nm%' or m.hin_rnm LIKE '%:hin_nm%') or (':hin_nm' = ''))
AND ((m.brand_cd = ':brand_cd') or (':brand_cd' = ''))
AND ((h2.han_cd is not null and h2.koumoku1 = ':dcat_cd') or (':dcat_cd' = ''))
AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
AND ((m.price_kbn = ':price_kbn') or (':price_kbn' = ''))
AND ((m.hin_sts = ':hin_sts') or (':hin_sts' = ''))
--AND ((':st_cond' != '1') or (st.now_stock > 0))
AND ((':st_cond' != '1') or ((COALESCE(st.now_stock,0) + (case when(':hin-renkei' = '1')then(COALESCE(other_stock,0))else(0)end)) > 0))
AND ((':stop_cond' != '0') or (m.stop_flg = 0))
AND ((':stop_cond' != '1') or (m.stop_flg = 1))
AND ((':inner_cond' != '0') or (m.inner_flg = 0))
AND ((':inner_cond' != '1') or (m.inner_flg = 1))
and m.del_flg = 0
--and m.inner_flg = 0
order by brand_cd, hin_cd

[exportList_komori]
SELECT
  m.hin_cd
, m.hin_nm
--, shin.tanka
--, sir.sir_nm
, m.jan_cd
, m.brand_cd
, h1.han_name as brand_nm
, m.htanka
, COALESCE(st.now_stock,0) + (case when('' = '1')then(COALESCE(other_stock,0))else(0)end) as now_stock
, m.htanka + fnc_get_zei(m.htanka,to_char(now(),'yyyymmdd')) as htanka_komi
, chin.hin_nm as chin_nm
, chin.cat_cd
, csir.sir_cd as csir_cd, csir.sir_nm as csir_nm
, h3.han_name as cat_nm
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
--LEFT JOIN m_sirhin shin on shin.hin_cd = m.hin_cd
--LEFT JOIN m_sir sir on shin.sir_cd = sir.sir_cd
LEFT JOIN m_sethin sethin on sethin.hin_cd = m.hin_cd
LEFT JOIN m_hin chin on sethin.chin_cd = chin.hin_cd
LEFT JOIN m_sirhin cshin on chin.hin_cd = cshin.hin_cd
LEFT JOIN m_sir csir on cshin.sir_cd = csir.sir_cd
LEFT JOIN m_hanyo h1 on (h1.mst_kbn = '06' and h1.han_cd = m.brand_cd)
LEFT JOIN m_hanyo h2 on (h2.mst_kbn = '05' and h2.han_cd = m.cat_cd)
LEFT JOIN m_hanyo h3 on (h2.mst_kbn = '05' and h2.han_cd = chin.cat_cd)
WHERE 1 = 1
--AND ((m.jan_cd LIKE '%:jan_cd' or m.jan_cd LIKE '%:jan_cd /%' or m.jan_cd LIKE '%:jan_cd/%') or (':jan_cd' = ''))
AND ((m.jan_cd LIKE '%:jan_cd%') or (':jan_cd' = ''))
--AND ((m.hin_cd LIKE '%:hin_cd' or m.jan_cd LIKE '%:hin_cd' or m.jan_cd LIKE '%:hin_cd /%' or m.jan_cd LIKE '%:hin_cd/%') or (':hin_cd' = ''))
AND ((m.hin_cd LIKE '%:hin_cd%' or m.jan_cd LIKE '%:hin_cd%') or (':hin_cd' = ''))
AND ((m.hin_nm LIKE '%:hin_nm%' or m.hin_rnm LIKE '%:hin_nm%') or (':hin_nm' = ''))
AND ((m.brand_cd = ':brand_cd') or (':brand_cd' = ''))
AND ((h2.han_cd is not null and h2.koumoku1 = ':dcat_cd') or (':dcat_cd' = ''))
AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
AND ((m.price_kbn = ':price_kbn') or (':price_kbn' = ''))
AND ((m.hin_sts = ':hin_sts') or (':hin_sts' = ''))
--AND ((':st_cond' != '1') or (st.now_stock > 0))
AND ((':st_cond' != '1') or ((COALESCE(st.now_stock,0) + (case when(':hin-renkei' = '1')then(COALESCE(other_stock,0))else(0)end)) > 0))
AND ((':stop_cond' != '0') or (m.stop_flg = 0))
AND ((':stop_cond' != '1') or (m.stop_flg = 1))
AND ((':inner_cond' != '0') or (m.inner_flg = 0))
AND ((':inner_cond' != '1') or (m.inner_flg = 1))
and m.del_flg = 0
--and m.inner_flg = 0
--order by brand_cd, hin_cd
order by chin.cat_cd

[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())