[loadOriginal]
select fnc_load_basv0240(:s_id, :syukin_no)

[updateOriginal]
select fnc_update_basv0240(:s_id)

[doDelete]
select fnc_delete_basv0240(:syukin_no, ':login_id')

[deleteOriginal]
delete from w_basv0240 where s_id = :s_id;
delete from w_basv0240_m where s_id = :s_id;
delete from w_basv0240_keshi where s_id = :s_id;

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

[getSirDen]
--sir_cd", "sir_nm", "keiyaku_gaku", "hacyu_gaku", "sir_gaku", "kon_sir_gaku", "hacyu_zan", "keshi_gaku_tsudo
select
  w.sir_cd
, w.sir_nm
, w.zeikomi_gaku as keiyaku_gaku
, (case when(w.hacyu_flg = 1)then(w.zeikomi_gaku)else(null)end) as hacyu_gaku
--常に表示したら？STT
--, (case when(w.hacyu_flg = 1)then(w.sir_gaku)else(null)end) as sir_gaku
, (case when(w.hacyu_flg = 1)then(COALESCE(w.sir_gaku,0))else(w.zeikomi_gaku)end) as sir_gaku
--常に表示したら？END
, COALESCE(w.syu_gaku,0) as keshi_gaku_tsudo
--未使用
, (case when(w.hacyu_flg = 1)then(w.sir_gaku2)else(null)end) as kon_sir_gaku
, (case when(w.hacyu_flg = 1)then(COALESCE(w.zeikomi_gaku,0) - COALESCE(w.sir_gaku1,0) - COALESCE(w.sir_gaku2,0))else(null)end) as hacyu_zan
, ms.eig_cd as msir_eig_cd

from (
select
  uh.sir_cd
, uh.sir_nm
--TODO 発注に税込額が無い STT
--, uh.zeikomi_gaku
, (case when(uh.hacyu_flg = 1)then(uh.zeinuki_gaku + fnc_get_zei(uh.zeinuki_gaku,uh.syori_ymd))else(uh.zeikomi_gaku)end) as zeikomi_gaku
--TODO 発注に税込額が無い END
, uh.hacyu_flg
, uri.sir_gaku
, uri.sir_gaku1
, uri.sir_gaku2
, nk.syu_gaku
, nk.syu_gaku1
, nk.syu_gaku2
--#25012 2022.07.28 MOD STT
--from v_hacyu_no v
from v_hacyu_no_seikyu v
--#25012 2022.07.28 MOD END
inner join t_sir uh on (uh.den_no = v.keiyaku_no)
inner join t_sir uh2 on (uh2.den_no = v.den_no)
left join (
	select v.keiyaku_no
	, sum(nk.zeikomi_gaku) as sir_gaku
	, sum((case when(nk.den_no != :sir_no)then(nk.zeikomi_gaku)else(0)end)) as sir_gaku1
	, sum((case when(nk.den_no = :sir_no)then(nk.zeikomi_gaku)else(0)end)) as sir_gaku2
	from t_sir nk
--#25012 2022.07.28 MOD STT
--	inner join v_hacyu_no v on (v.den_no = nk.den_no)
	inner join v_hacyu_no_seikyu v on (v.den_no = nk.den_no)
--#25012 2022.07.28 MOD END
	where nk.hacyu_flg = 0
--#25012 2022.07.28 MOD STT
--	and nk.hacyu_no in (select keiyaku_no from v_hacyu_no where den_no = :sir_no)
	and nk.hacyu_no in (select keiyaku_no from v_hacyu_no_seikyu where den_no = :sir_no)
--#25012 2022.07.28 MOD END
	group by v.keiyaku_no
) uri on (uri.keiyaku_no = v.keiyaku_no)
left join (
	select v.keiyaku_no, sum(nk.syukin_gaku) as keshi_gaku
	, sum(nk.syukin_gaku) as syu_gaku
	, sum(case when(jh.hacyu_flg = 1 and nk.sir_no = v.keiyaku_no)then(nk.syukin_gaku)else(0)end) as syu_gaku1
	, sum(case when(jh.hacyu_flg = 1 and nk.sir_no = v.keiyaku_no)then(0)else(nk.syukin_gaku)end) as syu_gaku2
	from t_syu_keshi nk
--#25012 2022.07.28 MOD STT
--	inner join v_hacyu_no v on (v.den_no = nk.sir_no)
	inner join v_hacyu_no_seikyu v on (v.den_no = nk.sir_no)
--#25012 2022.07.28 MOD END
	inner join t_sir jh on (jh.den_no = v.keiyaku_no)
--#25012 2022.07.28 MOD STT
--	where v.keiyaku_no in (select keiyaku_no from v_hacyu_no where den_no = :sir_no)
	where v.keiyaku_no in (select keiyaku_no from v_hacyu_no_seikyu where den_no = :sir_no)
--#25012 2022.07.28 MOD END
	and nk.syukin_no != fnc_to_number(':w_syukin_no')
	group by v.keiyaku_no
) nk on (nk.keiyaku_no = v.keiyaku_no)
where v.den_no = :sir_no
) w
left join m_sir ms on (ms.sir_cd = w.sir_cd)
where 1 = 1

