[Home] [Help]
19: SELECT mmtt.transaction_temp_id
20: , mmtt.transaction_type_id
21: , mmtt.move_order_line_id
22: , mol.line_status
23: FROM mtl_material_transactions_temp mmtt
24: , mtl_txn_request_lines mol
25: WHERE mmtt.transfer_lpn_id = p_lpn_id
26: AND mmtt.move_order_line_id = mol.line_id
27: AND mol.line_status = inv_globals.g_to_status_cancel_by_source;
110:
111: CURSOR mmtt_to_del(mol_id NUMBER) IS
112: SELECT mmtt.transaction_temp_id
113: , ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
114: FROM mtl_material_transactions_temp mmtt
115: WHERE mmtt.move_order_line_id = mol_id
116: AND NOT EXISTS(
117: SELECT wdt.transaction_temp_id
118: FROM wms_dispatched_tasks wdt
196: , l_item_id
197: , l_content_lpn_id
198: , l_transfer_lpn_id
199: , l_wms_task_types
200: FROM mtl_material_transactions_temp
201: WHERE transaction_temp_id = p_temp_id;
202:
203: IF (l_debug = 1) THEN
204: mydebug(' mol_id ' || mol_id);
207: END IF;
208: EXCEPTION
209: WHEN NO_DATA_FOUND THEN
210: IF (l_debug = 1) THEN
211: mydebug(' No data found in mtl_material_transactions_temp ');
212: END IF;
213:
214: mol_id := -1;
215: END;
305: END IF;
306:
307: SELECT COUNT(transaction_temp_id)
308: INTO cnt
309: FROM mtl_material_transactions_temp mmtt
310: WHERE mmtt.move_order_line_id = mol_id;
311:
312: IF (cnt = 0) THEN
313: IF (l_debug = 1) THEN
336: SELECT msi.lot_control_code
337: , msi.serial_number_control_code
338: INTO v_lot_control_code
339: , v_serial_control_code
340: FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
341: WHERE msi.inventory_item_id = mmtt.inventory_item_id
342: AND msi.organization_id = mmtt.organization_id
343: AND mmtt.transaction_temp_id = p_temp_id;
344:
343: AND mmtt.transaction_temp_id = p_temp_id;
344:
345: SELECT nvl(mp.allocate_serial_flag,'N') /*Bug#4003553.Added NVL function*/
346: INTO v_allocate_serial_flag
347: FROM mtl_parameters mp, mtl_material_transactions_temp mmtt
348: WHERE mp.organization_id = mmtt.organization_id
349: AND mmtt.transaction_temp_id = p_temp_id;
350:
351: IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
486:
487: END IF;
488:
489: --The lpn ids must be set to null for this task
490: UPDATE mtl_material_transactions_temp
491: SET lpn_id = NULL
492: , content_lpn_id = NULL
493: , transfer_lpn_id = NULL
494: WHERE transaction_temp_id = p_temp_id;
500: mydebug('deleted WDT with temp_id ' || p_temp_id);
501: END IF;
502:
503: IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
504: DELETE FROM mtl_material_transactions_temp
505: WHERE transaction_temp_id = p_temp_id;
506: END IF;
507: END IF;
508:
517: )
518: THEN
519: SELECT COUNT(1)
520: INTO l_count
521: FROM mtl_material_transactions_temp
522: WHERE transfer_lpn_id = l_transfer_lpn_id;
523:
524: IF l_count = 0 THEN -- no more rows and the current row is the
525: --last allocation
648: SELECT 'E'
649: INTO l_processed
650: FROM DUAL
651: WHERE EXISTS(SELECT 1
652: FROM mtl_material_transactions_temp
653: WHERE transaction_header_id = p_header_id
654: AND process_flag = 'E');
655:
656: IF (l_debug = 1) THEN
2292: AND device_request_id IS NOT NULL;
2293:
2294: CURSOR c_mo_line_id IS
2295: SELECT mtrl.line_id
2296: FROM mtl_material_transactions_temp mmtt
2297: , mtl_txn_request_lines mtrl
2298: WHERE (mmtt.transaction_temp_id = p_temp_id OR mmtt.parent_line_id = p_temp_id)
2299: AND mtrl.line_id = mmtt.move_order_line_id
2300: AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
2300: AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
2301:
2302: CURSOR c_mmtt_to_del IS
2303: SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
2304: FROM mtl_material_transactions_temp mmtt
2305: WHERE mmtt.move_order_line_id = l_mo_line_id
2306: AND NOT EXISTS(SELECT 1
2307: FROM mtl_material_transactions_temp t1
2308: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2303: SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
2304: FROM mtl_material_transactions_temp mmtt
2305: WHERE mmtt.move_order_line_id = l_mo_line_id
2306: AND NOT EXISTS(SELECT 1
2307: FROM mtl_material_transactions_temp t1
2308: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2309: AND NOT EXISTS(SELECT 1
2310: FROM wms_dispatched_tasks wdt
2311: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
2311: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
2312:
2313: CURSOR c_get_mmtt_count IS
2314: SELECT count(*)
2315: FROM mtl_material_transactions_temp mmtt
2316: WHERE mmtt.move_order_line_id = l_mo_line_id
2317: AND NOT EXISTS ( SELECT 1
2318: FROM mtl_material_transactions_temp t1
2319: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2314: SELECT count(*)
2315: FROM mtl_material_transactions_temp mmtt
2316: WHERE mmtt.move_order_line_id = l_mo_line_id
2317: AND NOT EXISTS ( SELECT 1
2318: FROM mtl_material_transactions_temp t1
2319: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2320:
2321: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2322: BEGIN
2446: END IF;
2447:
2448: SELECT transfer_lpn_id
2449: INTO l_transfer_lpn_id
2450: FROM mtl_material_transactions_temp
2451: WHERE transaction_temp_id = p_temp_id
2452: AND content_lpn_id = transfer_lpn_id;
2453:
2454: IF (l_debug = 1) THEN
2479: SELECT 'Y'
2480: INTO l_multiple_rows
2481: FROM DUAL
2482: WHERE EXISTS(SELECT transaction_temp_id
2483: FROM mtl_material_transactions_temp
2484: WHERE transfer_lpn_id = l_transfer_lpn_id
2485: AND transaction_temp_id <> p_temp_id);
2486:
2487: IF (l_debug = 1) THEN