[loadOriginal]
select fnc_load_basv0220(:s_id, :den_no)

[updateOriginal]
select fnc_update_basv0220(:s_id)

[deleteOriginal]
delete from w_basv0220 where s_id = :s_id;
delete from w_basv0220_m where s_id = :s_id;

[deleteHeader]
delete from w_basv0220 where s_id = :s_id;
update w_basv0220_m set
  sir_su = null
, hacyu_zan = hacyu_su
--#14344 2020.05.30 MOD STT
, hacyu_no = null
, hacyu_row_no = null
--#14344 2020.05.30 MOD END
where s_id = :s_id
;

[doDelete]
select fnc_delete_basv0220(:den_no, ':login_id')

[getOtherId]
select other_id as w_other_id
from m_hin
where hin_cd = ':w_hin_cd'

[getMaxDenNo]
select
  (select max(den_no) from m_saiban where saiban_kbn = 4) as last_sir_no
, (select max(den_no) from m_saiban where saiban_kbn = 3) as last_hacyu_no

[loadNewKey]
select den_no as w_new_den_no
, sir_no as w_new_sir_no
from w_basv0220
where s_id = :s_id

[loadUri]
insert into w_basv0220_m (
  s_id
, row_no
, hin_cd
, hin_nm
, suryo
, tanka
, kingaku
, tanka_jpn
, zaiko_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, um.row_no
, um.hin_cd
, mh.hin_rnm
, um.suryo
, mh.now_ztanka --tanka
, um.suryo * mh.now_ztanka --kingaku
, mh.now_ztanka --tanka_jpn
--#19889 2021.07.09 MOD STT
--, 1 --zaiko_flg
, 0 --zaiko_flg
--#19889 2021.07.09 MOD END
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_uri_m um
inner join v_m_hin mh on (mh.hin_cd = um.hin_cd)
where um.den_no = :uri_no
order by um.row_no

[loadJucyu]
insert into w_basv0220_m (
  s_id
, row_no
, hin_cd
, hin_nm
, hacyu_su
, htanka
, tanka
, hacyu_gaku
, size_l
, size_w
, size_h
, sunpo_kbn
, sunpo_kbn_nm
, color_cd
, color_nm
, jucyu_no
, jucyu_row_no
, tani
, tani_hacyu_su
, tani_hacyu_zan
, meisai_biko
, hosoku3
--#26184 2022.11.17 MOD STT
, reg_hin_cd
--#26184 2022.11.17 MOD END
, hacyu_req_zan
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, row_number() over(order by um.row_no, vh.row_no) - 1 --row_no
, vh.chin_cd --hin_cd
, mh.hin_nm
, (um.jucyu_su * vh.set_su - coalesce(vjm.order,0)) --hacyu_su
, mh.htanka --htanka
, mh.stanka --tanka
, ((um.jucyu_su * vh.set_su) - coalesce(vjm.order,0)) * mh.stanka --hacyu_gaku
, um.size_l
, um.size_w
, um.size_h
, um.sunpo_kbn
, h52.han_name --sunpo_kbn_nm
, um.color_cd
, um.color_nm
, um.den_no --jucyu_no
, um.row_no
, '' --tani
, '' --tani_hacyu_su
, '' --tani_hacyu_zan
, '' as meisai_biko
, '' as hosoku3
--#26184 2022.11.17 MOD STT
, vh.chin_cd --reg_hin_cd
--#26184 2022.11.17 MOD END
, (um.jucyu_su * vh.set_su - coalesce(vjm.order,0)) --hacyu_req_zan
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_uri_m um
inner join v_sethin vh on (vh.hin_cd = um.hin_cd)
inner join m_hin mh on (mh.hin_cd = vh.chin_cd)
inner join m_hanyo h1 on (h1.mst_kbn = '04' and h1.han_cd = mh.hin_sts)
inner join w_basv0211 w211 on (vh.chin_cd = w211.hin_cd and w211.den_no = :jucyu_no) --選択品番
left join (select sum(hacyu_su) as order, hin_cd, jucyu_no, jucyu_row_no
			from v_jyucyu_mihaccyu
			group by hin_cd, jucyu_no, jucyu_row_no) vjm on 
				(vjm.hin_cd = vh.chin_cd and um.den_no = vjm.jucyu_no and um.row_no = vjm.jucyu_row_no)
--#15064 2020.07.20 MOD STT
left join m_hanyo h99 on (h99.mst_kbn = '99' and h99.han_cd = 'uri-hacyu')
left join m_hanyo h52 on (h52.mst_kbn = '52' and h52.han_cd = um.sunpo_kbn)
--#15064 2020.07.20 MOD END
where um.den_no = :jucyu_no
and h1.koumoku1 = '1'
--#15064 2020.07.20 MOD STT
and COALESCE(h99.han_name,'') != '2'
--#15064 2020.07.20 MOD END

--#15064 2020.07.20 MOD STT
union all
select
  :s_id
, row_number() over(order by um.row_no) - 1 --row_no
--#26184 2022.11.17 MOD STT
--, um.hin_cd
, (case when(um.input_hin_cd != '''')then(um.input_hin_cd)else(um.hin_cd)end) --hin_cd
--#26184 2022.11.17 MOD END
, um.hin_nm
--#27860 2023.04.30 MOD STT
--, um.jucyu_su --hacyu_su
, (um.jucyu_su - coalesce(jms.hacyu_su,0)) --hacyu_su
--#27860 2023.04.30 MOD END
, mh.htanka --htanka
--#24567 2022.06.25 MOD STT
--, mh.stanka --tanka
, (case when(h99b.han_name = '1')then(um.gentanka)else(mh.stanka)end) --tanka
--#24567 2022.06.25 MOD END
--#27860 2023.04.30 MOD STT
--, um.jucyu_su * mh.stanka --hacyu_gaku
, (um.jucyu_su - coalesce(jms.hacyu_su,0)) * mh.stanka --hacyu_gaku
--#27860 2023.04.30 MOD END
, um.size_l
, um.size_w
, um.size_h
, um.sunpo_kbn
, h52.han_name --sunpo_kbn_nm
, um.color_cd
, um.color_nm
, um.den_no --jucyu_no
, um.row_no
, um.tani
, um.tani --tani_hacyu_su
, um.tani --tani_hacyu_zan
, (case when(h99a.han_name = '1')then((case when(um.tok_hacyu_no != '')then(um.tok_hacyu_no)else('受注No. '||cast(um.den_no as varchar))end))
  else(mh.hacyu_biko)end) --meisai_biko
, um.hosoku3
--#26184 2022.11.17 MOD STT
, um.hin_cd --reg_hin_cd
--#26184 2022.11.17 MOD END
, (um.jucyu_su - coalesce(jms.hacyu_su,0)) --hacyu_req_zan
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_uri_m um
--#27860 2023.04.30 MOD STT
left join fnc_query_jucyu_hacyu_summary(um.den_no, um.row_no, -1) jms on (jms.jucyu_no = um.den_no and jms.jucyu_row_no = um.row_no)
--#27860 2023.04.30 MOD END
left join m_hin mh on (mh.hin_cd = um.hin_cd)
left join m_hanyo h99 on (h99.mst_kbn = '99' and h99.han_cd = 'uri-hacyu')
left join m_hanyo h52 on (h52.mst_kbn = '52' and h52.han_cd = um.sunpo_kbn)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'sp-v0220-8')
left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'uri-gtan-e')
--#26096 2022.11.06 MOD STT
left join m_hanyo h99c on (h99c.mst_kbn = '99' and h99c.han_cd = 'msir-v0210')
--#26096 2022.11.06 MOD END
where um.den_no = :jucyu_no
and COALESCE(h99.han_name,'') = '2'
--#22688 2022.01.30 MOD STT
--#26096 2022.11.06 MOD STT
--and ((':sir_cd' = '') or (mh.sir_cd = ':sir_cd'))
and ((':sir_cd' = '') or (h99c.han_name = '1') or (mh.sir_cd = ':sir_cd'))
and ((':sir_cd' = '') or (COALESCE(h99c.han_name,'') != '1') or (um.sir_cd = ':sir_cd'))
--#26096 2022.11.06 MOD END
--#22688 2022.01.30 MOD END
--#15064 2020.07.20 MOD END

--order by um.row_no, vh.row_no

[loadJucyuHeader]
select
  mh.sir_cd
from t_uri_m um
left join m_hin mh on (mh.hin_cd = um.hin_cd)
where um.den_no = :jucyu_no
and um.row_no = 0

[loadJucyuHeaderCyokuso]
select
  uh.tok_cd as c_tok_cd
, uh.loc_no
--#27290 2023.03.03 MOD STT
--, (case when(uh.sohin_nm != '')then(uh.sohin_nm)else( (case when(h99a.koumoku2 = 'add_tok_nm')then(uh.tok_nm||' ')else('')end) || uh.nonyu_nm)end) as c_tok_nm
, rtrim(case when(uh.sohin_nm != '')then(uh.sohin_nm)else(
--#27819 2023.04.17 MOD STT
--	(case when(h99a.koumoku2 = 'add_tok_nm')then(uh.tok_nm||' ')else('')end) || (case when(h99a.koumoku2 = 'add_tok_nm' and uh.nonyu_no = 0)then('')else(uh.nonyu_nm)end)
	(case when(h99a.koumoku2 = 'add_tok_nm' and (uh.nonyu_no = 0 or length(uh.tok_nm||' ' ||uh.nonyu_nm) <= 50))then(uh.tok_nm||' ')else('')end) || (case when(h99a.koumoku2 = 'add_tok_nm' and uh.nonyu_no = 0)then('')else(uh.nonyu_nm)end)
--#27819 2023.04.17 MOD END
)end) as c_tok_nm
--#27290 2023.03.03 MOD END
, (case when(uh.sohin_nm != '')then(uh.sohin_zip_no)else(uh.nonyu_zip_no)end) as c_tok_zip_no
, (case when(uh.sohin_nm != '')then(COALESCE(uh.sohin_addr1,'')||COALESCE(uh.sohin_addr2,'')||COALESCE(uh.sohin_addr3,'')||COALESCE(uh.sohin_addr4,''))else(COALESCE(uh.nonyu_addr1,'')||COALESCE(uh.nonyu_addr2,'')||COALESCE(uh.nonyu_addr3,'')||COALESCE(uh.nonyu_addr4,''))end) as c_tok_addr
, (case when(uh.sohin_nm != '')then(uh.sohin_tel_no)else(uh.nonyu_tel_no)end) as c_tok_tel_no
, (case when(uh.sohin_nm != '')then(uh.sohin_fax_no)else(uh.nonyu_fax_no)end) as c_tok_fax_no
, (case when(uh.sohin_nm != '')then(uh.sohin_tan)else(uh.nonyu_tan)end) as c_tok_tan
, (case when(uh.sohin_nm != '')then(null)else(uh.nonyu_no)end) as c_nonyu_no
, (case when(uh.sohin_nm != '')then(uh.sohin_no)else(null)end) as c_sohin_no
from t_uri uh
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'nonyu')
where uh.den_no = :jucyu_no

[loadDetails]
insert into w_basv0220_m (
  s_id
, row_no
, hin_cd
, hin_nm
, size_l
, size_w
, size_h
, size_free
, hacyu_su
, tanka
, hacyu_gaku
, tanka_jpn
, zaiko_flg
, shiji_no
, shiyou_row_no
, nyuka_yotei_ymd
, sunpo_kbn
, sunpo_kbn_nm
, color_cd
, color_nm
, meisai_biko
, meisai_biko2
, hacyu_lot
--#20072 2021.07.29 MOD STT
, tani
, iri_tani
, iri_su
--#20072 2021.07.29 MOD END
--#22489 2022.01.12 MOD STT
, jucyu_no
, jucyu_row_no
--#22489 2022.01.12 MOD END
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, (row_number() over(order by w.shiji_no, w.row_no)) - 1 --row_no
, w.hin_cd
--#22489 2022.01.12 MOD STT
--, (case when(w.shiji_no > 0)then(t.hin_nm)else(m.hin_rnm)end)
--, (case when(w.shiji_no > 0)then(t.size_l)else(m.size_l)end)
--, (case when(w.shiji_no > 0)then(t.size_w)else(m.size_w)end)
--, (case when(w.shiji_no > 0)then(t.size_h)else(m.size_h)end)
--, (case when(w.shiji_no > 0)then(t.size_free)else('')end)
--, (case when(w.shiji_no > 0)then(t.hacyu_irai_su)else(m.hacyu_irai_su)end)
, (case when(w.shiji_no > 0)then(t.hin_nm)when(w.jucyu_no > 0)then(jm.hin_nm)else(m.hin_rnm)end)
, (case when(w.shiji_no > 0)then(t.size_l)when(w.jucyu_no > 0)then(jm.size_l)else(m.size_l)end)
, (case when(w.shiji_no > 0)then(t.size_w)when(w.jucyu_no > 0)then(jm.size_w)else(m.size_w)end)
, (case when(w.shiji_no > 0)then(t.size_h)when(w.jucyu_no > 0)then(jm.size_h)else(m.size_h)end)
, (case when(w.shiji_no > 0)then(t.size_free)when(w.jucyu_no > 0)then(jm.size_free)else('')end)
, (case when(w.shiji_no > 0)then(t.hacyu_irai_su)when(w.jucyu_no > 0)then(COALESCE(jm.jucyu_su,0) - COALESCE(jm.uri_su,0))else(m.hacyu_irai_su)end)
--#22489 2022.01.12 MOD END
--#8810 2019.05.12 MOD STT
--, t.tanka --tanka
--, t.hacyu_irai_su * t.tanka --kingaku
--, t.tanka --tanka_jpn
--#22489 2022.01.12 MOD STT
--, (case when(w.shiji_no > 0)then(t.tanka)else(m.stanka)end) --tanka
--, (case when(w.shiji_no > 0)then(t.hacyu_irai_su * t.tanka)else(m.hacyu_irai_su * m.stanka)end) --kingaku
--, (case when(w.shiji_no > 0)then(t.tanka)else(m.stanka)end) --tanka_jpn
, (case when(w.shiji_no > 0)then(t.tanka)when(w.jucyu_no > 0)then((case when(jm.jucyu_su != 0)then(jm.genka_gaku / jm.jucyu_su)else(null)end))else(m.stanka)end) --tanka
, (case when(w.shiji_no > 0)then(t.hacyu_irai_su * t.tanka)when(w.jucyu_no > 0)then((COALESCE(jm.jucyu_su,0) - COALESCE(jm.uri_su,0)) * (case when(jm.jucyu_su != 0)then(jm.genka_gaku / jm.jucyu_su)else(null)end))else(m.hacyu_irai_su * m.stanka)end) --kingaku
, (case when(w.shiji_no > 0)then(t.tanka)when(w.jucyu_no > 0)then((case when(jm.jucyu_su != 0)then(jm.genka_gaku / jm.jucyu_su)else(null)end))else(m.stanka)end) --tanka_jpn
--#22489 2022.01.12 MOD END
--#8810 2019.05.12 MOD END
--#19889 2021.07.09 MOD STT
--, 1 --zaiko_flg
, 0 --zaiko_flg
--#19889 2021.07.09 MOD END
--#15986 2020.09.23 MOD STT
--, w.shiji_no
--, w.row_no
, (case when(w.shiji_no > 0)then(w.shiji_no)else(null)end) --shiji_no
, (case when(w.shiji_no > 0)then(w.row_no)else(null)end) --shiyou_row_no
--#15986 2020.09.23 MOD END
--#12756 2020.03.03 MOD STT
, (case when(w.shiji_no > 0)then(t.nyuka_yotei_ymd)else(null)end)
--#12756 2020.03.03 MOD STT
, m.sunpo_kbn
, h52.han_name --sunpo_kbn_nm
--#17072 2020.12.16 MOD STT
--, m.color_cd
--, h53.han_name --color_nm
--#22489 2022.01.12 MOD STT
--, t.color_cd
--, t.color_nm
, (case when(w.shiji_no > 0)then(t.color_cd)when(w.jucyu_no > 0)then(jm.color_cd)else(null)end)
, (case when(w.shiji_no > 0)then(t.color_nm)when(w.jucyu_no > 0)then(jm.color_nm)else(null)end)
--#22489 2022.01.12 MOD END
--#17072 2020.12.16 MOD END
--#23857 2022.05.27 MOD STT
--, m.hacyu_biko --meisai_biko
, (case when(h99a.han_name = '1')then((case when(jm.tok_hacyu_no != '')then(jm.tok_hacyu_no)else('受注No. '||cast(w.jucyu_no as varchar))end))
  else(m.hacyu_biko)end) --meisai_biko
--#23857 2022.05.27 MOD END
, (case when(w.shiji_no > 0)then(t.meisai_biko)else(m.hacyu_irai_biko)end) --meisai_biko2
, m.hacyu_lot
--#20072 2021.07.29 MOD STT
--#24567 2022.06.25 MOD STT
--, m.tani
, (case when(w.shiji_no > 0)then(m.tani)when(w.jucyu_no > 0 and jm.tani != '')then(jm.tani)else(m.tani)end)
--#24567 2022.06.25 MOD END
, m.iri_tani
, m.iri_su
--#20072 2021.07.29 MOD END
--#22489 2022.01.12 MOD STT
, (case when(w.shiji_no > 0)then(w.jucyu_no)when(w.jucyu_no > 0)then(w.jucyu_no)else(null)end)
, (case when(w.shiji_no > 0)then(w.jucyu_row_no)when(w.jucyu_no > 0)then(w.jucyu_row_no)else(null)end)
--#22489 2022.01.12 MOD END
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from w_basv3120_sel w
left join m_hin m on (m.hin_cd = w.hin_cd)
left join t_shiyou_zairyo t on (t.shiji_no = w.shiji_no and t.row_no = w.row_no)
--#22489 2022.01.12 MOD STT
left join t_uri_m jm on (jm.den_no = w.jucyu_no and jm.row_no = w.jucyu_row_no)
--#22489 2022.01.12 MOD END
left join m_hanyo h52 on (h52.mst_kbn = '52' and h52.han_cd = m.sunpo_kbn)
left join m_hanyo h53 on (h53.mst_kbn = '53' and h53.han_cd = m.color_cd)
--#23857 2022.05.27 MOD STT
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'sp-v0220-8')
--#23857 2022.05.27 MOD END
where w.s_id = :load_s_id
and ((':cond_sir_cd' = '') or (w.sir_cd = ':cond_sir_cd'))
and ((':cond_sir_loc_no' = '') or (w.sir_loc_no = ':cond_sir_loc_no'))
union all
select
  :s_id
, w.row_no --row_no
, w.hin_cd
, mh.hin_rnm --hin_nm
, null --size_l
, null --size_w
, null --size_h
, '' --size_free
, w.hacyu_su
, null --tanka
, null --kingaku
, null --tanka_jpn
--#19889 2021.07.09 MOD STT
--, 1 --zaiko_flg
, 0 --zaiko_flg
--#19889 2021.07.09 MOD END
, null --shiji_no
, null --shiyou_row_no
, null --nyuka_yotei_ymd
, null --sunpo_kbn
, null --sunpo_kbn_nm
, null --color_cd
, null --color_nm
, mh.hacyu_biko --meisai_biko
, '' --meisai_biko
, mh.hacyu_lot
--#20072 2021.07.29 MOD STT
, mh.tani
, mh.iri_tani
, mh.iri_su
--#20072 2021.07.29 MOD END
--#22489 2022.01.12 MOD STT
, null --jucyu_no
, null --jucyu_row_no
--#22489 2022.01.12 MOD END
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from w_basv0220_load_m w
left join v_m_hin mh on (mh.hin_cd = w.hin_cd)
where w.s_id = :load_s_id


[deleteRecord]
delete from w_basv0220_m
where s_id = :s_id

[insertRecord]
insert into w_basv0220_m (
  s_id
, row_no
, hin_cd
, suryo
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, :w_row_no
, ':w_hin_cd'
, :w_suryo
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
);
update w_basv0220_m w set
  hin_nm = m1.hin_nm
from m_hin m1
where w.s_id = :s_id
and w.row_no = :w_row_no
and m1.hin_cd = w.hin_cd
;
update w_basv0220_m w set
  now_ztanka = st.now_ztanka
, now_stock = st.now_stock
from m_stock st
where w.s_id = :s_id
and w.row_no = :w_row_no
and st.hin_cd = w.hin_cd
;

[deleteSub]
delete from t_sir_m_sub where den_no = :den_no;

[chkSetHin]
select st.chin_cd 
--from t_sir_m sr
from w_basv0220_m sr
left join m_sethin st on sr.hin_cd = st.hin_cd
left join m_hin hin on st.chin_cd = hin.hin_cd
--where sr.den_no = :den_no
where sr.s_id = :s_id and st.chin_cd  is not null
group by st.chin_cd
order by st.chin_cd
;

[createSirSub]
select fnc_create_basv0221(:s_id, ':_den_no');


[getSirHin]
select sh.hin_cd, sh.sir_cd, sh.row_no, sh.biko
from m_sirhin sh
where sh.hin_cd = ':s_hin_cd'
and sh.sir_cd = ':s_sir_cd'

[getReportId]
select koumoku1 as rpt
from m_hanyo
where mst_kbn = '16'
--and han_cd = '1'
order by han_cd limit 1

[getTanka]
select (case when suryo is null then 0 else genka_gaku / suryo end) as sel_tanka, hin_nm as sel_hin_nm from t_uri_m
--select tanka as sel_tanka from t_uri_m
where den_no = :tanka_den
and row_no = :tanka_row
;

[getTankaSir]
select tanka as sel_tanka , hin_nm from t_sir_m
--select (case when suryo is null then 0 else genka_gaku / suryo end) as sel_tanka, hin_nm as sel_hin_nm from t_sir_m
where den_no = :tanka_den
and row_no = :tanka_row

[get_default_sts]
select koumoku6
from m_hanyo
--update m_hanyo set koumoku6 = '01'
where mst_kbn = '00' and han_cd = '11' 
;

[getJyucyuNo]
select max(jucyu_no) as j_no
from w_basv0220_m
where s_id = :s_id

[getUriNo]
select coalesce(max(den_no), :j_no) as j_no
from t_uri
where jucyu_no = :j_no
and del_flg = 0
;

[updatePrint]
update t_sir set print_time = now()
where den_no = :w_den_no

[loadPrintState]
select
(case when(print_time is not null)then(to_char(print_time,'yyyy/mm/dd'))else(null)end) as w_print_ymd
from t_sir
where den_no = :w_den_no

[loadExclusiveTime]
select to_char(upd_time,'yyyymmddhh24miss') as w_exclusive_time
from w_basv0220
where s_id = :s_id

[getExclusiveNowTime]
select to_char(upd_time,'yyyymmddhh24miss') as w_exclusive_time
from t_sir
where den_no = :den_no

[getZeiRate]
select fnc_get_zei_rate(':syori_ymd') * 100 as zei_rate

[getSirSeiYmd]
select seikyu_ymd from m_sir where sir_cd = ':sir_cd';

[doc_sort]
select fnc_update_file_image_sort(:s_id);

[deleteDocRow]
delete from w_den_atch where s_id = :s_id and row_no = :d_row_no;

[getDocs]
select file_image from w_den_atch
where s_id = :s_id
and row_no = :_row_no

[insertSizeDetail]
delete from w_basv0220_s where s_id = :s_id
;
--発注数
insert into w_basv0220_s (s_id, row_no) values (:s_id, 0)
;
--発注残
insert into w_basv0220_s (s_id, row_no) values (:s_id, 1)
;
--仕入数
insert into w_basv0220_s (s_id, row_no) values (:s_id, 2)
;
--単価
insert into w_basv0220_s (s_id, row_no) values (:s_id, 3)
;
--発注金額
insert into w_basv0220_s (s_id, row_no) values (:s_id, 4)
;
--仕入金額
insert into w_basv0220_s (s_id, row_no) values (:s_id, 5)
;

[getValidSizeFields]
select 'size'||lpad(h52.han_cd,2,'0') as valid_col_id
, mh.stanka
from m_hin mh
left join m_hanyo h52 on (h52.mst_kbn = '52' and cast(h52.han_name as numeric(6,2)) = mh.size_l)
where mh.dhin_cd = ':dhin_cd'
order by valid_col_id

[getHinTankaCalcInfo]
select stanka_calc_kbn, hasu_point, hasu_point2, tanka_hasu_kbn, tanka_hasu_kbn2, density, stanka
from m_hin
where hin_cd = ':w_hin_cd'

[getDiscTanka]
select fnc_get_sir_disc_per(':sir_cd',':cat_cd') as disc_per

[loadCyokusoBySir]
select
  sir_nm as c_tok_nm
, zip_no as c_tok_zip_no
, COALESCE(addr1,'')||COALESCE(addr2,'')||COALESCE(addr3,'')||COALESCE(addr4,'') as c_tok_addr
, tel_no as c_tok_tel_no
from m_sir
where sir_cd = ':w_sir_cd'

[loadCyokusoNonyu]
select
  nonyu_nm as c_tok_nm
, zip_no as c_tok_zip_no
, COALESCE(addr1,'')||COALESCE(addr2,'')||COALESCE(addr3,'')||COALESCE(addr4,'') as c_tok_addr
, tel_no as c_tok_tel_no
, fax_no as c_tok_fax_no
, nonyu_tan as c_tok_tan
from m_nonyu
where tok_cd = ':c_tok_cd'
and nonyu_no = fnc_to_number(':c_nonyu_no')

[loadCyokusoSohin]
select
  sohin_nm as c_tok_nm
, zip_no as c_tok_zip_no
, COALESCE(addr1,'')||COALESCE(addr2,'')||COALESCE(addr3,'')||COALESCE(addr4,'') as c_tok_addr
, tel_no as c_tok_tel_no
, fax_no as c_tok_fax_no
, sohin_tan as c_tok_tan
from m_sohin
where tok_cd = ':c_tok_cd'
and sohin_no = fnc_to_number(':c_sohin_no')

[changeNyukaYoteiYmd]
update w_basv0220_sohin set
  nyuka_yotei_ymd = ':nyuka_yotei_ymd'
where s_id = :s_id

[changeAnsNyoteiYmd]
update w_basv0220_sohin set
  ans_nyotei_ymd = ':ans_nyotei_ymd'
where s_id = :s_id

[searchTani]
select han_cd, han_name, sort_key
from m_hanyo
where mst_kbn = '44'
and del_flg = 0
order by sort_key, han_cd

[getSirEigCd]
select eig_cd as w_sir_eig_cd
from m_sir
where sir_cd = ':w_sir_cd'

[getZeiRateMulti]
select
  han_cd as zei_rate_cd
, koumoku3 as zei_rate
, koumoku5 as zei_rate2
from m_hanyo
where mst_kbn = '93'
and ':syori_ymd' between koumoku1 and koumoku2

[checkSohinYmd_nyuka_yotei_ymd]
select link_no, uchi_seq
from w_basv0220_sohin
where s_id = :s_id
and coalesce(nyuka_yotei_ymd,'') != ':w_ymd'

[checkSohinYmd_ans_nyotei_ymd]
select link_no, uchi_seq
from w_basv0220_sohin
where s_id = :s_id
and coalesce(ans_nyotei_ymd,'') != ':w_ymd'

[checkSohinSirDone]
select hacyu_no, hacyu_row_no, uchi_seq
from t_sir_sohin
where hacyu_no = :den_no
and sir_no is not null

[loadSohinManualKiboYmd]
select link_no, uchi_seq
from w_basv0220_sohin
where s_id = :s_id
and coalesce(nyuka_yotei_ymd,'') != ':nyuka_yotei_ymd'

[loadSohinManualAnsYmd]
select link_no, uchi_seq
from w_basv0220_sohin
where s_id = :s_id
and coalesce(ans_nyotei_ymd,'') != ':ans_nyotei_ymd'

[checkSirDone]
select um.den_no, um.row_no
from t_sir_m um
inner join t_sir uh on (uh.den_no = um.den_no)
where um.hacyu_no = :den_no
and um.del_flg = 0
and uh.del_flg = 0

[updateAtch]
delete from w_den_atch i
where s_id = -(:s_id)
;
insert into w_den_atch (
  s_id
, row_no
, file_name
, file_image
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  -(:s_id)
, 0 --row_no
, ':file_name' --file_name
, :docs --file_image
, 0
, ':login_id'
, now()
, ':login_id'
, now()
);

[sendFax]
select fnc_insert_msir_send_log(-(:s_id), t.sir_cd, ':login_id')
from t_sir t
where t.den_no = :den_no

[deleteAtch]
delete from w_den_atch i
where s_id = -(:s_id)