[loadOriginal]
select fnc_load_basv0210(:s_id, :den_no, :mitsumori_mode)

[updateOriginal]
select fnc_update_basv0210(:s_id, :mitsumori_mode)

[deleteOriginal]
delete from w_basv0210 where s_id = :s_id;
delete from w_basv0210_m where s_id = :s_id;

[deleteHeader]
delete from w_basv0210 where s_id = :s_id;
update w_basv0210_genka_g set hacyu_irai_su = null,hacyu_irai_ymd = null, hacyu_no = null, hacyu_row_no = null
where s_id = :s_id
;
update w_basv0210_genka_z set hacyu_irai_su = null,hacyu_irai_ymd = null, hacyu_no = null, hacyu_row_no = null
where s_id = :s_id
;
update w_basv0210_m set uri_su = null, jucyu_zan = jucyu_su
where s_id = :s_id
;

[doDelete]
select fnc_delete_basv0210(: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 = (case when(:mitsumori_mode = 1)then(11)else(2)end)) as last_uri_no
, (select max(den_no) from m_saiban where saiban_kbn = 1) as last_jucyu_no
, (select max(den_no) from m_saiban where saiban_kbn = 14) as last_jucyu_no_ext
, (select max(den_no) from m_saiban where saiban_kbn = 19) as last_uri_no_ext

[getNonyu]
select nonyu_no as w_nonyu_no
from m_nonyu
where tok_cd = ':tok_cd'
order by nonyu_no limit 1

[getNonyuList]
select n.nonyu_no as w_nonyu_no
from m_nonyu n
where n.tok_cd = ':tok_cd'
--and not exists (select 1 from m_nonyu n2 where n2.tok_cd = n.tok_cd and n2.nonyu_no != n.nonyu_no)
--TODO 2021.11.21 MOD STT
--まだ展開させない
--and 1 = 0
order by nonyu_no

[loadNewKey]
select den_no as w_new_den_no
, uri_no as w_new_uri_no
from w_basv0210
where s_id = :s_id

