[selectRecords]
select '1' as kbn,
	ms.sir_cd,
	ms.sir_nm,
	ms.tori_cond,
	tk.seikyu_zan,
	--tk.seikyu_ymd,
	substr(tk.seikyu_ymd, 1, 4) || '/' ||substr (tk.seikyu_ymd, 5, 2) || '/' ||substr (tk.seikyu_ymd, 7, 2) as seikyu_ymd,
	mt.tok_cd,
	mt.tok_nm,
	tu.seikyu_zan as uri_seikyu_zan,
	case 
		when mt.pay_d = 99 
			then to_char(to_date(to_char(to_date(tu.seikyu_ymd,'yyyymmdd') +  CAST((mt.pay_m + 1) || ' months' AS interval ),'yyyymm')||'01','yyyymmdd') + cast('-1 days' as interval),'yyyy/mm/dd')
 		else to_char(to_date(tu.seikyu_ymd,'yyyymmdd') +  CAST( mt.pay_m || ' months' AS interval ),'yyyy/mm') || '/' || to_char(mt.pay_d,'FM00') end as sousai_dt
from m_sir ms
left join t_kaikake tk on(ms.sir_cd = tk.sir_cd)
left join m_tokui mt on mt.tok_cd = ms.sousai_cd
left join (select tu1.tok_cd,tu1.seikyu_ymd,tu1.seikyu_zan from t_urikake tu1 where tu1.seikyu_ymd = (select max(tu2.seikyu_ymd) from t_urikake tu2 where tu2.tok_cd = tu1.tok_cd)) tu on tu.tok_cd = mt.tok_cd
where 1 = 1
and to_char(to_date(substring(tk.seikyu_ymd,1,6)||'01','yyyymmdd') + cast(ms.pay_m || ' months' AS interval ),'yyyymm') = '&1'
--and (substring(tk.seikyu_ymd,1,6)) = '&1'
and ( tk.shime_dd::varchar = '&2' or '' = '&2' )
and coalesce(ms.tori_cond,'0') = '0'
--���H
union all
select '2' as kbn,
	ms.sir_cd,
	ms.sir_nm,
	ms.tori_cond,
	tk.seikyu_zan,
	substr(tk.seikyu_ymd, 1, 4) || '/' ||substr (tk.seikyu_ymd, 5, 2) || '/' ||substr (tk.seikyu_ymd, 7, 2) as seikyu_ymd,
	mt.tok_cd,
	mt.tok_nm,
	tu.seikyu_zan as uri_seikyu_zan,
	case 
		when mt.pay_d = 99 
			then to_char(to_date(to_char(to_date(tu.seikyu_ymd,'yyyymmdd') +  CAST((mt.pay_m + 1) || ' months' AS interval ),'yyyymm')||'01','yyyymmdd') + cast('-1 days' as interval),'yyyy/mm/dd')
 		else to_char(to_date(tu.seikyu_ymd,'yyyymmdd') +  CAST( mt.pay_m || ' months' AS interval ),'yyyy/mm') || '/' || to_char(mt.pay_d,'FM00') end as sousai_dt
from m_sir ms
left join t_kaikake tk on(ms.sir_cd = tk.sir_cd)
left join m_tokui mt on mt.tok_cd = ms.sousai_cd
left join (select tu1.tok_cd,tu1.seikyu_ymd,tu1.seikyu_zan from t_urikake tu1 where tu1.seikyu_ymd = (select max(tu2.seikyu_ymd) from t_urikake tu2 where tu2.tok_cd = tu1.tok_cd)) tu on tu.tok_cd = mt.tok_cd
where 1 = 1
and to_char(to_date(substring(tk.seikyu_ymd,1,6)||'01','yyyymmdd') + cast(ms.pay_m || ' months' AS interval ),'yyyymm') = '&1'
--and (substring(tk.seikyu_ymd,1,6)) = '&1'
and ( tk.shime_dd::varchar = '&2' or '' = '&2' )
and coalesce(ms.tori_cond,'0') = '1'
--�o��
union all
select '3' as kbn,
	ms.sir_cd,
	ms.sir_nm,
	ms.tori_cond,
	tk.seikyu_zan,
	substr(tk.seikyu_ymd, 1, 4) || '/' ||substr (tk.seikyu_ymd, 5, 2) || '/' ||substr (tk.seikyu_ymd, 7, 2) as seikyu_ymd,
	mt.tok_cd,
	mt.tok_nm,
	tu.seikyu_zan as uri_seikyu_zan,
	case 
		when mt.pay_d = 99 
			then to_char(to_date(to_char(to_date(tu.seikyu_ymd,'yyyymmdd') +  CAST((mt.pay_m + 1) || ' months' AS interval ),'yyyymm')||'01','yyyymmdd') + cast('-1 days' as interval),'yyyy/mm/dd')
 		else to_char(to_date(tu.seikyu_ymd,'yyyymmdd') +  CAST( mt.pay_m || ' months' AS interval ),'yyyy/mm') || '/' || to_char(mt.pay_d,'FM00') end as sousai_dt
