有以下疑问，PUber们帮忙看看。谢谢了。
1 同样的程序，在另外一个组织中，可以执行，在目前组织中不可以执行 （不同的组织，仅仅组织ID号不同而已）
2 这个程序中的cc1是什么东东，也没见有什么声明的；
3 bom_structure_tl_all_es_c和bom_structure_tl_all中居然没有数据，会不会是这个里面的问题导致程序无法运行的。
这个是一个BOM的东东。运行的时候提示错误。
ORA-01436: CONNECT BY loop in user data
ORA-06512: at line 153
程序如下：
Declare
v_assembly_item_id number;
v_max
number;
v_level
number;
v_sub
number;
v_num
number;
v_seg
varchar2(40);
v_pseg
varchar2(40);
v_cseg
varchar2(40);
Cursor c_bom_strct(v_ll number) is
select distinct ba.segment1,
ba.psegment1,
ba.csegment1,
ba.levela,
ba.extended_quantity
from bom_structure_tl_all_es_c ba
where ba.levela = v_ll;
Cursor bom_level is
select distinct t.levela from bom_structure_tl_all_es_c t;
Cursor cur_assembly is
select inventory_item_id, segment1
from mtl_system_items
where item_type = 'FG' and organization_id = 33
order by segment1;
begin
begin
delete from BOM_STRUCTURE_TL_ES_C;
commit;
end;
begin
insert into BOM_STRUCTURE_TL_ES_C
(assembly_item_id,
psegment1,
component_item_id,
component_quantity,
component_yield_factor,
csegment1,
item_type,
EFFECTIVE_DATE,
EXTENDED_QUANTITY)
select bbom.assembly_item_id,
pmsi.segment1 psegment1,
bic.component_item_id,
bic.component_quantity,
bic.component_yield_factor,
cmsi.segment1 csegment1,
cmsi.item_type,
trunc(bic.effectivity_date) effective_date,
BIC.COMPONENT_QUANTITY /
decode(BIC.COMPONENT_YIELD_FACTOR,
0,
1,
BIC.COMPONENT_YIELD_FACTOR) extended_quantity
from mtl_system_items pmsi,
mtl_system_items cmsi,
bom_bill_of_materialsbbom,
bom_inventory_components bic
where pmsi.organization_id = 33 and cmsi.organization_id = 33 and
bbom.organization_id = 33 and
pmsi.inventory_item_id = bbom.assembly_item_id and
bbom.bill_sequence_id = bic.bill_sequence_id and
cmsi.inventory_item_id = bic.component_item_id and
trunc(sysdate) >= bic.effectivity_date and
trunc(sysdate)v_level then
update bom_structure_tl_all_es_c t
set t.pextended_quantity = v_num
where t.segment1 = v_seg and t.psegment1 = v_cseg and
t.levela = v_sub;
end if;
end loop;
commit;
end loop;
commit;
end;
end;
/
|
