How to cope with Lists Of Values (LOVs)

Another problem that has been perplexing me is what is the best way to manage LOVs. Apex provides a variety of mechanisms for managing LOVs including static or dynamic LOV which can be specified centrally or locally to an item. Best Practice dictates that centrally managing LOVs is preferred where possible as it provides a single point of update.

I have found a few issues with the Static LOVs as follows:
• Discoverer Reporting. Viewing LOV values in Discoverer is tricky as you have to query the flows table. This can be done with the following view but you have to remember to change the names of the flows tables after an upgrade of Apex.

SELECT
DISTINCT lov.lov_name
,lovd.lov_disp_value
,lovd.lov_return_value
,lovd.lov_disp_sequence disp_sequence
FROM flows_030100.WWV_FLOW_LISTS_OF_VALUES$ lov
,flows_030100.WWV_FLOW_LIST_OF_VALUES_DATA lovd
WHERE lov.id = lovd.lov_id


• Conditions. It is possible to add conditions to each static LOV item, which in effect writes nested sub-queries that are difficult to manage and probably not very performant.
• Maintenance. Changing the values of the LOVs can only be done from the workspace, so a minor change to an LOV values can only be done as part of a release.
• Deleting an LOV item causes the ID from the Flows table to be returned (if display additional items is checked) or a null value is returned (If display nulls is checked) or return the first value in the select list. None of these options are ideal.

My preferred method of managing LOVs is to create my own custom table along the following lines:

LOV_ID (NUMBER)
DISPLAY_VALUE (VARCHAR2)
ELEMENT_NAME(VARCHAR2)
LOV_NAME (VARCHAR2)
SORT_ORDER (NUMBER)
VALID_FROM (DATE)
VALID_TO (DATE)
NOTES (VARCHAR)
EDITABLE (VARCHAR2)
ATTRIBUTE1 (VARCHAR2)
ATTRIBUTE2 (VARCHAR2)
ATTRIBUTE3 (VARCHAR2)
ATTRIBUTE4 (VARCHAR2)


I have added a few additional attributes columns, which are useful for storing values for reporting or conditional displays. Each LOV item has a validity period, which can be used to expire values so they do not appear as a selectable items in an LOV. However, as they still exist in the table they can be displayed in reports and as additional items. The element name is useful for writing scripts to update attribute values, as the ID could differ from your development environment and the display value may change. The notes field is used for storing information about the values set in the additional attributes.

To get invalid items to display as additional item in a Select List or popup lov use the following for your LOV query.

SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to ,SYSDATE)
UNION
SELECT display_value
,lov_id
FROM lov
WHERE lov_id = :p1_colour_id
AND lov_name = 'COLOUR'


Its quite nice to let the user know that the value has expired and needs changing. I like to do this by appending an asterisk to the display value with the following lov query.

SELECT nvl2(lov2.lov_id
,lov1.display_value
,'(' lov1.display_value ')') display_value
,lov1.lov_id
FROM (SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to,SYSDATE)
UNION
SELECT display_value
,lov_id
FROM lov
WHERE lov_id = :p1_colour_id
AND lov_name = 'COLOUR') lov1
FULL OUTER JOIN (SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to,SYSDATE))lov2

ON lov1.lov_id = lov2.lov_id

Example.

Comments