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!

AME Part Five – Defining a Dynamic Approval Group

One of the most recent search terms that I’ve seen for my blog here was “AME dynamic approval group example” – so in this post, I’m going to go through the steps required to create a dynamic approval group and provide an example.  This example continues on from part four, since we will be replacing the static approval group with a simple dynamic one.

Using the “Approvals Management Business Analyst” responsibility, in the “Approval Process Setup” panel on the right hand side of the dashboard, choose the “iRecruitment Vacancy Approval” transaction type again:

Define Process for the Transaction Type

Click on the “Approver Groups” link, and we will modify the existing group from static to dynamic.  Click on the “Update” icon for the Approver Group we created earlier, and then change the usage type from Static to Dynamic.  As soon as the change is made, you will be shown a warning, indicating that the static approvers will be removed from the group:

Warning when changing usage

Since we are going to be redefining the group completely, this is fine, so click on the “Yes” button and you will be returned to the group definition with no static approvers shown.

As a simple example, my approver group requires approval from two people – the user MATT and the HR person Tim Cleary.  The SQL code required to retrieve the two records in the same query is

SELECT 'user_id:'||user_id
FROM   fnd_user
WHERE  user_name = 'MATT'
UNION ALL
SELECT 'person_id:'||person_id
FROM   per_all_people_f  papf
WHERE  papf.full_name = 'Cleary, Mr. Timothy'

In the SQL above, we need to tell AME exactly what kind of approver we are retrieving – by prefixing the user record with “user_id:” and prefixing the person record with “person_id:”, AME then knows where the records are being retrieved from.  Enter the SQL into the Query box, and then hit the Apply button. You can also use the Validate button, which checks that the query is structurally sound, but does not seem to check much more than that (for example, if you prefix the user record with “usssssser_id:”, it is still classed as a valid query).

That’s all there is to it – if we now go and create a new vacancy (using the same BJOSPEH user that we did in the last post), when we review the vacancy, we see that AME has added the two approvers to the bottom of the approval list:

Additional Approvers are generated by AME

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 Three – Roles and Grants for AME

By default, the “Approvals Management Administrator” and “Approvals Management Business Analyst” responsibilities do not have any functions associated with them, because their access is restricted by Role Based Access Control (RBAC). In this section, I will detail the steps needed to add functions so that we can get into using AME.

Firstly, we’ll create a new user for this demonstration, and assign them the two responsibilities, thus:

Screenshot showing new user creation with responsibilities "Approvals Management Administrator" and "Approvals Management Business Analyst"

If I now log into the eBusiness Suite as that user and try to switch to either of those responsibilities, the system indicates that there are no functions available for the user (see screenshot below).  As I said above, this is because role based access control ensures that only users with the right roles as well as responsibilities can access the functions.

By default, there are no functions available for this user and responsibility

So, now we need to modify the user to give them the right roles to administer AME properly.  As a system administrator, open the “User Management” responsibility, as shown below.

System Administrator - User Management responsibility

If you do not have any functions assigned to the responsibility, then you will need to use the SYSADMIN user and assign the “Security Administrator” role to your user. Select the “Users” option to continue.

Search for the user that you want to add the functions to (in my case, the AMEADMIN user I created earlier), open the user details and then click on the “Assign Roles” button. You are then presented with a search window, where you should search for all Roles beginning “Approval”:

Searching for the AME roles to grant

You are then returned to the user screen, where you should complete a justification for granting the roles to the user – if you don’t then the screen will prevent you from continuing.  Once you have entered the justification, you should then hit the Apply button.

Log out as this user, and when you log in as the AME user, you now have functions associated with the two responsibilities:

AME Administrator now has functions associated with the responsibilities

Now that we have granted the roles to the user, they can access the functions correctly.  However, RBAC means that when we open the Dashboard, the data is still protected and cannot be viewed:

No Transaction Types available

Again, we need to grant some RBAC roles to allow the user to access the different transaction types. The main principle behind this is to enable organizations to split the Transaction Type maintenance between different roles – for example to allow HR Super Users to maintain only the HR rules.

Using a different user, navigate to the Functional Administrator responsibility and click on the “Create Grant” button to create the new grant. In this example, we will create a specific grant for the AMEADMIN user, however in the real world it is more likely that you will create the grant for a group of users. The Object is set to “AME Transaction Types”, thus:

Define a grant for the AMEADMIN user

In the second step, accept the default data context type of “All Rows” and continue to the third step. Define the “Set” to be “AME Calling Applications” as shown in the following screenshot:

Choose the "AME Calling Applications" set to define access

Finally, review the settings that you have entered and click on the “Finish” button. In order to ensure that the changes are replicated, now flush the cache by clicking on the “Core Services” tab then choose the “Caching Framework” sub-menu. Choose “Global Configuration” from the sidebar and then click on the “Clear All Cache” button. You will be presented with a warning message (see below) which you can ignore and click on the “Yes” button.

Cache reset warning

Log out of this user and log back in as the AMEADMIN user, and navigate to one of the AME responsibilities – you are now given a list of the AME Transaction Types that are configured in the system:

User can administer Transaction Types

In the next blog post, I’ll be providing a practical example of customizing AME within iRecruitment.

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.

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