ALTER TABLE TBWORKFLOW_CAMPO
    ADD WORKFLOW_SUBTASK_ID INTEGER DEFAULT 0;

COMMENT ON COLUMN TBWORKFLOW_CAMPO.WORKFLOW_SUBTASK_ID IS
'Modelo de Workflow que será utilizado para conclusão da tarefa';

/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_campo_b1u for tbworkflow_campo
active before update position 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);

DECLARE VARIABLE OLD_WORKFLOW_SUBTASK VARCHAR(500);
DECLARE VARIABLE NEW_WORKFLOW_SUBTASK VARCHAR(500);
BEGIN

  HISTORICO = '';

  IF (OLD.ROTULO <> NEW.ROTULO) THEN
  BEGIN
     HISTORICO = HISTORICO||' ROT:'||OLD.ROTULO||'->'||NEW.ROTULO;
  END

  IF (OLD.TIPO <> NEW.TIPO) THEN
  BEGIN
     HISTORICO = HISTORICO||' TIPO:'||OLD.TIPO||'->'||NEW.TIPO;
  END 

  IF (OLD.REQUERIDO <> NEW.REQUERIDO) THEN
  BEGIN
     HISTORICO = HISTORICO||' REQ:'||OLD.REQUERIDO||'->'||NEW.REQUERIDO;
  END 

  IF (OLD.EXIBIR_PAINEL <> NEW.EXIBIR_PAINEL) THEN
  BEGIN
     HISTORICO = HISTORICO||' EXIB:'||OLD.EXIBIR_PAINEL||'->'||NEW.EXIBIR_PAINEL;
  END 

  IF (OLD.FAIXA_VALOR_MIN <> NEW.FAIXA_VALOR_MIN) THEN
  BEGIN
     HISTORICO = HISTORICO||' VL.MIN:'||OLD.FAIXA_VALOR_MIN||'->'||NEW.FAIXA_VALOR_MIN;
  END                          

  IF (OLD.FAIXA_VALOR_MAX <> NEW.FAIXA_VALOR_MAX) THEN
  BEGIN
     HISTORICO = HISTORICO||' VL.MAX:'||OLD.FAIXA_VALOR_MAX||'->'||NEW.FAIXA_VALOR_MAX;
  END

  IF (OLD.WORKFLOW_SUBTASK_ID <> NEW.WORKFLOW_SUBTASK_ID) THEN
  BEGIN
     OLD_WORKFLOW_SUBTASK = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = OLD.WORKFLOW_SUBTASK_ID), '');
     NEW_WORKFLOW_SUBTASK = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = NEW.WORKFLOW_SUBTASK_ID), '');

     HISTORICO = HISTORICO||' WORK.SUBTASK:'||OLD_WORKFLOW_SUBTASK||'->'||NEW_WORKFLOW_SUBTASK;
  END

  IF (HISTORICO <> '') THEN
  BEGIN
     INSERT INTO TBHISTORICO (
     TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
     'TBWORKFLOW_TAREFA', OLD.WORKFLOW_TAREFA_ID,
    (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
    (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
     FN_ELIPSE(:HISTORICO, 500));
  END
END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

ALTER TABLE TBWORKFLOW_ITEM_CAMPO
    ADD WORKFLOW_ITEM_SUBTASK_ID INTEGER;

COMMENT ON COLUMN TBWORKFLOW_ITEM_CAMPO.WORKFLOW_ITEM_SUBTASK_ID IS
'Workflow que será utilizado para conclusão da tarefa';

/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_b01d for tbworkflow_item
active before delete position 0
AS
DECLARE VARIABLE WORKFLOW_ITEM_SUBTASK INTEGER;
BEGIN

   /*IF(OLD.PROGRESSO > 0)THEN
   BEGIN
      EXCEPTION "..." 'O WORKFLOW JÁ ESTÁ EM ANDAMENTO, OPERAÇÃO CANCELADA!';
   END  */
    
   IF (OLD.STATUS_CONCLUSAO = '3') THEN
   BEGIN
      EXCEPTION "..." 'O WORKFLOW JÁ FOI CONCLUÍDO, OPERAÇÃO CANCELADA!';
   END


   WORKFLOW_ITEM_SUBTASK = COALESCE((SELECT FIRST 1 A.WORKFLOW_ITEM_ID FROM TBWORKFLOW_ITEM_CAMPO A WHERE A.WORKFLOW_ITEM_SUBTASK_ID = OLD.ID), 0);

   IF (WORKFLOW_ITEM_SUBTASK > 0) THEN
   BEGIN
      EXCEPTION "..." 'O WORKFLOW ESTA VICULADO A OUTRO WORKFLOW. ID:'||WORKFLOW_ITEM_SUBTASK||'';
   END
END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

ALTER TABLE TBWORKFLOW_ITEM
    ADD TIPO CHAR(1) CHARACTER SET UTF8 DEFAULT 'M' COLLATE UTF8;

COMMENT ON COLUMN TBWORKFLOW_ITEM.TIPO IS
'"M" - Workflow Mestre;
"D" - Workflow Detalhe (Vinculado a algum mestre);';


UPDATE TBWORKFLOW_ITEM T
	SET T.TIPO = 'M'
	WHERE COALESCE((SELECT FIRST 1 W.ID FROM TBWORKFLOW_ITEM_CAMPO W WHERE W.WORKFLOW_ITEM_SUBTASK_ID = T.ID), 0) = 0;

UPDATE TBWORKFLOW_ITEM T
	SET T.TIPO = 'D'
	WHERE COALESCE((SELECT FIRST 1 W.ID FROM TBWORKFLOW_ITEM_CAMPO W WHERE W.WORKFLOW_ITEM_SUBTASK_ID = T.ID), 0) > 0;


CREATE INDEX ITBWORKFLOW_ITEM_08
ON TBWORKFLOW_ITEM (TIPO);

/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_b4u for tbworkflow_item
active before update position 4
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
BEGIN

    HISTORICO = '';

    IF (OLD.TITULO <> NEW.TITULO) THEN
    BEGIN
        HISTORICO = HISTORICO||' TITULO:'||OLD.TITULO||'->'||NEW.TITULO;
    END

    IF (OLD.DESCRICAO <> NEW.DESCRICAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' DESC.:'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
    END
    
    IF (OLD.PROGRESSO <> NEW.PROGRESSO) THEN
    BEGIN
       HISTORICO = HISTORICO||' PROG.:'||OLD.PROGRESSO||'->'||NEW.PROGRESSO;
    END
    
    IF (OLD.STATUS_CONCLUSAO <> NEW.STATUS_CONCLUSAO) THEN
    BEGIN
       HISTORICO = HISTORICO||' STATUS:'||OLD.STATUS_CONCLUSAO||'->'||NEW.STATUS_CONCLUSAO;
    END

    IF (OLD.USUARIO_ID <> NEW.USUARIO_ID) THEN
    BEGIN
       HISTORICO = HISTORICO||' USU:'||OLD.USUARIO_ID||'->'||NEW.USUARIO_ID;
    END
    
    IF (OLD.DATAHORA_INI_PREVISTA <> NEW.DATAHORA_INI_PREVISTA) THEN
    BEGIN
       HISTORICO = HISTORICO||' INI PREV:'||OLD.DATAHORA_INI_PREVISTA||'->'||NEW.DATAHORA_INI_PREVISTA;
    END
    
    IF (OLD.DATAHORA_FIM_PREVISTA <> NEW.DATAHORA_FIM_PREVISTA) THEN
    BEGIN
       HISTORICO = HISTORICO||' FIM PREV:'||OLD.DATAHORA_FIM_PREVISTA||'->'||NEW.DATAHORA_FIM_PREVISTA;
    END
    
    IF (OLD.DATAHORA_INI <> NEW.DATAHORA_INI) THEN
    BEGIN
       HISTORICO = HISTORICO||' INI:'||OLD.DATAHORA_INI||'->'||NEW.DATAHORA_INI;
    END
    
    IF (OLD.DATAHORA_FIM <> NEW.DATAHORA_FIM) THEN
    BEGIN
       HISTORICO = HISTORICO||' FIM:'||OLD.DATAHORA_FIM||'->'||NEW.DATAHORA_FIM;
    END

    IF (OLD.TIPO <> NEW.TIPO) THEN
    BEGIN
       HISTORICO = HISTORICO||' TIPO:'||OLD.TIPO||'->'||NEW.TIPO;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
       HISTORICO = 'REG. ALTERADO:'||HISTORICO;
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
       'TBWORKFLOW_ITEM', OLD.ID,
      (SELECT FIRST 1 MON$USER FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      (SELECT FIRST 1 MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
       FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^

/******************************************************************************/

CREATE INDEX ITBWORKFLOW_ITEM_CAMPO_09
ON TBWORKFLOW_ITEM_CAMPO (WORKFLOW_ITEM_SUBTASK_ID);

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_b3u for tbworkflow_item
active before update position 3
AS
DECLARE VARIABLE TASK_PAUSE INTEGER;
DECLARE VARIABLE TASK_ATEND INTEGER;
BEGIN

    TASK_ATEND = COALESCE((SELECT FIRST 1 WT.ID FROM TBWORKFLOW_ITEM_TAREFA WT WHERE WT.STATUS_CONCLUSAO = '1' AND WT.WORKFLOW_ITEM_ID = OLD.ID), 0);
    TASK_PAUSE = COALESCE((SELECT FIRST 1 WT.ID FROM TBWORKFLOW_ITEM_TAREFA WT WHERE WT.STATUS_CONCLUSAO = '2' AND WT.WORKFLOW_ITEM_ID = OLD.ID), 0);

    
    -- Se nao estiver sendo encerrada
    IF (NEW.STATUS_CONCLUSAO <> '3') THEN
    BEGIN
    
        IF (NEW.PROGRESSO = 0) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '0';
        END

        IF (TASK_ATEND > 0) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '1';
        END

        ELSE IF (TASK_PAUSE > 0) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '2';
        END

        ELSE IF (NEW.PROGRESSO = 100) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '3';
        END
    
        ELSE IF (NEW.PROGRESSO > 0) THEN
        BEGIN
            NEW.STATUS_CONCLUSAO = '1';
        END

    END

END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_tarefa_b3u for tbworkflow_item_tarefa
active before update position 3
AS
DECLARE VARIABLE QTD_TAREFA INTEGER;
DECLARE VARIABLE QTD_CONCLUIDA INTEGER;
DECLARE VARIABLE WORKFLOW_SUBTASK_LIST VARCHAR(5000);
DECLARE VARIABLE WORKFLOW_SUBTASK_ID INTEGER;
BEGIN

    -- VERIFICA AS ALTERACOES DE SITUACOES NAS TAREFAS PARA AUSTAR REGISTRO PRINCIPAL
    IF ((OLD.STATUS_CONCLUSAO <> NEW.STATUS_CONCLUSAO) AND (NEW.STATUS_CONCLUSAO <> '0') AND (NEW.STATUS_CONCLUSAO <> '3')) THEN
    BEGIN

        UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = NEW.STATUS_CONCLUSAO
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;

    END

    -- SE TIVER CONCLUINDO OU REINICIANDO A TAREFA.
    IF ((NEW.VERIFICADOR = 1) AND (NEW.STATUS_CONCLUSAO = '3' OR OLD.STATUS_CONCLUSAO = '3')) THEN
    BEGIN
        
        WORKFLOW_SUBTASK_LIST = '';

        QTD_TAREFA  = (SELECT COUNT(WT.ID)
                        FROM TBWORKFLOW_ITEM_TAREFA WT
                        WHERE WT.STATUSEXCLUSAO = '0'
                        AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID);

        -- SE ESTIVER REINICIANDO A TAREFA.
        IF (OLD.STATUS_CONCLUSAO = '3') THEN
        BEGIN
            -- SUBTRAI -1 PORQUE DEVE CONTAR OS CONCLUIDOS ANTERIORES MENOS O CONCLUIDO ATUAL, QUE ESTA SENDO REINICIADO.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)-1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND WT.STATUS_CONCLUSAO = '3');

            WORKFLOW_SUBTASK_LIST = COALESCE((SELECT LIST(WC.WORKFLOW_ITEM_SUBTASK_ID, ',')
                                    FROM TBWORKFLOW_ITEM_CAMPO WC
                                    WHERE WC.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                    AND WC.WORKFLOW_ITEM_TAREFA_ID = OLD.ID), '');

        END
        ELSE
        BEGIN
            -- ADICIONA +1 PORQUE DEVE CONTAR OS CONCLUIDOS ANTERIORES E O CONCLUIDO ATUAL.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)+1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND WT.STATUS_CONCLUSAO = '3');
        END

        UPDATE TBWORKFLOW_ITEM W
        SET W.PROGRESSO = ((:QTD_CONCLUIDA * 100) / :QTD_TAREFA)
        WHERE W.ID = OLD.WORKFLOW_ITEM_ID;

        IF(NEW.STATUS_CONCLUSAO = '1' AND OLD.STATUS_CONCLUSAO = '3')THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = 1
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END

        IF(:QTD_CONCLUIDA >= 100)THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.PROGRESSO = 100,
                W.STATUS_CONCLUSAO = '3'
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END


        /*******************************************************************/

        IF (WORKFLOW_SUBTASK_LIST <> '') THEN
        BEGIN

            FOR
                SELECT
                    SP.OSPLIT AS WORKFLOW_SUBTASK_ID
                FROM SPLIT(:WORKFLOW_SUBTASK_LIST, ',') SP
            INTO
                :WORKFLOW_SUBTASK_ID    
            DO
            BEGIN

                IF (COALESCE(WORKFLOW_SUBTASK_ID, 0) > 0) THEN
                BEGIN

                    UPDATE TBWORKFLOW_ITEM_TAREFA WT
                        SET WT.STATUS_CONCLUSAO = '0',
                            WT.VERIFICADOR = 1
                        WHERE WT.WORKFLOW_ITEM_ID = :WORKFLOW_SUBTASK_ID;

                    UPDATE TBWORKFLOW_ITEM WI
                        SET WI.PROGRESSO = 0,
                            WI.STATUS_CONCLUSAO = '0'
                        WHERE WI.ID = :WORKFLOW_SUBTASK_ID;

                END

            END

        END

        /*******************************************************************/


        NEW.VERIFICADOR = 0;
    END

END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBCASO_WORKFLOWS;

CREATE TABLE TBCASO_WORKFLOWS (
    ID                  INTEGER NOT NULL,
    PAINEL_ID           INTEGER,
    MODELO_WORKFLOW_ID  INTEGER DEFAULT 0
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBCASO_WORKFLOWS ADD CONSTRAINT PK_TBCASO_WORKFLOWS PRIMARY KEY (ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBCASO_WORKFLOWS_01 ON TBCASO_WORKFLOWS (PAINEL_ID);
CREATE INDEX ITBCASO_WORKFLOWS_02 ON TBCASO_WORKFLOWS (MODELO_WORKFLOW_ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBCASO_WORKFLOWS_B01D */
CREATE OR ALTER TRIGGER TTBCASO_WORKFLOWS_B01D FOR TBCASO_WORKFLOWS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_MODELO_WORKFLOW VARCHAR(500);
BEGIN

    :HISTORICO = '';

    :OLD_MODELO_WORKFLOW = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW A WHERE A.ID = OLD.MODELO_WORKFLOW_ID), '');

    IF (OLD.ID > 0) THEN
    BEGIN
        :HISTORICO = :HISTORICO||'MODELO WORKFLOW: '||OLD_MODELO_WORKFLOW||' EXCLUIDO';
    END

    IF (:HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCASO_PAINEL', OLD.PAINEL_ID, FN_ELIPSE(:HISTORICO, 500));
    END
END
^


/* Trigger: TTBCASO_WORKFLOWS_B01I */
CREATE OR ALTER TRIGGER TTBCASO_WORKFLOWS_B01I FOR TBCASO_WORKFLOWS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (COALESCE(NEW.ID, 0) = 0) THEN
        NEW.ID = GEN_ID(GTBCASO_WORKFLOWS, 1);
END
^


/* Trigger: TTBCASO_WORKFLOWS_B01U */
CREATE OR ALTER TRIGGER TTBCASO_WORKFLOWS_B01U FOR TBCASO_WORKFLOWS
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_MODELO_WORKFLOW VARCHAR(500);
DECLARE VARIABLE NEW_MODELO_WORKFLOW VARCHAR(500);
BEGIN
    
    :HISTORICO = '';

    :OLD_MODELO_WORKFLOW = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW A WHERE A.ID = OLD.MODELO_WORKFLOW_ID), '');
    :NEW_MODELO_WORKFLOW = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW A WHERE A.ID = NEW.MODELO_WORKFLOW_ID), '');

    IF (OLD.PAINEL_ID <> NEW.PAINEL_ID) THEN
    BEGIN
        :HISTORICO = :HISTORICO||' PAINEL:'||OLD.PAINEL_ID||'->'||NEW.PAINEL_ID;
    END

    IF (OLD.MODELO_WORKFLOW_ID <> NEW.MODELO_WORKFLOW_ID) THEN
    BEGIN
        :HISTORICO = :HISTORICO||' MODELO WORKFLOW:'||:OLD_MODELO_WORKFLOW||'->'||:NEW_MODELO_WORKFLOW;
    END

    IF (:HISTORICO <> '') THEN
    BEGIN
        :HISTORICO = 'MODELO WORKFLOW: '||NEW_MODELO_WORKFLOW||' ALTERADO:'||:HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBCASO_PAINEL', OLD.PAINEL_ID, FN_ELIPSE(:HISTORICO, 500));
    END
