[load_grdList]
SELECT 
  uri.den_no
, uri.syori_ymd
, (case when(uri.henpin_flg = 1)then('返品')else('')end) as henpin_flg
, uri.prc_sts
, h1.han_name as prc_sts_nm
, (case when(jucyu.gokei_uri_su = '0')then('未納')
	when(jucyu.gokei_jucyu_su > jucyu.gokei_uri_su)then('分納')
	when(jucyu.gokei_jucyu_su = jucyu.gokei_uri_su)then('完納')
	else('受注無')end) as jucyu_state
--, jucyu.gokei_jucyu_su
--, jucyu.gokei_uri_su
, uri.tok_cd
, uri.tok_nm
, uri.nonyu_nm
, uri.zeinuki_gaku
, uri.zeikomi_gaku
, (
	select sum(tsm.kousu) 
	from t_seizou sh 
	inner join t_seizou_m tsm on (tsm.shiji_no = sh.shiji_no)
	where sh.jucyu_no = uri.den_no
	) as kousu
, uri.payment_term
, uri.biko1
, uri.biko1 as biko1_ex
, uri.biko2
, uri.seikyu_flg
, uri.keshi_flg
, (case when(uri.syukka_flg = 1)then('出荷')else('')end) as syukka_flg
, (case when(uri.seikyu_flg = 1)then(to_char(to_date(uri.seikyu_ymd,'yyyymmdd'),'mm/dd'))else(null)end) as seikyu_md
, (case when(uri.keshi_flg = 1)then(to_char(to_date(uri.keshi_ymd,'yyyymmdd'),'mm/dd'))else(null)end) as nyukin_md
, tk.furikikan
, h2.koumoku5 as furikikan_s
, um.hin_nm
, si.den_no as hacyu_no
, si.sir_nm --sir
, case mt.tan_rnm when '' then mt.tan_nm
	else mt.tan_rnm
	end as ttan
, h3.han_name as cat_nm
, h4.han_name as pay_name
,uri.kanri_no
,uri.tok_hacyu_no
,coalesce(tk.tori_kbn,'0') as tori_kbn
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join t_uri_m um on (um.den_no = uri.den_no and um.row_no = 0)
left join t_sir_m sm on (sm.jucyu_no = um.jucyu_no and um.jucyu_row_no = sm.jucyu_row_no and sm.hacyu_no < 0)
left join t_sir si on sm.den_no = si.den_no and si.hacyu_flg = 1
left join m_tanto mt on (uri.tan_cd = mt.tan_cd)
left join m_hanyo h3 on (h3.mst_kbn = '35' and h3.han_cd = tk.cat_cd)
left join 
(select den_no
	, sum(uri_su) as gokei_uri_su
	, sum(jucyu_su) as gokei_jucyu_su
	from t_uri_m
	group by den_no) as jucyu
