Wide variety of reporting requirements varies from environment to environment and business to business.This Article is going to Focus on the some of reporting tools which is normally used in typical 11i environment.

Reporting Tool Functionalities

Data is totally useless until it does come in presentation layer, with proper security . Hence its is important to have such tool with great capability, and tools must have these features:

Reporting Tool overview with respect to EBS Suites

• Brio ONE
  The Brio ONE solution is specifically designed to enable everyone in the extended enterprise to fully realize the promise of e-business by delivering dynamic, reliable, easy access to real-time business information, regardless of where that information is stored or of the user’s location. The Brio ONE solution enables your organization to build and deliver business intelligence, enterprise reporting, self-service information exchange, and analytic applications to the broadest possible range of users in client/server, Web-based, and hosted application environments-all with unmatched ease of experience and scalability.
• Business Objects
  Streamlining operations, reducing costs, increasing revenues, improving customer satisfaction… These are just some of the measurable benefits that customers of Business Objects have realized directly by implementing e-business intelligence (e-BI) solutions from Business Objects.
  Whether they need to access prepared information via a wireless portal or perform sophisticated analysis on a networked PC, BusinessObjects can help you offer your employees, customers, partners, and suppliers a set of easy-to-use tools to turn information into knowledge into profit.
• NoetixViews
  NoetixViews enables super-users to develop ad hoc reports from Oracle Applications data. NoetixViews takes the information in your Oracle Applications database and repackages it in a set of user-friendly, plug-and-play business views that provide relevant information on a given business topic. With these views, users can query their data using common business terms and client/server query and reporting tools, without dealing with raw tables. With typical installations requiring less than one day of training, NoetixViews dramatically reduces specialized consulting costs and IT involvement.
• Oracle Discoverer
  Oracle Discoverer is an intuitive ad hoc reporting tool. It enables a developer or knowledgeable end user to create reports with impressive formatting and attractive exporting and publishing functionality. Oracle Discoverer empowers your user population by placing business and decision critical data at their fingertips.
• Business Intelligence System (BI)

Oracle BI tools is a set of tools to provide high level information for the managers (decision makers) to run their business. The information this tool provides helps managers to take the right decision with the daily data that is upload on their systems.

• Hyperion

Hyperion is a Business Intelligence (BI) and Business Performance Management (BPM ) Tool. Its a market leader in Financial, Operational and Strategic Planning. It is having applications for Planning, consolidation, scorecarding, reporting, dashboards, Analysis, Workspace, Master Data Management and Foundation.

• GL wand

Gl wand is a formula driven approach to reporting on Oracle General Ledger data using Microsoft productive tool like Excel.GL Wand will be used for monthly Balance Sheet reconciliations and account balance checks,i.e. Trial Balance. This tool is bit popular in UK.

For more information, adviced to look at the product company website for latest updated info.

1. Brio ONE http://www.brio.com
2. Business Objects http://www.businessobjects.com
3. NoetixViews - http://www.noetix.com
4. Information gathered from http://www.oracle.com
5. Hyperion - www.hyperion.com
6. Glwand - http://www.excel4apps.com

No, Not yet at least similar to sub ledger accounting as introduced in R12..lets hope this must be in future ..:)

Why Reporting is Must in ERP?

The more important is to understand the objective of an accounting system is to summarize transactional data into useful management reports that management can use to manage the business. Hence need for Reporting can’t be denied in any ERP accounting system. In oracle application also there are different ways to fulfill reporting requirement.

If we consider EBS as a transparent box, we can see there are number of reporting options available.The need can be changing time to time and business with cases. Typical reporting options can be best described as:

The reporting need can be classified as

• Financial Reporting
• Transactional Reporting
• Ad Hoc Reporting

These reporting does have there own limitation and uniqueness thus , on the top of technology it is important to understand when and where it is required.Broadly such reporting requirement can be categorize into:

• Oracle seeded reports
• End User Reports

Let’s take some of reporting options in Oracle application:

