Reports Development in 11i

Before You Begin
1. Have a good understanding of how your report is to look and function.
2. Obtain a copy of both the Oracle Applications Developer’s Guide and Oracle Applications User Interface Standards. You will need both of these manuals for reference while creating your report.
3. Determine the Tables and Views to be used for your queries. If the tables being created are for the sole purpose of report generation, the standard row who columns are not required.
4. Naming Standards : All file names must be no greater than 8 chars in length plus a three character extension (FILENAME.ext). However, global localizations (country specific) must have the 2 character country code prefix and be no greater than 11 chars in length. For example, a purchasing report for UK should be UK_POCALERT.rdf
For MRC and EFC reports, first 3 character prefix should be either MRC or EFC and be no greater than 12 characters length.
The files names must be all caps except for the extension (FILENAME.ext).. This will provide a consistent naming scheme across all platforms.

Global Report Source File Names
XXXDDDDD.rdf

XXX = custom application shortname
DDDDD = Description

Localized Report Source File Names
CC_XXXDDDDD.rdf

· CC = 2 character country code

MRC or EFC Report Source File Names
MRC_XXXDDDDD.rdf or
EFC_XXXDDDDD.rdf.

Data Model

User Parameters – Report parameters must be identified by the following:
P_parameter
P_ = signifies parameter
parameter = the descriptive name you would give to the parameter

Queries – Queries must be identified by the following:
Q_query_name
Q_ = signifies query
query_name = the descriptive name you would give to the query
The main query should be noted as such with a _main

Formula Columns -
CF_formula
CF_ = signifies formula column
formula = the descriptive name you would give to the formula column

Summary Columns -
CS_summary
CS_ = signifies summary column
summary = the descriptive name you would give to the summary column

Placeholder Columns -
CP_placeholder
CP_ = signifies placeholder column
placeholder = the descriptive name you would give to the summary column


There is another way to show the name of the report in the report header. By using this method, you don’t have to worry about modifying report name in the header section. In order to see the actual report name comes out, the report has to be registered in the application.

* Create a place holder named, ‘RP_REPORT_NAME’, and a formula column named ‘Report Name’ In the formula column, use the following script:

DECLARE
l_report_name CHAR(80);
BEGIN
:RP_Company_Name := :Company_Name;
SELECT cp.user_concurrent_program_name
INTO l_report_name
FROM FND_CONCURRENT_PROGRAMS_TL cp,
FND_CONCURRENT_REQUESTS cr
WHERE cr.request_id = :P_CONC_REQUEST_ID
AND cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id;

:RP_Report_Name := l_report_name ;

