[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
, 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
, 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
, um.hin_cd
, um.hin_nm
, um.jucyu_su --hacyu_su
, mh.htanka --htanka
, mh.stanka --tanka
, um.jucyu_su * 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
, um.tani
, um.tani --tani_hacyu_su
, um.tani --tani_hacyu_zan
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_uri_m um
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)
where um.den_no = :jucyu_no
and COALESCE(h99.han_name,'') = '2'
--#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
  tok_cd as c_tok_cd
, loc_no
, (case when(sohin_nm != '')then(sohin_nm)else(nonyu_nm)end) as c_tok_nm
, (case when(sohin_nm != '')then(sohin_zip_no)else(nonyu_zip_no)end) as c_tok_zip_no
, (case when(sohin_nm != '')then(COALESCE(sohin_addr1,'')||COALESCE(sohin_addr2,'')||COALESCE(sohin_addr3,'')||COALESCE(sohin_addr4,''))else(COALESCE(nonyu_addr1,'')||COALESCE(nonyu_addr2,'')||COALESCE(nonyu_addr3,'')||COALESCE(nonyu_addr4,''))end) as c_tok_addr
, (case when(sohin_nm != '')then(sohin_tel_no)else(nonyu_tel_no)end) as c_tok_tel_no
, (case when(sohin_nm != '')then(sohin_fax_no)else(nonyu_fax_no)end) as c_tok_fax_no
, (case when(sohin_nm != '')then(sohin_tan)else(nonyu_tan)end) as c_tok_tan
from t_uri
where 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
, 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
, (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)
--#8810 2019.05.12 MOD STT
--, t.tanka --tanka
--, t.hacyu_irai_su * t.tanka --kingaku
--, t.tanka --tanka_jpn
, (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
--#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)
, (case when(w.shiji_no > 0)then(w.row_no)else(null)end)
--#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
, t.color_cd
, t.color_nm
--#17072 2020.12.16 MOD END
, m.hacyu_biko --meisai_biko
, (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
, m.tani
, m.iri_tani
, m.iri_su
--#20072 2021.07.29 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)
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)
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
, 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'