In this article, we will look into how to fetch the lookup values when there are country-specific values.
In Lookups, most of the values are straightforward and the same for all countries. However, there are some lookups that vary by country Ex: Gender, Marital Status, National Identifier.
For these lookups, there will be inclusion and exclusion rules defined in the tag based on which countries use the lookup value and which countries are excluded from it.
Let’s take example of Marital Status
select lookup_type, lookup_code, meaning, tag from hcm_lookups where lookup_type = 'MAR_STATUS' and enabled_flag = 'Y'Values look like this:
If the Tag is blank, the value is available for all legislations.
If the value has +US only, then it is available for US legislation only. +AE for United Arab Emirates, etc
We can get the Country Code and Description from below query:
select TERRITORY_CODE, TERRITORY_SHORT_NAME from fnd_territories_tlNow, if we want to fetch the values only for a specific legislation,we will have to pick all the ones with tag as blank and values with +Country Code and exclude – Country Code.
SQL Query to get the values for a AE Legislation for Marital Status:
SELECT DISTINCT LOOKUP_CODE, meaning
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE ='MAR_STATUS'
AND ENABLED_FLAG='Y'
AND (TAG LIKE '%+AE%' OR TAG IS NULL OR CASE WHEN TAG LIKE '%-%' THEN TAG END NOT LIKE '%-AE%')
ORDER BY LOOKUP_CODE
We can clearly see that it has fetched blank tag values, and +AE values.
