[selectRecords]
select
	'請求明細書' as tait
	,uh.den_no
    , uh.eigyou_tan_cd
	, (case when(h99d.han_name = '1')then('')else('担当：' || tan.tan_nm)end) as eigyou_tan_nm_s
	,(case when(h99a.han_name = '1')then(um.rel_row_no)else(1+um.row_no)end) as row_no
	,(case when(h99a.han_name = '1')then(um.rel_row_no)else((row_number() over(partition by um.den_no order by um.row_kind,um.row_no)))end) as w_row_no
	,to_char(to_date(uh.input_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as input_ymd
	,to_char(to_date(uh.syori_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd
	,uh.zeinuki_gaku
	,uh.zei_gaku
	,uh.zeikomi_gaku
--#24485 2022.06.25 MOD STT
--	,(case when('&12' = um.hin_cd or um.set_row_no is not null)then(null)else(um.hin_cd)end) as hin_cd
--#26478 2022.12.20 MOD STT
--	,(case when('&12' = um.hin_cd or um.set_row_no is not null)then(null)when(h99n.han_name != '')then(substr(um.hin_cd,1,cast(h99n.han_name as int)))else(um.hin_cd)end) as hin_cd
	,(case when('&12' = um.hin_cd or um.set_row_no is not null)then(null)when(h99n.han_name != '')then(substr(um.hin_cd,1,cast(h99n.han_name as int)))else( case when(um.input_hin_cd != '')then(um.input_hin_cd)else(um.hin_cd)end  )end) as hin_cd
--#26478 2022.12.20 MOD END
--#24485 2022.06.25 MOD END
	,replace(um.hin_nm,chr(10),'')as hin_nm
	,(case when('&12' = um.hin_cd or um.set_row_no is not null)then(null)else(case when(uh.jucyu_flg = 1)then(um.jucyu_su)else(um.suryo)end)end) as suryo
	,(case when('&12' = um.hin_cd or '&14' = '1')then(null)else(um.tanka)end) as tanka
	,(case when('&12' = um.hin_cd or '&15' = '1')then(null)else(um.htanka)end) as htanka
	,(case when('&12' = um.hin_cd or um.set_row_no is not null or '&14' = '1')then(null)else(um.kingaku)end) as kingaku
	,(case when('&12' = um.hin_cd or '&16' = '1' or coalesce(um.disc_per,0) = 0)then(null)else(case when(h99c.koumoku2 = 'rev')then(100 - coalesce(um.disc_per,0))else(coalesce(um.disc_per,0))end)end) as disc_per
	,(case when(um.hin_nm_ext != '')then(um.hin_nm_ext||' ')else('')end)
		||(case when(h99j.han_name = '1')then('')else(COALESCE(um.meisai_biko,''))end)
		||(case when(h99i.han_name = '1' and um.tok_hacyu_no != '')then('お客様注文No:'||um.tok_hacyu_no)else('')end)
		||(case when('&15' != '1' and h99e.han_name = '1' and um.htanka != 0 and COALESCE(um.zei_kbn,0) = 0)then('税込定価 '||to_char(um.htanka + fnc_get_zei(um.htanka,uh.syori_ymd),'FM999,999,990'))else('')end)
	as meisai_biko
	,(case when('&17' = '1')then(mh.loc_no)else('')end) as loc_no
	,to_char(to_date(nullif(uh.nonyu_ymd,''), 'YYYYMMDD'), 'yyyy/mm/dd') as nonyu_ymd
	,uh.nonyu_no
	, uh.nonyu_nm || (case when(uh.nonyu_tan != '')then('　'||uh.nonyu_tan)else('')end)||'　'||fnc_get_keisyo(uh.nonyu_nm, uh.nonyu_tan, '') as nonyu_nm
	, uh.tok_nm || (case when(uh.tok_tan != '')then('　'||uh.tok_tan)else('')end)||'　'||fnc_get_keisyo(uh.tok_nm, uh.tok_tan, uh.keisyo) as tok_nm
	, (case when(t.hojin_nm != '' and '&21' = '')then(t.hojin_nm)else( uh.tok_nm||'　'||fnc_get_keisyo_1(uh.tok_nm, uh.tok_tan, uh.keisyo) )end) as tok_nm_s
	, (case when(t.hojin_nm != '' and '&21' = '')then(uh.tok_nm||'　'||fnc_get_keisyo_1(uh.tok_nm, uh.tok_tan, uh.keisyo))else( (case when(uh.tok_tan != '')then(uh.tok_tan||'　'||fnc_get_keisyo_2(uh.tok_nm, uh.tok_tan, uh.keisyo))end) )end) as tok_tan
	, (case when(t.hojin_nm != '' and '&21' = '')then((case when(uh.tok_tan != '')then(uh.tok_tan||'　'||fnc_get_keisyo_2(uh.tok_nm, uh.tok_tan, uh.keisyo))end))else( '' )end) as tok_nm_add
	, case when &9 = '1' then (
					case uh.nonyu_zip_no when '' then ''
					else '〒'||left(uh.nonyu_zip_no,3)||'-'||right(uh.nonyu_zip_no,4)
	 				end)
       		else ''
       		end as zip_no
	, case when &9 = '1' then uh.nonyu_addr1
       		else ''
       		end as addr1
    , case when &9 = '1' then uh.nonyu_addr2
       		else ''
       		end as addr2
    , case when &9 = '1' then uh.nonyu_addr3
       		else ''
       		end as addr3
    , case when &9 = '1' then uh.nonyu_addr4
       		else ''
       		end as addr4
    , case when &9 = '1' then coalesce(uh.nonyu_tel_no,'')
       		else ''
       		end as nonyu_tel
    , case when &9 = '1' then coalesce(uh.nonyu_fax_no,'')
       		else ''
       		end as nonyu_fax
	, (case when(h99h.han_name = '1')then('')else(fnc_get_report_multiline(uh.payment_term))end) as tori_cond
	, replace(replace(uh.biko1,chr(13)||chr(10),chr(10)),chr(10),chr(13)||chr(10)) as biko1
	, trunc(um.row_no / 15) as page_no
	, trunc((row_number() over(partition by um.den_no order by um.row_kind,um.row_no) - 1)/15) as w_page_no
--#24493 2022.06.25 MOD STT
--	, mh.tani
	, um.tani
--#24493 2022.06.25 MOD END
	, (case when(h99f.han_name = '1')then( select '合計数量 '||to_char(sum(um_kei.suryo),'FM999,990') from t_uri_m um_kei where um_kei.den_no = uh.den_no and '&12' != um_kei.hin_cd )else('')end) as suryo_kei_view
	, (case when(&11 = '0' and h99k.han_name = '1')then('お客様ｺｰﾄﾞ:'||coalesce(uh.tok_cd,''))else('')end) as tok_cd_bottom_view
--#23704 2022.05.27 MOD STT
	--常に出す
--	, (case when(&11 = '0' and COALESCE(h99k.han_name,'') != '1')then('お客様ｺｰﾄﾞ:'||coalesce(uh.tok_cd,''))else('')end) as tok_cd
	, 'お客様ｺｰﾄﾞ:'||coalesce(uh.tok_cd,'') as tok_cd
--#23704 2022.05.27 MOD END
	, case when &9 = '1' then (case uh.tok_zip_no
		when '' then ''
		else '〒'||left(uh.tok_zip_no,3)||'-'||right(uh.tok_zip_no,4)
	 	end)
       		else (case uh.nonyu_zip_no
		when '' then ''
		else '〒'||left(uh.nonyu_zip_no,3)||'-'||right(uh.nonyu_zip_no,4)
	 	end)
	 end as tok_zip_no
	, case when &9 = '1' then uh.tok_addr1 --書類送付先機能がONの場合&9は1が入りtok_addrシリーズを使う
       		else uh.nonyu_addr1 --書類送付先機能がOFFの場合&9は0が入りnonyu_addrシリーズを使う、画面上が空白なら空になる
       		end as tok_addr1
    , case when &9 = '1' then uh.tok_addr2
       		else uh.nonyu_addr2
       		end as tok_addr2
    , case when &9 = '1' then uh.tok_addr3
       		else uh.nonyu_addr3
       		end as tok_addr3
    , case when &9 = '1' then uh.tok_addr4
       		else uh.nonyu_addr4
       		end as tok_addr4
    , case when &9 = '1' then coalesce(uh.tok_tel_no,'')
       		else coalesce(uh.nonyu_tel_no,'')
       		end as tok_tel
    , case when &9 = '1' then coalesce(uh.tok_fax_no,'')
       		else coalesce(uh.nonyu_fax_no,'')
       		end as tok_fax
	,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_email
	,'TEL:' || j.koumoku5 as j_tel_s
	,'FAX:' || j.koumoku6 as j_fax_s
--#26348 2022.12.02 MOD STT
--	,(case when j.koumoku7 != '' then 'URL:' || j.koumoku7 else '' end) as j_url_s
	,(case when j.koumoku7 != '' then coalesce(h98a.koumoku1,'URL:') || j.koumoku7 else '' end) as j_url_s
--#26348 2022.12.02 MOD END
	,(case when j.koumoku8 != '' then 'EMAIL:' || j.koumoku8 else '' end) as j_email_s
	,uh.eig_cd
	,(case when(h99b.han_name = '1')then('お客様注文No:'||COALESCE(uh.tok_hacyu_no,''))else('')end) as tok_hacyu_no_h
	, (case when(h99g.han_name = '1')then( '['||COALESCE(t.cat_cd,'')||']' )else('')end) as tok_cat_cd
	, (trunc((row_number()over(order by uh.tok_cd,um.den_no) - 1) / 15) + 1) as row_page
--	, (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
	, (select ceil(count(um2.den_no)/15.0) as max from t_uri_m um2 where um2.den_no = uh.den_no ) as max_page
	,um.zei_rate
	, m90.koumoku1 as mongon1
	, m90.koumoku2 as mongon2
	, m90.koumoku3 as mongon3
	, m90.koumoku4 as mongon4
	, replace(m90.koumoku5,'%tok_cd%',uh.tok_cd) as mongon5
	, (case when(m90.koumoku6 != '')then(m90.koumoku6)else('下記の通りご請求申し上げます。')end) as mongon6
	, '消費税'||(case when(h99l.han_name = '1')then('('||to_char(uh.zei_rate,'FM990')||'%)')else('')end) as total_zei_label
	, h99m.han_name as sp4_kbn
	, um.row_kind
from (

--通常明細
select
	 1 as row_kind
	,uh.den_no
	,um.row_no
	,um.rel_row_no
	,um.set_row_no
--#19593 2021.06.18 MOD STT
--JANコードの印字は止める
--	,(case when('&12' = um.hin_cd or um.set_row_no is not null)then(null)else(case when(mh.jan_cd != '')then(mh.jan_cd)else(um.hin_cd)end)end) as hin_cd
--#20312 2021.08.14 MOD STT
--	,(case when('&12' = um.hin_cd or um.set_row_no is not null)then(null)else(um.hin_cd)end) as hin_cd
--	,(case when('&12' = um.hin_cd or h0f.koumoku1 = '1')then(null)else(um.hin_cd)end) as hin_cd
	, um.hin_cd
--#20312 2021.08.14 MOD END
--#19593 2021.06.18 MOD END
	,replace(um.hin_nm,chr(10),'')as hin_nm
--	,(case when('&12' = um.hin_cd)then(null)else(case when(&8 = 1)then(um.jucyu_su)else(um.suryo)end)end) as suryo
	,um.suryo
	,um.jucyu_su
--#20312 2021.08.14 MOD STT
--	,(case when('&12' = um.hin_cd)then(null)else(um.tanka)end) as tanka
--	,(case when('&12' = um.hin_cd or h0f.koumoku2 = '1')then(null)else(um.tanka)end) as tanka
	, (case when(uh.bundle_title != '')then(null)else(um.tanka)end) as tanka
--#20312 2021.08.14 MOD END
	,um.htanka
--#20312 2021.08.14 MOD STT
--	,(case when('&12' = um.hin_cd)then(null)else(um.kingaku)end) as kingaku
--	,(case when('&12' = um.hin_cd or h0f.koumoku2 = '1')then(null)else(um.kingaku)end) as kingaku
--	, um.kingaku
	, (case when(uh.bundle_title != '')then(null)else(um.kingaku)end) as kingaku
--#20312 2021.08.14 MOD END
--#24493 2022.06.25 MOD STT
--	, mh.tani
	, um.tani
--#24493 2022.06.25 MOD END
	, coalesce(um.zei_rate,uh.zei_rate) as zei_rate
	, um.disc_per
	, um.hin_nm_ext
	, um.meisai_biko
	, um.tok_hacyu_no
	, um.zei_kbn
	, um.input_hin_cd
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 m_hin mh on (mh.hin_cd = um.hin_cd)
--left join m_hanyo j on (j.mst_kbn='90' and j.han_cd='jisya')
--left join m_hanyo jf on (jf.mst_kbn='09' and jf.han_cd='0')
--left join m_hanyo h1 on (h1.mst_kbn = '09' and h1.han_cd = (case when(t.furikikan != '')then(t.furikikan)else(j.koumoku9)end))
left join m_hanyo h0f on (h0f.mst_kbn = '0F' and h0f.han_cd = um.dtl_kbn)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'nstp-r0024')
where 1 = 1
and uh.del_flg = 0
and coalesce(uh.mitsumori_flg,0) = 0
and uh.jucyu_flg = &8
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.den_no = cast('0'||'&3' as int)) or ('' = '&3'))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
--and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = fnc_to_number('&7')) ))
and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = cast('0'||'&7' as int)) ))
--#22019 2021.12.09 MOD STT
--TODO セット親を除く
and ((COALESCE(h99a.han_name,'') != '1') or (COALESCE(um.dtl_kbn,'') != 'P'))
--#22019 2021.12.09 MOD END

