Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

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’

Image

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

Image

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

Image

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.

Image

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.

Image

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.

The Case of the Disappearing Error

I ran into a irritating bug while executing a packaged procedure a few days ago. Intially, it seemed that the was running into errors randomly but then I ran a few test cases and noticed the following:

  • The first time I execute the procedure after recompiling the package, it invariably throws an exception
  • On executing the procedure after this first run, no more exceptions are raised

After examining the code, I identified a global variable declaration in the package body as a possible culprit since the size of the variable was less than the length of actual string being stored.


g_module_name VARCHAR2(1) := 'XX_PROCESS_UTIL';

A few frenzied minutes of reading later, I knew what was happening:

The first time I executed the packaged procedure, Oracle tried to initialize the package (including all global variables) and threw an ‘ORA-06502: PL/SQL: numeric or value error: character string buffer too small’ error when it encountered the above line of code. However, even though the package was not initialized properly it was internally marked as initialized so when the same package procedure was executed again Oracle skipped the initialization part(including the above line of code) and hence did not throw the error.
Since a package is reinitialized whenever a new session is started or when the package is recompiled, so every time I recompiled the package and ran the procedure it again threw an error.

Steven Feuerstein provides a more comprehensive explanation of this issue and the best practices to avoid this here.

Use the Oracle Database to Fetch Cricket Scores Through PL/SQL

There I was working at my desk, one fine afternoon, while simultaneously trying to keep track of an ongoing match between India and Sri Lanka. Now, I do not particularly like the desktop widgets some cricket websites offer and opening up a browser window after every 10 minutes was a bit too distracting (both for me as well as my colleagues). So I wrote this little script to fetch the scores from the ESPNcricinfo RSS feed which allows me view the latest scores without having to move away from my PL/SQL editor. The added advantage is that to the untrained eye, it would seem like I was hard at work while I secretly check out the scores! Of course, for all of these the database needs to be able to connect to the Internet.

Many PL/SQL editors allow you to refresh a query automatically at fixed intervals, so you can convert this script into a function and then use it in a query. That way, you do not even have to execute the script every time you want to refresh the scores.

So without much ado, here is the code which is pretty self-explanatory and has lots of room for improvement.  Note that the team whose score you want goes into the v_my_team variable, partial names would also work.

set serveroutput on;
DECLARE
 l_http_request   utl_http.req;
 l_http_response  utl_http.resp;
 l_clob           CLOB;
 l_text           VARCHAR2(32767);
 v_count          NUMBER          := 1;
 v_score          VARCHAR2(100);
 v_score_my_team  VARCHAR2(1000)  := NULL;
 v_my_team        VARCHAR2(100)   := 'India'; --'ALL' --The team whose score you want or 'ALL' for all ongoing matches
BEGIN
 -- Initialize the CLOB.
 dbms_lob.createtemporary(l_clob, FALSE);

 -- Make a HTTP request and get the response.
 -- We are using the RSS feed from Cricinfo
 l_http_request  := utl_http.begin_request('http://rss.cricinfo.com/rss/livescores.xml');
 l_http_response := utl_http.get_response(l_http_request);

 -- Copy the response into the CLOB.
 BEGIN
   LOOP
     utl_http.read_text(l_http_response, l_text, 32767);
     dbms_lob.writeappend (l_clob, LENGTH(l_text), l_text);
   END LOOP;
 EXCEPTION
   WHEN utl_http.end_of_body THEN
     utl_http.end_response(l_http_response);
 END;

  -- Convert the CLOB to XMLType and extract scores the from the XML
  WHILE XMLType(l_clob).existsNode('/rss/channel/item[' || v_count || ']') = 1
  LOOP
    v_score := XMLType(l_clob).extract('/rss/channel/item[' || v_count || ']/title/text()').getStringVal();
    --Store all scores, if 'ALL' is specified
    IF UPPER(v_my_team) = 'ALL' THEN
      --If this is the first value, do not use a comma
      IF v_score_my_team IS NULL THEN
        v_score_my_team := v_score;
      ELSE
        v_score_my_team := v_score_my_team||', '||v_score;
      END IF;
    ELSE
      -- If the score is for your team, store it in a variable
      -- Providing a value of 'Australia' to the variable v_my_team would fetch scores involving both Australia and Australia A.
      -- We will display all matching scores for such cases.
      IF UPPER(v_score) LIKE '%'||UPPER(v_my_team)||'%' THEN
        --If this is the first value, do not use a comma
        IF v_score_my_team IS NULL THEN
          v_score_my_team := v_score;
        ELSE
          v_score_my_team := v_score_my_team||', '||v_score;
        END IF;
      END IF;
    END IF;

      v_count := v_count + 1;
  END LOOP;
  -- Display comma-separated scores
  dbms_output.put_line(NVL(v_score_my_team, 'Sorry, there are no ongoing matches involving '||INITCAP(v_my_team)));

EXCEPTION
 WHEN OTHERS THEN
   utl_http.end_response(l_http_response);
   -- Release the resources associated with the temporary LOB.
   dbms_lob.freetemporary(l_clob);
   dbms_output.put_line (utl_http.get_detailed_sqlerrm);
   dbms_output.put_line (SQLERRM);
END;

Notes:

  1. The database Access Control List should allow network access for this to work.
  2. In scores involving two innings, for example 129/10 & 13/2, the ampersand is not displayed properly.
  3. The scores can also be fetched directly through the query below provided the XML file fetched is not more than 2000 bytes. This is because the REQUEST function used in the query returns up to the first 2000 bytes of the data retrieved from the given URL.
SELECT * FROM (
SELECT extractvalue(column_value, '/item/title') score
FROM TABLE(XMLSequence(XMLTYPE(utl_http.request('http://rss.cricinfo.com/rss/livescores.xml')).EXTRACT('/rss/channel/item')))
)
Follow

Get every new post delivered to your Inbox.

Join 32 other followers