RETURN(l_report_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN :RP_REPORT_NAME := ‘’;
RETURN(‘’);
END;

Layout Editor

· Header Section: The report header section should consist of the following lines


ASC - CONFIDENTIAL AND PROPRIETARY
Use pursuant to Company instructions

The report header section will contain the parameters issued by the user to run the report, this information is printed once for the report run. The parameters block should:

a. Have the parameter names printed with capitalized initial letters;
b. Have the parameter name and the user entered value separated by a colon (:);
c. Be centered within the report and left justified within the parameters block.
d. Have the separating colons line up;
e. Have characters left-justified and numeric/monetary values right-justified.

Main Section:

· Page Headers : The page header consists of two lines and will display the following information:

a. Company Name: it is anticipated that this will be country dependent;
b. Report Name: full name in block capitals; (Program short name can be included if it is necessary)
c. Date and time of report run
d. Page number.

· Column Headings: Column headings should:

a. Have their initial letters capitalized;
b. Have the same justification as the data they display;
c. Not be significantly longer than the data they contain;
d. Be underlined;
e. Be easily recognizable as the heading of the column they refer to - not always easy to do, especially with multi-column headings.

· Column Justification : Columns should:

a. Be right-justified for numeric/monetary values;
b. Be left-justified and be able to column wrap, where necessary, for character values;
c. Be in standard Oracle 9 character date report date (DD-MON-RR) for dates.

· Page Dimensions : The dimensions of pages should be:

a. Portrait Reports - 80 columns by 66 lines;
b. Landscape Reports - 132 columns by 55 lines.
c. Click on “Edit, Select All” and change the font to Courier 8

There should be a 2 line top margin and a 2-line bottom margin.

· Page Footers : If there is no data for the report, print two blank lines under the column headings, then the following line
(centered horizontally):
***No Data Found***


· Trailer Section : The report footer should consist of the following line (centered horizontally):
***End of Report***

Maintaining Copyright and Version Control :

· Add the ASC Standard report documentation block to the report level Comments Property. Here is an example:

$Header:ARGXAGR.rdf FMS 8.0.0.0.0 01-JUL-00 12:00:00

Name : ARGXAGR.rdf
Description : Aged Trial Balance - ASC - based on ARXAGR 7.0.11
Version : 8.0.0.0
Type : Reports 2.5
Application : Oracle Receivables - GADFMS
Called by : Standard Report Submission
Calls to :

History :

Date Ver Author Reason
----------- --- ------------------- -----------------------------
11-MAY-1998 1.0 Ajeet Singh Converted from Reports 2.0
10-DEC-1999 1.1 Ajeet Singh Added Master Customer
Name and Master Customer
Number. Made global for
Release 11.

· The last (5th) digit of the version number must be changed each time you check the report out of the repository. This digit can then be used to verify that the correct version of the report has been packaged and/or installed on a particular instance. When customizing a standard Oracle report, the Oracle version should be referenced in the description.

· Register the report as a new custom report. Please refer to the R11i CONC REQUESTS GUIDE for instructions on registering a report.
Customizing an Oracle Standard Report

· Make a backup copy of the Oracle report. Save a copy the original standard oracle report in the repository, named as reportname>.orig.rdf.

· Register the report as a new custom report.



Reports Documentation
· Open report
· Save the report in the database.
· Then open the report designer. In the object navigator go to tool and choose tools option.
· In the tools option dialogue, choose runtime parameter Tab.
· In the destination, set Type as “file”, name as your directory where you need to save the file and select Mode as “Character”.
· Then close this tools option dialogue box.
· Now in the object navigator go to file, administration, report doc, then choose portrait or landscape. This will open the runtime parameter form of Oracle Reports documentation Report.
· Here Name as “your report”(which are the one you need to do the documentation).
· If the destination as Screen, then change this to file.
· Change the layout information “Y” to “N”
· Parameter information “Y” to “N.
· Run the report. Now the report is saved as a file.
· Open these files in the word pad, and at the end of this document attach the output of the report, which we run through the application.
Hints and Tips

· When transferring files from the remote unix machine to the desktop or vice versa, use FTP. Transfer the file in BINARY reportat. Select the “Default” menu option and click on “Binary”. FTP will rename the file’s extension to uppercase “.RDF”. If you double-click the file, a popup screen will allow you to rename the file’s extension to lowercase “.rdf”
· When opening the Layout Model., the layout may appear with strange fonts, etc. Click on “Edit, Select All” and change the font to Courier 8. Be careful when adjusting frame sizes and page sizes. This is tedious and takes a lot of practice.
· Object and frame borders in the Layout Model can often times be hard to see. Since the location of the objects in the Layout Model is so critical it helps to use the coloring/patterning functions in Oracle Reports to distinguish one object or frame from another. To do this, choose the object by clicking on it once, then click the ‘Fill Color’ button on the toolbar and choose a color.
· Editing the text that displays on a label on the Layout Model can sometimes be confusing. To do it, choose the ‘Text’ button on the toolbar then click the object you want to edit.
· To add a user parameter to a report navigate to the ‘Object Navigator’ window, expand the ‘Data Model’, expand the ‘User Parameters’, then click the ‘Create’ button on the toolbar.