[selectRecords]
select
	'請求明細書' as tait,
	uh.den_no,
	(1+um.row_no) as row_no,
	to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd,
	uh.zeinuki_gaku,
	uh.zei_gaku,
	uh.zeikomi_gaku,
--	um.hin_cd,
	(case when(mh.jan_cd != '')then(mh.jan_cd)else(um.hin_cd)end) as hin_cd,
	replace(um.hin_nm,chr(10),'')as hin_nm,
	(case when(&8 = 1)then(um.jucyu_su)else(um.suryo)end) as suryo,
	um.tanka,
	um.kingaku,
	uh.tok_cd,
	(case coalesce(uh.tok_nm,'')
        when '' then ''
        else uh.tok_nm ||'　'|| (case coalesce(uh.tok_tan,'')
			when '' then (case coalesce(uh.keisyo,'')
			when '' then (case coalesce(t.keisyo,'') when '' then '' else t.keisyo end) 
		else uh.keisyo end)
		else uh.tok_tan ||'　'||(case coalesce(uh.keisyo,'')
			when '' then'' else uh.keisyo end)end)
    end) as tok_nm,
	uh.nonyu_no,
	uh.nonyu_nm,
	(case coalesce(uh.nonyu_nm,'')
		when '' then ''
		else COALESCE(h5.han_name,'納入先:')||uh.nonyu_nm 
	end) as nonyu_nm_s,
--	t.zip_no,
	(case zip_no
		when '' then ''
		else '〒'||left(zip_no,3)||'-'||right(zip_no,4)
	 end) as zip_no
	,uh.nonyu_addr1 as addr1
	,uh.nonyu_addr2 as addr2
	,uh.nonyu_addr3 as addr3
	,uh.nonyu_addr4 as addr4
/*	t.addr1, 得意先マスタの住所を見てるっぽいので一旦廃止
	t.addr2,
	t.addr3,
	t.addr4,
*/	,uh.payment_term as tori_cond,
--	uh.biko1
	replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1
--	, jf.koumoku1 as furikikan1
--	, jf.koumoku2 as furikikan2
--	, jf.koumoku3 as furikikan3
	, trunc(um.row_no / 37) as page_no
--	, j.han_name as jisya_name
--	, j.koumoku1 as jisya_zip_no
--	, j.koumoku2 as jisya_addr1
--	, j.koumoku3 as jisya_addr2
--	, j.koumoku4 as jisya_addr3
--	, j.koumoku5 as jisya_tel_no
--	, j.koumoku6 as jisya_fax_no
--	, j.koumoku7 as jisya_url
--	, j.koumoku8 as jisya_tanto
--	, j.koumoku9 as jisya_yobi
	,mh.tani
from t_uri uh
left join m_tokui t on(uh.tok_cd = t.tok_cd)
left join t_uri_m um on(uh.den_no = um.den_no)
left join m_hin mh on (mh.hin_cd = um.hin_cd)
left join m_hanyo h5 on (h5.mst_kbn = '98' and h5.han_cd = 'r-nonyunm')
--left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya')
--left join m_hanyo jf on (jf.mst_kbn='09' and jf.han_cd='0')
--left join m_hanyo h1 on (h1.mst_kbn = '09' and h1.han_cd = (case when(t.furikikan != '')then(t.furikikan)else(j.koumoku9)end))
where 1 = 1
and uh.jucyu_flg = &8
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((uh.den_no = fnc_to_number('&3')) or ('' = '&3'))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = fnc_to_number('&7')) ))
order by uh.den_no,um.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
	,'EMAIL:' ||j_email as j_email_s
	,w.*
from(
	select
		 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.koumoku10 as j_daihyo
--		,j.koumoku9 as j_daihyo koumoku9の0がなんなのかわからないので救急措置
		,j.koumoku8 as j_email
--		,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
		,m1.koumoku1 as mongon1
		,m1.koumoku2 as mongon2
		,m1.koumoku3 as mongon3
		,m1.koumoku4 as mongon4
		,m1.koumoku5 as mongon5
		/*, '毎度お引き立てくださいましてありがとうございます。' 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')
	left join m_hanyo m1 on (m1.mst_kbn = '90' and m1.han_cd = '&2')
	where 1=1
	and j.mst_kbn = '90'
	and j.han_cd = 'jisya'
)w

[SelectRecords_Furi]
select
	han_name || ' ' || furikikan1 || ' ' || furikikan2 || ' ' || furikikan3 as furikikan
from(
	select 
		mst_kbn,
		han_cd,
		han_name,
		koumoku1 as furikikan1,
		koumoku2 as furikikan2,
		koumoku3 as furikikan3,
		koumoku4,
		koumoku5,
		koumoku6,
		koumoku7,
		koumoku8,
		koumoku9,
		koumoku10,
		sort_key
	from m_hanyo
	where 1=1
	and mst_kbn = '09'
	and coalesce(koumoku6,'') = ''
)w
order by han_cd