AP: AP_S360_DB_AMOUNT_BY_STATUS Pivot Grid Message Generic Error While Executing The Query datasource (268,91) For MSSQL (Doc ID 2265515.1)

Last updated on MAY 12, 2017

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

Error received for AP_S360_DB_AMOUNT_BY_STATUS Pivot Grid Wizard

ERROR:
AP_S360_DB_AMOUNT_BY_STATUS Pivot Grid Message Generic error while executing the Query datasource (268,91) - Image creation failed message

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. Reporting Tools > Pivot Grid > Pivot Grid Wizard > AP_S360_DB_AMOUNT_BY_STATUS - Invoice Amount by Status
2. Go through Steps 1-4
3. For Step 5, the Pivot Grid Display shows the following message for MSSQL:
AP_S360_DB_AMOUNT_BY_STATUS Pivot Grid Message Generic error while executing the Query datasource (268,91) - Image creation failed message



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