Solving ORA-01722 – Invalid Number while navigating from Oracle BI 11g to Oracle eBS R12


In a previous blog post, I covered how to navigate from Oracle BI 11g to Oracle eBS R12. While setting this up, I had some challenges to overcome. One of them was the following. When I clicked the column with the Action Link, the following error showed up;

The Oracle eBS Server was available, so that couldn’t be the problem. Checking the logfile(s) showed that there was a ORA-01722 error involved.

The question at this point is; Which query is throwing a ‘Invalid Number’-error. Thanks to Robin I got the idea to Sql Trace the Connection Pool which is being used for the Action Link.

Via the generated Trace Files I was able to identify the problem – query. It turned out to be the query which constructs the Url to navigate to Oracle eBS.

select fnd_run_function.get_run_function_url
 ( cast
 ( fnd_function.get_function_id ( 'AP_APXINWKB_SUMMARY_VIEW' ) as number )
 , cast ( '200.00' as number )
 , cast ( '*****' as number )
 , cast ( '0.00' as number )
 , 'INVOICE_ID=*****'
 , null ) as action_link
 from DUAL

A quick check in SQL*Plus gave the same error. The problem is in the Cast functions and the NLS_NUMERIC_CHARACTERS-settings.

An easy;

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;

….. solved the problem in SQL*Plus.

In Oracle BI, I added the ‘Alter Session’-Statement in the Connection Pool which is being used for the Action Link.

Problem Solved.

– Daan

Navigating from Oracle BI 11g to Oracle eBS R12

One of the interesting features of Oracle BI 11g is the Action Framework. This feature makes it possible to e.g. navigate from Oracle BI 11g to Oracle eBS R12. When you have a look at the different Oracle BI(A) presentations from Oracle you will see a slide regarding Action Links ‘Insight to Action’.

It looks like this is standard Out-of-the-Box functionality. This is partly true. You should have at least the Oracle BI 11g & Oracle eBS R12 integration in place. Next to that there is some additional configuration / implementation required.

Oracle BI configuration

  • Enable Action Link to Oracle eBS (ActionFrameworkConfig.xml)

Navigate to the following location

ORACLE_MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/config/fmwconfig/biinstances/coreapplication

Edit the ActionFrameworkConfig.xml

After you have modified the ActionframeworkConfig.xml file, you have to restart the Managed Server in Weblogic that is hosting your Oracle BI EE environment.

  • Oracle BI Connection Pool

Edit / Add a Connection Pool entry in the Oracle BI Administration Pool. This Connection Pool will be used to connect to Oracle BI Applications

Note  

I – the Application Role must have privileges to execute direct database requests against the Oracle eBS Connection Pool.

II –  for the Application Role (responsibility) to successfully invoke a Navigate to E-Business Suite action, the target Oracle eBS function must be accessible from the user’s current Oracle eBS Context.

Identify Oracle eBS Form Parameter

If you want to navigate to a query the form you are navigating to, you should identify the Oracle eBS Form Parameter. This can be achieved by the following steps.

1. Log into Oracle eBS and select the Form you want to navigate to

2. Identify the name of the Form and the Function which calls this Form

Navigate to the Forms Personalization Screen via Help, Diagnostics, Custom Code, Personalize

  • Forms
  • Function

We will use the name of the Form to identify the possible parameters of this Form. In this case;  APXINWKB. The Function Code will be used later on when creating the actual Action Link.

You can download the related .fmb-file from the Oracle eBS Application Server in the following location; $AU_TOP/forms/NL. The ‘NL’-part refers to the language.

When you open the .fmb-file in Oracle Forms, you should be able to locate the parameters


Action Link

Now everything is in place to create the actual Action Link.

  • Create Action Link

Create the Action Link via New, Action, Navigate to E-Business Suite 

  • Edit Action Link

Now we must edit the specific Action Link details

This consists of;

  • The Function Code (AP_APXINWKB_SUMMARY_VIEW)
  • The Oracle BI Connection Pool (Oracle EBS OLTP Connection Pool – Action Framework)
  • Parameter(s) –> INVOICE_ID

With this Action Link it is possible to call this link directly end fill in the Invoice Id yourself.

Another option is to make the Action Link part of an Oracle BI Answers Request.

  • Edit Oracle BI Answers Request

Select and edit a column in an Oracle BI Answers Request. Navigate to the Interaction Tab. Here we can add / edit an (existing) Action Link.

I have chosen to use a Invoice Number with an Action Link which uses a hidden (Invoice Id) column to navigate to Oracle eBS. This way I do not have to show the Invoice Id in the Oracle BI Answers Request. Still I can use the Invoice Id as a parameter in the Oracle eBS Form.

It’s a little bit of work, but still it’s a nice feature.

– Daan

RittmanMead BI Forum 2011 – Masterclass (Part II)

After the lunch, which was excellent by the way, Mark continued the Masterclass. Next on is the OBIEE11g – Server & RPD New Features. There are new features in the RPD which you should try yourself (including me), before you really can understand what is happening. The guys over at RittmanMead are decompiling .jar-files to make anything more clear! I guess I will start with the Admin-tool. A few highlights;

  • Column Descriptors –> Use the description to show, while in the background you use an Id (indexed) to ‘fire’ a query (Oracle Discoverer functionality)
  • Lookups Tables & Functions –> Interesting functionality Venkat already blogged about
  • Hierarchies (Ragged & Skip Level) –> Only use Ragged and Skip Level when you really need it, because it will generate complex queries to identify and it will be giving problems with generating MDX for Essbase.
  • Parent Child Hierarchies –> PC Aggregation is only aggregating for the member by default. You should do some extra modeling to bring the closure table into the model.
  • Oracle OLAP Option –> The BI Server now can manage navitve Oracle OLAP. As Mark mentioned; “A cautious welcome”

Tony closes the Masterclass whit the OBIEE11g – SOA Integration. New in Oracle BI 11g is the Action Framework. The Action Framework makes it possible to integrate your Business Intelligence System with your business process. Either by navigating through an url or by integrating with Web Services. Calling Web Services opens a whole new world for me. Now you see a further integration of Oracle BI 11g and Oracle Fusion Middleware. On the one end this Action Framework gives us a lot of new possibilities. On the other end it gets more and more complex. You should get to know a few of the following and more:

  • SOA Concepts
  • JDeveloper
  • BPEL Workflows
  • Oracle SOA

This way you should be able to integrate with external applications by building and deploying web services and invoke them from Oracle BI EE.

Thanks to Mark and Tony, they did a great job with this Masterclass. Although it seems like an information overload, both guys have presented a lot of information in such a way we do not have to be bored in the coming months.