[SelectRecords]
select
  ah.anken_cd
, ah.anken_nm
, (case when(ah.start_ymd != '')then(to_char(to_date(ah.start_ymd,'yyyymmdd'),'yyyy/mm/dd'))else(null)end) as start_ymd
, (case when(ah.jucyu_ymd != '')then(to_char(to_date(ah.jucyu_ymd,'yyyymmdd'),'yyyy/mm/dd'))else(null)end) as jucyu_ymd
, (case when(ah.end_ymd != '')then(to_char(to_date(ah.end_ymd,'yyyymmdd'),'yyyy/mm/dd'))else(null)end) as end_ymd
, ah.status
, ah.biko
, ah.hin_nm
, ah.addr1
, ah.space_info
, ah.kingaku
, fnc_get_anken_ops(ah.anken_cd) as all_op_nm
, mt1.tan_nm as tan1_nm
, mt2.tan_nm as tan2_nm
, mt3.tan_nm as tan3_nm
, mt4.tan_nm as tan4_nm
, mt5.tan_nm as tan5_nm
, mt6.tan_nm as tan6_nm
, mt7.tan_nm as tan7_nm
, mt8.tan_nm as tan8_nm
, am.koutei_cd
, am.koutei_nm
, am.no
, am.title
, am.meisai
, am.koutei_no
, am.d_status
, am.color_s
, (case(am.no)when(1)then(8)when(2)then(8)when(3)then(9)when(4)then(8)when(5)then(9)else(9)end) as format_row_i
from t_anken ah
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t1 on (t1.anken_cd = ah.anken_cd and t1.tan_kind = '01')
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t2 on (t2.anken_cd = ah.anken_cd and t2.tan_kind = '02')
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t3 on (t3.anken_cd = ah.anken_cd and t3.tan_kind = '03')
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t4 on (t4.anken_cd = ah.anken_cd and t4.tan_kind = '04')
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t5 on (t5.anken_cd = ah.anken_cd and t5.tan_kind = '05')
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t6 on (t6.anken_cd = ah.anken_cd and t6.tan_kind = '06')
--left join (select anken_cd, tan_kind, min(tan_cd) as tan_cd from t_anken_tan group by anken_cd, tan_kind) t7 on (t7.anken_cd = ah.anken_cd and t7.tan_kind = '07')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t1 on (t1.anken_cd = ah.anken_cd and t1.tan_kind = '01')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t2 on (t2.anken_cd = ah.anken_cd and t2.tan_kind = '02')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t3 on (t3.anken_cd = ah.anken_cd and t3.tan_kind = '03')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t4 on (t4.anken_cd = ah.anken_cd and t4.tan_kind = '04')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t5 on (t5.anken_cd = ah.anken_cd and t5.tan_kind = '05')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t6 on (t6.anken_cd = ah.anken_cd and t6.tan_kind = '06')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t7 on (t7.anken_cd = ah.anken_cd and t7.tan_kind = '07')
left join (select at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end) as tan_kind, min(at.tan_cd) as tan_cd from t_anken_tan at left join m_hanyo h1 on (h1.mst_kbn='24' and h1.han_cd=at.tan_kind) group by at.anken_cd, (case when(h1.koumoku4 != '')then(h1.koumoku4)else(at.tan_kind)end)) t8 on (t8.anken_cd = ah.anken_cd and t8.tan_kind = '08')
left join m_tanto mt1 on (mt1.tan_cd = t1.tan_cd)
left join m_tanto mt2 on (mt2.tan_cd = t2.tan_cd)
left join m_tanto mt3 on (mt3.tan_cd = t3.tan_cd)
left join m_tanto mt4 on (mt4.tan_cd = t4.tan_cd)
left join m_tanto mt5 on (mt5.tan_cd = t5.tan_cd)
left join m_tanto mt6 on (mt6.tan_cd = t6.tan_cd)
left join m_tanto mt7 on (mt7.tan_cd = t7.tan_cd)
left join m_tanto mt8 on (mt8.tan_cd = t8.tan_cd)
--left join v_m_koutei ko on ('&4' != '' and strpos(ko.koutei_filter,'&4') > 0)
left join (

	select am.anken_cd, am.koutei_no, am.koutei_cd, am.koutei_nm, am.koutei_filter, 1 as no, '事業計画' as title, to_char(m.yotei_stt_time,'yyyy/mm/dd') as meisai, 0 as d_status, '' as color_s
	from t_anken_m am
	inner join t_anken ah on (ah.anken_cd = am.anken_cd)
	left join t_yplan_anken y on (ah.anken_cd = y.anken_cd)
	left join t_yplan_anken_m m on (m.yplan_anken_cd = y.yplan_anken_cd and m.koutei_no = am.koutei_no)
	where '&5' = '1'
	union all
	select am.anken_cd, am.koutei_no, am.koutei_cd, am.koutei_nm, am.koutei_filter, 2 as no, '実施計画' as title, to_char(am.yotei_stt_time,'yyyy/mm/dd') as meisai, 0 as d_status, '' as color_s
	from t_anken_m am
	where '&6' = '1'
	union all
	select am.anken_cd, am.koutei_no, am.koutei_cd, am.koutei_nm, am.koutei_filter, 3 as no, '対事業計画差異' as title, cast(EXTRACT(DAY FROM ym.yotei_stt_time - am.yotei_stt_time) as text) as meisai, 0 as d_status, '' as color_s
	from t_anken_m am
	left join t_yplan_anken y on (y.anken_cd = am.anken_cd)
	left join t_yplan_anken_m ym on (ym.yplan_anken_cd = y.yplan_anken_cd and ym.koutei_no = am.koutei_no)
	where '&7' = '1'
	union all
	select am.anken_cd, am.koutei_no, am.koutei_cd, am.koutei_nm, am.koutei_filter, 4 as no, '実施日' as title, to_char(am.stt_time,'yyyy/mm/dd') as meisai, am.status as d_status, '204,204,204' as color_s
	from t_anken_m am
	where '&8' = '1'
	union all
	select am.anken_cd, am.koutei_no, am.koutei_cd, am.koutei_nm, am.koutei_filter, 5 as no, '対実施計画差異' as title, cast(EXTRACT(DAY FROM am.yotei_stt_time - am.stt_time) as text) as meisai, 0 as d_status, '' as color_s
	from t_anken_m am
	where '&9' = '1'
	union all
	select am.anken_cd, am.koutei_no, am.koutei_cd, am.koutei_nm, am.koutei_filter, 6 as no, '工程毎差異' as title, cast((case when(m2.next_kikan is not null)then(m2.next_kikan - (EXTRACT(DAY FROM am.stt_time - m2.stt_time)))else(0)end) as text) as meisai, 0 as d_status, '' as color_s
	from t_anken_m am
	left join t_anken_m m2 on (m2.anken_cd = am.anken_cd and m2.koutei_no = am.koutei_no - 1)
	where '&10' = '1'

--) am on (am.anken_cd = ah.anken_cd and am.koutei_cd = ko.koutei_cd)
) am on (am.anken_cd = ah.anken_cd)

