GNM_RCS_Design_Approach_TR

.

• 1. .Introduction

     1. Purpose

This document describes how the design of the database schema and the structures within Oracle Forms and Oracle Reports all conform to a set of common principles.

It also describes how these various components are integrated.

• 1. Scope

This document deals with general principles of the overall design philosophy, rather than specifics of individual Forms and Reports.

It also highlights some of the common complex design issues and how they are resolved.

Separate (high-level) Functional Specifications and Technical Specifications provide more detail on the business functions supported by RCS.

• 1. .Overview

A good practice approach is taken to the on-going development of RCS to ensure it remains fit for purpose, and well maintained.

• 1. One place on time

Generally code should only exist in one place, callable from elsewhere. This is achieved by making good use of Forms and Reports libraries, Forms object libraries, Forms object groups, a reference form, localised inheritance, and use of database packages.

• 1. Performance

Performance of the system is important during development, and is monitored on an on-going basis:

• • SQL is tuned for optimum performance;

  • PLSQL is defined to be performant;

  • SQL is defined on the database not on the client (except block base queries and record groups);

  • Query reuse is maximised;

  • Client round trips are minimized;

  • Programme modularity is maintained, with due consideration for cohesion and coupling;

  • Caching techniques are used where applicable.

• 1. Multi-user considerations

Locking – the maxim is to lock as little as possible, as late as possible, and release locks as soon as possible.

• 1. Inter-system considerations

Fully agreed commit strategies ensure data is saved or rolled back on both databases consistently. Any logging of failures is done with autonomous transactions (that have their own commit).

• 1. Maintainability

Coding standards are adhered to for consistency across the system and ease of maintenance.

Code is commented where unusually complicated.

Code is regularly refactored when upgrades are applied to ensure the latest features from Oracle are embraced.

The system is further documented in this suite of documents.

• 1. .Database design

The design of the database schema is at the heart of the overall application architecture.

• 1. Best practice design

The design of the database schema is separately described in the Schema Design document.

The database software is designed to be modular, with high cohesion and low coupling.

Needless to say the system has to be designed for integrity, security, robustness, performance, and scalability.

In the context of the overall design, an important underlying principle of the database design is that each table is notionally allocated a 4-letter alias, which is then used in foreign key column names, constraint names, sequence names, trigger names and package names.

This 4-letter alias is also very important in the Oracle Forms and Oracle Reports, both in the file names and in the structures defined within these files such as Blocks, Record Groups, LOVs, and Forms PLSQL packages.

• 1. Using Oracle where possible

Whenever possible features of the Oracle database are used instead of writing bespoke code. Also where Oracle introduces new features that can replace existing software then the application is refactored to use the Oracle software. Examples of this include:

• • Generating XML from SQL using XMLagg, XMLElement, XMLAttribute and XML Forest functions;

  • Receiving and parsing XML using the UTL_HTTP, DBMS_XMLPARSER and DBMS_XMLDOM database packages;

  • Generating HTML using the HTP package;

  • Obfuscating / encrypting data using the DBMS_OBFUSCTAION_TOOLKIT package;

  • Oracle’s Event Driven Publishing to create batch PDF reports using the UTL_HTTP and SRW packages;

  • Batch processes scheduled with the DBMS_JOB package, although DBMS_SCHEDULER could be used instead. (There is little benefit in making this change although it may be enforced if DBMS_JOB is ever made obsolete.)

And although, not strictly a database feature, using Oracle’s Webutil Forms add-on for client file system access

The most obvious omission from this list is that emails are not generated using UTL_SMTP. This is for historic reasons whereby emails have to be sent via the Notes servers, and this is managed by Fingerpost’s fip software.

• 1. .Oracle Forms

Web-enabled Oracle Forms implement the RCS user interface.

• 1. Object libraries

rcs_objects.olb is the object library that contains groups of objects (defined within object groups) that can be inherited into each Oracle Form. These object groups include:

• • ALERTS – various one, two and three button alerts for information, warnings and errors;

  • CALENDAR – an object group containing all the components needed for a popup calendar;

  • PROPERTY_CLASSES – the main property classes pre-defined for the system;

  • VISUAL_ATTRIBUTES – the look and feel for forms UI objects;

  • WINDOWS – standard windows;

  • CANVASES – standard canvases for the standard WINDOWS;

  • POPUP_MENUS – menus available from a right click;

  • TRIGGERS_FORM – standard form-level triggers.

