Clarity

I'm trying to creating a portlet that would show the Approver's Name, Date of Approval, Approval Status and Document Name for any given project. Wondering if anyone has a SQL query that I could use.

Hi All,

Any quick thoughts here for this one?

Thanks!
Chris

Thank you everyone for your inputs. I finally got it together. Here is a working MSSQL Server version. Will soon post an ANSI SQL version as well.

This one lists all the the process related info. for a document workflow. I have it working in a portlet, which was my goal.

SELECT
brs.id step_id,
brp.id process_id,
ccn.name process_name,
dms_files.name file_name,
bdsv.name approver_role,
FULL_NAME approver_name,
CCN_ACTIONS.name action_name,
ASSIGNEES.LAST_UPDATED_DATE last_updated_date,
NOTES.NOTE approver_comments,
LOOKUP.NAME approval_status
FROM
BPM_RUN_ASSIGNEES ASSIGNEES,
BPM_RUN_ASSIGNEE_NOTES NOTES,
SRM_RESOURCES RESOURCES,
BPM_RUN_STEP_ACTION_RESULTS RUNACTION,
BPM_DEF_STEP_ACTIONS ACTION,
CMN_CAPTIONS_NLS ccn_actions,
CMN_LOOKUPS_V LOOKUP,
BPM_RUN_STEPS brs,
BPM_DEF_STEPS_V bdsv,
BPM_RUN_PROCESSES brp,
BPM_DEF_PROCESS_VERSIONS bdpv,
CMN_CAPTIONS_NLS ccn,
BPM_RUN_OBJECTS o,
BPM_DEF_OBJECTS def_obj,
clb_dms_files dms_files
WHERE
ASSIGNEES.ID *= NOTES.RUN_ASSIGNEE_ID AND
ASSIGNEES.PK_ID = RUNACTION.ID AND
ASSIGNEES.TABLE_NAME = 'BPM_RUN_STEP_ACTION_RESULTS' AND
ASSIGNEES.USER_ID = RESOURCES.USER_ID AND
ACTION.ID = RUNACTION.STEP_ACTION_ID AND
CCN_ACTIONS.PK_ID = ACTION.ID AND
CCN_ACTIONS.TABLE_NAME = 'BPM_DEF_STEP_ACTIONS' AND
CCN_ACTIONS.LANGUAGE_CODE = 'en' AND
LOOKUP.LOOKUP_CODE = ASSIGNEES.AI_STATUS_CODE AND
LOOKUP.LOOKUP_TYPE = ASSIGNEES.AI_STATUS_TYPE_CODE AND
LOOKUP.LANGUAGE_CODE = 'en'
AND brs.id = RUNACTION.step_instance_id
and brs.step_id = bdsv.id
and bdsv.language_code='en'
and bdpv.process_id = ccn.pk_id
AND ccn.table_name = 'BPM_DEF_PROCESSES'
AND brp.process_version_id = bdpv.id
AND ccn.language_code = 'en'and
brs.process_instance_id = brp.id
and (brp.id=o.pk_id and O.TABLE_NAME = 'BPM_RUN_PROCESSES')
and (o.object_type_code = def_obj.object_type_code AND def_obj.TABLE_NAME = 'BPM_DEF_PROCESS_VERSIONS'
AND def_obj.type_code = 'BPM_POT_PRIMARY' AND O.TABLE_NAME = 'BPM_RUN_PROCESSES' )
and ( brp.PROCESS_VERSION_ID = def_obj.pk_id )
and o.object_name='thisDocument' and dms_files.id = o.object_id
and dms_files.id in (
select id from clb_dms_files where parent_folder_id in (
select dms_folder_proj.id from clb_dms_folders dms_folder_proj,
clb_dms_folders top_level
where dms_folder_proj.parent_folder_id = top_level.id))

Knowing Chummar had to be something more complicated.
Thanks for the update and posting the query.

Martti K.

I am trying to do something similar but I can not get the project it is associated with. They are submitting the process from the collaboraton tab linked to a document then action> process. I see processes that are submitted from the PROCESES TAB but not from the Collaboration Tab. ANy ideas how to take the sql posted above and link it to the project?

