[load_grdList]
select
  um.den_no
, uh.group_no
, uh.syori_ymd
, uh.tan_cd
, tan.bu_cd
, h55.han_name as bu_nm
, uh.tok_cd
, uh.tok_nm
, uh.tok_tan
, tk.cat_cd as t_cat_cd
, h35.han_name as t_cat_nm
, uh.zeinuki_gaku as h_zeinuki_gaku
, uh.zei_gaku
, uh.zeikomi_gaku
, uh.kazei_gaku as h_kazei_gaku
, uh.soto_gaku
, uh.uchi_gaku as h_uchi_gaku
, uh.genka_gaku as h_genka_gaku
--#19840 2021.07.07 MOD STT
--, uh.jucyu_no
, um.jucyu_no
--#19840 2021.07.07 MOD END
, uh.nonyu_no
, uh.nonyu_nm
, uh.nonyu_zip_no
, uh.nonyu_addr1
, uh.nonyu_addr2
, uh.nonyu_addr3
, uh.nonyu_addr4
, uh.nonyu_tel_no
, uh.nonyu_fax_no
, uh.nonyu_tan
, um.row_no
, um.hin_cd
, um.hin_nm
, um.size_l
, um.size_w
, um.size_h
, um.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
, um.suryo
, um.tanka
, um.tanka as upd_tanka
, (case when(um.suryo = 0)then(null)else(round(um.genka_gaku / um.suryo,2))end) as gentanka
, um.kingaku
, um.genka_gaku
, um.kazei_gaku
, um.zeinuki_gaku
, um.uchi_gaku
, um.tok_hacyu_no
, um.nonyu_ymd
, (coalesce(um.kingaku,0) - coalesce(um.genka_gaku,0)) as arari
, um.meisai_biko
, um.meisai_biko2
, um.hosoku1
, uh.keshi_flg as keshi_flg
, (case when(uh.keshi_flg = 1)then(to_char(to_date(uh.keshi_ymd,'yyyymmdd'),'mm/dd'))else(null)end) as nyukin_md
, um.hosoku2
, um.zaiko_flg
, um.syuko_flg
, uh.syukka_req_no
, uh.unsobin
, h49.han_name as unsobin_nm
, uh.syukka_no
, jm.rel_row_no as jucyu_rel_row_no
, uh.den_kbn
, h54.han_name as den_kbn_nm
, tk.tori_kbn
, h08.han_name as tori_kbn_nm
, uh.biko1
, uh.biko2
, um.tani_suryo
, um.bante
, um.kei_suryo
--#20669 2021.09.11 MOD STT
, um.htanka
, tk.tan_cd as tok_tan_cd
, mtan.tan_nm as tok_tan_nm
--#20669 2021.09.11 MOD END

