How To Identify All Requisitions And Purchase Orders With Errored Activities

Ref :Metalink Note:464407.1

The following scripts have been provided to return requisitions and purchase orders with erroredactivities:

SELECT prh.segment1,

prh.org_id,

prh.authorization_status,

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

po_requisition_headers_all prh,

wf_item_activity_statuses ias,

wf_process_activities pa,

wf_activities_vl ac,

wf_activities_vl ap,

wf_items i

WHERE ias.item_type = 'REQAPPRV'

AND ias.item_key = prh.wf_item_key

AND ias.item_type = prh.wf_item_type

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 = 'REQAPPRV'

AND i.item_key = ias.item_key

AND i.begin_date >= ac.begin_date

AND i.begin_date

ORDER BY prh.segment1, ias.execution_time

Purchase Orders:

SELECT

pha.segment1,

pha.org_id,

pha.authorization_status,

ac.display_name Activity,

ias.activity_result_code Result,

ias.error_name ERROR_NAME,

ias.error_message ERROR_MESSAGE,

ias.error_stack ERROR_STACKFROM

po_headers_all pha,

wf_item_activity_statuses ias,

wf_process_activities pa,

wf_activities_vl ac,

wf_activities_vl ap,

wf_items i

WHERE ias.item_type = 'POAPPRV'

AND ias.item_key = pha.wf_item_key

AND ias.item_type = pha.wf_item_type

AND pha.authorization_status IN ('IN PROCESS', 'PRE-APPROVED')

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 = 'POAPPRV'

AND i.item_key = ias.item_key

AND i.begin_date >= ac.begin_date

AND i.begin_date <>

ORDER BY pha.segment1, ias.execution_time;