/*

	REQUERIDO: FTR 4.4.0.19
	FORÇA DE VENDA: 
	XAMPP

*/

ALTER TABLE PEDIDO ADD HASHING VARCHAR(500);
ALTER TABLE VENDA ADD HASHING VARCHAR(500);
ALTER TABLE ITEM_PED ADD HASHING VARCHAR(500);

ALTER TABLE CONFIG_SYS ADD MGR_PRC_MIN_MKT SMALLINT DEFAULT 0 NOT NULL;
	
UPDATE CONFIG_SYS SET CONFIG_SYS.MGR_PRC_MIN_MKT = CONFIG_SYS.MNG_PRC_MIN_MKT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SP_RECENT_PRODUCT_ORDERS (
    ID_PES INTEGER,
    ID_PRO INTEGER)
RETURNS (
    QUANT NUMERIC(18,3),
    EMISSAO DATE)
AS
DECLARE OPERACAO SMALLINT = 1;
DECLARE ABERTO   SMALLINT = 1;
DECLARE FATURADO SMALLINT = 2;
BEGIN
  FOR
    SELECT FIRST 3
    ITEM_PED.QUANT,
    PEDIDO.EMISSAO
    FROM ITEM_PED
    JOIN PEDIDO ON (ITEM_PED.ID_PED = PEDIDO.ID_PED)
    AND ITEM_PED.ID_PRO = :ID_PRO
    AND PEDIDO.ID_PES   = :ID_PES
    AND PEDIDO.ID_COP   = :OPERACAO
    AND PEDIDO.STATUS IN (:ABERTO, :FATURADO)
    ORDER BY PEDIDO.EMISSAO DESC
    INTO :QUANT,
         :EMISSAO
  DO
    SUSPEND;
END^

CREATE OR ALTER PROCEDURE SP_INS_PEDIDO (
    ID_PED INTEGER,
    ID_PES INTEGER,
    ID_REP INTEGER,
    ID_COP SMALLINT,
    EMISSAO DATE,
    FATURA DATE,
    STATUS SMALLINT,
    TOTAL_NEG NUMERIC(10,2),
    TOTAL_SUG NUMERIC(10,2),
    OBSERV_1 VARCHAR(80),
    OBSERV_2 VARCHAR(80),
    OBSERV_3 VARCHAR(80),
    NUM_PED VARCHAR(20),
    HASHING VARCHAR(500))
AS
DECLARE ID_EMP DOM_ID_EMPRESA;
DECLARE EMISSOR integer = 2;
BEGIN

  STATUS = 99;
  SELECT FIRST 1 EMPRESA.ID_EMP FROM EMPRESA ORDER BY EMPRESA.EMP_DEF DESC INTO :ID_EMP;

  INSERT INTO PEDIDO (
  ID_PED,
  ID_PES,
  ID_REP,
  ID_EMP,
  ID_COP,
  EMISSAO,
  FATURA,
  STATUS,
  TOTAL_NEG,
  TOTAL_SUG,
  TOTAL_FID,
  TOTAL_MIN,
  PESO_LIQ,
  PESO_BRT,
  EMISSOR,
  OBSERV_1,
  OBSERV_2,
  OBSERV_3,
  TRANS_EMISSOR,
  PICKING_STATUS,
  COD_COMPRA,
  HASHING)
  VALUES (:ID_PED,
          :ID_PES,
          :ID_REP,
          :ID_EMP,
          :ID_COP,
          :EMISSAO,
          :FATURA,
          :STATUS,
          :TOTAL_NEG,
          :TOTAL_SUG,
          0,
          0,
          0,
          0,
          :EMISSOR,
          :OBSERV_1,
          :OBSERV_2,
          :OBSERV_3,
          CURRENT_TIMESTAMP,
          0,
          :NUM_PED,
          :HASHING);

END^

