Kuwait commerce department is making it mandatory to include Arabic in the item descriptions and we also started working on this requirement. Our Oracle EBS R12 environment is set for both English and Arabic, hence the only task was to update the Arabic description for the items. We’ve considerably large items database and updating such a huge repository definitely required an API interface. We selected a small set of items for the first attempt and everything worked as expected, however started getting multiple errors when we tried to execute the same API for 10k items. One of the errors as seen below (The question marks should be SQL Developer’s bug) and I was able to pick the words “MTL_ITEM_BULKLOAD_RECS_N2” and “APPS_TS_INTERFACE” from the error message.
EGO_ITEM_PVT Process_Items: ORA-01654: INV.MTL_ITEM_BULKLOAD_RECS_N2 APPS_TS_INTERFACE
Initialized applications context: 1353 50599 401
Error Messages :
??? ??? ??? ????? ?? ????? EGO_ITEM_PVT ?????? Process_Items: ORA-01654: ?? ???? ?? ???? INV.MTL_ITEM_BULKLOAD_RECS_N2 ?????? 16 ?????? ?????? APPS_TS_INTERFACE
???? ??????? ?????? ?????? ?? ?????? ????? ?????
Searched Oracle support documents and couldn’t find anything relevant until I noticed the term “APPS_TS_INTERFACE” & as I handle the Oracle EBS R12 12.2 database myself, immediately assumed the same to be another APPS related tablespace. I was not wrong, the seeded tablespace comes with 2 data files and both were almost full with just few kilobytes left. All I needed was to add new data file with a size of 4GB (didn’t want to take another risk). We’ve been using the application for last 13 years and the tablespace size until the new file was merely 3GBs, hence the new data file should hold the fort for another many years ahead.
We were updating the Arabic description part of the master items and the sample API as given below.
SET DEFINE OFF;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
x_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
l_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
l_role_grant_tbl EGO_ITEM_PUB.ROLE_GRANT_TBL_TYPE;
l_user_name VARCHAR2(30) := 'USERNAME';
l_resp_name VARCHAR2(30) := 'XYZ INV Super User';
l_item_catalog_group_id NUMBER := 0;
--CREATE a staging table with the column names as given in the below select statement.
--If your staging table has different column names, adjust the below select statement.
CURSOR item_list IS
SELECT ORG_ID,INVENTORY_ITEM_ID,ITEM_CODE, DESC_AR FROM XX_AR_DESCRIPTIONS
WHERE NVL(STATUS,'E')='E'; -- for retry purposes.
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_vl
WHERE responsibility_name = l_resp_name;
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);
--Set the language context, here we are updating the item master with Arabic language
--Sample few other languages
--FND_GLOBAL.SET_NLS_CONTEXT(P_NLS_LANGUAGE => 'PORTUGUESE');
--FND_GLOBAL.SET_NLS_CONTEXT(P_NLS_LANGUAGE => 'AMERICAN');
--FND_GLOBAL.SET_NLS_CONTEXT(P_NLS_LANGUAGE => 'JAPANESE');
--Is It Possible to Update Item Description in Local Language in MTL_SYSTEM_ITEMS_TL Using Public API ? (Doc ID 2542546.1)
FND_GLOBAL.SET_NLS_CONTEXT(P_NLS_LANGUAGE => 'ARABIC');
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
FOR i in item_list loop
l_item_tbl(l_rowcnt).Transaction_Type := 'UPDATE';
l_item_tbl(l_rowcnt).inventory_item_id := i.inventory_item_id;
l_item_tbl(l_rowcnt).organization_id := i.org_id;--Should be your master inventory organization id.
--l_item_tbl(l_rowcnt).ATTRIBUTE6 := i.movement;
l_item_tbl(l_rowcnt).Description := i.DESC_AR;
-- call API to load Items
EGO_ITEM_PUB.PROCESS_ITEMS(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_item_tbl => l_item_tbl
,p_role_grant_tbl => l_role_grant_tbl
,x_item_tbl => x_item_tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count);
--How To Clean Or Avoid Error Data In Interface Tables Using EGO_ITEM_PUB.Process_Item (Doc ID 1548555.1)
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
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;
DBMS_OUTPUT.PUT_LINE( i.item_code||' Failed Update'); --comment, only for correction purposes.
--We willl update the staging table with failed status
UPDATE XX_AR_DESCRIPTIONS SET STATUS='E' WHERE INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID;
ELSE
--Update the staging table with success status
UPDATE XX_AR_DESCRIPTIONS SET STATUS='S' WHERE INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID;
END IF;
END loop;
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occurred :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('=====================================');
ROLLBACK;
RETURN;
END;
The following documents were referred during the attempts, which you may not experience.
- Is It Possible to Update Item Description in Local Language in MTL_SYSTEM_ITEMS_TL Using Public API ? (Doc ID 2542546.1)
- How To Clean Or Avoid Error Data In Interface Tables Using EGO_ITEM_PUB.Process_Item (Doc ID 1548555.1)
