EGL: Journal Approval Page Shows "SQL error occurred. Please consult your system log for details." When Clicking OK To Return to Approval Page From Attachments Link (Doc ID 1667168.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

Customer environment
==================
Application release 9.1
Bundles :   replicated on bundle 29
PeopleTools 8.53
Production

Issue
=====
User wants to use the attachments link on the journal approval page AFTER the final approval is done.

EXPECTATION:
User should be able to navigate to the attachment page (AFTER final approval was completed) and navigate back to Journal approval screen without any errors.

Actual Result
When navigating back to the journal approver screen from the attachment screen, a SQL error message appears. After clicking OK on the error message normal functionality seems to resume with no other know side-affects.

ERROR MESSAGE:
A SQL error occurred. Please consult your system log for details.

SYSTEM LOG ENTRY:
SQL error. Stmt #: 656 Error Position: 401 Return: 2014 - ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Failed SQL stmt: SELECT BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, TO_CHAR(JOURNAL_DATE,'YYYY-MM-DD'), BUSINESS_UNIT_LN, LEDGER_GROUP, ADJUSTING_ENTRY, FISCAL_YEAR, ACCOUNTING_PERIOD, EOAWTHREAD_STATUS, JRNL_TOTAL_DEBITS, JRNL_TOT_CREDITS, JRNL_DESCR, REQUESTOR_ID, CURRENCY_CD, OPRID, GL_APPR_STATUS, TO_CHAR(EOAWSTART_DT,'YYYY-MM-DD'), ENTERED_BY, TO_CHAR(ENTERED_DT,'YYYY-MM-DD'), ATTACHMENT_EXIST, SOURCE, NAME FROM PS_JRNL_AF_SRCH_VW WHERE BUSINESS_UNIT=:1 AND JOURNAL_ID=:2 AND JOURNAL_DATE=TO_DATE(:3,'YYYY-MM-DD') AND BUSINESS_UNIT_LN=:4 FOR UPDATE OF EOAWTHREAD_STATUS


Replication steps
================
1. Identify journal that requires final approval and have attachments
2. Navigate to Worklist (Main Menu>Worklist>Worklist)
3. Select journal identified in step 1
4. Search for journal in Manage GL Journal Approval screen
5. Click on journal link in search results
6. Click Approve in GL Journal Approval screen
7. Click on attachment link
8. Click OK (to return to previous screen) a SQL error message appears


Business Impact
============
Low impact on all final approvers. These users need to find an alternative pathway e.g. Journal entry page to view attachments immediately after the approval was completed instead of using the provided attachment link on the same approval page.

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