Routing Interface - bom_op_sequences_interface, bom_op_resources_interface

Routing Interface

ROUTING INTERFACE
*****************************
Steps to do :-
1. Data receiving in Data Template
2. Create the staging table
3. Upload data from excel template to staging table
4. Create the below script and execute it(data from staging to interface table with custom validations)
5. go to bills of material responsibility and run the "Bill and Routing Interface"
6. parameter :-All Organizations - applicable only if you are running for multiple organizations
Import Routings - Yes
Import Bills of Material -No
Delete Processed Rows - Yes (imported records will delete successfully)

to view the errors
-------------------
SELECT process_flag,
err. transaction_id,
error_message,
message_name
FROM mtl_interface_errors err,
bom_op_resources_interface bom
WHERE err.transaction_id = bom.transaction_id
***********************************************
Routing Interface Tables are :-
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_OP_RESOURCES_INTERFACE

View Error :- MTL_INTERFACE_ERRORS

Routing Import Program is :- Bill and Routing Interface
Here Bill Import will be No

Data Template
--------------
item_name,
routing_revision_no,
operation_seq_num,
department,
resource_seq_num,
total_time,
schedule_sequence,
assigned_units,
scheduling_flag

staging table
------------
create table xxx_bom_op_routing_stg
(
item_name varchar2(30),
revision_no number(2),
operation_sequence number(3),
department varchar2(30),
resource_sequence number(3),
resource_code varchar2(20),
usage_rate number(10,2),
assigned_units number(5),
schedule_flag varchar2(5),
schedule_seq_num number(2),
rout_verify_flag varchar2(1),
seq_verify_flag varchar2(1),
res_verify_flag varchar2(1),
error_message varchar2(2500)
)
----------------------------------------------------

CREATE OR REPLACE procedure 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_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;

begin

for rout1 in c_rout
loop

l_rout_verify_flag := 'Y';
l_seq_verify_flag := 'Y';
l_res_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';
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) = trim(upper(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;

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;

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;

for res1 in c_res (seq1.item_name,seq1.operation_sequence)
loop

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;

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 ;

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;
end loop ;

END xxx_bom_routing_api ;
/