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

CREATE PROCEDURE ATUALIZA_LOCALESTOQ_PDR
   @P_CDFILIAL      VARCHAR(4),
   @P_CDALMOXARIFE  VARCHAR(6),
   @P_CDLOCALESTOQ  VARCHAR(6)

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 DELETA_LOCAL*/
DECLARE @V_DELETA_LOCAL_CDFILIAL     VARCHAR(4); 
DECLARE @V_DELETA_LOCAL_CDALMOXARIFE VARCHAR(6);
DECLARE @V_DELETA_LOCAL_CDLOCALESTOQ VARCHAR(6);
/*VARIAVEIS DO CURSOR C_INSLOCALEST */
DECLARE @V_INSPARAM_CDFILIAL         VARCHAR(4);
DECLARE @V_CAMPO_NAME                SYSNAME

BEGIN
   BEGIN TRANSACTION;
   SELECT @V_CDFILIAL_PADRAO = CDFILIPADRAO
     FROM PARAMGERAL; 
   
   IF @V_CDFILIAL_PADRAO IS NOT NULL BEGIN
      IF (@P_CDALMOXARIFE IS NOT NULL) BEGIN
         IF (@P_CDFILIAL IS NULL)BEGIN

            DECLARE C_DELETA_LOCAL CURSOR FOR 
             SELECT LE.CDFILIAL, LE.CDALMOXARIFE,LE.CDLOCALESTOQ
               FROM LOCALESTOQUE  LE, PARAMFILIPADR PF
              WHERE PF.IDTABPARAMFILI = 'S'
                AND PF.NMTABPARAMFILI = 'LOCALESTOQUE'
                AND PF.CDFILIAL = LE.CDFILIAL
                AND PF.CDFILIAL <> @V_CDFILIAL_PADRAO
                AND LE.CDALMOXARIFE = @P_CDALMOXARIFE
                AND LE.CDLOCALESTOQ = ISNULL(@P_CDLOCALESTOQ, LE.CDLOCALESTOQ);

            OPEN C_DELETA_LOCAL;
            FETCH NEXT FROM C_DELETA_LOCAL INTO @V_DELETA_LOCAL_CDFILIAL,
                                                @V_DELETA_LOCAL_CDALMOXARIFE,
                                                @V_DELETA_LOCAL_CDLOCALESTOQ; 
            WHILE (@@FETCH_STATUS = 0) BEGIN
print 'deleta'
               DELETE FROM LOCALESTOQUE
               WHERE  CDFILIAL     = @V_DELETA_LOCAL_CDFILIAL
                  AND CDALMOXARIFE = @V_DELETA_LOCAL_CDALMOXARIFE
                  AND CDLOCALESTOQ = @V_DELETA_LOCAL_CDLOCALESTOQ;
               FETCH NEXT FROM C_DELETA_LOCAL INTO @V_DELETA_LOCAL_CDFILIAL,
                                                   @V_DELETA_LOCAL_CDALMOXARIFE,
                                                   @V_DELETA_LOCAL_CDLOCALESTOQ; 
            END;
            CLOSE C_DELETA_LOCAL;
            DEALLOCATE C_DELETA_LOCAL; 
         END; 
      END
      ELSE BEGIN

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

         OPEN C_INSLOCALEST;
         FETCH NEXT FROM C_INSLOCALEST INTO @V_INSPARAM_CDFILIAL;
         WHILE (@@FETCH_STATUS = 0) BEGIN
            SET @V_UPDATE_TEXT = 'INSERT INTO LOCALESTOQUE (';
            SET @V_INSERT_TEXT = '(SELECT ' ;

            DECLARE C_CAMPO CURSOR FOR
             SELECT NAME
               FROM SYSCOLUMNS
              WHERE ID = OBJECT_ID('LOCALESTOQUE');
            OPEN C_CAMPO ;
            
            FETCH NEXT FROM C_CAMPO INTO @V_CAMPO_NAME;
            WHILE (@@FETCH_STATUS = 0) BEGIN
               IF NOT (@v_CAMPO_NAME = 'CDFILIAL') BEGIN
                  SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_CAMPO_NAME + ', ';
                  SET @V_INSERT_TEXT = @V_INSERT_TEXT + @v_CAMPO_NAME + ', ';
               END; 
               FETCH NEXT FROM C_CAMPO INTO @V_CAMPO_NAME;
            END;
            CLOSE C_CAMPO;
            DEALLOCATE C_CAMPO;

            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;


            SET @V_UPDATE_TEXT = @V_UPDATE_TEXT +'CDFILIAL) ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +''''+@V_INSPARAM_CDFILIAL+''''+' ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'FROM LOCALESTOQUE LE ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'WHERE CDFILIAL = '+''''+@V_CDFILIAL_PADRAO+''''+' ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND NOT EXISTS (SELECT 1 FROM LOCALESTOQUE LO ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'WHERE LO.CDFILIAL   = '+''''+@V_INSPARAM_CDFILIAL+''''+' ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND LO.CDALMOXARIFE = LE.CDALMOXARIFE ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND LO.CDLOCALESTOQ = LE.CDLOCALESTOQ ) ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND EXISTS (SELECT 1 FROM ALMOXARIFADO AL ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'WHERE CDFILIAL ='+''''+@V_INSPARAM_CDFILIAL+''''+' ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND AL.CDALMOXARIFE = LE.CDALMOXARIFE))';
            SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_INSERT_TEXT;
            EXECUTE SP_EXECUTESQL @V_UPDATE_TEXT ;                         

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

-- VERSAO --
EXEC ATUVEROBJ 'ATUALIZA_LOCALESTOQ_PDR','5.07.006';
GO
