Workflow Related Queries Link between PO and Work flow , Open Notifications - Req ,Open Notifications - PO

Here are couple of scripts. Haven't included the basic scripts for finding 

the po_header_id,WF item attributes. 

*

*Action History - PO 

* 

select poah.sequence_num seq#, poah.action_date, poah.action_code, 

poah.employee_id emp_id, fnd.user_name, 

substr(poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type, 

poah.object_revision_num rev, pohead.org_id 

from po_action_history poah, fnd_user fnd, po_headers_all pohead 

where poah.object_id = pohead.po_header_id 

and pohead.segment1 = '&PO_NUMBER' 

and pohead.org_id = '&ORG_ID' 

and substr(poah.object_type_code,1,3) = 'PO' 

and poah.employee_id = fnd.employee_id 

and fnd.session_number != 0 

order by 2,1; 

*Action History - Req * 

select poah.sequence_num seq#, poah.action_date, poah.action_code, 

poah.employee_id emp_id, fnd.user_name, 

substr(poah.object_type_code,1,3) type, poah.object_sub_type_code sub_type, 

poah.object_revision_num rev, pohead.org_id 

from po_action_history poah, fnd_user fnd, po_requisition_headers_all pohead 

where poah.object_id = pohead.requisition_header_id 

and pohead.segment1 = '&REQ_NUMBER' 

and substr(poah.object_type_code,1,3) = 'REQ' 

and pohead.org_id = '&ORG_ID' 

and poah.employee_id = fnd.employee_id 

and fnd.session_number != 0 

order by 9,2,1; 

*Open Notifications - Req * 

select reqh.segment1 req, wfn.status notify_status, wias.notification_id 

notify_id, 

wfn.to_user name, reqh.org_id org 

from wf_item_activity_statuses wias, 

wf_notifications wfn, 

po_requisition_headers_all reqh 

where wias.notification_id is not null 

and wias.notification_id = wfn.group_id 

and wfn.status = 'OPEN' 

and wias.item_type = 'REQAPPRV' 

and wias.item_key = reqh.wf_item_key 

and reqh.authorization_status IN ('IN PROCESS') 

order by 5,1,3; 

*Open Notifications - PO 

* 

select poh.segment1 po, wfn.status notify_status, wias.notification_id 

notify_id, 

wfn.to_user name, poh.org_id org 

from wf_item_activity_statuses wias, 

wf_notifications wfn, 

po_headers_all poh 

where wias.notification_id is not null 

and wias.notification_id = wfn.group_id 

and wfn.status = 'OPEN' 

and wias.item_type = 'POAPPRV' 

and wias.item_key = poh.wf_item_key 

and poh.authorization_status IN ('IN PROCESS','PRE-APPROVED') 

order by 4,5,1,3; 

*Hierarchy By Employee ID * 

SELECT superior_id, hrec.full_name ,poeh.superior_level 

FROM hr_employees_current_v hrec, po_employee_hierarchies poeh, 

PO_DOCUMENT_TYPES_ALL pdt 

WHERE pdt.org_id = 19897 

AND pdt.DOCUMENT_TYPE_CODE = 'REQUISITION' 

AND pdt.DOCUMENT_SUBTYPE = 'PURCHASE' 

AND pdt.default_approval_path_id = poeh.position_structure_id 

AND poeh.employee_id = 100958 

AND hrec.employee_id = poeh.superior_id 

AND poeh.superior_level > 0 

ORDER BY poeh.superior_level, hrec.full_name; 

SELECT prh.segment1 req_number, 

wf.from_user, 

wf.to_user, 

prh.authorization_status 

FROM po_action_history pah, 

po_requisition_headers_all prh, 

wf_notifications wf, 

wf_items wi 

WHERE pah.object_type_code = 'REQUISITION' 

AND pah.action_code IS NULL 

AND pah.object_id = prh.requisition_header_id 

AND wi.item_type = 'REQAPPRV' 

AND prh.wf_item_type = wi.item_type 

AND wi.parent_item_key = prh.wf_item_key 

AND wi.item_key = wf.item_key 

AND wf.message_type = 'REQAPPRV'