Cancel Open Workflow Notifications

SET SERVEROUTPUT ON;
DECLARE
    l_result                   VARCHAR2(20);
    l_item_type                WF_ITEMS.ITEM_TYPE%TYPE  := NULL;
    l_item_key                 WF_ITEMS.ITEM_KEY%TYPE   := NULL;
    l_records_exists VARCHAR2(1);
   
    CURSOR open_notifications_non_LS
    IS
        SELECT DISTINCT wn.message_type item_type,
               wn.item_key,
               wn.notification_id
         FROM wf_notifications wn,
              WF_ITEM_ATTRIBUTE_VALUES wiav
        WHERE wn.message_type      IN ( 'POSCHORD', 'APINV') --Give WF: ITEM_TYPE
          AND wn.status            = 'OPEN'
          AND wn.message_type      = wiav.item_type
          AND wn.item_key          = wiav.item_key
          AND wiav.name            IN ('ORG_ID', 'APINV_AOI') --Give Attribute Names
          AND wiav.number_value    IN (1686,1687)                 --Give Condition       
        ORDER BY wn.message_type,
                 wn.item_key,
                 wn.notification_id;

    CURSOR open_notifications_all
    IS
        SELECT DISTINCT wn.message_type item_type,
               wn.item_key,
               wn.notification_id
         FROM wf_notifications wn
        WHERE wn.message_type   IN ('POSASNNB', 'IBEALERT')    --Give Message Type
          AND wn.status         = 'OPEN'       
        ORDER BY wn.message_type,
                 wn.item_key,
                 wn.notification_id;

BEGIN
    dbms_output.put_line('Starts..');

    /* ------------Workflows and notifications for all orgs excluding LifeSize OU org */
    dbms_output.put_line('Processing Workflows and notifications for all orgs excluding Given??? orgs ');
    FOR i IN open_notifications_non_LS
    LOOP

        dbms_output.put_line('    Calling cancel workflow notification for item_type: '||i.item_type||
         ' item_key: '||i.item_key|| ' notification_id: '||i.notification_id);
        BEGIN
            WF_NOTIFICATION.cancel(i.notification_id,'testing');
            UPDATE WF_NOTIFICATIONS
               SET mail_status = 'SENT'
             WHERE notification_id = i.notification_id;
           --If it is not updated then an email would be sent to the original recipient with warning notification cancelled.
        EXCEPTION
            WHEN OTHERS
            THEN
                dbms_output.put_line('    Exception occured while cancelling notification for item_type: '||i.item_type||
                ' item_key: '||i.item_key|| ' notification_id: '||i.notification_id);
                dbms_output.put_line('        Error message : '||SQLERRM);
        END;

        IF (l_item_type IS NULL OR l_item_key IS NULL)
        THEN
            l_records_exists := 'Y';
            l_item_type      := i.item_type;
            l_item_key       := i.item_key;
        END IF;

        IF (l_item_type <> i.item_type OR l_item_key <> i.item_key)
        THEN
            dbms_output.put_line('    Calling Abort complete workflow for item_type: '||l_item_type||
            ' item_key: '||l_item_key);
            BEGIN
                WF_ENGINE.AbortProcess(l_item_type, l_item_key, NULL, l_result);
                dbms_output.put_line('        Successfully aborted workflow');
                dbms_output.put_line('    ----------------------------------------------------');
            EXCEPTION
                WHEN OTHERS
                THEN
                    dbms_output.put_line('        Exception occured while Aborting workflow for item_type: '||l_item_type||
                    ' item_key: '||l_item_key);
                    dbms_output.put_line('        Error message : '||SQLERRM);
                    dbms_output.put_line('    ----------------------------------------------------');
            END;
            l_item_type := i.item_type;
            l_item_key    := i.item_key;
        END IF;
    END LOOP; --open_notifications_non_LS

    IF (nvl(l_records_exists,'N') = 'Y') THEN
        dbms_output.put_line('    Calling Abort complete workflow for last item_type: '||l_item_type||
' item_key: '||l_item_key);
        BEGIN
            WF_ENGINE.AbortProcess(l_item_type, l_item_key, NULL, l_result);
            dbms_output.put_line('        Successfully aborted workflow');
            dbms_output.put_line('    ----------------------------------------------------');
        EXCEPTION
        WHEN OTHERS
        THEN
            dbms_output.put_line('        Exception occured while Aborting workflow for last item_type: '||l_item_type||
            ' item_key: '||l_item_key);
            dbms_output.put_line('        Error message : '||SQLERRM);
            dbms_output.put_line('    ----------------------------------------------------');
        END;
    END IF;
       
    /* ------------Workflows and notifications for all orgs */
    dbms_output.put_line('*****************************************************************************');
    dbms_output.put_line('Processing Workflows and notifications for all orgs ');
    dbms_output.put_line('*****************************************************************************');
   
    l_item_type := NULL;
    l_item_key := NULL;
    l_records_exists := NULL;
   
    FOR i IN open_notifications_all
    LOOP
        dbms_output.put_line('    Calling cancel workflow notification for item_type: '||i.item_type||
        ' item_key: '||i.item_key|| ' notification_id: '||i.notification_id);
        BEGIN
            WF_NOTIFICATION.cancel(i.notification_id,'testing');
        EXCEPTION
            WHEN OTHERS
            THEN
                dbms_output.put_line('    Exception occured while cancelling notification for item_type: '||i.item_type||
                ' item_key: '||i.item_key|| ' notification_id: '||i.notification_id);
                dbms_output.put_line('        Error message : '||SQLERRM);       
        END;

        IF (l_item_type IS NULL OR l_item_key IS NULL) THEN
            l_records_exists := 'Y';
            l_item_type     := i.item_type;
            l_item_key        := i.item_key;       
        END IF;

        IF (l_item_type <> i.item_type OR l_item_key <> i.item_key) THEN
            dbms_output.put_line('    Calling Abort complete workflow for item_type: '||l_item_type|| ' item_key: '||l_item_key);
            BEGIN
                WF_ENGINE.AbortProcess(l_item_type, l_item_key, NULL, l_result);
                dbms_output.put_line('        Successfully aborted workflow');
                dbms_output.put_line('    ----------------------------------------------------');
            EXCEPTION
                WHEN OTHERS
                THEN
                    dbms_output.put_line('        Exception occured while Aborting workflow for item_type: '||l_item_type||
                    ' item_key: '||l_item_key);
                    dbms_output.put_line('        Error message : '||SQLERRM);
                    dbms_output.put_line('    ----------------------------------------------------');
            END;
            l_item_type := i.item_type;
            l_item_key    := i.item_key;       
        END IF;
    END LOOP; --open_notifications_all
   
    IF (nvl(l_records_exists,'N') = 'Y') THEN
        dbms_output.put_line('    Calling Abort complete workflow for last item_type: '||l_item_type|| ' item_key: '||l_item_key);
        BEGIN
            WF_ENGINE.AbortProcess(l_item_type, l_item_key, NULL, l_result);
            dbms_output.put_line('        Successfully aborted workflow');
            dbms_output.put_line('    ----------------------------------------------------');
        EXCEPTION
            WHEN OTHERS
            THEN
                dbms_output.put_line('        Exception occured while Aborting workflow for last item_type: '||l_item_type||
                ' item_key: '||l_item_key);
                dbms_output.put_line('        Error message : '||SQLERRM);
                dbms_output.put_line('    ----------------------------------------------------');
        END;
    END IF;

    COMMIT; --We can do an explicit Commit
    dbms_output.put_line('Completed');
EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line('Exception :'||SQLERRM);
END;

Workflow Background Proces - Concurrent Program

Workflow Background Process is a concurrent program for processing

• deferred activities
• timed out activities
• stuck processes

The background engine executes all activities that satisfy the given arguments at the time that the background engine is invoked. Any activities that are newly deferred or timed out or processes that become stuck after the current background engine starts are processed by the next background engine that is invoked.

Workflow Background Process is run with the help of Workflow Background Engine which is PL/SQL Procedure which runs this concurrent program with specified parameters.

Parameters:

Item Type : Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any activity regardless of its item type.

Minimum Threshold : Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.

Maximum Threshold : Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second. By using Minimum Threshold and Maximum Threshold you can create multiple background engines to handle very specific types of activities. The default values for these arguments are null so that the background engine runs activities regardless of cost.

Process Deferred : Specify whether this background engine checks for deferred activities. Setting this parameter to Yes allows the engine to check for deferred activities.

Process Timeout – Specify whether this background engine checks for activities that have timed out. Setting this parameter to Yes allows the engine to check for timed out activities.

Process Stuck : Specify whether this background engine checks for stuck processes. Setting this parameter to Yes allows the engine to check for stuck processes.

 Note: Make sure you have a least one background engine that can check for timed out activities, one that can process deferred activities, and one that can handle stuck processes. At a minimum, you need to set up one background engine that can handle both timed out and deferred activities as well as stuck processes.

Navigation : System Administrator --> Requests > Run

Difference between Alerts and Workflow

Alert	Workflow
Oracle Alert is a database event detection tool designed to detect database events.	Oracle workflow is designed to manage the execution complex of business processes that result from database events.
Oracle Alert does contain some workflow type features such as response processing, that allow a sequence of actions to be taken depending on a users response to a message. (Example : Approval sequence)	Oracle Workflows response processing capabilities are more advanced than Oracle Alert.

How to restrict the user from forwarding the approval notifications to other users?

