Tag Archives: Oracle Reports

Troubleshooting Custom Reports

Posted by on September 15, 2008 No comments

Running Report from Command Prompt

To run the pay_register.rdf custom report from the Command Prompt, enter the following command at the prompt:

C:> C:oracleproduct10.2.0db_2binrwbuilder.exe report="C:Documents and Settingsuser01DesktopReports Tuning Issuetest.aippay_register.rdf" userid=apps/apps@vis2.world tracemode=TRACE_APPEND tracefile=c:temppay_register01.txt traceopts=(trace_SQL,TRACE_PLS,trace_prf,trace_tms)

The report loads in Reports Builder.

1. Compile the packages by typing Shift+Ctrl+K.

2. Run the Paper Layout version of the report by Shift+Ctrl+R.

3. In the Payroll ID parameter field, enter 112.

4. In the Pay Advice Date field, enter 2003/08/29.

Running SQL Code in SQL Developer

1. Open the report in Reports Developer and double-click on the Data Model icon (not word).

2. Double-click the Q_1 box. The SQL code for the Q_1 query appears.

3. Copy all the SQL code and run it in SQL Developer.

4. For the p_payroll_id, enter 112

5. For the p_pay_advice_date, enter 29-AUG-2003

Reports Developer Quirks and Tips

Posted by on November 23, 2007 No comments

1. To change the orientation of a report, navigate to Layout Model > Main Section. Bring up Property Palette. Change the Report Width and Report Height under Character Mode e.g. 132 x 66.

2. To view the output as it would appear in Oracle Applications, navigate to Data Model > System Parameters > MODE. Set the Initial Value under Parameter to Character.

3. For Oracle reports that will be registered in Oracle Applications, set the format of the date parameter in the Oracle report to Input Mask:
RRRR/MM/DD HH24:MI:SS

4. Set the corresponding parameter’s format in the Oracle Application concurrent program definition to Date (FND_STANDARD_DATE).

5. When using date parameters in the Where clause of the query, use:
LAST_UPDATE_DATE >=  :P_START_DATE
Not TO_CHAR (:P_START_DATE) or TO_DATE(:P_START_DATE)

6. Colour each frame with a different background to easily identify and distinguish them.

7. To break the page on a frame, change the property ‘Maximum Records per Page’ from 0 to 1.

8. Ensure that the Token field for the Parameter for the Concurrent program matches the user parameters for the Oracle Report.

9. To reset page numbers on a group, insert a field in the layout. Set the source at Physical page number. In the properties for the field you will see field Page Numbering below format mask field. Here you can reset the page numbers according to the group for PO.

10. To get a summary field by group name, in the Data Model view, click on the Summary Column button and then click on the group to enter it in.

11. REP-1212: Object ‘Body’ is not fully enclosed by its enclosing object ”.
Set the Output Style in the Concurrent Program definition for the report to a wider style (either Landscape or Landwide).

Resolving REP-3000 Error

Posted by on July 19, 2007 No comments

Problem: When the Active Users (PDF) report is run, it completes in error with the following log file:

+---------------------------------------------------------------------------+
Application Object Library: Version : 11.5.0 - Development
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
FNDSCURSPDF module: Active Users (PDF)
+---------------------------------------------------------------------------+
Current system time is 17-JUL-2007 11:37:42
+---------------------------------------------------------------------------+
+-----------------------------
| Starting concurrent program execution...
+-----------------------------
APPLLCSP Environment Variable set to :
XML_REPORTS_XENVIRONMENT is :
/u2/oracle/vis2ora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb
XENVIRONMENT is set to:  /u2/oracle/vis2ora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb
Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.UTF8
'.,'
Spawned Process 27647
REP-3000: Internal error starting Oracle Toolkit.
REP-3000: Internal error starting Oracle Toolkit.
Report Builder: Release 6.0.8.24.0 - Production on Tue Jul 17 11:37:53 2007
(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Enter Username:
Program exited with status 1
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 3019814.
Review your concurrent request log and/or report output file for more detailed information.
+---------------------------------------------------------------------------+
Executing request completion options...
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 17-JUL-2007 11:38:06
+---------------------------------------------------------------------------+

Solution: Change the DISPLAY environment variable in Oracle Applications.

Log on to Oracle Applications Manager, and navigate to Site Map > AutoConfig.

There are two context files: one for the Database tier and the other for the Applications Tier. In the Applications tier row, click Edit Parameters. Click the Environments tab. Expand oa_environment:generic_service. Under X11 Display (s_display), change ‘hostname:0.0’ to ‘:0.0’. Save the changes. Then log out of Oracle Applications Manager.

The context file for the application tier must be regenerated. On the server, source the environment, stop the application tier services (adstpall.sh), run AutoConfig on the application tier (adconfig.sh), then restart the application tier services (adstrtal.sh).

Refer to:

Metalink Doc ID: Note: 181244.1

Subject:  Configuring VNC Or XVFB As The X Server For Applications 11i

Metalink Doc ID: Note: 200474.1

Subject:  Comprehensive REP-3000 Troubleshooting and Overview Guide

Running Oracle Report From Command Line

Posted by on June 5, 2007 No comments

Problem: How is an Oracle Report running from the command line?

Solution:

1. Source the appl_top environment file

2. ar60run report=/u0/thetaappl/fnd/11.5.0/reports/US/FNDSCARU.rdf batch=yes
destype=file desname=/u0/thetacomn/admin/out/theta_zetadb/otest_tcl_20070605a.out
desformat=/u0/thetaappl/fnd/11.5.0/reports/HPL printer=noprint pagesize=132x45