[loadOriginal]
select fnc_load_basv0230(:s_id, :nyukin_no)

[updateOriginal]
select fnc_update_basv0230(:s_id)

[doDelete]
select fnc_delete_basv0230(:nyukin_no, ':login_id')

[deleteOriginal]
delete from w_basv0230 where s_id = :s_id;
delete from w_basv0230_m where s_id = :s_id;
delete from w_basv0230_keshi where s_id = :s_id;

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

[getUriDen]
--tok_cd", "tok_nm", "keiyaku_gaku", "jucyu_gaku", "uri_gaku", "kon_uri_gaku", "jucyu_zan", "keshi_gaku_tsudo
select
  w.tok_cd
, w.tok_nm
, w.zeikomi_gaku as keiyaku_gaku
, (case when(w.jucyu_flg = 1)then(w.zeikomi_gaku)else(null)end) as jucyu_gaku
--常に表示したら？STT
--, (case when(w.jucyu_flg = 1)then(w.uri_gaku)else(null)end) as uri_gaku
, (case when(w.jucyu_flg = 1)then(COALESCE(w.uri_gaku,0))else(w.zeikomi_gaku)end) as uri_gaku
--常に表示したら？END
, COALESCE(w.nyu_gaku,0) as keshi_gaku_tsudo
--未使用
, (case when(w.jucyu_flg = 1)then(w.uri_gaku2)else(null)end) as kon_uri_gaku
, (case when(w.jucyu_flg = 1)then(COALESCE(w.zeikomi_gaku,0) - COALESCE(w.uri_gaku1,0) - COALESCE(w.uri_gaku2,0))else(null)end) as jucyu_zan
, mt.eig_cd as mtok_eig_cd

from (
select
  uh.tok_cd
, uh.tok_nm
--#19614 2021.07.06 MOD STT
--, uh.zeikomi_gaku
, (case when(uh.gaika_rate > 0)then(round(uh.gaika_rate * uh.zeikomi_gaku,0))else(uh.zeikomi_gaku)end) as zeikomi_gaku
--#19614 2021.07.06 MOD END
, uh.jucyu_flg
, uri.uri_gaku
, uri.uri_gaku1
, uri.uri_gaku2
, nk.nyu_gaku
, nk.nyu_gaku1
, nk.nyu_gaku2
from v_jucyu_no v
inner join t_uri uh on (uh.den_no = v.keiyaku_no)
inner join t_uri uh2 on (uh2.den_no = v.den_no)
left join (
	select v.keiyaku_no
--#19614 2021.07.06 MOD STT
--	, sum(nk.zeikomi_gaku) as uri_gaku
--	, sum((case when(nk.den_no != :uri_no)then(nk.zeikomi_gaku)else(0)end)) as uri_gaku1
--	, sum((case when(nk.den_no = :uri_no)then(nk.zeikomi_gaku)else(0)end)) as uri_gaku2
	, sum(case when(nk.gaika_rate > 0)then(round(nk.gaika_rate * nk.zeikomi_gaku,0))else(nk.zeikomi_gaku)end) as uri_gaku
	, sum((case when(nk.den_no != :uri_no)then(case when(nk.gaika_rate > 0)then(round(nk.gaika_rate * nk.zeikomi_gaku,0))else(nk.zeikomi_gaku)end)else(0)end)) as uri_gaku1
	, sum((case when(nk.den_no = :uri_no)then(case when(nk.gaika_rate > 0)then(round(nk.gaika_rate * nk.zeikomi_gaku,0))else(nk.zeikomi_gaku)end)else(0)end)) as uri_gaku2
--#19614 2021.07.06 MOD END
	from t_uri nk
	inner join v_jucyu_no v on (v.den_no = nk.den_no)
	where nk.jucyu_flg = 0
	and nk.jucyu_no in (select keiyaku_no from v_jucyu_no where den_no = :uri_no)
	group by v.keiyaku_no
) uri on (uri.keiyaku_no = v.keiyaku_no)
left join (
	select v.keiyaku_no, sum(nk.nyukin_gaku) as keshi_gaku
	, sum(nk.nyukin_gaku) as nyu_gaku
	, sum(case when(jh.jucyu_flg = 1 and nk.uri_no = v.keiyaku_no)then(nk.nyukin_gaku)else(0)end) as nyu_gaku1
	, sum(case when(jh.jucyu_flg = 1 and nk.uri_no = v.keiyaku_no)then(0)else(nk.nyukin_gaku)end) as nyu_gaku2
	from t_nyu_keshi nk
	inner join v_jucyu_no v on (v.den_no = nk.uri_no)
	inner join t_uri jh on (jh.den_no = v.keiyaku_no)
	where v.keiyaku_no in (select keiyaku_no from v_jucyu_no where den_no = :uri_no)
	and nk.nyukin_no != fnc_to_number(':w_nyukin_no')
	group by v.keiyaku_no
) nk on (nk.keiyaku_no = v.keiyaku_no)
where v.den_no = :uri_no
) w
left join m_tokui mt on (mt.tok_cd = w.tok_cd)
where 1 = 1