--税率1
union all
select
	 8 as row_kind
	,uh.den_no
	,cast(null as numeric(10)) as row_no
	,cast(null as numeric(10)) as rel_row_no
	,cast(null as numeric(10)) as set_row_no
	,cast(null as varchar(255)) as hin_cd
	,'【消費税（'||COALESCE(mzei.han_name,'')||'）】課税対象額 '||to_char(uh.kazei_gaku,'FM999,999,999,990')
	,cast(null as numeric(9,2)) as suryo
	,cast(null as numeric(9,2)) as jucyu_su
	,cast(null as numeric(10,2)) as tanka
	,cast(null as numeric(12,2)) as htanka
	,uh.soto_gaku as kingaku
	,cast(null as varchar(10)) as tani
	,cast(null as numeric(5,2)) as zei_rate
	,cast(null as numeric(5,2)) as disc_per
	,cast(null as varchar(255)) as hin_nm_ext
	,cast(null as varchar(255)) as meisai_biko
	,cast(null as varchar(30)) as tok_hacyu_no
	,cast(null as numeric(2)) as zei_kbn
	,cast(null as varchar(255)) as input_hin_cd
from t_uri uh
left join m_hanyo h99z on (h99z.mst_kbn = '99' and h99z.han_cd = 'zeid-r0024')
left join m_hanyo mzei on (mzei.mst_kbn = '93' and uh.syori_ymd between mzei.koumoku1 and mzei.koumoku2)
where 1 = 1
and uh.del_flg = 0
and coalesce(uh.mitsumori_flg,0) = 0
and uh.jucyu_flg = &8
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.den_no = cast('0'||'&3' as int)) or ('' = '&3'))
and ((uh.tok_cd = '&4') or ('&4' = ''))
--and (('&7' = '') or ((uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = fnc_to_number('&7'))))
and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = cast('0'||'&7' as int)) ))
and h99z.han_name = '1'
and uh.soto_gaku != 0

