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