/******************************************************************************/
/****              GENERATED BY IBEXPERT 20/12/2023 08:04:16               ****/
/******************************************************************************/

/******************************************************************************/
/****     FOLLOWING SET SQL DIALECT IS JUST FOR THE DATABASE COMPARER      ****/
/******************************************************************************/
SET SQL DIALECT 3;

/******************************************************************************/
/****                             GENERATORS                               ****/
/******************************************************************************/

CREATE SEQUENCE GTBCLIENTE_COBRANCAS;


/******************************************************************************/
/****                                TABLES                                ****/
/******************************************************************************/


CREATE TABLE TBCLIENTE_COBRANCAS (
    ID                 INTEGER NOT NULL,
    CLIENTE_ID         INTEGER NOT NULL,
	NFS_CONTROLE       INTEGER DEFAULT 0,
	PDV_CFE_ID         INTEGER DEFAULT 0,
	OCORRENCIA         BLOB SUB_TYPE 0 SEGMENT SIZE 80,
	VALOR_EM_ABERTO    NUMERIC(15,2) DEFAULT 0,
	DATA			   DATE DEFAULT 'NOW',
    DATAHORA  		   TIMESTAMP DEFAULT 'NOW',
    USUARIO_ID         INTEGER
);

/******************************************************************************/
/****                             PRIMARY KEYS                             ****/
/******************************************************************************/

ALTER TABLE TBCLIENTE_COBRANCAS ADD PRIMARY KEY (ID);


/******************************************************************************/
/****                               INDICES                                ****/
/******************************************************************************/

CREATE INDEX ITBCLIENTE_COBRANCAS_01 ON TBCLIENTE_COBRANCAS (ID);
CREATE INDEX ITBCLIENTE_COBRANCAS_02 ON TBCLIENTE_COBRANCAS (CLIENTE_ID);
CREATE INDEX ITBCLIENTE_COBRANCAS_03 ON TBCLIENTE_COBRANCAS (DATA);


/******************************************************************************/
/****                               TRIGGERS                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         TRIGGERS FOR TABLES                          ****/
/******************************************************************************/


/* TRIGGER: TTBCLIENTE_COBRANCAS01I */
CREATE OR ALTER TRIGGER TTBCLIENTE_COBRANCAS01I FOR TBCLIENTE_COBRANCAS
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(500);
BEGIN
  IF(NEW.ID IS NULL)THEN
  BEGIN
    NEW.ID = GEN_ID(GTBCLIENTE_COBRANCAS,1);
  END

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

    EXECUTE PROCEDURE SPI_HISTORICO('TBCLIENTE_COBRANCAS',NEW.ID,:HISTORICO);
 END
END
^

/* TRIGGER: TBCLIENTE_COBRANCAS_B02U */
CREATE OR ALTER TRIGGER TBCLIENTE_COBRANCAS_B02U FOR TBCLIENTE_COBRANCAS
ACTIVE BEFORE UPDATE POSITION 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(2000);
DECLARE VARIABLE HISTORICO_OCORRENCIA VARCHAR(2000);
BEGIN
    HISTORICO            = '';
    HISTORICO_OCORRENCIA = '';

    IF(OLD.VALOR_EM_ABERTO IS DISTINCT FROM NEW.VALOR_EM_ABERTO)THEN
    BEGIN
       HISTORICO_OCORRENCIA = 'VALOR: R$ '||FN_FORMAT_NUMBER2(COALESCE(OLD.VALOR_EM_ABERTO,0.00),2)||'-> R$ '||FN_FORMAT_NUMBER2(COALESCE(NEW.VALOR_EM_ABERTO,0.00),2);
    END

    IF(OLD.OCORRENCIA IS DISTINCT FROM NEW.OCORRENCIA)THEN
    BEGIN
       HISTORICO_OCORRENCIA = 'OCORRENCIA: '||FN_ELIPSE2(COALESCE(OLD.OCORRENCIA,''),200)||'->'||FN_ELIPSE2(COALESCE(NEW.OCORRENCIA,''),200);
    END  

    IF(:HISTORICO <> '')THEN
    BEGIN
       EXECUTE PROCEDURE SPI_HISTORICO('TBCLIENTE_COBRANCAS',NEW.ID,'REG. ALTERADO: '||:HISTORICO);
    END

    IF(:HISTORICO_OCORRENCIA <> '')THEN
    BEGIN
       EXECUTE PROCEDURE SPI_HISTORICO('TBCLIENTE_COBRANCAS',NEW.ID,'REG. ALTERADO: '||:HISTORICO_OCORRENCIA);
    END
END^


/* TRIGGER: TTBCLIENTE_COBRANCAS0IU */
CREATE OR ALTER TRIGGER TTBCLIENTE_COBRANCAS0IU FOR TBCLIENTE_COBRANCAS
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
  IF(NEW.USUARIO_ID IS NULL)THEN
  BEGIN
    NEW.USUARIO_ID = FN_CURRENT_USER_ID();
  END
END
^

/* TRIGGER: TBCLIENTE_COBRANCAS_BD0 */
CREATE OR ALTER TRIGGER TBCLIENTE_COBRANCAS_BD0 FOR TBCLIENTE_COBRANCAS
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE HISTORICO VARCHAR(2000);
BEGIN
  EXECUTE PROCEDURE SPI_HISTORICO('TBCLIENTE_COBRANCAS',OLD.ID,'REGISTRO ID: '||FN_LPAD(OLD.ID,4,0)||', VR: R$ '||FN_FORMAT_NUMBER(OLD.VALOR_EM_ABERTO, 2)||' EXCLUIDO');
END
^

SET TERM ; ^


/******************************************************************************/
/****                              PRIVILEGES                              ****/
/******************************************************************************/


/* PRIVILEGES OF USERS */
GRANT ALL ON TBCLIENTE_COBRANCAS TO GC;
GRANT SELECT ON MON$ATTACHMENTS TO PUBLIC;
GRANT SELECT ON MON$CALL_STACK TO PUBLIC;
GRANT SELECT ON MON$CONTEXT_VARIABLES TO PUBLIC;
GRANT SELECT ON MON$DATABASE TO PUBLIC;
GRANT SELECT ON MON$IO_STATS TO PUBLIC;
GRANT SELECT ON MON$MEMORY_USAGE TO PUBLIC;
GRANT SELECT ON MON$RECORD_STATS TO PUBLIC;
GRANT SELECT ON MON$STATEMENTS TO PUBLIC;
GRANT SELECT ON MON$TABLE_STATS TO PUBLIC;
GRANT SELECT ON MON$TRANSACTIONS TO PUBLIC;

/* PRIVILEGES OF ROLES */
GRANT ALL ON TBCLIENTE_COBRANCAS TO RDB$ADMIN;