[loadOriginal]
select fnc_load_basv0640(:s_id, :den_no)

[updateOriginal]
select fnc_update_basv0640(:s_id)

[deleteOriginal]
delete from w_basv0640 where s_id = :s_id;
delete from w_basv0640_m where s_id = :s_id;

[doDelete]
select fnc_delete_basv0640(:den_no, ':login_id')

[getOtherId]
select other_id as w_other_id
from m_hin
where hin_cd = ':w_hin_cd'

[getMaxDenNo]
select
  (select max(den_no) from m_saiban where saiban_kbn = 7) as last_den_no

[loadNewKey]
select den_no as w_new_den_no
from w_basv0640
where s_id = :s_id

[loadSir]
insert into w_basv0640_m (
  s_id
, row_no
, hin_cd
, hin_nm
, suryo
, kakutei_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, sm.row_no
, sm.hin_cd
, mh.hin_rnm
, sm.suryo
, 0 --kakutei_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_sir_m sm
inner join v_m_hin mh on (mh.hin_cd = sm.hin_cd)
where sm.den_no = :sir_no
order by sm.row_no

[loadUri]
insert into w_basv0640_m (
  s_id
, row_no
, hin_cd
, hin_nm
, suryo
, kakutei_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, um.row_no
, um.hin_cd
, mh.hin_rnm
, um.suryo
, 0 --kakutei_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_uri_m um
inner join v_m_hin mh on (mh.hin_cd = um.hin_cd)
where um.den_no = :uri_no
order by um.row_no

[loadHacyu]
insert into w_basv0640_m (
  s_id
, row_no
, hin_cd
, hin_nm
, suryo
, yotei_su
, kakutei_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, row_number() over(order by min(w.row_no))
, w.chin_cd
, w.hin_nm
--, ceil((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) ) * ms.set_su as suryo
, 0 --suryo
, sum(w.suryo) --yotei_su
, 0 --kakutei_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from(
  select
  row_number() over(order by um.row_no, ms.row_no) as row_no
  , ms.chin_cd --hin_cd
  , v.hin_nm
  --, (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) * ms.set_su --suryo
--#12520 2020.02.17 MOD STT
--  ,ceil((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0))) * ms.set_su as suryo
  , ceil( (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) * ms.set_su / (case when(mh.set_iri_su != 0)then(mh.set_iri_su)else(1)end) ) as suryo
--#12520 2020.02.17 MOD END
  --, ceil((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) / COALESCE(mh.set_iri_su,1)) * ms.set_su --suryo
  
  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)
  inner join m_hin v on (v.hin_cd = ms.chin_cd)
  where uh.den_no = :hacyu_no
  and uh.hacyu_flg = 1
  and uh.del_flg = 0
  and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
  and COALESCE(v.non_stock_flg,0) = 0
 ) w
group by w.chin_cd, w.hin_nm
order by 2
;

[loadHacyu_log]
insert into w_basv0640_m (
  s_id
, row_no
, hin_cd
, hin_nm
, suryo
, kakutei_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, row_number() over(order by um.row_no, ms.row_no) --row_no
, ms.chin_cd --hin_cd
, v.hin_nm
--, (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) * ms.set_su --suryo
, ceil((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0))) * ms.set_su 
--, ceil((COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) / COALESCE(mh.set_iri_su,1)) * ms.set_su --suryo
, 0 --kakutei_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
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)
inner join m_hin v on (v.hin_cd = ms.chin_cd)
where uh.den_no = :hacyu_no
and uh.hacyu_flg = 1
and uh.del_flg = 0
and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
and COALESCE(v.non_stock_flg,0) = 0
order by um.row_no, ms.row_no

[chk_oya_hin]
select hin_cd from m_sethin
where hin_cd = ':ohin_cd'
;

[getCombo_cat_cd]
select han_cd, han_name
from m_hanyo
where mst_kbn = '05'
order by sort_key,mst_kbn, han_cd
;

