Getting Error JBO-27122 When Searching Territory and Check Box "Display Territories Inheriting These Values" (Doc ID 1439982.1)

Last updated on SEPTEMBER 02, 2016

Applies to:

Oracle Territory Management - Version 12.1.2 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

When Using Territory Search page , by entering Matching attributes criteria and check box "Display Territories Inheriting These Values" , then enter value for Country=(ex: US) , the Search page raised an

Error:
Oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException:
JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM
(SELECT t.terr_id territoryId,
t.name terr_name,
t.num_winners,
t.rank,
t.territory_type_id,
typ.name template_name,
t.start_date_active terr_start_date,
t.end_date_active terr_end_date,
ou.name ou_name,
t.org_id ou_id,
jtu.source_id,
t.parent_territory_id
FROM jtf_terr t,
jtf_terr_types typ,
hr_operating_units ou,
jtf_terr_usgs jtu
WHERE t.org_id = ou.organization_id
AND t.territory_type_id = typ.terr_type_id
AND t.org_id = typ.org_id
AND jtu.terr_id = t.terr_id
AND jtu.org_id = t.org_id) QRSLT WHERE (ou_id = :p_1 AND source_id = :p_2
AND terr_start_date <= :p_3 AND terr_end_date >= :p_4 AND NOT EXISTS (
SELECT null FROM jtf_terr_all jt WHERE jt.end_date_active < :p_5 OR
jt.start_date_active > :p_6 CONNECT BY PRIOR jt.parent_territory_id =
jt.terr_id AND jt.terr_id <> 1 START WITH jt.terr_id = territoryId ) AND
territoryId IN (SELECT jtdr.terr_id FROM JTY_1001_DENORM_ATTR_VALUES jtdr
WHERE jtdr.source_id = :p_7 AND ( (jtdr.q1003_low_value_char like :p_8 ) OR
( jtdr.q1003_cp = 'BETWEEN' AND ( :p_9 between jtdr.q1003_low_value_char
AND jtdr.q1003_high_value_char ) ) ) ))
at
oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAExce
ption.java:996)
at
oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at
oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invok
..

Steps :
- (R) Territory Management
- (N) Territory Administration > Territories
- Click Search button
- Select appropriate OU
- Select Usage = Sales
- Click on 'Show More Options'
- Got Search  territory Page > check box "Display Territories Inheriting These Values"
and enter a value for a Country=(ex: US) and search , then got the error.

Note : CMAs could be used as well.

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