NLS_LANGUAGE what a pain... Oracle EBS issue

NLS_LANGUAGE what a pain... Oracle EBS issue

I have a customer who has a report that works great in the U.S. but when they rolled the report out to users in the U.K. the report returned no values.  At first blush, everything seemed the same and we expected that the report should run.

The only difference we could find was that the UK Oracle client was setup with an nls_language of 'ENGLISH' while in the US it was setup with an nls_language of 'AMERICAN' - but even so, we didn't see why that would make a difference, since none of the report parameter values would be any different using ENGLISH vs. AMERICAN.  I had originally though the issue might be the date but it turned out that both systems were using nls_date format.

The developer of the report started from scratch, adding each table and join condition one at a time until he found the one which resulted in the query returning no values.  It was an Oracle EBS table hr_operating_units.  Well, actually, it turned out that hr_operating_units is a view and not a table and when you look at the view code:

SELECT   o.business_group_id,
         o.organization_id,
         otl.name,
         o.date_from,
         o.date_to,
         o3.org_information5,
         o3.org_information3,
         o3.org_information2,
         o3.org_information6
FROM     hr_all_organization_units o,
         hr_all_organization_units_tl otl,
         hr_organization_information o2,
         hr_organization_information o3
WHERE    o.organization_id = o2.organization_id
     AND o.organization_id = o3.organization_id
     AND o2.org_information_context || '' = 'CLASS'
     AND o3.org_information_context = 'Operating Unit Information'
     AND o2.org_information1 = 'OPERATING_UNIT'
     AND o2.org_information2 = 'Y'
     AND o.organization_id = otl.organization_id
     AND otl.language = userenv ('LANG')

What you find is the very interesting last line... the view is filtering based on the user's session's language setting.  Set to 'ENGLISH' there are no matching records, set to 'AMERICAN' there are.  We confirmed this as follows:

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;

SELECT COUNT(*) FROM hr_operating_units;
 

61 rows
 

ALTER SESSION SET NLS_LANGUAGE = ENGLISH;

SELECT COUNT(*) FROM hr_operating_units;
 

0 rows
 

I hadn't expected this and it took some methodical troubleshooting to resolve.  If you are getting different results running queries on different PCs, check the language settings and check the query - particularly if you are using views that you didn't create!!!

Hope that helps.