[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
mdebug('Inside wms_txnrsn_actions_pub.Inadequate Quantity: Before update quantity ');
SELECT transaction_temp_id
INTO l_mmtt_id
FROM wms_dispatched_tasks
WHERE task_id=p_task_id;
SELECT inventory_item_id, locator_id,subinventory_code,revision,lot_number,
move_order_line_id, reservation_id, transaction_quantity,transaction_header_id
INTO l_item_id,l_locator_id, l_sub_code,l_revision,l_lot,
l_line_num, l_reservation_id,l_transaction_quantity,l_mmtt_header_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=l_mmtt_id;
SELECT uom_code
INTO l_trans_uom
FROM mtl_txn_request_lines
WHERE line_id = l_line_num;
mdebug('before update mo line');
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed-l_qty_diff_txn
WHERE line_id = l_line_num;
mdebug('after update mo line');
mdebug('before update mmtt');
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity - l_qty_diff_txn,
transaction_quantity = l_transaction_quantity - l_qty_diff_prim
where transaction_temp_id=l_mmtt_id;
mdebug('after update mmtt');
SELECT oe_header_id
INTO l_oe_header_id
FROM wsh_inv_delivery_details_v
WHERE move_order_line_id=l_line_num
AND ROWNUM = 1; -- bug fix 1837592, if the same mol being detailed to
l_cc_insert_flag VARCHAR2(1):='Y';
l_do_update_mmtt VARCHAR2(1); -- Bug : 6034090
SELECT organization_id,
standard_operation_id,
transaction_temp_id,
operation_plan_id,
move_order_line_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id=l_line_num
AND transaction_temp_id <> l_mmtt_id;
select primary_quantity
,lot_number
-- ,transaction_quantity
from mtl_transaction_lots_temp
where transaction_temp_id = p_temp_id;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_mmtt_id;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM
mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_mmtt_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
select move_order_type
INTO l_move_order_type
from mtl_txn_request_headers
where header_id=l_header_id;
mdebug('others exception in selecting move order type');
SELECT revision
,lot_number
,reservation_id
,primary_quantity
,transaction_uom
,transaction_quantity
,transaction_source_id
INTO l_revision
,l_lot
,l_reservation_id
,l_primary_qty
,l_mmtt_transaction_uom
,l_transaction_qty
,l_mso_header_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt_id;
SELECT serial_number_control_code, lot_control_code
INTO l_serial_control_code
,l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = l_organization_id;
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_mmtt_id;
/* update mtl_material_transactions_temp
set primary_quantity = 0
,transaction_quantity = 0
where transaction_temp_id = l_mmtt_id
and organization_id = l_organization_id;
SELECT 'N'
INTO l_do_update_mmtt
FROM mtl_transaction_reasons mtr
, wms_exceptions we
WHERE we.reason_id = mtr.reason_id
AND we.task_id = l_mmtt_id
AND mtr.workflow_process = 'WMS_CYC_COUNT'
AND mtr.reason_context_code IN ('CP','PP');
IF NVL(l_do_update_mmtt,'Y') = 'Y' THEN -- Bug : 5886105
UPDATE mtl_material_transactions_temp
SET primary_quantity = 0
,transaction_quantity = 0
WHERE transaction_temp_id = l_mmtt_id
AND organization_id = l_organization_id;
select count(*)
into l_msnt_cnt
from mtl_transaction_lots_temp mtlt
,mtl_serial_numbers_temp msnt
where mtlt.transaction_temp_id = l_mmtt_id
and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id;
select count(*)
into l_msnt_cnt
from mtl_serial_numbers_temp msnt
where transaction_temp_id = l_mmtt_id;
mdebug('within loop before update msn for from serial_number:'||l_fm_serial_number);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE inventory_item_id = l_item_id
AND current_organization_id = l_organization_id
AND serial_number BETWEEN l_fm_serial_number AND l_to_serial_number;
mdebug('within loop after update msn for to serial_number :' || l_to_serial_number);
mdebug('before delete msnt');
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_mmtt_id);
mdebug('after delete msnt');
update mtl_transaction_lots_temp
set primary_quantity = 0
,transaction_quantity = 0
where transaction_temp_id = l_mmtt_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE inventory_item_id = l_item_id
AND current_organization_id = l_organization_id
AND serial_number BETWEEN l_fm_serial_number AND l_to_serial_number;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_mmtt_id;
l_cc_insert_flag := 'F';
l_cc_insert_flag := 'F';
select quantity_detailed, line_status
,nvl(quantity_delivered,0)
into l_old_quantity_detailed
,l_line_status
,l_quantity_delivered
from mtl_txn_request_lines
where line_id = l_line_num;
select count(*)
into l_old_mmtt_cnt
from mtl_material_transactions_temp
where move_order_line_id = l_line_num;
mdebug('before update mol, the number of mmtt rows :' || l_old_mmtt_cnt);
mdebug('update move order line before calling allocation APIs');
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_mol_delta_qty
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE organization_id = l_organization_id
AND line_id = l_line_num;
mdebug('In J patchset update move order line');
UPDATE mtl_txn_request_lines
SET quantity_detailed = (nvl(quantity_detailed,0) -nvl(l_quantity_delivered,0)) - l_mol_delta_qty --bug3278170
, last_update_date = SYSDATE
, last_updated_by = l_user_id
, quantity_delivered = 0 --bug3278170
, quantity = quantity - nvl(l_quantity_delivered,0) --bug3278170
WHERE organization_id = l_organization_id
AND line_id = l_line_num;
update mtl_reservations
set detailed_quantity = detailed_quantity - l_lot_qty
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE organization_id = l_organization_id
AND reservation_id = l_reservation_id;
select primary_reservation_quantity
,detailed_quantity
into l_primary_reservation_quantity
,l_detailed_quantity
from mtl_reservations
WHERE organization_id = l_organization_id
AND reservation_id = l_reservation_id;
mdebug('after update mol, the detailed_quantity at reservation :'|| l_detailed_quantity);
mdebug('after update mol, the primary_quantity at reservation :' || l_primary_reservation_quantity);
select quantity_detailed,
to_account_id --BUG#3048061
into l_old_quantity_detailed,
l_to_account_id --BUG#3048061
from mtl_txn_request_lines
where line_id = l_line_num;
mdebug('after update mol, the quantity_detailed :' || l_old_quantity_detailed);
SELECT mtl_material_transactions_s.nextval
INTO v_header_id
FROM dual;
update mtl_txn_request_lines
set quantity_detailed = l_detailed_qty + l_quantity_delivered
where line_id = l_line_num
and organization_id = l_organization_id;
UPDATE mtl_material_transactions_temp
SET distribution_account_id = l_to_account_id
WHERE move_order_line_id = l_line_num;
SELECT sum(transaction_quantity)
INTO l_new_mmtt_qty
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_line_num;
UPDATE mtl_txn_request_lines
SET quantity = nvl(l_new_mmtt_qty,0) + l_quantity_delivered ,
quantity_detailed = nvl(l_new_mmtt_qty,0) + l_quantity_delivered ,
quantity_delivered = l_quantity_delivered
WHERE organization_id = l_organization_id
AND line_id = l_line_num;
select wms_exceptions_s.NEXTVAL INTO l_sequence from dual;
mdebug('Inserting into exceptions');
INSERT INTO wms_exceptions(
TASK_ID,
SEQUENCE_NUMBER,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PERSON_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
INVENTORY_LOCATION_ID,
REASON_ID,
DISCREPANCY_TYPE,
SUBINVENTORY_CODE,
LOT_NUMBER,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
created_by,
transaction_header_id,
lpn_id
)
VALUES(p_mmtt_id,
l_sequence,
p_organization_id,
p_item_id,
p_user_id,
Sysdate,
Sysdate,
p_locator_id,
p_reason_id,
p_discrepancy_type,
p_subinventory_code,
p_lot_number,
p_revision,
Sysdate,
FND_GLOBAL.user_id,--p_user_id,Bug:2672785
Sysdate,
FND_GLOBAL.user_id,--p_user_id,Bug:2672785
p_mmtt_id,
p_lpn_id);
UPDATE mtl_material_transactions_temp
SET reason_id = p_reason_id
WHERE transaction_header_id = p_mmtt_id;
l_return_err := 'Insert into WMS_Exceptions failed'||
substrb(sqlerrm,1,55);
EXCEPT in case of BULK, there will be multiple MMTTs selected for the given temp_id
-- it should be called only for qty exceptions where picked quantity < suggested quantity
-- and not for overpicked qty
3. CURTAIL PICK for all children of BULK- */
PROCEDURE cleanup_task(
p_temp_id IN NUMBER
, p_qty_rsn_id IN NUMBER
, p_user_id IN NUMBER
, p_employee_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2)
IS
l_mmtt_msg_cnt NUMBER;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = l_org_id
AND mmtt.inventory_item_id = l_item_id
AND mmtt.subinventory_code = l_sub
AND mmtt.locator_id = l_loc
AND mmtt.transaction_temp_id <> p_temp_id
AND mmtt.parent_line_id IS NULL -- Bug# 5760606 - add condition so only non bulk tasks are considered
-- without the condition curtail pick for bulk pick will fail since this cursor picks up child mmtt lines
AND mmtt.item_lot_control_code = 1
AND mmtt.item_serial_control_code in (1,6)
AND NOT EXISTS (
SELECT 1 FROM wms_dispatched_tasks
WHERE transaction_temp_id= mmtt.transaction_temp_id
AND status in (4,9)) ;
select organization_id,inventory_item_id,subinventory_code,locator_id
into l_org_id,l_item_id,l_sub,l_loc
from mtl_material_transactions_temp
where transaction_temp_id = p_temp_id;
l_update_parent BOOLEAN := FALSE ; -- No need to call update_parent_mmtt in
SELECT mmtt.transaction_header_id
, mmtt.transaction_temp_id
, mmtt.parent_line_id --For checking bulk task
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.revision
, mmtt.lot_number
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.move_order_line_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id)
UNION ALL
SELECT mmtt.transaction_header_id
, mmtt.transaction_temp_id
, mmtt.parent_line_id --For checking bulk task
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.revision
, mmtt.lot_number
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.move_order_line_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
SELECT mtrh.move_order_type
, mtrl.txn_source_id
, mtrl.txn_source_line_id
, mtrl.reference_id
, mtrl.quantity
, mtrl.uom_code
, mtrl.quantity_delivered
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_mo_line_id
AND mtrh.header_id = mtrl.header_id;
SELECT COUNT(*)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_id
AND mmtt.transaction_temp_id <> l_txn_temp_id
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id);
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = p_qty_rsn_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
wms_insert_wdth_pvt.insert_into_wdth
(x_return_status => x_return_status,
p_txn_header_id => 0,
p_transaction_temp_id => p_temp_id,
p_transaction_batch_id => NULL,
p_transaction_batch_seq => NULL,
p_transfer_lpn_id => NULL,
p_status => 11); -- aborted
DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = p_temp_id;
mdebug ('NO WDT TO DELETE' );
mdebug ('Calling INV_TRX_UTIL_PUB.delete_transaction ' );
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 => l_txn_temp_id
,p_update_parent => l_update_parent
);
mdebug('CLEANUP_TASK: Before we update MO and delete MMTT, we need to update reservation ');
SELECT nvl(mmtt.reservation_id,-1) , mr.primary_reservation_quantity ,
mr.reservation_quantity, mr.primary_uom_code , mr.reservation_uom_code
INTO l_reservation_id , l_pri_rsv_qty, l_rsv_qty , l_pri_rsv_uom, l_rsv_uom
FROM mtl_material_transactions_temp mmtt , mtl_reservations mr
WHERE mmtt.transaction_temp_id = l_txn_temp_id
AND mr.reservation_id = mmtt.reservation_id ;
mdebug('CLEANUP_TASK: Calling update_reservation api : ' );
inv_reservation_pub.update_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_original_rsv_rec => l_old_upd_resv_rec
, p_to_rsv_rec => l_new_upd_resv_rec
, p_original_serial_number => l_upd_dummy_sn
, p_to_serial_number => l_upd_dummy_sn
, p_validation_flag => fnd_api.g_true
);
mdebug('CLEANUP_TASK: return of update_reservation api : ' || x_return_status);
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 => l_txn_temp_id
,p_update_parent => l_update_parent
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_txn_qty
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE line_id = l_mo_line_id;
DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = p_temp_id;
mdebug ('NO WDT TO DELETE' );
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_delivered
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE line_id = l_mo_line_id;
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 => l_txn_temp_id
,p_update_parent => l_update_parent
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_delivered
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE line_id = l_mo_line_id;
mdebug('Now calling delete transaction for parent line');
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 => l_parent_line_id
,p_update_parent => l_update_parent
);
mdebug('Inserting into exceptions', 'PROCESS_EXCEPTIONS');
l_return_err := 'Insert into WMS_Exceptions failed'|| substrb(sqlerrm,1,55);