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

CREATE PROCEDURE GERA_ETIQUETA_RETIRADA
   @P_CDFILIAL     VARCHAR(04),
   @P_DTRETIRADA   DATETIME,
   @P_NRLISTA      VARCHAR(10)
AS
   DECLARE @V_NRSEQUENCIAL    FLOAT;
   DECLARE @V_QTCONTAINER     FLOAT;

   DECLARE @V_CDPRODACABADO_1   VARCHAR(10);
   DECLARE @V_CDCONTAINER_1     VARCHAR(5);
   DECLARE @V_QTPRODACACONTAI_1 FLOAT;
   DECLARE @V_CDPRODACABADO_2   VARCHAR(10);
   DECLARE @V_CDCONTAINER_2     VARCHAR(5);
   DECLARE @V_QTPRODACACONTAI_2 FLOAT;
   DECLARE @V_CDPRODACABADO     VARCHAR(10);
   DECLARE @V_CDCONTAINER       VARCHAR(5);
   DECLARE @V_QTPRODACACONTAI   FLOAT;

   /*Cursor C_PRODACABADO*/
   DECLARE @C_PRODACABADO_CDFILIAL        VARCHAR(04);
   DECLARE @C_PRODACABADO_DTRETIRADALANC  DATETIME;
   DECLARE @C_PRODACABADO_CDPRODACABADO   VARCHAR(10);
   DECLARE @C_PRODACABADO_CDFILAGREGSERV  VARCHAR(04);
   DECLARE @C_PRODACABADO_CDSERVFILAGREGA VARCHAR(05);
   DECLARE @C_PRODACABADO_QTPRODSERVCONS  FLOAT;

