Oracle Business Events and step by step process to subscribe to a Business Event

The concept of Business Events in the context of EBS plays a critcal role in enabling event-driven integration with other systems outside the application.

In addition to that, the Business Events in E-Business Suite in particular allow for an exceptionally effective way of decoupling the standard product functionality, available out of the box, from client customizations that seek to adapt the standard product to meet customer-specific business needs. In other words, Oracle Applications developers should consider using Business Events whenever possible when configuring and customizing the standard products.

 What are Business Events

“A business event is an occurrence in an internet or intranet application or program that might be significant to other objects in a system or to external agents.”

For example, the creation of a purchase order is an example of a business event in a purchasing application

 Oracle Business Events =>Architecture

The Oracle Workflow Business Event System is an application service that leverages the Oracle Advanced Queuing (AQ) infrastructure to communicate business events between systems.

The Business Event System consists of the Event Manager and workflow process event activities

• Is available with both standalone and E-Business Suite Workflow
• Provides event driven processing
• Allows Application modules and external systems to raise events
• Facilitates Oracle Application modules and external system to subscribe to these events
• Subscriptions can be synchronous or asynchronous

o you know,

11i10 E-Business Suite is preconfigured with 915 Business Events

Each Business Event represents a ready to use Integration or extension point

915 Outbound Integration/extension points

915 Inbound Integration/extension points

Integration points centered around the major E-Business Suite flows like p2p, o2c etc

dgreybarrowComponent Architecture

Typically Business events Component can be best understood as:

Transactional Diagram of business Events can be best understood as:

Below is Architectural Diagram for Outbound Business Events , typical flow consist of

• Creates deferred subscription to the selected
• Deferred subscription transfers the event to the customer queue (WF_BPEL_Q)
• Unique consumer is created automatically

“A business event is an occurrence in an internet or intranet application or program that might be significant to other objects in a system or to external agents.”

For example, the creation of a purchase order is an example of a business event in a purchasing application

Event Manager for Oracle Applications

The Oracle Workflow Event Manager lets you register interesting business events that may occur in your applications, the systems among which events will be communicated, named communication agents within those systems, and subscriptions indicating that an event is significant to a particular system. The Event Manager also performs subscribtion processing when events occur.

 Subscriptions for Business Events

• Events that trigger custom code
• Events that send information to Workflow
• Events that send information to other queues or systems

 where you can Uses of Business Events

• System integration messaging hubs
• Distributed applications messaging
• Message-based system integration
• Business-event based workflow processes
• Non-invasive customization of packaged applications

 PLSQL vs Java Business Event System

Oracle Workflow provides Business Event System implementation within the database (PLSQL) and in the middle tier (Java).

The implementation is exactly the same in terms of the event subscription processing in both these layers but the only difference is how the Developer wants to leverage Business Event System's capabilities for event processing requirements.

With the availability of Business Event System implementation in PLSQL and Java, different subscription processing scenarios can be achieved.

 How to Proceed if Business events are required to use

• Design your Business Event/s
• Define your event

 Setting Up the Business Event System [Adopted workflow user documentation]

To set up the Business Event System and enable message propagation, perform the following steps:

1. If you want to communicate business events between the local system and external systems, create database links to those external systems.
2. If you want to use custom queues for propagating events, set up your queues.
3. Check the Business Event System setup parameters.
4. Schedule listeners for local inbound agents.
5. Schedule propagation for local outbound agents.
6. If you are using the version of Oracle Workflow embedded in Oracle Applications, synchronize event and subscription license statuses with product license statuses.
7. Ensure that the WF_CONTROL queue is periodically cleaned up to remove inactive subscribers.

---------------------------------------------------------------------------------------

Business Event

Business Event is an occurrence of a business activity which has some significance. For example, the activity of creating a purchase order (PO) is a business event, like wise approving PO, receiving goods against a PO, matching a PO receipt with invoice is a Business Event.

Subscription

Subscription is an activity to be performed on occurrence of a Business Event.
Eg:- If you would like to send an email notification to some set of users when a PO receipt is created then you can subscribe to PO Receipt related Business Event and trigger a workflow notification from the subscription to send email.

How is Business Event Raised?

Usually seeded Business Events are raise by workflows or Forms through a PL/SQL code.

Oracle not only provides an option to create custom subscriptions to seeded Business Event s but also provides a flexibility to create a complete custom Business Event.

Workflow Engine vs Business Event System

Oracle Workflow has two major execution engines.

• Workflow Engine
• Workflow Business Event System

Here is a simple comparison of what they process and their associated background components.

