Hi guys
Currently we are developing a small custom module better controlled sub-inventory transfers. This solution involves an approach as described below:
Our engineering division technicians will receive tools from main warehouse & each technician will have a store locator automatically created when the tools are issued against them for the 1st time.
So the requirement was to create stock locators under the main sub-inventory automatically while the issue happens.
We opted to use INV_LOC_WMS_PUB public API to create stock locators and came across an error, that keep on giving us the below error:
‘Could not create locator.’ and the return status was ‘U’. It looks like the API is not widely used, hence there were hardly many references available online & all we had was the Oracle support knowledge base. Unfortunately most of the documents were not referring obsolete versions like 12.0.x & we decided to go through each and every other document that was dealing with the API.
The we came across a document “Cannot Create Locators Using The INV_LOC_WMS_PUB API (Doc ID 283472.1)”, which had the correct solution for the issues we were facing with the API.
Please note, the below instructions ONLY deal with the situations when the return status from the API is “U”
Using one of the INV super user responsibilities, navigate to:
- Setup -> Flexfields -> Key -> Segments
- Query for “Stock Locators” Flexfield title
- Check whether “Allow Dynamic Inserts” checkbox is selected. If not, select it and save. Now onwards you should able to run the API successfully.
Sample API Script (Adjust to your environment, tested against R12 12.0.6, Database:11gR2)
[code language=”sql” gutter=”false”]
SET DEFINE OFF;
SET SERVEROUTPUT ON SIZE UNLIMITED;
Declare
— Common Declarations
l_api_version NUMBER := 1.0;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2(2000);
— 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) := ‘ABC’;
l_resp_name VARCHAR2(50) := ‘XYZ INV Super User’;
— API specific declarations
x_inventory_location_id NUMBER := NULL;
x_locator_exists VARCHAR2(1) := NULL;
BEGIN
— MTL_ITEM_LOCATIONS
— 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);
dbms_output.put_line(‘Initialized applications context: ‘|| l_user_id || ‘ ‘|| l_resp_id ||’ ‘|| l_application_id );
— call API to update material status
DBMS_OUTPUT.PUT_LINE(‘=======================================================’);
DBMS_OUTPUT.PUT_LINE(‘Calling INV_LOC_WMS_PUB.CREATE_LOCATOR’);
—fnd_profile.put(‘MFG_ORGANIZATION_ID’,245) ;
INV_LOC_WMS_PUB.CREATE_LOCATOR
( x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_inventory_location_id => x_inventory_location_id
, x_locator_exists => x_locator_exists
, p_organization_id => 309 –245
, p_organization_code => ‘202’ — ‘KWH’
, p_concatenated_segments => ‘EMP.2716.’ –‘A5.A5.A5..’
, p_description => ‘RAJESH VARGHESE’
, p_inventory_location_type=> 3 –Type Storage Locator
, p_picking_order => NULL
, p_location_maximum_units => NULL
, p_SUBINVENTORY_CODE => ‘TOOLS’
, p_LOCATION_WEIGHT_UOM_CODE => NULL
, p_mAX_WEIGHT => NULL
, p_vOLUME_UOM_CODE => NULL
, p_mAX_CUBIC_AREA => NULL
, p_x_COORDINATE => NULL
, p_Y_COORDINATE => NULL
, p_Z_COORDINATE => NULL
, p_PHYSICAL_LOCATION_ID => NULL — required when creating logical locators
, p_PICK_UOM_CODE => NULL
, p_DIMENSION_UOM_CODE => NULL
, p_LENGTH => NULL
, p_WIDTH => NULL
, p_HEIGHT => NULL
, p_STATUS_ID => 1
, p_dropping_order => NULL
, p_attribute_category => NULL
, p_attribute1 => NULL
, p_attribute2 => NULL
, p_attribute3 => NULL
, p_attribute4 => NULL
, p_attribute5 => NULL
, p_attribute6 => NULL
, p_attribute7 => NULL
, p_attribute8 => NULL
, p_attribute9 => NULL
, p_attribute10 => NULL
, p_attribute11 => NULL
, p_attribute12 => NULL
, p_attribute13 => NULL
, p_attribute14 => NULL
, p_attribute15 => NULL
, p_alias => NULL );
DBMS_OUTPUT.PUT_LINE(‘=======================================================’);
DBMS_OUTPUT.PUT_LINE(‘Return Status: ‘||x_return_status);
DBMS_OUTPUT.PUT_LINE(‘x_locator_exists: ‘||x_locator_exists||’ x_inventory_location_id:’||x_inventory_location_id);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE(‘Msg Count:’||x_msg_count||’ Error Message :’||x_msg_data);
IF ( x_msg_count > 1 ) THEN
FOR i IN 1 .. x_msg_count LOOP
x_msg_data := fnd_msg_pub.get ( p_msg_index => i , p_encoded =>FND_API.G_FALSE ) ;
dbms_output.put_line ( ‘message :’ || x_msg_data);
END LOOP;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE(‘=======================================================’);
END;
[/code]
