Organization Hierarchy Page Error ORA-06512: At "APPS.PQH_GENERIC_HIERARCHY_PACKAGE", Line 252

(Doc ID 1636276.1)

Last updated on DECEMBER 22, 2016

Applies to:

Oracle Human Resources - Version 12.1.1 and later
Information in this document applies to any platform.


On : 12.1.1 version, Hierarchy Diagrammers

When attempting to create Organization Hierarchy or view an existing organization hierarchy, the following error occurs.

oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select hr_general.decode_lookup('HIERARCHY_NODE_TYPE',gnd.node_type) Type,
  (gnd.entity_id,gnd.parent_hierarchy_node_id,gnd.hierarchy_node_id) Name,
  gnd.hierarchy_node_id NodeId,
  ghr.type HierarchyType,
  -1*gnd.hierarchy_version_id) ParentNodeId,
pqh_generic_hierarchy_package.chk_if_parent_node_type(gnd.node_type,ghr.type) AddChild,
  'U' UpdateNode,
  'D' DeleteNode,
  gnd.node_type TypeCode,
  gnd.object_version_number Ovn,
  decode(ghr.business_group_id,null,'Y','N') Global
from per_gen_hierarchy_nodes gnd,
  per_gen_hierarchy_versions gvr,
  per_gen_hierarchy ghr
where gnd.hierarchy_version_id = gvr.hierarchy_version_id
and gvr.hierarchy_id = ghr.hierarchy_id) QRSLT WHERE PARENTNODEID = :1
at oracle.apps.fnd.framework.OAException.wrapperException(
at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(
at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(
at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(
at _OA._jspService(

## Detail 0 ##
java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

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

1. Go into responsibility 'Environmental Accounting and Reporting Administrator'.

2. Navigate Setup -> Organization Hierarchy

3. Next page is 'Maintain Hierarchy Content'.
  This page shows the 'Search' options to search an existing organization hierarchy as well as create new hierarchies.

4. Scenario 1: While viewing an existing hierarchy:
    4.1. Search for 'Active' organizations and result set shows a number of organization hierarchies.
    4.2. Next, click on any of the organization hierarchies, for eg, 'GSP Purge Process' and page errors out with OA exception.
    4.3. You have encountered an unexpected error. Please contact the System Administrator for assistance.
    Click here for exception details.

   On clicking the error it shows the OA exception.

5. Scenario 2: While creating new hierarchy.
    5.1. Click on 'Create Hierarchy'.
    5.2. Enter details for new hierarchy and click on 'Continue'.
    5.3. The next page is 'Maintain Nodes'.
          Here click on '+' icon to add a child organization.
    5.4. Next page is 'Add Child Node'.
          Add a child node details and click on 'Apply' button.
    5.5. This shows OA page exception.




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