[selectRecords]
select
  uh.den_no
--, (1 + um.row_no) as row_no
, um.rel_row_no as row_no
--, trunc((um.row_no) / 15) as row_page
, trunc((um.row_no) / &10) as row_page
, wh.shiji_no
, (case when(uh.input_ymd != '')then(to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as input_ymd
, (case when(uh.syori_ymd != '')then(to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd'))else('')end) as syori_ymd
, uh.tan_cd
, t.tan_nm
, wh.sir_cd
, ms.sir_nm||'　'||fnc_get_keisyo_1(ms.sir_nm, ms.sir_tan, '御中') as sir_nm
, ms.sir_tan||'　'||fnc_get_keisyo_2(ms.sir_nm, ms.sir_tan, '様') as sir_tan
, 'TEL:' || ms.tel_no as tel_no_s
, 'FAX:' || ms.fax_no as fax_no_s

, (case when('&7' = um.hin_cd)then(null)else(um.hin_cd)end) as hin_cd
, replace(um.hin_nm,chr(10),'') as hin_nm
, (case when('&7' = um.hin_cd or um.set_row_no is not null)then(null)else(um.jucyu_su)end) as hacyu_su
, (case when('&7' = um.hin_cd)then(null)else(um.tanka)end) as tanka
, (case when('&7' = um.hin_cd)then(null)else(um.kingaku)end) as kingaku
, um.tani
--, um.tani as hin_tani
, (case when('&7' = um.hin_cd or um.set_row_no is not null)then(null)else(um.tani)end) as hin_tani
, hi.iri_su
, um.htanka
, um.disc_per
--, um.price_rank
, (case when('&7' = um.hin_cd or um.set_row_no is not null)then(null)else(um.price_rank)end) as price_rank

, replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1
, replace(replace(uh.biko2,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko2

, uh.tok_nm
, uh.kanri_no
--TODO hosoku2 != ''
--#19646 2021.06.24 MOD STT
--, (select to_char(sum(um_kei.jucyu_su),'FM999,990') from t_uri_m um_kei where um_kei.den_no = uh.den_no) as suryo_kei
, (select to_char(sum(um_kei.jucyu_su),'FM999,990') from t_uri_m um_kei where um_kei.den_no = uh.den_no and um_kei.hosoku2 != '' and um_kei.set_row_no is null) as suryo_kei
--#19646 2021.06.24 MOD END
, uh.biko6

--, dh.hin_nm as dhin_nm
, um.hin_nm_part1 as dhin_nm
--, to_char(hi.size_h,'FM990.0')||' '||w.hosoku2_view as kind_s
, um.hin_nm_part2 as kind_s
--, hi.size_h
--, to_char(hi.size_h,'FM990.0')||'mm' as size_h_view
, um.hin_nm_part3||'mm' as size_h_view

/*
, (case(w.hosoku3_cat)when('11')then(w.hosoku3_nm||' '||m3.hin_nm)when('12')then(w.hosoku3)else(w.hosoku3_nm)end)
||(case when(w.hosoku5 != '')then(' '||(case when(w.hosoku3_dhin != '')then('+ ')else('')end)||w.hosoku5)else('')end)
||' / '
||(case(w.hosoku4_cat)when('11')then(w.hosoku4_nm||' '||m4.hin_nm)when('12')then(w.hosoku4)else(w.hosoku4_nm)end)
||(case when(w.hosoku6 != '')then(' '||(case when(w.hosoku4_dhin != '')then('+ ')else('')end)||w.hosoku6)else('')end)
as hin_nm_s
*/
--, (case when(h21a.koumoku1 != '')then(trim(split_part(um.meisai_biko,'/',1)))else('')end) as hin_nm_s1
--, (case when(h21b.koumoku1 != '')then(trim(split_part(um.meisai_biko,'/',2)))else('')end) as hin_nm_s2
--#19971 2021.07.16 MOD STT
--, um.hin_nm_ext_part1 as hin_nm_s1
--, um.hin_nm_ext_part2 as hin_nm_s2
--#20015 2021.07.24 MOD STT
--, (case when(h21a.koumoku1 != '')then(um.hin_nm_ext_part1)else('')end) as hin_nm_s1
--, (case when(h21b.koumoku1 != '')then(um.hin_nm_ext_part2)else('')end) as hin_nm_s2
, (case when(h21a.koumoku8 = '1')then(um.hin_nm_ext_part1)else('')end) as hin_nm_s1
, (case when(h21b.koumoku8 = '1')then(um.hin_nm_ext_part2)else('')end) as hin_nm_s2
--#20015 2021.07.24 MOD END
--#19971 2021.07.16 MOD END
, um.hosoku1

, to_char(wh.yotei_end_time,'yyyy/mm/dd') as nouki_ymd
, h20_1.han_name as koutei1_nm
, h20_2.han_name as koutei2_nm
, h20_3.han_name as koutei3_nm
, h20_4.han_name as koutei4_nm
, to_char(sm1.yotei_end_time,'yyyy/mm/dd') as koutei1_ymd
, to_char(sm2.yotei_end_time,'yyyy/mm/dd') as koutei2_ymd
, to_char(sm3.yotei_end_time,'yyyy/mm/dd') as koutei3_ymd
, to_char(sm4.yotei_end_time,'yyyy/mm/dd') as koutei4_ymd
, fnc_get_uri_pack_names(uh.den_no) as pack_keijyos
, h20.koumoku8 as report_type


from (

select distinct
  uh.den_no
, mt.sir_cd
, max(sh.shiji_no) as shiji_no
, min(sm.koutei_cd) as koutei_cd
, min(sm.yotei_end_time) as yotei_end_time
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 and sm.seizou_tan_cd != '')
inner join m_tanto mt on (mt.tan_cd = sm.seizou_tan_cd)

where 1 = 1
and uh.del_flg = 0
--and uh.jucyu_flg = 1
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 ((sm.hin_nm like '%&5%') or ('&5' = ''))
--and ((sm.nyuka_yotei_ymd >= '&8') or ('&8' = ''))
--and ((sm.nyuka_yotei_ymd <= '&9') or ('&9' = ''))
group by uh.den_no, mt.sir_cd

) wh

/*
inner join ( 
select um.den_no, um.row_no, um.hin_cd, um.hin_nm, um.tani, um.hosoku2, um.hosoku3, um.hosoku4, um.hosoku5, um.hosoku6, um.jucyu_su, um.tanka, um.kingaku, um.meisai_biko 
, mhd.dhin_cd as ddhin_cd, mh.dhin_cd, mh.size_h 
, h21a.koumoku1 as hosoku3_cat 
, h21b.koumoku1 as hosoku4_cat 
, h21a.koumoku6 as hosoku3_nm 
, h21b.koumoku6 as hosoku4_nm 
, (select min(c1.hin_cd) from m_hin c1 where c1.hin_rnm = um.hosoku3) as hosoku3_cd 
, (select min(c2.hin_cd) from m_hin c2 where c2.hin_rnm = um.hosoku4) as hosoku4_cd 
, (select min(dhin_cd) from m_hin where hin_cd = (select min(c1.hin_cd) from m_hin c1 where c1.hin_rnm = um.hosoku3)) as hosoku3_dhin 
, (select min(dhin_cd) from m_hin where hin_cd = (select min(c2.hin_cd) from m_hin c2 where c2.hin_rnm = um.hosoku4)) as hosoku4_dhin 
, mhd.hin_nm as dhin_nm 
, h26.koumoku3 as hosoku2_view 
, (select min(c1.hin_cd) from m_hin c1 where c1.hin_rnm = um.hosoku5) as hosoku5_cd 
, (select min(c2.hin_cd) from m_hin c2 where c2.hin_rnm = um.hosoku6) as hosoku6_cd 
, h21a.koumoku2 as hosoku3_cat_add 
, h21b.koumoku2 as hosoku4_cat_add 
from t_uri_m um 
inner join t_uri uh on (uh.den_no = um.den_no)
left join m_hin mh on (mh.hin_cd = um.hin_cd) 
left join m_hin mhd on (mhd.hin_cd = mh.dhin_cd) 
left join m_hanyo h26 on (h26.mst_kbn = '26' and h26.han_name = um.hosoku2) 
left join m_hanyo h21a on (h21a.mst_kbn = '21' and h21a.han_cd = h26.koumoku1) 
left join m_hanyo h21b on (h21b.mst_kbn = '21' and h21b.han_cd = h26.koumoku2) 
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 ((uh.den_no = fnc_to_number('&3')) or ('&3' = ''))
and ((uh.tok_cd = '&4') or ('&4' = ''))
--and ((um.hin_nm like '%&5%') or ('&5' = ''))
) w on (w.den_no = wh.den_no)
*/

inner join t_uri uh on (uh.den_no = wh.den_no)
inner join t_uri_m um on (um.den_no = uh.den_no)
left join m_sir ms on(ms.sir_cd = wh.sir_cd)
left join m_tanto t on (t.tan_cd = uh.tan_cd)
left join m_hin hi on(um.hin_cd = hi.hin_cd)
left join m_hin dh on(dh.hin_cd = hi.dhin_cd)
left join m_hanyo h26 on (h26.mst_kbn = '26' and h26.han_name = um.hosoku2)
left join m_hanyo h21a on (h21a.mst_kbn = '21' and h21a.han_cd = h26.koumoku1)
left join m_hanyo h21b on (h21b.mst_kbn = '21' and h21b.han_cd = h26.koumoku2)
--left join m_hin m3 on (m3.hin_cd = w.hosoku3_cd)
--left join m_hin m4 on (m4.hin_cd = w.hosoku4_cd)
--left join m_hanyo h26 on (h26.mst_kbn = '26' and h26.han_name = um.hosoku2)
left join m_hanyo h20 on (h20.mst_kbn = '20' and h20.han_cd = wh.koutei_cd)
left join m_hanyo h20_1 on (h20_1.mst_kbn = '20' and h20_1.koumoku7 = '1')
left join m_hanyo h20_2 on (h20_2.mst_kbn = '20' and h20_2.koumoku7 = '2')
left join m_hanyo h20_3 on (h20_3.mst_kbn = '20' and h20_3.koumoku7 = '3')
left join m_hanyo h20_4 on (h20_4.mst_kbn = '20' and h20_4.koumoku7 = '4')
left join t_seizou sh on (sh.jucyu_no = uh.den_no and sh.jucyu_row_no = -1)
left join t_seizou_m sm1 on (sm1.shiji_no = sh.shiji_no and sm1.koutei_cd = h20_1.han_cd)
left join t_seizou_m sm2 on (sm2.shiji_no = sh.shiji_no and sm2.koutei_cd = h20_2.han_cd)
left join t_seizou_m sm3 on (sm3.shiji_no = sh.shiji_no and sm3.koutei_cd = h20_3.han_cd)
left join t_seizou_m sm4 on (sm4.shiji_no = sh.shiji_no and sm4.koutei_cd = h20_4.han_cd)

where 1 = 1

order by uh.den_no, wh.sir_cd, um.row_no




[_selectRecords]


	from t_sir sh
left join m_tanto t on(sh.tan_cd = t.tan_cd)
left join t_sir_m sm on(sh.den_no = sm.den_no)
left join (select sm2.den_no,sum(1000*sm2.hacyu_su) as sum_hacyu_su from t_sir_m sm2 group by sm2.den_no ) sm2 on sm2.den_no = sh.den_no
left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya')
left join m_hanyo loc1 on (loc1.mst_kbn='17' and loc1.han_cd = sh.loc_no)
left join m_sir loc2 on (loc2.sir_cd = sh.loc_no)
left join v_hanyo_all loc3 on (loc3.han_cd = sh.loc_no and loc3.mst_kbn = 'loc')
where 1 = 1
and sh.hacyu_flg = '1' and sh.del_flg = 0
--and ((cast(sh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((sh.den_no = fnc_to_number('&3')) or ('&3' = ''))
and ((sh.sir_cd = '&4') or ('&4' = ''))
and ((sh.syori_ymd >= '&1') or ('&1' = '')) and ((sh.syori_ymd <= '&2') or ('&2' = ''))
--and ((sm.hin_nm like '%&5%') or ('&5' = ''))
and ((sm.nyuka_yotei_ymd >= '&8') or ('&8' = ''))
and ((sm.nyuka_yotei_ymd <= '&9') or ('&9' = ''))
order by sh.den_no,sm.row_no




[SelectRecords_Jisya]
select
	 w.*
	,'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
	,case j_email when '' then '' else ('EMAIL:' || j_email) end as j_email_s
	,w.j_address2 || w.j_address3 as j_address2_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 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 as j_daihyo koumoku9の0がなんなのかわからないので救急措置
		,j.koumoku8 as j_email
--		,j.koumoku9
--		,j.koumoku10
		,j.sort_key as sort_key
		,j3.mst_kbn
		,j3.han_cd
		,j3.han_name
		,j3.koumoku1 as koumoku1
		,j3.koumoku2 as koumoku2
		,j3.koumoku3 as koumoku3
		,j3.koumoku4 as koumoku4
		,j3.koumoku5 as koumoku5
		,j3.koumoku6 as mongon1
		,j3.koumoku7 as mongon2
		,j3.koumoku8 as mongon3
		,j3.koumoku9 as koumoku9
		,j3.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
		, case when coalesce(m3.han_name,'') = '1' then coalesce(m3.han_name,'')
			else '0' end as hin_cd_FLG
		, case when coalesce(m4.han_name,'') = '1' then coalesce(m4.han_name,'')
			else '0' end as sp1_FLG
		, case when coalesce(m5.koumoku1,'') = '1' then coalesce(m5.koumoku1,'')
			else '0' end as format_FLG
		, m5.koumoku6 as sp_mongon1
		, m5.koumoku7 as sp_mongon2
		, m5.koumoku8 as sp_mongon3
		, case when coalesce(m6.han_name,'') != '' then '1'
			else '0' end as dec_FLG
		, case when coalesce(m7.han_name,'') != '' then '1'
			else '0' end as cyokus_FLG
		, case when coalesce(m8.han_name,'') != '' then '1'
			else '0' end as dis_FLG
	from m_hanyo j
	left join m_hanyo j3 on (j3.mst_kbn = '90' and j3.han_cd = 'jisya3')
	left join m_hanyo m2 on (m2.mst_kbn = '90' and m2.han_cd = 'hin-title') --見出し行対応（数量・単価・金額の非表示）
	left join m_hanyo m3 on (m3.mst_kbn = '99' and m3.han_cd = 'sp-r0040-2') --注文書／品番表示フラグ
	left join m_hanyo m4 on (m4.mst_kbn = '99' and m4.han_cd = 'sp-r0040-1') --注文書／連続コード非表示
	left join m_hanyo m5 on (m5.mst_kbn = '90' and m5.han_cd = 'r0040-a') --注文書／文言
	left join m_hanyo m6 on (m6.mst_kbn = '99' and m6.han_cd = 'sir-su-dec') --数量小数管理
	left join m_hanyo m7 on (m7.mst_kbn = '99' and m7.han_cd = 'sir-cyokus') 
	left join m_hanyo m8 on (m8.mst_kbn = '90' and m8.han_cd = 'r0040-b') --注文書／明細レス率表示
	left join t_sir t on (t.den_no = fnc_to_number('&3'))
	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

[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-r0020' 
and han_name = '1'
;

[SelectImage]
select 
	file_image
from m_image
where image_cd = '&1'
;

[SelectSpecList]
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 limit 5