APPS.EAM_REBUILD SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 6

/*  List of Changes made for IB on update_genealogy procedure

   1 Removed fetching Asset_Group_Id and Asset_Number columns from WDJ from
      the initial query since the columns were not being used anywhere
*/

  procedure update_genealogy(p_tempId IN NUMBER,
                             x_retVal OUT NOCOPY VARCHAR2,
                             x_errMsg OUT NOCOPY VARCHAR2) IS

    l_rebuildSerNum VARCHAR2(30);

Line: 29

    select mmtt.rebuild_serial_number,
           mmtt.rebuild_item_id,
           mmtt.organization_id,
           mmtt.transaction_date
     into l_rebuildSerNum,
           l_rebuildItemID,
           l_orgID,
           l_txnDate
      from mtl_material_transactions_temp mmtt
     where mmtt.transaction_temp_id = p_tempId;

Line: 42

    l_serial_status := 0; --fix for 3733049.initialise so that update_eam_genealogy will be called even if there is no serial number

Line: 46

            select current_status
             into l_serial_status
             from mtl_serial_numbers
            where serial_number = l_rebuildSerNum
              and inventory_item_id = l_rebuildItemID;

Line: 55

		    wip_eam_genealogy_pvt.update_eam_genealogy(
			p_api_version => 1.0,
			p_object_type => 2, -- serial number
			p_serial_number => l_rebuildSerNum,
			p_inventory_item_id => l_rebuildItemID,
			p_organization_id => l_orgID,
			p_genealogy_type => 5, --asset/item releationship
			p_end_date_active => l_txnDate,
			x_return_status => x_retVal,
			x_msg_count => l_msgCount,
			x_msg_data => x_errMsg);

Line: 72

    fnd_message.set_token('ERROR_TEXT', 'eam_rebuild.update_genealogy');

Line: 74

  end update_genealogy;

Line: 95

    SELECT mmtt.created_by,
          mmtt.last_update_login,
          mmtt.last_updated_by,
          mmtt.request_id,
          mmtt.program_application_id,
          mmtt.program_id,
          mmtt.program_update_date,
          mmtt.rebuild_job_name,
          msi.description,
          mmtt.rebuild_activity_id,
          mmtt.organization_id,
          mmtt.rebuild_item_id,
          mmtt.rebuild_serial_number,
          mmtt.transaction_source_id,
          eomd.activity_type_code,
          eomd.activity_cause_code,
          to_number(meaa.priority_code) as priority_code,
          eomd.owning_department_id,
          eomd.tagging_required_flag,
          to_number(eomd.shutdown_type_code) as shutdown_type_code
     FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
          mtl_system_items msi,
          mtl_eam_asset_activities meaa,
          (select * from eam_org_maint_defaults
		   where organization_id = l_org_id) eomd
          --activity has to be assigned to the work order organization
          -- hence no meed to join on MP.
    WHERE transaction_temp_id = p_tempId
      and mmtt.rebuild_activity_id = msi.inventory_item_id (+)
      and mmtt.organization_id = msi.organization_id (+)
      and mmtt.rebuild_activity_id = meaa.asset_activity_id (+)
      and l_maintenance_object_id = meaa.maintenance_object_id (+)
      and l_maintenance_object_type = meaa.maintenance_object_type(+)
      and eomd.object_type (+) = 60
      and eomd.object_id (+) = meaa.activity_association_id;

Line: 201

       SELECT rebuild_item_id,rebuild_serial_number,organization_id
       INTO l_rebuild_item_id,l_rebuild_serial_number,l_org_id
       FROM MTL_MATERIAL_TRANSACTIONS_TEMP
       where transaction_temp_id=p_tempId;

Line: 209

		     select cii.instance_id into l_maintenance_object_id
		       from csi_item_instances cii, mtl_parameters mp
			where cii.inventory_item_id = l_rebuild_item_id and cii.serial_number = l_rebuild_serial_number
            and mp.organization_id = cii.last_vld_organization_id
            and mp.maint_organization_id =l_org_id;

Line: 222

			SELECT msi.inventory_item_id into l_maintenance_object_id
		         FROM mtl_system_items msi, mtl_parameters mp
			 WHERE msi.inventory_item_id = l_rebuild_item_id
             and mp.organization_id = msi.organization_id
            and mp.maint_organization_id =l_org_id
            and rownum = 1;

Line: 238

      update_genealogy(p_tempId => p_tempId,
                       x_retVal => x_retVal,
                       x_errMsg => x_errMsg);

Line: 242

        ROLLBACK TO REBUILD; --gen update failure