create or replace PROCEDURE CALCULA_INSUMOS_PESAGEM(P_CDFILIAL     IN ORDPRODUCAO.CDFILIAL%TYPE,
                                                    P_NRORDPROD    IN ORDPRODUCAO.NRORDPROD%TYPE,
                                                    P_NRSUBORDPROD IN ORDPRODUCAO.NRSUBORDPROD%TYPE)

IS
V_COUNT            INTEGER;
V_NRRETEST         VARCHAR2(20);
V_CDSETOR          VARCHAR2(04);
V_CDCENTCUST       VARCHAR2(04);
V_NRSEQRETPROD     INTEGER; 
V_CDTIPORETI       VARCHAR2(2); 
V_CDPRODUTO        VARCHAR2(10);
V_DSFASE           VARCHAR2(50);
V_CDSETORAUX       VARCHAR2(04);
V_CDCENTCUSTAUX    VARCHAR2(04);
V_QTPRERETPROD     FLOAT;
V_QTPREVI_MATPRIMA FLOAT;

BEGIN
   /*DELETA OS REGISTROS QUE AINDA NAO FORAM ATUALIZADOS ESTOQUE DA TELA DE RETIRADA*/
   FOR C_RETIRADAESTO IN (SELECT CDFILIAL, NRRETEST
                            FROM RETIRADAESTO
                           WHERE CDFILIAL = P_CDFILIAL
                             AND NRORDPROD = P_NRORDPROD
                             AND NRSUBORDPROD = P_NRSUBORDPROD
                             AND NRLANCESTQ IS NULL)
   LOOP
      DELETE FROM RETIRADAESTO
      WHERE CDFILIAL = C_RETIRADAESTO.CDFILIAL
        AND NRRETEST = C_RETIRADAESTO.NRRETEST;

      DELETE FROM ITEMRETIEST
      WHERE CDFILIAL = C_RETIRADAESTO.CDFILIAL
        AND NRRETEST = C_RETIRADAESTO.NRRETEST;

      DELETE FROM LOTITRETIEST
      WHERE CDFILIAL = C_RETIRADAESTO.CDFILIAL
        AND NRRETEST = C_RETIRADAESTO.NRRETEST;
   END LOOP;

   SELECT MAX(P.CDSETORPROD), MAX(S.CDCENTCUST), MAX(P.CDTIPORETI)
     INTO V_CDSETOR, V_CDCENTCUST, V_CDTIPORETI
     FROM PARAMAN P, SETOR S
    WHERE P.CDFILIAL = P_CDFILIAL
      AND P.CDSETORPROD = S.CDSETOR(+);

   /*REGERA OS DADOS DA TELA DE RETIRADA DE ACORDO COM OS PRODUTOS ACABADOS DA OP
     SE O PRODUTO DA OP FOI PESADO E GERADO DE ACORDO COM A QUANTIDADE PESADA*/
   FOR C_PRODACABADO IN (SELECT O.CDFILIAL, O.NRORDPROD, O.NRSUBORDPROD, O.IDORDPRODCON, P.CDSUBPRODETA, P.NRSUBPRETAOP, P.CDPRODUTO, P.NRPRODACAB,
                                P.QTPRACABADO, P.NRLANCESTQ, P.NRSEQUITEM, P.QTLIQACUM QTPRODUZIDA, NVL(P.IDPRODUSUPL, 'N') IDPRODUSUPL,
                                O.CDPROCFABRIC
                           FROM ORDPRODUCAO O, PRODACABADO P
                          WHERE O.CDFILIAL = P_CDFILIAL
                            AND O.NRORDPROD = P_NRORDPROD
                            AND O.NRSUBORDPROD = P_NRSUBORDPROD
                            AND O.CDFILIAL = P.CDFILIAL
                            AND O.NRORDPROD = P.NRORDPROD
                            AND O.NRSUBORDPROD = P.NRSUBORDPROD
                            AND NVL(P.QTLIQACUM,0) <> 0)LOOP
      
      IF C_PRODACABADO.IDORDPRODCON = 'C' THEN
      
         FOR C_INSUMO IN (SELECT DISTINCT I.CDPRODUTO, I.CDPROCFABRIC, I.CDETAPAPROD, I.CDPRODINSUMO, I.IDUTAPRREQET, I.QTINSUMETAPA,
                                 I.QTRENDINSU, I.IDSEMIACETAP, O.IDORDPRODCON, O.CDPLANPRODOP, O.NRORDPRODCON,
                                 O.NRSUBOPCON, I.DSFASE, SUM(O.QTAPRODPREOP) AS QTAPRODPREOP
                            FROM ORDPRODUCAO O, INSUMETAPA I
                           WHERE O.CDFILIAL     = C_PRODACABADO.CDFILIAL
                             AND O.NRORDPRODCON = C_PRODACABADO.NRORDPROD
                             AND O.NRSUBOPCON   = C_PRODACABADO.NRSUBORDPROD
                             AND O.CDPRODUTO    = C_PRODACABADO.CDPRODUTO
                             AND O.CDFILIAL = I.CDFILIAL
                             AND O.CDPRODUTO = I.CDPRODUTO
                             AND O.CDPROCFABRIC = I.CDPROCFABRIC
                             AND C_PRODACABADO.IDPRODUSUPL = 'N'
                           GROUP BY I.CDPRODUTO, I.CDPROCFABRIC, I.CDETAPAPROD, I.CDPRODINSUMO, I.IDUTAPRREQET, I.QTINSUMETAPA,
                                    I.QTRENDINSU, I.IDSEMIACETAP, O.IDORDPRODCON, O.CDPLANPRODOP, O.NRORDPRODCON, O.NRSUBOPCON, I.DSFASE
                          UNION ALL
                          SELECT P.CDPRODUTO, P.CDPROCFABRIC, I.CDETAPAPROD, I.CDPRODINSUMO, I.IDUTAPRREQET, I.QTINSUMETAPA,
                                 I.QTRENDINSU, I.IDSEMIACETAP, 'S' IDORDPRODCON, '' CDPLANPRODOP, '' NRORDPRODCON,
                                 '' NRSUBOPCON, I.DSFASE, 0 QTAPRODPREOP
                            FROM PROCFABRICA P, INSUMETAPA I
                           WHERE P.CDFILIAL = C_PRODACABADO.CDFILIAL
                             AND P.CDPRODUTO = C_PRODACABADO.CDPRODUTO
                             AND P.IDPROFABPADR = 'S'
                             AND C_PRODACABADO.IDPRODUSUPL = 'S'
                             AND P.CDFILIAL = I.CDFILIAL
                             AND P.CDPRODUTO = I.CDPRODUTO
                             AND P.CDPROCFABRIC = I.CDPROCFABRIC)
         LOOP
            V_COUNT := 0;
            SELECT COUNT(1)
              INTO V_COUNT
              FROM RETIRADAESTO
             WHERE CDFILIAL     = C_PRODACABADO.CDFILIAL
               AND NRORDPROD    = C_PRODACABADO.NRORDPROD
               AND NRSUBORDPROD = C_PRODACABADO.NRSUBORDPROD;

            IF V_COUNT = 0 THEN
               NOVO_CODIGO_NT('RETIESTO'||C_PRODACABADO.CDFILIAL, V_NRRETEST);
               V_NRRETEST := SUBSTR(V_NRRETEST, 11, 10);

               INSERT INTO RETIRADAESTO
                 (CDFILIAL, NRRETEST, DTRETEST, NRORDPROD, NRSUBORDPROD,
                  CDETAPAPROD, CDCENTCUST, CDSETOR)
               VALUES
                 (C_PRODACABADO.CDFILIAL, V_NRRETEST, TRUNC(SYSDATE), C_PRODACABADO.NRORDPROD,  C_PRODACABADO.NRSUBORDPROD,
                  C_INSUMO.CDETAPAPROD, V_CDCENTCUST, V_CDSETOR);

               V_NRSEQRETPROD := 0;
               SELECT NVL(TO_NUMBER(MAX(NRSEQRETPROD)), 0)
                 INTO V_NRSEQRETPROD
                 FROM ITEMRETIEST
                WHERE CDFILIAL  = C_PRODACABADO.CDFILIAL
                  AND NRRETEST  = V_NRRETEST;

               INSERT INTO ITEMRETIEST
                  (CDFILIAL, NRRETEST, NRSEQRETPROD, CDPRODUTO, DTRETPROD,
                   QTPRERETPROD, CDTIPORETI, IDLOTEEXT, DSFASE)
               VALUES
                  (C_PRODACABADO.CDFILIAL, V_NRRETEST, LPAD(TO_CHAR(V_NRSEQRETPROD+1),4,'0'), C_INSUMO.CDPRODINSUMO, TRUNC(SYSDATE),
                   QTPREVI_MATPRIMA(C_PRODACABADO.CDFILIAL, C_INSUMO.CDPRODUTO, C_INSUMO.CDPROCFABRIC, C_INSUMO.CDETAPAPROD, C_INSUMO.CDPRODINSUMO, C_PRODACABADO.QTPRODUZIDA),
                   V_CDTIPORETI, 'N', C_INSUMO.DSFASE);
            ELSE

               SELECT MAX(I.NRSEQRETPROD), MAX(I.NRRETEST), MAX(I.CDPRODUTO), MAX(I.DSFASE)
                 INTO V_NRSEQRETPROD, V_NRRETEST, V_CDPRODUTO, V_DSFASE
                 FROM RETIRADAESTO R, ITEMRETIEST I
                WHERE R.CDFILIAL     = C_PRODACABADO.CDFILIAL
                  AND R.NRORDPROD    = C_PRODACABADO.NRORDPROD
                  AND R.NRSUBORDPROD = C_PRODACABADO.NRSUBORDPROD
                  AND R.CDFILIAL     = I.CDFILIAL
                  AND R.NRRETEST     = I.NRRETEST
                  AND I.CDPRODUTO    = C_INSUMO.CDPRODINSUMO;

               IF V_CDPRODUTO IS NULL THEN
                  NOVO_CODIGO_NT('RETIESTO'||C_PRODACABADO.CDFILIAL, V_NRRETEST);
                  V_NRRETEST := SUBSTR(V_NRRETEST, 11, 10);
                  
                  INSERT INTO RETIRADAESTO
                    (CDFILIAL, NRRETEST, DTRETEST, NRORDPROD, NRSUBORDPROD,
                     CDETAPAPROD, CDCENTCUST, CDSETOR)
                  VALUES
                    (C_PRODACABADO.CDFILIAL, V_NRRETEST, TRUNC(SYSDATE), C_PRODACABADO.NRORDPROD,  C_PRODACABADO.NRSUBORDPROD,
                     C_INSUMO.CDETAPAPROD, V_CDCENTCUST, V_CDSETOR);

                  V_NRSEQRETPROD := 0;
                  SELECT NVL(TO_NUMBER(MAX(NRSEQRETPROD)), 0)
                    INTO V_NRSEQRETPROD
                    FROM ITEMRETIEST
                   WHERE CDFILIAL  = C_PRODACABADO.CDFILIAL
                     AND NRRETEST  = V_NRRETEST;

                  INSERT INTO ITEMRETIEST
                     (CDFILIAL, NRRETEST, NRSEQRETPROD, CDPRODUTO, DTRETPROD,
                      QTPRERETPROD, CDTIPORETI, IDLOTEEXT, DSFASE)
                  VALUES
                     (C_PRODACABADO.CDFILIAL, V_NRRETEST, LPAD(TO_CHAR(V_NRSEQRETPROD+1),4,'0'), C_INSUMO.CDPRODINSUMO, TRUNC(SYSDATE),
                      QTPREVI_MATPRIMA(C_PRODACABADO.CDFILIAL, C_INSUMO.CDPRODUTO, C_INSUMO.CDPROCFABRIC, C_INSUMO.CDETAPAPROD, C_INSUMO.CDPRODINSUMO, C_PRODACABADO.QTPRODUZIDA),
                      V_CDTIPORETI, 'N', C_INSUMO.DSFASE);
               ELSE
                  UPDATE ITEMRETIEST
                     SET QTPRERETPROD = QTPRERETPROD + QTPREVI_MATPRIMA(C_PRODACABADO.CDFILIAL, C_INSUMO.CDPRODUTO, C_INSUMO.CDPROCFABRIC, C_INSUMO.CDETAPAPROD, C_INSUMO.CDPRODINSUMO, C_PRODACABADO.QTPRODUZIDA)
                   WHERE CDFILIAL     = C_PRODACABADO.CDFILIAL
                     AND NRRETEST     = V_NRRETEST
                     AND NRSEQRETPROD = V_NRSEQRETPROD;

                  IF (C_INSUMO.DSFASE <> V_DSFASE) THEN
                     UPDATE ITEMRETIEST
                        SET DSFASE = NULL
                        WHERE CDFILIAL     =  C_PRODACABADO.CDFILIAL
                          AND NRRETEST     = V_NRRETEST
                          AND NRSEQRETPROD = V_NRSEQRETPROD;
                  END IF;
               END IF;
            END IF;
         END LOOP;
      ELSE
        
         FOR C_INSUMO IN (SELECT I.CDPRODUTO, I.CDPROCFABRIC, I.CDETAPAPROD, I.CDPRODINSUMO, I.IDUTAPRREQET, I.QTINSUMETAPA,
                                  I.QTRENDINSU, I.IDSEMIACETAP, I.CDTIPOINSU, I.IDLOTEEXT, I.DSFASE
                             FROM INSUMETAPA I, ETAFABRICOP E
                            WHERE I.CDFILIAL     = E.CDFILIAL
                              AND I.CDPRODUTO    = C_PRODACABADO.CDPRODUTO
                              AND I.CDPROCFABRIC = C_PRODACABADO.CDPROCFABRIC
                              AND E.CDFILIAL     = C_PRODACABADO.CDFILIAL
                              AND E.NRORDPROD    = C_PRODACABADO.NRORDPROD
                              AND E.NRSUBORDPROD = C_PRODACABADO.NRSUBORDPROD
                              AND E.CDETAPAPROD  = I.CDETAPAPROD
                              AND C_PRODACABADO.IDPRODUSUPL = 'N'
                          UNION ALL
                          SELECT P.CDPRODUTO, P.CDPROCFABRIC, I.CDETAPAPROD, I.CDPRODINSUMO, I.IDUTAPRREQET, I.QTINSUMETAPA,
                                 I.QTRENDINSU, I.IDSEMIACETAP, I.CDTIPOINSU, I.IDLOTEEXT, I.DSFASE
                            FROM PROCFABRICA P, INSUMETAPA I
                           WHERE P.CDFILIAL = C_PRODACABADO.CDFILIAL
                             AND P.CDPRODUTO = C_PRODACABADO.CDPRODUTO
                             AND P.IDPROFABPADR = 'S'
                             AND C_PRODACABADO.IDPRODUSUPL = 'S'
                             AND P.CDFILIAL = I.CDFILIAL
                             AND P.CDPRODUTO = I.CDPRODUTO
                             AND P.CDPROCFABRIC = I.CDPROCFABRIC)LOOP
                             
            V_COUNT := 0;
            SELECT COUNT(1)
              INTO V_COUNT
              FROM RETIRADAESTO
             WHERE CDFILIAL     = C_PRODACABADO.CDFILIAL
               AND NRORDPROD    = C_PRODACABADO.NRORDPROD
               AND NRSUBORDPROD = C_PRODACABADO.NRSUBORDPROD;
           
            IF V_COUNT = 0 THEN
               
               SELECT MAX(E.CDSETOR), MAX(S.CDCENTCUST)
                 INTO V_CDSETORAUX, V_CDCENTCUSTAUX
                 FROM ETAFABRIC E, SETOR S
                WHERE E.CDFILIAL  = C_PRODACABADO.CDFILIAL
                  AND E.CDPRODUTO = C_PRODACABADO.CDPRODUTO
                  AND E.CDPROCFABRIC = C_PRODACABADO.CDPROCFABRIC
                  AND E.CDETAPAPROD = C_INSUMO.CDETAPAPROD
                  AND E.CDSETOR = S.CDSETOR;

               IF V_CDSETORAUX IS NOT NULL THEN
                  V_CDSETOR := V_CDSETORAUX;
               END IF;

               IF V_CDCENTCUSTAUX IS NOT NULL THEN
                  V_CDCENTCUST := V_CDCENTCUSTAUX;
               END IF;
               
               NOVO_CODIGO_NT('RETIESTO'||C_PRODACABADO.CDFILIAL, V_NRRETEST);
               V_NRRETEST := SUBSTR(V_NRRETEST, 11, 10);

               INSERT INTO RETIRADAESTO
                 (CDFILIAL, NRRETEST, DTRETEST, NRORDPROD, NRSUBORDPROD,
                  CDETAPAPROD, CDCENTCUST, CDSETOR)
               VALUES
                 (C_PRODACABADO.CDFILIAL, V_NRRETEST, TRUNC(SYSDATE), C_PRODACABADO.NRORDPROD, C_PRODACABADO.NRSUBORDPROD,
                  C_INSUMO.CDETAPAPROD, V_CDCENTCUST, V_CDSETOR);

               V_NRSEQRETPROD := 0;
               SELECT NVL(TO_NUMBER(MAX(NRSEQRETPROD)), 0)
                 INTO V_NRSEQRETPROD
                 FROM ITEMRETIEST
                WHERE CDFILIAL  = C_PRODACABADO.CDFILIAL
                  AND NRRETEST  = V_NRRETEST
                  AND CDPRODUTO =  C_INSUMO.CDPRODINSUMO;

               INSERT INTO ITEMRETIEST
                  (CDFILIAL, NRRETEST, NRSEQRETPROD, CDPRODUTO, DTRETPROD,
                   QTPRERETPROD, CDTIPORETI, IDLOTEEXT, DSFASE)
               VALUES
                  (C_PRODACABADO.CDFILIAL, V_NRRETEST, LPAD(TO_CHAR(V_NRSEQRETPROD+1),4,'0'), C_INSUMO.CDPRODINSUMO, TRUNC(SYSDATE),
                   QTPREVI_MATPRIMA(C_PRODACABADO.CDFILIAL, C_INSUMO.CDPRODUTO, C_INSUMO.CDPROCFABRIC, C_INSUMO.CDETAPAPROD, C_INSUMO.CDPRODINSUMO, C_PRODACABADO.QTPRODUZIDA),
                   V_CDTIPORETI, C_INSUMO.IDLOTEEXT, C_INSUMO.DSFASE);
            ELSE
                  
               SELECT MAX(I.NRSEQRETPROD), MAX(I.NRRETEST), MAX(I.CDPRODUTO), MAX(I.DSFASE)
                 INTO V_NRSEQRETPROD, V_NRRETEST, V_CDPRODUTO, V_DSFASE
                 FROM RETIRADAESTO R, ITEMRETIEST I
                WHERE R.CDFILIAL     = C_PRODACABADO.CDFILIAL
                  AND R.NRORDPROD    = C_PRODACABADO.NRORDPROD
                  AND R.NRSUBORDPROD = C_PRODACABADO.NRSUBORDPROD
                  AND R.CDFILIAL     = I.CDFILIAL
                  AND R.NRRETEST     = I.NRRETEST
                  AND I.CDPRODUTO    = C_INSUMO.CDPRODINSUMO;
               
               IF V_CDPRODUTO IS NULL THEN
                  
                  NOVO_CODIGO_NT('RETIESTO'||C_PRODACABADO.CDFILIAL, V_NRRETEST);
                  V_NRRETEST := SUBSTR(V_NRRETEST, 11, 10);
                  
                  INSERT INTO RETIRADAESTO
                    (CDFILIAL, NRRETEST, DTRETEST, NRORDPROD, NRSUBORDPROD,
                     CDETAPAPROD, CDCENTCUST, CDSETOR)
                  VALUES
                    (C_PRODACABADO.CDFILIAL, V_NRRETEST, TRUNC(SYSDATE), C_PRODACABADO.NRORDPROD,  C_PRODACABADO.NRSUBORDPROD,
                     C_INSUMO.CDETAPAPROD, V_CDCENTCUST, V_CDSETOR);
                     
                  V_NRSEQRETPROD := 0;
                  SELECT NVL(TO_NUMBER(MAX(NRSEQRETPROD)), 0)
                    INTO V_NRSEQRETPROD
                    FROM ITEMRETIEST
                   WHERE CDFILIAL  = C_PRODACABADO.CDFILIAL
                     AND NRRETEST  = V_NRRETEST;
                  
                  INSERT INTO ITEMRETIEST
                     (CDFILIAL, NRRETEST, NRSEQRETPROD, CDPRODUTO, DTRETPROD,
                      QTPRERETPROD, CDTIPORETI, IDLOTEEXT, DSFASE)
                  VALUES
                     (C_PRODACABADO.CDFILIAL, V_NRRETEST, LPAD(TO_CHAR(V_NRSEQRETPROD+1),4,'0'), C_INSUMO.CDPRODINSUMO, TRUNC(SYSDATE),
                      QTPREVI_MATPRIMA(C_PRODACABADO.CDFILIAL, C_INSUMO.CDPRODUTO, C_INSUMO.CDPROCFABRIC, C_INSUMO.CDETAPAPROD, C_INSUMO.CDPRODINSUMO, C_PRODACABADO.QTPRODUZIDA),
                      V_CDTIPORETI, C_INSUMO.IDLOTEEXT, C_INSUMO.DSFASE);
               ELSE
               
                  UPDATE ITEMRETIEST
                     SET QTPRERETPROD = QTPRERETPROD + QTPREVI_MATPRIMA(C_PRODACABADO.CDFILIAL, C_INSUMO.CDPRODUTO, C_INSUMO.CDPROCFABRIC, C_INSUMO.CDETAPAPROD, C_INSUMO.CDPRODINSUMO, C_PRODACABADO.QTPRODUZIDA)
                   WHERE CDFILIAL     = C_PRODACABADO.CDFILIAL
                     AND NRRETEST     = V_NRRETEST
                     AND NRSEQRETPROD = V_NRSEQRETPROD
                     AND CDPRODUTO    =  C_INSUMO.CDPRODINSUMO;

                  IF (C_INSUMO.DSFASE <> V_DSFASE) THEN
                     UPDATE ITEMRETIEST
                        SET DSFASE = NULL
                        WHERE CDFILIAL     = C_PRODACABADO.CDFILIAL
                          AND NRRETEST     = V_NRRETEST
                          AND NRSEQRETPROD = V_NRSEQRETPROD
                          AND CDPRODUTO    = C_INSUMO.CDPRODINSUMO;
                  END IF;

               END IF;
            END IF;
         END LOOP;
      END IF;
   END LOOP;
END;