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)