[load_grdList]
select
	  w.den_no
	, w.mitsumori_no
	, w.mitsumori_mode
	, w.syori_ymd
	, w.tok_cd
	, w.tok_nm
	, w.zeinuki_gaku
	, w.zeikomi_gaku
	, w.biko1
	, w.biko1_ex
	, w.biko2
	, w.nebiki_gaku
	, w.title1
	, w.tan_nm
	, w.sekkei_tan_nm
	, w.eigyou_tan_nm
	, w.nonyu_nm
	, w.prc_sts
	, w.hin_cd
	, w.hin_nm
	, w.jucyu_su
	, w.gentanka
	, w.genka_gaku
	, w.htanka
	, w.htanka_f2
	, w.disc_per
	, w.tanka
	, w.tanka_f2
	, w.kingaku
	, w.arari_gaku
	, w.meisai_biko
	, w.hosoku2
	, w.tok_hacyu_no
	, w.sir_nm
	, w.jucyu_cnt
	, w.hosoku3
from (
(select
  uri.den_no
, case when (coalesce(h3.han_name,'') = '') then (uri.mitsumori_no) else substring(upper(uri.mitsumori_no),1,length(uri.mitsumori_no) - 2) || '-' || right(uri.mitsumori_no, 2) end as mitsumori_no
, 1 as mitsumori_mode
, uri.syori_ymd
, uri.tok_cd
, uri.tok_nm
, uri.zeinuki_gaku
, uri.zeikomi_gaku
, uri.biko1
, uri.biko1 as biko1_ex
, uri.biko2
, um2.kingaku as nebiki_gaku
, uri.title1
, tan1.tan_nm as tan_nm
, tan2.tan_nm as sekkei_tan_nm
, tan3.tan_nm as eigyou_tan_nm
, uri.nonyu_nm
, h1.han_name as prc_sts
, null as hin_cd
, null as hin_nm
, null as jucyu_su
, null as gentanka
, null as genka_gaku
, null as htanka
, null as htanka_f2
, null as disc_per
, null as tanka
, null as tanka_f2
, null as kingaku
, null as arari_gaku
, null as meisai_biko
, null as hosoku2
, null as tok_hacyu_no
, null as sir_cd
, null as sir_nm
, (select count(*) from t_uri jh where jh.mitsu_den_no = uri.den_no and jh.del_flg = 0 and jh.jucyu_flg = 1 and coalesce(jh.mitsumori_flg,0) = 0) as jucyu_cnt
, null as hosoku3
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_tanto tan1 on (tan1.tan_cd = uri.tan_cd)
left join m_tanto tan2 on (tan2.tan_cd = uri.sekkei_tan_cd)
left join m_tanto tan3 on (tan3.tan_cd = uri.eigyou_tan_cd)
left join t_uri_m um on (um.den_no = uri.den_no and um.orow_no is null)
left join t_uri_m um2 on (um2.den_no = uri.den_no and um2.orow_no is null and um2.hin_cd = '99')
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
left join m_hanyo h3 on (h3.mst_kbn = '99' and h3.han_cd = 'mitsu-eda')
WHERE 1 = 1
and (0 = :meisai_flg)
and um.orow_no is null
and uri.del_flg = 0
and uri.mitsumori_flg = 1
and uri.syori_ymd != ''
AND ((fnc_translate_case(uri.mitsumori_no) LIKE '%'||fnc_translate_case(':mitsumori_no')||'%') or (':mitsumori_no' = ''))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and (('' = ':tan_cd') or (uri.tan_cd = ':tan_cd'))
and (('' = ':sekkei_tan_cd') or (uri.sekkei_tan_cd = ':sekkei_tan_cd'))
and (('' = ':eigyou_tan_cd') or (uri.eigyou_tan_cd = ':eigyou_tan_cd'))
and (('' = ':eig_cd') or (uri.eig_cd = ':eig_cd'))
--ユニット名
AND ((fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':ar_meisai_biko')||'%') or (':ar_meisai_biko' = ''))
AND ((fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':meisai_biko')||'%') or (':meisai_biko' = '') or (fnc_translate_case(um.hosoku2) LIKE '%'||fnc_translate_case(':meisai_biko')||'%') or (':meisai_biko' = ''))
--納入場所
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':nonyu_nm')||'%') or (':nonyu_nm' = ''))
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':ar_nonyu_nm')||'%') or (':ar_nonyu_nm' = ''))
AND (((um.hin_cd) LIKE '%'||(':hin_cd')||'%') or (':hin_cd' = ''))
AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
:multi_cond_hin_nm
AND ((':tok_nm' = '') or (fnc_translate_case(uri.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%'))
--工事名称
AND ((fnc_translate_case(uri.title1) LIKE '%'||fnc_translate_case(':title1')||'%') or (':title1' = '') or (fnc_translate_case(uri.title2) LIKE '%'||fnc_translate_case(':title1')||'%') or (':title1' = ''))
AND ((fnc_translate_case(uri.title1) LIKE '%'||fnc_translate_case(':ar_title1')||'%') or (':ar_title1' = '') or (fnc_translate_case(uri.title2) LIKE '%'||fnc_translate_case(':ar_title1')||'%') or (':ar_title1' = ''))
--備考・特記事項
AND ((fnc_translate_case(uri.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko3) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko4) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko5) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko6) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
AND ((fnc_translate_case(uri.biko1) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko2) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko3) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko4) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko5) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko6) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = ''))
--明細備考
AND ((fnc_translate_case(um.hosoku1) LIKE '%'||fnc_translate_case(':ar_hosoku1')||'%') or (':ar_hosoku1' = '') or (fnc_translate_case(um.hosoku2) LIKE '%'||fnc_translate_case(':ar_hosoku1')||'%') or (':ar_hosoku1' = ''))
--全て検索
AND ((fnc_translate_case(um.search_text) LIKE '%'||fnc_translate_case(':all_search')||'%') or (':all_search' = ''))
--得意先発注番号
AND ((fnc_translate_case(uri.tok_hacyu_no) LIKE '%'||fnc_translate_case(':tok_hacyu_no')||'%') or (':tok_hacyu_no' = '') or (fnc_translate_case(um.tok_hacyu_no) LIKE '%'||fnc_translate_case(':tok_hacyu_no')||'%') or (':tok_hacyu_no' = ''))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':hosoku3') or (um.hosoku3 like '%:hosoku3%'))
group by 
  uri.den_no
