R12 - How to use Business Events in Oracle Workflow

Following is a simple step by step guide to use Business Events feature of Oracle Workflow in Oracle Applications (E-Business Suite).

Step 1) Navigate to the workflow administrator responsibility and choose the Business Events function. Define a Business Event.Owner name should be application name and owner tag should be the application short name.

Step 2) Define a Subscription for the Business Event defined in Step 1

1.  System => should be the name of the database where the workflow is installed
2. Phase => Keep the value for phase as 99 if you want the workflow to run immediately.
3. Event Filter => Name of the event

Step 3) In workflow builder create a workflow item type and define 3 attributes as follows

Step 4) Create a event as follows

Step 5) Create a process as follows

Note: The starting node should be the Event that we have created

Associate the attributes that were created with the Event in the process.

Step 6) Test the event

Click “Raise in PLSQL”.

Now check if the workflow has been triggered in the Status Monitor

References:

http://www.oracleerpappsguide.com/2015/04/r12-how-to-use-business-events-in-oracle-workflow.html

Workflow Troubleshooting SQL queries

SELECT DISTINCT rs.text, r.display_name
  FROM wf_resources rs, wf_local_roles r
 WHERE rs.name = 'WF_ADMIN_ROLE' AND rs.text = r.name;

 select *
 from WF_ITEM_ACTIVITY_STATUSES_V
 where (user_key='110133340' or ITEM_KEY like '8834663-7239%')
 and activity_status_code!='COMPLETE'
 order by execution_time;

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
where role_name='USER.NAME';

SELECT * FROM WF_USER_ROLES
where role_name='USER.NAME';

SELECT * FROM WF_ROLES
where name='USER.NAME';

SELECT distinct orig_system 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;

  SELECT name,
         num_active,
         num_error,
         num_defer,
         num_suspend,
         num_complete,
         num_purgeable,
         wit.*
    FROM WF_ITEM_TYPES wit
   WHERE   num_active
         + num_error
         + num_defer
         + num_suspend
         + num_complete
         + num_purgeable != 0
ORDER BY wit.num_active DESC;

SELECT *
  FROM WF_ITEM_TYPES_TL
 WHERE 1 = 1 AND language = 'US' AND NAME = 'REQAPPRV';

       --         AND DISPLAY_NAME = 'PO Approval';

  SELECT A.ITEM_KEY,
         B.ACTIVITY_NAME,
         A.ACTIVITY_STATUS,
         A.ACTIVITY_RESULT_CODE,
         A.ASSIGNED_USER,
         A.BEGIN_DATE,
         A.END_DATE,
         A.*
    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 LIKE 'REQAPPRV'                --'XXLVMGMT' --XXOMFSF
         AND A.ACTIVITY_STATUS != 'COMPLETE'
         AND A.begin_date >= TRUNC (SYSDATE) - 1
-- AND A.ITEM_KEY = 64077
--       AND ACTIVITY_NAME IN
--              ('PLANNING', 'PURCHASING', 'MFGFINANCE', 'CSD', 'TAX')
ORDER BY A.item_key;                                                                                   --, b.instance_id;

SELECT *
  FROM wf_notifications
 WHERE notification_id = 61394049;

  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;

SELECT * FROM wf_deferred_table_m;

WORKFLOW MAILER DEBUGGING SCRIPT FOR DEBUGGING EMAILS ISSUES

This article containts various Workflow and Business Event debugging scripts.

--Checking workflow Components status wheather are they running or stopped.
select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error
from fnd_svc_components 
where component_type like 'WF%'
order by 1 desc,2,3;


--Query to get the log file of active workflow mailer and workflow agent listener Container
--Note All Workflow Agent Components logs will stored in single file ie. container log file.
select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
order by fcp.logfile_name;



--Linux Shell script Command to get outbound error in Mailer
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix

--Linux Shell script Command to get inbound processing error in Mailer 
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;


---Query to Check Workflow Mailer Backlog 
--State=Ready implies that emails are not being sent & Waiting mailer to sent emails
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;


--Check any particular Alert Message email has be pending by Mailer 
select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'
and upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';


--Check The Workflow notification has ben sent or not
select mail_status, status from wf_notifications where notification_id=<notification_id>
--If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by loggin in application + click on preference + the notification preference 


--Check Wheather workflow background Engine is workfing for given workflow or not in last 2 days 
-- Note: Workflow Deferred activities are run by workflow background engine. 
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'
order by last_update_date desc

-- Check wheather any business event is pending to process 
-- ie. Query to get event status & parameters value of particular event in wf_deferred table.
select wd.user_Data.event_name,wd.user_Data.event_key,
rank() over ( partition by wd.user_Data.event_name, wd.user_Data.event_key order by n.name) as serial_no,
n.NAME Parameter_name, N.value Parameter_value ,
decode(state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained',
3, '3 = Exception', 4,'4 = Wait', to_char(state)) state,
wd.user_Data.SEND_DATE,
wd.user_Data.ERROR_MESSAGE,
wd.user_Data.ERROR_STACK,
wd.msgid,wd.delay
from WF_DEFERRED wd , TABLE(wd.user_Data.PARAMETER_LIST) n
where lower(wd.user_data.event_name)='<event Name >'
order by wd.user_Data.event_name, wd.user_Data.event_key, n.name;

References:

http://www.apps2fusion.com/at/gt/tc/328-workflow-mailer-debugging-script-for-debugging-emails-issues

Purge Obsolete Workflow Runtime Data (FNDWFPR)