• XML Publisher: Since last couple of years, this become very popular reporting tool, and also learn to know that Oracle is pushing most of the reporting requirement into XML Publisher . The reason of having popular tool is because it is final outcome is integrated with office productive tool like word and excel, which fits for all kind of user without having much knowledge of technology. We can use XML Publisher to save reports to many different file types including XML, PDF, RTF, and Excel and to publish reports by printing, sending in an e-mail, or posting to web sites or portals. XML Publisher can be applied to both reports and documents.
• ADI Reports: This is one of most useful tool by the end user with the use of office productive tool like Excel. This provides support for creating and summarizing both input and output data in two-dimensional spreadsheets for further analysis.
• RXi Reports : This enable us to design the content and layout of your RXi reports. You can print the same report using different layouts, and you can control the data items included in your report. RXi lets you tailor your reports to meet statutory and other reporting requirements, and to create reports for your own internal use.
• SQL Extracts: This can used for adhoc basis. Your staff can create and run SQL extracts to obtain additional information from the database.
• Oracle Discoverer
  Oracle Discoverer is an intuitive tool for creating reports and performing on-line analysis . Users uses the EUL (End User Layer), a metadata definition can access and analyze data in a company’s database without having to understand difficult database concepts. Users can also view workbooks and integrate database output onto a web site and portal that can be easily customized to conform to a particular web site look and feel, or to build custom Discoverer applications for the web.
• Oracle Portal
  You can use Oracle Portal to create a portal Web interface, publish and manage information, access dynamic data, and customize the portal experience. You can design enterprise portals that are the single entry point to business applications, content,
  collaborative tools, and Web sites.
• FSG (Financial Statement Generator) Reports
  FSG is an inbuilt reporting tool in the General Ledger module using which a functional user can create reports from the front end. It extracts the General ledger balances of the account code combinations and displays the same as an output for the reports defined in FSG. Thus the functionality of FSG is restricted to the General Ledger module.
• Report Manager
  Report Manager is a secure point-in-time report repository offering centralized management and distribution of reports.
  Main Benefits can be summarized as:
  • Access business information from a central site using a standard browser
  • Ensure that reports are protected from unauthorized access
  • Store and distribute any type of report or file
  • Preview reports prior to mass distribution

These above are oracle inbuilt as well as oracle technology based reporting Options.The more important is that the use of above does not cost you any additional cost for licensee fee. Apart from this , there are also some third party tools can be used for reporting purpose. I have seen number of other tools used by various clients, would walk through some of them, in next post.

Couple of options are available in Oracle E Business, where you get use of mailing facility directly from Oracle E Business.

Many times there is similar kind of requirement wherein we need to send the output of a Concurrent Program as an attachment with an mail to a Distribution list, or just a notification by email. Lets explore some of the options :

Read the rest of this entry »

How does one can provide read only access to the oracle applications screens for a particular user?

We need to provide helpdesk and auditor read only access to the oracle applications screens. Is it possible to give read only access to the oracle applications screens?

Do you able to think any clue..

This is one of the question that was asked by a client finance super user while doing SAP migration to Oracle Apps.Oracle does work on responsibility level where SAP product does control at function leval.You can consider function level in the same way , that you are giving access to certain folder to another user in your network domain by write, read or both or same way chmod in Unix.

Lets take a supplier master screen, the same screen can be used for creating, maintaining as well as deleting the supplier.what would be case if you want to give access to a user not a delete any suppliers, then ???

..Custom.pll ..pretty simple approach right…,the good think is that it can be handled but bad thing is that the username needs to be hard coded
in the custom.pll.

So to avoid hard coding the userid in the custom.pll , the other approach should be consist of these steps to make a user with read only privilege:

1. Addding a new function from the System Administrator -> application > function, with the same form name as the original function and with the parameter as QUERY_ONLY=”YES”
2. Create a new menu and add the new function to it.
3. Create a new responsibility with the new menu.
4. Add the responsibility to the user who will have the read only access.

Similarly create and add the other function to the new menu with function parameter as QUERY_ONLY=”YES”

