Showing posts with label Oracle Business Event. Show all posts
Showing posts with label Oracle Business Event. Show all posts

30 October, 2017

How to Re-trigger Oracle Business events


1.       Go to responsibility: Workflow Administrator Web Applications.
2.       Navigate to: Administrator Workflow à Business Event
3.       Search for the event name: oracle.apps.wsh.delivery.gen.shipconfirmed


1.       Click on “Test” Button.
Note: If Test button is not visible on screen then this is the access issue.
In this case, run below command.
update wf_resources set text='*' where name like 'WF_ADMIN_ROLE';            
commit;              
Re-open application.
Now you can see the Test button.
2.       In this screen, give details as below.
Event Key : <Delivery Id which needs to retrigger eg. 17190692>
Event parameters,
Label: DELIVERY_ID
Value: <Delivery Id which needs to retrigger eg. 17190692>

Now click on button: Raise in PLSQL


Thus, Event is re-triggered for the given delivery ID.
You can check the entry in event tables with below queries.
   select DEQ_TIME, DEQ_TID, a.* from WF_DEFERRED a WHERE CORRID = 'APPS:oracle.apps.wsh.delivery.gen.shipconfirmed';


If DEQ_TIME, DEQ_TID is not null then, Data is been dequeued to BPEL Queue.

If DEQ_TIME, DEQ_TID is null then, we need to raise ticket to bounce the workflow manager “Workflow agent listener”.
Retest the process after bounce.

  Now check below query.
      
select CONSUMER_NAME, MSG_STATE ,DEQ_TIME , DEQ_TIMESTAMP,
tab.* from apps.AQ$WF_BPEL_QTAB tab 
where tab.user_data.event_name = 'oracle.apps.wsh.delivery.gen.shipconfirmed' order by ENQ_TIME DESC   ;


If MSG_STATE is ‘PROCESSED’ then the Data is received at SOA end.
If MSG_STATE is other than Processed then ask SOA to restart the process.

Query to check parameters passed to event:

SELECT wd.user_data.event_name,
       wd.user_data.event_key,
       rank() over(PARTITION BY wd.user_data.event_name, wd.user_data.event_key ORDER BY n.NAME) AS serial_no,
       n.NAME parameter_name,
       n.VALUE parameter_value,
       wd.user_data.error_message,
       wd.user_data.error_stack,
       wd.msgid,
       wd.delay
  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n
 WHERE  1=1
 AND wd.user_data.event_name LIKE '%shipconfirmed%'
 ORDER BY wd.user_data.send_date DESC,
          wd.user_data.event_name,
          wd.user_data.event_key,
          n.NAME;


If you wish to re-trigger the event from database then use below block.
give the required delivery id for p_value and p_event_key parameters.

   declare
  l_parameters wf_parameter_list_t := wf_parameter_list_t();
begin

  wf_log_pkg.init(1, null, 1, 'wf%');
  wf_event.addparametertolist(p_name          => 'DELIVERY_ID',
                              p_value         => '9169530',
                              p_parameterlist => l_parameters);
  wf_event.raise(p_event_name => 'oracle.apps.wsh.delivery.gen.shipconfirmed',
                 p_event_key  => '9169530',
                 p_event_data => NULL,
                 p_parameters => l_parameters,
                 p_send_date  => sysdate);
  commit;

end;

How To Setup Oracle Business Event


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.
The Event Manager contains a registry of business events, systems, named communication agents within those systems, and subscriptions indicating that an event is significant to a particular system. Events can be raised locally or received from an external system or the local system through AQ. When a local event occurs, the subscribing code is executed in the same transaction as the code that raised the event, unless the subscriptions are deferred.
Subscriptions can include the following types of processing:
  • Executing custom code on the event information
  • Sending event information to a workflow process
  • Sending event information to other queues or systems
  • Sending a notification based on the event information
  • Sending or receiving an Oracle XML Gateway message
  • Invoking a business process execution language (BPEL) process or other Web service

The uses of the Business Event System include:
  • System integration messaging hubs - Oracle Workflow with the Business Event System can serve as a messaging hub for complex system integration scenarios. The Event Manager can be used to "hard-wire" routing between systems based on event and originator. Workflow process event activities can be used to model more advanced routing, content-based routing, transformations, error handling, and so on.
  • Distributed applications messaging - Applications can supply generate and receive event message handlers for their business entities. For example, message handlers can be used to implement master/copy replication for distributed applications.
  • Message-based system integration - You can set up subscriptions which cause messages to be sent from one system to another when business events occur. In this way, you can use the Event Manager to implement point-to-point messaging integration.
  • Business event-based workflow processes - You can develop sophisticated workflow processes that include advanced routing or processing based on the content of business events.
  • Non-invasive customization of packaged applications - Analysts can register interesting business events for their internet or intranet applications. Users of those applications can register subscriptions to those events to trigger custom code or workflow processes.
 Lets see in details, how to set up the business event.     
      
Go to system administrator responsibility.

Setup Event:

Navigate to workflow à Administrator workflowà Business events
Search for the below business event.
If event status is not enabled, then go to event details and make it enable.

oracle.apps.wsh.delivery.gen.shipconfirmed


Setup event subscription: 

Click on subscription.
                Click on create subscription. Give below details.
System: <System name from lookup>
Source type: Local
Event filter: oracle.apps.wsh.delivery.gen.shipconfirmed
Phase: 105 (enter some number greater than 100)
Status: Enabled
Rule Data: Key
Action Type: Custom
On Error: Stop and Rollback