[load_grdList_den]
select nk.sir_no, nk.syukin_no, n.syukin_ymd, n.total_gaku
, uh.hacyu_flg
, (case when(uh.hacyu_flg = 1)then('発注')else('仕入')end) as ha_sir
from t_syu_keshi nk
inner join t_syukin n on (n.syukin_no = nk.syukin_no)
--#25012 2022.07.28 MOD STT
--inner join v_hacyu_no v on (v.den_no = nk.sir_no)
inner join v_hacyu_no_seikyu v on (v.den_no = nk.sir_no)
--#25012 2022.07.28 MOD END
inner join t_sir uh on (uh.den_no = nk.sir_no)
--#25012 2022.07.28 MOD STT
--where v.keiyaku_no in (select keiyaku_no from v_hacyu_no where den_no = :sir_no)
where v.keiyaku_no in (select keiyaku_no from v_hacyu_no_seikyu where den_no = :sir_no)
--#25012 2022.07.28 MOD END
and nk.syukin_no != fnc_to_number(':w_syukin_no')
order by syukin_no

[checkAutoRegisted]
select nk.keshi_no
from t_syu_keshi nk
inner join t_sir uh on (uh.den_no = nk.sir_no)
where nk.syukin_no = :syukin_no
--TODO
--and uh.cash_flg = 1
and 1 = 0

[getSirSeiYmd]
select seikyu_ymd from m_sir where sir_cd = ':sir_cd'

[updateInitKbn]
delete from w_basv0240_m
where s_id = :s_id
;
insert into w_basv0240_m (
	s_id,
	row_no,
	syukin_kbn,
	kbn_nm,
	syukin_gaku,
	sort_key,
	futan_flg,
	del_flg,
	cre_id,
	cre_time,
	upd_id,
	upd_time
)
select
  :s_id
, row_number() over(order by h19.sort_key, h19.han_cd) -1 as row_no
, han_cd --syukin_kbn
, han_name --kbn_nm
, 0 --syukin_gaku
, h19.sort_key
, h19.koumoku3 --futan_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_hanyo h19
where h19.mst_kbn = '19'
and h19.del_flg = 0

[deleteKeshiWork]
DELETE FROM w_basv0240_keshi
WHERE 1=1 
AND s_id = :s_id

[insertKeshiWork]
INSERT INTO w_basv0240_keshi(
  s_id
, sir_no
, keshi_kind
, den_no
, select_flg
, syori_ymd
, zeikomi_gaku
, syukin_gaku
, keshi_zan
, keshi_gaku
, kon_keshi_zan
, biko1
, biko2
, sir_cd
, seikyu_ymd
, sir_kbn
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
--仕入伝票
select
  :s_id
, w.sir_no
, 1 as keshi_kind
, w.sir_no as den_no
, (case when(w.keshi_gaku != 0)then(1)else(0)end) --select_flg
, w.syori_ymd
, w.zeikomi_gaku
, w.syukin_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.syukin_gaku,0) --keshi_zan
, w.keshi_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.syukin_gaku,0) - COALESCE(w.keshi_gaku,0) --kon_keshi_zan
, trim(COALESCE(w.biko1,'')||' '||COALESCE(w.biko2,'')) as biko1
--, w.biko2
, '' as biko2
, w.sir_cd
, w.seikyu_ymd
, '仕入' as sir_kbn
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from(
select
	  tu.den_no as sir_no
--	, -1 as keshi_no --keshi_no
--	, case when coalesce(tu.seikyu_ymd,'')  = '' then 1 else 0 end as keshi_kind
--	, coalesce(tu.keshi_flg,0) as select_flg
	, tu.syori_ymd
--	, tn.syukin_no
--#24961 2022.07.23 MOD STT
--	, tu.zeikomi_gaku
--#25038 2022.07.29 MOD STT
--	, (case when(kk.zei_calc_kbn = '1')then(tu.zeinuki_gaku)else(tu.zeikomi_gaku)end) as zeikomi_gaku
	, (case when(kk.zei_calc_kbn = '1' or (coalesce(tu.seikyu_flg,0) = 0 and ms.zei_calc_kbn = '1'))then(tu.zeinuki_gaku)else(tu.zeikomi_gaku)end) as zeikomi_gaku
--#25038 2022.07.29 MOD END
--#24961 2022.07.23 MOD END
	, tu.biko1
	, tu.biko2
--	, 0 as mul_den_flg --mul_den_flg
	, tu.sir_cd
	, tu.seikyu_ymd
	, (select sum(tnk.syukin_gaku) from t_syu_keshi tnk where tnk.sir_no = tu.den_no and tnk.syukin_no != fnc_to_number(':syukin_no')) as syukin_gaku
	, (select sum(tnk.syukin_gaku) from t_syu_keshi tnk where tnk.sir_no = tu.den_no and tnk.syukin_no = fnc_to_number(':syukin_no')) as keshi_gaku
from t_sir tu
--#20438 2021.08.25 MOD STT
inner join m_sir ms on (ms.sir_cd = tu.sir_cd)
--#20438 2021.08.25 MOD END
--left join t_syu_keshi tnk on (tnk.sir_no = tu.den_no)
--left join t_syukin tn on (tn.syukin_no = tnk.syukin_no)
--#20457 2021.08.28 MOD STT
left join t_kaikake kk on (kk.sir_cd = tu.sir_cd and kk.seikyu_ymd = tu.seikyu_ymd)
--#20457 2021.08.28 MOD END
where 1 = 1
and tu.del_flg = 0
--and coalesce(tu.henpin_flg,0) = 0
--and coalesce(tu.mitsumori_flg,0) = 0
and coalesce(tu.hacyu_flg,0) = 0
--#20458 2021.08.26 MOD STT
--and coalesce(tu.seikyu_flg,0) = 1
and ((':select_miseikyu' = '1') or (coalesce(tu.seikyu_flg,0) = 1))
--#20458 2021.08.26 MOD END
and tu.sir_cd = ':sir_cd'
--#20438 2021.08.25 MOD STT
--#24961 2022.07.23 MOD STT
--and ms.zei_calc_kbn = '0'
--#24961 2022.07.23 MOD END
--#20438 2021.08.25 MOD END
--#20457 2021.08.28 MOD STT
--#24961 2022.07.23 MOD STT
--and ((tu.seikyu_flg = 0) or (COALESCE(kk.zei_calc_kbn,'') != '1'))
--#24961 2022.07.23 MOD END
--#20457 2021.08.28 MOD END
--and ((tn.syukin_no = fnc_to_number(':syukin_no')) or ('' = ':syukin_no'))
--order by tu.syori_ymd
) w
--where (w.syukin_no = fnc_to_number(':syukin_no')) or(w.select_flg = '0')
where 1 = 1
and COALESCE(w.zeikomi_gaku,0) != COALESCE(w.syukin_gaku,0)

