DROP INDEX ITBESTEIRA_05;

CREATE OR ALTER VIEW TBGP(
    ID,
    DESCRICAO,
    FAMILIA_ID,
    PERFIL,
    STATUS,
    REMESSAS,
    DOM,
    SEG,
    TER,
    QUA,
    QUI,
    SEX,
    SAB,
    TEMPO,
    CODIGO1,
    H_SEG,
    H_TER,
    H_QUA,
    H_QUI,
    H_SEX,
    H_SAB,
    H_DOM,
    SUPERVISOR1,
    SUPERVISOR2,
    REG_ESTOQUE,
    REG_QUANTIDADE,
    FLAG,
    MONITOR,
    PERFIL_PED,
    MENSAGEM,
    CCUSTO,
    DIAS,
    VER_PECA_DISPONIVEL,
    VER_PARES,
    HABILITA_QUEBRA_TALAO_SKU,
    REQUISITA_SOBRA)
AS
SELECT
    CODIGO,
    DESCRICAO,
    FAMILIA_CODIGO,
    '' as PERFIL,
    STATUS,
    TALOES,
    DOM,
    SEG,
    TER,
    QUA,
    QUI,
    SEX,
    SAB,
    TEMPO,
    CODIGO1,
    H_SEG,
    H_TER,
    H_QUA,
    H_QUI,
    H_SEX,
    H_SAB,
    H_DOM,
    SUPERVISOR1,
    SUPERVISOR2,
    REG_ESTOQUE,
    REG_QUANTIDADE,
    FLAG,
    MONITOR,
    PERFIL_PED,
    MENSAGEM,
    CCUSTO,
    TALOES,
    VER_PECA_DISPONIVEL,
    VER_PARES,
    HABILITA_QUEBRA_TALAO_SKU,
    REQUISITA_SOBRA
FROM TBESTEIRA
;

CREATE OR ALTER trigger ttbesteira_b0iu_01 for tbesteira
active before insert or update position 0
AS
DECLARE VARIABLE ID INTEGER;
begin

end;

CREATE OR ALTER trigger ttbesteira_b9u for tbesteira
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(1000);
begin

end;

ALTER TABLE TBESTEIRA ADD OLD_PERFIL VARCHAR(10) DEFAULT '';
update TBESTEIRA set OLD_PERFIL = PERFIL;
ALTER TABLE TBESTEIRA DROP PERFIL;
ALTER TABLE TBESTEIRA ADD PERFIL VARCHAR(10) DEFAULT '';
update TBESTEIRA set PERFIL = OLD_PERFIL;
ALTER TABLE TBESTEIRA DROP OLD_PERFIL;

CREATE INDEX ITBESTEIRA_05 ON TBESTEIRA (PERFIL);