Cheers,
Vivian

Here it is with project information. The query assumes that the document is in the first level folder under the root and the processes object name is "'thisDocument'". Hope this helps.

SELECT
brs.id step_id,
brp.id process_id,
ccn.name process_name,
dms_files.name file_name,
bdsv.name approver_role,
FULL_NAME approver_name,
CCN_ACTIONS.name action_name,
ASSIGNEES.LAST_UPDATED_DATE last_updated_date,
NOTES.NOTE approver_comments,
LOOKUP.NAME approval_status,
top_level.assoc_obj_id project_id,
proj.unique_name project_code,
proj.name project_name
FROM
BPM_RUN_ASSIGNEES ASSIGNEES,
BPM_RUN_ASSIGNEE_NOTES NOTES,
SRM_RESOURCES RESOURCES,
BPM_RUN_STEP_ACTION_RESULTS RUNACTION,
BPM_DEF_STEP_ACTIONS ACTION,
CMN_CAPTIONS_NLS ccn_actions,
CMN_LOOKUPS_V LOOKUP,
BPM_RUN_STEPS brs,
BPM_DEF_STEPS_V bdsv,
BPM_RUN_PROCESSES brp,
BPM_DEF_PROCESS_VERSIONS bdpv,
CMN_CAPTIONS_NLS ccn,
BPM_RUN_OBJECTS o,
BPM_DEF_OBJECTS def_obj,
clb_dms_files dms_files,
clb_dms_folders dms_folder_proj,
clb_dms_folders top_level,
srm_projects proj
WHERE
ASSIGNEES.ID *= NOTES.RUN_ASSIGNEE_ID AND
ASSIGNEES.PK_ID = RUNACTION.ID AND
ASSIGNEES.TABLE_NAME = 'BPM_RUN_STEP_ACTION_RESULTS' AND
ASSIGNEES.USER_ID = RESOURCES.USER_ID AND
ACTION.ID = RUNACTION.STEP_ACTION_ID AND
CCN_ACTIONS.PK_ID = ACTION.ID AND
CCN_ACTIONS.TABLE_NAME = 'BPM_DEF_STEP_ACTIONS' AND
CCN_ACTIONS.LANGUAGE_CODE = 'en' AND
LOOKUP.LOOKUP_CODE = ASSIGNEES.AI_STATUS_CODE AND
LOOKUP.LOOKUP_TYPE = ASSIGNEES.AI_STATUS_TYPE_CODE AND
LOOKUP.LANGUAGE_CODE = 'en'
AND brs.id = RUNACTION.step_instance_id
and brs.step_id = bdsv.id
and bdsv.language_code='en'
and bdpv.process_id = ccn.pk_id
AND ccn.table_name = 'BPM_DEF_PROCESSES'
AND brp.process_version_id = bdpv.id
AND ccn.language_code = 'en'and
brs.process_instance_id = brp.id
and (brp.id=o.pk_id and O.TABLE_NAME = 'BPM_RUN_PROCESSES')
and (o.object_type_code = def_obj.object_type_code AND def_obj.TABLE_NAME = 'BPM_DEF_PROCESS_VERSIONS'
AND def_obj.type_code = 'BPM_POT_PRIMARY' AND O.TABLE_NAME = 'BPM_RUN_PROCESSES' )
and ( brp.PROCESS_VERSION_ID = def_obj.pk_id )
and o.object_name='thisDocument' and dms_files.id = o.object_id
and dms_files.parent_folder_id = dms_folder_proj.id
and dms_folder_proj.parent_folder_id = top_level.id
and top_level.assoc_obj_id=proj.id

:#:lol:

Thanks for the sql join to projects via folders. I was pulling my hair out!

Cheers!
Vivian

Check the below thread -

57092834

NJ

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

Also attached is the ERD in v12

NJ

Please clarify:

Process Approval Portlet

I'm trying to creating a portlet that would show the Approver's Name, Date of Approval, Approval Status and Document Name for any given project. Wondering if anyone has a SQL query that I could use.

Are you talking about Process or Project?

I can relate Approver's Name, Date of Approval, Approval Status to a project not to a process nor to a document


Martti K.