Business Events in Oracle EBS
This article explains what is a business event in Oracle EBS and how do we run/execute business events with custom code.
Sometimes we might fall into a situation where we have to apply some custom logic as soon as some action is performed in Oracle EBS. Generally, we will go with the solution as Oracle Trigger or Oracle Alert apart from these two we have business events in Oracle EBS. These are Oracle predefined business events and we have to search for business events that fulfill our requirement.
Ex: When the AP invoice got approved update one custom table, generally we will write a trigger on AP Invoice tables or will design an Oracle Alert. Instead of Oracle trigger an Alert we can use Oracle predefined business events in some of the scenarios.
Navigation: Workflow Administrator -> Administrator Workflow -> Business Events (Its OAF Page)
Finding a correct business event for your request, check in the business event search area as oracle.apps.ap %( here application short name is ap)
We can do a below tasks with an Oracle business events
- Custom – Call custom PLSQL code
- Invoke Web Service
- Launch Workflow
- Send a notification
Call custom PLSQL code:
The requirement is sending a custom notification using custom PLSQL program as soon as the concurrent program is completed.
- Identify the business event
- Enable the business even for concurrent program
- Create a procedure/sql to send a notification
- Create a subscription
- Create a custom PLSQL package and attach it to subscription
- Run the concurrent()
Identify the business event
Go to the business events page and search for oracle.apps.fnd.conc%
Here we are using oracle.apps.fnd.concurrent.program.completed
Enable the business event for the concurrent program:
Concurrent Program Name: Concurrent Program Details Report
Code to send a notification:
DECLARE
v_from VARCHAR2 (80) := 'oracleerphub@gmail.com';
v_recipient VARCHAR2 (180) := 'oracleerphub1@gmail.com';
v_subject VARCHAR2 (180);
v_mail_host VARCHAR2 (80) := 'test.com'; --SMTP host name
v_mail_conn UTL_SMTP.connection;
v_crlf VARCHAR2 (2) := CHR (13) || CHR (10); --can be replaced by utl_tcp.v_crlf
v_body VARCHAR2 (10000);
BEGIN
--
v_mail_conn := UTL_SMTP.open_connection (v_mail_host, 25); --25 is SMTP Port Number
UTL_SMTP.helo (v_mail_conn, v_mail_host);
UTL_SMTP.mail (v_mail_conn, v_from);
UTL_SMTP.rcpt (v_mail_conn, v_recipient);
v_Subject := 'Test Email';
--
/* To get mail_host details
SELECT fscpv.parameter_value,
fscpt.display_name ,
fscpt.description
FROM fnd_svc_comp_params_tl fscpt,
fnd_svc_comp_param_vals fscpv
WHERE 1 = 1
AND fscpt.display_name = 'Outbound Server Name'
AND fscpt.language = 'US'
AND fscpt.source_lang = 'US'
AND fscpt.parameter_id = fscpv.parameter_id; */
--
v_body :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| v_crlf
|| 'From: '
|| v_From
|| v_crlf
|| 'Subject: '
|| v_Subject
|| v_crlf
|| --'To: ' || v_Recipient || v_crlf ||v_msg|| '';
'To: '
|| v_Recipient
|| v_crlf
|| v_crlf
|| 'Here you can write the body message'
|| v_crlf;
--
--utl_smtp.DATA (v_mail_conn, 'MIME-Version: 1.0' || v_crlf || 'Content-type: text/html' || v_crlf || v_body );
UTL_SMTP.DATA (v_mail_conn, 'MIME-Version: 1.0' || v_crlf || v_body);
UTL_SMTP.quit (v_mail_conn);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
Package Specification:
CREATE OR REPLACE PACKAGE XXCUS_CONC_REQ_EVENT
IS
FUNCTION REQUEST_COMPLETED (p_subscription_guid IN RAW,
p_event IN OUT WF_EVENT_T)
RETURN VARCHAR2;
END XXCUS_CONC_REQ_EVENT;
Package Body:
CREATE OR REPLACE PACKAGE BODY XXCUS_CONC_REQ_EVENT
IS
FUNCTION REQUEST_COMPLETED (p_subscription_guid IN RAW,
p_event IN OUT WF_EVENT_T)
RETURN VARCHAR2
IS
v_programName VARCHAR2 (100);
v_appShortName VARCHAR2 (30);
v_respKey VARCHAR2 (100);
v_userName VARCHAR2 (30);
num_concRequestID NUMBER;
l_Req_Id NUMBER;
v_from VARCHAR2 (80) := 'oracleerphub@gmail.com';
v_recipient VARCHAR2 (180) := 'oracleerphub1@gmail.com';
v_subject VARCHAR2 (180);
v_mail_host VARCHAR2 (80) := 'test.com'; --SMTP host name
v_mail_conn UTL_SMTP.connection;
v_crlf VARCHAR2 (2) := CHR (13) || CHR (10); --can be replaced by utl_tcp.v_crlf
v_body VARCHAR2 (10000);
BEGIN
num_concRequestID := p_event.getValueForParameter ('REQUEST_ID');
SELECT fcp.concurrent_program_name,
fa.application_short_name,
fr.responsibility_key,
fu.user_name
INTO v_programName,
v_appShortName,
v_respKey,
v_userName
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_application fa,
fnd_responsibility fr,
fnd_user fu
WHERE fcr.request_id = num_concRequestID
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fa.application_id
AND fcr.responsibility_id = fr.responsibility_id
AND fcr.requested_by = fu.user_id;
IF v_programName = 'FNDCPPGD' AND v_appShortName = 'FND' AND v_userName = 'DEVUSER'
THEN
--Concurrent: Business Intelligence Integration Enable
--fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
-- Sending an Email
BEGIN
--
v_mail_conn := UTL_SMTP.open_connection (v_mail_host, 25); --25 is SMTP Port Number
UTL_SMTP.helo (v_mail_conn, v_mail_host);
UTL_SMTP.mail (v_mail_conn, v_from);
UTL_SMTP.rcpt (v_mail_conn, v_recipient);
v_Subject := 'Test Email';
--
/* To get mail_host details
SELECT fscpv.parameter_value,
fscpt.display_name ,
fscpt.description
FROM fnd_svc_comp_params_tl fscpt,
fnd_svc_comp_param_vals fscpv
WHERE 1 = 1
AND fscpt.display_name = 'Outbound Server Name'
AND fscpt.language = 'US'
AND fscpt.source_lang = 'US'
AND fscpt.parameter_id = fscpv.parameter_id; */
--
v_body :=
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
|| v_crlf
|| 'From: '
|| v_From
|| v_crlf
|| 'Subject: '
|| v_Subject
|| v_crlf
|| --'To: ' || v_Recipient || v_crlf ||v_msg|| '';
'To: '
|| v_Recipient
|| v_crlf
|| v_crlf
|| 'Here you can write the body message'
|| v_crlf;
--
--utl_smtp.DATA (v_mail_conn, 'MIME-Version: 1.0' || v_crlf || 'Content-type: text/html' || v_crlf || v_body );
UTL_SMTP.DATA (v_mail_conn,
'MIME-Version: 1.0' || v_crlf || v_body);
UTL_SMTP.quit (v_mail_conn);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ' - ' || SQLERRM);
END;
END IF;
COMMIT;
RETURN 'SUCCESS'; -- You should return Success or Error
END REQUEST_COMPLETED;
END XXCUS_CONC_REQ_EVENT;
The business event will execute for all the concurrent programs which are enabled for business event execution. We want to restrict our custom logic execution specific to our program (Concurrent Program Details Report) then we have to use below conditions.
v_programName = ‘FNDCPPGD’ AND v_appShortName = ‘FND’ AND v_userName = ‘DEVUSER’
Here we restricted based on the concurrent program and username, the business event will execute only for the user (DEVUSER).
Enable the Profile Option:
Create a subscription:
Subscription with Parameters:
CREATE FUNCTION XXCUS_BUSINESS_EVE_PARA(p_subscription_guid IN RAW, p_event IN OUT wf_event_t) RETURN VARCHAR2
IS
v_param_list wf_parameter_list_t; -- Subscription parameter list, that we defined (para1 and para2)
v_param_name VARCHAR2 (240);
v_param_value VARCHAR2 (2000);
v_event_name VARCHAR2 (2000); -- Business event name : oracle.apps.fnd.concurrent.program.completed
v_event_key VARCHAR2 (2000); -- A unique identifier
v_event_data VARCHAR2 (4000); -- Event Name, Event Key and Event Data(Additional Details)
BEGIN
v_param_list := p_event.getparameterlist;
v_event_name := p_event.geteventname ();
v_event_key := p_event.geteventkey ();
v_event_data := p_event.geteventdata ();
IF v_param_list IS NOT NULL
THEN
FOR i IN v_param_list.FIRST .. v_param_list.LAST
LOOP
v_param_name := v_param_list (i).getname;
v_param_value := v_param_list (i).getvalue;
END LOOP;
END IF;
COMMIT;
RETURN 'SUCCESS';
END XXCUS_BUSINESS_EVE_PARA;
Now run the concurrent program (Concurrent Program Details Report) using DEVUSER name and will receive an email notification.