Clarity

Hello,

I wish I had a view of the planned processes with their names.

I use the following query NSQL

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:JB.ID||RS.LAST_NAME||' '||RS.FIRST_NAME:RESSNAME@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:RS.LAST_NAME||' '||RS.FIRST_NAME:NOM_Utilisateur@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:Rs.ID:Ressource_ID@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.name:JOB_NAME@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.start_date:Date_Debut@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.end_date:date_Fin@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.schedule_date:Date_Planif@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.hours:Heures@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.minutes:Minutes@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.months:Mois@,

         @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.days_of_month:Jour_du_mois@

from CMN_SCH_JOBS JB

INNER JOIN CMN_SEC_USERS RS ON RS.ID=JB.created_by

where status_code like 'SCHEDULED'

      AND @FILTER@

But I lack the means to retrieve the name of the process. Can anyone help me?

Thanks

Do you mean "Process" or "Job" - your query is just looking at job tables - some more information on those tables here  TIP: ** Bonus Tip** Query: What Jobs/Reports Ran Over the Last n Days?

I think that the job name is on the cmn_sch_job_definitions table though.

--

For "process", it would be all the various "bpm" tables.

I think this is what you're asking for...

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:JB.ID||RS.LAST_NAME||' '||RS.FIRST_NAME:RESSNAME@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:RS.LAST_NAME||' '||RS.FIRST_NAME:NOM_Utilisateur@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:Rs.ID:Ressource_ID@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.name:JOB_NAME@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:DECODE( CSJD.JOB_CODE, 'EXECUTE_NON_OBJECT_PROCESS', PROCS.PROCESS_NAME, 'N/A'):PROCESS_NAME@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.start_date:Date_Debut@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.end_date:date_Fin@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.schedule_date:Date_Planif@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.hours:Heures@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.minutes:Minutes@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.months:Mois@,
  @select:dim_prop:user_def:IMPLIED:RESOURCE:JB.days_of_month:Jour_du_mois@
from CMN_SCH_JOBS JB INNER JOIN 
  CMN_SEC_USERS RS ON RS.ID=JB.created_by INNER JOIN
  CMN_SCH_JOB_DEFINITIONS CSJD ON 
  JB.JOB_DEFINITION_ID = CSJD.ID LEFT OUTER JOIN
  (
  SELECT BUSINESS_OBJECT_INSTANCE_ID CMN_SCH_JOBS_ID,
  ATTRIBUTE_VALUE PROCESS_NAME
  FROM CMN_BUSINESS_OBJECTS CBO INNER JOIN 
  CMN_ATTRIBUTE_VALUE_SETS CAVS ON 
  CBO.ID = CAVS.BUSINESS_OBJECT_ID INNER JOIN 
  CMN_ATTRIBUTE_VALUES CAV ON 
  CAVS.ID = CAV.ATTRIBUTE_SET_ID INNER JOIN 
  CMN_ATTRIBUTES CA ON 
  CAV.ATTRIBUTE_ID = CA.ID 
  WHERE CA.COLUMN_NAME = 'param_process_code_text' AND 
  CBO.PK_ID = (SELECT ID FROM CMN_SCH_JOB_DEFINITIONS WHERE JOB_CODE = 'EXECUTE_NON_OBJECT_PROCESS')  AND  
  CBO.TABLE_NAME = 'CMN_SCH_JOB_DEFINITIONS' 
  ) PROCS ON 
  JB.ID = PROCS.CMN_SCH_JOBS_ID
where status_code like 'SCHEDULED' AND 
  @FILTER@

I forgot to mention that it's for Oracle. For SQL Server you would need to swap out the DECODE with a CASE statement.

Thank you for your help.

This is exactly what I wanted.