
IF EXISTS(SELECT
             NAME
          FROM
             SYSOBJECTS
          WHERE
             NAME = 'ATUALIZA_DADOS_MAOOBRA' AND
             TYPE = 'TR')


  DROP TRIGGER ATUALIZA_DADOS_MAOOBRA
  GO


CREATE TRIGGER ATUALIZA_DADOS_MAOOBRA
ON LANCDESPFATU
FOR UPDATE, INSERT, DELETE
AS

   DECLARE @V_DTINICIAL       DATETIME;
   DECLARE @V_DTFINAL         DATETIME;
   DECLARE @V_IDFECHDIAROP    VARCHAR(1);
   DECLARE @V_NEW_CDFILIAL    VARCHAR(4);
   DECLARE @V_OLD_CDFILIAL    VARCHAR(4);
   DECLARE @V_NEW_DTLANCDESFAT  DATETIME;
   DECLARE @V_OLD_DTLANCDESFAT  DATETIME;
   DECLARE @V_ID_COMANDO      VARCHAR(1);
   DECLARE @V_IDUTPADRROP     VARCHAR(1);
   DECLARE @V_IDUTPADRROPFIL  VARCHAR(1);
   DECLARE @V_NEW_VRLANCDESFAT    FLOAT;
   DECLARE @V_NEW_VRTXLANCDESP    FLOAT;
   DECLARE @V_NEW_VRPREVDESFAT    FLOAT;
   DECLARE @V_OLD_VRLANCDESFAT    FLOAT;
   DECLARE @V_OLD_VRTXLANCDESP    FLOAT;
   DECLARE @V_OLD_VRPREVDESFAT    FLOAT;

