PO Workflow Error Check

1.
select * from wf_item_activity_statuses where ITEM_TYPE in ('POAPPRV') and ACTIVITY_STATUS ='&activity_status'
order by item_key

• 
  

  ACTIVITY_STATUS 

  --------------- 

  SUSPEND         

  COMPLETE        

  NOTIFIED        

  ERROR           

  ACTIVE          

  WAITING         

  DEFERRED  

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;