Is this good solutions,what about the change management guys..are they allow this way.. what you think..any comment .I hope DBA must have some other options.[]

Finally a note on user security

Q .Is security defined by user, user group or a combination? Can this be set by ledger/company/module or is it across the system?

In Oracle
Security is defined by user – menus are completely tailor able but can be reused so can use include/exclude functions.
In SAP
A user ID is linked to one or many user profiles which manages the authorizations within the SAP system. More than one user can be attached to a user profile. Ledger, company etc. are entities or authorization objects wherein we can specify that the user is authorized only for specified company codes.

Will you able to tell… who are in TCA? what is TCA ?? Why bank moved in TCA ? Why TCA is becoming a popular model now a days rather than architecture? Taking R12 into action we learn bank and supplier moved into this schema. So its time to recall and list who else in this:

1. Customer
2. Bank and Branch
3. Supplier
4. Student
5. Employee
6. Legal entity & intercompany

Mean a single model cater all the need…everything in one place….sound intresting…:) will take all this into action..

And this can be best represented similar to our solar system.

Will keep posted with some of case study with TCA …keep watching this space.

Hey did you notice there is one thing that keep changing since last 2 releases …the bank. We have seen there was once from pre 10.x to 11i when supplier bank separated from suppliers data and now its again in R12 when it become part of TCA.This time , because of changing business need and high demand of global partners working model. Not only your company is operating globally,your partner too operating global,then why not use them. In typical business cost model, if corporate office is using Citibank for payroll for USA operation then why not Citibank Singapore branch is used for payroll for Singapore if they are operating there. Sound bit low…why ..

As we aware the key message of R12 while release was .

• Think Globally - using business intelligence and analysis tools
• Work Globally - using the global capabilities of the applications
• Manage Globally - using the latest system architecture and middleware

so what , think globally and work globally is factor driving for the changes .This release have witness the great changes ever into the bank model. Now the bank accounts is attached to your legal entity level rather than Operating Unit in which current and existing versions Offers. This makes bank with strong capability to pay across operating units. More over its important to understand banks accounts can be shared by applications and can be designed for use by Payables, Receivables and Payroll.

What is new in R12 for Bank

These changes make easier and more reliable by

• Single access point
• Single Legal Entity ownership
• Usage rights granted to one or more Organizations
  • Reconciliation option defined at Bank Account level
  • More flexibility and control

Take a looks of some of the releases

Release 10.6 character/10.7 NCA to 11i
How 10.x supplier banks are mapped to 11i

Release 11i

These are tables which hold the bank details irrespective of supplier or internal banks.

Comparing the 11i Vs R12

If we compare the bank with 11i vs R12, we can notice the bank was utilized into three different places , finance ,payroll and treasury, which requires altogether a different setup. It was one of the big issues with integration aspect, as significant problem was recognized once the Expense management and payroll uses same bank for the respective person.

There was a common question/confusion between the Integration Existence between Bank Data in Accounts Payable and Bank Data In Payroll ?

As discussed above , you know most of release of 11i family of oracle Application does not have integration between HR and AP for bank account data.

We have notice in 11i there was functionality in which Payables in which we will create an employee type supplier from HR data and it will contain name and address info but not bank information. The reason for this is that HR/Payroll does not store the bank information in a standard way that makes the integration possible.

So now r12 this was well taken care and integration is built. There are plans under way for all bank account data models in the various products to be consolidated in the new TCA architecture. The Cash Management team is working on this project. Payables and HR/Payroll are working so that the eventual idea will be that you can set up bank accounts in one place and then indicate the usage (pay, expense reimbursement, etc).

For understanding it is comparison between 11i and release 12 , where TCA community take cares of every things.

Release 12 , what is new than

Bank Accounts will be stored in a new table called CE_BANK_ACCOUNTS and will be located at a Bank Branch.

The new table which hold the bank information are as:

1. CE_BANK_ACCOUNT:stores bank account attributes
2. CE_BANK_ACCT_USES_ALL : This stores the bank account use attributes specific to Operating Unit (AR, AP) and Legal Entity (Treasury).
3. CE_GL_ACCOUNTS_CCID :The accounting data pertaining to the bank account use will be stored in the table.

This new data model allows the bank and bank branch entities to be defined separately allowing users to establish a hierarchical relationship between them.

Missing link between Supplier And Supplier Banks?

You should know

• The link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id.
• The link between PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.
• When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS
• When a bank Is Created, the banking information will be stored in

IBY_EXT_BANK_ACCOUNTS IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id

• When the Bank is assigned to Vendors then it will be updated in HZ_CODE_ASSIGNMENTS.

HZ_CODE_ASSIGNMENTS.owner_table_id = IBY_EXT_BANK_ACCOUNTS.branch_id.

Internal Bank Accounts & Supplier and Customer Bank Accounts in R12

Internal Bank Accounts
In Release 12, each internal bank account is assigned to a Legal Entity. Any or all operating units associated with that legal entity are permitted to use that bank account.
Supplier and Customer Bank Accounts
In Release 12 provides a centralized repository for suppliers’ and customers’ bank account and credit card information. All data is stored in one, secure place, providing shared service centers and collection departments consistent information that they need.

Any comment guys..

In Oracle supplier can be entered from three different place.These are the Setup required to perform for a suppliers.

• Supplier Accounts
• Taxes
• Payments
• Term Date Basis
• Due Date Basis
• Invoice Currency
• Holds

In Oracle these are the important features through the screen:

• Enter Suppliers
• Enter employees as Suppliers
• Merge duplicate Suppliers

Supplier Data Entry

Other Details

• Classification Information (Significant for type and employee name)
• Parent subsidiary relationship
• Supplier bank information
• Appropriate GL accounts information
• Record tax information
• Hold conditions to supplier site (rather than on individual invoices)

and these are the optional field

• Tax payer ID
• VAT registration number
• Inactive date (to prevent invoice/PO entry after a certain date)

Important Table that holds supplier

• PO_VENDORS : this hold a suppliers information

Some important columns:

• • SEGMENT1 – SYSTEM GENERATED OR MANUALLY ENTERED NUMBER FOR SUPPLIER (PO_UNIQUE_IDENTIFIER_CONTROL)
  • VENDOR_ID – SYSTEM GENERATED UNIQUE NUMBER
  • CREDIT STATUS –GOOD/POOR
  • ORGANIZATION_TYPE_LOOKUP_CODE
  • VENDOR_TYPE_LOOKUP_CODE – VENDOR/EMPLOYEE (EMPLOYEE_ID)
  • ONE_TIME_FLAG
• PO_VENDOR_SITES_ALL : This hold corresponding site information Contains Details of the site like Full Address/Work of site
  and its link with vendor_id.Important columns includes:
  • PURCHASING_SITE_FLAG, VENDOR_SITE_CODE, RFQ_ONLY_SITE_FLAG, PAY_SITE_FLAG,HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG
• PO_VENDOR_CONTACTS : This hold supplier contact information for respective sites.

How they connected

These are typically connected as:

Take a note Segment1 is holding suppliers Number.

Quick query for new babies

select * from po_vendors
where segment1 = ‘1001‘;

select *
from po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = ‘1001′
and pov.vendor_id = pvs.vendor_id;

select *
from po_vendor_contacts pvc,
po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = ‘1001′
and pov.vendor_id = pvs.vendor_id
and pvs.VENDOR_SITE_ID = pvc.vendor_site_id;

Supplier Interface

Yes, that was one of the enhancement in oracle 11.5.10 release. Prior to that version, there was no interface or Public exist for supplliers, thus it was a tricky to handle supplier. As we have already seen AP availability of AP some time back.

If you are having 11.5.10+ version,and if you have requirement to create supplier from your legacy data than use Supplier Interface, life will be easier. Is n’t.

How we start

This consist in 2 steps process:

