[selectRecords]
select
	trunc((row_no / 20)) as row_page,--切り上げ
	'お客様コード：'|| sir_cd as sir_cd_s,
	(case coalesce(sir_nm,'') 
		when '' then '' 
		else sir_nm ||'　'|| (case coalesce(sir_tan,'') when '' then (case coalesce(keisyo,'') when '' then '様' else keisyo end) else '' end) end)||'　'|| (case coalesce(sir_tan,'') when '' then '' else sir_tan ||'　様' 
	end) as sir_nm_ss,
	(case coalesce(nonyu_tan,'') 
		when '' then '' 
		else nonyu_tan ||'　様' 
	end) as nonyu_tan_s,
	'担当者：'||coalesce(utt_tan_nm,'')|| (case 
		when (coalesce(utt_tan_cd,'') = coalesce(sh_tan_cd,'')) then '' 
		when (coalesce(sh_tan_cd,'') = '') then '' 
		else '(入力：'||coalesce(st_tan_nm,'') ||')' 
	end) as tan_nm_s,
	to_char(to_date(input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	(case syori_ymd
		when '' then ''
		else to_char(to_date(syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end) as syori_ymd,
	(case nyuka_yotei_ymd
		when '' then ''
		else to_char(to_date(nyuka_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end) as nouki_ymd,
	(case hacyu_su 
		when 0 then NULL 
		else hacyu_su 
	end ) as hacyu_su,
	(case hacyu_su 
		when 0 then NULL 
		else (case tanka when 0 then NULL else tanka end )
	end) as tanka,
	(case hacyu_su 
		when 0 then NULL 
		else (case kingaku when 0 then NULL else kingaku end )
	end) as kingaku,
	(case hacyu_su 
		when 0 then NULL 
		else tani
	end)as tani,
	v.*
from(
	select
		--sh
		sh.den_no,
		sh.sir_cd,
		sh.sir_nm,
		sh.sir_tan,
		sh.syori_ymd,
		sh.nyuka_yotei_ymd,
		sh.tan_cd as sh_tan_cd,
		sh.input_ymd,
		replace(replace(sh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as tekiyo,
		sh.biko2,
		coalesce(sh.zeinuki_gaku,0) as zeinuki_gaku,
		coalesce(sh.zei_gaku,0) as zei_gaku,
		coalesce(sh.zeikomi_gaku,0) as zeikomi_gaku,
		--sm
		sm.row_no as row_no,
		sm.jucyu_no,
		coalesce(sm.hacyu_su,0) as hacyu_su,
		coalesce(sm.tanka,0) as tanka,
		coalesce(sm.kingaku,0) as kingaku,
		sm.hin_cd,
		replace(sm.hin_nm,chr(10),'') as hin_nm,
		sm.hacyu_no,
		sm.sir_su,
		sm.tani,
		sm.meisai_biko,
		--uh
		uh.nonyu_no,
		uh.nonyu_nm,
		uh.nonyu_tan,
		uh.tok_cd,
		uh.tok_nm,
		uh.nonyu_tel_no as nonyu_tel_no,
		--ms
		ms.keisyo,
		--st
		st.tan_nm as st_tan_nm,
		--ut
		ut.tan_nm as ut_tan_nm,
		--utt
		utt.tan_nm as utt_tan_nm,
		utt.tan_cd as utt_tan_cd,
		--loc
		loc.han_name as loc_nm
	from t_sir sh
	left join t_sir_m sm on(sh.den_no = sm.den_no)
	left join t_uri uh on(sm.jucyu_no = uh.den_no)
	left join m_sir ms on(sh.sir_cd = ms.sir_cd)
	left join m_tanto st on(sh.tan_cd = st.tan_cd)
	left join m_tanto ut on(uh.eigyou_tan_cd = ut.tan_cd)
	left join m_tokui mt on(uh.tok_cd = mt.tok_cd)
	left join m_tanto utt on(mt.tan_cd = utt.tan_cd)
	left join v_hanyo_all loc on (loc.mst_kbn = 'loc' and loc.han_cd = sh.loc_no )
	where 1 = 1
	and sh.hacyu_flg = '1'
	and ((cast(sh.den_no as char(6)) = '&3') or ('&3' = ''))
	and ((sh.sir_cd = '&4') or ('&4' = ''))
	and ((sh.syori_ymd >= '&1') or ('&1' = '')) and ((sh.syori_ymd <= '&2') or ('&2' = ''))
)v
order by v.den_no,v.sir_cd,v.row_no

[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