, uri.mitsumori_no
, uri.syori_ymd
, uri.tok_cd
, uri.tok_nm
, uri.zeinuki_gaku
, uri.zeikomi_gaku
, uri.biko1
, uri.biko1 
, uri.biko2
, um2.kingaku 
, uri.title1
, tan1.tan_nm 
, tan2.tan_nm 
, tan3.tan_nm 
, uri.nonyu_nm
, h1.han_name
, h3.han_name
--order by uri.syori_ymd desc, uri.den_no desc
)
union all
(
select
  uri.den_no
, case when (coalesce(h3.han_name,'') = '') then (uri.mitsumori_no) else substring(upper(uri.mitsumori_no),1,length(uri.mitsumori_no) - 2) || '-' || right(uri.mitsumori_no, 2) end as mitsumori_no
, 1 as mitsumori_mode
, uri.syori_ymd
, uri.tok_cd
, uri.tok_nm
, uri.zeinuki_gaku
, uri.zeikomi_gaku
, uri.biko1
, uri.biko1 as biko1_ex
, uri.biko2
, um2.kingaku as nebiki_gaku
, uri.title1
, tan1.tan_nm as tan_nm
, tan2.tan_nm as sekkei_tan_nm
, tan3.tan_nm as eigyou_tan_nm
, uri.nonyu_nm
, h1.han_name as prc_sts
, um.hin_cd
, um.hin_nm||(case when(h99b.han_name = '1')then('')else(COALESCE(' '||um.hin_nm_ext,''))end) as hin_nm
, um.jucyu_su
, um.gentanka
, um.genka_gaku
, um.htanka
, um.htanka as htanka_f2
, um.disc_per
, um.tanka
, um.tanka as tanka_f2
, um.kingaku
, coalesce(um.kingaku) - coalesce(um.genka_gaku) as arari_gaku
, um.meisai_biko
, um.hosoku2
, um.tok_hacyu_no
, um.sir_cd
, um.sir_nm
, (select count(*) from t_uri jh where jh.mitsu_den_no = uri.den_no and jh.del_flg = 0 and jh.jucyu_flg = 1 and coalesce(jh.mitsumori_flg,0) = 0) as jucyu_cnt
, um.hosoku3
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_tanto tan1 on (tan1.tan_cd = uri.tan_cd)
left join m_tanto tan2 on (tan2.tan_cd = uri.sekkei_tan_cd)
left join m_tanto tan3 on (tan3.tan_cd = uri.eigyou_tan_cd)
left join t_uri_m um on (um.den_no = uri.den_no and um.orow_no is null)
left join t_uri_m um2 on (um2.den_no = uri.den_no and um2.orow_no is null and um2.hin_cd = '99')
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
left join m_hanyo h3 on (h3.mst_kbn = '99' and h3.han_cd = 'mitsu-eda')
left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'nnmx-v0412')
WHERE 1 = 1
and (1 = :meisai_flg)
and um.orow_no is null
and uri.del_flg = 0
and uri.mitsumori_flg = 1
and uri.syori_ymd != ''
AND ((fnc_translate_case(uri.mitsumori_no) LIKE '%'||fnc_translate_case(':mitsumori_no')||'%') or (':mitsumori_no' = ''))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and (('' = ':tan_cd') or (uri.tan_cd = ':tan_cd'))
and (('' = ':sekkei_tan_cd') or (uri.sekkei_tan_cd = ':sekkei_tan_cd'))
and (('' = ':eigyou_tan_cd') or (uri.eigyou_tan_cd = ':eigyou_tan_cd'))
and (('' = ':eig_cd') or (uri.eig_cd = ':eig_cd'))
--ユニット名
AND ((fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':ar_meisai_biko')||'%') or (':ar_meisai_biko' = ''))
AND ((fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':meisai_biko')||'%') or (':meisai_biko' = '') or (fnc_translate_case(um.hosoku2) LIKE '%'||fnc_translate_case(':meisai_biko')||'%') or (':meisai_biko' = ''))
--納入場所
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':nonyu_nm')||'%') or (':nonyu_nm' = ''))
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':ar_nonyu_nm')||'%') or (':ar_nonyu_nm' = ''))
AND (((um.hin_cd) LIKE '%'||(':hin_cd')||'%') or (':hin_cd' = ''))
AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
:multi_cond_hin_nm
AND ((':tok_nm' = '') or (fnc_translate_case(uri.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%'))
--工事名称
AND ((fnc_translate_case(uri.title1) LIKE '%'||fnc_translate_case(':title1')||'%') or (':title1' = '') or (fnc_translate_case(uri.title2) LIKE '%'||fnc_translate_case(':title1')||'%') or (':title1' = ''))
AND ((fnc_translate_case(uri.title1) LIKE '%'||fnc_translate_case(':ar_title1')||'%') or (':ar_title1' = '') or (fnc_translate_case(uri.title2) LIKE '%'||fnc_translate_case(':ar_title1')||'%') or (':ar_title1' = ''))
--備考・特記事項
AND ((fnc_translate_case(uri.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko3) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko4) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko5) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko6) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
AND ((fnc_translate_case(uri.biko1) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko2) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko3) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko4) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko5) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko6) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = ''))
--明細備考
AND ((fnc_translate_case(um.hosoku1) LIKE '%'||fnc_translate_case(':ar_hosoku1')||'%') or (':ar_hosoku1' = '') or (fnc_translate_case(um.hosoku2) LIKE '%'||fnc_translate_case(':ar_hosoku1')||'%') or (':ar_hosoku1' = ''))
--全て検索
AND ((fnc_translate_case(um.search_text) LIKE '%'||fnc_translate_case(':all_search')||'%') or (':all_search' = ''))
--得意先発注番号
AND ((fnc_translate_case(uri.tok_hacyu_no) LIKE '%'||fnc_translate_case(':tok_hacyu_no')||'%') or (':tok_hacyu_no' = '') or (fnc_translate_case(um.tok_hacyu_no) LIKE '%'||fnc_translate_case(':tok_hacyu_no')||'%') or (':tok_hacyu_no' = ''))
--単価検索
and (('' = ':tanka_fr') or (um.tanka >= fnc_to_number(':tanka_fr')))
and (('' = ':tanka_to') or (um.tanka <= fnc_to_number(':tanka_to')))
--数量検索
and (('' = ':suryo_fr') or (um.jucyu_su >= fnc_to_number(':suryo_fr')))
and (('' = ':suryo_to') or (um.jucyu_su <= fnc_to_number(':suryo_to')))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and ((um.sir_cd like '%:sir_cd%') or ('' = ':sir_cd'))
and ((um.sir_nm like '%:sir_nm%') or ('' = ':sir_nm'))
and (('' = ':hosoku3') or (um.hosoku3 like '%:hosoku3%'))
--order by uri.syori_ymd desc, uri.den_no desc
) 
) w
where 1 = 1
and ((':cond_jucyu' != '0') or (w.jucyu_cnt <  1))
and ((':cond_jucyu' != '1') or (w.jucyu_cnt >= 1))
order by w.syori_ymd desc, w.den_no desc