[load_grdList_den]
select nk.uri_no, nk.nyukin_no, n.nyukin_ymd, n.total_gaku
, uh.jucyu_flg
, (case when(uh.jucyu_flg = 1)then('受注')else('売上')end) as ju_uri
from t_nyu_keshi nk
inner join t_nyukin n on (n.nyukin_no = nk.nyukin_no)
inner join v_jucyu_no v on (v.den_no = nk.uri_no)
inner join t_uri uh on (uh.den_no = nk.uri_no)
where v.keiyaku_no in (select keiyaku_no from v_jucyu_no where den_no = :uri_no)
and nk.nyukin_no != fnc_to_number(':w_nyukin_no')
order by nyukin_no

[checkAutoRegisted]
select nk.keshi_no
from t_nyu_keshi nk
inner join t_uri uh on (uh.den_no = nk.uri_no)
where nk.nyukin_no = :nyukin_no
and uh.cash_flg = 1

[getTokSeiYmd]
select seikyu_ymd from m_tokui where tok_cd = ':tok_cd'

[updateInitKbn]
delete from w_basv0230_m
where s_id = :s_id
;
insert into w_basv0230_m (
	s_id,
	row_no,
	nyukin_kbn,
	kbn_nm,
	nyukin_gaku,
	sort_key,
	del_flg,
	cre_id,
	cre_time,
	upd_id,
	upd_time
)
select
  :s_id
, row_number() over(order by h13.sort_key, h13.han_cd) -1 as row_no
, han_cd --nyukin_kbn
, han_name --kbn_nm
, 0 --nyukin_gaku
, h13.sort_key
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_hanyo h13
where h13.mst_kbn = '13'
and h13.del_flg = 0

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

