GNM_RCS_Software_Techniques_TR

• 1. .Introduction

     1. Purpose

This document describes how various software development techniques have been applied in the design of the RCS system.

• 1. Scope

This document covers the most significant topics that are not addressed anywhere else in this documentation set.

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

• 1. .O3 Team library code

All of the screens and reports in the application are dependent on a library of code provided by O3 Team.

This “library code”, incudes a library for both Forms and Reports along with the original template Form, reference Form and Object library Form.

Within the RCS environment:

• • the library has been renamed rcs_library.pll (for both Forms and Reports);

  • the reference form has been renamed rcs_reference.fmb

  • the object library has been renamed rcs_objects.olb

The use of this library code standardises techniques across the application, controls the look and feel of the system, and speeds up the development process.

The O3 supplied software has only been modified where GNM specific enhancements have been required.

• 1. .UI style

The user interface style is a hybrid of the following:

• • O3 Team’s in-house style;

  • Good practice interface design; and

  • GNM’s requests in relation to specific designs including very occasional input from ESD.

Every effort is made to keep the style of the system consistent throughout. However, from time to time new problems need to be solved, and new designs may evolve e.g. search palettes, floating windows, audit change highlighting etc.

The user interface does not attempt to replicate designs of other systems used in-house at GNM as these systems are developed with different underlying software often for different users.

Key elements of the UI style are:

• • Every window has a relevant title;

  • Every window has an Exit or Close button;

  • The font used is MS Sans Serif as this renders well on both Macs and PCs;

  • The primary background colour is r50g88b88 but r75g88b88 is used if differentiation is needed;

  • The primary font colour is black, on a white background;

  • Issues in the data are highlighting with a red font and warnings about the data are highlighted with a magenta font;

  • Required data items have a red prompt, and the item’s background is highlighted in red if the user tries to save the data without entering data in the field. Standard required field validation stops processing;

  • The backgrounds of preferred data items are highlighted in yellow if the user saves changes without entering data in these fields. A warning message is issued to encourage them to enter the data but they can continue and save the changes;

  • Disabled data may be greyed out;

  • Generally button that are not available are greyed out, or not visible;

  • All fields must be sized to appear fully on PC as well as Mac – Macs require more space;

  • Date and datetime have the format DD/MM/YYYY and DD/MM/YYYY HH24:MI:SS;

  • With few exceptions LOVs validate the fields they are attached to;

  • LOV buttons are not used: instead an ellipsis is visible within the relevant field(s);

  • All data entry screen items have a prompt, a hint and a tootlip (which is the same as the hint);

  • All screens share a standard menu;

  • The user cannot have more than 2 screens open at once;

  • Buttons commonly replace block scrollbars due to historic issues on older Macs;

  • Fields are left aligned and the tab order flows top to bottom, left to right;

  • Lists of people’s names are always presented in forename then surname order;

  • Drop down lists are used sparingly e.g. for title, gender, and currency, but for longer lists of data LOVs are preferred;

  • Created and modified audit data are shown in a standard format, similar to boiler plate text;

  • Change history audit always shows old data values on the left and new data values on the right with changes highlighted in red. If the new data is blank the field prompt is highlighted in red;

  • Clicking on a new tab in Tab canvas will always navigate the cursor to an item in that canvas.

• 1. .Caching data

     1. Client caching

        1. Avoiding round trips

Round trips over the network, from the java applet running in the browser to the webserver and database, are reduced where possible.

This is achieved by the following means:

• • POST-QUERY (and other) triggers may get multiple items of data in a single call rather than making multiple calls each getting a single item of data;

  • Database functions and procedure are called rather than executing SQL and DML in the Form. This means a series of cursor instructions (open, fetch(es), and close) can all happen on the database within a single call from the Oracle Form;

  • Various function are defined in the client libraries that also exist in database packages to avoid calls to the database; and

  • The synchronize built-in is used sparingly as it slows down screen responses.

• 1. Caching frequently used data

Data that is repeatedly used in the Oracle Forms may be cached in the client, as described below, and/or cached on the database as described in the next section:

• • Commonly used database data, such as details of the current user, as stored as package data in a Forms library; and

  • Configuration parameters i.e. values returned by the CPAR packaged functions (which query data from CONFIGURATION_PARAMETERS) are often stored as package data in a package F_FORM, so the values only need to be found once but used multiple times thereafter.

And in Oracle Reports:

• • Commonly the values returned by the CPAR packaged functions (which query data from CONFIGURATION_PARAMETERS) are requested once and stored in Report parameters for repeated use later.

• 1. Database caching

     1. Results cache

Oracle 11g database functions can be defined to results cache, whereby the result of ther function, once first determined, can be relied upon until the data in the underlying restricting table changes. This facility works across database sessions and is very effective and so the technique is being applied to suitable RCS database functions.

Unfortunately the PLSQL compiler in Oracle Forms 11g cannot parse calls to such functions and so a wrapper is needed on the database to route these calls through.

• 1. Caching frequently used data