limit 10000

 --コメントアウトだらけなので一旦移動します。
[load_grdList_old]
SELECT -- distinct
  uri.den_no
, case when (coalesce(h3.han_name,'') = '') then (uri.mitsumori_no) else substring(upper(uri.mitsumori_no),1,length(uri.mitsumori_no) - 2) || '-' || right(uri.mitsumori_no, 2) end as mitsumori_no
--, uri.mitsumori_no
, 1 as mitsumori_mode
, uri.syori_ymd

--, (case when(sum(jucyu.gokei_uri_su) = '0')then('未納')
--	when(sum(jucyu.gokei_jucyu_su) > sum(jucyu.gokei_uri_su))then('分納')
--	when(sum(jucyu.gokei_jucyu_su) = sum(jucyu.gokei_uri_su))then('完納')
--	else('受注無')end) as jucyu_state

, uri.tok_cd
, uri.tok_nm
, uri.zeinuki_gaku
, uri.zeikomi_gaku
--, uri.payment_term
, uri.biko1
, uri.biko1 as biko1_ex
, uri.biko2
, um2.kingaku as nebiki_gaku
--, uri.seikyu_flg
--, uri.keshi_flg
--, (case when(uri.syukka_flg = 1)then('出荷')else('')end) as syukka_flg
--, (case when(uri.seikyu_flg = 1)then(to_char(to_date(uri.seikyu_ymd,'yyyymmdd'),'mm/dd'))else(null)end) as seikyu_md
--, (case when(uri.keshi_flg = 1)then(to_char(to_date(uri.keshi_ymd,'yyyymmdd'),'mm/dd'))else(null)end) as nyukin_md
--, tk.furikikan
--, h2.koumoku5 as furikikan_s
--, um.hin_nm
, uri.title1
, tan1.tan_nm as tan_nm
, tan2.tan_nm as sekkei_tan_nm
, tan3.tan_nm as eigyou_tan_nm
, uri.nonyu_nm
, h1.han_name as prc_sts
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_tanto tan1 on (tan1.tan_cd = uri.tan_cd)
left join m_tanto tan2 on (tan2.tan_cd = uri.sekkei_tan_cd)
left join m_tanto tan3 on (tan3.tan_cd = uri.eigyou_tan_cd)
--left join t_uri_m um on (um.den_no = uri.den_no and um.row_no = 0)
--inner join t_uri_m um on (um.den_no = uri.den_no and um.orow_no is null)
left join t_uri_m um on (um.den_no = uri.den_no and um.orow_no is null)
left join t_uri_m um2 on (um2.den_no = uri.den_no and um2.orow_no is null and um2.hin_cd = '99')
--left join (select den_no, uri_su as gokei_uri_su, jucyu_su as gokei_jucyu_su from t_uri_m  where orow_no is null) as jucyu on (uri.jucyu_no = jucyu.den_no)
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
left join m_hanyo h3 on (h3.mst_kbn = '99' and h3.han_cd = 'mitsu-eda')
WHERE 1 = 1
and um.orow_no is null
and uri.del_flg = 0
and uri.mitsumori_flg = 1
and uri.syori_ymd != ''
--and ((uri.mitsumori_no like '%:mitsumori_no%') or ('' = ':mitsumori_no'))