from m_sir ms
left join t_kaikake tk on(ms.sir_cd = tk.sir_cd)
left join m_tokui mt on mt.tok_cd = ms.sousai_cd
left join (select tu1.tok_cd,tu1.seikyu_ymd,tu1.seikyu_zan from t_urikake tu1 where tu1.seikyu_ymd = (select max(tu2.seikyu_ymd) from t_urikake tu2 where tu2.tok_cd = tu1.tok_cd)) tu on tu.tok_cd = mt.tok_cd
where 1 = 1
and to_char(to_date(substring(tk.seikyu_ymd,1,6)||'01','yyyymmdd') + cast(ms.pay_m || ' months' AS interval ),'yyyymm') = '&1'
--and (substring(tk.seikyu_ymd,1,6)) = '&1'
and ( tk.shime_dd::varchar = '&2' or '' = '&2' )
and coalesce(ms.tori_cond,'0') = '2'
order by kbn, sir_cd ;
--limit 20;

[h_select]
select koumoku1, koumoku2, koumoku3, koumoku4, koumoku5, koumoku6, koumoku7, koumoku8, koumoku9, koumoku10, koumoku11, koumoku12
from m_hanyo
where mst_kbn ='50'
and han_cd = ':chgr'

[sime_select]
select distinct seikyu_ymd
from t_urikake
order by seikyu_ymd desc


[delete_wk]
delete from w_basr0030
where 1=1
and s_id = :s_id

[insert_wk]
--insert into w_basr0030(s_id, tok_cd, del_flg, cre_id, cre_time, upd_id, upd_time)
--values(:s_id, ':tok_cd', 0, ':login_id', now(), ':login_id', now())
insert into w_basr0030(s_id, seikyu_ymd, tok_cd, del_flg, cre_id, cre_time, upd_id, upd_time)
--values(:s_id, '&16', '&20', 0, '&24', now(), '&24', now())
values(:s_id, ':seikyu_ymd', ':tok_cd', 0, ':login_id', now(), ':login_id', now())

[createBASR0410]
delete from w_basr0410 where s_id = :s_id;
insert into w_basr0410
(s_id, tok_cd, syori_ymd, del_flg, cre_id, cre_time, upd_id, upd_time)
select :s_id, m.tok_cd, to_char((case m.shime_dd when '99' then (to_timestamp(':taisyo_y'||':taisyo_m' || '01', 'yyyymmdd')
			 + interval '1 month') else (to_timestamp(':taisyo_y'||':taisyo_m' || m.shime_dd,
				'yyyymmdd') + interval '1 days') end),'yyyymmdd'), 0, ':login_id', now(), ':login_id', now()
from m_tokui m
where (m.tok_cd >= ':tok_cd_fr' or '' = ':tok_cd_fr')
and (m.tok_cd <= ':tok_cd_to' or '' = ':tok_cd_to')
;

[update3yearsUri]
select fnc_update_sum_3years_uri(:s_id, ':taisyo_y'||':taisyo_m', ':bu_cd', ':tan_cd_fr', ':tan_cd_to', ':tok_cat_cd', ':tok_cd_fr', ':tok_cd_to', ':dcat_cd_fr', ':dcat_cd_to', ':cat_cd_fr', ':cat_cd_to', ':hin_cd_fr', ':hin_cd_to', ':ext6', ':login_id', ':eig_cd_fr', ':eig_cd_to')