CREATE OR ALTER PROCEDURE SP_INS_ITEM_PED (
    ID_IPE INTEGER,
    ID_PED INTEGER,
    ID_PRO INTEGER,
    QUANT NUMERIC(10,2),
    QUANT_SOLIC NUMERIC(10,2),
    PRECO_UNIT NUMERIC(10,2),
    PRECO_SUG NUMERIC(10,2),
    DESCONTO NUMERIC(10,5),
    HASHING VARCHAR(500))
AS
BEGIN
  INSERT INTO ITEM_PED (
     ID_IPE,
     ID_PED,
     ID_PRO,
     ID_EMB,
     QUANT,
     QUANT_SOLIC,
     PRECO_UNIT,
     PRECO_SUG,
     DESCONTO,
     DESCONTO_FID,
     PRECO_CUSTO,
     HASHING)
         VALUES (:ID_IPE,
                 :ID_PED,
                 :ID_PRO,
                 0,
                 :QUANT,
                 :QUANT_SOLIC,
                 :PRECO_UNIT,
                 :PRECO_SUG,
                 :DESCONTO,
                  0,
                  0,
                  :HASHING);
END^

CREATE OR ALTER PROCEDURE SP_INS_VENDA (
    ID_PED INTEGER,
    ID_CPG INTEGER,
    ID_FPG INTEGER,
    HASHING VARCHAR(500))
AS
BEGIN
    INSERT INTO VENDA (
      ID_PED,
      ID_CPG,
      ID_FPG,
      COMISSAO,
      HASHING)
    VALUES (:ID_PED,
            :ID_CPG,
            :ID_FPG,
             0,
            :HASHING);
END^

CREATE OR ALTER TRIGGER BI_ITEM_PED_CHECK_HASH FOR ITEM_PED
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (UPPER(USER) = 'FORCA_V' AND NEW.HASHING IS NOT NULL AND EXISTS(SELECT ITEM_PED.HASHING FROM ITEM_PED WHERE ITEM_PED.HASHING = NEW.HASHING)) THEN
    EXCEPTION EXC_UNIQUE 'SQLCODE:-803';

END^

SET TERM ; ^

DROP TRIGGER BI_PEDIDO_COD_COMPRA;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SET_REPLICANDO (
    ID_REP INTEGER,
    TABLE_NAME VARCHAR(20))
AS
DECLARE ID INTEGER;
BEGIN
  SELECT ID + 1 FROM SP_CURR_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;
  UPDATE FV_REPLICATE SET
         FV_REPLICATE.REPLICATING = :ID
  WHERE  FV_REPLICATE.ID_REP      = :ID_REP
  AND    FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
  AND    FV_REPLICATE.REPLICATING = 0
  AND    FV_REPLICATE.DATE_REPL   IS NULL
  ORDER BY FV_REPLICATE.ID_ITEM;
  --ROWS 1 TO 200;
END^

CREATE OR ALTER PROCEDURE SP_INS_BONIFIC (
    ID_PED INTEGER)
AS
BEGIN
  INSERT INTO BONIFIC (ID_PED)VALUES(:ID_PED);
END^

SET TERM ; ^


GRANT INSERT ON BONIFIC TO PROCEDURE SP_INS_BONIFIC;

GRANT EXECUTE ON PROCEDURE SET_REPLICANDO TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SET_REPLICANDO TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_VENDA TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_VENDA TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_PEDIDO TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_PEDIDO TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_ITEM_PED TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_ITEM_PED TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_BONIFIC TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_BONIFIC TO ROLE_ADMIN;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SEL_PESSOA (
    ID_REP INTEGER)
RETURNS (
    ID_PES INTEGER,
    RAZ_SOC VARCHAR(60),
    FANTASIA VARCHAR(60),
    E_MAIL VARCHAR(160),
    INATIVO SMALLINT,
    TIPO_PESSOA SMALLINT,
    REG_ENTR VARCHAR(50),
    OPER_TYPE SMALLINT)
