[selectRecords]
select
	  (case when(syori_ymd != '')then(to_char(to_date(syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as syori_ymd
	, (case 
		when (&19 = 0) then null
		else (case suryo when 0 then 0 else (genka_gaku / suryo) end)
	  end)as gen_tanka
	, (case
		when (&19 = 0) then null
		else (kingaku - genka_gaku)
	  end) as arari
	, tok_nm ||' '||coalesce(keisyo,'') as tok_nm
	, v.*
from(
	select 
		 th.den_no
		,th.syori_ymd
		,th.tok_cd
		,th.tok_nm
		,th.eigyou_tan_cd
		,th.nonyu_no
		,replace(replace(th.nonyu_nm,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as nonyu_nm
		,tm.hin_cd
		,tm.hin_nm
		,tm.tani
		,tm.row_no
		,coalesce(tm.suryo,0) as suryo
		,coalesce(tm.tanka,0) as tanka
		,coalesce(tm.kingaku,0) as kingaku
		,coalesce(tm.genka_gaku,0) as genka_gaku
		,tm.meisai_biko
		,tk.keisyo
		,hin.hin_nm as hin_nm_m
		,han.han_name as state
		,tan.tan_nm
	from t_uri_m tm
	inner join t_uri th on (tm.den_no = th.den_no)
	left join m_tokui tk on (tk.tok_cd = th.tok_cd)
	left join m_hanyo han on (th.prc_sts = han.han_cd and mst_kbn = '01')
	left join m_tanto tan on (tan.tan_cd = th.eigyou_tan_cd)
	left join m_hin hin on (hin.hin_cd = tm.hin_cd)
	where 1 = 1
	and th.del_flg = 0 and jucyu_flg = 0
	and (tk.shime_dd = fnc_to_number('&2') or '' = '&2') --締日
	and (th.prc_sts = '&5' or '' = '&5')
	and (th.tok_cd >= '&6' or '' = '&6') and (th.tok_cd <= '&7' or '' = '&7')
	and (th.nonyu_no >= fnc_to_number('&8') or '' = '&8') and (th.nonyu_no <= fnc_to_number('&9') or '' = '&9')
	and (th.eigyou_tan_cd >= '&10' or '' = '&10') and (th.eigyou_tan_cd <= '&11' or '' = '&11')
	and (tm.hin_cd >= '&12' or '' = '&12') and (tm.hin_cd <= '&13' or '' = '&13')
	and (hin.cat_cd >= '&14' or '' = '&14') and (hin.cat_cd <= '&15' or '' = '&15')
	and (th.den_no >= fnc_to_number('&16') or '' = '&16') and (th.den_no <= fnc_to_number('&17') or '' = '&17')
	and ((fnc_translate_case(tm.hin_nm) LIKE '%'||fnc_translate_case('&18')||'%') or ('&18' = ''))
	and (th.syori_ymd >= '&3' or '' = '&3') and (th.syori_ymd <= '&4' or '' = '&4')
) v
--order by v.syori_ymd,v.den_no,v.row_no
--order by v.tok_cd,v.syori_ymd,v.den_no,v.row_no
order by v.tok_cd,v.nonyu_no,v.syori_ymd,v.den_no,v.row_no
--order by v.eigyou_tan_cd,v.syori_ymd,v.den_no,v.row_no
--order by v.hin_cd,v.syori_ymd,v.den_no,v.row_no
;


[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