4 Replies Latest reply: Mar 29, 2013 7:43 AM by Prajakta Deshpande RSS

    Error while running report in oracle apps

    Prajakta Deshpande
      While running the report in oracle apps, i am getting below error

      Arguments
      ------------
      AS_ON_DATE='2013/03/21 00:00:00'
      sdate='2012/04/01 00:00:00'
      edate='2013/03/31 00:00:00'
      ------------

      Forcing NLS_NUMERIC_CHARACTERS to: '.,' for XDO processing

      APPLLCSP Environment Variable set to :

      Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
      American_America.AL32UTF8

      '.,'

      Enter Password:
      REP-0004: Warning: Unable to open user preference file.
      REP-0300: ORACLE error occurred.
      REP-0069: Internal error
      REP-57054: In-process job terminated:Terminated with error:
      REP-300: ORACLE error occurred.

      I tried to run the same report in report builder, it is working as per the requirement. But it is not working in oracle apps.

      Please help.
      Thanks in advance.
        • 1. Re: Error while running report in oracle apps
          Kishore Rout
          Hi,
          To resolve the warning(REP-0004: Warning: Unable to open user preference file), copy the prefs.ora file from your Reports Builder $ORACLE_HOME/tools/admin/ directory into the Applications $HOME directory.

          Please do the above step, after that if you are getting any error, please paste the same here.

          Mark if this helps you.

          Regards,
          Kishore
          • 2. Re: Error while running report in oracle apps
            Prajakta Deshpande
            Thanks Kishore. I copied the prefs.ora file. So the first warning from the is resolved. But still i am getting error.

            Query: I am trying to get the total absence days for each employee in current financial year.

            SUM((CASE
            WHEN ( (TO_CHAR (TO_DATE (paa.date_start, 'RRRR/MM/DD HH24:MI:SS'),'DD-MON-RRRR') BETWEEN TO_CHAR (TO_DATE (:sdate, 'RRRR/MM/DD HH24:MI:SS'),'DD-MON-RRRR') AND TO_CHAR (TO_DATE (:edate, 'RRRR/MM/DD HH24:MI:SS'),'DD-MON-RRRR'))
            OR (TO_CHAR (TO_DATE (paa.date_end, 'RRRR/MM/DD HH24:MI:SS'),'DD-MON-RRRR') BETWEEN TO_CHAR (TO_DATE (:sdate, 'RRRR/MM/DD HH24:MI:SS'),'DD-MON-RRRR') AND TO_CHAR (TO_DATE (:edate, 'RRRR/MM/DD HH24:MI:SS'),'DD-MON-RRRR')))
            THEN
            (CASE
            when (GREATEST(paa.date_end,to_date(:edate,'dd-mon-yyyy')))= to_date(paa.date_end)
            THEN to_date(:edate,'dd-mon-yyyy')
            ELSE
            TO_DATE(paa.date_end,'DD-MON-YYYY')
            END)
            - (CASE
            when(LEAST(paa.DATE_START,to_date(:sdate,'dd-mon-yyyy')))= to_date(paa.date_start)
            THEN to_date(:sdate,'dd-mon-yyyy')
            ELSE
            TO_DATE(paa.date_start,'DD-MON-YYYY')
            END)
            + 1
            ELSE
            0
            END)) AS "ABSENCE_DAYS"

            sdate(financial year start date) and edate(financial year end date) are input parameters. In oracle apps parameters are defined as FND_STANDARD_DATE.
            paa.date_start and paa.date_end is taken from per_absence_attendances table.

            Error:

            Arguments
            ------------
            as_on_date='2013/03/22 00:00:00'
            sdate='2012/04/01 00:00:00'
            edate='2013/03/31 00:00:00'
            ------------

            Forcing NLS_NUMERIC_CHARACTERS to: '.,' for XDO processing

            APPLLCSP Environment Variable set to :

            Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
            American_America.AL32UTF8

            '.,'

            Enter Password:
            REP-0300: ORACLE error occurred.
            REP-0069: Internal error
            REP-57054: In-process job terminated:Terminated with error:
            REP-300: ORACLE error occurred.

            Please help.

            Edited by: 995343 on Mar 22, 2013 4:17 AM
            • 3. Re: Error while running report in oracle apps
              Kishore Rout
              Hi,
              have you executed the query with the parameter what you are providing while executing the report from concurrent manager from sql plus or sql developer?
              Please try to do that and see if you are getting any oracle error.

              Also ask your DBA to check the alert.log file(from database level) for any error entry during report execution.

              Regards,
              Kishore
              • 4. Re: Error while running report in oracle apps
                Prajakta Deshpande
                Thanks Kishore,

                I made some changes in date format. Now its working properly.