create or replace
PROCEDURE GERA_ETIQUETA_RETIRADA(
   P_CDFILIAL     IN CARDIAPME.CDFILIAL%TYPE,
   P_DTRETIRADA   IN RETIRADA.DTRETIRADA%TYPE,
   P_NRLISTA      IN LISTA.NRLISTA%TYPE
) AS
   V_NRSEQUENCIAL    NUMBER;
   V_QTCONTAINER     NUMBER;
   
   V_CDPRODACABADO_1   VARCHAR2(10);
   V_CDCONTAINER_1     VARCHAR2(5);
   V_QTPRODACACONTAI_1 NUMBER;
   V_CDPRODACABADO_2   VARCHAR2(10);
   V_CDCONTAINER_2     VARCHAR2(5);
   V_QTPRODACACONTAI_2 NUMBER;
   V_CDPRODACABADO     VARCHAR2(10);
   V_CDCONTAINER       VARCHAR2(5);
   V_QTPRODACACONTAI   NUMBER;
BEGIN

   FOR C_PRODACABADO IN
      (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 SUBSTR(CDLISTA, 1, 4) = CF.CDFILAGREGSERV
         AND SUBSTR(CDLISTA, 5, 5) = CF.CDSERVFILAGREGA) LOOP                  
          
       SELECT MAX(PC.CDCONTAINER)
       INTO V_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) THEN
            
           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;
                     
           V_NRSEQUENCIAL := 1;
           V_QTCONTAINER := C_PRODACABADO.QTPRODSERVCONS;
           
           WHILE V_QTCONTAINER > 0
           LOOP
           
              SELECT MAX(PP.CDPRODACABADO), MAX(PC.CDCONTAINER), MAX(PC.QTPRODACACONTAI)
              INTO V_CDPRODACABADO_1, V_CDCONTAINER_1, V_QTPRODACACONTAI_1
              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 MAX(PP.CDPRODACABADO), MAX(PC.CDCONTAINER), MAX(PC.QTPRODACACONTAI)
              INTO V_CDPRODACABADO_2, V_CDCONTAINER_2, V_QTPRODACACONTAI_2
              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 THEN
                 V_CDPRODACABADO := V_CDPRODACABADO_2; 
                 V_CDCONTAINER := V_CDCONTAINER_2;
                 V_QTPRODACACONTAI := V_QTPRODACACONTAI_2;
              ELSE
                 IF V_CDCONTAINER_2 IS NULL THEN
                    V_CDPRODACABADO := V_CDPRODACABADO_1; 
                    V_CDCONTAINER := V_CDCONTAINER_1;
                    V_QTPRODACACONTAI := V_QTPRODACACONTAI_1;
                 ELSE
                    IF (V_QTCONTAINER - V_CDCONTAINER_1) > (V_CDCONTAINER_2 - V_QTCONTAINER) THEN
                       V_CDPRODACABADO := V_CDPRODACABADO_1; 
                       V_CDCONTAINER := V_CDCONTAINER_1;
                       V_QTPRODACACONTAI := V_QTPRODACACONTAI_1;
                    ELSE
                       V_CDPRODACABADO := V_CDPRODACABADO_2; 
                       V_CDCONTAINER := V_CDCONTAINER_2;
                       V_QTPRODACACONTAI := V_QTPRODACACONTAI_2;
                    END IF;
                 END IF;
              END IF;   
              
              IF (V_QTCONTAINER < V_QTPRODACACONTAI) THEN
                 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, SUBSTR(TO_CHAR(V_NRSEQUENCIAL, '0000'), 2, 4), V_CDCONTAINER, V_QTCONTAINER);
                 
                 V_QTCONTAINER := 0;
              ELSE
                 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, SUBSTR(TO_CHAR(V_NRSEQUENCIAL, '0000'), 2, 4), V_CDCONTAINER, V_QTPRODACACONTAI); 
                 
                 V_QTCONTAINER := V_QTCONTAINER - V_QTPRODACACONTAI;
              END IF;
              
              V_NRSEQUENCIAL := V_NRSEQUENCIAL + 1;         
        
           END LOOP;
       
       END IF;
        
   END LOOP;

END;
/