Locator Maintenance APIs
In Oracle WMS, available weight capacity, available volume capacity, available
units capacity of a locator is maintained real time. This is essential for an
efficient put away process. During any material transaction, the available weight
capacity, available volume capacity and available units capacity of the transacted
locator is updated suitably based on the total weight and total weight of the
transacted item and maximum weight, maximum volume and maximum units allowed of the
locator. It is hence imperative that all locators used in WMS have weight unit of
measure, maximum weight, volume unit of measure, maximum volume and maximum units
defined.
X, Y and Z coordinates of a locator indicate its physical position in a warehouse.
This information is used in Task Dispatching, a WMS functionality that
intelligently and efficiently dispatches tasks to warehouse personnel real time.
During the course of dispatching, the system considers among other things where
the warehouse personnel is currently. This information is obtained through the
physical position of the last locator that was worked on. Hence the physical
location of a locator is essential for efficient task dispatching.
For existing Oracle Applications customers who are upgrading to Oracle WMS, it is
recommended that a set of check scripts that are provided with the product be run
and necessary corrective action taken before using WMS. These scripts identify pre-
WMS data that may have to be enhanced to better utilize the capabilities of WMS
software. One of the check scripts is Locator check script. It lists locators with
either one of the following not defined:
· Weight unit of measure
· Maximum Weight
· Volume unit of measure
· Maximum Volume
· Maximum Units
· X Coordinate
· Y Coordinate
· Z Coordinate
· Dimension unit of measure
· Length
· Width
· Height
The existing Locators maintenance form could be used for adding the missing
attributes to the definition of the listed locators. But data entry would be time
consuming and cumbersome if the locators list is large. A faster approach would be
to write a script in an appropriate language that would repeatedly call APIs that
perform Locator Maintenance passing the required data.
Currently in Oracle Inventory, items can be restricted to certain locators, but
with Oracle WMS the same functionality is used to assign items to locators to
create a kind of soft assignment that can be used by the Rules Engine.
Currently in Oracle Inventory, locators cannot be deleted. There may be a need to
delete locators that are obsolete. This may reduce the volume of data and also
improve performance. But then its very essential that before deleting, its
confirmed that the locator to be deleted doesn't exist in any core Inventory table.
Locator Maintenance APIs handle the above said requirements. They constitute the
following:
· Create Locator API to create a new locator (CREATE_LOCATOR)
· Update Locator API to update an existing locator (UPDATE_LOCATOR)
· Locator Item Tie API to assign an item to a locator (CREATE_LOC_ITEM_TIE)
- Delete Locator API to delete an existing locator (DELETE_LOCATOR)
The APIs are part of PL/SQL package INV_LOC_WMS_PUB. This is defined in
$INV_TOP/patch/115/sql/[Link]. Below is a description of each API and their
usage with examples.
----
Procedure:
CREATE_LOCATOR
Package:
INV_LOC_WMS_PUB
Technology:
Oracle PL/SQL
Description:
For given concatenated locator segments and organization, this API will create a
new locator in the organization and return the locator identifier. If a locator
already exists with the same concatenated segments, the API returns the locator
identifier.
Signature:
CREATE_LOCATOR (
x_return_status OUT VARCHAR2
,x_msg_count OUT NUMBER
,x_msg_data OUT VARCHAR2
,x_inventory_location_id OUT NUMBER
,x_locator_exists OUT VARCHAR2
,p_organization_id IN NUMBER
,p_organization_code IN VARCHAR2
,p_concatenated_segments IN VARCHAR2
,p_description IN VARCHAR2
,p_inventory_location_type IN NUMBER
,p_picking_order IN NUMBER
,p_location_maximum_units IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_location_weight_uom_code IN VARCHAR2
,p_max_weight IN NUMBER
,p_volume_uom_code IN VARCHAR2
,p_max_cubic_area IN NUMBER
,p_x_coordinate IN NUMBER
,p_y_cordinate IN NUMBER
,p_z_coordinate IN NUMBER
,p_physical_location_id IN NUMBER
,p_pick_uom_code IN VARCHAR2
,p_dimension_uom_code IN VARCHAR2
,p_length IN NUMBER
,p_width IN NUMBER
,p_height IN NUMBER
,p_status_id IN NUMBER);
Output Parameters Description:
x_return_status:
return status indicating success('S'), error('E'), unexpected error('U')
x_msg_count:
number of messages in message list
x_msg_data:
if the number of messages in message list is 1, contains message text
x_inventory_location_id:
identifier of newly created locator or existing locator
x_locator_exists:
'Y' - locator exists for given input
'N' - locator created for given input
Input Parameters Description:
p_organization_id:
identifier of organization in which locator is to be created
p_organization_code:
organization code of organization in which locator is to be created. Either
p_organization_id or p_organization_code MUST be passed .
p_concatenated_segments:
concatenated segment string with separator of the locator to be created. Eg:A.1.1
p_description:
locator description
p_inventory_location_type:
Type of locator. The valid values are dock door(1) or staging lane(2) or storage
locator(3)
p_picking_order:
number that identifies relative position of locator for travel optimization during
task dispatching. It has a higher precedence over x,y,z coordinates.
p_location_maximum_units:
Maxmimum units the locator can hold
p_subinventory_code:
Subinventory to which locator belongs
p_location_weight_uom_code:
UOM of locator's max weight capacity
p_max_weight:
Max weight locator can hold
p_volume_uom_code:
UOM of locator's max volume capacity
p_max_cubic_area:
Max volume capacity of the locator
p_x_coordinate:
X-position of the locator in space. Used for travel optimization during task
dispatching.
p_y_coordinate:
Y-position of the locator in space. Used for travel optimization during task
dispatching.
p_z_coordinate:
Z-position of the locator in space. Used for travel optimization during task
dispatching.
p_physical_location_id:
locators that are the same physically have the same inventory_location_id in this
column.
p_pick_uom_code:
UOM in which material is picked from locator
p_dimension_uom_code:
UOM in which locator dimensions are expressed
p_length:
Length of the locator
p_width:
Width of the locator
p_height:
Height of the locator
p_status_id:
Material Status that needs to be associated to locator
Example:
The use of CREATE_LOCATOR API is illustrated with the following piece of code that
creates a locator 'A.1.1' in Organization 'WH1' and subinventory 'BULK'.
declare
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_inventory_location_id NUMBER;
l_locator_exists VARCHAR2(1);
begin
INV_LOC_WMS_PUB.CREATE_LOCATOR (
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_inventory_location_id => l_inventory_location_id
,x_locator_exists => l_locator_exists
,p_organization_id => NULL
,p_organization_code => 'WH1'
,p_concatenated_segments => 'A.1.1'
,p_description => 'Locator A.1.1'
,p_inventory_location_type => 3 /* Storage Locator */
,p_picking_order => 4
,p_location_maximum_units => 10
,p_subinventory_code => 'BULK'
,p_location_weight_uom_code => 'Kg'
,p_max_weight => 100
,p_volume_uom_code => 'ml'
,p_max_cubic_area => 10000
,p_x_coordinate => 10
,p_y_cordinate => 1
,p_z_coordinate => 2
,p_physical_location_id => NULL
,p_pick_uom_code => 'Ea'
,p_dimension_uom_code => 'm'
,p_length => 1
,p_width => 1
,p_height => 1
,p_status_id => 1 /* Active */);
IF (l_return_status = 'S') then
// Success. Process Locator Id and check if locator existed
dbms_output.put_line('Locator Id:' || l_inventory_location_id);
dbms_output.put_line('Locator exists:' || l_locator_exists);
ELSIF (lv_msg_count = 1) THEN
// Error. Process message.
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
ELSE
// Error. Process messages
For I in 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(lv_msg_data,chr(0),' '));
END LOOP;
END IF;
end;
----
Procedure:
UPDATE_LOCATOR
Package:
INV_LOC_WMS_PUB
Technology:
Oracle PL/SQL
Description:
This API will update an existing locator with the information provided as input
parameters. If the default value is passed, the corresponding locator column will
retain its original value. This can be achieved by not passing that parameter
during the API call.
Signature:
UPDATE_LOCATOR (
x_return_status OUT VARCHAR2
,x_msg_count OUT NUMBER
,x_msg_data OUT VARCHAR2
,p_organization_id IN NUMBER
,p_organization_code IN VARCHAR2
,p_inventory_location_id IN NUMBER
,p_concatenated_segments IN VARCHAR2
,p_description IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
,p_disabled_date IN DATE DEFAULT FND_API.G_MISS_DATE
,p_inventory_location_type IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_picking_order IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_location_maximum_units IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_location_weight_uom_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
,p_max_weight IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_volume_uom_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
,p_max_cubic_area IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_x_coordinate IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_y_coordinate IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_z_coordinate IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_physical_location_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_pick_uom_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
,p_dimension_uom_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
,p_length IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_width IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_height IN NUMBER DEFAULT FND_API.G_MISS_NUM
,p_status_id IN NUMBER DEFAULT FND_API.G_MISS_NUM);
Output Parameters Description:
x_return_status:
return status indicating success('S'), error('E'), unexpected error('U')
x_msg_count:
number of messages in message list
x_msg_data:
if the number of messages in message list is 1, contains message text
Input Parameters Description:
p_organization_id:
Identifier of organization in which locator is to be updated.
p_organization_code:
Organization code of organization in which locator is to be updated. Either
p_organization_id or p_organization_code MUST be passed
p_inventory_location_id:
Identifier of locator to be updated
p_concatenated_segments:
Concatenated segment string with separator of the locator to be updated. Eg:A.1.1
either p_inventory_location_id or p_concatenated_segments MUST be passed.
p_description:
Locator description
p_inventory_location_type:
Type of locator. The valid values are dock door(1) or staging lane(2) or storage
locator(3)
p_picking_order:
Number that identifies physical position of locator for travel optimization during
task dispatching. It has a higher precedence over x,y,z coordinates.
p_location_maximum_units:
Maximum units the locator can hold
p_subinventory_code:
Subinventory to which locator belongs
p_location_weight_uom_code:
UOM of locator's max weight capacity
p_max_weight:
Max weight locator can hold
p_volume_uom_code:
UOM of locator's max volume capacity
p_max_cubic_area:
Max volume capacity of the locator
p_x_coordinate:
X-position of the locator in space. Used for travel optimization during task
dispatching.
p_y_coordinate:
Y-position of the locator in space. Used for travel optimization during task
dispatching.
p_z_coordinate:
Z-position of the locator in space. Used for travel optimization during task
dispatching.
p_physical_location_id:
locators that are the same physically have the same inventory_location_id in this
column
p_pick_uom_code:
UOM in which material is picked from locator
p_dimension_uom_code:
UOM in which locator dimensions are expressed.
p_length:
Length of the locator
p_width:
Width of the locator
p_height:
Height of the locator
p_status_id:
Material Status that needs to be associated to locator
Example:
The use of UPDATE_LOCATOR API is illustrated with the following piece of code. It
updates the picking order of locator 'A.1.1', created in previous example to 5.
Existing values for other columns are retained. Locator 'A.1.1' is in Organization
'WH1' and subinventory 'BULK'.
declare
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
begin
INV_LOC_WMS_PUB.UPDATE_LOCATOR (
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_organization_id => NULL
,p_organization_code => 'WH1'
,p_inventory_location_id => NULL
,p_concatenated_segments => 'A.1.1'
,p_picking_order => 5);
IF (l_return_status = 'S') then
// Success.
dbms_output.put_line('Successful Update');
ELSIF (lv_msg_count = 1) THEN
// Error. Process message.
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
ELSE
// Error. Process messages
For I in 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(lv_msg_data,chr(0),' '));
END LOOP;
END IF;
end;
----
Procedure:
CREATE_LOC_ITEM_TIE
Package:
INV_LOC_WMS_PUB
Technology:
Oracle PL/SQL
Description:
For a given set of organization, subinventory, item and locator, this API ties the
given item to the given locator.
Signature:
CREATE_LOC_ITEM_TIE(
x_return_status OUT VARCHAR2
,x_msg_count OUT NUMBER
,x_msg_data OUT VARCHAR2
,p_inventory_item_id IN NUMBER
,p_item IN VARCHAR2
,p_organization_id IN NUMBER
,p_organization_code IN VARCHAR2
,p_subinventory_code IN VARCHAR2
,p_inventory_location_id IN NUMBER
,p_locator IN VARCHAR2
,p_status_id IN NUMBER);
Output Parameters Description:
x_return_status:
return status indicating success('S'), error('E'), unexpected error('U')
x_msg_count:
number of messages in message list
x_msg_data:
if the number of messages in message list is 1, contains message text
Input Parameters Description:
p_inventory_item_id:
Identifier of item
p_item:
Concatenated segment string with separator of the item. Either P_inventory_item_id
or the p_item MUST be passed
p_organization_id:
Identifier of organization
p_organization_code:
Organization code of organization in which locator is to be updated. Either
p_organization_id or p_organization_code MUST be passed
p_subinventory_code:
The subinventory to which the tied locator belongs
p_inventory_location_id:
Identifier of locator to be attached to the specified subinventory
p_locator:
Concatenated segment string with separator of the locator to be updated. Eg:A.1.1
either p_inventory_location_id or p_concatenated_segments MUST be passed.
p_status_id: Identifier of locator material status
Example:
The use of CREATE_LOC_ITEM_TIE API is illustrated with the following piece of code.
It ties item 'BOX101' to locator 'A.1.1', created in a previous example. Locator
'A.1.1' is in Organization 'WH1' and subinventory 'BULK'.
declare
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
begin
INV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE(
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_inventory_item_id => NULL
,p_item => 'BOX101'
,p_organization_id => NULL
,p_organization_code => 'WH1'
,p_subinventory_code => 'BULK'
,p_inventory_location_id => NULL
,p_locator => 'A.1.1'
,p_status_id => NULL);
IF (l_return_status = 'S') then
// Success.
dbms_output.put_line('Successful Update');
ELSIF (lv_msg_count = 1) THEN
// Error. Process message.
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
ELSE
// Error. Process messages
For I in 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(lv_msg_data,chr(0),' '));
END LOOP;
END IF;
end;
----
Procedure:
DELETE_LOCATOR
Package:
INV_LOC_WMS_PUB
Technology:
Oracle PL/SQL
Description:
This API will delete an existing locator. This is done after ensuring that the
locator is obsolete and doesn't exist in any core Inventory tables.
Signature:
DELETE_LOCATOR(
x_return_status OUT VARCHAR2
, x_msg_count OUT NUMBER
, x_msg_data OUT VARCHAR2
, p_inventory_location_id IN NUMBER
, p_concatenated_segments IN VARCHAR2
, p_organization_id IN NUMBER
, p_organization_code IN VARCHAR2
, p_validation_req_flag IN VARCHAR2 DEFAULT 'Y' )
Output Parameters Description:
x_return_status:
return status indicating success('S'), error('E'), unexpected error('U')
x_msg_count:
number of messages in message list
x_msg_data:
if the number of messages in message list is 1, contains message text
Input Parameters Description:
p_inventory_location_id:
Identifier of locator to be deleted.
p_concatenated_segments:
Concatenated segment string with separator of the locator to be deleted. Eg:A.1.1
Either p_inventory_location_id or p_concatenated_segments must be passed.
p_organization_id:
Identifier of organization in which locator is to be deleted.
p_organization_code:
Organization code of organziation in which locator is to be deleted.
Either p_organization_id or p_organziation_code MUST be passed
p_validation_req_flag:
Flag which determines whether validation is required or not. If it is 'N',the
locator is deleted without any further validation on its existence in core
Inventory tables. If it is'Y', the locator is deleted only if does not exist in
core Inventory tables.
Example:
The use of DELETE_LOCATOR API is illustrated with the following piece of code. It
deletes an obsolete locator, 'B.1.1'. Locator 'B.1.1' is in Organization 'WH1'.
declare
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
begin
INV_LOC_WMS_PUB.DELETE_LOCATOR(
x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_inventory_location_id => NULL
,p_concatenated_segments => 'B.1.1'
,p_organization_id => NULL
,p_organization_code => 'WH1'
,p_validation_req_flag => 'Y');
IF (l_return_status = 'S') then
// Success.
dbms_output.put_line('Successful Update');
ELSIF (lv_msg_count = 1) THEN
// Error. Process message.
dbms_output.put_line(replace(l_msg_data,chr(0),' '));
ELSE
// Error. Process messages
For I in 1..l_msg_count LOOP
l_msg_data := fnd_msg_pub.get(I,'F');
dbms_output.put_line(replace(lv_msg_data,chr(0),' '));
END LOOP;
END IF;
end;
----