[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'
;