Product(s):	APM Implementation and Performance Management
	Version(s):	7
	Environment:	N/A
	Area:	N/A
	Subarea:	N/A

Problem Description

An APM user with EAM running on an Oracle database recently upgraded from release 7.16 to 7.19. Post upgrade smart client crashes when certain configurations are accessed or export to excel is being invoked. Configurations where the problem occurs are:

1. Invoicing view > Liabilities tab > open liabilities configuration when exporting to excel.

2. Requisition class, Activities view when Requisition has open lines with estimates

3. Asset class > Production view > Default configurations

The following log (fatal) error appears:

2023-08-01 14:36:48.5673Z Fatal RCLARK-L7520 ASHGROVE\rclark Ivara.OTAssert [47] File: C:\a\1\s\framework\oqpers\database.cpp(2975) :
================= An error has occurred =============================================================Severe Database Error:
DBErrorType = 'dbOther'
Native SQL Code = 979
ODBC sqlstate = S1000
ODBC errortext = '[Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression
'
File C:\a\1\s\framework\oqpers\database.cpp Line 2905

This is preceded by a debug level log entry which shows an incorrect sql statement showing UPPER being applied to the grouping:

2023-08-01 14:36:48.3798Z Debug Computername Domain\username Ivara.Pers.Database.Access [47] File: C:\a\1\s\framework\oqpers\Zedsql.cpp(850) : <hstmt xxxxxxxxxxxxxxxxxx> SQLPrepare: SQL String <SELECT nvl(s1.SupplierNumb,''),nvl(s1.SupplierName,''),SUM(p.ValueRemaini_amt),1875000000,1875000000,1875000000 FROM PRO.PurchaseLiability p,FND.Site s,PRO.Supplier s1 WHERE (p.Site_oi=s.Siteoi (+) AND p.Supplier_oi=s1.Supplieroi (+) ) AND ((s.Siteoi=? AND ((p.Status=1 OR p.Status=10)))) GROUP BY NVL(s1.SupplierNumb,''),NVL(UPPER(S1.SUPPLIERNAME),'') ORDER BY SUM(p.ValueRemaini_amt) DESC,nvl(UPPER(S1.SUPPLIERNAME),'') ASC/* SliceContext MCLBConfigurationOI=1000028585 */ >
2023-08-01 14:36:48.3798Z Debug Computername Domain\username Ivara.Pers.Database.Access [47] File: C:\a\1\s\framework\oqpers\Zodbc.cpp(456) : <hstmt  xxxxxxxxxxxxxxxxxx><hdbc  xxxxxxxxxxxxxxxxxx> oODBC::dbPrepareCommon: getting stmt from hdbc. <SQL SELECT nvl(s1.SupplierNumb,''),nvl(s1.SupplierName,''),SUM(p.ValueRemaini_amt),1875000000,1875000000,1875000000 FROM PRO.PurchaseLiability p,FND.Site s,PRO.Supplier s1 WHERE (p.Site_oi=s.Siteoi (+) AND p.Supplier_oi=s1.Supplieroi (+) ) AND ((s.Siteoi=? AND ((p.Status=1 OR p.Status=10)))) GROUP BY NVL(s1.SupplierNumb,''),NVL(UPPER(S1.SUPPLIERNAME),'') ORDER BY SUM(p.ValueRemaini_amt) DESC,nvl(UPPER(S1.SUPPLIERNAME),'') ASC/* SliceContext MCLBConfigurationOI=1000028585 */ >

Solution

This has been reported as a bug, ADO 1243362.