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

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