END
^


/* Trigger: TTBCASO_WORKFLOWS_B02I */
CREATE OR ALTER TRIGGER TTBCASO_WORKFLOWS_B02I FOR TBCASO_WORKFLOWS
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE NEW_MODELO_WORKFLOW VARCHAR(500);
BEGIN

    :HISTORICO = '';

    :NEW_MODELO_WORKFLOW = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW A WHERE A.ID = NEW.MODELO_WORKFLOW_ID), '');

    IF (NEW.ID > 0) THEN
    BEGIN
        :HISTORICO = :HISTORICO||'MODELO WORKFLOW: '||NEW_MODELO_WORKFLOW||' INCLUIDO';
    END

    IF (:HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO('TBCASO_PAINEL', NEW.PAINEL_ID, FN_ELIPSE(:HISTORICO, 500));
    END
END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_painel_b02d for tbcaso_painel
active before delete position 1
AS
BEGIN
   IF(OLD.ID > 0) THEN
   BEGIN
      DELETE FROM TBCASO_VISUALIZACAO_CONF V WHERE V.PAINEL_ID = OLD.ID;    -- VISUALIZAÇÃO ESTÁ VINCULADA COM PAINEL DURANTE INCLUSÃO.
      DELETE FROM TBCASO_SQL S WHERE S.ID = OLD.SQL_EMAIL_CONTATO;          -- SQL DO CAMPO DOS EMAILS DOS CONATATOS

      DELETE FROM TBCASO_PAINEL_CONF A WHERE A.PAINEL_ID = OLD.ID;          -- CAMPOS (ALT+2)
      DELETE FROM TBCASO_USUARIOS B WHERE B.PAINEL_ID = OLD.ID;             -- USUÁRIOS (ALT+3)
      DELETE FROM TBCASO_PARAMETROS_USUARIO C WHERE C.PAINEL_ID = OLD.ID;   -- PARÂMETROS (ALT+4)
      DELETE FROM TBCASO_STATUS D WHERE D.PAINEL_ID = OLD.ID;               -- STATUS (ALT+5)
      DELETE FROM TBCASO_MOTIVO E WHERE E.PAINEL_ID = OLD.ID;               -- MOTIVO (ALT+6)
      DELETE FROM TBCASO_VISUALIZACAO_CAMPO F WHERE F.PAINEL_ID = OLD.ID;   -- VISUALIZAÇÃO (ALT+8)
      DELETE FROM TBCASO_VALIDACAO G WHERE G.PAINEL_ID = OLD.ID;            -- VALIDAÇÃO (ALT+9)
      DELETE FROM TBCASO_CONTATO_CONF H WHERE H.PAINEL_ID = OLD.ID;         -- CONTATO (ALT+0)
      DELETE FROM TBCASO_CAMPOS_AGRUPAMENTO I WHERE I.PAINEL_ID = OLD.ID;   -- AGRUPAMENTOS (ALT+7)
      DELETE FROM TBCASO_WORKFLOWS J WHERE J.PAINEL_ID = OLD.ID;            -- MODELOS DE WORKFLOW (ALT+R)
      
      DELETE FROM TBMENU M WHERE M.CONTROLE = 99000 + OLD.ID;               -- EXCLUSÃO DO PAINEL DA LISTA DE MENUS
   END
END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBCASO_REGISTRO_WORKFLOWS;

CREATE TABLE TBCASO_REGISTRO_WORKFLOWS (
    ID                INTEGER NOT NULL,
    REGISTRO_ID       INTEGER,
    PAINEL_ID         INTEGER,
    WORKFLOW_ITEM_ID  INTEGER DEFAULT 0,
    ORDEM             INTEGER
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBCASO_REGISTRO_WORKFLOWS ADD CONSTRAINT PK_TBCASO_REGISTRO_WORKFLOWS PRIMARY KEY (ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBCASO_REGISTRO_WORKFLOWS_B01D */
CREATE OR ALTER TRIGGER TTBCASO_REGISTRO_WORKFLOWS_B01D FOR TBCASO_REGISTRO_WORKFLOWS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (OLD.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'WORKFLOW '||OLD.WORKFLOW_ITEM_ID||' EXCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO(OLD.REGISTRO_ID, OLD.PAINEL_ID, HISTORICO);
    END
END
^


/* Trigger: TTBCASO_REGISTRO_WORKFLOWS_B01I */
CREATE OR ALTER TRIGGER TTBCASO_REGISTRO_WORKFLOWS_B01I FOR TBCASO_REGISTRO_WORKFLOWS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (COALESCE(NEW.ID, 0) = 0) THEN
        NEW.ID = GEN_ID(GTBCASO_REGISTRO_WORKFLOWS, 1);
END
^


/* Trigger: TTBCASO_REGISTRO_WORKFLOWS_B01U */
CREATE OR ALTER TRIGGER TTBCASO_REGISTRO_WORKFLOWS_B01U FOR TBCASO_REGISTRO_WORKFLOWS
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);
DECLARE VARIABLE OLD_WORKFLOW_ITEM VARCHAR(500);
DECLARE VARIABLE NEW_WORKFLOW_ITEM VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_WORKFLOW_ITEM = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = OLD.WORKFLOW_ITEM_ID), '');
    NEW_WORKFLOW_ITEM = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.TITULO FROM TBWORKFLOW_ITEM A WHERE A.ID = NEW.WORKFLOW_ITEM_ID), '');

    IF (OLD.REGISTRO_ID <> NEW.REGISTRO_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' REGISTRO:'||OLD.REGISTRO_ID||'->'||NEW.REGISTRO_ID;
    END

    IF (OLD.PAINEL_ID <> NEW.PAINEL_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' PAINEL:'||OLD.PAINEL_ID||'->'||NEW.PAINEL_ID;
    END

    IF (OLD.WORKFLOW_ITEM_ID <> NEW.WORKFLOW_ITEM_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' WORKFLOW:'||OLD_WORKFLOW_ITEM||'->'||NEW_WORKFLOW_ITEM;
    END

    IF (OLD.ORDEM <> NEW.ORDEM) THEN
    BEGIN
        HISTORICO = HISTORICO||' ORDEM:'||OLD.ORDEM||'->'||NEW.ORDEM;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'WORKFLOW '||NEW_WORKFLOW_ITEM||' ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO(OLD.REGISTRO_ID, OLD.PAINEL_ID, FN_ELIPSE(HISTORICO, 500));
    END
END
^


/* Trigger: TTBCASO_REGISTRO_WORKFLOWS_B02D */
CREATE OR ALTER TRIGGER TTBCASO_REGISTRO_WORKFLOWS_B02D FOR TBCASO_REGISTRO_WORKFLOWS
ACTIVE BEFORE DELETE POSITION 1
AS
BEGIN
    DELETE FROM TBWORKFLOW_ITEM A WHERE A.ID = OLD.WORKFLOW_ITEM_ID;        /* Exclui Workflow gerado */
END
^


/* Trigger: TTBCASO_REGISTRO_WORKFLOWS_B02I */
CREATE OR ALTER TRIGGER TTBCASO_REGISTRO_WORKFLOWS_B02I FOR TBCASO_REGISTRO_WORKFLOWS
ACTIVE BEFORE INSERT POSITION 1
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN

    HISTORICO = '';

    IF (NEW.ID > 0) THEN
    BEGIN
        HISTORICO = HISTORICO||'WORKFLOW '||NEW.WORKFLOW_ITEM_ID||' INCLUIDO';
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        EXECUTE PROCEDURE SPI_HISTORICO(NEW.REGISTRO_ID, NEW.PAINEL_ID, HISTORICO);
    END
END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_registro_b01d for tbcaso_registro
active before delete position 0
AS
begin
  delete from tbcaso_registro_detalhe d where d.registro_id = old.id;
  delete from tbcaso_registro_itens i where i.registro_id =  old.id;
  delete from tbcaso_registro_workflows w where w.registro_id =  old.id;
  execute procedure SPI_CASO_HISTORICO(old.id,old.painel_id,'Caso Excluido');
end
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

DROP TRIGGER TTBCASO_PAINEL_B03I;
DROP TRIGGER TTBCASO_PAINEL_B3IU;

SET TERM ^ ;

CREATE OR ALTER trigger ttbcaso_painel_b02d for tbcaso_painel
active before delete position 1
AS
BEGIN
   IF(OLD.ID > 0) THEN
   BEGIN
      DELETE FROM TBCASO_VISUALIZACAO_CONF V WHERE V.PAINEL_ID = OLD.ID;    -- VISUALIZAÇÃO ESTÁ VINCULADA COM PAINEL DURANTE INCLUSÃO.
      DELETE FROM TBCASO_SQL S WHERE S.ID = OLD.SQL_EMAIL_CONTATO;          -- SQL DO CAMPO DOS EMAILS DOS CONATATOS

      DELETE FROM TBCASO_PAINEL_CONF A WHERE A.PAINEL_ID = OLD.ID;          -- CAMPOS (ALT+2)
      DELETE FROM TBCASO_USUARIOS B WHERE B.PAINEL_ID = OLD.ID;             -- USUÁRIOS (ALT+3)
      DELETE FROM TBCASO_PARAMETROS_USUARIO C WHERE C.PAINEL_ID = OLD.ID;   -- PARÂMETROS (ALT+4)
      DELETE FROM TBCASO_STATUS D WHERE D.PAINEL_ID = OLD.ID;               -- STATUS (ALT+5)
      DELETE FROM TBCASO_MOTIVO E WHERE E.PAINEL_ID = OLD.ID;               -- MOTIVO (ALT+6)
      DELETE FROM TBCASO_VISUALIZACAO_CAMPO F WHERE F.PAINEL_ID = OLD.ID;   -- VISUALIZAÇÃO (ALT+8)
      DELETE FROM TBCASO_VALIDACAO G WHERE G.PAINEL_ID = OLD.ID;            -- VALIDAÇÃO (ALT+9)
      DELETE FROM TBCASO_CONTATO_CONF H WHERE H.PAINEL_ID = OLD.ID;         -- CONTATO (ALT+0)
      DELETE FROM TBCASO_CAMPOS_AGRUPAMENTO I WHERE I.PAINEL_ID = OLD.ID;   -- AGRUPAMENTOS (ALT+7)
      DELETE FROM TBCASO_WORKFLOWS J WHERE J.PAINEL_ID = OLD.ID;               -- MODELOS DE WORKFLOW (ALT+R)
      
      -- DELETE FROM TBMENU M WHERE M.CONTROLE = 99000 + OLD.ID;               -- EXCLUSÃO DO PAINEL DA LISTA DE MENUS
   END
END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure CASO_DETALHE (
    PAINEL_ID integer,
    CASO_ID integer)
returns (
    TIPO integer,
    DESCRICAO varchar(100),
    VALOR varchar(7000),
    CAMPO_ID integer)
as
declare variable EMAIL varchar(500);
declare variable MONITOR varchar(500);
declare variable MOTIVO varchar(500);
declare variable TIPOV varchar(500);
declare variable ORIGEM varchar(500);
declare variable RESPONSAVEL varchar(500);
declare variable COR varchar(500);
declare variable STATUS varchar(500);
begin

  for
       SELECT first 1
            p.email,
            p.email_monitor,
            m.descricao   as MOTIVO,
            T.descricao   as TIPO,
            o.descricao   as ORIGEM,
            u.usuario     as RESPONSAVEL,
            i.descricao   as STATUS,
            i.cor         as COR
        
        FROM tbcaso_registro r, tbcaso_painel p, tbcaso_tipo t,tbcaso_motivo m,
            tbcaso_origem o, tbusuario u, tbcaso_status i
        where p.id   = :painel_id
        and r.id     = :caso_id
        and t.id     = r.tipo_id
        and m.id     = r.motivo_id
        and o.id     = r.origem_id
        and i.id     = r.status_id
        and u.codigo = r.responsavel_id
    into
        :email,
        :monitor,
        :MOTIVO,
        :TIPOV,
        :ORIGEM,
        :RESPONSAVEL,
        :STATUS,
        :COR
    do
    begin
        :campo_id = 0;
        tipo = 7;
        descricao = 'Motivo';         valor = MOTIVO;      suspend;
        descricao = 'Tipo';           valor = TIPOV;       suspend;
        descricao = 'Tipo de Origem'; valor = ORIGEM;      suspend;
        descricao = 'Responsável';    valor = RESPONSAVEL; suspend;
        descricao = 'Status';         valor = STATUS;      suspend;
    end

    for
       select
        
            a.tipo,
            a.campo_id,
            a.descricao,
            iif(a.tipo = 9 or a.tipo = 9 , i.descricao,
            iif(a.tipo = 4,
            (
                select
                    list(s.descricao||':'||r.selecionado,', ')
                from tbcaso_registro_itens r, tbcaso_painel_conf_itens s
                where r.campo_id = a.id
                and r.valor = s.valor
                and s.campo_id = r.campo_id
                and r.registro_id = a.registro_id
            ),
            iif(a.tipo = 7,
        
                FN_JSON_ARRAY(a.json,a.campo_visualizar)
        
            ,a.valor))) as valor
        
        from
            (
                select
                
                    f.id,
                    c.campo_id,
                    c.registro_id,
                    f.tipo,
                    f.descricao,
                    c.json,
                    f.campo_visualizar,
                    fn_is_empty(cast(c.valor as varchar(7000)), '0') as valor,
                    iif(f.tipo = 9 or f.tipo = 5, fn_is_empty(c.valor, '0'), 0) as chave
                
                from tbcaso_registro_detalhe c, tbcaso_painel_conf f
                where c.registro_id = :caso_id
                and   c.painel_id   = :painel_id
                and   c.campo_id    = f.id
                order by c.campo_id
            )a
            left join tbcaso_painel_conf_itens i on (i.campo_id = a.ID and i.valor = a.chave)
    into
        :tipo,
        :campo_id,
        :descricao,
        :valor
    do
    begin
        if(:valor = '')then
        begin
            :valor = null;
        end
        suspend;
    end


end
^

SET TERM ; ^

COMMENT ON PARAMETER CASO_DETALHE.PAINEL_ID IS
'id do painel de casos';

COMMENT ON PARAMETER CASO_DETALHE.CASO_ID IS
'id do caso';

/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_a01d for tbworkflow_item
active after delete position 0
AS
BEGIN

    DELETE FROM TBWORKFLOW_ITEM_COMENTARIO C    WHERE C.WORKFLOW_ITEM_ID = OLD.ID AND C.WORKFLOW_ID = OLD.WORKFLOW_ID;
    DELETE FROM TBWORKFLOW_ITEM_DEPENDENCIA D   WHERE D.WORKFLOW_ITEM_ID = OLD.ID AND D.WORKFLOW_ID = OLD.WORKFLOW_ID;
    DELETE FROM TBWORKFLOW_ITEM_DESTINATARIO E  WHERE E.WORKFLOW_ITEM_ID = OLD.ID AND E.WORKFLOW_ID = OLD.WORKFLOW_ID;
    DELETE FROM TBWORKFLOW_ITEM_NOTIFICADO H    WHERE H.WORKFLOW_ITEM_ID = OLD.ID AND H.WORKFLOW_ID = OLD.WORKFLOW_ID;
    DELETE FROM TBWORKFLOW_ITEM_TAREFA I        WHERE I.WORKFLOW_ITEM_ID = OLD.ID AND I.WORKFLOW_ID = OLD.WORKFLOW_ID;
    DELETE FROM TBWORKFLOW_ITEM_CAMPO B         WHERE B.WORKFLOW_ITEM_ID = OLD.ID AND B.WORKFLOW_ID = OLD.WORKFLOW_ID;

    DELETE FROM TBWORKFLOW_ITEM_HISTORICO F     WHERE F.WORKFLOW_ITEM_ID = OLD.ID;
    DELETE FROM TBWORKFLOW_ITEM_MOVIMENTACAO G  WHERE G.WORKFLOW_ITEM_ID = OLD.ID;

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_campo_a01d for tbworkflow_item_campo
active after delete position 0
AS
BEGIN

    DELETE FROM TBWORKFLOW_ITEM A WHERE A.ID = OLD.WORKFLOW_ITEM_SUBTASK_ID;

END
^

SET TERM ; ^

/******************************************************************************/
/******************************************************************************/

ALTER TABLE TBCASO_FEED
    ADD WORKFLOW_ITEM_ID INTEGER;

COMMENT ON COLUMN TBCASO_FEED.WORKFLOW_ITEM_ID IS
'ID do Worklfow a qual as mensagens estarão vinculadas';


CREATE INDEX ITBCASO_FEED_05
ON TBCASO_FEED (WORKFLOW_ITEM_ID);

/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPU_AJUSTA_STATUS_SUBWORKFLOW (
    WORKFLOW_ITEM_ID integer not null)
as
declare variable WORK_CAMPO_ID integer;
declare variable WORKFLOW_ID integer;
declare variable WORKFLOW_ITEM_PAI_ID integer;
declare variable WORK_TAREFA_ID integer;
declare variable WORK_TIPO varchar(10);
declare variable WORK_TAREFA_CAMPO_ID integer;
declare variable WORK_ITEM_SUBTASK_ID integer;
declare variable TAREFA_ID integer;
declare variable TAREFA_STATUS varchar(10);
declare variable TAREFA_VERIF integer;
declare variable WORK_ITEM_ID integer;
declare variable SUBTASK integer;
BEGIN

    FOR
        SELECT
            T.ID,
            T.WORKFLOW_ID,
            T.WORKFLOW_ITEM_ID,
            T.WORKFLOW_ITEM_TAREFA_ID,
            T.TIPO,
            T.WORKFLOW_TAREFA_CAMPO_ID,
            T.WORKFLOW_ITEM_SUBTASK_ID
        
        FROM TBWORKFLOW_ITEM_CAMPO T
        INNER JOIN TBWORKFLOW_ITEM A ON A.ID = T.WORKFLOW_ITEM_ID AND A.WORKFLOW_ID = T.WORKFLOW_ID
        INNER JOIN TBWORKFLOW_CAMPO B ON B.ID = T.WORKFLOW_TAREFA_CAMPO_ID AND B.WORKFLOW_ID = T.WORKFLOW_ID
        WHERE T.WORKFLOW_ITEM_SUBTASK_ID = :WORKFLOW_ITEM_ID
        AND COALESCE(B.REQUERIDO, '1') = '1'
    
    INTO
        :WORK_CAMPO_ID,
        :WORKFLOW_ID,
        :WORKFLOW_ITEM_PAI_ID,
        :WORK_TAREFA_ID,
        :WORK_TIPO,
        :WORK_TAREFA_CAMPO_ID,
        :WORK_ITEM_SUBTASK_ID
    
    DO
    BEGIN
    
        FOR
            SELECT
                C.ID,
                C.STATUS_CONCLUSAO,
                C.VERIFICADOR
    
            FROM TBWORKFLOW_ITEM_TAREFA C
            INNER JOIN TBWORKFLOW_ITEM_CAMPO D ON D.WORKFLOW_ITEM_TAREFA_ID = C.ID AND D.WORKFLOW_ID = C.WORKFLOW_ID
            WHERE C.WORKFLOW_ITEM_ID = :WORKFLOW_ITEM_PAI_ID
            AND C.ID = :WORK_TAREFA_ID
    
        INTO
            :TAREFA_ID,
            :TAREFA_STATUS,
            :TAREFA_VERIF
        
        DO
        BEGIN
    
            UPDATE TBWORKFLOW_ITEM_TAREFA C
                SET C.STATUS_CONCLUSAO = 1,
                    C.VERIFICADOR = 1
                WHERE C.ID = :TAREFA_ID;
    
            /*******************************************************/
    
            FOR
                SELECT
                    A.ID
    
                FROM TBWORKFLOW_ITEM A
                WHERE A.ID = :WORKFLOW_ITEM_PAI_ID
                AND A.STATUS_CONCLUSAO = 0
    
            INTO
                :WORK_ITEM_ID
    
            DO
            BEGIN
                
                UPDATE TBWORKFLOW_ITEM A
                    SET A.STATUS_CONCLUSAO = 1
                    WHERE A.ID = :WORK_ITEM_ID;
    
            END
    
    
            FOR
                SELECT
                    A.ID
    
                FROM TBWORKFLOW_ITEM A
                WHERE A.ID = :WORKFLOW_ITEM_PAI_ID
                AND A.STATUS_CONCLUSAO = 2
    
            INTO
                :WORK_ITEM_ID
    
            DO
            BEGIN
                
                UPDATE TBWORKFLOW_ITEM A
                    SET A.STATUS_CONCLUSAO = 1
                    WHERE A.ID = :WORK_ITEM_ID;
    
            END
    
            /*******************************************************/
    
            :SUBTASK = COALESCE((SELECT FIRST 1
                                    T.WORKFLOW_ITEM_SUBTASK_ID
                                FROM TBWORKFLOW_ITEM_CAMPO T
                                WHERE T.WORKFLOW_ITEM_SUBTASK_ID = :WORKFLOW_ITEM_PAI_ID), 0);
    
            IF (COALESCE(:SUBTASK, 0) > 0) THEN
            BEGIN
                EXECUTE PROCEDURE SPU_AJUSTA_STATUS_SUBWORKFLOW(:SUBTASK);
            END
        END
    END

END^

SET TERM ; ^

/******************************************************************/
/******************************************************************/
/******************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPU_AJUSTA_USUARIO_SUBWORKFLOW (
    CASO_ID integer not null,
    PAINEL_ID integer not null,
    WORKFLOW_ITEM_ID integer not null)
as
declare variable REG_WORKFLOW_ITEM_ID integer;
declare variable REG_WORKFLOW_ID integer;
declare variable REG_WORKFLOW_ITEM_PROGRESSO integer;
declare variable REG_WORKFLOW_ITEM_STATUS varchar(10);
declare variable REG_WORKFLOW_ITEM_TIPO varchar(10);
declare variable REG_TAREFA_ID integer;
declare variable REG_TAREFA_TITULO varchar(100);
declare variable REG_TAREFA_STATUS varchar(10);
declare variable TAREFA_ID integer;
declare variable PERM_ID integer;
declare variable PERM_USUARIO integer;
declare variable PERM_NOTIF varchar(10);
declare variable PERM_REINICIAR varchar(10);
declare variable PERM2_ID integer;
declare variable PERM2_USUARIO integer;
declare variable PERM2_NOTIF integer;
declare variable INFO_CAMPO_ID integer;
declare variable INFO_CAMPO_ROTULO varchar(200);
declare variable INFO_CAMPO_TIPO varchar(10);
declare variable INFO_CAMPO_SUBWORKFLOW integer;
declare variable VERIF_EXIST integer;
BEGIN

    FOR
        SELECT
            B.ID,
            B.WORKFLOW_ID,
            B.PROGRESSO,
            B.STATUS_CONCLUSAO AS STATUS,
            B.TIPO
    
        FROM TBWORKFLOW_ITEM B
        WHERE B.ID = :WORKFLOW_ITEM_ID
    
    INTO
        :REG_WORKFLOW_ITEM_ID,
        :REG_WORKFLOW_ID,
        :REG_WORKFLOW_ITEM_PROGRESSO,
        :REG_WORKFLOW_ITEM_STATUS,
        :REG_WORKFLOW_ITEM_TIPO
    
    DO
    BEGIN
    
        FOR
            SELECT
                C.ID,
                C.TITULO,
                C.STATUS_CONCLUSAO AS STATUS,
                C.WORKFLOW_TAREFA_MODELO_ID
    
            FROM TBWORKFLOW_ITEM_TAREFA C
            WHERE C.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
            AND C.WORKFLOW_ID = :REG_WORKFLOW_ID
    
        INTO
            :REG_TAREFA_ID,
            :REG_TAREFA_TITULO,
            :REG_TAREFA_STATUS,
            :TAREFA_ID
    
        DO
        BEGIN
    
            FOR
                SELECT
                    D.ID,
                    D.USUARIO_ID,
                    COALESCE(D.NOTIFICACAO, '0') AS NOTIFICACAO,
                    D.REINICIAR
    
                FROM TBWORKFLOW_ITEM_DESTINATARIO D
                WHERE D.WORKFLOW_ID = :REG_WORKFLOW_ID
                AND D.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                AND D.WORKFLOW_ITEM_TAREFA_ID = :REG_TAREFA_ID
    
            INTO
                :PERM_ID,
                :PERM_USUARIO,
                :PERM_NOTIF,
                :PERM_REINICIAR

            DO
            BEGIN

				:VERIF_EXIST = COALESCE((SELECT FIRST 1
                                                E.ID
                                            FROM TBCASO_USUARIOS E
                                            WHERE E.PAINEL_ID = :PAINEL_ID
                                            AND E.USUARIO_ID = :PERM_USUARIO), 0);

				IF (COALESCE(:VERIF_EXIST, 0) = 0) THEN
                BEGIN

					INSERT INTO TBCASO_USUARIOS (PAINEL_ID, USUARIO_ID, VER_ANOTACOES, VER_EMAIL, FEED, NOTIFICACAO)
						VALUES (:PAINEL_ID, :PERM_USUARIO, 1, 1, 1, :PERM_NOTIF);

				END

            END
    
            /*********************************************/

            FOR
                SELECT
                    E.ID,
                    E.USUARIO_ID,
                    COALESCE(E.NOTIFICACAO, 0) AS NOTIFICACAO

                FROM TBCASO_USUARIOS E
                WHERE E.PAINEL_ID = :PAINEL_ID
    
            INTO
                :PERM2_ID,
                :PERM2_USUARIO,
                :PERM2_NOTIF
    
            DO
            BEGIN

				:VERIF_EXIST = COALESCE((SELECT FIRST 1
                                                D.ID
                                            FROM TBWORKFLOW_ITEM_DESTINATARIO D
                                            WHERE D.WORKFLOW_ID = :REG_WORKFLOW_ID
                                            AND D.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                                            AND D.WORKFLOW_ITEM_TAREFA_ID = :REG_TAREFA_ID
                                            AND D.USUARIO_ID = :PERM2_USUARIO), 0);

				IF (COALESCE(:VERIF_EXIST, 0) = 0) THEN
				BEGIN

					INSERT INTO TBWORKFLOW_ITEM_DESTINATARIO (WORKFLOW_ID, WORKFLOW_ITEM_ID, WORKFLOW_ITEM_TAREFA_ID, USUARIO_ID, NOTIFICACAO, REINICIAR)
						VALUES (:REG_WORKFLOW_ID, :REG_WORKFLOW_ITEM_ID, :REG_TAREFA_ID, :PERM2_USUARIO, :PERM2_NOTIF, 0);

				END

            END


            /*********************************************/
            /* Verifica subworkflow */
            FOR
                SELECT
                    F.ID,
                    F.ROTULO,
                    F.TIPO,
                    F.WORKFLOW_ITEM_SUBTASK_ID

                FROM TBWORKFLOW_ITEM_CAMPO F
                WHERE F.WORKFLOW_ID = :REG_WORKFLOW_ID
                AND F.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                AND F.WORKFLOW_ITEM_TAREFA_ID = :TAREFA_ID
                AND COALESCE(F.TIPO, '0') = '5'

            INTO
                :INFO_CAMPO_ID,
                :INFO_CAMPO_ROTULO,
                :INFO_CAMPO_TIPO,
                :INFO_CAMPO_SUBWORKFLOW

            DO
            BEGIN

                IF (COALESCE(:INFO_CAMPO_SUBWORKFLOW, 0) > 0) THEN
                BEGIN
                    EXECUTE PROCEDURE SPU_AJUSTA_USUARIO_SUBWORKFLOW(:CASO_ID, :PAINEL_ID, :INFO_CAMPO_SUBWORKFLOW);
                END

            END
    
        END
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER procedure SPU_AJUSTA_USUARIO_WORKFLOW (
    CASO_ID integer not null)
as
declare variable REGISTRO_ID integer;
declare variable REG_PAINEL_ID integer;
declare variable REG_USUARIO integer;
declare variable REG_WORK_ID integer;
declare variable REG_WORKFLOW_ITEM_ID integer;
declare variable REG_WORKFLOW_ID integer;
declare variable REG_WORKFLOW_ITEM_PROGRESSO integer;
declare variable REG_WORKFLOW_ITEM_STATUS varchar(10);
declare variable REG_WORKFLOW_ITEM_TIPO varchar(10);
declare variable REG_TAREFA_ID integer;
declare variable REG_TAREFA_TITULO varchar(100);
declare variable REG_TAREFA_STATUS varchar(10);
declare variable TAREFA_ID integer;
declare variable PERM_ID integer;
declare variable PERM_USUARIO integer;
declare variable PERM_NOTIF varchar(10);
declare variable PERM_REINICIAR varchar(10);
declare variable PERM2_ID integer;
declare variable PERM2_USUARIO integer;
declare variable PERM2_NOTIF integer;
declare variable INFO_CAMPO_ID integer;
declare variable INFO_CAMPO_ROTULO varchar(200);
declare variable INFO_CAMPO_TIPO varchar(10);
declare variable INFO_CAMPO_SUBWORKFLOW integer;
declare variable VERIF_EXIST integer;
BEGIN

    FOR
        SELECT
            T.ID,
            T.PAINEL_ID,
            T.USUARIO_ID
        
        FROM TBCASO_REGISTRO T
        WHERE T.ID = :CASO_ID
        
    INTO
        :REGISTRO_ID,
        :REG_PAINEL_ID,
        :REG_USUARIO
        
    DO
    BEGIN
        
        FOR
            SELECT
                A.ID,
                A.WORKFLOW_ITEM_ID,
        
                B.WORKFLOW_ID,
                B.PROGRESSO,
                B.STATUS_CONCLUSAO AS STATUS,
                B.TIPO
        
            FROM TBCASO_REGISTRO_WORKFLOWS A
            INNER JOIN TBWORKFLOW_ITEM B ON B.ID = A.WORKFLOW_ITEM_ID
            WHERE A.REGISTRO_ID = :REGISTRO_ID
            AND A.PAINEL_ID = :REG_PAINEL_ID
        
            ORDER BY A.ORDEM
        
        INTO
            :REG_WORK_ID,
            :REG_WORKFLOW_ITEM_ID,
            :REG_WORKFLOW_ID,
            :REG_WORKFLOW_ITEM_PROGRESSO,
            :REG_WORKFLOW_ITEM_STATUS,
            :REG_WORKFLOW_ITEM_TIPO
        
        DO
        BEGIN
        
            FOR
                SELECT
                    C.ID,
                    C.TITULO,
                    C.STATUS_CONCLUSAO AS STATUS,
                    C.WORKFLOW_TAREFA_MODELO_ID
        
                FROM TBWORKFLOW_ITEM_TAREFA C
                WHERE C.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                AND C.WORKFLOW_ID = :REG_WORKFLOW_ID
        
            INTO
                :REG_TAREFA_ID,
                :REG_TAREFA_TITULO,
                :REG_TAREFA_STATUS,
                :TAREFA_ID
        
            DO
            BEGIN
        
                FOR
                    SELECT
                        D.ID,
                        D.USUARIO_ID,
                        COALESCE(D.NOTIFICACAO, '0') AS NOTIFICACAO,
                        D.REINICIAR
        
                    FROM TBWORKFLOW_ITEM_DESTINATARIO D
                    WHERE D.WORKFLOW_ID = :REG_WORKFLOW_ID
                    AND D.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                    AND D.WORKFLOW_ITEM_TAREFA_ID = :REG_TAREFA_ID

                INTO
                    :PERM_ID,
                    :PERM_USUARIO,
                    :PERM_NOTIF,
                    :PERM_REINICIAR

                DO
                BEGIN

					:VERIF_EXIST = COALESCE((SELECT FIRST 1
                                                E.ID
                                            FROM TBCASO_USUARIOS E
                                            WHERE E.PAINEL_ID = :REG_PAINEL_ID
                                            AND E.USUARIO_ID = :PERM_USUARIO), 0);

					IF (COALESCE(:VERIF_EXIST, 0) = 0) THEN
                    BEGIN

						INSERT INTO TBCASO_USUARIOS (PAINEL_ID, USUARIO_ID, VER_ANOTACOES, VER_EMAIL, FEED, NOTIFICACAO)
							VALUES (:REG_PAINEL_ID, :PERM_USUARIO, 1, 1, 1, :PERM_NOTIF);

					END

                END
        
                /*********************************************/
        
                FOR
                    SELECT
                        E.ID,
                        E.USUARIO_ID,
                        COALESCE(E.NOTIFICACAO, 0) AS NOTIFICACAO
        
                    FROM TBCASO_USUARIOS E
                    WHERE E.PAINEL_ID = :REG_PAINEL_ID
        
                INTO
                    :PERM2_ID,
                    :PERM2_USUARIO,
                    :PERM2_NOTIF
        
                DO
                BEGIN

					:VERIF_EXIST = COALESCE((SELECT FIRST 1
                                                D.ID
                                            FROM TBWORKFLOW_ITEM_DESTINATARIO D
                                            WHERE D.WORKFLOW_ID = :REG_WORKFLOW_ID
                                            AND D.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                                            AND D.WORKFLOW_ITEM_TAREFA_ID = :REG_TAREFA_ID
                                            AND D.USUARIO_ID = :PERM2_USUARIO), 0);

					IF (COALESCE(:VERIF_EXIST, 0) = 0) THEN
                    BEGIN

						INSERT INTO TBWORKFLOW_ITEM_DESTINATARIO (WORKFLOW_ID, WORKFLOW_ITEM_ID, WORKFLOW_ITEM_TAREFA_ID, USUARIO_ID, NOTIFICACAO, REINICIAR)
							VALUES (:REG_WORKFLOW_ID, :REG_WORKFLOW_ITEM_ID, :REG_TAREFA_ID, :PERM2_USUARIO, :PERM2_NOTIF, 0);

					END

                END


                /*********************************************/
                /* Verifica subworkflow */
                FOR
                    SELECT
                        F.ID,
                        F.ROTULO,
                        F.TIPO,
                        F.WORKFLOW_ITEM_SUBTASK_ID
    
                    FROM TBWORKFLOW_ITEM_CAMPO F
                    WHERE F.WORKFLOW_ID = :REG_WORKFLOW_ID
                    AND F.WORKFLOW_ITEM_ID = :REG_WORKFLOW_ITEM_ID
                    AND F.WORKFLOW_ITEM_TAREFA_ID = :TAREFA_ID
                    AND COALESCE(F.TIPO, '0') = '5'
    
                INTO
                    :INFO_CAMPO_ID,
                    :INFO_CAMPO_ROTULO,
                    :INFO_CAMPO_TIPO,
                    :INFO_CAMPO_SUBWORKFLOW
    
                DO
                BEGIN
    
                    IF (COALESCE(:INFO_CAMPO_SUBWORKFLOW, 0) > 0) THEN
                    BEGIN
                        EXECUTE PROCEDURE SPU_AJUSTA_USUARIO_SUBWORKFLOW(:REGISTRO_ID, :REG_PAINEL_ID, :INFO_CAMPO_SUBWORKFLOW);
                    END

                END
        
            END
        END
    END
END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/
/***************************************************************************/

COMMENT ON COLUMN TBWORKFLOW_ITEM_TAREFA.STATUS_CONCLUSAO IS 
'0 - NAO INICIADO;
1 - INICIADO;
2 - PAUSADO;
3 - CONCLUIDO;
4 - ENCERRADO/ABORTADO;';


/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_tarefa_b05u for tbworkflow_item_tarefa
active before update position 5
AS
BEGIN
    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 0)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' INICIADA.',
              FN_CURRENT_USER_ID()
         );
    END 

    IF(NEW.STATUS_CONCLUSAO = 2 AND OLD.STATUS_CONCLUSAO = 1)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' PAUSADA.',
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 2)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' REINICIADA.',
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 3 AND OLD.STATUS_CONCLUSAO = 1)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' CONCLUIDA.',
              FN_CURRENT_USER_ID()
         );
    END  

    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 3)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID),'')||' REINICIADA.',
              FN_CURRENT_USER_ID()
         );
    END

    /************************************************************************/

    IF(NEW.STATUS_CONCLUSAO = 4 AND ((OLD.STATUS_CONCLUSAO = 0) OR (OLD.STATUS_CONCLUSAO = 1) OR (OLD.STATUS_CONCLUSAO = 2)))THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID), '')||' ENCERRADA.',
              FN_CURRENT_USER_ID()
         );
    END


    IF(NEW.STATUS_CONCLUSAO = 0 AND OLD.STATUS_CONCLUSAO = 4)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID), '')||' REATIVADA.',
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 1 AND OLD.STATUS_CONCLUSAO = 4)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID), '')||' REINICIADA.',
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 2 AND OLD.STATUS_CONCLUSAO = 4)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID), '')||' PAUSADA.',
              FN_CURRENT_USER_ID()
         );
    END

    IF(NEW.STATUS_CONCLUSAO = 3 AND OLD.STATUS_CONCLUSAO = 4)THEN
    BEGIN
       INSERT INTO TBWORKFLOW_ITEM_HISTORICO(
              WORKFLOW_ITEM_TAREFA_ID,
              WORKFLOW_ITEM_ID,
              HISTORICO,
              USUARIO_ID
         )VALUES(
              NEW.ID,
              NEW.WORKFLOW_ITEM_ID,
              'TAREFA '||COALESCE((SELECT FIRST 1 A.TITULO FROM TBWORKFLOW_TAREFA A WHERE A.ID = NEW.WORKFLOW_TAREFA_MODELO_ID), '')||' CONCLUIDA.',
              FN_CURRENT_USER_ID()
         );
    END
