Clarity

Hello

I have Change Request processes. An action item is send to manager depending on workflow conditions. Manager enters notes into action items.

We need to query Project Code, Project Name, Change Request Code, Action Item Subject, Action Item Notes

I have a solution for notes below, but I couldn't relate this with project and change request tables.

King regards

select csu.id, userId, csu.first_name||' '||csu.last_name username, bran.id noteId, to_char(bran.note),bra.ai_status_code aiStatus,

brs.id bsId,brsar.id actionId, brs.step_id stepId,bran.created_date noteCreateDate

from

bpm_run_assignees bra left outer join bpm_run_assignee_notes bran on bra.id=bran.run_assignee_id,

BPM_RUN_STEP_ACTION_RESULTS brsar,

cmn_sec_users csu,

bpm_run_steps brs,

bpm_run_processes brp

where

brs.process_instance_id=brp.id and

brsar.step_instance_id=brs.id and

bra.pk_id=brsar.id and csu.id=bra.user_id

and bra.ai_status_code not IN('CAL_OPEN')

and brs.status_code='BPM_SIS_DONE'

and brs.process_instance_id=6349960 -- my process id

You should just be able to get this info from joining from CAL_ACTION_ITEMS

CAL_ACTION_ITEMS.SUB_OBJECT_ID will be your change ID and CAL_ACTION_ITEMS.OBJECT_ID will be your project id.

(also look at OBJECT_TYPE and SUB_OBJECT_TYPE in CAL_ACTION_ITEMS to qualify the action item)
 

None of the BPM tables need to be referenced.

RIM_RISKS_AND_ISSUES.ID = CAL_ACTION_ITEMS.SUB_OBJECT_ID
INV_INVESTMENTS.ID = CAL_ACTION_ITEMS.OBJECT_ID

OK not sure about that sorry
 

Use  BPM_RUN_OBJECTS to get the object on which the process has run..by providing OBJECT_TYPE_CODE and OBJECT_ID you can get respecitive instance.

BPM_RUN_PROCESSES BRP,
BPM_RUN_OBJECTS BRO

BRP.ID=BRO.PK_ID
AND BRO.TABLE_NAME='BPM_RUN_PROCESSES'

Thanks all for your answers, I've solved my problem. Here is the solution :

select inv.code, inv.name project_name, CHG.RIM_RISK_ISSUE_CODE, chg.id, chg.name change_name, RA.AI_STATUS_CODE, to_char(ran.NOTE)

from BPM_RUN_ASSIGNEE_NOTES ran

inner join BPM_RUN_ASSIGNEES ra on ran.RUN_ASSIGNEE_ID = ra.ID

inner join BPM_RUN_STEP_ACTION_RESULTS rsar on rsar.ID = ra.PK_ID

inner join BPM_RUN_STEPS rs on rs.ID = rsar.STEP_INSTANCE_ID

inner join BPM_DEF_STEPS bdf on rs.STEP_ID = bdf.id

inner join BPM_DEF_STEP_ACTIONS bdsa on rsar.step_action_id = bdsa.id

inner join bpm_run_objects obj on OBJ.PK_ID=RS.PROCESS_INSTANCE_ID and obj.table_name='BPM_RUN_PROCESSES' and OBJECT_TYPE_CODE='change'

inner join RIM_RISKS_AND_ISSUES chg on chg.id=OBJ.OBJECT_ID and chg.table_name='SRM_PROJECTS'

inner join inv_investments inv on inv.id=chg.pk_id