Click on next.
PL/SQL Rule Function: WF_RULE.DEFAULT_RULE
Out agent: WF_BPEL_QAGENT@<System name>
Priority: Normal
Owner Name: Shipping Execution
Owner Tag: WSH
Description: Subscription for enqueuing event in WF_BPEL_Q


Apply and save the changes.

Setup event queue subscriber


Now, we need to create the subscriber for the SOA Queue.
To create subscriber, Run below script.
DECLARE
subscriber sys.aq$_agent;
BEGIN
subscriber := sys.aq$_agent('WF_BPEL_Q', NULL, 0);
DBMS_AQADM.ADD_SUBSCRIBER(queue_name => 'APPS.WF_BPEL_Q',
subscriber => subscriber,
rule => 'tab.user_data.event_name=''' ||
'oracle.apps.wsh.delivery.gen.shipconfirmed' || '''');
END;
  

Restart the workflow agent listener service


Once finished with above steps.
Restart the workflow agent listened service.
Go to system administrator responsibility.
Navigate to: Concurrent à Manager à Administer
Search for: Workflow Agent Listener Service
And click on Restart.



Note: Whenever you change any setup on event/ event subscriptions/ queue subscriber, restart the Workflow Agent Listener Service to reflect the change. Else Event stops working.


Imp Queries related to events:

-------------- Query to Check the subscribers of the event ---------------

select * from wf_event_subscriptions wes
where wes.event_filter_guid in (select we.guid
from wf_events we where we.name= ‘oracle.apps.wsh.delivery.gen.shipconfirmed’
union
select weg.group_guid from wf_event_groups weg
where weg.member_guid in (select we.guid
from wf_events we where we.name= ‘oracle.apps.wsh.delivery.gen.shipconfirmed’)
);

--------------- Trigger business event from backend -----------------

   declare
  l_parameters wf_parameter_list_t := wf_parameter_list_t();
begin

  wf_log_pkg.init(1, null, 1, 'wf%');
  wf_event.addparametertolist(p_name          => 'DELIVERY_ID',
                              p_value         => '9169530',
                              p_parameterlist => l_parameters);
  wf_event.raise(p_event_name => 'oracle.apps.wsh.delivery.gen.shipconfirmed',
                 p_event_key  => '9169530',
                 p_event_data => NULL,
                 p_parameters => l_parameters,
                 p_send_date  => sysdate);
  commit;
end;

----------------- Event Error Table ---------------------------

SELECT * FROM WF_ERROR   ;

---------- Query to find event parameters/ key details --------------
SELECT wd.user_data.event_name,
       sender_name,
       sender_address,
       sender_protocol,
       wd.user_data.event_key,
       n.NAME parameter_name,
       n.VALUE parameter_value,
       wd.user_data.error_message,
       wd.user_data.error_stack,
       wd.msgid,
       wd.delay
  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n
 WHERE  1=1
AND wd.user_data.event_name LIKE '%oracle.apps.wsh.delivery.gen.shipconfirmed%'
 ORDER BY wd.user_data.send_date DESC,
          wd.user_data.event_name,
          wd.user_data.event_key,
          n.NAME

--------------- Query to get triggered event details -------------------

 select * from WF_DEFERRED WHERE CORRID = 'oracle.apps.wsh.delivery.gen.shipconfirmed';

   select CONSUMER_NAME, ADDRESS,  PROTOCOL,  EXPIRATION_REASON,
tab.* from AQ$WF_BPEL_QTAB tab  where tab.user_data.event_name = 'oracle.apps.wsh.delivery.gen.shipconfirmed' order by ENQ_TIME DESC   ;

    SELECT * FROM APPs.WF_BPEL_QTAB ;

--------------- Query to get queue subscriber details ----------------

 SELECT * FROM ALL_QUEUE_SUBSCRIBERS WHERE QUEUE_NAME = 'WF_BPEL_Q' ;


When, Event activity happens and Event is triggered for the given delivery ID.
You can check the entry in event tables with below queries.
   select DEQ_TIME, DEQ_TID, a.* from WF_DEFERRED a WHERE CORRID = 'APPS:oracle.apps.wsh.delivery.gen.shipconfirmed';


If DEQ_TIME, DEQ_TID is not null then, Data is been dequeued to BPEL Queue.

If DEQ_TIME, DEQ_TID is null then, we need to raise ticket to bounce the workflow manager “Workflow agent listener”.
Retest the process after bounce.

  Now check below query.
      
select CONSUMER_NAME, MSG_STATE ,DEQ_TIME , DEQ_TIMESTAMP,
tab.* from apps.AQ$WF_BPEL_QTAB tab 
where tab.user_data.event_name = 'oracle.apps.wsh.delivery.gen.shipconfirmed' order by ENQ_TIME DESC   ;


If MSG_STATE is ‘PROCESSED’ then the Data is received at SOA end.
If MSG_STATE is other than Processed then ask SOA to restart the process.

Query to check parameters passed to event:

SELECT wd.user_data.event_name,
       wd.user_data.event_key,
       rank() over(PARTITION BY wd.user_data.event_name, wd.user_data.event_key ORDER BY n.NAME) AS serial_no,
       n.NAME parameter_name,
       n.VALUE parameter_value,
       wd.user_data.error_message,
       wd.user_data.error_stack,
       wd.msgid,
       wd.delay
  FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n
 WHERE  1=1
 AND wd.user_data.event_name LIKE '%shipconfirmed%'
 ORDER BY wd.user_data.send_date DESC,
          wd.user_data.event_name,
          wd.user_data.event_key,
          n.NAME;