How do I link TRANSACTION_ID to NOTIFICATION_ID and ITEM_KEY?

A long while back, there was a question about linking the transaction ID to a notification ID and item key within Oracle HR – I wrote the blog post below shortly afterwards, but somehow it never got published.  So, somewhat later than I’d planned, here’s the answer!

The original question can be found here and reads

Hello all,

how can i link Transaction_id in HR_API_TRANSACTIONS table with NOTIFICATION_ID and ITEM_KEY in wf_notifications table?

Thanks
KK

There are a couple of ways that you can link the different pieces of information – it depends on what your starting point is, though.

Firstly, the HR_AMEUTIL_SS package contains functions to find the item type and the item key from the transaction ID:

SELECT hr_ameutil_ss.get_item_type(transaction ID)
,      hr_ameutil_ss.get_item_key(transaction ID)
FROM   DUAL
/

From here, you can then find the notification ID by looking at the WF_NOTIFICATIONS table, eg.

SELECT notification_id
FROM   wf_notifications
WHERE  message_type = hr_ameutil_ss.get_item_type(transaction ID)
AND    item_key     = hr_ameutil_ss.get_item_key(transaction ID)
/

If, however, you have the notification ID and you want to get to the transaction ID, then you need to look at the Workflow tables to find that information:

SELECT wiav.item_type
,      wiav.item_key
,      wiav.number_value
FROM   wf_item_attribute_values wiav
JOIN   wf_notifications         wfn  ON (    wfn.message_type = wiav.item_type
                                         AND wfn.item_key     = wiav.item_key )
WHERE  wiav.name           = 'TRANSACTION_ID'
AND    wfn.notification_id = <i>notification ID</i>
/

The number value for the item attribute will contain the transaction ID and you can then work from there to do what you need.

Apologies it’s taken over a year for me to get round to publishing this one – there may well be some more posts being published which I never got round to making public soon!

Can I change the display name for an attachment link?

Some time last year, I was asked about adding different attachments to notifications – something that has been discussed in the forum a number of times. This time, I was asked about whether it was possible to modify the display name, so that each attachment link contained the name of the file that had been attached:

Hello

I have a requirement to send a notification with attachment but attachment name should be attachment name. Is there any way to change the display name of the Attachment?

This is something that was also asked in the forum in June last year – where there are multiple attachments (or even if there is a single attachment), users like to see the details of the attachment rather than a generic “attached file” link, for example.

When the notification is built, any attached documents are included as attributes to the message.  The link to the attachment is created using the display name for the attribute in the text.  It is possible to modify the display name of an attribute programmatically (although this is NOT supported by Oracle since it would be a direct table update), BUT attributes aren’t versioned.  There is only one definition of an attribute within the process definition, so if we changed the display name, this would impact EVERY notification that uses the same message.

So, the quick and simple answer is “No”, and hopefully this quick post explains why you can’t.

How do I restrict the list of users to reassign to?

Standard Workflow functionality provides the users with the ability to reassign a notification to another user or role (unless you specifically tell the process not to).  However, if you aren’t careful, then everyone in the Workflow directory service is included in that list, which can produce some problems.

Firstly, the end user may not know which of the users they should be using – if there are multiple John Smith’s in the organization for example.  Or if there are two users called “Faisal Mohammad” and “Mohammad Faisal” which one is the right person to be receiving the notification?

Secondly, the business may want to restrict who the notification can be reassigned or transferred to.  For example, if the notification is one requiring an approval, then there may need to be some logic included to ensure that only people in a specific department can be used as the new recipient.

Thankfully, there is a mechanism included in the product which allows you to restrict the reassignment list of values using an attribute on the message.  Here’s how:

