[load_grdList]
select
  w.res_no
, w.furikikan
, h09.han_name as furikikan_nm
, w.nyukin_ymd
--#28025 2023.05.10 MOD STT
--, w.nyukin_gaku
, coalesce(w.total_gaku,0) - (coalesce(w.tesuryo,0) + coalesce(w.cyosei_gaku,0)) as nyukin_gaku
--#28025 2023.05.10 MOD END
, coalesce(w.tesuryo,0) + coalesce(w.cyosei_gaku,0) as tesuryo
, w.total_gaku
, w.moto_bank
, w.moto_shiten
, w.furikomi_nm
, w.keshi_gaku
, coalesce(w.total_gaku,0) - coalesce(w.keshi_gaku,0) as keshi_zan
, w.del_flg
, (case when(w.del_flg = '1')then('削除')when(w.nyukin_no > 0)then('手動')else('')end) as reg_type
, w.nyukin_no
, w.tok_cd
from (
--取込分
select
  t.res_no
, t.furikikan
, t.nyukin_ymd
--#28025 2023.05.10 MOD STT
--, t.nyukin_gaku
--#28025 2023.05.10 MOD END
, t.tesuryo
, t.cyosei_gaku
, t.total_gaku
, t.moto_bank
, t.moto_shiten
, t.furikomi_nm
, (
	select coalesce(sum(k.nyukin_gaku),0)
	from t_nyukin h
	inner join t_nyu_keshi k on (k.nyukin_no = h.nyukin_no)
	where h.res_no = t.res_no
	and h.del_flg = 0
) as keshi_gaku
, t.del_flg
, null as nyukin_no
, t.tok_cd
from t_nyukin_res t
left join m_tokui mt on (mt.tok_cd = t.tok_cd)
where 1 = 1
and (('' = ':furikikan') or (t.furikikan = ':furikikan'))
and (('' = ':syori_ymd_fr') or (t.nyukin_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (t.nyukin_ymd <= ':syori_ymd_to'))
and (('' = ':eig_cd') or (coalesce(t.tok_cd,'') = '') or (mt.eig_cd = ':eig_cd'))
and ((':view_deleted' = '1') or (t.del_flg = 0))
--手動分
union all
select
  null as res_no
, t.furikikan
, t.nyukin_ymd
--#28025 2023.05.10 MOD STT
--, t.nyukin_gaku
--, t.tesuryo
--, t.cyosei_gaku
, (
	select sum(m.nyukin_gaku)
	from t_nyukin_m m
	inner join m_hanyo h13 on (h13.mst_kbn = '13' and h13.han_cd = m.nyukin_kbn)
	where m.nyukin_no = t.nyukin_no
	and h13.koumoku2 = 'tesuryo'
) as tesuryo
, (
	select sum(m.nyukin_gaku)
	from t_nyukin_m m
	inner join m_hanyo h13 on (h13.mst_kbn = '13' and h13.han_cd = m.nyukin_kbn)
	where m.nyukin_no = t.nyukin_no
	and h13.koumoku2 = 'cyosei_gaku'
) as cyosei_gaku
--#28025 2023.05.10 MOD END
, t.total_gaku
, null as moto_bank
, null as moto_shiten
, t.tok_nm as furikomi_nm
, (
	select coalesce(sum(k.nyukin_gaku),0)
	from t_nyu_keshi k
	where k.nyukin_no = t.nyukin_no
) as keshi_gaku
, t.del_flg
, t.nyukin_no
, t.tok_cd
from t_nyukin t
where 1 = 1
and t.res_no is null
and (('' = ':furikikan') or (t.furikikan = ':furikikan'))
and (('' = ':syori_ymd_fr') or (t.nyukin_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (t.nyukin_ymd <= ':syori_ymd_to'))
and (('' = ':eig_cd') or (t.eig_cd = ':eig_cd'))

--and ((':view_deleted' = '1') or (t.del_flg = 0))

) w
left join m_hanyo h09 on (h09.mst_kbn = '09' and h09.han_cd = w.furikikan)
where 1 = 1
and ((':cond_keshi' != '0') or ((coalesce(w.total_gaku,0) - coalesce(w.keshi_gaku,0)) != 0))
and ((':cond_keshi' != '1') or ((coalesce(w.total_gaku,0) - coalesce(w.keshi_gaku,0)) = 0))
order by nyukin_ymd, res_no




[load_grdList_m]
select
  w.tok_cd
, mt.tok_nm
, w.uri_no as seikyu_no
, w.seikyu_ymd
--, w.nyukin_yotei_ymd
, '' as nyukin_yotei_ymd
, w.zeikomi_gaku as kon_seikyu_gaku
, w.nyukin_gaku as keshi_gaku
, w.kon_keshi_zan as keshi_zan
, w.keshi_gaku as kon_keshi_gaku
, w.keshi_kind as keshi_kbn
, w.uri_kbn
from fnc_query_nyu_keshi_target('{
  "tok_cd":":w_tok_cd"
, "furikomi_nm":":w_furikomi_nm"
, "res_no":":w_res_no"
, "nyukin_no":":w_nyukin_no"
, "sei_plan":":sei_plan"
, "select_miseikyu":"0"
, "select_jucyu":"0"
, "keshi_sumi_flg":":keshi_sumi_flg"
, "eig_cd":":eig_cd"
}') w
left join m_tokui mt on (mt.tok_cd = w.tok_cd)
where 1 = 1
--and ((:keshi_sumi_flg = 1) or (w.kon_keshi_gaku != 0) or ((coalesce(w.kon_seikyu_gaku,0) - coalesce(w.keshi_gaku,0)) != 0))
order by tok_cd, seikyu_ymd desc, keshi_kbn desc, seikyu_no desc

[_load_grdList_m]
select
  w.tok_cd
, mt.tok_nm
, w.seikyu_no
, w.seikyu_ymd
, w.nyukin_yotei_ymd
, w.kon_seikyu_gaku
, coalesce(w.keshi_gaku,0) - coalesce(w.kon_keshi_gaku,0) as keshi_gaku
, coalesce(w.kon_seikyu_gaku,0) - coalesce(w.keshi_gaku,0) as keshi_zan
, w.kon_keshi_gaku
, w.keshi_kbn
from (

--締め請求
select
  uk.tok_cd
, uk.seikyu_ymd
, uk.seikyu_no
, uk.nyukin_yotei_ymd
, uk.zeikomi_gaku as kon_seikyu_gaku
, (
	select coalesce(sum(k.nyukin_gaku),0)
	from t_nyu_keshi k
	where k.keshi_kind = 0
	and k.del_flg = 0
	and k.tok_cd = uk.tok_cd
	and k.seikyu_ymd = uk.seikyu_ymd
) as keshi_gaku
, (
	select sum(k.nyukin_gaku)
	from t_nyu_keshi k
	inner join t_nyukin h on (h.nyukin_no = k.nyukin_no)
	where k.keshi_kind = 0
	and k.del_flg = 0
	and k.tok_cd = uk.tok_cd
	and k.seikyu_ymd = uk.seikyu_ymd
	and ((h.res_no = :w_res_no) or (h.nyukin_no = :w_nyukin_no))
) as kon_keshi_gaku
, 0 as keshi_kbn
from t_urikake uk
where 1 = 1
and :sei_plan = 1
and uk.del_flg = 0
and uk.tok_cd in (
select m.tok_cd
from m_tokui_nyukin m
where m.del_flg = 0
and m.furikomi_nm = fnc_translate_case(':w_furikomi_nm')
union all
select ':w_tok_cd' as tok_cd
)
and uk.zeikomi_gaku != 0

--売上伝票
union all
select
  uh.tok_cd
, uh.seikyu_ymd
, uh.den_no as seikyu_no
, uk.nyukin_yotei_ymd
, uh.zeikomi_gaku as kon_seikyu_gaku
, (
	select coalesce(sum(k.nyukin_gaku),0)
	from t_nyu_keshi k
	where k.keshi_kind = 1
	and k.del_flg = 0
	and k.uri_no = uh.den_no
) as keshi_gaku
, (
	select sum(k.nyukin_gaku)
	from t_nyu_keshi k
	inner join t_nyukin h on (h.nyukin_no = k.nyukin_no)
	where k.keshi_kind = 1
	and k.del_flg = 0
	and k.uri_no = uh.den_no
	and ((h.res_no = :w_res_no) or (h.nyukin_no = :w_nyukin_no))
) as kon_keshi_gaku
, 1 as keshi_kbn
from t_uri uh
left join m_tokui mt on (mt.tok_cd = uh.tok_cd)
left join t_urikake uk on (uk.tok_cd = mt.sei_cd and uk.seikyu_ymd = uh.seikyu_ymd)
where 1 = 1
and :sei_plan = 0
and uh.del_flg = 0
and uh.jucyu_flg = 0
and coalesce(uh.mitsumori_flg,0) = 0
--and uh.seikyu_flg = 1 --TODO
and uh.tok_cd in (
select m.tok_cd
from m_tokui_nyukin m
where m.del_flg = 0
and m.furikomi_nm = fnc_translate_case(':w_furikomi_nm')
union all
select ':w_tok_cd' as tok_cd
)
and uh.zeikomi_gaku != 0



) w
left join m_tokui mt on (mt.tok_cd = w.tok_cd)
where 1 = 1
and ((:keshi_sumi_flg = 1) or (w.kon_keshi_gaku != 0) or ((coalesce(w.kon_seikyu_gaku,0) - coalesce(w.keshi_gaku,0)) != 0))

order by tok_cd, seikyu_ymd desc, seikyu_no desc


[updateKobetsu]
select fnc_update_basv0730_kobetsu(:s_id, :w_res_no, :w_keshi_kbn, :w_den_no, ':w_tok_cd', ':w_seikyu_ymd', :w_keshi_gaku, :w_tesuryo, ':login_id')
[updateKobetsuManual]
select fnc_update_basv0730_kobetsu_manual(:s_id, :w_nyukin_no, :w_keshi_kbn, :w_den_no, ':w_tok_cd', ':w_seikyu_ymd', :w_keshi_gaku, :w_tesuryo, ':login_id')

[cancelKobetsu]
select fnc_update_basv0730_cancel(:s_id, :res_no, ':login_id')
[cancelKobetsuManual]
select fnc_update_basv0730_cancel_manual(:s_id, :nyukin_no, ':login_id')

[deleteRes]
select fnc_update_basv0730_delete(:s_id, :res_no, ':login_id')
[deleteNyukin]
select fnc_delete_basv0230(:nyukin_no, ':login_id')

[updateImportWork]
delete from w_nyukin_res_import_h where s_id = :s_id;
insert into w_nyukin_res_import_h (
  s_id
, furikikan
, eig_cd
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, ':furikikan'
, ':eig_cd'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
);








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