--税率2
union all
select
	 9 as row_kind
	,uh.den_no
	,cast(null as numeric(10)) as row_no
	,cast(null as numeric(10)) as rel_row_no
	,cast(null as numeric(10)) as set_row_no
	,cast(null as varchar(255)) as hin_cd
	,'【消費税（'||COALESCE(mzei.koumoku4,'')||'）】課税対象額 '||to_char(uh.kazei_gaku2,'FM999,999,999,990')
	,cast(null as numeric(9,2)) as suryo
	,cast(null as numeric(9,2)) as jucyu_su
	,cast(null as numeric(10,2)) as tanka
	,cast(null as numeric(12,2)) as htanka
	,uh.soto_gaku2 as kingaku
	,cast(null as varchar(10)) as tani
	,cast(null as numeric(5,2)) as zei_rate
	,cast(null as numeric(5,2)) as disc_per
	,cast(null as varchar(255)) as hin_nm_ext
	,cast(null as varchar(255)) as meisai_biko
	,cast(null as varchar(30)) as tok_hacyu_no
	,cast(null as numeric(2)) as zei_kbn
	,cast(null as varchar(255)) as input_hin_cd
from t_uri uh
left join m_hanyo h99z on (h99z.mst_kbn = '99' and h99z.han_cd = 'zeid-r0024')
left join m_hanyo mzei on (mzei.mst_kbn = '93' and uh.syori_ymd between mzei.koumoku1 and mzei.koumoku2)
where 1 = 1
and uh.del_flg = 0
and coalesce(uh.mitsumori_flg,0) = 0
and uh.jucyu_flg = &8
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.den_no = cast('0'||'&3' as int)) or ('' = '&3'))
and ((uh.tok_cd = '&4') or ('&4' = ''))
--and (('&7' = '') or ((uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = fnc_to_number('&7'))))
and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = cast('0'||'&7' as int)) ))
and h99z.han_name = '1'
and uh.soto_gaku2 != 0

