Oracle Workflow - Important Queries

I've collected below queries from internet and kept in my notes. These are very useful set of queries for workflow related issues. Sharing for everyone's use:

Oracle Workflow - Important Queries
--------------------------------------------------------------

--Select all workflow items for a given item type
SELECT item_type,
       item_key,
       to_char(begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       to_char(end_date,
               'DD-MON-RR HH24:MI:SS') end_date,
       root_activity activity
  FROM apps.wf_items
 WHERE item_type = '&item_type'
   AND end_date IS NULL
 ORDER BY to_date(begin_date,
                  'DD-MON-YYYY hh24:mi:ss') DESC;


-- notifications sent by a given workflow
select  wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where  wn.group_id = wias.notification_id
and  wias.item_type = 'WSHSUPI'
and  wias.item_key = 'CMS21408'
/

--prompt **** Find the Activity Statuses for all workflow activities of a given item type and item key
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/


--Get a list of all Errored Workflow Activities for a given item type/ item key
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = '&item_type'
   AND ias.item_key = '&item_key'
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.item_type = '&item_type'
   AND i.item_key = ias.item_key
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/


--prompt *** Error Process Activity Statuses
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
UNION ALL
SELECT execution_time,
       to_char(ias.begin_date,
               'DD-MON-RR HH24:MI:SS') begin_date,
       ap.display_name || '/' || ac.display_name activity,
       ias.activity_status status,
       ias.activity_result_code RESULT,
       ias.assigned_user ass_user
  FROM wf_item_activity_statuses_h ias,
       wf_process_activities       pa,
       wf_activities_vl            ac,
       wf_activities_vl            ap,
       wf_items                    i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY 2,
          1
/


prompt **** Error Process Errored Activities
SELECT ac.display_name          activity,
       ias.activity_result_code RESULT,
       ias.error_name           error_name,
       ias.error_message        error_message,
       ias.error_stack          error_stack
  FROM wf_item_activity_statuses ias,
       wf_process_activities     pa,
       wf_activities_vl          ac,
       wf_activities_vl          ap,
       wf_items                  i
 WHERE ias.item_type = i.item_type
   AND ias.item_key = i.item_key
   AND ias.activity_status = 'ERROR'
   AND ias.process_activity = pa.instance_id
   AND pa.activity_name = ac.name
   AND pa.activity_item_type = ac.item_type
   AND pa.process_name = ap.name
   AND pa.process_item_type = ap.item_type
   AND pa.process_version = ap.version
   AND i.parent_item_type = '&item_type'
   AND i.parent_item_key = '&item_key'
   AND i.begin_date >= ac.begin_date
   AND i.begin_date < nvl(ac.end_date,
                          i.begin_date + 1)
 ORDER BY ias.execution_time
/


prompt **** Attribute Values
SELECT NAME attr_name,
       nvl(text_value,
           nvl(to_char(number_value),
               to_char(date_value))) VALUE
  FROM wf_item_attribute_values
 WHERE item_type = upper('&item_type')
   AND item_key = nvl('&item_key',
                      item_key)
/
--Count of all workflow deferred activities based
SELECT COUNT(1),
       was.item_type
  FROM apps.wf_items                  wi,
       apps.wf_item_activity_statuses was,
       apps.wf_process_activities     pra
 WHERE wi.item_type = was.item_type
   AND wi.item_key = was.item_key
   AND wi.end_date IS NULL
   AND was.end_date IS NULL
   AND was.activity_status = 'DEFERRED'
      --AND was.item_type = 'REQAPPRV'
   AND was.item_type = wi.item_type
   AND pra.instance_id(+) = was.process_activity
 GROUP BY was.item_type;


--check the various workflow agent listeners and their statuses
SELECT t.component_name,
       p.owner,
       p.queue_table,
       t.correlation_id
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';                        
 
--query to find records that are pending in each of the workflow agent listener queues
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
       ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
       ' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
       nvl2(t.correlation_id,
            'and corrid like ''' || t.correlation_id || ''' ',
            NULL) || 'having count(*)>0;'
  FROM applsys.fnd_svc_components t,
       applsys.wf_agents          o,
       dba_queues                 p
 WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
   AND p.owner || '.' || p.name = o.queue_name
   AND t.component_type LIKE 'WF_%AGENT%';


--Look for deferred events in wf_deferred. this can also be used to track the status of notifications/business events that are waiting to be processed/that have errored out
SELECT a.user_data.geteventname(),
       decode(a.state,
              0,
              '0 = Ready',
              1,
              '1 = Delayed',
              2,
              '2 = Retained/Processed',
              3,
              '3 = Exception',
              to_char(a.state)) state,
       a.user_data.PARAMETER_LIST,
       a.user_data.event_data,
       a.user_data.event_key,
       a.*
  FROM apps.wf_deferred a
 WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
   AND rownum < 10;

Query for fetching claim from ChRM/Trade Management

Below is a basic query to fetch claims, claim types and status. Can add more columns if needed.

SELECT      (SELECT vendor_name
                        FROM apps.ap_suppliers aps
                          where oca.vendor_id = aps.vendor_id) vendor_name,
       oca.claim_id,
       oca.claim_number,
       oca.creation_date,
       oct.name,
       claim_date,
       oca.amount,
       aus.name user_status,
  FROM
       apps.ozf_claims_all oca,
       apps.ozf_claim_types_all_tl oct,
       apps.ams_user_statuses_vl aus
 WHERE     oca.claim_type_id = oct.claim_type_id
       AND aus.user_status_id = oca.user_status_id;

Query to find letters sent to customers by Advanced collections/Dunning

Below query can be used for fetching the mails that were sent out to customers by advanced collections. You can add more columns if you need and change the date accordingly:

SELECT ixr.status,
       ixr.destination,
       ixr.creation_date,
       hp.party_name,
       hca.account_number,
       hcsa.cust_acct_site_id
  FROM IEX_XML_REQUEST_HISTORIES ixr,
       hz_cust_site_uses_all hcsua,
       hz_cust_acct_sites_all hcsa,
       hz_cust_accounts hca,
       hz_parties hp
 WHERE     ixr.creation_date > '10-AUG-2013'
       AND ixr.method = 'EMAIL'
       AND ixr.cust_site_use_id = hcsua.site_use_id
       AND hcsua.cust_acct_site_id = hcsa.cust_acct_site_id
       AND hcsa.cust_account_id = hca.cust_account_id
       AND hca.party_id = hp.party_id;

Query for fetching all responsibilities a user has

We can use below simple query to find all the responsibilities a user has:

SELECT
              (SELECT responsibility_name
                  FROM fnd_responsibility_vl frv
               WHERE fur.responsibility_id = frv.responsibility_id
                    AND nvl(frv.end_date,sysdate+1)  >  sysdate) responsibility_name,
              fur.end_date
  FROM apps.fnd_user_resp_groups_direct fur,
              fnd_user fu
 WHERE fu.user_id = fur.user_id
      AND fu.user_name = '&USERNAME';

Unix/Linux mail commands

While working on a unix based application, I used uuencode command to send mails with attachments. This also helps in avoiding FTP for small files

Below is the command:

uuencode $ATTFILE $ATTFILE | mail -s "$SUBJECT" $MAILTO

Here 

$ATTFILE is the full file name with extension

$SUBJECT is what we want to see in the subject of the mail

$MAILTO is the TO mail address

When I tried the same command to send mail in a Linux machine, it gave junk values. So, I used mutt command. Below is the syntax:

mutt -s $SUBJECT $MAILTO -a $ATTFILE1 $ATTFILE2 < mail_body.txt

$ATTFILE is the full file name with extension
$SUBJECT is what we want to see in the subject of the mail
$MAILTO is the TO mail address

mail_body.txt is what we want to see in the email body

Error while opening the forms (identifier APP_ITEM_PROPERTY.SET_PROPERTY must be declared)

Below learning was shared by one of my teammates:

While compiling forms in form builder sometimes we get some standard errors like –

“identifier APP_ITEM_PROPERTY.SET_PROPERTY must be declared”

“identifier APP_WINDOW.CLOSE_FIRST_WINDOW must be declared”

“identifier FND_STANDARD.SET_WHO must be declared”

This happens because some standard library functions will be unavailable in your form builder library.

You can resolve this by placing all standard libraries required into your ‘LIB’ folder in the following path – C:\Oracle_Home (Wherever oracle forms is installed)

You can verify the same by checking if the below files are present in your ‘Attached Libraries’ in your form builder – (this is for 6i version)

        APPCORE.pll

                    APPDAYPK.pll

                    GLOBE.pll

                    FNDSQF.pll

                    JA.pll

                    JE.pll

                    JL.PLL

                    GHR.pll

                    CUSTOM.pll

If you don't have these in your local machine,FTP these in binary mode from $AU_TOP/resource

System Profiles and values

From beginner's point of view, Profiles are something similar to global variables used by Oracle to set context based values based on which the application behavior can be controlled. There are seeded profiles(part and parcel with EBS) and we have flexibility to define custom profiles. We can control the values which can be allowed to enter as well. We need to be careful while changing values for seeded profiles as it could impact the system behavior.

Oracle allows the profile values to be set at different Levels i.e

• Site Level
• Application Level
• Responsibility Level
• User Level

The hierarchy that is followed in Oracle Applications to get the value of the profile is

• User Level
• Responsibility Level
• Application Level
• Site Level

User Level is the lowest level and oracle takes this value (if it is set) for a logged in user

Some profiles are set at responsibility level (Eg: Ledger ID) which is applicable whenever that responsibility is accessed

Site level is the highest level; if no application/resp/user values were set,this value will be considered 

Once the value is set a record gets inserted into fnd_profile_option_values. One record gets inserted for each level’s value for that profile. Oracle uses the following values for the level_id column to identify different levels

10001 è Site,

10002 è Application,

10003 è Responsibility,

10004 è User

If we are retrieving the value for a profile using SQL then we must mention the level as well in where clause or else it will go into too many rows exception.

The sql to get the values of a profile for all levels is as below

SELECT   SUBSTR (e.profile_option_name, 1, 35) PROFILE,

         DECODE (a.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Resp',

                 10004, 'User'

                ) LEVEL,

         DECODE (a.level_id,

                 10001, 'Site',

                 10002, c.application_short_name,

                 10003, b.responsibility_name,

                 10004, d.user_name

                ) lvalue,

         NVL (a.profile_option_value, 'Is Null') VALUE

    FROM fnd_profile_option_values a,

         fnd_responsibility_tl b,

         fnd_application c,

         fnd_user d,

         fnd_profile_options e

   WHERE e.profile_option_name LIKE '%MO%'

     AND e.profile_option_id = a.profile_option_id

     AND a.level_value = b.responsibility_id(+)

     AND a.level_value = c.application_id(+)

     AND a.level_value = d.user_id(+)

ORDER BY 1, 2