Hello,
I'm get a Invalid Rowid Error while trying to Run this procedure. I run this procedure every 4 hours but the error only occurs at 2:30am . There is no other job running at that time against this database. Also the source that it selects from has only 1 column which is Not Null. Can you please provide some input as to what I can do to fix it ?
Thanks for the help.
PROCEDURE refresh_sku_part_qty IS
cursor sku_cursor is
select rtrim(sku_num) sku_num
from active_sku_num;
sku_val sku_cursor%ROWTYPE;
l_nme VARCHAR2(31) := 'refresh_sku_part_qty';
l_loc number := -1;
v_error Varchar2(255);
BEGIN
truncate_table('sku_part_qty');
Begin
for sku_val in sku_cursor
loop
Begin
l_loc:= -2;
l_nme:= 'inside loop :';
set_sku_part_qty(sku_val.sku_num);
commit;
Exception
When others then
dbms_output.put_line(substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250));
dbms_output.put_line('Sku num: ' ||sku_val.sku_num);
v_error:= substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250);
insert into chk_sku_part_qty values (substr(sku_val.sku_num || v_error,1,250) );
commit;
end;
end loop;
Exception
When others then
dbms_output.put_line(substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250));
dbms_output.put_line('Sku num: ' ||sku_val.sku_num);
v_error:= substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250);
insert into chk_sku_part_qty values (substr(sku_val.sku_num || v_error,1,250) );
commit;
end;
END;
****************************************
PROCEDURE set_sku_part_qty(in_sku_num IN VARCHAR2 ) IS
l_max number;
l_cnt number;
l_nme VARCHAR2(31) := 'set_sku_part_qty';
l_loc number := 0;
v_error Varchar2(255);
BEGIN
l_loc:= 1;
truncate_table('bom_parts');
truncate_table('bom_parts1');
--dbms_output.put_line('Process SKU : '||in_sku_num);
begin
l_loc:= 2;
insert into bom_parts
select item, comp_item, level, comp_qty
from glovia_bom
where comp_qty > 0
start with item = in_sku_num
connect by prior comp_item = item
group by item, comp_item, level, comp_qty;
insert into bom_parts1
select * from bom_parts;
Exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('SKU number : '||in_sku_num);
when others then
dbms_output.put_line(substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250));
v_error:= substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250);
insert into chk_sku_part_qty values (substr(in_sku_num || v_error,1,250) );
commit;
end;
Begin
select max(l_num)
into l_max
from bom_parts;
l_loc:= 3;
if (l_max > 1) then
for c_val in 2..l_max
loop
update bom_parts set qty = qty * get_base_qty0(item, c_val)
where l_num = c_val;
update bom_parts1 set qty = qty * get_base_qty1(item, c_val)
where l_num = c_val;
end loop;
end if;
Exception
when others then
dbms_output.put_line(substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250));
v_error:= substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250);
insert into chk_sku_part_qty values (substr(in_sku_num || v_error,1,250) );
commit;
end;
-- Clean up existing records
Begin
l_loc:= 4;
delete from sku_part_qty
where sku_num = in_sku_num;
for bp_val in (
select comp_item, sum(qty) qty
from bom_parts
group by comp_item
having sum(qty) > 0 ) -- Reverting 03-09-02 AD
loop
insert into sku_part_qty values (in_sku_num, bp_val.comp_item, bp_val.qty);
end loop;
Exception
when others then
dbms_output.put_line(substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250));
v_error:= substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250);
insert into chk_sku_part_qty values (substr(in_sku_num || v_error,1,250) );
commit;
end;
Exception
when others then
dbms_output.put_line(substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250));
v_error:= substr('Error '||l_nme||l_loc||' '||sqlerrm,1,250);
insert into chk_sku_part_qty values (substr(in_sku_num || v_error,1,250) );
commit;
END;
