Dr. Paul Dorsey

Dulcian, Inc.

A few years ago, when I saw my first beta copy of Forms 4.5, I almost immediately recognized the need for template development. Since that time, my thinking about templating has evolved from using a base template and modifying it for different types of forms to building small objects such as multi-select LOVs for use in application development, and finally to application-specific objects. Over time, the objects I have developed have gotten larger and larger. At the same time, my company has been involved in a number of projects, several of which had common elements. The elements that kept re-appearing were the following:

• Comments

• History

• Security

• Demographics

• Workflow

Each time I used one of these structures, I found myself reusing old data models, code and, to some extent, forms and reports. I was doing the same type of repetitive work that prompted me to build a simple template in the first place.

The question then arose: Could I take these structures and build them in such a way that they could be bolted into a new application with a minimum of effort? Almost immediately, I found that by extracting these modules and storing them together, a great deal of code reuse was possible and the time required to reuse these structures was very small.

I wanted to take this process one step farther and genericize these large complex objects in the same way that I had genericized smaller objects such as the multi-select LOV. I am coining the word COB (Complex OBject) to refer to these objects. I wanted to be able to bring any one of the modules mentioned above into a new system and to production with 5-10 minutes of effort. Surprisingly, it turned out to be much easier to do this than originally envisioned. This paper outlines the steps used to create a COB.

Complex objects (COBS) are not difficult to build once you have completed the first one. However, to build them, you need to understand and be able to use DBMS SQL.

These structures can be quite complex. For example, the Workflow complex object we constructed not only stores workflow acts such as approval/denial, but also stores rules about who performs such actions. It requires 10-15 tables and three multi-tab Forms modules to support it. COBS can also be very simple. For example, a Comments COB involves little more than a single table.

The remainder of this paper will explain how to build the Comments COB. Once you see how one of these objects is built, you should be able to take the idea and build more structures on your own.

Before trying to build a COB, you should build the structure as a non-generic object. For example, in this exercise, you should first build the Comments structure in the traditional way and then modify it to be a COB. If you have done a lot of template building in the past, you will probably feel comfortable going directly to the COB environment.

The basic table structure of the simple Comment COB is the comment table and a reference table that stores information about the objects that can be commented on. If we only want to allow commenting by people with a specific role, we need a few more tables. We will discuss the structure of each of the tables in turn.

We want to store the tables that will allow commenting. We need to do this so we can write database triggers to enforce the necessary pseudo referential integrity. To do this we build a reference table and store the commentable table names in it.

The reference table (COB_REF) is used for all COBS. For this simple structure, we need only one column to store the table name. However, we want to anticipate using more than one COB so we will add a second column to indicate which tables will allow commenting.

In order to allow commenting on projects and purchase orders (at both the master and detail level). The appropriate reference table would be:

If we were to have more COBs in this application, we would simply add more indicator columns to the reference table. Of course, we could use a table that stores the names of the COBs and create an intersection table between table names and COBs. This is unnecessary because I have to make code changes to add a new COB anyway. Adding a column is painless and it makes it easier to see quickly which table can use which COB.

Another useful factor in the Comments COB is to have a real name for this object to facilitate reporting from the table and to report more than the UID of the table. One way to support this is to put an extra VARCHAR2 (100) column in the comment table to store a character description of the object being commented on. For example, for Employees, use the employee name, for Inventory, use the name of the inventory item. However, if the name of the object changes, the linked comment field would not be automatically updated. To solve this problem, a display name column is added to the reference table.

This expression is usually just a column name. For the Employee table, it would be a concatenation of several columns with spaces or commas. It could also be a function of the table UID, which could return a character string, possibly requiring a query of its own.

For example, if someone were commenting on a purchase order detail line, when referencing that comment, it might be worthwhile to see the parent purchase order number, date and vendor name. This might be a query involving a multi-table join.

In the above example we have shown the following display names:

Project – the name of the project

Purchase Order – the PO number followed by the date the purchase order was approved (e.g. 21432 – 02/12/1998)