In other areas of the system data is cached programmatically using record and table structures defined in packages, with the cache being initialised by code that runs on instantiation of the package. Elsewhere deterministic functions improve the performance of various functions.

• 1. .Function-based indexes

Function-based indexes are in widespread use in RCS to target data that needs to be queries. These indexes are mainly used to support queues of data in areas such as matching content, approving expenditure, and various other areas.

The queries that use these indexes must have the index name included in a hint, and must use an identical function in the where clause to the function in the index definition.

The benefits of function-based indexes in RCS are:

• • Index entries only exist if the function returns a value. This reduces database I/O where the function returns null during inserts and updates as an index entry is not written;

  • Function-based indexes occupy less space, due to the reduced number of entries; and

  • Function-based indexes can still be multi-column for more complex queries.

Other points to note with function based indexes are:

• • They are invalidated if based on a deterministic function defined on the database, if that function’s definition changes, so it is better to explicitly code the function in the index definition; and

  • Index columns cannot be compared to DATE datatypes, so it is better to convert dates in the index definition to character format YYYYMMDDHH24MISS. This has the additional benefit of ordering the data for range scans

• 1. .Batch generation of reports

Most reports on RCS are generated in batch. All of the letters that are issued by the database are generated in batch using Oracle event driven publishing techniques. Database jobs invoke the report server and run Oracle Reports in batch to generate PDF output.

In addition to this a lot of csv reports can be requested by users. Their requests are stored in the REPORT_REQUESTS table and then a database job processes the queue of pending requests and invokes the relevant software to generate the report output.

• 1. Report requests

Users have access to wide a variety of reports that are emailed to them in csv format. Technical specifications have been written which describe each of these reports, and so this information is not repeated here.

The overall process is as follows:

• • User choose the report they require from the menu system;

  • A parameter screen opens in which they enter the parameters for the report;

  • The request details are saved to the REPORT_REQUESTS table;

• 1. Report generation

A database job processes outstanding requests in order. For each report it invokes a separate packaged procedure to generate the output;

The packaged procedures that generate the output create a report file and an email file. (These procedures use Oracle’s standard UTL_FILE package to create, open, write to, and then close operating system files.) The report file is generated first, and provided that is successful an email file is then created which references the report file to be sent as an attachment; and finally

• 1. Emailing files

A fingerpost (fip) process polls the file system’s “Out” folders for email files to send and sends the emails. This process is described further in the section 7 Email production.

• 1. .Email production

RCS does not use Oracle’s UTL_SMTP email package (or UTL_MAIL) but instead relies upon software from Fingerpost to process file-based email data converting them into emails and sending those emails via GNM’s Notes servers.

In an ideal world this process would be replaced by UTL_SMTP with outbound emails accessible in Gmail as per all other GNM email, but whilst the current system is working it is unlikely to be changed.

• 1. Flat files and fip processing

Database software writes meta data to files for Fingerpost software to process.

• 1. Emails without attachments

The email meta data files are written to this “Out” directory by the database software:

nasinternal-dc1a\fip\spool\rcs\email\out\

The files contain simple text and are in the format shown below:

~

DA:<email to>

DI:<subject>

DJ:<from email>

DK:<reply to address>

QM:<date and time as DD/MM/YYYY HH24:MI:SS>

~

<body of email>

After a short delay, ensuring the file is complete and closed, the fip process reads this file and creates an outbound email using the data within. This email is sent via the Notes servers. Then the fip process moves the corresponding file to this “Done” directory

nasinternal-dc1a\fip\spool\rcs\email\done\

• 1. Emails with attachments

The email meta data files and attachments are written to these “Out” directories by the database software:

nasinternal-dc1a\fip\spool\rcs\reports\out\emails\ and

nasinternal-dc1a\fip\spool\rcs\reports\out\attachments\

The email meta data files contain simple text, as before, but additionally a reference to the attachment (excluding the path) as shown below:

~

DA:<email to>

DI:<subject>

DJ:<from email>

DK:<reply to address>

DL:<file attachment name>

QM:<date and time as DD/MM/YYYY HH24:MI:SS>

~

<body of email>

Again the fip process reads this file and creates an outbound email, with the attachment, that is sent via the Notes servers. Then the fip process moves the corresponding files to these “Done” directories:

nasinternal-dc1a\fip\spool\rcs\reports\done\emails\ and

nasinternal-dc1a\fip\spool\rcs\reports\done\attachments\

• 1. Database storage

Where the attachment is an Oracle Report output file generated as a PDF (usually a letter, contract, or term sheet) the report is saved to the database table called STORED_DOCUMENTS and linked to the relevant contributor, commission, contract, expense, customer, licence request or syndication contract record.

The software that manages the storing and extraction of documents from this table is contained within the SDOC package – files are loaded using the standard database procedure dbms_lob.loadfromfile

The path of the document to be loaded is defined within a database directory named ARRANGEMNT_DOCUMENTS which maps to /fip/spool/rcs/reports/out/attachments. This is the mapping from the database server Oracleserve1 to nasinternal-dc1a.

