GNM_RCS_Oracle_Reports_Standards_TR

• 1. .Introduction

     1. Purpose

This document outlines the standards for all Oracle Reports development for the Rights & Contributor System (RCS).

These standards are to be followed in development wherever possible, unless there is a pressing need to deviate from them.

The standards are based on O3 Team Limited’s own in-house standards.

• 1. Scope

This document covers Oracle Reports standards, but not the PL/SQL written within the report triggers and program units: a separate set of PL/SQL standards deals with PL/SQL objects stored on the database (triggers, packages, procedures & functions), and PL/SQL written into in Oracle Reports and Oracle Forms.

• 1. .Overall style

This section provides guidelines relating to the overall design of Oracle Reports.

• 1. Code placement

Where possible (and practical) code that is common to two or more parts of the system is only written once, but called wherever needed.

Code cannot be referenced between Oracle Reports in the same way it can in Oracle Forms, and there is no concept of object libraries as found in Oracle Forms.

However there are several ways to achieve code reuse:

• 1. Libraries

Code that is called by most or all reports should be defined in a library, and then the library attached to all the reports. Library code tends to follow these rules:

• 1. The code is commonly reusable;

  2. The code does not include SQL (as this code would be on the database); and

  3. The code does not reference report columns, as the library would not compile.

  4. NB This restriction can be overcome by using input parameters in the PL/SQL program units.

Typical uses of library code include code that calls database packages to get information relating to the current session, code relating to layout style, wrappers around standard Oracle functions, and any other universal system features that are needed.

• 1. Generic reports

If two or more reports can use the same underlying query, and share the same or similar layouts, then it is often better to write one Oracle Report, and to make it behave differently depending on how it is called.

This can be achieved with a report parameter, for example MODE, which is used to determine how to modify the report query and/or layout. For example in a tabular report, if the value of parameter MODE = “DETAIL” then a format trigger on a Group Frame could ensure all layout objects are displayed. But if MODE = “SUMMARY”, perhaps only the report totals would be shown, with the detailed data suppressed by a trigger. In this example the format trigger’s function might be as simple as

begin

return :parameter.mode =’DETAIL’;

end;

A similar technique can also be used to vary the columns shown in the report output. This is achieved by using format triggers on the relevant layout objects – if a format trigger’s function returns FALSE it suppresses the object.

The same technique can also be used to add predicates to the underlying report query. This is achieved by programmatically defining query predicates in report parameters using the After Parameter Form Trigger i.e. writing to the parameter after the report is open. A lexical reference is then made to these parameters in the query itself.

• 1. Database packages

As a general rule PL/SQL program logic that involves SQL queries should be written into a database package, which is then called from the reports.

• 1. Database views

Where there is considerable complexity in a query, and that query is used by two or more reports, or a combination of reports and Forms, a database view should be considered to hide that complexity from each usage.

• 1. .Report

There is one report library called rcs_rep_style.pll

Besides that library reports should follow this naming convention:

rcs_<alias>_999.rdf

where <alias> is the 4-letter alias of the main table being queried.

• 1. Report properties

There a many report-level properties that can be set, but the following are the most important to pay attention to:

• 1. .Data model

     1. System parameters

The system parameters’ default values must be set to values consistent with the way the report will be run. If, for example, ORIENTATION has a default value of Portrait and this is appropriate, then the calling program will not need to supply a value for this parameter.

• 1. User parameters

User parameters are used for 3 purposes:

• 1. To receive data values from the calling module;

  2. As report-level variables that can be set once and utilised elsewhere. NB public variable declarations in package specifications achieve a similar purpose, but packaged variables cannot then be used as the data source for a layout field, whereas parameters can; and

  3. As a means of altering the report query (or queries) by making lexical references to these parameters in the query.

In the first two examples above the parameters are named <DataName> e.g. CPRO_ID

In the third example the parameters are named LEXICAL_<Purpose> e.g. LEXICAL_WHERE_JOIN

• 1. Queries

Queries are named Q_<DataDescription> e.g. Q_letters

• 1. Groups

Groups are named G_<DataDescription>. E.g. G_Letters

Groups should not have group filters unless absolutely required by the report e.g. if you have to Report of the top 10…

• 1. Database columns

Database columns will take the name given to them in the select-list of the query.

Care should be taken to set the Break order property of these columns. The default is Ascending, but this should be reset to None in most cases to make it more intuitive.

• 1. Formula columns

Formula columns are named CF_<DataName>.

• 1. Summary columns

Summary columns are named CS_<DataName>.

• 1. Placeholder columns

Placeholder columns are named CP_<DataName>.

• 1. Data links

Data links are used to define parent-child links between different Queries (or Groups) in the Data Model. Where possible these links should be defined to reflect foreign key to primary key relationships in the underlying database tables.

When defining multiple linked queries attention consider whether the report would be more efficient with a single query rather than multiple queries. As a rule of thumb fewer queries tend to be better than more.

However if master data needs to be reported even when there is no detail data then multiple queries may be better, or outer joins could be used in a single query.

• 1. .Layout model

All layout objects are given meaningful names to make maintenance of the software much easier.

• 1. Header & Trailer Sections

To save unnecessary printing it is better not to have a these sections.

• 1. Main section

     1. Body

        1. Group Frame

Group frames are named M_<DataDescription>_GRPFM e.g. M_COMMISSIONS_GRPFM