Login as admin
2. Click System administrator --> Oracle Application Manager --> workflow
5. Clcik on Notification Mailers --> Edit --> Advanced --> Email Servers
6. click "edit"
7. Uncheck "Allow Forwarded Response" which will restrict the user to respond by e-mail to an e-mail notification that has been forwarded from another role.

Delegate or Transfer or Reassign ?

Today I have learnt some interesting stuff in Oracle Workflow Notifications...

Every Oracle Workflow Notifications have the feature to delegate or transfer or reassign the notification.

What all this means? and where this values are coming from?

The value is coming from the profile 'WF: Notification Reassign Mode' , the values for this profile can be Delegate, Transfer or Reassign.

Delegate

The Notification will be forwarded to the delegated employee/user, but the original recipient of the notification remains the owner.

Transfer

The Notification will be forwarded to the delegated employee/user and the new recipient becomes the owner of the notification.

Reassign

The user can decide whether to Delegate or Transfer.

Order Details Workflow

To process an Order in 11i, Order Management uses internal Workflow technology.



As you can see it contains a start activity and an end activity and bunch of other activities in between.
11i Order Management primarily uses two workflow processes two process a Sales Order. One for processing an header and the other one for processing lines. Depending on our requirement we will configure these workflow processes to send out an event at appropriate steps within the processing. To configure an event there is a standard activity, Event activity, which we will embed and populate it with relevant data.
Let’s look at a Header processing workflow. How do I get to it. Well in 11i most of the functionality is inside the database. Workflow technology is also implemented as a PL/SQL engine inside the database. These processes are executed at runtime by this engine. The picture that you saw above is a design time tool and can be run from your desktop. All the relevant files are stored as “wft” file extensions. As you can imagine now …these are loaded into database for execution.
So let’s pull the header workflow by connecting to the 11i database.



There are bunch of processes listed here. For header processing we have “R_STANDARD_HEADER”. This is what is shipped out of the box from 11i. Of course customers have the option of creating their own and customize it to their needs.



As you can see in the above workflow there is a standard “Start”, called as “Enter”, and an “End” activity. Besides this we have “Book Order, Manual” and “Close Order” activities. Let’s focus on “Book” and “Close” activity. Picture of these activities indicates that they invoke sub processes.

Booking Order Process

A Sales Order can be entered through the Sales Order form or it can be received from external systems through various order channels like web sites, EDI or  XML. When they enter the Order management system they are in a status called as “Entered”. Next step in the Order processing cycle is to book the order. Normally you enter into Order Management’s Order Form and click on the button “Book”. Above mentioned workflow get’s triggered as a result of that.

Configuring Oracle Workflow

Let’s say we want to send the Order status to external system’s when the order is Booked. So let’s see if there is any event available out of the box. To do this we can browse through 11i Event’s screen by selecting responsibility “Workflow Administrator Web Applications” and clicking on “Administrator  Workflow'” ---> “Business Events”. Give the search criterion as “oracle.apps.ont”. Here “ont” is the internal code for Order management module and “oracle.apps” is a standard used for all events shipped out of the box by 11i.



Alternatively you can browse the same through E-Business Suite adapter from Oracle BPEL or Oracle ESB.



Both of the approaches gives the same view.

Looking at the what is being shipped out its very clear that there is no event which  get’s triggered when Order is booked….OK…hum… what needs to be done in order to inform external systems, UI’s etc when Order Booking happens. Here are the steps…

• Define a custom business event
• Insert a new “Events” activity in the Workflow
• Configure the activity
• Optionally….insert another custom activity to populate additional data

Let’s go through each of the above in detail.

Define a custom Business Event

Log into Oracle apps as “sysadmin/sysadmin”. Select the responsibility “Workflow Administrator Web Applications” and clicking on “Administrator  Workflow'” ---> “Business Events”.



You can see “Create Event” button click on it to define the event and input the details as in the screen

Insert a new “Events” activity in the Workflow

In the 11i workflow builder look at the toolbar. You will notice there is an event activity. Drag and drop that in the Workflow panel







Your workflow so far looks like this

Configure the activity

Click on “Event Details” tab. Make sure you put the Event Name value as “oracle.apps.fnd.order_booked”. Another required value to raise the event is “Event Key”. This has to be a unique value. In our case we can map this to Order Header ID which is always unique.

 

Optionally you can define the variables that you want to pass as a part of the event. Map these variables through the “Node Attributes” tab
In the following diagram I have defined variables (Activity Attributes) starting with name as “X”. For example in this scenario when a Order Booked event is raised we want to know following
Order Number, Customer PO Number, Organization ID and Order Header ID



And map these variables from the “Node Attributes” tab.



Well in this scenario most of the data that we want is not available as pre-defined variables….but don’t worry its quite easy to get exactly what data you want to populate. So what we will do is define additional variables for the workflow and populate them through a custom PL/SQL program and then map them through the Event we created.

