[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
2277: AND device_request_id IS NOT NULL;
2278:
2279: CURSOR c_mo_line_id IS
2280: SELECT mtrl.line_id
2281: FROM mtl_material_transactions_temp mmtt
2282: , mtl_txn_request_lines mtrl
2283: WHERE (mmtt.transaction_temp_id = p_temp_id OR mmtt.parent_line_id = p_temp_id)
2284: AND mtrl.line_id = mmtt.move_order_line_id
2285: AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
2285: AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
2286:
2287: CURSOR c_mmtt_to_del IS
2288: SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
2289: FROM mtl_material_transactions_temp mmtt
2290: WHERE mmtt.move_order_line_id = l_mo_line_id
2291: AND NOT EXISTS(SELECT 1
2292: FROM mtl_material_transactions_temp t1
2293: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2288: SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
2289: FROM mtl_material_transactions_temp mmtt
2290: WHERE mmtt.move_order_line_id = l_mo_line_id
2291: AND NOT EXISTS(SELECT 1
2292: FROM mtl_material_transactions_temp t1
2293: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2294: AND NOT EXISTS(SELECT 1
2295: FROM wms_dispatched_tasks wdt
2296: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
2296: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
2297:
2298: CURSOR c_get_mmtt_count IS
2299: SELECT count(*)
2300: FROM mtl_material_transactions_temp mmtt
2301: WHERE mmtt.move_order_line_id = l_mo_line_id
2302: AND NOT EXISTS ( SELECT 1
2303: FROM mtl_material_transactions_temp t1
2304: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2299: SELECT count(*)
2300: FROM mtl_material_transactions_temp mmtt
2301: WHERE mmtt.move_order_line_id = l_mo_line_id
2302: AND NOT EXISTS ( SELECT 1
2303: FROM mtl_material_transactions_temp t1
2304: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2305:
2306: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2307: BEGIN
2431: END IF;
2432:
2433: SELECT transfer_lpn_id
2434: INTO l_transfer_lpn_id
2435: FROM mtl_material_transactions_temp
2436: WHERE transaction_temp_id = p_temp_id
2437: AND content_lpn_id = transfer_lpn_id;
2438:
2439: IF (l_debug = 1) THEN
2464: SELECT 'Y'
2465: INTO l_multiple_rows
2466: FROM DUAL
2467: WHERE EXISTS(SELECT transaction_temp_id
2468: FROM mtl_material_transactions_temp
2469: WHERE transfer_lpn_id = l_transfer_lpn_id
2470: AND transaction_temp_id <> p_temp_id);
2471:
2472: IF (l_debug = 1) THEN