[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE supply_source_type_id = p_supply_source_type_id
AND supply_source_header_id = p_supply_source_header_id
AND supply_source_line_id = p_supply_source_line_id;
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE supply_source_type_id = p_supply_source_type_id
AND supply_source_header_id = p_supply_source_header_id;
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE supply_source_type_id = p_supply_source_type_id
AND supply_source_header_id = p_supply_source_header_id
AND supply_source_line_id = p_supply_source_line_id
AND nvl(project_id, -99) = nvl(p_project_id, -99)
AND nvl(task_id, -99) = nvl(p_task_id, -99);
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE supply_source_type_id = p_supply_source_type_id
AND supply_source_header_id = p_supply_source_header_id
AND supply_source_line_id = p_supply_source_line_id
AND supply_source_line_detail = p_supply_source_line_detail
AND nvl(project_id, -99) = nvl(p_project_id, -99)
AND nvl(task_id, -99) = nvl(p_task_id, -99);
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE supply_source_type_id = p_supply_source_type_id
AND organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND demand_source_type_id <> 5;-- bug 9706800: Consider reservations only for Sales Order and not for WIP Jobs/OPM batches since MOL quantity
SELECT Nvl(ABS(SUM(primary_quantity)),0)
INTO l_rti_primary_quantity
FROM rcv_transactions_interface rti
WHERE to_organization_id = p_organization_id
AND item_id = p_item_id
AND NVL(item_revision, '@@@') = NVL(p_revision,NVL(item_revision, '@@@'))
AND rti.processing_status_code <> 'ERROR'
AND rti.transaction_status_code <> 'ERROR'
AND NOT exists (SELECT '1' FROM rcv_transactions rt
WHERE rt.interface_transaction_id = rti.interface_transaction_id)
AND (TRANSACTION_TYPE = 'DELIVER'
OR (TRANSACTION_TYPE IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
AND EXISTS (SELECT '1' FROM rcv_transactions rt
WHERE rt.transaction_id = rti.parent_transaction_id
AND rt.transaction_type IN ('RECEIVE','ACCEPT','REJECT','TRANSFER')))
OR (TRANSACTION_TYPE IN ('CORRECT')
AND quantity < 0
AND EXISTS (SELECT '1' FROM rcv_transactions rt
WHERE rt.transaction_id = rti.parent_transaction_id
AND rt.transaction_type IN ('RECEIVE')))
OR (TRANSACTION_TYPE IN ('CORRECT')
AND quantity > 0
AND EXISTS (SELECT '1' FROM rcv_transactions rt
WHERE rt.transaction_id = rti.parent_transaction_id
AND rt.transaction_type IN ('DELIVER'))));
SELECT count(*)
FROM mtl_lot_uom_class_conversions
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND (disable_date IS NULL or disable_date > sysdate);
SELECT organization_id, inventory_item_id, lot_number,
primary_uom_code, primary_reservation_quantity, reservation_uom_code
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
AND demand_source_line_detail is null
AND lot_number is not null;
SELECT order_quantity_uom
FROM oe_order_lines_all
WHERE line_id = p_demand_source_line_id;
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
AND demand_source_line_detail = p_demand_source_line_detail;
SELECT nvl(sum(primary_reservation_quantity), 0)
INTO l_primary_reserved_quantity
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id;
SELECT nvl(sum(requested_quantity), 0) , nvl(sum(requested_quantity2), 0)
INTO l_wdd_primary_quantity, l_wdd_secondary_quantity
FROM wsh_delivery_details
WHERE source_line_id = p_demand_source_line_id
AND delivery_detail_id = p_demand_source_line_detail
AND nvl(project_id, -99) = nvl(p_project_id, -99)
AND nvl(task_id, -99) = nvl(p_task_id, -99);
SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
,nvl(sum(secondary_reservation_quantity), 0)
INTO l_wdd_primary_reserved_qty, l_rsv_primary_uom_code, l_wdd_secondary_reserved_qty
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
AND demand_source_line_detail = p_demand_source_line_detail
GROUP BY primary_uom_code;
SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
,nvl(sum(secondary_reservation_quantity), 0)
INTO l_primary_reserved_quantity, l_rsv_primary_uom_code ,l_secondary_reserved_quantity
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
GROUP BY primary_uom_code;
SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
,nvl(sum(secondary_reservation_quantity), 0)
INTO l_primary_reserved_quantity, l_rsv_primary_uom_code ,l_secondary_reserved_quantity
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
AND lot_number IS NULL --Bug 12978409
GROUP BY primary_uom_code;
SELECT nvl(sum(primary_reservation_quantity), 0), primary_uom_code
,nvl(sum(secondary_reservation_quantity), 0)
INTO l_primary_reserved_quantity, l_rsv_primary_uom_code ,l_secondary_reserved_quantity
FROM mtl_reservations
WHERE demand_source_type_id = p_demand_source_type_id
AND demand_source_header_id = p_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
AND demand_source_line_detail is null
AND lot_number is null --lydal
GROUP BY primary_uom_code;
SELECT ordered_quantity , order_quantity_uom
,ordered_quantity2 , ordered_quantity_uom2
INTO l_available_quantity, l_order_quantity_uom_code
,l_available_quantity2, l_order_quantity_uom2
FROM oe_order_lines_all
WHERE line_id = p_demand_source_line_id; --Bug14629017
SELECT nvl((ordered_quantity * ship_tolerance_above/100),0) INTO l_over_shippable_qty
FROM oe_order_lines_all
WHERE line_id = p_demand_source_line_id
AND nvl(project_id, -99) = nvl(p_project_id, -99)
AND nvl(task_id, -99) = nvl(p_task_id, -99)
AND NOT EXISTS (SELECT 1 FROM MTL_RESERVATIONS MR
WHERE MR.demand_source_line_id = p_demand_source_line_id
AND MR.demand_source_type_id = p_demand_source_type_id
AND MR.demand_source_header_id = p_demand_source_header_id
AND NVL (MR.staged_flag,'N') <> 'Y' ) ;
SELECT primary_uom_code INTO l_rsv_primary_uom_code FROM
mtl_system_items WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT nvl((Sum(wdd.picked_quantity)),0), wdd.requested_quantity_uom
,nvl((Sum(wdd.picked_quantity2)),0)
INTO l_wdd_picked_qty, l_wdd_uom_code
,l_wdd_picked_qty2
FROM wsh_delivery_details wdd
WHERE wdd.source_line_id = p_demand_source_line_id
AND wdd.released_status IN ('Y','C')
AND NOT EXISTS
(
SELECT 1 FROM mtl_reservations mr
WHERE mr.demand_source_line_id = wdd.source_line_id
AND nvl(mr.staged_flag, 'N') = 'Y'
AND mr.inventory_item_id = wdd.inventory_item_id
AND mr.organization_id = wdd.organization_id
AND nvl(mr.subinventory_code, '@@@') = nvl(wdd.subinventory, '@@@')
AND nvl(mr.locator_id, -999) = nvl(wdd.locator_id, -999)
AND nvl(mr.lot_number, '@@@') = nvl(wdd.lot_number, '@@@')
)
AND NOT EXISTS (SELECT 1 from mtl_parameters
WHERE organization_id = wdd.organization_id
AND NVL(wms_enabled_flag,'N') = 'Y') --Bug 9036307
GROUP BY wdd.requested_quantity_uom ;