[loadOriginal]
select fnc_load_basv1020(:s_id, ':tok_cd', ':login_id');

[updateOriginal]
select fnc_update_basv1020(:s_id,':login_id');

[doDelete]
select fnc_delete_basv1020(':tok_cd', ':login_id')

[existKey]
select 1 from m_tokui
where tok_cd = ':tok_cd'

[getNewNo]
select coalesce(max(nonyu_no),0) +1 as newno
from m_nonyu
where tok_cd = ':tok_cd'
;

[load_grdList_disc]
select 
  w.s_id
--, w.tok_cd
, w.cat_cd
, w.cat_nm
, (case when(h99.koumoku2 = 'rev')then(100 - w.disc_per)else(w.disc_per)end) as disc_per
, w.rebate_per
from w_basv1020_disc w
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = w.cat_cd)
left join m_hanyo h99 on (h99.han_cd = 'tok-disc' and h99.mst_kbn = '99')
where 1 = 1
and s_id = :s_id
order by h05.sort_key, h05.han_cd


[load_grdList_price]
select tk.tok_cd, han.han_name, coalesce(tk.cat_cd, han.han_cd) as cat_cd, tk.price_rate
from m_hanyo han 
left join m_tok_price tk on tk.cat_cd = han.han_cd and tk.tok_cd = ':tok_cd'
where han.mst_kbn = '14'
order by cat_cd
;

[load_grdList_hin]
select hin.hin_cd, hin.hin_nm, count(th.hin_cd) as kakutei_flg
from m_hin hin 
left join m_tok_hin th on th.hin_cd = hin.hin_cd and th.tok_cd = ':tok_cd'
left join m_tok_hin th2 on th2.hin_cd = hin.hin_cd and th2.tok_cd != ':tok_cd'
where th2.hin_cd is null
group by hin.hin_cd, hin.hin_nm
order by hin.hin_cd
;

