[selectRecords]
select
	trunc((uri_row)/ 29) + 1 as uri_row_kai
	,'page:'||trunc((uri_row)/ 29) + 1 as uri_row_kai_s
	,(case 
		when syori_ymd = '' then '日付を入力してください'
		else to_char(to_date(syori_ymd,'yyyymmdd'),'yyyy/mm/dd')
	end) as u_ymd
	,'お客様コード：'||tokui_cd as tokui_cd_s
	,(case  coalesce(tokui_nm,'')
		when '' then ''
		else tokui_nm ||'　'|| (case coalesce(keisyo,'') when '' then '様' else keisyo end)
	end) as tokui_nm_s
	,'現場：'||nonyu_nm as nonyu_nm_s
	,'担当：'||tan_nm as tan_nm_s
	,(case zip_no
		when '' then ''
		else left(zip_no,3)||'-'||right(zip_no,4)
	 end) as post_n
	 ,coalesce(nonyu_no::varchar,'')||coalesce(nonyu_nm,'') as concat_nonyu
	 ,(case when uri_s is null then null else uri_g end) as uri_g
	,w.*
from(
	select
		--uh
		uh.den_no as uri_no,
		uh.jucyu_no as jyucyu,
		uh.syori_ymd as syori_ymd,
		uh.tok_cd as tokui_cd,
		uh.zeinuki_gaku as zn_gaku,
		(case mt.zei_calc_kbn when '1' then null else uh.zei_gaku end) as z_gaku,
		(case mt.zei_calc_kbn when '1' then uh.zeinuki_gaku else uh.zeikomi_gaku end) as zi_gaku,
		--uh.zei_gaku as z_gaku,
		--uh.zeikomi_gaku as zi_gaku,
		mt.zei_calc_kbn,
		uh.seikyu_ymd as s_ymd,
		uh.tok_nm as tokui_nm,
		uh.nonyu_no,
		uh.nonyu_nm,
		replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1,
		--um
		um.row_no as uri_row,
		um.jucyu_no as jyucyum,
		um.jucyu_row_no as jyucyu_row,
		um.tani as tani,
		um.suryo as uri_s,
		um.tanka as uri_t,
		um.kingaku as uri_g,
		um.hin_nm,
		um.meisai_biko,
		--mt
		mt.tok_kana as tokui_kana,
		mt.keisyo as keisyo,
		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		--tan
		tan.tan_nm
	from t_uri uh
	left join t_uri_m um on (uh.den_no = um.den_no)
	left join m_tokui mt on (uh.tok_cd = mt.tok_cd)
--	left join m_tanto tan on (uh.tan_cd = tan.tan_cd)
	left join m_tanto tan on (uh.eigyou_tan_cd = tan.tan_cd)
--	left join m_hanyo han on (han.mst_kbn = '01' and han.han_cd = uh.prc_sts)
	where 1=1
--	and coalesce(han.koumoku3, '') = ''
	and uh.del_flg = 0 and jucyu_flg = 0
	and (mt.shime_dd = fnc_to_number('&2') or '' = '&2') --締日
	and (uh.prc_sts = '&5' or '' = '&5')
	and (uh.tok_cd >= '&6' or '' = '&6') and (uh.tok_cd <= '&7' or '' = '&7')
	and (uh.nonyu_no >= fnc_to_number('&8') or '' = '&8') and (uh.nonyu_no <= fnc_to_number('&9') or '' = '&9')
	and (uh.eigyou_tan_cd >= '&10' or '' = '&10') and (uh.eigyou_tan_cd <= '&11' or '' = '&11')
	and (um.hin_cd >= '&12' or '' = '&12') and (um.hin_cd <= '&13' or '' = '&13')
--	and (hin.cat_cd >= '&14' or '' = '&14') and (hin.cat_cd <= '&15' or '' = '&15')
	and (uh.den_no >= fnc_to_number('&16') or '' = '&16') and (uh.den_no <= fnc_to_number('&17') or '' = '&17')
	and ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case('&18')||'%') or ('&18' = ''))
	and (uh.syori_ymd >= '&3' or '' = '&3') and (uh.syori_ymd <= '&4' or '' = '&4')
)w
order by tokui_cd,nonyu_no,syori_ymd,uri_no,uri_row
;

