[Home] [Help]
433: mti.transaction_batch_seq,
434: mti.kanban_card_id,
435: mti.transaction_mode --J-dev
436: FROM MTL_TRANSACTIONS_INTERFACE MTI,
437: MTL_TRANSACTION_LOTS_INTERFACE MTLI,
438: MTL_SYSTEM_ITEMS_B MSI
439: WHERE mti.transaction_interface_id = p_interface_id
440: AND MTI.transaction_interface_id = mtli.transaction_interface_id
441: AND MTI.organization_id = msi.organization_id
569: l_mti_csr.cost_group_id,
570: l_mti_csr.transaction_batch_id,
571: l_mti_csr.transaction_batch_seq,
572: l_mti_csr.kanban_card_id);
573: INSERT INTO mtl_transaction_lots_interface
574: (transaction_interface_id, --J-dev
575: last_update_date ,
576: last_updated_by ,
577: creation_date ,
1309: --Jalaj Srivastava Bug 4969885
1310: --if errors are for lot record then also update MLTI
1311: IF (lot_rowid IS NOT NULL) THEN
1312:
1313: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
1314: SET LAST_UPDATE_DATE = SYSDATE,
1315: LAST_UPDATED_BY = l_userid,
1316: LAST_UPDATE_LOGIN = l_loginid,
1317: PROGRAM_UPDATE_DATE = SYSDATE,
1564: l_is_yield_correction NUMBER := 0;
1565: CURSOR check_yield_correction IS
1566: SELECT 1
1567: FROM mtl_material_transactions mmt,mtl_transaction_lot_numbers mtln,
1568: mtl_transactions_interface mti, mtl_transaction_lots_interface mtli
1569: WHERE mti.ROWID = p_rowid
1570: AND mtli.ROWID = p_lot_rowid
1571: AND mmt.transaction_id = mti.transaction_sequence_id
1572: AND mmt.transaction_id = mtln.transaction_id
4302: parent_object_type2, --R12 Genealogy enhancements
4303: parent_object_id2, --R12 Genealogy enhancements
4304: parent_object_number2, --R12 Genealogy enhancements
4305: status_id -- Material Status Enhancement - Tracking bug: 13519864
4306: FROM MTL_TRANSACTION_LOTS_INTERFACE
4307: WHERE TRANSACTION_INTERFACE_ID = p_intid;
4308: -- INVCONV fabdi end
4309:
4310:
4458: ERROR_EXPLANATION = substrb(l_error_exp,1,240)
4459: WHERE ROWID = p_rowid
4460: AND ABS(TRANSACTION_QUANTITY) <>
4461: (SELECT ABS(SUM(TRANSACTION_QUANTITY))
4462: FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI
4463: WHERE MTLI.TRANSACTION_INTERFACE_ID = p_intid);
4464:
4465: IF SQL%FOUND THEN
4466: return(FALSE);
4469: IF p_lotuniq = 1 THEN
4470: FND_MESSAGE.set_name('INV','INV_INT_LOTUNIQCODE');
4471: l_error_code := FND_MESSAGE.get;
4472:
4473: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4474: SET LAST_UPDATE_DATE = SYSDATE,
4475: LAST_UPDATED_BY = l_userid,
4476: LAST_UPDATE_LOGIN = l_loginid,
4477: PROGRAM_APPLICATION_ID = l_applid,
4591: IF (l_debug = 1) THEN
4592: inv_log_util.trace('validate_quantities IN (lotcheck) ==> PASS ' , 'INV_TXN_MANAGER_GRP', 9);
4593: END IF;
4594:
4595: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4596: SET TRANSACTION_QUANTITY = l_lotqty,
4597: SECONDARY_TRANSACTION_QUANTITY = l_secondary_quantity
4598: WHERE ROWID = l_lotrowid;
4599: ELSE
4662: -- Expiration date was getting reset and hence ensured that appropriate value gets updated to MTLI.
4663: -- nsinghi bug#5209065 rework. Added fnd_date.date_to_canonical call.
4664: l_lotexpdate := fnd_date.date_to_canonical(l_expiration_date);
4665:
4666: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4667: SET GRADE_CODE = l_grade_code ,
4668: RETEST_DATE = l_retest_date ,
4669: MATURITY_DATE = l_maturity_date,
4670: PARENT_LOT_NUMBER = l_parent_lot_number,
4778:
4779: FND_MESSAGE.set_name('INV','INV_INT_UOMCONVCODE');
4780: l_error_code := FND_MESSAGE.get;
4781:
4782: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4783: SET LAST_UPDATE_DATE = SYSDATE,
4784: LAST_UPDATED_BY = l_userid,
4785: LAST_UPDATE_LOGIN = l_loginid,
4786: PROGRAM_APPLICATION_ID = l_applid,
4829: l_error_exp := FND_MESSAGE.get;
4830: FND_MESSAGE.set_name('INV','INV_LOT_EXPREQD');
4831: l_error_code := FND_MESSAGE.get;
4832:
4833: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4834: SET LAST_UPDATE_DATE = SYSDATE,
4835: LAST_UPDATED_BY = l_userid,
4836: LAST_UPDATE_LOGIN = l_loginid,
4837: PROGRAM_APPLICATION_ID = l_applid,
4886: EXCEPTION
4887: WHEN NO_DATA_FOUND THEN
4888: loaderrmsg('INV_INT_SERMISCODE','INV_INT_SERMISEXP');
4889:
4890: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4891: SET LAST_UPDATE_DATE = SYSDATE,
4892: LAST_UPDATED_BY = l_userid,
4893: LAST_UPDATE_LOGIN = l_loginid,
4894: PROGRAM_APPLICATION_ID = l_applid,
4919: WHERE TRANSACTION_INTERFACE_ID = l_sertempid;
4920: END IF;
4921:
4922: END IF;
4923: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4924: SET LAST_UPDATE_DATE = SYSDATE,
4925: LAST_UPDATED_BY = l_userid,
4926: LAST_UPDATE_LOGIN = l_loginid,
4927: PROGRAM_APPLICATION_ID = l_applid,
4947: FND_MESSAGE.set_name('INV', 'INV_STATUS_COMINGLING');
4948: l_error_code := 'INV_STATUS_COMINGLING';
4949: l_error_exp := FND_MESSAGE.get;
4950:
4951: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
4952: SET LAST_UPDATE_DATE = SYSDATE,
4953: LAST_UPDATED_BY = l_userid,
4954: LAST_UPDATE_LOGIN = l_loginid,
4955: PROGRAM_APPLICATION_ID = l_applid,
4992: AND EXISTS
4993: (
4994: select 'comingling exists'
4995: from mtl_onhand_quantities_detail moqd,
4996: mtl_transaction_lots_interface mtli
4997: WHERE moqd.organization_id = mti.organization_id
4998: AND moqd.inventory_item_id = mti.inventory_item_id
4999: and moqd.subinventory_code = mti.subinventory_code
5000: and nvl(moqd.locator_id, -9999) = nvl(mti.locator_id, -9999)
5871: CURSOR lots IS
5872: SELECT ROWID
5873: , lot_number
5874: , status_id
5875: FROM MTL_TRANSACTION_LOTS_INTERFACE
5876: WHERE transaction_interface_id = p_txn_interface_id;
5877:
5878: Begin
5879: IF (l_debug = 1) THEN
5931: IF (l_debug = 1) THEN
5932: inv_log_util.trace('Before Update of MTLI', 'INV_TXN_MANAGER_GRP','1');
5933: End If;
5934:
5935: UPDATE mtl_transaction_lots_interface
5936: SET status_id = l_mtli_status_id
5937: ,last_updated_by = fnd_global.user_id
5938: ,last_update_date = sysdate
5939: ,last_update_login = fnd_global.login_id
5964: *
5965: * validate_lot_serial_for_rcpt()
5966: * SDPAUL Bug# 5710830
5967: * This private procedure is used to validate a set of
5968: * MTL_TRANSACTION_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE records
5969: * and inserts them into the corresponding master tables.
5970: * These validations are only needed for Receipt into stores transaction -> 27
5971: * and for the transaction sources -> 3,6 and 13.
5972: *
6071: , TERRITORY_CODE
6072: , SUPPLIER_LOT_NUMBER
6073: , VENDOR_NAME
6074: , SERIAL_TRANSACTION_TEMP_ID
6075: FROM MTL_TRANSACTION_LOTS_INTERFACE
6076: WHERE TRANSACTION_INTERFACE_ID = p_interface_id;
6077:
6078: CURSOR cur_MSNI(interface_id NUMBER) IS
6079: SELECT FM_SERIAL_NUMBER
8038: END IF;
8039:
8040: SELECT NVL(SUM(ABS(NVL(mtli.transaction_quantity,0))), 0)
8041: INTO l_lot_ser_qty
8042: FROM mtl_transaction_lots_interface mtli
8043: WHERE mtli.transaction_interface_id =l_intid
8044: AND ABS(nvl(mtli.primary_quantity, inv_convert.inv_um_convert
8045: (l_itemid,5,mtli.transaction_quantity,l_trxuom,l_priuom,'','')))
8046: = (SELECT SUM(get_serial_diff_wrp
8178: IF l_intid IS NOT NULL THEN
8179: BEGIN
8180: SELECT 1
8181: into l_tnum
8182: FROM MTL_TRANSACTION_LOTS_INTERFACE
8183: WHERE TRANSACTION_INTERFACE_ID = l_intid
8184: AND ROWNUM < 2;
8185:
8186: /**********************************************************
8211: END IF;
8212:
8213: SELECT SUM(PRIMARY_QUANTITY)
8214: INTO l_lot_ser_qty
8215: FROM mtl_transaction_lots_interface
8216: WHERE transaction_interface_id =l_intid;
8217: inv_log_util.trace('total lot primary quantity '||l_lot_ser_qty,'INV_TXN_MANAGER_GRP', 9);
8218:
8219: EXCEPTION
8245: RAISE fnd_api.g_exc_error;
8246: END IF;--l_intid is null
8247: ELSE
8248: IF l_intid IS NOT NULL THEN
8249: DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
8250: WHERE TRANSACTION_INTERFACE_ID = l_intid;
8251: END IF;
8252:
8253: /* Additional checking for Dynamic SerCtrl and srctype = 8
8801: inv_log_util.trace('update secondary quantity on line as sum of lot level secondary quantities', 'INV_TXN_MANAGER_GRP', 9);
8802: END IF;
8803: UPDATE MTL_TRANSACTIONS_INTERFACE MTI
8804: SET secondary_transaction_quantity = (SELECT SUM(SECONDARY_TRANSACTION_QUANTITY)
8805: FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI
8806: WHERE MTLI.TRANSACTION_INTERFACE_ID = p_line_Rec_Type.TRANSACTION_INTERFACE_ID)
8807: WHERE ROWID = l_rowid;
8808: END IF;
8809:
8935: OR EXISTS
8936: (
8937: select msn.serial_number, msn.group_mark_id,mti.transaction_interface_id
8938: FROM mtl_serial_numbers msn, mtl_serial_numbers_interface msni,
8939: mtl_transactions_interface mti,mtl_transaction_lots_interface mtli,
8940: mtl_material_transactions_temp mmtt
8941: WHERE msn.serial_number BETWEEN msni.fm_serial_number AND msni.to_serial_number
8942: AND Length(msn.serial_number) = Length(msni.fm_serial_number)
8943: AND msn.current_organization_id = mti.organization_id
10735: parent_item_id, --R12 Genealogy enhancements
10736: parent_object_type2, --R12 Genealogy enhancements
10737: parent_object_id2, --R12 Genealogy enhancements
10738: parent_object_number2 --R12 Genealogy enhancements
10739: FROM MTL_TRANSACTION_LOTS_INTERFACE
10740: WHERE TRANSACTION_INTERFACE_ID IN (
10741: SELECT TRANSACTION_INTERFACE_ID
10742: FROM MTL_TRANSACTIONS_INTERFACE MTI
10743: WHERE mti.TRANSACTION_HEADER_ID = p_header_id
10930: , inv_globals.g_type_inv_lot_translate
10931: )
10932: UNION ALL
10933: SELECT SERIAL_TRANSACTION_TEMP_ID
10934: FROM MTL_TRANSACTION_LOTS_INTERFACE
10935: WHERE TRANSACTION_INTERFACE_ID IN (
10936: SELECT TRANSACTION_INTERFACE_ID
10937: FROM MTL_TRANSACTIONS_INTERFACE
10938: WHERE TRANSACTION_HEADER_ID = p_header_id
11327: l_select_stmt :=
11328: l_select_stmt
11329: || ' from mtl_serial_numbers_interface msni,'
11330: || ' mtl_serial_numbers msn,'
11331: || ' mtl_transaction_lots_interface mtli,'
11332: || ' mtl_transactions_interface mti'
11333: || ' where mti.parent_id = :b_parent_id'
11334: || ' and mti.transaction_interface_id <> mti.parent_id'
11335: || ' and mtli.transaction_interface_id = mti.transaction_interface_id'
11787: , parent_item_id
11788: , parent_object_type2
11789: , parent_object_id2
11790: , parent_object_number2 --R12 Genealogy enhancements
11791: FROM mtl_transaction_lots_interface
11792: WHERE transaction_interface_id = p_transaction_interface_id;
11793:
11794: CURSOR msni_csr (l_serial_transaction_temp_id IN NUMBER)
11795: IS
12082: CURSOR mtli_parent_lots_csr (l_transaction_interface_id IN NUMBER)
12083: IS
12084: SELECT mtli.lot_number
12085: , mtli.serial_transaction_temp_id
12086: FROM mtl_transaction_lots_interface mtli
12087: WHERE mtli.transaction_interface_id =
12088: (SELECT mti.transaction_interface_id
12089: FROM mtl_transactions_interface mti
12090: WHERE mti.parent_id = l_transaction_interface_id
13482: ,l_old_sub_code
13483: ,l_old_locator_id
13484: ,l_old_lpn_id
13485: FROM mtl_transactions_interface mti
13486: ,mtl_transaction_lots_interface mtli
13487: WHERE mti.transaction_interface_id = mtli.transaction_interface_id
13488: AND mti.transaction_interface_id = mti.parent_id
13489: AND mti.transaction_interface_id = l_mti_csr.parent_id;
13490: IF(l_old_item_id <> l_mti_csr.inventory_item_id) THEN
13781: END IF;
13782:
13783: SELECT lot_number
13784: INTO l_parent_object_number2
13785: FROM mtl_transaction_lots_interface
13786: WHERE transaction_interface_id = l_mti_csr.parent_id;
13787:
13788: SELECT serial_transaction_temp_id
13789: INTO l_serial_temp_id
14221: decode(p_line_rec_type.TRANSACTION_ACTION_ID,2,p_line_rec_type.TRANSFER_SUBINVENTORY,28,p_line_rec_type.TRANSFER_SUBINVENTORY,5,p_line_rec_type.transfer_subinventory,null) TRANSFER_SUBINVENTORY,
14222: p_line_rec_type.TRANSFER_LOCATOR,
14223: p_line_rec_type.transaction_date,
14224: MP.NEGATIVE_INV_RECEIPT_CODE
14225: FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI,
14226: MTL_PARAMETERS MP,
14227: MTL_SYSTEM_ITEMS MSI
14228: WHERE MP.ORGANIZATION_ID = p_line_rec_type.ORGANIZATION_ID
14229: --AND MP.NEGATIVE_INV_RECEIPT_CODE = 2 'bug 3679189'
15258: WHERE TRANSACTION_HEADER_ID =l_header_id
15259: AND PROCESS_FLAG <> 3
15260: union all
15261: SELECT SERIAL_TRANSACTION_TEMP_ID
15262: FROM MTL_TRANSACTION_LOTS_INTERFACE
15263: WHERE TRANSACTION_INTERFACE_ID
15264: IN (
15265: SELECT TRANSACTION_INTERFACE_ID
15266: FROM MTL_TRANSACTIONS_INTERFACE
15265: SELECT TRANSACTION_INTERFACE_ID
15266: FROM MTL_TRANSACTIONS_INTERFACE
15267: WHERE TRANSACTION_HEADER_ID = l_header_id
15268: AND PROCESS_FLAG <> 3 )) ;
15269: DELETE FROM MTL_TRANSACTION_LOTS_INTERFACE
15270: WHERE TRANSACTION_INTERFACE_ID IN
15271: (SELECT TRANSACTION_INTERFACE_ID
15272: FROM MTL_TRANSACTIONS_INTERFACE
15273: WHERE TRANSACTION_HEADER_ID =l_header_id
15953: /******************* END Perform Date validation logic ********************/
15954: EXCEPTION
15955: WHEN FND_API.G_EXC_ERROR THEN
15956: x_return_status := FND_API.G_RET_STS_ERROR;
15957: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
15958: SET LAST_UPDATE_DATE = SYSDATE,
15959: LAST_UPDATED_BY = l_userid,
15960: LAST_UPDATE_LOGIN = l_loginid,
15961: PROGRAM_APPLICATION_ID = l_applid,
15984: end if;
15985:
15986: l_error_code := x_msg_data;
15987: l_error_exp := x_msg_data;
15988: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
15989: SET LAST_UPDATE_DATE = SYSDATE,
15990: LAST_UPDATED_BY = l_userid,
15991: LAST_UPDATE_LOGIN = l_loginid,
15992: PROGRAM_APPLICATION_ID = l_applid,
16016:
16017: l_error_code := x_msg_data;
16018: l_error_exp := x_msg_data;
16019:
16020: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
16021: SET LAST_UPDATE_DATE = SYSDATE,
16022: LAST_UPDATED_BY = l_userid,
16023: LAST_UPDATE_LOGIN = l_loginid,
16024: PROGRAM_APPLICATION_ID = l_applid,
16169: IF l_object_id IS NOT NULL OR l_object_number IS NOT NULL OR
16170: l_object_number2 IS NOT NULL OR l_object_id2 IS NOT NULL
16171: THEN
16172: IF p_table = 'MTLI' THEN
16173: UPDATE MTL_TRANSACTION_LOTS_INTERFACE MTLI
16174: SET parent_object_id = l_object_id
16175: ,parent_object_id2 = l_object_id2
16176: ,parent_object_number = l_object_number
16177: ,parent_object_number2 = l_object_number2