Workflow Engine	Workflow Business Event System
Executes workflow processes created using Windows based Workflow Builder client	Executes subscriptions to business events registered using Event Manager in Workflow Administrator Web Applications Responsibility
Entry point foreground APIs are WF_ENGINE.CreateProcess and WF_ENGINE.StartProcess	Entry point foreground API is WF_EVENT.Raise
Execution deferred to background by enqueuing message to AQ WF_DEFERRED_QUEUE_M	Execution deferred to background by enqueuing message to AQ WF_DEFERRED
Entry point background API is WF_ENGINE.Background	Entry point background API is WF_EVENT.Listen
AQ Payload is SYSTEM.WF_PAYLOAD_T	AQ Payload is WF_EVENT_T
Background processing is done by Concurrent Program - FNDWFBG (Workflow Background Engine)	Background processing is done by GSC Component - Workflow Deferred Agent Listener
Background Engine is submitted as recurring concurrent request from SRS form or Workflow Manager in OAM	Agent Listener is a service component managed through Workflow Manager in OAM

• When troubleshooting issues with Business Event System, users verify that the Workflow Background Engine is running.
• When troubleshooting deferred workflow processes, users verify that the Workflow Deferred Agent Listener is running.

Steps to Subscribe to a Business Event

In this article I will show you how to subscribe to PO Receipt standard business event (oracle.apps.po.rcv.rcvtxn). Our subscription would just insert the seeded business event details into a custom temporary table.

Navigate to “Workflow Administrator Web (New)” responsibility –> Administrator Workflow –> Business Events

Search for a business event oracle.apps.po.rcv.rcvtxn

Click on Subscription icon

and then click on Create Subscription button

In Create Subscription page enter the following details and save the page:
– System: < choose your system name from LOV >
– Phase: 101 (enter some number greater than 100)
– Status: Enabled
– Rule Data: Message
– Action Type: Custom
– On Error: Stop and Rollback
– PL/SQL Rule Function: xx_be_test_pkg.xx_insert (we will create this package and procedure in next step)
– Priority: Normal
– Owner Name: (enter your custom application short name)
– Owner Tag: (enter your custom application short name)

Execution Condition: PHASE

If you want to execute the business event subscription on sync with workflow activity then you need to select phase below 100 OR else enter phase more than 100 if you want to execute business event subscription after completion of workflow business activity i.e, asynchronously.

Package Specification

-- CREATE OR REPLACE PACKAGE xx_be_test_pkg AS FUNCTION xx_insert (p_subscription_guid IN RAW, p_event IN OUT wf_event_t) RETURN VARCHAR2; END xx_be_test_pkg; / --

Package Body

CREATE OR REPLACE PACKAGE BODY xx_be_test_pkg AS FUNCTION xx_insert (p_subscription_guid IN RAW, p_event IN OUT wf_event_t) RETURN VARCHAR2 IS -- l_param_list wf_parameter_list_t; l_param_name VARCHAR2 (240); l_param_value VARCHAR2 (2000); l_event_name VARCHAR2 (2000); l_event_key VARCHAR2 (2000); l_event_data VARCHAR2 (4000); -- BEGIN -- l_param_list := p_event.getparameterlist; l_event_name := p_event.geteventname (); l_event_key := p_event.geteventkey (); l_event_data := p_event.geteventdata ();   -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES ('EVENT NAME: ' || l_event_name ); -- -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES ('EVENT KEY: ' || l_event_key ); -- -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES ('EVENT DATA: ' || l_event_data );   IF l_param_list IS NOT NULL THEN FOR i IN l_param_list.FIRST .. l_param_list.LAST LOOP -- l_param_name := l_param_list (i).getname; l_param_value := l_param_list (i).getvalue;   -- INSERT INTO xx_be_debug_log_tmp (text ) VALUES (l_param_name || ': ' || l_param_value );   COMMIT; -- END LOOP; END IF;   COMMIT; RETURN 'SUCCESS'; -- -- EXCEPTION WHEN OTHERS THEN -- --Provide context information that helps locate the source of an error. -- wf_core.CONTEXT (pkg_name => 'XX_BE_TEST_PKG', proc_name => 'XX_INSERT', arg1 => p_event.geteventname (), arg2 => p_event.geteventkey (), arg3 => p_subscription_guid ); -- --Retrieves error information from the error stack and sets it into the event message. -- wf_event.seterrorinfo (p_event => p_event, p_type => 'ERROR'); -- RETURN 'ERROR'; -- END xx_insert; END xx_be_test_pkg;

Compile the above package and Restart Workflow Agent Listener service as shown in the below screen shot

More about the package

