[selectRecords]
select 
	w.s_id,
	w.kbn,
	w.den_no,
	w.s_row_no,
	w.row_order,
	tan.email,
	'No.'||w.mitsumori_no as mitsumori_no,
	w.mitsu_kigen_ymd,
	w.mitsu_kigen_mongon,
	w.noki,
	w.keisyou,
	w.keisyou_2,
	w.row_no,
	coalesce(w.row_page,0) as row_page,
	w.input_ymd,
	w.syori_ymd,
	w.zeinuki_gaku,
	w.zei_gaku,
	w.tok_tan,
	w.zeikomi_gaku,
	w.u_biko1,
	w.u_biko2,
	w.u_biko3,
	w.u_biko4,
	w.u_biko5,
	w.u_biko6,
	w.unit_nm,
	w.hin_cd,
	w.hin_nm,
	w.tok_hacyu_no,
	w.suryo,
	w.tanka,
	w.kingaku,
	w.jucyu_su,
	w.tani,
	w.title1,
	w.title2,
	w.payment_term,
	w.nonyu_ymd,
	w.tok_cd,
	w.tok_nm,
	w.yubin_no,
	w.jucyu_row_no,
	w.tori_cond,
	w.biko1,
	w.biko2,
	w.biko3,
	w.biko4,
	w.hosoku1,
	w.hosoku2,
	w.nonyu_nm,
	w.nonyu_yubin_no,
	w.nonyu_tan,
	w.keisyou2,
	w.keisyou2_2,
	w.brkey,
	((w.brkey)+1) as page_no,
	((select
		max(w.brkey) as max_page
	from w_basr0310 w
	where 1=1
	and w.s_id = '&7')+1) as max_page_no,
	disp_row_no,
	total_kingaku
from w_basr0310 w
inner join t_uri tu on (w.den_no = tu.den_no)
left join m_tanto tan on (tu.tan_cd = tan.tan_cd)
where 1=1
and w.s_id = '&7'
order by den_no,s_row_no,kbn,row_order;

[selectRecords_old]
select
	s_id
	, w.kbn
	, w.den_no
	, w.s_row_no
	, w.row_order
	, w.mitsumori_no
	, w.mitsu_kigen_ymd
	, w.noki
	, w.keisyou
	, w.keisyou_2
	, w.row_no
	, w.row_page
	, w.input_ymd
	, w.syori_ymd
	, w.zeinuki_gaku
	, w.zei_gaku
	, w.tok_tan
	, w.zeikomi_gaku
	, w.u_biko1
	, w.u_biko2
	, w.u_biko3
	, w.u_biko4
	, w.u_biko5
	, w.u_biko6
	, w.unit_nm
	, w.hin_cd
	, w.hin_nm
	, w.tok_hacyu_no
	, w.suryo
	, w.tanka
	, w.kingaku
	, w.jucyu_su
	, w.tani
	, w.title1
	, w.title2
	, w.payment_term
	, w.nonyu_ymd
	, w.tok_cd
	, w.tok_nm
	, w.yubin_no
	, w.jucyu_row_no
	, w.tori_cond
	, w.biko1
	, w.biko2
	, w.biko3
	, w.biko4
	, w.hosoku1
	, w.hosoku2
	, w.nonyu_nm
	, w.nonyu_yubin_no
	, w.nonyu_tan
	, w.keisyou2
	, w.keisyou2_2
	, w.disp_row_no
	, w.brkey
	, case coalesce (w.unit_nm,'')
		when '' then '0' 
		else '1' 
		end as ht_unit
	, '&1' as pr1
	, '&2' as pr2
	, '&3' as pr3
	, '&4' as pr4
	, '&5' as pr5
	, '&6' as pr6
	, '&7' as pr7
	, '&8' as pr8
	, '&10' as pr9
	, '&11' as pr10
	, '&12' as pr11
from w_basr0310 w
where w.s_id = '&7'
order by den_no,row_no,kbn,row_order;




[updateOriginal]
select fnc_report_basr0310(:s_id);