Step 1. Populate the new interface tables with data. This can be done via SQL*Loader or typically used to load data in a table.

Step 2. Run the Supplier Open Interface Request Set (FNDRSSUB1703)

Alternatively, run the individual import programs to load one table at a time

Some important notes for suppliers Interface

• When you load the data it should be ‘NEW’ initially. After the Supplier Sites Open Interface is run the status gets changed to PROCESSED’/'REJECTED’.

The entire flow can be shown as:

A Note with R12 for Suppliers

As we know in R12 Supplier is part of TCA , thus the link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id. The link between
PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.

When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS.

Some time back in last post we have already seen the definition of interfaces and there types.Just to summarize here are the high points for interface :

• Import data from any environment , including own system or old system or from data collection device or external system or any others in the easier way possible,
• This consist of review the result of import run which as whole include verification ,validation, review, reimport and error identification

Guess the next question in your mind will be what are the options available for interfacing with oracle ERP or any other ERP? The way integration and interfacing works in typical commercial application have almost similar in nature. Lets emphasize some options that technology offer in Oracle application. But the option will be drived on the basis of nature of system and process how its works , moreover Cost is yet another driving factors. From the integration and interfacing these important factors are :

Is this really driving the integration decisions for choice.? …The answer is YES, on the top of other factors like cost, support etc…etc.But how…lets understand what is really means and how they really affect in :

• Frequency - The frequency is important in term of running the interface. Many times a interface need to run during month end processing, many times it runs in nightly basis as most of airlines industry updations happen in night only, or may be weekly only.
• Batch - It should be real time, one time in day or one in batch …somehow it is linked with Frequency.
• Volume - This is yet another important to understand what volume data need to interface. This is very very important which is affect how technology is helpful in handling such a big volume.
• Technology : This is based out of all above three factors ,
• Accessing data :What access is there to the data? Typically points considered here are like;
  • One way sync (Read Only)
  • vs. bi-directional (Read Write)
  • vs. access in place

Under these set of schema , a developer and designer have to take a call which options should he/she can go for interfacing?? Lets take some of the options :

Before exploring the some options, note that integration is broadly categorize into two types:

• Data Integration
• Application Integration

We are not going to discuss details her, lets take some of the options as for integration and interfacing in EBS.

Options 1: Open Interface Table (OIT)?

This is the the most standard way for doing interfacing in oracle application till day.This consist of developed interface table and programs. The underline interface table allow you to load data and then call concurrent program if any else use the standard AOL API’s and have a custom concurrent program. The standard/custom concurrent program will take data from the interface table validate it and the data into application base products table.

This kind of Open Interface Table satisfy in most of time, as design and development does not requires much efforts.In oracle each product has number of OIT available to cater most of interfacing/integration need.

Options 2 : Application Programming Interface(API’s)

Many time we need a tighter integration to oracle application that can be provided with interface table. In those cases, Oracle Application API’s are indeed a good Options .APIs allow for tight integration into oracle application product. There are also 3rd party products that need to bolt-on into oracle apps with its integration capability. A typical API’s works as figure in the right.

Options 3 : Enterprise Application Integration Tools (EAI) /Middleware Tools

When there is no standard Oracle process (no interface tables) as well no API’s to import data.In that case, you have to write a program that inserts data directly into the Oracle Base Tables.Though this is not recommended by oracle as it any changes from oracle standard functionality may malfunction during patch or upgrade version. So there is another options where we can use some EAI or middle ware tool . EAI/Middle ware tools typically works as adaptor or connector which then take care of any insert /update in oracle base table without any risk.There are number of adapter available to integrate with oracle application.

These tools normally provide robust visual development GUI as well as have strong functionality to provide integration monitoring and alerting tools. I personally know Taviz( How good this is..i really like this ..sorry guys i am not sells person but Still i will recommode). Normally these tools are higher-quality than a options discussed in OIT or API’s.

There are EAI vendors which has capability with interface and integrate with oracle application. Some of them are oracle cerified partners.

