Resolving Fusion OBIEE / EBS R12 / SQL Server Driver Datetime field overflow errors

Tip of the week:

Environment: EBS 12.1.3, Oracle, Microsoft Windows 2008 R2, SQL-Server 2008 R2, Oracle Fusion BI 11g.

As we continue to scuffle through Fusion OBIEE / EBS R12 / SQLServer we came across yet another enthralling issue. Yes its enthralling especially when you have heterogeneous databases in such tightly integrated complex environments.

While trying to perform a full load first time with 12.1.3 adapter (OBI Analytics provides 12.1.3 adapter to ease migration from EBS 11i to R12.1.3) , we ran into an interesting error that we found in informatica powercenter workflow monitor‘s log events.

ERROR : (8840 | WRITER_1_*_1) : (IS | BIA_IS) : node01_server_name : WRT_8229 : Database errors occurred:

FnName: Execute — [Microsoft][ODBC SQL Server Driver]Datetime field overflow

FnName: Execute — [Microsoft][ODBC Driver Manager] Function sequence error”

Looking further into the logs, we were able to figure out the exact record that was causing the failure. Interesting we found the error was due to a record with date 31-MAY-0201. Even though this error can be rectified in EBS(our source system) or try to locate where this error is occurring and fix the mapping to perform the operation with YYYY instead of just YY. Vital material to remember is SQLServer doesn’t accept records outside of the date range January 1, 1753 – December 31, 9999. When we are so used to work on Oracle databases, such SQL Server limitation could be missed.

As you may know that you can skip errors in informatica using the property  “Stop on Errors” to 0, which is  set to 0. This is not the case with 12.1.3 adapter.  Its set to 1 by default, which will force the job to fail on error. Of course, this is not applicable for fatal errors though.

Further tips on Fusion Oracle Business Intelligence / SQLServer environment @

As always, thanks to contributors Ramke Ramakrishnan and Steve Meder.


Oracle EBS R12.2 – Key Features – Revisited

As we get closer to EBS 12.2 release, I had to revisit my earlier post on R12.2 key features to ensure its up to date. Here are some of the stand-out feature set that may help you to get a quick glimpse at.

Native Technology Stack Installation

• Now tech. stack has better integration with Oracle Universal Installer

• Another big one. Yes, Faster Technology Stack Installation. At least 25% much faster.

Silent-mode calls to install and configure Oracle Database 11.2, WebLogic Server (WLS) and Oracle HTTP Server (OHS)

• AutoConfig is tightly integrated with native technology stack installation

 Database RMAN Restore

• Simplifies Integration with Grid Infrastructure.

• Seamless integration with different storage methods (OCFS2, NFS, etc)

Faster integration with Automatic Storage Management (ASM)

 Dual File System

• A must needed feaure,  dual file system capability is instrumental for 12.2’s Online Patching feature that allows users to continue using the application while patching.

• Dual File System allows replacing files with minimum downtime that leads to improved High Availability

Dual Port Configuration

As part of online patching feature, Oracle requires DPC (Dual Port Configuration)

• Dual Port Pool configuration

• Easier port assignments

• Allows port customization in run-time and patching file systems

Optional 11g Home for Upgrades

I always wonder why we can’t simply try to use an existing Oracle home during configuration. Well, Oracle incorporated this capability in 12.2.

• Allow integration with an existing 11gR2 Oracle Home

• Simplified Technology Stack Upgrade

• Reduced Upgrade time

WebLogic Server Features

As middleware technology stack is based on the new improved Weblogic that has atleast the following key capabilities to help manage the EBS environment better. As WLST 11gR2 is what is slated to get bundled with 12.2, its interesting to know if Oracle will WLST 12c into the mix over 11gR2

• • WebLogic Scripting Tool (WLST)

• High Availability & Failover

• Consolidated Administration, Configuration and Deployment

• Enhanced Monitoring & Messaging via centralized service administration.

• Troubleshooting framework

• Integration with OAM

• WLS-Specific configuration setups

• Proactive Management via Server & Performance Monitoring

• Easier Problem Solving & Performance Tuning

• Better resource planning via historical reporting

 Online Patching

In 12.2 all patching operations are online. In other words, Oracle E‐Business Suite will remain available to users during patching operations.  EBS 12.2 will take advantage of 11gR2 Database’s Edition-Based Redefinition features to provide support for Online Patching.  Edition-Based Redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

• Downtime will be measured in minutes not hours or days

• Downtime windows will be very predictable

• Patches are applied while the Production Application remains online