[getCombo_loc_no]
select han_cd, han_name
from m_hanyo
where mst_kbn = '17'
order by sort_key,mst_kbn, han_cd
;

[getCombo_new_loc_no]
select han_cd, han_name
from m_hanyo
where mst_kbn = '17'
order by sort_key,mst_kbn, han_cd
;

[getNyukoLocMei]
select 
	sum(loc.now_stock) as new_loc_stock
from m_stock_loc loc
where loc.loc_no = ':new_loc_no'
group by loc_no
limit 1
;

[getSyukoLocMei]
select 
	sum(loc.now_stock) as now_loc_stock
from m_stock_loc loc
where loc.loc_no = ':loc_no'
group by loc_no
limit 1
;

[loadDetails]
insert into w_basv0640_m (
	s_id
	, row_no
	, hin_cd
	, w_hin_cd
	, hin_nm
	, cat_cd
	, serial_no
	, suryo
	, kakutei_flg
	, hosoku1
	, loc_no
	, new_loc_no
	, yotei_su
	, now_loc_stock
	, new_loc_stock
	, init_yotei_su
	, temp_sir_cd
	, yotei_ymd
	, print_time
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
)
select 
	:s_id
	, (row_number() over(order by w.hin_cd)) - 1 --row_no
	, mh.dhin_cd
	, w.hin_cd as w_hin_cd
	, mh.hin_nm
	, mh.cat_cd
	, w.serial_no
	, st.now_stock + st.nyuko_yotei_su
	, 0
	, ''
	, w.loc_no
	, ''
	, null
	, st.now_stock
	, null
	, null
	, ''
	, ''
	, null
	, 0
	, ':login_id'
	, now()
	, ':login_id'
	, now()
from w_basv4010_chk w
left join m_hin mh on (mh.hin_cd = w.hin_cd)
left join m_stock_loc st on (st.hin_cd = w.hin_cd and st.loc_no = w.loc_no)
where 1=1
and w.upd_id = ':login_id'
and w.tok_cd = ':cond_tok_cd'


[chk_hin_cd]
select 
	w_hin_cd
from w_basv0640_m w
where 1=1
and w.s_id = :s_id
and w.row_no = :row_no
and w.hin_cd = ':hin_cd'
and w.loc_no = ':loc_no'
;

[chk_syuko_zaiko]
select
	(ts.suryo) as moto_zaiko
from t_syuko ts
--left join m_stock_loc loc on (loc.hin_cd = 'w_hin_cd' and loc.loc_no = 'loc_no')
where 1=1
and ts.hin_cd = ':w_hin_cd'
and ts.den_no = :w_den_no
;

[chk_ido_zaiko]
select
	now_stock as moto_zaiko
from m_stock_loc
where 1=1
and hin_cd = ':w_hin_cd'
and loc_no = ':new_loc_no'
;

[chk_yotei_zaiko]
select
	(now_stock + coalesce(nyuko_yotei_su,0)) as moto_zaiko
from m_stock_loc
where 1=1
and hin_cd = ':w_hin_cd'
and loc_no = ':loc_no'
;

[chk_zaiko]
select loc.hin_cd,loc.loc_no,loc.now_stock as now_stock
from w_basv0640_m w
left join m_stock_loc loc on(loc.hin_cd = w.w_hin_cd and loc.loc_no = w.loc_no)
where 1=1
and w.s_id = :s_id
and w.row_no = :row_no
and w.w_hin_cd = ':w_hin_cd'
and loc.loc_no = ':loc_no'
and loc.now_stock + (coalesce(:moto_zaiko,0)) >= (coalesce(:suryo,0))
--and loc.now_stock > 0
;


-- select loc.hin_cd,loc.loc_no,loc.now_stock as now_stock
-- from m_stock_loc loc
-- where 1=1
-- and loc.now_stock > 0
-- and loc.now_stock >= (':suryo'::numeric(10))
-- and loc.hin_cd = ':new_hin_cd'
-- and loc.loc_no = ':loc_no'