Clarity PPM

Expand all | Collapse all

Report on Processes in Execution

Jump to Best Answer
  • 1.  Report on Processes in Execution

    Posted 09-07-2017 02:15 PM

    Hello, I contact with the comunity because I at this moment I do try to develop a new porlet that sow the process running but I have a problem with an INNER JOIN because in doing so it shows all the steps of the process and not the one that the process is currently in.

     

     

    The query that I did is the following:

     

    SELECT
    InvID InvID,
    NroGDM NroGDM,
    NombreGDM NombreGDM,
    Descripcion Descripcion,
    EtapaID EtapaID,
    EtapaNombre EtapaNombre,
    IdEjecutadoPor IdEjecutadoPor,
    NombreProceso NombreProceso,
    EstadoProceso EstadoProceso,
    EjecutadoPor EjecutadoPor,
    FechaInicio FechaInicio,
    PorcentajeCompletado PorcentajeCompletado,
    TiempoTranscurrido TiempoTranscurrido,
    PasoProceso PasoProceso

     

    FROM (
    SELECT
    I.ID InvID,
    I.CODE NroGDM,
    I.NAME NombreGDM,
    I.DESCRIPTION Descripcion,
    I.STAGE_CODE EtapaID,
    v_stage.name EtapaNombre,
    I.MANAGER_ID IdEjecutadoPor,
    descPro.NAME NombreProceso,
    RunPro.STATUS_CODE EstadoProceso,
    Init_User.FULL_NAME EjecutadoPor,
    RunPro.START_DATE FechaInicio,
    RunPro.PERCENT_COMPLETE PorcentajeCompletado,
    DateDiff(day,RunPro.START_DATE,GETDATE()) AS TiempoTranscurrido,
    DefSteps.STEP_CODE PasoProceso

     

    FROM BPM_DEF_Steps DefSteps

    INNER JOIN BPM_Def_Stages DefStages
    ON DefSTeps.Stage_Id = DefStages.id

     

    INNER JOIN BPM_Run_Processes RunPro
    ON RunPro.Process_Version_Id = DefSTeps.Stage_Id

     

    INNER JOIN BPM_Run_objects RunObj
    ON RunObj.pk_id = RunPro.id
    AND RunObj.Type_code = 'BPM_POT_PRIMARY'

     

    LEFT OUTER JOIN Inv_Investments I
    ON I.odf_object_code = RunObj.Object_Type_Code
    AND I.id = RunObj.Object_Id
    AND I.odf_object_code = RunObj.Object_Type_Code

     

    INNER JOIN Bpm_Def_Process_Versions DefVer
    ON DefVer.id = RunPro.PROCESS_VERSION_ID

    LEFT OUTER JOIN CMN_CAPTIONS_NLS descPro
    ON descPro.language_Code = 'en'
    AND descPro.table_Name = 'BPM_DEF_PROCESSES'
    AND descPro.pk_id = DefVer.Process_id

    LEFT JOIN CMN_LOOKUPS_V v_stage ON v_stage.LOOKUP_TYPE = 'INV_STAGE_TYPE'
    AND v_stage.LANGUAGE_CODE = 'en'
    AND v_stage.LOOKUP_CODE = I.STAGE_CODE

    LEFT OUTER JOIN SRM_RESOURCES Init_User
    ON Init_User.user_id = RunPro.Initiated_By

    WHERE STATUS_CODE like '%Running%'
    AND descPro.NAME like '%CC Alta%'
    ) T

     

     

     

    I need to see the actual step in which are the process who called "OSDE Solicitud CC Alta". If anyone can helpme I'll be very greatful



  • 2.  Re: Report on Processes in Execution
    Best Answer

    Posted 09-07-2017 04:42 PM

    I was able to solve the query. 

     

    SELECT * FROM niku.BPM_RUN_STEPS RunSteps

    INNER JOIN BPM_DEF_Steps DefSteps
    ON DefSteps.ID = RunSteps.STEP_ID

    INNER JOIN bpm_run_processes RunPro
    ON RunPro.ID = RunSteps.PROCESS_INSTANCE_ID

    INNER JOIN BPM_Def_Stages DefStages
    ON DefStages.Process_Version_Id = RunPro.Process_Version_Id

    INNER JOIN Bpm_Def_Process_Versions DefVer
    ON DefVer.id = RunPro.PROCESS_VERSION_ID

    LEFT OUTER JOIN CMN_CAPTIONS_NLS descPro
    ON descPro.language_Code = 'en'
    AND descPro.table_Name = 'BPM_DEF_PROCESSES'
    AND descPro.pk_id = DefVer.Process_id

    INNER JOIN BPM_Run_objects RunObj
    ON RunObj.pk_id = RunPro.id
    AND RunObj.Type_code = 'BPM_POT_PRIMARY'

    LEFT OUTER JOIN Inv_Investments I
    ON I.odf_object_code = RunObj.Object_Type_Code
    AND I.id = RunObj.Object_Id
    AND I.odf_object_code = RunObj.Object_Type_Code

    LEFT OUTER JOIN SRM_RESOURCES Init_User
    ON Init_User.user_id = RunPro.Initiated_By

    WHERE RunSteps.STATUS_CODE = 'BPM_SIS_READY_TO_EVAL_POSTCON'
    AND descPro.NAME like '%CC Alta%'