[selectRecords]
select
	to_char(to_date(w.seikyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as seikyu_ymd_s,
	substr(w.seikyu_ymd,5,2)||'月分' as gatsubun,
	'お客様コード:' || v.tok_cd as tok_cd_s,
	'担当:' || tan_nm as tan_nm_s,
	(case coalesce(tok_nm,'')
		when '' then ''
		else tok_nm ||'　'|| (case coalesce(keisyo,'') when '' then '御中' else keisyo end)
	end) as tok_nm_s,
	'〒'||substr(replace(zip_no,'-',''),1,3)||'-'||substr(replace(zip_no,'-',''),4,6) as yubin_no,
	addr1 as address1 ,
	addr2 as address2 ,
	addr3||addr4 as address3 ,
	to_char(to_date(den_dt, 'YYYYMMDD'), 'mm/dd') as den_dt ,
	concat(coalesce(('' || nonyu_no),''),nonyu_nm) as concat_nonyu,
	cast(suryo as varchar) as suryo ,
	coalesce(zeinuki_gaku,0) as zeinuki_gaku ,
	coalesce(zei_gaku,0) as zei_gaku ,
	coalesce(zeikomi_gaku,0) as zeikomi_gaku ,
	coalesce(tok_tan, '') as tok_tan ,
	coalesce(freight,0) as freight,
	coalesce(toll,0) as toll,
	coalesce(kazei_gaku,0) as kazei_gaku,
	(coalesce(zeinuki_gaku,0) - coalesce(kazei_gaku,0) - coalesce(uchi_gaku,0)) as hikazei_gaku,
	v.*
from w_basr0030 w
inner join(
	select
		--t_urikake
		v.seikyu_ymd ,
		v.tok_cd ,
		v.zen_seikyu_ymd ,
		v.zen_seikyu_zan ,
		v.nyukin_gaku ,
		v.kurikoshi_gaku ,
		v.zeinuki_gaku ,
		v.zei_gaku ,
		v.zeikomi_gaku ,
		v.seikyu_zan ,
		v.kazei_gaku,
		v.uchi_gaku,
		v.den_cnt ,
		--t_uri
		uh.syori_ymd,
		uh.den_no as den_no ,
		uh.zei_kbn,
		(case uh.henpin_flg when '1' then '返品' else '売上' end) as kbn ,
		(case hin.inner_flg when 1 then uh.chimei2_nm else uh.chimei1_nm end) as chimei1_nm, --積地
		(case hin.inner_flg when 1 then uh.chimei1_nm else uh.chimei2_nm end) as chimei2_nm, --降地
		uh.haiso_info1, --車番
		uh.biko1,
		uh.biko2 as biko,
        uh.nonyu_no,
        uh.nonyu_nm,
		--t_uri_m
		um.hin_nm as hin_nm ,
		um.suryo,
		um.tanka as tanka ,
		um.kingaku as kingaku ,
		um.row_no as m_row ,
		um.tani ,
		um.meisai_biko ,
		(case when hin.zei_kbn = '2' then null else um.kingaku end) as freight, 
		(case when hin.zei_kbn = '2' then um.kingaku else null end) as toll, 
		um.zei_kbn,
		(case um.record_ymd when '' then uh.record_ymd else um.record_ymd end) as den_dt,
		--m_hin
		hin.zei_kbn,
		--m_tokui
		mt.tok_nm ,
		mt.tel_no ,
		mt.tok_tan ,
		mt.cat_cd,
		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		mt.addr4,
		mt.keisyo,
		--info2
		info2.han_name as haiso_info2, --車種
		--m_tanto
		ta.tan_nm ,
		1 as sort
	from t_urikake v
	left join t_uri uh on (v.seikyu_ymd = uh.seikyu_ymd and v.tok_cd = uh.tok_cd and uh.jucyu_flg = 0)
	left join t_uri_m um on (uh.den_no = um.den_no)
	left join m_hin hin on (um.hin_cd = hin.hin_cd)
	left join m_hanyo info2 on (info2.han_cd = uh.haiso_info2 and info2.mst_kbn = '80') --車種
	left join m_tokui mt on (mt.tok_cd = v.tok_cd)
	left join m_tanto ta on (mt.tan_cd = ta.tan_cd)
	/*入金無し
	union all select
		--t_urikake
		v.seikyu_ymd ,
		v.tok_cd ,
		v.zen_seikyu_ymd ,
		v.zen_seikyu_zan ,
		v.nyukin_gaku ,
		v.kurikoshi_gaku ,
		v.zeinuki_gaku ,
		v.zei_gaku ,
		v.zeikomi_gaku ,
		v.seikyu_zan ,
		v.kazei_gaku,
		v.uchi_gaku,
		v.den_cnt ,
		--t_nyukin
		n.nyukin_ymd as den_dt,
		n.nyukin_no as den_no ,
		null ,
		'入金' as kbn ,
		null as chimei1_nm, --積地
		null as chimei2_nm, --降地
		null as haiso_info1, --車番
		null as haiso_info2, --車種
		null as biko,
		'(' ||han .han_name||')' as hin_nm ,
		null as suryo ,
		null as tanka ,
		n.total_gaku as kingaku ,
		null as m_row ,
		'' ,
		n.biko ,
		null as freight, --運賃
		null as toll, --通行料
		null,
		--m_hin
		null,
		--m_tokui
		mt.tok_nm ,
		mt.tel_no ,
		mt.cat_cd,
		mt.tok_tan ,
		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		mt.addr4,
		mt.keisyo,
		--m_tanto
		ta.tan_nm as tan_nm ,
		1 as sort
	from t_urikake v
	left join t_nyukin n on (v.seikyu_ymd = n.seikyu_ymd and v.tok_cd = n.tok_cd)
	left join m_hanyo han on (n.nyukin_kbn = han.han_cd and han.mst_kbn = '13')
	left join m_tokui mt on (mt.tok_cd = v.tok_cd)
	left join m_tanto ta on (mt.tan_cd = ta.tan_cd)
	where 1 = 1
	and n.nyukin_gaku is not null
	*/
	/*売上計無し
	union all select 
		--t_urikake
		v.seikyu_ymd ,
		v.tok_cd ,
		v.zen_seikyu_ymd ,
		v.zen_seikyu_zan ,
		v.nyukin_gaku ,
		v.kurikoshi_gaku ,
		v.zeinuki_gaku ,
		v.zei_gaku ,
		v.zeikomi_gaku ,
		v.seikyu_zan ,
		v.kazei_gaku,
		v.uchi_gaku,
		v.den_cnt ,
		null ,
		null ,
		null ,
		'' ,
		null as chimei1_nm, --積地
		null as chimei2_nm, --降地
		null as haiso_info1, --車番
		null as haiso_info2, --車種
		null as biko,
		'【売上計】' ,
		null ,
		null ,
		v.zeikomi_gaku as kingaku ,
		null as m_row ,
		'' ,
		'' ,
		null as freight, --運賃
		null as toll, --通行料
		null,
		--m_hin
		null,
		--m_tokui
		mt.tok_nm ,
		mt.tel_no ,
		mt.tok_tan ,
		mt.cat_cd,
		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		mt.addr4,
		mt.keisyo,
		--m_tanto
		ta.tan_nm as tan_nm ,
		2 as sort
	from t_urikake v
	left join m_tokui mt on (mt.tok_cd = v.tok_cd)
	left join m_tanto ta on (mt.tan_cd = ta.tan_cd)
	*/
	/*入金計無し
	union all select 
		--t_urikake
		v.seikyu_ymd ,
		v.tok_cd ,
		v.zen_seikyu_ymd ,
		v.zen_seikyu_zan ,
		v.nyukin_gaku ,
		v.kurikoshi_gaku ,
		v.zeinuki_gaku ,
		v.zei_gaku ,
		v.zeikomi_gaku ,
		v.seikyu_zan ,
		v.kazei_gaku,
		v.uchi_gaku,
		v.den_cnt ,
		null ,
		null ,
		null ,
		'' ,
		null as chimei1_nm, --積地
		null as chimei2_nm, --降地
		null as haiso_info1, --車番
		null as haiso_info2, --車種
		null as biko,
		'【入金計】' ,
		null ,
		null ,
		(case v.nyukin_gaku when 0 then null else v.nyukin_gaku end) as kingaku ,
		null as m_row ,
		'' ,
		'' ,
		null as freight, --運賃
		null as toll, --通行料
		null,
		--m_tokui
		mt.tok_nm ,
		mt.tel_no ,
		mt.tok_tan ,
		mt.cat_cd,
		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		mt.addr4,
		mt.keisyo,
		--m_tanto
		ta.tan_nm as tan_nm ,
		3 as sort
	from t_urikake v
	left join m_tokui mt on (mt.tok_cd = v.tok_cd)
	left join m_tanto ta on (mt.tan_cd = ta.tan_cd)
	*/
) v on (v.seikyu_ymd = w.seikyu_ymd and v.tok_cd = w.tok_cd)
where 1=1
and w.s_id = &1
order by tok_cd,sort,nonyu_no,v.den_dt,den_no,m_row

[SelectRecords_Jisya]
select
	'TEL:' || j_tel as j_tel_s,
	'FAX:' || j_fax as j_fax_s,
	'URL:' || j_url as j_url_s,
	w.*
from(
	select
		 '請求書' as tait
		,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

[SelectRecords_Furi]
select
	han_name || ' ' || furikikan1 || ' ' || furikikan2 || ' ' || furikikan3 as furikikan
from(
	select  
		mst_kbn,
		han_cd,
		han_name,
		koumoku1 as furikikan1,
		koumoku2 as furikikan2,
		koumoku3 as furikikan3,
		koumoku4,
		koumoku5,
		koumoku6,
		koumoku7,
		koumoku8,
		koumoku9,
		koumoku10,
		sort_key
	from m_hanyo
	where 1=1
	and mst_kbn = '09'
)w
order by han_cd

[selectRecords_mei]
select 
	uh.seikyu_ymd,
	uh.tok_cd,
	coalesce(uh.nonyu_no::varchar,'')||coalesce(uh.nonyu_nm,'') as concat_nonyu, 
	--sum(uh.zeinuki_gaku) as kazei_gaku, --非課税抜き
	sum(uh.kazei_gaku) as kazei_gaku,
	fnc_get_zei(sum(uh.kazei_gaku), uh.seikyu_ymd) as zei_gaku,
	sum(coalesce(uh.zeinuki_gaku,0)) - (sum(coalesce(uh.kazei_gaku,0) - coalesce(uh.uchi_gaku,0))) as hikazei_gaku,
	((sum(uh.kazei_gaku)) + (fnc_get_zei(sum(uh.kazei_gaku), uh.seikyu_ymd)) +(sum(coalesce(uh.zeinuki_gaku,0)) - (sum(coalesce(uh.kazei_gaku,0) - coalesce(uh.uchi_gaku,0))))) as zeikomi_gaku
from t_uri uh
where 1 = 1
and uh.tok_cd = '&2'
and uh.seikyu_ymd = '&3'
and ((coalesce(uh.nonyu_no::varchar,'')||coalesce(uh.nonyu_nm,'')) = ('&4'||'&5'))
group by uh.seikyu_ymd,uh.tok_cd,uh.nonyu_no,uh.nonyu_nm
order by uh.seikyu_ymd,uh.tok_cd,uh.nonyu_no