Tuesday, 5 September 2017

Query to get PO approval activity workflow history

Query to get Po approval activity workflow history 

SELECT execution_time,
  ias.begin_date,
  ap.name Process,
  ap.display_name ,
  ac.name Activity,
  ac.display_name ,
  ias.activity_status,
  ias.activity_result_code,
  ias.assigned_user,
  ias.notification_id ,
  ntf.status,
  ias.error_name,
  ias.error_message        error_message,
  ias.error_stack          error_stack
FROM applsys.wf_item_activity_statuses ias,
  applsys.wf_process_activities pa,
  apps.wf_activities_vl ac,
  apps.wf_activities_vl ap,
  applsys.wf_items i,
  applsys.wf_notifications ntf
WHERE 1=1
and ias.item_type        = 'POAPPRV'
AND ias.item_key   = '727622-1234'
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            = 'POAPPRV'
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)
AND ntf.notification_id(+)= ias.notification_id
ORDER BY 2,1;

1 comment:

AP Invoice Notes related Table details in Oracle Fusion

 What is the table that stores the AP Invoice notes? AP notes are stored in ZMM_NOTES table with SOURCE_OBJECT_CODE = 'AP_STANDARD_INVOI...