[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Bug#2200989. Added local procedure to update the wt and volume
of the LPNs in shipping when the corrosponding LPN is modified
in WMS_LICENSE_PLATE_NUMBERS. */
PROCEDURE update_shipping_details(p_lpn_id IN NUMBER, p_gross_weight IN NUMBER, p_net_weight IN NUMBER, p_weight_uom IN VARCHAR2, p_volume IN NUMBER, p_volume_uom IN VARCHAR2) IS
CURSOR wsh_lpn_id IS
SELECT 1
FROM wsh_delivery_details
WHERE lpn_id = p_lpn_id;
SELECT nvl(inventory_item_id, -99999)
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT delivery_detail_id,
container_name
INTO l_delivery_detail_id,
l_container_name
FROM wsh_delivery_details
WHERE lpn_id = p_lpn_id;
mdebug('***in Update Shipping Details proc***');
wsh_container_grp.update_container(l_api_version, fnd_api.g_false,fnd_api.g_false,fnd_api.g_valid_level_full, x_return_status, x_msg_count, x_msg_data, l_changed_attributes);
END update_shipping_details;
SELECT 'Validate-Serial'
FROM DUAL
WHERE EXISTS( SELECT 'Subinventory-not-given'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_container_item_id
AND current_organization_id = p_organization_id
AND serial_number = l_current_serial
AND current_status IN (1, 5, 6, 7));
l_insert_update_flag VARCHAR2(1); -- flag to signal existing lpn or new one
l_insert_update_flag := 'i';
l_insert_update_flag := 'u';
l_insert_update_flag := 'i';
l_insert_update_flag := 'u';
IF (l_insert_update_flag = 'u') THEN
UPDATE wms_license_plate_numbers
SET inventory_item_id = p_container_item_id,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
revision = p_revision,
lot_number = p_lot_number,
serial_number = p_serial_number,
organization_id = p_organization_id,
subinventory_code = p_subinventory,
/* Bug 3936269 Modifying the locator_id field as null if p_locator_id =0
locator_id = p_locator_id, */
locator_id = decode (p_locator_id,0,null,p_locator_id),
--End of fix for Bug 3936269
gross_weight_uom_code = l_new_weight_uom,
gross_weight = l_new_weight,
tare_weight_uom_code = l_container_item.weight_uom_code,
tare_weight = l_container_item.unit_weight,
sealed_status = 2,
cost_group_id = p_cost_group_id,
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
WHERE lpn_id = p_lpn_id;
/* Added code to check if the LPN being updated is in Shipping
if so, then the updated Wt ,container item are passed on to the
WSH_DELIVERY_DETAILS table. Bug#2200989*/
l_net_weight := l_lpn.gross_weight;
mdebug('Associate LPN***before update of shipping details***');
update_shipping_details(
p_lpn_id => p_lpn_id
, p_gross_weight => l_new_weight
, p_net_weight => l_net_weight
, p_weight_uom => l_new_weight_uom
, p_volume => l_lpn.content_volume
, p_volume_uom => l_lpn.content_volume_uom_code
);
ELSE /* l_insert_update_flag = 'i' */
/* Need to generate a license plate number to go along with the given lpn id */
LOOP
SELECT wms_license_plate_numbers_s2.NEXTVAL
INTO l_curr_seq
FROM DUAL;
INSERT INTO wms_license_plate_numbers
(
lpn_id,
license_plate_number,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
revision,
lot_number,
serial_number,
organization_id,
subinventory_code,
locator_id,
parent_lpn_id,
gross_weight_uom_code,
gross_weight,
content_volume_uom_code,
content_volume,
tare_weight_uom_code,
tare_weight,
status_id,
lpn_context,
sealed_status,
cost_group_id,
source_type_id,
source_header_id,
source_line_id,
source_line_detail_id,
source_name
)
VALUES (
l_new_lpn_id,
l_new_lpn,
p_container_item_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
p_revision,
p_lot_number,
p_serial_number,
p_organization_id,
p_subinventory,
/* Bug 3936269 Inserting null for the locator_id field if p_locator_id is 0
p_locator_id,*/
decode(p_locator_id,0,null,p_locator_id),
--End of fix for Bug 3936269
NULL,
l_container_item.weight_uom_code,
l_container_item.unit_weight,
l_container_item.volume_uom_code,
0,
l_container_item.weight_uom_code,
l_container_item.unit_weight,
NULL,
1,
2,
p_cost_group_id,
p_source_type_id,
p_source_header_id,
p_source_line_id,
p_source_line_detail_id,
p_source_name
);
SELECT lpn_id,
organization_id,
subinventory_code,
locator_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn.lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT *
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn.lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT organization_id,
lpn_content_id,
parent_lpn_id,
inventory_item_id
FROM wms_lpn_contents
WHERE parent_lpn_id = l_current_lpn;
SELECT 1
FROM mtl_serial_numbers
WHERE lpn_id = l_current_lpn;
SELECT 1
FROM wms_license_plate_numbers
WHERE parent_lpn_id = l_current_lpn;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_content_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT organization_id,
lpn_content_id,
parent_lpn_id,
inventory_item_id
FROM wms_lpn_contents
WHERE parent_lpn_id = l_current_lpn
AND NVL(serial_summary_entry, 2) = 2;
SELECT current_organization_id,
current_subinventory_code,
current_locator_id,
inventory_item_id,
serial_number
FROM mtl_serial_numbers
WHERE lpn_id = l_current_lpn;
SELECT wlc.quantity,
wlc.uom_code
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
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 NVL(wlc.revision, '###') = NVL(p_revision, '###')
AND NVL(wlc.lot_number, '###') = NVL(p_lot_number, '###')
AND NVL(wlc.serial_number, '###') = NVL(l_current_serial, '###')
AND NVL(wlc.cost_group_id, l_null_cost_group_val) = NVL(DECODE(wlpn.lpn_context, 3, wlc.cost_group_id, p_cost_group_id), l_null_cost_group_val)
AND NVL(wlc.source_type_id, -9999) = NVL(p_source_type_id, -9999)
AND NVL(wlc.source_header_id, -9999) = NVL(p_source_header_id, -9999)
AND NVL(wlc.source_line_id, -9999) = NVL(p_source_line_id, -9999)
AND NVL(wlc.source_line_detail_id, -9999) = NVL(p_source_line_detail_id, -9999)
AND NVL(wlc.source_name, '###') = NVL(p_source_name, '###')
AND wlc.parent_lpn_id = wlpn.lpn_id
AND NVL(wlc.serial_summary_entry, 2) = 2;
SELECT wlc.quantity,
wlc.uom_code,
wlc.source_type_id,
wlc.source_header_id,
wlc.source_line_id,
wlc.source_line_detail_id,
wlc.source_name,
wlc.cost_group_id
FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
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 NVL(wlc.revision, '###') = NVL(p_revision, '###')
AND NVL(wlc.lot_number, '###') = NVL(p_lot_number, '###')
AND NVL(wlc.serial_number, '###') = NVL(l_current_serial, '###')
--AND NVL(wlc.cost_group_id, l_null_cost_group_val) = NVL(DECODE(wlpn.lpn_context, 3, wlc.cost_group_id, NVL(p_cost_group_id, wlc.cost_group_id)), l_null_cost_group_val)
AND NVL(wlc.source_type_id, -9999) = NVL(p_source_type_id, NVL(wlc.source_type_id, -9999))
AND NVL(wlc.source_header_id, -9999) = NVL(p_source_header_id, NVL(wlc.source_header_id, -9999))
AND NVL(wlc.source_line_id, -9999) = NVL(p_source_line_id, NVL(wlc.source_line_id, -9999))
AND NVL(wlc.source_line_detail_id, -9999) = NVL(p_source_line_detail_id, NVL(wlc.source_line_detail_id, -9999))
AND NVL(wlc.source_name, '###') = NVL(p_source_name, NVL(wlc.source_name, '###'))
AND wlc.parent_lpn_id = wlpn.lpn_id
AND NVL(wlc.serial_summary_entry, 2) = 2
AND (NVL(wlc.source_name, '###') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
OR NVL(p_source_name, '###') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
)
ORDER BY 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 'Validate Serial'
FROM DUAL
WHERE EXISTS( SELECT 'X'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_content_item_id
AND current_organization_id = p_organization_id
AND serial_number = l_current_serial
AND current_status IN (1, 4, 5, 6, 7));
SELECT 'Validate Lot'
FROM DUAL
WHERE EXISTS( SELECT 'X'
FROM mtl_lot_numbers
WHERE inventory_item_id = p_content_item_id
AND organization_id = p_organization_id
AND lot_number = p_lot_number);
SELECT quantity
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, l_null_cost_group_val) = NVL(p_cost_group_id, l_null_cost_group_val)
AND NVL(serial_summary_entry, 2) = l_serial_summary_entry;
SELECT lpn_id,
inventory_item_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT COUNT(*)
INTO l_temp_count
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND lot_number = p_lot_number
AND inventory_item_id = p_content_item_id;
SELECT COUNT(*)
INTO l_dynamic_status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_content_item_id
AND serial_number = l_current_serial
AND current_organization_id = p_organization_id
AND current_status = 6;
SELECT 'Validate Serial'
FROM DUAL
WHERE EXISTS( SELECT 'X'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_content_item_id
AND current_organization_id = p_organization_id
AND serial_number = l_current_serial
AND current_status IN (1, 5, 6, 7));
SELECT COUNT(*)
INTO l_dynamic_status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_content_item_id
AND serial_number = l_current_serial
AND current_organization_id = p_organization_id
AND current_status = 6;
SELECT gross_weight,
content_volume
INTO lpn_weight,
lpn_volume
FROM wms_license_plate_numbers
WHERE lpn_id = g_lpn_wt_vol_changes(i).lpn_id;
UPDATE wms_license_plate_numbers
SET gross_weight = lpn_weight - g_lpn_wt_vol_changes(i).gross_weight_change
, content_volume = lpn_volume - g_lpn_wt_vol_changes(i).content_volume_change
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = g_lpn_wt_vol_changes(i).lpn_id;
g_lpn_wt_vol_changes.DELETE;
IS SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT lpn_context
, organization_id
INTO l_lpn_context
, l_organization_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT COUNT(1)
INTO l_valid
FROM mtl_material_transactions_temp
WHERE organization_id = l_organization_id
AND ( lpn_id = p_lpn_id
OR transfer_lpn_id = p_lpn_id
OR content_lpn_id = p_lpn_id
OR allocated_lpn_id = p_lpn_id
OR cartonization_id = p_lpn_id
);
SELECT COUNT(1)
INTO l_valid
FROM mtl_transactions_interface
WHERE organization_id = l_organization_id
AND ( lpn_id = p_lpn_id
OR transfer_lpn_id = p_lpn_id
OR content_lpn_id = p_lpn_id
);
SELECT COUNT(1)
INTO l_valid
FROM mtl_onhand_quantities_detail
WHERE organization_id = l_organization_id
AND lpn_id = p_lpn_id;
SELECT COUNT(1)
INTO l_valid
FROM mtl_txn_request_lines
WHERE organization_id = l_organization_id
AND lpn_id = p_lpn_id
AND line_status = 7;
SELECT COUNT(1)
INTO l_valid
FROM mtl_reservations
WHERE organization_id = l_organization_id
AND lpn_id = p_lpn_id;
SELECT COUNT(1)
INTO l_valid
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND current_organization_id = l_organization_id
AND current_status <> 4;
SELECT COUNT(1)
INTO l_valid
FROM rcv_transactions_interface
WHERE lpn_id = p_lpn_id
OR transfer_lpn_id = p_lpn_id;
SELECT COUNT(1)
INTO l_valid
FROM wsh_delivery_details
WHERE organization_id = l_organization_id
AND lpn_id = p_lpn_id
AND released_status = 'X';
IS SELECT lpn_id
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_lpn_id;
IS SELECT lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id <> p_lpn_id
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
IS SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT count(1)
INTO l_valid
FROM mtl_parameters
WHERE organization_id = p_new_org_id
AND wms_enabled_flag = 'Y';
SELECT organization_id
, lpn_context
, ROWID
, license_plate_number
, parent_lpn_id
, outermost_lpn_id
, inventory_item_id
INTO l_organization_id
, l_lpn_context
, l_wlpn_row_id
, l_lpn_name
, l_parent_lpn_id
, l_outermost_lpn_id
, l_container_item_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT license_plate_number
INTO l_outermost_lpn_name
FROM wms_license_plate_numbers
WHERE lpn_id = l_outermost_lpn_id;
INSERT INTO wms_lpn_histories (
LPN_HISTORY_ID -- Sequence
, SECONDARY_QUANTITY -- wlc.parent_lpn_id
, SECONDARY_UOM_CODE --wlc.secondary_uom_code
, CALLER
, SOURCE_TRANSACTION_ID
, TO_SERIAL_NUMBER
, SOURCE_TYPE_ID -- wlpn.source_type_id
, SOURCE_HEADER_ID -- wlpn.source_header_id
, SOURCE_LINE_ID -- wlpn.source_line_id
, SOURCE_LINE_DETAIL_ID --wlpn.source_line_detail_id
, SOURCE_NAME --wlpn.source_name
, PARENT_LPN_ID -- wlc.parent_lpn_id
, PARENT_LICENSE_PLATE_NUMBER --wlpn.license_plate_number
, LPN_ID
, LICENSE_PLATE_NUMBER
, INVENTORY_ITEM_ID -- wlc.inventory_item_id
, ITEM_DESCRIPTION -- wlc.item_description
, REVISION -- wlc.revision
, LOT_NUMBER -- wlc.lot_number
, SERIAL_NUMBER -- msn.serial_number
, QUANTITY -- Need to derive
, UOM_CODE -- wlc.uom_code
, ORGANIZATION_ID -- wlpn.organization_id
, SUBINVENTORY_CODE -- wlpn.subinventory_code
, LOCATOR_ID -- wlpn.locator_id
, STATUS_ID -- wlpn.status_id
--, LPN_STATE -- wlpn.lpn_state --Commented for Bug#7828840
, SEALED_STATUS -- wlpn.sealed_status
, OPERATION_MODE -- Need to derive PACK or UNPACK
, LAST_UPDATE_DATE -- SYSDATE
, LAST_UPDATED_BY -- FND_GLOBAL.USER_ID
, CREATION_DATE -- SYSDATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE -- SYSDATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, COST_GROUP_ID --wlc.cost_group_id
, LPN_CONTEXT --wlpn.lpn_context
, LPN_REUSABILITY --wlpn.lpn_reusability
, OUTERMOST_LPN_ID --wlpn.outermost_lpn_id
, OUTERMOST_LICENSE_PLATE_NUMBER -- Need to derive
, HOMOGENEOUS_CONTAINER --wlpn.homogeneous_container
) SELECT
wms_lpn_histories_s.NEXTVAL -- LPN_HISTORY_ID
, wlc.quantity -- SECONDARY_QUANTITY
, wlc.secondary_uom_code -- SECONDARY_UOM_CODE
, NULL -- CALLER
, NULL -- SOURCE_TRANSACTION_ID
, msn.serial_number -- TO_SERIAL_NUMBER
, wlpn1.source_type_id -- SOURCE_TYPE_ID
, wlpn1.source_header_id -- SOURCE_HEADER_ID
, wlpn1.source_line_id -- SOURCE_LINE_ID
, wlpn1.source_line_detail_id -- SOURCE_LINE_DETAIL_ID
, wlpn1.source_name -- SOURCE_NAME
, wlpn1.lpn_id -- PARENT_LPN_ID
, wlpn1.license_plate_number -- PARENT_LICENSE_PLATE_NUMBER
, wlpn2.lpn_id -- LPN_ID
, wlpn2.license_plate_number -- LICENSE_PLATE_NUMBER
, wlc.inventory_item_id -- INVENTORY_ITEM_ID
, wlc.item_description -- ITEM_DESCRIPTION
, wlc.revision -- REVISION
, wlc.lot_number -- LOT_NUMBER
, msn.serial_number -- SERIAL_NUMBER
, NVL2(msn.serial_number, 1,wlc.quantity) --QUANTITY
, wlc.uom_code -- UOM_CODE
, wlpn1.organization_id -- ORGANIZATION_ID
, wlpn1.subinventory_code -- SUBINVENTORY_CODE
, wlpn1.locator_id -- LOCATOR_ID
, wlpn1.status_id -- STATUS_ID
--, wlpn1.lpn_state -- LPN_STATE --Commented for Bug#7828840
, wlpn1.sealed_status -- SEALED_STATUS
, 2 -- OPERATION_MODE
, SYSDATE -- LAST_UPDATE_DATE
, FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
, SYSDATE -- CREATION_DATE
, FND_GLOBAL.USER_ID -- CREATED_BY
, FND_GLOBAL.USER_ID -- LAST_UPDATE_LOGIN
, NULL -- REQUEST_ID
, NULL -- PROGRAM_APPLICATION_ID
, NULL -- PROGRAM_ID
, NULL -- PROGRAM_UPDATE_DATE
, wlpn1.attribute_category
, wlpn1.ATTRIBUTE1
, wlpn1.ATTRIBUTE2
, wlpn1.ATTRIBUTE3
, wlpn1.ATTRIBUTE4
, wlpn1.ATTRIBUTE5
, wlpn1.ATTRIBUTE6
, wlpn1.ATTRIBUTE7
, wlpn1.ATTRIBUTE8
, wlpn1.ATTRIBUTE9
, wlpn1.ATTRIBUTE10
, wlpn1.ATTRIBUTE11
, wlpn1.ATTRIBUTE12
, wlpn1.ATTRIBUTE13
, wlpn1.ATTRIBUTE14
, wlpn1.ATTRIBUTE15
, wlc.cost_group_id -- COST_GROUP_ID
, wlpn1.lpn_context -- LPN_CONTEXT
, wlpn1.lpn_reusability -- LPN_REUSABILITY
, wlpn1.lpn_id -- OUTERMOST_LPN_ID
, l_outermost_lpn_name -- OUTERMOST_LICENSE_PLATE_NUMBER
, wlpn1.homogeneous_container -- HOMOGENEOUS_CONTAINER
FROM wms_license_plate_numbers wlpn1
, wms_license_plate_numbers wlpn2
, wms_lpn_contents wlc
, mtl_serial_numbers msn
WHERE wlpn1.lpn_id = wlc.parent_lpn_id(+)
AND wlpn1.lpn_id = msn.lpn_id(+)
AND wlpn1.lpn_id = wlpn2.parent_lpn_id(+)
AND wlpn1.lpn_id = p_lpn_id;
mdebug('Inserted wms_lpn_histories rows for lpn : '|| p_lpn_id);
DELETE FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id;
UPDATE wms_license_plate_numbers
SET ATTRIBUTE1 = NULL
, ATTRIBUTE2 = NULL
, ATTRIBUTE3 = NULL
, ATTRIBUTE4 = NULL
, ATTRIBUTE5 = NULL
, ATTRIBUTE6 = NULL
, ATTRIBUTE7 = NULL
, ATTRIBUTE8 = NULL
, ATTRIBUTE9 = NULL
, ATTRIBUTE10 = NULL
, ATTRIBUTE11 = NULL
, ATTRIBUTE12 = NULL
, ATTRIBUTE13 = NULL
, ATTRIBUTE14 = NULL
, ATTRIBUTE15 = NULL
, ATTRIBUTE_CATEGORY = NULL
WHERE ROWID = l_wlpn_row_id;
UPDATE wms_license_plate_numbers
SET parent_lpn_id = NULL
, outermost_lpn_id = immediate_child_wlpn_rec.lpn_id
WHERE lpn_id = immediate_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET outermost_lpn_id = immediate_child_wlpn_rec.lpn_id
WHERE lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET parent_lpn_id = l_parent_lpn_id
WHERE lpn_id = immediate_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 5
, subinventory_code = NULL
, locator_id = NULL
, parent_lpn_id = NULL
, outermost_lpn_id = p_lpn_id
, organization_id = p_new_org_id
WHERE ROWID = l_wlpn_row_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 5
, subinventory_code = NULL
, locator_id = NULL
, parent_lpn_id = NULL
, outermost_lpn_id = p_lpn_id
WHERE ROWID = l_wlpn_row_id;
UPDATE mtl_serial_numbers
SET lpn_id = NULL
WHERE lpn_id = p_lpn_id
AND current_organization_id = l_organization_id;
UPDATE wms_license_plate_numbers
SET content_volume = NULL
, content_volume_uom_code = NULL
WHERE ROWID = l_wlpn_row_id;
UPDATE wms_license_plate_numbers
SET inventory_item_id = NULL
, gross_weight = NULL
, gross_weight_uom_code = NULL
, tare_weight = NULL
, tare_weight_uom_code = NULL
, container_volume = NULL
, container_volume_uom = NULL
WHERE ROWID = l_wlpn_row_id;
UPDATE wms_license_plate_numbers
SET gross_weight = inv_cache.item_rec.unit_weight
, gross_weight_uom_code = inv_cache.item_rec.weight_uom_code
, tare_weight = inv_cache.item_rec.unit_weight
, tare_weight_uom_code = inv_cache.item_rec.weight_uom_code
, container_volume = inv_cache.item_rec.unit_volume
, container_volume_uom = inv_cache.item_rec.volume_uom_code
WHERE ROWID = l_wlpn_row_id;
SELECT license_plate_number
INTO l_outermost_lpn_name
FROM wms_license_plate_numbers
WHERE lpn_id = (SELECT outermost_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = all_child_wlpn_rec.lpn_id);
INSERT INTO wms_lpn_histories (
LPN_HISTORY_ID -- Sequence
, SECONDARY_QUANTITY -- wlc.parent_lpn_id
, SECONDARY_UOM_CODE --wlc.secondary_uom_code
, CALLER
, SOURCE_TRANSACTION_ID
, TO_SERIAL_NUMBER
, SOURCE_TYPE_ID -- wlpn.source_type_id
, SOURCE_HEADER_ID -- wlpn.source_header_id
, SOURCE_LINE_ID -- wlpn.source_line_id
, SOURCE_LINE_DETAIL_ID --wlpn.source_line_detail_id
, SOURCE_NAME --wlpn.source_name
, PARENT_LPN_ID -- wlc.parent_lpn_id
, PARENT_LICENSE_PLATE_NUMBER --wlpn.license_plate_number
, LPN_ID
, LICENSE_PLATE_NUMBER
, INVENTORY_ITEM_ID -- wlc.inventory_item_id
, ITEM_DESCRIPTION -- wlc.item_description
, REVISION -- wlc.revision
, LOT_NUMBER -- wlc.lot_number
, SERIAL_NUMBER -- msn.serial_number
, QUANTITY -- Need to derive
, UOM_CODE -- wlc.uom_code
, ORGANIZATION_ID -- wlpn.organization_id
, SUBINVENTORY_CODE -- wlpn.subinventory_code
, LOCATOR_ID -- wlpn.locator_id
, STATUS_ID -- wlpn.status_id
--, LPN_STATE -- wlpn.lpn_state --Commented for Bug#7828840
, SEALED_STATUS -- wlpn.sealed_status
, OPERATION_MODE -- Need to derive PACK or UNPACK
, LAST_UPDATE_DATE -- SYSDATE
, LAST_UPDATED_BY -- FND_GLOBAL.USER_ID
, CREATION_DATE -- SYSDATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE -- SYSDATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, COST_GROUP_ID --wlc.cost_group_id
, LPN_CONTEXT --wlpn.lpn_context
, LPN_REUSABILITY --wlpn.lpn_reusability
, OUTERMOST_LPN_ID --wlpn.outermost_lpn_id
, OUTERMOST_LICENSE_PLATE_NUMBER -- Need to derive
, HOMOGENEOUS_CONTAINER --wlpn.homogeneous_container
) SELECT
wms_lpn_histories_s.NEXTVAL -- LPN_HISTORY_ID
, wlc.quantity -- SECONDARY_QUANTITY
, wlc.secondary_uom_code -- SECONDARY_UOM_CODE
, NULL -- CALLER
, NULL -- SOURCE_TRANSACTION_ID
, msn.serial_number -- TO_SERIAL_NUMBER
, wlpn1.source_type_id -- SOURCE_TYPE_ID
, wlpn1.source_header_id -- SOURCE_HEADER_ID
, wlpn1.source_line_id -- SOURCE_LINE_ID
, wlpn1.source_line_detail_id -- SOURCE_LINE_DETAIL_ID
, wlpn1.source_name -- SOURCE_NAME
, wlpn1.lpn_id -- PARENT_LPN_ID
, wlpn1.license_plate_number -- PARENT_LICENSE_PLATE_NUMBER
, wlpn2.lpn_id -- LPN_ID
, wlpn2.license_plate_number -- LICENSE_PLATE_NUMBER
, wlc.inventory_item_id -- INVENTORY_ITEM_ID
, wlc.item_description -- ITEM_DESCRIPTION
, wlc.revision -- REVISION
, wlc.lot_number -- LOT_NUMBER
, msn.serial_number -- SERIAL_NUMBER
, NVL2(msn.serial_number, 1,wlc.quantity) --QUANTITY
, wlc.uom_code -- UOM_CODE
, wlpn1.organization_id -- ORGANIZATION_ID
, wlpn1.subinventory_code -- SUBINVENTORY_CODE
, wlpn1.locator_id -- LOCATOR_ID
, wlpn1.status_id -- STATUS_ID
--, wlpn1.lpn_state -- LPN_STATE --Commented for Bug#7828840
, wlpn1.sealed_status -- SEALED_STATUS
, 2 -- OPERATION_MODE
, SYSDATE -- LAST_UPDATE_DATE
, FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
, SYSDATE -- CREATION_DATE
, FND_GLOBAL.USER_ID -- CREATED_BY
, FND_GLOBAL.USER_ID -- LAST_UPDATE_LOGIN
, NULL -- REQUEST_ID
, NULL -- PROGRAM_APPLICATION_ID
, NULL -- PROGRAM_ID
, NULL -- PROGRAM_UPDATE_DATE
, wlpn1.attribute_category
, wlpn1.ATTRIBUTE1
, wlpn1.ATTRIBUTE2
, wlpn1.ATTRIBUTE3
, wlpn1.ATTRIBUTE4
, wlpn1.ATTRIBUTE5
, wlpn1.ATTRIBUTE6
, wlpn1.ATTRIBUTE7
, wlpn1.ATTRIBUTE8
, wlpn1.ATTRIBUTE9
, wlpn1.ATTRIBUTE10
, wlpn1.ATTRIBUTE11
, wlpn1.ATTRIBUTE12
, wlpn1.ATTRIBUTE13
, wlpn1.ATTRIBUTE14
, wlpn1.ATTRIBUTE15
, wlc.cost_group_id -- COST_GROUP_ID
, wlpn1.lpn_context -- LPN_CONTEXT
, wlpn1.lpn_reusability -- LPN_REUSABILITY
, wlpn1.lpn_id -- OUTERMOST_LPN_ID
, l_outermost_lpn_name -- OUTERMOST_LICENSE_PLATE_NUMBER
, wlpn1.homogeneous_container -- HOMOGENEOUS_CONTAINER
FROM wms_license_plate_numbers wlpn1
, wms_license_plate_numbers wlpn2
, wms_lpn_contents wlc
, mtl_serial_numbers msn
WHERE wlpn1.lpn_id = wlc.parent_lpn_id(+)
AND wlpn1.lpn_id = msn.lpn_id(+)
AND wlpn1.lpn_id = wlpn2.parent_lpn_id(+)
AND wlpn1.lpn_id = all_child_wlpn_rec.lpn_id;
DELETE FROM wms_lpn_contents
WHERE parent_lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET ATTRIBUTE1 = NULL
, ATTRIBUTE2 = NULL
, ATTRIBUTE3 = NULL
, ATTRIBUTE4 = NULL
, ATTRIBUTE5 = NULL
, ATTRIBUTE6 = NULL
, ATTRIBUTE7 = NULL
, ATTRIBUTE8 = NULL
, ATTRIBUTE9 = NULL
, ATTRIBUTE10 = NULL
, ATTRIBUTE11 = NULL
, ATTRIBUTE12 = NULL
, ATTRIBUTE13 = NULL
, ATTRIBUTE14 = NULL
, ATTRIBUTE15 = NULL
, ATTRIBUTE_CATEGORY = NULL
WHERE lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 5
, subinventory_code = NULL
, locator_id = NULL
, parent_lpn_id = NULL
, outermost_lpn_id = all_child_wlpn_rec.lpn_id
, organization_id = p_new_org_id
WHERE lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = 5
, subinventory_code = NULL
, locator_id = NULL
, parent_lpn_id = NULL
, outermost_lpn_id = all_child_wlpn_rec.lpn_id
WHERE lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE mtl_serial_numbers
SET lpn_id = NULL
WHERE lpn_id = all_child_wlpn_rec.lpn_id
AND current_organization_id = l_organization_id;
UPDATE wms_license_plate_numbers
SET content_volume = NULL
, content_volume_uom_code = NULL
WHERE lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET inventory_item_id = NULL
, gross_weight = NULL
, gross_weight_uom_code = NULL
, tare_weight = NULL
, tare_weight_uom_code = NULL
, container_volume = NULL
, container_volume_uom = NULL
WHERE lpn_id = all_child_wlpn_rec.lpn_id;
UPDATE wms_license_plate_numbers
SET gross_weight = inv_cache.item_rec.unit_weight
, gross_weight_uom_code = inv_cache.item_rec.weight_uom_code
, tare_weight = inv_cache.item_rec.unit_weight
, tare_weight_uom_code = inv_cache.item_rec.weight_uom_code
, container_volume = inv_cache.item_rec.unit_volume
, container_volume_uom = inv_cache.item_rec.volume_uom_code
WHERE lpn_id = all_child_wlpn_rec.lpn_id;