END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_tarefa_b3u for tbworkflow_item_tarefa
active before update position 3
AS
DECLARE VARIABLE QTD_TAREFA INTEGER;
DECLARE VARIABLE QTD_CONCLUIDA INTEGER;
DECLARE VARIABLE WORKFLOW_SUBTASK_LIST VARCHAR(5000);
DECLARE VARIABLE WORKFLOW_SUBTASK_ID INTEGER;
BEGIN

    -- VERIFICA AS ALTERACOES DE SITUACOES NAS TAREFAS PARA AUSTAR REGISTRO PRINCIPAL
    IF ((OLD.STATUS_CONCLUSAO <> NEW.STATUS_CONCLUSAO) AND (NEW.STATUS_CONCLUSAO <> '0') AND (NEW.STATUS_CONCLUSAO <> '3') AND (NEW.STATUS_CONCLUSAO <> '4')) THEN
    BEGIN

        UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = NEW.STATUS_CONCLUSAO
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;

    END

    -- SE TIVER CONCLUINDO OU REINICIANDO A TAREFA.
    IF ((NEW.VERIFICADOR = 1) AND (((NEW.STATUS_CONCLUSAO = '3') OR (OLD.STATUS_CONCLUSAO = '3')) OR ((NEW.STATUS_CONCLUSAO = '4') OR (OLD.STATUS_CONCLUSAO = '4'))) ) THEN
    BEGIN
        
        WORKFLOW_SUBTASK_LIST = '';

        QTD_TAREFA  = (SELECT COUNT(WT.ID)
                        FROM TBWORKFLOW_ITEM_TAREFA WT
                        WHERE WT.STATUSEXCLUSAO = '0'
                        AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID);

        -- SE ESTIVER REINICIANDO A TAREFA.
        IF ((OLD.STATUS_CONCLUSAO = '3') OR (OLD.STATUS_CONCLUSAO = '4')) THEN
        BEGIN
            -- SUBTRAI -1 PORQUE DEVE CONTAR OS CONCLUIDOS ANTERIORES MENOS O CONCLUIDO ATUAL, QUE ESTA SENDO REINICIADO.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)-1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND ((WT.STATUS_CONCLUSAO = '3') OR (WT.STATUS_CONCLUSAO = '4')));

            WORKFLOW_SUBTASK_LIST = COALESCE((SELECT LIST(WC.WORKFLOW_ITEM_SUBTASK_ID, ',')
                                    FROM TBWORKFLOW_ITEM_CAMPO WC
                                    WHERE WC.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                    AND WC.WORKFLOW_ITEM_TAREFA_ID = OLD.ID), '');

        END
        ELSE
        BEGIN
            -- ADICIONA +1 PORQUE DEVE CONTAR OS CONCLUIDOS ANTERIORES E O CONCLUIDO ATUAL.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)+1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND ((WT.STATUS_CONCLUSAO = '3') OR (WT.STATUS_CONCLUSAO = '4')));
        END

        UPDATE TBWORKFLOW_ITEM W
        SET W.PROGRESSO = ((:QTD_CONCLUIDA * 100) / :QTD_TAREFA)
        WHERE W.ID = OLD.WORKFLOW_ITEM_ID;

        IF(NEW.STATUS_CONCLUSAO = '1' AND ((OLD.STATUS_CONCLUSAO = '3') OR (OLD.STATUS_CONCLUSAO = '4')))THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = 1
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END

        IF(:QTD_CONCLUIDA >= 100)THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.PROGRESSO = 100,
                W.STATUS_CONCLUSAO = '3'
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END


        /*******************************************************************/

        IF (WORKFLOW_SUBTASK_LIST <> '') THEN
        BEGIN

            FOR
                SELECT
                    SP.OSPLIT AS WORKFLOW_SUBTASK_ID
                FROM SPLIT(:WORKFLOW_SUBTASK_LIST, ',') SP
            INTO
                :WORKFLOW_SUBTASK_ID    
            DO
            BEGIN

                IF (COALESCE(WORKFLOW_SUBTASK_ID, 0) > 0) THEN
                BEGIN

                    UPDATE TBWORKFLOW_ITEM_TAREFA WT
                        SET WT.STATUS_CONCLUSAO = '0',
                            WT.VERIFICADOR = 1
                        WHERE WT.WORKFLOW_ITEM_ID = :WORKFLOW_SUBTASK_ID;

                    UPDATE TBWORKFLOW_ITEM WI
                        SET WI.PROGRESSO = 0,
                            WI.STATUS_CONCLUSAO = '0'
                        WHERE WI.ID = :WORKFLOW_SUBTASK_ID;

                END

            END

        END

        /*******************************************************************/


        NEW.VERIFICADOR = 0;
    END

