Oracle Reports, Oracle E-Business Suite, Oracle Applications, Oracle Database Schemas
The Oracle E-Business Suite, also known as Oracle Applications or Oracle Financials, has close to 25,100 tables and 33,000 views on its database. One of the main issues for Oracle E-Business suite reports development is to find the right data in so many available objects. In this article we are going to discuss how Oracle has organized its database to make developers´ life easier.
A database schema determines the ownership of the products´ database objects. Oracle has separated each product of Oracle E-Business suite in one schema. For example, the module Bill of Materials has a code id ‘BOM’ and a named schema with the same name as its code id, in this case BOM. Each Oracle E-Business suite module has a default Oracle database user id, with the product abbreviation as name of the database schema. There is one “main schema”, called APPS, with a user also called APPS, which has privileges to access most of the objects from any schema.
The APPS schema owns procedures, triggers, functions, packages, views and materialized views and has permission to access tables, indexes, sequences and constraints from other schemas (products). The APPS schema improves the reliability over Oracle E-Business database and reduces the time needed for installation, upgrade, and patching by eliminating the need for cross-product grants.
Once APPS user has permissions to access all the objects that are part of Oracle E-Business Suite, developers should be connected to the database as APPS user (in a test instance) to produce their reports. Oracle also has helped developers by creating standards on database objects naming. All objects names starts with the abbreviation of the product. For example, the purchasing product has ‘PO’ as its abbreviation and all Purchasing objects start with ‘PO_’.
Finding the data that you need in more than 50,000 objects is not an easy job. Knowing the naming standards of the objects and how they are storage facilitates developers’ life. Oracle has also made available an Electronic Technical Reference Manual that has information about all the objects in all schemas. It can be accessed on line at http://etrm.oracle.com (metalink access needed) and it provides a very good help to find the data that you need. Our company is specialized on Oracle E-Business suite customizations; if you need service in this area we will be glad to help.