--一括表示
union all
select
	 0 as row_kind
	,uh.den_no
	,cast(null as numeric(10)) as row_no
	,cast(null as numeric(10)) as rel_row_no
	,cast(null as numeric(10)) as set_row_no
	,cast(null as varchar(255)) as hin_cd
	,uh.bundle_title as hin_nm
	,cast(null as numeric(9,2)) as suryo
	,cast(null as numeric(9,2)) as jucyu_su
	,cast(null as numeric(10,2)) as tanka
	,cast(null as numeric(12,2)) as htanka
	,(coalesce(uh.zeikomi_gaku,0) - coalesce(uh.soto_gaku,0) - coalesce(uh.soto_gaku2,0)) as kingaku --TODO
	,cast(null as varchar(10)) as tani
	,cast(null as numeric(5,2)) as zei_rate
	,cast(null as numeric(5,2)) as disc_per
	,cast(null as varchar(255)) as hin_nm_ext
	,cast(null as varchar(255)) as meisai_biko
	,cast(null as varchar(30)) as tok_hacyu_no
	,cast(null as numeric(2)) as zei_kbn
	,cast(null as varchar(255)) as input_hin_cd
from t_uri uh
where 1 = 1
and uh.del_flg = 0
and coalesce(uh.mitsumori_flg,0) = 0
and uh.jucyu_flg = &8
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((uh.den_no = cast('0'||'&3' as int)) or ('' = '&3'))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = cast('0'||'&7' as int)) ))
and uh.bundle_title != ''

) um
--t_uri uh
inner join t_uri uh on (uh.den_no = um.den_no)
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 mh on (mh.hin_cd = um.hin_cd)
left join m_hanyo h5 on (h5.mst_kbn = '98' and h5.han_cd = 'r-nonyunm')
left join m_tanto tan on (uh.eigyou_tan_cd = tan.tan_cd)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'uri-relrow')
left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'aith-r0023')
left join m_hanyo h99c on (h99c.mst_kbn = '99' and h99c.han_cd = 'tok-disc' and h99c.han_name = '1')
left join m_hanyo h99d on (h99d.mst_kbn = '99' and h99d.han_cd = 'heta-r0023')
left join m_hanyo h99e on (h99e.mst_kbn = '99' and h99e.han_cd = 'sp-r0023-1')
left join m_hanyo h99f on (h99f.mst_kbn = '99' and h99f.han_cd = 'sttl-r0023')
left join m_hanyo h99g on (h99g.mst_kbn = '99' and h99g.han_cd = 'tcat-r0023')
left join m_hanyo h99h on (h99h.mst_kbn = '99' and h99h.han_cd = 'npay-r0023')
left join m_hanyo h99i on (h99i.mst_kbn = '99' and h99i.han_cd = 'aitm-r0023')
left join m_hanyo h99j on (h99j.mst_kbn = '99' and h99j.han_cd = 'sp-r0023-2')
left join m_hanyo h99k on (h99k.mst_kbn = '99' and h99k.han_cd = 'sp-r0023-3')
left join m_hanyo h99l on (h99l.mst_kbn = '99' and h99l.han_cd = 'zeir-r0023')
left join m_hanyo h99m on (h99m.mst_kbn = '99' and h99m.han_cd = 'sp-r0023-4')
left join m_hanyo h99n on (h99n.mst_kbn = '99' and h99n.han_cd = 'sp-r0023-5')
left join m_hanyo eig on (eig.mst_kbn = '99' and eig.han_cd = 'uri-eig')
left join m_hanyo j on (j.mst_kbn = '90' and j.han_cd = 'jisya' || (case when(eig.han_name = '1' and uh.eig_cd != '')then('_' || uh.eig_cd)else('')end))
left join m_hanyo m90 on (m90.mst_kbn = '90' and m90.han_cd = '&22') --文言マスタ mst_kbn=03,koumoku7(ex: r0020.s,r0020.n)
left join m_hanyo h98a on (h98a.mst_kbn = '98' and h98a.han_cd = 'jisya-k07')
where 1 = 1
and uh.del_flg = 0
and uh.jucyu_flg = &8
and COALESCE(uh.mitsumori_flg,0) = 0
and COALESCE(uh.via_seq,0) = 0
and ((uh.syori_ymd >= '&1') or ('&1' = ''))
and ((uh.syori_ymd <= '&2') or ('&2' = ''))
and ((uh.input_ymd >= '&19') or ('&19' = ''))
and ((uh.input_ymd <= '&20') or ('&20' = ''))
--and ((uh.den_no = fnc_to_number('&3')) or ('' = '&3'))
and ((uh.den_no = cast('0'||'&3' as int)) or ('' = '&3'))
and ((uh.tok_cd = '&4') or ('&4' = ''))
and ((um.hin_nm like '%&5%') or ('&5' = ''))
--and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = fnc_to_number('&7')) ))
and (('&7' = '') or ( (uh.tok_cd,uh.seikyu_ymd) in (select tok_cd, seikyu_ymd from w_basr0030 w where w.s_id = cast('0'||'&7' as int)) ))
and ((uh.eig_cd = '&18') or ('&18' = ''))
order by uh.den_no, um.row_kind, um.row_no




[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
	,(case j_email
		when '' then ''
		else 'EMAIL:' ||j_email
	end) as j_email_s
	,w.*
--#20519 2021.08.30 MOD STT
	--,replace(replace('&1','(定価非表示)',''),'(金額無し)','') as report_title
--#20620 2021.09.04 MOD STT
--	,(case('&4')when('1')then('納品書')when('')then('請求書')else('&1')end) as report_title
	,(case when('&5' != '')then('&5')else( (case('&4')when('1')then('納品書')when('')then('請求書')else('&1')end) )end) as report_title
--#20620 2021.09.04 MOD END
--#20519 2021.08.30 MOD END
from(
	select
		 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
		,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
		,case when coalesce(m1.koumoku1,'') = '' then ''
			else coalesce(m1.koumoku1,'') end as mongon1 --1行目
		,case when coalesce(m1.koumoku2,'') = '' then ''
			else coalesce(m1.koumoku2,'') end as mongon2 --1行目
		,case when coalesce(m1.koumoku3,'') = '' then ''
			else coalesce(m1.koumoku3,'') end as mongon3 --2行目
		,case when coalesce(m1.koumoku4,'') = '' then ''
			else coalesce(m1.koumoku4,'') end as mongon4 --2行目
		,case when coalesce(m1.koumoku5,'') = '' then ''
			else coalesce(m1.koumoku5,'') end as mongon5 --3行目
		,case when coalesce(m1.koumoku6,'') = '' then '下記の通りご請求申し上げます。'
			else coalesce(m1.koumoku6,'') end as mongon6
		/*,m1.koumoku1 as mongon1
		,m1.koumoku2 as mongon2
		,m1.koumoku3 as mongon3
		,m1.koumoku4 as mongon4
		,m1.koumoku5 as mongon5
		, '毎度お引き立てくださいましてありがとうございます。' 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(ar1.han_name,'') = '1' then coalesce(ar1.han_name,'')
			else '0' end as uri_tokadr_kbn
		, case when coalesce(sp1.han_name,'') = '1' then coalesce(sp1.han_name,'')
			else '0' end as tok_cd_FLG
		, case when coalesce(sp2.han_name,'') = '1' then coalesce(sp2.han_name,'')
			else '0' end as hin_cd_FLG
		--, case when coalesce(rev.han_name,'') = '1' then coalesce(sp1.han_name,'')
		, rev.koumoku2 as rev
		, (case when(h98a.han_name != '')then(h98a.han_name)else('発行日')end)||'：' as syori_ymd_label
		, (case when(h98b.han_name != '')then(h98b.han_name)else('管理番号')end)||'：' as den_no_label
		, (case when(h98c.han_name != '')then(h98c.han_name)else('定価')end) as htanka_label
		, (case when(h99a.han_name != '')then(cast(h99a.han_name as int))else(0)end) as uri_tn_dec
		, (case when(h99b.han_name = '1')then(1)else(0)end) as furikomi_head_flg
	from m_hanyo j
	left join m_hanyo j2 on (j2.mst_kbn = '90' and j2.han_cd = 'jisya2') --自社キャッチフレーズマスタ
	left join m_hanyo m1 on (m1.mst_kbn = '90' and m1.han_cd = '&2') --文言マスタ mst_kbn=03,koumoku7(ex: r0020.s,r0020.n)
	left join m_hanyo m2 on (m2.mst_kbn = '90' and m2.han_cd = 'hin-title') --見出し行対応（数量・単価・金額の非表示）
	left join m_hanyo ar1 on (ar1.mst_kbn='99' and ar1.han_cd='uri-tokadr') --住所出力区分
	left join m_hanyo sp1 on (sp1.mst_kbn='99' and sp1.han_cd='sp-r0020-1') --納品書／得意先コード非表示
	left join m_hanyo sp2 on (sp2.mst_kbn='99' and sp2.han_cd='sp-r0020-2') --納品書／連続商品コード非表示
	--left join t_uri t on (t.den_no = fnc_to_number('&3'))
	left join t_uri t on (t.den_no = cast('0'||'&3' as int))
	left join m_hanyo eig on (eig.mst_kbn = '99' and eig.han_cd = 'uri-eig')
	left join m_hanyo rev on (rev.mst_kbn = '99' and rev.han_cd = 'tok-disc')
	left join m_hanyo h98a on (h98a.mst_kbn = '98' and h98a.han_cd = 'r0023-symd')
	left join m_hanyo h98b on (h98b.mst_kbn = '98' and h98b.han_cd = 'r0023-denn')
	left join m_hanyo h98c on (h98c.mst_kbn = '98' and h98c.han_cd = 'r0023-htan')
	left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'uri-tn-dec') --売上単価小数点管理
	left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'furh-r0023') --納品書／ヘッダに振込先印字する
	where 1=1
	and j.mst_kbn = '90'
--2021.04.21 MOD STT
--少し判りづらい
--	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)
	and j.han_cd = 'jisya' || (case when(eig.han_name = '1' and t.eig_cd != '')then('_' || t.eig_cd)else('')end)
--2021.04.21 MOD END
)w

[SelectRecords_Furi]
select
	h09.han_name || ' ' || h09.koumoku1 || ' ' || h09.koumoku2 || ' ' || h09.koumoku3 as furikikan
from m_hanyo h09
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'mtok-eig-f')
left join t_uri uh on (uh.den_no = '&3')
where 1 = 1
and h09.mst_kbn = '09'
and coalesce(h09.koumoku6,'') = ''
and ((coalesce(h99a.han_name,'') != '1') or (h09.koumoku12 = uh.eig_cd))
order by h09.sort_key, h09.han_cd

[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'
;

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

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

[SelectImageList]
select 
	image_cd, file_image
from m_image
where image_cd like '&1%'
order by image_cd