Purchase Order Detail – the PO number followed by the name of the item ordered. In this case the name of the item is returned through a function.

In addition to a Comment table, we need a Comment Type table and an intersection table to validate which types of comments are appropriate for each table.

The COMMENT_TYPE table is a simple code description table.

VALID_COMMENT_TYPE is an intersection table between COB_REF and COMMENT_TYPE. Each row in this table represents an allowable type for a particular commentable table.

If you don’t want to go to the trouble of genericizing the code (this is a bit annoying), instead, you can have the user specify the comment type in the calling application which is then passed as an additional user parameter. However, if you build a significant quantity of applications, genericizing the code will pay off in the long run and will be well worth the time and effort.

A simple Comments table is one where a user can log comments about any topic such as budgets, purchase order, manufacturing, etc. We can genericize all of these comments within a single table. The table structure is as follows:

1. COMMENT_ID - NUMBER - Primary key column.

2. TIME - DATE - Date and time comment was logged.

3. PERSON - VARCHAR2(40) - Name of person logging comment.

4. COMMENT TYPE – NUMBER – Foreign key to the comment type table.

5. COMMENT - VARCHAR2(2000) - Comment itself.

6. FK_TABLE - Name of the table on which the comment is logged (This is a foreign key to the COB_REF table).

7. FK_LINK - NUMBER - Foreign key link. The key to the row on which the comment was made. (This is an overloaded foreign key that points to the table sited in the FK_TABLE column.)

The first four columns are all of the information we need about the comment. This table must now be linked to the object that the comment refers to. We need to make the table generic. The traditional way to do this would be by using an arc foreign key, which would require one foreign key column for every object that can be commented on. The disadvantage to this approach is that if we want to place commenting capability in a new system or extend it to new objects, data model and programmatic changes to whatever modules are supporting comments would be required.

What is needed is a generic approach to link comments to any object. To support this, we added 2 columns to the comment table:

1. The first additional column consists of the name of the table we are pointing to (FK_TABLE).

2. The second is the UID for that table (FK_LINK). This imposes a minor limitation, namely that it forces us to use single column primary keys in anything we need to link a generic object to. You can make this work with multi-column primary keys; however, the coding is more difficult. In practice, we use system-generated numeric IDs only for columns that may require generic structures. In Oracle8, it may be possible to use the OID; but we have not been able to test this yet.

The FK_TABLE column is enforced through simple referential integrity. The FK_LINK column, however, is enforced through a BEFORE_INSERT trigger. The trigger must enforce that the UID exists in the underlying table that we are trying to comment on.

This is where we need to use DBMS SQL because the table name is being passed on the inserting row. The trigger does a lookup back to the underlying table to enforce the referential integrity.

You also need to add a BEFORE_DELETE trigger to each of the commentable tables to make sure that comments are not orphaned in the comment tables when commented on objects are deleted.

To implement this you have a few alternatives:

1. Do nothing. Creation of the comments is only through the comments application anyway. The trigger is difficult to write and is not necessary. Orphaning comments is not a big problem since it won’t happen much. Disks are cheap anyway.

    

2. Write the triggers by hand. As long as you are not using many COBs and don’t have too many tables accessing them, this approach may be fine. However, if you are planning on writing a lot of systems, this becomes an arduous task.

    

3. Write utilities to create the triggers for you. If you are doing a lot of COB work, this will save you time in the long run.

Depending upon the system, each of these may be used. Surprisingly, the first alternative is not always as silly as it sounds. We did a system where few rows were ever deleted and all access to the COBs was through the applications. After much soul searching, we took the do-nothing approach.

Obviously, you need to create a simple form to support the maintenance of the COB reference information. In general you will build a single application to support all COB maintenance. The application will have a single tab that supports the REF_COB table and then additional tabs for each the intersection type tables.

Of course, it is possible to use a single type table for all COB intersection type tables. In fact, that is the way we do it. However, that will be a topic for another paper.