As a general rule all of the above object groups are referenced into each form. Some may be excluded from some Forms however, for example a Form with no date fields does not need the CALENDAR object group.

webutil.olb is the object library that Oracle ships to support the interaction of Oracle Forms with the (Windows) operating system for opening and closing files.

The WEBUTIL object group is referenced into those Forms that can open operating system files.

• 1. Libraries

rcs_library.pll is a library containing O3 Team’s “Library Materials” which are designed to aid faster application development, and also a limited amount of bespoke library code written for GNM and the RCS application in particular.

This library is attached to all the user interface Forms.

All the software in this package is contained within packages prefixed L_, for example L_ALERT, L_CALENDAR, L_USER, and L_WEB.

webutil.pll is the library that Oracle ships to support interaction of Oracle Forms with the (Windows) operating system for opening and closing files.

This library is attached to every screen that requires access to files on the operating system, which is mainly those where documents can be stored in support of contracts or relationships with contributors and customers.

• 1. Reference Form

rcs_reference.fmb is a form that contains the definition of various objects that may or may not be contained within the RCS Object library.

These objects can then be referenced by other Forms from this Form.

• 1. Menus

rcs_menu_nologin.mmb

This menu is used during the login process, and does not make any reference to database roles as the user is not yet logged in.

rcs_menu.mmb

This menu appears above all screens once the user has logged in.

One or other menu is attached to every user interface Oracle Form.

• 1. User Interface Forms

These forms are the ones that the users see and use and are the most populous type of Form.

These forms are named as follows:

rcs_<Alias>_999_pc.fmb

The alias is also used for block names, and in some cases for Record Groups and LOVs.

For example, a block that queries CUSTOMERS data is called CUST; a record group that queries CUSTOMERS may be called CUST and the corresponding LOV could be called CUST as well; and for each block there is a Forms package of code that relates to it called F_<Alias> i.e. F_CUST.

NB the package cannot be called CUST as that would prevent the Form from seeing the database package CUST.

• 1. Moving objects to the object library

Where objects are common to many Forms they should be moved to a group in RCS_OBJECTS e.g. a new property class or visual attribute that gets defined. This ensures changes to that object only need to be made in one place.

• 1. Referencing objects

Where objects are used in a small group of Forms they should be referenced from one Form to the next. Typically the source object is in the lower numbered Form e.g. defined in RCS_AGMT_010_PC and referenced into rcs_agmt_020_pc, rcs_agmt_030_pc, rcs_agmt_040_pc, and rcs_agmt_050_pc.

• 1. Moving code to the library

Where PLSQL is common to many Forms it should be moved to a package in RCS_LIBRARY. NB The library packages are prefixed L_ rather than F_ just to make it easy to see where the code being referred to resides.

• 1. Referencing code

Where code is used in a small group of Forms it should be referenced from one Form to the next, again from the lower numbered Form to the higher.

• 1. Maintaining the inheritance hierarchy

If any property of an object is overwritten then that property will no longer be maintained when further changes are made to the corresponding property in the source object.

NB It is critical that search and replace not be used in Oracle Forms Builder as this has the effect of overriding the PLSQL property of all Forms triggers and packages, and thereby removing them from the inheritance hierarchy.

• 1. Complexities with Block order

When Oracle Forms executes a commit data is written to the database in the order the blocks appear in Oracle Forms Builder, not the order they appear on screen. Within a block deletes are processed first, then the inserts and updates in the order they appear in the block.

The order in which blocks are processed is crucial in a number of areas of the application – especially the rights model. The data for the rights profile of each commission and contract is generated by database triggers; the following tables are populated ARRANGEMENT_FORMATS, ARRANGEMENT_RIGHTS and ARRANGEMENT_RIGHTS_DETAIL. Thereafter this data can only be updated by the user, not deleted or inserted. The corresponding Forms blocks, AFMT, ARGT and ARDE, are presented to the user as Master/Detail but in the Builder they are in reverse order so that during a commit ARDE is processed first, then ARGT, and then AFMT. This ensures that when saving the master record the new values of the detail records can be examined by database query, as they are used to set a flag on the master record.

