The blog provides the Oracle Apps R12 SQL Query to get fetch PO approval workflow activity details
SELECT EXECUTION_TIME, WF_Item_Actvity_Status.BEGIN_DATE, WF_Actvity_tbl2.NAME PROCESS, WF_Actvity_tbl2.DISPLAY_NAME , WF_Actvity_tbl1.NAME ACTIVITY, WF_Actvity_tbl1.DISPLAY_NAME , WF_Item_Actvity_Status.ACTIVITY_STATUS, WF_Item_Actvity_Status.ACTIVITY_RESULT_CODE, WF_Item_Actvity_Status.ASSIGNED_USER, WF_Item_Actvity_Status.NOTIFICATION_ID , WF_NOTIF_TBL.STATUS, WF_Item_Actvity_Status.ERROR_NAME, WF_Item_Actvity_Status.ERROR_MESSAGE , WF_Item_Actvity_Status.ERROR_STACK FROM APPLSYS.WF_ITEM_ACTIVITY_STATUSES WF_Item_Actvity_Status, APPLSYS.WF_PROCESS_ACTIVITIES WF_Proc_Actvity, APPS.WF_ACTIVITIES_VL WF_Actvity_tbl1, APPS.WF_ACTIVITIES_VL WF_Actvity_tbl2, APPLSYS.WF_ITEMS WF_ITEM_TBL, APPLSYS.WF_NOTIFICATIONS WF_NOTIF_TBL WHERE 1=1 AND WF_Item_Actvity_Status.ITEM_TYPE = 'POAPPRV' AND WF_Item_Actvity_Status.ITEM_KEY = '<ENTER_ITEM_KEY>' AND WF_Item_Actvity_Status.PROCESS_ACTIVITY = WF_Proc_Actvity.INSTANCE_ID AND WF_Proc_Actvity.ACTIVITY_NAME = WF_Actvity_tbl1.NAME AND WF_Proc_Actvity.ACTIVITY_ITEM_TYPE = WF_Actvity_tbl1.ITEM_TYPE AND WF_Proc_Actvity.PROCESS_NAME = WF_Actvity_tbl2.NAME AND WF_Proc_Actvity.PROCESS_ITEM_TYPE = WF_Actvity_tbl2.ITEM_TYPE AND WF_Proc_Actvity.PROCESS_VERSION = WF_Actvity_tbl2.VERSION AND WF_ITEM_TBL.ITEM_TYPE = 'POAPPRV' AND WF_ITEM_TBL.ITEM_KEY = WF_Item_Actvity_Status.ITEM_KEY AND WF_ITEM_TBL.BEGIN_DATE >= WF_Actvity_tbl1.BEGIN_DATE AND WF_ITEM_TBL.BEGIN_DATE < NVL(WF_Actvity_tbl1.END_DATE, I.BEGIN_DATE+1) AND WF_NOTIF_TBL.NOTIFICATION_ID(+) = WF_Item_Actvity_Status.NOTIFICATION_ID ORDER BY 2,1;
Query to get Requisition Approval PathÂ
SELECT PER_POS_STRUC_TBL.NAME FROM PO_REQUISITION_HEADERS_ALL PO_REQ_HDR_TBL, WF_ITEM_ATTRIBUTE_VALUES WF_ITM_ATTR_VAL_TBL, PER_POSITION_STRUCTURES PER_POS_STRUC_TBL WHERE 1=1 AND WF_ITM_ATTR_VAL_TBL.ITEM_TYPE = PO_REQ_HDR_TBL.WF_ITEM_TYPE AND WF_ITM_ATTR_VAL_TBL.ITEM_KEY = PO_REQ_HDR_TBL.WF_ITEM_KEY AND WF_ITM_ATTR_VAL_TBL.NAME = 'APPROVAL_PATH_ID' AND TO_NUMBER(WF_ITM_ATTR_VAL_TBL.NUMBER_VALUE) = PER_POS_STRUC_TBL.POSITION_STRUCTURE_ID AND PO_REQ_HDR_TBL.SEGMENT1 = '<ENMTER_REQUISITION_NUMBER>' ; AND PO_REQ_HDR_TBL.ORG_ID = <ENTER_ORG_ID>;
Query to get Requisition Approval PathÂ
SELECT PER_POS_STRUC_TBL.NAME FROM po_headers_all poh PO_HDR_TBL, WF_ITEM_ATTRIBUTE_VALUES WF_ITM_ATTR_VAL_TBL, PER_POSITION_STRUCTURES PER_POS_STRUC_TBL WHERE 1=1 AND WF_ITM_ATTR_VAL_TBL.ITEM_TYPE = PO_HDR_TBL.WF_ITEM_TYPE AND WF_ITM_ATTR_VAL_TBL.ITEM_KEY = PO_HDR_TBL.WF_ITEM_KEY AND WF_ITM_ATTR_VAL_TBL.NAME = 'APPROVAL_PATH_ID' AND TO_NUMBER(WF_ITM_ATTR_VAL_TBL.NUMBER_VALUE) = PER_POS_STRUC_TBL.POSITION_STRUCTURE_ID AND PO_HDR_TBL.PO_HEADER_ID = <PO_HEADER_ID>; AND PO_HDR_TBL.ORG_ID = <ENTER_ORG_ID>;