[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
mr.RESERVATION_ID
,TO_CHAR(mr.REQUIREMENT_DATE, l_date_format)
,mr.PRIMARY_UOM_CODE
,mr.RESERVATION_UOM_CODE
,NVL(mr.RESERVATION_QUANTITY,0)
,NVL(mr.PRIMARY_RESERVATION_QUANTITY,0)
,mr.SUBINVENTORY_CODE
,mr.SUBINVENTORY_ID
,NVL(mr.LOCATOR_ID, -1)
,mr.LOT_NUMBER
,mr.LOT_NUMBER_ID
,NVL(mr.DETAILED_QUANTITY,0)
,NVL(mr.SECONDARY_DETAILED_QUANTITY,0)
,NVL(mr.SECONDARY_RESERVATION_QUANTITY,0)
,mr.SECONDARY_UOM_CODE
,mr.inventory_item_id
,mr.revision
FROM mtl_reservations mr
WHERE mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_item_id
AND mr.lot_number = p_lot_number;
SELECT
mr.RESERVATION_ID
FROM mtl_reservations mr,
wms_item_locations_kfv loc
WHERE mr.organization_id = p_organization_id
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND mr.demand_source_line_id = p_material_detail_id
AND NVL(mr.subinventory_code, '-1') = NVL(p_subinventory_code, '-1')
AND NVL(mr.locator_id, -1) = NVL(p_locator_id, -1)
AND mr.organization_id = loc.organization_id(+)
AND mr.subinventory_code = loc.subinventory_code(+)
AND mr.locator_id = loc.inventory_location_id(+)
AND NVL(mr.lot_number, '-1') = NVL(p_lot_number, '-1')
AND mr.reservation_id <> NVL(p_exclude_res_id, 0)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id);
SELECT
mr.RESERVATION_ID
,TO_CHAR(mr.REQUIREMENT_DATE, l_date_format)
,mr.PRIMARY_UOM_CODE
,mr.RESERVATION_UOM_CODE
,NVL(mr.RESERVATION_QUANTITY,0)
,NVL(mr.PRIMARY_RESERVATION_QUANTITY,0)
,mr.SUBINVENTORY_CODE
,mr.SUBINVENTORY_ID
,NVL(mr.LOCATOR_ID, -1)
,mr.LOT_NUMBER
,mr.LOT_NUMBER_ID
,NVL(mr.DETAILED_QUANTITY,0)
,NVL(mr.SECONDARY_DETAILED_QUANTITY,0)
,NVL(mr.SECONDARY_RESERVATION_QUANTITY,0)
,mr.SECONDARY_UOM_CODE
,mr.inventory_item_id
,loc.concatenated_segments
,mr.revision
FROM mtl_reservations mr,
wms_item_locations_kfv loc
WHERE mr.organization_id = p_organization_id
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND mr.demand_source_line_id = p_material_detail_id
AND (p_subinventory_code IS NULL OR (mr.subinventory_code = p_subinventory_code))
AND (p_locator_id IS NULL OR (mr.locator_id = p_locator_id))
AND mr.organization_id = loc.organization_id(+)
AND mr.subinventory_code = loc.subinventory_code(+)
AND mr.locator_id = loc.inventory_location_id(+)
AND (p_lot_number IS NULL OR (mr.lot_number = p_lot_number))
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date;
SELECT
mr.RESERVATION_ID
,TO_CHAR(mr.REQUIREMENT_DATE, l_date_format)
,mr.PRIMARY_UOM_CODE
,mr.RESERVATION_UOM_CODE
,NVL(mr.RESERVATION_QUANTITY,0)
,NVL(mr.PRIMARY_RESERVATION_QUANTITY,0)
,mr.SUBINVENTORY_CODE
,mr.SUBINVENTORY_ID
,NVL(mr.LOCATOR_ID, -1)
,mr.LOT_NUMBER
,mr.LOT_NUMBER_ID
,NVL(mr.DETAILED_QUANTITY,0)
,NVL(mr.SECONDARY_DETAILED_QUANTITY,0)
,NVL(mr.SECONDARY_RESERVATION_QUANTITY,0)
,mr.SECONDARY_UOM_CODE
,mr.inventory_item_id
,loc.concatenated_segments
,mr.revision
FROM mtl_reservations mr,
wms_item_locations_kfv loc
WHERE mr.organization_id = p_organization_id
AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = p_batch_id
AND mr.demand_source_line_id = p_material_detail_id
AND (mr.subinventory_code IS NOT NULL)
AND (p_revcontrol = 0 OR (p_revcontrol = 1 AND mr.revision IS NOT NULL))
AND (p_eff_loccontrol = 0 OR (p_eff_loccontrol = 1 AND mr.locator_id IS NOT NULL))
AND mr.organization_id = loc.organization_id(+)
AND mr.subinventory_code = loc.subinventory_code(+)
AND mr.locator_id = loc.inventory_location_id(+)
AND (p_lotcontrol = 0 OR (p_lotcontrol = 1 AND mr.lot_number IS NOT NULL))
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date;
| Update_Qty_Tree_For_Rsrv
|
| USAGE
|
| ARGUMENTS
|
| RETURNS
|
| HISTORY
| Created 26-Apr-05 Eddie Oumerretane
|
+========================================================================+*/
PROCEDURE Update_Qty_Tree_For_Rsrv (p_organization_id IN NUMBER,
p_batch_id IN NUMBER,
p_material_detail_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_revision IN VARCHAR2,
p_subinventory_code IN VARCHAR2,
p_locator_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_revision_control IN VARCHAR2,
p_lot_control IN VARCHAR2,
p_primary_qty IN NUMBER,
p_secondary_qty IN NUMBER,
x_tree_id OUT NOCOPY NUMBER,
x_atr OUT NOCOPY NUMBER,
x_satr OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2) IS
l_is_revision_control BOOLEAN := FALSE;
INV_Quantity_Tree_Grp.Update_Quantities(
p_api_version_number => 1.0,
p_init_msg_lst => 'T',
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => x_error_msg,
p_tree_id => x_tree_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_primary_quantity => l_primary_qty,
p_quantity_type => 3, --inv_quantity_tree_pvt.g_qr_other_demand
p_secondary_quantity => l_secondary_qty,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att,
x_atr => x_atr,
x_sqoh => l_sqoh,
x_srqoh => l_srqoh,
x_sqr => l_sqr,
x_sqs => l_sqs,
x_satt => l_satt,
x_satr => x_satr,
p_containerized => 0,
p_lpn_id => NULL);
gme_debug.put_line('Create Qty Tree exception in Update Qty Tree');
gme_debug.put_line('When others exception in Update Qty Tree');
fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','Update_Qty_Tree_For_Rsrv');
END Update_Qty_Tree_For_Rsrv;
fnd_msg_pub.delete_msg;
| Update_Reservation
|
| USAGE
|
| ARGUMENTS
|
| RETURNS
|
| HISTORY
| Created 26-Apr-05 Eddie Oumerretane
|
+========================================================================+*/
PROCEDURE Update_Reservation(p_reservation_id IN NUMBER,
p_revision IN VARCHAR2,
p_subinventory_code IN VARCHAR2,
p_locator_id IN NUMBER,
p_lot_number IN VARCHAR2,
p_reserved_qty IN NUMBER,
p_reserved_uom_code IN VARCHAR2,
p_sec_reserved_qty IN NUMBER,
p_requirement_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
GME_RESERVATIONS_PVT.update_reservation(
p_reservation_id => p_reservation_id,
p_revision => p_revision,
p_subinventory => p_subinventory_code,
p_locator_id => p_locator_id,
p_lot_number => p_lot_number,
p_new_qty => p_reserved_qty,
p_new_sec_qty => p_sec_reserved_qty,
p_new_uom => p_reserved_uom_code,
p_new_date => p_requirement_date,
x_return_status => x_return_status);
gme_common_pvt.log_message('GME_RESERVATION_UPDATED');
gme_debug.put_line('When others exception in Update Reservation');
fnd_msg_pub.add_exc_msg('GME_MOBILE_RSRV','update_reservation');
END Update_Reservation;