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

Last updated on MAY 02, 2023

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.