[selectRecords]
select
	trunc((row_no -1)/ 15)+1 as row_page
	--, case when row_page2 = 15 then trunc((row_no -1)/ 15)+1 else trunc((row_no-15)/24)+ 2 end as row_pageX --テスト中20210514
	, case when row_page2 = 15 then trunc((row_no -1)/ 15)+1 else trunc((row_no-16)/25)+ 2 end as row_pageX
	--, trunc((row_no)/ 14) as uri_row_kai
	,'page:'||trunc((row_no -1)/ 15) + 1 as uri_row_kai_s
	,(case 
		when syori_ymd = '' then '日付を入力してください'
		else to_char(to_date(syori_ymd,'yyyymmdd'),'yyyy/mm/dd')
	end) as u_ymd
	,'お客様コード：'||tokui_cd as tokui_cd_s

	,(case  coalesce(tokui_nm,'')
		when '' then ''
		else tokui_nm ||'　'|| (case coalesce(w.keisyo,'') when '' then '様' else keisyo end)
	end) as tokui_nm_s
	
	,'現場：'||nonyu_nm as nonyu_nm_s
	,'担当：'||w.tan_nm as tan_nm_s
	,(case w.zip_no
		when '' then ''
		else left(w.zip_no,3)||'-'||right(w.zip_no,4)
	 end) as post_n
	 ,coalesce(nonyu_no::varchar,'')||coalesce(nonyu_nm,'') as concat_nonyu
	 ,(case when uri_s is null then null else uri_g end) as uri_g
	 , case (coalesce(w.hin_nm2,'')) when '' then '' else w.hin_nm1 end as hin_nm1
	 , case (coalesce(w.hin_nm2,'')) when '' then w.hin_nm1 else w.hin_nm2 end as hin_nm2
	, (trunc((row_number()over(order by w.tokui_cd,w.uri_no) - 1) / 15) + 1) as row_page
	, (select ceil((count(um2.den_no)+10)/25.0) as max from t_uri_m um2 where um2.den_no = fnc_to_number('&3')) as max_page
	, (select (count(um2.den_no)) as max from t_uri_m um2 where um2.den_no = fnc_to_number('&3')) as max_row_no
	--,(select ceil(count(um2.den_no)/15.0) as max from t_uri_m um2 where um2.den_no = fnc_to_number('&3') ) as max_page
	,w.*