CREATE OR ALTER trigger ttbesteira_b9u for tbesteira
active before update position 9
AS
DECLARE VARIABLE HISTORICO VARCHAR(1000);
begin

   HISTORICO = '';

   if (OLD.DESCRICAO <> NEW.DESCRICAO) then
       BEGIN
       HISTORICO = HISTORICO||' Desc.'||OLD.DESCRICAO||'->'||NEW.DESCRICAO;
       END
   if (OLD.Perfil <> NEW.Perfil) then
       BEGIN
       HISTORICO = HISTORICO||' Perf.'||OLD.Perfil||'->'||NEW.Perfil;
       END
   if (OLD.Perfil_Ped <> NEW.Perfil_Ped) then
       BEGIN
       HISTORICO = HISTORICO||' Perf.Ped.'||OLD.Perfil_Ped||'->'||NEW.Perfil_Ped;
       END
   if (OLD.FAMILIA_CODIGO <> NEW.FAMILIA_CODIGO) then
       BEGIN
       HISTORICO = HISTORICO||' Fam.'||OLD.FAMILIA_CODIGO||'->'||NEW.FAMILIA_CODIGO;
       END
   if (OLD.CODIGO1 <> NEW.CODIGO1) then
       BEGIN
       HISTORICO = HISTORICO||' Cod.Bar.'||OLD.CODIGO1||'->'||NEW.CODIGO1;
       END
   if (OLD.Taloes <> NEW.Taloes) then
       BEGIN
       HISTORICO = HISTORICO||' Rem.Aber.'||OLD.Taloes||'->'||NEW.Taloes;
       END

   if (OLD.REG_ESTOQUE <> NEW.REG_ESTOQUE) then
       BEGIN
       HISTORICO = HISTORICO||' Reg.Est.'||OLD.REG_ESTOQUE||'->'||NEW.REG_ESTOQUE;
       END

   if (OLD.REG_QUANTIDADE <> NEW.REG_QUANTIDADE) then
       BEGIN
       HISTORICO = HISTORICO||' Reg.Qtd.'||OLD.REG_QUANTIDADE||'->'||NEW.REG_QUANTIDADE;
       END

   if (OLD.SUPERVISOR1 <> NEW.SUPERVISOR1) then
       BEGIN
       HISTORICO = HISTORICO||' Superv.1:'||OLD.SUPERVISOR1||'->'||NEW.SUPERVISOR1;
       END
   if (OLD.SUPERVISOR2 <> NEW.SUPERVISOR2) then
       BEGIN
       HISTORICO = HISTORICO||' Superv.2:'||OLD.SUPERVISOR2||'->'||NEW.SUPERVISOR2;
       END

   if (OLD.BSC_GRUPO <> NEW.BSC_GRUPO) then
       BEGIN
       HISTORICO = HISTORICO||' Grp.BSC:'||OLD.BSC_GRUPO||'->'||NEW.BSC_GRUPO;
       END
   if (OLD.CCUSTO <> NEW.CCUSTO) then
       BEGIN
       HISTORICO = HISTORICO||' C.Custo:'||OLD.CCUSTO||'->'||NEW.CCUSTO;
       END
   if (OLD.PRAZO_PRODUCAO <> NEW.PRAZO_PRODUCAO) then
       BEGIN
       HISTORICO = HISTORICO||' Pzo.Prod:'||OLD.PRAZO_PRODUCAO||'->'||NEW.PRAZO_PRODUCAO;
       END
   if (OLD.PERC_INEFICIENCIA <> NEW.PERC_INEFICIENCIA) then
       BEGIN
       HISTORICO = HISTORICO||' % Inefic.'||OLD.PERC_INEFICIENCIA||'->'||NEW.PERC_INEFICIENCIA;
       END

   if (OLD.PRODUCAO <> NEW.PRODUCAO) then
       BEGIN
       HISTORICO = HISTORICO||' Cap.Prod:'||OLD.PRODUCAO||'->'||NEW.PRODUCAO;
       END

   if (OLD.SEG  <> NEW.SEG or OLD.H_SEG <> NEW.H_SEG) then
       BEGIN
       HISTORICO = HISTORICO||' Min.SEG.'||OLD.SEG||'->'||NEW.SEG||' Hor.'||OLD.H_SEG||'->'||NEW.H_SEG;
       END
   if (OLD.TER  <> NEW.TER or OLD.H_TER <> NEW.H_TER) then
       BEGIN
       HISTORICO = HISTORICO||' Min.TER.'||OLD.TER||'->'||NEW.TER||' Hor.'||OLD.H_TER||'->'||NEW.H_TER;
       END
   if (OLD.QUA  <> NEW.QUA or OLD.H_QUA <> NEW.H_QUA) then
       BEGIN
       HISTORICO = HISTORICO||' Min.QUA.'||OLD.QUA||'->'||NEW.QUA||' Hor.'||OLD.H_QUA||'->'||NEW.H_QUA;
       END
   if (OLD.QUI  <> NEW.QUI or OLD.H_QUI <> NEW.H_QUI) then
       BEGIN
       HISTORICO = HISTORICO||' Min.QUI.'||OLD.QUI||'->'||NEW.QUI||' Hor.'||OLD.H_QUI||'->'||NEW.H_QUI;
       END
   if (OLD.SEX  <> NEW.SEX or OLD.H_SEX <> NEW.H_SEX) then
       BEGIN
       HISTORICO = HISTORICO||' Min.SEX.'||OLD.SEX||'->'||NEW.SEX||' Hor.'||OLD.H_SEX||'->'||NEW.H_SEX;
       END
   if (OLD.SAB  <> NEW.SAB or OLD.H_SAB <> NEW.H_SAB) then
       BEGIN
       HISTORICO = HISTORICO||' Min.SAB.'||OLD.SAB||'->'||NEW.SAB||' Hor.'||OLD.H_SAB||'->'||NEW.H_SAB;
       END
   if (OLD.DOM  <> NEW.DOM or OLD.H_DOM <> NEW.H_DOM) then
       BEGIN
       HISTORICO = HISTORICO||' Min.DOM.'||OLD.DOM||'->'||NEW.DOM||' Hor.'||OLD.H_DOM||'->'||NEW.H_DOM;
       END

   if (OLD.STATUS <> NEW.STATUS) then
       BEGIN
       if (NEW.STATUS = '0') then
           HISTORICO = HISTORICO||' Status Inativado';
       if (NEW.STATUS = '1') then
           HISTORICO = HISTORICO||' Status Ativado';
       END

   if (char_length(:HISTORICO) > 500) then
       BEGIN
       :HISTORICO = SUBSTRING(:HISTORICO from 1 for 500);
       END

   if (HISTORICO <> '') Then
       Begin
       INSERT INTO TBHISTORICO (
       TABELA, TABELA_ID, USUARIO, IP, HISTORICO) VALUES (
      'TBESTEIRA', OLD.CODIGO,
      (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),
       :HISTORICO);
       End
