[load_grdList]
select
--明細単位
  cast(0 as int) as sel_flg
, cast('1' as varchar(1)) as kbn
, um.den_no
, um.row_no
, um.hin_cd
, um.hin_nm||(case when(h99b.han_name = '1')then('')else(COALESCE(' '||um.hin_nm_ext,''))end) as hin_nm
, cast('' as varchar(255)) as hin_nm_row1
, COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) as jucyu_zan
, COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) as suryo
, um.jucyu_su
, um.gentanka
--#24959 2022.07.23 MOD STT
--, um.tanka
, (case when(uh.gaika_rate > 0)then(round(uh.gaika_rate * um.tanka,0))else(um.tanka)end) as tanka
, (case when(uh.gaika_rate > 0)then(um.tanka)else(null)end) as gaika_tanka
--#24959 2022.07.23 MOD END
, cast(null as numeric(10)) as kingaku
, um.zeinuki_gaku
--, um.kingaku
, uh.syori_ymd
, um.nonyu_ymd
, uh.tok_cd
, uh.tok_nm
, uh.nonyu_nm||' '||COALESCE(uh.nonyu_tan) as nonyu_nm
--#20497 2021.08.27 MOD STT
--, si.sir_cd --sir
--, si.sir_nm --sir
--, cast(null as varchar(16)) as sir_cd --sir
--, cast(null as varchar(50)) as sir_nm --sir
, (case when(um.sir_cd != '')then(um.sir_cd)else(mh.sir_cd)end) as sir_cd
, (case when(um.sir_cd != '')then(um.sir_nm)else(ms.sir_nm)end) as sir_nm
--#20497 2021.08.27 MOD END
, um.hosoku1
, uh.biko1
, uh.biko2
, um.tok_hacyu_no
, sh.shiji_no
, (case when(sh.sekkei_flg = 1)then('済')else(to_char(sh.sekkei_yotei_end_time,'mm/dd'))end) as sekkei_end_md
, uh.keshi_flg
, (case when(uh.keshi_flg = 1)then(to_char(to_date(uh.keshi_ymd,'yyyymmdd'),'mm/dd'))else('')end) as nyukin_md
--#24959 2022.07.23 MOD STT
--, um.tanka as tanka_f2
, (case when(uh.gaika_rate > 0)then(round(uh.gaika_rate * um.tanka,0))else(um.tanka)end) as tanka_f2
--#24959 2022.07.23 MOD END
, uh.prc_sts
, h1.han_name as prc_sts_nm
--#20497 2021.08.27 MOD STT
--酒井商会の仕様（このままリリースできない）
--, si.den_no as hacyu_no
, cast(null as int) as hacyu_no
--#20497 2021.08.27 MOD END
, round(extract(epoch from now() - uh.cre_time) / 60 / 60) as regist_i
, case mt.tan_rnm when '' then mt.tan_nm
	else mt.tan_rnm
	end as ttan
