CREATE OR REPLACE PROCEDURE SU_PROCESS_BOM
AS
-- API input variables
l_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec;
l_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl;
l_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl;
l_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref_designator_tbl_type := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
l_bom_sub_component_tbl Bom_Bo_Pub.bom_sub_component_tbl_type := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
-- API output variables
x_bom_header_rec Bom_Bo_Pub.bom_head_rec_type := Bom_Bo_Pub.g_miss_bom_header_rec;
x_bom_revision_tbl Bom_Bo_Pub.bom_revision_tbl_type := Bom_Bo_Pub.g_miss_bom_revision_tbl;
x_bom_component_tbl Bom_Bo_Pub.bom_comps_tbl_type := Bom_Bo_Pub.g_miss_bom_component_tbl;
x_bom_ref_designator_tbl Bom_Bo_Pub.bom_ref_designator_tbl_type := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
x_bom_sub_component_tbl Bom_Bo_Pub.bom_sub_component_tbl_type := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
x_message_list Error_Handler.Error_Tbl_Type;
l_error_table Error_Handler.Error_Tbl_Type;
l_output_dir VARCHAR2(500) := \'/usr/tmp/visus25\';
l_debug_filename VARCHAR2(60) := \'su_debug_07_16.dbg\';
l_return_status VARCHAR2(1) := NULL;
l_msg_count NUMBER := 0;
l_cnt NUMBER := 1;
-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2(30) := \'MFG\';
l_resp_name VARCHAR2(30) := \'MFG_AND_DIST_SUPER_USER_APS\';
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
-- intiialize applications information
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- Mfg / Mfg & Dist Mgr / INV
dbms_output.put_line(\'Initialized applications context: \'|| l_user_id || \' \'|| l_resp_id ||\' \'|| l_application_id );
-- initialize BOM header
l_bom_header_rec.assembly_item_name := \'RS_BOM_ASSY1\';
l_bom_header_rec.organization_code := \'M1\';
l_bom_header_rec.assembly_type := 1;
l_bom_header_rec.transaction_type := \'CREATE\';
l_bom_header_rec.return_status := NULL;
l_cnt := 1;
-- initialize BOM components
-- component 1
l_bom_component_tbl (l_cnt).organization_code := \'M1\';
l_bom_component_tbl (l_cnt).assembly_item_name := \'RS_BOM_ASSY1\';
l_bom_component_tbl (l_cnt).start_effective_date := sysdate; -- to_date(\'16-JUL-2010 19:30:39\',\'DD-MON-YY HH24:MI:SS\'); -- should match timestamp for UPDATE
l_bom_component_tbl (l_cnt).component_item_name := \'RS_BOM_C1\';
l_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
l_bom_component_tbl (l_cnt).supply_subinventory := \'RIP\';
l_bom_component_tbl (l_cnt).location_name := NULL; -- \'6.6.6..\'; -- provide concatenated segments for locator
l_bom_component_tbl (l_cnt).comments := \'Created from BOM API\';
l_bom_component_tbl (l_cnt).item_sequence_number := \'10\';
l_bom_component_tbl (l_cnt).operation_sequence_number := \'10\';
l_bom_component_tbl (l_cnt).transaction_type := \'CREATE\';
l_bom_component_tbl (l_cnt).quantity_per_assembly := 2;
l_bom_component_tbl (l_cnt).return_status := NULL;
-- component 2
l_cnt := l_cnt + 1;
l_bom_component_tbl (l_cnt).organization_code := \'M1\';
l_bom_component_tbl (l_cnt).assembly_item_name := \'RS_BOM_ASSY1\';
l_bom_component_tbl (l_cnt).start_effective_date := SYSDATE;
l_bom_component_tbl (l_cnt).component_item_name := \'RS_BOM_C2\';
l_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
l_bom_component_tbl (l_cnt).supply_subinventory := \'RIP\';
l_bom_component_tbl (l_cnt).location_name := NULL;
l_bom_component_tbl (l_cnt).comments := \'Created from BOM API\';
l_bom_component_tbl (l_cnt).item_sequence_number := \'20\';
l_bom_component_tbl (l_cnt).operation_sequence_number := \'10\';
l_bom_component_tbl (l_cnt).transaction_type := \'CREATE\';
l_bom_component_tbl (l_cnt).quantity_per_assembly := 5;
l_bom_component_tbl (l_cnt).return_status := NULL;
-- initialize error stack for logging errors
Error_Handler.initialize;
-- call API to create / update bill
DBMS_OUTPUT.PUT_LINE(\'=======================================================\');
DBMS_OUTPUT.PUT_LINE(\'Calling Bom_Bo_Pub.process_bom API\');
Bom_Bo_Pub.process_bom (p_bo_identifier => \'BOM\',
p_api_version_number => 1.0,
p_init_msg_list => TRUE,
p_bom_header_rec => l_bom_header_rec,
p_bom_revision_tbl => l_bom_revision_tbl,
p_bom_component_tbl => l_bom_component_tbl,
p_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
p_bom_sub_component_tbl => l_bom_sub_component_tbl,
x_bom_header_rec => x_bom_header_rec,
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 => l_return_status,
x_msg_count => l_msg_count ,
p_debug => \'Y\',
p_output_dir => l_output_dir,
p_debug_filename => l_debug_filename
);
DBMS_OUTPUT.PUT_LINE(\'=======================================================\');
DBMS_OUTPUT.PUT_LINE(\'Return Status: \'||l_return_status);
IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
dbms_output.put_line(\'x_msg_count:\' || l_msg_count);
Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
DBMS_OUTPUT.PUT_LINE(\'Error Message Count :\'||l_error_table.COUNT);
FOR i IN 1..l_error_table.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(to_char(i)||\':\'||l_error_table(i).entity_index||\':\'||l_error_table(i).table_name);
DBMS_OUTPUT.PUT_LINE(to_char(i)||\':\'||l_error_table(i).message_text);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(\'=======================================================\');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(\'Exception Occured :\');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||\':\'||SQLERRM);
DBMS_OUTPUT.PUT_LINE(\'=======================================================\');
RAISE;
END SU_PROCESS_BOM;
Source:How to Create a Bill of Material (BOM) using Bom_Bo_Pub.Process_Bom API [Video] (Doc ID 1242665.1)