When the Event Manager calls the rule function, it passes two parameters to the function and expects a return code when the function completes. The parameters are defined here:

p_subscription_ guid – The globally unique identifier for the subscription.
p_event – The event message with which we can access Event Key, Event Name, Event Data and Parameters. Every seeded business event has some parameters which we can access through p_event parameter.

The function must return one of the three status codes: SUCCESS or WARNING or ERROR.

Create PO Receipt Transaction to raise Business Event

Create a PO Receipt transaction so that the business event oracle.apps.po.rcv.rcvtxn will get raised the custom subscription we created will get executed.

After creating PO Receipt transaction wait for a minute and query for the custom table which we have mentioned in the package.

SELECT * FROM xx_be_debug_log_tmp;

Event Key

A string that uniquely identifies an instance of an event. Together, the event name, event key, and event data fully communicate what occurred in the event.

Event Data

A set of additional details describing an event. The event data can be structured as an XML document. Together, the event name, event key, and event data fully communicate what occurred in the event.

Event Message

A standard Workflow structure for communicating business events, defined by the datatype WF_EVENT_T. The event message contains the event data as well as several header properties, including the event name, event key, addressing attributes, and error information.

Event Activity

A business event modeled as an activity so that it can be included in a workflow process.

To know if the business event is fired or not query for WF_DEFERRED queue table if the subscription is using PL/SQL procedure or else use WF_JAVA_DEFERRED queue table if the subscription is using Java procedure.

SELECT substr(wfd.corrid,1,40) corrid, decode(wfd.state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(substr(wfd.state,1,12))) State, COUNT(*) COUNT FROM applsys.wf_deferred wfd GROUP BY wfd.corrid, wfd.state;

Status	Meaning
Ready	Activity is ready to be processed
Delayed	Activity will be processed later
Retained	Activity was already processed
Exception	Activity had an error

Note: Make sure Workflow Background Process is running in the background.

In this way we can use business events in many ways to solve business requirements.

R12 Multi-Org Access Control

R12 Multi-Org Access Control (MOAC)

In previous releases, data was restricted to a single operating unit using views that striped base table data based on the current operating unit setting.

In release 12 a more flexible architecture has been put in place to support Multi-Org Access Control (MOAC). This architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.

To accomplish this

• Multi-org views have been removed, and replaced with synonyms. For example, MY_TABLE

would no longer be a view defined on MY_TABLE_ALL, but rather a synonym which points

to MY_TABLE_ALL

• The data restriction is accomplished by assigning a virtual private database (VPD) policy to

the synonym. This policy allows the system to dynamically generate restricting conditions

when queries are run against the synonym.

Data relating to defined VPD policies is viewable in the data dictionary view DBA_POLICIES. These policies associate a function with an object, and when the object is accessed, this function can return additional restrictions on the object to restrict the data returned. The particular policy used to

implement Multi-Org in release 12 is:

• Policy_name: ORG_SEC

• Policy_group: SYS_DEFAULT

• Package: MO_GLOBAL

• Function: ORG_SECURITY

MOAC Setup

The setup for MOAC can be found in the HR Foundation Responsibility. Use the Navigation Path

Security => Global Profile. This Profile should be set up to have access to all Operating Units defined

in the E-business Suite. To do this a Security Type of Secure organization by organization hierarchy and/or organization list should be assigned to the Global Profile and each of the valid Operating Units

within your business should be added to the list of Organization Names. E.g.

Additional profiles can be added through the Security => Profile screen. Each profile can be set up to

access one or a number of operating units. A profile must be set up for each combination of operating

units you wish to access through the E-business suite.

We will use a worked example throughout this document to demonstrate the MOAC functionality. For the example, I have set the Global Profile to include 2 operating units (these operating units had org  id’s of 82 and 84 respectively).

Security Profiles can then be assigned to the MO: Security Profile profile at Site, Application,

Responsibility, Organization and User levels. For the purpose of this document I have set it up at user level for my own user.

When I now try to access data through the UI can see data spanning the 2 Operating Units.

Demonstrate this we will look at 2 the Standard AR Receipts screen (Below). We can clearly see that

the screen is showing Receipts from 2 different Operating Units.

Database Multi-Org Access Control

In previous versions of the E-business Suite you may have used standard applications procedures such as

DBMS_APPLICATION_INFO.SET_CLIENT_INFO or FND_CLIENT_INFO.SET_ORG_CONTEXT.

These are superseded in Oracle Release 12 by the procedure MO_GLOBAL.INIT.

