APPS.WIP_EAMMTLPROC_PRIV dependencies on MTL_SERIAL_NUMBERS

Line 336: from mtl_serial_numbers msn

332: and transaction_source_type_id = 5
333: and rebuild_item_id is not null
334: and rebuild_serial_number is not null
335: and not exists(select 1 --subquery verifies rebuild sn is predefined or (issued out/in stores and in the asset's genealogy)
336: from mtl_serial_numbers msn
337: where mti.rebuild_item_id = msn.inventory_item_id
338: and mti.rebuild_serial_number = msn.serial_number
339: and ( msn.current_status = 1 --defined not used
340: or ( msn.current_status in (3,4) --issued out or in stores

Line 343: csi_item_instances cii, mtl_serial_numbers msn_parent

339: and ( msn.current_status = 1 --defined not used
340: or ( msn.current_status in (3,4) --issued out or in stores
341: and exists(select 1
342: from wip_discrete_jobs wdj, mtl_object_genealogy mog,
343: csi_item_instances cii, mtl_serial_numbers msn_parent
344: where wdj.maintenance_object_id = cii.instance_id
345: and wdj.maintenance_object_type = 3
346: and wdj.wip_entity_id = mti.transaction_source_id
347: and cii.inventory_item_id = msn_parent.inventory_item_id

Line 431: from mtl_serial_numbers_temp

427: --we have already checked the serial_control_code so a serial number must be in MSNT
428: if(p_compRec.lotControlCode = wip_constants.no_lot) then
429: select fm_serial_number
430: bulk collect into l_serialNumTbl
431: from mtl_serial_numbers_temp
432: where transaction_temp_id = p_compRec.txnTmpID;
433:
434: select to_serial_number
435: bulk collect into l_to_serialNumTbl

Line 436: from mtl_serial_numbers_temp

432: where transaction_temp_id = p_compRec.txnTmpID;
433:
434: select to_serial_number
435: bulk collect into l_to_serialNumTbl
436: from mtl_serial_numbers_temp
437: where transaction_temp_id = p_compRec.txnTmpID; --bug 10262178
438:
439: else
440: select fm_serial_number

Line 442: from mtl_serial_numbers_temp

438:
439: else
440: select fm_serial_number
441: bulk collect into l_serialNumTbl
442: from mtl_serial_numbers_temp
443: where transaction_temp_id = (select serial_transaction_temp_id
444: from mtl_transaction_lots_temp
445: where transaction_temp_id = p_compRec.txnTmpID);
446:

Line 449: from mtl_serial_numbers_temp

445: where transaction_temp_id = p_compRec.txnTmpID);
446:
447: select to_serial_number
448: bulk collect into l_to_serialNumTbl
449: from mtl_serial_numbers_temp
450: where transaction_temp_id = (select serial_transaction_temp_id
451: from mtl_transaction_lots_temp
452: where transaction_temp_id = p_compRec.txnTmpID); --bug 10262178
453:

Line 461: from wip_discrete_jobs wdj, csi_item_instances cii, mtl_serial_numbers msn

457:
458: select wdj.maintenance_object_id, wdj.maintenance_object_type,
459: wdj.maintenance_object_source, msn.gen_object_id
460: into l_maintObjID, l_maintObjType, l_maintObjSrc, l_maintGenObjID
461: from wip_discrete_jobs wdj, csi_item_instances cii, mtl_serial_numbers msn
462: where wdj.wip_entity_id = p_compRec.wipEntityID
463: and wdj.maintenance_object_type = 3
464: and wdj.maintenance_object_id = cii.instance_id
465: and msn.serial_number (+) = cii.serial_number --Modified outer join for bug 6892336