[load_grdList]
SELECT
  sh.den_no
, sh.syori_ymd
--# 2022.05.30 MOD STT
, sh.input_ymd
--# 2022.05.30 MOD END
, sh.tan_cd
, tan.bu_cd
, h55.han_name as bu_nm
, sh.sir_cd
, sh.sir_nm
, sh.sir_tan
, sir.cat_cd as s_cat_cd
, h45.han_name as s_cat_nm
, sh.zeinuki_gaku as h_zeinuki_gaku
, sh.zei_gaku
, sh.zeikomi_gaku
, sh.kazei_gaku as h_kazei_gaku
, sh.soto_gaku
, sh.uchi_gaku as h_uchi_gaku
, coalesce(sh.hacyu_no::text,'') as hacyu_no
, sm.row_no
, sm.hin_cd
-- #21808 2022.06.12
--, sm.hin_nm
, sm.hin_nm||(case when(h99b.han_name = '1')then('')else(COALESCE(' '||sm.hin_nm_ext,''))end) as hin_nm --ToDo
-- #21808 2022.06.12
, sm.size_l
, sm.size_w
, sm.size_h
--# 2022.05.30 MOD STT
, sm.hosoku3
, sm.hosoku4
--# 2022.05.30 MOD END
, sm.color_nm
, h05.koumoku1 as dcat_cd
, h14.han_name as dcat_nm
, hin.cat_cd as hcat_cd
, h05.han_name as cat_nm
, hin.brand_cd
, h06.han_name as brand_nm
, sm.suryo
, sm.tanka
, sm.kingaku
, sm.kazei_gaku
, sm.zeinuki_gaku
, sm.uchi_gaku
, uri.tok_nm as simuketi
--#15878 2020.09.21 MOD STT
--, sm.jucyu_no
, COALESCE(hm.jucyu_no,ts.jucyu_no) as jucyu_no
--#15878 2020.09.21 MOD END
, sir.tori_cond
, h18.han_name as zei_kbn
, h05.han_name as cat_cd
, sm.meisai_biko as m_biko
, sm.zaiko_flg
, sh.kanri_no
--TODO
, (case when(sm.iri_su != 0 and COALESCE(h99a.han_name,'') != '1')then(sm.iri_tani)else(sm.tani)end) as tani
, (case when(sm.iri_su != 0 and COALESCE(h99a.han_name,'') != '1')then(sm.tani)else(null)end) as bara_tani
, (case when(sm.iri_su != 0 and COALESCE(h99a.han_name,'') != '1')then(sm.suryo * sm.iri_su)else(null)end) as bara_sir_su
, sm.iri_tani
, (case when(sm.iri_su != 0)then(sm.suryo / sm.iri_su)else(null)end) as iri_sir_su
, sh.biko1
, sh.biko2
--# 2022.05.30 MOD STT
, sm.zei_rate
--# 2022.05.30 MOD END
FROM t_sir sh
left join t_sir_m sm on (sh.den_no = sm.den_no)
left join m_sir sir on (sh.sir_cd = sir.sir_cd)
left join m_hin hin on (hin.hin_cd = sm.hin_cd)
left join m_tanto tan on (tan.tan_cd = sh.tan_cd)
--#15878 2020.09.21 MOD STT
--left join t_uri uri on (sm.jucyu_no = uri.jucyu_no)
left join t_sir_m hm on (hm.den_no = sm.hacyu_no and hm.row_no = sm.hacyu_row_no)
left join t_shiyou_zairyo sz on (sz.hacyu_no = sm.hacyu_no and sz.hacyu_row_no = sm.hacyu_row_no)
left join t_seizou ts on (ts.shiji_no = sz.shiji_no)
left join t_uri uri on (uri.den_no = COALESCE(hm.jucyu_no,ts.jucyu_no))
--#15878 2020.09.21 MOD END
left join m_hanyo h55 on (h55.mst_kbn = '55' and h55.han_cd = tan.bu_cd)
left join m_hanyo h45 on (h45.mst_kbn = '45' and h45.han_cd = sir.cat_cd)
left join m_hanyo h18 on (h18.mst_kbn = '18' and h18.han_cd = sm.zei_kbn::text)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = hin.cat_cd)
left join m_hanyo h14 on (h14.mst_kbn = '14' and h14.han_cd = h05.han_cd)
left join m_hanyo h06 on (h06.mst_kbn = '06' and h06.han_cd = hin.brand_cd)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'st-no-iris')
left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'nnmx-v0460')
WHERE 1 = 1
and sh.del_flg = 0
and sh.syori_ymd != ''
and sh.hacyu_flg = 0
and ((sh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (sh.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (sh.syori_ymd <= ':syori_ymd_to'))
and (('' = ':prc_sts') or (sh.prc_sts = ':prc_sts'))
and (('' = ':sir_cd') or (sh.sir_cd = ':sir_cd'))
and ((sir.sir_nm like '%:sir_nm%') or ('' = ':sir_nm'))
--#20600 2021.09.05 MOD STT
--and ((sh.biko1 like '%:biko1%' or sh.biko2 like '%:biko1%') or ('' = ':biko1'))
and ((sh.biko1 like '%:biko1%' or sh.biko2 like '%:biko1%' or sm.meisai_biko like '%:biko1%') or ('' = ':biko1'))
--#20600 2021.09.05 MOD END
and (('' = ':tori_kbn') or (sir.sir_cd is not null and sir.tori_kbn = ':tori_kbn'))
--#15878 2020.09.21 MOD STT
--and ((sm.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
and ((uri.den_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
--#15878 2020.09.21 MOD END
and ((sh.hacyu_no = fnc_to_number(':hacyu_no')) or ('' = ':hacyu_no'))
and ((sm.hacyu_row_no = fnc_to_number(':hacyu_row_no')) or ('' = ':hacyu_row_no'))
and (('' = ':hin_cd') or (sm.hin_cd = ':hin_cd'))
--#26486 2022.12.20 MOD STT
--and ((sm.hin_nm like '%:hin_nm%') or (fnc_translate_case(hin.hin_kana) like '%'||fnc_translate_case('%:hin_nm')||'%') or ('' = ':hin_nm')) --TODO•i–Ό‰Ό–ΌŒŸυŽb’θ
and ((sm.hin_nm||COALESCE(' '||sm.hin_nm_ext,'') like '%:hin_nm%') or (fnc_translate_case(hin.hin_kana) like '%'||fnc_translate_case('%:hin_nm')||'%') or ('' = ':hin_nm')) --TODO•i–Ό‰Ό–ΌŒŸυŽb’θ
--#26486 2022.12.20 MOD END
:multi_cond_hin_nm
and (('' = ':eig_cd') or (sh.eig_cd = ':eig_cd'))
and ((h05.koumoku1 = ':dcat_cd') or ('' = ':dcat_cd'))
and (('' = ':kanri_no') or (sh.kanri_no LIKE '%:kanri_no%'))
--TODO
--and (('0' = ':sir-nyuko') or ('1' != ':cond_syori') or (COALESCE(um.syuko_flg,0) = 0))
--and (('0' = ':sir-nyuko') or ('2' != ':cond_syori') or (COALESCE(um.zaiko_flg,0) = 1))
and (('' = ':scat_limit') or (h05.koumoku9 = ':scat_limit'))
--’P‰ΏŒŸυ
and (('' = ':tanka_fr') or (sm.tanka >= fnc_to_number(':tanka_fr')))
and (('' = ':tanka_to') or (sm.tanka <= fnc_to_number(':tanka_to')))
--”—ΚŒŸυ
and (('' = ':suryo_fr') or (sm.suryo >= fnc_to_number(':suryo_fr')))
and (('' = ':suryo_to') or (sm.suryo <= fnc_to_number(':suryo_to')))

--# 2022.05.30 MOD STT
and (('' = ':input_ymd_fr') or (sh.input_ymd >= ':input_ymd_fr'))
and (('' = ':input_ymd_to') or (sh.input_ymd <= ':input_ymd_to'))
and ((hin.cat_cd = ':cat_cd') or ('' = ':cat_cd'))
and (('' = ':hosoku3') or (sm.hosoku3 like '%:hosoku3%'))

--# 2022.05.30 MOD STT
--order by sh.syori_ymd desc, sh.den_no desc
order by :order_by_sql sh.syori_ymd desc, sh.den_no desc
--TODO
limit 100000

[updateZaikoFlg]
select fnc_update_sir_dtl_zaiko(:s_id, :w_den_no, :w_row_no, 0, ':login_id')

[cancelZaikoFlg]
select fnc_update_sir_dtl_zaiko(:s_id, :w_den_no, :w_row_no, 1, ':login_id')