Main Menu
| Home |
| Technical Articles |
| Training Articles |
| Receive Email for New Articles |
| Contributors |
| Apps Book |
Related Items
Home 
Technical Articles Oracle Workflows Workflows [Business Events] Training Lesson 4
Technical Articles Oracle Workflows Workflows [Business Events] Training Lesson 4 
| Workflows [Business Events] Training Lesson 4 | | Print | |
| Written by Anil Passi | |
| Tuesday, 26 December 2006 | |
|
In this article, I would like to extend upon the existing Workflow Tutorials. Oracle Apps is moving towards Oracle Fusion in which systems will be raising Events that will get actioned via a service. Lets understand this in plain English and plain PL/SQL. What is an event in Oracle Workflows? Event is nothing but a business activity that takes place in your system. Some of the examples for event are :- Purchase Order is approved A TCA record is end-dated An employee in HRMS gets terminated What is an Event Subscription? When an activity[or say event] occurs, you may want to do some additional processing by means of calling a Workflow or by executing some PL/SQL or by sending a message to some third party. Such a workflow or a PL/SQL is called subscription. For example, terminating an employee is an event and also sending an email to that person indicating their termination.... is a subscription. This subscription is either a workflow or a pl/sql or message etc. Can one Workflow Event have more than one Subscriptions? Indeed. You can attach as many subscriptions to a workflow event. If termination of an employee has 4 subscriptions attached, then termination screen performance can suffer [Does event subscription not make the processing slow]? Each subscription can be either made to run asap[called real-time] or in deferred mode[or call it background mode]. This is controlled by means of a field called Phase Code. Which example will we undertake in this article? a. We will create a table b. Define an event. c Raise that event....in other words invoke that event. c. Subscription of the event will insert records into that table What are the steps to have a simplest possible event-subscription? Step 1. Create table xx_event_result ( x_user_id INTEGER, x_user_name VARCHAR2(100) ) ; Step 2. Define an Event named xx.oracle.apps.test01 Step 3. Create a pl/sql function xx_test_event_01 that will be executed when the event is raised. All that this pl/sql function will do is to create a record in table xx_event_result Step 4. Attach pl/sql function named xx_test_event_01 [of step 3] to event xx.oracle.apps.test01 [of step 3]. This attachment will be done via event subscriptions screen 5. Raise the event passing parameters named xx_user_id and xx_user_name. 6. Check if the record has now been inserted in the table xx_event_result . All the executable code is being highlighted in this colour Now, lets do these steps in details with step by step with screenshots 1. CREATE TABLE xx_event_result ( x_user_id INTEGER, x_user_name VARCHAR2(100) ) ; 2. Define an Event named xx.oracle.apps.test01 This can be done via Workflow Administrator screen. Navigate to that screen and click on Business Events. Define an Event in the Event Definition screen. The name of this event is xx.oracle.apps.test01 Now create an event subscription, by clicking on Subscription button, Enter Source Type =Local System will be the name of your Database/Environment Instance. Note: For this example change the Phase to 99 Changing the phase code to between 1 and 99 will make our pl/sql function to get executed immediately, in real-time. 3. Create a pl/sql function xx_test_event_01 that will be executed when the event is raised. All that this pl/sql will do is to create a record in table xx_event_result. When invoking the event, two parameters namely user_id and user_name will be passed. It is the value of these parameters that will be inserted into the tables. CREATE OR REPLACE FUNCTION xx_test_event_01( p_subscription_guid IN RAW ,p_event IN OUT NOCOPY wf_event_t) RETURN VARCHAR2 IS l_user_name VARCHAR2(100); l_user_id INTEGER; BEGIN --read the parameters values passed to this event l_user_id := p_event.getvalueforparameter('XX_TEST_USER_ID'); l_user_name := p_event.getvalueforparameter('XX_TEST_USER_NAME'); INSERT INTO xx_event_result (x_user_id ,x_user_name) VALUES (l_user_id ,l_user_name); COMMIT; RETURN 'SUCCESS'; END xx_test_event_01; / Note: In the above pl/sql we are reading two parameters using API getvalueforparameter. Also note that our subscribing function xx_test_event_01 has two input parameters type RAW and type wf_event_t 4. Attach the procedure xx_handle_event_01 to event xx.oracle.apps.test01 This is done via subscriptions screen 5. Raise the event passing in parameters named xx_user_id and xx_user_name. DECLARE x_event_parameter_list wf_parameter_list_t; x_user_id INTEGER := 99999; x_user_name VARCHAR2(100) := 'ANILPASSI'; x_param wf_parameter_t; x_event_name VARCHAR2(100) := 'xx.oracle.apps.test01'; x_event_key VARCHAR2(100) := 'ANIL_0001'; x_parameter_index NUMBER := 0; BEGIN x_event_parameter_list := wf_parameter_list_t(); --Lets add the first value to the Event Parameter i.e. user_id x_param := wf_parameter_t(NULL ,NULL); x_event_parameter_list.EXTEND; x_param.setname('XX_TEST_USER_ID'); x_param.setvalue(x_user_id); x_parameter_index := x_parameter_index + 1; x_event_parameter_list(x_parameter_index) := x_param; --Lets add the second value to the Event Parameter i.e. User Name x_param := wf_parameter_t(NULL ,NULL); x_event_parameter_list.EXTEND; x_param.setname('XX_TEST_USER_NAME'); x_param.setvalue(x_user_name); x_parameter_index := x_parameter_index + 1; x_event_parameter_list(x_parameter_index) := x_param; wf_event.RAISE(p_event_name => x_event_name ,p_event_key => x_event_key ,p_parameters => x_event_parameter_list /*,p_event_data => p_data*/ ); END; / The above wf_event.raise will execute pl/sql function xx_test_event_01. This will insert a record into table xx_event_result as seen in below step. 6. We can now see that a record has now been inserted in the table. Comments
(46)
 written by balkrishna , December 29, 2006 written by balkrishna , December 29, 2006
