[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;
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;
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 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 in (11,8) -- picked/loaded
AND wlpn.organization_id = p_org_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND EXISTS (
SELECT msi.inventory_item_id
FROM wms_lpn_contents wlc , mtl_system_items_b msi
WHERE wlc.organization_id = msi.organization_id
AND wlc.inventory_item_id = msi.inventory_item_id
AND msi.ont_pricing_qty_source = 'S'
AND msi.organization_id = p_org_id
AND wlc.parent_lpn_id = wlpn.lpn_id)
UNION
SELECT UNIQUE wlpn.license_plate_number,
nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id) outer_lpn,
wlpn.lpn_context
FROM mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn,
mtl_system_items_b msi
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = nvl(mmtt.transfer_lpn_id, content_lpn_id)
AND wlpn.lpn_context = 8 -- loaded
AND msi.ont_pricing_qty_source = 'S'
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND parent_line_id is null -- exclude bulk-picked tasks
UNION
SELECT UNIQUE wlpn.license_plate_number,
transfer_lpn_id,
wlpn.lpn_context
FROM mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn,
mtl_system_items_b msi
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND mmtt.transfer_lpn_id IS NOT NULL
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 msi.ont_pricing_qty_source = 'S'
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND parent_line_id is null; -- exclude bulk-picked tasks
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 in (11) -- picked
AND wlpn.organization_id = p_org_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND EXISTS (
SELECT msi.inventory_item_id, wddl.lpn_id
FROM mtl_system_items_b msi,
wsh_delivery_details wddl,
wsh_delivery_assignments_v wda,
wsh_delivery_details wddit
WHERE wddit.organization_id = msi.organization_id
AND wddit.inventory_item_id = msi.inventory_item_id
AND msi.ont_pricing_qty_source = 'S'
AND wddl.lpn_id = wlpn.lpn_id
AND wddl.released_status = 'X' -- For LPN reuse ER : 6845650
AND msi.organization_id = p_org_id
AND wddl.delivery_detail_id = wda.parent_delivery_detail_id
AND wddit.delivery_detail_id = wda.delivery_detail_id
AND wddit.picked_quantity2 is null )
UNION
SELECT UNIQUE wlpn.license_plate_number,
nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id) outer_lpn,
wlpn.lpn_context
FROM mtl_material_transactions_temp mmtt, wms_license_plate_numbers wlpn,
mtl_system_items_b msi, mtl_transaction_lots_temp mtlt
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND mmtt.organization_id = p_org_id
AND msi.ont_pricing_qty_source = 'S'
AND wlpn.lpn_id = nvl(mmtt.transfer_lpn_id, content_lpn_id)
AND wlpn.lpn_context = 8 -- loaded
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND parent_line_id is null -- exclude bulk-picked tasks
AND secondary_transaction_quantity is null -- catch.wt not enterd
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mtlt.secondary_quantity IS NULL
UNION
SELECT UNIQUE wlpn.license_plate_number,
transfer_lpn_id, wlpn.lpn_context
FROM mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn,
mtl_system_items_b msi,
mtl_transaction_lots_temp mtlt
WHERE mmtt.inventory_item_id = msi.inventory_item_id
AND mmtt.organization_id = msi.organization_id
AND mmtt.organization_id = p_org_id
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.license_plate_number LIKE (p_lpn || '%')
AND mmtt.transfer_lpn_id IS NOT NULL
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.secondary_transaction_quantity is null -- catch.wt not enterd
AND msi.ont_pricing_qty_source = 'S'
AND parent_line_id is null -- exclude bulk-picked tasks
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mtlt.secondary_quantity IS NULL;
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 EXISTS (
SELECT 1
FROM mtl_system_items_b msi, wms_lpn_contents wlc
WHERE wlc.organization_id = wlpn.organization_id
AND wlc.parent_lpn_id = wlpn.lpn_id
AND msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.ont_pricing_qty_source = 'S'
)
) 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 msi.inventory_item_id
FROM wms_lpn_contents wlc , mtl_system_items_b msi
WHERE wlc.organization_id = msi.organization_id
AND wlc.inventory_item_id = msi.inventory_item_id
AND msi.ont_pricing_qty_source = 'S'
AND wlc.parent_lpn_id = wlpn.lpn_id)
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.content_lpn_id = p_outer_lpn_id
OR mmtt.transfer_lpn_id = p_outer_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 msi.ont_pricing_qty_source = 'S'
AND wlpn.lpn_id = mmtt.content_lpn_id
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
SELECT distinct license_plate_number,
transfer_lpn_id
FROM mtl_system_items msi,
mtl_material_transactions_temp mmtt,
wms_license_plate_numbers wlpn
WHERE (mmtt.content_lpn_id = p_outer_lpn_id
OR mmtt.transfer_lpn_id = p_outer_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 msi.ont_pricing_qty_source = 'S'
AND wlpn.lpn_id = mmtt.transfer_lpn_id
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
SELECT distinct wlpn.license_plate_number, 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.content_lpn_id = p_outer_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 msi.ont_pricing_qty_source = 'S'
AND mmtt.secondary_transaction_quantity is null
AND mmtt.content_lpn_id = wlpn.lpn_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
AND mtlt.secondary_quantity IS NULL
AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
UNION
SELECT distinct wlpn.license_plate_number, 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.organization_id = msi.organization_id
AND mmtt.inventory_item_id = msi.inventory_item_id
AND msi.ont_pricing_qty_source = 'S'
AND mmtt.secondary_transaction_quantity is null
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 || '%')
AND mtlt.secondary_quantity is NULL;
SELECT distinct outer.container_name, outer.lpn_id
FROM wsh_delivery_Details inner,
(SELECT wda.delivery_detail_id,
wdd.inventory_item_id,
wdd.lpn_id,wdd.container_name
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.lpn_id in (
SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
WHERE outermost_lpn_id = p_outer_lpn_id)
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = p_org_id
AND wda.parent_delivery_detail_id is not null
AND picked_quantity2 is null) outer
WHERE inner.delivery_detail_id = outer.delivery_Detail_id
AND outer.container_name LIKE (p_inner_lpn || '%')
AND exists (SELECT msi.inventory_item_id
FROM mtl_system_items_b msi
WHERE msi.organization_id = inner.organization_id
AND msi.organization_id = p_org_id
AND msi.ont_pricing_qty_source = 'S'
AND msi.inventory_item_id = inner.inventory_item_id);
SELECT DISTINCT msiv.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
--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 msiv,
wms_lpn_contents wlc,
mtl_system_items msi
WHERE wlc.parent_lpn_id = p_lpn_id
AND wlc.organization_id = p_org_id
AND msi.organization_id = wlc.organization_id
AND msi.inventory_item_id = wlc.inventory_item_id
AND msi.ont_pricing_qty_source = 'S'
AND msiv.inventory_item_id = msi.inventory_item_id
AND msiv.organization_id = msi.organization_id
AND msiv.concatenated_segments LIKE (p_concat_item_segment || '%')
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
--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 msi.ont_pricing_qty_source = 'S'
AND wlc.organization_id = msi.organization_id
AND msi.organization_id = p_org_id
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
--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 msi.ont_pricing_qty_source = 'S'
AND mmtt.inventory_item_id = msi.inventory_item_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
--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 msi.ont_pricing_qty_source = 'S'
and mmtt.secondary_transaction_quantity is null;
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
--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, wsh_Delivery_Details wdd1
WHERE wdd1.inventory_item_id = msi.inventory_item_id
AND wdd1.organization_id = msi.organization_id
AND msi.ont_pricing_qty_source = 'S'
AND wdd1. picked_quantity2 is NULL
AND wdd1.delivery_detail_id in
(SELECT wda.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.lpn_id= p_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd.organization_id = p_org_id
AND wda.parent_delivery_detail_id is not null)
AND msi.organization_id = p_org_id;
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';