[selectRecords]
select
	'1' as kbn,
	uh.den_no,
	(1+um.row_no) as row_no,
	trunc((row_no)/6) as row_page,
	(trunc((row_no)/6)+1) as page1,
	sm2.pege_m,
	(trunc((row_no)/6)+1)||'/'||sm2.pege_m as page_c,
	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.tok_tan,
	replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as u_biko1,
	replace(replace(uh.biko2,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as u_biko2,
	um.hin_cd,
	replace(um.hin_nm,chr(10),'')as hin_nm,
	um.tok_hacyu_no,
	case uh.jucyu_flg
	when '0' then um.kei_suryo
	else um.kei_jucyu_su
	end as suryo,
	um.tanka,
	um.kingaku,
	--原価、粗利 STT
	um.genka_gaku,
	(case when(um.jucyu_su != 0)then(round(um.genka_gaku / um.jucyu_su, 2))else(null)end) as gentanka,
	um.kingaku - um.genka_gaku as arari,
	--原価、粗利 END
	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,
	coalesce(uh.nonyu_nm,'') ||'　'||coalesce(uh.nonyu_tan,'') as nonyu_nm,
--    (case coalesce(uh.tok_nm,'')
--        when '' then ''
--        else uh.tok_nm ||'　'|| (case coalesce(uh.tok_tan,'')
--			when '' then (case coalesce(uh.keisyo,'')
--			when '' then (case coalesce(t.keisyo,'') when '' then '' else t.keisyo end) 
--		else uh.keisyo end)
--		else uh.tok_tan ||'　'||(case coalesce(uh.keisyo,'')
--			when '' then'' else uh.keisyo end)end)
--    end) as tok_nm,
	uh.tok_nm,
	(case t.zip_no
		when '' then ''
		else left(t.zip_no,3)||'-'||right(t.zip_no,4)
	 end) as yubin_no,
	 (case uh.nonyu_zip_no
		when '' then ''
		else left(uh.nonyu_zip_no,3)||'-'||right(uh.nonyu_zip_no,4)
	 end) as n_yubin_no, 
	coalesce(cast(um.jucyu_no as varchar(10)),'')||'-'|| coalesce (cast(um.row_no as varchar(10)),'') as jucyu_row_no,
	(case uh.tok_zip_no
		when '' then ''
		else '〒'||left(uh.tok_zip_no,3)||'-'||right(uh.tok_zip_no,4)
	 end) as tok_zip_no,
--	coalesce(uh.tok_addr1,'')||coalesce(uh.tok_addr2,'') as addr1,
--	coalesce(uh.tok_addr3,'')||coalesce(uh.tok_addr4,'') as addr2,
	coalesce(uh.tok_addr1,'') as addr1,
	coalesce(uh.tok_addr2,'') as addr2,
--	coalesce(uh.tok_tel_no,'') as tok_tel,
	(case when(uh.tok_tel_no != '')then('Phone:'||uh.tok_tel_no)else('')end) as tok_tel,
	coalesce(uh.tok_fax_no,'') as tok_fax,
	uh.nonyu_tel_no as n_tel_no,
	t.tel_no as tel_no, 
	t.fax_no as fax_no, 
	(case uh.nonyu_zip_no
		when '' then ''
		else '〒'||left(uh.nonyu_zip_no,3)||'-'||right(uh.nonyu_zip_no,4)
	 end) as nonyu_yubin_no,
--	coalesce(uh.nonyu_addr1,'')||coalesce(uh.nonyu_addr2,'') as nonyu_addr1,
--	coalesce(uh.nonyu_addr2,'')||coalesce(uh.nonyu_addr3,'') as nonyu_addr2,
	uh.nonyu_addr1,
	uh.nonyu_addr2,
	uh.nonyu_addr3,
	uh.nonyu_addr4,
	uh.nonyu_tan,
	coalesce(uh.nonyu_tel_no,'') as nonyu_tel_no,
	coalesce(uh.nonyu_fax_no,'') as nonyu_fax_no,
	tan.tan_nm,
	coalesce(t.cat_cd,'1') as tok_dm,
	h.tani as tan,
	h.bante as bante,
	um.tani_suryo as havy,
	um.jucyu_su,
	um.bante as bante,
	um.htanka as teika,
	um.meisai_biko as biko_m
	, j.mst_kbn
	, j.han_cd
	, j.han_name
	, j.koumoku1
	, j.koumoku2
	, j.koumoku3
	, j.koumoku4
	, j.koumoku5
	, j.koumoku6
	, j.koumoku7
	, j.koumoku8
	, j.koumoku9
	, stan1.tan_rnm as tan1
	, tm1.kousu as k1
	, stan2.tan_rnm as tan2
	, tm2.kousu as k2
	, stan3.tan_rnm as tan3
	, tm3.kousu as k3
	, stan4.tan_rnm as tan4
	, tm4.kousu as k4
	, stan5.tan_rnm as tan5
	, tm5.kousu as k5
	, stan6.tan_rnm as tan6
	, tm6.kousu as k6
	, stan7.tan_rnm as tan7
	, tm7.kousu as k7
	, stan8.tan_rnm as tan8
	, tm8.kousu as k8
	, stan9.tan_rnm as tan9
	, tm9.kousu as k9
	, stan10.tan_rnm as tan10
	, tm10.kousu as k10
	,uh.zeinuki_gaku
	,uh.zei_gaku
	,uh.zeikomi_gaku
	,f1.han_name as pay_kbn
	,f2.han_name as haiso_kbn
	,f3.han_name as konpo_keijyo
	,case uh.nyukin_yotei_ymd
	when '' then ''
	else to_char(to_date(uh.nyukin_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as nyukin_yotei_ymd
	/*,uh.pay_kbn as pay_kbn
	,uh.haiso_kbn as haiso_kbn
	,uh.konpo_keijyo as konpo_keijyo
	,uh.nyukin_yotei_ymd as nyukin_yotei_ymd*/
	
	,um.hosoku2
	,um.tani
	,coalesce(um.hosoku3,'')||' / '||coalesce(um.hosoku4,'') as hin_nm_s

	,uh.kanri_no
	,dh.hin_nm as dhin_nm
	,h.size_h
--	,to_char(h.size_h,'FM990.0')||' '||um.hosoku2 as kind_s
	,to_char(h.size_h,'FM990.0')||' '||h26.koumoku3 as kind_s

	--,um.hin_cd||um.hosoku2||um.size_h||um.hosoku3||um.hosoku4 as hin_nm_s
	--,um.hin_nm_s
	--,hosoku1||size_h||hosoku2||size_h||hosoku3||hosoku4 as hin_nm_s
	--,hin_cd||hosoku2||size_h||hosoku3||hosoku4 as hin_nm_s
	,uh.tok_hacyu_no as tok_hacyu_no_h
	,uh.payment_term
	,uh.biko4
	,uh.biko5
	,uh.biko6
	,uh.pay_kbn
	,h76.koumoku1 as pay_kbn_nm
	,(case when(uh.gaika_rate > 0)then(round(uh.gaika_rate * um.tanka,0))else(um.tanka)end) as tanka_j
	,(case when(uh.gaika_rate > 0)then(um.jucyu_su * round(uh.gaika_rate * um.tanka,0))else(um.kingaku)end) as kingaku_j
	,(case when(uh.gaika_rate > 0)then('1$')else(null)end) as rate_label
	,uh.gaika_rate
	,fnc_get_uri_hin_notes(uh.den_no) as hin_notes
	,fnc_get_uri_dhin_names(uh.den_no, '12') as dhin_names
from t_uri uh
left join m_tanto tan on(uh.tan_cd = tan.tan_cd)
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 m_hin h on(um.hin_cd = h.hin_cd)
left join m_hin dh on(dh.hin_cd = h.dhin_cd)
left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya')
left join (select um2.den_no,max(trunc((row_no)/6)+1) as pege_m from t_uri_m um2 group by um2.den_no) sm2 on sm2.den_no = uh.den_no
left join m_hanyo h76 on (h76.mst_kbn = '76' and h76.han_cd = uh.pay_kbn)
left join m_hanyo h26 on (h26.mst_kbn = '26' and h26.han_name = um.hosoku2)
left join t_seizou ts on(uh.den_no = ts.jucyu_no and ts.shiji_no = (um.row_no + 100 * uh.den_no))
left join t_seizou_m tm1 on(ts.shiji_no = tm1.shiji_no and tm1.koutei_no = 0)
left join t_seizou_m tm2 on(ts.shiji_no = tm2.shiji_no and tm2.koutei_no = 1)
left join t_seizou_m tm3 on(ts.shiji_no = tm3.shiji_no and tm3.koutei_no = 2)
left join t_seizou_m tm4 on(ts.shiji_no = tm4.shiji_no and tm4.koutei_no = 3)
left join t_seizou_m tm5 on(ts.shiji_no = tm5.shiji_no and tm5.koutei_no = 4)
left join t_seizou_m tm6 on(ts.shiji_no = tm6.shiji_no and tm6.koutei_no = 5)
left join t_seizou_m tm7 on(ts.shiji_no = tm7.shiji_no and tm7.koutei_no = 6)
left join t_seizou_m tm8 on(ts.shiji_no = tm8.shiji_no and tm8.koutei_no = 7)
left join t_seizou_m tm9 on(ts.shiji_no = tm9.shiji_no and tm9.koutei_no = 8)
left join t_seizou_m tm10 on(ts.shiji_no = tm10.shiji_no and tm10.koutei_no = 9)
left join m_tanto stan1 on(tm1.seizou_tan_cd = stan1.tan_cd)
left join m_tanto stan2 on(tm2.seizou_tan_cd = stan2.tan_cd)
left join m_tanto stan3 on(tm3.seizou_tan_cd = stan3.tan_cd)
left join m_tanto stan4 on(tm4.seizou_tan_cd = stan4.tan_cd)
left join m_tanto stan5 on(tm5.seizou_tan_cd = stan5.tan_cd)
left join m_tanto stan6 on(tm6.seizou_tan_cd = stan6.tan_cd)
left join m_tanto stan7 on(tm7.seizou_tan_cd = stan7.tan_cd)
left join m_tanto stan8 on(tm8.seizou_tan_cd = stan8.tan_cd)
left join m_tanto stan9 on(tm9.seizou_tan_cd = stan9.tan_cd)
left join m_tanto stan10 on(tm10.seizou_tan_cd = stan10.tan_cd)
left join m_hanyo f1 on (f1.mst_kbn = '76' and uh.pay_kbn = f1.han_cd)
left join m_hanyo f2 on (f2.mst_kbn = '77' and uh.haiso_kbn = f2.han_cd)
left join m_hanyo f3 on (f3.mst_kbn = '42' and uh.konpo_keijyo = f3.han_cd)
where 1 = 1
and uh.jucyu_flg = 1
and uh.del_flg = 0
and um.hosoku2 != ''
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,kbn,row_no;

[SelectRecords_Footer]
select 1+spec_row_no as spec_seq, spec_cd, spec_naiyo
from t_uri_spec
where den_no = &1
and del_flg = 0
order by spec_row_no

[SelectRecords_Process]
select
  sm.koutei_no
, sm.koutei_cd
, sm.koutei_nm
, sm.seizou_tan_cd
, mt.tan_nm as seizou_tan_nm
, to_char(sm.yotei_end_time,'YYYY/MM/DD') as koutei_ymd
from t_uri uh
inner join t_seizou sh on (sh.jucyu_no = uh.den_no and sh.jucyu_row_no = -1)
inner join t_seizou_m sm on (sm.shiji_no = sh.shiji_no)
left join m_tanto mt on (mt.tan_cd = sm.seizou_tan_cd)
where 1 = 1
and uh.den_no = &1
order by koutei_no


[_SelectRecords_Process]
select han_cd, han_name
from m_hanyo
where mst_kbn = '20'
order by sort_key, han_cd


[SelectRecords_Jisya]
select
	 'TEL:' || j_tel as j_tel_s
	,'FAX:' || j_fax as j_fax_s
	,(case j_url
		when '' then ''
		else 'URL:' || j_url
	end) as j_url_s
	--,'URL:' || j_url as j_url_s
	,(case j_daihyo
		when '' then ''
		else '担当者:' || j_daihyo
	end) as j_daihyo_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 as j_address2
		,j.koumoku4 as j_address3
		,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