union all
--支払締
select
  :s_id
, cast(null as int) as sir_no
--#24961 2022.07.23 MOD STT
--, 0 as keshi_kind
, 2 as keshi_kind
--#24961 2022.07.23 MOD END
, row_number() over() as den_no
, (case when(w.keshi_gaku != 0)then(1)else(0)end) --select_flg
, w.seikyu_ymd as syori_ymd
, w.zeikomi_gaku
, w.syukin_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.syukin_gaku,0) --keshi_zan
, w.keshi_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.syukin_gaku,0) - COALESCE(w.keshi_gaku,0) --kon_keshi_zan
, '' as biko1
, '' as biko2
, w.sir_cd
, w.seikyu_ymd
--#24961 2022.07.23 MOD STT
--, '締め' as sir_kbn
, '消費税' as sir_kbn
--#24961 2022.07.23 MOD END
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from(
select
--#24961 2022.07.23 MOD STT
--	  tu.zeikomi_gaku
	  tu.zei_gaku as zeikomi_gaku
--#24961 2022.07.23 MOD END
	, tu.sir_cd
	, tu.seikyu_ymd
--#24961 2022.07.23 MOD STT
--	, (select sum(tnk.syukin_gaku) from t_syu_keshi tnk where tnk.keshi_kind = 0 and tnk.sir_cd = tu.sir_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.syukin_no != fnc_to_number(':syukin_no')) as syukin_gaku
--	, (select sum(tnk.syukin_gaku) from t_syu_keshi tnk where tnk.keshi_kind = 0 and tnk.sir_cd = tu.sir_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.syukin_no = fnc_to_number(':syukin_no')) as keshi_gaku
	, (select sum(tnk.syukin_gaku) from t_syu_keshi tnk where tnk.keshi_kind = 2 and tnk.sir_cd = tu.sir_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.syukin_no != fnc_to_number(':syukin_no')) as syukin_gaku
	, (select sum(tnk.syukin_gaku) from t_syu_keshi tnk where tnk.keshi_kind = 2 and tnk.sir_cd = tu.sir_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.syukin_no = fnc_to_number(':syukin_no')) as keshi_gaku
--#24961 2022.07.23 MOD END
from t_kaikake tu
inner join m_sir ms on (ms.sir_cd = tu.sir_cd)
where 1 = 1
and tu.del_flg = 0
and tu.sir_cd = ':sir_cd'
--#25311 2022.08.19 MOD STT
--and ms.zei_calc_kbn = '1'
--#25311 2022.08.19 MOD END
--#20457 2021.08.28 MOD STT
and tu.zei_calc_kbn = '1'
--#20457 2021.08.28 MOD END
) w
where 1 = 1
and COALESCE(w.zeikomi_gaku,0) != COALESCE(w.syukin_gaku,0)




order by syori_ymd, sir_no