Firstly, you need to determine your list of new users and put them all in a Workflow Role.  This could be a new ad-hoc role that you have defined specifically for this one instance of the process, or you may already have a suitable role defined as standard (e.g. if you only want to be able to delegate to users who have been assigned the “UK Super HRMS Manager” responsibility.

Once you know the name of the role which have all the users assigned to it, then you need to store that name in the Workflow process in an item attribute.

The final step is to define an attribute on the message that you want to restrict the list, called #WF_REASSIGN_LOV and synchronise that with the item attribute holding the value of the role.

Once the new definition of the process has been deployed to the database, the messages will now check the value of the attribute and the list of users who can now be set as the new owner will be restricted accordingly.

Can I use a profile to conditionally send a notification via email?

Another one from the inbox – is it possible to send a notification via email based on a profile option (or some other condition)?  The requirement is that notifications should always appear in the Workflow work list, but for some notifications, there should be an additional copy sent via email.  If the condition is not met, then the notification should only appear in the work list.

Firstly, let’s look at how notifications are displayed.  Every notification is automatically available in a Workflow worklist – there is no way to stop that happening.  Additionally, as an “optional extra”, you can send the same notification via email, based on the following conditions:

  • The Workflow Mailer must be running
  • The Workflow Mailer must be configured correctly
  • The recipient role has an email address configured for it OR (if sending to a role which has multiple users assigned) the individual recipients have an email address configured
  • Either the global preference is set to receive email (and there is no over-riding preference set by the user) OR the user preference has been set to receive email

If any of those are not happening in your system, then the notification isn’t going to be sent as an email, regardless of what you want to happen.

So, it looks as if the only thing you can do is to either enable or disable the sending of an email.  BUT, there is an alternative which doesn’t quite meet the requirements, but might do the trick – it depends on what the business requirements are about whether the notification should be emailed or not.

If the business are concerned about sensitive information being emailed out, then there is an attribute which can be used for the message called #WF_SECURITY_POLICY which determines whether the notification should be sent by email or not.  If this is set to NO_EMAIL then an email will be generated which tells the user that they need to log into the application to be able to read it.  If that is what the business really want, then this is the way forward – if not, then adding the attribute may make the situation worse rather than better.

How to populate the “From” field on a Workflow notification

Another quick one, but it pops up in fora and my email fairly regularly – when a notification is sent to a user / role, there is nothing in the “From” field to indicate who it actually came from. Sometimes, it’s there (in a seeded notification) and sometimes it’s missing, so how do we put something in there?

Firstly, the “from” needs to be defined in the Workflow Directory Service as a role – if you just want to put in “Bob Smith” for example, then you need to make sure that Bob exists in the directory. If not, then you need to create an ad-hoc role to use instead.

Once you have identified the role (and it may already be in your process) then you really should store that in an item attribute. Then you need to define an attribute on your message called #FROM_ROLE. You can set this to a constant, but I would recommend setting it to synchronize with an item attribute so you can easily change it later if necessary. Set the value of the attribute to the role that you want as the from, and save it.

Now, when the notification is sent out, the value will display the display name for the role. As a warning, though – messages aren’t versioned in the database, so you need to ensure that your changes (if you are using a new item attribute to synchronize with, for example) remain valid for any processes which are already running.

AME Part Four – Customizing AME in iRecruitment

In the last blog post, I detailed the functional configuration required to setup a new user with AME administration privileges. Now that the user is ready, in this post I will be going through a quick example of how AME can be used within iRecruitment.

In this example, we will implement the following approval rules for creating a new job vacancy within iRecruitment:

  • When a vacancy is created, the vacancy must be approved by the supervisor of the person creating the vacancy.
  • Once the supervisor has approved the vacancy, it must then be sent to a different user for final approval.

In my example, I have picked two random users within Vision – BJOSEPH (Joseph, Brian) and his supervisor, RJAMES (James, Robert).

The first rule (that the vacancy must be approved by the supervisor) is standard behaviour within iRecruitment, so there is nothing that we need to do with that. So – what do we need to do to meet the second requirement?

Firstly, we’ll create a new static AME Approval Group and add the Recruiting Administrator to that group. An Approval Group can return either a static or a dynamic list of users. Once we have created the Approval Group, we will then add the Group to the AME Rule that is used by the standard Vacancy Creation process.

Before we start, run through the standard Vacancy Creation process and see who the notification is sent to. Log in as the first user to create the vacancy – in my example, I will use the BJOSEPH user. Using the “iRecruitment Recruiter – VC” responsibility, navigate to the iRecruitment Home page, and click on the “New Vacancy” link in the sidebar:

iRecruitment Home Page

In the “New Vacancy” window, enter a name for the vacancy and then click on the “Review” button. At this stage, we could enter more information about the vacancy, but since this is just a quick test to see the default functionality, there is nothing additional required here. The bottom of the vacancy summary shows the default approver that has been found – the supervisor of the person creating the vacancy:

Default behaviour - supervisor is the one approver

As I said above – the default functionality meets our first requirement, so all we need to do is to add the additional requirements. Firstly, we need to find out what AME Transaction Type is used by the Vacancy Creation process. To do this, we need to examine the function for “Irc Vacancy Details – Create”, as shown in the screenshot below:

Function definition for Vacancy Creation

Navigate to the “Form” tab and open the Parameters to see the parameters which are passed to the form:

Function parameters for create vacancy

The parameter that we are interested in is “pAMETranType=IRCVACAPPROVAL”. Now that we know the name of the internal name for the transaction type (or Transaction Type Key), switch responsibility to “Approvals Management Administrator” – I will switch user completely to the AMEADMIN user we created in the last post. Navigate to the Admin Dashboard and query the IRCVACAPPROVAL Transaction Type Key:

Searching for the transaction type using transaction type key

Now that we know the name of the Transaction Type (iRecruitment Vacancy Approval), switch responsibility to “Approvals Management Business Analyst” and enter the Transaction Type in the right hand side in Step 1 of the “Approvals Setup Process”:

Approval Process Setup panel

If you click on the “Rules” link, you can view the existing approval rules in place. Click on the link and then locate the “iRecruitment Create Vacancy” rule in the list of rules. When you open the rule, you are presented with the default rule:

Default rule for vacancy create

Return to the dashboard, and we’ll create our custom Approval Group to add to the Rule. Under “Step 2” of the Process Setup, there is a link to “Approver Groups”. Click on the link to navigate to setup a new Approver Group, and then click on the “Create” button. The screenshot below shows the values that I have entered for my Approver Group:

Creating a new Approval Group

The “Approver Type” can be an Applications User (FND User), a group of people (Nested Group) or an HR person (HR People). Multiple users can be added to the approval hierarchy here – I have only added one person, a user called MATT who will act as the final approver for vacancies. Click on the “Apply” button and then return to the dashboard to add this new Approval Group to the rule.

As before, click on the “Rules” link on the right hand side of the screen and locate the rule for iRecruitment Create Vacancy. Click on the update icon to open the rule details. At the bottom of the page, you will see the default action type, which seeks approval from the supervisor of the person creating the vacancy. Click on the Add Action button to add our Approval Group to the rule, as shown below:

Adding the new Action Type

If the “approval-group chain of authority” option is not available, this is because it has not been enabled. Click on the “Setup” tab and choose the “Action Types” submenu. You will be presented with a list of suitable action types – typically only the default “Supervisory level” one will be enabled. Click on the “Use Existing Action Type” button and navigate through the list of available types until you see the “approval-group chain of authority” type. Tick the box next to the action type and then click on the Continue button, then click on the Finish button. Now return to the rule and add the Approval Group as above.

Once you have added the action type, click on the Apply button. That’s all there is to it – now let’s check it again by logging out and creating a new vacancy. The final screenshot below shows that the new vacancy will be sent to MATT for approval as well as the originators supervisor:

Testing: a new vacancy requires 2 approvers

AME Part Two – Components of AME

In the last blog post, I provided a quick overview of what AME is and what some of the benefits of using AME are. In the second post in my series on AME, I’ll be looking at the different components of AME and how they interact.

Before you start using AME, it is important that you understand exactly what the different parts of the Engine are, what you need to configure, and how they all work together. There is frequently a requirement to modify the seeded components or to create new ones for an organization, in order to develop their business rules.

Transaction Types

A Transaction Type defined the type of transaction for which the business rules and approval routes will be based. This is an eBusiness Suite transaction, for example creating a new vacancy in iRecruitment or creating purchase orders. There are many different Transaction Types seeded within the application to meet the requirements of the most common transactions which occur within the eBusiness Suite that would require an approval to take place. If you are integrating a bespoke application with AME, then new Transaction Types can be defined within AME. However, the creation of new Transaction Types is not a straightforward task, and Oracle discourage this because of the amount of programming effort that is required to integrate a new Transaction Type with AME.

Attributes

Within AME, Attributes can be defined as business variables which represent the value of an element in a transaction. For example, in the case of an invoice approval, a typical attribute would be the supplier name, or the invoice value. When defining the business rule, the attributes that are available to you will help define the rule itself and it’s implementation within AME. The reason for this is that the value of an Attribute for a transaction is usually evaluated as part of the rule in order to determine whether the condition has been met or not – for example, is this invoice worth more than £500? Within AME, Attributes can be created as static (where they have a constant value which remains the same for every transaction associated with the Attribute), or as dynamic (where they use a SQL query to determine the value of the Attribute at run time). Most Attributes that are used within AME are dynamic.

Within AME, there are different types of Attribute available to you:

  • Strings can be alphanumeric and have a maximum length of 100 characters.
  • Numeric attributes can have any numeric value that is acceptable in PL/SQL, including decimals and negative numbers. When using a numeric Attribute within AME, the number must be converted to a canonical form – within the FND_NUMBER package there is a function NUMBER_TO_CANONICAL which will do the conversion for you.
  • Currency Attributes are used when the organization is using multiple currencies. This allows Oracle to perform currency conversions when retrieving the Attribute value. If currency Attributes are used, the SQL query which retrieves the value must include the numeric column, currency and conversion method. Additionally, any AME conditions which use currency Attributes must include a condition for EACH CURRENCY that the transaction might use.
  • Boolean Attributes can only have a valid value of true or false. Any dynamic Attributes which are defined as Boolean MUST return one of these two states – NULL is not a valid value in an AME Boolean Attribute. When using Boolean Attributes, AME provides a format string which should be used – AME_UTIL.BooleanAttributeTrue and AME_UTIL.BooleanAttributeFalse.
  • Date Attributes are commonly used within AME – for example the invoice date, shipping date, and so forth. Again, AME is particular with the format and requires all date Attributes to be in the format YYYY:MON:DD:HH24:MI:SS. There is a format string again provided in the AME_UTIL package – AME_UTIL.versionDateFormatModel which can be used to return a date in the required format.

Read more

An introduction to AME

Recently, I’ve been asked from a number of places about Oracle’s Approvals Management Engine, or AME, so in the next few blog posts, I’ll be taking a look in more detail about what it is and what it can do for you and your organization.

AME is an “easy” way for a business analyst / functional consultant to define business rules for transaction approvals within eBusiness Suite. Not all applications modules support AME, however, in which case you are back to the age-old bespoke method of identifying approvers and including custom logic to ensure that the right people are included within the approvals process. Note that I have used inverted commas for the word “easy” – AME provides the opportunity to include business rules which can vary widely in complexity.

In this post, I’ll be looking at the components of AME which are required for configuration, and in later posts I’ll move onto some practical examples from within iRecruitment and Financials.

AME Basics

As I said earlier, the purpose of using AME is to implement specific business rules within the framework of the application to manage the approval of a transaction. This can always be done outside of AME (and for some modules, this is still a requirement!), but if the approvals process can be captured within AME then this makes for a more straightforward implementation which (theoretically at least) is easier to maintain over the long-term. When approval is sought, AME uses Workflow notifications to inform the approver that they need to action the request, and to capture their response.

AME (as I said earlier) allows you to implement rules of differing complexity – for example, any invoices which are over £500 must be approved by the requester’s supervisor; all vacancies created in iRecruitment must be approved by Bob in HR; and so forth. Many organizations, however, have much more complicated – for example, any invoice which is matched to a purchase order and has any lines which are over £5000 and include heavy machinery must be approved by the Operations Manager and his immediate supervisor (or the next available supervisor with the appropriate authorization limit). The flexibility that AME brings means that the same method can be adopted for any of these requirements.

So, why bother with AME?

You can already build all of this through custom code – and in versions of the eBusiness Suite that were availably prior to AME, that is what most organizations did. However, there are some clear advantages of managing the approvals process using AME over using bespoke software.

Firstly, in many cases, business rules can be defined and implemented without the need for any code. Now that’s a big one, so I’ll say it again – IN MANY CASES, BUSINESS RULES CAN BE IMPLEMENTED WITHOUT THE NEED FOR ANY CODE. Less code means less things to maintain, and less things to go wrong (assuming that the functional configuration is correct and the code is always the thing to go wrong, but we’ll not go there in this post!!).

Secondly, if there are already hierarchical structures defined in the application, then AME can leverage these as standard – again, there is no need to write custom code to find the next available approver, since AME does it automatically.

Another advantage of using AME is that if there are specific requirements to route an approval to a specific group of people or an individual, then AME provides the person making the functional configuration with the ability to setup specific hierarchies or approval groups which can be used to determine where to route the approval.

The last main benefit (and it’s a good one!) is that AME has the ability to respond to any changes in the approvals process whilst the process is still going on. This includes, for example, a change in the approvals hierarchy (which can be included fairly easily within custom code) but also if the business rules themselves are modified during the process, or even if the values of the transaction themselves change during the process – now that’s clever! Every time an approver for a transaction responds with an approval, AME checks the rules as they stand at that point in time, and rebuilds the approver list to determine who the next approver should be. So, let’s take the simple example above where if the invoice is over £500 then the supervisor must approve the invoice. An invoice for £1500 is created, AME checks the business rules and notifies the supervisor. The business analyst then decides that if the invoice is over £1000, it requires three levels of approval, and so makes the change within AME. When the supervisor responds to the notification, AME rebuilds the approval list using the new rules, identifies who the next approver should be, and then routes the transaction to them for approval.

That’s the end of my very quick overview of what AME is and why you should use it – in the next post, I’ll go into the components of AME and how they work together.

Bulk updating workflow users

Recently, there was a question asked on Metalink about how to update a large number of user preferences in one easy step. The original question was to see how to turn off the email preference for users, in an environment that had been cloned from production.

There are a couple of ways that you could do this, but my preferred way is through the WF_DIRECTORY APIs – I’m currently working with TCA a lot, and so the importance of using the supplied APIs is really being hammered home!

I’ve attached a script which loops through the records in the WF_USERS table, and updates the notification preference to ‘QUERY’ for all the records it finds – if you want to change different preference settings, then you just plug them into the script. Although the API is designed for use with ad-hoc roles, there is no reason why this does not work with standard roles. I tested it this morning on eBS release 11.5.10.2 on a 10g database running on Linux, and it worked fine for me.

Enjoy!

SQL script

Bulk responding to notifications

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;
/

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.

Close