[load_grdList]
select
  w.syukin_no
, w.sir_cd
, w.syukin_ymd
, w.syukin_kbn
, w.syukin_gaku
, w.tesuryo
, w.cyosei_gaku
, w.total_gaku
, w.biko
, w.seikyu_flg
, w.seikyu_ymd
, w.syukin_kbn_nm
, w.sir_nm
, w.syukin_term_id
, w.syukin_term_nm
, w.tan_nm
, w.meisai_biko
, w.syukin_kbn_sort
, w.keshi_gaku
--#20451 2021.08.28 MOD STT
--, COALESCE(w.total_gaku,0) - COALESCE(w.keshi_gaku,0) as keshi_zan
, COALESCE(w.total_gaku,0) - COALESCE(w.futan_gaku,0) - COALESCE(w.keshi_gaku,0) as keshi_zan
--#20451 2021.08.28 MOD END
, w.tegata_ymd
, w.tegata_no

from (

--明細なし
select
  t.syukin_no
, t.sir_cd
, t.syukin_ymd
, t.syukin_kbn
, t.syukin_gaku
, t.tesuryo
, t.cyosei_gaku
, t.total_gaku
, t.biko
, t.seikyu_flg
, t.seikyu_ymd
, h1.han_name as syukin_kbn_nm
--2018.10.21 本来はt.sir_nmでよいが、登録済データのNULL値があるので、残念な対応 STT
--, tk.sir_nm
, COALESCE(t.sir_nm,tk.sir_nm) as sir_nm
--2018.10.21 本来はt.sir_nmでよいが、登録済データのNULL値があるので、残念な対応 END
, t.syukin_term_id
, h2.han_name as syukin_term_nm
, tan.tan_nm
, '' as meisai_biko
, h1.sort_key as syukin_kbn_sort
, (select COALESCE(sum(k.syukin_gaku),0) from t_syu_keshi k where k.syukin_no = t.syukin_no) as keshi_gaku
, t.tegata_ymd
, t.tegata_no
--#20451 2021.08.28 MOD STT
, 0 as futan_gaku
--#20451 2021.08.28 MOD END
from t_syukin t
left join m_sir tk on (tk.sir_cd = t.sir_cd)
left join m_hanyo h1 on (h1.mst_kbn = '19' and h1.han_cd = t.syukin_kbn)
left join m_hanyo h2 on (h2.mst_kbn = '64' and h2.han_cd = t.syukin_term_id)
left join m_tanto tan on (t.tan_cd = tan.tan_cd)
where 1 = 1
and ':meisai_mode' = '0'
and t.del_flg = 0
and ((t.syukin_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (t.syukin_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (t.syukin_ymd <= ':syori_ymd_to'))
and (('' = ':sir_cd') or (t.sir_cd = ':sir_cd'))
AND ((fnc_translate_case(tk.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(tk.sir_kana) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(t.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
and ((t.biko like '%:biko1%') or ('' = ':biko1'))
and (('' = ':syukin_kbn') or (t.syukin_kbn = ':syukin_kbn'))
and (('' = ':eig_cd') or (t.eig_cd = ':eig_cd'))
--TODO
--and ((t.syukin_term_id = ':syukin_term_id') or ('' = ':syukin_term_id'))
--and (('' = ':tan_cd') or (t.tan_cd = ':tan_cd'))
--明細あり
union all
select
  t.syukin_no
, t.sir_cd
, t.syukin_ymd
, m.syukin_kbn
, m.syukin_gaku
, cast(null as numeric(12,2)) as tesuryo
, cast(null as numeric(12,2)) as cyosei_gaku
, t.total_gaku
, t.biko
, t.seikyu_flg
, t.seikyu_ymd
, h1.han_name as syukin_kbn_nm
, COALESCE(t.sir_nm,tk.sir_nm) as sir_nm
, t.syukin_term_id
, h2.han_name as syukin_term_nm
, tan.tan_nm
, m.biko as meisai_biko
, h1.sort_key as syukin_kbn_sort
, (select COALESCE(sum(k.syukin_gaku),0) from t_syu_keshi k where k.syukin_no = t.syukin_no) as keshi_gaku
, t.tegata_ymd
, t.tegata_no
--#20451 2021.08.28 MOD STT
, t.futan_gaku
--#20451 2021.08.28 MOD END
from t_syukin t
inner join t_syukin_m m on (m.syukin_no = t.syukin_no)
left join m_sir tk on (tk.sir_cd = t.sir_cd)
left join m_hanyo h1 on (h1.mst_kbn = '19' and h1.han_cd = m.syukin_kbn)
left join m_hanyo h2 on (h2.mst_kbn = '64' and h2.han_cd = t.syukin_term_id)
left join m_tanto tan on (t.tan_cd = tan.tan_cd)
where 1 = 1
and ':meisai_mode' = '1'
and t.del_flg = 0
and ((t.syukin_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (t.syukin_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (t.syukin_ymd <= ':syori_ymd_to'))
and (('' = ':sir_cd') or (t.sir_cd = ':sir_cd'))
AND ((fnc_translate_case(tk.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(tk.sir_kana) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(t.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
--#20051 2021.07.28 MOD STT
--and ((m.biko like '%:biko1%') or ('' = ':biko1'))
and ((t.biko like '%:biko1%') or (m.biko like '%:biko1%') or ('' = ':biko1'))
--#20051 2021.07.28 MOD END
and (('' = ':syukin_kbn') or (m.syukin_kbn = ':syukin_kbn'))
and (('' = ':eig_cd') or (t.eig_cd = ':eig_cd'))
--TODO
--and ((t.syukin_term_id = ':syukin_term_id') or ('' = ':syukin_term_id'))
--and (('' = ':tan_cd') or (t.tan_cd = ':tan_cd'))

) w
where 1 = 1
--#20451 2021.08.28 MOD STT
--and ((':cond_keshi' != '0') or ((COALESCE(w.total_gaku,0) - COALESCE(w.keshi_gaku,0)) != 0))
--and ((':cond_keshi' != '1') or ((COALESCE(w.total_gaku,0) - COALESCE(w.keshi_gaku,0)) = 0))
and ((':cond_keshi' != '0') or ((COALESCE(w.total_gaku,0) - COALESCE(w.futan_gaku,0) - COALESCE(w.keshi_gaku,0)) != 0))
and ((':cond_keshi' != '1') or ((COALESCE(w.total_gaku,0) - COALESCE(w.futan_gaku,0) - COALESCE(w.keshi_gaku,0)) = 0))
--#20451 2021.08.28 MOD END

order by syukin_ymd desc, syukin_no desc, syukin_kbn_sort, syukin_kbn
--TODO
limit 1000