Use the standard concurrent program FNDWFPR "Purge Obsolete Workflow Runtime Data" to purge old data from the Oracle Workflow tables regularly.

This program purges obsolete runtime information associated with work items, including status information, any associated notifications, and, if the ECX: Purge ECX data with WF profile option is set to Y, any associated Oracle XML Gateway transactions. By default, it also purges obsolete design information, such as activities that are no longer in use and expired ad hoc users and roles, and obsolete runtime information not associated with work items, such as notifications that were not handled through a workflow process and, if the ECX: Purge ECX data with WF profile option is set to Y, Oracle XML Gateway transactions that were not handled through a workflow process. You can optionally choose to purge only core runtime information associated with work items for performance gain during periods of high activity, and purge all obsolete information as part of your routine maintenance during periods of low activity.

Note: This program does not delete ad hoc users or roles whose expiration date is null. To ensure that ad hoc users and roles are purged in a timely fashion after they are no longer needed, estimate how long they should be active and specify an appropriate expiration date when you call WF_DIRECTORY.CreateAdHocUser(), WF_DIRECTORY.CreateAdHocRole(), or WF_DIRECTORY.CreateAdHocRole2() to create them.

To preserve electronic signature evidence for future reference, this program by default does not delete any notifications that required signatures or their associated signature information. If you do not need to maintain signature evidence, you can choose to delete signature-related information as well.

Navigate to the Submit Requests form in Oracle E-Business Suite to submit the Purge Obsolete Workflow Runtime Data concurrent program. When you install and set up Oracle E-Business Suite and Oracle Workflow, your system administrator needs to add this concurrent program to a request security group for the responsibility that you want to run this program from. See: Overview of Concurrent Programs and Requests, Oracle E-Business Suite System Administrator's Guide - Configuration and Running Reports and Programs, Oracle E-Business Suite User's Guide.

You can supply the following parameters for the Purge Obsolete Workflow Runtime Data concurrent program:

• Item Type - The item type to purge. Leaving this field blank defaults to purging the runtime data for all item types.
• Item Key - The item key to purge. Leaving this field blank defaults to purging the runtime data for all item keys.
• Age - Minimum age of data to purge, in days. The default is0.
• Persistence Type - The persistence type to be purged, either Temporary or Permanent. The default is Temporary
• Core Workflow Only - Enter 'Y' to purge only obsolete runtime data associated with work items, or 'N' to purge all obsolete runtime data as well obsolete design data. The default is 'N'.
• Commit Frequency - The number of records to purge before the program commits data. To reduce rollback size and improve performance, set this parameter to commit data after a smaller number of records. The default is 500 records.
  
  Note: After performing a commit, the program resumes purging work items with the next subsequent begin date. In some cases, if additional items have the same begin date as the last item that was purged before a commit, the program may not purge all eligible items. To purge these remaining work items, simply rerun the program.
  
• Signed Notifications - Enter 'N' to preserve signature evidence, including notifications that required electronic signatures and their associated signature information. Enter 'Y' to purge signature-related information. The default is 'N'.
  
  

What is Fan Trap in Discoverer and how it handles them?

Fan Trap is a situation while running discoverer reports that return unexpected results due to a group of joined database tables. The most common manifestation of a fan trap occurs when a master table is joined to two or more detail tables independently.

If you use a straightforward SQL statement to aggregate data points here, you may get incorrect results due to fan trap. Now, if you enable fan trap detection in Discoverer and if you use Discoverer to aggregate the data points, Discoverer will never return incorrect results.

Example of Fan Trap:

Consider an example fan trap schema that includes a master folder (ACCOUNT) and two detail folders (SALES and BUDGET), as shown below:

Now let’s say we need to answer the question, “What is the total sales and total budget by account?

Straightforward SQL statement approach:

1 2 3 4 5 6 7 8 9 10 11

SELECT Account.Name,        SUM(sales),        SUM(budget) FROM       Account,       Sales,       Budget Where       Account.id=Sales.accid       AND Account.id=Budget.accid GROUP BY Account.Name;

Account    Sales Budget

Account 1   800   1200

Account 2   130    200

Account 3    600   750

Account 4    600   600

The above results are incorrect, because they are based on a single query in which the tables are first joined together in a temporary table, and then the aggregation is performed. However, this approach causes the aggregates to be summed (incorrectly) multiple times.

Discoverer Approach:

If we run the query in Discoverer interrogates the query, detects a fan trap, and rewrites the query to ensure the aggregation is done at the correct level. Discoverer rewrites the query using inline views, one for each master-detail aggregation, and then combines the results of the outer query.

Here are the results from discoverer which is correct:

Account   Sales   Budget

Account 1  400      400

Account 2  130      100

Account 3  200      750

Account 4  300      200

How to enable fan trap in discoverer?

By default, fan trap detection is always enabled for you. If you want to disable it (however not recommended), you can logon to Discoverer Plus, go to Tools > Options >Advanced Tab and click on ‘Disable fan trap detection’.

How Discoverer handles fan trap?

If a fan trap is detected, Discoverer can usually rewrite the query using inline views to ensure the aggregation is done at the correct level. Discoverer creates an inline view for each master-detail aggregation, and then combines the results of the outer query.

In some circumstances, Discoverer will detect a query that involves an unresolvable fan trap schema, as follows:

• If the detail folders use different keys from the master for the join
• If there is a direct join relationship between the detail folders (thereby creating an ambiguous circular relationship)
• If non-aggregated values are chosen from more than one of the detail folders
• If more than one detail folder has a separate join relationship to a different master folder

In the above circumstances, Discoverer disallows the query and displays an error message.