[selectRecords_old]
select
	w.*
	,trunc((uri_row)/ 29) + 1 as uri_row_kai
	,'page:'||trunc((uri_row)/ 29) + 1 as uri_row_kai_s
	,(case 
		when syori_ymd = '' then '日付を入力してください'
		else to_char(to_date(syori_ymd,'yyyymmdd'),'yyyy/mm/dd')
	end) as u_ymd
	,'お客様コード：'||tokui_cd as tokui_cd_s
	,(case  coalesce(tokui_nm,'')
		when '' then ''
		else tokui_nm ||'　'|| (case coalesce(keisyo,'') when '' then '様' else keisyo end)
	end) as tokui_nm_s
	,'現場：'||nonyu_nm as nonyu_nm_s
	,'担当：'||tan_nm as tan_nm_s
	,(case zip_no
		when '' then ''
		else left(zip_no,3)||'-'||right(zip_no,4)
	 end) as post_n
	, 'TEL：'||koumoku5 as tel_no
	, 'FAX：'||koumoku6 as fax_no
from(
	select 
		uh.den_no as uri_no,
		um.row_no as uri_row,
		uh.jucyu_no as jyucyu,
		um.jucyu_no as jyucyum,
		um.jucyu_row_no as jyucyu_row,
		uh.syori_ymd as syori_ymd,
		uh.tok_cd as tokui_cd,
		um.tani as tani,
		um.suryo as uri_s,
		--um.jucyu_su as uri_s,
		um.tanka as uri_t,
		um.kingaku as uri_g,
		uh.zeinuki_gaku as zn_gaku,
		uh.zei_gaku as z_gaku,
		uh.zeikomi_gaku as zi_gaku,
		uh.seikyu_ymd as s_ymd,
		uh.tok_nm as tokui_nm,
		mt.tok_kana as tokui_kana,
		mt.keisyo as keisyo,
		uh.nonyu_no,
		uh.nonyu_nm,
		tan.tan_nm,
		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		um.hin_nm,
		um.meisai_biko,
		replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1
		, j.mst_kbn
		, j.han_cd
		, j.han_name
		, j.koumoku1
		, j.koumoku2
		, j.koumoku3
		, j.koumoku4
		, j.koumoku5
		, j.koumoku6
		, j.koumoku7
		, j.koumoku8
		, j.koumoku9
	from t_uri uh
	left join t_uri_m um on (uh.den_no = um.den_no)
	left join m_tokui mt on (uh.tok_cd = mt.tok_cd)
	left join m_tanto tan on (uh.tan_cd = tan.tan_cd)
	left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya')
	left join m_hanyo cat on (cat.mst_kbn = '35' and cat.han_cd = mt.cat_cd)
	where 1=1
	and uh.jucyu_flg = 0
	and uh.del_flg = 0
	and (mt.shime_dd = fnc_to_number('&2') or '' = '&2') --締日
	and ((uh.syori_ymd >= '&3') or ('&3' = '')) and ((uh.syori_ymd <= '&4') or ('&4' = ''))
	and (uh.den_no >= fnc_to_number('&16') or '' = '&16') and (uh.den_no <= fnc_to_number('&17') or '' = '&17')
	and (mt.tok_cd >= '&6' or '' = '&6') and (mt.tok_cd <= '&7' or '' = '&7')
	and (um.hin_cd >= '&12' or '' = '&12') and (um.hin_cd <= '&13' or '' = '&13')
)w
order by uri_no,tokui_cd,u_ymd,uri_row
;

[SelectRecords_Jisya]
select
	 'TEL:' || j_tel as j_tel_s
	,'FAX:' || j_fax as j_fax_s
	,(case j_url
		when '' then ''
		else 'URL:' || j_url
	end) as j_url_s
	--,'URL:' || j_url as j_url_s
	,(case j_daihyo
		when '' then ''
		else '担当者:' || j_daihyo
	end) as j_daihyo_s
	,w.*
from(
	select
		'発注書' as tait
		,j.mst_kbn
		,j.han_cd
		,j.han_name as j_nm
		,j.koumoku1 as j_post_s
		,j.koumoku2 as j_address
		,j.koumoku3 as j_address2
		,j.koumoku4 as j_address3
		,j.koumoku5 as j_tel
		,j.koumoku6 as j_fax
		,j.koumoku7 as j_url
		,j.koumoku8 as j_daihyo
--		,j.koumoku9
--		,j.koumoku10
		,j.sort_key
		,j2.mst_kbn
		,j2.han_cd
		,j2.han_name
		,j2.koumoku1 as koumoku1
		,j2.koumoku2 as koumoku2
		,j2.koumoku3 as koumoku3
		,j2.koumoku4 as koumoku4
		,j2.koumoku5 as koumoku5
		,j2.koumoku6 as koumoku6
		,j2.koumoku7 as koumoku7
		,j2.koumoku8 as koumoku8
		,j2.koumoku9 as koumoku9
		,j2.koumoku10 as koumoku10
		, '下記のとおり、納品致しました。' as mongon1
		, '' as mongon2
		, '' as mongon3
		, '' as mongon4
		, '' as mongon5
	from m_hanyo j
	left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'jisya2')
	where 1=1
	and j.mst_kbn = '90'
	and j.han_cd = 'jisya'
)w