[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(lot_control_code,1)lot_control_code,nvl(serial_number_control_code,1) serial_number_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_organization_id;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id=p_source_task_id;
l_insert_lot NUMBER;
/*Calling API INV_TRX_UTIL_PUB.copy_insert_line_trx to create the child MMTT. This is a new API
in INVTRUS.pls,hence dependency with version 115. */
IF (l_debug=1) THEN
print_debug('Calling INV_TRX_UTIL_PUB.copy_insert_line_trx to create child MMTT',l_module_name,9);
Update MMTT.WMS_TASK_TYPE to 'Inspect' for both parent and child tasks.*/
IF (p_operation_type_id=G_OP_TYPE_INSPECT) THEN
IF (l_debug=1) THEN
print_debug('Operation is inspect,hence setting task type to Inspect',l_module_name,9);
INV_TRX_UTIL_PUB.copy_insert_line_trx
(
x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,x_new_txn_temp_id => x_source_task_id
,p_transaction_temp_id => p_source_task_id
,p_organization_id => p_document_rec.organization_id
,p_subinventory_code => FND_API.G_MISS_CHAR
,p_locator_id => FND_API.G_MISS_NUM
,p_parent_line_id => p_source_task_id
,p_wms_task_type => l_wms_task_type
,p_operation_plan_id => p_subsequent_op_plan_id
);
INV_TRX_UTIL_PUB.copy_insert_line_trx
(
x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,x_new_txn_temp_id => x_source_task_id
,p_transaction_temp_id => p_source_task_id
,p_organization_id => p_document_rec.organization_id
,p_subinventory_code => FND_API.G_MISS_CHAR
,p_locator_id => FND_API.G_MISS_NUM
,p_parent_line_id => p_source_task_id
,p_wms_task_type => l_wms_task_type
);
/*Update MMTT record where transaction_temp_ID = P_source_task_ID.
Null out move_order_line_ID and lpn_ID.*/
UPDATE mtl_material_transactions_temp
SET lpn_id = NULL,
move_order_line_id = NULL,
wms_task_type = l_wms_task_type
WHERE transaction_temp_id = p_source_task_id;
print_debug('Updated Parent MMTT nulling LPN Id,MOL Id',l_module_name,9);
/*and Call INV_TRX_UTIL_PUB.INSERT_LOT_TRX passing the appropriate parameters
from the record variable to insert MTLT record.*/
IF (l_debug=1) THEN
print_debug('Calling Insert_lot_trx to insert child MTLT records',l_module_name,9);
l_insert_lot:=INV_TRX_UTIL_PUB.INSERT_LOT_TRX
(p_trx_tmp_id => x_source_task_id,
p_user_id => FND_GLOBAL.USER_ID ,
p_lot_number => l_mtlt_rec.lot_number ,
p_trx_qty => l_mtlt_rec.transaction_quantity,
p_pri_qty => l_mtlt_rec.primary_quantity,
p_exp_date => l_mtlt_rec.LOT_EXPIRATION_DATE,
p_description => l_mtlt_rec.DESCRIPTION,
p_vendor_name => l_mtlt_rec.VENDOR_NAME ,
p_supplier_lot_number => l_mtlt_rec.SUPPLIER_LOT_NUMBER,
p_origination_date => l_mtlt_rec.ORIGINATION_DATE,
p_date_code => l_mtlt_rec.DATE_CODE,
p_grade_code => l_mtlt_rec.GRADE_CODE,
p_change_date => l_mtlt_rec.CHANGE_DATE,
p_maturity_date => l_mtlt_rec.MATURITY_DATE,
p_status_id => l_mtlt_rec.STATUS_ID ,
p_retest_date => l_mtlt_rec.RETEST_DATE,
p_age => l_mtlt_rec.age,
p_item_size => l_mtlt_rec.item_size,
p_color => l_mtlt_rec.color,
p_volume => l_mtlt_rec.volume,
p_volume_uom => l_mtlt_rec.volume_uom,
p_place_of_origin => l_mtlt_rec.place_of_origin,
p_best_by_date => l_mtlt_rec.best_by_date,
p_length => l_mtlt_rec.length,
p_length_uom => l_mtlt_rec.length_uom,
p_recycled_content => l_mtlt_rec.recycled_content,
p_thickness => l_mtlt_rec.thickness,
p_thickness_uom => l_mtlt_rec.thickness_uom,
p_width => l_mtlt_rec.width,
p_width_uom => l_mtlt_rec.width_uom,
p_curl_wrinkle_fold => l_mtlt_rec.curl_wrinkle_fold,
p_lot_attribute_category => l_mtlt_rec.lot_attribute_category,
p_c_attribute1 => l_mtlt_rec.c_attribute1,
p_c_attribute2 => l_mtlt_rec.c_attribute2,
p_c_attribute3 => l_mtlt_rec.c_attribute3,
p_c_attribute4 => l_mtlt_rec.c_attribute4,
p_c_attribute5 => l_mtlt_rec.c_attribute5,
p_c_attribute6 => l_mtlt_rec.c_attribute6,
p_c_attribute7 => l_mtlt_rec.c_attribute7,
p_c_attribute8 => l_mtlt_rec.c_attribute8,
p_c_attribute9 => l_mtlt_rec.c_attribute9,
p_c_attribute10 => l_mtlt_rec.c_attribute10,
p_c_attribute11 => l_mtlt_rec.c_attribute11,
p_c_attribute12 => l_mtlt_rec.c_attribute12,
p_c_attribute13 => l_mtlt_rec.c_attribute13,
p_c_attribute14 => l_mtlt_rec.c_attribute14,
p_c_attribute15 => l_mtlt_rec.c_attribute15,
p_c_attribute16 => l_mtlt_rec.c_attribute16,
p_c_attribute17 => l_mtlt_rec.c_attribute17,
p_c_attribute18 => l_mtlt_rec.c_attribute18,
p_c_attribute19 => l_mtlt_rec.c_attribute19,
p_c_attribute20 => l_mtlt_rec.c_attribute20,
p_d_attribute1 => l_mtlt_rec.d_attribute1,
p_d_attribute2 => l_mtlt_rec.d_attribute2,
p_d_attribute3 => l_mtlt_rec.d_attribute3,
p_d_attribute4 => l_mtlt_rec.d_attribute4,
p_d_attribute5 => l_mtlt_rec.d_attribute5,
p_d_attribute6 => l_mtlt_rec.d_attribute6,
p_d_attribute7 => l_mtlt_rec.d_attribute7,
p_d_attribute8 => l_mtlt_rec.d_attribute8,
p_d_attribute9 => l_mtlt_rec.d_attribute9,
p_d_attribute10 => l_mtlt_rec.d_attribute10,
p_n_attribute1 => l_mtlt_rec.n_attribute1,
p_n_attribute2 => l_mtlt_rec.n_attribute2,
p_n_attribute3 => l_mtlt_rec.n_attribute3,
p_n_attribute4 => l_mtlt_rec.n_attribute4,
p_n_attribute5 => l_mtlt_rec.n_attribute5,
p_n_attribute6 => l_mtlt_rec.n_attribute6,
p_n_attribute7 => l_mtlt_rec.n_attribute7,
p_n_attribute8 => l_mtlt_rec.n_attribute8,
p_n_attribute9 => l_mtlt_rec.n_attribute9,
p_n_attribute10 => l_mtlt_rec.n_attribute10,
x_ser_trx_id => l_ser_trx_id,
x_proc_msg => l_proc_msg,
p_territory_code => l_mtlt_rec.territory_code,
p_vendor_id => l_mtlt_rec.vendor_id);
IF (l_insert_lot<>0) THEN
IF (l_debug=1) THEN
print_debug('Failed to insert lots',l_module_name,1);
* Activate_operation_instance. This API updates MMTT records and
* with the suggested subinventory,locator
* @param x_return_status -Return Status
* @param x_msg_data -Returns Message Data
* @param x_msg_count -Returns the message count
* @param x_error_code -Returns Appropriate error code in case of any error.
* @param p_source_task_id -Identifier of the document record.
* @param p_update_param_rec -Record Type of WMS_ATF_RUNTIME_PUB_APIS.DEST_PARAM_REC_TYPE
*
**/
PROCEDURE ACTIVATE(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
, x_msg_count OUT NOCOPY NUMBER
, x_error_code OUT NOCOPY NUMBER
, p_source_task_id IN NUMBER
, p_update_param_rec IN DEST_PARAM_REC_TYPE
, p_document_rec IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
)IS
/* CURSOR c_locator_type(v_location_id NUMBER,v_org_id NUMBER) IS
SELECT Nvl(inventory_location_type, 3)
FROM mtl_item_locations
WHERE inventory_location_id=v_location_id
AND organization_id=v_org_id;
SELECT Nvl(subinventory_type, 1)
FROM mtl_secondary_inventories
WHERE secondary_inventory_name=v_sub_code
AND organization_id=v_org_id;
SELECT nvl(transfer_subinventory,subinventory_code) subinventory_code
,nvl(transfer_to_location,locator_id) locator_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=v_txn_temp_id;
/*Whenever we update transfer_to_location and transfer_subinventory, we need to aware that we are
* dealing with three types of MMTTS:
* 1.Receiving transfer: Transaction type 'PO receipt, interorg transfer receipt,
RMA receipt', destination locator type 'receiving'.
For this type of MMTT, destination is populated in 'transfer_ sub/locator' fields.
2.Receiving deliver: Transaction type 'PO receipt', destination locator type 'inventory'.
For this type of MMTT, destination is populated in 'sub/locator' fields.
3.Inventory transfer: Transaction type 'Move order transfer'. For this type of MMTT,
destination is populated in 'transfer_ sub/locator' fields.
Transaction type is referred in the document for simplicity sake,
check in the code should be based on transaction_action_ID and transaction_source_type_ID.
Query for destination locator is actually simpler, if transfer_to_location and
transfer_subinventory are NULL, we know that destination is stored in subinventory_code and
locator_ID columns.
1. Query locator_type for P_UPDATE_PARAM_REC.LOCATOR_ID
2. Update MMTT with P_UPDATE_PARAM_REC
*/
/*Fetching the subinventory Type*/
/* Checking if the dest sub,locator and cartonization Id have been suggested. Returning success
* if they arent populated.
*/
IF p_update_param_rec.SUG_SUB_CODE IS NOT NULL OR p_update_param_rec.SUG_LOCATION_ID IS NOT NULL
OR p_update_param_rec.cartonization_id IS NOT NULL THEN
l_progress:=25;
OPEN c_sub_type(p_update_param_rec.sug_sub_code,p_document_rec.organization_id);
print_debug('Deliver transaction, update sub/loc.',l_module_name,1);
UPDATE MTL_material_transactions_temp
SET subinventory_code = p_update_param_rec.sug_sub_code,
locator_id = p_update_param_rec.sug_location_id,
cartonization_id = p_update_param_rec.cartonization_id
WHERE transaction_temp_id = p_source_task_id
AND organization_id = p_document_rec.organization_id;
print_debug('Transfer transaction, update transfer sub/loc.',l_module_name,1);
UPDATE MTL_material_transactions_temp
SET transfer_subinventory = p_update_param_rec.sug_sub_code,
transfer_to_location = p_update_param_rec.sug_location_id,
cartonization_id = p_update_param_rec.cartonization_id
WHERE transaction_temp_id = p_source_task_id
AND organization_id = p_document_rec.organization_id;
/*If the suggestion is not system suggested locator then Update suggested capacity*/
l_progress:=40;
IF l_orig_sugges.subinventory_code<>p_update_param_rec.sug_sub_code OR
l_orig_sugges.locator_id<>p_update_param_rec.sug_location_id THEN
IF (l_debug=1) THEN
print_debug('Suggested Capacity of Locator needs to be updated',l_module_name,9);
IF (p_update_param_rec.sug_location_id IS NOT NULL) THEN
inv_loc_wms_utils.update_loc_sugg_cap_wo_empf(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_document_rec.organization_id
, p_inventory_location_id => p_update_param_rec.sug_location_id
, p_inventory_item_id => p_document_rec.inventory_item_id
, p_primary_uom_flag => 'Y'
, p_transaction_uom_code => NULL
, p_quantity => p_document_rec.primary_quantity
);
UPDATE mtl_txn_request_lines
SET backorder_delivery_detail_id = NULL
, to_subinventory_code = NULL
, to_locator_id = NULL
, crossdock_type = NULL
WHERE line_id = p_move_order_line_id
returning organization_id INTO l_organization_id;
SELECT released_status
INTO l_cur_rel_status
FROM wsh_delivery_details
WHERE delivery_detail_id = p_backorder_delivery_detail_id;
l_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 => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_detail_info_tab => l_detail_info_tab
, p_in_rec => l_in_rec
, x_out_rec => l_out_rec
);
( 'Error status from WSH_INTERFACE_GRP.Create_Update_Delivery_Detail: '
|| l_return_status
, l_module_name, 1
);
print_debug('Successfully updated the WDD record to status ''R'''
, l_module_name, 1);
print_debug('Before calling inv_reservation_pvt.delete_reservation. ', l_module_name,1);
inv_reservation_pvt.delete_reservation
(
p_api_version_number=> l_api_version_number
, 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_original_serial_number => l_original_serial_number
, p_validation_flag => NULL
);
print_debug('After calling inv_reservation_pvt.delete_reservation. ', l_module_name,1);
print_debug('inv_reservation_pvt.delete_reservation. returned with x_return_status = '||l_return_status, l_module_name,1);
SELECT
mmtt.lpn_id,
mmtt.transfer_lpn_id,
mmtt.content_lpn_id,
mmtt.operation_plan_id,
Nvl(msi.subinventory_type, 1) subinventory_type,
mmtt.move_order_line_id,
mmtt.operation_seq_num,
mmtt.repetitive_line_id,
mmtt.primary_quantity,
mmtt.inventory_item_id,
mmtt.reason_id,
mol.crossdock_type,
mol.backorder_delivery_detail_id,
mmtt.transfer_to_location,
mmtt.locator_id
FROM mtl_material_transactions_temp mmtt,
mtl_secondary_inventories msi,
mtl_txn_request_lines mol
WHERE mmtt.transaction_temp_id = p_source_task_id
AND Nvl(mmtt.transfer_subinventory, mmtt.subinventory_code) = msi.secondary_inventory_name (+)
AND mmtt.organization_id = msi.organization_id (+)
AND mol.line_id = mmtt.move_order_line_id;
SELECT inventory_location_type
FROM mtl_item_locations
WHERE inventory_location_id=v_location_id
AND organization_id=v_org_id;
SELECT subinventory_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = v_subinventory_code
AND organization_id = v_org_id;
print_debug('p_document_rec.parent_line_id is not null, therefore need to delete parent MMTT record',l_module_name,4);
print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
inv_trx_util_pub.delete_transaction
(x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_transaction_temp_id => p_document_rec.parent_line_id
, p_update_parent => FALSE
);
print_debug('After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
print_debug('inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT backorder_delivery_detail_id
INTO l_backorder_delivery_detail_id
FROM mtl_txn_request_lines
WHERE line_id = p_document_rec.move_order_line_id;
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = l_mmtt_data_rec.reason_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
print_debug('This is a receipt transaction, need to delete MMTT. '||l_return_status,l_module_name,4);
SELECT uom_code
INTO l_mol_uom_code
FROM mtl_txn_request_lines
WHERE line_id = p_document_rec.move_order_line_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom,
lpn_id =(SELECT Nvl(mmtt.transfer_lpn_id, mmtt.content_lpn_id)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_document_rec.transaction_temp_id),
wms_process_flag = 1 -- Bug 4657716
WHERE line_id = p_document_rec.move_order_line_id;
print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
inv_trx_util_pub.delete_transaction
(x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_transaction_temp_id => p_source_task_id
, p_update_parent => FALSE
);
print_debug('Drop - After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
print_debug('inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT subinventory_type
INTO l_dest_subinventory_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_document_rec.transfer_subinventory
AND organization_id = p_document_rec.organization_id;
SELECT subinventory_type
INTO l_dest_subinventory_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_document_rec.subinventory_code
AND organization_id = p_document_rec.organization_id;
SELECT uom_code
INTO l_mol_uom_code
FROM mtl_txn_request_lines
WHERE line_id = p_document_rec.move_order_line_id;
print_debug('Before update MOL.quantity_delivered. l_qty_in_mol_uom = '||l_qty_in_mol_uom, l_module_name,4);
UPDATE mtl_txn_request_lines
SET line_status = g_to_status_closed,
quantity_delivered = Nvl(quantity_delivered, 0) + l_qty_in_mol_uom --,
-- lpn_id =(SELECT Nvl(mmtt.transfer_lpn_id, mmtt.content_lpn_id)
-- FROM mtl_material_transactions_temp mmtt
-- WHERE mmtt.transaction_temp_id = p_document_rec.transaction_temp_id)
WHERE line_id = p_document_rec.move_order_line_id;
SELECT uom_code
INTO l_mol_uom_code
FROM mtl_txn_request_lines
WHERE line_id = p_document_rec.move_order_line_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom
WHERE line_id = p_document_rec.move_order_line_id;
print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
inv_trx_util_pub.delete_transaction
(x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_transaction_temp_id => p_source_task_id
, p_update_parent => FALSE
);
print_debug('Inspect - After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
print_debug('inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT subinventory_type
INTO l_dest_subinventory_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_document_rec.transfer_subinventory
AND organization_id = p_document_rec.organization_id;
SELECT subinventory_type
INTO l_dest_subinventory_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_document_rec.subinventory_code
AND organization_id = p_document_rec.organization_id;
UPDATE mtl_txn_request_lines
SET lpn_id = Nvl(l_mmtt_data_rec.transfer_lpn_id, l_mmtt_data_rec.content_lpn_id)
WHERE line_id = p_document_rec.move_order_line_id;
print_debug('Before calling inv_trx_util_pub.copy_insert_line_trx with following parameters: ',l_module_name,4);
INV_TRX_UTIL_PUB.copy_insert_line_trx
(
x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,x_new_txn_temp_id => x_source_task_id
,p_transaction_temp_id => p_source_task_id
,p_subinventory_code => l_new_subinventory_code
,p_locator_id => l_new_locator_id
,p_to_subinventory_code => l_new_transfer_to_sub
,p_to_locator_id => l_new_transfer_to_loc
,p_txn_type_id => l_new_txn_type_id
,p_txn_action_id => l_new_txn_action_id
,p_txn_source_type_id => l_new_txn_source_type_id
,p_wms_task_type => l_new_wms_task_type_id
,p_lpn_id => l_new_lpn_id
,p_transfer_lpn_id => fnd_api.g_miss_num -- null out transfer_lpn_id
,p_content_lpn_id => fnd_api.g_miss_num -- null out content_lpn_id
,p_operation_plan_id => p_subsequent_op_plan_id
,p_transaction_status => 2 -- Bug 5156015
);
INV_TRX_UTIL_PUB.copy_insert_line_trx
(
x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,x_new_txn_temp_id => x_source_task_id
,p_transaction_temp_id => p_source_task_id
,p_subinventory_code => l_new_subinventory_code
,p_locator_id => l_new_locator_id
,p_to_subinventory_code => l_new_transfer_to_sub
,p_to_locator_id => l_new_transfer_to_loc
,p_txn_type_id => l_new_txn_type_id
,p_txn_action_id => l_new_txn_action_id
,p_txn_source_type_id => l_new_txn_source_type_id
,p_wms_task_type => l_new_wms_task_type_id
,p_lpn_id => l_new_lpn_id
,p_transfer_lpn_id => fnd_api.g_miss_num -- null out transfer_lpn_id
,p_content_lpn_id => fnd_api.g_miss_num -- null out content_lpn_id
,p_transaction_status => 2 -- Bug 5156015
);
print_debug('After calling inv_trx_util_pub.copy_insert_line_trx: ',l_module_name,4);
print_debug('Error occured while calling inv_trx_util_pub.copy_insert_line_trx',l_module_name,4);
SELECT lot_control_code,
serial_number_control_code
INTO l_lot_control_code,
l_serial_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_document_rec.inventory_item_id
AND organization_id = p_document_rec.organization_id;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = x_source_task_id
WHERE transaction_temp_id = p_source_task_id;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = x_source_task_id
WHERE transaction_temp_id = p_source_task_id;
print_debug('This is a receipt transaction, need to delete MMTT. '||l_return_status,l_module_name,4);
print_debug('Before calling inv_trx_util_pub.delete_transaction with following parameters: ',l_module_name,4);
inv_trx_util_pub.delete_transaction
(x_return_status => l_return_status
, x_msg_data => l_msg_data
, x_msg_count => l_msg_count
, p_transaction_temp_id => p_source_task_id
, p_update_parent => FALSE
);
print_debug('Not last operation - Drop - After calling inv_trx_util_pub.delete_transaction.',l_module_name,4);
print_debug('Not last operation - Drop - inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
SELECT
transfer_subinventory,
transfer_to_location,
subinventory_code,
locator_id,
cartonization_id,
transaction_action_id,
transaction_source_type_id,
organization_id,
inventory_item_id,
primary_quantity
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_source_task_id;
SELECT mol.lpn_id
INTO l_mol_lpn_id
FROM mtl_txn_request_lines mol
WHERE mol.line_id =
(SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_source_task_id);
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = Decode(operation_plan_id, NULL, transfer_subinventory, NULL),
transfer_to_location = Decode(operation_plan_id, NULL, transfer_to_location, NULL),
cartonization_id = Decode(operation_plan_id, NULL, cartonization_id, NULL),
transfer_lpn_id = NULL,
content_lpn_id = NULL,
lpn_id = l_mol_lpn_id,
wms_task_type = Decode(wms_task_type, -1, g_wms_task_type_putaway, wms_task_type) -- revert it back to putaway if it has been set to -1 from complete_putaway
WHERE transaction_temp_id = p_source_task_id;
UPDATE mtl_material_transactions_temp
SET subinventory_code = Decode(operation_plan_id, NULL, subinventory_code, NULL),
locator_id = Decode(operation_plan_id, NULL, locator_id, NULL),
cartonization_id = Decode(operation_plan_id, NULL, cartonization_id, NULL),
transfer_lpn_id = NULL,
content_lpn_id = NULL,
lpn_id = l_mol_lpn_id,
wms_task_type = Decode(wms_task_type, -1, g_wms_task_type_putaway, wms_task_type) -- revert it back to putaway if it has been set to -1 from complete_putaway
WHERE transaction_temp_id = p_source_task_id;
* This API deletes the parent MMTT record, deletes the child MMTT record,
* update and close the move order line as appropriate.
*
*
* @param x_return_status -Return Status
* @param x_msg_data -Returns Message Data
* @param x_msg_count -Returns the message count
* @param p_source_task_id -Identifier of the document record.
*
**/
PROCEDURE cancel
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
, x_msg_count OUT NOCOPY NUMBER
, p_source_task_id IN NUMBER
, p_retain_mmtt IN VARCHAR2 DEFAULT 'N'
, p_mmtt_error_code IN VARCHAR2 DEFAULT NULL
, p_mmtt_error_explanation IN VARCHAR2 DEFAULT NULL
)
IS
l_module_name VARCHAR2(30) := 'CANCEL';
SELECT
mmtt.transaction_temp_id,
mmtt.move_order_line_id,
mmtt.parent_line_id,
mmtt.transaction_action_id,
mmtt.transaction_source_type_id,
mmtt.transaction_uom,
mmtt.transaction_quantity,
mmtt.inventory_item_id,
mol.uom_code mol_uom_code,
mol.backorder_delivery_detail_id,
mol.crossdock_type,
mmtt.repetitive_line_id,
mmtt.operation_seq_num,
mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol
WHERE transaction_temp_id = p_source_task_id
AND mmtt.move_order_line_id = mol.line_id;
SELECT
nvl(transfer_to_location,locator_id) locator_id,
primary_quantity,
organization_id,
inventory_item_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=v_txn_temp_id;
SELECT status
FROM wms_dispatched_tasks
WHERE transaction_temp_id=p_source_task_id
AND task_type=g_wms_task_type_putaway;
print_debug('Delete parent - Before calling INV_TRX_UTIL_PUB.Delete_transaction with following parameters: ' ,l_module_name,4);
print_debug('p_update_parent => '|| 'FALSE',l_module_name,4);
INV_TRX_UTIL_PUB.Delete_transaction
(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_transaction_temp_id => l_mmtt_data_rec.parent_line_id,
p_update_parent => FALSE);
print_debug('Delete parent - After calling INV_TRX_UTIL_PUB.Delete_transaction.' ,l_module_name,4);
print_debug('Delete parent - inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
print_debug('Task is loaded... do not update quantity_detailed. '||l_return_status,l_module_name,4);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom
WHERE line_id = l_mmtt_data_rec.move_order_line_id;
print_debug('Updated quantity_detailed. '||l_return_status,l_module_name,4);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_qty_in_mol_uom,
line_status = g_to_status_closed
WHERE line_id = l_mmtt_data_rec.move_order_line_id;
print_debug('Do not delete child MMTT, retain and update sub/loc/error',l_module_name,4);
SELECT wlpn.subinventory_code
, wlpn.locator_id
INTO l_subinventory_code
, l_locator_id
FROM wms_license_plate_numbers wlpn
, mtl_material_transactions_temp mmtt
WHERE mmtt.lpn_id = wlpn.lpn_id
AND mmtt.transaction_temp_id = p_source_task_id;
UPDATE mtl_material_transactions_temp mmtt
SET (subinventory_code,
locator_id,
error_code,
error_explanation,
parent_line_id,
operation_plan_id) =
(SELECT
l_subinventory_code,
l_locator_id,
p_mmtt_error_code,
p_mmtt_error_explanation,
NULL,
NULL
FROM dual
)
WHERE mmtt.transaction_temp_id = p_source_task_id;
print_debug('Delete child - Before calling INV_TRX_UTIL_PUB.Delete_transaction with following parameters: ' ,l_module_name,4);
print_debug('p_update_parent => '|| 'FALSE',l_module_name,4);
INV_TRX_UTIL_PUB.Delete_transaction
(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_transaction_temp_id => p_source_task_id,
p_update_parent => FALSE);
print_debug('Delete child - After calling INV_TRX_UTIL_PUB.Delete_transaction.' ,l_module_name,4);
print_debug('Delete child - inv_trx_util_pub.delete_transaction finished with error. l_return_status = ' || l_return_status,l_module_name,4);
* This API deletes the parent MMTT record, clear several fields of the child MMTT record,
*
*
* @param x_return_status -Return Status
* @param x_msg_data -Returns Message Data
* @param x_msg_count -Returns the message count
* @param p_source_task_id -Identifier of the document record.
* @param p_document_rec -MMTT PL/SQL record
*
**/
PROCEDURE ABORT
(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY fnd_new_messages.MESSAGE_TEXT%TYPE
, x_msg_count OUT NOCOPY NUMBER
, p_document_rec IN mtl_material_transactions_temp%ROWTYPE
, p_plan_orig_sub_code IN VARCHAR2
, p_plan_orig_loc_id IN NUMBER
, p_for_manual_drop IN BOOLEAN DEFAULT FALSE
)
IS
l_progress NUMBER;
/* null out the Operation Plan Id ,parent Line Id and delete the Parent transaction temp Id*/
/*Call table hanlder to delete MMTT/MSNT/MTLT for the Parent MMTT*/
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_document_rec.parent_line_id,
p_update_parent => FALSE);
print_debug('Return status from Delete Transaction'||x_return_status,l_module_name,9);
print_debug('Also update destination sub/loc Src Document record to that of the original task. ',l_module_name,9);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET operation_plan_id = NULL ,
parent_line_id = NULL,
subinventory_code = p_plan_orig_sub_code,
locator_id = p_plan_orig_loc_id
WHERE transaction_temp_id = p_document_rec.transaction_temp_id;
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET operation_plan_id = NULL ,
parent_line_id = NULL,
transfer_subinventory = p_plan_orig_sub_code,
transfer_to_location = p_plan_orig_loc_id
WHERE transaction_temp_id = p_document_rec.transaction_temp_id;