[loadSir]
insert into w_basv0210_m (
  s_id
, row_no
, hin_cd
, hin_nm
, suryo
, tanka
, kingaku
, zaiko_flg
, now_stock
, now_ztanka
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, sm.row_no
, sm.hin_cd
, mh.hin_rnm
, sm.suryo
--, mh.tanka
--, sm.suryo * mh.tanka --kingaku
, 0 --tanka
, 0 --kingaku
, 1 --zaiko_flg
, mh.now_stock
, mh.now_ztanka
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from t_sir_m sm
inner join v_m_hin mh on (mh.hin_cd = sm.hin_cd)
where sm.den_no = :sir_no
order by sm.row_no




[deleteRecord]
delete from w_basv0210_m
where s_id = :s_id

[insertRecord]
insert into w_basv0210_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_basv0210_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_basv0210_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
;

[getRebateTanka]
select fnc_get_uri_rebate_per(':tok_cd',':cat_cd') as rebate_per

[getDiscTanka]
select fnc_get_uri_disc_per(':tok_cd',':cat_cd') as disc_per

[__getDiscTanka]
select coalesce(max(disc_per), 0) as disc_per
from m_tok_disc
where tok_cd = ':tok_cd'
and cat_cd = ':cat_cd'

[getGenkaM]
select sum(w1.kousu) as w_kousu
from w_basv0210_genka_m w1
where w1.s_id = :s_id
and w1.link_no = :link_no
;

[getTanka]
select tanka as sel_tanka, hin_nm as sel_hin_nm from t_uri_m
where den_no = :tanka_den
and row_no = :tanka_row
;

[getLoc]
select sir.loc_no
from t_uri uri
left join t_sir_m sirm on (uri.den_no = sirm.jucyu_no and sirm.row_no = 0)
left join t_sir sir on (sirm.den_no = sir.den_no)
where uri.den_no = :den_no
and uri.jucyu_flg = 1
union all
select sir.loc_no
from t_uri uri
left join t_sir_m sirm on (uri.jucyu_no = sirm.jucyu_no and sirm.row_no = 0)
left join t_sir sir on (sirm.den_no = sir.den_no)
where uri.den_no = :den_no
and uri.jucyu_flg = 0
;

[loadMitsumori]
select
  (case when(biko1 != '')then(biko1||chr(13)||chr(10))else('')end)
||COALESCE(biko2,'')
as biko1
, (case when(biko3 != '')then(biko3||chr(13)||chr(10))else('')end)
||(case when(biko4 != '')then(biko4||chr(13)||chr(10))else('')end)
||(case when(biko5 != '')then(biko5||chr(13)||chr(10))else('')end)
||COALESCE(biko6,'')
as biko2
from t_uri
where den_no = :den_no

[getTankaHaiso]
select fnc_get_haiso_uri_tanka(':w_tok_cd', ':w_chimei1_cd', ':w_chimei2_cd', ':w_hin_cd') as w_tanka


[getUrikakeZan]
select m.tok_cd,(coalesce(v.seikyu_zan, 0) - coalesce(v.nyukin_gaku, 0)) + (coalesce(v.zeikomi_gaku, 0) - coalesce(t.zeikomi_gaku, 0)) as seikyu_zan
	, coalesce(m.yoshin_gaku, 0) as yoshin_gaku
from m_tokui m 
left join v_urikake_zan v on m.tok_cd = v.tok_cd
left join w_basv0210 w on m.tok_cd = w.tok_cd and w.s_id = :s_id
left join t_uri t on t.tok_cd = m.tok_cd and w.den_no = t.den_no
where 1 = 1
and m.tok_cd = ':tok_cd'
;

[updatePrint]
update t_uri 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_uri
where den_no = :w_den_no

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

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

[checkFinishedKeshiJucyu]
select den_no
from t_uri
where den_no = :jucyu_no
and del_flg = 0
and jucyu_flg = 1
and keshi_flg = 1

[checkGenTanka]
select 1 from w_basv0210_genka
where s_id = :s_id
and link_no = :g_row
and tanka != :g_tanka

[checkGenSuryo]
select 1 from w_basv0210_genka
where s_id = :s_id
and link_no = :g_row
and suryo != :g_suryo
;

[getPrc_sts]
select 1 from m_hanyo h01 where (h01.mst_kbn = '01' and h01.han_cd = ':prc_sts' and coalesce(h01.koumoku3, '') = '')
;

[getTokSeiYmd]
--#21551 2021.11.06 MOD STT
--select seikyu_ymd from m_tokui where tok_cd = ':tok_cd';
select m1.seikyu_ymd
from m_tokui m2
inner join m_tokui m1 on (m1.tok_cd = m2.sei_cd)
where m2.tok_cd = ':tok_cd'
--#21551 2021.11.06 MOD END


[getNowTanka]
select tanka as nowtanka
from w_basv0210_genka
where s_id = :s_id
and link_no = :link_no
order by s_id desc limit 1
;

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

[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

[searchDDHin]
select w.dhin_cd as han_cd, w.dhin_cd as han_name, w.dhin_cd as sort_key
from (
select distinct dhin_cd
from m_hin
where cat_cd = '00'
and del_flg = 0
) w

[searchDHin]
select hin_cd as han_cd, size_cd as han_name, size_cd as sort_key
from m_hin
where cat_cd = '00'
and dhin_cd = ':ddhin_cd'
and del_flg = 0

[searchHinPart]
select hin_cd as han_cd, to_char(size_h,'FM990.0') as han_name, to_char(size_h,'FM000.0') as sort_key
from m_hin
where dhin_cd = ':dhin_cd'
and del_flg = 0

[searchHosoku2]
select h26.han_name as han_cd, h21a.han_name||' / '||h21b.han_name as han_name, h26.sort_key
from m_hanyo h26
inner join m_hanyo h21a on (h21a.mst_kbn = '21' and h21a.han_cd = h26.koumoku1)
--#10454 2021.06.17 MOD STT
--inner join m_hanyo h21b on (h21b.mst_kbn = '21' and h21b.han_cd = h26.koumoku2)
left join m_hanyo h21b on (h21b.mst_kbn = '21' and h21b.han_cd = h26.koumoku2)
--#10454 2021.06.17 MOD END
where h26.mst_kbn = '26'
--#10454 2021.06.17 MOD STT
--and h26.han_name in (select hin_class from m_hin_class where hin_cd = ':hin_cd')
and h26.han_name in (select hin_class from m_hin_class where hin_cd = ':hin_cd' and :mng_flg_field_id = 1)
--#10454 2021.06.17 MOD END
order by sort_key, han_cd

[searchHosoku34]
--select hin_cd as han_cd, hin_nm as han_name, hin_cd as sort_key, dhin_cd as limit_cd
select hin_cd as han_cd, hin_nm as han_name, sort_key as sort_key, dhin_cd as limit_cd
from m_hin
where cat_cd = ':hosoku34_cat'
--#10454 2021.06.17 MOD STT
--and ((':hosoku34_cat' = '11') or (dhin_cd != hin_cd))
and ((':hosoku34_cat' != '12') or (dhin_cd != hin_cd))
--#10454 2021.06.17 MOD END
order by sort_key, hin_cd

[searchHosoku56]
--select hin_cd as han_cd, hin_nm as han_name, hin_cd as sort_key, dhin_cd as limit_cd
select hin_cd as han_cd, hin_nm as han_name, sort_key as sort_key, dhin_cd as limit_cd
from m_hin
where cat_cd = ':hosoku56_cat'
--and ((':hosoku34_cat' = '11') or (dhin_cd != hin_cd))
order by sort_key, hin_cd

[searchHosoku1]
select hin_cd as han_cd, hin_nm as han_name, sort_key as sort_key, dhin_cd as limit_cd
from m_hin
where cat_cd = ':hosoku1_cat'
order by sort_key, hin_cd

[getDHin]
select size_cd as hin_part2
from m_hin
where hin_cd = ':dhin_cd'

[getHinPart]
select to_char(size_h,'FM990.0') as hin_part3
from m_hin
where hin_cd = ':hin_cd'

[getMeisaiHosoku1]
--select hin_rnm as hosoku1
select hin_nm as hosoku1
from m_hin
where cat_cd = ':hosoku1_cat'
and hin_cd = ':hosoku1_id'

[getMeisaiHosoku2]
select h26.han_name as hosoku2
, h21a.koumoku1 as hosoku3_cat
, h21b.koumoku1 as hosoku4_cat
, h21a.koumoku2 as hosoku5_cat
, h21b.koumoku2 as hosoku6_cat
, case h21a.koumoku1 when '' then h21a.han_name else '' end as hosoku3_nm
, case h21b.koumoku1 when '' then h21b.han_name else '' end as hosoku4_nm
, h26.koumoku5 as hosoku1_cat
from m_hanyo h26
inner join m_hanyo h21a on (h21a.mst_kbn = '21' and h21a.han_cd = h26.koumoku1)
--#10454 2021.06.17 MOD STT
--inner join m_hanyo h21b on (h21b.mst_kbn = '21' and h21b.han_cd = h26.koumoku2)
left join m_hanyo h21b on (h21b.mst_kbn = '21' and h21b.han_cd = h26.koumoku2)
--#10454 2021.06.17 MOD END
where h26.mst_kbn = '26'
and h26.han_name = ':hosoku2_id'

[getMeisaiHosoku34]
select hin_rnm as hosoku34
from m_hin
where cat_cd = ':hosoku34_cat'
and hin_cd = ':hosoku34_id'

[getMeisaiHosoku56]
select hin_rnm as hosoku56
from m_hin
where cat_cd = ':hosoku56_cat'
and hin_cd = ':hosoku56_id'

[getUpdatedGenkaInfo]
select w.hin_cd, w.hin_nm, w.koyu_nm
, w.sunpo_kbn, w.size_l, w.size_w, w.size_h
, w.suryo
, w.tanka
, h52.han_name as sunpo_kbn_nm
, w.hosoku1
, w.hosoku2
, w.hosoku3
, w.hosoku4
from w_basv0210_genka w
left join m_hanyo h52 on (h52.mst_kbn = '52' and h52.han_cd = w.sunpo_kbn)
where w.s_id = :s_id
and w.link_no = :link_no

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

[searchHosoku34Limit]
--#14057 2021.05.07 MOD STT
--select hin_cd as han_cd, hin_nm as han_name, hin_cd as sort_key
select hin_cd as han_cd, hin_nm as han_name, sort_key
--#14057 2021.05.07 MOD END
from m_hin
where hin_cd in (
select distinct dhin_cd
from m_hin
where cat_cd = ':hosoku34_cat'
)
order by m_hin.sort_key, hin_cd

[get_mitsumori]
select den_no,mitsumori_no
,substring(upper(mitsumori_no),1,length(mitsumori_no) - 2)
from t_uri
where substring(upper(mitsumori_no),1,length(mitsumori_no) - 2) = upper(':mitsumori_no')
--where mitsumori_no = upper(':mitsumori_no')
and del_flg = 0
and mitsumori_no != ''
and mitsumori_flg = 1
;

[next_eda_no]
--SELECT COUNT(1) as next_no
--#16350 2022.05.06 MOD STT
--SELECT 1 AS next_no
SELECT count(*) AS w_next_no
--#16350 2022.05.06 MOD END
FROM t_uri
WHERE substring(upper(mitsumori_no),1,length(mitsumori_no) - 2) = upper(':_kanri_no')
AND mitsumori_flg = 1
and mitsumori_no != ''

[update_prc_sts]
UPDATE t_uri SET prc_sts = ':p_sts' 
WHERE den_no = :p_den_no
;

[initSpec]
select fnc_init_basv0210_spec(:s_id, ':login_id')

[__initSpec]
delete from w_basv0210_spec where s_id = :s_id;
insert into w_basv0210_spec (
  s_id
, spec_row_no
, spec_cd
, spec_naiyo
, select_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, (select count(*) from m_hanyo h2 where h2.mst_kbn = 'A5' and COALESCE(h2.sort_key,'')||h2.han_cd < COALESCE(h1.sort_key,'')||h1.han_cd) --spec_row_no
, h1.han_cd --spec_cd
, h1.han_name --spec_naiyo
, (case when(h1.koumoku6 = '1')then(1)else(0)end) --select_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_hanyo h1
where h1.mst_kbn = 'A5'
order by sort_key, han_cd

[getHinSize]
select size_l, size_w, size_h
from m_hin
where hin_cd = ':w_hin_cd'

[selectSpot]
select han_cd, han_name, koumoku3, sort_key
from m_hanyo
where mst_kbn = '78'
order by sort_key, han_cd

[createJucyu]
select fnc_insert_jucyu_from_mitsumori(:s_id) as w_new_jucyu_den_no

[_updateSohinHeader_old]
update w_basv0210_m set tok_cd = ':w_tok_cd', sohin_no = :w_sohin_no, nohinsyo_kbn = ':w_nohinsyo_kbn'
where s_id = :s_id

[updateSohinHeader]
delete from w_basv0210_w where s_id = :s_id;
insert into w_basv0210_w (
  s_id
, tok_cd
, sohin_no
, sohin_nm
, sohin_zip_no
, sohin_addr1
, sohin_addr2
, sohin_addr3
, sohin_addr4
, sohin_tel_no
, sohin_fax_no
, sohin_tan
, nohinsyo_kbn
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, ':w_tok_cd'
, :w_sohin_no
, ':w_sohin_nm'
, ':w_sohin_zip_no'
, ':w_sohin_addr1'
, ':w_sohin_addr2'
, ':w_sohin_addr3'
, ':w_sohin_addr4'
, ':w_sohin_tel_no'
, ':w_sohin_fax_no'
, ':w_sohin_tan'
, ':w_nohinsyo_kbn'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
);

[updateSohinYmd]
update w_basv0210_m set
  nonyu_ymd = (case when(':nonyu_ymd' != '')then(':nonyu_ymd')else(nonyu_ymd)end)
, syotei_ymd = (case when(':syotei_ymd' != '')then(':syotei_ymd')else(syotei_ymd)end)
, uri_yotei_ymd = (case when(':uri_yotei_ymd' != '')then(':uri_yotei_ymd')else(uri_yotei_ymd)end)
where s_id = :s_id

[getDetailYmd]
select
  sum(case when(':nonyu_ymd' != COALESCE(nonyu_ymd,''))then(1)else(0)end) as nonyu_ymd_cnt
, sum(case when(':syotei_ymd' != COALESCE(syotei_ymd,''))then(1)else(0)end) as syotei_ymd_cnt
, sum(case when(':uri_yotei_ymd' != COALESCE(uri_yotei_ymd,''))then(1)else(0)end) as uri_yotei_ymd_cnt
from w_basv0210_m
where s_id = :s_id

[getSohinFields]
select
  sum(case when(':nonyu_hoho' != COALESCE(nonyu_hoho,''))then(1)else(0)end) as nonyu_hoho_cnt
, sum(case when(':skonpo_keijyo' != COALESCE(skonpo_keijyo,''))then(1)else(0)end) as skonpo_keijyo_cnt
, sum(case when(':unsobin' != COALESCE(unsobin,''))then(1)else(0)end) as unsobin_cnt
, sum(case when(':sohin_pay' != COALESCE(sohin_pay,''))then(1)else(0)end) as sohin_pay_cnt
from w_basv0210_sohin
where s_id = :s_id

[initGenkaSeisanJucyu]
insert into w_basv0210_genka (
  s_id
, link_no
, input_ymd
, suryo
, koutei_base_tanka
, zairyo_yobi
, price_rate
, hin_cd
, hin_lv1
, hin_lv2
, hin_lv3
--#19515 2021.06.11 MOD STT
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
--#19515 2021.06.11 MOD END
)
select
  :s_id
, :link_no
, to_char(now(),'yyyymmdd') --input_ymd
, :suryo --suryo
, (select cast(max(h90a.koumoku1) as numeric(12,2)) from m_hanyo h90a where h90a.mst_kbn = '90' and h90a.han_cd = 'seizou-tan') --koutei_base_tanka
, (select cast(max(h90b.koumoku1) as numeric(5,2)) from m_hanyo h90b where h90b.mst_kbn = '90' and h90b.han_cd = 'zairyo-yob') --zairyo_yobi
, (select cast(max(h90c.koumoku1) as numeric(5,2)) from m_hanyo h90c where h90c.mst_kbn = '90' and h90c.han_cd = 'price-rate') --price_rate
--#22737 2022.04.28 MOD STT
--, gm.hin_cd
--, substr(gm.hin_cd,1,1)
--, substr(gm.hin_cd,2,1)
--, substr(gm.hin_cd,3,1)
, COALESCE(COALESCE(gm.hin_cd,gm2.hin_cd),gm3.hin_cd)
, substr(COALESCE(COALESCE(gm.hin_cd,gm2.hin_cd),gm3.hin_cd),1,1)
, substr(COALESCE(COALESCE(gm.hin_cd,gm2.hin_cd),gm3.hin_cd),2,1)
, substr(COALESCE(COALESCE(gm.hin_cd,gm2.hin_cd),gm3.hin_cd),3,1)
--#22737 2022.04.28 MOD END
--#19515 2021.06.11 MOD STT
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
--#19515 2021.06.11 MOD END
from (select 1 as dual_i) w
left join m_hin mh on (mh.hin_cd = ':hin_cd')
--#22737 2022.04.28 MOD STT
--left join m_hanyo ha9 on (ha9.mst_kbn = 'A9' and ha9.han_cd = mh.ext_cat2_cd)
--left join m_hanyo hae on (hae.mst_kbn = 'AE' and hae.han_cd = mh.ext_cat5_cd)
--left join m_hanyo haf on (haf.mst_kbn = 'AF' and haf.han_cd = mh.ext_cat6_cd)
left join m_hanyo ha9 on (ha9.mst_kbn = 'A9' and ha9.han_cd = mh.ext_cat2_cd and ha9.koumoku3 != '')
left join m_hanyo hae on (hae.mst_kbn = 'AE' and hae.han_cd = mh.ext_cat5_cd and hae.koumoku3 != '')
left join m_hanyo haf on (haf.mst_kbn = 'AF' and haf.han_cd = mh.ext_cat6_cd and haf.koumoku3 != '')
--#22737 2022.04.28 MOD END
left join m_hin gm on (gm.cat_cd = '999' and gm.ext_cat2_cd = ha9.koumoku3 and gm.ext_cat5_cd = hae.koumoku3 and gm.ext_cat6_cd = haf.koumoku3)
--#22737 2022.04.28 MOD STT
left join m_hin gm2 on (gm2.cat_cd = '999' and gm2.ext_cat2_cd = ha9.koumoku3 and gm2.ext_cat5_cd = hae.koumoku3 and COALESCE(gm2.ext_cat6_cd,'') = '' and gm.hin_cd is null)
left join m_hin gm3 on (gm3.cat_cd = '999' and gm3.ext_cat2_cd = ha9.koumoku3 and COALESCE(gm3.ext_cat5_cd,'') = '' and COALESCE(gm3.ext_cat6_cd,'') = '' and gm2.hin_cd is null)
--#22737 2022.04.28 MOD END
where 1 = 1
--and mh.hin_cd = ':hin_cd'
and not exists (
select 1 from w_basv0210_genka w
where w.s_id = :s_id
and w.link_no = :link_no
);
update w_basv0210_genka w set
  koyu_nm = ':koyu_nm'
, sunpo_kbn = ':sunpo_kbn'
, size_l = :size_l
, size_w = :size_w
, size_h = :size_h
--#23747 2022.04.28 MOD STT
, jucyu_hin_cd = ':hin_cd'
--#23747 2022.04.28 MOD END
where w.s_id = :s_id
and w.link_no = :link_no
;

[addFile]
insert into w_basv0210_file
(s_id, file_kind, row_no, file_name, del_flg, cre_id, cre_time, upd_id, upd_time)
values(
  :s_id
, :w_file_kind
, (select COALESCE(max(row_no),-1) + 1 from w_basv0210_file where s_id = :s_id) --row
, ':w_file_name'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
)

[getUriFilePath]
select fnc_get_uri_filepath(:den_no,':tok_cd',':tok_nm',':syori_ymd') as file_name

[loadJucyuIkkatsu]
select fnc_init_basv0210_ikkatsu(:s_id, :ikkatsu_s_id, ':login_id')

[loadJucyuIkkatsuHead]
select
  t.tok_cd
, fnc_get_ikkatsu_tok_hacyu_no_list(:ikkatsu_s_id) as tok_hacyu_no
, t.sohin_no
, t.sohin_nm
, t.sohin_zip_no
, t.sohin_addr1
, t.sohin_addr2
, t.sohin_addr3
, t.sohin_addr4
, t.sohin_tel_no
, t.sohin_fax_no
, t.sohin_tan
, t.nohinsyo_kbn
from w_basv0210_ikkatsu w
inner join t_uri t on (t.den_no = w.jucyu_no)
where w.s_id = :ikkatsu_s_id
order by w.jucyu_no, w.jucyu_row_no
limit 1

[initKoutei]
select fnc_init_basv0210_koutei(:s_id, ':login_id')

[getTokuiEigCd]
select eig_cd as w_tok_eig_cd
from m_tokui
where tok_cd = ':w_tok_cd'

[getEigTanka]
select :tanka_field_id as tanka
from m_hin
where hin_cd = ':hin_cd'

[getEigGenka]
select :tanka_field_id as gentanka
from m_hin
where hin_cd = ':hin_cd'

[getSetHin]
select ms.chin_cd, ms.set_su
from m_hin mh
inner join m_sethin ms on (ms.hin_cd = mh.hin_cd)
where mh.hin_cd = ':hin_cd'
and mh.set_uri_flg = 1
order by row_no

[clearCopy]
update w_basv0210_m w set
  jucyu_no = null
, jucyu_row_no = null
where w.s_id = :s_id
;
--#23902 2022.05.14 MOD STT
update w_basv0210_sohin w set
  syukka_flg = 0
, uri_no = null
where w.s_id = :s_id
;
--#23902 2022.05.14 MOD END
--#23919 2022.05.16 MOD STT
--暫定？
--複製の時は消す
delete from w_basv0210_sohin w
where w.s_id = :s_id
;
--#23919 2022.05.16 MOD END

[getTokHTanka]
select htanka as w_htanka
from m_tok_hin
where tok_cd = ':tok_cd'
and hin_cd = ':hin_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

[checkThisDivDen]
select base_den_no
from w_basv0210
where s_id = :s_id
and base_den_no is not null

[checkExistsDivDen]
select den_no
from t_uri
where 1 = 1
and del_flg = 0
and jucyu_flg = 1
and base_den_no = :den_no

[checkSyukka1Done]
select row_no
from t_uri_m
where den_no = :den_no
and syukka1_time is not null

[checkSyukka2Done]
select row_no
from t_uri_m
where den_no = :den_no
and syukka2_time is not null

[checkUriDone]
select um.den_no, um.row_no
from t_uri_m um
inner join t_uri uh on (uh.den_no = um.den_no)
where um.jucyu_no = :den_no
and um.del_flg = 0
and uh.del_flg = 0

[getHacyuNo]
select sm.den_no as w_hacyu_no, sm.row_no as w_hacyu_row_no
, sh.nyuka_yotei_ymd as w_nyuka_yotei_ymd
from t_sir_m sm
left join t_sir sh on (sh.den_no = sm.den_no)
where sm.del_flg = 0
and sm.jucyu_no = :den_no
and sm.jucyu_row_no = :w_row_no
order by sm.den_no desc, sm.row_no limit 1

[checkSohinUriDone]
select jucyu_no, jucyu_row_no, uchi_seq
from t_uri_sohin
where jucyu_no = :den_no
and uri_no is not null

[loadSohinManual]
select link_no, uchi_seq
from w_basv0210_sohin
where s_id = :s_id
and manual_flg = 1

[initMark]
delete from w_den_mark
where s_id = :s_id
and den_kind = 1
;
update w_basv0210_m set mark_flg = null, mark2_flg = null, mark3_flg = null, nonyu_ymd = null, meisai_biko2 = null
where s_id = :s_id
;

[loadMark]
select mark_key
from w_den_mark
where s_id = :s_id
and den_kind = 1
and mark_flg = 1

[deleteMark]
delete from w_den_mark
where s_id = :s_id
and den_kind = 1
and mark_key = ':mark_key'

[insertMark]
insert into w_den_mark (
  s_id
, den_kind
, mark_key
, mark_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  :s_id
, 1 --den_kind
, ':mark_key'
, 1 --mark_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
)

[getTokAtch]
select t.row_no, t.file_name, t.file_image
from t_tok_atch t
where t.tok_cd in (
select mt.tok_cd
from m_tokui mt
where mt.tok_cd = ':tok_cd'
union all
select mt.sei_cd
from m_tokui mt
where mt.tok_cd = ':tok_cd'
union all
select mt.group_cd
from m_tokui mt
where mt.tok_cd = ':tok_cd'
)
and t.del_flg = 0

order by row_no

[getDefaultReportKind]
select h35.koumoku4 as w_kind
from m_tokui mt
left join m_hanyo h35 on (h35.mst_kbn = '35' and h35.han_cd = mt.cat_cd)
where mt.tok_cd = ':tok_cd'

[insertUserMst]
insert into m_user (
  user_cd
, user_nm
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  (select cast(coalesce(max(cast(user_cd as int)),0) + 1 as varchar) from m_user)
, ':user_nm'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
)

[getNewUserMst]
select user_cd
from m_user
where user_nm = ':user_nm'
and del_flg = 0
order by upd_time desc, user_cd limit 1

[copyRow_genka]
select fnc_update_basv0210_copy_genka(:s_id, :w_link_no_org, :w_link_no_new, ':login_id')

[getStockLoc]
select fnc_get_hin_stock_loc(':hin_cd', ':loc_no', ':loc_no_d') as now_stock

[checkHacyuMissHin]
select mh.hin_cd
from m_hin mh
inner join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = mh.cat_cd)
inner join m_hanyo h14 on (h14.mst_kbn = '14' and h14.han_cd = h05.koumoku1)
where mh.hin_cd = ':w_hin_cd'
--実体商品判定
and coalesce(h14.koumoku8,'') = ''

[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_mtok_send_log(-(:s_id), t.tok_cd, ':login_id')
from t_uri t
where t.den_no = :den_no

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