[selectRecords]
select
	(trunc(row_number() over (PARTITION BY tok_cd order by tok_cd,brand_cd,hin_cd)/ 31) +1) as uri_row_kai
	,'Page.'||(trunc(row_number() over (PARTITION BY tok_cd order by tok_cd,brand_cd,hin_cd)/ 31) +1) as uri_row_kai_s
	, tok_nm || ' 様' as tokui_nm
	--, to_char(now(),'yyyy/mm/dd') as now_ymd
	, (to_timestamp(v.syori_ymd, 'yyyymmdd') - interval '1 days') as now_ymd
	, (to_timestamp(v.syori_ymd, 'yyyymmdd') - interval '1 month') as syori_ymd_fr
	, (to_timestamp(v.syori_ymd, 'yyyymmdd') - interval '1 days') as syori_ymd_to
	--, to_char(to_date('&16', 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd_fr
	--, to_char(to_date('&17', 'YYYYMMDD'), 'yyyy/mm/dd') as syori_ymd_to
	,(now_stock - (nyuko - syuko)) as stock
	,v.*
from(
	select 
		th.tok_cd
		, mt.tok_nm
		, mt.zip_no
		, mt.addr1
		, mt.addr2
		, coalesce(mt.addr3,'') || coalesce(mt.addr4,'') as addr3
		, hin.hin_cd
		, hin.hin_nm
		, hin.brand_cd
		, coalesce(st.now_stock,'0') as now_stock
		, coalesce(ny.nyuko,'0') as nyuko
		, coalesce(sy.syuko,'0') as syuko
		, han.han_name as brand_nm
		, w.syori_ymd
	from m_hin hin 
	left join m_stock st on (hin.hin_cd = st.hin_cd)
	left join m_tok_hin th on (hin.hin_cd = th.hin_cd)
	left join m_tokui mt on (mt.tok_cd = th.tok_cd)
	inner join w_basr0410 w on (w.s_id = &22 and mt.tok_cd = w.tok_cd)
	left join (
		select
			t1.tok_cd
			,nyu.hin_cd
			,sum(nyu.suryo) as nyuko 
		from t_nyusyuko t1
		left join t_nyuko nyu on (nyu.den_no = t1.den_no)
		inner join w_basr0410 w on (w.s_id = &22 and t1.tok_cd = w.tok_cd)
		where 1=1
		and t1.ns_kbn = '1' 
		and t1.syori_ymd >= w.syori_ymd
		and t1.del_flg = 0 
		group by t1.tok_cd,nyu.hin_cd
		) ny on (ny.tok_cd = th.tok_cd and ny.hin_cd = th.hin_cd)
	left join (
		select 
			t1.tok_cd
			,syu.hin_cd
			,sum(syu.suryo) as syuko 
		from t_nyusyuko t1
		left join t_syuko syu on (syu.den_no = t1.den_no)
		inner join w_basr0410 w on (w.s_id = &22 and t1.tok_cd = w.tok_cd)
		where 1=1
		and t1.ns_kbn = '2' 
		and t1.syori_ymd >= w.syori_ymd
		and t1.del_flg = 0
		group by t1.tok_cd,syu.hin_cd
		) sy on (sy.tok_cd = th.tok_cd and sy.hin_cd = th.hin_cd)
	left join m_hanyo han on (hin.brand_cd = han.han_cd and mst_kbn = '06')
	where 1=1 
	--and (mt.cat_cd) = '&4'
	and (th.tok_cd) != ''
	and (('' = '&20') or (th.tok_cd >= '&20')) 
	and (('' = '&21') or (th.tok_cd <= '&21'))
)v
where (now_stock + nyuko + syuko) > 0
--order by v.tok_cd,uri_row_kai,v.brand_cd, v.hin_cd
order by v.tok_cd,v.brand_cd,v.hin_cd,uri_row_kai

[SelectRecords_Jisya]
select
	 w.*
	,'TEL:' || j_tel as j_tel_s
	,'FAX:' || j_fax as j_fax_s
	,'URL:' || j_url as j_url_s
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
--		,j.koumoku4
		,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

[SelectRecords_Furi]
select
	han_name || ' ' || furikikan1 || ' ' || furikikan2 || ' ' || furikikan3 as furikikan
from(
	select 
		mst_kbn,
		han_cd,
		han_name,
		koumoku1 as furikikan1,
		koumoku2 as furikikan2,
		koumoku3 as furikikan3,
		koumoku4,
		koumoku5,
		koumoku6,
		koumoku7,
		koumoku8,
		koumoku9,
		koumoku10,
		sort_key
	from m_hanyo
	where 1=1
	and mst_kbn = '09'
)w
order by han_cd

[SelectRecords_Max]
select
	max(w.uri_row_kai) as uri_row_kai_max
from(select
	v.*
	,(trunc(row_number() over (PARTITION BY tok_cd order by tok_cd,brand_cd,hin_cd)/ 31) +1) as uri_row_kai
	,(now_stock - (nyuko - syuko)) as stock
from(
	select 
		th.tok_cd
		, hin.hin_cd
		, hin.brand_cd
		, coalesce(st.now_stock,'0') as now_stock
		, coalesce(ny.nyuko,'0') as nyuko
		, coalesce(sy.syuko,'0') as syuko
	from m_hin hin 
	left join m_stock st on (hin.hin_cd = st.hin_cd)
	left join m_tok_hin th on (hin.hin_cd = th.hin_cd)
	left join m_tokui mt on (mt.tok_cd = th.tok_cd)
	inner join w_basr0410 w on (w.s_id = &2 and mt.tok_cd = w.tok_cd)
	left join (
		select
			t1.tok_cd
			,nyu.hin_cd
			,sum(nyu.suryo) as nyuko 
		from t_nyusyuko t1
		left join t_nyuko nyu on (nyu.den_no = t1.den_no)
		inner join w_basr0410 w on (w.s_id = &2 and t1.tok_cd = w.tok_cd)
		where 1=1
		and t1.ns_kbn = '1' 
		and t1.syori_ymd >= w.syori_ymd
		and t1.del_flg = 0 
		group by t1.tok_cd,nyu.hin_cd
		) ny on (ny.tok_cd = th.tok_cd and ny.hin_cd = th.hin_cd)
	left join (
		select 
			t1.tok_cd
			,syu.hin_cd
			,sum(syu.suryo) as syuko 
		from t_nyusyuko t1
		left join t_syuko syu on (syu.den_no = t1.den_no)
		inner join w_basr0410 w on (w.s_id = &2 and t1.tok_cd = w.tok_cd)
		where 1=1
		and t1.ns_kbn = '2' 
		and t1.syori_ymd >= w.syori_ymd
		and t1.del_flg = 0
		group by t1.tok_cd,syu.hin_cd
		) sy on (sy.tok_cd = th.tok_cd and sy.hin_cd = th.hin_cd)
	left join m_hanyo han on (hin.brand_cd = han.han_cd and mst_kbn = '06')
	where 1=1 
	and (th.tok_cd) != ''
	and (('' = '&1') or (th.tok_cd = '&1'))
)v
where (now_stock + nyuko + syuko) > 0
)w
*/
select
	max(w.uri_row_kai) as uri_row_kai_max
from(
	select
		(trunc(row_number() over (PARTITION BY tok_cd order by tok_cd,brand_cd,hin_cd) as uri_row_kai
	from(
		select 
			th.tok_cd
			, coalesce(st.now_stock,'0') as now_stock
			, coalesce(ny.nyuko,'0') as nyuko
			, coalesce(sy.syuko,'0') as syuko
		from m_hin hin 
		left join m_stock st on (hin.hin_cd = st.hin_cd)
		left join m_tok_hin th on (hin.hin_cd = th.hin_cd)
		left join m_tokui mt on (mt.tok_cd = th.tok_cd)
		left join m_hanyo han on (hin.brand_cd = han.han_cd and mst_kbn = '06')
		inner join w_basr0410 w on (w.s_id = &2 and mt.tok_cd = w.tok_cd)
		left join (
			select
				t1.tok_cd
				,nyu.hin_cd
				,sum(nyu.suryo) as nyuko 
			from t_nyusyuko t1
			left join t_nyuko nyu on (nyu.den_no = t1.den_no)
			inner join w_basr0410 w on (w.s_id = &2 and t1.tok_cd = w.tok_cd)
			where 1=1
			and t1.ns_kbn = '1' 
			and t1.syori_ymd >= w.syori_ymd
			and t1.del_flg = 0 
			group by t1.tok_cd,nyu.hin_cd
			) ny on (ny.tok_cd = th.tok_cd and ny.hin_cd = th.hin_cd)
		left join (
			select 
				t1.tok_cd
				,syu.hin_cd
				,sum(syu.suryo) as syuko 
			from t_nyusyuko t1
			left join t_syuko syu on (syu.den_no = t1.den_no)
			inner join w_basr0410 w on (w.s_id = &2 and t1.tok_cd = w.tok_cd)
			where 1=1
			and t1.ns_kbn = '2' 
			and t1.syori_ymd >= w.syori_ymd
			and t1.del_flg = 0
			group by t1.tok_cd,syu.hin_cd
			) sy on (sy.tok_cd = th.tok_cd and sy.hin_cd = th.hin_cd)
		where 1=1 
		and (th.tok_cd) != ''
		and (('' = '&1') or (th.tok_cd = '&1'))
	)v
where 1=1
and (now_stock + nyuko + syuko) > 0
)w
*/