Script to upload EAM Routing, Sequence, Resources & BOM Components
Templates:
(i) Routing
(ii) BOM Components
1- BOM Routing, Shifting & Resource
a- Create table to hold staging data
CREATE TABLE APPS.XXX_BOM_OP_ROUTING_STG
(
ITEM_NAME VARCHAR2(30 BYTE),
REVISION_NO NUMBER(2),
OPERATION_SEQUENCE NUMBER(3),
DEPARTMENT VARCHAR2(30 BYTE),
OPERATION_DESCRIPTION VARCHAR2(240 BYTE),
RESOURCE_SEQUENCE NUMBER(3),
RESOURCE_CODE VARCHAR2(20 BYTE),
USAGE_RATE NUMBER(10,2),
ASSIGNED_UNITS NUMBER(5),
SCHEDULE_FLAG VARCHAR2(5 BYTE),
SCHEDULE_SEQ_NUM NUMBER(2),
ROUT_VERIFY_FLAG VARCHAR2(1 BYTE),
SEQ_VERIFY_FLAG VARCHAR2(1 BYTE),
RES_VERIFY_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(2500 BYTE)
);
b- Create a procedure to process staging data into interface table.
CREATE OR REPLACE procedure APPS.xxx_bom_routing_api is
l_organization_id number(5);
l_rout_verify_flag CHAR(1);
l_seq_verify_flag CHAR(1);
l_res_verify_flag CHAR(1);
-- l_bom_verify_flag char(1);
l_error_message varchar2(2500);
l_inventory_item_id number(20);
l_department_id number(10);
l_resource_id number(10);
l_department_code varchar2(20);
l_resource_code varchar2(20);
l_schedule_flag number(3);
l_res_uom varchar(10);
l_link_resource number(10);
---Routing Cursor
cursor c_rout is select distinct item_name,revision_no from xxx_bom_op_routing_stg;
---Operation Sequence Cursor
cursor c_seq (p_item_name varchar2) is select distinct item_name,revision_no,operation_sequence,department
from xxx_bom_op_routing_stg where item_name = p_item_name order by operation_sequence;
---Resource Cursor
cursor c_res (p_item_name varchar2,p_operation_sequence number) is select * from xxx_bom_op_routing_stg
where item_name = p_item_name and operation_sequence = p_operation_sequence order by operation_sequence,resource_sequence;
---Bom Cursor
cursor c_bom (p_item_name varchar2,p_operation_sequence number) is select * from xxx_maintenance_bom_stg
where item_name = p_item_name and operation_sequence = p_operation_sequence order by operation_sequence, item_sequence;
begin
for rout1 in c_rout loop
l_rout_verify_flag := 'Y';
l_seq_verify_flag := 'Y';
l_res_verify_flag := 'Y';
--l_bom_verify_flag := 'Y';
l_error_message := null;
begin
/*select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Spares Store';*/
l_organization_id:='127';
/*exception when others then
l_rout_verify_flag := 'N';
l_error_message := 'Organization is not Valid';*/
end;
begin
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where
--upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4)
segment1= trim(rout1.item_name)
and organization_id = l_organization_id;
exception when others then
l_rout_verify_flag := 'N';
l_error_message := l_error_message||'Item Name is not Valid';
end;
IF l_rout_verify_flag <> 'N' then
savepoint a1;
---ROUTING AREA STARTS
begin
insert into bom_op_routings_interface
(assembly_item_id,
process_revision,
process_flag,
transaction_type,
routing_type,
organization_id
)
values
(l_inventory_item_id,
Decode(rout1.revision_no,0,null,rout1.revision_no),
1, --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'CREATE',
1,----1) manufacturing, 2) engineering
l_organization_id
);
update xxx_bom_op_routing_stg set rout_verify_flag='Y' where item_name = ROUT1.item_name ;
exception
when others then
l_error_message := sqlerrm;
update xxx_bom_op_routing_stg set rout_verify_flag = 'N', error_message = l_error_message
where item_name = rout1.item_name;
goto next_rout;
end;
---ROUTING AREA ENDS
---SEQUENCE AREA STARTS
for seq1 in c_seq (rout1.item_name)
loop
begin
select department_id,department_code
into l_department_id,l_department_code
from bom_departments
where upper(department_code) = upper(trim(seq1.department))
and organization_id = l_organization_id ;
exception
when others then
l_error_message := l_error_message||'Department Code is not valid';
l_seq_verify_flag := 'N';
end;
if trim(seq1.operation_sequence) is null then
l_error_message := l_error_message||'Operation sequence should not be null';
l_seq_verify_flag := 'N';
end if;
if l_seq_verify_flag <> 'N' then
begin
insert into bom_op_sequences_interface
(
assembly_item_id,
operation_seq_num,
department_id,
department_code,
process_flag,
transaction_type,
organization_id,
effectivity_date
)
values
(
l_inventory_item_id,
trim(seq1.operation_sequence),
l_department_id,
l_department_code,
1,
'CREATE',
l_organization_id,
sysdate
);
update xxx_bom_op_routing_stg
set seq_verify_flag = 'Y'
where item_name = seq1.item_name
and operation_sequence = seq1.operation_sequence;
exception
when others then
rollback to a1;
l_error_message := 'Op Sequence Error.'||SQLERRM;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N',
error_message = l_error_message
where item_name = SEQ1.item_name
and operation_sequence = SEQ1.operation_sequence;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = SEQ1.item_name ;
goto next_rout;
end;
----- SEQUENCE AREA ENDS
----- RESOURCES AREA STARTS
for res1 in c_res (seq1.item_name,seq1.operation_sequence)
loop
-- GETTING RESOURCE CODE, ID & UOM FROM BOM_RESOURCES
begin
select resource_id,resource_code,unit_of_measure
into l_resource_id,l_resource_code,l_res_uom
from bom_resources
where upper(resource_code) = trim(upper(res1.resource_code))
and organization_id = l_organization_id ;
exception
when others then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource code is not valid';
end;
-- GETTING RESOURCE ID INTO A VARIABLE
begin
select resource_id
into l_link_resource
from bom_department_resources_v
where organization_id = l_organization_id
and department_id = l_department_id
and resource_id = l_resource_id ;
exception
when others then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource code is not Linked with Department';
end;
if ((res1.resource_sequence is null) or (res1.usage_rate is null) or (res1.assigned_units is null)) then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource Seq or usage rate or assigned units is null';
end if;
If (upper(trim(res1.schedule_flag)) = 'YES' and l_res_uom <> 'HR') Then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'This Resource cannot be Schedule';
elsif upper(trim(res1.schedule_flag)) = 'YES'then
l_schedule_flag := 1 ;
else
l_schedule_flag := null;
end if;
IF l_res_verify_flag <> 'N' then
begin
insert into bom_op_resources_interface
(
resource_seq_num,
resource_id,
resource_code,
usage_rate_or_amount, --basis_type ,
assigned_units,--schedule_flag,autocharge_type,
assembly_item_id,
operation_seq_num, --operation_sequence_id,
process_flag,
transaction_type,
effectivity_date,
organization_id ,
schedule_flag,
schedule_seq_num
)
values
(
trim(res1.resource_sequence),
l_resource_id,
l_resource_code,
trim(res1.usage_rate), --1,
trim(res1.assigned_units), -- 1, 2,
l_inventory_item_id,
trim(seq1.operation_sequence), --2346216 ,
1,
'CREATE',
sysdate,
l_organization_id,
l_schedule_flag,
trim(res1.schedule_seq_num)
);
update xxx_bom_op_routing_stg
set res_verify_flag = 'Y'
where item_name = RES1.item_name
and department = res1.department
and resource_code = res1.resource_code;
exception
when others then
rollback to a1;
l_error_message := l_error_message||'Op Resource Sequence Error.'||sqlerrm;
update xxx_bom_op_routing_stg
set res_verify_flag = 'N',
error_message = l_error_message
where item_name = RES1.item_name
and department = RES1.department
and resource_code = RES1.resource_code;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N'
where item_name = RES1.item_name
and department = RES1.department;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = RES1.item_name;
goto next_rout;
end;
else
rollback to a1;
update xxx_bom_op_routing_stg
set res_verify_flag = 'N',
error_message = l_error_message
where item_name = res1.item_name
and department = res1.department
and resource_code = res1.resource_code;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N'
where item_name = res1.item_name
and department = res1.department;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = RES1.item_name;
goto next_rout;
end if; --if_res_verify_flag
end loop ;
----- RESOURCES AREA ENDS **********************
else
rollback to savepoint a1;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N',
error_message = l_error_message
where item_name = seq1.item_name
and operation_sequence = seq1.operation_sequence;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = SEQ1.item_name;
goto next_rout;
end if; --if_seq_verify_flag
end loop ;
else
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N',
error_message = l_error_message
where item_name = ROUT1.item_name;
end if; ---if_rout_verify_flag
--next_rout --(This will be in << >>)
commit;
<<next_rout>>
begin
continue;
end next_rout;
end loop ;
END xxx_bom_routing_api ;
/
Templates:
(i) Routing
(ii) BOM Components
(iii) Activity Association
1- BOM Routing, Shifting & Resource
a- Create table to hold staging data
CREATE TABLE APPS.XXX_BOM_OP_ROUTING_STG
(
ITEM_NAME VARCHAR2(30 BYTE),
REVISION_NO NUMBER(2),
OPERATION_SEQUENCE NUMBER(3),
DEPARTMENT VARCHAR2(30 BYTE),
OPERATION_DESCRIPTION VARCHAR2(240 BYTE),
RESOURCE_SEQUENCE NUMBER(3),
RESOURCE_CODE VARCHAR2(20 BYTE),
USAGE_RATE NUMBER(10,2),
ASSIGNED_UNITS NUMBER(5),
SCHEDULE_FLAG VARCHAR2(5 BYTE),
SCHEDULE_SEQ_NUM NUMBER(2),
ROUT_VERIFY_FLAG VARCHAR2(1 BYTE),
SEQ_VERIFY_FLAG VARCHAR2(1 BYTE),
RES_VERIFY_FLAG VARCHAR2(1 BYTE),
ERROR_MESSAGE VARCHAR2(2500 BYTE)
);
b- Create a procedure to process staging data into interface table.
CREATE OR REPLACE procedure APPS.xxx_bom_routing_api is
l_organization_id number(5);
l_rout_verify_flag CHAR(1);
l_seq_verify_flag CHAR(1);
l_res_verify_flag CHAR(1);
-- l_bom_verify_flag char(1);
l_error_message varchar2(2500);
l_inventory_item_id number(20);
l_department_id number(10);
l_resource_id number(10);
l_department_code varchar2(20);
l_resource_code varchar2(20);
l_schedule_flag number(3);
l_res_uom varchar(10);
l_link_resource number(10);
---Routing Cursor
cursor c_rout is select distinct item_name,revision_no from xxx_bom_op_routing_stg;
---Operation Sequence Cursor
cursor c_seq (p_item_name varchar2) is select distinct item_name,revision_no,operation_sequence,department
from xxx_bom_op_routing_stg where item_name = p_item_name order by operation_sequence;
---Resource Cursor
cursor c_res (p_item_name varchar2,p_operation_sequence number) is select * from xxx_bom_op_routing_stg
where item_name = p_item_name and operation_sequence = p_operation_sequence order by operation_sequence,resource_sequence;
---Bom Cursor
cursor c_bom (p_item_name varchar2,p_operation_sequence number) is select * from xxx_maintenance_bom_stg
where item_name = p_item_name and operation_sequence = p_operation_sequence order by operation_sequence, item_sequence;
begin
for rout1 in c_rout loop
l_rout_verify_flag := 'Y';
l_seq_verify_flag := 'Y';
l_res_verify_flag := 'Y';
--l_bom_verify_flag := 'Y';
l_error_message := null;
begin
/*select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Spares Store';*/
l_organization_id:='127';
/*exception when others then
l_rout_verify_flag := 'N';
l_error_message := 'Organization is not Valid';*/
end;
begin
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where
--upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4)
segment1= trim(rout1.item_name)
and organization_id = l_organization_id;
exception when others then
l_rout_verify_flag := 'N';
l_error_message := l_error_message||'Item Name is not Valid';
end;
IF l_rout_verify_flag <> 'N' then
savepoint a1;
---ROUTING AREA STARTS
begin
insert into bom_op_routings_interface
(assembly_item_id,
process_revision,
process_flag,
transaction_type,
routing_type,
organization_id
)
values
(l_inventory_item_id,
Decode(rout1.revision_no,0,null,rout1.revision_no),
1, --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
'CREATE',
1,----1) manufacturing, 2) engineering
l_organization_id
);
update xxx_bom_op_routing_stg set rout_verify_flag='Y' where item_name = ROUT1.item_name ;
exception
when others then
l_error_message := sqlerrm;
update xxx_bom_op_routing_stg set rout_verify_flag = 'N', error_message = l_error_message
where item_name = rout1.item_name;
goto next_rout;
end;
---ROUTING AREA ENDS
---SEQUENCE AREA STARTS
for seq1 in c_seq (rout1.item_name)
loop
begin
select department_id,department_code
into l_department_id,l_department_code
from bom_departments
where upper(department_code) = upper(trim(seq1.department))
and organization_id = l_organization_id ;
exception
when others then
l_error_message := l_error_message||'Department Code is not valid';
l_seq_verify_flag := 'N';
end;
if trim(seq1.operation_sequence) is null then
l_error_message := l_error_message||'Operation sequence should not be null';
l_seq_verify_flag := 'N';
end if;
if l_seq_verify_flag <> 'N' then
begin
insert into bom_op_sequences_interface
(
assembly_item_id,
operation_seq_num,
department_id,
department_code,
process_flag,
transaction_type,
organization_id,
effectivity_date
)
values
(
l_inventory_item_id,
trim(seq1.operation_sequence),
l_department_id,
l_department_code,
1,
'CREATE',
l_organization_id,
sysdate
);
update xxx_bom_op_routing_stg
set seq_verify_flag = 'Y'
where item_name = seq1.item_name
and operation_sequence = seq1.operation_sequence;
exception
when others then
rollback to a1;
l_error_message := 'Op Sequence Error.'||SQLERRM;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N',
error_message = l_error_message
where item_name = SEQ1.item_name
and operation_sequence = SEQ1.operation_sequence;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = SEQ1.item_name ;
goto next_rout;
end;
----- SEQUENCE AREA ENDS
----- RESOURCES AREA STARTS
for res1 in c_res (seq1.item_name,seq1.operation_sequence)
loop
-- GETTING RESOURCE CODE, ID & UOM FROM BOM_RESOURCES
begin
select resource_id,resource_code,unit_of_measure
into l_resource_id,l_resource_code,l_res_uom
from bom_resources
where upper(resource_code) = trim(upper(res1.resource_code))
and organization_id = l_organization_id ;
exception
when others then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource code is not valid';
end;
-- GETTING RESOURCE ID INTO A VARIABLE
begin
select resource_id
into l_link_resource
from bom_department_resources_v
where organization_id = l_organization_id
and department_id = l_department_id
and resource_id = l_resource_id ;
exception
when others then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource code is not Linked with Department';
end;
if ((res1.resource_sequence is null) or (res1.usage_rate is null) or (res1.assigned_units is null)) then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'Resource Seq or usage rate or assigned units is null';
end if;
If (upper(trim(res1.schedule_flag)) = 'YES' and l_res_uom <> 'HR') Then
l_res_verify_flag := 'N';
l_error_message := l_error_message||'This Resource cannot be Schedule';
elsif upper(trim(res1.schedule_flag)) = 'YES'then
l_schedule_flag := 1 ;
else
l_schedule_flag := null;
end if;
IF l_res_verify_flag <> 'N' then
begin
insert into bom_op_resources_interface
(
resource_seq_num,
resource_id,
resource_code,
usage_rate_or_amount, --basis_type ,
assigned_units,--schedule_flag,autocharge_type,
assembly_item_id,
operation_seq_num, --operation_sequence_id,
process_flag,
transaction_type,
effectivity_date,
organization_id ,
schedule_flag,
schedule_seq_num
)
values
(
trim(res1.resource_sequence),
l_resource_id,
l_resource_code,
trim(res1.usage_rate), --1,
trim(res1.assigned_units), -- 1, 2,
l_inventory_item_id,
trim(seq1.operation_sequence), --2346216 ,
1,
'CREATE',
sysdate,
l_organization_id,
l_schedule_flag,
trim(res1.schedule_seq_num)
);
update xxx_bom_op_routing_stg
set res_verify_flag = 'Y'
where item_name = RES1.item_name
and department = res1.department
and resource_code = res1.resource_code;
exception
when others then
rollback to a1;
l_error_message := l_error_message||'Op Resource Sequence Error.'||sqlerrm;
update xxx_bom_op_routing_stg
set res_verify_flag = 'N',
error_message = l_error_message
where item_name = RES1.item_name
and department = RES1.department
and resource_code = RES1.resource_code;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N'
where item_name = RES1.item_name
and department = RES1.department;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = RES1.item_name;
goto next_rout;
end;
else
rollback to a1;
update xxx_bom_op_routing_stg
set res_verify_flag = 'N',
error_message = l_error_message
where item_name = res1.item_name
and department = res1.department
and resource_code = res1.resource_code;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N'
where item_name = res1.item_name
and department = res1.department;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = RES1.item_name;
goto next_rout;
end if; --if_res_verify_flag
end loop ;
----- RESOURCES AREA ENDS **********************
else
rollback to savepoint a1;
update xxx_bom_op_routing_stg
set seq_verify_flag = 'N',
error_message = l_error_message
where item_name = seq1.item_name
and operation_sequence = seq1.operation_sequence;
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N'
where item_name = SEQ1.item_name;
goto next_rout;
end if; --if_seq_verify_flag
end loop ;
else
update xxx_bom_op_routing_stg
set rout_verify_flag = 'N',
error_message = l_error_message
where item_name = ROUT1.item_name;
end if; ---if_rout_verify_flag
--next_rout --(This will be in << >>)
commit;
<<next_rout>>
begin
continue;
end next_rout;
end loop ;
END xxx_bom_routing_api ;
/
2- BOM Components
a- Create table to hold staging data:
CREATE TABLE APPS.XXX_MAINTENANCE_BOM_STG
(
ITEM_NAME VARCHAR2(30 BYTE),
ITEM_SEQUENCE NUMBER(3),
OPERATION_SEQUENCE NUMBER(3),
INV_ITEM VARCHAR2(30 BYTE),
COMPONENT_QUANTITY NUMBER(10,2),
BOM_VERIFY_FLAG CHAR(1 BYTE),
BOM_COMP_VERIFY_FLAG CHAR(1 BYTE),
ERROR_MESSAGE VARCHAR2(2500 BYTE)
);
b- Create procedure to process the data from staging table to interface
CREATE OR REPLACE procedure APPS.xxx_bom_components_api is
l_organization_id number(5);
l_bom_verify_flag char(1);
l_bom_comp_verify_flag char(1);
l_error_message varchar2(2500);
l_inventory_item_id number(20);
l_component_item_id number(20);
l_schedule_flag number(3);
---Routing Cursor
cursor c_rout is select distinct item_name from xxx_maintenance_bom_stg;
---Bom Cursor
cursor c_bom (p_item_name varchar2) is select * from xxx_maintenance_bom_stg
where item_name = p_item_name --and item_sequence = p_item_sequence
order by item_sequence;
--, item_sequence;
begin
for rout1 in c_rout loop
l_bom_verify_flag := 'Y';
l_bom_comp_verify_flag:='Y';
l_error_message := null;
begin
/*select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Spares Store';*/
l_organization_id:='127';
exception when others then
l_bom_verify_flag := 'N';
l_error_message := 'Organization is not Valid';
end;
begin
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where
--upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4)
segment1= trim(rout1.item_name)
and organization_id = l_organization_id;
exception
when others then
l_bom_verify_flag := 'N';
l_error_message := l_error_message||'Item Name is not Valid';
end;
IF l_bom_verify_flag <> 'N' then
savepoint a1;
-- start of insertion of data into bom structure interface table
begin
insert into bom_bill_of_mtls_interface
(
assembly_item_id,
process_flag,
transaction_type,
organization_id,
assembly_type
)
values
(l_inventory_item_id,
1,
'CREATE',
l_organization_id,
1);
update XXX_MAINTENANCE_BOM_STG set bom_verify_flag='Y' where item_name = rout1.item_name ;
exception
when others then
rollback to a1;
l_error_message := sqlerrm;
update XXX_MAINTENANCE_BOM_STG set bom_verify_flag = 'N', error_message =l_error_message
where item_name = rout1.item_name;
goto next_rout;
end;
-- End of Data Insertion into Bom Structure Table
-- Start of Data Insertion into BOM Components Table
for bom1 in c_bom (rout1.item_name)
loop
begin
select max(inventory_item_id)
into l_component_item_id
from mtl_system_items_b
where
--upper(segment1||'.'||segment2||'.'||segment3||'.'||segment4)
segment1= bom1.inv_item
and organization_id = l_organization_id;
exception when others then
l_bom_comp_verify_flag := 'N';
l_error_message := l_error_message||'BOM comp Item Name is not Valid';
end;
if l_bom_comp_verify_flag <>'N' then
begin
insert into bom_inventory_comps_interface
(assembly_item_id,
item_num,
transaction_type,
process_flag,
organization_id,
operation_seq_num,
component_item_id,
component_quantity)
values
(
l_inventory_item_id,
trim(bom1.item_sequence),
'CREATE',
1,
l_organization_id,
trim(bom1.operation_sequence),
l_component_item_id,
bom1.component_quantity
);
update XXX_MAINTENANCE_BOM_STG
set bom_comp_verify_flag = 'Y'
where item_name = bom1.item_name
and operation_sequence = bom1.operation_sequence
and item_sequence = bom1.item_sequence;
exception
when others then
rollback to a1;
l_error_message := l_error_message||'BOM Components Error.'||sqlerrm;
update XXX_MAINTENANCE_BOM_STG
set bom_comp_verify_flag = 'N',
error_message = l_error_message
where item_name = bom1.item_name
and operation_sequence = bom1.operation_sequence
and item_sequence = bom1.item_sequence;
goto next_rout;
end;
else
rollback to savepoint a1;
update XXX_MAINTENANCE_BOM_STG
set bom_comp_verify_flag = 'N',
error_message = l_error_message
where item_name = bom1.item_name
and operation_sequence = bom1.operation_sequence
and item_sequence = bom1.item_sequence;
goto next_rout;
end if; --if_seq_verify_flag
end loop;
-- End of Data Insertion into BOM Components Table
else
update XXX_MAINTENANCE_BOM_STG
set bom_verify_flag = 'N',
error_message = l_error_message
where item_name = ROUT1.item_name;
goto next_rout;
end if;
<<next_rout>>
begin
continue;
end next_rout;
commit;
end loop;
end;
/
This comment has been removed by the author.
ReplyDelete