[Home] [Help]
1495: -- Need to delete MTLT before deleting MMTT
1496: -- Need tuning ???
1497:
1498:
1499: DELETE FROM mtl_transaction_lots_temp mtlt
1500: WHERE EXISTS(
1501: SELECT 1
1502: FROM mtl_material_transactions_temp mmtt
1503: WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
2758: RETURN FALSE;
2759: END insert_msnt_rec;
2760:
2761:
2762: /* Local function to insert a record in MTL_TRANSACTION_LOTS_TEMP
2763: * from the original MTLT record. These MTLT records are needed by
2764: * the receiving transaction manager for the deliver transaction
2765: */
2766: FUNCTION insert_dup_mtlt (
2769: , p_serial_temp_id IN NUMBER
2770: , p_item_id IN NUMBER
2771: , p_organization_id IN NUMBER) RETURN BOOLEAN IS
2772: BEGIN
2773: INSERT INTO mtl_transaction_lots_temp
2774: (
2775: transaction_temp_id
2776: , last_update_date
2777: , last_updated_by
2958: , mln.attribute12
2959: , mln.attribute13
2960: , mln.attribute14
2961: , mln.attribute15
2962: FROM mtl_transaction_lots_temp mtlt
2963: , mtl_lot_numbers mln
2964: WHERE mtlt.transaction_temp_id = p_orig_temp_id
2965: AND mln.lot_number = mtlt.lot_number
2966: AND mln.inventory_item_id = p_item_id
4229: ) THEN
4230: /* bug12607020 */
4231: -- Need to get the next value for serial temp id
4232: IF (l_lpn_context in (1,2)) THEN
4233: select serial_transaction_temp_id INTO l_ser_seq FROM mtl_transaction_lots_temp
4234: WHERE transaction_temp_id = p_temp_id
4235: AND lot_number = p_lot;
4236: END IF;
4237: -- Need to get the next value for serial temp id
4247: END IF;
4248: END IF;
4249:
4250: /* FP-J Lot/Serial Support Enhancement
4251: * Do not update MTL_TRANSACTION_LOTS_TEMP from here if the LPN Resides in
4252: * Receiving and WMS and PO patch levels are J or higher
4253: * For other LPN contexts and if patch levels are lower than J,
4254: * continue with the updates
4255: */
4258: IF l_debug = 1 THEN
4259: mydebug('complete_putaway: LPN Resides in Receiving. No updates to MTLT from here.');
4260: END IF;
4261: ELSE
4262: UPDATE mtl_transaction_lots_temp
4263: SET serial_transaction_temp_id = l_ser_seq
4264: , last_update_date = SYSDATE
4265: , last_updated_by = p_user_id
4266: WHERE transaction_temp_id = p_temp_id
4272: IF l_debug = 1 THEN
4273: mydebug('complete_putaway: INV/WIP LPN. Update MTLT.');
4274: END IF;
4275:
4276: UPDATE mtl_transaction_lots_temp
4277: SET transaction_quantity = l_orig_qty
4278: , primary_quantity = l_pr_qty
4279: , serial_transaction_temp_id = l_ser_seq
4280: , last_update_date = SYSDATE
4931:
4932: IF (l_debug = 1) THEN
4933: mydebug('complete_putaway: primary qty: ' || l_pr_qty);
4934: END IF;
4935: UPDATE mtl_transaction_lots_temp
4936: SET transaction_quantity = l_qty
4937: , primary_quantity = l_pr_qty
4938: , last_update_date = SYSDATE
4939: , last_updated_by = p_user_id
5993: , primary_quantity = primary_quantity - l_qty_diff_prim
5994: WHERE transaction_temp_id = l_temp_id
5995: AND organization_id = l_org_id;
5996:
5997: -- Update mtl_transaction_lots_temp, if necessary
5998: --calling this procedure
5999: /* Bug #2966531
6000: * Do not update MTLT again since they would already have been done before
6001: * coming here */
6000: * Do not update MTLT again since they would already have been done before
6001: * coming here */
6002: /*
6003: BEGIN
6004: UPDATE mtl_transaction_lots_temp
6005: SET transaction_quantity = transaction_quantity-l_qty_diff,
6006: primary_quantity = primary_quantity-l_qty_diff_prim
6007: WHERE transaction_temp_id = l_temp_id;
6008: EXCEPTION
6625: -- Delete lot and serial records
6626: BEGIN
6627: DELETE mtl_serial_numbers_temp
6628: WHERE transaction_temp_id = (SELECT serial_transaction_temp_id
6629: FROM mtl_transaction_lots_temp
6630: WHERE transaction_temp_id = l_temp_id);
6631: EXCEPTION
6632: WHEN NO_DATA_FOUND THEN
6633: NULL;
6633: NULL;
6634: END;
6635:
6636: BEGIN
6637: DELETE mtl_transaction_lots_temp
6638: WHERE transaction_temp_id = l_temp_id;
6639: EXCEPTION
6640: WHEN NO_DATA_FOUND THEN
6641: NULL;
6746: BEGIN
6747: DELETE mtl_serial_numbers_temp
6748: WHERE transaction_temp_id IN
6749: (SELECT serial_transaction_temp_id
6750: FROM mtl_transaction_lots_temp
6751: WHERE transaction_temp_id = l_temp_id);
6752: EXCEPTION
6753: WHEN OTHERS THEN
6754: mydebug('archive_task: Error deleting MSNT for MTLT: ' || sqlerrm);
6755: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6756: END;
6757:
6758: BEGIN
6759: DELETE mtl_transaction_lots_temp
6760: WHERE transaction_temp_id = l_temp_id;
6761: EXCEPTION
6762: WHEN OTHERS THEN
6763: mydebug('archive_task: Error deleting MTLT: ' || sqlerrm);
6842: -- Add cursor to get serial_transaction_temp_id values for performance.
6843: -- SQL compiler complains of a Hash Join otherwise.
6844: CURSOR c_serial_transaction_temp_id IS
6845: SELECT serial_transaction_temp_id
6846: FROM mtl_transaction_lots_temp
6847: WHERE transaction_temp_id = l_temp_id;
6848: l_serial_transaction_temp_id NUMBER;
6849: -- Bug# 3434940 - Performance Fixes
6850: -- Add cursor to get the serial number(s) for a given MSNT record.
7453:
7454: CURSOR lot_csr IS
7455: SELECT lot_number
7456: , serial_transaction_temp_id
7457: FROM mtl_transaction_lots_temp
7458: WHERE transaction_temp_id = l_temp_id;
7459:
7460: CURSOR ser_csr IS
7461: SELECT serial_number
8078: WHERE organization_id = p_organization_id
8079: AND lpn_id = l_lpn_id)
8080: );
8081:
8082: DELETE FROM mtl_transaction_lots_temp
8083: WHERE transaction_temp_id IN
8084: (SELECT transaction_temp_id
8085: FROM mtl_material_transactions_temp
8086: WHERE move_order_line_id IN
8894: END IF;
8895:
8896: -- Update the MTLT record to clear out the serial_transaction_temp_id column
8897: -- since insert_lot_trx by default will insert a value for it.
8898: UPDATE mtl_transaction_lots_temp
8899: SET serial_transaction_temp_id = NULL
8900: WHERE transaction_temp_id = l_txn_temp_id;
8901:
8902: IF (l_debug = 1) THEN
8921:
8922:
8923: -- Delete MTLT
8924: FORALL i in 1..l_mmtt_id_tab.COUNT
8925: DELETE FROM mtl_transaction_lots_temp
8926: WHERE transaction_temp_id = l_mmtt_id_tab(i);
8927:
8928: FOR i IN 1..l_mmtt_id_tab.COUNT LOOP
8929: UPDATE mtl_txn_request_lines