END
^

SET TERM ; ^

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_tarefa_b4u for tbworkflow_item_tarefa
active before update position 4
AS
BEGIN
    
    -- Se a tarefa estiver sendo concluida
    IF ((NEW.STATUS_CONCLUSAO = '3') OR (NEW.STATUS_CONCLUSAO = '4')) THEN
    BEGIN

        UPDATE TBNOTIFICACAO N SET
            N.EXECUTADO = '1'
        WHERE
            N.TABELA = 'TBWORKFLOW_ITEM_TAREFA'
        AND N.TABELA_ID = OLD.ID;

    END

END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPU_AJUSTA_STATUS_SUBWORKFLOW (
    WORKFLOW_ITEM_ID integer not null)
as
declare variable WORK_TAREFA_ID integer;
declare variable WORKFLOW_ITEM_PAI_ID integer;
declare variable WORK_SUBTASK_ID integer;
declare variable WORK_TIPO varchar(10);
declare variable WORK_REQUERIDO varchar(10);
declare variable WORK_PRINC integer;
declare variable CONTEM_REG integer;
BEGIN

    FOR
        SELECT
            X.WORKFLOW_ITEM_TAREFA_ID AS ID,
            X.WORKFLOW_ITEM_ID,
        
            X.WORKFLOW_ITEM_SUBTASK_ID AS SUBTASK_ID,
        
            X.TIPO,
            X.REQUERIDO
        
        FROM (
            SELECT
                T.ID,
                T.WORKFLOW_ID,
                T.WORKFLOW_ITEM_ID,
                T.WORKFLOW_ITEM_TAREFA_ID,
                T.ROTULO,
                T.TIPO,
                T.VALOR,
                T.WORKFLOW_ITEM_SUBTASK_ID,
            
                COALESCE((SELECT FIRST 1 COALESCE(Y.REQUERIDO, '0') FROM TBWORKFLOW_CAMPO Y WHERE Y.WORKFLOW_ID = T.WORKFLOW_ID AND Y.WORKFLOW_TAREFA_ID = A.WORKFLOW_TAREFA_MODELO_ID), '0') AS REQUERIDO,
            
                A.TITULO,
                A.DESCRICAO
            
            FROM TBWORKFLOW_ITEM_CAMPO T
            INNER JOIN TBWORKFLOW_ITEM_TAREFA A ON A.WORKFLOW_ITEM_ID = T.WORKFLOW_ITEM_ID AND A.WORKFLOW_ID = T.WORKFLOW_ID AND A.ID = T.WORKFLOW_ITEM_TAREFA_ID
            WHERE T.WORKFLOW_ITEM_SUBTASK_ID = :WORKFLOW_ITEM_ID
        ) X
        WHERE X.WORKFLOW_ITEM_SUBTASK_ID > 0
        --AND X.REQUERIDO = '1'
        
        
        GROUP BY
            X.WORKFLOW_ITEM_TAREFA_ID,
            X.WORKFLOW_ITEM_ID,
        
            X.WORKFLOW_ITEM_SUBTASK_ID,
        
            X.TIPO,
            X.REQUERIDO    
    
    INTO
        :WORK_TAREFA_ID,
        :WORKFLOW_ITEM_PAI_ID,
        :WORK_SUBTASK_ID,
        :WORK_TIPO,
        :WORK_REQUERIDO
    
    DO
    BEGIN

        UPDATE TBWORKFLOW_ITEM_TAREFA A
            SET A.STATUS_CONCLUSAO = 1,
                A.VERIFICADOR = 1
            WHERE A.ID = :WORK_TAREFA_ID;

        /************************************/

        :WORK_PRINC = COALESCE((SELECT FIRST 1
                                    C.ID
                                FROM TBWORKFLOW_ITEM C
                                WHERE C.STATUS_CONCLUSAO = '0'
                                AND C.ID = :WORKFLOW_ITEM_PAI_ID), 0);

        IF (COALESCE(:WORK_PRINC, 0) > 0) THEN
        BEGIN

            UPDATE TBWORKFLOW_ITEM C
                SET C.STATUS_CONCLUSAO = '1'
                WHERE C.ID = :WORK_PRINC;

        END


        :WORK_PRINC = COALESCE((SELECT FIRST 1
                                    C.ID
                                FROM TBWORKFLOW_ITEM C
                                WHERE C.STATUS_CONCLUSAO = '2'
                                AND C.ID = :WORKFLOW_ITEM_PAI_ID), 0);

        IF (COALESCE(:WORK_PRINC, 0) > 0) THEN
        BEGIN

            UPDATE TBWORKFLOW_ITEM C
                SET C.STATUS_CONCLUSAO = '1'
                WHERE C.ID = :WORK_PRINC;

        END

        /************************************/

        :CONTEM_REG = COALESCE((SELECT FIRST 1
                                    T.ID
                                FROM TBWORKFLOW_ITEM_CAMPO T
                                INNER JOIN TBWORKFLOW_ITEM_TAREFA A ON A.WORKFLOW_ITEM_ID = T.WORKFLOW_ITEM_ID AND A.WORKFLOW_ID = T.WORKFLOW_ID AND A.ID = T.WORKFLOW_ITEM_TAREFA_ID
                                WHERE T.WORKFLOW_ITEM_SUBTASK_ID = :WORKFLOW_ITEM_PAI_ID), 0);

        IF (COALESCE(:CONTEM_REG, 0) > 0) THEN
        BEGIN

            EXECUTE PROCEDURE SPU_AJUSTA_STATUS_SUBWORKFLOW(:WORKFLOW_ITEM_PAI_ID);

        END
    END

