My Oracle Support Banner

JBO-27122 When Query for an Asset Number in Self Service (Doc ID 755385.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Enterprise Asset Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
***Checked for relevance on 07-Mar-2014***

Symptoms

-- Problem Statement:
On 12.0.4 , when attempting to query for an Asset number in Self Service page the following error occurs:

ERROR
OASevereException=oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT
msi.concatenated_segments,
cii.serial_number,
CASE
when (length(cii.instance_description ) > 30)
THEN
concat(substr(cii.instance_description , 1, 30), '...')
ELSE
cii.instance_description
END AS descriptive_text,
mck.concatenated_segments asset_category,
ml.meaning asset_criticality,
eomd.accounting_class_code wip_accounting_class_code,
el.location_codes eam_location,
asset_number,
nvl(cii.maintainable_flag, 'Y') maintainable_flag,
nvl(cii.network_asset_flag, 'N') network_asset_flag,
bd.department_code owning_department,
mp.maint_organization_id current_organization_id,
cii.inventory_item_id,
'N' select_flag,
cii.asset_criticality_code asset_criticality_code,
fa.ASSET_NUMBER as fixed_asset_no,
msi.eam_item_type,
cii.instance_id maintenance_object_id,
3 maintenance_object_type,
cii.pn_location_id pn_location_id,
meaavd.concatenated_segments as Attribute,
pl.location_code,
msn_prod.current_organization_id prod_organization_id,
msn_prod.inventory_item_id equipment_item_id,
msn_prod.serial_number eqp_serial_number,
msi_eqp.concatenated_segments as EquipmentItem,
mp.organization_code,
pl.location_alias,
cii.instance_number,
cii.inv_organization_id,
cii.inv_subinventory_name current_subinventory_code,
cii.inv_locator_id as current_locator_id,
nvl(cii.checkin_status,1) checkin_status,
cii.instance_description full_description
,cii.category_id
FROM mtl_parameters mp,
csi_item_instances cii,
eam_org_maint_defaults eomd,
csi_i_assets cia,
fnd_lookup_values ml,
bom_departments bd,
mtl_eam_locations el,
mtl_categories_kfv mck,
mtl_system_items_b_kfv msi,
mtl_serial_numbers msn_prod,
fa_additions_b fa,
mtl_eam_asset_attr_values_dfv meaavd,
mtl_eam_asset_attr_values meaav,
pn_locations_all pl,
mtl_system_items_b_kfv msi_eqp,
mtl_parameters mp_eqp
WHERE
mp.organization_id = cii.last_vld_organization_id
AND cii.last_vld_organization_id = msi.organization_id
AND msi.inventory_item_id = cii.inventory_item_id
AND msi.eam_item_type in (1,3)
AND nvl(cii.active_start_date, sysdate-1) <= sysdate
AND nvl(cii.active_end_date, sysdate+1) >= sysdate
AND msi.serial_number_control_code <> 1
AND cii.instance_id = eomd.object_id (+)
AND eomd.object_type (+) = 50
AND eomd.organization_id (+) = :1
AND cii.asset_criticality_code = ml.lookup_code(+)
AND ml.lookup_type(+) = 'MTL_EAM_ASSET_CRITICALITY'
AND bd.department_id(+) = eomd.owning_department_id
AND eomd.area_id = el.location_id(+)
AND cii.category_id = mck.category_id(+)
AND cii.equipment_gen_object_id = msn_prod.gen_object_id(+)
AND cii.instance_id = cia.instance_id(+)
AND cia.fa_asset_id = fa.asset_id(+)
AND cii. instance_id = meaav. maintenance_object_id (+)
AND meaav. maintenance_object_type (+) = 3
AND meaav.rowid = meaavd.row_id(+)
AND cii.pn_location_id = pl.location_id(+)
AND msn_prod.current_organization_id = mp_eqp.organization_id(+)
AND msn_prod.current_organization_id = msi_eqp.organization_id(+)
AND msn_prod.inventory_item_id = msi_eqp.inventory_item_id(+)
AND msi.eam_item_type = 1
AND ml.language(+) = userenv('LANG')
AND ml.view_application_id(+) = 700
AND ml.security_group_id(+) = 0) QRSLT WHERE (current_organization_id = :2) ORDER BY ASSET_NUMBER
WLcurrentVO=oracle.apps.fnd.wf.worklist.server.OpenWorklistVOImpl@9f83bf
PDF_BLOB=

......................

## Detail 0 ##
java.sql.SQLException: ORA-00904: "MEAAVD"."ROW_ID": invalid identifier


-- Steps To Reproduce:
The issue can be reproduced at will with the following steps:
  Maintenance Super User > Asset (tab) > Enter asset number > click GO.
do not go to next page and got an unexpected error

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.