Location, Location, Location…

How many of you need to print the address of an operating unit on a custom report or OAF page? How many of you need to include the county? How many of you ended up with a query like

select haou.name organization_name, flv.meaning county
from hr_all_organization_units haou
, hr_locations_all hl
, fnd_lookup_values_vl flv
where haou.location_id = hl.location_id
and flv.lookup_type = hl.style || ‘_COUNTY’
and flv.lookup_code = hl.region_1

and learn that it does not work in Europe? Let me tell you something…

Address styles are predefined in Oracle eBusiness Suite for all supported countries and languages. This is a lot. Counties are also pre-seeded in the applications. They exist in corresponding lookups, one lookup per country. If you are in the Unites States, Ireland, or Great Britain, then your lookup type is <style>_COUNTY indeed. However, once you move to the continental Europe, this is no longer true. And it is not that Oracle eBusiness Suite setup is wrong, it is that this ‘naming convention’ is not the right one. Moreover, there is no naming convention at all. Why? Because counties are defined by a value set associated with corresponding descriptive flex field (DFF). As such, it is up to the value set to read data from whatever source it seems appropriate. Our task as developers is to properly handle this situation.
The first step to uncover the mystery of the county is to find the DFF against the HR_LOCATIONS_ALL table and the value set which feeds the data. A quick search in DFF registration against this table gives us the title of DFF as ‘Address Location’.

Let us query the segments for this DFF. On the screen you see that STYLE is the context field in this DFF definition, and each style has its own set of segments. Though it does give us the information about the value set for the county, however, it also means that REGION_1 is not necessarily a county. For example, in style NL field REGION_1 is a free text used for street name, while in style NL_GLB this is county indeed. Moreover, not all styles use REGION_1 at all.

Under the assumption that if there is anything in REGION_1 column, this is what we need to print, out query morphs into

select haou.name organization_name
, hl.region_1 county
, fdfcu.flex_value_set_id
from hr_all_organization_units haou
, hr_locations_all hl
, FND_DESCR_FLEX_COL_USAGE_VL fdfcu
where haou.location_id = hl.location_id
and fdfcu.descriptive_flexfield_name (+) = ‘Address Location’
and fdfcu.application_column_name (+) = ‘REGION_1′
and fdfcu.descriptive_flex_context_code (+) = hl.style

What is left is little. We need to get the value from the value set which matches the value from column REGION_1.

There are multiple approaches to sole this issue. One can definitely go to the definition of the value set, read the lookup type from the where clause and build the query to extract the meaning. Too hard core, and not reusable.

Another approach is to build the query based on what is in the value set definition and get the value. Possible, but is not generic enough. For example, it will not work with independent value sets.

The only proper way is to use package FND_VSET and navigate through the returned value. This is the standard package which recognizes how the value set is defined and loops through all allowed values.
Luckily for us, there is another package which uses FND_VSET and does exactly what we need to do. Our final query becomes

select haou.name organization_name
, nvl( oe_sys_parameters_util.get_value(fdfcu.flex_value_set_id, hl.region_1), hl.region_1) county
from hr_all_organization_units haou
, hr_locations_all hl
, FND_DESCR_FLEX_COL_USAGE_VL fdfcu
where haou.location_id = hl.location_id
and fdfcu.descriptive_flexfield_name (+) = ‘Address Location’
and fdfcu.application_column_name (+) = ‘REGION_1′
and fdfcu.descriptive_flex_context_code (+) = hl.style

In this example we learnt how to research DFFs and display values from value sets. There are multiple applications to this approach, including showing data for custom DFFs or other seeded DFFs such as organization information.