from(
	select
		--uh
		uh.den_no as uri_no,
		uh.jucyu_no as jyucyu,
		uh.syori_ymd as syori_ymd,
		uh.tok_cd as tokui_cd,
		uh.tok_tan,	
		uh.zeinuki_gaku as zn_gaku,
		(case mt.zei_calc_kbn when '1' then null else uh.zei_gaku end) as z_gaku,
		(case mt.zei_calc_kbn when '1' then uh.zeinuki_gaku else uh.zeikomi_gaku end) as zi_gaku,
		--uh.zei_gaku as z_gaku,
		--uh.zeikomi_gaku as zi_gaku,
		mt.zei_calc_kbn,
		uh.seikyu_ymd as s_ymd,
		uh.tok_nm as tokui_nm,
		uh.tok_nm,
		uh.nonyu_tan,
		uh.nonyu_no,
		uh.nonyu_nm,
		regexp_replace(regexp_replace(uh.biko1, '\r|\n|\r\n', ''), '\r|\n|\r\n', '') as biko1,
		regexp_replace(regexp_replace(uh.biko2, '\r|\n|\r\n', ''), '\r|\n|\r\n', '') as biko2,
		--regexp_replace(uh.biko2, '\r|\n|\r\n', '') as biko2,
		--fnc_get_report_multiline(uh.biko1) as biko1,
		--fnc_get_report_multiline(uh.biko2) as biko2,
		--um
		um.row_no + 1 as row_no,
		case when (um.row_no +1) < 16 then 15 else 24 end as row_page2, --テスト中20210514
		case when '&17' = um.hin_cd then null
			else um.row_no +1
			end as uri_row,
		um.jucyu_no as jyucyum,
		um.jucyu_row_no as jyucyu_row,
		um.tani as tani,
		case when '&17' = um.hin_cd then null
			else um.jucyu_su
			end as jucyu_su,
		um.suryo as uri_s,
		case when '&17' = um.hin_cd then null
			else um.tanka
		end as uri_t,
		/*
		case when '&17' = um.hin_cd then null
			else um.htanka
			end as htanka,
		*/
		case when '&17' = um.hin_cd then null
			else case when '&12' = 'n_htanka' then null
				else htanka end
		end as htanka,
		case when '&17' = um.hin_cd then null
			else um.kingaku 
			end as uri_g,
		case when '&17' = um.hin_cd then null
--#26738 2023.01.03 MOD STT
--			else um.hin_cd 
			else (case when(h99b.han_name = '1')then(um.hin_nm)else(um.hin_cd)end) 
--#26738 2023.01.03 MOD END
			end as hin_cd,
--#26738 2023.01.03 MOD STT
--		um.hin_nm,
		(case when(h99b.han_name = '1')then(um.hin_nm_ext)else(um.hin_nm)end) as hin_nm,
--#26738 2023.01.03 MOD END
		substr(um.hin_nm,1,25) as hin_nm1,
		substr(um.hin_nm,26,50) as hin_nm2,
		case when '&17' = um.hin_cd then null
			else um.meisai_biko
			end as meisai_biko,
		case when '&17' = um.hin_cd then null
			else um.tok_hacyu_no
			end as tok_hacyu_no,
		mt.keisyo,
		mt.tok_kana as tokui_kana,
		fnc_get_keisyo_1(uh.tok_nm, uh.tok_tan, uh.keisyo) keisyo1,
		fnc_get_keisyo_2(uh.tok_nm, uh.tok_tan, uh.keisyo) keisyo2,
--		case coalesce(uh.tok_tan,'')
--			 when '' then uh.keisyo
--			 else '' end as keisyo1,
--		case coalesce(uh.tok_tan,'')
--			 when '' then ''
--			 else uh.keisyo end as keisyo2,
			 
-- 2022/05/12 #23862 MOD STT
-- 会社名の後ろに御中が欲しいとのことだが、他と表現を合わせるため、一旦一般的な付け方へ
		fnc_get_keisyo_1(uh.tok_nm, uh.tok_tan, uh.keisyo) keisyo1_ex,
		fnc_get_keisyo_2(uh.tok_nm, uh.tok_tan, uh.keisyo) keisyo2_ex,
--		'御中' as keisyo1_ex,
--		case coalesce(uh.tok_tan,'')
--			 when '' then ''
--			 else '様' end as keisyo2_ex,
-- 2022/05/12 #23862 MOD END

		mt.zip_no,
		mt.addr1,
		mt.addr2,
		mt.addr3,
		--tan
		tan.tan_cd,
		tan.tan_nm,
		uh.mitsumori_flg,
		--coalesce(uh.mitsumori_no,'') as mitsumori_no,
		case(coalesce(uh.mitsumori_no::varchar,'')) when '' then uh.den_no::varchar else uh.mitsumori_no end as mitsumori_no,
		case(han.han_name) when '1' then han.koumoku2 else uh.payment_term end as payment_term,
		uh.title1,
		uh.title2,
		uh.nonyu_ymd_str,
		uh.mitsu_kigen_mongon
	from t_uri uh
	left join t_uri_m um on (uh.den_no = um.den_no)
	left join m_tokui mt on (uh.tok_cd = mt.tok_cd)
	left join m_tanto tan on (uh.tan_cd = tan.tan_cd)
--	left join m_tanto tan on (uh.eigyou_tan_cd = tan.tan_cd)
--	left join m_hanyo han on (han.mst_kbn = '01' and han.han_cd = uh.prc_sts)
	left join m_hanyo han on (han.mst_kbn = '99' and han.han_cd = 'pays-r0310')
	left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'hnme-r0310')
	where 1=1
--	and coalesce(han.koumoku3, '') = ''
	and uh.del_flg = 0
	and (uh.den_no = fnc_to_number('&3') or '' = '&3')
	and (uh.tok_cd >= '&4' or '' = '&4')
	and (uh.syori_ymd >= '&1' or '' = '&1') and (uh.syori_ymd <= '&2' or '' = '&2')
