[selectRecords]
select
	 '作成日:'||(case when('&22' != '')then(to_char(to_date('&22', 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as output_ymd_s
	,'対象年月：'||(case when('&4' != '')then(to_char(to_date('&3', 'YYYYMMDD'), 'yyyy年mm月'))else('')end) as taisyo_ym_s
	, v.*
from(
	select
		--uh.den_no,
		uh.sir_cd,
		sir.sir_nm,
		uh.eig_cd,
		eig.han_name as eig_nm ,--営業所名
		sum(coalesce(tm.kingaku, 0)) as zeinuki_gaku ,--運賃収入
		sum(coalesce(tm.genka1_gaku, 0)) as genka1_gaku ,--傭車料
		sum(coalesce(tm.kingaku, 0)) - sum(coalesce(tm.genka1_gaku, 0)) as saeki,--差益
		null as component,--構成比
		fnc_get_zei(sum(coalesce(tm.genka1_gaku, 0)), min(uh.syori_ymd)) as zei--消費税
	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)--傭車先
--	inner join m_sir sir on (uh.sir_cd = sir.sir_cd)
	left join m_hanyo eig on (eig.mst_kbn = '79' and eig.han_cd = uh.eig_cd)
	where 1=1
	and uh.del_flg = 0 and uh.jucyu_flg = 0
	and (uh.record_ymd >= '&3' or '' = '&3') and (uh.record_ymd <= '&4' or '' = '&4')
	and (uh.prc_sts = '&7' or '' = '&7')
	--and (uh.tok_cd >= '&8' or '' = '&8') and (uh.tok_cd <= '&9' or '' = '&9')
	and (uh.nonyu_no >= fnc_to_number('&10') or '' = '&10') and (uh.nonyu_no <= fnc_to_number('&11') or '' = '&11')
	and (uh.haiso_tan_cd >= '&12' or '' = '&12') and (uh.haiso_tan_cd <= '&13' or '' = '&13')
	--and (uh.den_no >= fnc_to_number('&16') or '' = '&16') and (uh.den_no <= fnc_to_number('&17') or '' = '&17')
	and (uh.sir_cd >= '&23' or '' = '&23') and (uh.sir_cd <= '&24' or '' = '&24')
	and (uh.eig_cd >= '&25' or '' = '&25') and (uh.eig_cd <= '&26' or '' = '&26')
	and (uh.sir_cd != '') --傭車先で集計できないデータは除外
	group by uh.sir_cd, sir.sir_nm, uh.eig_cd,eig.han_name
)v
order by sir_cd, eig_cd

[selectRecords_old]
select --t.den_no, 
 t.sir_cd, sir.sir_nm, t.eig_cd, eig.han_name as eig_nm --営業所名
,sum(coalesce(tm.kingaku,0)) as zeinuki_gaku --運賃収入
,sum(coalesce(tm.genka1_gaku,0)) as genka1_gaku --庸車
,sum(coalesce(tm.kingaku,0)) - sum(coalesce(tm.genka1_gaku,0)) as genka1_gaku --差益
,null as component --構成比
,fnc_get_zei(sum(coalesce(tm.genka1_gaku,0)), min(t.syori_ymd)) as zei --消費税
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
group by t.sir_cd,sir.sir_nm,t.eig_cd
and t.syori_ymd >= ''
and t.syori_ymd <= ''
and t.sir_cd >= ''
and t.sir_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