NB The M prefix and GRPFM suffix are not intuitive, but are consistent with Oracle Reports default naming convention. It is easier to keep to this convention than to change it.

• 1. Repeating Frame

Repeating frames are named R_<DataDescription> e.g. R_COMMISSIONS

The <DataDescription> for a Repeating Frame should match that of the Source Group. For example Repeating Frame R_COMMISSIONS would have the Group G_ COMMISSIONS as its Source.

• 1. Fields

Fields are named F_<DataDescription> e.g. F_CONTRIBUTOR_NAME

Where possible the field name should match the Source data, with the F_ prefix.

• 1. Text Boilerplate

Text boilerplate is named B_<ObjectDescription> e.g. B_CONTRIBUTOR_NAME

• 1. Graphical Boilerplate

Graphical boilerplate is named B_<ObjectDescription> e.g. B_RECTANGLE

For graphical boilerplate (e.g. column headings in tabular reports) that is used to underline text boilerplate, the name should be the name of the text boilerplate with _UNDERLINE appended.

NB The object navigator shows all items (at the same level) in alphabetical order, so this naming convention helps keep related items together.

• 1. Image Boilerplate

Image boilerplate is named B_<ObjectDescription> e.g. B_COMPANY_LOGO

• 1. Margin

All reports in an application should have standardised margin layouts.

For letters that are issued by the system the first page margin should show the standard company logo, and all subsequent pages a smaller version of the logo.

For other reports the top margin always shows Organisation, System name and Report code on the left, Date and time, Run by and Page on the right, with the report title is centred; the bottom margin shows the parameter values.

• 1. .Parameter forms

Parameter forms should not be used. More user-friendly user interfaces can be provided in the calling application (Oracle Forms).

• 1. .Triggers

There are five pre-defined report triggers, each of which is described in this section.

It is also possible to define format triggers on layout objects as discussed in the relevant section of this document (8 Program units).

• 1. Before Parameter Form

This trigger fires before the parameter form is displayed. Use it to manipulate parameter values, PL/SQL package data and report-level columns.

This trigger is also used to set the schema of the current user session to RCS, thereby making the schema objects accessible without the need for synonyms or the coding of the schema name as a prefix to each object.

This trigger fires even if the parameter form is suppressed.

• 1. After Parameter Form

This trigger fires after the parameter form has been closed, if there is one.

Use it for the same purposes as the Before Parameter Form, but noting that it fires after the user has processed the parameter form.

In applications where the parameter form is never shown, the convention is to use the After Parameter Form trigger for processing parameter data rather than the Before Parameter Form trigger.

• 1. Before Report

This trigger fires before a report is executed, but after the query has been parsed and the data fetched.

This trigger is not generally used.

• 1. Between The Pages

This trigger fires before a page is formatted, but does not fire for the first page.

Use this trigger to change the layout of pages, if necessary.

This trigger is not generally used.

• 1. After Report

This trigger fires after the Runtime Previewer is closed, or after the output has been sent to its destination. It fires irrespective of the success or failure of the report. It can be used to tidy up temporary data structures that may have been set up in the Before Report trigger.

[Temporary data structures should be used sparingly, as they can lead to unexpected failures.]

• 1. .Program units

     1. Functions that support Format triggers

Oracle Reports allows the developer to write PL/SQL against various report objects, such as format triggers for layout objects, and group filters for data model Groups. These are always PL/SQL functions that return a Boolean value.

These functions are automatically named as soon as the format trigger is opened. This name can be changed but is usually suitable and can be left alone. However it is often useful and helpful to change the name of this function if the name of the related report object is changed. Fields and their format triggers should also be renamed meaningfully if they have been copied and pasted.

• 1. User defined packages

User defined packages, procedures and functions can be defined within the Program Units node. Packages should be defined instead of standalone procedures and functions. However, in unusual circumstances a stand-alone procedure or function may be defined if it significantly improves the readability of the code that calls it.

Packaged code should be defined where the code is either complex, lengthy, or called multiple times within the report e.g. the same code called on multiple layout objects.

Where the same code is used in many reports, it should be defined in a library. This code cannot reference queried data values, and tends to only be useful for applying format masks to fields, or for calls to the database.

• 1. Names

Program units in library modules should be prefixed L_, and those in Reports R_. This makes it easier for programmers to see quickly where code called from a trigger is actually written.

Package names should follow the conventions R_<TableAlias> or R_<Function>, for code that relates mainly to a particular table, and code that performs a specific function, respectively.

It is useful to have a package R_REPORT in which report-level constants and processing logic may be defined. For example any session variables, or system configuration parameters held on the database can be gathered once in an After Parameter Form trigger and stored locally in this package.

• 1. Structure and layout

All package bodies should define private procedures and functions before the public procedures and functions, and clearly mark the point where the public declarations start.

The PL/SQL Standards must be adhered to. All code must be in lower case, including any references to report columns.

• 1. Declarations

Use %type in variable declarations whenever possible.

Use %rowtype in record declarations whenever possible.

Always use varchar2 instead of varchar or char.

Schema names and database link names must NOT be coded into Forms code (or any code for that matter).

• 1. SQL and DML

All SQL and DML statements should be moved from the Form to an appropriate package on the database. This is done to:

• • Reduce network traffic;

  • Improve usage of shared SQL in the System Global Area on the database; and

  • Improve modularity of the code.

Generally DML should be avoided in Oracle Reports.

• 1. .Attached libraries

When attaching libraries the path of the library file should be discarded.

End of Document