[load_grdList_jyu]
select
  um.den_no
, um.row_no
, um.hin_cd
, um.hin_nm
, COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) as jucyu_zan
, um.jucyu_su
, um.tanka
, uh.syori_ymd
, um.nonyu_ymd
, uh.tok_cd
, uh.tok_nm
, uh.biko1
, um.tok_hacyu_no
from t_uri uh
inner join t_uri_m um on (um.den_no = uh.den_no)
left join m_tokui tk on (uh.tok_cd = tk.tok_cd)
WHERE 1 = 1
and uh.jucyu_flg = 1
and ((uh.den_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
--and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
--and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
--and (('' = ':nonyu_ymd_fr') or (um.nonyu_ymd >= ':nonyu_ymd_fr'))
--and (('' = ':nonyu_ymd_to') or (um.nonyu_ymd <= ':nonyu_ymd_to'))
--and (('' = ':tok_cd') or (uh.tok_cd = ':tok_cd'))
--and ((uh.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
--and ((uh.biko1 like '%:biko1%' or uh.biko2 like '%:biko1%') or ('' = ':biko1'))
--and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
order by um.nonyu_ymd, uh.syori_ymd, um.den_no, um.row_no
;

[load_grdList_syk]
select
  s.den_no
, s.row_no
, s.sethin_row_no
, s.hin_cd
, s.hin_nm
, s.suryo
, null as tanka
, null as kingaku
, um.hin_cd as ohin_cd
, um.hin_nm as ohin_nm
, um.suryo as uri_su
, uh.syori_ymd
, uh.tok_cd
, uh.tok_nm
, uh.jucyu_no
, null as sir_no
, s.den_no||'-'||s.row_no as den_key
from t_syuko_plan s
inner join t_uri_m um on (um.den_no = s.den_no and um.row_no = s.row_no)
inner join t_uri uh on (uh.den_no = um.den_no)
where COALESCE(um.syuko_flg,0) = 0
and ((uh.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
--and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
--and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
--and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
--and (('' = ':tok_cd') or (uh.tok_cd = ':tok_cd'))
--and ((uh.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
--and (':nk_cond' != '1')
union all
select
  s.uri_no as den_no
, s.uri_row_no as row_no
, s.den_no as sethin_row_no
, s.hin_cd
, s.hin_nm
, s.suryo
, s.tanka
, s.kingaku
, s.ohin_cd
, s.ohin_nm
, um.suryo as uri_su
, s.syori_ymd
, uh.tok_cd
, uh.tok_nm
, s.jucyu_no
, s.sir_no
, s.uri_no||'-'||s.uri_row_no as den_key
from t_syuko s
left join t_uri_m um on (um.den_no = s.uri_no and um.row_no = s.uri_row_no)
left join t_uri uh on (uh.den_no = um.den_no)
where 1 = 1
and ((s.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
--and ((s.uri_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
--and (('' = ':syori_ymd_fr') or (s.syori_ymd >= ':syori_ymd_fr'))
--and (('' = ':syori_ymd_to') or (s.syori_ymd <= ':syori_ymd_to'))
--and (('' = ':tok_cd') or (uh.tok_cd = ':tok_cd'))
--and ((uh.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
--and (':nk_cond' != '0')
order by syori_ymd, den_no, row_no, sethin_row_no
;

[load_grdList_hc]
select distinct
  um.den_no
, um.row_no
, um.hin_cd
, um.hin_nm
, COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0) as hacyu_zan
, um.hacyu_su
, um.tanka
--, um.suryo
, um.hacyu_su - um.sir_su as haccyu_suryo
, um.kingaku
, uh.syori_ymd
, um.nyuka_yotei_ymd as nonyu_ymd
, uh.sir_cd
, uh.sir_nm
, uh.biko1
, um.jucyu_no
, '' as syubetsu
from t_sir uh
inner join t_sir_m um on (um.den_no = uh.den_no)
inner join m_hin hin on (um.hin_cd = hin.hin_cd)
WHERE 1 = 1
and uh.hacyu_flg = 1
--and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
--and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
--and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
--and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
--and (('' = ':nonyu_ymd_fr') or (um.nyuka_yotei_ymd >= ':nonyu_ymd_fr'))
--and (('' = ':nonyu_ymd_to') or (um.nyuka_yotei_ymd <= ':nonyu_ymd_to'))
--and (('' = ':sir_cd') or (uh.sir_cd = ':sir_cd'))
--and ((uh.sir_nm like '%:sir_nm%') or ('' = ':sir_nm'))
--and ((uh.biko1 like '%:biko1%' or uh.biko2 like '%:biko1%') or ('' = ':biko1'))
--and (('' = ':zan_flg') or (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0) > 0))
and ((um.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
order by um.nyuka_yotei_ymd, uh.syori_ymd, um.den_no, um.row_no
;

[load_grdList_sir]
SELECT distinct
  h.den_no
, h.syori_ymd
, coalesce(h.hacyu_no::text,'����') as hacyu_no
, h.sir_nm
, m1.hin_nm
, m1.suryo
, m1.tanka
, m1.kingaku
, uri.tok_nm as simuketi
, m1.jucyu_no
, sir.tori_cond
, h2.han_name as zei_kbn
, h1.han_name as cat_cd
FROM t_sir h
left join t_sir_m m1 on (h.den_no = m1.den_no)
left join m_sir sir on (h.sir_cd = sir.sir_cd)
left join m_hin hin on (hin.hin_cd = m1.hin_cd)
left join m_hanyo h1 on (h1.mst_kbn = '05' and h1.han_cd = hin.cat_cd)
left join t_uri uri on (m1.jucyu_no = uri.jucyu_no)
left join m_hanyo h2 on (h2.mst_kbn = '18' and h2.han_cd = m1.zei_kbn::text)
WHERE 1 = 1
and h.syori_ymd != ''
and h.hacyu_flg = 0
--and ((h.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
--and (('' = ':syori_ymd_fr') or (h.syori_ymd >= ':syori_ymd_fr'))
--and (('' = ':syori_ymd_to') or (h.syori_ymd <= ':syori_ymd_to'))
--and (('' = ':prc_sts') or (h.prc_sts = ':prc_sts'))
--and (('' = ':sir_cd') or (h.sir_cd = ':sir_cd'))
--and ((sir.sir_nm like '%:sir_nm%') or ('' = ':sir_nm'))
--and ((h.biko1 like '%:biko1%' or h.biko2 like '%:biko1%') or ('' = ':biko1'))
--and (('' = ':tori_kbn') or (sir.sir_cd is not null and sir.tori_kbn = ':tori_kbn'))
and ((m1.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
order by h.syori_ymd desc, h.den_no desc
;

[getHeader]
select tok_nm,biko1,syori_ymd,zeikomi_gaku
from t_uri
where jucyu_flg = 1
and (den_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no')
;