BOM Interface

[nishabelgi]

I read there is BOM API , does any one know that BOM has Interface tables and import program or only APIs, If anybody knows please let me know.

Thanks,
Nisha

[Ritz]

It is

BOM_BILL_OF_MTLS_INTERFACE



Ritz

[josesincable]

I read there is BOM API , does any one know that BOM has Interface tables and import program or only APIs, If anybody knows please let me know.

Thanks,
Nisha

<i><div align="right">Originally posted by nishabelgi - 26/04/2007 : 08:32:54 AM</div id="right"></i>

There is a API. It is better than table interfaces. You can findt it in metalink.

[Waliabi]

Hi guys,
There are six tables involved in BOM interface.

1.BOM_BILLS_OF_MTLS_INTERFACE ------ bom header information
2. BOM_INVENTORY_COMPONENTS ------ bom components details

If you do not have routing then use on these two tables for import.

3. BOM_ASSY_COMMENT_INTERFACE

4.BOM_REF_DESGS_INTERFACE

5. BOM_SUB_COMPS_INTERFACE

6. MTL_ITEM_REFISIONS_INTERFACE


After loading data into these tables, you can run import program from ?BILLS OF MATRIAL? responsibility.

[erpstuffguy]

Hi Guys,

Please find the code for BOM interface through API.

/*******************************************************/

CREATE OR REPLACE package ****_bom_pkg is
procedure ****_bom_proc ;
End;
/