AS
DECLARE ID         INTEGER;
DECLARE TABLE_NAME VARCHAR(20) = 'PESSOA';
DECLARE TP_CLIENTE SMALLINT = 0;
BEGIN

  SELECT ID FROM SP_MIN_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;

  FOR SELECT FV_REPLICATE.FIELD_KEY AS ID_PES,
             PESSOA.RAZ_SOC,
             PESSOA.FANTASIA,
             PESSOA.INATIVO,
             PESSOA.E_MAIL_NFE,
             REGIAO_ENTR.DESCRICAO,
             FV_REPLICATE.OPER_TYPE
       FROM  FV_REPLICATE 
       LEFT JOIN PESSOA      ON (PESSOA.ID_PES = FV_REPLICATE.FIELD_KEY)
       LEFT JOIN REGIAO_ENTR ON (PESSOA.ID_REG = REGIAO_ENTR.ID_REG)
       WHERE FV_REPLICATE.ID_REP      = :ID_REP
       AND   FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
       AND   FV_REPLICATE.DATE_REPL   IS NULL
       AND  (FV_REPLICATE.REPLICATING = :ID AND FV_REPLICATE.REPLICATING > 0)
       ORDER BY FV_REPLICATE.ID_ITEM
       INTO :ID_PES,
            :RAZ_SOC,
            :FANTASIA,
            :INATIVO,
            :E_MAIL,
            :REG_ENTR,
            :OPER_TYPE
  DO BEGIN
    TIPO_PESSOA = :TP_CLIENTE;
    SUSPEND;
  END
  EXECUTE PROCEDURE SP_UPDATE_REPLICADO(:ID, :ID_REP, :TABLE_NAME);
END^

CREATE OR ALTER TRIGGER AIUD_PESSOA_REPL_FV FOR PESSOA
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS
DECLARE SINCRON   SMALLINT = 0;
DECLARE OPERAC    SMALLINT;
DECLARE FALSE     SMALLINT = 0;
DECLARE TRUE      SMALLINT = 1;
DECLARE OPER_UPD  SMALLINT = 1;
BEGIN
  IF (UPDATING) THEN BEGIN
    IF ((OLD.ID_PES     <> NEW.ID_PES)     OR
        (OLD.INATIVO    <> NEW.INATIVO)    OR
        (OLD.E_MAIL_NFE <> NEW.E_MAIL_NFE) OR
        (OLD.IBGE       <> NEW.IBGE)       OR
        (OLD.ID_REG     <> NEW.ID_REG)     OR
        (OLD.FANTASIA   <> NEW.FANTASIA)   OR
        (OLD.RAZ_SOC    <> NEW.RAZ_SOC)) THEN BEGIN
      SINCRON = TRUE;
      OPERAC  = OPER_UPD;
    END
  END

  IF (SINCRON = TRUE) THEN BEGIN
    INSERT INTO FV_REPLICATE (ID_REP, TABLE_NAME, FIELD_KEY, OPER_TYPE)
      SELECT PERFIL_CLIENTE_REPRES.ID_REP, 'PESSOA', PERFIL_CLIENTE_REPRES.ID_CLI, :OPERAC
        FROM PERFIL_CLIENTE_REPRES JOIN REPRES ON (PERFIL_CLIENTE_REPRES.ID_REP = REPRES.ID_PES)
         AND PERFIL_CLIENTE_REPRES.ID_CLI = NEW.ID_PES AND REPRES.FORC_VEND = :TRUE;

    INSERT INTO FV_REPLICATE (ID_REP, TABLE_NAME, FIELD_KEY, OPER_TYPE)
      SELECT REPRES.ID_PES, 'FORNECEDOR', PESSOA.ID_PES, :OPERAC
        FROM PESSOA, REPRES
        JOIN FORNEC ON (PESSOA.ID_PES = FORNEC.ID_PES)
         AND REPRES.FORC_VEND = :TRUE AND FORNEC.ID_PES = NEW.ID_PES AND PESSOA.INATIVO = :FALSE;
  END
END^

CREATE OR ALTER PROCEDURE SEL_FORNECEDOR (
    ID_REP INTEGER)
