[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
	,sum(kingaku) as zeinuki_gaku 
	,sum(case koumoku3 when '2' then kingaku else 0 end) as component1 
	,sum(case koumoku3 when '1' then genka1_gaku else 0 end) as genka1_gaku 
	,sum(case koumoku3 when '2' then genka1_gaku else 0 end) as component2 
	,sum((kingaku - genka1_gaku)) as saeki
	--,null as component  --差益率
	,v.*
from(
	select
		 uh.sir_cd
		,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--売上日
		,coalesce(tm.kingaku,0) as kingaku 
		,coalesce(tm.genka1_gaku,0) as genka1_gaku 
		,sir.sir_nm--傭車先
		,info2.han_name as haiso_info2--車種
		,han.koumoku3
--		, 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)
	inner 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 (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.sir_cd,sir.sir_nm,tm.record_ymd,uh.haiso_tan_cd,uh.haiso_tan_nm, uh.haiso_kanri_cd,uh.tok_cd,uh.tok_nm,uh.haiso_info1, info2.han_name,uh.chimei1_nm,uh.chimei2_nm 
)v
group by genka1_gaku,koumoku3,kingaku,sir_cd,sir_nm,record_ymd,haiso_tan_cd,haiso_tan_nm,haiso_kanri_cd,tok_cd,tok_nm,haiso_info1,haiso_info2,chimei1_nm,chimei2_nm 
order by v.sir_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 t.syori_ymd >= ''
and t.syori_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