Clarity PPM1

Expand all | Collapse all

Determining which process generated an action item and who took action on the action item ?

  • 1.  Determining which process generated an action item and who took action on the action item ?

    Posted 03-29-2017 02:58 PM

    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.  



  • 2.  Re: Determining which process generated an action item and who took action on the action item ?

    Posted 03-30-2017 12:54 AM

    Have you checked this?

     

    How to Capture Each Approved Action Item in a Process 

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

     

    NJ



  • 3.  Re: Determining which process generated an action item and who took action on the action item ?

    Posted 03-30-2017 03:04 AM

    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.



  • 4.  Re: Determining which process generated an action item and who took action on the action item ?

    Posted 04-18-2017 05:23 AM

    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