BEGIN

   DECLARE C_PRODACABADO CURSOR FOR
      (SELECT CF.CDFILIAL, CF.DTRETIRADALANC, CF.CDPRODACABADO,
              CF.CDFILAGREGSERV, CF.CDSERVFILAGREGA, CF.QTPRODSERVCONS
       FROM CONSFILIAGREGA CF, LISTA LI
       WHERE CF.CDFILIAL = @P_CDFILIAL
         AND CF.DTRETIRADALANC = @P_DTRETIRADA
         AND LI.NRLISTA = @P_NRLISTA
         AND SUBSTRING(CDLISTA, 1, 4) = CF.CDFILAGREGSERV
         AND SUBSTRING(CDLISTA, 5, 5) = CF.CDSERVFILAGREGA)
   OPEN C_PRODACABADO;

   FETCH NEXT FROM C_PRODACABADO INTO @C_PRODACABADO_CDFILIAL, @C_PRODACABADO_DTRETIRADALANC,
                                      @C_PRODACABADO_CDPRODACABADO, @C_PRODACABADO_CDFILAGREGSERV,
                                      @C_PRODACABADO_CDSERVFILAGREGA, @C_PRODACABADO_QTPRODSERVCONS;
   WHILE (@@FETCH_STATUS = 0) BEGIN

       SELECT @V_CDCONTAINER = MAX(PC.CDCONTAINER)
       FROM PRODACABAPRATO PP, PROACACONTAINER PC
       WHERE PP.CDFILIAL = @P_CDFILIAL
         AND PP.DTRETIRADALANC = @P_DTRETIRADA
         AND PP.CDPRODACABADO = @C_PRODACABADO_CDPRODACABADO
         AND PC.CDFILIAL = PP.CDFILIAL
         AND PC.DTRETIRADALANC = PP.DTRETIRADALANC
         AND PC.CDPRODACABADO = PP.CDPRODACABADO;

       IF (@V_CDCONTAINER IS NOT NULL) BEGIN

           DELETE FROM ETIQPRODACABADO
           WHERE CDFILIAL = @P_CDFILIAL
             AND DTRETIRADALANC = @P_DTRETIRADA
             AND CDPRODACABADO = @C_PRODACABADO_CDPRODACABADO
             AND CDFILAGREGSERV = @C_PRODACABADO_CDFILAGREGSERV
             AND CDSERVFILAGREGA = @C_PRODACABADO_CDSERVFILAGREGA;
                     
           SET @V_NRSEQUENCIAL = 1;
           SET @V_QTCONTAINER = @C_PRODACABADO_QTPRODSERVCONS;

           WHILE (@V_QTCONTAINER > 0) BEGIN

              SELECT @V_CDPRODACABADO_1 = MAX(PP.CDPRODACABADO),
                     @V_CDCONTAINER_1 = MAX(PC.CDCONTAINER),
                     @V_QTPRODACACONTAI_1 = MAX(PC.QTPRODACACONTAI)
              FROM PRODACABAPRATO PP, PROACACONTAINER PC
              WHERE PP.CDFILIAL = @P_CDFILIAL
                AND PP.DTRETIRADALANC = @P_DTRETIRADA
                AND PP.CDPRODACABADO = @C_PRODACABADO_CDPRODACABADO
                AND PC.CDFILIAL = PP.CDFILIAL
                AND PC.DTRETIRADALANC = PP.DTRETIRADALANC
                AND PC.CDPRODACABADO = PP.CDPRODACABADO
                AND PC.QTPRODACACONTAI > 0
                AND PC.QTPRODACACONTAI = (SELECT MIN(P.QTPRODACACONTAI)
                                          FROM PROACACONTAINER P
                                          WHERE P.CDFILIAL = PP.CDFILIAL
                                            AND P.DTRETIRADALANC = PP.DTRETIRADALANC
                                            AND P.CDPRODACABADO = PP.CDPRODACABADO
                                            AND P.QTPRODACACONTAI >= @V_QTCONTAINER);

              SELECT @V_CDPRODACABADO_2 = MAX(PP.CDPRODACABADO),
                     @V_CDCONTAINER_2 = MAX(PC.CDCONTAINER),
                     @V_QTPRODACACONTAI_2 = MAX(PC.QTPRODACACONTAI)
              FROM PRODACABAPRATO PP, PROACACONTAINER PC
              WHERE PP.CDFILIAL = @P_CDFILIAL
                AND PP.DTRETIRADALANC = @P_DTRETIRADA
                AND PP.CDPRODACABADO = @C_PRODACABADO_CDPRODACABADO
                AND PC.CDFILIAL = PP.CDFILIAL
                AND PC.DTRETIRADALANC = PP.DTRETIRADALANC
                AND PC.CDPRODACABADO = PP.CDPRODACABADO
                AND PC.QTPRODACACONTAI > 0
                AND PC.QTPRODACACONTAI = (SELECT MAX(P.QTPRODACACONTAI)
                                          FROM PROACACONTAINER P
                                          WHERE P.CDFILIAL = PP.CDFILIAL
                                            AND P.DTRETIRADALANC = PP.DTRETIRADALANC
                                            AND P.CDPRODACABADO = PP.CDPRODACABADO
                                            AND P.QTPRODACACONTAI < @V_QTCONTAINER);

              IF @V_CDCONTAINER_1 IS NULL BEGIN
                 SET @V_CDPRODACABADO = @V_CDPRODACABADO_2;
                 SET @V_CDCONTAINER = @V_CDCONTAINER_2;
                 SET @V_QTPRODACACONTAI = @V_QTPRODACACONTAI_2;
              END
              ELSE BEGIN
                 IF @V_CDCONTAINER_2 IS NULL BEGIN
                    SET @V_CDPRODACABADO = @V_CDPRODACABADO_1;
                    SET @V_CDCONTAINER = @V_CDCONTAINER_1;
                    SET @V_QTPRODACACONTAI = @V_QTPRODACACONTAI_1;
                 END
                 ELSE BEGIN
                    IF (@V_QTCONTAINER - @V_CDCONTAINER_1) > (@V_CDCONTAINER_2 - @V_QTCONTAINER) BEGIN
                       SET @V_CDPRODACABADO = @V_CDPRODACABADO_1;
                       SET @V_CDCONTAINER = @V_CDCONTAINER_1;
                       SET @V_QTPRODACACONTAI = @V_QTPRODACACONTAI_1;
                    END
                    ELSE BEGIN
                       SET @V_CDPRODACABADO = @V_CDPRODACABADO_2;
                       SET @V_CDCONTAINER = @V_CDCONTAINER_2;
                       SET @V_QTPRODACACONTAI = @V_QTPRODACACONTAI_2;
                    END;
                 END;
              END;

              IF (@V_QTCONTAINER < @V_QTPRODACACONTAI) BEGIN
                 INSERT INTO ETIQPRODACABADO
                 (CDFILIAL, DTRETIRADALANC, CDPRODACABADO, CDFILAGREGSERV,
                  CDSERVFILAGREGA, NRSEQETIQPRACAB, CDCONTAINER, QTPRODACAETIQ)
                 VALUES
                 (@P_CDFILIAL, @P_DTRETIRADA, @C_PRODACABADO_CDPRODACABADO, @C_PRODACABADO_CDFILAGREGSERV,
                 @C_PRODACABADO_CDSERVFILAGREGA, SUBSTRING(CONVERT(CHAR(4), @V_NRSEQUENCIAL), 2, 4), @V_CDCONTAINER, @V_QTCONTAINER);

                 SET @V_QTCONTAINER = 0;
              END
              ELSE BEGIN
                 INSERT INTO ETIQPRODACABADO
                 (CDFILIAL, DTRETIRADALANC, CDPRODACABADO, CDFILAGREGSERV,
                  CDSERVFILAGREGA, NRSEQETIQPRACAB, CDCONTAINER, QTPRODACAETIQ)
                 VALUES
                 (@P_CDFILIAL, @P_DTRETIRADA, @C_PRODACABADO_CDPRODACABADO, @C_PRODACABADO_CDFILAGREGSERV,
                 @C_PRODACABADO_CDSERVFILAGREGA, SUBSTRING(CONVERT(CHAR(4), @V_NRSEQUENCIAL), 2, 4), @V_CDCONTAINER, @V_QTPRODACACONTAI);

                 SET @V_QTCONTAINER = @V_QTCONTAINER - @V_QTPRODACACONTAI;
              END;

              SET @V_NRSEQUENCIAL = @V_NRSEQUENCIAL + 1;

           END;

       END;

       FETCH NEXT FROM C_PRODACABADO INTO @C_PRODACABADO_CDFILIAL, @C_PRODACABADO_DTRETIRADALANC,
                                          @C_PRODACABADO_CDPRODACABADO, @C_PRODACABADO_CDFILAGREGSERV,
                                          @C_PRODACABADO_CDSERVFILAGREGA, @C_PRODACABADO_QTPRODSERVCONS;

   END;
   CLOSE C_PRODACABADO;
   DEALLOCATE C_PRODACABADO;

END;
GO

