[load_grdList]
SELECT
  m.sir_cd
, m.sir_nm
, m.sir_rnm
, m.sir_kana
, m.zip_no
, COALESCE(addr1,'') || COALESCE(addr2,'') || COALESCE(addr3,'') || COALESCE(addr4,'') AS addr1
, m.tel_no
, m.mob_no
, m.tori_kbn
, h1.han_name as tori_kbn_nm
, m.biko1
, m.biko2
, h2.han_name as cat_nm
, case (m.shime_dd::varchar)
	when '99' then '末日'
	else m.shime_dd::varchar || '日'
	end as shime_dd
, COALESCE(h3.han_name,'')||(case when(m.pay_d = 99)then('末日')else(cast(m.pay_d as varchar)||'日')end) as pay_md
, h29a.han_name as kaisyu_cond
, h29b.han_name as kaisyu_cond2
, m.kaisyu_site
, m.kaisyu_site2
, m.tegata_ratio
--#20420 2021.08.24 MOD STT
, (case when(h99a.han_name = '1')then(m.sir_cd)else(coalesce(m.sir_kana, ''))end) as order_value
--#20420 2021.08.24 MOD END
FROM m_sir m
LEFT JOIN m_hanyo h1 on (h1.mst_kbn = '12' and h1.han_cd = m.tori_kbn)
LEFT JOIN m_hanyo h2 on (h2.mst_kbn = '45' and h2.han_cd = m.cat_cd)
LEFT JOIN m_hanyo h3 on (h3.mst_kbn = '10' and h3.han_cd = cast(m.pay_m as varchar))
LEFT JOIN m_hanyo h29a on (h29a.mst_kbn = '29' and h29a.han_cd = m.kaisyu_cond)
LEFT JOIN m_hanyo h29b on (h29b.mst_kbn = '29' and h29b.han_cd = m.kaisyu_cond2)
LEFT JOIN m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'orcd-v8030')
WHERE 1 = 1
AND ((m.sir_cd = ':sir_cd') or (':sir_cd' = ''))
--AND ((m.sir_nm LIKE '%:sir_nm%') or (m.sir_kana LIKE '%:sir_nm%') or (':sir_nm' = ''))
--#14318 2020.10.09 MOD STT
--AND ((fnc_translate_case(m.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(m.sir_kana) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
AND ((m.search_nm LIKE '%'||':search_sir_nm'||'%') or (m.search_kana LIKE '%'||':search_sir_nm'||'%') or (':sir_nm' = ''))
--#14318 2020.10.09 MOD END
AND ((COALESCE(addr1,'') || COALESCE(addr2,'') || COALESCE(addr3,'') || COALESCE(addr4,'')  LIKE '%:addr1%') or (':addr1' = ''))
--AND ((m.tel_no LIKE '%:tel_no%' or m.mob_no LIKE '%:tel_no%') or (':tel_no' = ''))
--#14318 2020.10.09 MOD STT
--AND ((fnc_translate_case(m.tel_no) LIKE '%'||fnc_translate_case(':tel_no')||'%' or fnc_translate_case(m.mob_no) LIKE '%'||fnc_translate_case(':tel_no')||'%') or (':tel_no' = ''))
AND ((fnc_translate_case(m.tel_no) LIKE '%'||':search_tel_no'||'%' or fnc_translate_case(m.mob_no) LIKE '%'||':search_tel_no'||'%') or (':tel_no' = ''))
--#14318 2020.10.09 MOD END
AND ((m.tori_kbn = ':tori_kbn') or (':tori_kbn' = ''))
AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
AND ((fnc_get_kana_group(m.sir_kana) = ':kana' or '' = ':kana')) --50音検索ないときはkana = ''
AND ((m.eig_cd = ':eig_cd') or (':eig_cd' = ''))
AND ((m.shime_dd::varchar = ':shime_dd') or (':shime_dd' = ''))
and m.del_flg = 0
--#20420 2021.08.24 MOD STT
--order by coalesce(m.sir_kana, ''), m.sir_cd
order by order_value, sir_cd
--#20420 2021.08.24 MOD END

[getTranslateCaseCond]
select fnc_translate_case(':sir_nm') as search_sir_nm, fnc_translate_case(':tel_no') as search_tel_no

[deleteRecord]
delete from m_sir
where sir_cd = ':sir_cd'


[insertRecord]
insert into m_sir
( 
  sir_cd
, sir_nm
, zip_no
, addr1
, addr2
, tel_no
, cre_time
, upd_time
, tan_cd
, rank
)
values
(
  ':tok_cd'			--tok_cd
, ':tok_nm'			--tok_nm
, ':zip_no'			--zip_no
, ':addr1'			--addr1
, ':addr2'			--addr2
, ':tel_no'			--tel_no
, 'now()'			--cre_time
, 'now()'			--upd_time
, ':tan_cd'			--tan_cd
, ':rank'			--rank
);