Clarity

Hi All, 

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. 

Regards, 

Siddharth Chawla.  

Have you checked this?

How to Capture Each Approved Action Item in a Process 

https://communities.ca.com/message/241693903 

NJ

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.

Hi,

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_date
From
bpm_run_processes rp
inner join bpm_def_process_versions bdpv on rp.process_version_id=bdpv.id
inner 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_id
inner join bpm_def_steps bds on bds.id=rs.step_id
inner join bpm_run_step_action_results ar on rs.id=ar.step_instance_id
inner join bpm_def_step_actions bdsa on ar.step_action_id =bdsa.id
inner join cal_action_items ai on ar.id= ai.process_handler_id
inner 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_id
inner 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_id
left outer join BPM_RUN_ASSIGNEE_NOTES usr_nt on usr_nt.RUN_ASSIGNEE_ID=ar_run.id
inner join bpm_run_objects obj on obj.object_id=rp.id and obj.Table_name='BPM_RUN_PROCESSES'

Regards,

Prashank Singh