The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* 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);
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;
l_serial_status := 0; --fix for 3733049.initialise so that update_eam_genealogy will be called even if there is no serial number
select current_status
into l_serial_status
from mtl_serial_numbers
where serial_number = l_rebuildSerNum
and inventory_item_id = l_rebuildItemID;
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);
fnd_message.set_token('ERROR_TEXT', 'eam_rebuild.update_genealogy');
end update_genealogy;
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;
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;
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;
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;
update_genealogy(p_tempId => p_tempId,
x_retVal => x_retVal,
x_errMsg => x_errMsg);
ROLLBACK TO REBUILD; --gen update failure