[load_grdList]
SELECT
  sh.den_no
, sh.syori_ymd
, 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
, sm.hin_nm
, sm.size_l
, sm.size_w
, sm.size_h
, sm.color_nm
, h05.koumoku1 as dcat_cd
, h14.han_name as dcat_nm
--, hin.cat_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
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')
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'))
and ((sm.hin_nm like '%:hin_nm%') or ('' = ':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 = ':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'))
order by sh.syori_ymd desc, sh.den_no desc
--TODO
limit 10000

[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')