RETURNS (
    ID_PES INTEGER,
    RAZ_SOC VARCHAR(60),
    FANTASIA VARCHAR(60),
    E_MAIL VARCHAR(160),
    INATIVO SMALLINT,
    TIPO_PESSOA SMALLINT,
    OPER_TYPE SMALLINT)
AS
DECLARE TP_FORNECEDOR SMALLINT = 4;
DECLARE ID            INTEGER;
DECLARE TABLE_NAME    VARCHAR(20) = 'FORNECEDOR';
BEGIN

  SELECT ID FROM SP_MIN_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;

  FOR SELECT FV_REPLICATE.FIELD_KEY AS ID_PES,
             PESSOA.RAZ_SOC,
             PESSOA.FANTASIA,
             PESSOA.INATIVO,
             PESSOA.E_MAIL_NFE,
             FV_REPLICATE.OPER_TYPE
       FROM  FV_REPLICATE 
       LEFT JOIN PESSOA      ON (PESSOA.ID_PES = FV_REPLICATE.FIELD_KEY)
       WHERE FV_REPLICATE.ID_REP      = :ID_REP
       AND   FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
       AND   FV_REPLICATE.DATE_REPL   IS NULL
       AND  (FV_REPLICATE.REPLICATING = :ID AND FV_REPLICATE.REPLICATING > 0)
       ORDER BY FV_REPLICATE.ID_ITEM
       INTO :ID_PES,
            :RAZ_SOC,
            :FANTASIA,
            :INATIVO,
            :E_MAIL,
            :OPER_TYPE
  DO BEGIN
    TIPO_PESSOA = :TP_FORNECEDOR;
    SUSPEND;
  END
  EXECUTE PROCEDURE SP_UPDATE_REPLICADO(:ID, :ID_REP, :TABLE_NAME);
END^

CREATE OR ALTER PROCEDURE SEL_PESSOA (
    ID_REP INTEGER,
    SINCR SMALLINT)
RETURNS (
    ID_PES INTEGER,
    RAZ_SOC VARCHAR(60),
    FANTASIA VARCHAR(60),
    E_MAIL VARCHAR(160),
    INATIVO SMALLINT,
    TIPO_PESSOA SMALLINT,
    REG_ENTR VARCHAR(50),
    ENDERECO VARCHAR(300),
    OPER_TYPE SMALLINT)
AS
DECLARE ID         INTEGER;
DECLARE TABLE_NAME VARCHAR(20) = 'PESSOA';
DECLARE TP_CLIENTE SMALLINT = 0;
BEGIN

  SELECT ID FROM SP_MIN_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;

  FOR SELECT FV_REPLICATE.FIELD_KEY AS ID_PES,
             PESSOA.RAZ_SOC,
             PESSOA.FANTASIA,
             PESSOA.INATIVO,
             PESSOA.E_MAIL_NFE,
             PESSOA.ENDERECO || ', ' || PESSOA.NUMERO || ' - ' || PESSOA.BAIRRO || ', ' || CIDADE.DESCRICAO || ' - ' || CIDADE.UF,
             REGIAO_ENTR.DESCRICAO,
             FV_REPLICATE.OPER_TYPE
       FROM  FV_REPLICATE 
       LEFT JOIN PESSOA      ON (PESSOA.ID_PES = FV_REPLICATE.FIELD_KEY)
       LEFT JOIN CIDADE      ON (PESSOA.ID_CID = CIDADE.ID_CID)
       LEFT JOIN REGIAO_ENTR ON (PESSOA.ID_REG = REGIAO_ENTR.ID_REG)
       WHERE FV_REPLICATE.ID_REP      = :ID_REP
       AND   FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
       AND   FV_REPLICATE.DATE_REPL   IS NULL
       AND  (FV_REPLICATE.REPLICATING = :ID AND FV_REPLICATE.REPLICATING > 0)
       ORDER BY FV_REPLICATE.ID_ITEM
       INTO :ID_PES,
            :RAZ_SOC,
            :FANTASIA,
            :INATIVO,
            :E_MAIL,
            :ENDERECO,
            :REG_ENTR,
            :OPER_TYPE
  DO BEGIN
    TIPO_PESSOA = :TP_CLIENTE;
    SUSPEND;
  END
  EXECUTE PROCEDURE SP_UPDATE_REPLICADO(:ID, :ID_REP, :TABLE_NAME);