, h3.han_name as cat_nm
, um.meisai_biko
, um.meisai_biko2
, cast(null as numeric(9,2)) as shiji_zan
, cast(null as numeric(9,2)) as syukka_zan
, st.now_stock
, uh.syotei_ymd
, uh.kanri_no
--#24959 2022.07.23 MOD STT
--, um.kingaku as m_kingaku
, (case when(uh.gaika_rate > 0)then(um.jucyu_su * round(uh.gaika_rate * um.tanka,0))else(um.kingaku)end) as m_kingaku
--#24959 2022.07.23 MOD END
, uh.den_kbn
, h54.han_name as den_kbn_nm
, um.rel_row_no
, um.price_rank
, uh.kobetsu_kbn
, h0d.han_name as kobetsu_kbn_nm
--#21978 2021.12.07 MOD STT
--受注残でなく受注数の方を諸元にする
--, mh.saisu * (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) as saisu
--, mh.weight * (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) as weight
, mh.saisu * um.jucyu_su as saisu
, mh.weight * um.jucyu_su as weight
--#21978 2021.12.07 MOD END
, COALESCE(uh.nonyu_addr1,'')||COALESCE(uh.nonyu_addr2,'')||COALESCE(uh.nonyu_addr3,'')||COALESCE(uh.nonyu_addr4,'') as nonyu_addr
, uh.base_den_no
, (select sum(COALESCE(sm.hacyu_su,0) - COALESCE(sm.sir_su,0)) from t_sir_m sm where sm.jucyu_no = um.den_no and sm.jucyu_row_no = um.row_no and sm.del_flg = 0 and sm.hacyu_su != 0) as hacyu_zan
, h0c.koumoku1 as gaika_nm
, uh.gaika_rate
, (case when(uh.gaika_rate > 0)then(um.jucyu_su * round(uh.gaika_rate * um.tanka,0))else(um.kingaku)end) as m_kingaku_jpn
, um.hosoku3
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_stock st on (st.hin_cd = um.hin_cd)
left join t_seizou sh on (sh.jucyu_no = um.den_no and sh.jucyu_row_no = um.row_no and sh.del_flg = 0)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uh.prc_sts)
--#20497 2021.08.27 MOD STT
--left join t_sir_m sm on (um.den_no = sm.jucyu_no and um.row_no = sm.jucyu_row_no and sm.hacyu_no is null)
--left join t_sir si on (sm.den_no = si.den_no and si.hacyu_flg = 1)
left join m_sir ms on (ms.sir_cd = mh.sir_cd)
--#20497 2021.08.27 MOD END
left join m_tanto mt on (uh.tan_cd = mt.tan_cd)
left join m_hanyo h3 on (h3.mst_kbn = '35' and h3.han_cd = tk.cat_cd)
left join m_hanyo h4 on (h4.mst_kbn = '90' and h4.han_cd = 'hin-title')
left join m_hanyo h54 on (h54.mst_kbn = '54' and h54.han_cd = uh.den_kbn)
left join m_hanyo h0c on (h0c.mst_kbn = '0C' and h0c.han_cd = uh.gaika_cd)
left join m_hanyo h0d on (h0d.mst_kbn = '0D' and h0d.han_cd = uh.kobetsu_kbn)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = mh.cat_cd)
--#25052 2022.07.29 MOD STT
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'pzan-v0430')
--#25052 2022.07.29 MOD END
left join m_hanyo h99b on (h99b.mst_kbn = '99' and h99b.han_cd = 'nnmx-v0430')
WHERE 1 = 1
and uh.jucyu_flg = 1
and uh.del_flg = 0
--#26485 2022.12.20 MOD STT
--and um.hin_cd <> COALESCE(h4.koumoku1,'')
and ((COALESCE(h4.koumoku1,'') = '') or (COALESCE(um.hin_cd,'') != COALESCE(h4.koumoku1,'')))
--#26485 2022.12.20 MOD END
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 (('' = ':syotei_ymd_fr') or (uh.syotei_ymd >= ':syotei_ymd_fr'))
and (('' = ':syotei_ymd_to') or (uh.syotei_ymd <= ':syotei_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' = ''))
--#24614 2022.06.29 MOD STT
and (('' = ':sir_cd') or (mh.sir_cd = ':sir_cd') or (um.sir_cd = ':sir_cd'))
and (('' = ':sir_nm') or (fnc_translate_case(ms.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(um.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%'))
--#20497 2021.08.27 MOD STT
--and (('' = ':sir_cd') or (mh.sir_cd = ':sir_cd'))
--AND ((fnc_translate_case(ms.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
--#20497 2021.08.27 MOD END
--#24614 2022.06.29 MOD END

--#20484 2021.08.27 MOD STT
--AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(uh.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(uh.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(um.meisai_biko) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(um.meisai_biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
--#20484 2021.08.27 MOD END
--and (('' = ':hin_cd') or (um.hin_cd = ':hin_cd'))
AND ((um.hin_cd LIKE ':hin_cd%') or (':hin_cd' = ''))
--AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
AND ((fnc_translate_case(mh.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(um.hin_nm||COALESCE(' '||um.hin_nm_ext,'')) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--#20193 2021.08.05 MOD STT
:multi_cond_hin_nm
--#20193 2021.08.05 MOD END
and ((sh.shiji_no is not null and sh.shiji_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
--#22425 2022.01.05 MOD STT
--and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) != 0))
--#22425 2022.01.05 MOD END
--#25052 2022.07.29 MOD STT
and (('' = ':zan_flg') or (COALESCE(h99a.han_name,'') != '1') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
--#25052 2022.07.29 MOD END
and (('0' != ':cond_sekkei') or (sh.shiji_no is null))
and (('1' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 0))
and (('2' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':tok_tan') or (uh.tan_cd = ':tok_tan'))
and (('' = ':prc_sts') or (uh.prc_sts = ':prc_sts'))
and (('' = ':hosoku1') or (uh.prc_sts = ':hosoku1'))
and (('' = ':eig_cd') or (uh.eig_cd = ':eig_cd'))
and (('' = ':den_kbn') or (uh.den_kbn = ':den_kbn'))
and (('' = ':tok_cat_cd') or (tk.cat_cd = ':tok_cat_cd'))
and (('' = ':type_den') or ('0' = ':type_den'))
AND ((fnc_translate_case(uh.nonyu_nm||' '||COALESCE(uh.nonyu_tan)) LIKE '%'||fnc_translate_case(':nonyu_nm')||'%') or (':nonyu_nm' = ''))
and (('' = ':hin_nm_part1') or (um.hin_nm_part1 = ':hin_nm_part1'))
and (('' = ':hin_nm_part2_1') or (um.hin_nm_part2 like ':hin_nm_part2_1%'))
and (('' = ':hin_nm_part2_2') or (um.hin_nm_part2 like '%:hin_nm_part2_2'))
and (('' = ':hosoku34_1') or (um.hin_nm_ext_part1 like '%:hosoku34_1%') or (um.hin_nm_ext_part2 like '%:hosoku34_1%'))
and (('' = ':hosoku34_2') or (um.hin_nm_ext_part1 like '%:hosoku34_2%') or (um.hin_nm_ext_part2 like '%:hosoku34_2%'))
and (('' = ':kobetsu_kbn') or (uh.kobetsu_kbn = ':kobetsu_kbn'))
and (('' = ':kanri_no') or (uh.kanri_no like '%:kanri_no%'))
AND ((fnc_translate_case(um.tok_hacyu_no) LIKE '%'||fnc_translate_case(':tok_hacyu_no')||'%') or (':tok_hacyu_no' = ''))
--and (('' = ':tok_hacyu_no') or (um.tok_hacyu_no like '%:tok_hacyu_no%'))
and ((h05.koumoku3 = ':ccat_cd') or ('' = ':ccat_cd'))
--単価検索
and (('' = ':tanka_fr') or (um.tanka >= fnc_to_number(':tanka_fr')))
and (('' = ':tanka_to') or (um.tanka <= fnc_to_number(':tanka_to')))
--数量検索
and (('' = ':suryo_fr') or (um.jucyu_su >= fnc_to_number(':suryo_fr')))
and (('' = ':suryo_to') or (um.jucyu_su <= fnc_to_number(':suryo_to')))
--#25433 2022.09.20 MOD STT
and (('' = ':uri_enable') or (uh.uri_enable_flg = 1))
--#25433 2022.09.20 MOD END
and (('0' != ':cond_hosoku') or (coalesce(um.hosoku2,'') = ''))
and (('1' != ':cond_hosoku') or (um.hosoku2 != ''))
and (('' = ':hosoku3') or (um.hosoku3 like '%:hosoku3%'))

union all
--伝票単位
select
  w.sel_flg
, w.kbn
, w.den_no
, w.row_no --row_no
, w.hin_cd --hin_cd
, w.hin_nm --hin_nm
, w.hin_nm_row1
, w.jucyu_zan
, w.suryo
, w.jucyu_su
, w.gentanka
, w.tanka
--#24959 2022.07.23 MOD STT
, w.gaika_tanka
--#24959 2022.07.23 MOD END
, w.kingaku
, w.zeinuki_gaku
, w.syori_ymd
, w.nonyu_ymd
, w.tok_cd
, w.tok_nm
, w.nonyu_nm
, w.sir_cd
, w.sir_nm
, w.hosoku1
, w.biko1
, w.biko2
, w.tok_hacyu_no
, w.shiji_no
, w.sekkei_end_md
, w.keshi_flg
, w.nyukin_md
, w.tanka_f2
, w.prc_sts
--#19140 2021.05.13 MOD STT
--, w.prc_sts_nm
, h1.han_name as prc_sts_nm
--#19140 2021.05.13 MOD END
, w.hacyu_no
, w.regist_i
, w.ttan
, w.cat_nm
, w.meisai_biko
, w.meisai_biko2
, w.shiji_zan
, cast(coalesce(w.syukka_zan,0) as numeric(9,0)) as syukka_zan
, cast(null as numeric(12,2)) as now_stock
, w.syotei_ymd
, w.kanri_no
, w.m_kingaku
, w.den_kbn
, w.den_kbn_nm
, cast(null as numeric(10)) as rel_row_no
, cast(null as varchar(6)) as price_rank
, w.kobetsu_kbn
, w.kobetsu_kbn_nm
--#21978 2021.12.07 MOD STT
, w.saisu
, w.weight
--#21978 2021.12.07 MOD END
, w.nonyu_addr
, w.base_den_no
, (select sum(COALESCE(sm.hacyu_su,0) - COALESCE(sm.sir_su,0)) from t_sir_m sm where sm.jucyu_no = w.den_no and sm.del_flg = 0 and sm.hacyu_su != 0) as hacyu_zan
, w.gaika_nm
, w.gaika_rate
, w.m_kingaku_jpn
, w.hosoku3
from (
select
  cast(0 as int) as sel_flg
, cast('2' as varchar(1)) as kbn
, uh.den_no
, cast(-1 as numeric(10)) as row_no --row_no
, cast('' as varchar(50)) as hin_cd --hin_cd
, cast('' as varchar(255)) as hin_nm --hin_nm
, (select min(um1.hin_nm) from t_uri_m um1 where um1.den_no = uh.den_no and um1.row_no = 0) as hin_nm_row1
, sum(COALESCE(um.jucyu_su,0)) - sum(COALESCE(um.uri_su,0)) as jucyu_zan
, sum(COALESCE(um.jucyu_su,0)) - sum(COALESCE(um.uri_su,0)) as suryo
, sum(COALESCE(um.jucyu_su,0)) as jucyu_su
, cast(null as numeric(10,2)) as gentanka
, cast(null as numeric(10,2)) as tanka
--#24959 2022.07.23 MOD STT
, cast(null as numeric(10,2)) as gaika_tanka
--#24959 2022.07.23 MOD END
, sum(COALESCE(um.jucyu_su,0) * COALESCE(um.tanka,0)) as kingaku
, sum(um.zeinuki_gaku) as zeinuki_gaku
, uh.syori_ymd
, min(um.nonyu_ymd) as nonyu_ymd
, uh.tok_cd
, uh.tok_nm
, uh.nonyu_nm||' '||COALESCE(uh.nonyu_tan) as nonyu_nm
, cast('' as varchar(16)) as sir_cd
, cast('' as varchar(50)) as sir_nm
, cast('' as varchar(30)) as hosoku1
, uh.biko1
, uh.biko2
, uh.tok_hacyu_no
, cast(null as int) as shiji_no
, cast('' as varchar(100)) as sekkei_end_md
, uh.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
, cast(null as numeric(10,2)) as tanka_f2
, uh.prc_sts
--#19140 2021.05.13 MOD STT
, cast('' as varchar(100)) as prc_sts_nm
--#19140 2021.05.13 MOD END
, cast(null as int) as hacyu_no
, round(extract(epoch from now() - uh.cre_time) / 60 / 60) as regist_i
, case mt.tan_rnm when '' then mt.tan_nm
	else mt.tan_rnm
	end as ttan
, h3.han_name as cat_nm
, cast('' as varchar(255)) as meisai_biko
, cast('' as varchar(255)) as meisai_biko2
, sum(COALESCE(um.jucyu_su,0)) - (select COALESCE(sum(sh.suryo),0) from t_seizou sh where sh.shiji_flg <> 0 and sh.jucyu_no = uh.den_no) as shiji_zan
, (select COALESCE(sum(sh.suryo),0) from t_seizou sh where sh.jucyu_no = uh.den_no and sh.koutei_end_flg = 1 and sh.syukka_flg = 0) as syukka_zan2
,(select 
sum(tst.suryo)
from t_seizou_tan tst
inner join t_seizou ts on ts.shiji_no = tst.shiji_no
where tst.koutei_no = (select tsm.koutei_no from t_seizou_m tsm where tsm.shiji_no = tst.shiji_no and tsm.koutei_cd = '9')
    and ts.jucyu_no = uh.den_no
    and tst.tan_cd <> '99999'
    and ts.koutei_end_flg = 1 
    and ts.syukka_flg = 0) as syukka_zan
--,(select COALESCE(sum(sh2.suryo),0) from t_seizou sh2 where sh2.jucyu_no = uh.den_no and sh2.koutei_end_flg = 1 and sh2.syukka_flg = 0) as syukka_zan
--  (select COALESCE(MIN(tan.suryo),0) from t_seizou_tan tan left join t_seizou sh2 on (tan.shiji_no = sh2.shiji_no) where sh2.jucyu_no = uh.den_no and tan.tan_cd = '99999' and sh2.koutei_end_flg = 1 and sh2.syukka_flg = 0)  as syukka_zan
, uh.syotei_ymd
, uh.kanri_no
--#24959 2022.07.23 MOD STT
--, sum(COALESCE(um.jucyu_su,0) * COALESCE(um.tanka,0)) as m_kingaku
, sum(case when(uh.gaika_rate > 0)then(um.jucyu_su * round(uh.gaika_rate * um.tanka,0))else(um.kingaku)end) as m_kingaku
--#24959 2022.07.23 MOD END
, uh.den_kbn
, h54.han_name as den_kbn_nm
, uh.kobetsu_kbn
, h0d.han_name as kobetsu_kbn_nm
--#21978 2021.12.07 MOD STT
--受注残でなく受注数の方を諸元にする
--, sum(mh.saisu * (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0))) as saisu
--, sum(mh.weight * (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0))) as weight
, sum(mh.saisu * um.jucyu_su) as saisu
, sum(mh.weight * um.jucyu_su) as weight
--#21978 2021.12.07 MOD END
, COALESCE(uh.nonyu_addr1,'')||COALESCE(uh.nonyu_addr2,'')||COALESCE(uh.nonyu_addr3,'')||COALESCE(uh.nonyu_addr4,'') as nonyu_addr
, uh.base_den_no
, h0c.koumoku1 as gaika_nm
, uh.gaika_rate
, sum(case when(uh.gaika_rate > 0)then(um.jucyu_su * round(uh.gaika_rate * um.tanka,0))else(um.kingaku)end) as m_kingaku_jpn
, cast('' as varchar(255)) as hosoku3
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 t_seizou sh on (sh.jucyu_no = um.den_no and sh.jucyu_row_no = um.row_no and sh.del_flg = 0)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uh.prc_sts)
--#20497 2021.08.27 MOD STT
--left join t_sir_m sm on (um.den_no = sm.jucyu_no and um.row_no = sm.jucyu_row_no and sm.hacyu_no is null)
--left join t_sir si on (sm.den_no = si.den_no and si.hacyu_flg = 1)
--#20497 2021.08.27 MOD END
left join m_tanto mt on (uh.tan_cd = mt.tan_cd)
left join m_hanyo h3 on (h3.mst_kbn = '35' and h3.han_cd = tk.cat_cd)
left join m_hanyo h4 on (h4.mst_kbn = '90' and h4.han_cd = 'hin-title')
left join m_hanyo h54 on (h54.mst_kbn = '54' and h54.han_cd = uh.den_kbn)
left join m_hanyo h0c on (h0c.mst_kbn = '0C' and h0c.han_cd = uh.gaika_cd)
left join m_hanyo h0d on (h0d.mst_kbn = '0D' and h0d.han_cd = uh.kobetsu_kbn)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = mh.cat_cd)
--#25052 2022.07.29 MOD STT
left join m_hanyo h99a on (h99a.mst_kbn = '99' and h99a.han_cd = 'pzan-v0430')
--#25052 2022.07.29 MOD END
left join m_sir ms on (ms.sir_cd = mh.sir_cd)
WHERE 1 = 1
and uh.jucyu_flg = 1
and uh.del_flg = 0
--#26485 2022.12.20 MOD STT
--and um.hin_cd <> COALESCE(h4.koumoku1,'')
and ((COALESCE(h4.koumoku1,'') = '') or (COALESCE(um.hin_cd,'') != COALESCE(h4.koumoku1,'')))
--#26485 2022.12.20 MOD END
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 (('' = ':syotei_ymd_fr') or (uh.syotei_ymd >= ':syotei_ymd_fr'))
and (('' = ':syotei_ymd_to') or (uh.syotei_ymd <= ':syotei_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' = ''))
--#20497 2021.08.27 MOD STT
--and ((si.den_no = fnc_to_number(':hacyu_no')) or ('' = ':hacyu_no'))
--and (('' = ':sir_cd') or (si.sir_cd = ':sir_cd'))
--AND ((fnc_translate_case(si.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
--#20497 2021.08.27 MOD END
--#24614 2022.06.29 MOD STT
and (('' = ':sir_cd') or (mh.sir_cd = ':sir_cd') or (um.sir_cd = ':sir_cd'))
and (('' = ':sir_nm') or (fnc_translate_case(ms.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (fnc_translate_case(um.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%'))
--#24614 2022.06.29 MOD END
AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (fnc_translate_case(uh.biko2) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
AND ((um.hin_cd LIKE ':hin_cd%') or (':hin_cd' = ''))
--and (('' = ':hin_cd') or (um.hin_cd = ':hin_cd'))
--AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
AND ((fnc_translate_case(mh.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
--#20193 2021.08.05 MOD STT
:multi_cond_hin_nm
--#20193 2021.08.05 MOD END
and ((sh.shiji_no is not null and sh.shiji_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
--and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
--#22425 2022.01.05 MOD STT
--and (('' = ':zan_flg') or exists (select 1 from t_uri_m um2 where COALESCE(um2.jucyu_su,0) - COALESCE(um2.uri_su,0) > 0 and um.den_no = um2.den_no)) 
and (('' = ':zan_flg') or exists (select 1 from t_uri_m um2 where COALESCE(um2.jucyu_su,0) - COALESCE(um2.uri_su,0) != 0 and um.den_no = um2.den_no)) 
--#22425 2022.01.05 MOD END
--#25052 2022.07.29 MOD STT
and (('' = ':zan_flg') or (COALESCE(h99a.han_name,'') != '1') or exists (select 1 from t_uri_m um2 where COALESCE(um2.jucyu_su,0) - COALESCE(um2.uri_su,0) > 0 and um.den_no = um2.den_no))
--#25052 2022.07.29 MOD END
--and ((('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) > 0)) or (('' = ':zan_flg') or (COALESCE(um.uri_su,0) > 0)) or (('' = ':zan_flg') or (COALESCE(sh.suryo) > 0)))
and (('0' != ':cond_sekkei') or (sh.shiji_no is null))
and (('1' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 0))
and (('2' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':tok_tan') or (uh.tan_cd = ':tok_tan'))
and (('' = ':prc_sts') or (uh.prc_sts = ':prc_sts'))
and (('' = ':hosoku1') or (uh.prc_sts = ':hosoku1'))
and (('' = ':eig_cd') or (uh.eig_cd = ':eig_cd'))
and (('' = ':den_kbn') or (uh.den_kbn = ':den_kbn'))
and (('' = ':tok_cat_cd') or (tk.cat_cd = ':tok_cat_cd'))
and (('' = ':type_den') or ('1' = ':type_den'))
AND ((fnc_translate_case(uh.nonyu_nm||' '||COALESCE(uh.nonyu_tan)) LIKE '%'||fnc_translate_case(':nonyu_nm')||'%') or (':nonyu_nm' = ''))
and (('' = ':kobetsu_kbn') or (uh.kobetsu_kbn = ':kobetsu_kbn'))
and (('' = ':kanri_no') or (uh.kanri_no like '%:kanri_no%'))
and (('' = ':tok_hacyu_no') or (um.tok_hacyu_no like '%:tok_hacyu_no%'))
and ((h05.koumoku3 = ':ccat_cd') or ('' = ':ccat_cd'))
--単価検索
and (('' = ':tanka_fr') or (um.tanka >= fnc_to_number(':tanka_fr')))
and (('' = ':tanka_to') or (um.tanka <= fnc_to_number(':tanka_to')))
--数量検索
and (('' = ':suryo_fr') or (um.jucyu_su >= fnc_to_number(':suryo_fr')))
and (('' = ':suryo_to') or (um.jucyu_su <= fnc_to_number(':suryo_to')))
--#25433 2022.09.20 MOD STT
and (('' = ':uri_enable') or (uh.uri_enable_flg = 1))
--#25433 2022.09.20 MOD END
and (('0' != ':cond_hosoku') or (coalesce(um.hosoku2,'') = ''))
and (('1' != ':cond_hosoku') or (um.hosoku2 != ''))
and (('' = ':hosoku3') or (um.hosoku3 like '%:hosoku3%'))

group by uh.den_no,uh,uh.syori_ymd,mt.tan_rnm,mt.tan_nm,h3.han_name
,uh.tok_cd,uh.tok_nm,uh.nonyu_nm||' '||COALESCE(uh.nonyu_tan), uh.biko1, uh.biko2, uh.tok_hacyu_no, uh.keshi_flg
, (case when(uh.keshi_flg = 1)then(to_char(to_date(uh.keshi_ymd,'yyyymmdd'),'mm/dd'))else(null)end) 
, uh.prc_sts
, round(extract(epoch from now() - uh.cre_time) / 60 / 60) 
, uh.syotei_ymd
, uh.den_kbn
, h54.han_name
, uh.kobetsu_kbn
, h0d.han_name
, h0c.koumoku1
, uh.gaika_rate

) w
--#19140 2021.05.13 MOD STT
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = w.prc_sts)
--#19140 2021.05.13 MOD END
where 1 = 1
--#22425 2022.01.05 MOD STT
--and (('' = ':shiji_zan_flg') or (w.shiji_zan > 0))
--and (('' = ':syukka_zan_flg') or (w.syukka_zan > 0))
and (('' = ':shiji_zan_flg') or (w.shiji_zan != 0))
and (('' = ':syukka_zan_flg') or (w.syukka_zan != 0))
--#22425 2022.01.05 MOD END

order by kbn, nonyu_ymd, syori_ymd, den_no, row_no

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

[chk_counter]
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))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
order by um.nonyu_ymd, uh.syori_ymd, um.den_no, um.row_no
;


[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))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
order by um.nonyu_ymd, uh.syori_ymd, um.den_no, um.row_no
;

[Log_getKousuValue]
select
  sm.seizou_tan_cd as tan_cd
, sum(sm.kousu) as val
from t_seizou sh
inner join t_seizou_m sm on (sm.shiji_no = sh.shiji_no)
where sh.jucyu_no = :w_den_no
and sh.jucyu_row_no = :w_row_no
--and sm.seizou_tan_cd != ''
group by sm.seizou_tan_cd

[getKousuValue]
select
'1' as kbn
--'kousu_' || tsm.seizou_tan_cd || '-' || sh.jucyu_no || '-' || sh.jucyu_row_no as key
,'kousu_' || COALESCE(h0.han_cd,'') || '-' || sh.jucyu_no || '-' || sh.jucyu_row_no as key
, sum(tsm.kousu) as val
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 t_seizou sh on (sh.jucyu_no = um.den_no and sh.jucyu_row_no = um.row_no and sh.del_flg = 0)
--inner join t_seizou_m tsm on (tsm.shiji_no = sh.shiji_no)
left join t_seizou_m tsm on (tsm.shiji_no = sh.shiji_no)
left join v_hanyo_all h0 on (h0.mst_kbn = 'TAN.SEIZOU' and h0.han_cd = tsm.seizou_tan_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uh.prc_sts)
left join t_sir_m sm on (um.den_no = sm.jucyu_no and um.row_no = sm.jucyu_row_no and sm.hacyu_no is null)
left join t_sir si on (sm.den_no = si.den_no and si.hacyu_flg = 1)
WHERE 1 = 1
and uh.jucyu_flg = 1
and uh.del_flg = 0
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 ((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 ((si.den_no = fnc_to_number(':hacyu_no')) or ('' = ':hacyu_no'))
and (('' = ':sir_cd') or (si.sir_cd = ':sir_cd'))
AND ((fnc_translate_case(si.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
and (('' = ':hin_cd') or (um.hin_cd = ':hin_cd'))
--AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
AND ((fnc_translate_case(mh.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
and ((sh.shiji_no is not null and sh.shiji_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
and (('0' != ':cond_sekkei') or (sh.shiji_no is null))
and (('1' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 0))
and (('2' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':tok_tan') or (uh.tan_cd = ':tok_tan'))
and (('' = ':prc_sts') or (uh.prc_sts = ':prc_sts'))
and (('' = ':hosoku1') or (uh.prc_sts = ':hosoku1'))
and (('' = ':type_den') or ('0' = ':type_den'))
--group by 'kousu_' || tsm.seizou_tan_cd || '-' || sh.jucyu_no || '-' || sh.jucyu_row_no
group by 'kousu_' || COALESCE(h0.han_cd,'') || '-' || sh.jucyu_no || '-' || sh.jucyu_row_no
union all
select
'2' as kbn
--'kousu_' || tsm.seizou_tan_cd || '-' || sh.jucyu_no || '-' || sh.jucyu_row_no as key
,'kousu_' || COALESCE(h0.han_cd,'') || '-' || sh.jucyu_no || '-'  as key
, sum(tsm.kousu) as val
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 t_seizou sh on (sh.jucyu_no = um.den_no and sh.jucyu_row_no = um.row_no and sh.del_flg = 0)
--inner join t_seizou_m tsm on (tsm.shiji_no = sh.shiji_no)
left join t_seizou_m tsm on (tsm.shiji_no = sh.shiji_no)
left join v_hanyo_all h0 on (h0.mst_kbn = 'TAN.SEIZOU' and h0.han_cd = tsm.seizou_tan_cd)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uh.prc_sts)
left join t_sir_m sm on (um.den_no = sm.jucyu_no and um.row_no = sm.jucyu_row_no and sm.hacyu_no is null)
left join t_sir si on (sm.den_no = si.den_no and si.hacyu_flg = 1)
WHERE 1 = 1
and uh.jucyu_flg = 1
and uh.del_flg = 0
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 ((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 ((si.den_no = fnc_to_number(':hacyu_no')) or ('' = ':hacyu_no'))
and (('' = ':sir_cd') or (si.sir_cd = ':sir_cd'))
AND ((fnc_translate_case(si.sir_nm) LIKE '%'||fnc_translate_case(':sir_nm')||'%') or (':sir_nm' = ''))
AND ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
and (('' = ':hin_cd') or (um.hin_cd = ':hin_cd'))
--AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
AND ((fnc_translate_case(mh.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
and ((sh.shiji_no is not null and sh.shiji_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
and (('0' != ':cond_sekkei') or (sh.shiji_no is null))
and (('1' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 0))
and (('2' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':tok_tan') or (uh.tan_cd = ':tok_tan'))
and (('' = ':prc_sts') or (uh.prc_sts = ':prc_sts'))
and (('' = ':hosoku1') or (uh.prc_sts = ':hosoku1'))
and (('' = ':type_den') or ('1' = ':type_den'))
--group by 'kousu_' || tsm.seizou_tan_cd || '-' || sh.jucyu_no || '-' || sh.jucyu_row_no
group by 'kousu_' || COALESCE(h0.han_cd,'') || '-' || sh.jucyu_no || '-'
order by kbn

[getTantoFields]
select
  m.tan_cd
, (case when(m.tan_rnm != '')then(m.tan_rnm)else(m.tan_nm)end) as tan_nm
, v.sort_key
from v_hanyo_all v
inner join m_tanto m on (m.tan_cd = v.han_cd)
where v.mst_kbn = 'TAN.SEIZOU'
and m.del_flg = 0
union all
select
null as tan_cd
,'その他' as tan_nm
,'99' as sort_key
order by sort_key, tan_cd
--order by m.sort_key, m.tan_cd

[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

[load_genkaList]
select
sm.seizou_tan_cd as tan_cd
, coalesce(mt.tan_nm,'その他') as tan_nm
, sum(sm.kousu) as val
, min(case uh.syori_ymd when '' then null else uh.syori_ymd end) as syori_ymd
, min(case um.nonyu_ymd when '' then null else um.nonyu_ymd end) as min_ny
, max(case um.nonyu_ymd when '' then null else um.nonyu_ymd end) as max_ny
, sum(COALESCE(um.jucyu_su,0)) ju
, sum(COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0)) as us
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 t_seizou sh on (sh.jucyu_no = um.den_no and sh.jucyu_row_no = um.row_no and sh.del_flg = 0)
inner join t_seizou_m sm on (sm.shiji_no = sh.shiji_no)
left join m_hanyo h1 on (h1.mst_kbn = '01' and h1.han_cd = uh.prc_sts)
--left join m_tanto mt on (sm.seizou_tan_cd = mt.tan_cd and mt.del_flg = 0)
left join m_tanto mt on (sm.seizou_tan_cd = mt.tan_cd)
left join v_hanyo_all v on (mt.tan_cd = v.han_cd and v.mst_kbn = 'TAN.SEIZOU')
WHERE 1 = 1
and uh.jucyu_flg = 1
and uh.del_flg = 0
--and mt.del_flg = 0
--and v.mst_kbn = 'TAN.SEIZOU'
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 ((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 ((fnc_translate_case(uh.biko1) LIKE '%'||fnc_translate_case(':biko1')||'%') or (':biko1' = ''))
and (('' = ':hin_cd') or (um.hin_cd = ':hin_cd'))
AND ((fnc_translate_case(um.hin_nm) LIKE '%'||fnc_translate_case(':hin_nm')||'%') or (':hin_nm' = ''))
and ((sh.shiji_no is not null and sh.shiji_no = fnc_to_number(':shiji_no')) or ('' = ':shiji_no'))
and (('' = ':zan_flg') or (COALESCE(um.jucyu_su,0) - COALESCE(um.uri_su,0) > 0))
and (('0' != ':cond_sekkei') or (sh.shiji_no is null))
and (('1' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 0))
and (('2' != ':cond_sekkei') or (sh.shiji_no is not null and sh.sekkei_flg = 1))
and (('' = ':tan_cd') or (tk.tan_cd = ':tan_cd'))
and (('' = ':tok_tan') or (uh.tan_cd = ':tok_tan'))
and (('' = ':prc_sts') or (uh.prc_sts = ':prc_sts'))
and (('' = ':hosoku1') or (uh.prc_sts = ':hosoku1'))
group by sm.seizou_tan_cd,mt.tan_nm
order by val DESC
;

[deleteImportWork]
delete from w_basv0210 where s_id = :s_id;
delete from w_basv0210_m where s_id = :s_id;

[commitWork]
select fnc_update_basv0210(:s_id);

[updZeinukigaku]


[insertHeaderWork]
insert into w_basv0210 (
  s_id
, henpin_flg
, seikyu_flg
, syukka_flg
, zaiko_flg
, jucyu_flg
, cash_flg
, tok_cd
, tok_nm
, tok_tan
, tok_addr1
, tok_addr2
, tok_addr3
, tok_tel_no
, tok_fax_no
, tok_email
, tok_hacyu_no
, syori_ymd
, input_ymd
, tan_cd
, biko1
, biko2
, zeinuki_gaku
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, 0 --henpin_flg
, 0 --seikyu_flg
, 0 --syukka_flg
, 0 --zaiko_flg
, 1 --jucyu_flg
, 0 --cash_flg
, mt.tok_cd
, mt.tok_nm
, mt.tok_tan
, mt.addr1
, mt.addr2
, mt.addr3
, mt.tel_no
, mt.fax_no
, mt.email
, ':tok_hacyu_no'
, ':syori_ymd'
, ':syori_ymd' --input_ymd
, ':login_id' --tan_cd
, ':biko1'
, ':biko2'
, :goukei
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_tokui mt
where mt.tok_cd = ':tok_cd'

[insertDetailWork]
insert into w_basv0210_m (
  s_id
, row_no
, hin_cd
, hin_nm
, hosoku1
, size_l
, jucyu_su
, tanka
, kingaku
, zeinuki_gaku
, jucyu_zeinuki_gaku
, nonyu_ymd
, zaiko_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, :row_no
, ':hin_cd'
, (select min(mh.hin_nm) from m_hin mh where hin_cd = ':hin_cd')
, ':hosoku1'
, :size_l
, :jucyu_su
, :tanka
, :jucyu_su * :tanka --kingaku
, :jucyu_su * :tanka --zeinuki_gaku
, :jucyu_su * :tanka --jucyu_zeinuki_gaku
, ':nonyu_ymd'
, 1 --zaiko_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time

[deleteSelWork]
delete from w_basv0430_sel where s_id = :s_id

[deleteUriWork]
delete from w_basv0210_ikkatsu where s_id = :s_id

[insertSelWork]
insert into w_basv0430_sel (s_id, den_no, row_no, del_flg, cre_id, cre_time, upd_id, upd_time)
 values (:s_id, :den_no, :row_no, 0, ':login_id', now(), ':login_id', now())

[insertUriWork]
insert into w_basv0210_ikkatsu (s_id, jucyu_no, jucyu_row_no, del_flg, cre_id, cre_time, upd_id, upd_time)
 values (:s_id, :den_no, :row_no, 0, ':login_id', now(), ':login_id', now())

[checkIkkatsuUri]
select count(distinct uh.tok_cd) as tok_cnt
from w_basv0210_ikkatsu w
inner join t_uri uh on (uh.den_no = w.jucyu_no)
where s_id = :s_id

[createUri]
select fnc_update_basv0430_uri(:s_id, :chk_group_syukka, :syukka_req_mng_id, ':login_id')

[createUriSeisanGrp]
select fnc_update_basv0430(:s_id, ':login_id');
--select fnc_update_basv0430(:s_id, ':login_id')

[separateSeizou]
select fnc_separate_seizou(:s_id, ':login_id');

[Ins_basv0430]
insert into w_basv0430
(s_id, den_no, row_no, syori_ymd, tok_cd, suryo, group_no, del_flg, 
	cre_id, cre_time, upd_id, upd_time)
values(:s_id, :den_no, :row_no, ':syori_ymd', ':tok_cd', :suryo, null, 0, 
	':login_id', now(), ':login_id', now());

[Upd_jyucyu]
update t_uri_m set suryo = :suryo
where den_no = :den_no and row_no = :row_no
;

[Del_jyucyu]
update t_uri_m
set del_flg = 1
	,upd_id = ':login_id'
	,upd_time = now()
where den_no = :den_no
;

update t_uri 
set del_flg = 1
	,upd_id = ':login_id'
	,upd_time = now()
where den_no = :den_no 
and jucyu_flg = 1
;

[Upd_basv0430]
select fnc_update_basv0430(:s_id);

[Load_basv0210]
select fnc_load_basv0210(:s_id, :den_no);

[Upd_basv0210]
select fnc_update_basv0210(:s_id, :den_no);


[Del_basv0430]
delete from w_basv0430 where s_id = :s_id;

[getNewShijiNo]
--select fnc_get_saiban(:s_id, '10') as shiji_no;
--select max(shiji_no) + 1 from t_seizou where jucyu_no = :jucyu_no;
select coalesce(max(shiji_no), (:jucyu_no*1000))::numeric as new_no
from t_seizou 
where jucyu_no = :jucyu_no

[inserSeizouKoutei]
select fnc_update_seizou_koutei(:shiji_no, ':login_id')

[inserSeizou_bd]
insert into t_seizou_bd (shiji_no, row_no, jucyu_no, jucyu_row_no, suryo, del_flg, cre_id, cre_time, upd_id, upd_time)
select
  :shiji_no
, :row_no
, :jucyu_no
, um.row_no --jucyu_row_no
, :jucyu_su
, 0
, ':login_id'
, now()
, ':login_id'
, now()
from t_uri_m um
where den_no = :jucyu_no
and size_l = :size_l

[deleteSeizou_bd]
delete from t_seizou_bd where shiji_no = :shiji_no;
delete from t_seizou where shiji_no = :shiji_no; --一旦削除

[inserSeizouHeader]
insert into t_seizou (shiji_no, input_ymd, syori_ymd, jucyu_no, jucyu_row_no, 
hacyu_irai_flg, sekkei_flg, koutei_end_flg, sekkei_kensa_flg, eigyou_kensa_flg, kikaku_kensa_flg, syukka_flg, shiji_flg, del_flg
, cre_id, cre_time, upd_id, upd_time)
--values(:shiji_no, ':syori_ymd', ':syori_ymd', :jucyu_no, :jucyu_row_no, 
values(:shiji_no, ':syori_ymd', ':syori_ymd', :jucyu_no, -1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, ':login_id', now(), ':login_id', now());
--更新
UPDATE t_seizou sh 
SET suryo = wk.suryo
, hin_cd = (
  select min(mh.dhin_cd)
  from t_uri_m um
  inner join m_hin mh on (mh.hin_cd = um.hin_cd)
  where um.den_no = sh.jucyu_no
)
FROM (
    select bd.shiji_no, sum(bd.suryo) as suryo
    from t_seizou_bd bd
    WHERE bd.jucyu_no = :jucyu_no
    group by bd.shiji_no) wk 
WHERE sh.shiji_no = wk.shiji_no
;

[inserShiyouZairyo]
select fnc_update_zairyo(:shiji_no, ':login_id');

[getRankTanka]
select rank.hin_cd, rank.price_rank, coalesce(rank.tanka,0) as tanka --nullの場合は0円セット
from m_hin_rank rank
left join m_hin hin on rank.hin_cd = hin.hin_cd
left join m_tokui tok on rank.price_rank = tok.price_rank
where 1 = 1
and rank.hin_cd = ':hin_cd'
and tok.tok_cd = ':tok_cd'
;

[getNewDenNo]
select den_no as new_den
from w_basv0210
where s_id = :s_id
;


--select coalesce(max(den_no)) as new_den
--from t_uri
--where jucyu_flg = '1'


[getNewShijiNo]
--select fnc_get_saiban(:s_id, '10') as shiji_no;
--select max(shiji_no) + 1 from t_seizou where jucyu_no = :jucyu_no;
select coalesce(max(shiji_no), (:jucyu_no*1000))::numeric as new_no
from t_seizou 
where jucyu_no = :jucyu_no

[getKingaku]
select sum(kingaku) as goukei
from w_basv0210_m
where s_id = :s_id;

[updKingaku]
update t_uri t set zeinuki_gaku = w.zeinuki_gaku
from w_basv0210 w
where w.s_id = :s_id and t.den_no = :jucyu_no

[updWorkKazei]
update t_uri t set kazei_gaku = (select sum(kingaku) as goukei from w_basv0210_m where s_id = :s_id)
where t.den_no = :jucyu_no
;

[updWorkZei]
update t_uri t set
	  zeinuki_gaku = kazei_gaku
	, soto_gaku = fnc_get_zei(kazei_gaku, t.syori_ymd)
	, uchi_gaku = 0
where t.den_no = :jucyu_no
;

[updWorkZeikomi]
update t_uri t set
	  zei_gaku = COALESCE(soto_gaku,0) + COALESCE(uchi_gaku,0)
	, zeikomi_gaku = COALESCE(zeinuki_gaku,0) + COALESCE(soto_gaku,0) + COALESCE(uchi_gaku,0)
where t.den_no = :jucyu_no
;

[get_tok_hacyu]
select tok_hacyu_no
from t_uri
where 1=1
and tok_hacyu_no = ':tok_hacyu_no'
and tok_cd = ':tok_cd'
and del_flg = 0
;

[gethinCd]
select hin_cd
from m_hin
where 1=1
and hin_cd = ':hin_cd'
;

[cancelJucyuzan]
update t_uri_m set
  jucyu_cancel_su = (jucyu_su - COALESCE(uri_su,0))
, upd_id = ':login_id'
, upd_time = now()
where den_no = :w_den_no
and ((:w_row_no < 0) or (row_no = :w_row_no))
and (jucyu_su - COALESCE(uri_su,0)) != 0


[get_shiji_no]
select shiji_no
from t_seizou
where 1=1
and jucyu_no = ':den_no'
and del_flg = '0'
and shiji_flg = '1'
;


[insertHeaderWork_type2]
insert into w_basv0210 (
  s_id
, henpin_flg
, seikyu_flg
, syukka_flg
, zaiko_flg
, jucyu_flg
, cash_flg
, tok_cd
, tok_nm
, tok_tan
, tok_addr1
, tok_addr2
, tok_addr3
, tok_tel_no
, tok_fax_no
, tok_email
, nonyu_no
, nonyu_nm
, nonyu_zip_no
, nonyu_addr1
, nonyu_addr2
, nonyu_addr3
, nonyu_tel_no
, nonyu_fax_no
--, tok_hacyu_no
, syori_ymd
, input_ymd
, tan_cd
, biko1
, biko2
, zeinuki_gaku
, zei_hasu_kbn
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, 0 --henpin_flg
, 0 --seikyu_flg
, 0 --syukka_flg
, 0 --zaiko_flg
, 1 --jucyu_flg
, 0 --cash_flg
, mt.tok_cd
, mt.tok_nm
, mt.tok_tan
, mt.addr1
, mt.addr2
, mt.addr3
, mt.tel_no
, mt.fax_no
, mt.email
, w.nonyu_no
, w.nonyu_nm
, w.zip_no
, w.addr1
, w.addr2
, w.addr3
, w.tel_no
, w.fax_no
--, ':tok_hacyu_no'
, ':syori_ymd'
, ':syori_ymd' --input_ymd
, ':login_id' --tan_cd
, ':biko1'
, ':biko2'
, :kingaku
, '2'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_tokui mt
left join (
select tok_cd, min(nonyu_no) as nonyu_no, nonyu_nm, zip_no, addr1, addr2, addr3, tel_no, fax_no from m_nonyu where tok_cd = ':tok_cd' group by tok_cd, nonyu_no order by nonyu_no limit 1
) as w on (w.tok_cd = mt.tok_cd)
where mt.tok_cd = ':tok_cd'
;

[insertDetailWork_type2]
insert into w_basv0210_m (
  s_id
, row_no
, hin_cd
, hin_nm
, jucyu_su
, tani
, tanka
, kingaku
, zeinuki_gaku
, jucyu_zeinuki_gaku
, hosoku1
, zaiko_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, :row_no
, ':hin_cd'
, ':hin_nm'
, :jucyu_su
, ':tani'
, :tanka
, :kingaku --kingaku
, :kingaku --zeinuki_gaku
, :kingaku --jucyu_zeinuki_gaku
, :tok_hacyu_no
, 1 --zaiko_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
;

[insertNohinWork_type2]
insert into w_basv0210_m (
  s_id
, row_no
, hin_cd
, hin_nm
, jucyu_su
, hosoku1
, zaiko_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, :row_no
, ':hin_cd'
, ':hin_nm'
, :jucyu_su
, :tok_hacyu_no
, 1 --zaiko_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
;

[checkGroupSyukka]
select distinct
  uh.nonyu_ymd
, uh.syotei_ymd
--#22012 2021.12.09 MOD STT
, fnc_translate_case(uh.tok_nm) as tok_nm
, fnc_translate_case((uh.nonyu_nm||' '||COALESCE(uh.nonyu_tan))) as nonyu_nm
--#22012 2021.12.09 MOD END
from w_basv0430_sel w
inner join t_uri uh on (uh.den_no = w.den_no)
where w.s_id = :s_id

[getSyukkaReqMngId]
select nextval('seq_syukka_req_mng_id') as syukka_req_mng_id

[updateSyukkaReq]
insert into t_syukka_req_den (
  syukka_req_mng_id
, syukka_req_no
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
) values (
  w_syukka_req_mng_id
, w_syukka_req_no
, 0 --del_flg
, p_login_id --cre_id
, now() --cre_time
, p_login_id --upd_id
, now() --upd_time
);




[insertHeaderWork_type3]
insert into w_basv0210 (
  s_id
, henpin_flg
, seikyu_flg
, syukka_flg
, zaiko_flg
, jucyu_flg
, cash_flg
, tok_cd
, tok_nm
, tok_tan
, tok_addr1
, tok_addr2
, tok_addr3
, tok_tel_no
, tok_fax_no
, tok_email
--, nonyu_ymd
--, nonyu_no
, nonyu_nm
--, nonyu_zip_no
--, nonyu_addr1
--, nonyu_addr2
--, nonyu_addr3
--, nonyu_tel_no
--, nonyu_fax_no
--, tok_hacyu_no
, syori_ymd
, input_ymd
, tan_cd
--, biko1
, biko2
--, zeinuki_gaku
, zei_hasu_kbn
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, 0 --henpin_flg
, 0 --seikyu_flg
, 0 --syukka_flg
, 0 --zaiko_flg
, 1 --jucyu_flg
, 0 --cash_flg
, mt.tok_cd
, mt.tok_nm
, mt.tok_tan
, mt.addr1
, mt.addr2
, mt.addr3
, mt.tel_no
, mt.fax_no
, mt.email
--, ':nonyu_ymd'
--, w.nonyu_no
, substr(':nonyu_nm',1,50) as nonyu_nm
--, w.zip_no
--, w.addr1
--, w.addr2
--, w.addr3
--, w.tel_no
--, w.fax_no
--, ':tok_hacyu_no'
, ':syori_ymd'
, ':syori_ymd' --input_ymd
, ':login_id' --tan_cd
--, ':biko1'
, ':biko2'
--, :kingaku
, '2'
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from m_tokui mt
left join (
select tok_cd, min(nonyu_no) as nonyu_no, nonyu_nm, zip_no, addr1, addr2, addr3, tel_no, fax_no from m_nonyu where tok_cd = ':tok_cd' group by tok_cd, nonyu_no order by nonyu_no limit 1
) as w on (w.tok_cd = mt.tok_cd)
where mt.tok_cd = ':tok_cd'
;

[insertDetailWork_type3]
insert into w_basv0210_m (
  s_id
, row_no
, hin_cd
, hin_nm
, jucyu_su
, tani_tani
, jyu_tani
, uri_tani
, tani
--, tanka
--, kingaku
--, zeinuki_gaku
--, jucyu_zeinuki_gaku
, tok_hacyu_no
, hosoku1
, zaiko_flg
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  :s_id
, :row_no
, '999'
, ':hin_nm'
, :jucyu_su
, case(':tok_cd') when '5210' then '個' else '' end
, case(':tok_cd') when '5210' then '個' else '' end
, case(':tok_cd') when '5210' then '個' else '' end
, case(':tok_cd') when '5210' then '個' else '' end
--, 0 --tanka
--, 0 --kingaku
--, 0 --zeinuki_gaku
--, 0 --jucyu_zeinuki_gaku
, ':tok_hacyu_no'
, ''
, 1 --zaiko_flg
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
;