/*API to Update Segment1 of Item Code In oracle APPs R12*/
DECLAREl_item_tbl_typ ego_item_pub.item_tbl_type;
x_item_table ego_item_pub.item_tbl_type;
x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
x_organization_id mtl_system_items_b.organization_id%TYPE;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER (10);
x_msg_data VARCHAR2 (1000);
x_message_list error_handler.error_tbl_type;
CURSOR c1 IS
SELECT MSI.inventory_item_id,
MSI.organization_id,
MSI.last_update_date,
msi.segment1 ITEM_CODE,
msi.description
FROM mtl_system_items_b msi
WHERE 1 = 1
AND msi.segment1 LIKE 'DG SYNC%%ETHERNET%' --11317029,11317030
--and msi.organization_id=395
;
BEGIN
FOR i IN c1 LOOP
fnd_global.Apps_initialize(user_id => 0, resp_id => 50916,
resp_appl_id => 401);
L_item_tbl_typ (1).transaction_type := 'UPDATE';
-- Replace this with 'UPDATE' for update transaction.
L_item_tbl_typ (1).inventory_item_id := i.inventory_item_id;
L_item_tbl_typ (1).organization_id := i.organization_id;
L_item_tbl_typ (1).segment1 := 'DG SYNC RELAY CD5.0 RS485/ETHERNET';
ego_item_pub.Process_items (p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true,
p_item_tbl => l_item_tbl_typ, x_item_tbl => x_item_table,
x_return_status => x_return_status, x_msg_count => x_msg_count);
--DBMS_OUTPUT.put_line ('==================================');
--DBMS_OUTPUT.put_line ('Return Status ==>' || x_return_status);
IF ( x_return_status = fnd_api.g_ret_sts_success ) THEN
FOR i IN 1 .. x_item_table.count LOOP
dbms_output.Put_line ('Inventory Item Id :'
|| To_char (X_item_table
(i).inventory_item_id)
||','
-- ||'Lead Time :'||x_item_table (i).FULL_LEAD_TIME||','
-- ||'SPQ :'||x_item_table (i).FIXED_LOT_MULTIPLIER||','
||'PLANNER_CODE :'
||X_item_table (i).planner_code);
--DBMS_OUTPUT.put_line ('Organization Id :' || TO_CHAR (x_item_table (i).organization_id));
END LOOP;
ELSE
dbms_output.Put_line ('Error Messages :');
error_handler.Get_message_list (x_message_list => x_message_list);
FOR i IN 1 .. x_message_list.count LOOP
dbms_output.Put_line (X_message_list (i).message_text);
END LOOP;
END IF;
--DBMS_OUTPUT.put_line ('==================================');
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line ('Exception Occured :');
dbms_output.Put_line (SQLCODE
|| ':'
|| SQLERRM);
dbms_output.Put_line ('=====================================');
END;