[selectRecords<type0>]
select 
	'1' as kbn,
	row_number() over (order by uh.den_no,um.row_no,um.row_order) as s_row_no,
	um.row_order,
	uh.den_no,
	uh.mitsumori_no,
		case uh.mitsu_kigen_ymd
	when '' then ''
	else to_char(to_date(uh.mitsu_kigen_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as mitsu_kigen_ymd,
		case un.noki
	when '' then ''
	else to_char(to_date(un.noki, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as noki,
	case uh.tok_tan
	when '' then '御中'
	else ''
	end as keisyou,
	case
	when coalesce(uh.tok_tan,'') = '' then ''
	else '様'
	end as keisyou_2,
	(1+um.row_no) as row_no,
	trunc((row_number() over ()-1)/35) as row_page,
	to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	case uh.syori_ymd
	when '' then ''
	else to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as syori_ymd,
	uh.zeinuki_gaku,
	uh.zei_gaku,
	uh.tok_tan,
	uh.zeikomi_gaku,
	uh.biko1 as u_biko1,
	uh.biko2 as u_biko2,
	uh.biko3 as u_biko3,
	uh.biko4 as u_biko4,
	uh.biko5 as u_biko5,
	uh.biko6 as u_biko6,
	um.hin_cd,
	replace(um.hin_nm,chr(10),'')as hin_nm,
	um.tok_hacyu_no,
	um.suryo,
	um.tanka,
	um.kingaku,
	um.jucyu_su,
	uh.title1,
	uh.payment_term,
		case um.nonyu_ymd
	when '' then ''
	else to_char(to_date(um.nonyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as nonyu_ymd,
	uh.tok_cd,
	t.tok_nm,
	'〒'||substr(t.zip_no,1,3)||'-'||substr(t.zip_no,4,7) as yubin_no,
	coalesce(cast(um.jucyu_no as varchar(10)),'')||'-'|| coalesce (cast(um.row_no as varchar(10)),'') as jucyu_row_no,
	t.tori_cond,
	t.biko1,
	t.biko2,
	t.biko3,
	t.biko4,
	'　' || um.hosoku1 as hosoku1,
	'　' || um.hosoku2 as hosoku2,
	uh.nonyu_nm,
	'〒'||substr(nonyu_zip_no,1,3)||'-'||substr(nonyu_zip_no,4,7) as nonyu_yubin_no,
	uh.nonyu_tan,
	case uh.nonyu_tan
	when '' then '御中'
	else ''
	end as keisyou2,
	case 
	when coalesce(uh.nonyu_tan,'') = '' then ''
	else '様'
	end as keisyou2_2
from t_uri uh
left join m_tokui t on(uh.tok_cd = t.tok_cd)
left join t_uri_m um on(uh.den_no = um.den_no)
left join (select distinct den_no, min(noki) as noki from t_uri_nouki group by den_no) un on (uh.den_no = un.den_no ) 
where 1 = 1
and um.seq is null
and uh.jucyu_flg = 0
and uh.del_flg = 0
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(uh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
union all select
	'2' as kbn,
	row_number() over (order by uh.den_no,um.row_no,um.row_order) as s_row_no,
	um.row_order,
	uh.den_no,
	uh.mitsumori_no,
		case uh.mitsu_kigen_ymd
	when '' then ''
	else to_char(to_date(uh.mitsu_kigen_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as mitsu_kigen_ymd,
		case un.noki
	when '' then ''
	else to_char(to_date(un.noki, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as noki,
	case uh.tok_tan
	when '' then '御中'
	else ''
	end as keisyou,
	case
	when coalesce(uh.tok_tan,'') = '' then ''
	else '様'
	end as keisyou_2,
	(1+um.row_no) as row_no,
	trunc((row_number() over ()-1)/35) as row_page,
	to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	case uh.syori_ymd
	when '' then ''
	else to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as syori_ymd,
	uh.zeinuki_gaku,
	uh.zei_gaku,
	uh.tok_tan,
	uh.zeikomi_gaku,
	uh.biko1 as u_biko1,
	uh.biko2 as u_biko2,
	uh.biko3 as u_biko3,
	uh.biko4 as u_biko4,
	uh.biko5 as u_biko5,
	uh.biko6 as u_biko6,
	um.hin_cd,
	replace(um.hin_nm,chr(10),'')as hin_nm,
	um.tok_hacyu_no,
	um.suryo,
	um.tanka,
	um.kingaku,
	um.jucyu_su,
	uh.title1,
--	uh.title2,
	uh.payment_term,
		case um.nonyu_ymd
	when '' then ''
	else to_char(to_date(um.nonyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as nonyu_ymd,
	uh.tok_cd,
	t.tok_nm,
	'〒'||substr(t.zip_no,1,3)||'-'||substr(t.zip_no,4,7) as yubin_no,
	coalesce(cast(um.jucyu_no as varchar(10)),'')||'-'|| coalesce (cast(um.row_no as varchar(10)),'') as jucyu_row_no,
	t.tori_cond,
	t.biko1,
	t.biko2,
	t.biko3,
	t.biko4,
	'　' || um.hosoku1 as hosoku1,
	'　' || um.hosoku2 as hosoku2,
	uh.nonyu_nm,
	'〒'||substr(nonyu_zip_no,1,3)||'-'||substr(nonyu_zip_no,4,7) as nonyu_yubin_no,
	uh.nonyu_tan,
	case uh.nonyu_tan
	when '' then '御中'
	else ''
	end as keisyou2,
	case 
	when coalesce(uh.nonyu_tan,'') = '' then ''
	else '様'
	end as keisyou2_2
from t_uri uh
left join m_tokui t on(uh.tok_cd = t.tok_cd)
left join t_uri_m um on(uh.den_no = um.den_no)
left join (select distinct den_no, min(noki) as noki from t_uri_nouki group by den_no) un on (uh.den_no = un.den_no ) 
where 1 = 1
and hin_cd != '99'
and coalesce(um.hosoku1,'') != ''
and um.seq is null
and uh.jucyu_flg = 0
and uh.del_flg = 0
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(uh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
union all select
	'1' as kbn,
	10000  as s_row_no,
	10000 as row_order,
	uh.den_no,
	uh.mitsumori_no,
		case uh.mitsu_kigen_ymd
	when '' then ''
	else to_char(to_date(uh.mitsu_kigen_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as mitsu_kigen_ymd,
		case un.noki
	when '' then ''
	else to_char(to_date(un.noki, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as noki,
	case uh.tok_tan
	when '' then '御中'
	else ''
	end as keisyou,
	case
	when coalesce(uh.tok_tan,'') = '' then ''
	else '様'
	end as keisyou_2,
	10000 as row_no,
	trunc((row_number() over ()-1)/35) as row_page,
	to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	case uh.syori_ymd
	when '' then ''
	else to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as syori_ymd,
	uh.zeinuki_gaku,
	uh.zei_gaku,
	uh.tok_tan,
	uh.zeikomi_gaku,
	uh.biko1 as u_biko1,
	uh.biko2 as u_biko2,
	uh.biko3 as u_biko3,
	uh.biko4 as u_biko4,
	uh.biko5 as u_biko5,
	uh.biko6 as u_biko6,
	'99' as hin_cd,
	'消費税' as hin_nm,
	null as tok_hacyu_no,
	null as suryo,
	null as tanka,
	uh.zei_gaku as kingaku,
	null as jucyu_su,
	uh.title1,
--	uh.title2,
	uh.payment_term,
	''  as nonyu_ymd,
	uh.tok_cd,
	t.tok_nm,
	'〒'||substr(t.zip_no,1,3)||'-'||substr(t.zip_no,4,7) as yubin_no,
	'' as jucyu_row_no,
	t.tori_cond,
	t.biko1,
	t.biko2,
	t.biko3,
	t.biko4,
	'' as hosoku1,
	'' as hosoku2,
	uh.nonyu_nm,
	'〒'||substr(nonyu_zip_no,1,3)||'-'||substr(nonyu_zip_no,4,7) as nonyu_yubin_no,
	uh.nonyu_tan,
	case uh.nonyu_tan
	when '' then '御中'
	else ''
	end as keisyou2,
	case 
	when coalesce(uh.nonyu_tan,'') = '' then ''
	else '様'
	end as keisyou2_2
from t_uri uh
left join m_tokui t on(uh.tok_cd = t.tok_cd)
--left join t_uri_m um on(uh.den_no = um.den_no)
left join (select distinct den_no, min(noki) as noki from t_uri_nouki group by den_no) un on (uh.den_no = un.den_no ) 
where 1 = 1
--and hin_cd != '99'
--and coalesce(um.hosoku1,'') != ''
--and um.seq is null
and uh.jucyu_flg = 0
and uh.del_flg = 0
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(uh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and '1' = '&10'
--and ((um.hin_nm like '%&5%') or ('&5' = ''))
union all select
	'3' as kbn,
	row_number() over (order by uh.den_no,um.row_no,um.row_order) as s_row_no,
	um.row_order,
	uh.den_no,
	uh.mitsumori_no,
		case uh.mitsu_kigen_ymd
	when '' then ''
	else to_char(to_date(uh.mitsu_kigen_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as mitsu_kigen_ymd,
		case un.noki
	when '' then ''
	else to_char(to_date(un.noki, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as noki,
	case uh.tok_tan
	when '' then '御中'
	else ''
	end as keisyou,
	case
	when coalesce(uh.tok_tan,'') = '' then ''
	else '様'
	end as keisyou_2,
	(1+um.row_no) as row_no,
	trunc((row_number() over ()-1)/35) as row_page,
	to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	case uh.syori_ymd
	when '' then ''
	else to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as syori_ymd,
	uh.zeinuki_gaku,
	uh.zei_gaku,
	uh.tok_tan,
	uh.zeikomi_gaku,
	uh.biko1 as u_biko1,
	uh.biko2 as u_biko2,
	uh.biko3 as u_biko3,
	uh.biko4 as u_biko4,
	uh.biko5 as u_biko5,
	uh.biko6 as u_biko6,
	um.hin_cd,
	replace(um.hin_nm,chr(10),'')as hin_nm,
	um.tok_hacyu_no,
	um.suryo,
	um.tanka,
	um.kingaku,
	um.jucyu_su,
	uh.title1,
	uh.payment_term,
		case um.nonyu_ymd
	when '' then ''
	else to_char(to_date(um.nonyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as nonyu_ymd,
	uh.tok_cd,
	t.tok_nm,
	'〒'||substr(t.zip_no,1,3)||'-'||substr(t.zip_no,4,7) as yubin_no,
	coalesce(cast(um.jucyu_no as varchar(10)),'')||'-'|| coalesce (cast(um.row_no as varchar(10)),'') as jucyu_row_no,
	t.tori_cond,
	t.biko1,
	t.biko2,
	t.biko3,
	t.biko4,
	'　' || um.hosoku1 as hosoku1,
	'　' || um.hosoku2 as hosoku2,
	uh.nonyu_nm,
	'〒'||substr(nonyu_zip_no,1,3)||'-'||substr(nonyu_zip_no,4,7) as nonyu_yubin_no,
	uh.nonyu_tan,
	case uh.nonyu_tan
	when '' then '御中'
	else ''
	end as keisyou2,
	case 
	when coalesce(uh.nonyu_tan,'') = '' then ''
	else '様'
	end as keisyou2_2
from t_uri uh
left join m_tokui t on(uh.tok_cd = t.tok_cd)
left join t_uri_m um on(uh.den_no = um.den_no)
left join (select distinct den_no, min(noki) as noki from t_uri_nouki group by den_no) un on (uh.den_no = un.den_no ) 
where 1 = 1
and hin_cd != '99'
and coalesce(um.hosoku2,'') != ''
and um.seq is null
and uh.jucyu_flg = 0
and uh.del_flg = 0
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(uh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
order by den_no,row_no,kbn,row_order

[SelectRecords_Jisya]
select
	 w.*
	,'TEL:' || j_tel as j_tel_s
	,'FAX:' || j_fax as j_fax_s
	,'URL:' || j_url as j_url_s
from(
	select
		 '御　見　積　書' as title
		,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.koumoku8 as mongon3
		,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