BEGIN
   SET @V_ID_COMANDO = 'N';

   DECLARE C_CURSOR_DATA CURSOR FOR
      SELECT
        'U' IDCOMANDO,
        DELETED.CDFILIAL,
        DELETED.DTLANCDESFAT,
        INSERTED.CDFILIAL,
        INSERTED.DTLANCDESFAT,
        INSERTED.VRPREVDESFAT,
        INSERTED.VRTXLANCDESP,
        INSERTED.VRPREVDESFAT,
        DELETED.VRPREVDESFAT,
        DELETED.VRTXLANCDESP,
        DELETED.VRPREVDESFAT
      FROM
          DELETED,
          INSERTED
      WHERE
         INSERTED.CDFILIAL  = DELETED.CDFILIAL

      UNION ALL

      SELECT
        'I' IDOCMANDO,
        NULL, NULL,
        INSERTED.CDFILIAL,
        INSERTED.DTLANCDESFAT,
        INSERTED.VRPREVDESFAT,
        INSERTED.VRTXLANCDESP,
        INSERTED.VRPREVDESFAT,
        NULL, NULL, NULL
      FROM
        INSERTED
      WHERE
        0 = (SELECT COUNT(*) FROM DELETED)

      UNION ALL

      SELECT
        'D' IDCOMANDO,
        DELETED.CDFILIAL,
        DELETED.DTLANCDESFAT,
        NULL, NULL, NULL, NULL, NULL,
        DELETED.VRPREVDESFAT,
        DELETED.VRTXLANCDESP,
        DELETED.VRPREVDESFAT
      FROM
        DELETED
      WHERE
        0 = (SELECT COUNT(*) FROM INSERTED);

   OPEN C_CURSOR_DATA;
   FETCH NEXT FROM C_CURSOR_DATA INTO @V_ID_COMANDO,
                                      @V_OLD_CDFILIAL,
                                      @V_OLD_DTLANCDESFAT,
                                      @V_NEW_CDFILIAL,
                                      @V_NEW_DTLANCDESFAT,
                                      @V_NEW_VRLANCDESFAT,
                                      @V_NEW_VRTXLANCDESP,
                                      @V_NEW_VRPREVDESFAT,
                                      @V_OLD_VRLANCDESFAT,
                                      @V_OLD_VRTXLANCDESP,
                                      @V_OLD_VRPREVDESFAT;

   WHILE (@@FETCH_STATUS = 0) BEGIN
      IF @V_ID_COMANDO = 'U' BEGIN
         SELECT @V_IDUTPADRROP = IDUTPADRROP
         FROM PARAMGERAL G;

         SELECT @V_IDFECHDIAROP   = ISNULL(MAX(IDFECHDIAROP),'N'),
                @V_IDUTPADRROPFIL = ISNULL(MAX(IDUTPADRROPFIL),'N')
         FROM  PARFILIALROP
         WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);

         IF @V_IDUTPADRROP = 'S' OR @V_IDUTPADRROPFIL = 'S' BEGIN
            SELECT @V_IDFECHDIAROP   = ISNULL(MAX(IDFECHDIAROP),'N'),
                   @V_IDUTPADRROPFIL = ISNULL(MAX(IDUTPADRROPFIL),'N')
            FROM  PARFILIALROP
            WHERE CDFILIAL = 'P';
         END;

         IF (@V_NEW_DTLANCDESFAT <> @V_OLD_DTLANCDESFAT) OR
            (@V_NEW_VRLANCDESFAT <> @V_OLD_VRLANCDESFAT) OR
            (@V_NEW_VRTXLANCDESP <> @V_OLD_VRTXLANCDESP) OR
            (@V_NEW_VRPREVDESFAT <> @V_NEW_VRPREVDESFAT) BEGIN
            SELECT @V_DTINICIAL = MAX(DTINIAPURACROP),
                   @V_DTFINAL   = MAX(DTFINAPURACROP)
            FROM APURACAOROP
            WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);

            IF (ISNULL(@V_IDFECHDIAROP,'N') <> 'S') BEGIN

               IF (@V_NEW_DTLANCDESFAT < @V_DTFINAL)  AND (@V_NEW_DTLANCDESFAT < @V_DTINICIAL) BEGIN

                  UPDATE APURACAOROP
                  SET DTINIAPURACROP  = @V_NEW_DTLANCDESFAT
                  WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);
               END;

            END
            ELSE BEGIN
               DELETE ROPFILIAL
               WHERE CDFILIAL     = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL)  AND
                     DTFILIALROP  >= @V_NEW_DTLANCDESFAT;

               IF (@V_NEW_DTLANCDESFAT < @V_DTINICIAL) BEGIN
                  UPDATE APURACAOROP
                  SET  DTINIAPURACROP  = @V_NEW_DTLANCDESFAT
                  WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);
               END;

            END;
         END;
      END;

      IF @V_ID_COMANDO = 'I' BEGIN
         SELECT @V_IDUTPADRROP = IDUTPADRROP
         FROM PARAMGERAL G;

         SELECT @V_IDFECHDIAROP   = ISNULL(MAX(IDFECHDIAROP),'N'),
                @V_IDUTPADRROPFIL = ISNULL(MAX(IDUTPADRROPFIL),'N')
         FROM  PARFILIALROP
         WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);

         IF @V_IDUTPADRROP = 'S' OR @V_IDUTPADRROPFIL = 'S' BEGIN
            SELECT @V_IDFECHDIAROP   = ISNULL(MAX(IDFECHDIAROP),'N'),
                   @V_IDUTPADRROPFIL = ISNULL(MAX(IDUTPADRROPFIL),'N')
            FROM  PARFILIALROP
            WHERE CDFILIAL = 'P';
         END;

         SELECT @V_DTINICIAL = MAX(DTINIAPURACROP),
                   @V_DTFINAL   = MAX(DTFINAPURACROP)
            FROM APURACAOROP
            WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);

            IF (ISNULL(@V_IDFECHDIAROP,'N') <> 'S') BEGIN

               IF (@V_NEW_DTLANCDESFAT < @V_DTFINAL)  AND (@V_NEW_DTLANCDESFAT < @V_DTINICIAL) BEGIN

                  UPDATE APURACAOROP
                  SET DTINIAPURACROP  = @V_NEW_DTLANCDESFAT
                  WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);
               END;

            END
            ELSE BEGIN
               DELETE ROPFILIAL
               WHERE CDFILIAL     = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL)  AND
                     DTFILIALROP  >= @V_NEW_DTLANCDESFAT;

               IF (@V_NEW_DTLANCDESFAT < @V_DTINICIAL) BEGIN
                  UPDATE APURACAOROP
                  SET  DTINIAPURACROP  = @V_NEW_DTLANCDESFAT
                  WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);
               END;
         END;
      END;

      IF @V_ID_COMANDO = 'D' BEGIN
         SELECT @V_IDUTPADRROP = IDUTPADRROP
         FROM PARAMGERAL G;

         SELECT @V_IDFECHDIAROP   = ISNULL(MAX(IDFECHDIAROP),'N'),
                @V_IDUTPADRROPFIL = ISNULL(MAX(IDUTPADRROPFIL),'N')
         FROM  PARFILIALROP
         WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);

         IF @V_IDUTPADRROP = 'S' OR @V_IDUTPADRROPFIL = 'S' BEGIN
            SELECT @V_IDFECHDIAROP   = ISNULL(MAX(IDFECHDIAROP),'N'),
                   @V_IDUTPADRROPFIL = ISNULL(MAX(IDUTPADRROPFIL),'N')
            FROM  PARFILIALROP
            WHERE CDFILIAL = 'P';
         END;

         SELECT @V_DTINICIAL = MAX(DTINIAPURACROP),
                   @V_DTFINAL   = MAX(DTFINAPURACROP)
            FROM APURACAOROP
            WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);

            IF (ISNULL(@V_IDFECHDIAROP,'N') <> 'S') BEGIN

               IF (@V_OLD_DTLANCDESFAT < @V_DTFINAL)  AND (@V_OLD_DTLANCDESFAT < @V_DTINICIAL) BEGIN

                  UPDATE APURACAOROP
                  SET DTINIAPURACROP  = @V_OLD_DTLANCDESFAT
                  WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);
               END;

            END
            ELSE BEGIN
               DELETE ROPFILIAL
               WHERE CDFILIAL     = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL)  AND
                     DTFILIALROP  >= @V_OLD_DTLANCDESFAT;

               IF (@V_OLD_DTLANCDESFAT < @V_DTINICIAL) BEGIN
                  UPDATE APURACAOROP
                  SET  DTINIAPURACROP  = @V_OLD_DTLANCDESFAT
                  WHERE CDFILIAL = ISNULL(@V_NEW_CDFILIAL, @V_OLD_CDFILIAL);
               END;
         END;
      END;

      FETCH NEXT FROM C_CURSOR_DATA INTO @V_ID_COMANDO,
                                      @V_OLD_CDFILIAL,
                                      @V_OLD_DTLANCDESFAT,
                                      @V_NEW_CDFILIAL,
                                      @V_NEW_DTLANCDESFAT,
                                      @V_NEW_VRLANCDESFAT,
                                      @V_NEW_VRTXLANCDESP,
                                      @V_NEW_VRPREVDESFAT,
                                      @V_OLD_VRLANCDESFAT,
                                      @V_OLD_VRTXLANCDESP,
                                      @V_OLD_VRPREVDESFAT;

   END;
   CLOSE C_CURSOR_DATA;
   DEALLOCATE C_CURSOR_DATA;
END;

GO

-- VERSAO --
EXEC ATUVEROBJ 'ATUALIZA_DADOS_MAOOBRA','5.11.000';
GO
