My Oracle Support Banner

EAP: Pivot Grid Wizard Error for AP_S360_DB_AMOUNT_BY_STATUS -- Generic error while executing the Query datasource (268,91) -- MS SQL Server issue (Doc ID 2265515.1)

Last updated on MAY 22, 2019

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On MS SQL Server, using the Pivot Grid Wizard for Pivot Grid Name = AP_S360_DB_AMOUNT_BY_STATUS.  When accessing Step 5 for the Pivot Grid Display page, an error is received. 

Able to access Step 5 on an Oracle environment without any problems.

ERROR
Generic error while executing the Query datasource (268,91) - Image creation failed

App Server Error:
SQL error. Stmt #: 7867 Error Position: 0 Return: 8603 - [Microsoft][SQL Server Native Client 11.0][SQL Server]Each GROUP BY expression must contain at least one column that is not an outer reference.
[Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 818
Failed SQL stmt: SELECT A.AP_DB_ENTRY_STATUS, COUNT(*), SUM( A.GROSS_AMT * B.RATE_MULT/ B.RATE_DIV), 'USD ', GROUPING(A.AP_DB_ENTRY_STATUS) AS DIM1M, (CONVERT(CHAR(10),SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10),121)) FROM PS_AP_DB_AMT_STS_V A, PS_RT_DFLT_VW B WHERE ( B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_RT_DFLT_VW B_ED WHERE B.FROM_CUR = B_ED.FROM_CUR AND B.TO_CUR = B_ED.TO_CUR AND B.RT_TYPE = B_ED.RT_TYPE AND B_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) AND B.RT_TYPE = 'CRRNT' AND B.FROM_CUR = A.TXN_CURRENCY_CD AND A.ENTRY_STATUS NOT IN ('X','T') AND A.VENDOR_SETID = :1 AND B.TO_CUR = :2 AND (( 'C' = 'C' AND DATEADD(MONTH, - 24, { fn CURDATE() }) <= A.ENTERED_DT AND { fn CURDATE() } >= A.ENTERED_DT) OR ( 'C' = 'E' AND DATEADD(MONTH, - 24, '2014-08-14') <= A.ENTERED_DT AND '2014-08-14' >= A.ENTERED_DT)) AND A.VENDOR_ID = :3) GROUP BY ROLLUP(A.AP_DB_ENTRY_STATUS), 'USD ' ORDER BY 1

STEPS
1. Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard
2. Display the Pivot Grid Name = AP_S360_DB_AMOUNT_BY_STATUS - Invoice Amount by Status
3. Go through Steps 1-4
4. For Step 5, the Pivot Grid Display displays the above error

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.