[ ] * |
MR_ORDER_TREE, :order_id |
ID , |
item_id |
ID , order_id |
item_ref |
ID |
kts_item_id |
ID |
item_qty |
|
is_item_buy |
|
item_position |
(item_ref, kts_item_id) . , , .MR_ORDER_TREE_PREV.MR_ORDER_TREE_COMP, :stat |
: -1 ( ) 0 1 2 3 ( - ) 4 |
comm |
, |
MR_ORDER_TREE_COMP. , item_id ( + 1) item_id . , .select nvl(max(item_id), 0) + 1
into v_id
from mr_order_tree_prev t
where t.order_id = p_order_id;
insert into MR_ORDER_TREE_COMP (ORDER_ID, ITEM_ID, KTS_ITEM_ID, ITEM_QTY, IS_ITEM_BUY, IS_ADD_WORK, ITEM_POSITION, N_GROUP, T_LEVEL, STAT, COMM)
values (p_order_id, -1, null, 0, 0, 0, 0, 0, 0, -1, ' ');
insert into MR_ORDER_TREE_COMP (ORDER_ID,
ITEM_ID,
ITEM_REF,
KTS_ITEM_ID,
KTS_ITEM_REF,
ITEM_QTY,
IS_ITEM_BUY,
IS_ADD_WORK,
ITEM_POSITION,
N_GROUP,
STAT,
COMM)
select p.order_id,
p.item_id,
nvl(p.item_ref, -1),
p.kts_item_id,
p.kts_item_ref,
p.item_qty,
p.is_item_buy,
p.is_add_work,
p.item_position,
p.n_group,
0,
''
from mr_order_tree_prev p
where p.order_id = p_order_id;
insert into MR_ORDER_TREE_COMP (ORDER_ID,
ITEM_ID,
ITEM_REF,
KTS_ITEM_ID,
KTS_ITEM_REF,
ITEM_QTY,
IS_ITEM_BUY,
IS_ADD_WORK,
ITEM_POSITION,
N_GROUP,
STAT,
COMM)
select p.order_id,
p.item_id + v_id,
case
when p.item_ref is null
then -1
else p.item_ref + v_id
end,
p.kts_item_id,
p.kts_item_ref,
p.item_qty,
p.is_item_buy,
p.is_add_work,
p.item_position,
p.n_group,
1,
''
from mr_order_tree p
where p.order_id = p_order_id;
for rec in (select item_id,
level lev
from (select *
from mr_order_tree_comp
where order_id = p_order_id)
connect by prior item_id = item_ref
start with item_id = -1)
loop
update mr_order_tree_comp c
set c.t_level = rec.lev
where c.order_id = p_order_id
and c.item_id = rec.item_id;
end loop;mr_order_tree_comp , . , , . procedure save_tree_stat(p_order in number)
is
begin
select TREE_BC_STAT_ROW(c.order_id, c.item_id, c.item_ref, c.kts_item_id, c.kts_item_ref)
bulk collect into tree_before_calc
from mr_order_tree_comp c
where c.order_id = p_order;
end save_tree_stat;select max(t_level)
into v_max_lvl
from mr_order_tree_comp
where order_id = p_order_id;kts_item_id item_ref, 1 0 0 1. , . procedure add_to_rdy (p_item in number,
p_order in number)
is
begin
item_ready_list.extend;
item_ready_list(item_ready_list.last) := tree_rdy_list_row(p_order, p_item);
end add_to_rdy;function item_rdy(p_item in number, p_order in number) return number<>
for i in 1..v_max_lvl
loop
<>
for rh in (select c.*
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.t_level = i)
loop
<>
for rl in (select c.*
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.item_ref = rh.item_id
and c.stat in (0, 1)
order by c.stat)
loop
if (item_rdy(rl.item_id, rl.order_id) = 0) then
if (rl.stat = 0) then
select count(*)
into v_cnt
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.item_ref = rh.item_id
and c.kts_item_id = rl.kts_item_id
and c.stat = 1;
case
when (v_cnt = 1) then
select c.item_id
into v_item
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.item_ref = rh.item_id
and c.kts_item_id = rl.kts_item_id
and c.stat = 1;
update mr_order_tree_comp c
set c.item_ref = v_item
where c.item_ref = rl.item_id
and c.order_id = p_order_id;
update mr_order_tree_comp c
set c.stat = 4
where c.item_id = v_item
and c.order_id = p_order_id;
diff_items(p_order_id, rl.item_id, v_item);
delete mr_order_tree_comp c
where c.item_id = rl.item_id
and c.order_id = p_order_id;
add_to_rdy(rl.item_id, rl.order_id);
add_to_rdy(v_item, rl.order_id);
end case;
end if; (rl.stat = 1) .diff_items. , , , - .kts_item_id . , . , .CASE.when (v_cnt = 0) then
select count(*)
into v_cnt
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.item_ref = rh.item_id
and c.stat = 1
and not exists (select 1
from table(item_ready_list) a
where a.order_id = c.order_id
and a.item_id = c.item_id); if (v_cnt = 1) then
select c.item_id, c.kts_item_id
into v_item, v_kts
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.item_ref = rh.item_id
and c.stat = 1
and not exists (select 1
from table(item_ready_list) a
where a.order_id = c.order_id
and a.item_id = c.item_id); if (is_item_comp(v_item, p_order_id) = is_item_comp(rl.item_id, p_order_id)) then
update mr_order_tree_comp c
set c.item_ref = v_item
where c.item_ref = rl.item_id
and c.order_id = p_order_id;
add_to_rdy(rl.item_id, rl.order_id);
add_to_rdy(v_item, rl.order_id);
end if;
end if;like_degree, lperc.if (v_cnt > 1) then
begin
select item_id, kts_item_id, max_lperc
into v_item, v_kts, v_perc
from (select c.item_id,
c.kts_item_id,
max(like_degree(rl.item_id, c.item_id, c.order_id)) max_lperc
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.item_ref = rh.item_id
and c.stat = 1
and not exists (select 1
from table(item_ready_list) a
where a.order_id = c.order_id
and a.item_id = c.item_id)
and is_item_comp(c.item_id, p_order_id) = (select is_item_comp(rl.item_id, p_order_id) from dual)
group by c.item_id, c.kts_item_id
order by max_lperc desc)
where rownum < 2;
if (v_perc >= lperc) then
update mr_order_tree_comp c
set c.item_ref = v_item
where c.item_ref = rl.item_id
and c.order_id = p_order_id;
update mr_order_tree_comp c
set c.comm = '. ' || kts_pack.item_code(v_kts) || ' (' || to_char(v_perc) || '%)'
where c.item_id = rl.item_id
and c.order_id = p_order_id;
add_to_rdy(rl.item_id, rl.order_id);
add_to_rdy(v_item, rl.order_id);
end if;
end;
end if;
<>
for rs in (select *
from mr_order_tree_comp c
where c.order_id = p_order_id
and c.stat in (0,1))
loop
<>
for rb in (select *
from (select *
from mr_order_tree_comp c
where c.order_id = p_order_id) t
connect by prior t.item_ref = t.item_id
start with t.item_id = rs.item_id)
loop
select count(*)
into v_cnt
from mr_order_tree_comp c
where c.item_ref = rb.item_id
and c.kts_item_id = rs.kts_item_id
and c.stat in (1,0)
and c.stat != rs.stat;
if (v_cnt = 1) then
select c.item_id
into v_item
from mr_order_tree_comp c
where c.item_ref = rb.item_id
and c.kts_item_id = rs.kts_item_id
and c.stat in (1,0)
and c.stat != rs.stat;
if (rs.stat = 0) then
update mr_order_tree_comp c
set c.stat = 4
where c.order_id = p_order_id
and c.item_id = v_item;
diff_items(p_order_id, rs.item_id, v_item);
update mr_order_tree_comp c
set c.item_ref = v_item
where c.order_id = p_order_id
and c.item_ref = rs.item_id;
delete mr_order_tree_comp
where order_id = p_order_id
and item_id = rs.item_id;
end if;
if (rs.stat = 1) then
update mr_order_tree_comp c
set c.stat = 4
where c.order_id = p_order_id
and c.item_id = rs.item_id;
diff_items(p_order_id, rs.item_id, v_item);
update mr_order_tree_comp c
set c.item_ref = rs.item_id
where c.order_id = p_order_id
and c.item_ref = v_item;
delete mr_order_tree_comp
where order_id = p_order_id
and item_id = v_item;
end if;
continue items;
end if;
end loop branch;
end loop items; item_ref. tree_before_calc, mr_order_tree_comp.
