[selectRecords]
select 
	sh.den_no,
	(1+sm.row_no) as row_no,
	trunc((row_no)/15) as row_page,
	to_char(to_date(sh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	case sh.syori_ymd
	when '' then ''
	else to_char(to_date(sh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as syori_ymd,
	sh.sir_cd,
	--sh.sir_nm,
	t.tan_nm,
	(case coalesce(sh.sir_nm,'')
        when '' then '' else sh.sir_nm ||'　'||
			(case coalesce(sh.sir_tan,'')
				when '' then (case coalesce(sh.keisyo,'') 
								when '' then (case coalesce(ms.keisyo,'')
			when '' then '御中' else ms.keisyo end)
		 else sh.keisyo end) else '' end)
			end) as sir_nm,
	(case coalesce(sh.sir_tan,'')
        when '' then ''
        else sh.sir_tan ||'　'||(case coalesce(sh.keisyo,'') 
			when '' then (case coalesce(ms.keisyo,'')
			when '' then '様' else ms.keisyo end)
				 else sh.keisyo end)
		end) as sir_tan,
	han.han_name as tori_cond,
	sh.zeinuki_gaku,
	sh.zei_gaku,
	--sh.sir_tan,
	sh.zeikomi_gaku,
	sh.tan_cd,
	--sm.hin_cd,
	case when '&7' = sm.hin_cd then null
		else sm.hin_cd
		end as hin_cd,
--#18031 2021.09.10 MOD STT
--	replace(sm.hin_nm,chr(10),'')as hin_nm,
	(case when(hi.sir_hin_cd != '')then(hi.sir_hin_cd||' ')else('')end) || replace(sm.hin_nm,chr(10),'')as hin_nm,
--#18031 2021.09.10 MOD END
	--case when (position(' ' in (replace(sm.hin_cd,'　',' ')))) = '0' then sm.hin_cd
	--else substring(sm.hin_cd,0,(position(' ' in (replace(sm.hin_cd,'　',' ')))))
	--end as hin_cd,
	--case when (position(' ' in (replace(sm.hin_nm,'　',' ')))) = '0' then sm.hin_nm
	--else substring(sm.hin_nm,0,(position(' ' in (replace(sm.hin_nm,'　',' ')))))
	--end as hin_nm,
	sm.hacyu_no,
	--sm.hacyu_su as hacyu_su,
	case when '&7' = sm.hin_cd then null
		else case when hi.tani ='g' then (round(sm.hacyu_su/1000,2))
		when hi.tani ='ｇ' then (round(sm.hacyu_su/1000,2))
--#19286 2021.06.11 MOD STT
--		else sm.hacyu_su end
		else COALESCE(sm.iri_hacyu_su,sm.hacyu_su) end
--#19286 2021.06.11 MOD END
		end as hacyu_su,
--#19286 2021.06.11 MOD STT
	(case when(sm.iri_hacyu_su is not null)then(sm.hacyu_su)else(null)end) as hacyu_su_lower,
	(case when(sm.iri_hacyu_su is not null)then(sm.tani)else(null)end) as tani_lower,
--#19286 2021.06.11 MOD END
	sm.sir_su,
	case when '&7' = sm.hin_cd then null
		else case when hi.tani ='g' then (round(1000*sm.tanka))
		when hi.tani ='ｇ' then (round(1000*sm.tanka))
		else sm.tanka end
		end  as tanka,
	--round(1000*sm.tanka) as tanka,
	case when '&7' = sm.hin_cd then null
		else sm.kingaku
		end as kingaku,
	coalesce(cast(sm.hacyu_no as varchar(10)),'')||'-'|| coalesce (cast(sm.row_no as varchar(10)),'') as hacyu_row_no,
	--to_char(to_date(sm.nyuka_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as nyuka_yotei_ymd,
	case (sh.nyuka_yotei_ymd)
	when '' then ''
	else '希望納期：'
	end as nouki_lbl,
	case sh.nyuka_yotei_ymd
	when '' then ''
	else to_char(to_date(sh.nyuka_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd')
	end as nouki_ymd,
	case when '&7' = sm.hin_cd then null
		else case sm.nyuka_yotei_ymd
		when '' then ''
		else to_char(to_date(sm.nyuka_yotei_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') end
		end as nouki_m_ymd,
	--sh.biko1,
	replace(replace(sh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1,
	--sh.biko2,
	replace(replace(sh.biko2,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko2,
	--t.biko3,
	--t.biko4,
	--t.tok_nm,
	--'〒'||substr(t.zip_no,1,3)||'-'||substr(t.zip_no,4,7) as yubin_no,
	--t.addr1,
	--t.addr2,
	--t.addr3,
	--t.addr4,
	--t.tori_cond,
	hi.sir_hin_cd,
--	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then ''
	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then hi.sir_hin_cd
	else substring(hi.sir_hin_cd,0,(position(' ' in (replace(hi.sir_hin_cd,'　',' ')))))
	end as s_hin_cd,
	hi.sir_hin_nm,
--	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then ''
	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then hi.sir_hin_nm
	else substring(hi.sir_hin_nm,0,(position(' ' in (replace(hi.sir_hin_nm,'　',' ')))))
	end as s_hin_nm,
	case when (position(' ' in (replace(hi.sir_hin_cd,'　',' ')))) = '0' then ''
	else substring(hi.sir_hin_cd,(1+position(' ' in (replace(hi.sir_hin_cd,'　',' ')))))
	end as cor,
	case when (position(' ' in (replace(hi.sir_hin_nm,'　',' ')))) = '0' then ''
	else substring(hi.sir_hin_nm,(1+position(' ' in (replace(hi.sir_hin_nm,'　',' ')))))
	end as cor_nm,
	(sm2.sum_hacyu_su/1000)as sum_hacyu_su,
	hi.sir_bante as sir_bante,
	sm.hosoku1 as sitate,
	sm.meisai_biko as m_biko,
	hi.biko1 as m_hin_biko,
	case when hi.tani = 'g' then 'Kg'
	else hi.tani
	end as tani,
	case sh.sir_tan
	when '' then '御中'
	else ''
	end as keisyou1,
	case sh.sir_tan
	when '' then ''
	else '様'
	end as keisyou2
	
	, j.mst_kbn
	, j.han_cd
	, j.han_name
	, j.koumoku1 as post_s
	, j.koumoku2
	, j.koumoku3
	, j.koumoku4
	, j.koumoku5
	, 'TEL:' || j.koumoku5 as tel_s
	, j.koumoku6
	, 'FAX:' || j.koumoku6 as fax_s
	, j.koumoku7
	, 'URL:' || j.koumoku7 as url_s
	, j.koumoku8
	, j.koumoku9
	, case coalesce(loc1.koumoku6,'')
		when '' then 
			case coalesce(loc1.han_name,'')
			when '' then coalesce('入庫場所：'||loc2.sir_nm,'')
			else coalesce('入庫場所：'||loc1.han_name,'')
			end
		else coalesce('入庫場所：'||loc1.koumoku6,'')
		end as loc_nm
	, case coalesce(loc1.koumoku1,'') when '' then coalesce('住所：'||loc2.addr1,'')||coalesce(loc2.addr2,'')||coalesce(loc2.addr3,'')||coalesce(loc2.addr4,'')
		else coalesce('住所：'||loc1.koumoku1,'')
		end as loc_addr
--#19286 2021.06.11 MOD STT
--	, sm.tani as hin_tani
	, (case when(sm.iri_hacyu_su is not null)then(sm.iri_tani)else(sm.tani)end) as hin_tani
--#19286 2021.06.11 MOD END
	, hi.iri_su
	--add.2020.08.06
	,'TEL:' || ms.tel_no as tel_no_s
	,'FAX:' || ms.fax_no as fax_no_s
	, loc3.han_name as v_loc_nm
	, (case when(sh.tok_nm != '')then(sh.tok_nm)else(loc3.han_name)end) as tok_nm
	, case coalesce(sh.tok_zip_no,'') when '' then '' else '〒'||substr(sh.tok_zip_no,1,3)||'-'||substr(sh.tok_zip_no,4,4) end as tok_zip_no_s
	, sh.tok_addr
	, case coalesce(sh.tok_tel_no,'') when '' then '' else 'TEL:'||sh.tok_tel_no end as tok_tel_no_s
	, sm.htanka
	, sm.disc_per
	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 m_sir ms on(sh.sir_cd = ms.sir_cd)
left join m_hanyo han on(ms.tori_cond = han.han_cd and mst_kbn = '10')
left join m_hin hi on(sm.hin_cd = hi.hin_cd)
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::varchar = '&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
		, COALESCE(m9.koumoku1,'納入先（送り先）') as cyokus_title
	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 m_hanyo m9 on (m9.mst_kbn = '90' and m9.han_cd = 'r0040.cykt') --注文書直送先タイトル
	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'
;


[selectRecords_old]
select 
	sh.den_no,
	(1+sm.row_no) as row_no,
	to_char(to_date(sh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd,
	to_char(to_date(sh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd,
	sh.zeinuki_gaku,
	sh.zei_gaku,
	sh.zeikomi_gaku,
--	sm.hin_cd,
	(case when(mh.jan_cd != '')then(mh.jan_cd)else(sm.hin_cd)end) as hin_cd,
	replace(sm.hin_nm,chr(10),'')as hin_nm,
	sm.suryo,
	sm.tanka,
	sm.kingaku,
	t.sir_cd,
	t.sir_nm,
	t.addr1,
	t.addr2,
	t.addr3,
	t.addr4,
	t.tori_cond,
	t.biko1,
	t.biko2,
	t.biko3,
	t.biko4
from t_sir sh
left join m_sir t on(sh.sir_cd = t.sir_cd)
left join t_sir_m sm on(sh.den_no = sm.den_no)
left join m_hin mh on (mh.hin_cd = sm.hin_cd)
where 1 = 1
and ((sh.syori_ymd >= '&1') or ('&1' = ''))
and ((sh.syori_ymd <= '&2') or ('&2' = ''))
and ((cast(sh.den_no as char(6)) = '&3') or ('&3' = ''))
and ((sh.tok_cd = '&4') or ('&4' = ''))
and ((sm.hin_nm like '%&5%') or ('&5' = ''))
order by sh.den_no,sm.row_no;