E-TR/NV: Issue With Tree Type "Levels Not Used" Failing Nvisions / PS Queries
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
Million Knowledge Articles and hundreds of Community platforms