These are the some of questions which was asked by loyal reader in last couple of months.

• where I get the information about API which is suitable for a specific business process?
• I need to know full description of an API?
• I need to understand full description of each parameter?
• Where I can get the technical information so that I can able to use these APIs?
• I need to know what other interface mechanisms are available for Integration with Oracle EBS Suite?
• How do I access the Oracle Integration Respository Screen Which Exposes Web Services , API's etc?
• Could you please explain what the Oracle Integration Repository is used for?
• Could you please guide me how to access irep...I see references in several documents, not clear as to how to access the screen?

So this is small but powerful post will help you get the answer of these question.

IREP ....Much about Nothing

Think of it as a knowledge base of all integration points for the e-business suite.

So...that mean the Oracle iRep(Integration Repository) is a compilation of information about the different service endpoints exposed by the Oracle E-business Suite of applications.

This provides you a complete catalog of Oracle E-Business Suite’s business service interfaces. You consider this as powerful tool that helps users to discover and deploy the appropriate business interface for integration with any system application .

There is no additional cost you need to pay to get this add-on tool. This is shipped as part of the E-Business Suite Bundle. As the instance is patched, the repository is automatically updated with content appropriate.

The key advantages of using Irep by Implementation consultants are..

• If you want to know how to get data in or out of the suite, you can go search inside it. Any customized modules/classes and functions written can also be published to integrators through the repository.
• You will get assurance from Oracle that you are using supported public interfaces and API's

How should you access IREP ?

Choice is yours , use any one of these options:

1) From E-business Suite, you need to add a Responsibility : Integration Repository
2) You can directly use Oracle's hosted url: http://irep.oracle.com/

What you can access in irep?

• PL/SQL Procedures and Functions :A business interface can be based on a PL/SQL package from which you invoke procedures and functions appropriate to a defined integration goal.
• Open Interface Tables :An open interface consists of the interface tables to store data from external sources and concurrent programs, to validate and apply this data into the Oracle Applications base tables. All open interfaces are implemented using concurrent programs.
• Interface Views :Interface views are database objects that make data from Oracle Applications products available for selection and used by destination applications.
• Concurrent Programs
• Java Methods :A business interface can be based on a Java class from which you invoke methods
• Web Services : Both Service Bean and XML Gateway message maps can be exposed as Web services, which are defined with Web Services Description Language (WSDL) content appropriate to the interface type.
• XML Gateway Maps :Oracle XML Gateway is a set of services that allows easy integration with Oracle Applications to support XML messaging.
• eCommerce Gateway Messages :Oracle e-Commerce Gateway is a file-based integration layer between Oracle Applications and any other external application.

You can get the details of these interfaces in these documents, like Service Data Objects ,XML Schema ,DTD ,Interface Tables ,Views

Accessing Oracle Integration Repository

Those who are still using 11i they can use Oracle url for accessing irep. Accessing through appropriate registration, you can use Oracle's hosted instance of Integration Repository [http://irep.oracle.com /]

Those who are using R12 , take a note, these are shipped with product therefore to access these you have to add a separate responsibility to acess irep.So Under sys admin Navigator menu,you can add Integration Repository responsibility.

Once responsibility is given then you can click the Integration Repository link that appears.

How Should I use irep/how irep Works for me?

There are 2 ways to use the Integration Repository.

• Either you can use through Navigation through the catalog
• or Use of the search page

1. IREP: Catalog Use:On the welcome page you can see on the left the tree of the catalog you can navigate through.
   
2. IREP: Search Page :Clicking on search button will drive you to the research page.You can specify criteria, helped by LOV for some of them.
   

Step by Step : Accessing Oracle Integration Repository

What you have to do minimum is , you can invoke the repository like any other Oracle E-Business Suite application. From the Navigator menu, select the Integration Repository responsibility, then click the Integration Repository link that appears.

Oracle Integration Repository has two main user navigation modes:

• Browse ..this is defualt options for you.
• And the second is Search

Let’s understand How it works ?

First and foremost you need to understand the "Business entities"..which are defined as objects that either perform business activities or have business activities performed on them .

Sales orders, employees, purchase orders, invoice, Payment, customers, and receipts are all examples of business entities.

An interface can be used by multiple business entities, and a business entity can be accessed using multiple interfaces.

Therefore if you are navigating through Integration Repository in that case you can browse directly to an appropriate list of interfaces if you know which product family and product you want to integrate with.

1)The Product Family view is organized as follows: Product Family > Product > Business Entity
For example, Financials > Payables > Payables Invoice.

2) The Interface Type view is organized as follows: Interface Type > Product Family > Product
For example, Web Service > Financials > Cash Management.