[insertKeshiWork]
INSERT INTO w_basv0230_keshi(
  s_id
, uri_no
, keshi_kind
, den_no
, select_flg
, syori_ymd
, zeikomi_gaku
, nyukin_gaku
, keshi_zan
, keshi_gaku
, kon_keshi_zan
, biko1
, biko2
, tok_cd
, seikyu_ymd
, uri_kbn
, tok_nm
--#22447 2022.01.06 MOD STT
, nonyu_nm
--#22447 2022.01.06 MOD END
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, w.uri_no
, w.keshi_kind
, coalesce(w.uri_no, row_number() over()) --den_no
, (case when(w.keshi_gaku != 0)then(1)else(0)end) --select_flg
, w.syori_ymd
, w.zeikomi_gaku
, w.nyukin_gaku
, w.keshi_zan
, w.keshi_gaku
, w.kon_keshi_zan
, trim(coalesce(w.biko1,'')||' '||coalesce(w.biko2,'')) --biko1
, '' --biko2
, w.tok_cd
, w.seikyu_ymd
, w.uri_kbn
, w.tok_nm
, w.nonyu_nm
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from fnc_query_nyu_keshi_target('{
  "tok_cd":":tok_cd"
, "nyukin_no":":nyukin_no"
, "sei_plan":":sei_plan"
, "select_miseikyu":":select_miseikyu"
, "select_jucyu":":select_jucyu"
}') w
where 1 = 1

order by syori_ymd, uri_no



[_insertKeshiWork]
INSERT INTO w_basv0230_keshi(
  s_id
, uri_no
, keshi_kind
, den_no
, select_flg
, syori_ymd
, zeikomi_gaku
, nyukin_gaku
, keshi_zan
, keshi_gaku
, kon_keshi_zan
, biko1
, biko2
, tok_cd
, seikyu_ymd
, uri_kbn
, tok_nm
--#22447 2022.01.06 MOD STT
, nonyu_nm
--#22447 2022.01.06 MOD END
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, w.uri_no
, 1 as keshi_kind
, w.uri_no as den_no
, (case when(w.keshi_gaku != 0)then(1)else(0)end) --select_flg
, w.syori_ymd
, w.zeikomi_gaku
--#20253 2021.08.10 MOD STT
--, w.nyukin_gaku
, COALESCE(w.nyukin_gaku,0) + COALESCE(w.jucyu_nyukin_gaku,0) as nyukin_gaku
--#20253 2021.08.10 MOD END
--#20253 2021.08.10 MOD STT
--, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) --keshi_zan
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) - COALESCE(w.jucyu_nyukin_gaku,0) --keshi_zan
--#20253 2021.08.10 MOD END
, w.keshi_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) - COALESCE(w.jucyu_nyukin_gaku,0) - COALESCE(w.keshi_gaku,0) --kon_keshi_zan
, trim(coalesce(w.biko1,'')||' '||coalesce(w.biko2,'')) as biko1
--, w.biko2
, '' as biko2
, w.tok_cd
, w.seikyu_ymd
, w.uri_kbn
, w.tok_nm
--#22447 2022.01.06 MOD STT
, w.nonyu_nm
--#22447 2022.01.06 MOD END
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from(
select
	  tu.den_no as uri_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.nyukin_no
--#22444 2022.01.06 MOD STT
--	, tu.zeikomi_gaku
--#25038 2022.07.29 MOD STT
--	, (case when(uk.zei_calc_kbn = '1')then(tu.zeinuki_gaku)else(tu.zeikomi_gaku)end) as zeikomi_gaku
	, (case when(uk.zei_calc_kbn = '1' or (coalesce(tu.seikyu_flg,0) = 0 and mt.zei_calc_kbn = '1'))then(tu.zeinuki_gaku)else(tu.zeikomi_gaku)end) as zeikomi_gaku
--#25038 2022.07.29 MOD END
--#22444 2022.01.06 MOD END
	, tu.biko1
	, tu.biko2
--	, 0 as mul_den_flg --mul_den_flg
	, tu.tok_cd
	, tu.seikyu_ymd
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.uri_no = tu.den_no and tnk.nyukin_no != fnc_to_number(':nyukin_no')) as nyukin_gaku
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.uri_no = tu.den_no and tnk.nyukin_no = fnc_to_number(':nyukin_no')) as keshi_gaku
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.uri_no = tu.jucyu_no) as jucyu_nyukin_gaku
	, '売上' as uri_kbn
	, tu.tok_nm
--#22447 2022.01.06 MOD STT
	, tu.nonyu_nm
--#22447 2022.01.06 MOD END
from t_uri tu
--left join t_nyu_keshi tnk on (tnk.uri_no = tu.den_no)
--left join t_nyukin tn on (tn.nyukin_no = tnk.nyukin_no)
--#18922 2021.06.17 MOD STT
inner join m_tokui mt on (mt.tok_cd = tu.tok_cd)
--#18922 2021.06.17 MOD END
--#20457 2021.08.28 MOD STT
--#24748 2022.07.07 MOD STT
--left join t_urikake uk on (uk.tok_cd = tu.tok_cd and uk.seikyu_ymd = tu.seikyu_ymd)
left join t_urikake uk on (uk.tok_cd = ':tok_cd' and uk.seikyu_ymd = tu.seikyu_ymd)
--#24748 2022.07.07 MOD END
--#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.jucyu_flg,0) = 0
--#19674 2021.08.10 MOD STT
--and coalesce(tu.seikyu_flg,0) = 1
and ((':select_miseikyu' = '1') or (coalesce(tu.seikyu_flg,0) = 1))
--#19674 2021.08.10 MOD END
--#18922 2021.06.17 MOD STT
--and tu.tok_cd = ':tok_cd'
--#22446 2022.01.06 MOD STT
and mt.sei_cd = ':tok_cd'
--and ((mt.sei_cd = ':tok_cd') or (mt.group_cd != '' and mt.group_cd = ':tok_cd'))
--#22446 2022.01.06 MOD END
--#18922 2021.06.17 MOD END
--and ((tn.nyukin_no = fnc_to_number(':nyukin_no')) or ('' = ':nyukin_no'))
--order by tu.syori_ymd
--#18812 2021.04.18 MOD STT
--個別請求を除く
--#20482 2021.08.29 MOD STT
--and coalesce(tu.kobetsu_kbn,'') = ''
and ((':select_miseikyu' = '1') or (coalesce(tu.kobetsu_kbn,'') = ''))
--#20482 2021.08.29 MOD END
--#18812 2021.04.18 MOD END
--#20464 2021.08.27 MOD STT
--#22444 2022.01.06 MOD STT
--and mt.zei_calc_kbn = '0'
--#22444 2022.01.06 MOD END
--#20464 2021.08.27 MOD END
--#20457 2021.08.28 MOD STT
--#22444 2022.01.06 MOD STT
--and ((tu.seikyu_flg = 0) or (COALESCE(uk.zei_calc_kbn,'') != '1'))
--#22444 2022.01.06 MOD END
--#20457 2021.08.28 MOD END
--#27456 2023.03.13 MOD STT
and ':sei_plan' = '0'
--#27456 2023.03.13 MOD END

--#19674 2021.08.10 MOD STT
union all
select
	  tu.den_no as uri_no
	, tu.syori_ymd
	, tu.zeikomi_gaku
	, tu.biko1
	, tu.biko2
	, tu.tok_cd
	, tu.seikyu_ymd
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.uri_no = tu.den_no and tnk.nyukin_no != fnc_to_number(':nyukin_no')) as nyukin_gaku
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.uri_no = tu.den_no and tnk.nyukin_no = fnc_to_number(':nyukin_no')) as keshi_gaku
	, 0 as jucyu_nyukin_gaku
	, '受注' as uri_kbn
	, tu.tok_nm
--#22447 2022.01.06 MOD STT
	, tu.nonyu_nm
--#22447 2022.01.06 MOD END
from t_uri tu
--#18922 2021.06.17 MOD STT
inner join m_tokui mt on (mt.tok_cd = tu.tok_cd)
--#18922 2021.06.17 MOD END
where 1 = 1
and tu.del_flg = 0
and tu.jucyu_flg = 1
and coalesce(tu.mitsumori_flg,0) = 0
--and coalesce(tu.seikyu_flg,0) = 1
and mt.sei_cd = ':tok_cd'
--個別請求を除く
--and coalesce(tu.kobetsu_kbn,'') = ''
--受注表示
and ':select_jucyu' = '1'
--出荷残があるもの
--#20252 2021.08.10 MOD STT
--and exists (select 1 from t_uri_m tum where tum.den_no = tu.den_no and (COALESCE(tum.jucyu_su,0) - COALESCE(tum.uri_su,0)) != 0)
and (exists (select 1 from t_uri_m tum where tum.den_no = tu.den_no and (COALESCE(tum.jucyu_su,0) - COALESCE(tum.uri_su,0)) != 0)
  or exists (select 1 from t_nyu_keshi tnk where tnk.uri_no = tu.den_no and tnk.nyukin_no = fnc_to_number(':nyukin_no'))
)
--#20252 2021.08.10 MOD END
--#19674 2021.08.10 MOD END
--#20464 2021.08.27 MOD STT
and mt.zei_calc_kbn = '0'
--#20464 2021.08.27 MOD END
--#27456 2023.03.13 MOD STT
and ':sei_plan' = '0'
--#27456 2023.03.13 MOD END

) w
--where (w.nyukin_no = fnc_to_number(':nyukin_no')) or(w.select_flg = '0')
where 1 = 1
--#20253 2021.08.10 MOD STT
--and COALESCE(w.zeikomi_gaku,0) != COALESCE(w.nyukin_gaku,0)
and COALESCE(w.zeikomi_gaku,0) != (COALESCE(w.nyukin_gaku,0) + COALESCE(w.jucyu_nyukin_gaku,0))
--#20253 2021.08.10 MOD END

--#20464 2021.08.27 MOD STT
union all
--請求締
select
  :s_id
