[selectRecords]
SELECT
	  to_char(to_date(seikyu_ymd, 'YYYYMMDD'), 'yyyy/mm/dd') as seikyu_ymd_s
	,(case tok_nm
		when '' then ''
		else tok_nm || '　様'
	end) as tok_nm_s
--	, tok_nm || '　様' as tok_nm_s
	,(case zip_no
		when '' then ''
		else '〒'||substr(replace(zip_no,'-',''),1,3)||'-'||substr(replace(zip_no,'-',''),4,6)
	end) as yubin_no
--	,'〒'||substr(replace(zip_no,'-',''),1,3)||'-'||substr(replace(zip_no,'-',''),4,6) as yubin_no
	, addr1 as address1
	, addr2 as address2
	, addr3||addr4 as address3
	--,(in_suryo + out_suryo) as asuryo
	--,(first_stock + medium_stock + second_stock + out_suryo) as nyusyukkoryou
	--,(tanka * (first_stock + medium_stock + second_stock + out_suryo)) as hokanryo
	--,(htanka * out_suryo) + (stanka * in_suryo) as kingaku
	--, fnc_get_zei((htanka * out_suryo) + (stanka * in_suryo) + (tanka * (first_stock + medium_stock + second_stock + out_suryo)), v.seikyu_ymd) as zei
	, (case ext_attr2 when '9' then null else coalesce(in_suryo,0) + coalesce(out_suryo,0) end) as asuryo
	, (case v.ext_attr1 when '0' then (zen_stock + first_stock + medium_stock + in_suryo)
									  when '1' then (first_stock + medium_stock + second_stock + in_suryo)
									  when '2' then (zen_stock + first_stock + medium_stock)
									  when '3' then (first_stock + medium_stock + second_stock)
									  else null end) as nyusyukkoryou
	, (case v.ext_attr1 when '0' then (zen_stock + first_stock + medium_stock + in_suryo) * (coalesce(tanka,0)) 
									  when '1' then (first_stock + medium_stock + second_stock + in_suryo) * (coalesce(tanka,0)) 
									  when '2' then (zen_stock + first_stock + medium_stock) * (coalesce(tanka,0)) 
									  when '3' then (first_stock + medium_stock + second_stock) * (coalesce(tanka,0)) 
									  else null end) as hokanryo
	, (case ext_attr2 when '9' then null else coalesce(in_suryo,0) * (coalesce(stanka,0)) + coalesce(out_suryo,0) * (coalesce(htanka,0)) end) as kingaku
	, fnc_get_zei((case v.ext_attr1 when '0' then (zen_stock + first_stock + medium_stock + in_suryo) * (coalesce(tanka,0)) 
									  when '1' then (first_stock + medium_stock + second_stock + in_suryo) * (coalesce(tanka,0)) 
									  when '2' then (zen_stock + first_stock + medium_stock) * (coalesce(tanka,0)) 
									  when '3' then (first_stock + medium_stock + second_stock) * (coalesce(tanka,0)) 
									  else null end) 
	+ (case ext_attr2 when '9' then null else coalesce(in_suryo,0) * (coalesce(stanka,0)) + coalesce(out_suryo,0) * (coalesce(htanka,0)) end), v.seikyu_ymd) as zei
	, v.*
FROM(
	SELECT
		  w.seikyu_ymd
		, t.tok_cd
		, t.hin_cd
		, t.zen_stock 
		, t.first_stock 
		, t.medium_stock 
		, t.second_stock 
		, t.in_suryo --入庫数
		, t.out_suryo --出庫数
		, tok.ext_attr1 --保管料区分
		, tok.ext_attr2 --荷役区分 区分9は荷役料計算無し
		--得意先(荷主)マスタ
		, tok.tok_nm
		, tok.tel_no
		, tok.zip_no
		, tok.addr1
		, tok.addr2
		, tok.addr3
		, tok.addr4
		, tok.tel_no
		--品番マスタ
		, hin.hin_nm
		, hin.brand_cd
		--, hin.tanka 
		, (case tok.ext_attr1 when '9' then null else hin.tanka end) as tanka
		, (case tok.ext_attr2 when '9' then null else hin.htanka end) as htanka
		, (case tok.ext_attr2 when '9' then null else hin.stanka end) as stanka
		--汎用マスタ
		, han1.han_name as brand_nm
	FROM w_basr0030 w
	INNER JOIN t_stock_rireki t on (w.seikyu_ymd = t.seikyu_ymd and w.tok_cd = t.tok_cd)
	INNER JOIN m_tokui tok on (tok.tok_cd = t.tok_cd)
	INNER JOIN m_hin hin on (hin.hin_cd = t.hin_cd)
	left join m_hanyo han1 on (hin.brand_cd = han1.han_cd and han1.mst_kbn = '06')
	WHERE 1=1
	AND w.s_id = &1
	AND (t.in_suryo + t.out_suryo + t.zen_stock) > 0
)v
order by v.tok_cd,v.brand_cd,v.hin_cd;


[selectRecords_sum]
select
	 kingaku_sum
	,asuryo_sum
	,coalesce(kingaku_sum,0) + coalesce(asuryo_sum,0) as zeinukigaku
	,fnc_get_zei(coalesce(kingaku_sum,0) + coalesce(asuryo_sum,0), v.seikyu_ymd) as zei 
	,coalesce(kingaku_sum,0) + coalesce(asuryo_sum,0) + fnc_get_zei(coalesce(kingaku_sum,0) + coalesce(asuryo_sum,0), v.seikyu_ymd) as zeikomigaku
from(
	SELECT
		  w.seikyu_ymd
		, sum((hin.htanka * t.out_suryo) + (hin.stanka * t.in_suryo) + (tanka * (first_stock + medium_stock + second_stock + out_suryo))) as zei
		--, sum(t.first_stock + t.medium_stock + t.second_stock + t.out_suryo) as hokanryo_sum
		--, sum(hin.tanka * (t.first_stock + t.medium_stock + t.second_stock + t.out_suryo)) as asuryo_sum
		--, sum((hin.htanka * t.out_suryo )+(hin.stanka * t.in_suryo )) as kingaku_sum
		, sum((case tok.ext_attr1 when '0' then (t.zen_stock + t.first_stock + t.medium_stock + t.in_suryo) * (coalesce(hin.tanka,0)) 
									  when '1' then (t.first_stock + t.medium_stock + t.second_stock + t.in_suryo) * (coalesce(hin.tanka,0)) 
									  when '2' then (t.zen_stock + t.first_stock + t.medium_stock) * (coalesce(hin.tanka,0)) 
									  when '3' then (t.first_stock + t.medium_stock + t.second_stock) * (coalesce(hin.tanka,0)) 
									  else null end)) as asuryo_sum
		, sum((case tok.ext_attr2 when '9' then null else coalesce(t.in_suryo,0) * (coalesce(hin.stanka,0)) + coalesce(t.out_suryo,0) * (coalesce(hin.htanka,0)) end)) as kingaku_sum
	FROM w_basr0030 w
	INNER JOIN t_stock_rireki t on (w.seikyu_ymd = t.seikyu_ymd and w.tok_cd = t.tok_cd)
	INNER JOIN m_tokui tok on (tok.tok_cd = t.tok_cd)
	INNER JOIN m_hin hin on (hin.hin_cd = t.hin_cd)
	AND w.s_id = &1
	and t.tok_cd = '&2'
	and w.seikyu_ymd = '&3'
group by w.seikyu_ymd
)v

[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
	,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

[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