the business interface information in Oracle Integration Repository is organized for browsing and searching by interface type, based on the integration technology used. Use this view to see all of the interfaces available for a particular product that use a particular interface type.XML Gateway and Web service-based interfaces conform to various industry standards .

3)The Standard view is organized as follows: Standard and Version > Product Family > Product.
For example, OAG7.2 > Financials > Payables > Process Invoice.

Use this view to browse for a product’s XML Gateway maps and Web services belonging to the specified standard; for example, W3C or OAG 7.2.

Oracle Integration Repository user navigation modes: Searching for an Interface

You can click the Search button anywhere in Oracle Integration Repository to access the main Search page or you can search for interfaces with any combination of the following criteria. This is screen snapshots which you can use.

you can also click Show More Search Options to include any of the following additional criteria in your search:

• Category and Category Value: Used to qualify product-specific features
  • Scope
    • Public - these interfaces can be used by anyone.
    • Internal To Oracle - these interfaces are intended to be used only by Oracle’s product development teams.
    • Private To Application - these interfaces are intended to be used only by the owning product’s development team.
  • Standard: Based on the certain standard like OAG or RosettaNet
• Status
• Active
• Deprecated
• Obsolete
• Planned

How to view Interface Details

Very simple, if you able to find the details

The Interface Details are provided if you click on the Interface Name.

If you take the example of FND_GLOBAL, then you can find the details of a PL/SQL API are as follows:

1. Internal Name
2. Interface
3. Type (of Interface)
4. Business Entity
5. Status
6. Scope
7. Description
8. Signature
9. Parameters etc…

Using Export Feature

Once the list of the APIs is displayed, you can press an export button provides a csv format file: to establish a list of APIs

Therefore , Integration Repository is one of such powerful tool that must be used by any EBS implementor or inhouse IT departments to integrate with other application.

A typical ERP Deployment for any Global company typical consider as Global, Regional or Local Deployment , which can be best understood as:

• Global ERP -Corporations with common business areas that are seeking to centralize management and standardize business processes across geographies and divisions
• Regional ERP -Corporations that operate in group of regional businesses which need to meet unique requirements of particular markets and comply with statutory or legal requirements
• Local ERP -Corporations with significantly different business areas having significantly different business process requirements

Read the rest of this entry »

Read this:

• KNOW WHAT IS SUPPLY CHAIN MANAGEMENT (SCM)? : An Overview
• SCM Subsystem

This article is an easy guide for Techno-functional consultant to understand SCM from Implementation as well as Oracle Application product Prospective.Lets start the topic with some of high points of earlier post .

• As mention in earlier post a Supply Chain is a network of retailers, distributors, transporters, storage facilities and suppliers that participate in the sale, delivery and production of a particular product.
  • Make a note a supply chain is product specific, not company specific
• Supply chain management (SCM) is a systematic approach to manage the entire flow of information, materials, and services from raw material suppliers through factories and warehouses to the end customer.
• Moreover , SCM involves the flows of material, information and finance in a network consisting of customers,suppliers, manufacturers, and distributors.
• You should be clear with Flow that SCM can manage :
  • The flow of actual materials, the top middle bars
    • From suppliers : flows of raw materials, intermediate products, finished goods
    • Reverse material flows : returns, repairs, servicing, recycling, disposal
  • and the information flows
    • From suppliers : manufacturing capacity, delivery schedules, promotions they have going
    • Reverse flows : sales, orders, inventory, quality, promotions
  • And finally, there are financial flows:
    • From suppliers: Credits, consignment, payment terms, invoice
    • Reverse Flows : payments, consignment
• Supply Chain Management is the management of the entire value-added chain, from the supplier to manufacturer right through to the retailer and the final customer.
• SCM has three primary goals: Reduce inventory, increase the transaction speed by exchanging data in real-time, and increase sales by implementing customer requirements more efficiently
• The need for SCM is because effective Supply Chain Mgt. is the next logical step towards increased profits and market share.
• Supply Chain Management (SCM) in line manager prospective is "let's-keep-things-moving-efficiently".

Read the rest of this entry »

This was a reader question : What are major reason, why companies undertake ERP in there business Line?

Answering to this question is very subjective matter and if you ask to ERP implementor, they can figure out n number of reason , but outmost these are very common:

1. A need to replace Outdated Business application
   • As we required Common processes and procedures
   • As need common shared data
   • As Need common reporting
2. A need to consolidate IT Platform
   • need to replace legacy system
   • Need to reduce the IT cost(will see this in another post , how this affected)
   • Need to connect with emerging technology gateway.

