Hello,

You did everything correct, but the trick here is that trace data cannot be received from "Terminate event". Please view trace data of approval element and check parameter values before this element execution and after this element execution so to get needed data.

Best regards,

Oscar

Thanks Oscar.

Actually the process was typically not accurate for the action that I wanted to perform.

Dear Janine, 

Process log gradually gets cleared by the archivation process. We don't recommend to delete data from Process Log yourself. However, if you really need to delete that data we recommend to send a request to the support@bpmonline.com if your instance is in cloud as it requires very resource heavy sql operations that would not be possible to track with sql executor. 

If your instance is on-site you can take the instruction below as an example to adapt it based on your needs: 

1) Register SQL procedures tsp_DeleteSysProcessLog.sql and tsp_DeleteSysProcessLogByStartDate.sql (procedures themselves below)

2) Execute the procedure.

Example: EXEC [dbo].[tsp_DeleteSysProcessLogByStartDate] 'Error', '2017-09-22', -1,

Where 'Error' is the status of processes,  '2017-09-22' by which start date to delete processes, '-1' how many processes to delete, -1 means all processes. 



Please note that the operation is very resource heavy and time consuming and we don't recommend doing that during business hours. 

Best regards, 

Dennis

IF NOT OBJECT_ID('[dbo].[tsp_DeleteSysProcessLogByStartDate]') IS NULL

BEGIN

    DROP PROCEDURE [dbo].[tsp_DeleteSysProcessLogByStartDate]

END

GO

CREATE PROCEDURE [dbo].[tsp_DeleteSysProcessLogByStartDate]

    @Status nvarchar(max),

    @StartDate date,

    @RowsCountToDelete int

AS

BEGIN

    SET NOCOUNT ON

    IF NOT OBJECT_ID('#SysProcessLogId') IS NULL

    BEGIN

        DROP TABLE #SysProcessLogId

    END

    

    CREATE TABLE #SysProcessLogId (Id uniqueidentifier)

    IF @RowsCountToDelete > -1

        INSERT INTO #SysProcessLogId ([Id])

        SELECT Top (@RowsCountToDelete)

            SysProcessLog.Id

        FROM SysProcessLog

        JOIN SysProcessStatus ON SysProcessStatus.Id = SysProcessLog.StatusId

        WHERE

            SysProcessLog.ParentId IS NULL AND

            SysProcessLog.StartDate < @StartDate AND

            SysProcessStatus.[Name] = @Status

    ELSE

        INSERT INTO #SysProcessLogId ([Id])

        SELECT

            SysProcessLog.Id

        FROM SysProcessLog

        JOIN SysProcessStatus ON SysProcessStatus.Id = SysProcessLog.StatusId

        WHERE

            SysProcessLog.ParentId IS NULL AND

            SysProcessLog.StartDate < @StartDate AND

            SysProcessStatus.[Name] = @Status

    EXEC [dbo].[tsp_DeleteSysProcessLog]

END

GO

CREATE OR REPLACE PROCEDURE "tsp_DeleteSysProcessLogById" (id VARCHAR2)

IS

    TYPE IdSet IS TABLE OF VARCHAR2(38);

    input_ids IdSet;

    i NUMBER;

BEGIN

    SELECT

        "Id"

        BULK COLLECT INTO input_ids

    FROM "SysProcessLog"

    WHERE "ParentId" = id;

    IF input_ids.COUNT() > 0 THEN

    FOR i IN input_ids.FIRST .. input_ids.LAST

      LOOP

        BEGIN

          "tsp_DeleteSysProcessLogById"(input_ids(i));

        END;

    END LOOP;

    END IF;

    DELETE FROM "SysProcessElementLog"

    WHERE "SysProcessId" = id;

    DELETE FROM "SysProcessLog"

    WHERE "Id" = id;

END;

/

CREATE OR REPLACE PROCEDURE "tsp_DeleteSysProcessLog" (inputRowsCountToDelete IN INT, status IN VARCHAR2)

    IS

    TYPE IdSet IS TABLE OF VARCHAR2(38);

    input_ids IdSet;

    i NUMBER;

    step INT := 1000;

    startDate TIMESTAMP;

    rowsCountToDelete INT := inputRowsCountToDelete;

    allRowsCount INT;

    rowsLeft INT;

BEGIN

    DBMS_OUTPUT.PUT_LINE('Database: ' || USER);

    startDate := CURRENT_TIMESTAMP;

    IF (inputRowsCountToDelete <= 0) THEN

            SELECT COUNT("Id") INTO rowsCountToDelete FROM "SysProcessLog";

    END IF;

    SELECT

        "SysProcessLog"."Id"

        BULK COLLECT INTO input_ids

    FROM "SysProcessLog"

    WHERE

    "SysProcessLog"."ParentId" IS NULL

    AND "SysProcessLog"."StatusId" IN

        (SELECT

            "SysProcessStatus"."Id"

        FROM "SysProcessStatus"

        WHERE INSTR(status, "SysProcessStatus"."Value") > 0)

    AND NOT EXISTS(SELECT "SysProcessData"."Id" FROM "SysProcessData" WHERE "SysProcessData"."Id" = "SysProcessLog"."Id")

    AND ROWNUM <= rowsCountToDelete;

    allRowsCount := input_ids.COUNT();

    DBMS_OUTPUT.PUT_LINE('Number of records to process: ' || rowsCountToDelete);

    IF allRowsCount > 0 THEN

        rowsLeft := allRowsCount;

        FOR i IN input_ids.FIRST .. input_ids.LAST

            LOOP

                BEGIN

                    IF MOD(i, step) = 0 THEN

                        DBMS_OUTPUT.PUT_LINE('Step ready: ' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MM-YYYY HH24:MI:SS.FF2'));

                        DBMS_OUTPUT.PUT_LINE('Rows left: ' || rowsLeft);

                    END IF;

                    "tsp_DeleteSysProcessLogById"(input_ids(i));

                    rowsLeft := rowsLeft - 1;

                END;

            END LOOP;

        END IF;

    DBMS_OUTPUT.PUT_LINE('Number of records processed: ' || allRowsCount);

    DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');

    DBMS_OUTPUT.PUT_LINE('Execution time: ' || TO_CHAR((CURRENT_TIMESTAMP - startDate), 'DD-MM-YYYY HH24:MI:SS.FF2'));

END;

/

--CALL "tsp_DeleteSysProcessLog"(10000, '2,4');

Thanks, I have reported it to Support since there are records even from April that haven't been archived.

I found Beesender tried to delete process log by a process to delete items from Process log (actual). Is that correct or is there any risk of doing that way? 

Van Ly,

Process log is connected to several other tables so there is a risk of not deleting all necessary records or possibly corrupting some necessary data.

Best regards,

Dennis