BILL OF MATERIAL INTERFACE - BOM_BILL_OF_MTLS_INTERFACE, BOM_INVENTORY_COMPS_INTERFACE

Bom Interfaces

BILL OF MATERIAL INTERFACE
****************************
Interface tables are :-
----------------------------
BOM_BILL_OF_MTLS_INTERFACE
BOM_INVENTORY_COMPS_INTERFACE

---MTL_INTERFACE_ERRORS(Error Message table)

Import Porgram :-
Bill and Routing Interface (Here parameter import routings will be no)
---------------------------------------------------------------------------------
BOM Sharing
--------------
Bill of material created for one organization can be share with other organizations.
Steps 1. Create the master bom and its inventory components
2. Create the same bom for other organizations with additional two fields i.e Common_Organization_Id and Common_Assembly_Item_Id. Here common_organization_id will be the master organization id and common assembly item id will be the master assembly item id(same assembly item id of sub org also).
If you are running Bill and Routing Interface for master and sub organization bom at the same time, there is possibility of throwing error if the master organization id is greater than sub organization id.
So better to run the Master bom first then run the child organizations.
--------------------------------------------------------------------------------

create the following staging table
CREATE TABLE XXX_BOM_BILL_MTLS_STG
(
ITEM_NAME VARCHAR2(50),
REVISION_NO CHAR(5),
ITEM_SEQ_BOM_COMP NUMBER(5),
OPERATION_SEQUENCE NUMBER(5),
BOM_COMPONENT VARCHAR2(50),
QUANTIY NUMBER(10,2),
YIELD_FACTOR NUMBER(10,2),
SUPPLY_TYPE VARCHAR2(10),
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(3000)
)
-------------------------------------------------------------------------------------
The script to upload data into interface tables
CREATE OR REPLACE procedure xxx_bom_bill_mtls_api
(errbuf varchar2,retcode varchar2) as
l_verify_flag char(1);
l_error_message varchar2(2500);
l_organization_id number(15);
l_inventory_item_id number(15);
l_component_item_id number(15);
l_bom_exists number(15);
l_wip_supply_type number(3);

CURSOR C_HEADER IS
select distinct item_name,revision_no
from xxx_bom_bill_mtls_stg
where nvl(h_verify_flag,'N') = 'N';

CURSOR C_LINES (p_item_name varchar2)IS
select *
from xxx_bom_bill_mtls_stg
where item_name = p_item_name
order by item_name,item_seq_bom_comp;

BEGIN

FOR C_BOM IN C_HEADER
LOOP

l_verify_flag := 'Y';
l_error_message := null;
l_bom_exists := null;

BEGIN
select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Main Store';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Organization is not valid';
END;

BEGIN
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and segment1'.'segment2'.'segment3'.'segment4
=trim(upper(c_bom.item_name));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Item is not valid';
END;

BEGIN
select assembly_item_id
into l_bom_exists
from BOM_BILL_OF_MATERIALS_V
where organization_id = l_organization_id
and assembly_item_id = l_inventory_item_id ;
if l_bom_exists >0 then
l_verify_flag := 'N';
l_error_message := l_error_message'Item already existing';
end if;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

IF l_verify_flag <> 'N' then
savepoint A;

BEGIN
insert into
BOM_BILL_OF_MTLS_INTERFACE
(
ASSEMBLY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE
,REVISION
,ASSEMBLY_TYPE
,PROCESS_FLAG
,ATTRIBUTE1
)
VALUES
(
l_inventory_item_id
,l_organization_id
,'CREATE'
,trim(nvl(c_bom.revision_no,0))
,1 ---1) Manufacturing, 2) Engineering
,1
,1
) ;

update xxx_bom_bill_mtls_stg
set h_verify_flag = 'Y'
where item_name = c_bom.item_name;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := SQLERRM;
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N',
error_message = l_error_message
where item_name = c_bom.item_name;
goto next_bom;
END;

FOR C_COMP IN C_LINES(c_bom.item_name)
LOOP

BEGIN
select inventory_item_id
into l_component_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and upper(segment1'.'segment2'.'segment3'.'segment4)
=upper(trim(C_COMP.bom_component));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message 'Bom Component is not valid';
END;

BEGIN
If c_comp.supply_type = 'Phantom' then
l_wip_supply_type := 6;
else
l_wip_supply_type := 1;
end if;
END;

IF l_verify_flag <> 'N' then

BEGIN
insert into
bom_inventory_comps_interface
(
assembly_item_id
,process_flag
,transaction_type
,component_item_id
,component_sequence_id
,item_num
,operation_seq_num
,organization_id
,effectivity_date
,component_quantity
,component_yield_factor
,attribute6
,attribute9
,wip_supply_type
)
VALUES
(
l_inventory_item_id
,1
,'CREATE'
,l_component_item_id
,bom_inventory_components_s.nextval
,trim(c_comp.item_seq_bom_comp)
,trim(C_COMP.operation_sequence)
,l_organization_id
,sysdate
,trim(c_comp.quantiy)
,trim(c_comp.yield_factor)
,1
,1
,l_wip_supply_type
);

update xxx_bom_bill_mtls_stg
set l_verify_flag = 'Y'
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
rollback to savepoint A;

update xxx_bom_bill_mtls_stg
set l_verify_flag = 'N',
error_message = l_error_message
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;

update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N'
where item_name = c_comp.item_name ;
goto next_bom ;
END;

ELSE
rollback to savepoint A;
update xxx_bom_bill_mtls_stg
set l_verify_flag = 'N',
error_message = l_error_message
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N'
where item_name = c_comp.item_name ;
goto next_bom;
END IF;

END LOOP;

ELSE
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N',
error_message = l_error_message
where item_name = c_bom.item_name ;

END IF;
<
COMMIT;
END LOOP;
end xxx_bom_bill_mtls_api;
/