[load_grdList]
SELECT
  m.hin_cd
, m.hin_nm
, m.hin_rnm
, m.jan_cd
, m.brand_cd
, h2.han_name as brand_nm
, h2.koumoku8 as brand_biko
, h2.koumoku8
, m.stanka
, m.htanka
, m.tanka
, st.now_stock
, m.cat_cd
, h1.han_name as cat_nm
, h3.han_name as hin_sts_nm
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
INNER JOIN m_hanyo h1 on (h1.mst_kbn = '14' and h1.han_cd = m.cat_cd)
INNER JOIN m_hanyo h2 on (h2.mst_kbn = '06' and h2.han_cd = m.brand_cd)
INNER JOIN m_hanyo h3 on (h3.mst_kbn = '05' and h3.han_cd = m.hin_sts)
--5 6桁
--6 9桁
--14 3桁
LEFT JOIN m_sethin ohin on (ohin.hin_cd = m.hin_cd and row_no = 0)
WHERE 1 = 1

AND (((':hin-srcmid' != '1') or (m.hin_cd LIKE '%:hin_cd%' or m.jan_cd LIKE '%:hin_cd%')) or (':hin_cd' = ''))
--AND (((':hin-srcmid' = '1') or (m.hin_cd LIKE ':hin_cd%' or m.jan_cd LIKE ':hin_cd%' or m.jan_cd LIKE '%:hin_cd%' or m.jan_cd LIKE '%:hin_cd%')) or (':hin_cd' = ''))

--AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
--AND ((m.hin_sts = ':cat_cd:hin_sts') or (':hin_sts' = ''))
--AND ((m.brand_cd = ':cat_cd:hin_sts:brand_cd') or (':brand_cd' = ''))

