My Oracle Support Banner

EAM 9.2: Asset Management’s Review Interface Transaction Page Returns the Online SQL Error When Trying to Modify the Asset ID and Save. Error - "A SQL error occurred. Please consult your system log for details." (Doc ID 2994654.1)

Last updated on JANUARY 04, 2024

Applies to:

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

Symptoms

Asset Management's Review Interface Transaction page returns the following SQL error when trying to modify the asset ID and save.

Error:

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

Trace error: msg=ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.” for the below sql.

SELECT BUSINESS_UNIT, INTFC_ID, INTFC_LINE_NUM, PROCESS_INSTANCE, ASSET_ID, BOOK, ERRNUM, INTFC_TYPE, MESSAGE_NBR, MESSAGE_SET_NBR FROM PS_INTFC_ERR_VW WHERE BUSINESS_UNIT=:1 AND INTFC_ID=:2 AND INTFC_LINE_NUM=:3 AND PROCESS_INSTANCE=:4 AND ASSET_ID=:5 AND BOOK=' ' AND ERRNUM=:6 FOR UPDATE OF ASSET_ID


Replication steps:

  1. Set the capitalization threshold as 100. (Set Up Financials/Supply Chain > Product Related > Asset Management > Financials > AM Capitalization Thresholds > Capitalization Thresholds)
  2. Create a Voucher with 50 Invoices total and Asset Details, Approved and Posted. (Accounts Payable > Vouchers > Add/Update > Regular Entry > Invoice Information)
  3. Run the INTFAPAM process for the voucher. (Accounts Payable > Batch Processes > Extracts and Loads > Load Assets Request)
  4. Run the AMPS1000 Process to retrieve the voucher to AM. (Asset Management > Send/Receive Information > Retrieve Info from AP/PO)
  5. Get the interface ID for the voucher from the PS_INTFC_PRE_AM table.
  6. Run AMIF1000 Process. (Asset Management > Send/Receive Information > Interface Transactions > Load into AM)
  7. Go to Asset Management > Send/Receive Information > Interface Transactions > Review Interface Transactions. Search for the interface ID. The Asset ID was not created for the interface ID. And the load status became "Excluded". The reason for the error is below - "Capitalization Threshold determines that this asset will be treated as an Expense and it will not be allowed to enter into the Asset System".
  8. Change the Load Status from "Excluded" to "Pending" on the Review Interface Transactions page. This makes the Asset ID available for editing. Change the Asset ID from 'NEXT' to a value and try to save. The above error is received online.

 

Replication Steps



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
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.