[selectRecords]
select
	 (case when(record_ymd != '')then(to_char(to_date(record_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as record_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_s
--	,null as component  --差益率
	,v.*
from(
	select
		 uh.haiso_tan_cd--乗務員
		,uh.haiso_tan_nm 
		,uh.haiso_kanri_cd--車輛
		,uh.tok_cd--得意先
		,uh.tok_nm
		,uh.haiso_info1--車番
		,uh.chimei1_nm--積地
		,uh.chimei2_nm--降地
		,tm.record_ymd--売上日
		,sum(coalesce(tm.kingaku,0)) as zeinuki_gaku --運賃収入
		,sum(case han.koumoku3 when '2' then coalesce(tm.kingaku,0) else 0 end) as component2 --高速代 兼通行料
		,sum(case han.koumoku3 when '1' then coalesce(tm.genka1_gaku,0) else 0 end) as genka1_gaku --傭車料
		--,sum(case han.koumoku3 when '2' then coalesce(tm.genka1_gaku,0) else 0 end) as component2 --通行料
		,sum(tm.kingaku - tm.genka1_gaku) as saeki --差益
		--,uh.sir_cd,sir.sir_nm--傭車先
		,info2.han_name as haiso_info2--車種
--		, t.eig_cd, eig.han_name as eig_nm --営業所名
	from t_uri uh
	inner join t_uri_m tm on (uh.den_no = tm.den_no)
	--left join m_sir sir on (uh.sir_cd = sir.sir_cd)--傭車先
	left join m_hanyo info2 on (info2.han_cd = uh.haiso_info2 and info2.mst_kbn = '80') --車種
	inner join m_hin hin on (hin.hin_cd = tm.hin_cd) --種別
	inner join m_hanyo han on (hin.cat_cd = han.han_cd and han.mst_kbn='05') --種別
	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.haiso_tan_cd >= '&10' or '' = '&10') and (uh.haiso_tan_cd <= '&11' or '' = '&11')
	and coalesce(uh.haiso_tan_cd, '') != '' 
	and (uh.den_no >= fnc_to_number('&16') or '' = '&16') and (uh.den_no <= fnc_to_number('&17') or '' = '&17')
	and (tm.record_ymd >= '&3' or '' = '&3') and (tm.record_ymd <= '&4' or '' = '&4')
	group by uh.haiso_tan_cd,uh.haiso_tan_nm,uh.haiso_kanri_cd,uh.tok_cd,uh.tok_nm
		,uh.haiso_info1,uh.chimei1_nm,uh.chimei2_nm,tm.record_ymd,info2.han_name
)v
order by v.haiso_tan_cd,v.record_ymd,v.tok_cd,v.haiso_info1

[selectRecords_old]
select 
 t.sir_cd, sir.sir_nm,tm.record_ymd --傭車先,売上日 
--, t.eig_cd, eig.han_name as eig_nm --営業所名
,t.tok_cd,t.tok_nm,t.haiso_kanri_cd --得意先,車番
,t.chimei1_nm,t.chimei2_nm --積地,降地
,coalesce(tm.kingaku,0) as zeinuki_gaku --運賃収入
,null as component --高速代 TODO
,coalesce(tm.genka1_gaku,0) as genka1_gaku --傭車
,null as component --通行料 TODO
,coalesce(tm.kingaku,0) - coalesce(tm.genka1_gaku,0) as saeki --差益
,null as component --差益率
from t_uri t
inner join t_uri_m tm on t.den_no = tm.den_no 
inner join m_sir sir on t.sir_cd = sir.sir_cd
--left join m_hanyo eig on (eig.mst_kbn = '79' and eig.han_cd = t.eig_cd)
where t.del_flg=0 and t.jucyu_flg=0
and tm.record_ymd >= ''
and tm.record_ymd <= ''
and t.sir_cd >= ''
and t.sir_cd <= ''
and t.tok_cd >= ''
and t.tok_cd <= ''
order by t.sir_cd, t.eig_cd
;

[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