[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
, secondary_quantity -- bug 8197499
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 DISTINCT(NVL(lpn_id,-999))
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.organization_id = l_organization_id
AND moqd.subinventory_code = p_subinventory_code
AND moqd.locator_id = p_locator_id
AND moqd.inventory_item_id = l_item_id
ORDER BY 1 DESC;
SELECT DISTINCT(NVL(moqd.lpn_id,-999))
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.organization_id = l_organization_id
AND moqd.subinventory_code = p_subinventory_code
AND moqd.locator_id = p_locator_id
AND moqd.inventory_item_id = l_item_id
AND moqd.lot_number = l_lot
ORDER BY 1 DESC;
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
,item_primary_uom_code
,transaction_uom
,transaction_quantity
,transaction_source_id
,allocated_lpn_id
,secondary_transaction_quantity --bug 8197499
,secondary_uom_code --bug 8197499
,parent_line_id --bug 8460179
,fulfillment_base
INTO l_revision
,l_lot
,l_reservation_id
,l_primary_qty
,l_primary_uom
,l_mmtt_transaction_uom
,l_transaction_qty
,l_mso_header_id
,l_allocated_lpn_id --bug 7504490 - Fetching allocated_lpn_id from MMTT
,l_secondary_quantity --bug 8197499
,l_sec_uom_code --bug 8197499
,l_parent_line_id --bug 8460179
,l_fulfillment_base
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;
IF l_allocated_lpn_id IS NULL THEN /*9251210-delete the remaing reservation.*/
mdebug('Before calling:INV_RESERVATION_PVT.delete_reservation(),with res_id :'||l_new_rsv_id);
INV_RESERVATION_PVT.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_original_serial_number=> l_original_serial_number
, p_validation_flag => fnd_api.g_true
);
mdebug(' INV_RESERVATION_PVT.delete_reservation failed !!!');
mdebug('Before calling:INV_RESERVATION_PVT.delete_reservation(),with res_id :'||l_new_rsv_id);
INV_RESERVATION_PVT.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_original_serial_number=> l_original_serial_number
,p_validation_flag => fnd_api.g_true);
mdebug(' INV_RESERVATION_PVT.delete_reservation failed');
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_mmtt_id;
/* Bug 7504490 - Commented out the check for l_do_update_mmtt. The MMTT has to be
updated to 0 quantity for the quantity tree to fetch correct quantity to reserve */
UPDATE mtl_material_transactions_temp
SET primary_quantity = 0
,transaction_quantity = 0
,secondary_transaction_quantity = DECODE(secondary_uom_code,NULL,NULL,0)
WHERE transaction_temp_id IN (l_mmtt_id ,l_parent_line_id) --8460179 , added l_parent_line_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
, secondary_quantity = DECODE(secondary_quantity,NULL,NULL,0) --bug 8197499
WHERE transaction_temp_id IN ( l_mmtt_id ,l_parent_line_id) ; /*9251210*/
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';
l_cc_insert_flag := 'F';
l_cc_insert_flag := 'F';
SELECT quantity_detailed
, secondary_quantity_detailed
, line_status
, NVL(quantity_delivered,0)
, NVL(secondary_quantity_delivered,0)
INTO l_old_quantity_detailed
, l_old_sec_qty_detailed
, l_line_status
, l_quantity_delivered
, l_sec_qty_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');
mdebug('In J patchset update move order line');
UPDATE mtl_txn_request_lines
SET quantity_detailed = l_new_quantity_detailed
, secondary_quantity_detailed = l_new_sec_qty_detailed
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE organization_id = l_organization_id
AND line_id = l_line_num;
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;
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
, secondary_detailed_quantity = secondary_detailed_quantity - l_sec_lot_qty --bug 8197499
, 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_new_quantity_detailed, -- added for bug 9895550
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_new_quantity_detailed); -- added for bug 9895550
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
, secondary_quantity_detailed = DECODE(secondary_uom_code,NULL,NULL,
l_sec_detailed_qty + l_sec_qty_delivered) -- Bug 8312574
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;
l_insert_sub VARCHAR2(50); -- 11858770
l_insert_loc number; -- 11858770
select wms_exceptions_s.NEXTVAL INTO l_sequence from dual;
mdebug('Inserting into exceptions');
select transaction_type_id, transaction_action_id, transaction_source_type_id,
subinventory_code, locator_id
into l_txn_typ_id, l_txn_action_id, l_txn_src_type_id,
l_sub_code, l_loc_id
from mtl_material_transactions_temp
where transaction_temp_id = p_mmtt_id;
l_insert_sub := l_sub_code;
l_insert_loc := l_loc_id;
l_insert_sub := p_subinventory_code;
l_insert_loc := p_locator_id;
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,
l_insert_loc, -- 11858770
p_reason_id,
p_discrepancy_type,
l_insert_sub, -- 11858770
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 NVL(mmtt.revision, '@') = NVL(l_revision, '@') --Bug 7504490
AND NVL(mmtt.allocated_lpn_id, -1)= NVL(l_allocated_lpn_id, -1) --Bug 7504490
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 NOT EXISTS( /*Bug8304954-If no serial allocation, we should cleanup(only for non-lot case) */
SELECT 1 FROM MTL_SERIAL_NUMBERS_TEMP MSNT
WHERE MSNT.TRANSACTION_TEMP_ID= mmtt.transaction_temp_id --Should not have serials allocated.
)
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,revision,allocated_lpn_id
into l_org_id,l_item_id,l_sub,l_loc,l_revision,l_allocated_lpn_id
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 INV_TRX_UTIL_PUB
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
, mmtt.item_primary_uom_code
, mmtt.secondary_transaction_quantity
, mmtt.secondary_uom_code
, mmtt.fulfillment_base
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
, mmtt.item_primary_uom_code
, mmtt.secondary_transaction_quantity
, mmtt.secondary_uom_code
, mmtt.fulfillment_base
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
, mtrl.primary_quantity
, mtrl.secondary_quantity
, mtrl.secondary_uom_code
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
p_orig_transaction_temp_id => p_orig_temp_id); --Bug12812580
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.primary_uom_code
,mr.reservation_quantity
,mr.reservation_uom_code
,mr.secondary_reservation_quantity
,mr.secondary_uom_code
,mr.lot_number
,mr.organization_id
INTO l_reservation_id
,l_pri_rsv_qty
,l_pri_rsv_uom
,l_rsv_qty
,l_rsv_uom
,l_sec_rsv_qty
,l_sec_rsv_uom
,l_rsv_lot_num
,l_rsv_org_id
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
, secondary_quantity_detailed = DECODE(secondary_uom_code,NULL,NULL,
secondary_quantity_detailed - l_sec_txn_qty) -- Bug 8312574
, 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
, secondary_quantity_detailed =
DECODE(secondary_uom_code,NULL,NULL,secondary_quantity_delivered) -- Bug 8312574
, 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
, secondary_quantity_detailed =
DECODE(secondary_uom_code,NULL,NULL,secondary_quantity_delivered) -- Bug 8312574
, 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);