My Oracle Support Banner

Get Unexpected Error When Trying to Open Existing Projects on Self Service Web Apps (Doc ID 352470.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Foundation - Version 11.5.10.2 and later
Information in this document applies to any platform.
***Checked for relevance on 14-Jan-2014***


Symptoms


In the self service html screen, when trying to open an existing project, the following error occurs:

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during
statement preparation.

Statement: SELECT * FROM ( select distinct ppa.project_id, null
select_checkbox, ppa.name project_name, ppa.segment1 project_number,
ppa.name || ' ( ' || ppa.segment1 || ') ' project_name_number,
ppa.project_type, ppa.project_status_code, pps.project_status_name,
pps.project_system_status_code, decode(ppa.actual_start_date, '',
decode(ppa.scheduled_start_date, '', ppa.target_start_date, ppa.scheduled_start_date),
ppa.actual_start_date) project_start_date, decode(ppa.actual_finish_date, '',
decode(ppa.scheduled_finish_date, '', ppa.target_finish_date, ppa.scheduled_finish_date),
ppa.actual_finish_date) project_end_date, ppa.carrying_out_organization_id
project_carrying_out_org_id, hou.name project_carrying_out_org_name,
PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER(ppa.project_id) project_manager_id,
PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME(ppa.project_id) project_manager_name,
null project_customer_name, to_number(null) project_customer_id,
ppa.record_version_number, ppa.template_flag, to_number(null)
manager_resource_id, ppa.probability_member_id, pro.probability_percentage
project_probability_percentage, ppa.description project_description, FL.meaning
public_sector, ppa.public_sector_flag, ftv.territory_short_name
country_name, pl.region, pl.city, ppt.service_type_code,
null progress_status_code, null progress_status_name, to_date(null)
progress_report_end_date, to_date(null) progress_date, null
progress_status, null progress_status_active, null progress_icon, null
progress_icon_active, psi.column4 psi_column4, psi.column5
psi_column5, psi.column6 psi_column6, psi.column7 psi_column7,
psi.column8 psi_column8, psi.column9 psi_column9, psi.column10
psi_column10, psi.column11 psi_column11, psi.column12 psi_column12,
psi.column13 psi_column13, psi.column14 psi_column14, psi.column15
psi_column15, psi.column16 psi_column16, psi.column17 psi_column17,
psi.column18 psi_column18, psi.column19 psi_column19, psi.column20
psi_column20, psi.column21 psi_column21, psi.column22 psi_column22,
psi.column23 psi_column23, psi.column24 psi_column24, psi.column25
psi_column25, psi.column26 psi_column26, psi.column27 psi_column27,
psi.column28 psi_column28, psi.column29 psi_column29, psi.column30
psi_column30, psi.column31 psi_column31, psi.column32 psi_column32,
psi.column33 psi_column33, psi.projfunc_currency_code currency_code, null
view_psi_columns, ppoa.opportunity_value, ppoa.opp_value_currency_code,
ppoa.projfunc_opp_value, ppa.projfunc_currency_code,
ppoa.project_opp_value, ppa.project_currency_code, null
report_type_name, null report_type_id, ppa.security_level
security_level, pal.meaning security_level_name, ppa.long_name
project_long_name, null role_name, null person_name, null
class_category, null class_code, null project_set_name,
ppa.actual_start_date, ppa.actual_finish_date, ppa.baseline_start_date,
ppa.baseline_finish_date, ppa.scheduled_start_date,
ppa.scheduled_finish_date, ppa.target_start_date,
ppa.target_finish_date, ppa.start_date transaction_start_date,
ppa.completion_date transaction_finish_date, priority.meaning project_priority,
null display_role, null display_class_cat, null display_report FROM
pa_projects_all PPA, PA_STATUS_PROJ_LIST_V PSI,
HR_ALL_ORGANIZATION_UNITS HOU , PA_PROJECT_STATUSES PPS ,
PA_PROBABILITY_MEMBERS PRO , PA_LOCATIONS PL ,
FND_TERRITORIES_TL FTV , PA_PROJECT_TYPES_ALL PPT ,
FND_LOOKUPS FL , pa_project_opp_attrs ppoa ,
pa_lookups PAL , pa_lookups priority , (select
decode(employee_id,null,PA_UTILS.GET_PARTY_ID(user_id),employee_id)
resource_source_id, decode(employee_id,null,112,101) resource_type_id from
fnd_user where user_id = FND_GLOBAL.user_id) login_user , pa_project_ctx_search ctx
WHERE PPA.TEMPLATE_FLAG = 'N' AND PPA.CARRYING_OUT_ORGANIZATION_ID =
HOU.ORGANIZATION_ID AND PPA.PROJECT_STATUS_CODE = PPS.PROJECT_STATUS_CODE AND
PPA.PROBABILITY_MEMBER_ID = PRO.PROBABILITY_MEMBER_ID (+) AND
PPA.LOCATION_ID = PL.LOCATION_ID (+) AND
PL.COUNTRY_CODE = FTV.TERRITORY_CODE (+) AND
USERENV('LANG') = FTV.LANGUAGE (+) AND PPA.PROJECT_TYPE =
PPT.project_type AND nvl(PPA.ORG_ID,-99) = nvl(PPT.ORG_ID,-99) AND
PPA.PUBLIC_SECTOR_FLAG = FL.LOOKUP_CODE AND FL.LOOKUP_TYPE =
'YES_NO' AND PSI.project_id = PPA.PROJECT_ID and
PPA.project_id = ppoa.project_id AND PAL.lookup_type =
'PA_PROJECT_ACCESS_LEVEL' AND PAL.lookup_code = to_char(PPA.security_level)
AND priority.lookup_type (+) = 'PA_PROJECT_PRIORITY_CODE' AND priority.lookup_code
(+) = ppa.priority_code AND ( 'SUPER_USER' =:1 OR (PPA.security_level = 1 and
login_user.resource_type_id= 101) OR exists( select '1' from
pa_project_parties where project_id = PPA.project_id and
resource_source_id = login_user.resource_source_id and resource_type_id =
login_user.resource_type_id) OR exists( select '1' from fnd_grants
fg, fnd_objects fo where FG.GRANTEE_KEY =
'PER:'||PA_UTILS.GetGlobalEmpId AND FG.GRANTEE_TYPE = 'USER' AND
FG.INSTANCE_TYPE = 'INSTANCE' AND sysdate between fg.start_date and nvl(
fg.end_date, sysdate + 1 ) AND FG.OBJECT_ID = FO.OBJECT_ID AND
FO.OBJ_NAME = 'ORGANIZATION' AND PPA.CARRYING_OUT_ORGANIZATION_ID =
to_number(FG.INSTANCE_PK1_VALUE) )) AND ctx.project_id = ppa.project_id AND
contains(ctx.ctx_description, :2 ) > 0 ) QRSLT WHERE (PROJECT_SYSTEM_STATUS_CODE <> :3 AND
PROJECT_SYSTEM_STATUS_CODE <> :4 AND PROJECT_SYSTEM_STATUS_CODE <> :5 AND
PROJECT_SYSTEM_STATUS_CODE <> :6)

and error:
java.sql.SQLException: ORA-20000: Oracle Text error:
DRG-10599: column is not indexed


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

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.