[Home] [Help]
185: , v_subinv VARCHAR2
186: , v_locatorid NUMBER
187: , v_trxqty NUMBER
188: , v_cost_group_id NUMBER := NULL
189: , v_mmtt_rec IN OUT NOCOPY mtl_material_transactions_temp%ROWTYPE
190: , x_trxtempid OUT NOCOPY NUMBER
191: , v_sectrxqty NUMBER := NULL --INVCONV kkillams
192: ) IS
193: l_cst_grp_id NUMBER;
364: inv_log_util.trace('v_mmtt_rec.transaction_reference is :' || v_mmtt_rec.transaction_reference, g_pkg_name || '.' || l_api_name, 5);
365:
366: BEGIN
367: select transaction_reference into v_mmtt_rec.transaction_reference
368: from mtl_material_transactions_temp
369: where transaction_temp_id = v_trxtempid
370: and organization_id = v_orgid;
371:
372: EXCEPTION
381: -- trx_flow_header_id and logical_trx_type_code
382: -- Added original_transaction_temp_id to MMTT as part of ERES changes
383: -- Added source_line_id and source_code for bug 4931515
384: -- Added SOURCE_PROJECT_ID and SOURCE_TASK_ID for bug#5224902
385: INSERT INTO mtl_material_transactions_temp
386: (
387: transaction_header_id
388: , transaction_temp_id
389: , source_code
774:
775: /********************************************************************
776: * Explode the contents of the lpn and insert into MMTT, MSNT and MTLT
777: *******************************************************************/
778: FUNCTION explode_and_insert(p_lpn_id NUMBER, p_hdr_id NUMBER, p_mmtt IN OUT NOCOPY mtl_material_transactions_temp%ROWTYPE)
779: RETURN NUMBER IS
780: tb_lpn_cnts wms_container_pub.wms_container_tbl_type;
781: lpnitndx NUMBER;
782: curlpnrec wms_container_pub.wms_container_content_rec_type;
873: -- Going to used the transaction_temp_id for the batch and 1 for the sequence.
874: p_mmtt.transaction_batch_id := NVL(p_mmtt.transaction_batch_id, p_mmtt.transaction_temp_id);
875: p_mmtt.transaction_batch_seq := NVL(p_mmtt.transaction_batch_seq, 1);
876:
877: UPDATE mtl_material_transactions_temp
878: SET inventory_item_id = -1
879: , transaction_batch_id = p_mmtt.transaction_batch_id
880: , transaction_batch_seq = p_mmtt.transaction_batch_seq
881: , subinventory_code = NVL(subinventory_code, l_lpn_subinv)
901: RAISE fnd_api.g_exc_error;
902: END IF;
903: END IF;
904: ELSIF(p_mmtt.inventory_item_id <> -1) THEN
905: UPDATE mtl_material_transactions_temp
906: SET inventory_item_id = -1
907: WHERE transaction_temp_id = p_mmtt.transaction_temp_id;
908: END IF;
909:
1265: IF (l_debug = 1) THEN
1266: inv_log_util.TRACE('** Going to upd lst rec1. qty = ' || v_lastitemqty, 'INV_LPN_TRX_PUB', 9);
1267: END IF;
1268:
1269: UPDATE mtl_material_transactions_temp
1270: SET transaction_quantity = v_lastitemqty
1271: --, primary_quantity = v_lastitemqty * l_conv_fact /*Bug#5486052.*/
1272: , primary_quantity = v_lastitempriqty --16445970
1273: , secondary_transaction_quantity = CASE WHEN v_lastitemsecqty <> 0 THEN v_lastitemsecqty ELSE secondary_transaction_quantity END --INVCONV kkillams
1443: v_lastitempriqty := -1 * v_lastitempriqty; --16445970
1444: END IF;
1445:
1446:
1447: UPDATE mtl_material_transactions_temp
1448: SET transaction_quantity = v_lastitemqty
1449: --, primary_quantity = v_lastitemqty * l_conv_fact /*Bug#5486052.*/
1450: , primary_quantity = v_lastitempriqty --16445970
1451: -- nsinghi bug#5553546 v_lastitemsecqty is being wrongly assigned as v_lastitemqty in THEN part. change it to v_lastitemsecqty
1511:
1512: --FOB changes. Patchset J. This API is only called wif PO pathset J is installed
1513: -- We track the FOB info in MMTT + MMT.
1514: PROCEDURE update_fob_point(
1515: v_mmtt IN OUT NOCOPY mtl_material_transactions_temp%ROWTYPE
1516: , x_return_status IN OUT NOCOPY VARCHAR2
1517: , x_msg_data IN OUT NOCOPY VARCHAR2
1518: , x_msg_count IN OUT NOCOPY NUMBER
1519: ) IS
1565: fnd_msg_pub.ADD;
1566: RAISE fnd_api.g_exc_error;
1567: END;
1568:
1569: UPDATE mtl_material_transactions_temp
1570: SET fob_point = l_fob_point
1571: , intransit_account = l_intransit_inv_account
1572: WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
1573:
1637: END IF;
1638: RAISE fnd_api.g_exc_error;
1639: END;
1640: END IF;
1641: UPDATE mtl_material_transactions_temp
1642: SET fob_point = l_fob_point
1643: , intransit_account = l_intransit_inv_account
1644: WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
1645:
3592:
3593: -- Bug# 7435480 Added trasanction_batch_seq in the order by clause
3594: CURSOR c_mmtt IS
3595: SELECT *
3596: FROM mtl_material_transactions_temp
3597: WHERE transaction_header_id = p_trx_hdr_id
3598: AND NVL(transaction_status, 1) <> 2 -- don't consider suggestions
3599: AND process_flag = 'Y'
3600: ORDER BY transaction_batch_id,transaction_batch_seq;
3704: insrowcnt NUMBER := 0;
3705: failedrowcnt NUMBER := 0;
3706: expldrowcnt NUMBER := 0;
3707: retval NUMBER := 0;
3708: v_mmtt mtl_material_transactions_temp%ROWTYPE;
3709: v_lpn wms_container_pub.lpn;
3710: v_deleterow BOOLEAN := FALSE; -- Should the original row in MMTT be deleted ?
3711: l_cst_grp VARCHAR2(30);
3712: l_req_id NUMBER;
3858: INTO l_is_cartonization
3859: FROM DUAL
3860: WHERE EXISTS(
3861: SELECT 1
3862: FROM mtl_material_transactions_temp
3863: WHERE transaction_action_id = inv_globals.g_action_containerpack
3864: AND transfer_lpn_id IS NULL
3865: AND transaction_header_id = p_trx_hdr_id);
3866: EXCEPTION
3885: SELECT organization_id
3886: , MAX(containers)
3887: INTO l_org
3888: , l_containers
3889: FROM mtl_material_transactions_temp
3890: WHERE transaction_header_id = p_trx_hdr_id
3891: AND NVL(transaction_status, 1) <> 2
3892: AND process_flag = 'Y'
3893: GROUP BY organization_id;
3955: IF (l_debug = 1) THEN
3956: inv_log_util.TRACE('Transfering Cartonization suggestions', 'INV_LPN_TRX_PUB', 9);
3957: END IF;
3958:
3959: UPDATE mtl_material_transactions_temp
3960: SET transfer_lpn_id = cartonization_id
3961: WHERE transaction_header_id = p_trx_hdr_id;
3962:
3963: IF (l_debug = 1) THEN
4265: AND wip_constants.dmf_patchset_level >= wip_constants.dmf_patchset_j_value) THEN
4266: IF (wip_mtltempproc_grp.istxnidrequired(v_mmtt.transaction_temp_id)) THEN
4267: inv_log_util.TRACE('Going to update mmtt with material_allocation_temp_id', 'INV_LPN_TRX_PUB', 1);
4268:
4269: UPDATE mtl_material_transactions_temp
4270: SET material_allocation_temp_id = mtl_material_transactions_s.NEXTVAL
4271: WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
4272: END IF;
4273: END IF;
4327:
4328: -- Continue with the process based on the value of l_proc_mode
4329: IF l_proc_mode = 2 THEN -- Concurrent Mode
4330: -- Concurrent request. Submit request and return to caller
4331: UPDATE mtl_material_transactions_temp
4332: SET transaction_mode = inv_txn_manager_pub.proc_mode_mmtt_async
4333: WHERE transaction_header_id = p_trx_hdr_id;
4334:
4335: l_req_id := fnd_request.submit_request(application => 'INV', program => 'INVMBTRX', argument1 => p_trx_hdr_id);
4361: END IF;
4362:
4363: -- In this case, the background transaction manager would pick
4364: -- up this row from MMTT. Update transaction_mode and return to caller
4365: UPDATE mtl_material_transactions_temp
4366: SET transaction_mode = inv_txn_manager_pub.proc_mode_mmtt_bgrnd
4367: WHERE transaction_header_id = p_trx_hdr_id;
4368:
4369: fnd_message.set_name('INV', 'INV_TXN_REQ_QUEUED');
4405: --get -ve of absolute
4406: v_mmtt.secondary_transaction_quantity := -1 * abs(v_mmtt.secondary_transaction_quantity);
4407: END IF;
4408: --END INVCONV kkillams
4409: UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
4410: SET PRIMARY_QUANTITY = v_mmtt.primary_quantity,
4411: TRANSACTION_QUANTITY = v_mmtt.transaction_quantity,
4412: SECONDARY_TRANSACTION_QUANTITY = CASE WHEN v_mmtt.secondary_uom_code IS NOT NULL THEN v_mmtt.secondary_transaction_quantity
4413: ELSE SECONDARY_TRANSACTION_QUANTITY
4464:
4465: IF (v_mmtt.transaction_type_id = 3) THEN
4466:
4467: SELECT Count(1) INTO l_rtv_exist FROM
4468: rcv_transactions_interface rti,mtl_material_transactions_temp mmtt
4469: WHERE rti.interface_transaction_id = mmtt.source_line_id
4470: AND rti.processing_status_code = 'WSH_INTERFACED'
4471: AND rti.group_id = mmtt.transaction_source_name
4472: AND mmtt.transaction_temp_id = v_mmtt.transaction_temp_id ;
4560: --Set message in MMTT line based in header id
4561: -- Bug 3804314: Changed the where clause to transaction_header_id
4562: BEGIN
4563: -- Update MMTT records with error message and set status to error
4564: UPDATE mtl_material_transactions_temp
4565: SET ERROR_CODE = l_error_code
4566: , error_explanation = x_proc_msg
4567: , process_flag = 'E'
4568: , lock_flag = 'N'
4599:
4600: -- Remove from MSNT rows with same MMTT transaction_temp_id
4601: DELETE FROM mtl_serial_numbers_temp
4602: WHERE transaction_temp_id IN(SELECT transaction_temp_id
4603: FROM mtl_material_transactions_temp
4604: WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
4605: AND transaction_batch_id = v_mmtt.transaction_batch_id);
4606:
4607: -- Remove from MSNT rows with same MTNT transaction_temp_id
4608: DELETE FROM mtl_serial_numbers_temp
4609: WHERE transaction_temp_id IN(SELECT serial_transaction_temp_id
4610: FROM mtl_transaction_lots_temp
4611: WHERE transaction_temp_id IN(SELECT transaction_temp_id
4612: FROM mtl_material_transactions_temp
4613: WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
4614: AND transaction_batch_id = v_mmtt.transaction_batch_id));
4615:
4616: -- Remove from MTLT rows with same MMTT transaction_temp_id
4615:
4616: -- Remove from MTLT rows with same MMTT transaction_temp_id
4617: DELETE FROM mtl_transaction_lots_temp
4618: WHERE transaction_temp_id IN(SELECT transaction_temp_id
4619: FROM mtl_material_transactions_temp
4620: WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
4621: AND transaction_batch_id = v_mmtt.transaction_batch_id);
4622:
4623: -- Remove from MMTT rows with same transaction group id
4620: WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
4621: AND transaction_batch_id = v_mmtt.transaction_batch_id);
4622:
4623: -- Remove from MMTT rows with same transaction group id
4624: DELETE FROM mtl_material_transactions_temp
4625: WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
4626: AND transaction_batch_id = v_mmtt.transaction_batch_id;
4627:
4628: ELSE -- IF v_mmtt.transaction_batch_id IS NULL
4657: DELETE FROM mtl_transaction_lots_temp
4658: WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
4659:
4660: -- Remove from MMTT rows with same transaction group id
4661: DELETE FROM mtl_material_transactions_temp
4662: WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
4663: END IF;
4664: EXCEPTION
4665: WHEN OTHERS THEN
4670: ELSE -- record originated in MMTT
4671: IF (v_mmtt.transaction_batch_id IS NOT NULL) THEN
4672: -- This record belongs to a batch. Fail all records of this batch
4673: -- stamp error-explanation only on the row that caused error
4674: UPDATE mtl_material_transactions_temp
4675: SET ERROR_CODE = l_error_code
4676: , error_explanation = x_proc_msg
4677: , process_flag = 'E'
4678: , lock_flag = 'N'
4678: , lock_flag = 'N'
4679: WHERE transaction_temp_id = v_mmtt.transaction_temp_id;
4680:
4681: -- stamp error-code on all records of the batch
4682: UPDATE mtl_material_transactions_temp
4683: SET ERROR_CODE = l_error_code
4684: , process_flag = 'E'
4685: , lock_flag = 'N'
4686: WHERE transaction_header_id = p_trx_hdr_id -- Bug 5748351
4687: AND transaction_batch_id = v_mmtt.transaction_batch_id;
4688: ELSE
4689: -- This record does not belong to a batch.
4690: -- Update MMTT records with error message and set status to error
4691: UPDATE mtl_material_transactions_temp
4692: SET ERROR_CODE = l_error_code
4693: , error_explanation = x_proc_msg
4694: , process_flag = 'E'
4695: , lock_flag = 'N'
4716: AND (v_mmtt.transaction_type_id not in (1004, 97)) /* Fix for 13435268 and 13536869. Do not update secondary transaction for lot uom decrease and increase txn */
4717: --Bug# 5453879 - if trx qty is 0 leave the sec qty as it is.
4718: AND (v_mmtt.transaction_quantity <> 0) THEN
4719: l_secondary_txn_quantity := sign(v_mmtt.transaction_quantity) * abs(v_mmtt.secondary_transaction_quantity);
4720: update mtl_material_transactions_temp
4721: set secondary_transaction_quantity = l_secondary_txn_quantity
4722: where transaction_temp_id = v_mmtt.transaction_temp_id;
4723: END IF;
4724: END IF; -- Bug 9569657
4726: IF (v_mmtt.move_order_line_id IS NOT null ) THEN --Bug15837987 begin for those allocation type mmtt records.
4727: IF (l_debug = 1) THEN
4728: inv_log_util.TRACE('Update WHO Columns for allocation type.','INV_LPN_TRX_PUB', 1);
4729: END IF;
4730: update mtl_material_transactions_temp
4731: set last_update_date = SYSDATE,
4732: last_updated_by = FND_PROFILE.VALUE('USER_ID'),
4733: last_update_login = FND_PROFILE.VALUE('LOGIN_ID'),
4734: CREATED_BY = FND_PROFILE.VALUE('USER_ID')
4838: -- by process code also. Otherwise, we will still call the TM.
4839: BEGIN
4840: SELECT COUNT(1)
4841: INTO l_process
4842: FROM mtl_material_transactions_temp
4843: WHERE transaction_header_id = p_trx_hdr_id
4844: AND process_flag = 'Y'
4845: AND ROWNUM < 2;
4846: IF (l_debug = 1) THEN
5160: SELECT 1
5161: INTO l_is_from_mti
5162: FROM DUAL
5163: WHERE EXISTS(SELECT 1
5164: FROM mtl_material_transactions_temp
5165: WHERE transaction_header_id = p_trx_hdr_id
5166: AND transaction_mode = inv_txn_manager_pub.proc_mode_mti);
5167: EXCEPTION
5168: WHEN NO_DATA_FOUND THEN
5184:
5185: -- Remove from MSNT rows with same MMTT Transaction_header_id
5186: DELETE FROM mtl_serial_numbers_temp
5187: WHERE transaction_temp_id IN(SELECT transaction_temp_id
5188: FROM mtl_material_transactions_temp
5189: WHERE transaction_header_id = p_trx_hdr_id);
5190:
5191: -- Remove from MSNT rows with same MTNT Transaction_header_id
5192: DELETE FROM mtl_serial_numbers_temp
5192: DELETE FROM mtl_serial_numbers_temp
5193: WHERE transaction_temp_id IN(SELECT serial_transaction_temp_id
5194: FROM mtl_transaction_lots_temp
5195: WHERE transaction_temp_id IN(SELECT transaction_temp_id
5196: FROM mtl_material_transactions_temp
5197: WHERE transaction_header_id = p_trx_hdr_id));
5198:
5199: -- Remove from MTLT rows with same MMTT Transaction_header_id
5200: DELETE FROM mtl_transaction_lots_temp
5198:
5199: -- Remove from MTLT rows with same MMTT Transaction_header_id
5200: DELETE FROM mtl_transaction_lots_temp
5201: WHERE transaction_temp_id IN(SELECT transaction_temp_id
5202: FROM mtl_material_transactions_temp
5203: WHERE transaction_header_id = p_trx_hdr_id);
5204:
5205: -- Remove from MMTT rows with same Transaction_header_id
5206: DELETE FROM mtl_material_transactions_temp
5202: FROM mtl_material_transactions_temp
5203: WHERE transaction_header_id = p_trx_hdr_id);
5204:
5205: -- Remove from MMTT rows with same Transaction_header_id
5206: DELETE FROM mtl_material_transactions_temp
5207: WHERE transaction_header_id = p_trx_hdr_id;
5208: ELSE
5209: UPDATE mtl_material_transactions_temp
5210: SET ERROR_CODE = l_error_code
5205: -- Remove from MMTT rows with same Transaction_header_id
5206: DELETE FROM mtl_material_transactions_temp
5207: WHERE transaction_header_id = p_trx_hdr_id;
5208: ELSE
5209: UPDATE mtl_material_transactions_temp
5210: SET ERROR_CODE = l_error_code
5211: , error_explanation = x_proc_msg
5212: , process_flag = 'E'
5213: , lock_flag = 'N'
6746:
6747: -- bug 2879208
6748: SELECT wms_task_type, reservation_id , transaction_header_id --9869993,fetching reservation_id -- Modified for bug 7010169
6749: INTO l_system_task_type , l_reservation_id, l_trx_hdr_id -- Modified for bug 7010169
6750: FROM mtl_material_transactions_temp
6751: WHERE transaction_temp_id = p_transaction_temp_id;
6752:
6753: IF (l_debug = 1) THEN
6754: inv_log_util.TRACE('l_system_task_type = ' || l_system_task_type, 'INV_LPN_TRX_PUB', 9);
7161:
7162: --Bug#7010169,We need to chekc for other tasks loaded into same LPN.
7163: SELECT count(transaction_temp_id)
7164: INTO l_loaded_tasks_count
7165: FROM mtl_material_transactions_temp mmtt
7166: WHERE transfer_lpn_id = p_transfer_lpn_id
7167: AND transaction_header_id <> l_trx_hdr_id ;
7168:
7169: IF (l_debug = 1) THEN
7665: p_transaction_action_id = inv_globals.G_Action_Planxfr OR
7666: p_transaction_action_id = inv_globals.G_Action_Orgxfr OR
7667: p_transaction_action_id = inv_globals.G_Action_IntransitShipment))) then
7668: BEGIN
7669: UPDATE mtl_material_transactions_temp
7670: SET content_lpn_id = (SELECT outermost_lpn_id
7671: FROM wms_license_plate_numbers
7672: WHERE lpn_id = p_content_lpn_id
7673: AND rownum < 2)
7695: IF NVL(inv_cache.org_rec.PROCESS_ENABLED_FLAG,'N') = 'Y' then
7696:
7697: -- Start 8379698
7698: SELECT transaction_type_id INTO l_transaction_type_id
7699: FROM mtl_material_transactions_temp
7700: WHERE transaction_temp_id = p_transaction_temp_id;
7701:
7702: IF (l_transaction_type_id = 44 AND p_transaction_action_id = 31 AND p_transaction_source_type_id = 5) THEN
7703: l_business_flow_code := 26;
7766: END IF;
7767:
7768: DELETE FROM mtl_serial_numbers_temp
7769: WHERE transaction_temp_id = (SELECT transaction_temp_id
7770: FROM mtl_material_transactions_temp
7771: WHERE transaction_temp_id = p_transaction_temp_id
7772: AND inventory_item_id = -1);
7773:
7774: IF SQL%ROWCOUNT = 0 THEN
7777: SELECT serial_transaction_temp_id
7778: FROM mtl_transaction_lots_temp
7779: WHERE transaction_temp_id =
7780: (SELECT transaction_temp_id
7781: FROM mtl_material_transactions_temp
7782: WHERE transaction_temp_id = p_transaction_temp_id
7783: AND inventory_item_id = -1));
7784: END IF;
7785:
7788: END IF;
7789:
7790: DELETE FROM mtl_transaction_lots_temp
7791: WHERE transaction_temp_id = (SELECT transaction_temp_id
7792: FROM mtl_material_transactions_temp
7793: WHERE transaction_temp_id = p_transaction_temp_id
7794: AND inventory_item_id = -1);
7795:
7796: IF (l_debug = 1) THEN
7796: IF (l_debug = 1) THEN
7797: inv_log_util.TRACE('* No of MTLT records deleted =' || SQL%ROWCOUNT, 'INV_LPN_TRX_PUB', 1);
7798: END IF;
7799:
7800: DELETE FROM mtl_material_transactions_temp
7801: WHERE transaction_temp_id = p_transaction_temp_id
7802: AND inventory_item_id = -1;
7803:
7804: IF (l_debug = 1) THEN