/******************************************************************************/
/****              Generated by IBExpert 10/03/2009 08:16:05               ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES NONE;



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


CREATE GENERATOR GN_CONTABILIZACAO;

CREATE TABLE TB_CONTABILIZACAO (
    CD_CONTABILIZACAO               INTEGER NOT NULL,
    CD_EMPRESA                      INTEGER NOT NULL,
    DT_CONTABIL                     TIMESTAMP NOT NULL,
    NR_DOCUMENTO                    DESCRICAO_MEDIA NOT NULL /* DESCRICAO_MEDIA = VARCHAR(40) */,
    FG_DB_CR                        DEBITO_CREDITO NOT NULL /* DEBITO_CREDITO = CHAR(1) NOT NULL CHECK (VALUE IN ('D','C')) */,
    VL_CONTABIL                     VALOR NOT NULL /* VALOR = DECIMAL(16,2) NOT NULL */,
    DS_OBSERVACAO                   OBSERVACAO /* OBSERVACAO = VARCHAR(255) */,
    CD_CONTA_CONTABIL               INTEGER NOT NULL,
    CD_NOTA_ENTRADA                 INTEGER,
    CD_NOTA_SAIDA                   INTEGER,
    CD_USUARIO_INCLUSAO             INTEGER NOT NULL,
    DT_INCLUSAO                     TIMESTAMP NOT NULL,
    CD_USUARIO_ALTERACAO            INTEGER NOT NULL,
    DT_ALTERACAO                    TIMESTAMP NOT NULL,
    CD_FORNECEDOR_EMPRESA           INTEGER,
    CD_CLIENTE_EMPRESA              INTEGER,
    CD_PESSOA                       INTEGER,
    CD_REQUISICAO_MATERIAL_ITEM     INTEGER,
    CD_REQUISICAO_MATERIAL_ITEM_DV  INTEGER,
    CD_MOVIMENTO_TR_ITEM            INTEGER,
    CD_ADIANTAMENTO_CLIENTE         INTEGER,
    CD_ADIANTAMENTO_CLIENTE_BAIXA   INTEGER,
    CD_CHEQUE                       INTEGER,
    CD_NOTA_ENTRADA_DEV_NFS         INTEGER,
    CD_DESPESA_VIAGEM               INTEGER,
    CD_MOVIMENTO_BANCARIO           INTEGER
);




/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT PK_TB_CONTABILIZACAO PRIMARY KEY (CD_CONTABILIZACAO);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_1 FOREIGN KEY (CD_CONTA_CONTABIL) REFERENCES TB_CONTA_CONTABIL (CD_CONTA_CONTABIL);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_10 FOREIGN KEY (CD_REQUISICAO_MATERIAL_ITEM) REFERENCES TB_REQUISICAO_MATERIAL_ITEM (CD_REQUISICAO_MATERIAL_ITEM);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_11 FOREIGN KEY (CD_REQUISICAO_MATERIAL_ITEM_DV) REFERENCES TB_REQUISICAO_MATERIAL_ITEM_DV (CD_REQUISICAO_MATERIAL_ITEM_DV);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_12 FOREIGN KEY (CD_MOVIMENTO_TR_ITEM) REFERENCES TB_MOVIMENTO_TR_ITEM (CD_MOVIMENTO_TR_ITEM);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_13 FOREIGN KEY (CD_ADIANTAMENTO_CLIENTE) REFERENCES TB_ADIANTAMENTO_CLIENTE (CD_ADIANTAMENTO_CLIENTE);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_14 FOREIGN KEY (CD_ADIANTAMENTO_CLIENTE_BAIXA) REFERENCES TB_ADIANTAMENTO_CLIENTE_BAIXA (CD_ADIANTAMENTO_CLIENTE_BAIXA);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_15 FOREIGN KEY (CD_CHEQUE) REFERENCES TB_CHEQUE (CD_CHEQUE);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_16 FOREIGN KEY (CD_NOTA_ENTRADA_DEV_NFS) REFERENCES TB_NOTA_ENTRADA_DEV_NFS (CD_NOTA_ENTRADA_DEV_NFS);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_17 FOREIGN KEY (CD_DESPESA_VIAGEM) REFERENCES TB_DESPESA_VIAGEM (CD_DESPESA_VIAGEM);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_18 FOREIGN KEY (CD_MOVIMENTO_BANCARIO) REFERENCES TB_MOVIMENTO_BANCARIO (CD_MOVIMENTO_BANCARIO);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_2 FOREIGN KEY (CD_NOTA_ENTRADA) REFERENCES TB_NOTA_ENTRADA (CD_NOTA_ENTRADA);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_3 FOREIGN KEY (CD_NOTA_SAIDA) REFERENCES TB_NOTA_SAIDA (CD_NOTA_SAIDA);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_4 FOREIGN KEY (CD_USUARIO_INCLUSAO) REFERENCES TB_USUARIO (CD_USUARIO);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_5 FOREIGN KEY (CD_USUARIO_ALTERACAO) REFERENCES TB_USUARIO (CD_USUARIO);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_6 FOREIGN KEY (CD_EMPRESA) REFERENCES TB_PESSOA (CD_PESSOA);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_7 FOREIGN KEY (CD_FORNECEDOR_EMPRESA) REFERENCES TB_FORNECEDOR_EMPRESA (CD_FORNECEDOR_EMPRESA);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_8 FOREIGN KEY (CD_CLIENTE_EMPRESA) REFERENCES TB_CLIENTE_EMPRESA (CD_CLIENTE_EMPRESA);
ALTER TABLE TB_CONTABILIZACAO ADD CONSTRAINT FK_TB_CONTABILIZACAO_9 FOREIGN KEY (CD_PESSOA) REFERENCES TB_PESSOA (CD_PESSOA);


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

CREATE INDEX TB_CONTABILIZACAO_IDX1 ON TB_CONTABILIZACAO (DT_CONTABIL);


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


SET TERM ^ ;


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



/* Trigger: TB_CONTABILIZACAO_BIU0 */
CREATE TRIGGER TB_CONTABILIZACAO_BIU0 FOR TB_CONTABILIZACAO
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
  declare variable usuario integer;
begin
  select cd_usuario
    from tb_historico_login
   where cd_conexao = current_connection
    into :usuario;

  if ( current_user = 'NOTRIGGER' ) then
  begin
    if (new.cd_usuario_inclusao is null ) then
      new.cd_usuario_inclusao = :usuario;
    if (new.dt_inclusao is null ) then
      new.dt_inclusao = current_timestamp;
    if (new.cd_usuario_alteracao is null ) then
      new.cd_usuario_alteracao = :usuario;
    if (new.dt_alteracao is null ) then
      new.dt_alteracao = current_timestamp;
    exit;
  end

  if (inserting) then
  begin
    new.dt_inclusao = current_timestamp;
    new.cd_usuario_inclusao = :usuario;
    if ( new.cd_contabilizacao is null ) then
      new.cd_contabilizacao = gen_id(gn_contabilizacao,1);
  end
  new.dt_alteracao = current_timestamp;
  new.cd_usuario_alteracao = :usuario;
end
^


SET TERM ; ^



/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/