[updateOriginal]
select fnc_update_basv0213(:s_id, :orow_no, :s_id_i);

[updateDelFlg]
update m_hin set
	del_flg = 1
	,upd_id = ':id_user'
	,upd_time = now()
where
	hin_cd = ':hin_cd'
;

[loadOriginal]
select fnc_load_basv0213(:s_id, :s_id_i, :orow_no);

[load_grdList]
select w.*
,v.han_name as tani_nm
from w_basv0210_mm w
left join v_hanyo_all v on w.tani = v.han_cd and v.mst_kbn = '44'
where 1 = 1
and s_id = :s_id
order by row_no

[getCombo]
select han_cd, han_name
from m_hanyo
where mst_kbn = '14'
order by sort_key,mst_kbn, han_cd
;

[getHeader]
select t.mitsumori_no, t.syori_ymd, t.title1, t.title2, hin_nm, meisai_biko
from t_uri t
left join t_uri_m d on t.den_no = d.den_no
where t.den_no = :den_no
and d.row_no = :orow_no
;

[get_hin_info]
select h1.han_name as cat_nm
	, h2.han_name as hin_nm
	, h3.han_name as hosoku1
	, h4.han_name as tani
	, m.hin_nm as hosoku2
	, m.cat_cd
	, m.hin_sts
	, m.brand_cd
	, h3.koumoku8 as konpo_biko
from m_hin m
left join m_hanyo h1 on m.cat_cd = h1.han_cd and h1.mst_kbn = '14'
left join m_hanyo h2 on m.hin_sts = h2.han_cd and h2.mst_kbn = '05'
left join m_hanyo h3 on m.brand_cd = h3.han_cd and h3.mst_kbn = '06'
left join m_hanyo h4 on m.tani = h4.han_cd and h4.mst_kbn = '44'
where hin_cd = ':_hin_cd'
;

[select_hin]
select hin_cd as select_cd
from m_hin m
--where fnc_translate_case(hin_nm) like '%'||fnc_translate_case(':_katashiki')||'%'
where 1 = 1
and del_flg = 0
and hin_nm = ':_katashiki'

[get_price_rate]
select fnc_price_rate(':orow_no') as rate
;

[load_grdList_rate]
--select w.cat_cd, nm.han_name as han_name, coalesce(coalesce(w.price_rate, coalesce(p.price_rate, m.koumoku5::numeric)),0) as price_rate
select w.cat_cd, nm.han_name as han_name, coalesce(w.price_rate, coalesce(p.price_rate, m.koumoku5::numeric)) as price_rate
from w_basv0213_rate w 
left join m_hanyo m on w.cat_cd = m.han_cd and m.mst_kbn = '14' and koumoku5 != ''
left join m_hanyo nm on w.cat_cd = nm.han_cd and nm.mst_kbn = '14' --名称取得用
left join m_tok_price p on p.tok_cd = ':_tok_cd' and w.cat_cd = p.cat_cd
where w.s_id = :s_id
and 0 = :_cond
union all 
select w.cat_cd, nm.han_name as han_name, coalesce(coalesce(p.price_rate, m.koumoku5::numeric),0) as price_rate
from w_basv0213_rate w 
left join m_hanyo m on w.cat_cd = m.han_cd and m.mst_kbn = '14' and koumoku5 != ''
left join m_hanyo nm on w.cat_cd = nm.han_cd and nm.mst_kbn = '14' --名称取得用
left join m_tok_price p on p.tok_cd = ':_tok_cd' and w.cat_cd = p.cat_cd
where w.s_id = :s_id
and 1 = :_cond
;

[exist_den]
select 1 from t_uri where den_no = :chk_no
;

[delete_work_rate]
delete from w_basv0213_rate where s_id = :s_id;

[insert_work_rate]
insert into w_basv0213_rate (s_id, cat_cd, price_rate)
values(:s_id, ':_cat_cd', :_num);

[deleteWorkList]
delete from w_basv0213_list
where s_id = :s_id
;

[insertWorkList]
insert into w_basv0213_list
(s_id, row_no, hin_nm, katashiki_no, brand1, suryo, meisai_biko)
values(:s_id, :_row_no, ':_hin_nm', ':_katashiki_no', ':_brand1', :_suryo, ':_meisai_biko');

[insertWorkTbl]
select fnc_list_to_work(:s_id, :orow_no, ':mitsumori_no');

[getWorkList]
select * from w_basv0213_list
where s_id = :s_id
order by row_no
;