• Allows an application to efficiently store multiple editions of its Application Definition in the same database

• Provides an isolation mechanism (The Edition) that allows pre-upgrade and post-upgrade schemas to co-exist

• Client code chooses the particular “Edition” that it wants to connect to.

Once again thanks to Ivo Dujmovic and Max Arderius from Oracle on sharing valuable information.

Resolving ORA-29278: SMTP transient error: 421 Service not available

Tip of the week: When trying to send email using UTL_SMTP in RedHat Linux Server getting following error

ORA-29278: SMTP transient error: 421 Service not available

The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem is due to database server’s network configuration not allowing it to contact an external server.


We found sendmail to use a smart host was not setup caused the error. Below steps should give you the needed  details to setup and as well as check the configuration.

In your /etc/mail/ file there should be a line as follows:

dnl define(`SMART_HOST’,`smtp.your.provider’)

Remove the “dnl” section and replace “smtp.your.provider” with the fully qualified domain name of the smart host you wish to use so that the line reads:

dnl define(`SMART_HOST’,`smtp.your.provider’)

Generate a new file by running

# cd /etc/mail

# m4 >

This will generate the .cf upon restarting the sendmail by

# service sendmail restart

However you can manually do this with the following command: make -C /etc/mail

If you still experience the error, perform the following diagnostics steps.

1. The first step is to send a message from the server you made the changes on.

[root@rhbox mail]# echo test1|mail -s test1

2. The next step can be done on the server.

Once the message is sent you will want to tail or grep /var/log/maillog for the relevant email that was just sent.

Nov 11 09:51:14 aloha sendmail[31900]: pABEpEiF031898: to=<>, ctladdr=<> (0/0), delay=00:00:00, xdelay=00:00:00, mailer=relay, pri=120370, [], dsn=4.0.0, stat=Deferred: Connection refused by

3. From the error message, its imperative that you need to open up the firewall to allow smtp.

Once thats resolved, you should be good to go.

Useful MOS note.

ORA-29278: SMTP transient error: 421 Service not available” When Using UTL_SMTP to Send Email [ID 604763.1]

SQL Server 2012 vs Oracle – Key Licensing update

How many of Oracle’s customers outcry about Oracle’s core based database licensing? We might have certainly heard ‘on the fense’ customers simply preferring to go with Microsoft SQLServer due to its bargain processor-based licensing. Of course, this article’s purpose is not to compare the features.

Looks like Microsoft recently realized that they need to do something to pump up their mid-20s stock price.  Rightly so, MS preferred to follow Oracle’s foot-steps to be ingenious with their licensing metrics. Effective SQLServer 2012, you will not be able to buy socket-based licenses. Yes. SQLServer will be sold in core-based licensing. Here are some additional details from Microsoft on SQLServer 2012 core-based licensing.

  • Enterprise and Standard editions will be available under core-based licensing. Cored-based licenses will be sold in two-core packs. The Standard Edition will also be available as a Server+ CAL licensing option.
  • To license a physical server, you must license all the cores in the server, with a minimum of 4 core licenses required for each physical processor in the server.

So what does this mean to companies using SQLServer and as well as Oracle? Well, now SQLServer doesn’t have the vast competitive cost advantage over Oracle as it did before and this could lead companies to stick to Oracle, especially with the magnitude of features that you get for the same or marginally higher price.

Nonetheless, this could also help Microsoft’s immobile stock price to shoot up a bit, when its customers stack up licensing until they figure out cost-effective strategies including server consolidation, etc.

Additional Oracle and SQLServer licensing details can be found here:

Defuse SQL Server Complexities in Fusion OBIEE

Tip of the week:

Environment: Microsoft Windows 2008 R2, SQL-Server 2008 R2, Oracle Fusion BI 11g.

Oracle’s BI platform fortifies its stronghold in Gartner’s magic quadrant for Enterprise Business Intelligence, especially with Fusion OBI. It’s imperative that several companies get creative to take advantage of heterogeneous BI tools to be cost-effective. For example, companies can buy limited OBIEE & OBIA licenses and leverage the data warehouse content via Microsoft’s BI tools. Of course, such strategy to consume Oracle’s content over non-Oracle tools requires Oracle’s blessing in terms of licensing.

Once you have cleared the licensing obstacle, now the actual challenge begins. Yes, using SQL-Server as the data warehousing DB for Fusion OBI platform is not as easy as using an Oracle DB. We came across several undocumented SQLServer related issues and I would like to share as much details as possible.

