SQLStmtException JBO-27122 Error Accessing Benefits in Self Service (Doc ID 756365.1)

Last updated on DECEMBER 08, 2016

Applies to:

Oracle Advanced Benefits - Version 11.5.10.2 and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

On 11.5.10.2 in Production:

When attempting to use Self Service for benefits, the following error occurs for some employees:

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.
Statement: select plt.name||' - '||pln.name
Plan_Name,
opt.name Option_Name,
ppf.first_name||' '||ppf.last_name || ' ' || ppf.suffix Dependent,
ppf.national_identifier Ssn,
(select HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type)
from per_contact_relationships pcr
where pcr.personal_flag = 'Y'
and pcr.person_id = pen.person_id
and pcr.contact_person_id = ecd.dpnt_person_id
and to_date(:1 ,'rrrr/mm/dd') between nvl(pcr.date_start, to_date(:2
,'rrrr/mm/dd'))
and nvl(pcr.date_end, to_date(:3 ,'rrrr/mm/dd'))
and decode(pcr.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8) <=
(select decode(pcr2.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8)
from per_contact_relationships pcr2
where pcr2.person_id = pcr.person_id
and pcr2.contact_person_id = pcr.contact_person_id
and to_date(:4 ,'rrrr/mm/dd') between nvl(pcr2.date_start,
to_date(:5 ,'rrrr/mm/dd'))
and nvl(pcr2.date_end, to_date(:6 ,'rrrr/mm/dd'))
and pcr2.personal_flag = 'Y'
)
and rownum = 1
) Relation,
ppf.full_name dependent_full_name,
ecd.cvg_strt_dt,
decode(ecd.cvg_thru_dt,to_date('4712/12/31','rrrr/mm/dd'),to_date(null),ecd.cvg_thru_dt) cvg_thru_dt
from per_all_people_f ppf,
ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f ecd,
ben_pl_typ_f plt,
ben_pl_f pln,
ben_opt_f opt,
ben_oipl_f oipl,
ben_per_in_ler pil
where pen.pl_id = pln.pl_id
and to_date(:7 ,'rrrr/mm/dd') between pln.effective_start_date and pln.effective_end_date
and pen.pl_typ_id = plt.pl_typ_id
and to_date(:8 ,'rrrr/mm/dd') between plt.effective_start_date and plt.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and to_date(:9 ,'rrrr/mm/dd') between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
and to_date(:10 ,'rrrr/mm/dd') between ecd.cvg_strt_dt and ecd.cvg_thru_dt
and ecd.cvg_thru_dt <= ecd.effective_end_date
and ecd.per_in_ler_id = pil.per_in_ler_id
and ecd.dpnt_person_id = ppf.person_id
and pen.person_id = :11
and to_date(:12 ,'rrrr/mm/dd') between ppf.effective_start_date
and ppf.effective_end_date
and pen.pgm_id = :13
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.oipl_id = oipl.oipl_id(+)
and oipl.opt_id = opt.opt_id(+)
and decode (opt.opt_id, null, 'N' , opt.invk_wv_opt_flag ) = 'N'
and to_date(:14, 'rrrr/mm/dd') between
oipl.effective_start_date (+) and
oipl.effective_end_date (+)
and to_date(:15 , 'rrrr/mm/dd') between
opt.effective_start_date (+) and
opt.effective_end_date (+)
order by pen.ptip_ordr_num,
pen.plip_ordr_num,
pen.pl_ordr_num,
pen.oipl_ordr_num,
pen.bnft_ordr_num,
Plan_Name,
Option_Name,
ppf.date_of_birth,
ppf.last_name,
ppf.first_name
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)

## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1972)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2559)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2950)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:656)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:582)


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

1. Log into Employee Self-Service and select the Benefits function.
2. Attempt to view employees' benefit enrollments. 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