, cast(null as int) as uri_no
--#22444 2022.01.06 MOD STT
--, 0 as keshi_kind
, 2 as keshi_kind
--#22444 2022.01.06 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.nyukin_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) --keshi_zan
, w.keshi_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) - COALESCE(w.keshi_gaku,0) --kon_keshi_zan
, '' as biko1
, '' as biko2
, w.tok_cd
, w.seikyu_ymd
--#22444 2022.01.06 MOD STT
--, '締め' as uri_kbn
, '消費税' as uri_kbn
--#22444 2022.01.06 MOD END
, w.tok_nm
--#22447 2022.01.06 MOD STT
, '' as nonyu_nm
--#22447 2022.01.06 MOD END
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from(
select
--#22444 2022.01.06 MOD STT
--	  tu.zeikomi_gaku
	  tu.zei_gaku as zeikomi_gaku
--#22444 2022.01.06 MOD END
	, tu.tok_cd
	, tu.seikyu_ymd
--#22444 2022.01.06 MOD STT
--	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.keshi_kind = 0 and tnk.tok_cd = tu.tok_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.nyukin_no != fnc_to_number(':nyukin_no')) as nyukin_gaku
--	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.keshi_kind = 0 and tnk.tok_cd = tu.tok_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.nyukin_no = fnc_to_number(':nyukin_no')) as keshi_gaku
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.keshi_kind = 2 and tnk.tok_cd = tu.tok_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.nyukin_no != fnc_to_number(':nyukin_no')) as nyukin_gaku
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.keshi_kind = 2 and tnk.tok_cd = tu.tok_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.nyukin_no = fnc_to_number(':nyukin_no')) as keshi_gaku
--#22444 2022.01.06 MOD END
	, mt.tok_nm
from t_urikake tu
inner join m_tokui mt on (mt.tok_cd = tu.tok_cd)
where 1 = 1
and tu.del_flg = 0
--#22446 2022.01.06 MOD STT
and tu.tok_cd = ':tok_cd'
--and ((mt.tok_cd = ':tok_cd') or (mt.group_cd != '' and mt.group_cd = ':tok_cd'))
--and mt.sei_cd = mt.tok_cd
--and tu.tok_cd = mt.sei_cd
--#22446 2022.01.06 MOD END
--#25311 2022.08.19 MOD STT
--and mt.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
--#27456 2023.03.13 MOD STT
and ':sei_plan' = '0'
--#27456 2023.03.13 MOD END
) w
where 1 = 1
and COALESCE(w.zeikomi_gaku,0) != COALESCE(w.nyukin_gaku,0)
--#20464 2021.08.27 MOD END

union all
--請求締め
select
  :s_id
, w.seikyu_no as uri_no
, 0 as keshi_kind
, row_number() over() as den_no --TODO
, (case when(w.keshi_gaku != 0)then(1)else(0)end) --select_flg
, w.seikyu_ymd as syori_ymd
, w.zeikomi_gaku
, w.nyukin_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) --keshi_zan
, w.keshi_gaku
, COALESCE(w.zeikomi_gaku,0) - COALESCE(w.nyukin_gaku,0) - COALESCE(w.keshi_gaku,0) --kon_keshi_zan
, '' as biko1
, '' as biko2
, w.tok_cd
, w.seikyu_ymd
, '請求' as uri_kbn
, w.tok_nm
, '' as nonyu_nm
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from(
select
	  tu.zeikomi_gaku
	, tu.tok_cd
	, tu.seikyu_ymd
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.keshi_kind = 0 and tnk.tok_cd = tu.tok_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.nyukin_no != fnc_to_number(':nyukin_no')) as nyukin_gaku
	, (select sum(tnk.nyukin_gaku) from t_nyu_keshi tnk where tnk.keshi_kind = 0 and tnk.tok_cd = tu.tok_cd and tnk.seikyu_ymd = tu.seikyu_ymd and tnk.nyukin_no = fnc_to_number(':nyukin_no')) as keshi_gaku
	, mt.tok_nm
	, tu.seikyu_no
from t_urikake tu
inner join m_tokui mt on (mt.tok_cd = tu.tok_cd)
where 1 = 1
and tu.del_flg = 0
and tu.tok_cd = ':tok_cd'
--and tu.zei_calc_kbn = '1'
and ':sei_plan' = '1'
) w
where 1 = 1
and COALESCE(w.zeikomi_gaku,0) != COALESCE(w.nyukin_gaku,0)




order by syori_ymd, uri_no