[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Updated the value of l_open_qty as the WMS engine is using its own intelligence to
calculate quantity and hence we just need to pass
l_open_qty as planned in case of Pending batches and Planned-actual in case of WIP batches.
G. Muratore 02-Jul-2010 Bug 9856765
We will no longer create a HLR if the remianing open qty after calling WMS is a small number
due to a rounding issue.
PROCEDURE: auto_detail_line
Kishore 22-Jul-2010 Bug No.9924437
1.Created new Procedures get_material_res, get_MO_allocated_qty. As WMS is not considering
allocations while doing reservations, added code to exclude allocations from planned qty
before sending to WMS at Auto-Detail line.
2. Reverted the fix 8599753 as we are recalculating l_open_qty by excluding allocations.
Kishore 27-Jul-2010 Bug No.9946085
Changed the cursor MO_line_allocation, in the procedure, get_MO_allocated_qty, to consider
non-lot controlled items also
Kishore 28-Jul-2010 Bug No.9946983
Changed the cursor cur_get_resvns, in the procedure, get_reserved_qty, to consider allocated
reservations also for deriving reserved quantity.
G. Muratore 02-Jul-2010 Bug 9959125
Clear qty tree so that multiple reservations can be created. This issue was found
while testing ADM rounding errors with auto detail.
PROCEDURE: create_material_reservation
G. Muratore 22-Jun-2011 Bug 12613813
Add p_locator_only parameter so picking can consider locator also.
PROCEDURE: get_reserved_qty
G. Muratore 12-Jul-2011 Bug 12737393 / 12613813
Make sure locator value is fetched so picking can consider locator also.
PROCEDURE: get_reserved_qty
G. Muratore 12-Jul-2011 Bug 12934259
Release lock on inventory records and release qty tree upon error.
PROCEDURE: auto_detail_batch auto_detail_line.
G. Muratore 28-Nov-2011 Bug 13355127
Bypass code that created HLR when auto detail cannot detail full quantity.
PROCEDURE: auto_detail_line.
G. Muratore 13-Apr-2012 Bug 13532998
Introduce date parameter for convert_dtl_reservation api.
If passed in stamp transactions with the date.
PROCEDURE: convert_dtl_reservation.
/*************************************************************************************************/
PROCEDURE get_reservations_msca (
p_organization_id IN NUMBER
,p_batch_id IN NUMBER
,p_material_detail_id IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_locator_id IN NUMBER
,p_lot_number IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_error_msg OUT NOCOPY VARCHAR2
,x_rsrv_cursor OUT NOCOPY g_msca_resvns)
IS
l_date_format VARCHAR2 (100);
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, mr.locator_id
,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
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 (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 d.*
FROM gme_material_details d, mtl_system_items i
WHERE d.batch_id = p_batch_id
AND d.line_type = -1
AND d.line_type = -1
AND ( NVL (p_timefence, 0) = 0
OR (d.material_requirement_date < SYSDATE + p_timefence) )
AND i.inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.reservable_type = 1
AND d.phantom_type = 0
AND (i.lot_control_code < 2
OR i.lot_control_code > 1 AND i.lot_divisible_flag = 'Y')
ORDER BY d.line_no;
SELECT mr.*
FROM mtl_reservations mr
WHERE organization_id = v_org_id
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = v_batch_id
AND demand_source_line_id = v_material_detail_id
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
ORDER BY mr.requirement_date, mr.reservation_id; -- nsinghi bug#5176319. Add mr.reservation_id in order by clause.
SELECT mr.*
FROM mtl_reservations mr
WHERE organization_id = v_org_id
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = v_batch_id
AND demand_source_line_id = v_material_detail_id
AND EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id)
UNION ALL
SELECT mr.*
FROM mtl_reservations mr
WHERE organization_id = v_org_id
AND demand_source_type_id = gme_common_pvt.g_txn_source_type
AND demand_source_header_id = v_batch_id
AND demand_source_line_id = v_material_detail_id
AND staged_flag = 'Y'
AND supply_source_type_id = inv_reservation_global.g_source_type_inv;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT mmtt.reservation_id
,mmtt.inventory_item_id
,mmtt.organization_id
,mmtt.subinventory_code
,mmtt.locator_id
,mtlt.lot_number
,Nvl(mtlt.transaction_quantity, mmtt.transaction_quantity)transaction_quantity /* Changed in Bug No.9946085 */
,mmtt.TRANSACTION_UOM
,Nvl(mtlt.primary_quantity, mmtt.primary_quantity)primary_quantity /* Changed in Bug No.9946085 */
,mmtt.ITEM_primary_UOM_CODE
,Nvl(mtlt.secondary_quantity, mmtt.secondary_transaction_quantity)secondary_quantity /* Changed in Bug No.9946085 */
,mmtt.secondary_uom_code
FROM mtl_txn_request_lines l,
mtl_txn_request_headers h ,
mtl_material_transactions_temp mmtt ,
mtl_transaction_lots_temp mtlt
WHERE l.transaction_source_type_id =
gme_common_pvt.g_txn_source_type
AND l.txn_source_id = v_batch_id
AND l.txn_source_line_id = v_material_detail_id
AND l.line_status NOT IN (5, 6)
AND h.header_id = l.header_id
AND h.move_order_type NOT IN
(gme_common_pvt.g_invis_move_order_type
,inv_globals.g_move_order_put_away)
AND mmtt.move_order_line_id = l.line_id
AND mmtt.transaction_source_id = l.txn_source_id
AND mmtt.trx_source_line_id =l.txn_source_line_id
AND mmtt.TRANSACTION_TEMP_ID = mtlt.TRANSACTION_TEMP_ID(+) /* Added outer join in Bug No.9946085 */
AND ((p_called_by = 'Z' and mmtt.reservation_id is null) or (p_called_by = 'R'))
ORDER BY l.creation_date DESC;
PROCEDURE delete_batch_reservations (
p_organization_id IN NUMBER
,p_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_batch_reservations';
delete_resvn_error EXCEPTION;
SELECT d.material_detail_id, d.batch_id, d.organization_id
FROM gme_material_details d, mtl_system_items_b i
WHERE d.organization_id = p_organization_id
AND d.batch_id = p_batch_id
AND d.line_type = gme_common_pvt.g_line_type_ing
AND i.organization_id = d.organization_id
AND i.inventory_item_id = d.inventory_item_id
AND i.reservable_type = 1;
gme_reservations_pvt.delete_material_reservations
(p_organization_id => get_rec.organization_id
,p_batch_id => get_rec.batch_id
,p_material_detail_id => get_rec.material_detail_id
,x_return_status => l_return_status);
RAISE delete_resvn_error;
WHEN delete_resvn_error THEN
x_return_status := l_return_status;
END delete_batch_reservations;
PROCEDURE delete_material_reservations (
p_organization_id IN NUMBER
,p_batch_id IN NUMBER
,p_material_detail_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_reservations';
gme_reservations_pvt.delete_reservation
(p_reservation_id => l_rsv_tbl (i).reservation_id
,x_return_status => l_return_status);
END delete_material_reservations;
PROCEDURE delete_reservation (
p_reservation_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'delete_reservation';
gme_debug.put_line ('Calling inv_reservation_pub.delete_reservation');
inv_reservation_pub.delete_reservation
(p_api_version_number => 1.0
,p_init_msg_lst => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_rsv_rec => l_rsv_rec
,p_serial_number => l_serial_number);
( 'inv_reservation_pub.delete_reservation returns '
|| l_return_status);
END delete_reservation;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT mr.*
FROM mtl_reservations mr
WHERE mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
AND mr.demand_source_header_id = l_mtl_dtl_rec.batch_id
AND mr.demand_source_line_id = l_mtl_dtl_rec.material_detail_id
AND ((p_supply_sub_only = fnd_api.g_false) OR (mr.subinventory_code = l_mtl_dtl_rec.subinventory))
AND (p_locator_only = fnd_api.g_false OR NVL(mr.locator_id, '-1') = NVL(l_mtl_dtl_rec.locator_id, '-1')); -- Bug 12737393
/* AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id = mr.reservation_id); */ /* Commented code in Bug No.9946983*/
/* Pass only values needed to be updated reservation ID is required */
PROCEDURE update_reservation (
p_reservation_id IN NUMBER
,p_revision IN VARCHAR2 DEFAULT NULL
,p_subinventory IN VARCHAR2 DEFAULT NULL
,p_locator_id IN NUMBER DEFAULT NULL
,p_lot_number IN VARCHAR2 DEFAULT NULL
,p_new_qty IN NUMBER DEFAULT NULL
,p_new_sec_qty IN NUMBER DEFAULT NULL
,p_new_uom IN VARCHAR2 DEFAULT NULL
,p_new_date IN DATE DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'update_reservation';
update_resvn_error EXCEPTION;
|| ':Calling inv_reservation_pub.update_reservation with reservation_id = '
|| p_reservation_id);
inv_reservation_pub.update_reservation
(p_api_version_number => 1.0
,p_init_msg_lst => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_original_rsv_rec => l_orig_rsv_rec
,p_to_rsv_rec => l_rsv_rec
,p_original_serial_number => l_serial_number
,p_to_serial_number => l_serial_number
,p_validation_flag => fnd_api.g_true
,p_check_availability => fnd_api.g_true);
|| 'Return status from inv_reservation_pub.update_reservation is '
|| l_return_status);
RAISE update_resvn_error;
WHEN update_resvn_error THEN
x_return_status := l_return_status;
END update_reservation;
update_resvn_error EXCEPTION;
RAISE update_resvn_error;
WHEN update_resvn_error THEN
x_return_status := l_return_status;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM mtl_reservations mr
WHERE organization_id = v_org_id
AND demand_source_type_id =
gme_common_pvt.g_txn_source_type
AND demand_source_header_id = v_batch_id
AND demand_source_line_id = v_material_detail_id
AND NOT EXISTS (
SELECT 1
FROM mtl_material_transactions_temp
WHERE reservation_id =
mr.reservation_id) );
SELECT *
FROM mtl_system_items_b
WHERE organization_id = v_org_id
AND inventory_item_id = v_inventory_item_id;
update_mo_fail EXCEPTION;
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT *
FROM mtl_txn_request_lines
WHERE line_id = v_move_order_line_id;
/* Update the Move Order */
gme_move_orders_pvt.update_move_order_lines
(p_batch_id => p_material_details_rec.batch_id
,p_material_detail_id => p_material_details_rec.material_detail_id
,p_new_qty => l_open_qty
,p_new_date => NULL
,p_invis_move_line_id => p_material_details_rec.move_order_line_id
,x_return_status => x_return_status);
|| ' Return from update_move_order_lines is '
|| x_return_status);
RAISE update_mo_fail;
l_trolin_rec_type.last_updated_by := l_trolin_rec.last_updated_by;
l_trolin_rec_type.last_update_date := l_trolin_rec.last_update_date;
l_trolin_rec_type.last_update_login := l_trolin_rec.last_update_login;
l_trolin_rec_type.program_update_date := l_trolin_rec.program_update_date;
relieve_res_error OR open_qty_error OR update_mo_fail OR
create_suggestions_err OR error_unexpected OR create_reservation_err THEN
IF g_debug <= gme_debug.g_log_unexpected THEN
gme_debug.put_line ( 'Exiting due to error exception in '
|| g_pkg_name
|| '.'
|| l_api_name);
SELECT d.*
FROM gme_material_details d,mtl_system_items_b i
WHERE d.batch_id = p_batch_rec.batch_id
AND d.line_type = -1
AND d.material_requirement_date < SYSDATE + NVL(gme_common_pvt.g_rule_based_resv_horizon,p_timefence)
AND i. inventory_item_id = d.inventory_item_id
AND i.organization_id = d.organization_id
AND i.reservable_type = 1
AND d.phantom_type = 0
ORDER BY d.inventory_item_id,d.line_no; --bug 9852628, order by items so that concurrent users will not see any data discrepancies