Custom activity to populate additional data

Check additional data you need …is that already available in the Workflow variables. If yes… good…otherwise create the required variables. In following diagram I have created additional variables starting with name as “XX_”



We have defined new variables for the workflow…now let’s populate them through the custom PL/SQL activity. Go back to our wonderful 11i Workflow builder and click on Function activity….drag it and drop it on the panel…



Function activity will prompt for input parameters. Most important one is the name of PL/SQL program that gets called from here….In this case it’s “XX_GET_ORD_DETAILS”




Your workflow now looks like following


To summarize what we did.
Created a custom event called as “XX_ORDERED_BOOKED_EVENT”. We created some additional attributes to pass some custom data…this is optional….Created a custom PL/SQL function activity “XX_GET_ORERE_DETAILS” to populate the additional variables in the custom event.

Launching Workflow from PL/SQL Code

The below script will launch  the desired workflow from PL/SQL code:

declare
   v_itemtype   VARCHAR2(50);
   v_itemkey    VARCHAR2(50);
   v_process    VARCHAR2(50);
   v_userkey    VARCHAR2(50);
begin
 
v_itemtype := 'CHANDRATEST'; 
v_itemkey := '1233';
v_userkey := '1233';
v_process := 'CHANDRAPROCESS';
WF_ENGINE.Threshold := -1;
WF_ENGINE.CREATEPROCESS(v_itemtype, v_itemkey, v_process);
wf_engine.setitemuserkey(v_itemtype, v_itemkey, v_userkey );
wf_engine.setitemowner (v_itemtype, v_itemkey,'SYSADMIN');
WF_ENGINE.STARTPROCESS(v_itemtype, v_itemkey);
commit;
exception when others
then
  dbms_output.put_line(SQLERRM);
end;

Workflow Tables and Queries

Contains all the table information related to Oracle Workflows and queries joining these tables.

Query1: Accepts Workflow itemtype / shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.

Query2: Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req
MULTIPLE INSTANCES OF COPY TO CLIPBOARD DEMO
WORKFLOW TABLES

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS



SELECT * FROM WF_USER_ROLES



SELECT * FROM WF_ROLES



SELECT * FROM WF_ITEMS



SELECT * FROM WF_ITEM_ATTRIBUTES



SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES



SELECT * FROM WF_ITEM_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITIES



SELECT * FROM WF_ACTIVITIES_TL



SELECT * FROM WF_ACTIVITY_ATTRIBUTES



SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL



SELECT * FROM WF_ACTIVITY_TRANSITIONS



SELECT * FROM WF_DEFERRED--WF_CONTROL



SELECT * FROM WF_ACTIVITY_ATTR_VALUES

WHERE NAME LIKE '%MASTER%'

AND PROCESS_ACTIVITY_ID

IN(

SELECT *-- PROCESS_ACTIVITY

FROM WF_ITEM_ACTIVITY_STATUSES

WHERE ITEM_TYPE = 'ERP'

AND ITEM_KEY ='63865'

)



SELECT * FROM WF_ITEM_TYPES



SELECT * FROM WF_LOOKUPS_TL



SELECT * FROM WF_NOTIFICATIONS

WHERE MESSAGE_TYPE ='ERP'

ORDER BY BEGIN_DATE DESC



SELECT * FROM WF_NOTIFICATION_ATTRIBUTES



SELECT * FROM WF_MESSAGES



SELECT * FROM WF_MESSAGES_TL



SELECT * FROM WF_MESSAGE_ATTRIBUTES



SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL



SELECT * FROM WF_ETS



SELECT * FROM WF_PROCESS_ACTIVITIES



Click here to copy script to clipboard
LIST OF ACTIVITIES FOR AN ITEMTYPE

SELECT A.ITEM_KEY,

B.ACTIVITY_NAME,

A.ACTIVITY_STATUS,

A.ACTIVITY_RESULT_CODE,

A.ASSIGNED_USER,

A.BEGIN_DATE,

A.END_DATE

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

AND A.ITEM_KEY = 64077

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

Click here to copy script to clipboard
TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING

SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS

Click here to copy script to clipboard
LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS



SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS

FROM

(SELECT B.ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,

COUNT(B.ACTIVITY_NAME) TOTAL_PENDING

FROM WF_ITEM_ACTIVITY_STATUSES A,

WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'ERP'

--AND A.ITEM_KEY = 1131

AND END_DATE IS NULL

AND ACTIVITY_STATUS != 'ERROR'

AND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')

GROUP BY ACTIVITY_NAME,

TRUNC(SYSDATE) - TRUNC(BEGIN_DATE)

ORDER BY ACTIVITY_NAME,

PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS

WHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5