My Oracle Support Banner

SQLStmtException: JBO-27122: SQL error Trying to visualize all schedules (Doc ID 416745.1)

Last updated on JUNE 29, 2021

Applies to:

Oracle Marketing - Version 11.5.10.0 to 11.5.10.3 [Release 11.5]
Information in this document applies to any platform.

Symptoms

On 11.5.10.2 in Production:
User is trying to visualize all schedules. When search is turned from 'My
Schedule' to 'All schedule' he gets the error:
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122:
SQL error during statement preparation.

Exception Details.

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during
statement preparation. Statement: SELECT * FROM (select itm1.description as proddesc,
prd1.act_product_used_by_id as objid,
look1.meaning offering_type,
'PRODUCT' offering_type_code
from ams_act_products prd1, mtl_system_items_b_kfv itm1, ams_lookups look1
where prd1.inventory_item_id = itm1.inventory_item_id
and prd1.level_type_code = 'PRODUCT'
and prd1.arc_act_product_used_by = :2
and prd1.act_product_used_by_id = :3
and itm1.organization_id = fnd_profile.value('AMS_ITEM_ORGANIZATION_ID')
and look1.lookup_type = 'AMS_CAMPAIGN_OFFERING_TYPES'
and look1.lookup_code = 'PRODUCT'
union
select cat2.category_desc as proddesc,
prd2.act_product_used_by_id as objid,
look2.meaning offering_type,
'PRODUCT_CAT' offering_type_code
from ams_act_products prd2, eni_prod_den_hrchy_parents_v cat2,
ams_lookups look2
where prd2.category_id = cat2.category_id
and prd2.level_type_code = 'FAMILY'
and prd2.arc_act_product_used_by = :4
and prd2.act_product_used_by_id = :5
and look2.lookup_type = 'AMS_CAMPAIGN_OFFERING_TYPES'
and look2.lookup_code = 'PRODUCT_CAT'
union
select cat3.description as proddesc,
prd3.act_offer_used_by_id as objid,
look3.meaning offering_type,
'OFFER' offering_type_code
from ozf_act_offers prd3, qp_list_headers cat3,
ams_lookups look3
where prd3.qp_list_header_id = cat3.list_header_id
and prd3.arc_act_offer_used_by = :6
and prd3.act_offer_used_by_id = :7
and look3.lookup_type = 'AMS_CAMPAIGN_OFFERING_TYPES'
and look3.lookup_code = 'OFFER') QRSLT WHERE OBJID = :1
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
....

STEPS
The issue can be reproduced at will with the following steps:
Campaign Workbench super user
Campaign dashboard
Schedule List
View All Schedule
GO



.

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
References


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