Some COBs may require their own maintenance application. The security COB that I mentioned at the beginning of this paper has 2 multi-tab forms just to support the required reference tables.

Next, create the underlying form to support the complex object (COB), in this case, Comments. Here is an example of the screen supplied for that purpose:

The screen in Figure 1 is called from the applications that maintain the commentable objects. The idea is that most commenting is done on this screen. In practice, a relatively small number of comments are actually logged on this screen. Most comments are logged in their respective applications. However, even that feature can be genericized. We created a Forms widget that can be easily brought into an application and used to support a number of comment types. To support this we made a small canvas that allows the user to enter a new comment as shown in Figure 2.

(I should note that the embedded comment widget was designed and implemented by I Michael Snyder of Dulcian, Inc.) The button labels in the comment widget become bold when there is an associated comment in the comment table. This way the user can quickly see that there are comments on an object without having to click on the button to check. The comment type is selected by clicking on a button above the comment field. Comments are automatically stored using an ON_VALIDATE trigger. The comments are populated in a post query trigger.

This Comment functionality can be brought into a new form in a few minutes. We write a COB package that maintains the COB so that coding is kept to a minimum.

The comment function uses the following input parameters:

• Function (insert, update, delete)

• Commented table name

• UID for the row in the table receiving the comment

• Comment

• Person making the comment

• Type of comment (optional)

The type of comment is optional, because some objects do not use comment type.

Comment date is not sent as a parameter since it is system generated.

The function returns an error code.

The main comment form uses a different architecture. In the calling forms, we use an iconic button on the toolbar that invokes the Comment module. This button creates a parameter list and passes it to the Comment form.

The parameters sent are:

• Commented table name

• UID for the row in the table receiving the comment

• Comment (optional)

• Person making the comment

• Type of comment (optional)

• Display name (optional)

Comment is optional because the application may partially write the comment based upon the action(s) of the user. For example, you may want to send the name of the calling form.

Display is optionally passed so that the comment module need not be required to perform a query to return the name of the commenting object.

In the Comment form, in the WHEN_NEW_FORM_ INSTANCE trigger, you will need a routine that calls up the name of the object being commented on (if necessary), taking the table name and retrieving the relevant expression from the FK_LINK_NAME table. Then, using DBMS SQL, form the appropriate query, retrieve the information and place it into the header field. The comment block in the comment form should filter on both the user parameter passed ID (optionally) and the table name and ID in the DEFAULT_WHERE clause.

The next step is to make sure that any new comments the user enters are attached to that same object. You can do this with a PRE_INSERT trigger.

The window can be designed to be full-screen because we can describe the object that is being commented on based on the information in the FK_LINK_NAME table. However, you may decide that the user interface is cleaner if the pop-up comment window is not so large. Also, you may wish to have a version of the window that does not display old comments, but only allows the user to enter a new comment. You may want the comment window to only display the user’s own comments but not be able to see others’ comments. All of these modifications are easily implemented using basic Forms techniques.

If you try to insert a comment against a table that does not appear in the FK_LINK_NAME table, or if the Comment_YN field is not set to "Y", then the following error message will be displayed "Commenting is not available for this structure."

Writing the code in this way allows you to make the comment functionality part of the template. Therefore, it requires not one line of additional code beyond what is in the template. Through a slight modification of the code, there are times when you are logging a comment when you don’t want the comment logged against the current block. For example, in a purchase order, even though the current record is the purchase order detail, you may only want to log comments against the purchase order itself. Modifications of this nature will require only a small amount of custom coding. If you don’t want to use the generic interface, there is nothing preventing you from building the application by hand, treating the Comment table as a simple detail table to whatever structure you are working with.

