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!

How To Provide Access to HR EITs from CRM Responsibilities

When trying to grant access to HR Extra Information Types to different responsibilities, one of the limitations in HR is that the only responsibilities which appear in the standard form are ones which are related to HR.  For example, querying “Service” in the Information Type Security form (PERWSITS) returns no rows because HR can’t see that responsibility:

Service Responsibility is not visible in HR

So, if you are working on a system where you need to grant access to HR information types to responsibilities which are not within the HR area (e.g. CRM-based responsibilities), then there is no obvious way to do it.  In this kind of situation, there may well tend to be over-use of responsibilities since an HR and CRM responsibility is now needed to provide the access.

The way to reduce this duplication is to add the seeded HR security menu to the seeded Service menu as follows.  Using the System Administrator responsibility, navigate to Application > Menu and query the menu CSX_CUSTOMER_SUPPORT.  Add a new entry for the submenu UK HRMS SECURITY:

Adding the submenu to the menu for Service

Once you have saved the new menu, navigate to the Service responsibility and you will have access to the form to enable you to map access to the information types:

New Sub-Menu is now available to Service Responsibility

Now when you open the form, you can easily map the required access.  The screenshot below shows adding different information types to the seeded Service responsibility:

Assigning access to Extra Information Types in Service

This little tip comes courtesy of Davyd Parry.

Generating an HR hierarchy from a Service Request

Another code snippet from my archive.

On a number of projects, we have implemented approvals for service requests based on the HR supervisor hierarchy, and occasionally during testing the approval notification ends up in an unexpected place.  If you are using AME, then you can run the live test based on the service request ID to find out who AME expects to notify, but if you are using an SR workflow then this becomes a little more complex.

The code sample below takes the SR number as the start point, and from the primary contact walks up the HR supervisor hierarchy until there are no more supervisors found.

Enjoy!

SET SERVEROUTPUT ON SIZE 1000000

DEFINE sr_number = &1

DECLARE
  --
  CURSOR c_per_info ( cp_party_id IN NUMBER ) IS
    SELECT full_name
    FROM   apps.per_people_x
    WHERE  person_id = cp_party_id;
  --
  CURSOR c_get_manager ( cp_party_id IN NUMBER ) IS
    SELECT paaf.supervisor_id
    FROM   apps.per_all_assignments_f       paaf
    JOIN   apps.per_assignment_status_types past   ON
      ( paaf.assignment_status_type_id = past.assignment_status_type_id )
    WHERE  paaf.person_id                         = cp_party_id
    AND    paaf.effective_start_date             <= SYSDATE
    AND    NVL(paaf.effective_end_date, SYSDATE) >= SYSDATE
    AND    paaf.primary_flag                      = 'Y';
  --
  CURSOR c_party_from_sr_number ( cp_sr_number IN NUMBER ) IS
    SELECT party_id
    FROM   apps.cs_hz_sr_contact_points
    WHERE  incident_id   = ( SELECT incident_id
                             FROM   apps.cs_incidents_all_b
                             WHERE  incident_number = cp_sr_number )
    AND    primary_flag  = 'Y';
  --
  v_start         PLS_INTEGER;
  v_curr_person   per_people_x.full_name%TYPE;
  v_next_person   per_people_x.full_name%TYPE;
  v_check         BOOLEAN;
  --
BEGIN
  --
  v_check := TRUE;
  --
  OPEN  c_party_from_sr_number ( cp_sr_number => &sr_number );
  FETCH c_party_from_sr_number INTO v_start;
  CLOSE c_party_from_sr_number;
  --
  IF v_start IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('No SR found for SR number &sr_number');
    v_check := FALSE;
  END IF;
  --
  IF v_check THEN
    WHILE v_start IS NOT NULL LOOP
      OPEN  c_per_info ( cp_party_id => v_start );
      FETCH c_per_info INTO v_curr_person;
      CLOSE c_per_info;
      --
      OPEN  c_get_manager ( cp_party_id => v_start );
      FETCH c_get_manager INTO v_start;
      CLOSE c_get_manager;
      --
      OPEN  c_per_info ( cp_party_id => v_start );
      FETCH c_per_info INTO v_next_person;
      CLOSE c_per_info;
      --
      IF v_start IS NULL THEN
        DBMS_OUTPUT.PUT_LINE ( 'Current person '
            ||v_curr_person
            ||' has no supervisor' );
      ELSE
        DBMS_OUTPUT.PUT_LINE('Current person '
            ||v_curr_person
            ||' is supervised by '
            ||v_start
            ||' / '||v_next_person );
      END IF;
    END LOOP;
  END IF;
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