September 26th, 2008 Matt
A question that comes up regularly is what’s the best way to respond / close / abort notifications in bulk. I’ve recently been writing a pretty horrendous workflow for a government organization, and when I was developing the code, needless to say there were a few errors that came out.
The workflow that errored raised the standard error handling workflow, so I ended up with somewhere in the region of 2000 notifications being sent to SYSADMIN. Once the code was fixed, I just wanted a quick way to respond to all of them. Depending on the patch level of the environment, Oracle have introduced a way of bulk responding to notifications via the worklist – however, having to click through the 2000 notifications to select each one is a pretty tedious task 
The WF_NOTIFICATION package contains everything that you need to do to respond to notifications programatically – I’ve used it a lot when responding from Portal and from forms as well. There are two different approaches, depending on whether the notification requires a response or not.
FYI notifications are the easiest – all you need to do is call WF_NOTIFICATION.Close, passing in the notification ID. Since it’s that easy, I’ll credit you with the ability to be able to write your own code to do it!
Notifications which require a response are slightly harder – you need to set the response in the notification, and then tell the engine that the notification has been responded it. Firstly, call WF_NOTIFICATION.SetAttrText to set an attribute called "RESULT" to the value that you are sending back. This needs to be the internal name of the lookup code that you are using. Once you’ve done that, you just need to call WF_NOTIFICATION.Respond to close the notification.
A code sample to retry all open notifications sent from the standard error workflow to the SYSADMIN user is:
BEGIN
FOR i IN ( SELECT notification_id
FROM wf_notifications
WHERE recipient_role = 'SYSADMIN'
AND status = 'OPEN'
AND message_type = 'WFERROR'
AND message_name = 'RESET_ERROR_MESSAGE' ) LOOP
wf_notification.setattrtext ( nid => i.notification_id
, aname => 'RESULT'
, avalue => 'RETRY' );
wf_notification.respond ( nid => i.notification_id );
COMMIT;
END LOOP;
END;
/
Posted in Oracle, Technical | 1 Comment »
September 23rd, 2008 Matt
When I run my training courses, one of the main discussion points that come up is what level of granularity should you use when you are building a Workflow process. This is particularly important if you are working with a non-technical business analyst – what appears on a Visio diagram as a simple two step process might well expand into a ten stage workflow process.
Here’s an example that I have used in the past which I’ll share here. A business analyst produces a business process flow about how to search the database for records matching a certain criteria. Once the search has been complete, there are some post-searching functions which need to be undertaken. At the simplest level, here’s the Workflow process:
Bad design!
Whilst there is nothing wrong with the process at the most simple level, there is nothing right about it either! Let’s make the process a bit more complex by adding some extra business rules. Firstly, a date range is provided which may cover data that has been captured within the database or not – the search routine should validate this. Secondly, there are different types of search which need to be performed, based on the inbound parameters. Thirdly, if the search cannot be completed automatically, then the search will need to be performed manually. Finally, there needs to be some external processing after the search, which needs to be communicated by updating a record in the database.
So, how best to implement the new business rules – in the existing code, or expand the process to accommodate the new logic?
No prizes for guessing what I’m going to suggest here – you should extend the Workflow process and separate the business logic into separate Workflow activities. Workflow is designed for this – including complex (or even simple) logic within the process makes more sense than embedding it hidden in PL/SQL. Here are a few reasons for doing it this way rather than in PL/SQL:
- Reduced complexity of the solution
- Easier to develop separate activities than one large procedure
- Easier to expand the process in the future by changing th Workflow rather than PL/SQL
- Easier to change the order of processing in a Workflow than in PL/SQL
- Changes to the Workflow require less testing than in PL/SQL, since the flow is obvious
- More visible solution to provide an overview of the business process
There are more reasons than these, but the main ones are the first and last – the lower complexity solution, which provides the best visibility of the process has to be the best solution. So here’s an example of the same process expanded:
Good Design
Just to go back to the start of the article, I’ll reiterate – conceptually speaking, there is nothing wrong with building a workflow in the manner of the first diagram. However, it completely defeats the object of using a Workflow engine (why bother with the “complexity” of three activities – why not just have one if you want to simplify the process? Indeed why use a workflow engine at all, if this is the approach you want to take when building the solution???)
In addition to this article, you should read the entry on storing information in item attributes here .
Posted in Oracle, Technical | No Comments »
September 18th, 2008 Matt
I’ve just read a post on the Workflow forums in Metalink asking about what the SQL is to display the history at the bottom of a Notification – there is nothing in the WF_ITEM_ATTRIBUTE_STATUSES_H for the history, so where does it reside?
The history is recorded as a comment in the WF_COMMENTS table, which you need to join to from the WF_NOTIFICATIONS table to get the history. However, the optimum was to get the history is to use the API (as ever!) that Oracle have provided: WF_NOTIFICATION.GetComments or WF_NOTIFICATION.GetComments2.
Since the original question was looking for a SELECT statement to retrieve the contents, I wrapped this in a function which can be called with the notification ID and returns the history either as plain text or as HTML that can then be embedded somewhere:
1 CREATE OR REPLACE FUNCTION get_hist ( p_nid IN PLS_INTEGER
2 , p_disp_type IN VARCHAR2 DEFAULT WF_NOTIFICATION.doc_text )
RETURN VARCHAR2 AS
3 v_hist VARCHAR2(32767);
4 BEGIN
5 Wf_Notification.GetComments2 ( p_nid => p_nid
6 , p_display_type =>
NVL(p_disp_type,WF_NOTIFICATION.doc_text)
7 , p_hide_reassign => 'Y'
8 , p_hide_requestinfo => 'Y'
9 , p_action_history => v_hist );
10
11 RETURN v_hist;
12
13 END get_hist;
14/
Then you can just call it in a select statement (or in PL/SQL), e.g.
APPS@DEV on 18-SEP-2008 10:43:34> select get_hist(1574024) from dual;
GET_HIST(1574024)
------------------------------------------------------------------------------------------------------------------------
Action History
Num : Action Date : Action : From : To : Details
1 : 17-JUL-2008 12:03:45 : Submit : SYSADMIN : Searle, Matthew :
2 : 18-SEP-2008 10:21:00 : Request Information : Searle, Matthew : MATT2 : Hello
I’m not going to show an output of the HTML, but you can retrieve it by calling GET_HIST(<nid>,’text/html’) instead of the default parameter.
Matt
Posted in Oracle, Technical | No Comments »
September 15th, 2008 Matt
I’ve been working with a client recently where there were a few specific problems that needed to be resolved – the best kind of consultancy project as far as I am concerned.
One of the problems that they were facing was that on a custom report, if the units of measurement were different between the sales order and the item definition, then the units were not being converted properly. The report produces custom invoices for the client, so this is a key requirement – recently they invoiced someone for 18 million euros by mistake since the calculation was completely wrong! For example, someone orders 17 rolls of paper at £42 per roll, which is entered into standard OM. The item is actually stored in the system in metres – there are 30 metres on a roll. Unfortunately, there is no direct conversion between a roll and a metre – the conversion is rolls into millimetres, millimetres into metres. A quirk of bad programming meant that instead of being invoiced for 17 rolls at £42 a roll, the end-client was invoiced for 510 rolls at £42 (17*30 metres = 510). So only an extra £20,000 or so! And that was just one order line on one order…
The source of the program was that the original developer wrote their own mechanism for converting between units of measurement – if there was no direct conversion, then the code returned a ratio of 1:1. So converting rolls to metres meant that the "default" value of 1:1 was calculated, resulting in our 510 metres.
I spent quite some time tracking the problem down – the main problem facing me was that I thought "there must be a standard API for converting units of measurement". And there is. The problem is that I couldn’t find it anywhere I looked – a few reference manuals, Google, Metalink all turned up a blank. So as a last resort, I opened SQL*Developer and searched for packages containing "%UOM%" and found the right one – PO_UOM_S which has a function to convert between units of measurement for a given inventory item.
So, if you ever need to manually convert between units of measurement, hopefully I’ve saved you some time and effort by listing the package here.
Posted in Oracle, Technical | 1 Comment »
September 10th, 2008 Matt
I was rooting through the PL/SQL fora on OTN recently, and found a couple of interesting posts about testing.
Firstly, I found this one entitled " Is testing really matter at PL/SQL"
The gist of the post is "do I need to bother testing my PL/SQL?" The OP asks the question because he’s finding it hard to write the tests to do it! It’s an interesting approach – maybe once the code is complete you should just install it, pack up and go home.
Some years back, I worked with a client where this was exactly their approach – the code compiles successfully, therefore there is no need to actually go through the hassle of having to test it as well! Needless to say, when they went live, there were a number of issues, since it didn’t perform as they expected, both in terms of what it did and the performance of the code.
Not an ideal solution, by any means! It may be difficult to do (I’m in the middle of running some complex tests at the moment, which take half an hour to set up and at least an hour to execute each one of the 50 tests!), but if you don’t test it, then unless you are a super-programmer, then it’s not going to work!
The second post looked quite a good one – asking about calling procedures using dynamic SQL. However, in the middle of the thread, comes this post , where the poster suggests that because since "procedures are not formed in an object oriented fashion", it makes it difficult to test them, and so really you shouldn’t bother with testing!
Again, a novel approach to software development – it’s not object oriented, and it’s a bit tricky to test, so we don’t really need to do this!
Hopefully, these are the exceptions to the rule – if it ain’t tested, it shouldn’t be going anywhere near production.
Posted in General Computing, Technical | No Comments »
September 5th, 2008 Matt
Some time back, I wrote this post about where should people ask for help for Workflow development and advice – I just wanted to remind people about it
The main reason is that recently, I’ve been inundated with a series of emails from people that I don’t know asking me for help with specific Workflow-related problems. Whilst I’m flattered that people are asking my opinion and advice on these things, one of the reasons that I created the WorkflowFAQ forum was so that people could ask questions there and share both the problem and the solution with others. Plus, by sharing on a forum, there may well be other people who have differing opinions of how / why you should do things in a certain way, which I always welcome, and we then build up a repository of information that is easily searchable for anyone. I will try to answer questions that I receive, but bear in mind that I spend less time on email than I could do (which is a deliberate ploy), I’m also writing my next book at the moment, as well as working full-on with a pretty demanding client on an incredibly interesting project. If you post your question on the forum, then it may well be that you get a response quicker than by sending me an email – if I spot something that I disagree with on the forum, I’m usually pretty quick to correct it / make my opinion known.
Of course, don’t let this disuade you from asking questions via email, but it’s something to bear in mind – also, bear in mind that I am more likely to respond if I know who you are rather than an out-of-the-blue email that begins “I’m having some problems with my Workflow system…”
Finally – if you want a quicker response / more formal advice / more detailed explanation of things, I can always make some time available for the right price
Matt
Posted in General Computing | No Comments »
September 3rd, 2008 Matt
A while back, I wrote this post about how to make sure that you ask for help in the right place.
I then remembered reading another excellent example of how not to ask for help. Having found the right place to ask questions, there is (or should be) a certain etiquette about how to ask for help / respond to responses and so forth. I think that this post on OTN shows exactly how NOT to do it!
In my search for guidelines for posting on forums, I found this excellent article in Shay’s blog.
If you ever post on OTN, Metalink, WorkflowFAQ, or any forum for that matter, it’s well worth a read – and even better if you follow the advice / guidelines!
Posted in General Computing, Non-Oracle | No Comments »