• Cast Iron
• WebMethods
• Tibco
• Informatica
• Blue Wolf
• Taviz(formerly SmartDB)
• Crossroads
• SeeBeyond (formerly STC)
• Vitria

Normally most of EAI products provide a tool called “Studio” which uses a graphical environment for you to design integration process flows called orchestrations (integrations). Integrations are deployed to a provided ‘Application Router’, which is an integration appliance pre-configured before it is installed at the customer’s site.Provides native ‘Connector’ to Oracle E-Business Suite.

Options 4 : EDI

EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO’s, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.This is also a good options. More details can be found in my last post.1.2

A bit on other options

Options 5 : BPEL Process Manager

• BPEL stands for Business Process Execution Language (This was developed by IBM, BEA, and MSFT)
• Development tools and server acquired with Oracle’s purchase of Collaxa Inc.
• Requires extra licenses beyond Oracle application licenses
• Uses industry standards, web services, and business process design to orchestrate integration development
• Marketed very heavily by Oracle as their current and future integration standard

It is typically as similar to EAI but i personally found bit simpler. It Provides “Dashboard” for server maintenance and monitoring and moreover this is exposes Oracle APIs as web services. Only drawback is extra license and relatively higher learning curve may lead to higher initial costs

Options 6 : other Oracle Technology

By means of making a either PL/sql or stored procedure or hybrid process.

• PL / SQL
  • Oracle Corporation’s proprietary server-based procedural extension to the SQL database language
  • Relatively sophisticated procedural programming language
  • Compiled to DB; allows for syntax checking and high-performance DB integration
  • Myriad of purposes in Oracle applications: application logic, integration logic, exception handling
  • Exposes an API layer to Oracle application logic
• Stored Procedures
  • Can be implemented as server-side Java
  • Capable of calling PL/SQL using JDBC; capable of being called by PL/SQL
  • Higher computational performance than PL/SQL
  • Allows for use of an industry standard technology instead of proprietary PL/SQL
  • Often coupled with PL/SQL as a hybrid approach to Oracle application integration
• Oracle Advanced Queuing (AQ)
  • Database Integrated Messaging
  • Integrated data, message, and message history
  • Recoverability(any point in time recovery)
  • Auditing,Tracking, Non-repudiation
  • Business Intelligence on message store
  • High Performance
  • Integrated with MQSeries via Oracle Messaging Gateway

Will take some deep drive for options 4 and options 5 in some other post.

I am bit busy in my project work since last 2 month and will be there till Oct first week, in fact I’ve been quietly studying this blog’s performance, vision and roadmap. Last few months I am getting lot of spam mails that really a pain to sort out.There are many things that need fixing (both technical and otherwise), that have piled over the last few months. For example:

1. Optimize load time - Last time I checked, this blog’s load time stands at a whopping 35 seconds!
2. Easier navigation - While current one isn’t all bad, but i have ideas for improving navigation.
3. Browser compatibility - Many readers reported they are facing some problem in IE7 and Safari.
4. Clear purpose - I want to further focus this blog and I want new readers to get at a glance what this blog’s about and where to find valuable content.
5. Readers easiness
6. Control spam : This is major one , in my action list.I am really fed up with all junk mails that I am getting.

….Points which I am going to cover to make ;

On top , I will follow KISS ……”Keep It Straight and Simple” as it is now..

…and Finally , thanks to all my reader.

First of all, a big thanks to all my lovely readers/visitor of this little blog, who keeps me motivated to share some information.and Yes,without them, I wouldn’t have the motivation to plan and do all of this.

Sharing through this blog for apps community,I’m really proud of: I’ve reached out to a fresh new reader of this blog and thanked him for having the interest to follow my blog. Then May I request what you thought about this blog, what you liked best and least. I was really amazed that he took the time and effort to reply, thoroughly describing his experience with the blog so far.

So now I want to generalize this concept:

In the comments, write at least one thing you like about this blog and , one thing you’d like to see changed. Your comments and suggestions will really helps me in to have a another action items in fixing minor technology stuff of this blog.