[search_hin_nm_old]
select mst_kbn, han_cd, right(han_cd,3) as han_name, koumoku1, koumoku2, han_name as koumoku3, sort_key
from m_hanyo
where mst_kbn = '05'
and koumoku1 = ':koumoku1'
order by mst_kbn, han_cd
;

[search_hin_nm]
select h1.han_cd, h1.koumoku2 as c_han_cd, h1.han_name, h1.sort_key
, h0.han_cd as p_han_cd, h0.han_name as p_han_name, h0.han_cd as p_key
from m_hanyo h1
left join m_hanyo h0 on (h0.mst_kbn = '14' and h0.han_cd = h1.koumoku1)
where h1.mst_kbn = '05'
and (('' = ':koumoku1') or (h1.koumoku1 = ':koumoku1'))
order by sort_key, han_cd

[search_maker_old]
select mst_kbn, han_cd, right(han_cd,3) as han_name, koumoku1, koumoku2, coalesce(han_name,'')||coalesce(koumoku8,'') as koumoku3, sort_key
from m_hanyo
where mst_kbn = '06'
and koumoku1 = ':koumoku1'
order by mst_kbn, han_cd
;

[search_bunrui]
select h1.han_cd, h1.koumoku2 as c_han_cd, h1.han_name, h1.sort_key
--, h0.han_cd as p_han_cd, h0.han_name as p_han_name, h0.han_cd as p_key
from m_hanyo h1
--left join m_hanyo h0 on (h0.mst_kbn = '14' and h0.han_cd = h1.koumoku1)
where h1.mst_kbn = '14'
--and (('' = ':koumoku1') or (h1.koumoku1 = ':koumoku1'))
order by sort_key, han_cd
;

[search_maker]
select h2.han_cd, h2.koumoku2 as c_han_cd, h2.han_name||''||COALESCE(h2.koumoku8,'') as han_name, h2.sort_key
, h1.koumoku2 as p_han_cd, h1.han_name as p_han_name, h1.han_cd as p_Key
, h0.han_cd as pp_han_cd, h0.han_name as pp_han_name, h0.han_cd as pp_Key
from m_hanyo h2
left join m_hanyo h1 on (h1.mst_kbn = '05' and h1.han_cd = h2.koumoku1)
left join m_hanyo h0 on (h0.mst_kbn = '14' and h0.han_cd = h1.koumoku1)
where h2.mst_kbn = '06'
and (('' = ':koumoku1') or (h2.koumoku1 = ':koumoku1'))
and (('' = ':_cat_cd') or (h0.han_cd = ':_cat_cd'))
and (('' = ':_hin_sts') or (h1.han_cd = ':_hin_sts'))
order by sort_key, han_cd

[search_katashiki]
select hin_cd as han_cd
, hin_nm as han_name
, hin_cd as sort_key
from m_hin
where ((brand_cd = ':_brand_cd') or (':_brand_cd' = ''))
and ((hin_sts = ':_hin_sts') or (':_hin_sts' = ''))
and cat_cd = ':_cat_cd'
order by hin_cd
;

[search_katashiki_old]
select hin_cd as han_cd, right(hin_cd,3) as han_name, hin_nm as koumoku3,hin_cd as sort_key
from m_hin
where ((brand_cd = ':_brand_cd') or (':_brand_cd' = ''))
and ((hin_sts = ':_hin_sts') or (':_hin_sts' = ''))
and cat_cd = ':_cat_cd'
order by hin_cd
;

[get_hin_nm]
select han_cd as hin_sts, han_name as hin_nm
from m_hanyo
where mst_kbn = '05'
and han_cd = ':_hin_sts'
order by mst_kbn, han_cd
;

[get_maker]
select han_cd as brand_cd, han_name as hosoku1,koumoku8 as konpo_biko
from m_hanyo
where mst_kbn = '06'
and han_cd = ':_brand_cd'
order by mst_kbn, han_cd
;

[getSupply]
select han_cd
from m_hanyo
where mst_kbn = '14'
and koumoku7 = '1'
order by han_cd
;

[max_rowNo]
select 
coalesce(max(row_no),0)+1 as max_row_no
from w_basv0210_mm
--where s_id = '28701'
where s_id = ':s_id'
;

[exportList]



[searchMatchHin]
select hin_nm as w_hin_nm, min(hin_cd) as w_hin_cd, count(*) as w_match_cnt
from m_hin
where 1 = 1
and del_flg = 0
and hin_nm = ':w_hin_nm'
group by hin_nm