--	and (uh.tok_cd <= '&7' or '' = '&7')
--	and (mt.shime_dd = fnc_to_number('&2') or '' = '&2') --締日
--	and (uh.prc_sts = '&5' or '' = '&5')
--	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 (um.hin_cd >= '&12' or '' = '&12') and (um.hin_cd <= '&13' or '' = '&13')
--	and (hin.cat_cd >= '&14' or '' = '&14') and (hin.cat_cd <= '&15' or '' = '&15')
--	and (uh.den_no <= fnc_to_number('&17') or '' = '&17')
--	and ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case('&18')||'%') or ('&18' = ''))
)w
order by tokui_cd,nonyu_no,syori_ymd,uri_no,row_no
;

[SelectRecords_Jisya]
select
	 w.*
	,'〒' || j_post as j_post_s
	,'TEL:' || j_tel as j_tel_s
	,'FAX:' || j_fax as j_fax_s
--	,'URL:' || j_url as j_url_s
	,coalesce(h98a.koumoku1,'URL:') || j_url as j_url_s
	,'email:' || j_email as j_email_s
from(
	select
		 '御　見　積　書' as title
		,j.mst_kbn
		,j.han_cd
		,j.han_name as j_nm
		,j.koumoku1 as j_post
		,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_email
--		,j.koumoku9
--		,j.koumoku10
		,j.sort_key as 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 mongon4
		,j2.koumoku10 as koumoku10
		, '下記の通り御見積申し上げます。' as mongon1
		, '何卒ご用命の程お願い申し上げます。' as mongon2
		--, '※契約後の仕様変更は別途打合せの上、請求させて頂きます。' as mongon3
		--, '※消費税は別途申し受けます。' as mongon4
		, '' as mongon5
		, case when coalesce(m2.koumoku1,'') != '' then coalesce(m2.koumoku1,'')
			else '' end as midasi_FLG
		, split_part('&2',',',1) as title1
		, split_part('&2',',',2) as title2
		, split_part('&2',',',3) as title3
	from m_hanyo j
	left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'r0313.m')
	left join m_hanyo m2 on (m2.mst_kbn = '90' and m2.han_cd = 'hin-title') --見出し行対応（数量・単価・金額の非表示）
	left join t_uri t on (t.den_no = fnc_to_number('&1'))
	left join m_hanyo eig on (eig.mst_kbn = '99' and eig.han_cd = 'uri-eig')
	where 1=1
	and j.mst_kbn = '90'
	and j.han_cd = 'jisya' || (case (eig.han_name) when '1' then case (coalesce(t.eig_cd,'')) when '' then '' else ('_' || t.eig_cd) end else('')  end)
)w
left join m_hanyo h98a on (h98a.mst_kbn = '98' and h98a.han_cd = 'jisya-k07')

[SelectLogo]
select 
	koumoku2 as image_cd
	, koumoku3 as logo_row
	, koumoku4 as logo_col
from m_hanyo
where 1=1
and mst_kbn = '99' 
and han_cd = 'logo-r0313' 
and han_name = '1'
;

[SelectKakuin]
select 
	koumoku2 as image_cd
	, koumoku3 as logo_row
	, koumoku4 as logo_col
from m_hanyo
where 1=1
and mst_kbn = '99' 
and han_cd = 'kaku-r0313' 
and han_name = '1'
;

[__SelectTanto]
select 
	koumoku2 as image_cd
	, koumoku3 as logo_row
	, koumoku4 as logo_col
from m_hanyo
where 1=1
and mst_kbn = '99' 
and han_cd = 'tan-r0313' 
and han_name = '1'
;

[SelectTanto]
select 
	koumoku2 as image_cd
	, koumoku3 as logo_row
	, koumoku4 as logo_col
from m_hanyo
where 1=1
and mst_kbn = '99' 
and han_cd = 'in-r0313' 
and han_name = '1'
;

[SelectSyaban]
select 
  koumoku2 as image_cd
, koumoku3 as img_row
, koumoku4 as img_col
from m_hanyo
where 1 = 1
and mst_kbn = '99' 
and han_cd = 'syab-r0313'
and han_name = '1'

[SelectImage]
select 
	file_image
from m_image
where 
	image_cd = 
	case 
		when (
			select 
				image_cd
			from m_image
			where image_cd = '&1'||'_m'
		) is null 
		then '&1'
		else '&1'||'_m'
	end 
;