Fluid Approval Tile Pending Approval Count - SQL Statement Does Not Work In DB2 (Doc ID 2102313.1)

Last updated on JUNE 15, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Fluid User Interface

Fluid Approval Tile pending approval count - SQL statement does not work in DB2

When the component behind the Fluid Approvals tile attmepts to get the pending approval count to disaply on the tile, the SQL used does not work in DB2. DB2 encounters a -112 sql error, although no error is displayed online. It simply does not display the pending count. The SQL statement in question is found in method HMAP_APPROVAL.Document.Data.GroupListDataDocument:fetchApprovalTileCountOnly. Here is the SQL statement:

SQLExec("SELECT SUM(COUNT(DISTINCT EOAWPARENT_THREAD)) FROM PS_HMAP_SUMMDTL_VW WHERE " | &sProcessWhere | " AND OPRID = :1 AND EOAWSTEP_STATUS IN ('P', 'H') GROUP BY EOAWPRCS_ID", %UserId, &nTotalCount);


ERROR
-----------------------
-112




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
 1) Login as APA1 at
http://slc05eip.us.oracle.com:8000/psp/e920paxx/?cmd=login
2) Fluid Home > Approvals
3) Message displayed "You have no pending approvals".
4) Check trace file.  Notice the following SQL Errors:
PSAPPSRV.1614 (322) 1-23     09.16.29 38448.278985 SQL error. Function:  
SQLExec("SELECT SUM(COUNT(DISTINCT EOAWPARENT_THREAD)) FROM PS_HMAP_SUMMDTL_VW WHERE " | &sProcessWhere | " AND OPRID = :1 AND EOAWSTEP_STATUS IN ('P', 'H') GROUP BY EOAWPRCS_ID", %UserId, &nTotalCount);
 Error Position:  0
Return:  8601 - [IBM][CLI Driver][DB2] SQL0122N  A SELECT statement with no
GROUP BY clause contains a column name or expression and a column function in
the SELECT clause, or a column name or expression is contained in the SELECT
clause but not in the GROUP BY clause.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot see approval tile count.

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