Need BPM Process Table Technical Reference

[Legacy User]

Any one have the a BPM Process Tables Technical Reference Document ?

[Anon Anon]

Hi,

Not able to find the full process related tables in clarity v12 Technical Reference Guide.In there only 1 table is mentioned in Action Items Entity Relationship Detail Diagram.

The process related tables start with BPM_.

i have got one from CA world which is shown below.You can search for sql queries in this forums posts.

Cheers,
sundar

[Deactivated User]

In r8.1SP3 the BPM tables are

BPM_CUSTOM_SCRIPT_PROPS
BPM_DEF_ASSIGNEES
BPM_DEF_OBJECTS
BPM_DEF_PROCESS_VERSIONS
BPM_DEF_PROCESSES
BPM_DEF_STAGES
BPM_DEF_STEP_ACTION_PARAMS
BPM_DEF_STEP_ACTIONS
BPM_DEF_STEP_AI_ACTIONS
BPM_DEF_STEP_CONDITIONS
BPM_DEF_STEP_TRANSITIONS
BPM_DEF_STEPS
BPM_EM_EVENT_PROCESS_LOCKS
BPM_EM_OBJECT_EVENTS
BPM_ERROR_ARGUMENTS
BPM_ERRORS
BPM_RUN_ASSIGNEE_NOTES
BPM_RUN_ASSIGNEES
BPM_RUN_OBJECTS
BPM_RUN_PROCESS_ENGINE_RUNS
BPM_RUN_PROCESS_ENGINES
BPM_RUN_PROCESSES
BPM_RUN_REPLACE_ASSIGNEES
BPM_RUN_STEP_ACTION_RESULTS
BPM_RUN_STEP_COND_OBJECTS
BPM_RUN_STEP_COND_RESULTS
BPM_RUN_STEP_TRANSITIONS
BPM_RUN_STEPS
BPM_RUN_THREADS
BPM_SESSION_DATA

Martti K.

[Anon Anon]

We have a workflow for project approval that issues action items to various management and business users to approve/reject projects. The workflow is working fine. But now I am trying to report on for every rejected project, who was the one who rejected the project.

I have found the action items issued by the process in the CAL_ACTION_ITEMS and CAL_ACTION_ITEM_ASSIGNEES tables, by specifying the object_id in the cal_action_items table. Depending on the project size, I might have a dozen or two action items associated with a single project approval. This gives me the action item itself and the user id who was assigned to the action item. I found I could join with the BPM_run_step_action_results table on the AI_ID column. But I am looking for a resolution of "approved" or "rejected". It looks like the table bpm_run_assignees has what I am looking for in the AI_STATUS_CODE, but what columns should I join with to be sure I am looking at the right action item(s)?

[Anon Anon]

I found I could join with the BPM_run_step_action_results table on the AI_ID column. But I am looking for a resolution of "approved" or "rejected". It looks like the table bpm_run_assignees has what I am looking for in the AI_STATUS_CODE

I don't think you need to go to the bpm_run_assignees table? Isn't the information you need just in the CAL_ACTION_ITEMS and CAL_ACTION_ITEM_ASSIGNEES tables (i.e. looking for CAL_ACTION_ITEM_ASSIGNEES.STATUS_CODE and then using the ASSIGNEE_ID)?

[Anon Anon]

Thank you Dave. That's what I thought too (and was hoping for). Unfortunately, in the CAL_ACTION_ITEM_ASSIGNEES table, the STATUS_CODE for this completed process is equal to CAL_CLOSED for all action item assignees. What I'm looking for is where Clarity retrieves the data for the attached screen shot?

Attachment(s)

screen shots.doc   76 KB 1 version

[Anon Anon]

hi this is a blind shot, but have you tried using bpm_run_assignees.ai_status_code?

The other bit of checking that I could suggest is checking the URLs for any hints as to where it's picking up data...

[Anon Anon]

Samos,
I think this is on the right track. But I am unable to figure out how to join the responses in this table to the bpm_run_processes table. I suspect the key is in the PK_ID column, but what table does that id point to?

[Anon Anon]

suep,

you can try the below join condition if this helps for you -


BPM_RUN_OBJECTS o
left join prj_projects pp
on o.object_id = pp.prid
inner join bpm_run_processes rp
on o.pk_id=rp.id and
o.table_name='BPM_RUN_PROCESSES'
inner join BPM_DEF_PROCESS_VERSIONS pv
on rp.process_version_id =pv.id
inner join BPM_DEF_PROCESSES pd
on pv.process_id = pd.id
inner join CMN_CAPTIONS_NLS proc_label
on pd.id=proc_label.pk_id and
proc_label.table_name='BPM_DEF_PROCESSES' and
proc_label.language_code='en'
inner join bpm_run_steps rs
on rp.id=rs.process_instance_id
inner join bpm_def_steps ds
on rs.STEP_ID=ds.id
inner join cmn_captions_nls cap
on ds.id=cap.pk_id and
cap.table_name='BPM_DEF_STEPS' and cap.language_code='en'
left join bpm_run_step_transitions st
on rs.id=st.STEP_INSTANCE_ID
left join bpm_run_steps nextstep
on st.next_STEP_INSTANCE_ID=nextstep.id
left join bpm_run_step_action_results ar
on rs.id=ar.step_instance_id
left join bpm_run_assignees ra
on ar.id=ra.pk_id


Thanks,
Rahul

[Deactivated User]

[Deactivated User]

[Daniel Perez]

What about  BPM_RUN_PROCESS_ENGINE_RUNS?

Some columns  seems to have interesting information  for troubleshooting but I don't know how to interpret this information.

[Deactivated User]

Start a new question on this. It will get more attention.