Generating Workflow Files Part 2

One of the services that I’ve done for a while now through my company, is running design and implementation reviews – spending time with customers getting to understand what they are expecting their Workflow system to do, and then looking at their workflow functional and / or technical design document, or their code that has been written, and providing a detailed analysis of the strengths, weaknesses and opportunities for improvements.  One thing that comes up time and time again is that generally the code that has been written to support a Workflow has been written without reference to the Workflow build standards that Oracle wrote some years ago – these have been out there for some time now, but here’s a brief list of what I normally find:

  • Procedure signature does not conform to the recommended signature from Oracle;
  • Use of hard-coded values for function mode and results rather than using WF_ENGINE constants;
  • No logging, or if any is in place, it doesn’t use the Workflow supplied logging package;
  • Missing or incorrect error handling.

One thing that this has really got me thinking was about how great it would be if there was some kind of framework that made it easier to write good Workflow code.

So, I sat down and put together a utility to provide well-formatted code, which provides a framework that you can plug in your specific logic.   The most important thing that a workflow developer should be focusing on is getting the flow of the process right – here’s where the developer should be devoting their efforts

  • Determining what activities are needed in the process; 
  • Deciding what level of granularity is needed
    • Should you have an activity that does lots of processing, a sub-process, or a couple of activities in one process?
    • Should you build a series of activities that perform simple steps, or use one activity which has a series of IF conditions in it?
    • etc.
  • Putting the right activities in the right order;
  • Determining error conditions;
  • Determining revisit conditions;
  • Deciding whether business events can and should be used;
  • Deciding whether to defer the process or not.

Instead of which, developers tend to be spending time on writing the PL/SQL to support the Workflow, and not enough time focusing on getting the process(es) right.

I wrote a utility which is designed to help shift this effort from PL/SQL developer onto becoming a Workflow developer.  If you install the package attached to this post, a PL/SQL package is installed which can then be used to generate code to support your Workflow.  You should define the Workflow process, and this code will then look for a specific Item Type and pick up the PL/SQL activity which you have defined and generate code for you.  If you have left any of the activities blank, then the code will create a PL/SQL package with the name of your item type, and procedures within the package named as the internal name of the activity.  The code adds logging to the procedures, error handling, and if the activity uses lookup codes, it will also generate the results at the end of the code for you.

It can also update your Workflow for you, if you set it to.  So for each Activity where you have not defined the Activity function, the code will generate the code for you to amend to support the business logic, and then update the Workflow with the name of the package and procedure which has been generated to support the process.

The code is copyright TS Fifteen Ltd (the parent company of, but I’m prepared to make the code available free for use.  If you install the code, then it must include the module header which is supplied with the code.   If you make any changes to the code, or have any suggestions for extra functionality, please contact me or add comments below.  I will be writing a fuller user guide to add to the module, but here’s the current version.

Package Specification

Package Body

Generating Workflow Files

Some years back, there was an internal consultation in Oracle Europe about whether we could write something to generate Workflows instead of using the workflow builder. One of the main flaws with the builder is that it only runs on a Windows machine – what do you do if you are running Linux? The only thing to do is to use an emulator to run the builder. Unless Oracle move the builder to something platform non-specific, such as Java, then this is always going to be a problem.

Sadly, this didn’t really come to anything – there wasn’t enough interest or time to put people on it, and unless a customer was paying, then it was never going to get done. We did look at writing something in XML that could then get loaded into the workflow tables, but it became quite tricky. It’s reasonably straight forward to work out the insert statements that you would need to call to load the core definition into the workflow datastore, since the tables identify which activity comes next in the process, and at one stage we did have something running that would allow this bit to work.

The part of the load that we found particularly difficult was the geometry and layout, which we couldn’t really work out in the time that we had available was the co-ordinate system for laying out the diagram correctly (something that is important for usability).

In the end, we gave up, since we also needed to come up with some mechanism to convert a flow from whatever mechanism people were starting with (Visio, Word, Powerpoint diagram, any third party tool, anything else!) into XML, to convert into an Oracle proprietary format, and since no-one had actually asked for it…

If anyone out there has actually worked on this and got it working, I’m more than interested in learning how it was done (or even why!), but there were some ideas that came out of the discussions – more about that in the next post though!

Keep reading,


DBMS_OUTPUT limitations

I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that (prior to 10g) you can only have 1000000 characters in the output. I know that that sounds a lot, but if you’re debugging complex workflows (with lots written out to the screen), and processing multiple flows in the same transaction, then this can easily be blown.

So I “googled” (yes, it’s a verb!) the error that I was getting thrown, and (as ever) found this thread on AskTom.

Tom has provided a new PL/SQL package which replicates pretty much what DBMS_OUTPUT does by storing it in a PL/SQL table, and then has a view on top of it to retrieve (and delete) the data from the table. You can then just select from the view to get the output back.

I’ve copied the code into the attached SQL file, just for ease.


SQL script

Who’s logged in?

I’ve just been asked to come up with a quick script for our DBAs to let them know who has been using the system recently. They want something that they can use to pick up the recent eBS users and their email addresses, so that they can let them know when the system is about to be bounced or shut down.

So, here’s the SQL:

SELECT DISTINCT ppf.full_name
,               fu.user_name
,               NVL(ppf.email_address,fu.email_address) email_address
FROM   per_people_f   ppf
,      fnd_user       fu
,      fnd_logins     fl
WHERE  fl.start_time   > SYSDATE - 2
AND    fu.user_id       = fl.user_id
AND    ppf.person_id(+) = fu.employee_id
AND    fu.user_name NOT IN ('SYSADMIN', 'GUEST')

Note, it will only pick up recent eBS users – it doesn’t look at database users who are logged in, so if there is other access to the system then you need to be aware that this isn’t going to be a definitive list. Good starter for ten though.

11i Sandpit

I was chatting with someone on a current project this week, who was asking about the best ways to get into Oracle eBusiness Suite. He was just after some kind of sandpit environment to play about in – he’s fairly technical but hasn’t got much Oracle exposure, and almost no eBS exposure.

So I said “oh, you want to have a look at Solution Beacon – they’ve got some instances.” His immediate response was “oh, I can’t remember the link, but I’m sure that I’ll read about it in your blog…”

So, here it is!

Solution Beacon have a few 11i and R12 eBusiness Suite systems running, that you can play about in – they refresh them periodically, and there’s no database login, but for finding your way around Oracle Apps, it’s a good starting point – plus you don’t need to install your own sandpit area to play in!

The links to the 11i environments can be found here.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.