Friday, July 29, 2016

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'

No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...