[selectRecords]
select
	 (case when(record_ymd != '')then(to_char(to_date(record_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as record_ymd
	,(case when(syori_ymd != '')then(to_char(to_date(syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as syori_ymd
	,(case when('&3' != '')then(to_char(to_date('&3', 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as record_ymd_fr
	,(case when('&4' != '')then(to_char(to_date('&4', 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as record_ymd_to
	,(case when('&20' != '')then(to_char(to_date('&20', 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as output_ymd
	,(row_no + 1) as row_no_s
	, v.*
from(
	select
		  uh.den_no
		, uh.nonyu_nm as tok_tan --相手得意先担当者
		, uh.record_ymd --売上日
		, uh.syori_ymd --請求日
		, uh.tok_nm --得意先
		--, uh.eig_cd --営業所CD
		, eig.han_name as eig_nm --営業所名
		--, uh.chimei1_cd --積地
		--, uh.chimei2_cd --降地
		, uh.chimei1_nm --積地
		, uh.chimei2_nm --降地
		, uh.haiso_kanri_cd --車輛
		, uh.haiso_info1 --車番
		, info2.han_name as haiso_info2 --車種
		, uh.haiso_tan_nm --乗務員
		--, tan.tan_nm as haiso_tan_nm --乗務員
		--, uh.sir_cd --傭車先
		, sir.sir_nm --傭車先
		, tan.tan_nm --入力者
		, uh.biko1 --備考
		, uh.biko2 --備考
		, um.row_no
		, um.hin_nm
		, coalesce(um.kingaku,0) as kingaku
		, coalesce(um.genka1_gaku,0) as genka1_gaku
		, coalesce(um.genka2_gaku,0) as genka2_gaku
	from t_uri uh
	inner join t_uri_m um on (um.den_no = uh.den_no)
	--left join m_tokui tok on (tok.tok_cd = uh.tok_cd)
	--left join m_nonyu non on (non.tok_cd = non.tok_cd and non.nonyu_no::text = uh.haiso_tan_cd) --担当者
	left join m_sir sir on (sir.sir_cd = uh.sir_cd) --傭車先
	left join m_haiso haiso on (haiso.haiso_kanri_cd = uh.haiso_kanri_cd) --配送方法
	left join m_tanto tan on (tan.tan_cd = uh.haiso_tan_cd) --配送担当
	left join m_hanyo eig on (eig.han_cd = uh.eig_cd and eig.mst_kbn = '79') --営業所
	left join m_hanyo info2 on (info2.han_cd = uh.haiso_info2 and info2.mst_kbn = '80') --車種
	WHERE 1 = 1
	and uh.del_flg = 0 and uh.jucyu_flg = 0
	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 (uh.den_no >= fnc_to_number('&16') or '' = '&16') and (uh.den_no <= fnc_to_number('&17') or '' = '&17')
	and (uh.record_ymd >= '&3' or '' = '&3') and (uh.record_ymd <= '&4' or '' = '&4')
)v
--order by v.record_ymd desc,v.syori_ymd desc,den_no
order by v.den_no, v.row_no
;

[SelectRecords_Jisya]
select
	'チェックリスト' as tait
	, 'TEL:' || j_tel as j_tel_s
	, 'FAX:' || j_fax as j_fax_s
	, 'URL:' || j_url as j_url_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
--		,j.koumoku4
		,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