AND ((fnc_translate_case(m.hin_nm) LIKE '%'||fnc_translate_case(':hosoku2')||'%') 
or  (fnc_translate_case(m.hin_rnm) LIKE '%'||fnc_translate_case(':hosoku2')||'%') 
or  (':hosoku2' = ''))
--『,.対応』
--AND (upper(replace(replace(m.hin_nm,' ',''),'-','')) LIKE upper(replace(replace('%:hosoku2%',' ',''),'-',''))
--	or (replace(replace(m.hin_rnm,' ',''),'-','') LIKE upper(replace(replace('%:hosoku2%',' ',''),'-','')))
--	or (':hosoku2' = ''))	
--『,.対応』
--AND (upper(replace(replace(replace(replace(m.hin_nm,' ',''),'-',''),',',''),'.','')) LIKE upper(replace(replace(replace(replace('%:hosoku2%',' ',''),'-',''),',',''),'.',''))
--	or (replace(replace(replace(replace(m.hin_rnm,' ',''),'-',''),',',''),'.','') LIKE upper(replace(replace(replace(replace('%:hosoku2%',' ',''),'-',''),',',''),'.','')))
--	or (':hosoku2' = ''))
AND (fnc_translate_case(h3.han_name) LIKE '%'||fnc_translate_case(':hin_nm')||'%' 
or  (':hin_nm' = ''))
--『,.対応』
--AND (upper(replace(replace(h3.han_name,' ',''),'-','')) like upper(replace(replace('%:hin_nm%',' ',''),'-',''))
--	or replace(replace(':hin_nm',' ',''),'-','') = '')
--『,.対応』
--AND (upper(replace(replace(replace(replace(h3.han_name,' ',''),'-',''),',',''),'.','')) like upper(replace(replace(replace(replace('%:hin_nm%',' ',''),'-',''),',',''),'.',''))
--	or replace(replace(replace(replace(':hin_nm',' ',''),'-',''),',',''),'.','') = '')
AND (fnc_translate_case(h2.han_name) LIKE '%'||fnc_translate_case(':hosoku1')||'%' 
or  (':hosoku1' = ''))
--『,.対応』
--AND (upper(replace(replace(h2.han_name,' ',''),'-','')) like upper(replace(replace('%:hosoku1%',' ',''),'-',''))
--	or replace(replace(':hosoku1',' ',''),'-','') = '')
--『,.対応』
--AND (upper(replace(replace(replace(replace(h2.han_name,' ',''),'-','')',',''),'.','')) like upper(replace(replace(replace(replace('%:hosoku1%',' ',''),'-','')',',''),'.',''))
--	or replace(replace(replace(replace(':hosoku1',' ',''),'-','')',',''),'.','') = '')
AND (fnc_translate_case(h2.koumoku8) LIKE '%'||fnc_translate_case(':biko')||'%' 
or  (':biko' = ''))
--『,.対応』
--AND (upper(replace(replace(h2.koumoku8,' ',''),'-','')) like upper(replace(replace('%:biko%',' ',''),'-',''))
--	or replace(replace(':biko',' ',''),'-','') = '')
--『,.対応』
--AND (upper(replace(replace(replace(replace(h2.koumoku8,' ',''),'-',''),',',''),'.','')) like upper(replace(replace(replace(replace('%:biko%',' ',''),'-',''),',',''),'.',''))
--	or (replace(replace(replace(replace(':biko',' ',''),'-',''),',',''),'.','') = '')
AND (fnc_translate_case(h1.han_name) LIKE '%'||fnc_translate_case(':dcat_nm')||'%' 
or  (':dcat_nm' = ''))
--『,.対応』
--AND (upper(replace(replace(h1.han_name,' ',''),'-','')) like upper(replace(replace('%:dcat_nm%',' ',''),'-',''))
--	or replace(replace(':dcat_nm',' ',''),'-','') = '')
--『,.対応』
--AND (upper(replace(replace(replace(replace(h1.han_name,' ',''),'-',''),',',''),'.','')) like upper(replace(replace(replace(replace('%:dcat_nm%',' ',''),'-',''),',',''),'.',''))
--	or replace(replace(replace(replace(':dcat_nm',' ',''),'-',''),',',''),'.','') = '')

AND ((m.price_kbn = ':price_kbn') or (':price_kbn' = ''))
:oya-hin --親品番のみ
AND ((':st_cond' != '1') or (st.now_stock > 0))
AND ((':stop_cond' != '0') or (m.stop_flg = 0))
AND ((':stop_cond' != '1') or (m.stop_flg = 1))
and m.del_flg = 0
--or (m.del_flg = 0 AND ':meisai_biko' != '' and h1.han_name like '%:meisai_biko%' or h3.han_name like '%:meisai_biko%' )
order by brand_cd, hin_cd


[exportList]
SELECT
  m.hin_cd
, m.hin_nm
, m.hin_rnm
, m.jan_cd
, m.brand_cd
, h1.han_name as brand_nm
, m.htanka
, m.tanka
, COALESCE(st.now_stock,0) + (case when(':hin-renkei' = '1')then(COALESCE(other_stock,0))else(0)end) as now_stock
, m.htanka + fnc_get_zei(m.htanka,to_char(now(),'yyyymmdd')) as htanka_komi
, vs.stanka1
, vs.stanka2
, vs.stanka3
, vs.stanka4
, vs.stanka5
, vu.utanka1
, vu.utanka2
, vu.utanka3
, vu.utanka4
, vu.utanka5
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
left join v_hin_stanka_rireki vs on (vs.hin_cd = m.hin_cd)
left join v_hin_utanka_rireki vu on (vu.hin_cd = m.hin_cd and vu.tok_cd = ':tok_cd')
LEFT JOIN m_hanyo h1 on (h1.mst_kbn = '06' and h1.han_cd = m.brand_cd)
LEFT JOIN m_hanyo h2 on (h2.mst_kbn = '05' and h2.han_cd = m.cat_cd)
WHERE 1 = 1
--AND ((m.jan_cd LIKE '%:jan_cd' or m.jan_cd LIKE '%:jan_cd /%' or m.jan_cd LIKE '%:jan_cd/%') or (':jan_cd' = ''))
AND ((m.jan_cd LIKE '%:jan_cd%') or (':jan_cd' = ''))
--AND ((m.hin_cd LIKE '%:hin_cd' or m.jan_cd LIKE '%:hin_cd' or m.jan_cd LIKE '%:hin_cd /%' or m.jan_cd LIKE '%:hin_cd/%') or (':hin_cd' = ''))
AND ((m.hin_cd LIKE '%:hin_cd%' or m.jan_cd LIKE '%:hin_cd%') or (':hin_cd' = ''))
AND ((m.hin_nm LIKE '%:hin_nm%' or m.hin_rnm LIKE '%:hin_nm%') or (':hin_nm' = ''))
AND ((m.brand_cd = ':brand_cd') or (':brand_cd' = ''))
AND ((h2.han_cd is not null and h2.koumoku1 = ':dcat_cd') or (':dcat_cd' = ''))
AND ((m.cat_cd = ':cat_cd') or (':cat_cd' = ''))
AND ((m.price_kbn = ':price_kbn') or (':price_kbn' = ''))
AND ((m.hin_sts = ':hin_sts') or (':hin_sts' = ''))
--AND ((':st_cond' != '1') or (st.now_stock > 0))
AND ((':st_cond' != '1') or ((COALESCE(st.now_stock,0) + (case when(':hin-renkei' = '1')then(COALESCE(other_stock,0))else(0)end)) > 0))
AND ((':stop_cond' != '0') or (m.stop_flg = 0))
AND ((':stop_cond' != '1') or (m.stop_flg = 1))
and m.del_flg = 0
and m.inner_flg = 0
order by brand_cd, hin_cd