[changeStock]
select fnc_update_basv0610(':hin_cd', ':loc_no', ':chg_stock', ':login_id')

[changeStock_ss]
select fnc_update_basv0610_ss(':hin_cd', ':loc_no', ':chg_stock', ':chg_loc_no', ':login_id')

[updateTana]
select fnc_update_basv0610_tana(:s_id)

[load_grdList]
select
  w.hin_cd
, w.hin_nm
, w.hin_rnm
, w.tanka
, w.stanka
, w.htanka
, w.hin_loc_no
, w.loc_no
, w.now_stock
, w.now_ztanka
, w.goukei_kingaku
, w.hin_sts_nm
, w.cat_nm
, w.loc_nm
, w.han_loc_nm
, w.jucyu_zan
, w.hacyu_zan
, w.yotei_stock
, w.tani
, w.iri_su
, w.iri_tani
from (
select
  m.hin_cd
, m.hin_nm
, m.hin_rnm
, m.tanka
, m.stanka
, m.htanka
, m.loc_no as hin_loc_no
, st.loc_no
, st.now_stock
, case when(coalesce(st.now_ztanka,0) = 0)then(m.stanka)else(st.now_ztanka)end as now_ztanka
, round(st.now_stock * (case when(':none-ztan' = '1' or coalesce(st.now_ztanka,0) = 0)then(m.stanka)else(st.now_ztanka)end), 2) as goukei_kingaku
, h1.han_name as hin_sts_nm
, h2.han_name as cat_nm
--#9495 2019.11.04 MOD STT
--#20370 2021.08.20 MOD STT
--, COALESCE(h3.han_name,(case when(st.loc_no != '')then('未登録 '||st.loc_no)else('')end)) as loc_nm
, (case when(h99a.han_name = '1')then( st.loc_no )else( COALESCE(h3.han_name,(case when(st.loc_no != '')then('未登録 '||st.loc_no)else('')end)) )end) as loc_nm
--#20370 2021.08.20 MOD END
--#9495 2019.11.04 MOD END
--#12940 2020.05.18 MOD STT
, h4.han_name as han_loc_nm
--#12940 2020.05.18 MOD END
, stt.jucyu_su as jucyu_zan
, stt.hacyu_su as hacyu_zan
, COALESCE(st.now_stock,0) - COALESCE(stt.jucyu_su,0) + (case when(':hacyu_taisyo_flg' = '1')then(COALESCE(stt.hacyu_su,0))else(0)end) as yotei_stock
, m.tani
, m.iri_su
, m.iri_tani
from m_hin m
left join (
	select
	  st.hin_cd
	, st.now_ztanka
	, st.loc_no
	, st.now_stock
	from v_stock_loc st
	where ':zaiko-ss' = '0'
	union all
	select
	  sm.hin_cd
	, sm.tanka as now_ztanka
	, COALESCE(sm.loc_no,'') as loc_no
	, sum(COALESCE(sm.suryo * (case when(mh.iri_su > 1)then(mh.iri_su)else(1)end),0) - COALESCE(sm.syuko_su,0)) as now_stock
	from t_sir sh
	inner join t_sir_m sm on (sm.den_no = sh.den_no)
	left join m_hin mh on (mh.hin_cd = sm.hin_cd)
	where sh.hacyu_flg = 0
	and (COALESCE(sm.suryo * (case when(mh.iri_su > 1)then(mh.iri_su)else(1)end),0) - COALESCE(sm.syuko_su,0)) > 0
	and ':zaiko-ss' = '1'
	group by 
	  sm.hin_cd
	, sm.tanka
	, COALESCE(sm.loc_no,'')
) st on (st.hin_cd = m.hin_cd)
left join m_hanyo h1 on (h1.mst_kbn = '04' and h1.han_cd = m.hin_sts)
left join m_hanyo h2 on (h2.mst_kbn = '05' and h2.han_cd = m.cat_cd)
left join v_hanyo_all h3 on (h3.mst_kbn = 'loc' and h3.han_cd = st.loc_no)
left join m_hanyo h4 on (h4.mst_kbn = '17' and h4.han_cd = st.loc_no)
--#22186 2021.12.21 MOD STT
--left join m_tok_hin th on (th.hin_cd = m.hin_cd)
--#22186 2021.12.21 MOD END
left join m_stock stt on (stt.hin_cd = m.hin_cd)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'sir-locknr') --入荷管理番号での場所管理
WHERE 1 = 1
AND ((m.jan_cd LIKE '%:jan_cd') or (':jan_cd' = ''))
--AND ((m.hin_cd LIKE '%:hin_cd') or (':hin_cd' = ''))
and (upper(replace(replace(replace(m.hin_cd,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:hin_cd%',' ',''),'-',''),'　','')) or (':hin_cd' = ''))
AND ((m.hin_nm LIKE '%:hin_nm%') or (':hin_nm' = ''))
:multi_cond_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.loc_no is not null and st.loc_no = ':loc_no') or (':loc_no' = ''))
--AND ((h4.han_cd = ':han_loc_cd') or (':han_loc_cd' = ''))
AND ((':st_cond' != '1') or (st.now_stock != 0))
--#22186 2021.12.21 MOD STT
--AND ((th.tok_cd = ':tok_cd') or (':tok_cd' = ''))
AND ((':tok_cd' = '') or exists (select 1 from m_tok_hin th where th.hin_cd = m.hin_cd and th.tok_cd = ':tok_cd'))
--#22186 2021.12.21 MOD END
AND m.del_flg = 0
AND m.non_stock_flg = 0
AND ((fnc_translate_case(m.loc_no) like '%:hin_loc_no%') or ('' = ':hin_loc_no'))
) w
where 1 = 1
AND ((':minus_flg' = '0') or (w.yotei_stock < 0))
--order by brand_cd, hin_cd
order by w.hin_cd

[CheckHinCd]
select hin_cd as chk_hin_cd from m_hin where hin_cd = ':hin_cd'

[updateTanka]
update m_stock set 
	now_ztanka = :now_ztanka
	, now_zkingaku = :now_zkingaku
where hin_cd = ':hin_cd'
;

[CheckTanaYmd]
select syori_ymd as chk_tana_ymd from t_tana where syori_ymd = ':tana_ymd'

[deleteWork_tana]
delete from w_basv0610_tana;
delete from w_basv0610_tana_m;

[insertWork_tana]
insert into w_basv0610_tana (
	s_id
	, den_no
	, input_ymd
	, syori_ymd
	, tan_cd
	, biko1
	, biko2
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
	)
	values (
	:s_id
	, fnc_get_saiban_with_update(9,':login_id')
	, to_char(now(),'YYYYMMDD')
	, ':tana_ymd'
	, ':login_id'
	, '在庫照会_棚卸取込'
	, ''
	, 0
	, ':login_id'
	, now()
	, ':login_id'
	, now()
	);


[insertWork_tana_m]
insert into w_basv0610_tana_m (
	s_id
	, loc_no
	, hin_cd
	, now_stock
	, suryo
	, tanka
	, kingaku
	, meisai_biko
	, meisai_biko2
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
	)
	values(
		:s_id
		, ':loc_no'
		, ':hin_cd'
		, :now_stock
		, ':chg_stock'
		, :now_ztanka
		, :now_zkingaku
		, ''
		, ''
		, 0
		, ':login_id'
		, now()
		, ':login_id'
		, now()
	);

[getNowZKingaku]
select now_ztanka , now_zkingaku from m_stock where hin_cd = ':hin_cd';

[getNowStock]
select now_stock from m_stock where hin_cd = ':hin_cd';