[getWorkKey]
select COALESCE(max(syori_seq),0) + 1 as syori_seq
from t_nyukin_ikkatsu t
where t.furikikan = ':furikikan'
and t.syori_ymd = ':syori_ymd'

[commitWork]
select fnc_update_basv0730_import(':furikikan', ':syori_ymd', :syori_seq, ':login_id');

[doKakutei]
select fnc_update_basv0730_kakutei(':furikikan', ':syori_ymd', ':login_id');

[saveLine]
update t_nyukin_ikkatsu t set
  :w_field_id = ':w_field_value'
, upd_id = ':login_id'
, upd_time = now()
where t.furikikan = ':furikikan'
and t.syori_ymd = ':syori_ymd'
and t.syori_seq = :syori_seq
and t.row_no = :row_no



[insertWork]
insert into t_nyukin_ikkatsu (
  furikikan
, syori_ymd
, syori_seq
, row_no
, nyukin_no
, tok_cd
, tok_nm
, nyukin_ymd
, nyukin_gaku
, tesuryo
, cyosei_gaku
, total_gaku
, biko
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  ':furikikan'
, ':syori_ymd'
, :syori_seq --syori_seq
, :row_no
, null --nyukin_no
, null --tok_cd
, ':tok_nm'
, ':nyukin_ymd'
, :nyukin_gaku
, null --tesuryo
, null --cyosei_gaku
, :nyukin_gaku --total_gaku
, ':tok_nm' --biko
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
)


[load_grdList]
select
  t.syori_seq
, t.row_no
, t.nyukin_no
, t.tok_cd
, t.tok_nm
, t.nyukin_ymd
, t.nyukin_gaku
, t.tesuryo
, t.cyosei_gaku
, t.total_gaku
, t.biko
, 1 as rec_flg
from t_nyukin_ikkatsu t
where t.furikikan = ':furikikan'
and t.syori_ymd = ':syori_ymd'
order by t.syori_seq desc, t.row_no




[load_grdList_]
select
  m.hin_cd
, m.hin_nm
, m.hin_rnm
, m.tanka
, m.stanka
, st.loc_no
, st.now_stock
, st.now_ztanka
, round(st.now_stock * st.now_ztanka, 2) as goukei_kingaku
, h1.han_name as hin_sts_nm
, h2.han_name as cat_nm
, h3.han_name as loc_nm
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 m_hanyo h3 on (h3.mst_kbn = '17' and h3.han_cd = st.loc_no)
left join v_hanyo_all h3 on (h3.mst_kbn = 'loc' and h3.han_cd = st.loc_no)
WHERE 1 = 1
AND ((m.jan_cd LIKE '%:jan_cd') or (':jan_cd' = ''))
AND ((m.hin_cd LIKE '%:hin_cd') or (':hin_cd' = ''))
AND ((m.hin_nm 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.loc_no is not null and st.loc_no = ':loc_no') or (':loc_no' = ''))
AND ((':st_cond' != '1') or (st.now_stock != 0))
and m.del_flg = 0
order by brand_cd, hin_cd