Create a staging table :
CREATE TABLE xx_bom_stg
(
org_code VARCHAR2(30),
asly_code VARCHAR2(240),
comp_code VARCHAR2(240),
qty NUMBER,
subinventory VARCHAR2(100),
uom VARCHAR2(30),
status VARCHAR2(30),
status_msg VARCHAR2(2000)
);
Insert the data into this staging table and then execute below procedure :
CREATE OR REPLACE PROCEDURE xx_bom_api
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/test';
l_debug_filename VARCHAR2(60) := 'test_debug.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) := 'TECHNICAL2';
l_resp_name VARCHAR2(30) := 'BILLS OF MATERIAL';
l_item_seq NUMBER;
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);
FOR ch IN (SELECT DISTINCT asly_code,
comp_code,
org_code
FROM xx_bom_stg A,
mtl_parameters B,
mtl_system_items C,
mtl_system_items d
WHERE A.org_code = B.organization_code
AND A.asly_code = C.segment1
AND B.organization_id = C.organization_id
AND a.comp_code = d.segment1
AND d.organization_id = C.organization_id
--AND A.ASLY_CODE='PACKTC1D0003'
AND C.bom_enabled_flag = 'Y'
--and d.BOM_ENABLED_FLAG='N'
AND NOT EXISTS (SELECT 1
FROM bom_bill_of_materials X
WHERE
X.assembly_item_id = C.inventory_item_id
AND X.organization_id = C.organization_id)
-- and not exists (select 1 from bom_inventory_components x where x.COMPONENT_ITEM_ID=d.inventory_item_id and x.PK2_VALUE=C.ORGANIZATION_ID)
) LOOP
l_bom_revision_tbl.DELETE;
l_bom_component_tbl.DELETE;
l_bom_ref_designator_tbl.DELETE;
l_bom_sub_component_tbl.DELETE;
x_bom_revision_tbl.DELETE;
x_bom_component_tbl.DELETE;
x_bom_ref_designator_tbl.DELETE;
x_bom_sub_component_tbl.DELETE;
-- initialize BOM header
l_bom_header_rec := bom_bo_pub.g_miss_bom_header_rec;
l_bom_revision_tbl := bom_bo_pub.g_miss_bom_revision_tbl;
l_bom_component_tbl := bom_bo_pub.g_miss_bom_component_tbl;
l_bom_ref_designator_tbl := bom_bo_pub.g_miss_bom_ref_designator_tbl;
l_bom_sub_component_tbl := bom_bo_pub.g_miss_bom_sub_component_tbl;
-- API output variables
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;
l_bom_header_rec.assembly_item_name := ch.asly_code;
l_bom_header_rec.organization_code := ch.org_code;
l_bom_header_rec.assembly_type := 1;
l_bom_header_rec.transaction_type := 'CREATE';
l_bom_header_rec.return_status := NULL;
l_cnt := 0;
l_item_seq := 0;
FOR cl IN (SELECT A.comp_code,
A.uom,
A.org_code,
A.subinventory,
A.qty
FROM xx_bom_stg A,
mtl_parameters B,
mtl_system_items C
WHERE A.org_code = B.organization_code
AND A.comp_code = C.segment1
AND B.organization_id = C.organization_id
AND A.asly_code = ch.asly_code
AND A.org_code = ch.org_code) LOOP
l_cnt := l_cnt + 1;
l_item_seq := l_item_seq + 10;
-- initialize BOM components
-- component 1
L_bom_component_tbl (l_cnt).organization_code := ch.org_code;
L_bom_component_tbl (l_cnt).assembly_item_name := ch.asly_code;
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 := cl.comp_code;
L_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
L_bom_component_tbl (l_cnt).supply_subinventory := cl.subinventory;
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 := l_item_seq;
L_bom_component_tbl (l_cnt).operation_sequence_number := l_item_seq;
L_bom_component_tbl (l_cnt).transaction_type := 'CREATE';
L_bom_component_tbl (l_cnt).quantity_per_assembly := cl.qty;
L_bom_component_tbl (l_cnt).return_status := NULL;
--- initialize error stack for logging errors
END LOOP;
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 => 'N'
--,
-- 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('=======================================================');
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line('Exception Occured :');
dbms_output.Put_line(SQLCODE
||':'
||SQLERRM);
dbms_output.Put_line('=======================================================');
RAISE;
END;
No comments:
Post a Comment