on (uri.jucyu_no = jucyu.den_no)
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
left join m_hanyo h4 on (h4.mst_kbn = '76' and h4.han_cd = uri.pay_kbn)
WHERE 1 = 1
and uri.del_flg = 0
and uri.jucyu_flg = 0
and coalesce(uri.mitsumori_flg,0) = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
AND ((fnc_translate_case(tk.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(tk.tok_kana) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(uri.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (':tok_nm' = ''))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':tok_tan') or (uri.tan_cd = ':tok_tan'))
and (('' = ':nonyu_no') or (uri.nonyu_no = fnc_to_number(':nonyu_no')))
AND ((fnc_translate_case(uri.nonyu_nm) LIKE '%'||fnc_translate_case(':nonyu_nm')||'%') or (':nonyu_nm' = ''))
and (('' = ':pay_kbn') or (uri.pay_kbn = ':pay_kbn'))
and ((uri.kanri_no like '%:kanri_no%') or ('' = ':kanri_no'))
and ((uri.tok_hacyu_no like '%:tok_hacyu_no%') or ('' = ':tok_hacyu_no'))
order by uri.syori_ymd desc, uri.den_no desc

[getNyukin]
select k.nyukin_no as w_nyukin_no
from t_uri uh
left join t_nyu_keshi k on (k.keshi_kind = 1 and k.uri_no = uh.den_no)
where uh.den_no = :w_upd_key_value
and uh.zeikomi_gaku = (select sum(nk.nyukin_gaku) from t_nyu_keshi nk where nk.keshi_kind = 1 and nk.uri_no = uh.den_no)
order by k.nyukin_no desc

[getNyukin_OLD]
select nyukin_no as w_nyukin_no
from t_nyu_keshi
where uri_no = :w_upd_key_value
and keshi_kind = 1

[doSeikyu]
update t_uri set
  seikyu_flg = 1
, seikyu_ymd = to_char(now(),'yyyymmdd')
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_upd_key_value
and seikyu_flg = 0

[doNyukin]
update t_uri set
  keshi_flg = 1
, keshi_ymd = to_char(now(),'yyyymmdd')
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_upd_key_value
and keshi_flg = 0


[delete_w0110]
delete from w_basr0110
where s_id = :s_id
;

[chk_counter]
SELECT 
 :s_id
 ,uri.den_no
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
WHERE 1 = 1
and uri.jucyu_flg = 0
and coalesce(uri.mitsumori_flg,0) = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and ((tk.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
;

[insert_w0110]
insert into w_basr0110
(s_id, den_no)
SELECT 
 :s_id
 ,uri.den_no
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join m_hanyo h2 on (h2.mst_kbn = '09' and h2.han_cd = tk.furikikan)
WHERE 1 = 1
and uri.jucyu_flg = 0
and coalesce(uri.mitsumori_flg,0) = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and ((tk.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
;

[output_deliv_csv]
SELECT 
  uri.den_no
, uri.syori_ymd
, uri.prc_sts
, h1.han_name as prc_sts_nm
, uri.tok_cd --住所録ｺｰﾄﾞ（佐川）、お届け先ｺｰﾄﾞ（ヤマト）
, uri.nonyu_nm as tok_nm --納入名
-- , uri.tok_nm
, uri.payment_term
, uri.biko1
, uri.biko1 as biko1_ex
, uri.biko2
, uri.seikyu_flg
, uri.keshi_flg
, um.hin_nm
, uri.nonyu_addr1||uri.nonyu_addr2 as addr1
--, uri.nonyu_addr2 as addr2
, uri.nonyu_addr3 as addr2
, uri.nonyu_addr4 as addr3
, uri.nonyu_tel_no as tel_no --お届け先電話番号（共通）
, uri.nonyu_zip_no as zip_no --お届け先郵便番号（共通）
, (uv_to_hankaku_kana(tk.tok_kana)) as tok_kana --お届け先ｶﾅ（共通）
, tk.tok_tan --得意先担当者（名称2などがある場合）
, (case when(tk.cat_cd = '2')then('様')
	when(tk.cat_cd = '1')then('御中')
	else(null)end) as keisyou --敬称（共通）
,'075-724-3550' as a_tel --御依頼主電話番号（共通）
,'1' as count --個数口表示（共通）
,'手芸用品' as hinmei --品名（共通でいけるか？）
,null as hinmei2 --品名2（共通でいけるか？）
,null as hinmei3 --品名3（共通でいけるか？）
,null as hinmei4 --品名4（共通でいけるか？）
,null as hinmei5 --品名5（共通でいけるか？）
,null as todokeymd --お届け予定日・配達日（共通）
,null as haitatudt --配達時間帯（共通）
,null as daipay --代引き（共通）
,null as daitax --代引き消費税（共通）
,null as e_code --営業所コード（共通）
,null as s_kban --お客様管理ナンバー（佐川）
,'143368340007' as s_acode --アヴリルのコード（佐川）
,null as s_busho --部署・担当（佐川）
,null as s_teln --御依頼主電話番号（佐川）
,null as s_zipn --御依頼主郵便番号（佐川）
,null as s_add1n --御依頼主住所1（佐川）
,null as s_add2n --御依頼主住所2（佐川）
,null as s_avril1n --御依頼主名1（佐川）
,null as s_avril2n --御依頼主名2（佐川）
,'008' as s_nisu --荷姿（佐川）
,'000' as s_bin_s --便種スピード（佐川）
,'001' as s_bin --便種（佐川）
,null as s_hzikan --配達時間時分（佐川）
,null as s_paytype --決済種別（佐川）
,null as s_hokenm --保険金額（佐川）
,'0' as s_hoken --保険金額印字（佐川）
,null as s_seal1 --シール1（佐川）
,null as s_seal2 --シール2（佐川）
,null as s_seal3 --シール3（佐川）
,null as s_stop --営業所止め置き（佐川）
,null as s_src --src区分（佐川）
,'1' as s_moto --元着区分（佐川）
,null as y_kanri --お客様管理番号（ヤマト）
,'2' as y_syubet --送り状種別（ヤマト）
,null as y_cool --クール区分（ヤマト）
,null as y_denno --伝票番号（ヤマト）
,to_char(current_timestamp , 'yyyy/mm/dd') as y_syukaymd --出荷予定日（ヤマト）当日にする
,to_char(current_timestamp + '1 days', 'yyyy/mm/dd') as y_otodokeymd --お届け予定日（ヤマト）翌日にする
,uri.nonyu_addr1||(case when(uri.nonyu_addr2 != '')then(uri.nonyu_addr2)else('')end)||(case when(uri.nonyu_addr3 != '')then(uri.nonyu_addr3)else('')end) as y_addr1 --ヤマトアドレス
,null as y_busho1 --お届け先会社・部門名1
,null as y_busho2 --お届け先会社・部門名2
,'0' as y_iraicode --御依頼主ｺｰﾄﾞ（ヤマト）
,null as y_ateleda --御依頼主電話番号枝番（ヤマト）
,'6068185' as y_azip --御依頼主郵便番号（ヤマト）
,'京都府京都市左京区一乗寺高槻町20-1' as y_a_add --御依頼主住所（ヤマト）
,null as y_a_add1 --御依頼主住所アパートなど（ヤマト）
,'株式会社アヴリル' as y_avril --御依頼主名（ヤマト）
,null as akana --御依頼主カナ（ヤマト）
,null as y_hincd --品名コード（ヤマト）
,null as y_hincd2 --品名コード2（ヤマト）
,'水濡厳禁' as y_niatu1 --荷扱い1（ヤマト）
,'下積厳禁' as y_niatu2 --荷扱い2（ヤマト）
,null as y_paytype --決済種別（ヤマト）
,null as y_kizi --記事（ヤマト）
,'0' as y_stop --営業所止め置き（ヤマト）
,null as y_outp --発行枚数（ヤマト）
,uri.zeikomi_gaku as y_daipay --代引き（共通）
,uri.zei_gaku as y_daitax --代引き消費税（共通）
,'0758031520' as y_acode --アヴリルのコード（ヤマト）
,'' as y_acodeNOT --アブリルの請求先コードに必要な空白（ヤマト）
,'01' as y_acode1 --アブリルの請求先コードに必要な情報（ヤマト）
,null as j_acode --アヴリルのコード（郵政）
FROM t_uri uri
left join m_tokui tk on (uri.tok_cd = tk.tok_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uri.prc_sts)
left join t_uri_m um on (um.den_no = uri.den_no and um.row_no = 0)
WHERE 1 = 1
and uri.del_flg = 0
and uri.jucyu_flg = 0
and coalesce(uri.mitsumori_flg,0) = 0
and uri.syori_ymd != ''
and ((uri.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uri.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uri.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (uri.prc_sts = ':prc_sts'))
and (('' = ':tok_cd') or (uri.tok_cd = ':tok_cd'))
and ((tk.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uri.biko1 like '%:biko1%' or uri.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':tori_kbn') or (tk.tok_cd is not null and tk.tori_kbn = ':tori_kbn'))
and ((':nk_cond' != '0') or (uri.keshi_flg = 0))
and ((':nk_cond' != '1') or (uri.keshi_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
order by uri.syori_ymd desc, uri.den_no desc

[delete_basv0230]
delete from w_basv0230 where s_id = :s_id;

[insert_basv0230]
insert into w_basv0230 (
		  s_id
		, nyukin_no
		, tok_cd
		, tok_nm
		, nyukin_ymd
		, nyukin_kbn
		, nyukin_gaku
		, tesuryo
		, cyosei_gaku
		, total_gaku
		, furikikan
		, biko
		, seikyu_flg
		, seikyu_ymd
		, uri_no
		, seikyu_gaku
		, tan_cd
		, keshi_gaku
		, seikyu_zan
		, tegata_ymd
		, tegata_no
		, nyukin_term_id
		, del_flg
		, cre_id
		, cre_time
		, upd_id
		, upd_time
	)
	select
		  :s_id
		, null --nyukin_no
		, uh.tok_cd
		, uh.tok_nm
		--2018.12.01 #6203 MOD STT
--		, uh.syori_ymd --nyukin_ymd
		, ':w_nyukin_ymd' --nyukin_ymd
		--2018.12.01 #6203 MOD END
		, ':w_nyukin_kbn'
		, uh.zeikomi_gaku --nyukin_gaku
		, null --tesuryo
		, null --cyosei_gaku
		, uh.zeikomi_gaku --total_gaku
		, null --furikikan
		, '一括入金'||(case when(uh.jucyu_flg = 1)then('受注')else('売上')end)||'�� '||cast(uh.den_no as varchar) --biko
		, 0 --seikyu_flg
		, null --seikyu_ymd
		, uh.den_no --uri_no
		, null --seikyu_gaku
		, uh.tan_cd
		, null --keshi_gaku
		, null --seikyu_zan
		, null --tegata_ymd
		, null --tegata_no
		--, ':w_nyukin_term_id'
		, ''
		, uh.del_flg
		, uh.cre_id
		, uh.cre_time
		, uh.upd_id
		, uh.upd_time
	from t_uri uh
	where den_no = :w_den_no
	;
[insert_basv0230_tesu]
insert into w_basv0230 (
		  s_id
		, nyukin_no
		, tok_cd
		, tok_nm
		, nyukin_ymd
		, nyukin_kbn
		, nyukin_gaku
		, tesuryo
		, cyosei_gaku
		, total_gaku
		, furikikan
		, biko
		, seikyu_flg
		, seikyu_ymd
		, uri_no
		, seikyu_gaku
		, tan_cd
		, keshi_gaku
		, seikyu_zan
		, tegata_ymd
		, tegata_no
		, nyukin_term_id
		, del_flg
		, cre_id
		, cre_time
		, upd_id
		, upd_time
	)
	select
		  :s_id
		, null --nyukin_no
		, han.koumoku2 
		, m.tok_nm
		--2018.12.01 #6203 MOD STT
--		, uh.syori_ymd --nyukin_ymd
		, ':w_nyukin_ymd' --nyukin_ymd
		--2018.12.01 #6203 MOD END
		, ':w_nyukin_kbn'
		, :w_tesuryo --nyukin_gaku
		, null --tesuryo
		, :w_cyosei_gaku --cyosei_gaku
		, :w_tesuryo + :w_cyosei_gaku --total_gaku
		, null --furikikan
		, '一括入金[手数料]'|| ' :w_syori_ymd' || ' 決済方法'||':w_pay_kbn_nm' --biko
		, 0 --seikyu_flg
		, null --seikyu_ymd
		, null --uri_no
		, null --seikyu_gaku
		, ':login_id'
		, null --keshi_gaku
		, null --seikyu_zan
		, null --tegata_ymd
		, null --tegata_no
		--, ':w_nyukin_term_id'
		, ''
		, 0
		, ':login_id'
		, now()
		, ':login_id'
		, now()
		from m_hanyo han
		inner join m_tokui m on han.koumoku2 = m.tok_cd
where 'uri-iktnyu'=han_cd
and mst_kbn = '99'
;

	
[update_basv0230]
select fnc_update_basv0230(:s_id);