[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Table to pass DFF attributes to create_locator and update_locator apis
**/
TYPE char_tbl IS TABLE OF VARCHAR2(1500) INDEX BY BINARY_INTEGER;
SELECT count(1) into l_dummy
FROM mtl_material_transactions_temp
WHERE organization_id = p_org_id
AND locator_id = p_loc_id
AND ROWNUM =1 ;
SELECT count(1) into l_dummy
FROM mtl_material_transactions_temp
WHERE transfer_organization = p_org_id
AND transfer_to_location = p_loc_id
AND ROWNUM =1 ;
SELECT count(1) INTO l_dummy
FROM mtl_onhand_quantities_detail
WHERE locator_id = p_loc_id
AND organization_id = p_org_id
AND ROWNUM =1 ;
SELECT count(1) INTO l_dummy
FROM mtl_system_items_b
WHERE organization_id = p_org_id
AND wip_supply_locator_id = p_loc_id
AND rownum = 1;
l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
SAVEPOINT locator_insert;
SELECT enforce_locator_alis_unq_flag
INTO l_org_alias_uniqueness
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT organization_id,
enforce_locator_alis_unq_flag
INTO l_organization_id,
l_org_alias_uniqueness
FROM mtl_parameters
WHERE organization_code = p_organization_code;
SELECT secondary_inventory_name,
enable_locator_alias,
enforce_alias_uniqueness,
NVL(subinventory_type, 1)
INTO l_subinventory_code,
l_alias_enabled,
l_sub_alias_uniqueness,
l_subinventory_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = l_organization_id;
SELECT concatenated_segments
INTO l_locator
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND alias = l_alias
AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
SELECT concatenated_segments
INTO l_locator
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND alias = l_alias
AND subinventory_code = p_subinventory_code
AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
SELECT inventory_location_id
INTO x_inventory_location_id
FROM mtl_item_locations_kfv
WHERE organization_id = l_organization_id
AND subinventory_code = p_subinventory_code
AND concatenated_segments = p_concatenated_segments
AND ROWNUM < 2;
SELECT status_id
INTO l_status_id
FROM mtl_material_statuses_vl
WHERE status_id = p_status_id
AND enabled_flag = 1;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_location_weight_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_volume_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_pick_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_dimension_uom_code;
/* SELECT 'FAILED'
INTO l_validity_check
FROM DUAL
WHERE EXISTS( */
-- We need to check the pphysical locator isntead of the Logical locator .
-- root cause of duplicate records in other subinventories ..
-- Bug 7581675
l_physical_concat_seg := INV_PROJECT.GET_LOCSEGS(p_concatenated_segments);
SELECT subinventory_code
INTO l_subinventory_code
FROM mtl_item_locations_kfv
WHERE concatenated_segments like l_physical_concat_seg||'%' -- instead of p_concatenated_segments
AND p_subinventory_code <> subinventory_code
AND organization_id = l_organization_id
AND ROWNUM = 1 ;
SELECT NVL(default_loc_status_id, 1)
INTO l_status_id
FROM mtl_secondary_inventories
WHERE organization_id = l_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT project_reference_enabled , project_control_level
INTO l_project_reference_enabled, l_project_control_level
FROM PJM_ORG_PARAMETERS_V
WHERE organization_id= l_organization_id;
SELECT segment19,segment20
INTO l_segment19,l_segment20
FROM mtl_item_locations
WHERE organization_id = l_organization_id
AND inventory_location_id = x_inventory_location_id;
UPDATE mtl_item_locations
SET subinventory_code = NVL(p_subinventory_code, NULL)
, status_id = NVL(l_status_id, NULL)
, inventory_location_type = NVL(l_loc_type, NULL)
, description = NVL(p_description, NULL)
, picking_order = NVL(p_picking_order, NULL)
, location_maximum_units = NVL(p_location_maximum_units, NULL)
, location_weight_uom_code = NVL(p_location_weight_uom_code, NULL)
, max_weight = NVL(p_max_weight, NULL)
, volume_uom_code = NVL(p_volume_uom_code, NULL)
, max_cubic_area = NVL(p_max_cubic_area, NULL)
, x_coordinate = NVL(p_x_coordinate, NULL)
, y_coordinate = NVL(p_y_coordinate, NULL)
, z_coordinate = NVL(p_z_coordinate, NULL)
, physical_location_id = NVL(p_physical_location_id, NULL)
, pick_uom_code = NVL(p_pick_uom_code, NULL)
, dimension_uom_code = NVL(p_dimension_uom_code, NULL)
, LENGTH = NVL(p_length, NULL)
, width = NVL(p_width, NULL)
, height = NVL(p_height, NULL)
, dropping_order = NVL(p_dropping_order, NULL)
, creation_date = SYSDATE
, created_by = fnd_global.user_id
, last_updated_by = fnd_global.user_id
, last_update_date = SYSDATE
, attribute_category = p_attribute_category
, attribute1 = p_attribute1
, attribute2 = p_attribute2
, attribute3 = p_attribute3
, attribute4 = p_attribute4
, attribute5 = p_attribute5
, attribute6 = p_attribute6
, attribute7 = p_attribute7
, attribute8 = p_attribute8
, attribute9 = p_attribute9
, attribute10 =p_attribute10
, attribute11 =p_attribute11
, attribute12 =p_attribute12
, attribute13 =p_attribute13
, attribute14 =p_attribute14
, attribute15 =p_attribute15
, alias = l_alias
WHERE organization_id = l_organization_id
AND inventory_location_id = x_inventory_location_id;
l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
l_status_rec.last_update_date := SYSDATE;
l_status_rec.last_update_login := fnd_global.user_id;
inv_material_status_pkg.insert_status_history(l_status_rec);
ROLLBACK TO locator_insert;
ROLLBACK TO locator_insert;
ROLLBACK TO locator_insert;
** procedure : update_locator
** description : this procedure updates an existing locator
**
** i/p :
** NOTE:
** if the default value of the input parameter is used, then
** that column retains its original value and is not changed
** during update.
** this can be achieved by not passing this parameter during the
** API call.
**
** p_organization_id
** identifier of organization in which locator is to
** be updated.
** p_organization_code
** organization code of organziation in which locator
** is to be updated. Either p_organization_id or
** p_organziation_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.
** 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 picking and task dispatching.
** It has a 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 picking and task dispatching.
** p_y_coordinate
** Y-position of the locator in space. Used
** for travel optimization during picking and task dispatching.
** p_z_coordinate
** Z-position of the locator in space. Used
** for travel optimization during picking and 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
** p_dropping_order
** For ordering drop-off locators and also to order by putaway
** drop-off operations (bug 2681871)
** For the DFF attributes mentioned below, to update correctly use the following strategy
** To retain the value in the table, do not pass any value OR pass NULL as i/p
** To update the attribute with NULL, pass fnd_api.g_miss_char
** To update with any other value, pass the appropriate value
** p_attribute_category Holds the Context of the Descriptive FlexField for the Locator
** p_attribute1 Holds the Descriptive FlexField attribute for the Locator
** p_attribute2 Holds the Descriptive FlexField attribute for the Locator
** p_attribute3 Holds the Descriptive FlexField attribute for the Locator
** p_attribute4 Holds the Descriptive FlexField attribute for the Locator
** p_attribute5 Holds the Descriptive FlexField attribute for the Locator
** p_attribute6 Holds the Descriptive FlexField attribute for the Locator
** p_attribute7 Holds the Descriptive FlexField attribute for the Locator
** p_attribute8 Holds the Descriptive FlexField attribute for the Locator
** p_attribute9 Holds the Descriptive FlexField attribute for the Locator
** p_attribute10 Holds the Descriptive FlexField attribute for the Locator
** p_attribute11 Holds the Descriptive FlexField attribute for the Locator
** p_attribute12 Holds the Descriptive FlexField attribute for the Locator
** p_attribute13 Holds the Descriptive FlexField attribute for the Locator
** p_attribute14 Holds the Descriptive FlexField attribute for the Locator
** p_attribute15 Holds the Descriptive FlexField attribute for the Locator
**
** o/p:
** x_return_status
** return status indicating success, error, unexpected error
** 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
**
** ---------------------------------------------------------------------------
*/
PROCEDURE UPDATE_LOCATOR (x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY 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 ,
p_disabled_date IN DATE ,
p_inventory_location_type IN NUMBER ,
p_picking_order IN NUMBER ,
p_location_maximum_units IN NUMBER ,
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_coordinate 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 ,
p_dropping_order IN NUMBER ,
p_attribute_category IN VARCHAR2 ,
p_attribute1 IN VARCHAR2
, p_attribute2 IN VARCHAR2
, p_attribute3 IN VARCHAR2
, p_attribute4 IN VARCHAR2
, p_attribute5 IN VARCHAR2
, p_attribute6 IN VARCHAR2
, p_attribute7 IN VARCHAR2
, p_attribute8 IN VARCHAR2
, p_attribute9 IN VARCHAR2
, p_attribute10 IN VARCHAR2
, p_attribute11 IN VARCHAR2
, p_attribute12 IN VARCHAR2
, p_attribute13 IN VARCHAR2
, p_attribute14 IN VARCHAR2
, p_attribute15 IN VARCHAR2
, p_alias IN VARCHAR2
) AS
l_organization_id NUMBER;
l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
SAVEPOINT locator_update;
l_procedure_name := 'UPDATE_LOCATOR';
SELECT enforce_locator_alis_unq_flag
INTO l_org_alias_uniqueness
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT organization_id,
enforce_locator_alis_unq_flag
INTO l_organization_id,
l_org_alias_uniqueness
FROM mtl_parameters
WHERE organization_code = p_organization_code;
SELECT inventory_location_id
INTO l_inventory_location_id
FROM MTL_ITEM_LOCATIONS_KFV
WHERE concatenated_segments = p_concatenated_segments
and organization_id = l_organization_id;
SELECT NVL(msi.subinventory_type, 1)
INTO l_subinventory_type
FROM mtl_item_locations mil, mtl_secondary_inventories msi
WHERE mil.inventory_location_id = l_inventory_location_id
AND mil.organization_id = l_organization_id
AND mil.subinventory_code = msi.secondary_inventory_name
AND msi.organization_id = l_organization_id;
SELECT status_id,
subinventory_code
INTO l_status_id,
l_subinventory_code
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
AND organization_id = l_organization_id;
SELECT enable_locator_alias,
enforce_alias_uniqueness
INTO l_alias_enabled,
l_sub_alias_uniqueness
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_subinventory_code
AND organization_id = l_organization_id;
SELECT concatenated_segments
INTO l_locator
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND alias = l_alias
AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
SELECT concatenated_segments
INTO l_locator
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND alias = l_alias
AND subinventory_code = l_subinventory_code
AND NVL(physical_location_id,inventory_location_id) = inventory_location_id;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_location_weight_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_volume_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_pick_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_units_of_measure
WHERE uom_code = p_dimension_uom_code;
SELECT 1
INTO l_chkflg
FROM mtl_material_statuses_vl
WHERE status_id = p_status_id
AND enabled_flag = 1;
We will update the MTL_ITEM_LOCATIONS
table with the information provided
*/
--bug 7297065
IF (p_disabled_date is not null ) THEN
l_is_disable_allow := is_disable_allow(p_org_id =>l_organization_id,
p_loc_id=> l_inventory_location_id
);
UPDATE mtl_item_locations
SET description = DECODE(p_description, fnd_api.g_miss_char, description, p_description)
, disable_date = DECODE(p_disabled_date, fnd_api.g_miss_date, disable_date, p_disabled_date)
, inventory_location_type =
DECODE(
p_inventory_location_type
, fnd_api.g_miss_num, inventory_location_type
, p_inventory_location_type
)
, picking_order = DECODE(p_picking_order, fnd_api.g_miss_num, picking_order, p_picking_order)
, location_maximum_units = DECODE(p_location_maximum_units, fnd_api.g_miss_num, location_maximum_units, p_location_maximum_units)
, location_weight_uom_code =
DECODE(
p_location_weight_uom_code
, fnd_api.g_miss_char, location_weight_uom_code
, p_location_weight_uom_code
)
, max_weight = DECODE(p_max_weight, fnd_api.g_miss_num, max_weight, p_max_weight)
, volume_uom_code = DECODE(p_volume_uom_code, fnd_api.g_miss_char, volume_uom_code, p_volume_uom_code)
, max_cubic_area = DECODE(p_max_cubic_area, fnd_api.g_miss_num, max_cubic_area, p_max_cubic_area)
, x_coordinate = DECODE(p_x_coordinate, fnd_api.g_miss_num, x_coordinate, p_x_coordinate)
, y_coordinate = DECODE(p_y_coordinate, fnd_api.g_miss_num, y_coordinate, p_y_coordinate)
, z_coordinate = DECODE(p_z_coordinate, fnd_api.g_miss_num, z_coordinate, p_z_coordinate)
, pick_uom_code = DECODE(p_pick_uom_code, fnd_api.g_miss_char, pick_uom_code, p_pick_uom_code)
, dimension_uom_code = DECODE(p_dimension_uom_code, fnd_api.g_miss_char, dimension_uom_code, p_dimension_uom_code)
, LENGTH = DECODE(p_length, fnd_api.g_miss_num, LENGTH, p_length)
, width = DECODE(p_width, fnd_api.g_miss_num, width, p_width)
, height = DECODE(p_height, fnd_api.g_miss_num, height, p_height)
, status_id = DECODE(p_status_id, fnd_api.g_miss_num, status_id, p_status_id)
, dropping_order = DECODE(p_dropping_order, fnd_api.g_miss_num, dropping_order, p_dropping_order)
, last_updated_by = fnd_global.user_id
, last_update_date = SYSDATE
, attribute_category = decode(p_attribute_category, NULL, attribute_category, fnd_api.g_miss_char, NULL, p_attribute_category)
, attribute1 = decode(p_attribute1, NULL, attribute1, fnd_api.g_miss_char, NULL, p_attribute1)
, attribute2 = decode(p_attribute2, NULL, attribute2, fnd_api.g_miss_char, NULL, p_attribute2)
, attribute3 = decode(p_attribute3, NULL, attribute3, fnd_api.g_miss_char, NULL, p_attribute3)
, attribute4 = decode(p_attribute4, NULL, attribute4, fnd_api.g_miss_char, NULL, p_attribute4)
, attribute5 = decode(p_attribute5, NULL, attribute5, fnd_api.g_miss_char, NULL, p_attribute5)
, attribute6 = decode(p_attribute6, NULL, attribute6, fnd_api.g_miss_char, NULL, p_attribute6)
, attribute7 = decode(p_attribute7, NULL, attribute7, fnd_api.g_miss_char, NULL, p_attribute7)
, attribute8 = decode(p_attribute8, NULL, attribute8, fnd_api.g_miss_char, NULL, p_attribute8)
, attribute9 = decode(p_attribute9, NULL, attribute9, fnd_api.g_miss_char, NULL, p_attribute9)
, attribute10 = decode(p_attribute10, NULL, attribute10, fnd_api.g_miss_char, NULL, p_attribute10)
, attribute11 = decode(p_attribute11, NULL, attribute11, fnd_api.g_miss_char, NULL, p_attribute11)
, attribute12 = decode(p_attribute12, NULL, attribute12, fnd_api.g_miss_char, NULL, p_attribute12)
, attribute13 = decode(p_attribute13, NULL, attribute13, fnd_api.g_miss_char, NULL, p_attribute13)
, attribute14 = decode(p_attribute14, NULL, attribute14, fnd_api.g_miss_char, NULL, p_attribute14)
, attribute15 = decode(p_attribute15, NULL, attribute15, fnd_api.g_miss_char, NULL, p_attribute15)
, alias = l_alias
WHERE inventory_location_id = l_inventory_location_id
AND organization_id = l_organization_id;
l_status_rec.update_method := inv_material_status_pub.g_update_method_manual;
l_status_rec.last_update_date := SYSDATE;
l_status_rec.last_update_login := fnd_global.user_id;
inv_material_status_pkg.insert_status_history(l_status_rec);
ROLLBACK TO locator_update;
ROLLBACK TO locator_update;
ROLLBACK TO locator_update;
END update_locator;
** be updated. Either p_organization_id or p_organziation_code
** MUST be passed
** p_subinventory_code
** The subinventory to which the locator need to be attached to .
** 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 status
** p_par_level
** PAR level for the item-locator. Valid only when the subinventory is PAR planned.
**
** o/p:
**
** x_return_status
** return status indicating success, error, unexpected error
** 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
**
** ---------------------------------------------------------------------------
*/
PROCEDURE create_loc_item_tie(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY 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
, p_par_level IN NUMBER DEFAULT NULL
) AS
l_inventory_item_id NUMBER;
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = p_organization_code;
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item
AND organization_id = l_organization_id;
SELECT NVL(planning_level, 2)
INTO l_planning_level
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = l_organization_id;
SELECT inventory_location_id
INTO l_locator_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_locator
AND organization_id = l_organization_id;
SELECT 'VALID'
INTO l_subflag
FROM mtl_item_locations
WHERE inventory_location_id = l_locator_id
AND subinventory_code = p_subinventory_code
AND organization_id = l_organization_id;
SELECT 'Y'
INTO l_locator_exists
FROM mtl_secondary_locators
WHERE secondary_locator = l_locator_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id;
SELECT 1
INTO l_status_chk
FROM mtl_material_statuses_vl
WHERE status_id = p_status_id
AND enabled_flag = 1;
SELECT 1
INTO l_item_sub
FROM mtl_item_sub_inventories
WHERE inventory_item_id = l_inventory_item_id
AND secondary_inventory = p_subinventory_code
AND organization_id = l_organization_id;
INSERT INTO mtl_item_sub_inventories
(
inventory_item_id
, organization_id
, secondary_inventory
, last_update_date
, last_updated_by
, creation_date
, created_by
, inventory_planning_code
)
VALUES (
l_inventory_item_id
, l_organization_id
, p_subinventory_code
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, 6
);
/* If the l_locator_flag is N then insert a row into MTL_SECONDARY_LOCATORS */
IF l_locator_exists = 'N' THEN
IF (inv_control.g_current_release_level >= inv_release.g_j_release_level) THEN
INSERT INTO mtl_secondary_locators
(
inventory_item_id
, organization_id
, secondary_locator
, last_update_date
, last_updated_by
, creation_date
, created_by
, subinventory_code
, status_id
, maximum_quantity
)
VALUES (
l_inventory_item_id
, l_organization_id
, l_locator_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, p_subinventory_code
, p_status_id
, DECODE(l_planning_level, 1, p_par_level, NULL)
);
INSERT INTO mtl_secondary_locators
(
inventory_item_id
, organization_id
, secondary_locator
, last_update_date
, last_updated_by
, creation_date
, created_by
, subinventory_code
, status_id
)
VALUES (
l_inventory_item_id
, l_organization_id
, l_locator_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, p_subinventory_code
, p_status_id
);
/* Locator selected has already been assigned to this item and subinventory */
fnd_msg_pub.ADD;
** procedure : delete_locator
** description : this procedure deletes a locator in a given organization.
** i/p
** 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
** 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
** the 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 other tables.If it is'Y', the locator is deleted
** only if doesnot exist in other tables.
**
** o/p
** x_return_status
** return status indicating success, error, unexpected error
** 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
**
**-----------------------------------------------------------------------------------
*/
PROCEDURE delete_locator(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY 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
) IS
/* Locator details */
l_inventory_location_id NUMBER;
SELECT 1
INTO l_chk_org
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_parameters
WHERE organization_id = p_organization_id);
SELECT organization_id
INTO l_organization_id
FROM mtl_parameters
WHERE organization_code = p_organization_code;
SELECT 1
INTO l_chk_loc
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_item_locations
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = l_organization_id);
SELECT 1
INTO l_physical_locator_id
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_item_locations
WHERE physical_location_id = l_inventory_location_id
AND organization_id = l_organization_id
AND inventory_location_id <> physical_location_id); --Bug :5036570
/*Locator LOCATOR cannot be deleted as it exists as a Physical Locator to Some other Locators*/
fnd_msg_pub.ADD;
SELECT 1
INTO l_active_loc
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
AND organization_id = l_organization_id
AND NVL(disable_date, SYSDATE + 1) > SYSDATE;
/*Locator locator cannot be deleted as it is active */
fnd_msg_pub.ADD;
delete the locator from MTL_ITEM_LOCATIONS table without validating further .
Otherwise do the following steps */
IF p_validation_req_flag = 'Y' THEN --If For Validation
/* Check if the locator is present in MTL_ONHAND_QUANTITIES_DETAIL
If the locator_id exists then error out*/
BEGIN
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_onhand_quantities_detail
WHERE locator_id = l_inventory_location_id
AND organization_id = l_organization_id);
/*Locator locator cannot be deleted as items exist in it*/
fnd_msg_pub.ADD;
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_reservations
WHERE locator_id = l_inventory_location_id
AND organization_id = l_organization_id);
/*The locator locator cannot be deleted as reservations exist against it*/
fnd_msg_pub.ADD;
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_license_plate_numbers
WHERE locator_id = l_inventory_location_id
AND organization_id = l_organization_id);
/*Locator locator cannot be deleted as LPNs reside in it*/
fnd_msg_pub.ADD;
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_secondary_locators
WHERE secondary_locator = l_inventory_location_id
AND organization_id = l_organization_id);
/*Locator locator cannot be deleted as it is tied to an item */
fnd_msg_pub.ADD;
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_transactions_interface
WHERE locator_id = l_inventory_location_id
AND organization_id = l_organization_id);
/*Locator locator cannot be deleted as there are pending transactions against it*/
fnd_msg_pub.ADD;
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_material_transactions_temp
WHERE (locator_id = l_inventory_location_id
AND organization_id = l_organization_id)
OR(transfer_to_location = l_inventory_location_id
AND NVL(transfer_organization, organization_id) = l_organization_id));
/*Locator locator cannot be deleted as there are pending transactions against it*/
fnd_msg_pub.ADD;
SELECT 1
INTO l_chk_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM rcv_transactions_interface
WHERE locator_id = l_inventory_location_id
AND to_organization_id = l_organization_id);
/*Locator locator cannot be deleted as there are pending transactions against it*/
fnd_msg_pub.ADD;
/* If all the above steps are success then delete the inventory_location_id
from MTL_ITEM_LOCATIONS
for the combination of inventory_location_id and organization_id*/
DELETE mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
AND organization_id = l_organization_id;
fnd_msg_pub.add_exc_msg('inv_loc_wms_pub', 'delete_locator');
END delete_locator;