This discussion is archived
4 Replies Latest reply: Mar 29, 2013 5:43 AM by PrajaktaDeshpande RSS

Error while running report in oracle apps

PrajaktaDeshpande Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    PrajaktaDeshpande Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    PrajaktaDeshpande Newbie
    Currently Being Moderated
    Thanks Kishore,

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points