END^

CREATE OR ALTER PROCEDURE SEL_CONFIG_SYS (
    ID_REP INTEGER)
RETURNS (
    ID_CFG SMALLINT,
    LIB_CAMP_DCT SMALLINT,
    LIB_CAMP_PRC SMALLINT,
    RECALC_ITEM SMALLINT,
    CALC_SUG_NEG SMALLINT,
    PRAZO_MAX_COB SMALLINT,
    PERC_SUG_NEG NUMERIC(10,2),
    APL_SUG_NEG_PED SMALLINT,
    MAX_DIAS_FAT SMALLINT,
    MAX_DIAS_AB SMALLINT,
    AVISO VARCHAR(2000),
    QUANT_PED_AB SMALLINT,
    QUANT_ITEM_PED SMALLINT,
    ORDER_PROD_FV SMALLINT,
    BLOQ_PED_MIN_FV SMALLINT,
    ACCESS_LOCATION_REQUIRED SMALLINT,
    MGR_PRC_MIN_MKT SMALLINT,
    OPER_TYPE SMALLINT)
AS
DECLARE ID INTEGER;
DECLARE TABLE_NAME VARCHAR(20) = 'CONFIG_SYS';
BEGIN
  
  SELECT ID FROM SP_MIN_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;

  FOR SELECT CONFIG_SYS.ID_CFG,
             CONFIG_SYS.LIB_CAMP_DCT_FV,
             CONFIG_SYS.LIB_CAMP_PRC_FV,
             CONFIG_SYS.RECALC_ITEM,
             CONFIG_SYS.FORM_CALC_MKT,
             CONFIG_SYS.PRAZO_MAX_COB,
             CONFIG_SYS.PERC_SUG_NEG,
             CONFIG_SYS.APL_SUG_NEG_PED,
             CONFIG_SYS.MAX_DIAS_FAT_FV,
             CONFIG_SYS.MAX_DIAS_ABE_FV,
             CONFIG_SYS.AVISOS_FV,
             CONFIG_SYS.QUANT_PED_AB,
             CONFIG_SYS.QUANT_ITEM_PED,
             CONFIG_SYS.ORDER_PROD_FV,
             CONFIG_SYS.BLOQ_PED_MIN_FV,
             CONFIG_SYS.ACCESS_LOCATION_REQUIRED,
             CONFIG_SYS.MGR_PRC_MIN_MKT,
             FV_REPLICATE.OPER_TYPE
       FROM  CONFIG_SYS
       JOIN  FV_REPLICATE ON (CONFIG_SYS.ID_CFG = FV_REPLICATE.FIELD_KEY)
       WHERE FV_REPLICATE.ID_REP      = :ID_REP
       AND   FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
       AND   FV_REPLICATE.DATE_REPL   IS NULL
       AND  (FV_REPLICATE.REPLICATING = :ID AND FV_REPLICATE.REPLICATING > 0)
       ORDER BY FV_REPLICATE.ID_ITEM
       INTO :ID_CFG,
            :LIB_CAMP_DCT,
            :LIB_CAMP_PRC,
            :RECALC_ITEM,
            :CALC_SUG_NEG,
            :PRAZO_MAX_COB,
            :PERC_SUG_NEG,
            :APL_SUG_NEG_PED,
            :MAX_DIAS_FAT,
            :MAX_DIAS_AB,
            :AVISO,
            :QUANT_PED_AB,
            :QUANT_ITEM_PED,
            :ORDER_PROD_FV,
            :BLOQ_PED_MIN_FV,
            :ACCESS_LOCATION_REQUIRED,
            :MGR_PRC_MIN_MKT,
            :OPER_TYPE
  DO BEGIN
    SUSPEND;
  END
  EXECUTE PROCEDURE SP_UPDATE_REPLICADO(:ID, :ID_REP, :TABLE_NAME);