In simple words, these are major five reason why companies undertake ERP.

Integrated Financial Information

Integrated supply Chain Information(Customer Order Information)

Standardize and speed up manufacturing operation

Reduce Inventory and decrease Procurement Cycle

Standardize HR Information

Suggested Reading

• Familiarize yourself with Oracle application World
• What is CEMLI’s Framework?
• What is Oracle’s Application Implementation Methodology (AIM)

Do you know: How many different regulatory regimes of different countries handled by EBS?

The EBS Financials applications include 56 country localizations* to deal with individual regularity requirements. Not only in addition to this due to the flexibility of the applications these are incorporate reporting/duality requirements within GL structures but also FA (Fixed Assets) can manage multiple accounting treatments.

Moreover, the tax procedures are different for each country. From R12 onward there is separate module called E business Tax, which can handle different tax procedures satisfying the need of local or global regulatory need.

Do you Know : How ERP products analyzed in different ways depending upon the requirement, e.g. management accounts, statutory GAAP,FSA, local GAAP, US GAAP ..like wise..

Lets take about Oracle, we have concept of rollups and parents so whilst the underlying "leaf/Children" codes might exist on all the countries chart of accounts they are rolled up and totaled differently.This way the system supports unlimited structures and it enables different entities to be reported by any rollup.

Moreover, the dynamic insertion in GL Module supported feature ensure the analysis of new account codes is considered.

Do you Know : Where can we include Off Balance Sheet items in the ledger?

In SAP this can be through memo records which appear in the Balance Sheet.

In Oracle , COA can also include statistical(STAT) balances in EBS.

Do you know: How can a debtor account be reported in e.g. creditors in the balance sheet if it has a credit balance at the reporting date?(This question was asked by Controller who used SAP before moving to OracleApps)

In this case, if I am not wrong, EBS's GL would only contain total debtors , as we donot have unitary ledger concept to separate accounts, and not seen in the GL at least till 11i.Correct me if wrong.

Catering the need for countries specific legislative requirements ,it is shown as a creditor then a reversing journal would be posted between debtor control credit balance account and creditor control debit balances . Moreover, the fact is Oracle system does not allow you direct postings to control accounts.

On checking with SAP Functionality, what is found is during the period end closing procedure provision to segregate the debit balances of creditors and credit balance of debtors.

* You need to check with Oracle Documentation if added recently in past one month.

It was request from one of reader to provide some information for new organization Hierarchy model between two release of 11i and R12.Therefore invite you to run through the following diagram as below with some basics.

Read the rest of this entry »

Read this

• Managing Cash , EBS and SWIFT

Whenever the Bank Intergation is required , the very first term would come in mind is SWIFT , a very similar way as EDI sort of messaging servics that Financial sectors are using .So let explore what is SWIFT and how many of these can be potenially targetted for Integration .

What is a S.W.I.F.T?

S.W.I.F.T. (or SWIFT) stands for Society for Worldwide Interbank Financial Telecommunication. It is a non-profit organization comprised of member financial institutions. It was established in 1973 by European bankers who needed a more efficient and secure system for inter bank communications and transfer of funds and securities. Until then, all inter bank communications were by telephone, telex, courier, or mail.

Swift Messages

SWIFT messages are preset and referred to by category numbers called MT numbers. Through this network (a.k.a. SWIFTnet) information can be exchanged using special crafted messages known as Message-Types (MT).

Read the rest of this entry »

Previous Post:

• Are seeking auditing ability in EBS…use ‘AuditTrail’

Steps by step: Enabling audit trial

As per the below example the 'Define An application user' is a user table name for FND_USER,the same steps you can follow for your own tables.

Read the rest of this entry »

What is an AuditTrail?

An AuditTrail is one of functionality for retaining a history of changes to data. What ,who and when can be identified on a particular table or column if the functionality is enabled.

When you enter or update data in your forms, you change the database tables underlying those forms. An audit trail tracks which row in the database was updated at what time, and which user was logged in using the associated form(s).

If you are seeking auditing ability to track changes on a particular table of Oracle this post might helpful to you.

Enabling the Functionality of AuditTrail

You can turn AuditTrail on or off (Yes or No). Normally the default setting is No (Off). When you enter or update data in your forms, you change the database tables underlying the forms you see and use. AuditTrail tracks which rows in a database table(s) were updated at what time and which user was logged in using the form(s). Also..