end;

CREATE OR ALTER trigger ttbesteira_b0iu_01 for tbesteira
active before insert or update position 0
AS
DECLARE VARIABLE ID INTEGER;
begin

   if (New.Familia_Codigo > 0) then
       Begin
       ID = Coalesce((Select First 1 F.Codigo From TbFamilia F Where F.Codigo = New.Familia_Codigo),0);
       if (id = 0) then
           Begin
           Exception "..." 'FAMÍLIA DE PRODUTOS INVÁLIDA!'||
                           ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
           End
       End

   if (Char_Length(New.CCusto) > 0) then
       Begin
       ID = Coalesce((Select First 1 C.ID From TbCentro_De_Custo C Where C.Codigo = New.CCusto),0);
       if (id = 0) then
           Begin
           Exception "..." 'CENTRO DE CUSTO INVÁLIDO!'||
                           ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
           End
       End


   if (Char_Length(New.Perfil) > 0) then
       Begin
       ID = Coalesce((Select First 1 P.Controle From TbPerfil P Where P.ID = New.Perfil and P.Tabela = 'GP'),0);
       if (id = 0) then
           Begin
           Exception "..." 'PERFIL DE GRUPO DE PRODUÇÃO INVÁLIDO!'||
                           ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
           End
       End

   if (Char_Length(New.Perfil_Ped) > 0) then
       Begin
       ID = Coalesce((Select First 1 P.Controle From TbPerfil P Where P.ID = New.Perfil_Ped and P.Tabela = 'PED'),0);
       if (id = 0) then
           Begin
           Exception "..." 'PERFIL DE PEDIDOS INVÁLIDO!'||
                           ' . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ';
           End
       End

end;


CREATE OR ALTER VIEW TBGP(
    ID,
    DESCRICAO,
    FAMILIA_ID,
    PERFIL,
    STATUS,
    REMESSAS,
    DOM,
    SEG,
    TER,
    QUA,
    QUI,
    SEX,
    SAB,
    TEMPO,
    CODIGO1,
    H_SEG,
    H_TER,
    H_QUA,
    H_QUI,
    H_SEX,
    H_SAB,
    H_DOM,
    SUPERVISOR1,
    SUPERVISOR2,
    REG_ESTOQUE,
    REG_QUANTIDADE,
    FLAG,
    MONITOR,
    PERFIL_PED,
    MENSAGEM,
    CCUSTO,
    DIAS,
    VER_PECA_DISPONIVEL,
    VER_PARES,
    HABILITA_QUEBRA_TALAO_SKU,
    REQUISITA_SOBRA)
AS
SELECT
    CODIGO,
    DESCRICAO,
    FAMILIA_CODIGO,
    PERFIL,
    STATUS,
    TALOES,
    DOM,
    SEG,
    TER,
    QUA,
    QUI,
    SEX,
    SAB,
    TEMPO,
    CODIGO1,
    H_SEG,
    H_TER,
    H_QUA,
    H_QUI,
    H_SEX,
    H_SAB,
    H_DOM,
    SUPERVISOR1,
    SUPERVISOR2,
    REG_ESTOQUE,
    REG_QUANTIDADE,
    FLAG,
    MONITOR,
    PERFIL_PED,
    MENSAGEM,
    CCUSTO,
    TALOES,
    VER_PECA_DISPONIVEL,
    VER_PARES,
    HABILITA_QUEBRA_TALAO_SKU,
    REQUISITA_SOBRA
FROM TBESTEIRA
;