END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_item_tarefa_b3u for tbworkflow_item_tarefa
active before update position 3
AS
DECLARE VARIABLE QTD_TAREFA INTEGER;
DECLARE VARIABLE QTD_CONCLUIDA INTEGER;
DECLARE VARIABLE WORKFLOW_SUBTASK_LIST VARCHAR(5000);
DECLARE VARIABLE WORKFLOW_SUBTASK_ID INTEGER;
BEGIN

    -- VERIFICA AS ALTERACOES DE SITUACOES NAS TAREFAS PARA AUSTAR REGISTRO PRINCIPAL
    IF ((OLD.STATUS_CONCLUSAO <> NEW.STATUS_CONCLUSAO) AND (NEW.STATUS_CONCLUSAO <> '0') AND (NEW.STATUS_CONCLUSAO <> '3') AND (NEW.STATUS_CONCLUSAO <> '4')) THEN
    BEGIN

        UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = NEW.STATUS_CONCLUSAO
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;

    END

    -- SE TIVER CONCLUINDO OU REINICIANDO A TAREFA.
    IF ((NEW.VERIFICADOR = 1) AND (((NEW.STATUS_CONCLUSAO = '3') OR (OLD.STATUS_CONCLUSAO = '3')) OR ((NEW.STATUS_CONCLUSAO = '4') OR (OLD.STATUS_CONCLUSAO = '4'))) ) THEN
    BEGIN
        
        WORKFLOW_SUBTASK_LIST = '';

        QTD_TAREFA  = (SELECT COUNT(WT.ID)
                        FROM TBWORKFLOW_ITEM_TAREFA WT
                        WHERE WT.STATUSEXCLUSAO = '0'
                        AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID);

        -- SE ESTIVER REINICIANDO A TAREFA.
        IF ((OLD.STATUS_CONCLUSAO = '3') OR (OLD.STATUS_CONCLUSAO = '4')) THEN
        BEGIN
            -- SUBTRAI -1 PORQUE DEVE CONTAR OS CONCLUIDOS ANTERIORES MENOS O CONCLUIDO ATUAL, QUE ESTA SENDO REINICIADO.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)-1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND ((WT.STATUS_CONCLUSAO = '3') OR (WT.STATUS_CONCLUSAO = '4')));

            WORKFLOW_SUBTASK_LIST = COALESCE((SELECT LIST(WC.WORKFLOW_ITEM_SUBTASK_ID, ',')
                                    FROM TBWORKFLOW_ITEM_CAMPO WC
                                    WHERE WC.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                    AND WC.WORKFLOW_ITEM_TAREFA_ID = OLD.ID), '');

        END
        ELSE
        BEGIN
            -- ADICIONA +1 PORQUE DEVE CONTAR OS CONCLUIDOS ANTERIORES E O CONCLUIDO ATUAL.
            QTD_CONCLUIDA   = (SELECT COUNT(WT.ID)+1
                                FROM TBWORKFLOW_ITEM_TAREFA WT
                                WHERE WT.STATUSEXCLUSAO = '0'
                                AND WT.WORKFLOW_ITEM_ID = OLD.WORKFLOW_ITEM_ID
                                AND ((WT.STATUS_CONCLUSAO = '3') OR (WT.STATUS_CONCLUSAO = '4')));
        END

        UPDATE TBWORKFLOW_ITEM W
        SET W.PROGRESSO = ((:QTD_CONCLUIDA * 100) / :QTD_TAREFA)
        WHERE W.ID = OLD.WORKFLOW_ITEM_ID;

        IF(NEW.STATUS_CONCLUSAO = '1' AND ((OLD.STATUS_CONCLUSAO = '3') OR (OLD.STATUS_CONCLUSAO = '4')))THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.STATUS_CONCLUSAO = 1
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END

        IF(:QTD_CONCLUIDA >= 100)THEN
        BEGIN
            UPDATE TBWORKFLOW_ITEM W
            SET W.PROGRESSO = 100,
                W.STATUS_CONCLUSAO = '3'
            WHERE W.ID = OLD.WORKFLOW_ITEM_ID;
        END


        /*******************************************************************/