• Several updates can be tracked, establishing a trail of audit data that documents the database table changes.
• AuditTrail is a feature enabled on a form-by-form basis by a developer using Oracle's Application Object Library.
• All the forms that support AuditTrail are referred to as an audit set. You should also note not all forms may be enabled to support AuditTrail.
• To enable or disable AuditTrail for a particular form, you need access to Oracle Application Object Library's Application Developer responsibility.
• Users cannot see nor change this profile option.
• This profile option is visible and updatable at the site and application levels.

 

The internal name for this profile option is AUDITTRAIL:ACTIVATE.

Setting Up AuditTrail(>11i )

You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

The steps for setting up AuditTrail include:

Yuu need to verify Select Privileges on SYS.DBA_TABLES

Have your database administrator grant SELECT privileges on SYS.DBA_TABLES to the APPLSYS account. Normally, this step would Normally taken care during the installation of Oracle.

Define Audit Groups

This is very very important.These are groups of tables and columns, where you do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables see at the end).

A given table can belong to more than one audit group. If so, the table is audited according to the highest "state" of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.

Navigation: Security -> AuditTrail -> Groups

Define Audit Installations

You choose the registered Oracle IDs at your site that you want to audit. This allows you to audit across multiple application installations. When a table is added to an audit group, auditing will automatically be enabled for all installations of the table for which audit is enabled.

Navigation: Security -> AuditTrail -> Install

Run the Audit Trail Update Tables Report to Enable Auditing

Your AuditTrail definitions (and auditing) do not take effect until you run the Audit Trail Update Tables Report. If you change any of your definitions later, you must rerun this program. You run the Audit Trail Update Tables Report from the standard submission (Submit Reports) form.

Audit Trail Update Tables Report

This program creates database triggers on the tables in your audit groups for your installations. It also creates shadow tables, one for each audited table, to contain the audit information. If you have changed your audit definitions or disabled auditing for an audit group, the program drops or modifies the auditing triggers and shadow tables appropriately.

The program also builds special views you can use to retrieve your audit data for reporting.

You can check SQL*Plus to see if the Shadow Tables have been created or not. Shadow Table name is the same 26 Characters of the Table being audited followed by a suffix of "_A" ,suffix of "_AI" for Insert Triggers, "_AU" for Update triggers , "_AD" for Delete Triggers,suffix of "_AIP" for Insert Procedures "_AUP" for Update Procedures and "_ADP" for Delete Procedures.

AuditTrail Limitations

These are limitation of AuditTrail:

• Your table should consist of maximum 240 columns
• You cann't use the column for audit trail whose data type is LONG, RAW, or LONG RAW
• Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically.
• Once you have added a column to an audit group, you cannot remove it.
• AuditTrail requires two database connections. If your operating platform does not automatically support two database connections
  (e.g., VMS or MPE/XL), then add the environment variable 'FDATDB=<database connect string>' to your environment file.
• Because the structure of the audited table may change between product versions, AuditTrail does NOT support upgrading existing shadow tables or audited data. Before an upgrade, you should archive the shadow tables and perform all necessary reporting on the audited data.
• Oracle recommended Disabling AuditTrail feature Prior to Upgrade to higher version.
• If your plan is to use some auditTrail on table which name is bit longer(> 26 characters), you can't achieve this. As there is Bug(#3872242
  ) reported by Oracle that restrict you from this, as AuditTrail Update Tables Errors out on audited tables that are > 26 characters.

Sometime back we found while enabling this on these tables.

• PO_REQUISITION_HEADERS_ALL
• PO_REQUISITION_LINES_ALL
• PO_REQ_DISTRIBUTIONS_ALL

What are the primary Auditing Tables?

• FND_AUDIT_COLUMNS
• FND_AUDIT_GROUPS
• FND_AUDIT_SCHEMAS
• FND_AUDIT_TABLES

Client Dilema : HOW TO ENABLE AUDITING AT "FORMS" AND "USER" LEVEL TOGETHER

Customer has set the profile "Sign-on: Audit Level" to FORMS to collect information about the forms sessions at a particular time .
By default the value for this profile was USER. Since he has set the value to Forms can he collect the User related information

Yes you can ...
If the profile "Sign-on: Audit Level" is set to FORMS then it will collect Form sessions information in addition to user session information.
So you will get information related to User session as well as forms session. There is no harm in setting this profile value to FORMS.

MOAC allows users of a single responsibility to perform task in multiple operating units without having to switch responsibilities.Out of Box functionality of MOAC, there is something called "Multi-Org Preferences".

What is "Multi-Org Preferences"?

Multi-Org Preferences allows you to control the list of operating units to which you have access.

Lets say you have around 20 OU's divided into three major Region (North America, Europe and APAC). Lets say within APAC region you are responsible for all 7 OU's within Region.

Read the rest of this entry »