E-TR/NV: Issue With Tree Type "Levels Not Used" Failing Nvisions / PS Queries

(Doc ID 2217913.1)

Last updated on APRIL 18, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.55 and later
Information in this document applies to any platform.


When attempting to run a query with InTree criteria that is set with "Levels Not Used" type, the following error occurs.

"File: E:\pt85510b-retail\peopletools\src\pssys\qpm.cppSQL error. Stmt #: 7853 Error Position: 98 Return: 936 - ORA-00936: missing expression Failed SQL stmt: SELECT A.SETID, A.ACCOUNT, TO_CHAR(A.EFFDT,'YYYY-MM-DD') FROM PS_GL_ACCOUNT_TBL A WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_GL_ACCOUNT_TBL A_ED WHERE A.SETID = A_ED.SETID AND A.ACCOUNT = A_ED.ACCOUNT AND A_ED.EFFDT <= SYSDATE) AND)"

The PS Queries are working fine if the tree is set as 'Strictly Enforced' type.

The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Tree Manager > Tree Manager
3 Open a delivered Tree (e.g. ACCTROLLUP )
4. Clone the tree under a different name (ACCTROLLUP_TST)
5. Create a new Query using InTree Criteria, then select the tree and the node
6. Run the Query
7. The error occurs


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