[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_item_uom_conversion_tb.delete;
PROCEDURE Get_Update_LPN_Start_Num (
p_org_id IN NUMBER
, p_qty IN NUMBER
, x_curr_seq OUT NOCOPY NUMBER
) IS PRAGMA AUTONOMOUS_TRANSACTION;
SELECT lpn_starting_number INTO l_lpn_start_num
FROM mtl_parameters
WHERE organization_id=p_org_id
FOR UPDATE;
UPDATE MTL_PARAMETERS
SET lpn_starting_number = l_lpn_start_num+p_qty,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE organization_id = p_org_id;
END Get_Update_LPN_Start_Num;
SELECT meaning
INTO l_lookup_meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_LPN_CONTEXT'
AND lookup_code = p_old_context;
SELECT meaning
INTO l_lookup_meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_LPN_CONTEXT'
AND lookup_code = p_new_context;
PROCEDURE Update_Locator_Capacity (
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_id IN NUMBER
, p_subinventory IN VARCHAR2
, p_locator_id IN NUMBER
, p_weight_change IN NUMBER
, p_weight_uom_change IN VARCHAR2
, p_volume_change IN NUMBER
, p_volume_uom_change IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Locator_Capacity';
SAVEPOINT WMS_Update_Locator_Capacity;
SELECT mil.location_weight_uom_code
, mil.volume_uom_code
INTO l_loc_wt_uom
, l_loc_vol_uom
FROM mtl_item_locations mil
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory
AND mil.inventory_location_id = p_locator_id;
g_progress := 'Update Locator capacity';
UPDATE mtl_item_locations mil
SET current_weight = nvl(current_weight,0) + wtdiff
, available_weight = nvl(available_weight,0) - wtdiff
, current_cubic_area = nvl(current_cubic_area,0) + voldiff
, available_cubic_area = nvl(available_cubic_area,0) - voldiff
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory
AND mil.inventory_location_id = p_locator_id;
ROLLBACK TO WMS_Update_Locator_Capacity;
END Update_Locator_Capacity;
SELECT fnd_global.user_id
, FND_PROFILE.value('CONC_REQUEST_ID')
INTO l_user_id
, l_request_id
FROM DUAL;
SELECT wms_license_plate_numbers_s1.NEXTVAL
INTO p_lpn_table(i).lpn_id
FROM DUAL;
l_progress := 'Passed validation inserting into bulk table';
mdebug('Bulk insert LPNs in WLPN: '||to_char(l_lpn_bulk_rec.lpn_id.first)||'-'||to_char(l_lpn_bulk_rec.lpn_id.last), G_INFO);
INSERT INTO wms_license_plate_numbers (
last_update_date
, last_updated_by
, creation_date
, created_by
, request_id
, lpn_id
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
, lpn_context
, sealed_status
, organization_id
, subinventory_code
, locator_id
, inventory_item_id
, revision
, lot_number
, serial_number
, cost_group_id
, tare_weight_uom_code
, tare_weight
, gross_weight_uom_code
, gross_weight
, container_volume_uom
, container_volume
, content_volume_uom_code
, content_volume
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
)
VALUES (
SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_request_id
, l_lpn_bulk_rec.lpn_id(j)
, l_lpn_bulk_rec.license_plate_number(j)
, l_lpn_bulk_rec.parent_lpn_id(j)
, l_lpn_bulk_rec.outermost_lpn_id(j)
, l_lpn_bulk_rec.lpn_context(j)
, 2 --sealed_status
, l_lpn_bulk_rec.organization_id(j)
, l_lpn_bulk_rec.subinventory_code(j)
, l_lpn_bulk_rec.locator_id(j)
, l_lpn_bulk_rec.inventory_item_id(j)
, l_lpn_bulk_rec.revision(j)
, l_lpn_bulk_rec.lot_number(j)
, l_lpn_bulk_rec.serial_number(j)
, l_lpn_bulk_rec.cost_group_id(j)
, l_lpn_bulk_rec.tare_weight_uom_code(j)
, l_lpn_bulk_rec.tare_weight(j)
, l_lpn_bulk_rec.tare_weight_uom_code(j)
, l_lpn_bulk_rec.tare_weight(j)
, l_lpn_bulk_rec.container_volume_uom(j)
, l_lpn_bulk_rec.container_volume(j)
, l_lpn_bulk_rec.content_volume_uom_code(j)
, NULL --content_volume
, l_lpn_bulk_rec.source_type_id(j)
, l_lpn_bulk_rec.source_header_id(j)
, l_lpn_bulk_rec.source_line_id(j)
, l_lpn_bulk_rec.source_line_detail_id(j)
, l_lpn_bulk_rec.source_name(j)
);
mdebug('Bulk insert LPNs in WLPN done count='||SQL%ROWCOUNT, G_INFO);
mdebug('Insert into WLPN failed SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
SELECT 1 INTO l_dummy_num
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn_bulk_rec.license_plate_number(k);
l_progress := 'Could not find reason for LPN insert failure. Give generic failure msg';
mdebug('Calling Create_Update_Containers size='||l_detail_info_tab.last, G_INFO);
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => l_detail_info_tab
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
mdebug('Create_Update_Containers Failed', G_ERROR);
mdebug('Done with Create_Update_Containers', G_INFO);
SELECT wms_license_plate_numbers_s2.NEXTVAL
INTO l_curr_seq
FROM DUAL;
Get_Update_LPN_Start_Num (
p_org_id => p_lpn_attributes.organization_id
, p_qty => l_quantity - l_lpn_cnt + 1
, x_curr_seq => l_curr_seq );
SELECT 1
INTO l_dummy_number
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE license_plate_number = l_lpn_tab(l_lpn_cnt).license_plate_number;
l_progress := 'LPN does not exist yet insert into LPN table';
l_progress := 'Insert the newly created lpn id/license plate number record into the table';
SELECT lpn_id
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
, lpn_context
, organization_id
, subinventory_code
, locator_id
, inventory_item_id
, revision
, lot_number
, serial_number
, cost_group_id
, tare_weight_uom_code
, tare_weight
, gross_weight_uom_code
, gross_weight
, container_volume_uom
, container_volume
, content_volume_uom_code
, content_volume
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM wms_license_plate_numbers
START WITH lpn_id = p_parent_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id
FOR UPDATE NOWAIT;
SELECT lpn_id
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
, lpn_context
, organization_id
, subinventory_code
, locator_id
, inventory_item_id
, revision
, lot_number
, serial_number
, cost_group_id
, tare_weight_uom_code
, tare_weight
, gross_weight_uom_code
, gross_weight
, container_volume_uom
, container_volume
, content_volume_uom_code
, content_volume
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM wms_license_plate_numbers
START WITH parent_lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id
FOR UPDATE NOWAIT;
SELECT wlc.rowid
, wlc.inventory_item_id
, msi.primary_uom_code
, msi.serial_number_control_code
, msi.lot_control_code
, msi.revision_qty_control_code
FROM wms_license_plate_numbers wlpn
, wms_lpn_contents wlc
, mtl_system_items msi
WHERE wlpn.organization_id = p_old_org_id
AND wlpn.outermost_lpn_id = p_outermost_lpn_id
AND wlc.parent_lpn_id = wlpn.lpn_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = p_new_org_id
ORDER BY wlc.inventory_item_id;
wsh_update_tbl WSH_Glbl_Var_Strct_GRP.delivery_details_Attr_tbl_Type;
l_wsh_delete_lpn_id_tbl wsh_util_core.id_tab_type;
mdebug('Values passed by caller to be updated l_lpn_tab_i='||l_lpn_tab_i||' lpn_tbl_cnt='||lpn_tbl_cnt, G_INFO);
fnd_message.set_name('WMS', 'WMS_UPDATE_LPN_ATTR_ERR');
SELECT lpn_id
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
, lpn_context
, organization_id
, subinventory_code
, locator_id
, inventory_item_id
, revision
, lot_number
, serial_number
, cost_group_id
, tare_weight_uom_code
, tare_weight
, gross_weight_uom_code
, gross_weight
, container_volume_uom
, container_volume
, content_volume_uom_code
, content_volume
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
INTO l_old.lpn_id
, l_old.license_plate_number
, l_old.parent_lpn_id
, l_old.outermost_lpn_id
, l_old.lpn_context
, l_old.organization_id
, l_old.subinventory_code
, l_old.locator_id
, l_old.inventory_item_id
, l_old.revision
, l_old.lot_number
, l_old.serial_number
, l_old.cost_group_id
, l_old.tare_weight_uom_code
, l_old.tare_weight
, l_old.gross_weight_uom_code
, l_old.gross_weight
, l_old.container_volume_uom
, l_old.container_volume
, l_old.content_volume_uom_code
, l_old.content_volume
, l_old.source_type_id
, l_old.source_header_id
, l_old.source_line_id
, l_old.source_line_detail_id
, l_old.source_name
, l_old.attribute_category
, l_old.attribute1
, l_old.attribute2
, l_old.attribute3
, l_old.attribute4
, l_old.attribute5
, l_old.attribute6
, l_old.attribute7
, l_old.attribute8
, l_old.attribute9
, l_old.attribute10
, l_old.attribute11
, l_old.attribute12
, l_old.attribute13
, l_old.attribute14
, l_old.attribute15
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_table(lpn_tbl_cnt).lpn_id;
mdebug('Old values from LPN to be updated l_lpn_tab_i='||l_lpn_tab_i||' lpn_tbl_cnt='||lpn_tbl_cnt, G_INFO);
mdebug('Need to update based on lpn_id l_lpn_tab_i='||l_lpn_tab_i, G_INFO);
fnd_message.set_name('WMS', 'WMS_UPDATE_LPN_ATTR_ERR');
mdebug(' LPN is not empty or already is assigned cannot update container item ctx='||l_old.lpn_context, G_ERROR);
fnd_message.set_name('WMS', 'WMS_UPDATE_CONTAINER_ITEM_ERR');
AND p_caller = 'UpdateLPNPage' ) THEN
IF (inv_cache.set_item_rec(
p_organization_id => NVL(p_lpn_table(lpn_tbl_cnt).organization_id, l_old.organization_id)
, p_item_id => l_old.inventory_item_id ))
THEN
IF (l_debug = 1) THEN
mdebug('Got Old Item info citm='||inv_cache.item_rec.container_item_flag||' snctl='||inv_cache.item_rec.serial_number_control_code, G_INFO);
IF ( p_caller = 'UpdateLPNPage' ) THEN
IF ( NVL(l_new.gross_weight, 0) = 0 OR l_new.gross_weight_uom_code IS NULL ) THEN
l_change_in_weight := 0 - l_old.gross_weight;
AND p_caller='UpdateLPNPage'
--AND NVL(p_lpn_table(lpn_tbl_cnt).inventory_item_id ,G_NULL_NUM) = G_NULL_NUM
) THEN
IF (l_new.tare_weight_uom_code IS NOT NULL ) THEN
IF ( l_change_in_weight_uom <> l_new.tare_weight_uom_code ) THEN
l_new.tare_weight := NVL( l_new.tare_weight,0) + inv_convert.inv_um_convert(
l_new.inventory_item_id,
6,
l_change_in_weight,
l_change_in_weight_uom ,
l_new.tare_weight_uom_code
,NULL,NULL);
l_progress := 'Update attribute_category';
l_progress := 'Update attribute1';
l_progress := 'Update attribute2';
l_progress := 'Update attribute3';
l_progress := 'Update attribute4';
l_progress := 'Update attribute5';
l_progress := 'Update attribute6';
l_progress := 'Update attribute7';
l_progress := 'Update attribute8';
l_progress := 'Update attribute9';
l_progress := 'Update attribute10';
l_progress := 'Update attribute11';
l_progress := 'Update attribute12';
l_progress := 'Update attribute13';
l_progress := 'Update attribute14';
l_progress := 'Update attribute15';
fnd_message.set_name('WMS', 'WMS_UPDATE_LPN_ATTR_ERR');
SELECT outermost_lpn_id
INTO l_new.outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = l_new.parent_lpn_id;
l_progress := 'Calling Update_Locator_Capacity';
Update_Locator_Capacity (
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_organization_id => l_new.organization_id
, p_subinventory => l_new.subinventory_code
, p_locator_id => l_new.locator_id
, p_weight_change => l_change_in_gross_weight
, p_weight_uom_change => l_change_in_gross_weight_uom
, p_volume_change => l_change_in_volume
, p_volume_uom_change => l_change_in_volume_uom
);
mdebug('Call to WMS_CONTAINER_PVT.Update_Locator_capacity failed !!!');
/* Update child LPNs */
/************************************************************/
IF ( l_old.outermost_lpn_id <> l_new.outermost_lpn_id ) THEN
IF ( l_old.lpn_id = l_old.outermost_lpn_id ) THEN
-- This LPN was the outermost can update based on outermost LPN column
IF ( l_outer_lpn_ids.exists(l_old.outermost_lpn_id) ) THEN
l_tmp_i := l_outer_lpn_ids(l_old.outermost_lpn_id);
mdebug('Update values to be inline with new outermost lpn l_tmp_i='||l_tmp_i, G_INFO);
/* Update Parent LPNs */
/*****************************************************************/
IF ( l_old.parent_lpn_id = l_new.parent_lpn_id ) THEN
-- List of attributes that affect parent LPNs. Only if there is a change in
-- these should we open cursor for parent LPNs
-- bug5404902 added change in tare weight to condition
IF ( NVL(l_change_in_gross_weight, 0) <> 0 OR
NVL(l_change_in_tare_weight, 0) <> 0 OR
NVL(l_change_in_volume, 0) <> 0 )
THEN
-- If The LPN has a parent and there is a change in weight or volume, cycle through
-- parent LPNs and update their weights and volumes as well
FOR parent_lpn_rec IN nested_parent_lpn_cursor( NVL(l_old.parent_lpn_id, l_new.parent_lpn_id) ) LOOP
IF (l_debug = 1) THEN
mdebug('Got parent lpn lpnid='||parent_lpn_rec.lpn_id, G_INFO);
l_progress := 'Update parent LPN gross weight';
l_progress := 'Update parent LPN tare weight';
wsh_update_tbl(NVL(wsh_update_tbl.last, 0) + 1) := To_DeliveryDetailsRecType(l_tmp_new);
/* Changes that only pertain to Update of entire heirarchy */
/* Update or all lpns with same outermost LPN */
/************************************************************/
l_progress := 'Do updates that effect entire heirarcy';
fnd_message.set_name('WMS', 'WMS_UPDATE_LPN_ATTR_ERR');
UPDATE wms_lpn_contents wlc
SET wlc.last_update_date = SYSDATE
, wlc.last_updated_by = fnd_global.user_id
, wlc.organization_id = l_new.organization_id
, wlc.serial_summary_entry = DECODE (new_org.serial_number_control_code,1,2,6,2,wlc.serial_summary_entry)
, wlc.serial_number = DECODE (new_org.serial_number_control_code,1,NULL,6,NULL,wlc.serial_number)
, wlc.lot_number = DECODE (new_org.lot_control_code,1,NULL,wlc.lot_number)
, wlc.revision = DECODE (new_org.revision_qty_control_code,1,NULL,wlc.revision)
, wlc.primary_quantity = Convert_UOM(new_org.inventory_item_id, primary_quantity, inv_cache.item_rec.primary_uom_code, new_org.primary_uom_code)
WHERE rowid = new_org.rowid;
fnd_message.set_name('WMS', 'WMS_UPDATE_LPN_ATTR_ERR');
l_progress := 'Add new record to update outermost lpn table to be updated';
mdebug('New values from LPN updated l_lpn_tab_i='||l_lpn_tab_i||' lpn_tbl_cnt='||lpn_tbl_cnt, G_INFO);
select nvl(wda.delivery_id,999) into l_delivery_id
from wsh_delivery_details wdd, wsh_delivery_assignments wda
WHERE wda.delivery_detail_id(+) = wdd.delivery_detail_id AND ROWNUM < 2
AND wdd.lpn_id IN
( select wlpn.lpn_id
from wms_license_plate_numbers wlpn
where wlpn.outermost_lpn_id = p_lpn_table(1).lpn_id);
l_wsh_delete_lpn_id_tbl(NVL(l_wsh_delete_lpn_id_tbl.last, 0) + 1) := l_old.lpn_id;
wsh_update_tbl(NVL(wsh_update_tbl.last, 0) + 1) := To_DeliveryDetailsRecType(l_new);
l_tmp_i := NVL(wsh_update_tbl.last, 0) + 1;
wsh_update_tbl(l_tmp_i).organization_id := l_new.organization_id;
wsh_update_tbl(l_tmp_i).lpn_id := l_new.outermost_lpn_id;
wsh_update_tbl(l_tmp_i).container_name := l_new.license_plate_number;
wsh_update_tbl(l_tmp_i).subinventory := l_new.subinventory_code;
wsh_update_tbl(l_tmp_i).locator_id := l_new.locator_id;
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_create_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
mdebug('Create_Update_Containers Failed, May alreade exist in WDD, Try update instead', G_ERROR);
l_IN_rec.action_code := 'UPDATE';
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_create_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
mdebug('Done with Create_Update_Containers', G_INFO);
wsh_create_tbl.delete;
IF ( wsh_update_tbl.last > 0 ) THEN
IF (l_debug = 1) THEN
mdebug('Calling WSH API to update WDD count='||wsh_update_tbl.last, G_INFO);
l_IN_rec.action_code := 'UPDATE';
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_update_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
mdebug('Create_Update_Containers Failed, Might not yet exist in WDD, Try create instead', G_ERROR);
WSH_WMS_LPN_GRP.Create_Update_Containers (
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_detail_info_tab => wsh_update_tbl
, p_IN_rec => l_IN_rec
, x_OUT_rec => l_OUT_rec );
mdebug('WSH Update Containers Failed', G_ERROR);
mdebug('Done with Create_Update_Containers', G_INFO);
wsh_update_tbl.delete;
mdebug('Done with call to WSH Create_Update_Containers', G_INFO);
l_wsh_unpack_lpn_id_tbl.delete;
IF ( l_wsh_delete_lpn_id_tbl.last > 0 ) THEN
IF (l_debug = 1) THEN
mdebug('Calling WSH API to remove LPN from WDD: '||l_wsh_delete_lpn_id_tbl.first||'-'||l_wsh_delete_lpn_id_tbl.last, G_INFO);
l_wsh_action_prms.action_code := 'DELETE';
, p_lpn_id_tbl => l_wsh_delete_lpn_id_tbl
, p_del_det_id_tbl => l_wsh_del_det_id_tbl
, p_action_prms => l_wsh_action_prms
, x_defaults => l_wsh_defaults
, x_action_out_rec => l_wsh_action_out_rec );
l_wsh_delete_lpn_id_tbl.delete;
l_progress := 'Update LPNs last='||l_outer_lpns.last;
mdebug('Bulk Update LPNs in WLPN: '||l_lpn_bulk_rec.lpn_id.first||'-'||l_lpn_bulk_rec.lpn_id.last, G_INFO);
UPDATE wms_license_plate_numbers wlpn
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, organization_id = l_lpn_bulk_rec.organization_id(bulk_i)
, license_plate_number = l_lpn_bulk_rec.license_plate_number(bulk_i)
, parent_lpn_id = l_lpn_bulk_rec.parent_lpn_id(bulk_i)
, outermost_lpn_id = l_lpn_bulk_rec.outermost_lpn_id(bulk_i)
, inventory_item_id = l_lpn_bulk_rec.inventory_item_id(bulk_i)
, subinventory_code = l_lpn_bulk_rec.subinventory_code(bulk_i)
, locator_id = l_lpn_bulk_rec.locator_id(bulk_i)
, tare_weight = l_lpn_bulk_rec.tare_weight(bulk_i)
, tare_weight_uom_code = l_lpn_bulk_rec.tare_weight_uom_code(bulk_i)
, gross_weight_uom_code = l_lpn_bulk_rec.gross_weight_uom_code(bulk_i)
, gross_weight = l_lpn_bulk_rec.gross_weight(bulk_i)
, container_volume = l_lpn_bulk_rec.container_volume(bulk_i)
, container_volume_uom = l_lpn_bulk_rec.container_volume_uom(bulk_i)
, content_volume_uom_code = l_lpn_bulk_rec.content_volume_uom_code(bulk_i)
, content_volume = l_lpn_bulk_rec.content_volume(bulk_i)
--, status_id = l_lpn_bulk_rec.status_id(bulk_i)
, lpn_context = l_lpn_bulk_rec.lpn_context(bulk_i)
--, sealed_status = l_lpn_bulk_rec.sealed_status(bulk_i)
, attribute_category = l_lpn_bulk_rec.attribute_category(bulk_i)
, attribute1 = l_lpn_bulk_rec.attribute1(bulk_i)
, attribute2 = l_lpn_bulk_rec.attribute2(bulk_i)
, attribute3 = l_lpn_bulk_rec.attribute3(bulk_i)
, attribute4 = l_lpn_bulk_rec.attribute4(bulk_i)
, attribute5 = l_lpn_bulk_rec.attribute5(bulk_i)
, attribute6 = l_lpn_bulk_rec.attribute6(bulk_i)
, attribute7 = l_lpn_bulk_rec.attribute7(bulk_i)
, attribute8 = l_lpn_bulk_rec.attribute8(bulk_i)
, attribute9 = l_lpn_bulk_rec.attribute9(bulk_i)
, attribute10 = l_lpn_bulk_rec.attribute10(bulk_i)
, attribute11 = l_lpn_bulk_rec.attribute11(bulk_i)
, attribute12 = l_lpn_bulk_rec.attribute12(bulk_i)
, attribute13 = l_lpn_bulk_rec.attribute13(bulk_i)
, attribute14 = l_lpn_bulk_rec.attribute14(bulk_i)
, attribute15 = l_lpn_bulk_rec.attribute15(bulk_i)
, source_type_id = l_lpn_bulk_rec.source_type_id(bulk_i)
, source_header_id = l_lpn_bulk_rec.source_header_id(bulk_i)
, source_line_id = l_lpn_bulk_rec.source_line_id(bulk_i)
, source_line_detail_id = l_lpn_bulk_rec.source_line_detail_id(bulk_i)
, source_name = l_lpn_bulk_rec.source_name(bulk_i)
WHERE lpn_id = l_lpn_bulk_rec.lpn_id(bulk_i);
mdebug('Bulk Update WLPN failed uniqueness constraint', G_ERROR);
SELECT 1 INTO l_dummy_num
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn_bulk_rec.license_plate_number(bulk_i);
mdebug('LPN '||l_lpn_bulk_rec.license_plate_number(bulk_i)||' already exists, cannot update another LPN with this name', G_ERROR);
mdebug('Bulk Updated WLPN count='||SQL%ROWCOUNT, G_INFO);
l_progress := 'Update outermost LPNs last='||l_outer_lpns.last;
mdebug('Bulk Update outermost LPNs in WLPN: '||l_lpn_bulk_rec.lpn_id.first||'-'||l_lpn_bulk_rec.lpn_id.last, G_INFO);
UPDATE wms_license_plate_numbers wlpn
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, outermost_lpn_id = l_lpn_bulk_rec.outermost_lpn_id(bulk_i)
, organization_id = l_lpn_bulk_rec.organization_id(bulk_i)
, subinventory_code = l_lpn_bulk_rec.subinventory_code(bulk_i)
, locator_id = l_lpn_bulk_rec.locator_id(bulk_i)
, lpn_context = l_lpn_bulk_rec.lpn_context(bulk_i)
, source_type_id = l_lpn_bulk_rec.source_type_id(bulk_i)
, source_header_id = l_lpn_bulk_rec.source_header_id(bulk_i)
, source_line_id = l_lpn_bulk_rec.source_line_id(bulk_i)
, source_line_detail_id = l_lpn_bulk_rec.source_line_detail_id(bulk_i)
, source_name = l_lpn_bulk_rec.source_name(bulk_i)
WHERE outermost_lpn_id = l_lpn_bulk_rec.reference_id(bulk_i);
mdebug('Bulk Updated outermost WLPN count='||SQL%ROWCOUNT, G_INFO);
fnd_message.set_name('WMS', 'WMS_UPDATE_LPN_FAILED');
SELECT wms_lpn_process_temp_s.NEXTVAL
INTO p_process_id
FROM DUAL;
INSERT INTO wms_lpn_process_temp (
process_id
, lpn_id )
VALUES (
p_process_id
, l_lpn_bulk_rec.lpn_id(i) );
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_outer_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT organization_id, parent_lpn_id, lpn_id, inventory_item_id,
tare_weight, tare_weight_uom_code, gross_weight, gross_weight_uom_code,
container_volume, container_volume_uom, content_volume, content_volume_uom_code
FROM wms_license_plate_numbers
START WITH lpn_id = p_child_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT wlc.rowid
, wlc.primary_quantity
, wlc.quantity
, wlc.uom_code
, wlc.cost_group_id
, wlc.secondary_quantity --INVCONV kkillams
, wlc.secondary_uom_code --INVCONV kkillams
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.organization_id = p_organization_id
AND wlc.uom_code = p_uom
AND wlc.inventory_item_id = p_content_item_id
AND NVL(wlc.revision, G_NULL_CHAR) = NVL(p_revision, G_NULL_CHAR)
AND NVL(wlc.lot_number, G_NULL_CHAR) = NVL(p_lot_number, G_NULL_CHAR)
AND NVL(wlc.source_type_id, G_NULL_NUM) = NVL(p_source_type_id, G_NULL_NUM)
AND NVL(wlc.source_header_id, G_NULL_NUM) = NVL(p_source_header_id, G_NULL_NUM)
AND NVL(wlc.source_line_id, G_NULL_NUM) = NVL(p_source_line_id, G_NULL_NUM)
AND NVL(wlc.source_line_detail_id, G_NULL_NUM) = NVL(p_source_line_detail_id, G_NULL_NUM)
AND NVL(wlc.source_name, G_NULL_CHAR) = NVL(p_source_name, G_NULL_CHAR)
AND NVL(wlc.serial_summary_entry, 2) = p_serial_summary_entry;
SELECT wlc.rowid
, wlc.primary_quantity
, wlc.quantity
, wlc.uom_code
, wlc.lot_number
, wlc.serial_summary_entry
, wlc.secondary_quantity --INVCONV kkillams
, wlc.secondary_uom_code --INVCONV kkillams
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = p_content_item_id
AND wlc.uom_code = NVL(p_uom_code, wlc.uom_code)
AND NVL(wlc.revision, G_NULL_CHAR) = NVL(p_revision, G_NULL_CHAR)
AND NVL(wlc.lot_number, G_NULL_CHAR) = NVL(DECODE(p_serial_summary_entry, NULL, wlc.lot_number, p_lot_number), G_NULL_CHAR)
AND NVL(wlc.source_type_id, G_NULL_NUM) = NVL(p_source_type_id, NVL(wlc.source_type_id, G_NULL_NUM))
AND NVL(wlc.source_header_id, G_NULL_NUM) = NVL(p_source_header_id, NVL(wlc.source_header_id, G_NULL_NUM))
AND NVL(wlc.source_line_id, G_NULL_NUM) = NVL(p_source_line_id, NVL(wlc.source_line_id, G_NULL_NUM))
AND NVL(wlc.source_line_detail_id, G_NULL_NUM) = NVL(p_source_line_detail_id, NVL(wlc.source_line_detail_id, G_NULL_NUM))
AND NVL(wlc.source_name, G_NULL_CHAR) = NVL(p_source_name, NVL(wlc.source_name, G_NULL_CHAR))
AND NVL(wlc.serial_summary_entry, 2) = NVL(p_serial_summary_entry, NVL(wlc.serial_summary_entry, 2))
AND (NVL(wlc.source_name, G_NULL_CHAR) NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
OR NVL(p_source_name, G_NULL_CHAR) IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
)
ORDER BY wlc.lot_number, wlc.source_type_id DESC, wlc.source_header_id DESC, wlc.source_line_id DESC, wlc.source_line_detail_id DESC, wlc.source_name DESC;
SELECT rowid
, primary_quantity
, quantity
, secondary_quantity --INVCONV kkillams
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND item_description = p_content_item_desc
AND NVL(cost_group_id, G_NULL_NUM) = NVL(p_cost_group_id, G_NULL_NUM)
AND NVL(serial_summary_entry, 2) = l_serial_summary_entry;
SELECT rowid
, lpn_id
, organization_id
, inventory_item_id
, tare_weight
, tare_weight_uom_code
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
l_tmp_bulk_lpns.lpn_id.delete;
mdebug('Bulk update child LPN WLC/MSN: '||l_tmp_bulk_lpns.lpn_id.first||'-'||l_tmp_bulk_lpns.lpn_id.last, G_INFO);
UPDATE wms_lpn_contents
SET organization_id = l_lpn.organization_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, request_id = l_request_id
WHERE parent_lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
mdebug('Bulk updated org in WLC cnt='||SQL%ROWCOUNT, G_INFO);
UPDATE mtl_serial_numbers
SET current_organization_id = l_lpn.organization_id
, current_subinventory_code = l_lpn.subinventory_code
, current_locator_id = l_lpn.locator_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
mdebug('Bulk updated org/sub/loc in MSN cnt='||SQL%ROWCOUNT, G_INFO);
l_progress := 'Pack LPN: Need to update parent lpns weight and volume';
UPDATE mtl_serial_numbers
SET lpn_id = p_lpn_id
, cost_group_id = p_cost_group_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, last_txn_source_type_id = p_source_type_id
, last_txn_source_id = p_source_header_id
, last_txn_source_name = p_source_name
, revision = DECODE(current_status, 3, revision, p_revision)
, lot_number = DECODE(current_status, 3, lot_number, p_lot_number)
WHERE inventory_item_id = p_content_item_id
AND current_organization_id = p_organization_id
AND length(serial_number) = length(p_from_serial_number)
AND serial_number BETWEEN p_from_serial_number AND NVL(p_to_serial_number, p_from_serial_number);
UPDATE wms_lpn_contents
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, request_id = l_request_id
, quantity = l_quantity
, uom_code = p_uom
, primary_quantity = l_primary_quantity
WHERE rowid = l_existing_record_cursor.rowid;
UPDATE WMS_LPN_CONTENTS
SET last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.USER_ID
, request_id = l_request_id
, quantity = quantity + l_quantity
, uom_code = p_uom
, primary_quantity = primary_quantity + l_primary_quantity
, secondary_quantity = CASE WHEN p_sec_uom IS NOT NULL THEN l_sec_converted_quantity + p_sec_quantity
ELSE secondary_quantity END --INVCONV kkillams
WHERE rowid = l_existing_record_cursor.rowid;
mdebug('Inserting new item row into WLC');
INSERT INTO wms_lpn_contents (
last_update_date
, last_updated_by
, creation_date
, created_by
, request_id
, lpn_content_id
, parent_lpn_id
, organization_id
, inventory_item_id
, item_description
, revision
, lot_number
, quantity
, uom_code
, primary_quantity
, cost_group_id
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
, serial_summary_entry
, secondary_quantity
, secondary_uom_code)
VALUES (
SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, l_request_id
, wms_lpn_contents_s.NEXTVAL
, p_lpn_id
, p_organization_id
, p_content_item_id
, p_content_item_desc
, p_revision
, p_lot_number
, l_quantity
, p_uom
, l_primary_quantity
, p_cost_group_id
, p_source_type_id
, p_source_header_id
, p_source_line_id
, p_source_line_detail_id
, p_source_name
, l_serial_summary_entry
, p_sec_quantity --INVCONV kkillams
, p_sec_uom --INVCONV kkillams
);
l_progress := 'Pack item: Need to update parent lpns weight and volume';
INSERT INTO wms_lpn_contents (
last_update_date
, last_updated_by
, creation_date
, created_by
, request_id
, lpn_content_id
, parent_lpn_id
, organization_id
, inventory_item_id
, item_description
, revision
, lot_number
, serial_number
, quantity
, uom_code
, primary_quantity
, cost_group_id
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
, serial_summary_entry
, secondary_quantity
, secondary_uom_code
)
VALUES (
SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, l_request_id
, WMS_LPN_CONTENTS_S.NEXTVAL
, p_lpn_id
, p_organization_id
, p_content_item_id
, p_content_item_desc
, p_revision
, p_lot_number
, p_from_serial_number
, l_quantity
, p_uom
, l_primary_quantity
, p_cost_group_id
, p_source_type_id
, p_source_header_id
, p_source_line_id
, p_source_line_detail_id
, p_source_name
, 2
, p_sec_quantity --INVCONV kkillams
, p_sec_uom --INVCONV kkillams
);
UPDATE wms_lpn_contents
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, request_id = l_request_id
, quantity = NVL(l_one_time_item_rec.quantity, 1) + NVL(l_quantity, 1)
, uom_code = p_uom
, source_type_id = p_source_type_id
, source_header_id = p_source_header_id
, source_line_id = p_source_line_id
, source_line_detail_id = p_source_line_detail_id
, source_name = p_source_name
, secondary_quantity = CASE WHEN p_sec_uom IS NOT NULL THEN NVL(l_one_time_item_rec.secondary_quantity, 1) +
inv_convert.inv_um_convert(inventory_item_id
,g_precision
,NVL(l_quantity,1)
,p_uom
,p_sec_uom
,NULL
,NULL)
ELSE secondary_quantity END --INVCONV kkillams
, secondary_uom_code = p_sec_uom --INVCONV kkillams
WHERE rowid = l_one_time_item_rec.rowid;
/* Bug 2308339: Update the Organization, Sub, Locator only if Sub is LPN Controlled */
IF( l_subinventory IS NOT NULL AND NVL(inv_cache.tosub_rec.lpn_controlled_flag, 2) = 1 ) THEN
FOR l_child_lpn IN nested_children_cursor(p_content_lpn_id) LOOP
-- Only if there is a difference in location, update nested
-- lpns/items/serials with the new location
IF ( l_content_lpn.organization_id <> p_organization_id OR
l_content_lpn.subinventory_code <> l_subinventory OR
l_content_lpn.locator_id <> l_locator_id ) THEN
-- Update the location information for the packed items
IF ( l_content_lpn.organization_id <> p_organization_id ) THEN
UPDATE wms_lpn_contents
SET organization_id = p_organization_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, request_id = l_request_id
WHERE organization_id = l_content_lpn.organization_id
AND parent_lpn_id = l_child_lpn.lpn_id;
UPDATE mtl_serial_numbers
SET current_organization_id = p_organization_id
, current_subinventory_code = l_subinventory
, current_locator_id = l_locator_id
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE current_organization_id = l_content_lpn.organization_id
AND lpn_id = l_child_lpn.lpn_id;
SELECT 0 INTO l_lpn_is_empty
FROM dual
WHERE EXISTS (
SELECT 1 FROM wms_lpn_contents
WHERE organization_id = p_organization_id
AND parent_lpn_id = l_lpn.lpn_id )
OR EXISTS (
SELECT 1 FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND parent_lpn_id = l_lpn.lpn_id
AND lpn_id <> l_content_lpn.lpn_id );
l_progress := 'Unpack LPN: Need to update parent lpns weight and volume';
UPDATE mtl_serial_numbers
SET lpn_id = NULL
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE inventory_item_id = p_content_item_id
AND current_organization_id = p_organization_id
AND length(serial_number) = length(p_from_serial_number)
AND serial_number BETWEEN p_from_serial_number AND NVL(p_to_serial_number, p_from_serial_number);
UPDATE wms_lpn_contents
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, request_id = l_request_id
, quantity = quantity - l_converted_quantity
, primary_quantity = primary_quantity - l_item_quantity
, secondary_quantity = CASE WHEN p_sec_uom IS NOT NULL
THEN (l_sec_converted_quantity - inv_convert.inv_um_convert(p_content_item_id,
g_precision,
l_item_quantity,
inv_cache.item_rec.primary_uom_code,
p_sec_uom,
NULL,
NULL)
)
ELSE secondary_quantity END --INVCONV kkillams
, secondary_uom_code = p_sec_uom
WHERE rowid = l_temp_record.rowid;
mdebug('Delete column from content table and decrement total unpack quantity', G_INFO);
DELETE FROM wms_lpn_contents
WHERE rowid = l_temp_record.rowid;
SELECT 0 INTO l_lpn_is_empty
FROM dual
WHERE EXISTS (
SELECT 1 FROM wms_lpn_contents
WHERE organization_id = p_organization_id
AND parent_lpn_id = l_lpn.lpn_id )
OR EXISTS (
SELECT 1 FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND parent_lpn_id = l_lpn.lpn_id );
l_progress := 'Unpack item: Need to update parent lpns weight and volume';
UPDATE wms_lpn_contents
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, request_id = l_request_id
, quantity = (l_one_time_item_rec.quantity - l_quantity)
, uom_code = p_uom
WHERE rowid = l_one_time_item_rec.rowid;
DELETE FROM wms_lpn_contents
WHERE rowid = l_one_time_item_rec.rowid;
l_tmp_bulk_lpns.lpn_id.delete;
SELECT unit_weight
, weight_uom_code
INTO l_lpn_tbl(l_tmp_i).tare_weight
, l_lpn_tbl(l_tmp_i).tare_weight_uom_code
FROM mtl_system_items
WHERE organization_id = l_child_lpn.organization_id
AND inventory_item_id = l_child_lpn.inventory_item_id;
l_progress := 'Add child LPN to bulk rec to delete contents';
mdebug('Bulk Delete/Update LPNs in WLC/MSN: '||l_tmp_bulk_lpns.lpn_id.first||'-'||l_tmp_bulk_lpns.lpn_id.last, G_INFO);
DELETE FROM wms_lpn_contents
WHERE parent_lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
mdebug('Bulk delete from WLC cnt='||SQL%ROWCOUNT, G_INFO);
UPDATE mtl_serial_numbers
SET last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
, lpn_id = NULL
WHERE lpn_id = l_tmp_bulk_lpns.lpn_id(bulk_i);
mdebug('Bulk update MSN cnt='||SQL%ROWCOUNT, G_INFO);
SELECT unit_weight
, weight_uom_code
INTO l_lpn_tbl(l_tmp_i).tare_weight
, l_lpn_tbl(l_tmp_i).tare_weight_uom_code
FROM mtl_system_items
WHERE organization_id = empty_lpn_rec.organization_id
AND inventory_item_id = empty_lpn_rec.inventory_item_id;
SELECT 0 INTO l_lpn_is_empty
FROM dual
WHERE EXISTS (
-- Check to make sure that the parent lpn has no items in it
SELECT 1 FROM wms_lpn_contents
WHERE organization_id = p_organization_id
AND parent_lpn_id = empty_lpn_rec.parent_lpn_id )
OR EXISTS (
-- Check to make sure that the parent lpn has no lpns in it
-- Ignore the child lpn that will become pregenerated later since
-- we already know it will unpacked from the parent
SELECT 1 FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND parent_lpn_id = empty_lpn_rec.parent_lpn_id
AND lpn_id <> empty_lpn_rec.lpn_id );
l_progress := 'UPDATE Wt and Vol when making to auto unnest empty LPNs';
SELECT lpn_id
, inventory_item_id
, gross_weight
, gross_weight_uom_code
, tare_weight
, tare_weight_uom_code
, content_volume
, content_volume_uom_code
INTO l_wt_vol_new.lpn_id
, l_wt_vol_new.inventory_item_id
, l_wt_vol_new.gross_weight
, l_wt_vol_new.gross_weight_uom_code
, l_wt_vol_new.tare_weight
, l_wt_vol_new.tare_weight_uom_code
, l_wt_vol_new.content_volume
, l_wt_vol_new.content_volume_uom_code
FROM wms_license_plate_numbers
WHERE lpn_id = empty_lpn_rec.parent_lpn_id;
mdebug('Done with call to WSH Create_Update_Containers', G_INFO);
mdebug('Done with call to WSH Create_Update_Containers', G_INFO);
l_progress := 'Inserting record into WLH';
INSERT INTO wms_lpn_histories (
lpn_history_id
, caller
, source_transaction_id
, parent_lpn_id
, parent_license_plate_number
, lpn_id
, license_plate_number
, inventory_item_id
, item_description
, revision
, lot_number
, serial_number
, to_serial_number
, quantity
, uom_code
, organization_id
, subinventory_code
, locator_id
, lpn_context
, status_id
, sealed_status
, operation_mode
, last_update_date
, last_updated_by
, creation_date
, created_by
, cost_group_id
, outermost_lpn_id
, source_type_id
, source_header_id
, source_line_id
, source_line_detail_id
, source_name
, secondary_quantity
, secondary_uom_code
)
VALUES (
WMS_LPN_HISTORIES_S.NEXTVAL
, p_caller
, p_source_transaction_id
, p_lpn_id
, l_lpn.license_plate_number
, p_content_lpn_id
, l_content_lpn.license_plate_number
, p_content_item_id
, p_content_item_desc
, p_revision
, p_lot_number
, p_from_serial_number
, p_to_serial_number
, l_quantity
, p_uom
, p_organization_id
, p_subinventory
, p_locator_id
, NVL(l_new.lpn_context, l_lpn.lpn_context)
, l_lpn.status_id
, l_lpn.sealed_status
, l_operation_mode
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, p_cost_group_id
, DECODE(l_new.outermost_lpn_id, fnd_api.g_miss_num, NULL, NVL(l_new.outermost_lpn_id, l_lpn.outermost_lpn_id))
, NVL(p_source_type_id, l_lpn.source_type_id)
, NVL(p_source_header_id, l_lpn.source_header_id)
, NVL(p_source_line_id, l_lpn.source_line_id)
, NVL(p_source_line_detail_id, l_lpn.source_line_detail_id)
, NVL(p_source_name, l_lpn.source_name)
, p_sec_quantity --INVCONV kkillams
, p_sec_uom --INVCONV kkillams
);
PROCEDURE Validate_Update_Wt_Volume (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_lpn_id IN NUMBER
, p_content_lpn_id IN VARCHAR2 := NULL
, p_content_item_id IN NUMBER := NULL
, p_quantity IN NUMBER := NULL
, p_uom IN VARCHAR2 := NULL
, p_organization_id IN NUMBER := NULL
, p_enforce_wv_constraints IN NUMBER := 2
, p_operation IN NUMBER
, p_action IN NUMBER
, x_valid_operation OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update_Wt_Volume';
END Validate_Update_Wt_Volume;
SELECT max_load_quantity
FROM WSH_CONTAINER_ITEMS
WHERE master_organization_id = p_organization_id
AND container_item_id = p_dest_cont_item_id
AND load_item_id = p_source_item_id;
SELECT container_item_id, max_load_quantity, preferred_flag
FROM WSH_CONTAINER_ITEMS
WHERE master_organization_id = p_organization_id
AND load_item_id = p_source_item_id
AND container_item_id IN
(SELECT inventory_item_id
FROM MTL_SYSTEM_ITEMS
WHERE mtl_transactions_enabled_flag = 'Y'
AND container_item_flag = 'Y'
AND organization_id = p_organization_id);
/* Select the most constraining value for l_max_load_quantity */
IF (l_dest_cont_item.maximum_load_weight IS NOT NULL) THEN
-- Check that the source item's unit weight is less than or
-- equal to the destination container item's maximum load weight
IF (l_temp_value <= l_dest_cont_item.maximum_load_weight) THEN
IF (l_max_load_quantity > FLOOR (l_dest_cont_item.maximum_load_weight /
l_temp_value)) THEN
l_max_load_quantity := FLOOR (l_dest_cont_item.maximum_load_weight /
l_temp_value);
SELECT max_load_quantity
FROM wsh_container_items
WHERE load_item_id = p_inventory_item_id
AND container_item_id = p_container_item_id
AND master_organization_id = p_organization_id
AND ROWNUM < 2;
SELECT lpn_id
FROM wms_lpn_process_temp
WHERE process_id = l_process_id;
SELECT mmtt.cartonization_id,
mmtt.transaction_quantity,
mmtt.primary_quantity,
mmtt.transaction_uom,
mtlt.lot_number,
NVL(msnt1.fm_serial_number, msnt2.fm_serial_number),
NVL(msnt1.to_serial_number, msnt2.to_serial_number)
, mmtt.secondary_transaction_quantity --INVCONV kkillams
, mmtt.secondary_uom_code --INVCONV kkillams
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt1, mtl_serial_numbers_temp msnt2
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transaction_header_id = l_trx_header_id
AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND msnt1.transaction_temp_id(+) = mtlt.serial_transaction_temp_id
AND msnt2.transaction_temp_id(+) = mmtt.transaction_temp_id;
SELECT primary_uom_code, lot_control_code
INTO l_primary_uom, l_lot_control_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT COUNT(*)
INTO temp
FROM wms_lpn_process_temp
WHERE process_id = l_process_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_header_id
FROM DUAL;
x_error_code := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id=> l_trx_header_id,
p_item_id=> p_inventory_item_id,
p_revision=> p_revision,
p_org_id=> p_organization_id,
p_trx_action_id=> inv_globals.g_action_containerpack,
p_subinv_code=> p_subinventory,
p_tosubinv_code=> NULL,
p_locator_id=> p_locator_id,
p_tolocator_id=> NULL,
p_xfr_org_id=> NULL,
p_trx_type_id=> inv_globals.g_type_container_pack,
p_trx_src_type_id=> inv_globals.g_sourcetype_inventory,
p_trx_qty=> p_quantity,
p_pri_qty=> l_primary_quantity,
p_uom=> p_uom,
p_date=> SYSDATE,
p_reason_id=> NULL,
p_user_id=> fnd_global.user_id,
p_frt_code=> NULL,
p_ship_num=> NULL,
p_dist_id=> NULL,
p_way_bill=> NULL,
p_exp_arr=> NULL,
p_cost_group=> NULL,
p_from_lpn_id=> NULL,
p_cnt_lpn_id=> NULL,
--p_xfr_lpn_id => l_lpn_to_pack,
x_trx_tmp_id=> l_trx_tmp_id,
x_proc_msg=> x_proc_msg,
p_secondary_trx_qty =>p_sec_quantity, --INVCONV kkillams
p_secondary_uom =>p_sec_uom --INVCONV kkillams
);
mdebug('failed INSERT_LINE_TRX '|| x_error_code, 1);
fnd_message.set_name('WMS', 'WMS_INSERT_LINE_TRX_FAIL');
mdebug('Insert lot entry in MTL_LOT_NUMBER_TEMP lot='|| l_lot_number, G_INFO);
x_error_code := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id=> l_trx_tmp_id,
p_user_id=> fnd_global.user_id,
p_lot_number=> l_lot_number,
p_trx_qty=> p_quantity,
p_pri_qty=> l_primary_quantity,
p_secondary_qty =>p_sec_quantity, --INVCONV kkillams
p_secondary_uom =>p_sec_uom, --INVCONV kkillams
x_ser_trx_id=> l_ser_trx_id,
x_proc_msg=> x_proc_msg
);
mdebug('failed INSERT_LOT_TRX lot='||l_lot_number||' '||x_error_code, G_ERROR);
fnd_message.set_name('WMS', 'WMS_INSERT_LOT_TRX_FAIL');
mdebug('inserting serials '|| l_serial_number_from || '-' || l_serial_number_to || ' into mmtt', G_INFO);
x_error_code := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id=> l_trx_tmp_id,
p_user_id=> fnd_global.user_id,
p_fm_ser_num=> l_serial_number_from,
p_to_ser_num=> l_serial_number_to,
x_proc_msg=> x_proc_msg
);
mdebug('failed INSERT_SER_TRX '|| x_error_code, G_ERROR);
fnd_message.set_name('WMS', 'WMS_INSERT_SER_TRX_FAIL');
SELECT wms_lpn_process_temp_s.NEXTVAL
INTO l_process_id
FROM DUAL;
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_source
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_lpn_to_pack;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_header_id
FROM DUAL;
x_error_code := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id=> l_trx_header_id,
p_item_id=> p_inventory_item_id,
p_revision=> p_revision,
p_org_id=> p_organization_id,
p_trx_action_id=> inv_globals.g_action_containerpack,
p_subinv_code=> p_subinventory,
p_tosubinv_code=> NULL,
p_locator_id=> p_locator_id,
p_tolocator_id=> NULL,
p_xfr_org_id=> NULL,
p_trx_type_id=> inv_globals.g_type_container_pack,
p_trx_src_type_id=> inv_globals.g_sourcetype_inventory,
p_trx_qty=> l_pack_quantity,
p_pri_qty=> l_primary_quantity,
p_uom=> l_primary_uom,
p_date=> SYSDATE,
p_reason_id=> NULL,
p_user_id=> fnd_global.user_id,
p_frt_code=> NULL,
p_ship_num=> NULL,
p_dist_id=> NULL,
p_way_bill=> NULL,
p_exp_arr=> NULL,
p_cost_group=> NULL,
p_from_lpn_id=> NULL,
p_cnt_lpn_id=> NULL,
p_xfr_lpn_id=> l_lpn_to_pack,
x_trx_tmp_id=> l_trx_tmp_id,
x_proc_msg=> x_proc_msg,
p_secondary_trx_qty =>l_sec_pack_quantity, --INVCONV kkillams
p_secondary_uom =>l_sec_uom --INVCONV kkillams
);
mdebug('failed INSERT_LINE_TRX '|| x_error_code, G_ERROR);
fnd_message.set_name('WMS', 'WMS_INSERT_LINE_TRX_FAIL');
mdebug('Insert lot entry in MTL_LOT_NUMBER_TEMP lot='|| l_lot_number, G_INFO);
x_error_code := inv_trx_util_pub.insert_lot_trx(
p_trx_tmp_id=> l_trx_tmp_id,
p_user_id=> fnd_global.user_id,
p_lot_number=> l_lot_number,
p_trx_qty=> l_primary_quantity,
p_pri_qty=> l_primary_quantity,
p_secondary_qty => l_sec_pack_quantity, --INVCONV kkillams
p_secondary_uom =>l_sec_uom, --INVCONV kkillams
x_ser_trx_id=> l_ser_trx_id,
x_proc_msg=> x_proc_msg
);
mdebug('failed INSERT_LOT_TRX '|| x_error_code, 1);
fnd_message.set_name('WMS', 'WMS_INSERT_LOT_TRX_FAIL');
mdebug('inserting serials '|| l_serial_number_from || '-' || l_serial_number_to || ' into mmtt', G_INFO);
x_error_code := inv_trx_util_pub.insert_ser_trx(
p_trx_tmp_id=> l_trx_tmp_id,
p_user_id=> fnd_global.user_id,
p_fm_ser_num=> l_serial_number_from,
p_to_ser_num=> l_serial_number_to,
x_proc_msg=> x_proc_msg
);
mdebug('failed INSERT_SER_TRX '|| x_error_code, 1);
fnd_message.set_name('WMS', 'WMS_INSERT_SER_TRX_FAIL');
mdebug('making insert to temp table', G_MESSAGE);
INSERT INTO wms_lpn_process_temp ( process_id, lpn_id )
VALUES ( l_process_id, l_lpn_to_pack);
DELETE FROM wms_lpn_process_temp
WHERE process_id = l_process_id;
DELETE FROM mtl_material_transactions_temp
WHERE transaction_header_id = l_trx_header_id;
UPDATE mtl_serial_numbers
SET lpn_id = p_lpn_id,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_txn_source_type_id = p_source_type_id,
revision = DECODE(current_status, 3, revision, p_revision),
lot_number = DECODE(current_status, 3, lot_number, p_lot_number)
WHERE inventory_item_id = p_content_item_id
AND serial_number = l_current_serial
AND current_organization_id = p_organization_id;
UPDATE wms_lpn_contents
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
serial_summary_entry = 1,
source_type_id = p_source_type_id
WHERE parent_lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_content_item_id
AND NVL(revision, G_NULL_CHAR) = NVL(p_revision, G_NULL_CHAR)
AND NVL(lot_number, G_NULL_CHAR) = NVL(p_lot_number, G_NULL_CHAR);
UPDATE wms_license_plate_numbers
SET last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
lpn_context = LPN_CONTEXT_WIP
WHERE lpn_id = p_lpn_id
AND organization_id = p_organization_id;
SELECT lpn_id, parent_lpn_id, inventory_item_id, organization_id,
revision, lot_number, serial_number, cost_group_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE Level <= p_explosion_level
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT lpn_id, parent_lpn_id, inventory_item_id, organization_id,
revision, lot_number, serial_number, cost_group_id
FROM WMS_LICENSE_PLATE_NUMBERS
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT parent_lpn_id, inventory_item_id, item_description,
organization_id, revision, lot_number,
serial_number, quantity, uom_code, cost_group_id,
secondary_quantity, secondary_uom_code --INVCONV kkillams
FROM WMS_LPN_CONTENTS
WHERE parent_lpn_id = l_current_lpn
AND NVL(serial_summary_entry, 2) = 2;
SELECT inventory_item_id, current_organization_id, lpn_id,
revision, lot_number, serial_number, cost_group_id
FROM MTL_SERIAL_NUMBERS
WHERE lpn_id = l_current_lpn
-- bug 5103594, added the Order By clause
ORDER BY inventory_item_id, revision, lot_number, serial_number;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
AND organization_id = v_lpn_serial_content.current_organization_id;
SELECT primary_uom_code
INTO l_temp_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
AND organization_id = v_lpn_serial_content.current_organization_id;
SELECT *
INTO p_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_lpn.lpn_id
FOR UPDATE;
SELECT *
INTO p_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_lpn.lpn_id;
SELECT *
INTO p_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LICENSE_PLATE_NUMBER = p_lpn.license_plate_number
FOR UPDATE;
SELECT *
INTO p_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LICENSE_PLATE_NUMBER = p_lpn.license_plate_number;
SELECT *
INTO p_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_lpn.lpn_id
AND LICENSE_PLATE_NUMBER = p_lpn.license_plate_number
FOR UPDATE;
SELECT *
INTO p_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE LPN_ID = p_lpn.lpn_id
AND LICENSE_PLATE_NUMBER = p_lpn.license_plate_number;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id;
SELECT parent_lpn_id INTO l_parent_lpn_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT WMS_CONTAINER_PVT.F
INTO l_lpn_is_valid
FROM mtl_reservations
WHERE organization_id = p_organization_id
AND lpn_id = lpn_rec.lpn_id
AND rownum < 2;
SELECT WMS_CONTAINER_PVT.F
INTO l_lpn_is_valid
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND ( ALLOCATED_LPN_ID = lpn_rec.lpn_id OR
lpn_id = lpn_rec.lpn_id OR
content_lpn_id = lpn_rec.lpn_id OR
transfer_lpn_id = lpn_rec.lpn_id )
AND rownum < 2;
SELECT WMS_CONTAINER_PVT.F
INTO l_lpn_is_valid
FROM mtl_onhand_quantities_detail
WHERE organization_id = p_organization_id
AND lpn_id = lpn_rec.lpn_id
AND rownum < 2;
SELECT WMS_CONTAINER_PVT.F
INTO l_lpn_is_valid
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND lpn_id = lpn_rec.lpn_id
AND rownum < 2;
l_insert_item_rec BOOLEAN := FALSE;
l_insert_lot_rec BOOLEAN := FALSE;
l_insert_ser_rec BOOLEAN := FALSE;
SELECT lpn_id, lpn_context, subinventory_code, locator_id, parent_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id <> p_outermost_lpn_id
START WITH lpn_id = p_outermost_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT inventory_item_id, quantity, uom_code, revision, lot_number, quantity lot_quantity,
cost_group_id, serial_summary_entry
,secondary_quantity, secondary_uom_code --INVCONV kkillams
FROM wms_lpn_contents
WHERE organization_id = p_organization_id
AND parent_lpn_id = p_parent_lpn_id
ORDER BY inventory_item_id, revision, cost_group_id, lot_number;
SELECT serial_number
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND lpn_id = p_lpn_id
AND NVL(revision, '@') = NVL(p_revision, '@')
AND NVL(lot_number, '@') = NVL(p_lot_number, '@');
SELECT mtl_material_transactions_s.nextval
INTO l_trx_hdr_id
FROM DUAL;
l_return_status := INV_TRX_UTIL_PUB.INSERT_LINE_TRX (
p_trx_hdr_id => l_trx_hdr_id
, p_item_id => -1
, p_org_id => p_organization_id
, p_subinv_code => Nested_LPN_rec.subinventory_code
, p_locator_id => Nested_LPN_rec.locator_id
, p_trx_src_type_id => INV_GLOBALS.G_SourceType_Inventory
, p_trx_action_id => INV_GLOBALS.G_Action_ContainerUnPack
, p_trx_type_id => INV_GLOBALS.G_TYPE_CONTAINER_UNPACK
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'Ea'
, p_date => SYSDATE
, p_user_id => fnd_global.user_id
, p_from_lpn_id => Nested_LPN_rec.parent_lpn_id
, p_cnt_lpn_id => Nested_LPN_rec.lpn_id
, x_trx_tmp_id => l_trx_tmp_id
, x_proc_msg => x_msg_data );
mdebug('Insert_Line_Trx failed :'||x_msg_data, 1);
mdebug('Inserted unpack hdrid='||l_trx_hdr_id||' tempid='||l_trx_tmp_id||' lpn='||Nested_LPN_rec.lpn_id||' parlpn='|| Nested_LPN_rec.parent_lpn_id, 4);
SELECT mtl_material_transactions_s.nextval
INTO l_trx_tmp_id
FROM DUAL;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = l_next_item_rec.inventory_item_id;
l_insert_lot_rec := TRUE;
l_insert_item_rec := TRUE;
l_insert_lot_rec := TRUE;
l_insert_ser_rec := TRUE;
IF ( l_insert_lot_rec ) THEN
IF ( l_crnt_item_rec.uom_code <> l_primary_uom ) THEN
l_converted_qty := Convert_UOM(l_crnt_item_rec.inventory_item_id, l_crnt_item_rec.quantity, l_crnt_item_rec.uom_code, l_primary_uom);
l_return_status := INV_TRX_UTIL_PUB.Insert_Lot_Trx(
p_trx_tmp_id => l_trx_tmp_id
, p_user_id => fnd_global.user_id
, p_lot_number => l_crnt_item_rec.lot_number
, p_trx_qty => l_crnt_item_rec.lot_quantity
, p_pri_qty => l_converted_qty
, p_secondary_qty => l_crnt_item_rec.secondary_quantity --INVCONV kkillams
, p_secondary_uom => l_crnt_item_rec.secondary_uom_code --INVCONV kkillams
, x_ser_trx_id => l_ser_tmp_id
, x_proc_msg => x_msg_data );
mdebug('Insert_Lot_Trx failed :'||x_msg_data, 1);
mdebug('Inserted lot tempid='||l_trx_tmp_id||' lot='||l_crnt_item_rec.lot_number||' qty='||l_crnt_item_rec.lot_quantity||' stmpid='||l_ser_tmp_id, 4);
l_insert_ser_rec := TRUE;
l_insert_lot_rec := FALSE;
IF ( l_insert_ser_rec ) THEN
OPEN LPN_serial_cur(Nested_LPN_rec.lpn_id, l_crnt_item_rec.inventory_item_id,
l_crnt_item_rec.revision, l_crnt_item_rec.lot_number);
mdebug('Insert ser tempid='||l_ser_tmp_id||' fmsn='||l_fm_serial||' tosn='||l_to_serial, 4);
l_return_status := INV_TRX_UTIL_PUB.Insert_Ser_Trx (
p_trx_tmp_id => l_ser_tmp_id
, p_user_id => fnd_global.user_id
, p_fm_ser_num => l_fm_serial
, p_to_ser_num => l_to_serial
, x_proc_msg => x_msg_data );
mdebug('Insert_Ser_Trx failed :'||x_msg_data, 1);
l_insert_ser_rec := FALSE;
IF ( l_insert_item_rec ) THEN
IF (l_debug = 1) THEN
mdebug('Insert split tmpid='||l_trx_tmp_id||' plpn='||Nested_LPN_rec.parent_lpn_id||' itm='||l_crnt_item_rec.inventory_item_id||' rev='||l_crnt_item_rec.revision||' qty='||l_crnt_item_rec.quantity||' cg='||l_crnt_item_rec.cost_group_id, 4);
INSERT INTO mtl_material_transactions_temp (
transaction_header_id
, transaction_temp_id
, process_flag
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, transaction_date
, organization_id
, subinventory_code
, locator_id
, inventory_item_id
, revision
, cost_group_id
, transaction_source_type_id
, transaction_action_id
, transaction_type_id
, transaction_quantity
, primary_quantity
, transaction_uom
, lpn_id
, transfer_lpn_id
, acct_period_id
, secondary_transaction_quantity --INCONV kkillams
, secondary_uom_code --INCONV kkillamsb
) VALUES (
l_trx_hdr_id
, l_trx_tmp_id
, 'Y'
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.user_id
, SYSDATE
, p_organization_id
, Nested_LPN_rec.subinventory_code
, Nested_LPN_rec.locator_id
, l_crnt_item_rec.inventory_item_id
, l_crnt_item_rec.revision
, l_crnt_item_rec.cost_group_id
, INV_GLOBALS.G_SourceType_Inventory
, INV_GLOBALS.G_Action_ContainerSplit
, INV_GLOBALS.G_TYPE_CONTAINER_SPLIT
, l_crnt_item_rec.quantity
, l_converted_qty
, l_crnt_item_rec.uom_code
, Nested_LPN_rec.lpn_id
, p_outermost_lpn_id
, v_acct_period_id
, l_crnt_item_rec.secondary_quantity --INCONV kkillams
, l_crnt_item_rec.secondary_uom_code --INCONV kkillams
);
SELECT mtl_material_transactions_s.nextval
INTO l_trx_tmp_id
FROM DUAL;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = l_crnt_item_rec.inventory_item_id;
l_insert_item_rec := FALSE;
SELECT rowid, lpn_id, parent_lpn_id, subinventory_code, locator_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id <> p_outermost_lpn_id
START WITH lpn_id = p_outermost_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT mtl_material_transactions_s.nextval
INTO l_trx_hdr_id
FROM DUAL;
l_return_status := INV_TRX_UTIL_PUB.INSERT_LINE_TRX (
p_trx_hdr_id => l_trx_hdr_id
, p_item_id => -1
, p_org_id => p_organization_id
, p_subinv_code => lpn_rec.subinventory_code
, p_locator_id => lpn_rec.locator_id
, p_trx_src_type_id => INV_GLOBALS.G_SourceType_Inventory
, p_trx_action_id => INV_GLOBALS.G_Action_ContainerUnPack
, p_trx_type_id => INV_GLOBALS.G_TYPE_CONTAINER_UNPACK
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'Ea'
, p_date => SYSDATE
, p_user_id => fnd_global.user_id
, p_from_lpn_id => lpn_rec.parent_lpn_id
, p_cnt_lpn_id => lpn_rec.lpn_id
, x_trx_tmp_id => l_trx_tmp_id
, x_proc_msg => x_msg_data );
mdebug('Inserted unpack tempid='||l_trx_tmp_id||' lpn='||lpn_rec.lpn_id||' parlpn='|| lpn_rec.parent_lpn_id, 4);
SELECT rowid, lpn_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND outermost_lpn_id = p_outermost_lpn_id;
DELETE FROM wms_lpn_contents
WHERE parent_lpn_id = lpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = LPN_CONTEXT_PREGENERATED
, subinventory_code = NULL
, locator_id = NULL
, parent_lpn_id = NULL
, outermost_lpn_id = lpn_id
, content_volume = NULL
, content_volume_uom_code = NULL
, gross_weight = tare_weight
, gross_weight_uom_code = tare_weight_uom_code
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE rowid = lpn_rec.rowid;
SELECT wda.delivery_id
INTO l_delivery_id
FROM wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments wda
WHERE wdd.lpn_id IN (SELECT lpn_id FROM wms_license_plate_numbers
WHERE outermost_lpn_id = (SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id)
AND lpn_context = 11)
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND ROWNUM = 1;