END^

CREATE OR ALTER TRIGGER AIUD_CONFIG_SYS_REPL_FV FOR CONFIG_SYS
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 1
AS
DECLARE SINCRON   SMALLINT = 0;
DECLARE OPERAC    SMALLINT;
DECLARE TRUE      SMALLINT = 1;
DECLARE OPER_INS  SMALLINT = 0;
DECLARE OPER_UPD  SMALLINT = 1;
DECLARE OPER_DEL  SMALLINT = 2;
DECLARE ID_CFG    SMALLINT;
BEGIN

  IF ((INSERTING) OR (UPDATING)) THEN
    ID_CFG = NEW.ID_CFG;
  ELSE --DELETING
    ID_CFG = OLD.ID_CFG;

  /* --- */
  IF (INSERTING) THEN BEGIN
    SINCRON = TRUE;
    OPERAC  = OPER_INS;
  END IF (UPDATING) THEN BEGIN
    IF ((OLD.ID_CFG                   <> NEW.ID_CFG)          OR
        (OLD.PRAZO_MAX_COB            <> NEW.PRAZO_MAX_COB)   OR
        (OLD.LIB_CAMP_DCT_FV          <> NEW.LIB_CAMP_DCT_FV) OR
        (OLD.LIB_CAMP_PRC_FV          <> NEW.LIB_CAMP_PRC_FV) OR
        (OLD.RECALC_ITEM              <> NEW.RECALC_ITEM)     OR
        (OLD.AVISOS_FV                <> NEW.AVISOS_FV)       OR
        (OLD.FORM_CALC_MKT            <> NEW.FORM_CALC_MKT)   OR
        (OLD.APL_SUG_NEG_PED          <> NEW.APL_SUG_NEG_PED) OR
        (OLD.MAX_DIAS_FAT_FV          <> NEW.MAX_DIAS_FAT_FV) OR
        (OLD.MAX_DIAS_ABE_FV          <> NEW.MAX_DIAS_ABE_FV) OR
        (OLD.QUANT_PED_AB             <> NEW.QUANT_PED_AB)    OR
        (OLD.ORDER_PROD_FV            <> NEW.ORDER_PROD_FV)   OR
        (OLD.QUANT_ITEM_PED           <> NEW.QUANT_ITEM_PED)  OR
        (OLD.BLOQ_PED_MIN_FV          <> NEW.BLOQ_PED_MIN_FV) OR
        (OLD.MGR_PRC_MIN_MKT          <> NEW.MGR_PRC_MIN_MKT) OR
        (OLD.ACCESS_LOCATION_REQUIRED <> NEW.ACCESS_LOCATION_REQUIRED) OR
        (OLD.PERC_SUG_NEG             <> NEW.PERC_SUG_NEG))   THEN BEGIN
      SINCRON = TRUE;
      OPERAC  = OPER_UPD;
    END
  END ELSE IF (DELETING) THEN BEGIN
    SINCRON = TRUE;
    OPERAC  = OPER_DEL;
  END
  
  IF (SINCRON = TRUE) THEN BEGIN
    INSERT INTO FV_REPLICATE (ID_REP, TABLE_NAME, FIELD_KEY, OPER_TYPE)
      SELECT REPRES.ID_PES, 'CONFIG_SYS', :ID_CFG, :OPERAC
      FROM REPRES WHERE FORC_VEND = :TRUE;
  END
END^

CREATE OR ALTER PROCEDURE SET_REPLICADO (
    ID_REP INTEGER,
    TABLE_NAME VARCHAR(20))
