My Oracle Support Banner

Unexpected Error When Restricting By Multiple Plan Types (Doc ID 2140284.1)

Last updated on JANUARY 10, 2017

Applies to:

Oracle Advanced Benefits - Version 12.2.4 and later
Information in this document applies to any platform.


When attempting to select the Individual Compensation Distributions (ICD) function, the following error occurs:

SELECT * FROM (select
txn.effective_start_date Effective
,txn.effective_end_date End_Date
, Type_Name
, Option_Name
  txn.input_value_id1, txn.input_value1,
  txn.input_value_id2, txn.input_value2,
  txn.input_value_id3, txn.input_value3,
  txn.input_value_id4, txn.input_value4,
  txn.input_value_id5, txn.input_value5,
  txn.input_value_id6, txn.input_value6,
  txn.input_value_id7, txn.input_value7,
  txn.input_value_id8, txn.input_value8,
  txn.input_value_id9, txn.input_value9,
  txn.input_value_id10, txn.input_value10,
  txn.input_value_id11, txn.input_value11,
  txn.input_value_id12, txn.input_value12,
  txn.input_value_id13, txn.input_value13,
  txn.input_value_id14, txn.input_value14,
  txn.input_value_id15, txn.input_value15,
  null) screen_value
 ,decode(conf.uom ,'M', ety.input_currency_code,hr_general.decode_lookup('BEN_NNMNTRY_UOM',txn.nnmntry_uom)) units
 ,hr_general.decode_lookup('BEN_ICD_NO_OF_OCCUR',ety.processing_type) Occurrences
 ,txn.pl_id plan_Id
 ,txn.opt_id Opt_Id
 ,txn.element_type_id element_Typ_Id
 ,'I' p_Data_source
 ,txn.icd_transaction_id trans_Id
,hr_general.decode_lookup('BEN_ICD_TXN_STATUS',decode(txn.status,'SP',txn.status,decode(wf.text_value,'Y','PA',txn.status))) status
 ,decode(:1,txn.item_key,decode(txn.dml_operation,'DELETE','View','Update'),'View') allow_update

The issue can be reproduced at will with the following steps:

1)Login into Employee Self Service responsibility
2)Go to ICD function
3)Click on the function
4)Get the error message:


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

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