The blog provides the Oracle SQL Query to fetch CREATEPO Workflow for the approved requisition. The Query requires the Requisition Number and particular ORG_ID for which approved requisition is to be determined.
Retrieve the Requisition Header Id
SELECT REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1 = '&REQ_NUMBER' AND ORG_ID = &ORG_ID;
Retrieve Item Type and Item Key by passing Requisition Header Id
SELECT ITEM_TYPE, ITEM_KEY FROM WF_ITEMS WHERE ITEM_KEY LIKE '&REQ_HDR_ID'||'%' AND ITEM_TYPE='CREATEPO' AND ROOT_ACTIVITY = 'OVERALL_AUTOCREATE_PROCESS';
Retrieve the Root Activity , item Type and Item Key by passing the Requisition Header Id
SELECT ITEM_TYPE, ITEM_KEY, ROOT_ACTIVITY
FROM WF_ITEMS
WHERE PARENT_ITEM_KEY LIKE '&REQ_HDR_ID'||'%'
AND ITEM_TYPE='CREATEPO'
AND ROOT_ACTIVITY IN ('REQ_LINE_PROCESSING', 'CREATE_AND_APPROVE_DOC');