UPDATE OR INSERT INTO TBMENU (CONTROLE, GRUPO, SUBGRUPO, TIPO, URL, DESCRICAO) 
	VALUES (32060, 'PCM', 'PCM', '1', '', 'CADASTRO DE ORIGENS DE CHAMADO') 
	MATCHING (CONTROLE);

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


CREATE GENERATOR GTBPCM_ORIGEM;

CREATE TABLE TBPCM_ORIGEM (
    ID         INTEGER NOT NULL,
    DESCRICAO  VARCHAR(50),
    STATUS     CHAR(1),
    SETOR_ID   INTEGER
);




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

ALTER TABLE TBPCM_ORIGEM ADD CONSTRAINT PK_TBPCM_ORIGEM PRIMARY KEY (ID);


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



SET TERM ^ ;



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



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

    HISTORICO = '';

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

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


/* Trigger: TTBPCM_ORIGEM_B01I */
CREATE OR ALTER TRIGGER TTBPCM_ORIGEM_B01I FOR TBPCM_ORIGEM
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBPCM_ORIGEM, 1);
END
^


/* Trigger: TTBPCM_ORIGEM_B01U */
CREATE OR ALTER TRIGGER TTBPCM_ORIGEM_B01U FOR TBPCM_ORIGEM
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(5000);

DECLARE VARIABLE OLD_SETOR VARCHAR(500);
DECLARE VARIABLE NEW_SETOR VARCHAR(500);
BEGIN

    HISTORICO = '';

    OLD_SETOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_SETOR A WHERE A.ID = OLD.SETOR_ID), '');
    NEW_SETOR = COALESCE((SELECT FIRST 1 FN_LPAD(A.ID, 4, '0')||' - '||A.DESCRICAO FROM TBPCM_SETOR A WHERE A.ID = NEW.SETOR_ID), '');

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

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

    IF (OLD.SETOR_ID <> NEW.SETOR_ID) THEN
    BEGIN
        HISTORICO = HISTORICO||' SETOR:'||OLD_SETOR||'->'||NEW_SETOR;
    END

    IF (HISTORICO <> '') THEN
    BEGIN
        HISTORICO = 'REGISTRO ALTERADO:'||HISTORICO;
        EXECUTE PROCEDURE SPI_HISTORICO('TBPCM_ORIGEM', OLD.ID, FN_ELIPSES(HISTORICO, 500));
    END
END
^


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

    HISTORICO = '';

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

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

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBPCM_ORIGEM.STATUS IS 
'0 - INATIVO;
1 - ATIVO;';