Unexpected Error Searching For Work Orders Under Work Orders Tab (Doc ID 1918354.1)

Last updated on NOVEMBER 17, 2016

Applies to:

Oracle Enterprise Asset Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

When attempting to use Simple Search to query work orders under Work Orders tab, the following error occurs.
Using search criteria such as Asset Type, Asset Number or Department produces the error.
The issue started after applying consolidated patch 14017681: R12.EAM.B.


ERROR

You have encountered an unexpected error. Please contact your system administrator for details. Click here for exception details.

  Error Page
  Exception Details.
  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT wdj.WIP_ENTITY_ID,
  wdj.DESCRIPTION,
  wdj.STATUS_TYPE,
  decode(wdj.FIRM_PLANNED_FLAG,1,'Y','N') as firm_planned_flag,
  wdj.SCHEDULED_START_DATE,
  wdj.SCHEDULED_COMPLETION_DATE,
  wdj.PROJECT_ID,
  wdj.TASK_ID,
  wdj.PRIORITY,
  m18.meaning as SHUTDOWN_TYPE,
  wdj.TAGOUT_REQUIRED,
  wdj.PLAN_MAINTENANCE,
  msi.concatenated_segments,
  msi.inventory_item_id,
  cii.serial_number as asset_serial_number,
  msi.eam_item_type ,
  round((wdj.SCHEDULED_COMPLETION_DATE - wdj.SCHEDULED_START_DATE) * 24, 3) AS duration,
  bd1.department_code as wo_owning_department,
  DECODE(ewod.pending_flag, 'Y', ewsv.work_order_status || ' - ' || fnd_message.get_string('EAM','EAM_PENDING_TEXT'),
  ewsv.work_order_status) AS status_type_disp,
  ml2.meaning as priorityval,
  we.wip_entity_name,
  ml3.meaning as eam_item_type_disp,
  to_char('N') as select_flag, DECODE(ewod.pending_flag,'Y','Disabled',decode(wdj.status_type, 3, 'Complete', 4,'Uncomplete', 'Disabled')) as action_code,
  'UpdateEnabled' as update_switcher,
  to_char('WorkOrder') as sub_tab,
ppv.project_number,
ptv.task_number,
ewod.user_defined_status_id,
DECODE(ewod.pending_flag,'Y',1,2) as pending_flag,
DECODE(ewod.assignment_complete,'1',1,2) as assignment_complete,
ewod.workflow_type,
ewod.warranty_claim_status,
  ewod.material_shortage_flag,
ewod.estimate_id,
wdj.class_code,
wdj.pm_schedule_id,
pm.name as pm_schedule_name,
NVL(wdj.estimation_status,1) as estimation_status,
ewod.cycle_id, ... etc.



STEPS

The issue can be reproduced at will with the following steps:
1. Responsibility: Maintenance Super User
2. Go to Work Order tab
3. Using Simple Search, try to search using criteria such as Asset Type, Asset Number or Department
4. The aforementioned error occurs

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms