[Home] [Help]
1350: -- Need to delete MTLT before deleting MMTT
1351: -- Need tuning ???
1352:
1353:
1354: DELETE FROM mtl_transaction_lots_temp mtlt
1355: WHERE EXISTS(
1356: SELECT 1
1357: FROM mtl_material_transactions_temp mmtt
1358: WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2586: RETURN FALSE;
2587: END insert_msnt_rec;
2588:
2589:
2590: /* Local function to insert a record in MTL_TRANSACTION_LOTS_TEMP
2591: * from the original MTLT record. These MTLT records are needed by
2592: * the receiving transaction manager for the deliver transaction
2593: */
2594: FUNCTION insert_dup_mtlt (
2597: , p_serial_temp_id IN NUMBER
2598: , p_item_id IN NUMBER
2599: , p_organization_id IN NUMBER) RETURN BOOLEAN IS
2600: BEGIN
2601: INSERT INTO mtl_transaction_lots_temp
2602: (
2603: transaction_temp_id
2604: , last_update_date
2605: , last_updated_by
2754: , mln.n_attribute9
2755: , mln.n_attribute10
2756: , mln.vendor_id
2757: , mln.territory_code
2758: FROM mtl_transaction_lots_temp mtlt
2759: , mtl_lot_numbers mln
2760: WHERE mtlt.transaction_temp_id = p_orig_temp_id
2761: AND mln.lot_number = mtlt.lot_number
2762: AND mln.inventory_item_id = p_item_id
4032: END IF;
4033: END IF;
4034:
4035: /* FP-J Lot/Serial Support Enhancement
4036: * Do not update MTL_TRANSACTION_LOTS_TEMP from here if the LPN Resides in
4037: * Receiving and WMS and PO patch levels are J or higher
4038: * For other LPN contexts and if patch levels are lower than J,
4039: * continue with the updates
4040: */
4043: IF l_debug = 1 THEN
4044: mydebug('complete_putaway: LPN Resides in Receiving. No updates to MTLT from here.');
4045: END IF;
4046: ELSE
4047: UPDATE mtl_transaction_lots_temp
4048: SET serial_transaction_temp_id = l_ser_seq
4049: , last_update_date = SYSDATE
4050: , last_updated_by = p_user_id
4051: WHERE transaction_temp_id = p_temp_id
4057: IF l_debug = 1 THEN
4058: mydebug('complete_putaway: INV/WIP LPN. Update MTLT.');
4059: END IF;
4060:
4061: UPDATE mtl_transaction_lots_temp
4062: SET transaction_quantity = l_orig_qty
4063: , primary_quantity = l_pr_qty
4064: , serial_transaction_temp_id = l_ser_seq
4065: , last_update_date = SYSDATE
4705:
4706: IF (l_debug = 1) THEN
4707: mydebug('complete_putaway: primary qty: ' || l_pr_qty);
4708: END IF;
4709: UPDATE mtl_transaction_lots_temp
4710: SET transaction_quantity = l_qty
4711: , primary_quantity = l_pr_qty
4712: , last_update_date = SYSDATE
4713: , last_updated_by = p_user_id
5716: , primary_quantity = primary_quantity - l_qty_diff_prim
5717: WHERE transaction_temp_id = l_temp_id
5718: AND organization_id = l_org_id;
5719:
5720: -- Update mtl_transaction_lots_temp, if necessary
5721: --calling this procedure
5722: /* Bug #2966531
5723: * Do not update MTLT again since they would already have been done before
5724: * coming here */
5723: * Do not update MTLT again since they would already have been done before
5724: * coming here */
5725: /*
5726: BEGIN
5727: UPDATE mtl_transaction_lots_temp
5728: SET transaction_quantity = transaction_quantity-l_qty_diff,
5729: primary_quantity = primary_quantity-l_qty_diff_prim
5730: WHERE transaction_temp_id = l_temp_id;
5731: EXCEPTION
6298: -- Delete lot and serial records
6299: BEGIN
6300: DELETE mtl_serial_numbers_temp
6301: WHERE transaction_temp_id = (SELECT serial_transaction_temp_id
6302: FROM mtl_transaction_lots_temp
6303: WHERE transaction_temp_id = l_temp_id);
6304: EXCEPTION
6305: WHEN NO_DATA_FOUND THEN
6306: NULL;
6306: NULL;
6307: END;
6308:
6309: BEGIN
6310: DELETE mtl_transaction_lots_temp
6311: WHERE transaction_temp_id = l_temp_id;
6312: EXCEPTION
6313: WHEN NO_DATA_FOUND THEN
6314: NULL;
6419: BEGIN
6420: DELETE mtl_serial_numbers_temp
6421: WHERE transaction_temp_id IN
6422: (SELECT serial_transaction_temp_id
6423: FROM mtl_transaction_lots_temp
6424: WHERE transaction_temp_id = l_temp_id);
6425: EXCEPTION
6426: WHEN OTHERS THEN
6427: mydebug('archive_task: Error deleting MSNT for MTLT: ' || sqlerrm);
6428: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6429: END;
6430:
6431: BEGIN
6432: DELETE mtl_transaction_lots_temp
6433: WHERE transaction_temp_id = l_temp_id;
6434: EXCEPTION
6435: WHEN OTHERS THEN
6436: mydebug('archive_task: Error deleting MTLT: ' || sqlerrm);
6515: -- Add cursor to get serial_transaction_temp_id values for performance.
6516: -- SQL compiler complains of a Hash Join otherwise.
6517: CURSOR c_serial_transaction_temp_id IS
6518: SELECT serial_transaction_temp_id
6519: FROM mtl_transaction_lots_temp
6520: WHERE transaction_temp_id = l_temp_id;
6521: l_serial_transaction_temp_id NUMBER;
6522: -- Bug# 3434940 - Performance Fixes
6523: -- Add cursor to get the serial number(s) for a given MSNT record.
7126:
7127: CURSOR lot_csr IS
7128: SELECT lot_number
7129: , serial_transaction_temp_id
7130: FROM mtl_transaction_lots_temp
7131: WHERE transaction_temp_id = l_temp_id;
7132:
7133: CURSOR ser_csr IS
7134: SELECT serial_number
7741: WHERE organization_id = p_organization_id
7742: AND lpn_id = l_lpn_id)
7743: );
7744:
7745: DELETE FROM mtl_transaction_lots_temp
7746: WHERE transaction_temp_id IN
7747: (SELECT transaction_temp_id
7748: FROM mtl_material_transactions_temp
7749: WHERE move_order_line_id IN
8537: END IF;
8538:
8539: -- Update the MTLT record to clear out the serial_transaction_temp_id column
8540: -- since insert_lot_trx by default will insert a value for it.
8541: UPDATE mtl_transaction_lots_temp
8542: SET serial_transaction_temp_id = NULL
8543: WHERE transaction_temp_id = l_txn_temp_id;
8544:
8545: IF (l_debug = 1) THEN
8564:
8565:
8566: -- Delete MTLT
8567: FORALL i in 1..l_mmtt_id_tab.COUNT
8568: DELETE FROM mtl_transaction_lots_temp
8569: WHERE transaction_temp_id = l_mmtt_id_tab(i);
8570:
8571: FOR i IN 1..l_mmtt_id_tab.COUNT LOOP
8572: UPDATE mtl_txn_request_lines