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

CREATE PROCEDURE ATUALIZA_ALMOXARIFADO_PDR
   @P_CDFILIAL      VARCHAR(4),
   @P_CDALMOXARIFE  VARCHAR(6)
AS
DECLARE @V_UPDATE_TEXT     NVARCHAR (4000);
DECLARE @V_INSERT_TEXT     NVARCHAR (4000);
DECLARE @V_CDFILIAL_PADRAO VARCHAR (4);


/*VARIAVEIS DO CURSOR DELETA_ALMOX*/
DECLARE @V_DELETA_ALMOX_CDFILIAL     VARCHAR(4); 
DECLARE @V_DELETA_ALMOX_CDALMOXARIFE VARCHAR(6);
/*VARIAVEIS DO CURSOR C_INSALMOX */
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_ALMOX CURSOR FOR 
             SELECT AL.CDFILIAL, AL.CDALMOXARIFE
               FROM ALMOXARIFADO AL, PARAMFILIPADR PF
              WHERE PF.IDTABPARAMFILI = 'S'
                AND PF.NMTABPARAMFILI = 'ALMOXARIFADO'
                AND PF.CDFILIAL = AL.CDFILIAL
                AND PF.CDFILIAL <> @V_CDFILIAL_PADRAO
                AND CDALMOXARIFE = @P_CDALMOXARIFE;
            OPEN C_DELETA_ALMOX;
            FETCH NEXT FROM C_DELETA_ALMOX INTO @V_DELETA_ALMOX_CDFILIAL,
                                                @V_DELETA_ALMOX_CDALMOXARIFE; 
            WHILE (@@FETCH_STATUS = 0) BEGIN

                  DELETE FROM ALMOXARIFADO
                  WHERE CDFILIAL     = @V_DELETA_ALMOX_CDFILIAL
                    AND CDALMOXARIFE = @V_DELETA_ALMOX_CDALMOXARIFE;
	
               FETCH NEXT FROM C_DELETA_ALMOX INTO @V_DELETA_ALMOX_CDFILIAL,
                                                   @V_DELETA_ALMOX_CDALMOXARIFE; 
            END;
            CLOSE C_DELETA_ALMOX;
            DEALLOCATE C_DELETA_ALMOX; 
         END; 
      END
      ELSE BEGIN

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

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

            DECLARE C_CAMPO CURSOR FOR
             SELECT NAME
               FROM SYSCOLUMNS
              WHERE ID = OBJECT_ID('ALMOXARIFADO');
            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;

            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 ALMOXARIFADO AL ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'WHERE AL.CDFILIAL = '+''''+@V_CDFILIAL_PADRAO+''''+' ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND NOT EXISTS (SELECT 1 FROM ALMOXARIFADO AM ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'WHERE AM.CDFILIAL   = '+''''+@V_INSPARAM_CDFILIAL+''''+' ';
            SET @V_INSERT_TEXT = @V_INSERT_TEXT +'AND AL.CDALMOXARIFE = AM.CDALMOXARIFE ))';
            SET @V_UPDATE_TEXT = @V_UPDATE_TEXT + @V_INSERT_TEXT;
            EXECUTE SP_EXECUTESQL @V_UPDATE_TEXT;

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

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