存储过程具体如下,编译没有错误,test时运行到红色字体行就卡住了,大概三十分钟后会有结果,不知为何,请帮忙看下:
create or replace procedure BOM_CHECK_JOB_HANDLE(job_no in varchar2,
job_multi_flag out varchar2,
model_cfc_config_flag out varchar2,
part_no_cursor out types.ref_cursor,
bom_check_station out varchar2)
is station_num_temp varchar2(20);cursor job_info_cursor is select distinct msib2.segment1 xcvr from apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,apps.mtl_system_items_b msib2
Where we.wip_entity_id = wro.wip_entity_id And wro.operation_seq_num > 0 and wro.segment1=msib.segment1 and msib.organization_id=84 and we.PRIMARY_ITEM_ID=msib2.inventory_item_id and msib2.organization_id=84 and we.wip_entity_name=job_no;cursor partno_all_cursor is select a.* from bom_check a, job_master b where a.xcvr = b.xcvr
and a.model = b.model and (a.attribute1 = 'Y1' or a.attribute1 = 'Y2' or a.attribute1 = 'Y3')
and b.wip_dj =job_no
union
select * from bom_check where (attribute1 = 'Y1' or attribute1 = 'Y2' or attribute1 = 'Y3')
and xcvr in(select distinct msib2.segment1 xcvr from apps.wip_discrete_jobs_v we,
apps.wip_requirement_operations wro, apps.mtl_system_items_b msib, apps.mtl_system_items_b msib2 Where we.wip_entity_id =wro.wip_entity_id
And wro.operation_seq_num > 0 and wro.segment1 = msib.segment1
and msib.organization_id = 84 and we.PRIMARY_ITEM_ID =msib2.inventory_item_id
and msib2.organization_id = 84 and we.wip_entity_name =job_no );
cursor partno_oracle_define_cursor is select we.organization_id, we.wip_entity_name,
nvl(wro.segment1, msib.segment1) segment1,wro.operation_seq_num,wro.supply_subinventory,
wro.required_quantity,wro.quantity_issued, wro.quantity_per_assembly,jm.ato_item,
jm.model,jm.xcvr, msib.attribute8 from JOB_MASTER jm, apps.wip_discrete_jobs_v we,
apps.wip_requirement_operations wro, apps.mtl_system_items_b msib
Where we.wip_entity_id = wro.wip_entity_id And jm.org_code = we.organization_id
And jm.wip_dj = we.wip_entity_name And wro.operation_seq_num > 0 and wro.inventory_item_id = msib.inventory_item_id and msib.organization_id = 84 and we.wip_entity_name = job_no
Union
select we.organization_id,we.wip_entity_name,nvl(wro.segment1, msib.segment1) segment1,
wro.operation_seq_num, wro.supply_subinventory, wro.required_quantity,
wro.quantity_issued, wro.quantity_per_assembly,null ato_item, null model,
from apps.wip_discrete_jobs_v we, apps.wip_requirement_operations wro,
apps.mtl_system_items_b msib Where we.wip_entity_id =
wro.wip_entity_id And wro.operation_seq_num > 0 and wro.inventory_item_id =
msib.inventory_item_id and msib.organization_id = 84 and we.wip_entity_name = job_no
and not exists (select 1 from JOB_MASTER jm where jm.wip_dj = we.WIP_ENTITY_NAME
and jm.org_code = we.ORGANIZATION_ID); begin
-- Judge whether the job no is multiple with any other
open job_info_cursor;
if (job_info_cursor%notfound) then
job_multi_flag := 'false';
else
-- 该Job号在ERP系统中重复定义,需重新定义该Job号!
job_multi_flag := 'true';
--return;
end if;
close job_info_cursor ;
--Judge whether the model has been download to CFC system
open partno_all_cursor;
open partno_oracle_define_cursor;
if partno_all_cursor%notfound or partno_oracle_define_cursor%notfound then
-- 此JOB号对应的MODEL没有维护在系统中
model_cfc_config_flag := 'false';
return;
else
model_cfc_config_flag :='true';
open part_no_cursor for select distinct msib2.segment1 xcvr
from apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,apps.mtl_system_items_b msib2
Where we.wip_entity_id = wro.wip_entity_id And wro.operation_seq_num > 0
and wro.segment1=msib.segment1 and msib.organization_id=84
and we.PRIMARY_ITEM_ID=msib2.inventory_item_id and msib2.organization_id=84
and we.wip_entity_name=job_no;
end if;
-- Get the station count of bom checking
--open bom_check_station_cursor ;
close part_no_cursor;
close partno_oracle_define_cursor;
select count(*) count into bom_check_station from
(select distinct bom.ATTRIBUTE1
from JOB_MASTER jm,apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,bom_check bom
Where we.wip_entity_id = wro.wip_entity_id And jm.org_code = we.organization_id And jm.wip_dj = we.wip_entity_name
and wro.operation_seq_num>0 and wro.segment1=msib.segment1 and msib.organization_id=84
and we.wip_entity_name=job_no and bom.model=jm.MODEL
and bom.PART=wro.segment1 and (bom.attribute1='Y1' or bom.attribute1='Y2' or bom.attribute1='Y3')
Union
select distinct bom.ATTRIBUTE1
from apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,apps.mtl_system_items_b msib2,bom_check bom
Where we.wip_entity_id = wro.wip_entity_id And wro.operation_seq_num > 0
and wro.segment1=msib.segment1 and msib.organization_id=84
and we.PRIMARY_ITEM_ID=msib2.inventory_item_id and msib2.organization_id=84
and we.wip_entity_name=job_no and bom.model=msib2.segment1
and bom.PART=wro.segment1 and
(bom.attribute1='Y1' or bom.attribute1='Y2' or bom.attribute1='Y3')) station_table_temp;
/* if bom_check_station_cursor%found then
--bom_check_station := bom_check_station_cursor.count;
end if;*/
-- fetch bom_check_station_cursor into bom_check_station;
end ;
create or replace procedure BOM_CHECK_JOB_HANDLE(job_no in varchar2,
job_multi_flag out varchar2,
model_cfc_config_flag out varchar2,
part_no_cursor out types.ref_cursor,
bom_check_station out varchar2)
is station_num_temp varchar2(20);cursor job_info_cursor is select distinct msib2.segment1 xcvr from apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,apps.mtl_system_items_b msib2
Where we.wip_entity_id = wro.wip_entity_id And wro.operation_seq_num > 0 and wro.segment1=msib.segment1 and msib.organization_id=84 and we.PRIMARY_ITEM_ID=msib2.inventory_item_id and msib2.organization_id=84 and we.wip_entity_name=job_no;cursor partno_all_cursor is select a.* from bom_check a, job_master b where a.xcvr = b.xcvr
and a.model = b.model and (a.attribute1 = 'Y1' or a.attribute1 = 'Y2' or a.attribute1 = 'Y3')
and b.wip_dj =job_no
union
select * from bom_check where (attribute1 = 'Y1' or attribute1 = 'Y2' or attribute1 = 'Y3')
and xcvr in(select distinct msib2.segment1 xcvr from apps.wip_discrete_jobs_v we,
apps.wip_requirement_operations wro, apps.mtl_system_items_b msib, apps.mtl_system_items_b msib2 Where we.wip_entity_id =wro.wip_entity_id
And wro.operation_seq_num > 0 and wro.segment1 = msib.segment1
and msib.organization_id = 84 and we.PRIMARY_ITEM_ID =msib2.inventory_item_id
and msib2.organization_id = 84 and we.wip_entity_name =job_no );
cursor partno_oracle_define_cursor is select we.organization_id, we.wip_entity_name,
nvl(wro.segment1, msib.segment1) segment1,wro.operation_seq_num,wro.supply_subinventory,
wro.required_quantity,wro.quantity_issued, wro.quantity_per_assembly,jm.ato_item,
jm.model,jm.xcvr, msib.attribute8 from JOB_MASTER jm, apps.wip_discrete_jobs_v we,
apps.wip_requirement_operations wro, apps.mtl_system_items_b msib
Where we.wip_entity_id = wro.wip_entity_id And jm.org_code = we.organization_id
And jm.wip_dj = we.wip_entity_name And wro.operation_seq_num > 0 and wro.inventory_item_id = msib.inventory_item_id and msib.organization_id = 84 and we.wip_entity_name = job_no
Union
select we.organization_id,we.wip_entity_name,nvl(wro.segment1, msib.segment1) segment1,
wro.operation_seq_num, wro.supply_subinventory, wro.required_quantity,
wro.quantity_issued, wro.quantity_per_assembly,null ato_item, null model,
from apps.wip_discrete_jobs_v we, apps.wip_requirement_operations wro,
apps.mtl_system_items_b msib Where we.wip_entity_id =
wro.wip_entity_id And wro.operation_seq_num > 0 and wro.inventory_item_id =
msib.inventory_item_id and msib.organization_id = 84 and we.wip_entity_name = job_no
and not exists (select 1 from JOB_MASTER jm where jm.wip_dj = we.WIP_ENTITY_NAME
and jm.org_code = we.ORGANIZATION_ID); begin
-- Judge whether the job no is multiple with any other
open job_info_cursor;
if (job_info_cursor%notfound) then
job_multi_flag := 'false';
else
-- 该Job号在ERP系统中重复定义,需重新定义该Job号!
job_multi_flag := 'true';
--return;
end if;
close job_info_cursor ;
--Judge whether the model has been download to CFC system
open partno_all_cursor;
open partno_oracle_define_cursor;
if partno_all_cursor%notfound or partno_oracle_define_cursor%notfound then
-- 此JOB号对应的MODEL没有维护在系统中
model_cfc_config_flag := 'false';
return;
else
model_cfc_config_flag :='true';
open part_no_cursor for select distinct msib2.segment1 xcvr
from apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,apps.mtl_system_items_b msib2
Where we.wip_entity_id = wro.wip_entity_id And wro.operation_seq_num > 0
and wro.segment1=msib.segment1 and msib.organization_id=84
and we.PRIMARY_ITEM_ID=msib2.inventory_item_id and msib2.organization_id=84
and we.wip_entity_name=job_no;
end if;
-- Get the station count of bom checking
--open bom_check_station_cursor ;
close part_no_cursor;
close partno_oracle_define_cursor;
select count(*) count into bom_check_station from
(select distinct bom.ATTRIBUTE1
from JOB_MASTER jm,apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,bom_check bom
Where we.wip_entity_id = wro.wip_entity_id And jm.org_code = we.organization_id And jm.wip_dj = we.wip_entity_name
and wro.operation_seq_num>0 and wro.segment1=msib.segment1 and msib.organization_id=84
and we.wip_entity_name=job_no and bom.model=jm.MODEL
and bom.PART=wro.segment1 and (bom.attribute1='Y1' or bom.attribute1='Y2' or bom.attribute1='Y3')
Union
select distinct bom.ATTRIBUTE1
from apps.wip_discrete_jobs_v we,apps.wip_requirement_operations wro,apps.mtl_system_items_b msib,apps.mtl_system_items_b msib2,bom_check bom
Where we.wip_entity_id = wro.wip_entity_id And wro.operation_seq_num > 0
and wro.segment1=msib.segment1 and msib.organization_id=84
and we.PRIMARY_ITEM_ID=msib2.inventory_item_id and msib2.organization_id=84
and we.wip_entity_name=job_no and bom.model=msib2.segment1
and bom.PART=wro.segment1 and
(bom.attribute1='Y1' or bom.attribute1='Y2' or bom.attribute1='Y3')) station_table_temp;
/* if bom_check_station_cursor%found then
--bom_check_station := bom_check_station_cursor.count;
end if;*/
-- fetch bom_check_station_cursor into bom_check_station;
end ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货