IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE
              O.NAME = 'ATUALIZA_PARAMFILIAL_PDR' AND
              TYPE = 'P'                  AND
              U.UID = O.UID               AND
              U.NAME = USER)
   DROP PROCEDURE ATUALIZA_PARAMFILIAL_PDR;
GO

CREATE PROCEDURE ATUALIZA_PARAMFILIAL_PDR
   @P_CDFILIAL      VARCHAR(4)

AS
DECLARE @V_UPDATE_TEXT     NVARCHAR (4000);
DECLARE @V_INSERT_TEXT     NVARCHAR (4000);
DECLARE @V_CDFILIAL_PADRAO VARCHAR (4);

DECLARE @V_CDFILIAL_ATUAL_ALMOX VARCHAR (4);

/*VARIAVEIS DO CURSOR C_INSPARAMFILIAL */
DECLARE @V_INSPARAM_CDFILIAL  VARCHAR(4);
DECLARE @V_CAMPO_NAME         SYSNAME;
DECLARE @V_DTPROCESSA         DATETIME;       
DECLARE @V_DSPARAMFATFI       VARCHAR(200);
DECLARE @V_ST_DTPROCESSA      VARCHAR(20);

DECLARE @V_CDALMOX_PADR       VARCHAR(6);
DECLARE @V_CDLOCALEST_PADR    VARCHAR(6);

DECLARE @V_CDFILIAL_ATUAL_LOCALEST VARCHAR(4);
DECLARE @V_CDSERVBAIESTCEG         VARCHAR(10);
DECLARE @V_CDSERVFILIAL            VARCHAR(10);