[load_grdList_sales]
SELECT tan.tan_nm,
COALESCE(p.yotei_stt_ymd, ''''::character varying) AS yotei_stt_ymd,
p.yotei_time,
p.ymd,
p.stt_time_s,
p.end_time_s,
p.rank,
p.biko
FROM (t_eigyo_plan p
LEFT JOIN m_tanto tan ON ((((tan.tan_cd)::text = (p.eigyou_tan_cd)::text) AND (p.del_flg <> 1))));
where p.tok_cd = ':tok_cd'
and p.del_flg != 1
;

[load_grdList_doc]
SELECT s_id,row_no,file_name
FROM w_den_atch
where s_id = :s_id
order by row_no
;

[delete_disc]
delete from w_basv1020_disc
where s_id = :s_id
;

[delete_rate]
delete from m_tok_price
where tok_cd = ':tok_cd'
;

[get_new_cd]
select
--#23818 2022.05.15 MOD STT
  (case when(:w_cd_len is null)then(cast(w.newcd as varchar))else(lpad(cast(w.newcd as varchar),:w_cd_len,'0'))end) as newcd
--#23818 2022.05.15 MOD END
from (
select max(tok_cd::numeric) + :mtok_auto::numeric as newcd
from m_tokui
where tok_cd ~ '[0-9]+' --���K�\��
--#23818 2022.05.15 MOD STT
and ((:w_upper_value is null) or (tok_cd::numeric < :w_upper_value))
--#23818 2022.05.15 MOD END
) w


[delete_wk]
delete from w_basr0030
where 1=1
and s_id = :s_id
;

[delete_hin]
delete from m_tok_hin
where tok_cd = ':tok_cd'
;

[insert_wk]
--insert into w_basr0030(s_id, tok_cd, del_flg, cre_id, cre_time, upd_id, upd_time)
--values(:s_id, ':tok_cd', 0, ':login_id', now(), ':login_id', now())
insert into w_basr0030(s_id, seikyu_ymd, tok_cd, del_flg, cre_id, cre_time, upd_id, upd_time)
values(:s_id, ':seikyu_ymd', ':tok_cd', 0, ':login_id', now(), ':login_id', now())


[exist_tok_hin]
select hin_cd from m_tok_hin
where hin_cd = ':e_hin_cd'
and tok_cd != ':tok_cd'
;

[get_urikake_Info]
--#25116 2022.08.04 MOD STT
--select seikyu_zan as _seikyu_zan, nyukin_gaku as _nyukin_gaku, zeikomi_gaku as _zeikomi_gaku, seikyu_ymd as _seikyu_ymd
--,(coalesce(seikyu_zan,0) + coalesce(zeikomi_gaku,0))- coalesce(nyukin_gaku,0) as _kake_zan
--from v_urikake_zan
--where tok_cd = ':tok_cd'
--;
select
  zen_seikyu_zan as _seikyu_zan
, nyukin_gaku as _nyukin_gaku
, zeikomi_gaku as _zeikomi_gaku
, zen_seikyu_ymd as _seikyu_ymd
, kon_seikyu_zan as _kake_zan
from fnc_query_urikake_zan(array[':tok_cd'])
--#25116 2022.08.04 MOD END

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

[update_atch]
select fnc_update_tok_atch(:s_id, ':tok_cd');

[load_atch]
select fnc_load_tok_atch(:s_id, ':tok_cd');

[insert_disc]

[deleteWork]
delete from w_basv1020 where s_id = :s_id

[initDisc]
delete from w_basv1020_disc where s_id = :s_id;
insert into w_basv1020_disc (
	  s_id
	, cat_cd
	, cat_nm
	, del_flg
	, cre_id
	, cre_time
	, upd_id
	, upd_time
)
select 
	  :s_id
	, h05.han_cd
	, h05.han_name
	, 0 --del_flg
	, ':login_id' --cre_id
	, now() --cre_time
	, ':login_id' --upd_id
	, now() --upd_time
from m_hanyo h05
where h05.mst_kbn = '05'
;

[insertHead]
delete from w_basv1020 where s_id = :s_id;
insert into w_basv1020 (
  s_id
, tok_cd
) values (
  :s_id
, ':tok_cd'
)
;

[updateDiscLowerState]
update w_basv1020_disc w set
  lower_cnt = (
select coalesce(sum(case
	when(md.manual_flg = 1)then(1)
	when(w.disc_per is null and md.disc_per is not null)then(1)
	when((case when(h99.koumoku2 = 'rev')then(100 - w.disc_per)else(w.disc_per)end) != md.disc_per)then(1)
	else(0)
end),0)
from m_tokui mt
inner join m_tok_disc md on (md.tok_cd = mt.tok_cd and md.cat_cd = w.cat_cd)
left join m_hanyo h99 on (h99.han_cd = 'tok-disc' and h99.mst_kbn = '99')
where mt.sei_cd = ':sei_cd'
and mt.tok_cd != mt.sei_cd
)
where s_id = :s_id

[updateDiscLowerState_g]
update w_basv1020_disc w set
  lower_cnt = (
select coalesce(sum(case
	when(md.manual_flg = 1)then(1)
	when(w.disc_per is null and md.disc_per is not null)then(1)
	when((case when(h99.koumoku2 = 'rev')then(100 - w.disc_per)else(w.disc_per)end) != md.disc_per)then(1)
	else(0)
end),0)
from m_tokui mt
inner join m_tok_disc md on (md.tok_cd = mt.tok_cd and md.cat_cd = w.cat_cd)
left join m_hanyo h99 on (h99.han_cd = 'tok-disc' and h99.mst_kbn = '99')
where mt.group_cd = ':group_cd'
and mt.group_cd != ''
and mt.tok_cd != mt.group_cd
)
where s_id = :s_id

[updateDiscUpperState]
update w_basv1020_disc w set
  lower_cnt = 0
, lower_upd_flg = (
select max(md.lower_upd_flg)
from m_tokui mt
inner join m_tok_disc md on (md.tok_cd = mt.tok_cd and md.cat_cd = w.cat_cd)
where mt.tok_cd = ':sei_cd'
)
where s_id = :s_id

[updateDiscUpperState_g]
update w_basv1020_disc w set
  lower_cnt = 0
, lower_upd_flg = (
select max(md.lower_upd_flg)
from m_tokui mt
inner join m_tok_disc md on (md.tok_cd = mt.tok_cd and md.cat_cd = w.cat_cd)
where mt.tok_cd = ':group_cd'
)
where s_id = :s_id

[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, ':tok_cd', ':login_id')

[sendFaxKojin]
select fnc_insert_mkojin_send_log(:s_id, ':tok_cd', ':w_kojin_no', ':login_id')

[getSendAtch]
select t.file_name, t.file_image
from t_send_log t
where t.send_no = :send_no
and t.file_image is not null