[loadOriginal]
select fnc_update_sum_3years_sir(:s_id, ':taisyo_ym', ':sir_cat_cd', ':sir_cd', ':sir_cd', ':hin_dcat_cd', ':hin_dcat_cd', ':hin_cat_cd', ':hin_cat_cd', ':hin_cd', ':hin_cd', ':eig_cd', ':eig_cd', ':login_id')

[load_grdList]
select
  0 as kbn_i
, (case(':sum_kbn')when('hdcat')then(w.hin_dcat_cd)when('tcat')then(w.sir_cat_cd)when('hcat')then(w.hin_cat_cd)when('hin')then(w.hin_cd)else(w.sir_cd)end) as sir_cd
, (case(':sum_kbn')when('hdcat')then(h14.han_name)when('tcat')then(h45.han_name)when('hcat')then(h05.han_name)when('hin')then(w.hin_nm)else((case when(mt.sir_rnm != '')then(mt.sir_rnm)else(w.sir_nm)end))end) as sir_nm
, 1 as row_kbn_i
, '仕入' as row_kbn
, round(w.sir_kon_01 / :tani_i, :dp_i) as kon_01
, round(w.sir_kon_02 / :tani_i, :dp_i) as kon_02
, round(w.sir_kon_03 / :tani_i, :dp_i) as kon_03
, round(w.sir_kon_04 / :tani_i, :dp_i) as kon_04
, round(w.sir_kon_05 / :tani_i, :dp_i) as kon_05
, round(w.sir_kon_06 / :tani_i, :dp_i) as kon_06
, round(w.sir_kon_07 / :tani_i, :dp_i) as kon_07
, round(w.sir_kon_08 / :tani_i, :dp_i) as kon_08
, round(w.sir_kon_09 / :tani_i, :dp_i) as kon_09
, round(w.sir_kon_10 / :tani_i, :dp_i) as kon_10
, round(w.sir_kon_11 / :tani_i, :dp_i) as kon_11
, round(w.sir_kon_12 / :tani_i, :dp_i) as kon_12
, round(w.sir_kon_total / :tani_i, :dp_i) as kon_total
from w_sum_3years_sir w
left join m_sir mt on (mt.sir_cd = w.sir_cd)
left join m_hanyo h14 on (h14.mst_kbn = '14' and h14.han_cd = w.hin_dcat_cd)
left join m_hanyo h45 on (h45.mst_kbn = '45' and h45.han_cd = w.sir_cat_cd)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = w.hin_cat_cd)
where 1 = 1
and w.s_id = :s_id
and w.sum_kbn = ':sum_kbn'
and ':chk_sir_gaku' = '1'
--TODO 
and w.suryo_kon_total != 0

--#23664 2022.04.25 MOD STT
union all
select
  0 as kbn_i
, (case(':sum_kbn')when('hdcat')then(w.hin_dcat_cd)when('tcat')then(w.sir_cat_cd)when('hcat')then(w.hin_cat_cd)when('hin')then(w.hin_cd)else(w.sir_cd)end) as sir_cd
, (case(':sum_kbn')when('hdcat')then(h14.han_name)when('tcat')then(h45.han_name)when('hcat')then(h05.han_name)when('hin')then(w.hin_nm)else((case when(mt.sir_rnm != '')then(mt.sir_rnm)else(w.sir_nm)end))end) as sir_nm
, 2 as row_kbn_i
, '数量' as row_kbn
, w.suryo_kon_01 as kon_01
, w.suryo_kon_02 as kon_02
, w.suryo_kon_03 as kon_03
, w.suryo_kon_04 as kon_04
, w.suryo_kon_05 as kon_05
, w.suryo_kon_06 as kon_06
, w.suryo_kon_07 as kon_07
, w.suryo_kon_08 as kon_08
, w.suryo_kon_09 as kon_09
, w.suryo_kon_10 as kon_10
, w.suryo_kon_11 as kon_11
, w.suryo_kon_12 as kon_12
, w.suryo_kon_total as kon_total
from w_sum_3years_sir w
left join m_sir mt on (mt.sir_cd = w.sir_cd)
left join m_hanyo h14 on (h14.mst_kbn = '14' and h14.han_cd = w.hin_dcat_cd)
left join m_hanyo h45 on (h45.mst_kbn = '45' and h45.han_cd = w.sir_cat_cd)
left join m_hanyo h05 on (h05.mst_kbn = '05' and h05.han_cd = w.hin_cat_cd)
where 1 = 1
and w.s_id = :s_id
and w.sum_kbn = ':sum_kbn'
and ':chk_suryo' = '1'
and w.suryo_kon_total != 0
--#23664 2022.04.25 MOD END

union all
select
  1 as kbn_i
, '' as sir_cd
, '総合計' as sir_nm
, 1 as row_kbn_i
, '仕入' as row_kbn
, round(w.sir_kon_01 / :tani_i, :dp_i) as kon_01
, round(w.sir_kon_02 / :tani_i, :dp_i) as kon_02
, round(w.sir_kon_03 / :tani_i, :dp_i) as kon_03
, round(w.sir_kon_04 / :tani_i, :dp_i) as kon_04
, round(w.sir_kon_05 / :tani_i, :dp_i) as kon_05
, round(w.sir_kon_06 / :tani_i, :dp_i) as kon_06
, round(w.sir_kon_07 / :tani_i, :dp_i) as kon_07
, round(w.sir_kon_08 / :tani_i, :dp_i) as kon_08
, round(w.sir_kon_09 / :tani_i, :dp_i) as kon_09
, round(w.sir_kon_10 / :tani_i, :dp_i) as kon_10
, round(w.sir_kon_11 / :tani_i, :dp_i) as kon_11
, round(w.sir_kon_12 / :tani_i, :dp_i) as kon_12
, round(w.sir_kon_total / :tani_i, :dp_i) as kon_total
from w_sum_3years_sir w
left join m_sir mt on (mt.sir_cd = w.sir_cd)
left join m_hanyo h14 on (h14.mst_kbn = '14' and h14.han_cd = w.hin_dcat_cd)
left join m_hanyo h45 on (h45.mst_kbn = '45' and h45.han_cd = w.sir_cat_cd)
where 1 = 1
and w.s_id = :s_id
and w.sum_kbn = 'total'
and ':chk_sir_gaku' = '1'
--TODO 
and w.suryo_kon_total != 0

--#23664 2022.04.25 MOD STT
union all
select
  1 as kbn_i
, '' as tok_cd
, '総合計' as tok_nm
, 2 as row_kbn_i
, '数量' as row_kbn
, w.suryo_kon_01 as kon_01
, w.suryo_kon_02 as kon_02
, w.suryo_kon_03 as kon_03
, w.suryo_kon_04 as kon_04
, w.suryo_kon_05 as kon_05
, w.suryo_kon_06 as kon_06
, w.suryo_kon_07 as kon_07
, w.suryo_kon_08 as kon_08
, w.suryo_kon_09 as kon_09
, w.suryo_kon_10 as kon_10
, w.suryo_kon_11 as kon_11
, w.suryo_kon_12 as kon_12
, w.suryo_kon_total as kon_total
from w_sum_3years_sir w
left join m_sir mt on (mt.sir_cd = w.sir_cd)
left join m_hanyo h14 on (h14.mst_kbn = '14' and h14.han_cd = w.hin_dcat_cd)
left join m_hanyo h45 on (h45.mst_kbn = '45' and h45.han_cd = w.sir_cat_cd)
where 1 = 1
and w.s_id = :s_id
and w.sum_kbn = 'total'
and ':chk_suryo' = '1'

and w.suryo_kon_total != 0
--#23664 2022.04.25 MOD END

order by kbn_i, sir_cd, sir_nm, row_kbn_i