where 1 = 1
and ((ah.anken_cd = '&1') or ('&1' = ''))
and ((ah.anken_kbn = '&18') or ('&18' = ''))
and ((ah.start_ymd >= '&2') or ('&2' = ''))
and ((ah.start_ymd <= '&3') or ('&3' = ''))
--and (('' = '&11') or (ah.anken_cd in (select anken_cd from v_tan_anken_koutei where tan_cd = '&11')))
and (('' = '&11') or (ah.anken_cd in (select anken_cd from t_anken_tan where tan_cd = '&11')))
and (('1' = '&12' and COALESCE(ah.status,0) in (0,2) and ah.jucyu_ymd != '')
  or ('1' = '&13' and COALESCE(ah.status,0) in (0,2) and COALESCE(ah.jucyu_ymd,'') = '')
  or ('1' = '&14' and ah.status = 1 and COALESCE(ah.cancel_ymd,'') = '')
  or ('1' = '&15' and ah.status = 3)
  or ('1' = '&16' and ah.status = 1 and ah.cancel_ymd != '')
)
and ((ah.sedai_cd = '&17') or ('&17' = ''))
and strpos(am.koutei_filter,'&4') > 0
--order by anken_cd, no, koutei_no
order by (case when(ah.jucyu_ymd != '')then(0)else(1)end), ah.end_ymd, ah.anken_cd, am.no, am.koutei_no

[getTanKindList]
select
  (case when(koumoku4 != '')then(koumoku4)else(han_cd)end) as tan_kind
, han_name as tan_kind_nm
from m_hanyo
where mst_kbn = '24'
and del_flg = 0
order by tan_kind
limit 8