Use one lookup table as base for all the LOV's

One lookup table as base for all the LOV's

Lists of Values ... it's probably one of the most often used components in APEX development. And actually in any kind of software development, I suppose.

Having an E-Business Suite background, I got the idea to steal one of the better ideas the EBS has and that's dealing with LOV's, "lookups" they're called.

All those lookups are based in one single table, operating over a lookup-type. In EBS a set of views is defined and those views are used depending on the EBS domain.

I choose not to go that far and kept it to one single table.

A second idea stolen from EBS is the dependancy between lookups. Also that idea I simplified a bit and I kept it to one level. Meaning a set of values can differ from the chosen value of another set.

Finally I used the idea behind the multi-lingual solution of the EBS. Though that is the one I simplified the most. Whereas in EBS you have a base table containing a code and an _TL table containing one line for each installed language, in this solution I simply maintain all languages in the same base table. All records have a language code and in most systems there are two, max three different languages.

Table structure

Let me run you first through some background info. This solution was built for an extension on the EBS, in the HR modules. But the customer has a whole different database setup for the APEX extensions, so we're not directly using the EBS itself. A daily routine copies some basic EBS data over to the "APEX db" using a database link.  The solution itself and the architecture has pro's and con's, but I'm not writing about that now, let's keep focus on the LOV's.

EBS uses a lot of LOV's and of course we want to offer the user the exact same LOV's in the APEX applications.

So the table EBS_GENERIC_LOVS was created.

• LOV_TYPE: As explained in the introduction, the column LOV_TYPE refers to the type of LOV in EBS. It's basically the grouping name of the LOV;
• LOV_CODE: that's the code used for the value itself. A code is unique within a type;
• MEANING: is in fact the display value that goes with the LOV_CODE;
• DEPENDANT_LOVE_TYPE and DEPENDANT_LOVE_CODE: it's the DEPENDANT combination of type and code. This links the value with a value chosen in another LOV. This LOV must of course exist and on the APEX page it must be entered before this LOV;
• ACTIVE_FLAG, VALID_FROM and VALID_TO: the combination of these columns make the value available or not in the LOV;
• LANGUAGE: a two-letter code for the language, depending on the language chosen in the application, it will show the correct value of the LOV;
• SEQUENCE: an optional way to fix the ordering sequence in the LOV.

Language option

In case you're not familiar with EBS and how languages are being used up there: EBS is always installed in English (code 'US'). Optionally one or more other languages are installed, e.g. Dutch (code 'NL'). It's just a side note, because it has nothing to do with the solution itself. But it explains why screenshots show weird data :-)

When a user logs on to the EBS, a language is picked. That displays all the screens in that language. Also the data seeded by Oracle will be shown in the correct language.

But when you enter data, let's say the names of all the departments of your organisation, you initially enter that in one language. As Oracle can't translate that (yet???) in the other languages by itself, by default the entered value gets copied over to all different languages installed. And the user is responsible to and translate the values manually.

An example makes it more clear:

Two languages are installed: US and NL. A person that sets up the different departments is logged on in Dutch (code NL).

This person enters "Verkoop" as name of the sales department. When the SAVE button is pressed, two records are being saved in the _TL table for departments:

1. Name: 'Verkoop' - Language: 'NL'
2. Name: 'Verkoop' - Language: 'US'

Now it's up to this person doing setup to go and change the second line to:

• Name: 'Sales' - Language: 'US'

If this step is not performed, every place where the department name is shown, regardless what language is chosen at login, the name 'Verkoop' will popup.

Dependant lists of values

This is exactly the same as what is done in APEX via Cascading LOV's. But here the link exists already in the table.

En example: the salary of an employee is identified by the grade that is defined for the employee. Each grade is split up in different steps (spinal points). The combination of a grade and a step (on a specific moment in time) define the salary.

This means the step (which is referred to as spinal point in the EBS) is dependant on the grade.

So every LOV_CODE of the type 'PER_SPINAL_POINT' will contain 'PER_GRADES' as dependant_lov_type and a specific value in dependant_lov_code. And that code of course must be a valid code for the type 'PER_GRADES'.

The LOV in APEX

In APEX you still need to define the LOV's that belong to the specific page items.

But this is where you gain from this solution. Because all your LOV's will look the same. It's a simple query every time on the same table:

As you can imagine, the language is stored in the application item AI_LANGUAGE.

The columns description and meaning are often mixed in EBS, hence the NVL.

On every LOV_TYPE you can make an APEX LOV with this same query as base. The only thing different will be the where clause "lkp.lov_type = ".

The dependant LOV identifying the step will then look like this:

The result in APEX

You notice that although the application language is English, the content - in this example the names of the grades - is in Dutch. This is - as explained above - because in EBS the (manual) translation of the setup of the grades from Dutch to English was never done!

Because Step is depending (cascading) on the Grade, no value can be selected as long as the Grade hasn't been entered:

Once a grade is chosen, the dependant values in Step appear:

My conclusion

This solutions requires hardly no extra effort compared to the standard way of working.

I would say even less effort is required.

On top of that it is absolutely no stopper of any existing LOV already in the system.

But most important advantage: I described the link with EBS, but in general it can be used for any system you link with: exactly the same LOV's on the base system can be re-used in the APEX application.

It should be clear that everything described above can also be done via normal LOV behaviour in APEX. But I truly believe that you will limit maintenance cost dramatically by using one single base table for all the LOV's!

Happy to share ....