--        IF (WORKFLOW_SUBTASK_LIST <> '') THEN
--        BEGIN
--
--            FOR
--                SELECT
--                    SP.OSPLIT AS WORKFLOW_SUBTASK_ID
--                FROM SPLIT(:WORKFLOW_SUBTASK_LIST, ',') SP
--            INTO
--                :WORKFLOW_SUBTASK_ID    
--            DO
--            BEGIN
--
--                IF (COALESCE(WORKFLOW_SUBTASK_ID, 0) > 0) THEN
--                BEGIN
--
--                    UPDATE TBWORKFLOW_ITEM_TAREFA WT
--                        SET WT.STATUS_CONCLUSAO = '0',
--                            WT.VERIFICADOR = 1
--                        WHERE WT.WORKFLOW_ITEM_ID = :WORKFLOW_SUBTASK_ID;
--
--                    UPDATE TBWORKFLOW_ITEM WI
--                        SET WI.PROGRESSO = 0,
--                            WI.STATUS_CONCLUSAO = '0'
--                        WHERE WI.ID = :WORKFLOW_SUBTASK_ID;
--
--                END
--
--            END
--
--        END

        /*******************************************************************/


        NEW.VERIFICADOR = 0;
    END

END
^

SET TERM ; ^

/***************************************************************************/
/***************************************************************************/
/***************************************************************************/

