[loadOriginal]
select fnc_load_basv3010(:s_id, ':anken_cd', ':login_id')

[updateOriginal]
select fnc_update_basv3010(:s_id)

[deleteOriginal]
delete from w_basv3010 where s_id = :s_id;
delete from w_basv3010_m where s_id = :s_id;
delete from w_basv3010_op where s_id = :s_id;
delete from w_basv3010_tan where s_id = :s_id;

[doDelete]
select fnc_delete_basv3010(':anken_cd', ':login_id')

[existKey]
select 1 from t_anken
where anken_cd = ':anken_cd'

[updateInitHeader]
delete from w_basv3010
where s_id = :s_id

[updateInitKoutei]
delete from w_basv3010_m
where s_id = :s_id
;
insert into w_basv3010_m (
  s_id
, row_no
, koutei_cd
, koutei_nm
, kouki
, kouki_std
, kouki_opt
, kouki_ext
, status
, yotei_stt_time
, yotei_end_time
, stt_time
, end_time
, stop_time
, restart_time
, stop_kikan
, stt_tan_cd
, end_tan_cd
, stop_tan_cd
, restart_tan_cd
, yotei_stt_ymd
, yotei_end_ymd
, next_kikan
, kousyu_cd
, alert_kbn
, koutei_filter
, kyuka_mode
, kouki_ext2_flg
, check_kbn
, next_kikan_std
, koutei_options
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, (row_number() over(order by v.sort_key, v.koutei_cd)) - 1 --row_no
, v.koutei_cd
, v.koutei_nm
, v.kousu_iki --kouki
, v.kousu_iki --kouki_std
, 0 --kouki_opt
, 0 --kouki_ext
, 0 --status
, null --yotei_stt_time
, null --yotei_end_time
, null --stt_time
, null --end_time
, null --stop_time
, null --restart_time
, 0 --stop_kikan
, null --stt_tan_cd
, null --end_tan_cd
, null --stop_tan_cd
, null --restart_tan_cd
, null --yotei_stt_ymd
, null --yotei_end_ymd
, v.kouki_std --next_kikan
, v.kousyu_cd --kousyu_cd
, v.alert_kbn --alert_kbn
, v.koutei_filter --koutei_filter
, v.kyuka_mode --kyuka_mode
, v.kouki_ext2_flg --kouki_ext2_flg
, v.check_kbn --check_kbn
, v.kouki_std --next_kikan_std
, v.koutei_options --koutei_options
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from v_m_koutei v
;

[updateInitOp]
delete from w_basv3010_op
where s_id = :s_id
;
insert into w_basv3010_op (
  s_id
, op_cd
, op_nm
, kakutei_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, v.op_cd
, v.op_nm
, 0 --kakutei_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from v_m_op v
;

[getTanShiftDate]
select fnc_get_anken_tan_shift_date(':anken_cd') as w_tan_shift_date

[getInsertPreDate]
select to_char(m2.stt_time + cast(m2.next_kikan || ' days' as interval),'yyyymmdd') as w_end_date
from t_anken_m m1
inner join t_anken_m m2 on (m2.anken_cd = m1.anken_cd and m2.koutei_no = (m1.koutei_no - 1))
where m1.anken_cd = ':anken_cd'
and m1.koutei_cd in (select substr(h1.koumoku3,1,3) from m_hanyo h1 where h1.mst_kbn = '24' and h1.koumoku3 != '')

[getNearestTan]
select ww.tan_kind, ww.tan_cd, (case when(ww.day_i > 0)then(0)else(1)end) as sort1, abs(ww.day_i) as sort2
from (
	select w.tan_kind, w.tan_cd, to_date(':w_end_date','yyyymmdd') - cast(w.end_time as date) as day_i
	from (
		select tan.tan_kind, tan.tan_cd
			, min(m1.stt_time) as stt_time
			, max(m2.end_time) as end_time
		from t_anken_tan tan
		inner join m_hanyo h1 on (h1.mst_kbn = '24' and h1.han_cd = tan.tan_kind)
		inner join t_anken h on (tan.anken_cd = h.anken_cd)
		inner join t_anken_m m1 on (m1.anken_cd = h.anken_cd and m1.koutei_cd = substr(h1.koumoku3,1,3))
		inner join t_anken_m m2 on (m2.anken_cd = h.anken_cd and m2.koutei_cd = substr(h1.koumoku3,5,3))
		inner join m_tanto mt on (mt.tan_cd = tan.tan_cd)
		inner join m_hanyo h2 on (h2.mst_kbn = '55' and h2.han_cd = mt.bu_cd)
		where 1 = 1
		and h1.koumoku3 != ''
		and h2.koumoku1 != ''
		group by tan.tan_kind, tan.tan_cd
	) w
) ww
union all
select h1.koumoku1 as tan_kind, mt.tan_cd, 0 as sort1, 0 as sort2
from m_tanto mt
inner join m_hanyo h1 on (h1.mst_kbn = '55' and h1.han_cd = mt.bu_cd)
where 1 = 1
and mt.del_flg = 0
and h1.koumoku1 != ''
and not exists (
	select 1
	from t_anken_tan tan
	inner join m_hanyo h1 on (h1.mst_kbn = '24' and h1.han_cd = tan.tan_kind)
	where 1 = 1
	and h1.koumoku3 != ''
	and tan.tan_cd = mt.tan_cd
)
order by sort1, sort2
limit 1

[registAutoTan]
delete from w_basv3010_tan
where s_id = :s_id
and tan_kind = ':w_tan_kind'
;
insert into w_basv3010_tan (
	  s_id
	, row_no
	, tan_kind
	, tan_kind_nm
	, tan_cd
	, tan_nm
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
)
select
	  :s_id
	, (select COALESCE(max(row_no),-1) + 1 from w_basv3010_tan where s_id = :s_id)
	, ':w_tan_kind'
	, h1.han_name
	, ':w_tan_cd'
	, h2.tan_nm
	, 0 --del_flg
	, ':login_id' --cre_id
	, now() --cre_time
	, ':login_id' --upd_id
	, now() --upd_time
from m_tanto t
left join m_hanyo h1 on (h1.mst_kbn = '24' and h1.han_cd = ':w_tan_kind')
left join m_tanto h2 on (h2.tan_cd = t.tan_cd)
where t.tan_cd = ':w_tan_cd'
;

[getAtchList]
select
  i.anken_atch_id
, i.koutei_no
, i.file_name
, m.koutei_cd
, m.koutei_nm
, i.file_image
from t_anken_atch i
left join t_anken_m m on (m.anken_cd = ':anken_cd' and m.koutei_no = i.koutei_no)
where i.anken_cd = ':anken_cd'
and i.del_flg = 0
order by koutei_no, anken_atch_id