Outbound Customer Interface Using Business Events – The Bare Bones

It is possible to write an outbound customer interface in Oracle without using Business Events. You would start by writing an SQL query which fetches data from the various TCA tables containing the entities that you are interested in. So, for example if you want to write an outbound interface which fetches all new and updated parties, you would start with something like

SELECT party_name
      ,party_number
      ,party_type
FROM   hz_parties
WHERE TRUNC(last_update_date) = TRUNC(SYSDATE);

However, as the TCA entities and their relationships increase in both number and complexity in the EBS instance, it becomes more practical and easier to use Business Events to track and record the changes. What are business events? Well, they are just some events which are fired whenever specific business actions take place.For more information on business events, Google is your friend. Using business events to implement an outbound interface essentially involves two steps:

1. Identifying the appropriate business event
2. Creating a subscription for the event

Let us assume, for simplicity’s sake, that we want to implement an outbound interface which is used to send updated customer account information to an external system. The first step, in such a scenario, is identifying the business event which would be raised when a customer account is updated. One of the better ways to do this is from the Integration Repository which is accessible from the Integrated SOA Gateway responsibility in R12. You can also query the WF_EVENTS_VL or try looking up the documents. The business event raised when a custom account is updated is ‘oracle.apps.ar.hz.CustAccount.update’.

Once this is known, we need to create a subscription for this. You create a subscription to specify a set of actions which need to be performed whenever a certain business event takes place. In our case, let us assume that whenever the ‘oracle.apps.ar.hz.CustAccount.update’ event is raised (that is, whenever a customer account is updated), we want to store the account information in a staging table. At the end of the day, we will collect the information for all accounts that were updated during the day and send it to an external system. So, in our case the subscription that we will create will fetch information for the updated customer account and store it in a staging table. The subscription can perform actions such as calling a Java class, a PL/SQL function or a Workflow Process. In this case, we will call a PL/SQL function. As a rule, the PL/SQL function should have the following input parameters and should return a VARCHAR2 data type.