SET TERM ^ ;

CREATE OR ALTER procedure SPU_ENCERRAR_WORKFLOW_TAREFAS (
    WORKFLOW_ITEM_ID integer not null,
    WORKFLOW_TAREFA_ID integer not null)
as
declare variable TAREFA_ID integer;
declare variable TAREFA_WORKFLOW_ID integer;
declare variable TAREFA_WORKFLOW_ITEM_ID integer;
declare variable TAREFA_SEQUENCIA varchar(10);
declare variable TAREFA_STATUS varchar(10);
declare variable TAREFA_CONTEM_SUBTASK varchar(10);
declare variable TAREFA_INFO_CAMPOS blob sub_type 1 segment size 80;
declare variable PRX_TAREFA_ID integer;
declare variable PRX_TAREFA_WORKFLOW_ID integer;
declare variable PRX_TAREFA_WORKFLOW_ITEM_ID integer;
declare variable PRX_TAREFA_SEQUENCIA varchar(10);
declare variable PRX_TAREFA_STATUS varchar(10);
declare variable PRX_TAREFA_CONTEM_SUBTASK varchar(10);
declare variable PRX_TAREFA_INFO_CAMPOS blob sub_type 1 segment size 80;
declare variable SUB_TAREFA_ID integer;
declare variable SUB_TAREFA_WORKFLOW_ID integer;
declare variable SUB_TAREFA_WORKFLOW_ITEM_ID integer;
declare variable SUB_TAREFA_SEQUENCIA varchar(10);
declare variable SUB_TAREFA_STATUS varchar(10);
declare variable SUB_TAREFA_CONTEM_SUBTASK varchar(10);
declare variable SUB_TAREFA_INFO_CAMPOS blob sub_type 1 segment size 80;
declare variable CAMPOS blob sub_type 1 segment size 80;
declare variable SUBWORK_ITEM_ID integer;
BEGIN

    FOR
        SELECT
            X.ID,
            X.WORKFLOW_ID,
            X.WORKFLOW_ITEM_ID,
            X.SEQUENCIA,
            X.STATUS_CONCLUSAO,
        
            MAX(X.CONTEM_SUBTASK) AS CONTEM_SUBTASK,
        
            LIST((X.CAMPO_ID||';'||X.CAMPO_ROTULO||';'||X.CAMPO_TIPO||';'||X.CAMPO_VALOR||';'||X.CAMPO_SUBTASK_ID), ',') AS INFO_CAMPOS
        
        FROM (
            SELECT
                A.ID,
                A.WORKFLOW_ID,
                A.WORKFLOW_ITEM_ID,
                A.SEQUENCIA,
                A.STATUS_CONCLUSAO,
        
                IIF(COALESCE(T.WORKFLOW_ITEM_SUBTASK_ID, 0) > 0 AND COALESCE(T.TIPO, '0') = '5', '1', '0') AS CONTEM_SUBTASK,
            
                COALESCE(T.ID, 0) AS CAMPO_ID,
                FN_IS_EMPTY(T.ROTULO) AS CAMPO_ROTULO,
                COALESCE(T.TIPO, '0') AS CAMPO_TIPO,
                FN_IS_EMPTY(T.VALOR) AS CAMPO_VALOR,
                COALESCE(T.WORKFLOW_ITEM_SUBTASK_ID, 0) AS CAMPO_SUBTASK_ID
            
            FROM TBWORKFLOW_ITEM_CAMPO T, TBWORKFLOW_ITEM_TAREFA A
            INNER JOIN TBWORKFLOW_TAREFA B ON B.ID = A.WORKFLOW_TAREFA_MODELO_ID AND B.WORKFLOW_ID = A.WORKFLOW_ID
            WHERE T.WORKFLOW_ID = A.WORKFLOW_ID
            AND T.WORKFLOW_ITEM_ID = A.WORKFLOW_ITEM_ID
            AND T.WORKFLOW_ITEM_TAREFA_ID = A.ID
            
            AND T.WORKFLOW_ITEM_ID = :WORKFLOW_ITEM_ID
            AND A.ID = :WORKFLOW_TAREFA_ID
        
        ) X
        WHERE X.STATUS_CONCLUSAO <> '3'
        AND X.STATUS_CONCLUSAO <> '4'
        
        GROUP BY
            X.ID,
            X.WORKFLOW_ID,
            X.WORKFLOW_ITEM_ID,
            X.SEQUENCIA,
            X.STATUS_CONCLUSAO
    
    INTO
        :TAREFA_ID,
        :TAREFA_WORKFLOW_ID,
        :TAREFA_WORKFLOW_ITEM_ID,
        :TAREFA_SEQUENCIA,
        :TAREFA_STATUS,
        :TAREFA_CONTEM_SUBTASK,
        :TAREFA_INFO_CAMPOS
    
    DO
    BEGIN
    
        /* Altera situacao da tarefa atual */
        UPDATE TBWORKFLOW_ITEM_TAREFA A
            SET A.STATUS_CONCLUSAO = '4',
                A.USUARIO_CONCLUSAO_ID = FN_CURRENT_USER_ID(),
                A.DATAHORA_FIM_CONCLUSAO = CURRENT_TIMESTAMP,
                A.VERIFICADOR = 1
            WHERE A.ID = :TAREFA_ID;
    
        /*********************************************/
    
        IF (COALESCE(:TAREFA_CONTEM_SUBTASK, 0) > 0) THEN
        BEGIN

            FOR
                SELECT
                    S.OSPLIT AS CAMPOS_SUBTASK
                
                FROM SPLIT_V2(:TAREFA_INFO_CAMPOS, ',') S
                WHERE FN_IS_EMPTY(S.OSPLIT) <> ''

            INTO
                :CAMPOS

            DO
            BEGIN

                :SUBWORK_ITEM_ID = COALESCE((SELECT FIRST 1
                                                S.OSPLIT AS WORKFLOW_SUBTASK_ID
                                            FROM SPLIT_V2('573471;CAMPO 5;5;;114303', ';') S
                                            ORDER BY S.OSPLIT DESC), 0);

                 IF (COALESCE(:SUBWORK_ITEM_ID, 0) > 0) THEN
                 BEGIN

                    FOR 
                        SELECT FIRST 1
                            X.ID,
                            X.WORKFLOW_ID,
                            X.WORKFLOW_ITEM_ID,
                            X.SEQUENCIA,
                            X.STATUS_CONCLUSAO,
                        
                            MAX(X.CONTEM_SUBTASK) AS CONTEM_SUBTASK,
                        
                            LIST((X.CAMPO_ID||';'||X.CAMPO_ROTULO||';'||X.CAMPO_TIPO||';'||X.CAMPO_VALOR||';'||X.CAMPO_SUBTASK_ID), ',') AS INFO_CAMPOS
                        
                        FROM (
                            SELECT
                                A.ID,
                                A.WORKFLOW_ID,
                                A.WORKFLOW_ITEM_ID,
                                A.SEQUENCIA,
                                A.STATUS_CONCLUSAO,
                        
                                IIF(COALESCE(T.WORKFLOW_ITEM_SUBTASK_ID, 0) > 0 AND COALESCE(T.TIPO, '0') = '5', '1', '0') AS CONTEM_SUBTASK,
                            
                                COALESCE(T.ID, 0) AS CAMPO_ID,
                                FN_IS_EMPTY(T.ROTULO) AS CAMPO_ROTULO,
                                COALESCE(T.TIPO, '0') AS CAMPO_TIPO,
                                FN_IS_EMPTY(T.VALOR) AS CAMPO_VALOR,
                                COALESCE(T.WORKFLOW_ITEM_SUBTASK_ID, 0) AS CAMPO_SUBTASK_ID
                            
                            FROM TBWORKFLOW_ITEM_CAMPO T, TBWORKFLOW_ITEM_TAREFA A
                            INNER JOIN TBWORKFLOW_TAREFA B ON B.ID = A.WORKFLOW_TAREFA_MODELO_ID AND B.WORKFLOW_ID = A.WORKFLOW_ID
                            WHERE T.WORKFLOW_ID = A.WORKFLOW_ID
                            AND T.WORKFLOW_ITEM_ID = A.WORKFLOW_ITEM_ID
                            AND T.WORKFLOW_ITEM_TAREFA_ID = A.ID
                            
                            AND T.WORKFLOW_ITEM_ID = :SUBWORK_ITEM_ID
                        
                        ) X
                        WHERE X.STATUS_CONCLUSAO <> '3'
                        AND X.STATUS_CONCLUSAO <> '4'
                        
                        GROUP BY
                            X.ID,
                            X.WORKFLOW_ID,
                            X.WORKFLOW_ITEM_ID,
                            X.SEQUENCIA,
                            X.STATUS_CONCLUSAO
            
                    INTO
                        :SUB_TAREFA_ID,
                        :SUB_TAREFA_WORKFLOW_ID,
                        :SUB_TAREFA_WORKFLOW_ITEM_ID,
                        :SUB_TAREFA_SEQUENCIA,
                        :SUB_TAREFA_STATUS,
                        :SUB_TAREFA_CONTEM_SUBTASK,
                        :SUB_TAREFA_INFO_CAMPOS
            
                    DO
                    BEGIN
            
                        EXECUTE PROCEDURE SPU_ENCERRAR_WORKFLOW_TAREFAS(:SUB_TAREFA_WORKFLOW_ITEM_ID, :SUB_TAREFA_ID);
            
                    END
                END
            END
        END
    
        /*********************************************/
    
        FOR
            SELECT FIRST 1
                X.ID,
                X.WORKFLOW_ID,
                X.WORKFLOW_ITEM_ID,
                X.SEQUENCIA,
                X.STATUS_CONCLUSAO,
            
                MAX(X.CONTEM_SUBTASK) AS CONTEM_SUBTASK,
            
                LIST((X.CAMPO_ID||';'||X.CAMPO_ROTULO||';'||X.CAMPO_TIPO||';'||X.CAMPO_VALOR||';'||X.CAMPO_SUBTASK_ID), ',') AS INFO_CAMPOS
            
            FROM (
                SELECT
                    A.ID,
                    A.WORKFLOW_ID,
                    A.WORKFLOW_ITEM_ID,
                    A.SEQUENCIA,
                    A.STATUS_CONCLUSAO,
            
                    IIF(COALESCE(T.WORKFLOW_ITEM_SUBTASK_ID, 0) > 0 AND COALESCE(T.TIPO, '0') = '5', '1', '0') AS CONTEM_SUBTASK,
                
                    COALESCE(T.ID, 0) AS CAMPO_ID,
                    FN_IS_EMPTY(T.ROTULO) AS CAMPO_ROTULO,
                    COALESCE(T.TIPO, '0') AS CAMPO_TIPO,
                    FN_IS_EMPTY(T.VALOR) AS CAMPO_VALOR,
                    COALESCE(T.WORKFLOW_ITEM_SUBTASK_ID, 0) AS CAMPO_SUBTASK_ID
                
                FROM TBWORKFLOW_ITEM_CAMPO T, TBWORKFLOW_ITEM_TAREFA A
                INNER JOIN TBWORKFLOW_TAREFA B ON B.ID = A.WORKFLOW_TAREFA_MODELO_ID AND B.WORKFLOW_ID = A.WORKFLOW_ID
                WHERE T.WORKFLOW_ID = A.WORKFLOW_ID
                AND T.WORKFLOW_ITEM_ID = A.WORKFLOW_ITEM_ID
                AND T.WORKFLOW_ITEM_TAREFA_ID = A.ID
                
                AND T.WORKFLOW_ITEM_ID = :TAREFA_WORKFLOW_ITEM_ID
                AND A.SEQUENCIA > :TAREFA_SEQUENCIA
            
            ) X
            WHERE X.STATUS_CONCLUSAO <> '3'
            AND X.STATUS_CONCLUSAO <> '4'
            
            GROUP BY
                X.ID,
                X.WORKFLOW_ID,
                X.WORKFLOW_ITEM_ID,
                X.SEQUENCIA,
                X.STATUS_CONCLUSAO
    
        INTO
            :PRX_TAREFA_ID,
            :PRX_TAREFA_WORKFLOW_ID,
            :PRX_TAREFA_WORKFLOW_ITEM_ID,
            :PRX_TAREFA_SEQUENCIA,
            :PRX_TAREFA_STATUS,
            :PRX_TAREFA_CONTEM_SUBTASK,
            :PRX_TAREFA_INFO_CAMPOS
    
        DO
        BEGIN
    
            EXECUTE PROCEDURE SPU_ENCERRAR_WORKFLOW_TAREFAS(:PRX_TAREFA_WORKFLOW_ITEM_ID, :PRX_TAREFA_ID);
    
        END
    END
END
^

SET TERM ; ^

/******************************************************************/
/******************************************************************/

CREATE INDEX ITBCASO_REGISTRO_WORKFLOWS_03
ON TBCASO_REGISTRO_WORKFLOWS (WORKFLOW_ITEM_ID);

CREATE INDEX ITBCASO_REGISTRO_WORKFLOWS_02
ON TBCASO_REGISTRO_WORKFLOWS (PAINEL_ID);

CREATE INDEX ITBCASO_REGISTRO_WORKFLOWS_01
ON TBCASO_REGISTRO_WORKFLOWS (REGISTRO_ID);

/******************************************************************/
/******************************************************************/

