Home » SQL & PL/SQL » SQL & PL/SQL » SQL failing when the language is NL (Version : 9.2.0.8.0)
SQL failing when the language is NL [message #379908] Thu, 08 January 2009 06:01 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,
We have concurrent program in Oracle applications which has the following the sql statement in a package. The program run successfully and produces the output when the language is 'American English' but fails in non-english languages.

.
.
FUNCTION  GetAFSIdent( pAccFlexStrct IN VARCHAR2 )
   RETURN NUMBER
   IS
   -- Variables
      vAFSIdent  fnd_id_flex_structures_vl.id_flex_num%TYPE;
   BEGIN

      SELECT id_flex_num
      INTO   vAFSIdent
      FROM   fnd_id_flex_structures_vl
      WHERE  id_flex_structure_name = pAccFlexStrct;

   RETURN(vAFSIdent);

   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
       RAISE_APPLICATION_ERROR(-20901,'Accounting flexfield structure '||pAccFlexStrct||' does not exist.');
   END GetAFSIdent;
.
.


Can anyone please let me know how to overcome this problem.

Thanks
Re: SQL failing when the language is NL [message #379910 is a reply to message #379908] Thu, 08 January 2009 06:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It will be really useful if you could post copy and paste the actual error message.

Regards

Raj
Re: SQL failing when the language is NL [message #379913 is a reply to message #379908] Thu, 08 January 2009 06:40 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

I have amended the exception handler to display the error message,

Oorzaak: FDPSTP is mislukt vanwege ORA-20901: Accounting flexfield structure Exel Accounting Flexfield does not exist.100 -ERROR- ORA-01403: Geen gegevens gevonden.
ORA-06512: in "APPS.DHL_CROSS_VALIDATION", regel 42


ORA-01403 is for no_data_found but the actually the sql query returns me value when I run the sql query in sqlplus or the concurrent program in English.

PPT(APPS)> SELECT id_flex_num
  2   --      INTO   vAFSIdent
  3         FROM   fnd_id_flex_structures_vl
  4         WHERE  id_flex_structure_name = &pAccFlexStrct;
Enter value for paccflexstrct:  'Exel Accounting Flexfield'
old   4:        WHERE  id_flex_structure_name = &pAccFlexStrct
new   4:        WHERE  id_flex_structure_name =  'Exel Accounting Flexfield'

ID_FLEX_NUM
-----------
        101

1 row selected.

PPT(APPS)>
Re: SQL failing when the language is NL [message #379919 is a reply to message #379908] Thu, 08 January 2009 07:17 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is fnd_id_flex_structures_vl a view by any chance?
And does it have a where clause that involves checking date columns in some way?
Re: SQL failing when the language is NL [message #379922 is a reply to message #379913] Thu, 08 January 2009 07:42 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about running the same query in sql*plus in the non english environment? By any chance do you use VPD ? Are you sure do you have the table populated with the required set of data ?

Regards

Raj

Re: SQL failing when the language is NL [message #379929 is a reply to message #379908] Thu, 08 January 2009 08:34 Go to previous message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

yes, the view "fnd_id_flex_structures_vl" is using a base table fnd_id_flex_structures_tl with a filter condition 't.language =userenv(language)'..the flexfield was not defined in NL and this has caused the issue. Issue resolved.

Thanks for your help.
Previous Topic: ORA-01799
Next Topic: What is wrong with this trigger code? (merged)
Goto Forum:
  


Current Time: Sat Dec 10 14:20:20 CST 2016

Total time taken to generate the page: 0.15759 seconds