AS
DECLARE TRUE SMALLINT = 1;
DECLARE ID INTEGER;
BEGIN
  SELECT ID FROM SP_MIN_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;

  /*UPDATE FV_REPLICATE SET FV_REPLICATE.DATE_REPL = CURRENT_TIMESTAMP
  WHERE  FV_REPLICATE.ID_REP      = :ID_REP
  AND    FV_REPLICATE.REPLICATED  = :TRUE
  AND    FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
  AND    FV_REPLICATE.DATE_REPL   IS NULL
  AND   (FV_REPLICATE.REPLICATING = :ID AND FV_REPLICATE.REPLICATING > 0); */

  DELETE FROM FV_REPLICATE
  WHERE  FV_REPLICATE.ID_REP      = :ID_REP
  AND    FV_REPLICATE.REPLICATED  = :TRUE
  AND    FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
  AND    FV_REPLICATE.DATE_REPL   IS NULL
  AND   (FV_REPLICATE.REPLICATING = :ID AND FV_REPLICATE.REPLICATING > 0);

END^

ALTER PROCEDURE SET_REPLICANDO (
    ID_REP INTEGER,
    TABLE_NAME VARCHAR(20))
AS
DECLARE ID INTEGER;
BEGIN
  SELECT ID + 1 FROM SP_CURR_ID_REPL(:ID_REP, :TABLE_NAME) INTO :ID;
  UPDATE FV_REPLICATE SET
         FV_REPLICATE.REPLICATING = :ID
  WHERE  FV_REPLICATE.ID_REP      = :ID_REP
  AND    FV_REPLICATE.TABLE_NAME  = :TABLE_NAME
  AND    FV_REPLICATE.REPLICATING = 0
  AND    FV_REPLICATE.REPLICATED  = 0
  AND    FV_REPLICATE.DATE_REPL   IS NULL
  ORDER BY FV_REPLICATE.ID_ITEM;
  --ROWS 1 TO 200;
END^

ALTER TRIGGER BI_FV_REPLICATE_VAL
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE OPER_INS SMALLINT = 0;
DECLARE OPER_UPD SMALLINT = 1;
DECLARE OPER_DEL SMALLINT = 2;
DECLARE ABORTAR  SMALLINT = 2;
DECLARE FALSE    SMALLINT = 0;
BEGIN
  /* SE HOUVER UMA EXCLUSÃO DE REGISTRO, SERÁ NECESSÁRIO ALTERAR O STATUS
  DE TODA INCLUSÃO E ALTERAÇÃO DESSE MESMO REGISTRO QUE AINDA NAO FOI REPLICADO */
  IF (NEW.OPER_TYPE = OPER_DEL) THEN BEGIN

    /* SE EXISTE UM INSERT QUE NÃO FOI REPLICADO, ENTAO ABORTA O INSERT E O DELETE */
    IF (EXISTS(SELECT FV_REPLICATE.ID_ITEM FROM FV_REPLICATE
               WHERE FV_REPLICATE.TABLE_NAME = NEW.TABLE_NAME
               AND FV_REPLICATE.FIELD_KEY    = NEW.FIELD_KEY
               AND FV_REPLICATE.ID_REP       = NEW.ID_REP
               AND FV_REPLICATE.REPLICATED   = :FALSE
               AND FV_REPLICATE.REPLICATING  = :FALSE
               AND FV_REPLICATE.OPER_TYPE    = :OPER_INS)) THEN BEGIN
      UPDATE FV_REPLICATE SET FV_REPLICATE.DATE_REPL = CURRENT_TIMESTAMP
               WHERE FV_REPLICATE.TABLE_NAME = NEW.TABLE_NAME
               AND FV_REPLICATE.FIELD_KEY    = NEW.FIELD_KEY
               AND FV_REPLICATE.ID_REP       = NEW.ID_REP
               AND FV_REPLICATE.REPLICATED   = :FALSE
               AND FV_REPLICATE.REPLICATING  = :FALSE;
      NEW.REPLICATED = :ABORTAR;
      NEW.DATE_REPL = CURRENT_TIMESTAMP;
    END

    /* SE EXISTE UM UPDATE QUE NÃO FOI REPLICADO, ENTAO ABORTA O UPDATE */
    IF (EXISTS(SELECT FV_REPLICATE.ID_ITEM FROM FV_REPLICATE
               WHERE FV_REPLICATE.TABLE_NAME = NEW.TABLE_NAME
               AND FV_REPLICATE.FIELD_KEY    = NEW.FIELD_KEY
               AND FV_REPLICATE.ID_REP       = NEW.ID_REP
               AND FV_REPLICATE.REPLICATED   = :FALSE
               AND FV_REPLICATE.REPLICATING  = :FALSE
               AND FV_REPLICATE.OPER_TYPE    = :OPER_UPD)) THEN BEGIN
      UPDATE FV_REPLICATE SET FV_REPLICATE.DATE_REPL = CURRENT_TIMESTAMP
               WHERE FV_REPLICATE.TABLE_NAME = NEW.TABLE_NAME
               AND FV_REPLICATE.FIELD_KEY    = NEW.FIELD_KEY
               AND FV_REPLICATE.ID_REP       = NEW.ID_REP
               AND FV_REPLICATE.REPLICATED   = :FALSE
               AND FV_REPLICATE.REPLICATING  = :FALSE
               AND FV_REPLICATE.OPER_TYPE    = :OPER_UPD;
      NEW.REPLICATED = :ABORTAR;
      NEW.DATE_REPL = CURRENT_TIMESTAMP;
    END

  END