The MO_GLOBAL.INIT procedure accepts one parameter, Application Short Name. In order for the procedure to work, the FND_GLOBAL.APPS_INITIALIZE procedure must also be run so that theMO_GLOBAL package can see the Application Profile Options Values defined in the in the E-business Suite.

To use MOAC at database level through an SQL Editing tool such as Toad or SQL Developer, both

these procedures must be run.

Before we do this however, we demonstrate the effect of trying to access data through the new standard synonyms without running the procedures first. We again turn to our previous example, where we displayed 2 AR Receipts.

Firstly we try to access the information from the base table AR_CASH_RECEIPTS_ALL without

setting any session variables. This demonstrates that the data is there and that the 2 Receipts span both Operating Units:

We now try to access the data using AR_CASH_RECEIPTS synonym (remember that this is now a

synonym that points to the base table, but has a VPD Policy assigned to it). We demonstrate here that

the data cannot be seen.

We now setup all the variables needed for MOAC by running the 2 procedures. (In this example I have

already looked up my own User ID = 1299, the Responsibility ID for Receivables = 50538 and the

Receivables Application ID = 222). We then run the same query using AR_CASH_RECEIPTS and the data is now visible for both Operating Units.

Understanding the VPD

We have already seen that we must execute the MO_GLOBAL.INIT procedure to access data via

synonyms with VPD assignments. When we do this the MO_GLOBAL.INIT procedure calls the

function MO_GLOBAL.ORG_SECURITY with the following parameters:

• obj_schema - the object schema, in this case APPS

• obj_name – the object name (e.g., MY_TABLE)

The function then returns additional where clause conditions to restrict the data accessible from the

object. The structure of this function will dynamically generate conditions that will either:

1. Restrict the data to a single operating unit if the access mode is Single

2. Restrict the data to multiple operating units if the access mode is Multiple

3. Restrict the data to eliminate only seed data rows is the access mode is All

4. Restrict the data to not return any rows if the access mode is None

The conditions returned in each case are as follows:

Single OU Access

org_id = sys_context('multi_org2','current_org_id')

Only data for the current operating unit is accessible. The value of

sys_context('multi_org2','current_org_id') would have to be set to the current operating unit.

Multiple OU Access

EXISTS (SELECT 1

          FROM mo_glob_org_access_tmp oa

              WHERE oa.organization_id = org_id)

The user will be able to access data for any org_id which has been populated into

mo_glob_org_access_tmp. When a session is initialized in the applications, values will be populated into

mo_glob_org_access_tmp for each of the operating units the user has access to based on their "MO:

Security Profile" setting.

All OU Access

org_id <> -3113

Seed template records, which are used to create new seed data when a new operating unit is created, are created with an org_id of –3113. So in this mode, only these template records, which do not correspond to any actual operating unit, will be filtered out.

No OU Access

1 = 2

The condition is never satisfied. No data will be returned from the object.

Switching Off MOAC

MOAC can be easily switched off by simply not setting the MO: Security Profile profile at any level.

The MO_GLOBAL.INIT procedure will then simple set the context to for the Single Operating Unit as defined in the profile MO: Operating Unit.

Backwards Compatibility

When running queries on multi-org objects in SQL, you can still use the old CLIENT_INFO settings to gather data and run queries against multi-org objects if the following profile option is set to "Yes":

User Profile Name MO: Set Client_Info for Debugging

Profile Nam: FND_MO_INIT_CI_DEBUG

When this profile option is set to “Yes” and the global access mode setting is null (as it would be in a

SQL*Plus or other client session unless specifically set), the VPD function

MO_GLOBAL.ORG_SECURITY will return the following as the additional where clause condition for the object:

org_id = substrb(userenv('CLIENT_INFO'),1,10)

This will limit the data returned by the object to the current value set in CLIENT_INFO.

Restrict duplicate records in Oracle forms during entering new records

The purpose is to reject two records that contain duplicated values  while entering the records.

 In the below diagram dept no & DName are repeated.

The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.

It uses two calculated items, one in the data bock and another in a control block.

The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:

Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)

Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.

Function COMPARAISON (val1 varchar2, val2 varchar2)

Return number

Is

   answer number := 0;

Begin

   if val1 = val2 then

      answer := 1;

   end if;

   return(answer);

End;

COMPARAISON is a program unit stored in the Forms module.

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.

The first value (:ctrl.charsave) contains the bakup value of the current record.

The DEPT block must have the following properties setting:

Query all records

YES

The CTRL block must have the following properties setting:

Query all records	YES
Single record	YES
Database data block	NO

The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.

It summarize the values contained in all the rows of the DEPT block (dept.match_found).

If the total is greater than 1, we have two duplicated data.