[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
--#17427 2020.12.26 MOD STT
--, (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
, (
select
(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)
from (
select count(*) as jucyu_cnt, sum(jm.jucyu_su) as gokei_jucyu_su, sum(jm.uri_su) as gokei_uri_su
from t_uri_m jm
where jm.den_no = uri.jucyu_no
) jucyu
) as jucyu_state
--#17427 2020.12.26 MOD END
--, 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
, coalesce(uri.sir_nm, si.sir_nm) as 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
,uri.group_no
,uri.jucyu_no
, case coalesce(mt2.tan_rnm,'') when '' then mt2.tan_nm
	else mt2.tan_rnm
	end as haiso_tan
--#17427 2020.12.26 MOD STT
,(select sum(um2.suryo)
	from t_uri_m um2
	where 1 = 1
--	and jucyu_no = uri.jucyu_no
	and um2.den_no = uri.den_no
	) as suryo
--#17427 2020.12.26 MOD END
,coalesce(tk.tori_kbn,'0') as tori_kbn
,uri.haiso_kanri_cd,uri.haiso_info1,uri.haiso_info2,uri.chimei1_nm,uri.chimei2_nm
, uri.den_kbn
, h54.han_name as den_kbn_nm
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_tanto mt2 on (uri.haiso_tan_cd = mt2.tan_cd)
left join m_hanyo h3 on (h3.mst_kbn = '35' and h3.han_cd = tk.cat_cd)
--#17427 2020.12.26 MOD STT
--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)
--#17427 2020.12.26 MOD END
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)
left join m_hanyo h54 on (h54.mst_kbn = '54' and h54.han_cd = uri.den_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 (('' = ':eigyou_tan_cd') or (uri.eigyou_tan_cd = ':eigyou_tan_cd'))
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'))
and ((uri.group_no = fnc_to_number(':group_no')) or ('' = ':group_no'))
and (('' = ':haiso_tan_cd') or (uri.haiso_tan_cd = ':haiso_tan_cd'))
and (('' = ':eig_cd') or (uri.eig_cd = ':eig_cd'))
and (('' = ':den_kbn') or (uri.den_kbn = ':den_kbn'))
and (('' = ':tok_cat_cd') or (tk.cat_cd = ':tok_cat_cd'))
:where_kingaku
--and (('' = ':kingaku_fr') or (uri.zeikomi_gaku >= fnc_to_number(':kingaku_fr')))
--and (('' = ':kingaku_to') or (uri.zeikomi_gaku <= fnc_to_number(':kingaku_to')))

order by uri.syori_ymd desc, uri.den_no desc
--#18003 2021.06.07 MOD STT
limit 10000
--#18003 2021.06.07 MOD END

[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

[insertNyukin]
select fnc_update_basv0410_nyukin(:s_id, :w_den_no, ':w_nyukin_ymd', ':w_nyukin_kbn', ':login_id')

[insertNyukinTesuryo]
select fnc_update_basv0410_nyukin_tesuryo(:s_id, :w_tesuryo, :w_cyosei_gaku, ':w_nyukin_ymd', ':w_nyukin_kbn', ':w_syori_ymd', ':w_pay_kbn_nm', ':login_id')

--TODO 1450独自仕様 hincd8桁
[update_hincd_8digit]
update w_uri_import set hin_cd = lpad(hin_cd,8,'0')
where s_id =:s_id
and hin_cd != ''
;

[beginImport]
delete from w_uri_import where s_id = :s_id

[commitImport]
select fnc_insert_uri_import(:s_id)

[insertImportWork]
insert into w_uri_import (
  s_id
, row_no
:import_reg_fields
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, :import_row_no
, :import_value_0
, :import_value_1
, :import_value_2
, :import_value_3
, :import_value_4
, :import_value_5
, :import_value_6
, :import_value_7
, :import_value_8
, :import_value_9
, :import_value_10
, :import_value_11
, :import_value_12
, :import_value_13
, 0
, ':login_id'
, now()
, ':login_id'
, now()
)

[Del_uriage]
select fnc_delete_basv0210(:den_no, ':login_id');
select fnc_return_seizou(:den_no, ':login_id');