Clarity

Good day everyone -

Hoping I can get some help from all the smart folks out here. We are on 13.2 and I don't have a DBA with Clarity experience available and need some guidance to be able to provide correct field data to someone who can write a script for me to put into a process:

I need to be able to update the ootb fields on the Change Request Object:

Approved By
Approved Date

I know these are found on the RIM_CHANGE_REQUESTS table in the database

These need to be udpated with the name of the person who received the Action Item (and changed the status of the Change Request) to Approved. I also need to pick up the date and timestamp and have them update the Approved Date field.

I have been struggling to find where in the database the Process or Action Item data for these is kept so a script can be written to update the Change Request table via a step in the Change Request Approval Process. The Tech Reference isn't helping much.

Thanks in advance for sharing your knowledge!

Ruthann

Hi Ruthann,

the following query lists the Action Item assignees and the assignee status for an Action Item generated in the step 'Start' of the process definition 'z_test_cr'. The process instance id ( ${gel_objectInstanceId} ) is hier the 5026021.

select aia.assignee_id, aia.status_code
from bpm_run_step_action_results run_ai               
inner join cal_action_items ai on run_ai.id = ai.process_handler_id        
inner join cal_action_item_assignees aia on AIA.CAL_ACTION_ITEM_ID = ai.id 
inner join bpm_run_steps rs on rs.id = run_ai.step_instance_id
inner join bpm_def_steps ds  on ds.id = rs.step_id
inner join bpm_def_stages dsta on dsta.id = ds.stage_id
inner join bpm_def_process_versions pv  on pv.id = dsta.process_version_id
inner join bpm_def_processes dp on dp.id = pv.process_id
where rs.process_instance_id = 5026021
and dp.process_code = 'z_test_cr'
and ds.step_code = 'Start'            

The query must be run before the processe is done (in a GEL script) and the Action Items in steps must have the option "Make Action Item available to other Steps" set to TRUE.
Your developer will have to check which assignee has the right status_code (according to your ai choices) and set the approved_by to the assignee_id.

Useful?

Best regards,

Sergiu Gavrila

------------------
Clarity Add-Ons www.itdesign.de/itd-addons/
Real Time Workload Analytics meisterplan.com

Thank you, Sergiu for the quick response. I will work with the developer to get this into place in test in the next day or two.

Best regards,

Ruthann