[selectRecords]
select
	to_char(to_date(record_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as record_ymd
	,'作成日:'||to_char(to_date('&20', 'YYYYMMDD'), 'yyyy/mm/dd') as output_ymd_s
	,to_char(to_date('&3', 'YYYYMMDD'), 'yyyy/mm/dd') as record_ymd_fr
	,to_char(to_date('&4', 'YYYYMMDD'), 'yyyy/mm/dd') as record_ymd_to
	,v.*
from(
	select 
		 tm.record_ymd --売上日
--		,uh.sir_cd, sir.sir_nm --傭車先
--		, t.eig_cd, eig.han_name as eig_nm --営業所名
		, uh.haiso_tan_cd, uh.haiso_tan_nm --乗務員
		, lpad(coalesce(uh.haiso_kanri_cd,''), 3,' ') as haiso_kanri_cd --車輛
		,uh.tok_cd,uh.tok_nm,uh.haiso_info1 --得意先,車番
		, info2.han_name as haiso_info2 --車種
		,uh.chimei1_nm,uh.chimei2_nm --積地,降地
		,sum(case han.koumoku4 when '1' then tm.kingaku else 0 end) as zeinuki_gaku --運賃収入
		,sum(case han.koumoku4 when '2' then tm.kingaku else 0 end) as ryuti_gaku --留置料
		,sum(case han.koumoku4 when '3' then tm.kingaku else 0 end) as component2 --通行料
		,sum(case han.koumoku4 when '4' then tm.kingaku else 0 end) as syo_gaku --諸経費
		,sum(tm.kingaku) as kingaku --運賃合計
		,sum(tm.genka1_gaku) as genka1_gaku --傭車料
		,sum(tm.genka2_gaku) as genka2_gaku --社内運賃
		--,coalesce(tm.kingaku,0) as kingaku --運賃収入
		--,null as component --高速代 TODO
		--,coalesce(tm.genka1_gaku,0) as genka1_gaku --傭車料
		--,coalesce((case hin.zei_kbn when 2 then tm.kingaku else 0 end),0) as component --通行料
		--,coalesce(tm.kingaku,0) - coalesce(tm.genka1_gaku,0) as saeki --差益
		--,null as component  --差益率
		--,han.koumoku3,han.koumoku4
	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 (tm.record_ymd >= '&3' or '' = '&3') and (tm.record_ymd <= '&4' or '' = '&4')
	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')
	group by 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
order by lpad(coalesce(v.haiso_kanri_cd,''), 3,' '),coalesce(v.haiso_info1,''),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