FUNCTION insert_stg_tbl (p_subscription_guid IN RAW
                        ,p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2;

One thing to note here is that each event has it own parameter(s) which will be passed on to the function called by the event subscription when the event is raised. They are accessible from the p_event parameter in the function. For the  ‘oracle.apps.ar.hz.CustAccount.update’ business event, the p_event  parameter will contain the cust_account_id. A list of parameters for various events is provided in the Oracle Trading Community Architecture Technical Implementation Guide.

With the basic theory out of the way, here are the steps that need to be followed to complete our interface:

From any appropriate Workflow responsibility, go to the Business Events page (For example: Workflow Administrator Web Applications>Administrator Workflow>Business Events) and search for the event ‘oracle.apps.ar.hz.CustAccount.update’

Click on the images for a larger view.

Click on the Subscription icon to view existing subscriptions for the events.

Click on the Create Subscription button to create a new subscription. Most of the required fields should be auto-populated. If not, fill in the details as show in the screenshot. The value for the ‘Sytem’ field will be available in the LOV. The ‘Action Type’ should be Custom. After entering the values in the fields, click on the Next button

In the ‘PL/SQL Rule Function’ field, enter the name of the function which will be used to perform actions when the event is raised. I have entered xx_cust_outbound_pkg.insert_stg_tbl where xx_cust_outbound_pkg is the package which contains the function insert_stg_tbl. Enter values in the ‘Owner Name’ and ‘Owner Tag’ fields. These should be valid application short names. Then click on the Apply button.

For our subscription function, we need to create a table which will store the cust_account_id, the account_name, the action that was performed and the last_update_date

CREATE TABLE xx_cust_outbound_tbl(
cust_account_id NUMBER
,account_name VARCHAR2(254)
,action VARCHAR2(30)
,last_update_date DATE
);

The package specification and body containing the function is provided below

CREATE OR REPLACE PACKAGE xx_cust_outbound_pkg IS

 FUNCTION insert_stg_tbl (p_subscription_guid IN RAW
 ,p_event IN OUT NOCOPY wf_event_t)
 RETURN VARCHAR2;

END xx_cust_outbound_pkg;
/

CREATE OR REPLACE PACKAGE BODY xx_cust_outbound_pkg IS
 --function which will be invoked by the subscription
 FUNCTION insert_stg_tbl (p_subscription_guid IN RAW
 ,p_event IN OUT NOCOPY wf_event_t)
 RETURN VARCHAR2 IS

 lv_cust_account_id NUMBER;
 lv_account_name VARCHAR2(254);

 BEGIN
 --check the event name
 IF UPPER(p_event.geteventname()) = 'ORACLE.APPS.AR.HZ.CUSTACCOUNT.UPDATE' THEN
 --if an account is updated, store the cust_account_id, account_name, action performed and update date in the staging table 

 --get the CUST_ACCOUNT_ID
 lv_cust_account_id := p_event.getvalueforparameter('CUST_ACCOUNT_ID'); 

 --get the account name based on the CUST_ACCOUNT_ID
 SELECT account_name
 INTO lv_account_name
 FROM hz_cust_accounts
 WHERE cust_account_id = lv_cust_account_id; 

 --insert into staging table
 INSERT INTO xx_cust_outbound_tbl VALUES (lv_cust_account_id, lv_account_name, 'ACCOUNT_UPDATE', SYSDATE); 

 END IF;
 COMMIT;

 RETURN 'SUCCESS';

 EXCEPTION
 WHEN OTHERS THEN
 RETURN 'ERROR';
 END insert_stg_tbl;

END xx_cust_outbound_pkg;
/

Once these steps are done all you need to do is update a customer account by navigating to Trading Community Manager>Trading Community>Customers>Standard, wait for the event to be processed and voila! you have the details in your staging table.

Notes:

1. Anil Passi’s blog has a code listing which you can use to find the parameters for a business event.
2. To check if an event was raised, you can query the WF_DEFFERED table (provided you entered a phase>99 while creating the subscription) where the CORRID column stores the event name in the form APPS:<event_name>. A value of 2 in the STATE column indicates that the event was processed.
3. Noticed that sometimes in EBS 12.1.3 changing the name of the subscription function in the front end does not take effect unless the middle tier is bounced. Could be an issue with my instance though.

Accessing Oracle Forms from Workflow(and a nifty Personalization)

It is quite an easy task to enable a user to open a Form directly from an Oracle Workflow Notifications page. In fact, we can also pass parameters to the form so that it automatically queries the form and displays a record based on the parameter which is passed. This post demonstrates how this is done. Along the way we will also look at a small but pretty useful trick – clicking a button through Form Personalization.

Following are the basic steps:

1. Register a form function as per requirement
2. Create an attribute of type ‘Form’ which stores the name of the form function and the parameter with which we want to call it.
3. In a workflow process include a notification message.
4. Associate the attribute with the notification message

The form that I want to call from Notifications window is the Find Service Request function. When calling the function, I will be passing a parameter which will be populated in the appropriate field and the search button will be clicked automatically.

Since the Find Service Request function does not accept any parameters, first I will register my own function(XXXSRTEST) based on the seeded form so that my function will be able to accept the parameter which I will pass to it from the Workflow.

As you can see from the following screen shots, the only difference between my function and the seeded Find Service Request function(CSXSRISV) is that my function can accept the CUSTOMER_ID parameter.

Add the function to the appropriate menu so that it is accessible from the responsibility from where the Workflow Notifications will be viewed. Now I will open the function and personalize it so that the parameter that I pass is populated in the appropriate field and the Search button is clicked automatically. I create the following personalization rule:
Seq:10
Description:Accept parameter and execute query
Condition
Trigger Event:WHEN-NEW-FORM-INSTANCE
Trigger Object:None
Condition:None
Processing Mode:Not In Enter-Query Mode
Context:
Level: Site

Then I add three actions to the rule. In the first action(seq#20), I populate the appropriate field in the form with the value stored in the parameter which I created for this function. During runtime, I will pass a value to this parameter from the Workflow.

In the second(seq#30) and third actions(seq#31) I click the Search button automatically through personalization. First I shift focus to the Search button using the ‘GO_ITEM’ builtin.

Then I call the EXECUTE_TRIGGER builtin with argument WHEN-BUTTON-PRESSED to simulate the button press.

Once these personalizations are done, I will make the function inaccessible from the front end by removing the prompt name for the function from the menu.
In the Workflow, I add two attributes – ‘PO Number’ and ‘Open a Form’. ‘PO Number’ is a text type attribute which is set to a value of 4880(this is the parameter which I will pass to my form) and it has the internal name ‘PO_NUM’

‘Open a Form’ is an attribute of type ‘Form’ and it is set to a value XXXSRTEST:CUSTOMER_ID=&PO_NUM. The value indicates that the form function to be opened is XXXSRTEST with the parameter CUSTOMER_ID. The value stored in the attribute PO_NUM will be passed to the parameter.

Create a message and associate both these attributes with the message. Attach the message to a notification.Associating a ‘Form’ type attribute with the notification message displays a link in the Notification at runtime which can be clicked to open the Form.

Run the Workflow and open the notifications page. You will see a link in the ‘References’ sections with the same name as that of the ‘Form’ type attribute.

Clicking on the link opens my custom function ‘XXXSRTEST’ while passing the value 4880(stored in the PO Number attribute) to parameter CUSTOMER_ID. The personalizations in the function populates the Service Request Number field with the value of the parameter and automatically clicks on the Search button to execute the query and the appropriate record is displayed.

Note:

1. The form function will open automatically only if it is accessible from the same responsibility from which the Workflow Notification is viewed otherwise it will prompt you to choose the responsibility manually.
2. The syntax for accessing a form with parameters may vary depending upon the EBS version, in my instance XXXSRTEST:CUSTOMER_ID=&PO_NUM works. If it doesn’t work in your system try using XXXSRTEST:CUSTOMER_ID=’&PO_NUM’ or XXXSRTEST:CUSTOMER_ID=”&PO_NUM”.