Where these unusual block orders exist they should be left as they are.

• 1. Complexities with Property Classes

Property classes are a useful way to apply a common set of properties to an object, and to be able to maintain those property values in one place. For example the property class TEXT is used to define the height, font, prompt, colour, render, and bevel properties etc. of a text item.

It is also possible to attach a trigger to a property class. Whenever that property class is attached to an item, block or form then the trigger will be inherited by that item as well. In RCS the DATE property class defines the attributes for a date field, including the above properties as well as a format mask and width. The property class also has triggers, such as a WHEN-MOUSE-DOUBLE-CLICK which invokes the CALENDAR.

• 1. Calling Forms

Historically it was found that continually calling one Form from another was causing memory problems on the Macs. As a result a limit of how many forms can be nested has been implemented in the application software. Currently only 3 Forms can be open at once.

• 1. Welcome screen

All users, once logged in, are presented with a Welcome screen. This is the screen from which all other screens are launched.

For most users this screen shows a number of counts that relate to queues they process. A button next to each count, when pressed, navigates the user to the relevant queue e.g. Approve commissions. Changes to the queue count and the queue itself must be synchronised.

• 1. .Oracle Reports

Oracle Reports are used for traditional tabular reports, and well as for the production of letters, contracts and licences.

• 1. Library code

Common code, for use in all reports, is held in the rcs_rep_style.pll reports library. This library is attached to all reports.

• 1. Reports code

The reports themselves are named rcs_<Alias>_nnn.rdf

• 1. Tabular reports

Only a few of these reports exist now, as other reporting techniques have also been implemented.

The Reports are built in pretty standard master detail format, in accordance with the standards. Highlighting of data – for example foreign currencies – is controlled by calls to common library procedure and functions.

• 1. Mail merge letters and contracts

RCS (including SLM) issues many letters and contracts, including:

• • Commission letters;

  • Contributor contracts of various types;

  • Book extract contracts;

  • Syndication licences;

  • Syndication letters of various types to help process requests for licences;

  • Syndication contracts.

These letters and contracts are all produced by reports that generate paragraphs and contract clauses merging in data recorded in the database.

In the case of the contracts, the list of clauses is defined by the appropriate system user, and these definitions are stored in tables in the RCS schema. This gives the user some flexibility to change, add, or remove clauses without the need to request software changes.

The clauses are defined by the user, and can include tags that identify data that needs to be merged from the database. The report software looks for these tags and then substitutes the relevant data. For example, if a clause was defined as “The total fee is <Fee>.” Then the report would identify the <Fee> tag and substitute in the total of the FEE column in the COMMISSION_PAYMENTS table for the current commission, and suffix the currency code. In this example the output (or sentence in the letter) could be “The total fee is 123.45 GBP.” Some of the substitutions are far more complicated, but the basic principle is the same.

• 1. Business Objects reports

Business Objects reports are used to satisfy specific on-going reporting requirements and to satisfy ad hoc report requests. The Business Objects software is outside the scope of this document and is managed by the RCS Administrator.

• 1. RCS CSV reports

A number of reports that are emailed to users as unformatted csv files which they open in Excel. In general each report has its own submission screen, available from the main menu, although sometimes a submission screen offers a choice of reports.

Requests for these csv reports are stored in the REPORT_REQUESTS table. These requests are processed asynchronously by a database job. The database job executes code appropriate to the report that was requested, generating a text file containing the requested data. A separate text file is also created which is the meta data required for the covering email. The two files are converted into an email with attachment by Fingerpost fip software.

• 1. Generic reporting model

In the case of Spend Analysis reporting a single submission screen allows the user to define the criteria for their report. This is a more generic reporting solution.

The user can also store their parameter selection for later use by giving the parameter set a name.

These requests are processed in the same way as the CSV files, via the REPORT_REQUESTS table.

Spend Analysis reports are generated from data held in the FINANCIAL_ANALYSIS table, which in turn is populated by a database job once every night. Consequently these reports are only accurate up to the previous day’s data.

End of Document