[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

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

[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]
select seikyu_ymd from m_tokui where tok_cd = ':tok_cd';


[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
SELECT 1 AS next_no
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]
update w_basv0210_m set tok_cd = ':w_tok_cd', sohin_no = :w_sohin_no
where s_id = :s_id

[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
, gm.hin_cd
, substr(gm.hin_cd,1,1)
, substr(gm.hin_cd,2,1)
, substr(gm.hin_cd,3,1)
--#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')
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_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)
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
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
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
;