--and (upper(replace(replace(replace(uri.mitsumori_no,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:mitsumori_no%',' ',''),'-',''),'　','')) or (':mitsumori_no' = ''))
AND ((fnc_translate_case(uri.mitsumori_no) LIKE '%'||fnc_translate_case(':mitsumori_no')||'%') or (':mitsumori_no' = ''))
--『,.対応』
--and (upper(replace(replace(replace(replace(replace(uri.mitsumori_no,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:mitsumori_no%',',',''),'.',''),' ',''),'-',''),'　','')) or (':mitsumori_no' = ''))

and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and (('' = ':tan_cd') or (uri.tan_cd = ':tan_cd'))
and (('' = ':sekkei_tan_cd') or (uri.sekkei_tan_cd = ':sekkei_tan_cd'))
and (('' = ':eigyou_tan_cd') or (uri.eigyou_tan_cd = ':eigyou_tan_cd'))
and (('' = ':eig_cd') or (uri.eig_cd = ':eig_cd'))
--ユニット名
--and (upper(replace(replace(replace(um.meisai_biko,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:meisai_biko%',' ',''),'-',''),'　','')) or (':meisai_biko' = ''))
AND ((fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':ar_meisai_biko')||'%') or (':ar_meisai_biko' = ''))
AND ((fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':meisai_biko')||'%') or (':meisai_biko' = '') or (fnc_translate_case(um.hosoku2) LIKE '%'||fnc_translate_case(':meisai_biko')||'%') or (':meisai_biko' = ''))
--『,.対応』
--and (upper(replace(replace(replace(replace(replace(um.meisai_biko,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:meisai_biko%',',',''),'.',''),' ',''),'-',''),'　','')) or (':meisai_biko' = ''))

--納入場所
--and (upper(replace(replace(replace(uri.nonyu_nm,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:nonyu_nm%',' ',''),'-',''),'　','')) or (':nonyu_nm' = ''))
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':nonyu_nm')||'%') or (':nonyu_nm' = ''))
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':ar_nonyu_nm')||'%') or (':ar_nonyu_nm' = ''))

--『,.対応』
--and (upper(replace(replace(replace(replace(replace(uri.nonyu_nm,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:nonyu_nm%',',',''),'.',''),' ',''),'-',''),'　','')) or (':nonyu_nm' = ''))

--and (upper(replace(replace(replace(um.hin_nm,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:hin_nm%',' ',''),'-',''),'　','')) or (':hin_nm' = ''))
AND (((um.hin_cd) LIKE '%'||(':hin_cd')||'%') or (':hin_cd' = ''))
AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--『,.対応』
--and (upper(replace(replace(replace(replace(replace(um.hin_nm,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:hin_nm%',',',''),'.',''),' ',''),'-',''),'　','')) or (':hin_nm' = ''))

--and (upper(replace(replace(replace(tk.tok_nm,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:tok_nm%',' ',''),'-',''),'　','')) or (':tok_nm' = ''))
--2018.11.01 MOD STT
--AND ((fnc_translate_case(uri.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(tk.tok_kana) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (':tok_nm' = ''))
AND ((':tok_nm' = '') or (fnc_translate_case(uri.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%'))
--2018.11.01 MOD END
--『,.対応』
--and (upper(replace(replace(replace(replace(replace(tk.tok_nm,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:tok_nm%',',',''),'.',''),' ',''),'-',''),'　','')) or (':tok_nm' = ''))

--and (upper(replace(replace(replace(uri.title1,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:title1%',' ',''),'-',''),'　','')) or (':title1' = ''))
--工事名称
AND ((fnc_translate_case(uri.title1) LIKE '%'||fnc_translate_case(':title1')||'%') or (':title1' = '') or (fnc_translate_case(uri.title2) LIKE '%'||fnc_translate_case(':title1')||'%') or (':title1' = ''))
AND ((fnc_translate_case(uri.title1) LIKE '%'||fnc_translate_case(':ar_title1')||'%') or (':ar_title1' = '') or (fnc_translate_case(uri.title2) LIKE '%'||fnc_translate_case(':ar_title1')||'%') or (':ar_title1' = ''))
--『,.対応』
--and (upper(replace(replace(replace(replace(replace(uri.title1,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:title1%',',',''),'.',''),' ',''),'-',''),'　','')) or (':title1' = ''))

--and ((upper(replace(replace(replace(uri.biko1,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:biko1%',' ',''),'-',''),'　','')) or upper(replace(replace(replace(uri.biko2,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:biko2%',' ',''),'-',''),'　',''))) or (':biko1' = ''))
--備考・特記事項
AND ((fnc_translate_case(uri.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko3) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko4) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko5) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = '') or (fnc_translate_case(uri.biko6) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
AND ((fnc_translate_case(uri.biko1) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko2) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko3) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko4) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko5) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = '') or (fnc_translate_case(uri.biko6) LIKE '%'||fnc_translate_case(':ar_biko1')||'%') or (':ar_biko1' = ''))
--『,.対応』
--and ((upper(replace(replace(replace(replace(replace(uri.biko1,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:biko1%',',',''),'.',''),' ',''),'-',''),'　','')) or upper(replace(replace(replace(replace(replace(uri.biko2,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:biko2%',',',''),'.',''),' ',''),'-',''),'　',''))) or (':biko1' = ''))

--and ((upper(replace(replace(replace(um.hosoku1,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:hosoku1%',' ',''),'-',''),'　','')) or upper(replace(replace(replace(um.hosoku2,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:hosoku1%',' ',''),'-',''),'　',''))) or (':hosoku1' = ''))
--明細備考
AND ((fnc_translate_case(um.hosoku1) LIKE '%'||fnc_translate_case(':ar_hosoku1')||'%') or (':ar_hosoku1' = '') or (fnc_translate_case(um.hosoku2) LIKE '%'||fnc_translate_case(':ar_hosoku1')||'%') or (':ar_hosoku1' = ''))
--『,.対応』
--and ((upper(replace(replace(replace(replace(replace(um.hosoku1,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%[%',',',''),'.',''),' ',''),'-',''),'　','')) or upper(replace(replace(replace(replace(replace(um.hosoku2,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:hosoku1%',',',''),'.',''),' ',''),'-',''),'　',''))) or (':hosoku1' = ''))

--and (upper(replace(replace(replace(uri.biko1,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(uri.biko2,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(uri.biko3,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(uri.biko4,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(uri.title1,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(uri.title2,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(um.hosoku1,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(um.hosoku2,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(tk.tok_nm,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(um.hin_nm,' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace('%:all_serch%',' ',''),'-',''),'　',''))
--or (':all_serch' = ''))
AND ((fnc_translate_case(um.search_text) LIKE '%'||fnc_translate_case(':all_search')||'%') or (':all_search' = ''))

--『,.対応』
--and (upper(replace(replace(replace(replace(replace(uri.biko1,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(uri.biko2,',',''),'.',''),',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(uri.biko3,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(uri.biko4,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(uri.title1,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(uri.title2,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(um.hosoku1,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(um.hosoku2,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(tk.tok_nm,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or upper(replace(replace(replace(replace(replace(um.hin_nm,',',''),'.',''),' ',''),'-',''),'　','')) LIKE upper(replace(replace(replace(replace(replace('%:all_serch%',',',''),'.',''),' ',''),'-',''),'　',''))
--or (':all_serch' = ''))

and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
--and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
--and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
group by 
  uri.den_no
, uri.mitsumori_no
, uri.syori_ymd
, uri.tok_cd
, uri.tok_nm
, uri.zeinuki_gaku
, uri.zeikomi_gaku
, uri.biko1
, uri.biko1 
, uri.biko2
, um2.kingaku 
, uri.title1
, tan1.tan_nm 
, tan2.tan_nm 
, tan3.tan_nm 
, uri.nonyu_nm
, h1.han_name
, h3.han_name
order by uri.syori_ymd desc, uri.den_no desc
limit 10000

[getNyukin]
select nyukin_no as w_nyukin_no
from t_nyu_keshi
where uri_no = :w_upd_key_value
and keshi_kind = 1

[doSeikyu]
update t_uri set
  seikyu_flg = 1
, seikyu_ymd = to_char(now(),'yyyymmdd')
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_upd_key_value
and seikyu_flg = 0

[doNyukin]
update t_uri set
  keshi_flg = 1
, keshi_ymd = to_char(now(),'yyyymmdd')
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_upd_key_value
and keshi_flg = 0


[delete_w0110]
delete from w_basr0110
where s_id = :s_id
;

[chk_counter]
SELECT 
 :s_id
 ,uri.den_no
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
WHERE 1 = 1
and uri.jucyu_flg = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and ((tk.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':sekkei_tan_cd') or (tk.tan_cd = ':sekkei_tan_cd'))
and (('' = ':eigyou_tan_cd') or (tk.tan_cd = ':eigyou_tan_cd'))

;

[insert_w0110]
insert into w_basr0110
(s_id, den_no)
SELECT 
 :s_id
 ,uri.den_no
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
WHERE 1 = 1
and uri.jucyu_flg = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and ((tk.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
;

[output_deliv_csv]
SELECT 
  uri.den_no
, uri.syori_ymd
, uri.prc_sts
, h1.han_name as prc_sts_nm
, uri.tok_cd --住所録ｺｰﾄﾞ（佐川）、お届け先ｺｰﾄﾞ（ヤマト）
, uri.tok_nm
, uri.payment_term
, uri.biko1
, uri.biko1 as biko1_ex
, uri.biko2
, uri.seikyu_flg
, uri.keshi_flg
, um.hin_nm
, uri.nonyu_addr1||uri.nonyu_addr2 as addr1
--, uri.nonyu_addr2 as addr2
, uri.nonyu_addr3 as addr2
, uri.nonyu_addr4 as addr3
, tk.tel_no --お届け先電話番号（共通）
, tk.zip_no --お届け先郵便番号（共通）
, (uv_to_hankaku_kana(tk.tok_kana)) as tok_kana --お届け先ｶﾅ（共通）
, tk.tok_tan --得意先担当者（名称2などがある場合）
, (case when(tk.cat_cd = '2')then('様')
	when(tk.cat_cd = '1')then('御中')
	else(null)end) as keisyou --敬称（共通）
,'075-724-3550' as a_tel --御依頼主電話番号（共通）
,'1' as count --個数口表示（共通）
,'手芸用品' as hinmei --品名（共通でいけるか？）
,null as hinmei2 --品名2（共通でいけるか？）
,null as hinmei3 --品名3（共通でいけるか？）
,null as hinmei4 --品名4（共通でいけるか？）
,null as hinmei5 --品名5（共通でいけるか？）
,null as todokeymd --お届け予定日・配達日（共通）
,null as haitatudt --配達時間帯（共通）
,null as daipay --代引き（共通）
,null as daitax --代引き消費税（共通）
,null as e_code --営業所コード（共通）
,null as s_kban --お客様管理ナンバー（佐川）
,'143368340007' as s_acode --アヴリルのコード（佐川）
,null as s_busho --部署・担当（佐川）
,null as s_teln --御依頼主電話番号（佐川）
,null as s_zipn --御依頼主郵便番号（佐川）
,null as s_add1n --御依頼主住所1（佐川）
,null as s_add2n --御依頼主住所2（佐川）
,null as s_avril1n --御依頼主名1（佐川）
,null as s_avril2n --御依頼主名2（佐川）
,'008' as s_nisu --荷姿（佐川）
,'000' as s_bin_s --便種スピード（佐川）
,'001' as s_bin --便種（佐川）
,null as s_hzikan --配達時間時分（佐川）
,null as s_paytype --決済種別（佐川）
,null as s_hokenm --保険金額（佐川）
,'0' as s_hoken --保険金額印字（佐川）
,null as s_seal1 --シール1（佐川）
,null as s_seal2 --シール2（佐川）
,null as s_seal3 --シール3（佐川）
,null as s_stop --営業所止め置き（佐川）
,null as s_src --src区分（佐川）
,'1' as s_moto --元着区分（佐川）
,null as y_kanri --お客様管理番号（ヤマト）
,'2' as y_syubet --送り状種別（ヤマト）
,null as y_cool --クール区分（ヤマト）
,null as y_denno --伝票番号（ヤマト）
,to_char(current_timestamp + '1 days', 'yyyy/mm/dd') as y_syukaymd --出荷予定日（ヤマト）当日にする
,addr1||(case when(addr2 != '')then(addr2)else('')end)||(case when(addr3 != '')then(addr3)else('')end) as y_addr1 --ヤマトアドレス
,null as y_busho1 --お届け先会社・部門名1
,null as y_busho2 --お届け先会社・部門名2
,'0' as y_iraicode --御依頼主ｺｰﾄﾞ（ヤマト）
,null as y_ateleda --御依頼主電話番号枝番（ヤマト）
,'6068185' as y_azip --御依頼主郵便番号（ヤマト）
,'京都府京都市左京区一乗寺高槻町20-1' as y_a_add --御依頼主住所（ヤマト）
,null as y_a_add1 --御依頼主住所アパートなど（ヤマト）
,'株式会社アヴリル' as y_avril --御依頼主名（ヤマト）
,null as akana --御依頼主カナ（ヤマト）
,null as y_hincd --品名コード（ヤマト）
,null as y_hincd2 --品名コード2（ヤマト）
,'水濡厳禁' as y_niatu1 --荷扱い1（ヤマト）
,'下積厳禁' as y_niatu2 --荷扱い2（ヤマト）
,null as y_paytype --決済種別（ヤマト）
,null as y_kizi --記事（ヤマト）
,'0' as y_stop --営業所止め置き（ヤマト）
,null as y_outp --発行枚数（ヤマト）
,uri.zeikomi_gaku as y_daipay --代引き（共通）
,uri.zei_gaku as y_daitax --代引き消費税（共通）
,'758031520' as y_acode --アヴリルのコード（ヤマト）
,null as j_acode --アヴリルのコード（郵政）
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join t_uri_m um on (um.den_no = uri.den_no and um.row_no = 0)
WHERE 1 = 1
and uri.del_flg = 0
and uri.jucyu_flg = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and ((tk.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
order by uri.syori_ymd desc, uri.den_no desc