[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(tracking_quantity_ind, G_TRACK_PRIMARY),
NVL(ont_pricing_qty_source, G_PRICE_PRIMARY),
secondary_default_ind,
secondary_uom_code,
dual_uom_deviation_high,
dual_uom_deviation_low
INTO x_tracking_quantity_ind,
x_ont_pricing_qty_source,
x_secondary_default_ind,
x_secondary_uom_code,
x_uom_deviation_high,
x_uom_deviation_low
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT NVL(ont_pricing_qty_source, G_PRICE_PRIMARY)
INTO l_ont_pricing_qty_source
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT ont_pricing_qty_source,
secondary_default_ind,
primary_uom_code,
secondary_uom_code
INTO x_ont_pricing_qty_source,
l_default_ind,
l_uom_code,
x_secondary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
, 5 -- Updated precision as 5 for Bug 15877579
, p_quantity
, l_uom_code
, l_secondary_uom_code
, NULL
, NULL );
PROCEDURE Update_Shipping_Secondary_Qty (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_delivery_detail_id IN NUMBER
, p_secondary_quantity IN NUMBER
, p_secondary_uom_code IN VARCHAR2 := NULL
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Shipping_Secondary_Qty';
SAVEPOINT UPDATE_SHIPPING_SECONDARY_QTY;
SELECT organization_id, inventory_item_id, picked_quantity, requested_quantity_uom
INTO l_organization_id, l_inventory_item_id, l_picked_quantity, l_requested_quantity_uom
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
l_shipping_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
p_api_version_number => 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_shipping_attr
, p_IN_rec => l_shipping_in_rec
, x_OUT_rec => l_shipping_out_rec );
print_debug('Error calling Create_Update_Delivery_Detail: '||x_msg_data, 9);
ROLLBACK TO UPDATE_SHIPPING_SECONDARY_QTY;
END Update_Shipping_Secondary_Qty;
PROCEDURE Update_Parent_Delivery_Sec_Qty (
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_organization_id IN NUMBER
, p_parent_del_det_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2 := NULL
, p_lot_number IN VARCHAR2 := NULL
, p_quantity IN NUMBER
, p_uom_code IN VARCHAR2
, p_secondary_quantity IN NUMBER
, p_secondary_uom_code IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Parent_Delivery_Sec_Qty';
SELECT wdd.delivery_detail_id, wdd.picked_quantity, wdd.requested_quantity_uom,
wdd.picked_quantity2, wdd.requested_quantity_uom2
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
WHERE wda.parent_delivery_detail_id = p_parent_del_det_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.organization_id = p_organization_id
AND wdd.inventory_item_id = p_inventory_item_id
AND NVL(wdd.revision, '@') = NVL(p_revision, '@')
AND NVL(wdd.lot_number, '@') = NVL(p_lot_number, '@');
SAVEPOINT UPDATE_PARENT_DELIVERY_SEC_QTY;
l_shipping_in_rec.action_code := 'UPDATE';
print_debug('Calling Create_Update_Delivery_Detail count='||l_shipping_attr.count, 9);
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
p_api_version_number => 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_shipping_attr
, p_IN_rec => l_shipping_in_rec
, x_OUT_rec => l_shipping_out_rec );
print_debug('Error calling Create_Update_Delivery_Detail: '||x_msg_data, 9);
ROLLBACK TO UPDATE_PARENT_DELIVERY_SEC_QTY;
END Update_Parent_Delivery_Sec_Qty;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_temp_id
FROM dual;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_hdr_id
FROM dual;
SELECT lpn_context
INTO l_orig_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT cat_wt_account
INTO l_adj_account_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
INSERT INTO mtl_material_transactions_temp(
transaction_header_id
, transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, transaction_uom
, primary_quantity
, item_primary_uom_code
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_date
, acct_period_id
, distribution_account_id
, item_location_control_code
, item_revision_qty_control_code
, item_lot_control_code
, item_serial_control_code
, posting_flag
, process_flag
, lpn_id
, transfer_lpn_id
, secondary_uom_code
, secondary_transaction_quantity
)
VALUES ( l_txn_hdr_id -- TRANSACTION_HEADER_ID
, l_txn_temp_id -- TRANSACTION_TEMP_ID
, l_txn_date -- LAST_UPDATE_DATE
, fnd_global.user_id -- LAST_UPDATED_BY
, l_txn_date -- CREATION_DATE
, fnd_global.user_id -- CREATED_BY
, fnd_global.user_id -- LAST_UPDATE_LOGIN
, p_inventory_item_id -- INVENTORY_ITEM_ID
, p_revision -- REVISION
, p_organization_id -- ORGANIZATION_ID
, p_subinv_code -- SUBINVENTORY_CODE
, p_locator_id -- LOCATOR_ID
, p_pri_qty -- TRANSACTION_QUANTITY
, p_pri_uom_code -- TRANSACTION_UOM
, p_pri_qty -- PRIMARY_QUANTITY
, p_pri_uom_code -- ITEM_PRIMARY_UOM_CODE
, l_txn_type_id -- TRANSACTION_TYPE_ID
, l_txn_action_id -- TRANSACTION_ACTION_ID
, l_txn_src_typ_id -- TRANSACTION_SOURCE_TYPE_ID
, l_adj_account_id -- TRANSACTION_SOURCE_ID
, l_txn_date -- TRANSACTION_DATE
, l_acct_period_id -- ACCT_PERIOD_ID
, l_adj_account_id -- DISTRIBUTION_ACCOUNT_ID
, l_loc_ctrl_code -- ITEM_LOCATION_CONTROL_CODE
, l_rev_ctrl_code -- ITEM_REVISION_QTY_CONTROL_CODE
, l_lot_ctrl_code -- ITEM_LOT_CONTROL_CODE
, l_srl_ctrl_code -- ITEM_SERIAL_CONTROL_CODE
, 'Y' -- POSTING_FLAG
, 'Y' -- PROCESS_FLAG
, DECODE ( p_ctwt_adj_type
, 'ISSUE', p_lpn_id
, 'RECEIPT', NULL
, NULL
) -- LPN_ID
, DECODE ( p_ctwt_adj_type
, 'ISSUE', NULL
, 'RECEIPT', p_lpn_id
, NULL
) -- TRANSFER_LPN_ID
, p_sec_uom_code -- SECONDARY_UOM_CODE
, 0 -- SECONDARY_TRANSACTION_QUANTITY
);
INSERT INTO mtl_transaction_lots_temp(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, transaction_quantity
, primary_quantity
, lot_number
, secondary_quantity
)
VALUES ( l_txn_temp_id -- TRANSACTION_TEMP_ID
, l_txn_date -- LAST_UPDATE_DATE
, fnd_global.user_id -- LAST_UPDATED_BY
, l_txn_date -- CREATION_DATE
, fnd_global.user_id -- CREATED_BY
, fnd_global.user_id -- LAST_UPDATE_LOGIN
, p_pri_qty -- TRANSACTION_QUANTITY
, p_pri_qty -- PRIMARY_QUANTITY
, p_lot_number -- LOT_NUMBER
, 0 -- SECONDARY_QUANTITY
);
SELECT lpn_context
INTO l_new_lpn_ctx
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
UPDATE wms_license_plate_numbers
SET lpn_context = l_orig_lpn_ctx
WHERE lpn_id = p_lpn_id;
PROCEDURE update_lpn_primary_quantity (
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_record_source IN VARCHAR2
, p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2 := NULL
, p_lot_number IN VARCHAR2 := NULL
, p_quantity IN NUMBER
, p_uom_code IN VARCHAR2
, p_secondary_quantity IN NUMBER
, p_secondary_uom_code IN VARCHAR2
, p_max_pri_residual IN NUMBER
, p_ccnt_sec_residual IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_lpn_primary_quantity';
SELECT SUM(wlc.primary_quantity)
, SUM(wlc.secondary_quantity)
FROM wms_license_plate_numbers wlpn
, wms_lpn_contents wlc
WHERE wlpn.lpn_id = p_lpn_id
AND wlpn.lpn_context = 1
AND wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.inventory_item_id = p_inventory_item_id
AND NVL(wlc.revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(wlc.lot_number,'@@@') = NVL(p_lot_number,'@@@');
SELECT reservation_id
FROM mtl_reservations
WHERE demand_source_type_id IN (2,8)
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'N';
SELECT SUM(wlc.primary_quantity)
, SUM(wlc.secondary_quantity)
FROM wms_license_plate_numbers wlpn
, wms_lpn_contents wlc
WHERE wlpn.lpn_id = p_lpn_id
AND wlpn.lpn_context = 11
AND wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.inventory_item_id = p_inventory_item_id
AND NVL(wlc.revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(wlc.lot_number,'@@@') = NVL(p_lot_number,'@@@');
SELECT wdd2.delivery_detail_id
, wdd2.source_line_id
, wdd2.picked_quantity
, wdd2.shipped_quantity
, DECODE( wdd2.requested_quantity_uom
, msi.primary_uom_code, wdd2.picked_quantity
, inv_convert.inv_um_convert( wdd2.inventory_item_id
, wdd2.lot_number
, wdd2.organization_id
, NULL
, wdd2.picked_quantity
, wdd2.requested_quantity_uom
, msi.primary_uom_code
, NULL
, NULL
)
) primary_picked_qty
, wdd2.requested_quantity_uom
FROM wms_license_plate_numbers wlpn
, wsh_delivery_details wdd1
, wsh_delivery_assignments_v wda
, wsh_delivery_details wdd2
, mtl_system_items msi
WHERE wlpn.lpn_id = p_lpn_id
AND wlpn.lpn_context = 11
AND wdd1.organization_id = p_organization_id
AND wdd1.lpn_id = p_lpn_id
AND wdd1.released_status = 'X'
AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
AND wdd2.delivery_detail_id = wda.delivery_detail_id
AND wdd2.organization_id = p_organization_id
AND wdd2.inventory_item_id = p_inventory_item_id
AND NVL(wdd2.revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(wdd2.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND msi.organization_id = wdd2.organization_id
AND msi.inventory_item_id = wdd2.inventory_item_id;
SELECT reservation_id
FROM mtl_reservations
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y';
SELECT SUM(mtlt.primary_quantity)
, SUM(mtlt.secondary_quantity)
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
WHERE (mmtt.content_lpn_id = p_lpn_id
OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.parent_line_id IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@')
AND mtlt.lot_number = p_lot_number;
SELECT SUM(mmtt.primary_quantity)
, SUM(mmtt.secondary_transaction_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE (mmtt.content_lpn_id = p_lpn_id
OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.parent_line_id IS NULL
AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@');
SELECT mtlt.transaction_temp_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.lpn_id
, mmtt.content_lpn_id
, mmtt.reservation_id
, mmtt.move_order_line_id
, mtlt.primary_quantity
, mtlt.secondary_quantity
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
WHERE (mmtt.content_lpn_id = p_lpn_id
OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.parent_line_id IS NULL
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@')
AND mtlt.lot_number = p_lot_number;
SELECT mmtt.transaction_temp_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.lpn_id
, mmtt.content_lpn_id
, mmtt.reservation_id
, mmtt.move_order_line_id
, mmtt.primary_quantity
, mmtt.secondary_transaction_quantity
FROM mtl_material_transactions_temp mmtt
WHERE (mmtt.content_lpn_id = p_lpn_id
OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.parent_line_id IS NULL
AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@');
SELECT reservation_quantity
, reservation_uom_code
, wms_catch_weight_pvt.get_uom_class(reservation_uom_code) rsv_uom_class
, primary_reservation_quantity
, detailed_quantity
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code) sec_uom_class
FROM mtl_reservations
WHERE reservation_id = p_rsv_id
FOR UPDATE WAIT 5;
SELECT primary_reservation_quantity
, detailed_quantity
FROM mtl_reservations
WHERE reservation_id = p_rsv_id
FOR UPDATE WAIT 5;
SELECT NVL(SUM(mtlt.primary_quantity),0)
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
, mtl_lot_numbers mln
, mtl_secondary_inventories msi
, mtl_item_locations mil
WHERE mmtt.reservation_id = p_rsv_id
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.lot_number = mtlt.lot_number
AND NVL(mln.reservable_type,1) = 1
AND NVL(mln.expiration_date,SYSDATE + 1) > SYSDATE
AND msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND NVL(msi.reservable_type,1) = 1
AND mil.organization_id = mmtt.organization_id
AND mil.inventory_location_id = mmtt.locator_id
AND NVL(mil.reservable_type,1) = 1;
SELECT NVL(SUM(mmtt.primary_quantity),0)
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
, mtl_item_locations mil
WHERE mmtt.reservation_id = p_rsv_id
AND msi.organization_id = mmtt.organization_id
AND msi.secondary_inventory_name = mmtt.subinventory_code
AND NVL(msi.reservable_type,1) = 1
AND mil.organization_id = mmtt.organization_id
AND mil.inventory_location_id = mmtt.locator_id
AND NVL(mil.reservable_type,1) = 1;
SELECT NVL(SUM(mmtt.primary_quantity),0)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.reservation_id = p_rsv_id
AND EXISTS ( SELECT 'x'
FROM mtl_onhand_quantities_detail moqd
, mtl_material_statuses mms
WHERE moqd.organization_id = mmtt.organization_id
AND moqd.subinventory_code = mmtt.subinventory_code
AND moqd.locator_id = mmtt.locator_id
AND moqd.inventory_item_id = mmtt.inventory_item_id
AND NVL(moqd.revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(moqd.lpn_id, -9999) = NVL(mmtt.lpn_id, -9999)
AND moqd.status_id = mms.status_id
AND NVL(mms.reservable_type, 1) = 1
);
SAVEPOINT update_lpn_primary_qty_sp;
t_wdd_id.DELETE;
g_src_line_id.DELETE;
t_temp_id.DELETE;
t_subinv.DELETE;
t_loc_id.DELETE;
t_lpn_id.DELETE;
t_clpn_id.DELETE;
t_rsv_id.DELETE;
t_mol_id.DELETE;
t_pck_qty.DELETE;
t_shp_qty.DELETE;
t_pri_qty.DELETE;
t_req_uom.DELETE;
t_sec_qty.DELETE;
t_proc_rsvs.DELETE;
l_shipping_attr.DELETE;
SELECT wlpn.subinventory_code
, sub.reservable_type
, wlpn.locator_id
INTO l_subinv_code
, l_sub_reservable
, l_locator_id
FROM wms_license_plate_numbers wlpn
, mtl_secondary_inventories sub
WHERE wlpn.lpn_id = p_lpn_id
AND sub.organization_id = wlpn.organization_id
AND sub.secondary_inventory_name = wlpn.subinventory_code;
SELECT NVL(SUM(primary_reservation_quantity),0)
INTO l_lpn_rsv_pri_qty
FROM mtl_reservations
WHERE demand_source_type_id IN (2,8)
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'N';
print_debug('Calling delete_reservation for rsv ID: ' || t_rsv_id(i), 4);
inv_reservation_pvt.delete_reservation (
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec
, p_original_serial_number => l_original_serial_number
, p_validation_flag => NULL
);
print_debug('Error status from inv_reservation_pvt.delete_reservation: '
|| l_api_return_status, 4);
UPDATE mtl_reservations
SET primary_reservation_quantity =
(primary_reservation_quantity -
ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
)
, reservation_quantity =
(reservation_quantity -
DECODE( reservation_uom_code
, secondary_uom_code, 0
, DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
, inv_convert.inv_um_convert(
inventory_item_id
, lot_number
, organization_id
, NULL
, ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
, primary_uom_code
, reservation_uom_code
, NULL
, NULL
)
)
)
)
WHERE demand_source_type_id IN (2,8)
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'N'
RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
t_rsv_id.DELETE;
UPDATE mtl_reservations
SET primary_reservation_quantity =
(primary_reservation_quantity +
ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
)
, reservation_quantity =
(reservation_quantity +
DECODE( reservation_uom_code
, secondary_uom_code, 0
, DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
, inv_convert.inv_um_convert(
inventory_item_id
, lot_number
, organization_id
, NULL
, ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
, primary_uom_code
, reservation_uom_code
, NULL
, NULL
)
)
)
)
WHERE demand_source_type_id IN (2,8)
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'N'
RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
t_rsv_id.DELETE;
SELECT wlpn.subinventory_code
, sub.reservable_type
, wlpn.locator_id
INTO l_subinv_code
, l_sub_reservable
, l_locator_id
FROM wms_license_plate_numbers wlpn
, mtl_secondary_inventories sub
WHERE wlpn.lpn_id = p_lpn_id
AND sub.organization_id = wlpn.organization_id
AND sub.secondary_inventory_name = wlpn.subinventory_code;
l_shipping_in_rec.action_code := 'UPDATE';
print_debug('Calling create_update_delivery_detail count='||l_shipping_attr.count,4);
WSH_INTERFACE_EXT_GRP.create_update_delivery_detail(
p_api_version_number => 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_shipping_attr
, p_in_rec => l_shipping_in_rec
, x_out_rec => l_shipping_out_rec
);
print_debug('Error calling create_update_delivery_detail: '||x_msg_data, 4);
SELECT NVL(SUM(primary_reservation_quantity),0)
INTO l_lpn_rsv_pri_qty
FROM mtl_reservations
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y';
print_debug('Calling delete_reservation for rsv ID: ' || t_rsv_id(i), 4);
inv_reservation_pvt.delete_reservation (
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec
, p_original_serial_number => l_original_serial_number
, p_validation_flag => NULL
);
print_debug('Error status from inv_reservation_pvt.delete_reservation: '
|| l_api_return_status, 4);
UPDATE mtl_reservations
SET primary_reservation_quantity =
(primary_reservation_quantity -
ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
)
, reservation_quantity =
(reservation_quantity -
DECODE( reservation_uom_code
, secondary_uom_code, 0
, DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
, inv_convert.inv_um_convert(
inventory_item_id
, lot_number
, organization_id
, NULL
, ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
, primary_uom_code
, reservation_uom_code
, NULL
, NULL
)
)
)
)
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y'
RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
t_rsv_id.DELETE;
UPDATE mtl_reservations
SET primary_reservation_quantity =
(primary_reservation_quantity +
ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
)
, reservation_quantity =
(reservation_quantity +
DECODE( reservation_uom_code
, secondary_uom_code, 0
, DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
, inv_convert.inv_um_convert(
inventory_item_id
, lot_number
, organization_id
, NULL
, ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
, primary_uom_code
, reservation_uom_code
, NULL
, NULL
)
)
)
)
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y'
RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
t_rsv_id.DELETE;
SELECT NVL(SUM(primary_reservation_quantity),0)
INTO l_nonlpn_rsv_pri_qty
FROM mtl_reservations
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND organization_id = p_organization_id
AND subinventory_code = l_subinv_code
AND locator_id = l_locator_id
AND lpn_id IS NULL
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y';
UPDATE mtl_reservations
SET primary_reservation_quantity =
(primary_reservation_quantity -
ROUND(((primary_reservation_quantity * l_rem_pri_qty_decr)/l_nonlpn_rsv_pri_qty),5)
)
, reservation_quantity =
(reservation_quantity -
DECODE( reservation_uom_code
, secondary_uom_code, 0
, DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
, inv_convert.inv_um_convert(
inventory_item_id
, lot_number
, organization_id
, NULL
, ROUND(((primary_reservation_quantity * l_rem_pri_qty_decr)/l_nonlpn_rsv_pri_qty),5)
, primary_uom_code
, reservation_uom_code
, NULL
, NULL
)
)
)
)
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND organization_id = p_organization_id
AND subinventory_code = l_subinv_code
AND locator_id = l_locator_id
AND lpn_id IS NULL
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y'
RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
t_rsv_id.DELETE;
UPDATE mtl_reservations
SET primary_reservation_quantity =
(primary_reservation_quantity +
ROUND(((primary_reservation_quantity * l_rem_pri_qty_incr)/l_nonlpn_rsv_pri_qty),5)
)
, reservation_quantity =
(reservation_quantity +
DECODE( reservation_uom_code
, secondary_uom_code, 0
, DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
, wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
, inv_convert.inv_um_convert(
inventory_item_id
, lot_number
, organization_id
, NULL
, ROUND(((primary_reservation_quantity * l_rem_pri_qty_incr)/l_nonlpn_rsv_pri_qty),5)
, primary_uom_code
, reservation_uom_code
, NULL
, NULL
)
)
)
)
WHERE demand_source_type_id IN (2,8)
AND demand_source_line_id IN
(SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
AND organization_id = p_organization_id
AND subinventory_code = l_subinv_code
AND locator_id = l_locator_id
AND lpn_id IS NULL
AND inventory_item_id = p_inventory_item_id
AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
AND NVL(staged_flag,'N') = 'Y'
RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
t_rsv_id.DELETE;
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity - l_sku_pri_decr
WHERE transaction_temp_id = t_temp_id(i)
AND lot_number = p_lot_number;
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity - l_sku_pri_decr
WHERE transaction_temp_id = t_temp_id(i);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed -
DECODE( uom_code
, l_pr_uom_code, l_sku_pri_decr
, inv_convert.inv_um_convert(
p_inventory_item_id
, NULL
, l_sku_pri_decr
, l_pr_uom_code
, uom_code
, NULL
, NULL
)
)
WHERE line_id = t_mol_id(i);
UPDATE mtl_reservations
SET reservation_quantity = l_tot_rsv_qty
WHERE reservation_id = t_rsv_id(i);
UPDATE mtl_reservations
SET primary_reservation_quantity = l_tot_rsv_pri_qty
, detailed_quantity = l_tot_rsv_pri_qty
WHERE reservation_id = t_rsv_id(i);
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity + l_sku_pri_incr
WHERE transaction_temp_id = t_temp_id(j)
AND lot_number = p_lot_number;
print_debug('Updated MTLT: temp ID ' || t_temp_id(j)
|| ' lot ' || p_lot_number, 4);
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity + l_sku_pri_incr
WHERE transaction_temp_id = t_temp_id(j);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed +
DECODE( uom_code
, l_pr_uom_code, l_sku_pri_incr
, inv_convert.inv_um_convert(
p_inventory_item_id
, NULL
, l_sku_pri_incr
, l_pr_uom_code
, uom_code
, NULL
, NULL
)
)
WHERE line_id = t_mol_id(j);
print_debug('Updated MMTT: temp ID ' || t_temp_id(j) ||
', and MOL ID: ' || t_mol_id(j)
, 4);
t_proc_rsvs.DELETE;
UPDATE mtl_reservations
SET detailed_quantity = LEAST(primary_reservation_quantity,l_tot_rsv_pri_qty)
WHERE reservation_id = t_rsv_id(k);
ROLLBACK TO update_lpn_primary_qty_sp;
ROLLBACK TO update_lpn_primary_qty_sp;
END update_lpn_primary_quantity;
PROCEDURE Update_LPN_Secondary_Quantity (
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_record_source IN VARCHAR2
, p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2 := NULL
, p_lot_number IN VARCHAR2 := NULL
, p_quantity IN NUMBER
, p_uom_code IN VARCHAR2
, p_secondary_quantity IN NUMBER
, p_secondary_uom_code IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_LPN_Secondary_Quantity';
SELECT rowid, transaction_temp_id, transaction_quantity, transaction_uom
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '@') = NVL(p_revision, '@')
AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id;
SELECT rowid, transaction_quantity
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_trx_temp_id
AND lot_number = p_lot_number;
SAVEPOINT UPDATE_LPN_SECONDARY_QUANTITY;
SELECT delivery_detail_id
INTO l_del_det_id
FROM wsh_delivery_details
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id
AND released_status = 'X'; -- For LPN reuse ER : 6845650
Update_Parent_Delivery_Sec_Qty (
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_organization_id => p_organization_id
, p_parent_del_det_id => l_del_det_id
, p_inventory_item_id => p_inventory_item_id
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_quantity => p_quantity
, p_uom_code => p_uom_code
, p_secondary_quantity => p_secondary_quantity
, p_secondary_uom_code => p_secondary_uom_code );
UPDATE mtl_material_transactions_temp
SET secondary_transaction_quantity = NULL,
secondary_uom_code = NULL
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '@') = NVL(p_revision, '@')
AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id;
UPDATE mtl_material_transactions_temp
SET secondary_transaction_quantity = round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision),
secondary_uom_code = p_secondary_uom_code
WHERE rowid = mmtt_rec.rowid;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = NULL,
secondary_unit_of_measure = NULL
WHERE lot_number = p_lot_number
AND transaction_temp_id IN (
SELECT transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND NVL(revision, '@') = NVL(p_revision, '@')
AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id );
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision),
secondary_unit_of_measure = p_secondary_uom_code
WHERE rowid = mtlt_rec.rowid;
ROLLBACK TO UPDATE_LPN_SECONDARY_QUANTITY;
END Update_LPN_Secondary_Quantity;
SELECT wlpn.lpn_id, wdd.delivery_detail_id
FROM wms_license_plate_numbers wlpn,
wsh_delivery_details wdd
WHERE wlpn.organization_id = p_organization_id
AND wlpn.outermost_lpn_id = p_outermost_lpn_id
AND wdd.organization_id = wlpn.organization_id
AND wdd.lpn_id = wlpn.lpn_id
AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT distinct wdd.organization_id, wdd.inventory_item_id, msi.primary_uom_code, msi.secondary_uom_code
FROM mtl_system_items msi,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id = p_parent_delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.line_direction = 'O'
AND wdd.picked_quantity2 IS NULL
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.ont_pricing_qty_source = G_PRICE_SECONDARY
AND msi.secondary_default_ind = G_SECONDARY_DEFAULT
ORDER BY msi.primary_uom_code, msi.secondary_uom_code;
SELECT distinct inventory_item_id, organization_id
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND ( transfer_lpn_id = p_outermost_lpn_id OR content_lpn_id = p_outermost_lpn_id )
AND ( secondary_transaction_quantity IS NULL OR secondary_uom_code IS NULL );
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id = p_outermost_lpn_id;
SELECT 1 INTO l_temp FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_system_items msi,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.parent_delivery_detail_id = wdd_nested_lpn_rec.delivery_detail_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.line_direction = 'O'
AND wdd.picked_quantity2 IS NULL
AND msi.organization_id = wdd.organization_id
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.ont_pricing_qty_source = G_PRICE_SECONDARY
AND msi.secondary_default_ind = G_SECONDARY_NO_DEFAULT );
SELECT ont_pricing_qty_source, secondary_default_ind, primary_uom_code,
secondary_uom_code, lot_control_code
INTO l_pricing_ind, l_default_ind, l_pri_uom, l_sec_uom, l_lot_control_code
FROM mtl_system_items
WHERE organization_id = mmtt_item_rec.organization_id
AND inventory_item_id = mmtt_item_rec.inventory_item_id;
SELECT 1 INTO l_temp FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mmtt.organization_id = mmtt_item_rec.organization_id
AND mmtt.inventory_item_id = mmtt_item_rec.inventory_item_id
AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
AND ( mmtt.transfer_lpn_id = p_outermost_lpn_id OR mmtt.content_lpn_id = p_outermost_lpn_id )
AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND ( mtlt.secondary_quantity IS NULL OR mtlt.secondary_unit_of_measure IS NULL) );
SELECT 'x'
FROM wms_lpn_contents wlc, mtl_system_items msi
WHERE wlc.parent_lpn_id IN ( SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id)
AND wlc.inventory_item_id = NVL(p_inventory_item_id,wlc.inventory_item_id)
AND wlc.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
);
SELECT 'x'
FROM wsh_delivery_details wdd,
mtl_system_items msi
WHERE wdd.delivery_detail_id IN (SELECT delivery_detail_id
FROM wsh_delivery_assignments wda
START WITH parent_delivery_detail_id = p_lpn_wdd_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id)
AND wdd.inventory_item_id = NVL(p_inventory_item_id,wdd.inventory_item_id)
AND wdd.container_flag = 'N'
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = p_organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
);
SELECT 'x'
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id IN (SELECT delivery_detail_id
FROM wsh_delivery_assignments wda
WHERE parent_delivery_detail_id IN
( SELECT parent_delivery_detail_id
FROM wsh_delivery_assignments wda2
START WITH wda2.parent_delivery_detail_id = p_lpn_wdd_id
CONNECT BY PRIOR wda2.delivery_detail_id = wda2.parent_delivery_detail_id)
AND EXISTS (SELECT 1
FROM wsh_delivery_details wdd2,
wms_license_plate_numbers wlpn
WHERE wdd2.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd2.lpn_id = wlpn.lpn_id
AND NVL(wlpn.CATCH_WEIGHT_FLAG,'N') = 'N') )
AND wdd.inventory_item_id = NVL(p_inventory_item_id,inventory_item_id)
AND wdd.container_flag = 'N'
AND (EXISTS (SELECT 1 FROM mtl_system_items msi
WHERE msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = p_organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
)
);
SELECT delivery_detail_id
INTO l_lpn_wdd_id
FROM wsh_delivery_details
WHERE lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND released_status = 'X';
SELECT UNIQUE wlpn2.license_plate_number,
wlpn.outermost_lpn_id outer_lpn_id,
wlpn.lpn_context
FROM wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2
WHERE wlpn.outermost_lpn_id = wlpn2.lpn_id
AND wlpn.lpn_context = 11
AND wlpn.organization_id = p_org_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND EXISTS ( SELECT 'x'
FROM wms_license_plate_numbers wlpn3
, wms_lpn_contents wlc
, mtl_system_items msi
WHERE wlpn3.outermost_lpn_id = wlpn.outermost_lpn_id
AND wlc.parent_lpn_id = wlpn3.lpn_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
)
UNION
SELECT UNIQUE wlpn.license_plate_number,
mmtt.transfer_lpn_id,
wlpn.lpn_context
FROM wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
mtl_system_items_b msi
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
AND mmtt.organization_id = p_org_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.lpn_context = 8 -- loaded
AND wlpn.license_plate_number LIKE (p_lpn || '%')
UNION
SELECT UNIQUE wlpn.license_plate_number,
mmtt.transfer_lpn_id,
wlpn.lpn_context
FROM wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
mtl_system_items_b msi
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
AND EXISTS (SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_id = mmtt.content_lpn_id
AND wlpn2.lpn_context = 8)
AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
UNION
SELECT UNIQUE wlpn.license_plate_number,
mmtt.transfer_lpn_id,
wlpn.lpn_context
FROM wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp mmtt2,
mtl_system_items_b msi
WHERE mmtt2.inventory_item_id = msi.inventory_item_id
AND mmtt2.organization_id = msi.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
AND mmtt2.transfer_lpn_id = mmtt.content_lpn_id
AND mmtt2.parent_line_id IS NULL -- exclude bulk-picked tasks
AND EXISTS (SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_id = mmtt.content_lpn_id
AND wlpn2.lpn_context = 8)
AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.license_plate_number LIKE (p_lpn || '%');
SELECT UNIQUE wlpn2.license_plate_number,wlpn.outermost_lpn_id,
wlpn2.lpn_context
FROM wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2
WHERE wlpn.outermost_lpn_id = wlpn2.lpn_id
AND wlpn.lpn_context = 11 -- picked
AND wlpn.organization_id = p_org_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND NVL(wlpn2.catch_weight_flag,'N') = 'N'
AND EXISTS ( SELECT 'x'
FROM wms_license_plate_numbers wlpn3
, wms_lpn_contents wlc
, mtl_system_items msi
WHERE wlpn3.outermost_lpn_id = wlpn.outermost_lpn_id
AND wlc.parent_lpn_id = wlpn3.lpn_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
)
UNION
SELECT UNIQUE wlpn.license_plate_number,
mmtt.transfer_lpn_id outer_lpn,
wlpn.lpn_context
FROM wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
mtl_system_items_b msi
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
AND mmtt.content_lpn_id IS NULL
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.lpn_context = 8 -- loaded
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND NVL(wlpn.catch_weight_flag,'N') = 'N'
UNION
SELECT UNIQUE wlpn.license_plate_number,
mmtt.transfer_lpn_id, wlpn.lpn_context
FROM mtl_material_transactions_temp mmtt,
mtl_system_items_b msi,
wms_license_plate_numbers wlpn
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
AND EXISTS (SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_id = mmtt.content_lpn_id
AND wlpn2.lpn_context = 8)
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
AND mmtt.content_lpn_id IS NOT NULL
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND NVL(wlpn.catch_weight_flag,'N') = 'N'
UNION
SELECT UNIQUE wlpn.license_plate_number,
mmtt.transfer_lpn_id,
wlpn.lpn_context
FROM wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp mmtt2,
mtl_system_items_b msi
WHERE mmtt2.inventory_item_id = msi.inventory_item_id
AND mmtt2.organization_id = msi.organization_id
AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
)
AND mmtt2.transfer_lpn_id = mmtt.content_lpn_id
AND mmtt2.parent_line_id IS NULL -- exclude bulk-picked tasks
AND EXISTS (SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_id = mmtt.content_lpn_id
AND wlpn2.lpn_context = 8)
AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND NVL(wlpn.catch_weight_flag,'N') = 'N';
SELECT owlpn.license_plate_number, owlpn.lpn_id
FROM (
SELECT wlpn.license_plate_number, wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
WHERE outermost_lpn_id = p_outer_lpn_id
AND wms_catch_weight_pvt.check_ds_lpn( wlpn.lpn_id
, p_org_id
, NULL
) = 'Y'
) owlpn
WHERE owlpn.license_plate_number LIKE (p_inner_lpn || '%')
AND NOT EXISTS (SELECT 1 FROM wms_ds_ct_wt_gtemp gt
WHERE NVL(gt.INNER_LPN_ID, gt.LPN_ID) = owlpn.lpn_id);
SELECT DISTINCT wlpn.license_plate_number, lpn_id
FROM wms_license_plate_numbers wlpn
WHERE outermost_lpn_id = p_outer_lpn_id
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
AND EXISTS (
SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = wlpn.lpn_id)
AND wms_catch_weight_pvt.check_wsh_lpn( wlpn.lpn_id
, p_org_id
, NULL
, p_entry_type
) = 'Y'
UNION
SELECT DISTINCT license_plate_number, content_lpn_id
FROM mtl_system_items msi, mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn
WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id IS NULL
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND ( msi.ont_pricing_qty_source = 'S'
OR NVL(mmtt.fulfillment_base,'P') = 'S')
AND wlpn.lpn_id = mmtt.content_lpn_id
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
SELECT distinct wlpn.license_plate_number, mmtt.content_lpn_id
FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
,wms_license_plate_numbers wlpn
,mtl_transaction_lots_temp mtlt
WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id IS NULL
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND ( ( msi.ont_pricing_qty_source = 'S'
AND NVL(mmtt.fulfillment_base,'P') = 'P'
AND ( (mmtt.secondary_transaction_quantity IS NULL
AND mtlt.secondary_quantity IS NULL)
OR NVL(wlpn.catch_weight_flag,'N') = 'N') )
OR ( NVL(mmtt.fulfillment_base,'P') = 'S'
AND NVL(wlpn.catch_weight_flag,'N') = 'N') )
AND wlpn.lpn_id = mmtt.content_lpn_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
SELECT wlpn.license_plate_number, wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_outer_lpn_id
AND wlpn.lpn_id <> wlpn.outermost_lpn_id
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
AND wms_catch_weight_pvt.check_wsh_lpn( wlpn.lpn_id
, p_org_id
, NULL
, p_entry_type
) = 'Y';
SELECT DISTINCT msiv.concatenated_segments
, msiv.inventory_item_id
, msiv.description
, NVL(msiv.revision_qty_control_code, 1)
, NVL(msiv.lot_control_code, 1)
, NVL(msiv.serial_number_control_code, 1)
, NVL(msiv.restrict_subinventories_code, 2)
, NVL(msiv.restrict_locators_code, 2)
, NVL(msiv.location_control_code, 1)
, msiv.primary_uom_code
, NVL(msiv.inspection_required_flag, 2)
, NVL(msiv.shelf_life_code, 1)
, NVL(msiv.shelf_life_days, 0)
, NVL(msiv.allowed_units_lookup_code, 2)
, NVL(msiv.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msiv.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msiv.inventory_item_id)
--Bug No 3952081
--Additional Fields for Process Convergence
, NVL(msiv.grade_control_flag,'N')
, NVL(msiv.default_grade,'')
, NVL(msiv.expiration_action_interval,0)
, NVL(msiv.expiration_action_code,'')
, NVL(msiv.hold_days,0)
, NVL(msiv.maturity_days,0)
, NVL(msiv.retest_interval,0)
, NVL(msiv.copy_lot_attribute_flag,'N')
, NVL(msiv.child_lot_flag,'N')
, NVL(msiv.child_lot_validation_flag,'N')
, NVL(msiv.lot_divisible_flag,'Y')
, NVL(msiv.secondary_uom_code,'')
, NVL(msiv.secondary_default_ind,'')
, NVL(msiv.tracking_quantity_ind,'P')
, NVL(msiv.dual_uom_deviation_high,0)
, NVL(msiv.dual_uom_deviation_low,0)
FROM mtl_system_items_kfv msiv,
wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.organization_id = p_org_id
AND wms_catch_weight_pvt.check_ds_lpn( p_lpn_id
, p_org_id
, wlc.inventory_item_id
) = 'Y'
AND msiv.inventory_item_id = wlc.inventory_item_id
AND msiv.organization_id = wlc.organization_id
AND msiv.concatenated_segments LIKE (p_concat_item_segment || '%' || l_append)
AND NOT EXISTS (SELECT 1 FROM wms_ds_ct_wt_gtemp gt
WHERE gt.inventory_item_id = wlc.inventory_item_id
AND gt.org_id = wlc.organization_id
AND NVL(gt.inner_lpn_id, gt.lpn_id) = wlc.parent_lpn_id);
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id)
--Bug No 3952081
--Additional Fields for Process Convergence
, NVL(msi.grade_control_flag,'N')
, NVL(msi.default_grade,'')
, NVL(msi.expiration_action_interval,0)
, NVL(msi.expiration_action_code,'')
, NVL(msi.hold_days,0)
, NVL(msi.maturity_days,0)
, NVL(msi.retest_interval,0)
, NVL(msi.copy_lot_attribute_flag,'N')
, NVL(msi.child_lot_flag,'N')
, NVL(msi.child_lot_validation_flag,'N')
, NVL(msi.lot_divisible_flag,'Y')
, NVL(msi.secondary_uom_code,'')
, NVL(msi.secondary_default_ind,'')
, NVL(msi.tracking_quantity_ind,'P')
, NVL(msi.dual_uom_deviation_high,0)
, NVL(msi.dual_uom_deviation_low,0)
FROM mtl_system_items_kfv msi, wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.inventory_item_id = msi.inventory_item_id
AND wlc.organization_id = msi.organization_id
AND wms_catch_weight_pvt.check_wsh_lpn( p_lpn_id
, p_org_id
, wlc.inventory_item_id
, p_entry_type
) = 'Y'
UNION
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id)
--Bug No 3952081
--Additional Fields for Process Convergence
, NVL(msi.grade_control_flag,'N')
, NVL(msi.default_grade,'')
, NVL(msi.expiration_action_interval,0)
, NVL(msi.expiration_action_code,'')
, NVL(msi.hold_days,0)
, NVL(msi.maturity_days,0)
, NVL(msi.retest_interval,0)
, NVL(msi.copy_lot_attribute_flag,'N')
, NVL(msi.child_lot_flag,'N')
, NVL(msi.child_lot_validation_flag,'N')
, NVL(msi.lot_divisible_flag,'Y')
, NVL(msi.secondary_uom_code,'')
, NVL(msi.secondary_default_ind,'')
, NVL(msi.tracking_quantity_ind,'P')
, NVL(msi.dual_uom_deviation_high,0)
, NVL(msi.dual_uom_deviation_low,0)
FROM mtl_system_items_kfv msi, mtl_material_transactions_temp mmtt
WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.organization_id = p_org_id
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.parent_line_id IS NULL
AND ( msi.ont_pricing_qty_source = 'S'
OR NVL(mmtt.fulfillment_base,'P') = 'S');
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id)
--Bug No 3952081
--Additional Fields for Process Convergence
, NVL(msi.grade_control_flag,'N')
, NVL(msi.default_grade,'')
, NVL(msi.expiration_action_interval,0)
, NVL(msi.expiration_action_code,'')
, NVL(msi.hold_days,0)
, NVL(msi.maturity_days,0)
, NVL(msi.retest_interval,0)
, NVL(msi.copy_lot_attribute_flag,'N')
, NVL(msi.child_lot_flag,'N')
, NVL(msi.child_lot_validation_flag,'N')
, NVL(msi.lot_divisible_flag,'Y')
, NVL(msi.secondary_uom_code,'')
, NVL(msi.secondary_default_ind,'')
, NVL(msi.tracking_quantity_ind,'P')
, NVL(msi.dual_uom_deviation_high,0)
, NVL(msi.dual_uom_deviation_low,0)
FROM wms_license_plate_numbers wlpn,
mtl_material_transactions_temp mmtt,
mtl_system_items_kfv msi
WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
AND mmtt.organization_id = p_org_id
AND mmtt.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.parent_line_id IS NULL
AND wlpn.lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND ( ( msi.ont_pricing_qty_source = 'S'
AND NVL(mmtt.fulfillment_base,'P') = 'P'
AND mmtt.secondary_transaction_quantity IS NULL)
OR ( NVL(mmtt.fulfillment_base,'P') = 'S'
AND NVL(wlpn.catch_weight_flag,'N') = 'N') );
SELECT DISTINCT msi.concatenated_segments
, msi.inventory_item_id
, msi.description
, NVL(msi.revision_qty_control_code, 1)
, NVL(msi.lot_control_code, 1)
, NVL(msi.serial_number_control_code, 1)
, NVL(msi.restrict_subinventories_code, 2)
, NVL(msi.restrict_locators_code, 2)
, NVL(msi.location_control_code, 1)
, msi.primary_uom_code
, NVL(msi.inspection_required_flag, 2)
, NVL(msi.shelf_life_code, 1)
, NVL(msi.shelf_life_days, 0)
, NVL(msi.allowed_units_lookup_code, 2)
, NVL(msi.effectivity_control, 1)
, '0'
, '0'
, '0'
, '0'
, '0'
, '0'
, ''
, 'N'
, msi.inventory_item_flag
, 0
, wms_deploy.get_item_client_name(msi.inventory_item_id)
--Bug No 3952081
--Additional Fields for Process Convergence
, NVL(msi.grade_control_flag,'N')
, NVL(msi.default_grade,'')
, NVL(msi.expiration_action_interval,0)
, NVL(msi.expiration_action_code,'')
, NVL(msi.hold_days,0)
, NVL(msi.maturity_days,0)
, NVL(msi.retest_interval,0)
, NVL(msi.copy_lot_attribute_flag,'N')
, NVL(msi.child_lot_flag,'N')
, NVL(msi.child_lot_validation_flag,'N')
, NVL(msi.lot_divisible_flag,'Y')
, NVL(msi.secondary_uom_code,'')
, NVL(msi.secondary_default_ind,'')
, NVL(msi.tracking_quantity_ind,'P')
, NVL(msi.dual_uom_deviation_high,0)
, NVL(msi.dual_uom_deviation_low,0)
FROM wms_lpn_contents wlc,
mtl_system_items_kfv msi
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.organization_id = p_org_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.organization_id = wlc.organization_id
AND wms_catch_weight_pvt.check_wsh_lpn( p_lpn_id
, p_org_id
, wlc.inventory_item_id
, p_entry_type
) = 'Y';
SELECT picked_quantity2
FROM wsh_delivery_details wdd,
mtl_system_items_b msib
WHERE wdd.inventory_item_id = p_from_item_id
AND wdd.inventory_item_id = msib.inventory_item_id
AND wdd.organization_id = p_org_id
AND wdd.organization_id = msib.organization_id
AND NVL(wdd.revision,'@@@') = NVL(NVL(p_from_item_revision,wdd.revision),'@@@')
AND NVL(wdd.lot_number,'@@@') = NVL(p_from_item_lot_number,'@@@')
AND wdd.delivery_detail_id in (SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd1
WHERE wdd1.lpn_id = in_lpn_id
AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd1.delivery_detail_id = wda.parent_delivery_Detail_id
AND wdd1.organization_id = p_org_id);
SELECT sum(inv_convert.inv_um_convert(wdd.inventory_item_id,
6,
wdd.REQUESTED_QUANTITY,
wdd.REQUESTED_QUANTITY_UOM,
--msib.primary_uom_code,
p_from_item_pri_uom,
NULL,
NULL)) requested_quantity,
sum(picked_quantity2) picked_quantity2,
msib.primary_uom_code
FROM wsh_delivery_details wdd,
mtl_system_items_b msib
WHERE wdd.inventory_item_id = p_from_item_id
AND wdd.inventory_item_id = msib.inventory_item_id
AND wdd.organization_id = p_org_id
AND wdd.organization_id = msib.organization_id
AND NVL(wdd.revision,'@@@') = NVL(p_from_item_revision,'@@@')
AND NVL(wdd.lot_number,'@@@') = NVL(p_from_item_lot_number,'@@@')
AND wdd.delivery_detail_id in (SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_Details wdd1
WHERE wdd1.lpn_id = p_from_lpn_id
AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd1.delivery_detail_id = wda.parent_delivery_Detail_id
AND wdd1.organization_id = p_org_id)
GROUP BY wdd.inventory_item_id, wdd.revision, wdd.lot_number, msib.primary_uom_code;
SELECT wdd.delivery_Detail_id,
wdd.inventory_item_id,
wdd.organization_id,
wdd.picked_quantity,
wdd.picked_quantity2,
msi.primary_uom_code,
msi.secondary_uom_code,
msi.secondary_default_ind
FROM wsh_delivery_Details wdd,
mtl_system_items msi
WHERE wdd.delivery_detail_id in
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wda.delivery_id = wnd.delivery_id
AND wnd.name = p_delivery_name
AND wda. PARENT_DELIVERY_DETAIL_ID is not NULL)
AND picked_quantity2 IS NULL
AND wdd.inventory_item_id = msi.inventory_item_id
AND wdd.inventory_item_id is not null
AND wdd.organization_id = msi.organization_id
AND msi.ont_pricing_qty_source = 'S';
SELECT (inv_ui_item_lovs.get_conversion_rate(
uom_code,
p_organization_id,
p_inventory_item_id)) uom_code_comp
, unit_of_measure
, description
, uom_class
FROM mtl_item_uoms_view
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND uom_class IN (SELECT uom_class
FROM mtl_system_items msi,
mtl_units_of_measure muom
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id
AND muom.uom_code = msi.primary_uom_code)
AND uom_code LIKE (l_code)
ORDER BY inv_ui_item_lovs.conversion_order(
inv_ui_item_lovs.get_conversion_rate(
uom_code
, p_organization_id
, p_inventory_item_id)) asc
, UPPER(uom_code);
SELECT uom_class
INTO l_uom_class
FROM mtl_units_of_measure_vl
WHERE uom_code = p_uom_code;
PROCEDURE update_lpn_tare (
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_lpn_id IN NUMBER
, p_org_id IN NUMBER
, p_tare_weight IN NUMBER
, p_tare_uom_code IN VARCHAR2
, p_update_tare_flag IN VARCHAR2
) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
SAVEPOINT update_tare_sp;
print_debug('Entered update_lpn_tare:' ||
' p_lpn_id: ' || p_lpn_id ||
', p_org_id: ' || p_org_id ||
', p_tare_weight: ' || p_tare_weight ||
', p_tare_uom_code: ' || p_tare_uom_code ||
', p_update_tare_flag: ' || p_update_tare_flag
, 4);
SELECT NVL(tare_weight, 0), NVL(tare_weight_uom_code, '###')
INTO l_curr_tare_wt, l_curr_tare_wt_uom_code
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id
AND organization_id = p_org_id;
print_debug('Inside update_lpn_tare l_curr_tare_wt : '|| l_curr_tare_wt ||', l_curr_tare_wt_uom_code: ' || l_curr_tare_wt_uom_code , 4);
SELECT NVL(SUM(DECODE(tare_weight_uom_code, NULL, 0 , inv_convert.inv_um_convert(
item_id => NULL
, precision => 5
, from_quantity => NVL(tare_weight, 0)
, from_unit => tare_weight_uom_code
, to_unit => l_curr_tare_wt_uom_code
, from_name => NULL
, to_name => NULL))), 0)
INTO l_inner_lpn_tare
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_lpn_id
AND organization_id = p_org_id;
IF(p_update_tare_flag = 'U') THEN
IF(p_tare_uom_code = NVL(l_curr_tare_wt_uom_code, '###')) THEN
l_new_tare_wt := l_tare_weight + NVL(l_curr_tare_wt,0);
print_debug('Inside update_lpn_tare for case where uom is same as curr LPN tare UoM', 4);
print_debug('Inside update_lpn_tare for case where uom is diff than curr LPN tare UoM', 4);
print_debug('Inside update_lpn_tare l_converted_qty : '|| l_converted_qty ||', in l_curr_tare_wt_uom_code: ' || l_curr_tare_wt_uom_code , 4);
print_debug('Inside update_lpn_tare l_new_tare_wt: ' || l_new_tare_wt || ', l_new_tare_wt_uom_code: ' || l_new_tare_wt_uom_code , 4);
l_lpn_tbl.DELETE;
ROLLBACK TO update_tare_sp;
ROLLBACK TO update_tare_sp;
END update_lpn_tare;