[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Select all required values at same time
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id; */
SELECT
physical_location_id,
inventory_location_id,
location_maximum_units,
location_current_units,
location_available_units,
location_suggested_units,
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight,
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
INTO l_physical_locator_id,
l_locator_id,
x_location_maximum_units,
x_location_current_units,
x_location_available_units,
x_location_suggested_units,
x_location_weight_uom_code,
x_max_weight,
x_current_weight,
x_suggested_weight,
x_available_weight,
x_volume_uom_code,
x_max_cubic_area,
x_current_cubic_area,
x_suggested_cubic_area,
x_available_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = p_inventory_location_id;
select
location_maximum_units,
location_current_units,
location_available_units,
location_suggested_units,
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight,
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
into
x_location_maximum_units,
x_location_current_units,
x_location_available_units,
x_location_suggested_units,
x_location_weight_uom_code,
x_max_weight,
x_current_weight,
x_suggested_weight,
x_available_weight,
x_volume_uom_code,
x_max_cubic_area,
x_current_cubic_area,
x_suggested_cubic_area,
x_available_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_physical_locator_id;
/* Select all required values at same time
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id; */
SELECT physical_location_id ,
inventory_location_id ,
location_maximum_units,
location_current_units,
location_available_units,
location_suggested_units
INTO l_physical_locator_id,
l_locator_id,
x_location_maximum_units,
x_location_current_units,
x_location_available_units,
x_location_suggested_units
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id;
select
location_maximum_units,
location_current_units,
location_available_units,
location_suggested_units
into
x_location_maximum_units,
x_location_current_units,
x_location_available_units,
x_location_suggested_units
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_inventory_location_id;
/* Select all required values at same time
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id; */
SELECT physical_location_id ,
inventory_location_id,
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight
INTO l_physical_locator_id,
l_locator_id,
x_location_weight_uom_code,
x_max_weight,
x_current_weight,
x_suggested_weight,
x_available_weight
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id;
select
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight
into
x_location_weight_uom_code,
x_max_weight,
x_current_weight,
x_suggested_weight,
x_available_weight
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_inventory_location_id;
/* Select all required values at same time
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id; */
SELECT physical_location_id ,
inventory_location_id,
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
INTO l_physical_locator_id,
l_locator_id,
x_volume_uom_code,
x_max_cubic_area,
x_current_cubic_area,
x_suggested_cubic_area,
x_available_cubic_area
FROM mtl_item_locations
WHERE organization_id = p_organization_id
and inventory_location_id = p_inventory_location_id;
select
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
into
x_volume_uom_code,
x_max_cubic_area,
x_current_cubic_area,
x_suggested_cubic_area,
x_available_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_physical_locator_id;
PROCEDURE update_loc_curr_capacity_nauto
( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
p_inventory_location_id IN NUMBER, -- identifier of locator
p_inventory_item_id IN NUMBER, -- identifier of item
p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
-- 'N' - transaction was NOT in item's primary UOM
-- or the information is not known
p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
-- locator capacity to get updated
p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
p_issue_flag IN VARCHAR2 -- 'Y' - Issue transaction
-- 'N' - Receipt transaction
)
IS
-- item attributes
l_item_primary_uom_code varchar2(3);
l_update_units boolean := TRUE; -- always update units
l_update_weight boolean := FALSE; -- only update if have location and item UOMs
l_update_volume boolean := FALSE; -- only update if have location and item UOMs
mdebug('In update_loc_curr_capacity_nauto');
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id;
############# If the locator capacity is infinite, then dont update anything, simply return#############
*/
SELECT location_maximum_units, max_weight, max_cubic_area
INTO l_max_units, l_max_weight, l_max_cubic_area
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND inventory_location_id = l_inventory_location_id;
mdebug('Before locking locator ' || l_inventory_location_id || ' in update_loc_curr_capacity_nauto');
SELECT inventory_location_id INTO l_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id
FOR UPDATE NOWAIT;
mdebug('After locking locator ' || l_inventory_location_id || ' in update_loc_curr_capacity_nauto');
select
primary_uom_code,
weight_uom_code,
unit_weight,
volume_uom_code,
unit_volume
into
l_item_primary_uom_code,
l_item_weight_uom_code,
l_item_unit_weight,
l_item_volume_uom_code,
l_item_unit_volume
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select
location_maximum_units,
location_current_units,
location_suggested_units,
location_available_units,
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight,
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
into
l_max_units,
l_current_units,
l_suggested_units,
l_available_units,
l_location_weight_uom_code,
l_max_weight,
l_current_weight,
l_suggested_weight,
l_available_weight,
l_volume_uom_code,
l_max_cubic_area,
l_current_cubic_area,
l_suggested_cubic_area,
l_available_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_inventory_location_id;
mdebug('After select: p_organization_id: '|| p_organization_id);
mdebug('After select: p_inventory_item_id: '|| p_inventory_item_id);
mdebug('After select: p_inventory_location_id: '|| l_inventory_location_id);
mdebug('After select: l_current_units: '|| l_current_units);
mdebug('After select: l_max_units: '|| l_max_units);
l_update_weight := TRUE;
l_update_volume := TRUE;
IF (l_update_weight) then
-- check that current_weight and suggested weight are not null or < 0
-- if current_weight is null, drive current_weight to zero
IF (l_current_weight IS NULL) OR (l_current_weight < 0) then
l_current_weight := 0;
-- update available weight, and make sure that it is > 0
l_upd_loc_available_weight := l_max_weight
- (l_upd_loc_current_weight + l_suggested_weight);
-- update current weight
l_upd_loc_current_weight := l_current_weight - l_loc_uom_xacted_weight;
-- update current_ weight, and make sure that it is > 0
IF (l_upd_loc_current_weight < 0) THEN
l_upd_loc_current_weight := 0;
-- update available weight, and make sure that it is > 0
l_upd_loc_available_weight := l_max_weight
- (l_upd_loc_current_weight + l_suggested_weight);
IF (l_update_volume) then
-- check that current_volume and suggested volume are not null or < 0
-- if current_volume is null, drive current_volume to zero
IF (l_current_cubic_area IS NULL) OR (l_current_cubic_area < 0) then
l_current_cubic_area := 0;
-- update available volume, and make sure that it is > 0
l_upd_loc_available_volume := l_max_cubic_area
- (l_upd_loc_current_volume + l_suggested_cubic_area);
-- update current volume
l_upd_loc_current_volume := l_current_cubic_area - l_loc_uom_xacted_volume;
-- update current_volume, and make sure that it is > 0
IF (l_upd_loc_current_volume < 0) THEN
l_upd_loc_current_volume := 0;
-- update available volume, and make sure that it is > 0
l_upd_loc_available_volume := l_max_cubic_area
- (l_upd_loc_current_volume + l_suggested_cubic_area);
IF (l_update_units) then
-- check that current_units and suggested units are not null or < 0
-- if current_units is null, drive current_units to zero
IF (l_current_units IS NULL) OR (l_current_units < 0) then
l_current_units := 0;
-- update available units, and make sure that it is > 0
l_upd_loc_available_units := l_max_units
- (l_upd_loc_current_units + l_suggested_units);
-- update current units
l_upd_loc_current_units := l_current_units - l_primary_quantity;
-- update current_units, and make sure that it is > 0
IF (l_upd_loc_current_units < 0) THEN
l_upd_loc_current_units := 0;
-- update available units, and make sure that it is > 0
l_upd_loc_available_units := l_max_units
- (l_upd_loc_current_units + l_suggested_units);
IF (l_update_weight) OR (l_update_volume) OR (l_update_units) THEN
UPDATE mtl_item_locations
SET
location_current_units = l_upd_loc_current_units,
location_available_units = l_upd_loc_available_units,
current_weight = l_upd_loc_current_weight,
available_weight = l_upd_loc_available_weight,
current_cubic_area = l_upd_loc_current_volume,
available_cubic_area = l_upd_loc_available_volume
where inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id;
, 'update_loc_curr_capacity_nauto'
);
END update_loc_curr_capacity_nauto;
PROCEDURE update_loc_current_capacity
( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
p_inventory_location_id IN NUMBER, -- identifier of locator
p_inventory_item_id IN NUMBER, -- identifier of item
p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
-- 'N' - transaction was NOT in item's primary UOM
-- or the information is not known
p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
-- locator capacity to get updated
p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
p_issue_flag IN VARCHAR2 -- 'Y' - Issue transaction
-- 'N' - Receipt transaction
)
IS
-- this whole function is an autonomous commit
PRAGMA autonomous_transaction;
inv_loc_wms_utils.update_loc_curr_capacity_nauto
( x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => p_organization_id,
p_inventory_location_id => p_inventory_location_id,
p_inventory_item_id => p_inventory_item_id,
p_primary_uom_flag => p_primary_uom_flag,
p_transaction_uom_code => p_transaction_uom_code,
p_quantity => p_quantity,
p_issue_flag => p_issue_flag);
, 'update_loc_current_capacity'
);
END update_loc_current_capacity;
PROCEDURE update_loc_sugg_cap_wo_empf
( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
p_inventory_location_id IN NUMBER, -- identifier of locator
p_inventory_item_id IN NUMBER, -- identifier of item
p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
-- 'N' - transaction was NOT in item's primary UOM
-- or the information is not known
p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
-- locator capacity to get updated
p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
)
IS
-- this whole function is an autonomous commit
PRAGMA autonomous_transaction;
inv_loc_wms_utils.update_loc_sugg_capacity_nauto
( x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => p_organization_id,
p_inventory_location_id => p_inventory_location_id,
p_inventory_item_id => p_inventory_item_id,
p_primary_uom_flag => p_primary_uom_flag,
p_transaction_uom_code => p_transaction_uom_code,
p_quantity => p_quantity
);
, 'update_loc_suggested_capacity'
);
END update_loc_sugg_cap_wo_empf;
PROCEDURE update_loc_sugg_capacity_nauto
( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
p_inventory_location_id IN NUMBER, -- identifier of locator
p_inventory_item_id IN NUMBER, -- identifier of item
p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
-- 'N' - transaction was NOT in item's primary UOM
-- or the information is not known
p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
-- locator capacity to get updated
p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
)
IS
-- item attributes
l_item_primary_uom_code varchar2(3);
l_update_units boolean := TRUE; -- always update units
l_update_weight boolean := FALSE; -- only update if have location and item UOMs
l_update_volume boolean := FALSE; -- only update if have location and item UOMs
mdebug('In update_loc_sugg_capacity_nauto');
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id;
############# If the locator capacity is infinite, then dont update anything, simply return#############
*/
SELECT location_maximum_units, max_weight, max_cubic_area
INTO l_max_units, l_max_weight, l_max_cubic_area
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND inventory_location_id = l_inventory_location_id;
mdebug('Before locking locator ' || l_inventory_location_id || ' in update_loc_sugg_capacity_nauto');
SELECT inventory_location_id INTO l_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id
FOR UPDATE NOWAIT;
mdebug('After locking locator ' || l_inventory_location_id || ' in update_loc_sugg_capacity_nauto');
select
primary_uom_code,
weight_uom_code,
unit_weight,
volume_uom_code,
unit_volume
into
l_item_primary_uom_code,
l_item_weight_uom_code,
l_item_unit_weight,
l_item_volume_uom_code,
l_item_unit_volume
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select
location_maximum_units,
location_current_units,
location_suggested_units,
location_available_units,
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight,
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
into
l_max_units,
l_current_units,
l_suggested_units,
l_available_units,
l_location_weight_uom_code,
l_max_weight,
l_current_weight,
l_suggested_weight,
l_available_weight,
l_volume_uom_code,
l_max_cubic_area,
l_current_cubic_area,
l_suggested_cubic_area,
l_available_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_inventory_location_id;
mdebug('After select: p_organization_id: '|| p_organization_id);
mdebug('After select: p_inventory_item_id: '|| p_inventory_item_id);
mdebug('After select: p_inventory_location_id: '|| l_inventory_location_id);
mdebug('After select: l_suggested_units: '|| l_suggested_units);
mdebug('After select: l_max_units: '|| l_max_units);
l_update_weight := TRUE;
l_update_volume := TRUE;
IF (l_update_weight) then
-- check that current_weight and suggested weight are not null or < 0
-- if current_weight is null, drive current_weight to zero
IF (l_current_weight IS NULL) OR (l_current_weight < 0) then
l_current_weight := 0;
-- update available weight, and make sure that it is > 0
l_upd_loc_available_weight := l_max_weight
- (l_upd_loc_suggested_weight + l_current_weight);
IF (l_update_volume) then
-- check that current_volume and suggested volume are not null or < 0
-- if current_volume is null, drive current_volume to zero
IF (l_current_cubic_area IS NULL) OR (l_current_cubic_area < 0) then
l_current_cubic_area := 0;
-- update available volume, and make sure that it is > 0
l_upd_loc_available_volume := l_max_cubic_area
- (l_upd_loc_suggested_volume + l_current_cubic_area);
IF (l_update_units) then
-- check that current_units and suggested units are not null or < 0
-- if current_units is null, drive current_units to zero
IF (l_current_units IS NULL) OR (l_current_units < 0) then
l_current_units := 0;
-- update available units, and make sure that it is > 0
l_upd_loc_available_units := l_max_units
- (l_upd_loc_suggested_units + l_current_units);
IF (l_update_weight) OR (l_update_volume) OR (l_update_units) THEN
UPDATE mtl_item_locations
SET
location_suggested_units = l_upd_loc_suggested_units,
location_available_units = l_upd_loc_available_units,
suggested_weight = l_upd_loc_suggested_weight,
available_weight = l_upd_loc_available_weight,
suggested_cubic_area = l_upd_loc_suggested_volume,
available_cubic_area = l_upd_loc_available_volume
where inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id;
, 'update_loc_sugg_capacity_nauto'
);
END update_loc_sugg_capacity_nauto;
PROCEDURE update_loc_suggested_capacity
( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
p_inventory_location_id IN NUMBER, -- identifier of locator
p_inventory_item_id IN NUMBER, -- identifier of item
p_primary_uom_flag IN VARCHAR2, -- 'Y' - transaction was in item's primary UOM
-- 'N' - transaction was NOT in item's primary UOM
-- or the information is not known
p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
-- locator capacity to get updated
p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
)
IS
-- this whole function is an autonomous commit
PRAGMA autonomous_transaction;
inv_loc_wms_utils.update_loc_sugg_capacity_nauto
( x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => p_organization_id,
p_inventory_location_id => p_inventory_location_id,
p_inventory_item_id => p_inventory_item_id,
p_primary_uom_flag => p_primary_uom_flag,
p_transaction_uom_code => p_transaction_uom_code,
p_quantity => p_quantity
);
, 'update_loc_suggested_capacity'
);
END update_loc_suggested_capacity;
-- locator capacity to get updated
-- Note: can be NULL if p_primary_uom_flag = 'Y'
p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
)
IS
-- item attributes
l_item_primary_uom_code varchar2(3);
l_update_units boolean := TRUE; -- always update units
l_update_weight boolean := FALSE; -- only update if have location and item UOMs
l_update_volume boolean := FALSE; -- only update if have location and item UOMs
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =p_inventory_location_id
and organization_id = p_organization_id;
############# If the locator capacity is infinite, then dont update anything, simply return#############
*/
SELECT location_maximum_units, max_weight, max_cubic_area
INTO l_max_units, l_max_weight, l_max_cubic_area
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND inventory_location_id = l_inventory_location_id;
SELECT inventory_location_id INTO l_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
AND organization_id = p_organization_id
FOR UPDATE NOWAIT;
select
primary_uom_code,
weight_uom_code,
unit_weight,
volume_uom_code,
unit_volume
into
l_item_primary_uom_code,
l_item_weight_uom_code,
l_item_unit_weight,
l_item_volume_uom_code,
l_item_unit_volume
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select
location_maximum_units,
location_current_units,
location_suggested_units,
location_available_units,
location_weight_uom_code,
max_weight,
current_weight,
suggested_weight,
available_weight,
volume_uom_code,
max_cubic_area,
current_cubic_area,
suggested_cubic_area,
available_cubic_area
into
l_max_units,
l_current_units,
l_suggested_units,
l_available_units,
l_location_weight_uom_code,
l_max_weight,
l_current_weight,
l_suggested_weight,
l_available_weight,
l_volume_uom_code,
l_max_cubic_area,
l_current_cubic_area,
l_suggested_cubic_area,
l_available_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
and inventory_location_id = l_inventory_location_id;
l_update_weight := TRUE;
l_update_volume := TRUE;
IF (l_update_weight) then
-- check that current_weight and suggested weight are not null or < 0
-- if current_weight is null, drive current_weight to zero
IF (l_current_weight IS NULL) OR (l_current_weight < 0) then
l_current_weight := 0;
-- update available weight, and make sure that it is > 0
l_upd_loc_available_weight := l_max_weight
- (l_upd_loc_suggested_weight + l_current_weight);
IF (l_update_volume) then
-- check that current_volume and suggested volume are not null or < 0
-- if current_volume is null, drive current_volume to zero
IF (l_current_cubic_area IS NULL) OR (l_current_cubic_area < 0) then
l_current_cubic_area := 0;
-- update available volume, and make sure that it is > 0
l_upd_loc_available_volume := l_max_cubic_area
- (l_upd_loc_suggested_volume + l_current_cubic_area);
IF (l_update_units) then
-- check that current_units and suggested units are not null or < 0
-- if current_units is null, drive current_units to zero
IF (l_current_units IS NULL) OR (l_current_units < 0) then
l_current_units := 0;
-- update available units, and make sure that it is > 0
l_upd_loc_available_units := l_max_units
- (l_upd_loc_suggested_units + l_current_units);
IF (l_update_weight) OR (l_update_volume) OR (l_update_units) THEN
UPDATE mtl_item_locations
SET
location_suggested_units = l_upd_loc_suggested_units,
location_available_units = l_upd_loc_available_units,
suggested_weight = l_upd_loc_suggested_weight,
available_weight = l_upd_loc_available_weight,
suggested_cubic_area = l_upd_loc_suggested_volume,
available_cubic_area = l_upd_loc_available_volume
where inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id;
-- locator capacity to get updated
-- Note: can be NULL if p_primary_uom_flag = 'Y'
p_quantity IN NUMBER -- transaction quantity in p_transaction_uom_code
)
IS
-- this whole function is an autonomous commit
PRAGMA autonomous_transaction;
SELECT nvl(sum(wlc.primary_quantity),0) /* Bug 5689378 */
INTO x_quantity
FROM wms_lpn_contents wlc
, wms_license_plate_numbers wlpn
WHERE wlc.parent_lpn_id = wlpn.lpn_id
AND wlpn.outermost_lpn_id = p_lpn_id;
select inventory_location_id,
subinventory_code,
location_weight_uom_code,
volume_uom_code,
location_maximum_units,
max_weight,
max_cubic_area
from mtl_item_locations
where organization_id = p_organization_id
AND subinventory_code = nvl(p_subinventory,subinventory_code)
--Bug #3949621 (Changed the query, because for PJM Orgs, Physical Location Id won't be null)
AND nvl(physical_location_id,inventory_location_id) = nvl(p_locator_id ,nvl(physical_location_id,inventory_location_id) );
SELECT
'MOQ_RECORD' record_source,
moq.inventory_item_id inventory_item_id,
moq.primary_transaction_quantity transaction_quantity,
moq.containerized_flag containerized_flag,
TO_NUMBER(NULL) transaction_action_id,
TO_NUMBER(NULL) transfer_lpn_id,
TO_NUMBER(NULL) content_lpn_id,
--TO_NUMBER(NULL) lpn_id,
--moq.lpn_id lpn_id,
wlpn.outermost_lpn_id lpn_id,
TO_NUMBER(NULL) transaction_status
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ,
mtl_item_locations mil,
wms_license_plate_numbers wlpn
WHERE moq.locator_id = mil.inventory_location_id
AND mil.organization_id = moq.organization_id
AND mil.subinventory_code = moq.subinventory_code
AND nvl(mil.physical_location_id,mil.inventory_location_id) = l_locator_id
AND moq.organization_id = p_organization_id
AND moq.subinventory_code = l_subinventory_code
AND moq.locator_id = l_locator_id --Added to address Bug 4333758
AND moq.transaction_quantity > 0
AND moq.lpn_id = wlpn.lpn_id(+)
UNION ALL
/*
** We want all transactions that are pending. And we want all Putaway
** suggestions. However, we are dealing with just the Source location/sub
** in this side of the UNION.
*/
SELECT
'MMTT_SOURCE_LOCATOR_RECORD' record_source
, mmtt.inventory_item_id inventory_item_id
, mmtt.primary_quantity transaction_quantity
, TO_NUMBER(NULL) containerized_flag
, mmtt.transaction_action_id transaction_action_id
, mmtt.transfer_lpn_id transfer_lpn_id
, mmtt.content_lpn_id content_lpn_id
, mmtt.lpn_id lpn_id
, transaction_status transaction_status
FROM mtl_material_transactions_temp mmtt,
mtl_item_locations mil
WHERE mmtt.locator_id = mil.inventory_location_id
AND mil.organization_id = mmtt.organization_id
AND mil.subinventory_code = mmtt.subinventory_code
AND nvl(mil.physical_location_id,mil.inventory_location_id) = l_locator_id
AND mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = l_subinventory_code
AND mmtt.locator_id = l_locator_id --Added to address Bug 4333758
AND mmtt.posting_flag = 'Y'
AND ( Nvl(mmtt.transaction_status,0) <> l_allocated_txn_status OR -- pending txns
-- only receipt suggested transactions are used
(Nvl(mmtt.transaction_status,0) = l_allocated_txn_status AND
mmtt.transaction_action_id IN (l_txn_action_int_rcpt , l_txn_action_rcpt ))
)
UNION ALL
/*
** We want all pending transactions and suggestions that are sub transfers.
** However, we only deal with the destination sub/locator in this side of UNION.
*/
SELECT
'MMTT_DESTINATION_LOCATOR_RECORD' record_source
, mmtt.inventory_item_id inventory_item_id
, mmtt.primary_quantity transaction_quantity
, TO_NUMBER(NULL) containerized_flag
, mmtt.transaction_action_id transaction_action_id
, mmtt.transfer_lpn_id transfer_lpn_id
, mmtt.content_lpn_id content_lpn_id
, mmtt.lpn_id lpn_id
, transaction_status transaction_status
FROM mtl_material_transactions_temp mmtt,
mtl_item_locations mil
WHERE mmtt.transfer_to_location = mil.inventory_location_id
AND mil.organization_id = mmtt.organization_id
AND mil.subinventory_code = mmtt.transfer_subinventory--bug 6110275 for destination locator
AND nvl(mil.physical_location_id,mil.inventory_location_id) = l_locator_id
AND mmtt.organization_id = p_organization_id
AND mmtt.transfer_subinventory = l_subinventory_code
AND mmtt.transfer_to_location = l_locator_id --Added to address Bug 4333758
AND mmtt.posting_flag = 'Y'
AND mmtt.transaction_action_id IN (l_txn_action_subxfer , l_txn_action_orgxfer, l_txn_action_stgxfer );
t_locator.delete;
t_subinventory_code.delete;
t_location_weight_uom_code.delete;
t_volume_uom_code.delete;
t_location_maximum_units.delete;
t_max_weight.delete;
t_max_cubic_area.delete;
t_location_current_units.delete;
t_location_suggested_units.delete;
t_location_available_units.delete;
t_current_weight.delete;
t_suggested_weight.delete;
t_available_weight.delete;
t_current_cubic_area.delete;
t_suggested_cubic_area.delete;
t_available_cubic_area.delete;
t_inventory_item_id.delete;
t_empty_flag.delete;
t_mixed_items_flag.delete;
t_item_units.delete;
select
inventory_item_id
, primary_uom_code
, weight_uom_code
, unit_weight
, volume_uom_code
, unit_volume
into
t_item_info(l_inventory_item_id).inventory_item_id
, t_item_info(l_inventory_item_id).item_primary_uom_code
, t_item_info(l_inventory_item_id).item_weight_uom_code
, t_item_info(l_inventory_item_id).item_unit_weight
, t_item_info(l_inventory_item_id).item_volume_uom_code
, t_item_info(l_inventory_item_id).item_unit_volume
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = l_inventory_item_id;
update mtl_item_locations
set
location_current_units = t_location_current_units(i)
, location_suggested_units = t_location_suggested_units(i)
, location_available_units = t_location_available_units(i)
, current_weight = t_current_weight(i)
, suggested_weight = t_suggested_weight(i)
, available_weight = t_available_weight(i)
, current_cubic_area = t_current_cubic_area(i)
, suggested_cubic_area = t_suggested_cubic_area(i)
, available_cubic_area = t_available_cubic_area(i)
, inventory_item_id = t_inventory_item_id(i)
, empty_flag = t_empty_flag(i)
, mixed_items_flag = t_mixed_items_flag(i)
, last_update_date = sysdate /* Added for Bug 6363028 */
where organization_id = p_organization_id
and inventory_location_id = t_locator(i);
SELECT physical_location_id
, inventory_location_id
INTO l_physical_locator_id
, l_loc_id
FROM mtl_item_locations
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
SELECT location_weight_uom_code
, max_weight
, suggested_weight
, suggested_cubic_area
, current_weight
, available_weight
, volume_uom_code
, max_cubic_area
, current_cubic_area
, available_cubic_area
, location_maximum_units
, location_current_units
, location_available_units
, location_suggested_units
INTO l_locator_weight_uom_code
, l_locator_max_weight
, l_locator_suggested_weight
, l_locator_suggested_cubic_area
, l_locator_current_weight
, l_locator_available_weight
, l_locator_volume_uom_code
, l_locator_max_cubic_area
, l_locator_current_cubic_area
, l_locator_available_cubic_area
, l_locator_maximum_units
, l_locator_current_units
, l_locator_available_units
, l_locator_suggested_units
FROM MTL_ITEM_LOCATIONS
WHERE organization_id = p_organization_id
AND inventory_location_id = p_inventory_location_id;
UPDATE MTL_ITEM_LOCATIONS mil
SET current_weight = nvl(l_locator_current_weight,current_weight)
, available_weight = nvl(l_locator_available_weight,available_weight)
, current_cubic_area = nvl(l_locator_current_cubic_area,current_cubic_area)
, available_cubic_area = nvl(l_locator_available_cubic_area,available_cubic_area)
, location_current_units = nvl(l_locator_current_units,location_current_units)
, location_available_units = nvl(l_locator_available_units,mil.location_available_units)
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
mdebug(l_proc_name||'Locator Current Weight after update : '||l_locator_current_weight);
mdebug(l_proc_name||'Locator Available Weight after update : '||l_locator_available_weight);
mdebug(l_proc_name||'Locator Current Cubic Area after update : '||l_locator_current_cubic_area);
mdebug(l_proc_name||'Locator Available Cubic Area after update: '||l_locator_available_cubic_area);
mdebug(l_proc_name||'Locator Current Units after update : '||l_locator_current_units);
mdebug(l_proc_name||'Locator Available Units after update : '||l_locator_available_units);
UPDATE MTL_ITEM_LOCATIONS
SET current_weight = nvl(l_locator_current_weight,current_weight)
, available_weight = nvl(l_locator_available_weight,available_weight)
, current_cubic_area = nvl(l_locator_current_cubic_area,current_cubic_area)
, available_cubic_area = nvl(l_locator_available_cubic_area,available_cubic_area)
, location_current_units = nvl(l_locator_current_units,location_current_units)
, location_available_units = nvl(l_locator_available_units,location_available_units)
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
mdebug(l_proc_name||'Locator Current Weight after update : '||l_locator_current_weight);
mdebug(l_proc_name||'Locator Available Weight after update : '||l_locator_available_weight);
mdebug(l_proc_name||'Locator Current Cubic Area after update : '||l_locator_current_cubic_area);
mdebug(l_proc_name||'Locator Available Cubic Area after update: '||l_locator_available_cubic_area);
mdebug(l_proc_name||'Locator Current Units after update : '||l_locator_current_units);
mdebug(l_proc_name||'Locator Available Units after update : '||l_locator_available_units);
l_update_table BOOLEAN := TRUE;
SELECT inventory_location_id INTO l_old_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_old_inventory_location_id
and organization_id = l_old_organization_id
FOR UPDATE NOWAIT;
First Update the Source Locator's Capacity.
* If content lpn is passed => Issue the lpn from the source locator.
* If lpn id is passed => check whether the whole lpn is being transfered or not. if so, need to
decrement the container weight and volume, else decrement the transacted
items weight and volume.
Then Update the destination Locator's capacity.
* If content lpn is passesd => receive into it.
* If transfer lpn is passed => receive into it.
* else => receive loose
-------------------------------------------------------------------------------------------------------*/
/* Do update the locator capacity only if the locator capacity is finite */
IF NOT (l_old_loc_attr.l_locator_maximum_units IS NULL AND l_old_loc_attr.l_locator_max_weight IS NULL AND l_old_loc_attr.l_locator_max_cubic_area IS NULL) THEN
IF p_content_lpn_id IS NOT NULL THEN
/*-----------------------------------------------------------------
Issue Content LPN from the source locator
-----------------------------------------------------------------*/
upd_lpn_loc_cpty_for_issue(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_loc_attr => l_old_loc_attr
, p_content_lpn_id => p_content_lpn_id
, p_cnt_lpn_attr => l_cnt_lpn_attr
, p_transaction_action_id => 1
, p_item_attr => p_item_attr
, p_quantity => l_quantity
, p_inventory_location_id => l_old_inventory_location_id
, p_organization_id => l_old_organization_id
);
l_update_table := FALSE;
l_update_table := FALSE;
IF l_update_table THEN
UPDATE MTL_ITEM_LOCATIONS
SET current_weight = nvl(l_old_loc_current_weight,current_weight)
, available_weight = nvl(l_old_loc_available_weight,available_weight)
, current_cubic_area = nvl(l_old_loc_current_vol,current_cubic_area)
, available_cubic_area = nvl(l_old_loc_available_vol,available_cubic_area)
, location_current_units = nvl(l_old_loc_current_units,location_current_units)
, location_available_units = nvl(l_old_loc_available_units,location_available_units)
WHERE inventory_location_id = l_old_inventory_location_id
AND organization_id = l_old_organization_id;
END IF; /* Update locator capacity only if the locator capacity is finite */
/* Do update the locator capacity only if the locator capacity is finite */
IF NOT (p_loc_attr.l_locator_maximum_units IS NULL AND p_loc_attr.l_locator_max_weight IS NULL AND p_loc_attr.l_locator_max_cubic_area IS NULL) THEN
IF p_trn_lpn_id IS NOT NULL THEN
/*-----------------------------------------------------------------
Receive the transfer LPN into the destination Locator.
-----------------------------------------------------------------*/
upd_lpn_loc_cpty_for_rcpt (
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_loc_attr => p_loc_attr
, p_transfer_lpn_id => p_trn_lpn_id
, p_trn_lpn_attr => p_trn_lpn_attr
, p_transaction_action_id => 27
, p_item_attr => p_item_attr
, p_quantity => p_quantity
, p_inventory_location_id => p_inventory_location_id
, p_organization_id => p_organization_id
);
UPDATE MTL_ITEM_LOCATIONS
SET current_weight = nvl(l_loc_current_weight,current_weight)
, available_weight = nvl(l_loc_available_weight,available_weight)
, current_cubic_area = nvl(l_loc_current_vol,current_cubic_area)
, available_cubic_area = nvl(l_loc_available_vol,available_cubic_area)
, location_current_units = nvl(l_loc_current_units,location_current_units)
, location_available_units = nvl(l_loc_available_units,location_available_units)
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
END IF; /* Update locator capacity only if the locator capacity is finite */
UPDATE MTL_ITEM_LOCATIONS
SET current_cubic_area = nvl(l_locator_current_cubic_area,current_cubic_area)
, available_cubic_area = nvl(l_locator_available_cubic_area,available_cubic_area)
, current_weight = nvl(l_locator_current_weight,current_weight)
, available_weight = nvl(l_locator_available_weight,available_weight)
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
mdebug(l_proc_name||'Locator Current Cubic Area after update : '||l_locator_current_cubic_area);
mdebug(l_proc_name||'Locator Available Cubic Area after update: '||l_locator_available_cubic_area);
mdebug(l_proc_name||'Locator Current weight after update : '||l_locator_current_weight);
mdebug(l_proc_name||'Locator Available weight after update : '||l_locator_available_weight);
UPDATE MTL_ITEM_LOCATIONS
SET current_cubic_area = nvl(l_locator_current_cubic_area,current_cubic_area)
, available_cubic_area = nvl(l_locator_available_cubic_area,available_cubic_area)
, current_weight = nvl(l_locator_current_weight,current_weight)
, available_weight = nvl(l_locator_available_weight,available_weight)
WHERE inventory_location_id = p_inventory_location_id
AND organization_id = p_organization_id;
mdebug(l_proc_name||'Locator Current Cubic Area after update : '||l_locator_current_cubic_area);
mdebug(l_proc_name||'Locator Available Cubic Area after update: '||l_locator_available_cubic_area);
mdebug(l_proc_name||'Locator Current Weight After update : '||l_locator_current_weight);
mdebug(l_proc_name||'Locator Available Weight after update : '||l_locator_available_weight);
-- locator capacity to get updated
,p_transaction_action_id IN NUMBER -- transaction action id for pack,unpack,issue,receive,
-- transfer
,p_lpn_id IN NUMBER -- lpn id
,p_transfer_lpn_id IN NUMBER -- transfer_lpn_id
,p_content_lpn_id IN NUMBER -- content_lpn_id
,p_quantity IN NUMBER -- Primary quantity in primary UOM.
,p_container_item_id IN NUMBER DEFAULT NULL
,p_cartonization_id IN NUMBER DEFAULT NULL
,p_from_org_id IN NUMBER DEFAULT NULL
,p_from_loc_id IN NUMBER DEFAULT NULL
)
IS
l_inventory_location_id NUMBER;
############# If the locator capacity is infinite, then dont update anything, simply return#############
*/
SELECT location_maximum_units, max_weight, max_cubic_area
INTO l_max_units, l_max_weight, l_max_cubic_area
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
AND inventory_location_id = l_inventory_location_id;
SELECT location_maximum_units, max_weight, max_cubic_area
INTO l_max_units, l_max_weight, l_max_cubic_area
FROM mtl_item_locations_kfv
WHERE organization_id = p_from_org_id
AND inventory_location_id = l_from_inventory_location_id;
SELECT inventory_location_id INTO l_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id
FOR UPDATE NOWAIT;
PROCEDURE update_lpn_loc_curr_capacity
( x_return_status OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
x_msg_count OUT NOCOPY NUMBER, -- number of messages in the message queue
x_msg_data OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
p_organization_id IN NUMBER, -- org of locator whose capacity is to be determined
p_inventory_location_id IN NUMBER, -- identifier of locator
p_inventory_item_id IN NUMBER, -- identifier of item
p_primary_uom_FLAG IN VARCHAR2, -- iF Y primary UOM
p_transaction_uom_code IN VARCHAR2, -- UOM of the transacted material that causes the
-- locator capacity to get updated
p_transaction_action_id IN NUMBER, -- transaction action id for pack,unpack,issue,receive,
-- transfer
p_lpn_id IN NUMBER, -- lpn id
p_transfer_lpn_id IN NUMBER, -- transfer_lpn_id
p_content_lpn_id IN NUMBER, -- content_lpn_id
p_quantity IN NUMBER, -- transaction quantity in p_transaction_uom_code
p_container_item_id IN NUMBER DEFAULT NULL,
p_cartonization_id IN NUMBER DEFAULT NULL,
p_from_org_id IN NUMBER DEFAULT NULL,
p_from_loc_id IN NUMBER DEFAULT NULL
)
IS
PRAGMA autonomous_transaction;
, 'update_lpn_loc_curr_capacity'
);
SELECT GROSS_WEIGHT_UOM_CODE,
CONTENT_VOLUME_UOM_CODE,
GROSS_WEIGHT,
CONTENT_VOLUME,
LOCATOR_ID,
SUBINVENTORY_CODE,
INVENTORY_ITEM_ID
INTO l_gross_weight_uom_code,
l_content_volume_uom_code,
l_gross_weight,
l_content_volume,
l_locator_id,
l_subinventory_code,
l_container_item_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
INV_TRX_UTIL_PUB.TRACE('The p_lpn_id '||to_char(p_lpn_id),'LPN-ATTRIBUTE-UPDATE_LPN_LOC',9);
,'LPN-ATTRIBUTE-UPDATE_LPN_LOC',9
);
INV_TRX_UTIL_PUB.TRACE('Error fetching container item attributes - '||l_return_status , 'LPN-ATTRIBUTE-UPDATE_LPN_LOC',4);
INV_TRX_UTIL_PUB.TRACE('Error fetching container item attributes - '||l_return_status , 'LPN-ATTRIBUTE-UPDATE_LPN_LOC',4);
INV_TRX_UTIL_PUB.TRACE('Gross_weight_uom_weight code different from container item weight uom code', 'LPN-ATTRIBUTE-UPDATE_LPN_LOC',4);
INV_TRX_UTIL_PUB.TRACE('l_container_item_xacted_weight is '||to_char(l_container_item_xacted_weight), 'LPN-ATTRIBUTE-UPDATE_LPN_LOC',4);
INV_TRX_UTIL_PUB.TRACE('l_container_item_xacted_volume is '||to_char(l_container_item_xacted_volume), 'LPN-ATTRIBUTE-UPDATE_LPN_LOC',4);
,'LPN-ATTRIBUTE-UPDATE_LPN_LOC',4
);
SELECT primary_uom_code
,weight_uom_code
,unit_weight
,volume_uom_code
,unit_volume
INTO
l_item_primary_uom_code
,l_item_weight_uom_code
,l_item_unit_weight
,l_item_volume_uom_code
,l_item_unit_volume
FROM
mtl_system_items
WHERE
inventory_item_id = p_inventory_item_id and
organization_id = p_organization_id;
INV_TRX_UTIL_PUB.TRACE('The value of primary_uom_flag <>y or PUOM is not equal to Transaction UOM', 'UPDATE_LPN_LOC_CURR_CAPACITY',9);
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_loc_id
FROM mtl_item_locations
WHERE inventory_location_id = p_inventory_location_id
and organization_id = p_organization_id;
SELECT inventory_location_id INTO l_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_inventory_location_id
and organization_id = p_organization_id
FOR UPDATE NOWAIT;
SELECT physical_location_id ,
inventory_location_id
INTO l_des_physical_locator_id,
l_des_loc_id
FROM mtl_item_locations
WHERE inventory_location_id = p_transfer_location_id
and organization_id = p_transfer_organization;
SELECT inventory_location_id INTO l_des_inventory_location_id
FROM mtl_item_locations
WHERE inventory_location_id = l_des_inventory_location_id
and organization_id = p_transfer_organization
FOR UPDATE NOWAIT;
UPDATE MTL_ITEM_LOCATIONS mil
SET EMPTY_FLAG = nvl(l_empty_flag,mil.empty_flag)
, MIXED_ITEMS_FLAG = nvl(l_mixed_flag,mil.mixed_items_flag)
, INVENTORY_ITEM_ID = nvl(l_item_id,mil.inventory_item_id)
, LAST_UPDATE_DATE = sysdate /* Added for Bug 6363028 */
WHERE INVENTORY_LOCATION_ID = l_des_inventory_location_id
AND ORGANIZATION_ID = P_TRANSFER_ORGANIZATION;
UPDATE MTL_ITEM_LOCATIONS MIL
SET EMPTY_FLAG = NVL(l_empty_flag,MIL.empty_flag)
, MIXED_ITEMS_FLAG = NVL(l_mixed_flag,MIL.mixed_items_flag)
, INVENTORY_ITEM_ID = NVL(l_item_id,MIL.inventory_item_id)
, LAST_UPDATE_DATE = sysdate /* Added for Bug 6363028 */
WHERE INVENTORY_LOCATION_ID = l_inventory_location_id
AND ORGANIZATION_ID = P_ORGANIZATION_ID;
SELECT inventory_item_id
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE LOCATOR_ID = p_locator_id
and organization_id =p_org_id
and inventory_item_id <> p_inventory_item_id
and rownum <3
group by inventory_item_id;
SELECT LOCATION_CURRENT_UNITS,
location_suggested_units,
mixed_items_flag,
inventory_item_id,
empty_flag
INTO l_loc_current_units ,
l_loc_suggested_units,
l_mixed_flag,
l_inventory_item_id,
l_empty_flag
FROM MTL_ITEM_LOCATIONS
WHERE INVENTORY_LOCATION_ID = p_locator_id
AND ORGANIZATION_ID = P_ORG_ID;
SELECT 1
INTO l_chk_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE LOCATOR_ID = P_LOCATOR_ID
AND ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
);
SELECT 1
INTO l_chk_flag
FROM dual
WHERE EXISTS (SELECT 1
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE LOCATOR_ID = P_LOCATOR_ID
AND ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID <> P_INVENTORY_ITEM_ID
);
SELECT subinventory_code
INTO l_subinventory_code
FROM mtl_item_locations
WHERE inventory_location_id = p_locator_id
and organization_id = p_org_id;
SELECT 1
INTO l_chk_flag
FROM dual
WHERE EXISTS (
-- Onhand
SELECT 1
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE LOCATOR_ID = P_LOCATOR_ID
AND ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID <> P_INVENTORY_ITEM_ID
AND SUBINVENTORY_CODE = l_subinventory_code --Added 3237709
UNION ALL --Bug 4566485
-- Pending/Suggestion receipts
SELECT 1
FROM mtl_material_transactions_temp
WHERE LOCATOR_ID = P_LOCATOR_ID
AND ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID <> P_INVENTORY_ITEM_ID
AND POSTING_FLAG = 'Y'
AND transaction_action_id IN (12,27)
AND SUBINVENTORY_CODE = l_subinventory_code --Added 3237709
UNION ALL --Bug 4566485
-- Pending/Suggestion receipts on transfer side
SELECT 1
FROM mtl_material_transactions_temp
WHERE TRANSFER_TO_LOCATION = P_LOCATOR_ID
AND ORGANIZATION_ID = P_ORG_ID
AND INVENTORY_ITEM_ID <> P_INVENTORY_ITEM_ID
AND POSTING_FLAG = 'Y'
AND transaction_action_id IN (2,3,28)
AND SUBINVENTORY_CODE = l_subinventory_code --Added 3237709
);
SELECT MMTT.organization_id,
MMTT.inventory_item_id,
MMTT.locator_id,
MMTT.transfer_organization,
MMTT.transfer_to_location,
MMTT.transaction_action_id,
MMTT.primary_quantity,
MMTT.transaction_quantity,
MMTT.transfer_lpn_id,
MMTT.content_lpn_id,
MMTT.lpn_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
MTL_ITEM_LOCATIONS MIL
WHERE MMTT.transaction_status <> 2
AND MMTT.organization_id = p_organization_id
AND MMTT.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND MIL.MIXED_ITEMS_FLAG ='Y'
AND MMTT.locator_id >0
AND (MMTT.transfer_lpn_id is not null or
MMTT.content_lpn_id is not null or
MMTT.lpn_id is not null
);
SELECT organization_id,
inventory_item_id,
locator_id,
transfer_organization,
transfer_to_location,
transaction_action_id,
primary_quantity,
transaction_quantity,
transfer_lpn_id,
content_lpn_id,
lpn_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE transaction_status <> 2
AND organization_id = p_organization_id
and locator_id >0
AND (transfer_lpn_id is not null or
content_lpn_id is not null or
lpn_id is not null
);
SELECT WLPN.locator_id,
nvl(sum(DECODE(MIL.LOCATION_WEIGHT_UOM_CODE,WLPN.GROSS_WEIGHT_UOM_CODE,
WLPN.gross_weight,
GREATEST(INV_CONVERT.INV_UM_CONVERT(
null
,null
,WLPN.GROSS_WEIGHT
,WLPN.GROSS_WEIGHT_UOM_CODE
,MIL.LOCATION_WEIGHT_UOM_CODE
,null
,null),0))),0) gross_weight,
nvl(sum(DECODE(MIL.VOLUME_UOM_CODE,WLPN.CONTENT_VOLUME_UOM_CODE,
WLPN.content_volume,
GREATEST(INV_CONVERT.INV_UM_CONVERT(
null
,null
,WLPN.CONTENT_VOLUME
,WLPN.CONTENT_VOLUME_UOM_CODE
,MIL.VOLUME_UOM_CODE
,null
,null),0))),0) content_volume
FROM wms_license_plate_numbers WLPN,
MTL_ITEM_LOCATIONS MIL
WHERE MIL.INVENTORY_LOCATION_ID = WLPN.LOCATOR_ID
AND WLPN.organization_id =p_organization_id
AND WLPN.lpn_context =1
AND WLPN.parent_lpn_id is null
AND WLPN.locator_id >0
GROUP by WLPN.locator_id ;
SELECT WLPN.locator_id,
nvl(sum(DECODE(MIL.LOCATION_WEIGHT_UOM_CODE,WLPN.GROSS_WEIGHT_UOM_CODE,
WLPN.gross_weight,
GREATEST(INV_CONVERT.INV_UM_CONVERT(
null
,null
,WLPN.GROSS_WEIGHT
,WLPN.GROSS_WEIGHT_UOM_CODE
,MIL.LOCATION_WEIGHT_UOM_CODE
,null
,null),0))),0) gross_weight,
nvl(sum(DECODE(MIL.VOLUME_UOM_CODE,WLPN.CONTENT_VOLUME_UOM_CODE,
WLPN.content_volume,
GREATEST(INV_CONVERT.INV_UM_CONVERT(
null
,null
,WLPN.CONTENT_VOLUME
,WLPN.CONTENT_VOLUME_UOM_CODE
,MIL.VOLUME_UOM_CODE
,null
,null),0))),0) content_volume
FROM wms_license_plate_numbers WLPN,
MTL_ITEM_LOCATIONS MIL
WHERE WLPN.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID
AND WLPN.organization_id =p_organization_id
AND WLPN.lpn_context =1
AND WLPN.parent_lpn_id is null
AND WLPN.locator_id >0
AND MIL.MIXED_ITEMS_FLAG ='Y'
GROUP by WLPN.locator_id ;
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =l_curr_cpty.locator_id
and organization_id = p_organization_id;
/* Update current weight and current cubic area of the locator with the
LPN's gross weight and content volume
*/
IF (l_debug = 1) THEN
INV_TRX_UTIL_PUB.TRACE('The value of locator _id is '||to_char(l_inventory_location_id), 'LPN_LOC_CURRENT_CAPACITY',10);
SELECT sum(abs(primary_transaction_quantity))
into l_units
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE locator_id = l_inventory_location_id
AND containerized_flag = 1
AND locator_id >0
AND organization_id =p_organization_id;
UPDATE mtl_item_locations
set current_weight=nvl(current_weight,0)+l_curr_cpty.gross_weight,
available_weight = max_weight-(nvl(suggested_weight,0) + nvl(current_weight,0)),
current_cubic_area = nvl(current_cubic_area,0) + l_curr_cpty.content_volume,
available_cubic_area = max_cubic_area -(nvl(suggested_cubic_area,0)+nvl(current_cubic_area,0)),
LOCATION_CURRENT_UNITS = nvl(LOCATION_CURRENT_UNITS,0) + l_units,
LOCATION_AVAILABLE_UNITS = greatest(nvl(LOCATION_MAXIMUM_UNITS,0)-(nvl(LOCATION_SUGGESTED_UNITS,0)+ nvl(LOCATION_CURRENT_UNITS,0)),0)
where
inventory_location_id =l_inventory_location_id
and organization_id = p_organization_id;
SELECT physical_location_id ,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id =l_curr_cpty.locator_id
and organization_id = p_organization_id;
/* Update current weight and current cubic area of the locator with the
LPN gross weight and content volume
*/
IF (l_debug = 1) THEN
INV_TRX_UTIL_PUB.TRACE('The value of locator _id is '||to_char(l_inventory_location_id), 'LPN_LOC_CURRENT_CAPACITY',10);
SELECT sum(abs(primary_transaction_quantity))
into l_units
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE locator_id = l_inventory_location_id
AND containerized_flag = 1
AND locator_id >0
AND organization_id =p_organization_id;
UPDATE mtl_item_locations
set current_weight=nvl(current_weight,0)+l_curr_cpty.gross_weight,
available_weight = max_weight-(nvl(suggested_weight,0) + nvl(current_weight,0)),
current_cubic_area = nvl(current_cubic_area,0) + l_curr_cpty.content_volume,
available_cubic_area = max_cubic_area -(nvl(suggested_cubic_area,0)+nvl(current_cubic_area,0)),
LOCATION_CURRENT_UNITS = nvl(LOCATION_CURRENT_UNITS,0) + nvl(l_units,0),
LOCATION_AVAILABLE_UNITS = greatest(nvl(LOCATION_MAXIMUM_UNITS,0)-(nvl(LOCATION_SUGGESTED_UNITS,0)+ nvl(LOCATION_CURRENT_UNITS,0)),0)
where
inventory_location_id =l_inventory_location_id
and organization_id = p_organization_id;
SELECT physical_location_id,
inventory_location_id
INTO l_physical_locator_id,
l_locator_id
FROM mtl_item_locations
WHERE inventory_location_id = p_locator_id
and organization_id = p_org_id;
SELECT 1
INTO l_chk_flag
FROM dual
WHERE EXISTS (
SELECT 1
FROM rcv_supply rs
WHERE rs.to_locator_id = p_locator_id
AND rs.to_organization_id = p_org_id
AND rs.to_subinventory = p_subinventory
AND rs.quantity > 0
);
SELECT COUNT(DISTINCT item_id)
INTO l_mixed_num
FROM rcv_supply rs
WHERE rs.to_locator_id = p_locator_id
AND rs.to_organization_id = p_org_id
AND rs.to_subinventory = p_subinventory
AND rs.quantity > 0;
UPDATE mtl_item_locations mil
SET empty_flag = NVL(l_empty_flag,mil.empty_flag)
, mixed_items_flag = NVL(l_mixed_flag,mil.mixed_items_flag)
, last_update_date = sysdate /* Added for Bug 6363028 */
WHERE inventory_location_id = l_locator_id
AND organization_id = p_org_id;