i have read all the articles on workflow
now i have problem 1) when isend notification to manager to his personal email , i need approval from that email how i can achive it if u have any note on it please tell me i am finding this on metalink .if possible please provide the training i want approval for leave or may be termination iam now customize HR workflow so guide me regards mukesh written by Holli , January 09, 2007
Great info here! I was wondering if there is there a master list of all Oracle seeded Business Events that we can tie custom code into?
Thanks! Holli written by Anil Passi , January 09, 2007
.
. . Hi Holli You can get the list from this link. http://oracle.anilpassi.com/list-of-business-events-in-11.5.10.html Thanks Anil Passi written by Holli , January 09, 2007
Great info here! I was wondering if there is there a master list of all Oracle seeded Business Events that we can tie custom code into?
Thanks! Holli written by Anil Passi , January 09, 2007
.
. . Hi Holli You can get the list from this link. http://oracle.anilpassi.com/list-of-business-events-in-11.5.10.html Thanks Anil Passi written by Yasir , February 08, 2007
Hi Anil,
I have question on the business event tut,in case we have business event oracle.apps.xx,how can I know the underlying PL/SQL for it which will help me know how to use the workflow. ~yasir written by Bala , February 27, 2007
Hi Anil,
I understood that business events are raised in the application when some activity happens in the system. But in this lesson the event you have created is not related to any activity. I have a problem here. I need to start a workflow when invoices are imported into AP table. Now that I know how create a business event , how do I relate this event to invoice import so that event is raised whenever invoices are imported. Also I tried to create an event. I takes any name. Why it should be in the format xx.oracle.apps.test01.? Your help is appreciated Thanks written by Anil Passi , March 01, 2007
hi bala
in answer to your other question, you can raise the event as soon as invoice gets imported. after your import process has completed, you can loop through the invoices just not interfaced, and raise the event. In case you desire an event for invoice approval, then you may leverage the std oracle event. oracle.apps.ap.event.invoice.approval oracle.apps.ap.inv.invoice.recv thanks anil written by Anil Passi , March 14, 2007
Hi Bala
It is not a good practice to create database triggers in Oracle, however you may consider raiseing the event from DB Trigger on AP_INVOICES_ALL, after insert. But never write any processing logic within the trigger itself. While raising event, make invoiice_id the eventkey itself. Thanks, written by Anil Passi , April 20, 2007
Hi Senthil
Please try from some other PC. The images are rendering through all 3 browsers that I tried. Something possibly to do with your browser settings. THanks Anil written by Sharmila Mahapatra , May 14, 2007
Hi Anil,
Incase i want to use a seeded business event,for eg. Irecruitment has seeded business event which is raised whenever an applicant is created. I have created a subnscription for this business event. But how do i know/find out which parameters are passed in this business event[as it is seeded one] so that i can use this api p_event.getvalueforparameter to get the value for the parameter. Regards, Sharmila. written by Anil Passi , May 14, 2007
Hi Sharmila,
You will need to either read through documentation or you need to search the PL/SQL or the Java Code from where your event is raised. For example, if your event name is oracle.apps.per.irc.api.party.registered_user_application then it is being called from irc_party_be3. If you open that package, you will notice that various parameters like person_id, vacancy_id, applicant_number etc are being passed in. Thanks, Anil passi written by suman , June 08, 2007
hi Anil,
i have one issue in FYI Notification. i want to send FYI notification after a concurrent program execution. for that i have created a FYI notification and it is also working. But i want to attache a URL attribute in that Notification . So that whenever i will click on the URL it will show the output file of concurrent program which is present on server. please help me.
I am unable to see the create event button on the business event, is there any profile option required to setup?
Hi Chan
If you are on a dev environment, run the below select text from wf_resources where name = 'WF_ADMIN_ROLE' ; update wf_resources set text = '*' where name = 'WF_ADMIN_ROLE'; commit; Thanks, Anil Passi
Hi Anil,
Thanks, Where can I find the detail documentation on the Business Events?
Hi Chan
You will need to visit the respective product module user guide. However, I find it quicker to search the all_source to find out the circumstances in which the specific events are raised, and looking at source code you can precisely know the parameters passed to the events Thanks Anil
Hi Anil,
How do I determine the application user that fired a business event if i want the notification to be sent to the applicant? I have enabled the following business event and created a subscription: oracle.apps.per.irc.api.party.registered_user_application The event launches a simple workflow which sends an acknowledgement notification. I want the workflow to send a notification to the applications user that fired the event. Please advise how to achieve this. I can set up the event and subscription successfully if I hard code the performer to SYSADMIN. Thanks, Kavita
Dear Anil,
You are really the PRIDE of India for maintaining such an informatory/training helpful site. Thanks for this job and it has really helped us. Now i have a question for you - please help us. There is standard event in Oracle Apps 11.5.10 something like oracle.apps.....DQM.realtimesync. we need to call a custom procedure whenever this event is raised. we have custom subscription to this event calling a PL/SQL procedure. i need to pass user_id to this custom procedure. How do i pass this to this custom procedure - how do i do this? How do i know the parameters for this standard event? Thanks for y our help, Regards, Upanishad
I am trying to create a subscription to a standard event oracle.apps.fnd.umf.reg.user_approved and call a PL/SQL procedure from it.
when i test it using the test icon given in the business event screen it is calling the PL/SQL procedure and inserting into the table. but when i do it through the core application and create an isupplier user, it does not invoke the PL/SQL procedure. can you please help us out on this one? Thanks, Mehta.
Hi Mehta
If the value of PHASE in business event is lower than 99, then it implies that business event subscription will be executed in the same session from where it was raised. Hence, for the purpose of testing ensure that phase is 99 [if possible] Next, bounce the Workflow Deferred Listener Queue. After that, re-raise the business event and run SQL below SELECT a.user_data.event_name event, a.msg_state status, a.* FROM applsys.aq$wf_deferred a WHERE a.user_data.event_name = '' AND enq_time > SYSDATE - .5 ORDER BY 1, 2; This SQL will tell you the status of queue for that event Thanks, Anil passi
Hi Anil,
In the example that you have showed. How are you raising this event? 5th point that you have mentioned, is it the procedure where we will pass parameters (x_user_id, x_user_name)? Do we only need to pass these 2 parameters... Please help me with my doubts... Thanks... Disha
Hi Anil,
In the example that you have showed. How are you raising this event? 5th point that you have mentioned, is it the procedure where we will pass parameters (x_user_id, x_user_name)? Is it the procedure xx_handle_event_01? Do we only need to pass these 2 parameters... Further addition to my doubt, what is the procedure xx_handle_event_01 and how do we attach it to the event. Is it the generate function that we specify in the create event screen? I have checked the subscriptions screen, didn't find a place where i can attach the procedure xx_handle_event_01. Please advice... It will be a great help... Thanks, Disha
Hi Anil,
U have done a great job. It was easy for me to create and subcribe for the event using this guide. My requirement is to invoke an ESB from the event which routes the event data to BPEL. I m using a Java rule function for the subscription, "oracle.apps.fnd.wf.bes. WebServiceInvokerSubscription(SERVICE_WSDL_URL,SERVICE_ NAME,SERVICE_PORTTYPE,SERVICE_OPERATION,SERVICE_PORT)" The event is getting raised but it is not invoking the ESB. I dont know how to trace the flow of the event and y it is not invoking ESB. Also, I m sending an XML content as event data. The ESB is routing a schema element of type string to BPEL.Now the XML content from event should be mapped to the schema element of ESB. I dont know how to do it. Can u pls guide me. Thanks, Shyam
Hi Anill
In HRMS I need to get approval before re-hiring an Ex-Employee, so If i created such a custome business event how to make it fire when the user press button save in the Person form ?
Hello Anil,
There was a urgent business requirement in my present project. Requirement:- When ever the user tries to change the project status='CLOSED' in 'Oracle Projects', a procedure should be triggred for any open Sales or purchase orders against the project. If any such open orders then a warning message should popup to user -> With YES/NO buttons if he choose YES the status should be allowed to change. If he choose NO then the status should not change. Problem:- I was unable to find the trigger event, so that i could place the logic. Thing is that when we select the status to change, the form is automatically commiting itself.Because there was a trigger 'WHEN-BUTTON-PRESSED' at item level in base form to saying forms_ddl('commit') Can u plz suggest the best method to achieve this requirement? it is very urgent.
Hi Gopi123
Please try to use Forms Personalization to trap WHEN-VALIDATE-RECORD event. Alternately, you can see the other set of triggers that can be trapped. Thanks, Anil
Thanks Anil,
Actually i tried it before, but i was unable to meet my requirement. Anyway i will once again give a try and get back to you Thanks
Hi Anil,
Thanks for the inputs provided by you yesterday. I tried the solution, suggested by u. Think is that i could not find any trigger firing between the update and commit of the status. The only trigger which is fired is 'WHEN-NEW-ITEM-INSTANCE' that to after commiting the new status at the base table. This is because at form level, where the item is defined there is a trigger 'WHEN-BUTTON-PRESSED' in that - commit command is there. So due to this when ever we do any changes to that particular 'button'-the result is the new status commited at the back end. Responsibility:-6010 PA Create and maintain projects transactions Form:- PAXPREPR Blockname:- PROJECT_FOLDER -There here any other way to handle it than changing the code at base form Item:- CHG_STATUS
Hi Gopi
If there is no supporting trigger, and if there is not standard business event, and also if there is no API hook, then you can try below [not ideal] 1. Create a trigger on that table on update for each row, when status changes 2. from here raise a business event 3. the scubscription to busincess event should have phase lower than 100 4. do your checks in pl/sql subscription and raise application error. This approach is preferable to merely typing all logic inside trigger itself. A slighly non-user friends ORA-20001 will be seen by the user on the screen. This will also force the rollback given that this will be an unhandled exception. Thanks, Anil Passi
Hello Anil,
Thanks a lot with the inputs provided. Yes, i tried it with one of the Orcale public(sp. for client extension) API and completed the job with a mix of form personalization. Here little part is pending....i.e. when the user wants to go ahead and close the status. Status need to be changed to 'Hard Close'-this we had done using a procedure call. Which is been affecting basetable. -> Little problem there is.....the same is not reflected back at front end immediately. if we close the form and reopen the same then the new status is reflected. is there any commmand from form personalization to refresh the form and get the result immediately, after base table is affected. --> if this is solved - then the component is 100% to go. Please help me to complete 100%. Thanks, Gopi
Hi Gopi,
I am pleased to hear your progress. All that remains now is that you execute query, this again can be done using forms personalization. Please see image below  Thanks, Anil Passi
Hello Anil,
Thanks again for your valuable help and guidance. I tried with the 'Execute Query', but the problem is, it is not quering for the particular block. But quering for all the remaining blocks of the form. Because this form is associated with 3 blocks. Anil, this is the only part pending....remaning functionality ... it is perfect.
Before Executing the query on that block, you first need to navigate to that specific block. You can try these steps manually first
1. Navigate to the block in question after saving data 2. Enter Query on that block 3. Execute Query on that block You will notice only one block gets refreshed. The same should be the case when this is done programatically. Thanks Anil
Hi Anil,
Once again, Thanks for ur valuable guidance. Anil i had manually quered -> the problem is i was unable to query that single block...the whole blocks gets affected. Thanks, Gopi
Hi Anil,
We have enabled the standard business event 'oracle.apps.per.api.assignment.terminate_apl_asg' and also enabled a subscription for a custom PL/SQL. The phase for subscription is 8 (hence it should be run synchronously). But the custom code never fires. I suspect the seeded business events are not being raised ? Is there any way to find out whether the seeded business events are being raised or not ? Do we need to implicitly raise the seeded business event using wf_event.raise ? Thanks in advance for your help. Regards Manish
Hi Manish,
The event should be raised from the application itself. Please use below SQL to find out if the desired event is being raised SELECT a.msg_state ,a.user_data.event_name ,to_char(a.user_data.send_date, 'DD-MON-YYYY HH24:MI:SS') send_date ,COUNT(*) FROM applsys.aq$wf_deferred a WHERE a.user_data.event_name = 'oracle.apps.per.api.assignment.terminate_apl_asg' -- AND a.user_data.send_date > SYSDATE -1 GROUP BY a.msg_state, a.user_data.event_name, a.user_data.send_date ORDER BY 1, 2 Using this SQL, if you discover that event is not being raised, then feel free to raise SR with Oracle. oracle.apps.per.api.assignment.terminate_apl_asg is a standard event, hence ideally, there should be no need to raise that event from custom code. Thanks, Anil Passi
Hi Anil,
Once again, Thanks for ur valuable guidance. Anil i had manually quered -> the problem is i was unable to query that single block...the whole blocks gets affected. But in standard functionality i.e. changing to 'Approved','Pending Review'...etc. that particular block only gets refreshed and other block stays the same. Anil, this is only thing pending. If this is 100% requirement will be completed. Thanks, Gopi
Hi Anil,
According to me i think individual record can be quered again(refreshed) by using set_record_property...but i need your help to figure out how to use this property in form personalization. If this works...it will be of great use. Thanks
Hi Anil,
Nice article. I have a small doubt. If I want to invoke a business event from a user hook in the Oracle HR API, how will I pass the parameter to it. Were can I find the list of parameters for the standard Business APIs like oracle.apps.per.api.assignment.terminate_apl_asg. Regards, Khwaja
Hi Anil,
For one of our project requirement is to customize the standard COGS account generation. So, in order to meet the requirement we had designed a new process and attached the same to OEXWFCOG.wft. Doudt,we had attached a new custom process in the standard item type (i.e.OEXWFCOG.wft). But we had not made changes to existing process or functions. Will there be any problem in future if Oracle applies any patches for existing workflow ? Regards, Gopis
Hi Anil,
Hope you are doing fine. It was really a nice article for beginners like me. I have a doubt in this. How will i retrieve the values of parameters passed in a Raise event procedure, when the subscription type is a Workflow and not a PL/SQL function. I want to pass values from the database trigger to the event and from the event to my workflow process which gets fired when the event is raised. Please help.. Thanx in advance. Pradeep
Hi Anil,
I am new to workflow and I need some help in sending notifications based on xmlposent event in Purchasing. I want to call a custom package which would get my required information based on PO number and org_id I know that this event has PO number and org_id parameters. But I am confused as how to pass these two parameters to my custom package for the event/event_key using subscriptions. Thanks, Venkat
Hi Anil,
I am trying to send custom message based on “oracle.apps.po.event.xmlposent” event. If xmlposent then send notification to preparer, else send notification to group. Steps I followed 1.Created a custom message in “PO XML” workflow. 2.Attached custom attributes (preparer, requisition, requisition description, vendor, buyer, preparer’s email, group email) 3.Developed a function, which would get po_header_id from event attributes and get the custom attributes information. 4.Attached this function as event subscription(phase = 1). 5.Output= it is setting the values for custom attributes for itemtype/itemkey. 6.Created another event subscription(phase= 101) to send notification. Here I’m attaching the custom message defined in step1 7.Output = it is sending notification to the role but with out the custom attribute values. 8.I noticed that item_attribute_values are being set but not he notification_attributes. Could you please tell me if I’m missing something here? Is there any better approach? Thanks, Venkat.
Hi Anil,
I am new to Worflow. But I found your tutorials using these screenshots, very helpful(especially for newbies). Thanks a lot for your great work. You must be logged in to a comment. Please register if you do not have an account yet.
|
Subscribe to this comment's feed
now i have problem
1)
when isend notification to manager to his personal email ,
i need approval from that email
how i can achive it
if u have any note on it please tell me i am finding this on metalink .if possible please provide the training
i want approval for leave or may be termination
iam now customize HR workflow
so guide me
regards
mukesh