• 1. Archiving

Once email files have been moved to the “Out” directories they are periodically filed and zip archived using the following batch command file that is run in a command window on a PC:

nasinternal-dc1a\fip\spool\rcs\filing.cmd

This moves the files to the appropriate subfolders and then calls:

nasinternal-dc1a\fip\spool\rcs\zip.cmd

This archives files within those subfolders.

These command file needs to be updated from time to time to add new years, and removed old years, as file names often contain the year and this is used to file documents into year subfolders to make finding the documents easier.

• 1. .XML processing

RCS has XML processing requirements: parsing and processing XML it receives, and generating XML it publishes. In both cases Oracle standard functions are used so as to avoid reinventing the wheel.

Both areas of software development are significantly complex.

• 1. Parsing XML

RCS learns about content published on the website from a content feed using HTTP GET. This feed is in XML format. It is parsed using these standard Oracle packages: UTL_HTTP, DBMS_XMLPARSER and DBMS_XMLDOM and the relevant details are then loaded into the database.

• 1. Generating XML

RCS publishes an XML feed of data for the website. This XML includes rights tags relating to content the website has published.

The XML is published using mod_plsql as described in section 10 mod_plsql. The XML itself is generated using Oracle’s standard database functions: XMLagg, XMLElement, XMLAttribute and XMLForest.

• 1. .Document storage

     1. User initiated storage

Oracle’s Webutil extension software is implemented in RCS. Oracle supports this for PC clients, but it also seems to work well on Macs, however this is not guaranteed.

The Webutil software enables users to interact with the client operating system and open files stored there and save them to the database. This facility is mainly used within RCS to save key contractual documents to the database against the corresponding contract, contributor or customer.

• 1. Batch storage of files

RCS generates many contractual documents every day, including commission letters, editorial contracts, syndication licences (and requests for information, quotes, prior approval requests, royalty advice etc.) and syndication contracts. These documents are generated in batch using Oracle’s event driven publishing model.

The documents are produced as PDF files and are automatically loaded into the database. This happens as soon as a file is created but prior to them being sent to the contributor or customer. Details of this process are found in section 7.2 Database storage.

• 1. .mod_plsql

mod_plsql supports deployment of PL/SQL database features to a browser. It is part of Oracle HTTP Server, which ships with Oracle Application Server and Oracle Database. mod_plsql accepts URLs, interprets any input parameters, and executes a database procedure that generates output to return to the calling process or browser.

• 1. Configuration

Mod_plsql needs to be configured on the application server - WebLogic. This involves defining a Database Access Descriptor (DAD) which identifies the host database alias and the connect string.

• 1. Main uses

Within RCS the following are the main uses of mod_plsql:

• 1. Failed logon error page

If the user enters the wrong username or password a web page is displayed telling them they got it wrong, and how many more chances they will get before their account is locked.

• 1. WorldPay integration

Pages are generated using mod_plsql and displayed in the browser to support WorldPay credit card payment integration within SLM.

• 1. Rights XML feed to the website

The XML feed to the website is initiated by an HTTP GET using a URL that invokes mod_plsql and returns XML to the calling process.

• 1. .Database table triggers and Oracle Forms

Care must be taken with database table triggers changing data in an Oracle Forms environment. If an Oracle Form sends a DML statement to the database and a trigger modifies the data in any way then the Form will believe another user has modified the data on screen, and the user will be forced to re-query it before any further updates can be made. To avoid this triggers should not change data unless absolutely necessary –if they do need to change data perhaps the Oracle Form should be modified to set the data correctly in the first place.

• 1. Default values

Triggers are used to ensure certain columns have a value on insert, such as ID columns and created by and datetime fields.

As described in above this should be avoided if Oracle Forms (only) are sending the DML to the database, but this is not a problem for other APIs, or complex processing.

• 1. Audit

Triggers are used to record audit data on 3 levels:

• • Created by and created datetime;

  • Modified by and modified datetime; and

  • Change history where old and new data values are recorded.

• 1. Mutating table / constraining table issues

Triggers are also used to maintain data across foreign keys in such a ways as to avoid mutating and constraining table issues. This is described elsewhere in this documentation set.

• 1. .Levels of help

     1. User help

Help is available at the following levels within the application:

• • Field prompts indicate what a field is for;

  • Hints and Tooltips give a little more information;

  • The available keys at any given time can be listed from menu option Help → Keys; and

  • A help system is accessible from the menu option Help → Help text. This help system is maintained in house by GNM.

• 1. Technical help

For technical help the following sources are available:

• • This set of 80+ documents describing the system software;

  • IT department documentation describing the system infrastructure;

  • eDBA (or current 3rd party supplier) documents relating to the database and application server tiers; and

  • Oracle documentation for the Oracle products that are in use at GNM, Oracle’s certification matrices, Oracle’s bug databases and technical notes, and any other security notices and advisories issued by Oracle.

End of Document