END^

SET TERM ; ^

ALTER TABLE CONFIG_SYS DROP MNG_PRC_MIN_MKT;

INSERT INTO REPLICATE_TABLE (DESCRICAO, TABLE_NAME, SQL_STATEMENT)
                     VALUES ('FABRICANTE', 'FORNECEDOR', 'INSERT INTO FV_REPLICATE (ID_REP, TABLE_NAME, FIELD_KEY, OPER_TYPE) SELECT :ID_REP, ''FORNECEDOR'', PESSOA.ID_PES, 0 FROM PESSOA, REPRES JOIN FORNEC ON (PESSOA.ID_PES = FORNEC.ID_PES) AND PESSOA.INATIVO = 0;');

GRANT EXECUTE ON PROCEDURE SP_RECENT_PRODUCT_ORDERS TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_RECENT_PRODUCT_ORDERS TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_RECENT_PRODUCT_ORDERS TO REPLICADOR;
GRANT EXECUTE ON PROCEDURE SP_RECENT_PRODUCT_ORDERS TO SYSDBA;
GRANT EXECUTE ON PROCEDURE SP_RECENT_PRODUCT_ORDERS TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_PEDIDO TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_PEDIDO TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_INS_PEDIDO TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_ITEM_PED TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_ITEM_PED TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_INS_ITEM_PED TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_VENDA TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_VENDA TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_INS_VENDA TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SET_REPLICANDO TO SYSDBA;
GRANT EXECUTE ON PROCEDURE SET_REPLICANDO TO MARCELO;
GRANT EXECUTE ON PROCEDURE SET_REPLICANDO TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_INS_BONIFIC TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_INS_BONIFIC TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_INS_BONIFIC TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SEL_PESSOA TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SEL_PESSOA TO MARCELO;
GRANT EXECUTE ON PROCEDURE SEL_PESSOA TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_MIN_ID_REPL TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_MIN_ID_REPL TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_MIN_ID_REPL TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SP_UPDATE_REPLICADO TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SP_UPDATE_REPLICADO TO MARCELO;
GRANT EXECUTE ON PROCEDURE SP_UPDATE_REPLICADO TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SEL_FORNECEDOR TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SEL_FORNECEDOR TO MARCELO;
GRANT EXECUTE ON PROCEDURE SEL_FORNECEDOR TO ROLE_ADMIN;

GRANT EXECUTE ON PROCEDURE SEL_CONFIG_SYS TO FORCA_V;
GRANT EXECUTE ON PROCEDURE SEL_CONFIG_SYS TO MARCELO;
GRANT EXECUTE ON PROCEDURE SEL_CONFIG_SYS TO ROLE_ADMIN;