Finally, this application requires a report that lists all of the comments breaking on table name and ID and should allow filtering for both name and ID as well as date range, user and comment type. (For information on building a Flexible Reporting Front-End, a copy of the paper "Building a Flexible Reporting System Template Using Developer/2000 by Joseph Strano and Dr. Paul Dorsey may be obtained from our web site mailto:cfisher@dulcian.com).)

Building a COB may seem like a great deal of effort rather than simply building a comment structure and adding it on. To build a simple comment structure and the associated forms and reports to support it probably requires 4-8 hours of development time. By doing this as a COB, we may spend an extra day or two initially to set up the structure. However, once completed, these structures can be invoked over and over again in less than an hour. Keep in mind that the example we have laid out is one of the simplest possible COBS.

Consider the impact of using a COB on the number of tables required in a system. In our simple example, using a traditional development style, we would need a comment table and a comment type table for each of our commentable objects. Using a COB, we are able to support any number of commentable tables with only four additional tables.

From the application perspective, it is even more advantageous to use COBs. With traditional development, you would need to add comment functionality to every application where appropriate for each of these objects. With a COB, you write the application once and then implement it for a new object in a matter of minutes.

Because of the ease of implementing COBs, we end up adding much more functionality to our applications than we otherwise would using traditional development methods. In a recent system, we added commenting functionality to over 30 different tables.

What follows are examples of a few other generic structures that we have built that can be dropped into any system in a matter of minutes:

The standard problem of who gets to do what in a form has plagued us for years. Oracle provides us with role-based security at the database level. But this doesn’t help when it comes to event-level security in a form. The COB we have built for this requires three extra tables:

The Role table in Figure 3 consists of the standard Oracle roles. The Form table consists of the ID, name of the .fmb file and descriptive name of the form. Form Security Event describes the different things that we can add security to, namely blocks, menu events, procedures and triggers. The columns in Security Events would be block name, item name, trigger name, menu event and procedure. In the Required Role table, we store which roles are required for which operations. All of these events can be handled through a single call in each of the program units called CHECK_HISTORY. This passes down the name of the event that is attempting to execute along with the user ID. The CHECK_HISTORY function looks up a person’s roles and passes back a "succeed" or "fail." This COB can be used to support security requirements such as the role required to change a specific field. It can be used (with some difficulty) for even more complex security requirements.

We use a very complex demographic structure involving Addresses, Phones, Contacts, appropriate business uses for each, various types of physical phones, Country, and State/Province lookup tables that attaches these various tables together in a way that supports the demographic requirements of most of the systems that we build.

The principle in the Demographics COB is the same as in Comments, History and Security except that there are now 3 FK_LINK tables – one each for Address, Phone and Contact.

This is the most complex COB that we have developed. The simplest explanation of how it works is that, just as we are logging comments to a particular record, we can also log workflow acts to a particular record. Whereas anyone can log comments, there are specific rules about what are the possible workflow acts that can occur at any point in an approval process. Who is allowed to perform those acts is also carefully controlled.

Two structures were generated for this COB. The first one was similar to Comments, namely a single table to store workflow acts. A second, more complex structure was needed to store the following:

• What the approval process for a particular type of object is, down to a detailed level. For example, large purchase requests have a significantly different approval process than small purchase requests.

• How often and in which order these events can take place

• What roles are required to perform each of these acts

One complexity with the workflow COB that had to be supported was that, in different parts of an organization, the workflow requirements could be significantly different.

All of these rules were stored in a different structure. When an attempt is made to log a workflow act, that act is checked and either the event is logged or the appropriate error message is generated.

This was a much more complex structure to build than the others described; however, the principle remains the same. Once the structure is built, the amount of coding required to implement Workflow in a new system now requires 10-15 minutes.

We will never reach a point where a whole system will be constructed by bolting together several COBS. However, for the kinds of structures that recur across systems such as the five described in this paper, they can be built once and reused many times with very little effort. Because these objects are bolted on, if a bug is found or slight modifications need to be made, the object-oriented philosophy used here means that little, if any, changes would be required to the calling structures.

This is absolutely the right development approach. The most important component of your development effort that must be object-oriented is the development team itself. Through the use of COBS, you can greatly decrease the time required to bring projects to production.