The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*) INTO l_count_rec
FROM mtl_system_items MSI , mtl_serial_numbers MSN
WHERE MSN.serial_number = p_asset_number
AND MSN.gen_object_id = p_maintenance_object_id
AND MSN.inventory_item_id = MSI.inventory_item_id
AND MSI.inventory_item_id = p_asset_group_id
AND MSI.organization_id = p_creation_organization_id;
SELECT count(*) INTO l_count_rec
FROM mtl_system_items
WHERE inventory_item_id = p_asset_group_id
AND organization_id = p_creation_organization_id;
SELECT
msn.gen_object_id
FROM
mtl_serial_numbers msn,
mtl_system_items_b msi
WHERE
msn.inventory_item_id = p_inventory_item_id and
msn.serial_number= p_serial_number and
msn.current_organization_id = p_organization_id and
msi.inventory_item_id = msn.inventory_item_id and
msi.eam_item_type = 1 and
msi.organization_id = msn.current_organization_id and
msn.current_status=3;
select network_asset_flag into l_asset_route from mtl_serial_numbers where gen_object_id = p_object_id;
SELECT COUNT(*) INTO l_count
FROM MTL_EAM_NETWORK_ASSETS
WHERE network_item_id= p_network_ITEM_id
AND network_serial_number= p_network_serial_number
AND inventory_item_id = p_inventory_item_id
AND serial_number= p_serial_number
AND organization_id = p_organization_id
AND decode(p_create_flag, 0, NETWORK_ASSOCIATION_ID,1) =
decode(p_create_flag, 0, p_network_association_id,1);
PROCEDURE insert_asset_routes
(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
p_commit IN VARCHAR2:= FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
P_ORGANIZATION_ID IN NUMBER ,
P_START_DATE_ACTIVE IN DATE default null,
P_END_DATE_ACTIVE IN DATE default null,
P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
P_ATTRIBUTE1 IN VARCHAR2 default null,
P_ATTRIBUTE2 IN VARCHAR2 default null,
P_ATTRIBUTE3 IN VARCHAR2 default null,
P_ATTRIBUTE4 IN VARCHAR2 default null,
P_ATTRIBUTE5 IN VARCHAR2 default null,
P_ATTRIBUTE6 IN VARCHAR2 default null,
P_ATTRIBUTE7 IN VARCHAR2 default null,
P_ATTRIBUTE8 IN VARCHAR2 default null,
P_ATTRIBUTE9 IN VARCHAR2 default null,
P_ATTRIBUTE10 IN VARCHAR2 default null,
P_ATTRIBUTE11 IN VARCHAR2 default null,
P_ATTRIBUTE12 IN VARCHAR2 default null,
P_ATTRIBUTE13 IN VARCHAR2 default null,
P_ATTRIBUTE14 IN VARCHAR2 default null,
P_ATTRIBUTE15 IN VARCHAR2 default null,
P_NETWORK_ITEM_ID IN NUMBER ,
P_NETWORK_SERIAL_NUMBER IN VARCHAR2 ,
P_INVENTORY_ITEM_ID IN NUMBER ,
P_SERIAL_NUMBER IN VARCHAR2 ,
P_NETWORK_OBJECT_TYPE IN NUMBER default null ,
P_NETWORK_OBJECT_ID IN NUMBER default null ,
P_MAINTENANCE_OBJECT_TYPE IN NUMBER default null ,
P_MAINTENANCE_OBJECT_ID IN NUMBER default null ,
P_NETWORK_ASSET_NUMBER IN VARCHAR2 default null ,
P_ASSET_NUMBER IN VARCHAR2 default null
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'APIname';
SAVEPOINT insert_asset_routes;
select instance_id into l_network_object_id
from csi_item_instances
where instance_number = p_network_asset_number;
select instance_id into l_network_object_id
from csi_item_instances
where serial_number = l_network_serial_number
and inventory_item_id = l_network_item_id;
select cii.serial_number, cii.inventory_item_id
into l_network_serial_number, l_network_item_id
from csi_item_instances cii
where cii.instance_id = l_network_object_id;
select count(*) into l_count
from csi_item_instances cii
where cii.instance_id = l_network_object_id
and cii.serial_number = l_network_serial_number
and cii.inventory_item_id = l_network_item_id
and nvl(cii.network_asset_flag, 'N') = 'Y';
select instance_id into l_maintenance_object_id
from csi_item_instances
where instance_number = p_asset_number;
select instance_id into l_maintenance_object_id
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_asset_group_id;
select serial_number, inventory_item_id into
l_serial_number, l_asset_group_id
from csi_item_instances
where instance_id = l_maintenance_object_id;
select count(*) into l_count
from csi_item_instances cii
where cii.instance_id = l_maintenance_object_id
and cii.serial_number = l_serial_number
and cii.inventory_item_id = l_asset_group_id
and nvl(cii.network_asset_flag, 'N') = 'N';
select count(*) into l_count
from mtl_eam_network_assets mena
where mena.network_object_id = l_network_object_id
and mena.network_object_type = l_network_object_type
and mena.maintenance_object_id = l_maintenance_object_id
and mena.maintenance_object_type = l_maintenance_object_type;
select MTL_EAM_NETWORK_ASSETS_S.NEXTVAL into l_network_id from dual;
INSERT INTO MTL_EAM_NETWORK_ASSETS (
NETWORK_ASSOCIATION_ID ,
ORGANIZATION_ID ,
NETWORK_OBJECT_TYPE ,
NETWORK_OBJECT_ID ,
MAINTENANCE_OBJECT_TYPE ,
MAINTENANCE_OBJECT_ID ,
NETWORK_ITEM_ID ,
NETWORK_SERIAL_NUMBER ,
INVENTORY_ITEM_ID ,
SERIAL_NUMBER ,
START_DATE_ACTIVE ,
END_DATE_ACTIVE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)VALUES
(
l_network_id ,
/*P_ORGANIZATION_ID ,*/
l_org_id,
l_NETWORK_OBJECT_TYPE ,
l_NETWORK_OBJECT_ID ,
/*P_MAINTENANCE_OBJECT_TYPE ,
P_MAINTENANCE_OBJECT_ID ,*/
l_maintenance_object_type,
l_maintenance_object_id ,
l_NETWORK_ITEM_ID ,
l_NETWORK_SERIAL_NUMBER ,
/*P_INVENTORY_ITEM_ID ,
P_SERIAL_NUMBER ,*/
l_asset_group_id,
l_serial_number ,
P_START_DATE_ACTIVE ,
P_END_DATE_ACTIVE ,
P_ATTRIBUTE_CATEGORY ,
P_ATTRIBUTE1 ,
P_ATTRIBUTE2 ,
P_ATTRIBUTE3 ,
P_ATTRIBUTE4 ,
P_ATTRIBUTE5 ,
P_ATTRIBUTE6 ,
P_ATTRIBUTE7 ,
P_ATTRIBUTE8 ,
P_ATTRIBUTE9 ,
P_ATTRIBUTE10 ,
P_ATTRIBUTE11 ,
P_ATTRIBUTE12 ,
P_ATTRIBUTE13 ,
P_ATTRIBUTE14 ,
P_ATTRIBUTE15 ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
sysdate ,
fnd_global.user_id
);
ROLLBACK TO insert_asset_routes;
ROLLBACK TO insert_asset_routes;
ROLLBACK TO insert_asset_routes;
END insert_asset_routes;
PROCEDURE update_asset_routes
(
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
p_commit IN VARCHAR2:= FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
P_ORGANIZATION_ID IN NUMBER ,
P_START_DATE_ACTIVE IN DATE default null,
P_END_DATE_ACTIVE IN DATE default null,
P_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
P_ATTRIBUTE1 IN VARCHAR2 default null,
P_ATTRIBUTE2 IN VARCHAR2 default null,
P_ATTRIBUTE3 IN VARCHAR2 default null,
P_ATTRIBUTE4 IN VARCHAR2 default null,
P_ATTRIBUTE5 IN VARCHAR2 default null,
P_ATTRIBUTE6 IN VARCHAR2 default null,
P_ATTRIBUTE7 IN VARCHAR2 default null,
P_ATTRIBUTE8 IN VARCHAR2 default null,
P_ATTRIBUTE9 IN VARCHAR2 default null,
P_ATTRIBUTE10 IN VARCHAR2 default null,
P_ATTRIBUTE11 IN VARCHAR2 default null,
P_ATTRIBUTE12 IN VARCHAR2 default null,
P_ATTRIBUTE13 IN VARCHAR2 default null,
P_ATTRIBUTE14 IN VARCHAR2 default null,
P_ATTRIBUTE15 IN VARCHAR2 default null,
P_NETWORK_ITEM_ID IN NUMBER ,
P_NETWORK_SERIAL_NUMBER IN VARCHAR2 ,
P_INVENTORY_ITEM_ID IN NUMBER ,
P_SERIAL_NUMBER IN VARCHAR2 ,
P_NETWORK_ASSOCIATION_ID IN NUMBER ,
P_NETWORK_OBJECT_TYPE IN NUMBER default null ,
P_NETWORK_OBJECT_ID IN NUMBER default null ,
P_MAINTENANCE_OBJECT_TYPE IN NUMBER default null ,
P_MAINTENANCE_OBJECT_ID IN NUMBER default null ,
P_NETWORK_ASSET_NUMBER IN VARCHAR2 default null ,
P_ASSET_NUMBER IN VARCHAR2 default null
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'APIname';
SAVEPOINT update_asset_routes;
select instance_id into l_network_object_id
from csi_item_instances
where instance_number = p_network_asset_number;
select instance_id into l_network_object_id
from csi_item_instances
where serial_number = l_network_serial_number
and inventory_item_id = l_network_item_id;
select serial_number, inventory_item_id into
l_network_serial_number, l_network_item_id
from csi_item_instances
where instance_id = l_network_object_id;
select count(*) into l_count
from csi_item_instances cii
where cii.instance_id = l_network_object_id
and cii.serial_number = l_network_serial_number
and cii.inventory_item_id = l_network_item_id
and cii.network_asset_flag = 'Y';
select instance_id into l_maintenance_object_id
from csi_item_instances
where instance_number = p_asset_number;
select instance_id into l_maintenance_object_id
from csi_item_instances
where serial_number = l_serial_number
and inventory_item_id = l_asset_group_id;
select serial_number, inventory_item_id into
l_serial_number, l_asset_group_id
from csi_item_instances
where instance_id = l_maintenance_object_id;
select count(*) into l_count
from csi_item_instances cii
where cii.instance_id = l_maintenance_object_id
and cii.serial_number = l_serial_number
and cii.inventory_item_id = l_asset_group_id
and nvl(cii.network_asset_flag,'N') = 'N'; -- added nvl check for bug 15850452
select count(*) into l_count
from mtl_eam_network_assets mena
where mena.network_object_id = l_network_object_id
and mena.network_object_type = l_network_object_type
and mena.maintenance_object_id = l_maintenance_object_id
and mena.maintenance_object_type = l_maintenance_object_type
and mena.network_association_id = p_network_association_id;
UPDATE MTL_EAM_NETWORK_ASSETS
SET
START_DATE_ACTIVE = P_START_DATE_ACTIVE ,
END_DATE_ACTIVE = P_END_DATE_ACTIVE ,
ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 = P_ATTRIBUTE1 ,
ATTRIBUTE2 = P_ATTRIBUTE2 ,
ATTRIBUTE3 = P_ATTRIBUTE3 ,
ATTRIBUTE4 = P_ATTRIBUTE4 ,
ATTRIBUTE5 = P_ATTRIBUTE5 ,
ATTRIBUTE6 = P_ATTRIBUTE6 ,
ATTRIBUTE7 = P_ATTRIBUTE7 ,
ATTRIBUTE8 = P_ATTRIBUTE8 ,
ATTRIBUTE9 = P_ATTRIBUTE9 ,
ATTRIBUTE10 = P_ATTRIBUTE10 ,
ATTRIBUTE11 = P_ATTRIBUTE11 ,
ATTRIBUTE12 = P_ATTRIBUTE12 ,
ATTRIBUTE13 = P_ATTRIBUTE13 ,
ATTRIBUTE14 = P_ATTRIBUTE14 ,
ATTRIBUTE15 = P_ATTRIBUTE15 ,
LAST_UPDATE_LOGIN = fnd_global.login_id ,
LAST_UPDATE_DATE = sysdate ,
LAST_UPDATED_BY = fnd_global.user_id
WHERE NETWORK_ASSOCIATION_ID = P_NETWORK_ASSOCIATION_ID;
ROLLBACK TO update_asset_routes;
ROLLBACK TO update_asset_routes;
ROLLBACK TO update_asset_routes;
END update_asset_routes;