BEGIN
   BEGIN TRANSACTION;
   SELECT @V_CDFILIAL_PADRAO = CDFILIPADRAO
     FROM PARAMGERAL;

   IF @V_CDFILIAL_PADRAO IS NOT NULL BEGIN

      SELECT @V_CDSERVBAIESTCEG = MAX(CDSERVBAIESTCEG)
      FROM PARAMFILIAL
      WHERE CDFILIAL = @V_CDFILIAL_PADRAO;

      DECLARE C_INSPARAMFILIAL CURSOR FOR
       SELECT CDFILIAL
         FROM PARAMFILIPADR
        WHERE IDTABPARAMFILI = 'S'
          AND NMTABPARAMFILI = 'PARAMFILIAL'
          AND CDFILIAL <> @V_CDFILIAL_PADRAO
          AND CDFILIAL = ISNULL(@P_CDFILIAL, CDFILIAL);

      OPEN C_INSPARAMFILIAL;
      FETCH NEXT FROM C_INSPARAMFILIAL INTO @V_INSPARAM_CDFILIAL;
      WHILE (@@FETCH_STATUS = 0) BEGIN

         SELECT @V_CDSERVFILIAL = MAX(CDSERVFILI)
         FROM SERVICO
         WHERE CDFILIAL = @V_INSPARAM_CDFILIAL
           AND CDSERVFILI = @V_CDSERVBAIESTCEG;

         SET @V_UPDATE_TEXT = 'INSERT INTO PARAMFILIAL (';
         SET @V_INSERT_TEXT = '(SELECT ' ;

         DECLARE C_CAMPO CURSOR FOR
          SELECT NAME
            FROM SYSCOLUMNS
           WHERE ID = OBJECT_ID('PARAMFILIAL');
          
           OPEN C_CAMPO ;            
           FETCH NEXT FROM C_CAMPO INTO @V_CAMPO_NAME;

         WHILE (@@FETCH_STATUS = 0) BEGIN
            IF NOT (@V_CAMPO_NAME IN ('DTPROCESSA', 'DSPARAMFATFI',
                                          'CDFILIAL', 'CDALMOXARIFE', 'CDLOCALESTOQ')) BEGIN
               IF @V_CDSERVBAIESTCEG IS NULL BEGIN
                  SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_CAMPO_NAME + ',';
                  SET @V_INSERT_TEXT = @V_INSERT_TEXT + @v_CAMPO_NAME + ',';
               END
               ELSE BEGIN
                  IF NOT (@V_CAMPO_NAME IN ('CDSERVBAIESTCEG')) BEGIN
                     SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_CAMPO_NAME + ',';
                     SET @V_INSERT_TEXT = @V_INSERT_TEXT + @v_CAMPO_NAME + ',';
                  END
                  ELSE  BEGIN
                     IF NOT @V_CDSERVFILIAL IS NULL BEGIN
                        SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_CAMPO_NAME + ',';
                        SET @V_INSERT_TEXT = @V_INSERT_TEXT + @V_CAMPO_NAME + ',';
                     END;
                  END;
               END;
            END;

            FETCH NEXT FROM C_CAMPO INTO @V_CAMPO_NAME;
         END;

         CLOSE C_CAMPO;
         DEALLOCATE C_CAMPO;

         SELECT @V_DTPROCESSA   =  DTPROCESSA,
                @V_DSPARAMFATFI = DSPARAMFATFI
           FROM PARAMFILIAL
          WHERE CDFILIAL = @V_INSPARAM_CDFILIAL;

          SELECT @V_CDALMOX_PADR = PF.CDALMOXARIFE
            FROM PARAMFILIAL PF, ALMOXARIFADO AL
           WHERE PF.CDFILIAL     = @V_CDFILIAL_PADRAO
             AND AL.CDFILIAL     = @V_INSPARAM_CDFILIAL
             AND PF.CDALMOXARIFE = AL.CDALMOXARIFE ;


         IF @V_CDALMOX_PADR IS NULL BEGIN
            SELECT @V_CDFILIAL_ATUAL_ALMOX = CDFILIAL
              FROM PARAMFILIPADR
             WHERE IDTABPARAMFILI = 'S'
               AND NMTABPARAMFILI = 'ALMOXARIFADO'
               AND CDFILIAL = @V_INSPARAM_CDFILIAL;

            IF (@V_CDFILIAL_ATUAL_ALMOX IS NOT NULL) BEGIN
               EXEC TEKNISA.ATUALIZA_ALMOXARIFADO_PDR @V_INSPARAM_CDFILIAL, NULL;
            END;
         END;

         SELECT @V_CDLOCALEST_PADR = PF.CDLOCALESTOQ
           FROM PARAMFILIAL PF, LOCALESTOQUE LE
          WHERE PF.CDFILIAL     = @V_CDFILIAL_PADRAO
            AND LE.CDFILIAL     = @V_INSPARAM_CDFILIAL
            AND PF.CDALMOXARIFE = LE.CDALMOXARIFE
            AND PF.CDLOCALESTOQ = LE.CDLOCALESTOQ;

         IF @V_CDLOCALEST_PADR IS NULL BEGIN
            SELECT @V_CDFILIAL_ATUAL_LOCALEST = CDFILIAL
              FROM PARAMFILIPADR
             WHERE IDTABPARAMFILI = 'S'
               AND NMTABPARAMFILI = 'LOCALESTOQUE'
               AND CDFILIAL = @V_INSPARAM_CDFILIAL;

            IF (@V_CDFILIAL_ATUAL_LOCALEST IS NOT NULL) BEGIN
               EXEC TEKNISA.ATUALIZA_LOCALESTOQ_PDR @V_INSPARAM_CDFILIAL, NULL, NULL ;
            END;

         END;

         DELETE FROM PARAMFILIAL
          WHERE CDFILIAL = @V_INSPARAM_CDFILIAL;

         SET @V_UPDATE_TEXT = @V_UPDATE_TEXT+'CDFILIAL, DTPROCESSA, CDALMOXARIFE, CDLOCALESTOQ, DSPARAMFATFI) ';
         SET @V_INSERT_TEXT = @V_INSERT_TEXT+''''+@V_INSPARAM_CDFILIAL+''''+',';

         SET @V_ST_DTPROCESSA = CONVERT(VARCHAR,@V_DTPROCESSA,103);

         IF @V_DTPROCESSA IS NULL BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+'NULL, ';
         END
         ELSE  BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+''''+@V_ST_DTPROCESSA+''''+',';
         END;

         IF (@V_CDALMOX_PADR IS NULL) AND
            (@V_CDFILIAL_ATUAL_ALMOX IS NULL) BEGIN
             SET @V_INSERT_TEXT = @V_INSERT_TEXT+'NULL, ';
         END
         ELSE BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+'CDALMOXARIFE, ';
         END;

         IF (@V_CDLOCALEST_PADR IS NULL) AND
            (@V_CDFILIAL_ATUAL_LOCALEST IS NULL) BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+'NULL, ';
         END
         ELSE BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+'CDLOCALESTOQ, ';
         END;
         
         IF @V_DSPARAMFATFI IS NULL BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+'NULL ';
         END
         ELSE BEGIN
            SET @V_INSERT_TEXT = @V_INSERT_TEXT+''''+@V_DSPARAMFATFI+''''+' ';
         END;

         SET @V_INSERT_TEXT = @V_INSERT_TEXT +'FROM PARAMFILIAL ';
         SET @V_INSERT_TEXT = @V_INSERT_TEXT +'WHERE CDFILIAL = '+''''+@V_CDFILIAL_PADRAO+''''+') ';
         SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_INSERT_TEXT;
         EXECUTE SP_EXECUTESQL @V_UPDATE_TEXT;                            

         FETCH NEXT FROM C_INSPARAMFILIAL INTO @V_INSPARAM_CDFILIAL;
      END;
      CLOSE C_INSPARAMFILIAL;
      DEALLOCATE C_INSPARAMFILIAL; 
   END;      
   COMMIT TRANSACTION;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'ATUALIZA_PARAMFILIAL_PDR','5.07.012';
GO
