API-INV-Maintain LOCATOR Information in MTL_ITEM_LOCATIONS

原文链接:https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=526674403655264&id=1291412.1&_adf.ctrl-state=1a2emrt00c_84

摘自:https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=526674403655264&id=1291412.1&_adf.ctrl-state=1a2emrt00c_84

Is There An API To Maintain LOCATOR Information in MTL_ITEM_LOCATIONS and MTL_ITEM_SUB_INVENTORIES? (文档 ID 1291412.1)

In this Document

  Goal
  Solution
  References

 

APPLIES TO:

Oracle Item Master - Version 11.5.10.2 and later
Oracle Inventory Management - Version 11.5.10.2 and later
Information in this document applies to any platform.

GOAL

Does there exist an interface or public API that can be used to load / maintain the following tables?

MTL_ITEM_SUB_INVENTORIES
MTL_ITEM_LOCATIONS

SOLUTION

INV_LOC_WMS_PUB is a public package that provides four maintenance APIs  that will:
 A) Create / update / delete locators
 B) Assign items to subinventories / locators.

INV_LOC_WMS_PUB.CREATE_LOCATOR is the public API for creating locators.
INV_LOC_WMS_PUB.UPDATE_LOCATOR is the public API for updating locators.
INV_LOC_WMS_PUB.DELETE_LOCATOR is the public API for deleting locators.
NV_LOC_WMS_PUB.CREATE_LOC_ITEM_TIE is the public API for assigning items to subinventories / locators.

File names for this package are:
INVLOCPS.pls
INVLOCPB.pls

This package appears to work in both WMS (Warehouse Management System) and NON-WMS customers.

See attachment Location API Document for documentation on how to use these APIs.

 

API-INV-Maintain LOCATOR Information in MTL_ITEM_LOCATIONSLOCATION API DOCUMENTATION.txtAPI-INV-Maintain LOCATOR Information in MTL_ITEM_LOCATIONS(20.5 KB)

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/INVLOCPS.pls. 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;

----

上一篇:[SDOI2010]古代猪文


下一篇:hdu7047 /2021“MINIEYE杯”中国大学生算法设计超级联赛(7) 1004 Link with Balls