[load_grdList]
select
  w.hin_cd
, w.hin_nm
, w.hin_rnm
, w.jan_cd
, w.brand_cd
, w.brand_nm
, w.brand_nm as brand_nm2
, w.htanka
, w.tanka
, w.stanka
, w.now_stock
, w.cat_cd
, w.cat_nm
, w.htanka_f2
, w.tanka_f2
, w.nyotei_su
, w.syotei_su
, COALESCE(w.now_stock,0) + COALESCE(w.nyotei_su,0) - COALESCE(w.syotei_su,0) as yotei_stock
, w.sort_key
, w.iri_su
, w.tani
, w.hanyo_nm
, w.sir_cd
, w.sir_nm
, w.hacyu_lot
--#20791 2021.09.22 MOD STT
, (case when(':hin_cd' != '' and w.hin_cd = ':hin_cd')then(0)else(9)end) as sort_rank
--#20791 2021.09.22 MOD END
--#24532 2022.06.30 MOD STT
, w.biko1
, w.biko2
--#24532 2022.06.30 MOD END
, w.hin_kana
from (
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
, m.stanka
, st.now_stock
, m.cat_cd
, h2.han_name as cat_nm
, m.htanka as htanka_f2
, m.tanka as tanka_f2
--comment1
, 0 as nyotei_su
--comment2
, 0 as syotei_su
, m.sort_key
, m.iri_su
, m.tani
, m.hanyo_nm
, m.sir_cd
, ms.sir_nm
, m.hacyu_lot
--#24532 2022.06.30 MOD STT
, m.biko1
, m.biko2
--#24532 2022.06.30 MOD END
, m.hin_kana
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
LEFT JOIN m_sir ms on (ms.sir_cd = m.sir_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)
LEFT JOIN m_sethin ohin on (ohin.hin_cd = m.hin_cd and row_no = 0)
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'nsjc-v8010')
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' = ''))
--#21468 2021.10.29 MOD STT
--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 (((':hin-srcmid' != '1') or (m.hin_cd LIKE '%:hin_cd%' or (COALESCE(h99a.han_name,'') != '1' and m.jan_cd LIKE '%:hin_cd%'))) or (':hin_cd' = ''))
AND (((':hin-srcmid' = '1') or (m.hin_cd LIKE ':hin_cd%' or (COALESCE(h99a.han_name,'') != '1' and (m.jan_cd LIKE ':hin_cd%' or m.jan_cd LIKE '%/ :hin_cd%' or m.jan_cd LIKE '%/:hin_cd%')))) or (':hin_cd' = ''))
--#21468 2021.10.29 MOD END
--AND ((m.hin_nm LIKE '%:hin_nm%' or m.hin_rnm LIKE '%:hin_nm%') or (':hin_nm' = ''))
AND ((fnc_translate_case(m.hanyo_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(m.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(m.hin_rnm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(m.hin_kana) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--2018.12.02 #5903 ADD
:multi_cond_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.ext_cat1_cd = ':ext_cat1_cd') or (':ext_cat1_cd' = ''))
AND ((m.ext_cat2_cd = ':ext_cat2_cd') or (':ext_cat2_cd' = ''))
AND ((m.ext_cat3_cd = ':ext_cat3_cd') or (':ext_cat3_cd' = ''))
AND ((m.ext_cat4_cd = ':ext_cat4_cd') or (':ext_cat4_cd' = ''))
AND ((m.price_kbn = ':price_kbn') or (':price_kbn' = ''))
AND ((m.hin_sts = ':hin_sts') or (':hin_sts' = ''))
: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 ((':inner_cond' != '0') or (m.inner_flg = 0))
AND ((':inner_cond' != '1') or (m.inner_flg = 1))
and m.del_flg = 0
--2019.06.10 #9034 ADD
AND ((':uri_hin_flg' = '') or (m.uri_hin_flg::text like '%:uri_hin_flg%'))
--2019.11.05 #11086 ADD
and (('' = ':sir_cd') or (m.sir_cd = ':sir_cd'))
--#23691 2022.05.26 MOD STT ToDo暫定処置
AND ((fnc_translate_case(ms.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(ms.sir_kana) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(m.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
--#23691 2022.05.26 MOD END 
:dhin_flg
--#24531 2022.06.30 MOD STT
AND ((m.biko1 LIKE '%:biko%') or (m.biko2 LIKE '%:biko%') or (':biko' = ''))
--#24531 2022.06.30 MOD END

) w
where ((':yotei_cond' != '1') or ((COALESCE(w.now_stock,0) + COALESCE(w.nyotei_su,0) - COALESCE(w.syotei_su,0)) < 0))
--order by brand_cd, hin_cd
--#20791 2021.09.22 MOD STT
--order by sort_key, brand_cd, hin_cd
order by sort_rank, sort_key, brand_cd, hin_cd
--#20791 2021.09.22 MOD END

[insertHin]
insert into m_hin (hin_cd, del_flg, cre_id, cre_time, upd_id, upd_time)
select
  ':w_hin_cd' --hin_cd
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
where not exists (
select 1 from m_hin
where hin_cd = ':w_hin_cd'
)

[updateHin]
update m_hin set
  hin_nm = ':w_hin_nm'
, cat_cd = ':w_cat_cd'
, brand_cd = ':w_brand_cd'
, tanka = :w_tanka
, stanka = :w_stanka
, upd_id = ':login_id'
, upd_time = now()
where hin_cd = ':w_hin_cd'

[updateHin2]
update m_hin set
  hin_nm = ':w_hin_nm'
,  htanka = :w_htanka
, stanka = :w_stanka
--, ext_cat1_cd = ':w_ext_cat1_cd'
, cat_cd = ':cat_cd'
, ext_cat2_cd = ':w_ext_cat2_cd'
, upd_id = ':login_id'
, upd_time = now()
where hin_cd = ':w_hin_cd'
;

[chkHin]
select
  hin_cd as chk_hin --hin_cd
from m_hin
where hin_cd = ':w_hin_cd'
;

[comment1]
, (
	select sum(COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0))
	from t_sir uh
	inner join t_sir_m um on (um.den_no = uh.den_no)
	WHERE 1 = 1
	and uh.hacyu_flg = 1
	and uh.del_flg = 0
	and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
	and um.hin_cd = m.hin_cd
	and ((':nyotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyotei_ymd'))
) as nyotei_su
[comment2]
,(
	select sum(w.syotei_su)
	from (
		--受注残
		select (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) as syotei_su
		from t_uri uh
		inner join t_uri_m um on (um.den_no = uh.den_no)
		WHERE 1 = 1
		and uh.jucyu_flg = 1
		and uh.del_flg = 0
		and (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) > 0
		and um.hin_cd = m.hin_cd
		and ((':syotei_ymd' = '') or (um.nonyu_ymd <= ':syotei_ymd'))
		--支給残
		union all
		select (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) * ms.set_su as syotei_su
		from t_sir uh
		inner join t_sir_m um on (um.den_no = uh.den_no)
		inner join m_sethin ms on (ms.hin_cd = um.hin_cd)
		WHERE 1 = 1
		and uh.hacyu_flg = 1
		and uh.del_flg = 0
		and (COALESCE(um.hacyu_su,0) - COALESCE(um.sir_su,0)) > 0
		and ms.chin_cd = m.hin_cd
		and ((':nyotei_ymd' = '') or (um.nyuka_yotei_ymd <= ':nyotei_ymd'))
		and COALESCE(m.non_stock_flg,0) = 0
	) w
) as syotei_su

[exportList]
SELECT
  m.hin_cd
, m.sethin_id
, 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 (((':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 ((fnc_translate_case(m.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(m.hin_rnm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
:multi_cond_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' = ''))
:oya-hin --親品番のみ
--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 ((':inner_cond' != '0') or (m.inner_flg = 0))
AND ((':inner_cond' != '1') or (m.inner_flg = 1))
and m.del_flg = 0
--and m.inner_flg = 0
order by brand_cd, hin_cd

[exportList_komori]
SELECT
  m.hin_cd
, m.hin_nm
--, shin.tanka
--, sir.sir_nm
, m.jan_cd
, m.brand_cd
, h1.han_name as brand_nm
, m.htanka
, COALESCE(st.now_stock,0) + (case when('' = '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
, chin.hin_nm as chin_nm
, chin.cat_cd
, csir.sir_cd as csir_cd, csir.sir_nm as csir_nm
, h3.han_name as cat_nm
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_cd)
--LEFT JOIN m_sirhin shin on shin.hin_cd = m.hin_cd
--LEFT JOIN m_sir sir on shin.sir_cd = sir.sir_cd
LEFT JOIN m_sethin sethin on sethin.hin_cd = m.hin_cd
LEFT JOIN m_hin chin on sethin.chin_cd = chin.hin_cd
LEFT JOIN m_sirhin cshin on chin.hin_cd = cshin.hin_cd
LEFT JOIN m_sir csir on cshin.sir_cd = csir.sir_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)
LEFT JOIN m_hanyo h3 on (h2.mst_kbn = '05' and h2.han_cd = chin.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 (((':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 ((fnc_translate_case(m.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(m.hin_rnm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
:multi_cond_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' = ''))
:oya-hin --親品番のみ
--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 ((':inner_cond' != '0') or (m.inner_flg = 0))
AND ((':inner_cond' != '1') or (m.inner_flg = 1))
and m.del_flg = 0
--and m.inner_flg = 0
--order by brand_cd, hin_cd
order by chin.cat_cd

[deleteHacyuWork]
delete from w_basv0220_load_m where s_id = :s_id

[insertHacyuWork]
insert into w_basv0220_load_m (s_id, row_no, hin_cd, hin_nm, suryo, hacyu_su, del_flg, cre_id, cre_time, upd_id, upd_time)
values (:s_id, 0, ':hin_cd', null, null, :hacyu_su, 0, ':login_id', now(), ':login_id', now())


[updateSethin]
select hin_cd, fnc_create_pattern_sethin(hin_cd, ':login_id')
from m_hin
where sethin_id = ':w_sethin_id'
order by hin_cd

[insertSethinPtn]
delete from m_sethin_ptn_comp
where sethin_id = ':w_sethin_id'
;
delete from m_sethin_ptn
where sethin_id = ':w_sethin_id'
;
insert into m_sethin_ptn (
  sethin_id
, sethin_nm
, biko1
, biko2
, set_iri_su1
, set_iri_su2
, set_iri_su
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  ':w_sethin_id'
, ':w_sethin_nm'
, ':w_biko1'
, null --biko2
, :w_set_iri_su1
, :w_set_iri_su2
, :w_set_iri_su
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
);

[insertSethinPtnComp]
insert into m_sethin_ptn_comp (
  sethin_id
, row_no
, org_hin_flg
, hin_nm
, chin_nm
, color_cd
, set_su
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  ':w_sethin_id'
, :w_row_no
, ':w_org_hin_flg'
, ':w_hin_nm'
, ':w_chin_nm'
, ':w_color_cd'
, :w_set_su
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
);


[export_sethin]
select
  w.hin_cd
, w.chin_cd
, w.set_su
, w.hin_nm
, w.chin_nm
from (
SELECT
  seth.hin_cd
, seth.chin_cd
, seth.set_su
, m.hin_nm
, mc.hin_nm as chin_nm
FROM m_hin m
LEFT JOIN m_stock st on (st.hin_cd = m.hin_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)
LEFT JOIN m_sethin ohin on (ohin.hin_cd = m.hin_cd and row_no = 0)
INNER JOIN m_sethin seth on (seth.hin_cd = m.hin_cd) 
LEFT JOIN m_hin mc on (mc.hin_cd = seth.chin_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 (((':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.hin_nm LIKE '%:hin_nm%' or m.hin_rnm LIKE '%:hin_nm%') or (':hin_nm' = ''))
AND ((fnc_translate_case(m.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--2018.12.02 #5903 ADD
:multi_cond_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' = ''))
: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 ((':inner_cond' != '0') or (m.inner_flg = 0))
AND ((':inner_cond' != '1') or (m.inner_flg = 1))
and m.del_flg = 0
) w
--where ((':yotei_cond' != '1') or ((COALESCE(w.now_stock,0) + COALESCE(w.nyotei_su,0) - COALESCE(w.syotei_su,0)) < 0))
order by hin_cd