I am trying to find the names of the timesheet approver who approved a timesheet.
We are using a custom process, which sends an action item to a resource manager and a proxy RM(if set) and then sets a timesheet status and if it is approved then the approved-by will show -process_admin. This is where the problem begins- from there I am not able to find the action items generated by this process, for whom were they created and for whom were they assigned to.
I tried researching the CAL_ACTION_ITEM_ASSIGNEES and CAL_ACTION_ITEMS tables, but I found nothing useful which I can use to fetch the information which I need. I could only make out that every process instance runs with a unique id and each process creates a unique action item. Bottom line- basically no join condition which i was able to find, or unique identifier which even identifies the custom process.
If anyone can provide me some information/tips on how can I achieve the above-stated, it will be highly helpful.
Have you checked this?
How to Capture Each Approved Action Item in a Process
To get the Process that triggered the Action Item, the join would be BPM_RUN_PROCESSES.ID = CAL_ACTION_ITEMS.OBJECT_ID.
To determine who took action on it, the join would be CAL_ACTION_ITEMS_ASSIGNEES.ASSIGNEE_ID = SRM_RESOURCES.USER_ID
You would ofcourse need to join CAL_ACTION_ITMES and CAL_ACTION_ITEMS_ASSIGNEES on their IDs as well.
I have created a sql written for orcale environment, might be os some help.
Select ai.id||asgn.assignee_id unique_id, bdp.process_code process_id, bds.step_code step_id, bdsa.action_code action_id, asgn.assignee_id ai_user_dbid, res.unique_name ai_user_id, res.full_name ai_user_name, res.email ai_user_email, ai.id ai_dbid, ai.created_date ai_created_date, LISTAGG(usr_nt.note, '; ') WITHIN GROUP (ORDER BY ai.id||asgn.assignee_id) OVER (PARTITION BY ai.id||asgn.assignee_id ) ai_comment, asgn.status_code status_code, Case when asgn.status_code='CAL_CLOSED' then ar_run.ai_status_code else asgn.status_code end ai_status, ai.due_date ai_due_dateFrom bpm_run_processes rpinner join bpm_def_process_versions bdpv on rp.process_version_id=bdpv.idinner join bpm_def_processes bdp on bdp.id=bdpv.process_id -- and bdp.process_code in (?)inner join bpm_run_steps rs on rp.id=rs.process_instance_idinner join bpm_def_steps bds on bds.id=rs.step_idinner join bpm_run_step_action_results ar on rs.id=ar.step_instance_idinner join bpm_def_step_actions bdsa on ar.step_action_id =bdsa.idinner join cal_action_items ai on ar.id= ai.process_handler_idinner join cal_action_item_assignees asgn ON ai.id = asgn.cal_action_item_id --and asgn.status_code='CAL_OPEN'inner join srm_resources res on res.user_id=asgn.assignee_idinner join bpm_run_assignees ar_run on ar_run.pk_id=ar.id and ar_run.table_name='BPM_RUN_STEP_ACTION_RESULTS' and ar_run.user_id=asgn.assignee_idleft outer join BPM_RUN_ASSIGNEE_NOTES usr_nt on usr_nt.RUN_ASSIGNEE_ID=ar_run.idinner join bpm_run_objects obj on obj.object_id=rp.id and obj.Table_name='BPM_RUN_PROCESSES'