As most of the issues gyrate around DAC installation/configuration, ensure you complete the following.

  1. Ensure you install Microsoft SQL Server JDBC Drivers 3.0 which is compatible with SQL Server 2008.
  2. Copy sqljdbc.jar and sqljdbc4.jar files from C:\Program Files\sqljdbc_3.0\enu to your DAC installation directory. For eg. to E:\oracle\DAC\bifoundation\dac\lib
  3. Stop the DAC Server. Open commands prompt and go to E:\oracle\DAC\bifoundation\dac\lib and copy sqljdbc4.jar sqljdbc.jar. In a way we invented this fix 😉

We spent several hours to resolve the issues using the above fixes and I am sure this will unquestionably save your time too 🙂 . Should you have any questions, please post the question.

JBO-29000: When uploading .rtf through XML Publisher

Tip of the week: JBO-29000, when uploading a .rtf through BI Publisher/XML Publisher Administration -> template in Oracle EBS R12.1.3

Error Details: When uploading a .rtf file through XML Publisher Administration ->template receiving following error:

oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.OutOfMemoryError, msg=Java heap Error Page

Exception Details:

oracle.apps.fnd.framework.OAException: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: java.lang.OutOfMemoryError, msg=Java heap space  at oracle.apps.fnd.framework.OAException.wrapperException(   at oracle.apps.fnd.framework.OAException.wrapperException(  at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(         at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(  at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(   at oracle.apps.xdo.oa.template.webui.TemplateFileAddCO.processFormRequest(   at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(

Solution :

Oracle recommends to use XDOLoader to get around this issue due to the upload process requires validation of the layout and it fails due to limitations of the iAS webserver.  Nonetheless, this will not be an issue in EBS R12.2 onwards due to iAS being replaced by WebLogic Server (WLS).

Oracle Fusion Apps – Key Financial Features and More

AT OOW, Oracle’s VP of product strategy revealed 100 plus customers being in Fusion. Its a solid number for a product that’s still evolving to be global ready. Nonetheless, its amazing to hear ingenious strategies Oracle proposes to adapt Fusion Apps. Interestingly, an extensively publicized option is a co-existence strategy to help companies who wanted to bleed Fusion early. Even though the Fusion sessions portray 100+ customers in Fusion Apps and more to follow, talking to Fusion product directors provides further insight to the adaption strategies, which to wait for Fusion 11g Release 2. Finally Oracle is taking the stance to have Fusion built on 100% standards-based architecture by providing a customization and extension friendly landscape.

Fusion early adapters in co-existence strategy are predominantly on Human Capital Management(HCM). But the product group’s viewpoint is to have Performance Management module in Fusion and the rest in R12, which makes perfect sense. Especially, when Fusion is still transforming into full-blown production state. Even though the focus of this article is Financials, I just wanted say HCM’s Predictive Analytics is my favorite oneIt is embedded within Oracle HCM and is automatically generated by Oracle Data Mining to enrich dashboards and manager’s portals with predictions about the likelihood that an employee will voluntarily leave the organization and a prediction about the employee’s likely future performance.

As Oracle continues to push for the Fusion version of the Apps, it also provides flexible adaption strategies such as

Module Co-Existence: Implement an individual Oracle Fusion Application module to complement or extend the functionality of existing Oracle EBS environment.
Pillar Co-Existence: Implement a suite of applications such as HCM in Fusion and run the rest in existing EBS environment.

Here is the pictorial representation of Module co-existence strategy.

As there are several blogs trumpeting Fusion capabilities, I try to limit it to a few that I haven’t seen much elsewhere.

Now on to Financials.

Fusion Accounting Hub as a Reporting Platform

How many times we’ve heard from business users about the lack of financial reporting capabilities in EBS. Well, Oracle certainly heard it all and responded with Fusion Accounting Hub’s reporting platform. Even though the product is pretty nice fit, it has to be licensed separately just as is the Financial Accounting Hub in EBS. 

The Fusion Accounting Hub’s reporting platform provides real-time access to financial information with exceptional reporting and analytics capabilities to all users. Interestingly, reporting platform has its own embedded multi-dimensional data model and leverages Oracle’s own Hyperion Essbase. It is important to understand that Fusion Applications only uses cubes with ASO.

The main purpose of embedding the Essbase cube within Accounting hub’s reporting platform is twofold: First, it allows you to store large quantities of data in a way that can be accessed rapidly. Second, it allows you to manage and organize this data for fast and powerful retrieval for immediate decision support.

When the Fusion Accounting Hub is integrated with an E-Business Suite General Ledger, customers can apply a chart of accounts mapping to convert chart of accounts values to a corporate or other standardized reporting structure. In this case, the E-Business Suite General Ledger can continue to serve as the local book that is used for journal posting, period close, and other accounting processing, while Fusion General Ledger can serve as the corporate book where you perform real-time reporting and analytics throughout the accounting period.

Fusion General Ledger
The Fusion Accounting Hub leverages the Fusion General Ledger. The Fusion General Ledger combines traditional general ledger functionality with Oracle Essbase functionality, which is seamlessly embedded within the Fusion General Ledger. For example, at the time users create their chart of accounts, the Oracle Essbase cube is created. If a cost center is added or a date-effective hierarchy is modified, Fusion General Ledger automatically creates or modifies the corresponding cube hierarchy in Essbase. Then as transactions or journals are posted, Fusion General Ledger automatically updates the multi-dimensional balances to ensure that balances are always in sync and up-to-the-minute accurate. Unlike a data warehouse, no batch programs need to be run to populate the multi-dimensional balances; it is all happening in real-time whenever a journal or transaction is posted.
Fusion General Ledger also pre-aggregates balances at every possible summarization level across each dimension of the chart of accounts and accounting periods for multiple date-effective hierarchies. The result: extremely fast reports and queries. Reports and analytics can be refreshed on-the-fly without having to run a process or program because the balances have already been pre-calculated. Multi-dimensional analysis is also instantaneous. Users can quickly slice and dice data across dimensions and drill up, down, and sideways on any parent level.

Oracle Fusion Functional Setup Manager.

Oracle Fusion Functional Setup Manager is an exciting new feature in Oracle Fusion Applications that provides a single user interface for performing all tasks related to application setup. This overview provides an introduction to Oracle Fusion Functional Setup Manager and explains the concepts, benefits, and how it works for an application implementer.

• Single entry point for setting up applications

• Built-in decision tree for fine-tuning your configuration

• Easy export and import of setup data

• Pre-seeded setup templates and guided task flows

• Built-in collaboration and reporting

• Full extensibility for creating custom setup objects

• Rapid start with predefined configuration packages


This is one of my favorite fusion features. With composers Oracle let the end-users do several things at  run-time, otherwise its impossible to do so with apps. Oracle Fusion applications provide content and functionality to users that enable them to complete their tasks (such as , learning about a product or service, keeping up with sales data, or submitting an order) as easily and efficiently as possible. Some key capabilities includes

•Let you change a page layout.

•Add task flows, portlets, documents, layout components, and other objects to a page.

•Provide values for the properties associated with pages and the objects they contain.

•Add a Data composer and make it available for users based on certain criteria.

•Let end users embed training materials videos to any screens and have it relay it to other users.

•Change process flow based on GUI.

•Pre-create, create dashboard reports and make it available to a group or to the entire organization.

Since you are here to read on Fusion, I try not to disappoint you with additional gobbledygook 🙂

 Fusion Financials: Streamline close management

• A complete performance and financial management cycle

• Proactive, intelligent monitoring of accounts and close statuses

• Integrated operational, statutory, and analytical financial reporting

Fusion PPM: Core PPM Functions& PPM Analytics

• Core PPM Business Processes & Calculations

• Integration APIs for Scheduling, Labor, and Materials for Primavera and Other

Fusion Product Portfolio Management: Project collaboration that makes teamwork happen

• Real-time project performance tracking

• Identifies missing or late critical performance information

• Instant collaboration between Project Team Members

Fusion Procurement: Bringing a consumer centric process to the enterprise user

• Simplifies the order process

• Provides Interactive Assistance

• Delivers Real-Time Performance Information

Fusion Supply Chain Management: Orchestrating order capture and fulfillment now

• Aggregate critical supply information from disparate systems

• Automatically apply sourcing and availability rules

• Proactively manage orders avoiding exceptions before they occur

 Fusion Human Capital Management: Evaluating Talent as a team

• Rapidly identify candidates

• Make informed decisions regarding talent recommendations

• Immediately take action on the recommendations

Key MOS documents:

Oracle Fusion Applications Release Notes, 11g Release 1 ( [ID 1355561.1]

Fusion Applications – Product Information Center [ID 100.1]

Oracle E-Business Suite and the Upgrade to Oracle Fusion Applications [ID 374704.1]

Fusion Accounting Hub in a Coexistence Scenario [ID 1275111.1]

As always, thanks to Oracle Fusion team for providing some vital content here.

Should you have any questions, please let me know. I will do my best to respond to you in timely manner.

Happy Fusioning 🙂

%d bloggers like this: