[loadOriginal]
select fnc_load_basv0219(:s_id, :parent_s_id)

[getJucyuDtl]
select
  m.row_no
from w_basv0210_h m
where m.s_id = :parent_s_id
and 'h' = ':dtl_suffix'
and m.jucyu_su != 0
union all
select
  m.row_no
from w_basv0210_m m
where m.s_id = :parent_s_id
and 'm' = ':dtl_suffix'
and m.jucyu_su != 0


[load_grdList_pack]
select
  m.s_id
, m.pack_seq
, m.pack_seq_c
, 1 + m.pack_seq as pack_seq_v
, 1 + m.pack_seq_c as pack_seq_c_v
, m.pack_net_weight
, m.pack_gross_weight
, h42.han_name as konpo_keijyo_nm
, h.konpo_size_l
, h.konpo_size_w
, h.konpo_size_h
, h.konpo_volume
, (select sum(l.suryo) from w_basv0210_pack_link l where m.s_id = l.s_id and m.pack_seq = l.pack_seq and m.pack_seq_c = l.pack_seq_c) as suryo
from w_basv0210_pack_m m
left join w_basv0210_pack h on (h.s_id = m.s_id and h.pack_seq = m.pack_seq)
left join m_hanyo h42 on (h42.mst_kbn = '42' and h42.han_cd = h.konpo_keijyo)
where m.s_id = :parent_s_id
order by pack_seq, pack_seq_c

[load_grdList_m]
select
  w.s_id
, w.row_no
, w.row_no_v
, w.hin_nm
, w.hin_nm_ext1
, w.hin_nm_ext2
, COALESCE(w.suryo,0) - COALESCE(w.linked_suryo,0) as suryo
from (
select
  m.s_id
, m.row_no
, 1 + m.row_no as row_no_v
, m.hin_nm
, m.hosoku2 as hin_nm_ext1
, COALESCE(m.hosoku3,'')||' / '||COALESCE(m.hosoku4,'') as hin_nm_ext2
, (case when(:jucyu_flg = 1)then(m.jucyu_su)else(m.suryo)end) as suryo
, (select sum(l.suryo) from w_basv0210_pack_link l where m.s_id = l.s_id and m.row_no = l.row_no) as linked_suryo
from w_basv0210_h m
where m.s_id = :parent_s_id
) w
where 1 = 1
and (COALESCE(w.suryo,0) - COALESCE(w.linked_suryo,0)) != 0
order by row_no

[load_grdList_link]
select
  l.s_id
, l.pack_seq
, l.pack_seq_c
, l.row_no
, l.suryo
, 1 + l.pack_seq as pack_seq_v
, 1 + l.pack_seq_c as pack_seq_c_v
, 1 + l.row_no as row_no_v
, h42.han_name as konpo_keijyo_nm
, um.hin_nm
, um.hosoku2 as hin_nm_ext1
, COALESCE(um.hosoku3,'')||' / '||COALESCE(um.hosoku4,'') as hin_nm_ext2
from w_basv0210_pack_link l
left join w_basv0210_pack_m m on (m.s_id = l.s_id and m.pack_seq = l.pack_seq and m.pack_seq_c = l.pack_seq_c)
left join w_basv0210_pack h on (h.s_id = m.s_id and h.pack_seq = m.pack_seq)
left join m_hanyo h42 on (h42.mst_kbn = '42' and h42.han_cd = h.konpo_keijyo)
left join w_basv0210_h um on (um.s_id = l.s_id and um.row_no = l.row_no)
where l.s_id = :parent_s_id
order by pack_seq, pack_seq_c, row_no






[deleteLink]
delete from w_basv0210_pack_link
where s_id = :parent_s_id
and pack_seq = :w_pack_seq
and pack_seq_c = :w_pack_seq_c
and row_no = :w_row_no

[insertLink]
insert into w_basv0210_pack_link (
  s_id
, pack_seq
, pack_seq_c
, row_no
, suryo
, del_flg
, cre_id
, cre_time
, upd_id
, upd_time
)
select
  m.s_id
, :w_pack_seq
, :w_pack_seq_c
, m.row_no
, :w_suryo
, 0 --del_flg
, ':login_id' --cre_id
, now() --cre_time
, ':login_id' --upd_id
, now() --upd_time
from w_basv0210_h m
where m.s_id = :parent_s_id
and m.row_no = :w_row_no

[updatePack]
update w_basv0210_pack_m m set
  pack_net_weight = :w_pack_net_weight
, pack_gross_weight = :w_pack_gross_weight
, upd_id = ':login_id'
, upd_time = now()
where m.s_id = :parent_s_id
and m.pack_seq = :w_pack_seq
and m.pack_seq_c = :w_pack_seq_c