CREATE OR REPLACE package body ****_bom is
PROCEDURE LOG (p_msg VARCHAR2)
IS
BEGIN
IF fnd_global.conc_login_id = -1
THEN
DBMS_OUTPUT.put_line (p_msg);
ELSE
fnd_file.put_line (fnd_file.LOG, p_msg);
END IF;
END LOG;
PROCEDURE ****_bom_proc
IS
CURSOR cur_****x_bom_bills_stg
IS
SELECT *
FROM ****x_bom_bills_stg
WHERE status_flag = 'N';
CURSOR cur_****x_bom_comp_stg (
p_organization_code IN VARCHAR2,
p_assembly_item IN VARCHAR2
)
IS
SELECT *
FROM ****x_bom_comp_stg
WHERE organization_code = p_organization_code
AND assembly_item = p_assembly_item;
CURSOR cur_****x_bom_sub_com_stg (
p_organization_code IN VARCHAR2,
p_assembly_item IN VARCHAR2,
p_item_seq IN VARCHAR2
)
IS
SELECT *
FROM ****x_bom_sub_com_stg
WHERE organization_code = p_organization_code
AND assembly_item_number = p_assembly_item
AND item_seq = p_item_seq;
CURSOR cur_****x_bom_ref_des_stg (
p_organization_code IN VARCHAR2,
p_assembly_item IN VARCHAR2,
p_item_seq IN VARCHAR2
)
IS
SELECT *
FROM ****x_bom_ref_des_stg
WHERE organization_code = p_organization_code
AND assembly_item_number = p_assembly_item
AND item_seq = p_item_seq;
/* Table types for Process BOM API Call (IN) */
bom_header_rec bom_bo_pub.bom_head_rec_type
:= bom_bo_pub.g_miss_bom_header_rec;
bom_revision_tbl bom_bo_pub.bom_revision_tbl_type
:= bom_bo_pub.g_miss_bom_revision_tbl;
bom_component_tbl bom_bo_pub.bom_comps_tbl_type
:= bom_bo_pub.g_miss_bom_component_tbl;
bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type;
-- := bom_bo_pub.g_miss_bom_ref_designator_tbl;
bom_sub_component_tbl bom_bo_pub.bom_sub_component_tbl_type
:= bom_bo_pub.g_miss_bom_sub_component_tbl;
/* Table types for Process BOM API Call (OUT) */
x_bom_header_rec bom_bo_pub.bom_head_rec_type;
x_bom_revision_tbl bom_bo_pub.bom_revision_tbl_type;
x_bom_component_tbl bom_bo_pub.bom_comps_tbl_type;
x_bom_ref_designator_tbl bom_bo_pub.bom_ref_designator_tbl_type;
x_bom_sub_component_tbl bom_bo_pub.bom_sub_component_tbl_type;
/* Variables for General Use */
l_error_message_list error_handler.error_tbl_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
r NUMBER :=0;
i NUMBER :=0;
j NUMBER :=0;
k NUMBER :=0;
l NUMBER :=0;
lb_status BOOLEAN;
lb_rtg_status BOOLEAN;
ln_conv_per NUMBER;
p NUMBER := 0;
q NUMBER := 0;
lc_order VARCHAR2 (3);
z NUMBER := 0;
lc_check VARCHAR2 (100);
/* Exceptions Used in the Procedure */
rtg_error EXCEPTION;
Begin
For rec_****x_bom_bills_stg in cur_****x_bom_bills_stg loop
Begin
bom_header_rec.Assembly_item_name := rec_****x_bom_bills_stg.segment1;
bom_header_rec.Organization_Code := rec_****x_bom_bills_stg.organization_code;
bom_header_rec.Alternate_Bom_Code := rec_****x_bom_bills_stg.alternate_bom_designator;
bom_header_rec.Transaction_Type := 'CREATE';
r :=1;
bom_revision_tbl(r).Assembly_Item_Name := rec_****x_bom_bills_stg.segment1;
bom_revision_tbl(r).Organization_Code := rec_****x_bom_bills_stg.Organization_Code;
bom_revision_tbl(r).Revision := rec_****x_bom_bills_stg.Revision;
bom_revision_tbl(r).Revision_Label := rec_****x_bom_bills_stg.Revision;
bom_revision_tbl(r).Transaction_Type := 'CREATE';
i := 0;
FOR rec_****x_bom_comp_stg IN
cur_****x_bom_comp_stg
(rec_****x_bom_bills_stg.organization_code,
rec_****x_bom_bills_stg.segment1
)
LOOP
i :=i+1;
BEGIN
bom_component_tbl(i).Organization_Code :=rec_****x_bom_bills_stg.organization_code;
bom_component_tbl(i).Assembly_Item_Name :=rec_****x_bom_bills_stg.segment1;
bom_component_tbl(i).Start_Effective_Date :=rec_****x_bom_comp_stg.effectivity_date;
bom_component_tbl(i).Operation_Sequence_Number :=rec_****x_bom_comp_stg.operation_seq_num;
bom_component_tbl(i).Component_Item_Name :=rec_****x_bom_comp_stg.component_item;
bom_component_tbl(i).Alternate_BOM_Code :=rec_****x_bom_comp_stg.alternate_bom_designator;
bom_component_tbl(i).Item_Sequence_Number :=rec_****x_bom_comp_stg.item_num;
bom_component_tbl(i).Include_In_Cost_Rollup :=rec_****x_bom_comp_stg.include_in_cost_rollup;
bom_component_tbl(i).Mutually_Exclusive :=rec_****x_bom_comp_stg.mutually_exclusive_options;
bom_component_tbl(i).Check_Atp :=rec_****x_bom_comp_stg.check_atp ;
bom_component_tbl(i).Shipping_Allowed :=rec_****x_bom_comp_stg.shipping_allowed ;
bom_component_tbl(i).Required_To_Ship :=rec_****x_bom_comp_stg.required_to_ship;
bom_component_tbl(i).Required_For_Revenue :=rec_****x_bom_comp_stg.required_for_revenue;
bom_component_tbl(i).Include_On_Ship_Docs :=rec_****x_bom_comp_stg.include_on_ship_docs;
bom_component_tbl(i).Transaction_Type :='CREATE';
bom_component_tbl(i).Enforce_Int_Requirements :=rec_****x_bom_comp_stg.enforce_int_requirements;
bom_component_tbl(i).Optional :=rec_****x_bom_comp_stg.optional ;
j :=0;
k :=0;
FOR rec_****x_bom_sub_com_stg IN
cur_****x_bom_sub_com_stg
(rec_****x_bom_comp_stg.organization_code,
rec_****x_bom_comp_stg.assembly_item,
rec_****x_bom_comp_stg.item_num
)
LOOP
Begin
j := j+1;
bom_sub_component_tbl(j).Organization_Code := rec_****x_bom_bills_stg.organization_code;
bom_sub_component_tbl(j).Assembly_Item_Name := rec_****x_bom_bills_stg.segment1;
bom_sub_component_tbl(j).Start_Effective_Date := rec_****x_bom_comp_stg.effectivity_date;
bom_sub_component_tbl(j).Operation_Sequence_Number := rec_****x_bom_comp_stg.operation_seq_num;
bom_sub_component_tbl(j).Component_Item_Name := rec_****x_bom_comp_stg.component_item;
bom_sub_component_tbl(j).Alternate_BOM_Code := rec_****x_bom_comp_stg.alternate_bom_designator;
bom_sub_component_tbl(j).Substitute_Component_Name := rec_****x_bom_sub_com_stg.substitute_item_number ;
bom_sub_component_tbl(j).Substitute_Item_Quantity := rec_****x_bom_sub_com_stg.substitute_item_quantity ;
bom_sub_component_tbl(j).Enforce_Int_Requirements := rec_****x_bom_sub_com_stg.enforce_int_requirements_desc ;
bom_sub_component_tbl(j).Transaction_Type := 'CREATE';
End;
End loop;
FOR rec_****x_bom_ref_des_stg IN
cur_****x_bom_ref_des_stg
(rec_****x_bom_comp_stg.organization_code,
rec_****x_bom_comp_stg.assembly_item,
rec_****x_bom_comp_stg.item_num
)
LOOP
Begin
k := k+1;
bom_ref_designator_tbl(k).Organization_Code := rec_****x_bom_bills_stg.organization_code;
bom_ref_designator_tbl(k).Assembly_Item_Name:= rec_****x_bom_bills_stg.segment1;
bom_ref_designator_tbl(k).Start_Effective_Date:= rec_****x_bom_comp_stg.effectivity_date;
bom_ref_designator_tbl(k).Operation_Sequence_Number:= rec_****x_bom_comp_stg.operation_seq_num;
bom_ref_designator_tbl(k).Component_Item_Name := rec_****x_bom_comp_stg.component_item;
bom_ref_designator_tbl(k).Alternate_Bom_Code := rec_****x_bom_comp_stg.alternate_bom_designator;
bom_ref_designator_tbl(k).Reference_Designator_Name := rec_****x_bom_ref_des_stg.component_reference_designator;
bom_ref_designator_tbl(k).Ref_Designator_Comment := rec_****x_bom_ref_des_stg.ref_designator_comment;
bom_ref_designator_tbl(k).Transaction_Type := 'CREATE';
End;
End loop;
End;
End loop;
x_bom_header_rec := bom_bo_pub.g_miss_bom_header_rec;
x_bom_revision_tbl := bom_bo_pub.g_miss_bom_revision_tbl;
x_bom_component_tbl := bom_bo_pub.g_miss_bom_component_tbl;
x_bom_ref_designator_tbl :=
bom_bo_pub.g_miss_bom_ref_designator_tbl;
x_bom_sub_component_tbl :=
bom_bo_pub.g_miss_bom_sub_component_tbl;
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM',
p_api_version_number => 1.0
-- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
,
p_init_msg_list => TRUE
-- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
,
p_bom_header_rec => bom_header_rec
-- This is a set of data structures that represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
,
p_bom_revision_tbl => bom_revision_tbl
-- All the p*_tbl parameters are data structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
,
p_bom_component_tbl => bom_component_tbl,
p_bom_ref_designator_tbl => bom_ref_designator_tbl,
p_bom_sub_component_tbl => bom_sub_component_tbl,
x_bom_header_rec => x_bom_header_rec
-- All the x*_tbl parameters are data structure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
,
x_bom_revision_tbl => x_bom_revision_tbl,
x_bom_component_tbl => x_bom_component_tbl,
x_bom_ref_designator_tbl => x_bom_ref_designator_tbl,
x_bom_sub_component_tbl => x_bom_sub_component_tbl,
x_return_status => x_return_status
-- This is a flag that indicates the state of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
,
x_msg_count => x_msg_count
-- This holds the number of messages in the API
-- message stack after the import.
,
p_debug => 'N',
p_output_dir => '',
p_debug_filename => ''
);
/* End of API Call */
/* *********************************************************/
LOG ( ' 1 After calling BOM_BO_PUB.PROCESS_BOM API '
|| rec_****x_bom_bills_stg.segment1
);
/* Capture Messages from the API */
IF x_return_status = 'E'
THEN
lb_status := FALSE;
error_handler.get_message_list (l_error_message_list);
LOG
( 'error during creation of BOM for Assembly Item : '
|| rec_****x_bom_bills_stg.segment1
);
LOG ( 'For organization :'
|| rec_****x_bom_bills_stg.organization_code
);
FOR i IN 1 .. x_msg_count
LOOP
LOG ( TO_CHAR (i)
|| ' MESSAGE TEXT '
|| SUBSTR (l_error_message_list (i).MESSAGE_TEXT,
1,
250
)
);
LOG ( TO_CHAR (i)
|| ' MESSAGE TYPE '
|| l_error_message_list (i).MESSAGE_TYPE
);
log( SUBSTR (l_error_message_list (i).MESSAGE_TEXT,
1,
250
));
l := l + 1;
END LOOP;
ELSIF x_return_status IN ('U', 'F')
THEN
lb_status := FALSE;
error_handler.get_message_list (l_error_message_list);
LOG
( 'unexpected error during creation of BOM for Assembly Item : '
|| rec_****x_bom_bills_stg.segment1
);
LOG ( 'For organization :'
|| rec_****x_bom_bills_stg.organization_code
);
FOR i IN 1 .. x_msg_count
LOOP
LOG ( TO_CHAR (i)
|| ' MESSAGE TEXT '
|| SUBSTR (l_error_message_list (i).MESSAGE_TEXT,
1,
250
)
);
LOG ( TO_CHAR (i)
|| ' MESSAGE TYPE '
|| l_error_message_list (i).MESSAGE_TYPE
);
log( SUBSTR (l_error_message_list (i).MESSAGE_TEXT,
1,
250
));
l := l + 1;
END LOOP;
ELSE
LOG ( 'Bill of Material created for Assembly Item: '
|| rec_****x_bom_bills_stg.segment1
);
LOG ( 'Organization: '
|| rec_****x_bom_bills_stg.organization_code
);
END IF;
End;
End loop;
End ****_bom_proc;
End ****_bom_pkg;
/