from (

select
  um.den_no
, um.row_no
, um.hin_cd
, um.hin_nm||COALESCE(' '||um.hin_nm_ext,'') as hin_nm
, um.size_l
, um.size_w
, um.size_h
, um.suryo
, um.tanka
, um.kingaku
, um.genka_gaku
, um.kazei_gaku
, um.zeinuki_gaku
, um.uchi_gaku
, um.tok_hacyu_no
, um.nonyu_ymd
, um.meisai_biko
, um.meisai_biko2
, um.hosoku1
, COALESCE(um.hosoku2,'')||' '||COALESCE(um.hosoku3,'')||' '||COALESCE(um.hosoku4,'') as hosoku2
, um.color_nm
, um.zaiko_flg
, um.syuko_flg
--#19840 2021.07.07 MOD STT
, um.jucyu_no
--#19840 2021.07.07 MOD END
, um.jucyu_row_no
, um.tani_suryo
, um.bante
, um.kei_suryo
--#20669 2021.09.11 MOD STT
, um.htanka
--#20669 2021.09.11 MOD END

from t_uri uh
inner join t_uri_m um on (um.den_no = uh.den_no)
left join m_tokui tk on (uh.tok_cd = tk.tok_cd)
left join m_hin mh on (mh.hin_cd = um.hin_cd)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = mh.cat_cd)
WHERE 1 = 1
and uh.del_flg = 0
and uh.jucyu_flg = 0
and COALESCE(uh.mitsumori_flg,0) = 0
and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no') or ('1' = ':seisan_grp'))
and ((uh.group_no = fnc_to_number(':den_no')) or ('' = ':den_no') or ('0' = ':seisan_grp'))
--#19840 2021.07.07 MOD STT
--and ((uh.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
and ((um.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
--#19840 2021.07.07 MOD END
and ((uh.syukka_req_no = fnc_to_number(':syukka_req_no')) or ('' = ':syukka_req_no'))
and ((um.hin_cd like '%:hin_cd%') or ('' = ':hin_cd')) --品番
and (((um.hin_nm||COALESCE(' '||um.hin_nm_ext,'')) like '%:hin_nm%') or ('' = ':hin_nm')) --品名
--#20193 2021.08.05 MOD STT
:multi_cond_hin_nm
--#20193 2021.08.05 MOD END
and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
and (('' = ':nonyu_ymd_fr') or (um.nonyu_ymd >= ':nonyu_ymd_fr'))
and (('' = ':nonyu_ymd_to') or (um.nonyu_ymd <= ':nonyu_ymd_to'))
and (('' = ':tok_cd') or (uh.tok_cd = ':tok_cd'))
AND ((fnc_translate_case(tk.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(tk.tok_kana) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(uh.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (':tok_nm' = ''))
and ((uh.biko1 like '%:biko1%' or uh.biko2 like '%:biko1%' or um.meisai_biko like '%:biko1%' or um.meisai_biko2 like '%:biko1%') or ('' = ':biko1'))
and ((um.hosoku1 like '%:hosoku1%') or ((COALESCE(um.hosoku2,'')||' '||COALESCE(um.hosoku3,'')||' '||COALESCE(um.hosoku4,'')) like '%:hosoku1%') or ('' = ':hosoku1'))
and (('0' = ':seisan_grp') or (uh.shiji_no is null))
and (('' = ':eig_cd') or (uh.eig_cd = ':eig_cd'))
and (('' = ':den_kbn') or (uh.den_kbn = ':den_kbn'))
and (('' = ':tan_cd') or (uh.tan_cd = ':tan_cd'))
and (('' = ':eigyou_tan_cd') or (uh.eigyou_tan_cd = ':eigyou_tan_cd'))
and (('' = ':tok_cat_cd') or (tk.cat_cd = ':tok_cat_cd'))
and (('' = ':unsobin') or (uh.unsobin = ':unsobin'))
and (('' = ':tori_kbn') or (tk.tori_kbn = ':tori_kbn'))
and (('' = ':tok_tan_cd') or (tk.tan_cd = ':tok_tan_cd'))
and ((h05.koumoku1 = ':dcat_cd') or ('' = ':dcat_cd'))
and ((mh.cat_cd = ':cat_cd') or ('' = ':cat_cd'))
--and (('0' = ':uri-syuko') or ('1' != ':cond_syori') or (COALESCE(um.zaiko_flg,0) = 0 and COALESCE(um.syuko_flg,0) = 0))
--and (('0' = ':uri-syuko') or ('2' != ':cond_syori') or (COALESCE(um.zaiko_flg,0) = 1 and COALESCE(um.syuko_flg,0) = 0))
and (('0' = ':uri-syuko') or ('1' != ':cond_syori') or (COALESCE(um.syuko_flg,0) = 0))
and (('0' = ':uri-syuko') or ('2' != ':cond_syori') or (COALESCE(um.zaiko_flg,0) = 1))

--生産グループ化のもの
union all
select
  uh.den_no
, -1 as row_no
, min(mh.dhin_cd) as hin_cd
, min(mhd.hin_nm) as hin_nm
, min(um.size_l) as size_l
, min(um.size_w) as size_w
, min(um.size_h) as size_h
, sum(um.suryo) as suryo
, min(um.tanka) as tanka
, sum(um.kingaku) as kingaku
, sum(um.genka_gaku) as genka_gaku
, sum(um.kazei_gaku) as kazei_gaku
, sum(um.zeinuki_gaku) as zeinuki_gaku
, sum(um.uchi_gaku) as uchi_gaku
, min(um.tok_hacyu_no) as tok_hacyu_no
, min(um.nonyu_ymd) as nonyu_ymd
, min(um.meisai_biko) as meisai_biko
, min(um.meisai_biko2) as meisai_biko2
, min(um.hosoku1) as hosoku1
, min(COALESCE(um.hosoku2,'')||' '||COALESCE(um.hosoku3,'')||' '||COALESCE(um.hosoku4,'')) as hosoku2
, min(um.color_nm) as color_nm
, max(um.zaiko_flg) as zaiko_flg
, max(um.syuko_flg) as syuko_flg
--#19840 2021.07.07 MOD STT
, min(uh.jucyu_no) as jucyu_no
--#19840 2021.07.07 MOD END
, cast(null as numeric(10)) as jucyu_row_no
, sum(um.tani_suryo) as tani_suryo
, min(um.bante) as bante
, sum(um.kei_suryo) as kei_suryo
--#20669 2021.09.11 MOD STT
, min(um.htanka) as htanka
--#20669 2021.09.11 MOD END

from t_uri uh
inner join t_uri_m um on (um.den_no = uh.den_no)
left join m_tokui tk on (uh.tok_cd = tk.tok_cd)
left join m_hin mh on (mh.hin_cd = um.hin_cd)
left join m_hin mhd on (mhd.hin_cd = mh.dhin_cd)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = mh.cat_cd)
WHERE 1 = 1
and uh.del_flg = 0
and uh.jucyu_flg = 0
and COALESCE(uh.mitsumori_flg,0) = 0
and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no') or ('1' = ':seisan_grp'))
and ((uh.group_no = fnc_to_number(':den_no')) or ('' = ':den_no') or ('0' = ':seisan_grp'))
and ((uh.jucyu_no = fnc_to_number(':jucyu_no')) or ('' = ':jucyu_no'))
and ((uh.syukka_req_no = fnc_to_number(':syukka_req_no')) or ('' = ':syukka_req_no'))
and ((um.hin_cd like '%:hin_cd%') or ('' = ':hin_cd')) --品番
and ((um.hin_nm like '%:hin_nm%') or ('' = ':hin_nm')) --品名
--#20193 2021.08.05 MOD STT
:multi_cond_hin_nm
--#20193 2021.08.05 MOD END
and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
and (('' = ':nonyu_ymd_fr') or (um.nonyu_ymd >= ':nonyu_ymd_fr'))
and (('' = ':nonyu_ymd_to') or (um.nonyu_ymd <= ':nonyu_ymd_to'))
and (('' = ':tok_cd') or (uh.tok_cd = ':tok_cd'))
AND ((fnc_translate_case(tk.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(tk.tok_kana) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (fnc_translate_case(uh.tok_nm) LIKE '%'||fnc_translate_case(':tok_nm')||'%') or (':tok_nm' = ''))
and ((uh.biko1 like '%:biko1%' or uh.biko2 like '%:biko1%' or um.meisai_biko like '%:biko1%' or um.meisai_biko2 like '%:biko1%') or ('' = ':biko1'))
and ((um.hosoku1 like '%:hosoku1%') or ((COALESCE(um.hosoku2,'')||' '||COALESCE(um.hosoku3,'')||' '||COALESCE(um.hosoku4,'')) like '%:hosoku1%') or ('' = ':hosoku1'))
and (('1' = ':seisan_grp') and (uh.shiji_no is not null))
and (('' = ':eig_cd') or (uh.eig_cd = ':eig_cd'))
and (('' = ':den_kbn') or (uh.den_kbn = ':den_kbn'))
and (('' = ':tan_cd') or (uh.tan_cd = ':tan_cd'))
and (('' = ':eigyou_tan_cd') or (uh.eigyou_tan_cd = ':eigyou_tan_cd'))
and (('' = ':tok_cat_cd') or (tk.cat_cd = ':tok_cat_cd'))
and (('' = ':unsobin') or (uh.unsobin = ':unsobin'))
and (('' = ':tori_kbn') or (tk.tori_kbn = ':tori_kbn'))
and (('' = ':tok_tan_cd') or (tk.tan_cd = ':tok_tan_cd'))
and ((h05.koumoku1 = ':dcat_cd') or ('' = ':dcat_cd'))
and ((mh.cat_cd = ':cat_cd') or ('' = ':cat_cd'))

group by uh.den_no

) um
inner join t_uri uh on (uh.den_no = um.den_no)
left join m_tokui tk on (uh.tok_cd = tk.tok_cd)
left join m_hin hin on (hin.hin_cd = um.hin_cd)
left join m_tanto tan on (tan.tan_cd = uh.tan_cd)
left join m_tanto mtan on (mtan.tan_cd = tk.tan_cd)
left join m_hanyo h55 on (h55.han_cd = tan.bu_cd and h55.mst_kbn = '55')
left join m_hanyo h35 on (h35.han_cd = tk.cat_cd and h35.mst_kbn = '35')
left join m_hanyo h05 on (h05.han_cd = hin.cat_cd and h05.mst_kbn = '05')
left join m_hanyo h14 on (h14.han_cd = h05.koumoku1 and h14.mst_kbn = '14')
left join m_hanyo h06 on (h06.han_cd = hin.brand_cd and h06.mst_kbn = '06')
left join m_hanyo h49 on (h49.mst_kbn = '49' and h49.han_cd = uh.unsobin)
--#19840 2021.07.07 MOD STT
--left join t_uri_m jm on (jm.den_no = uh.jucyu_no and jm.row_no = um.jucyu_row_no)
left join t_uri_m jm on (jm.den_no = um.jucyu_no and jm.row_no = um.jucyu_row_no)
--#19840 2021.07.07 MOD END
left join m_hanyo h54 on (h54.mst_kbn = '54' and h54.han_cd = uh.den_kbn)
left join m_hanyo h08 on (h08.mst_kbn = '08' and h08.han_cd = tk.tori_kbn)

where 1 = 1



order by uh.syori_ymd desc, um.den_no desc, um.row_no
--TODO 2019.01.23
limit 10000




[delete_w0110]
delete from w_basr0110
where s_id = :s_id
;

[insert_w0110]
insert into w_basr0110
(s_id, den_no)
SELECT 
 :s_id
 ,um.den_no
from t_uri uh
inner join t_uri_m um on (um.den_no = uh.den_no)
left join m_tokui tk on (uh.tok_cd = tk.tok_cd)
WHERE 1 = 1
and uh.jucyu_flg = 1
and ((uh.den_no = fnc_to_number(':den_no')) or ('' = ':den_no'))
and (('' = ':syori_ymd_fr') or (uh.syori_ymd >= ':syori_ymd_fr'))
and (('' = ':syori_ymd_to') or (uh.syori_ymd <= ':syori_ymd_to'))
and (('' = ':nonyu_ymd_fr') or (um.nonyu_ymd >= ':nonyu_ymd_fr'))
and (('' = ':nonyu_ymd_to') or (um.nonyu_ymd <= ':nonyu_ymd_to'))
and (('' = ':tok_cd') or (uh.tok_cd = ':tok_cd'))
and ((uh.tok_nm like '%:tok_nm%') or ('' = ':tok_nm'))
and ((uh.biko1 like '%:biko1%' or uh.biko2 like '%:biko1%') or ('' = ':biko1'))
and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
order by um.nonyu_ymd, uh.syori_ymd, um.den_no, um.row_no
;

[getNyukin]
select k.nyukin_no as w_nyukin_no
from t_uri uh
left join t_nyu_keshi k on (k.keshi_kind = 1 and k.uri_no = uh.den_no)
where uh.den_no = :w_upd_key_value
and uh.zeikomi_gaku = (select sum(nk.nyukin_gaku) from t_nyu_keshi nk where nk.keshi_kind = 1 and nk.uri_no = uh.den_no)
order by k.nyukin_no desc

[updateTanka]
select fnc_update_basv0450_tanka(:s_id, :w_den_no, :w_row_no, :w_upd_tanka, ':login_id')

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

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

[updateSyukoFlg]
select fnc_update_uri_dtl_syuko(:s_id, :w_den_no, :w_row_no, 0, ':login_id')

[cancelSyukoFlg]
select fnc_update_uri_dtl_syuko(:s_id, :w_den_no, :w_row_no, 1, ':login_id')

[changeUnsobin]
update t_uri set
  unsobin = ':w_unsobin'
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_den_no

[changeSyukkaNo]
update t_uri set
  syukka_no = ':w_syukka_no'
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_den_no