[selectRecords]

select
	--to_char(to_date(record_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as record_ymd
	--, to_char(to_date(syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd
	--, 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
	tan.tan_cd as haiso_tan_cd
	,max(tan.tan_nm) as haiso_tan_nm
	--, max(v.haiso_tan_nm) as haiso_tan_nm
	, sum(coalesce(v.uriage,0)) as uriage --売上
	, max(coalesce(v.keihi1,0)) as keihi1
	, max(coalesce(v.keihi2,0)) as keihi2
	, max(coalesce(v.keihi3,0)) as keihi3
	, max(coalesce(v.keihi4,0)) as keihi4
	, max(coalesce(v.keihi5,0)) as keihi5
	, max(coalesce(v.keihi6,0)) as keihi6
	, max(coalesce(v.keihi7,0)) as keihi7
	, max(coalesce(v.keihi8,0)) as keihi8
	, max(coalesce(v.keihi9,0)) as keihi9
	, max(coalesce(v.keihi10,0)) as keihi10
	, max(coalesce(v.keihi11,0)) as keihi11
	, max(coalesce(v.keihi12,0)) as keihi12
	, max(coalesce(v.keihi1,0)) + max(coalesce(v.keihi2,0)) + max(coalesce(v.keihi3,0)) + max(coalesce(v.keihi4,0)) + max(coalesce(v.keihi5,0)) + max(coalesce(v.keihi6,0)) + max(coalesce(v.keihi7,0)) + max(coalesce(v.keihi8,0)) + max(coalesce(v.keihi9,0)) + max(coalesce(v.keihi10,0)) + max(coalesce(v.keihi11,0)) + max(coalesce(v.keihi12,0)) as gokei_keihi
	, to_char(to_date('&20', 'YYYYMMDD'), 'yyyy/mm/dd') as output_ymd
	--, v.*
	
from m_tanto tan 
left join 
(
	select
		--um.record_ymd --売上日
		uh.haiso_tan_cd
		, uh.haiso_tan_nm --乗務員
		, null as kousu_keisu --率
		--, um.genka1_gaku
		, sum(um.genka2_gaku) as uriage --売上
		, null as keihi1
		, null as keihi2
		, null as keihi3
		, null as keihi4
		, null as keihi5
		, null as keihi6
		, null as keihi7
		, null as keihi8
		, null as keihi9
		, null as keihi10
		, null as keihi11
		, null as keihi12
	from t_uri uh
	inner join t_uri_m um on (um.den_no = uh.den_no)
	WHERE 1 = 1
	and (um.record_ymd >= '&16' or '' = '&16') and (um.record_ymd <= '&17' or '' = '&17')
--	and (uh.haiso_tan_cd >= '&24' or '' = '&24') and (uh.haiso_tan_cd <= '&25' or '' = '&25')
	group by uh.haiso_tan_cd, uh.haiso_tan_nm
	union all select 
		sh.tan_cd
		, '' as haiso_tan_nm --乗務員
		, null as kousu_keisu --率
		, null as uriage
		, sum(case sm.hin_cd when '900' then sm.kingaku else null end) as keihi1
		, sum(case sm.hin_cd when '901' then sm.kingaku else null end) as keihi2
		, sum(case sm.hin_cd when '902' then sm.kingaku else null end) as keihi3
		, sum(case sm.hin_cd when '903' then sm.kingaku else null end) as keihi4
		, sum(case sm.hin_cd when '904' then sm.kingaku else null end) as keihi5
		, sum(case sm.hin_cd when '905' then sm.kingaku else null end) as keihi6
		, sum(case sm.hin_cd when '906' then sm.kingaku else null end) as keihi7
		, sum(case sm.hin_cd when '907' then sm.kingaku else null end) as keihi8
		, sum(case sm.hin_cd when '908' then sm.kingaku else null end) as keihi9
		, sum(case sm.hin_cd when '909' then sm.kingaku else null end) as keihi10
		, sum(case sm.hin_cd when '910' then sm.kingaku else null end) as keihi11
		, sum(case sm.hin_cd when '911' then sm.kingaku else null end) as keihi12
	from t_sir sh
	inner join t_sir_m sm on (sm.den_no = sh.den_no)
	where 1=1
--	and sh.sir_cd = '99999' --TODO 経費仕入先
--	and (sm.hin_cd >= '900' and sm.hin_cd <= '920') --TODO 経費品番
	and (sh.syori_ymd >= '&16' or '' = '&16') and (sh.syori_ymd <= '&17' or '' = '&17')
--	and (sh.tan_cd >= '&24' or '' = '&24') and (sh.tan_cd <= '&25' or '' = '&25')
	group by sh.tan_cd
	) v on tan.tan_cd = v.haiso_tan_cd
group by tan.sort_key, tan.tan_cd
order by tan.sort_key, tan.tan_cd
--	
	/*
	900	燃料費
901	修理費
902	タイヤ・幌
903	高速プレート
904	保険
905	社保
906	車輛使用
907	事故
908	高速フェリー
909	その他２
910	運行費
911	その他

	and (uh.record_ymd >= '&3' or '' = '&3') and (uh.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.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')
	*/

[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