[load_grdList]
select
  um.den_no
, um.row_no
, um.hin_cd
--#26104 2022.11.07 MOD STT
--, um.hin_nm
, um.hin_nm||(case when(h99a.han_name = '1')then('')else(COALESCE(' '||um.hin_nm_ext,''))end) as hin_nm
--#26104 2022.11.07 MOD END
, COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0) as hacyu_zan
, um.hacyu_su
, hin.tani as tani --（単位）AVの機能
, um.tanka as tanka
, um.tanka as tankaF2
--, um.suryo
, um.hacyu_su - um.sir_su as haccyu_suryo
, um.kingaku
, uh.syori_ymd
--#15286 2020.08.02 MOD STT
--, um.nyuka_yotei_ymd as nonyu_ymd
, (case when(':sir_kb_ymd' = '1')then((case when(um.kibo_nyuka_ymd != '')then(um.kibo_nyuka_ymd)else(um.nyuka_yotei_ymd)end))else(um.nyuka_yotei_ymd)end) as nonyu_ymd
, (case when(':sir_kb_ymd' = '1')then((case when(um.kibo_nyuka_ymd != '')then(um.nyuka_yotei_ymd)else(null)end))else(um.nyuka_yotei_ymd)end) as ans_nonyu_ymd
--#15286 2020.08.02 MOD END
, uh.sir_cd
, uh.sir_nm
, uh.biko1
--#20495 2021.08.30 MOD STT
, uh.biko2
--, um.meisai_biko as biko2 --明細行の備考欄
, um.meisai_biko --明細行の備考欄
--#20495 2021.08.30 MOD END
, um.hosoku1 --（仕立）AVの機能
, um.jucyu_no
, '' as syubetsu
, hin.cat_cd
, h.koumoku1
, ur.tok_nm
, hin.jan_cd
, h2.han_name as prc_sts
, to_char(uh.print_time,'YYYYMMDD') as print_ymd
, jm.tok_hacyu_no
, uh.tok_cd as c_tok_cd
, uh.tok_nm as c_tok_nm
, uh.kanri_no
, um.hosoku3
from t_sir uh
inner join t_sir_m um on (um.den_no = uh.den_no)
left join m_hin hin on (um.hin_cd = hin.hin_cd)
left join m_sir sh on (sh.sir_cd = uh.sir_cd)
left join m_hanyo h on (h.mst_kbn = '05' and h.han_cd = hin.cat_cd)
left join m_hanyo h2 on (h2.mst_kbn = '11' and h2.han_cd = uh.prc_sts)
left join t_uri ur on (ur.den_no = um.jucyu_no)
left join t_uri_m jm on (jm.den_no = um.jucyu_no and jm.row_no = um.jucyu_row_no)
left join m_tokui tk on (ur.tok_cd = tk.tok_cd)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'nnmx-v0440')
WHERE 1 = 1
and uh.hacyu_flg = 1
and uh.del_flg = 0
and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
and (('' = ':nonyu_ymd_fr') or (um.nyuka_yotei_ymd >= ':nonyu_ymd_fr'))
and (('' = ':nonyu_ymd_to') or (um.nyuka_yotei_ymd <= ':nonyu_ymd_to'))
and (('' = ':sir_cd') or (uh.sir_cd = ':sir_cd'))
AND ((fnc_translate_case(sh.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(sh.sir_kana) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(uh.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
--#20495 2021.08.30 MOD STT
--AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(uh.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
--#20495 2021.08.30 MOD END
and (('' = ':zan_flg') or (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0) > 0))
and ((um.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
and (('' = ':hin_cd') or (um.hin_cd = ':hin_cd'))
--#26104 2022.11.07 MOD STT
--AND ((fnc_translate_case(hin.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
AND ((fnc_translate_case(hin.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(um.hin_nm||COALESCE(' '||um.hin_nm_ext,'')) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--#26104 2022.11.07 MOD END
:multi_cond_hin_nm
--AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--and ((um.hin_nm like '%:hin_nm%') or ('' = ':hin_nm'))
and (('' = ':tok_tan') or (uh.tan_cd = ':tok_tan'))
and ((h.han_cd is not null and h.koumoku1 = ':dcat_cd') or (':dcat_cd' = ''))
and (('' = ':cat_cd') or (hin.cat_cd = ':cat_cd'))
and (('' = ':tok_cd') or (ur.tok_cd = ':tok_cd'))
and (('' = ':prc_sts') or (uh.prc_sts = ':prc_sts'))
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(ur.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (':tok_nm' = ''))
and (('' = ':eig_cd') or (uh.eig_cd = ':eig_cd'))
and (('' = ':scat_limit') or (h.koumoku8 = ':scat_limit'))
AND ((fnc_translate_case(jm.tok_hacyu_no) LIKE '%'||fnc_translate_case(':tok_hacyu_no')||'%') or (':tok_hacyu_no' = ''))
--and (('' = ':tok_hacyu_no') or (jm.tok_hacyu_no like '%:tok_hacyu_no%'))
--単価検索
and (('' = ':tanka_fr') or (um.tanka >= fnc_to_number(':tanka_fr')))
and (('' = ':tanka_to') or (um.tanka <= fnc_to_number(':tanka_to')))
--数量検索
and (('' = ':suryo_fr') or (um.hacyu_su >= fnc_to_number(':suryo_fr')))
and (('' = ':suryo_to') or (um.hacyu_su <= fnc_to_number(':suryo_to')))
and ((':cond_cyokus' != '0') or (coalesce(uh.tok_nm,'') = ''))
and ((':cond_cyokus' != '1') or (uh.tok_nm != ''))
and (('' = ':kanri_no') or (uh.kanri_no like '%:kanri_no%'))
and (('' = ':hosoku3') or (um.hosoku3 like '%:hosoku3%'))
and (('' = ':eigyou_tan_cd') or (ur.eigyou_tan_cd = ':eigyou_tan_cd'))

order by um.nyuka_yotei_ymd, uh.syori_ymd, um.den_no, um.row_no



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

[commitWork]
select fnc_update_basv0220(:s_id);


[insertHeaderWork_type2]
insert into w_basv0220 (
  s_id
, henpin_flg
, seikyu_flg
--, syukka_flg
, zaiko_flg
, hacyu_flg
--, cash_flg
, eig_cd
, sir_cd
, sir_nm
, sir_tan
, syori_ymd
, input_ymd
, nyuka_yotei_ymd
, tan_cd
--, biko1
--, biko2
, zeinuki_gaku
, zei_hasu_kbn
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, 0 --henpin_flg
, 0 --seikyu_flg
--, 0 --syukka_flg
, 0 --zaiko_flg
, 1 --hacyu_flg
--, 0 --cash_flg
, ':eig_cd' --eig_cd
, mt.sir_cd
, mt.sir_nm
, mt.sir_tan
, ':syori_ymd'
, ':syori_ymd' --input_ymd
, ':nyuka_yotei_ymd'
, ':login_id' --tan_cd
--, ':biko1'
--, ':biko2'
, :zeinuki_gaku
, '2'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_sir mt
where mt.sir_cd = ':sir_cd'
;

[insertDetailWork_type2]
insert into w_basv0220_m (
  s_id
, row_no
, hin_cd
, hin_nm
, hacyu_su
, tanka
, kingaku
, zeinuki_gaku
--, hacyu_zeinuki_gaku
, nyuka_yotei_ymd
, zaiko_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, :row_no
, ':hin_cd'
, ':hin_nm'
, :hacyu_su
, :tanka
, :kingaku --kingaku
, :kingaku --zeinuki_gaku
--, :kingaku --hacyu_zeinuki_gaku
, ':nyuka_yotei_ymd'
, 1 --zaiko_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
;


[Upd_hacyu]
update t_sir_m set suryo = :suryo
where den_no = :den_no and row_no = :row_no
;

[Del_hacyu]
update t_sir_m
set del_flg = 1
	,upd_id = ':login_id'
	,upd_time = now()
where den_no = :den_no
;

update t_sir 
set del_flg = 1
	,upd_id = ':login_id'
	,upd_time = now()
where den_no = :den_no 
and hacyu_flg = 1
;

[Load_basv0220]
select fnc_load_basv0220(:s_id, :den_no);

[Upd_basv0220]
select fnc_update_basv0220(:s_id, :den_no);


[getNewDenNo]
select den_no as new_den
from w_basv0220
where s_id = :s_id
;

--select coalesce(max(den_no)) as new_den
--from t_sir
--where hacyu_flg = '1'



[getKingaku]
select sum(kingaku) as goukei
from w_basv0220_m
where s_id = :s_id;

[updKingaku]
update t_sir t set zeinuki_gaku = w.zeinuki_gaku
from w_basv0220 w
where w.s_id = :s_id and t.den_no = :hacyu_no

[updWorkKazei]
update t_sir t set kazei_gaku = (select sum(kingaku) as goukei from w_basv0220_m where s_id = :s_id)
where t.den_no = :hacyu_no
;

[updWorkZei]
update t_sir t set
	  zeinuki_gaku = kazei_gaku
	, soto_gaku = fnc_get_zei(kazei_gaku, t.syori_ymd)
	, uchi_gaku = 0
where t.den_no = :hacyu_no
;

[updWorkZeikomi]
update t_sir t set
	  zei_gaku = COALESCE(soto_gaku,0) + COALESCE(uchi_gaku,0)
	, zeikomi_gaku = COALESCE(zeinuki_gaku,0) + COALESCE(soto_gaku,0) + COALESCE(uchi_gaku,0)
where t.den_no = :hacyu_no
;

[get_tok_hacyu]
select tok_hacyu_no
from t_sir
where 1=1
and tok_hacyu_no = ':tok_hacyu_no'
and tok_cd = ':tok_cd'
and del_flg = 0
;

[gethinCd]
select hin_cd
from m_hin
where 1=1
and hin_cd = ':hin_cd'
;

[cancelJucyuzan]
update t_sir_m set
  hacyu_cancel_su = (hacyu_su - COALESCE(uri_su,0))
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_den_no
and ((:w_row_no < 0) or (row_no = :w_row_no))
and (hacyu_su - COALESCE(uri_su,0)) != 0


[get_shiji_no]
select shiji_no
from t_seizou
where 1=1
and hacyu_no = ':den_no'
and del_flg = '0'
and shiji_flg = '1'
;

[updatePrint]
update t_sir set print_time = now(), upd_id = ':login_id', upd_time = now()
where den_no = :w_den_no

[clearPrint]
update t_sir set print_time = null, upd_id = ':login_id', upd_time = now()
where den_no = :w_den_no

[deleteSelWork]
delete from w_basv0440_sel where s_id = :s_id

[insertSelWork]
insert into w_basv0440_sel (s_id, den_no, row_no, del_flg, cre_id, cre_time, upd_id, upd_time)
 values (:s_id, :den_no, :row_no, 0, ':login_id', now(), ':login_id', now())

[createSir]
select fnc_update_basv0440_sir(:s_id, ':login_id')