[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT delivery_detail_id, oe_header_id, oe_line_id, released_status
FROM wsh_inv_delivery_details_v
WHERE move_order_line_id = p_mo_line_rec.line_id
AND move_order_line_id IS NOT NULL
AND released_status = 'S';
DEBUG('Calling Update Shipping Attributes', 'BACKORDER_SOURCE');
wsh_interface.update_shipping_attributes(
p_source_code => 'INV'
, p_changed_attributes => l_shipping_attr
, x_return_status => x_return_status
);
DEBUG('Updated Shipping Attributes - Return Status = ' || x_return_status, 'BACKORDER_SOURCE');
SELECT 'N' FROM DUAL
WHERE EXISTS( SELECT 1
FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_rec.line_id
AND wdt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id)
AND wdt.status IN (4,9));
SELECT mtrh.move_order_type
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_mo_line_rec.line_id
AND mtrh.header_id = mtrl.header_id;
SELECT mmtt.transaction_temp_id
, ABS(mmtt.primary_quantity) primary_quantity
, ABS(mmtt.transaction_quantity) transaction_quantity
, ABS(mmtt.secondary_transaction_quantity) secondary_transaction_quantity
, mmtt.reservation_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_line_id
AND NOT EXISTS (SELECT 1 FROM mtl_material_transactions_temp t
WHERE t.parent_line_id = mmtt.transaction_temp_id)
FOR UPDATE NOWAIT;
delete_details(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_mmtt_info.transaction_temp_id
, p_move_order_line_id => p_line_id
, p_reservation_id => l_mmtt_info.reservation_id
, p_transaction_quantity => l_mmtt_info.transaction_quantity
, p_primary_trx_qty => l_mmtt_info.primary_quantity
, p_secondary_trx_qty => l_mmtt_info.secondary_transaction_quantity
);
UPDATE mtl_txn_request_lines
SET line_status = 5
, quantity_detailed = NVL(quantity_delivered,0)
, quantity = NVL(quantity_delivered,0)
WHERE line_id = p_line_id;
UPDATE mtl_txn_request_lines
SET line_status = 5
, quantity_detailed = NVL(quantity_delivered,0)
, secondary_quantity_detailed = NVL(secondary_quantity_delivered,0)
, quantity = NVL(quantity_delivered,0)
, secondary_quantity = NVL(secondary_quantity_delivered,0)
WHERE line_id = p_line_id;
PROCEDURE delete_details(
p_transaction_temp_id IN NUMBER
, p_move_order_line_id IN NUMBER
, p_reservation_id IN NUMBER
, p_transaction_quantity IN NUMBER
, p_primary_trx_qty IN NUMBER
, p_secondary_trx_qty IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
DEBUG('Transaction Temp ID = ' || p_transaction_temp_id, 'DELETE_DETAILS');
DEBUG('Move Order Line ID = ' || p_move_order_line_id, 'DELETE_DETAILS');
DEBUG('Transaction Qty = ' || p_transaction_quantity, 'DELETE_DETAILS');
DEBUG('Secondary Qty = ' || p_secondary_trx_qty, 'DELETE_DETAILS');
DEBUG('Reservation ID = ' || p_reservation_id, 'DELETE_DETAILS');
DEBUG('x_return_status = ' || x_return_status, 'DELETE_DETAILS');
DEBUG('l_error_code = ' || l_error_code, 'DELETE_DETAILS');
DEBUG('l_mtl_reservation_tbl_count = ' || l_mtl_reservation_tbl_count, 'DELETE_DETAILS');
DEBUG('g_retain_ato_profile = Y', 'DELETE_DETAILS');
SELECT 1, primary_uom_code
INTO l_ato_item, l_primary_uom
FROM mtl_system_items
WHERE replenish_to_order_flag = 'Y'
AND bom_item_type = 4
AND inventory_item_id = l_mtl_reservation_tbl(1).inventory_item_id
AND organization_id = l_mtl_reservation_tbl(1).organization_id;
DEBUG('l_ato_item = ' || l_ato_item, 'DELETE_DETAILS');
DEBUG('l_ato_item = 1', 'DELETE_DETAILS');
DEBUG('UOM Convert = ', 'DELETE_DETAILS');
DEBUG('call inv_reservation_pub.update_reservation = ', 'DELETE_DETAILS');
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_mtl_reservation_rec
, p_to_rsv_rec => l_mtl_reservation_tbl(1)
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
);
DEBUG('x_return_status' || x_return_status, 'DELETE_DETAILS');
DEBUG('Allocation UOM = ' || l_mmtt_rec.transaction_uom, 'DELETE_DETAILS');
DEBUG('Reservation UOM = ' || l_mtl_reservation_rec.reservation_uom_code, 'DELETE_DETAILS');
DEBUG('After UOM Conversion TxnQty = ' || l_umconvert_trans_quantity, 'DELETE_DETAILS');
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_mtl_reservation_rec
, p_to_rsv_rec => l_mtl_reservation_tbl(1)
, p_original_serial_number => l_original_serial_number
, p_to_serial_number => l_to_serial_number
);
DEBUG('x_return_status from inv_reservation_pub.update_reservation ' || x_return_status , 'DELETE_DETAILS');
debug('l_mmtt_rec.transaction_action_id = ' || l_mmtt_rec.transaction_action_id,'delete_details');
debug('l_mmtt_rec.transaction_status = ' || l_mmtt_rec.transaction_status,'delete_details');
DEBUG('call inv_trx_util_pub.delete_transaction ' , 'DELETE_DETAILS');
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => p_transaction_temp_id
);
DEBUG('x_return_status ' || x_return_status , 'DELETE_DETAILS');
fnd_msg_pub.add_exc_msg(g_pkg_name, 'DELETE_DETAILS');
END delete_details;