My Oracle Support Banner

FIN 9.1 BRA LOC: Several Brazilian Statutory Reports on Different Modules (AP, AR, BI, AM) Error With Code ORA-01795 if Statistical Accounts Count Is Greater Than 1000 (Doc ID 2655152.1)

Last updated on APRIL 01, 2020

Applies to:

PeopleSoft Enterprise FIN Receivables Brazil - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

Having more than 1,000 defined Statistical Accounts in the system, which have been stored within delivered Record PS_GL_ACCOUNT_TBL, when running Brazilian Localization Statutory Reports in Accounts Receivable module, such as the below listed ones, the SQR Reports are all abending with the Error Code ORA-01795.

     - Statutory Reports > Accounts Payable > Ledger Activity Aux (SQR Report BRLAPRPR)

     - Statutory Reports > Accounts Payable > Auxiliary Daily (SQR Report APBRL001)

     - Statutory Reports > Accounts Receivable > Ledger Activity Aux (SQR Report BRLARRAZ , Razão de Clientes - AR)

     - Statutory Reports > Accounts Receivable > Auxiliary Daily (SQR Report ARBRL004 , Diário Auxiliar de Clientes)

     - Statutory Reports > Billing > Client Auxiliary Daily (SQR Report BRLDIRAZ)

     - Statutory Reports > Billing > Client Ledger Activity Aux (SQR Report BIDIABBL)

     - Statutory Reports > Other Brazilian Legal Reports > AM Auxiliary Daily (SQR Report AMBRL003)

     - Statutory Reports > Other Brazilian Legal Reports > AM Ledger Activity Auxiliary (SQR Report BRLAMRPR)

This is due to the fact that an Oracle Database cannot have more than 1000 values in an NOT IN clause from a formed SQL Statement, which is what is doing, when trying to filter out all the Statistical Accounts from the generation of balances and amounts.

REPLICATION STEPS:

    1.- Log into the FSCM Online Application Brazilian Localization as a Receivables User
    2.- Navigate to: Statutory Reports > Accounts Receivable > Ledger Activity Aux
    3.- Define a new Run Control with all needed criteria, and click on RUN button
    4.- Select both BRLARBAL, and BRLARRAZ SQR Reports
    5.- Confirm that both SQR Reports have ended up in Error
    6.- Navigate to: Statutory Reports > Accounts Receivable > Auxiliary Daily
    7.- Define a new Run Control with all needed criteria, and click on RUN button
    8.- Select both BRLARBAL, and ARBRL004 SQR Reports
    9.- Confirm that both SQR Reports have also ended up in Error

The same steps can be performed for the remainder of the Brazilian Statutory Reports, with the navigation paths provided above.

ERROR MESSAGES:

    " ARBRL004
      /app/psft/PSHOME/FINBRTST//sqr/arbrl004.sqr: ARBRL004 - SQL Statement =
      SQL Status =  1795, SQL Error  = ORA-01795: maximum number of expressions in a list is 1000
      Error on line 71: (SQR 3301) Program stopped by user request.
      SQR for PeopleSoft: Program Aborting. "

    " BRLARBAL
      (SQR 5528) ORACLE OCIStmtExecute error 1795 in cursor 57:
      ORA-01795: maximum number of expressions in a list is 1000
      Error on line 1847: (SQR 3722) Could not set up cursor.
      SQR for PeopleSoft: Program Aborting. "

    " BRLARRAZ
      (SQR 5528) ORACLE OCIStmtExecute error 1795 in cursor 59:
      ORA-01795: maximum number of expressions in a list is 1000
      Error on line 1847: (SQR 3722) Could not set up cursor.
      SQR for PeopleSoft: Program Aborting. "

Users are unable to launch any of these 2 Statutory Reports, including its balance generator sub-program, and as such, the reports cannot be reviewed.

While Oracle Databases may not be capable of including more than 1000 values in an NOT IN clause, the processes should be able to complete successfully, changing the delivered logic to ensure all Statistical Accounts are being filtered out if needed.

NOTE: In the images/screenshots/examples mentioned and/or the attached document, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision instance).  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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.