[Home] [Help]
261: and transaction_source_type_id = 5
262: and rebuild_item_id is not null
263: and rebuild_activity_id is not null
264: and not exists (select 1
265: from mtl_eam_asset_activities meaa, csi_item_instances cii, eam_org_maint_defaults eomd
266: where mti.rebuild_activity_id = meaa.asset_activity_id
267: and meaa.activity_association_id = eomd.object_id
268: and eomd.object_type = 60
269: and eomd.organization_id = mti.organization_id
338: and ( msn.current_status = 1 --defined not used
339: or ( msn.current_status in (3,4) --issued out or in stores
340: and exists(select 1
341: from wip_discrete_jobs wdj, mtl_object_genealogy mog,
342: csi_item_instances cii, mtl_serial_numbers msn_parent
343: where wdj.maintenance_object_id = cii.instance_id
344: and wdj.maintenance_object_type = 3
345: and wdj.wip_entity_id = mti.transaction_source_id
346: and cii.inventory_item_id = msn_parent.inventory_item_id
435:
436: select wdj.maintenance_object_id, wdj.maintenance_object_type,
437: wdj.maintenance_object_source, msn.gen_object_id
438: into l_maintObjID, l_maintObjType, l_maintObjSrc, l_maintGenObjID
439: from wip_discrete_jobs wdj, csi_item_instances cii, mtl_serial_numbers msn
440: where wdj.wip_entity_id = p_compRec.wipEntityID
441: and wdj.maintenance_object_type = 3
442: and wdj.maintenance_object_id = cii.instance_id
443: and msn.serial_number (+) = cii.serial_number --Modified outer join for bug 6892336