create or replace PROCEDURE ALTERA_QTDE_PRODACABADO
(
P_NRSEQPRODACABADO NUMBER,
P_NRPLANPROD NUMBER,
P_CDPRODACABADO VARCHAR2,
P_IDCOOKCHILL VARCHAR2,
P_IDFINALIZACAO VARCHAR2,
P_IDTPPRODCHILL VARCHAR2,
P_IDESTADO VARCHAR2,
P_QTPRODACABADO FLOAT,
P_QTAPRODUZIR FLOAT,
P_NRJUSTIFICATIVA NUMBER,
P_NRORG NUMBER,
P_INSEREDIF VARCHAR2 DEFAULT 'N',
P_CDOPERADOR VARCHAR2 DEFAULT 1,
P_CAPACIDADE OUT VARCHAR2
) AS

V_PERCENT_ALTERACAO FLOAT;
V_NRSEQ NUMBER;
V_QTOTALPROD FLOAT;
V_DIFERENCA FLOAT := 0.00;
V_CDFILIAGREG VARCHAR2(4);
V_CDSERVFILIAGREG VARCHAR2(5);
V_DTCONSUMO DATE;
V_CDPRODUTO VARCHAR2(10);
V_OLD_QTAPRODUZIR FLOAT;

V_NRSEQPRODACABADO FLOAT;
V_DIFNOVOPROD FLOAT := 0.00;
V_SEQUENCIAL VARCHAR2(20);
V_PERCENT_DIF FLOAT;
BEGIN

    --INSERE NOVO PRODUTO COM A DIFERENA (QTPRODACABADO - QTAPRODUZIR)
    IF P_INSEREDIF = 'S' THEN 

        SELECT MAX(NRSEQPRODACABADO) INTO V_NRSEQPRODACABADO
        FROM PLANPRODACABADO 
        WHERE NRPLANPROD       = P_NRPLANPROD       AND 
             CDPRODACABADO    = P_CDPRODACABADO    AND
             IDCOOKCHILL      = P_IDCOOKCHILL      AND
             IDFINALIZACAO    = P_IDFINALIZACAO    AND
             IDESTADO         = P_IDESTADO         AND
             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
             NRORG            = P_NRORG;

        V_NRSEQPRODACABADO := V_NRSEQPRODACABADO + 1; 

        V_DIFNOVOPROD := P_QTPRODACABADO - (P_QTAPRODUZIR + V_DIFERENCA);
        V_PERCENT_DIF:= (V_DIFNOVOPROD / P_QTPRODACABADO);

        ------PLANPRODACABADO
        INSERT INTO PLANPRODACABADO
        (NRPLANPROD, CDPRODACABADO, CDPRATO, CDFILAUXPRAT, IDPRODUZIR, IDCONCLUIDO, CDCONTAINER, NRSEQITLANEST,
         NRORG, NRORGINCLUSAO, NRORGULTATU, DTINCLUSAO, DTULTATU, CDOPERINCLUSAO, CDOPERULTATU, 
         IDATIVO, IDCOOKCHILL, IDFINALIZACAO, IDTPPRODCHILL, IDESTADO, 
         QTPRODACABADO, NRSEQPRODACABADO, QTAPRODUZIR, QTPERDA, QTEXTRA, 
         NRJUSTPLANPROD, IDORIGEM, CDPRATOORI, CDFILAUXPRATORI, HRINICIO, HRFIM)

         SELECT  NRPLANPROD, CDPRODACABADO, CDPRATO, CDFILAUXPRAT, IDPRODUZIR, IDCONCLUIDO, CDCONTAINER, NRSEQITLANEST,
                 NRORG, NRORGINCLUSAO, NRORGULTATU, SYSDATE, SYSDATE, P_CDOPERADOR, P_CDOPERADOR, 
                 IDATIVO, IDCOOKCHILL, IDFINALIZACAO, IDTPPRODCHILL, IDESTADO, 
                 V_DIFNOVOPROD, V_NRSEQPRODACABADO, V_DIFNOVOPROD, 0, 0, 
                 NRJUSTPLANPROD, IDORIGEM, CDPRATOORI, CDFILAUXPRATORI, HRINICIO, HRFIM 
         FROM PLANPRODACABADO
         WHERE NRSEQPRODACABADO   = P_NRSEQPRODACABADO AND 
                 NRPLANPROD       = P_NRPLANPROD       AND 
                 CDPRODACABADO    = P_CDPRODACABADO    AND
                 IDCOOKCHILL      = P_IDCOOKCHILL      AND
                 IDFINALIZACAO    = P_IDFINALIZACAO    AND
                 IDESTADO         = P_IDESTADO         AND
                 IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                 NRORG            = P_NRORG;

        ------PLANPRODFILIAGREGA
        INSERT INTO PLANPRODFILIAGREGA
         (NRPLANPROD,CDPRODACABADO,CDFILIAGREG,CDSERVFILIAGREG, DTCONSUMO,VRUNITPRODSERV,IDPRODUZIR,IDCONCLUIDO,IDCOOKCHILL,     
          IDFINALIZACAO, IDATIVO, IDTPPRODCHILL, IDESTADO,
          DTINCLUSAO, DTULTATU, NRORG, NRORGINCLUSAO, NRORGULTATU, CDOPERINCLUSAO, CDOPERULTATU,
          NRSEQPRODACABADO,QTPRODSERV,QTAPRODUZIR, QTPERDA, QTEXTRA,
          QTCOMENSSERV, VRTOTALPRODSERV)
          SELECT  NRPLANPROD,CDPRODACABADO,CDFILIAGREG,CDSERVFILIAGREG, DTCONSUMO,VRUNITPRODSERV,IDPRODUZIR,IDCONCLUIDO,IDCOOKCHILL,     
                  IDFINALIZACAO, IDATIVO, IDTPPRODCHILL, IDESTADO,
                  SYSDATE, SYSDATE, NRORG, NRORGINCLUSAO, NRORGULTATU, P_CDOPERADOR, P_CDOPERADOR,
                  V_NRSEQPRODACABADO, ROUND(V_PERCENT_DIF * QTPRODSERV, 3), ROUND(V_PERCENT_DIF * QTPRODSERV, 3) , 0 , 0,
                  ROUND(V_PERCENT_DIF * QTCOMENSSERV,3), ROUND((V_PERCENT_DIF * VRTOTALPRODSERV),2)
          FROM PLANPRODFILIAGREGA
          WHERE  NRSEQPRODACABADO = P_NRSEQPRODACABADO AND 
                 NRPLANPROD       = P_NRPLANPROD       AND 
                 CDPRODACABADO    = P_CDPRODACABADO    AND
                 IDCOOKCHILL      = P_IDCOOKCHILL      AND
                 IDFINALIZACAO    = P_IDFINALIZACAO    AND
                 IDESTADO         = P_IDESTADO         AND
                 IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                 NRORG            = P_NRORG;

        --------CARDACLIPLANPROD
       /* FOR C_CARDACLI IN (SELECT C.NRCARDACLIPLANPROD, C.NRPLANPROD, C.CDPRODUTO, C.CDPRATO, C.CDFILAUXPRAT, C.CDFILIAL, C.CDSERVFILI, C.DTCONSUMO, C.CDCLIENTE, C.CDDESTFILI, C.CDTIPOPRAT,
                                    C.QTCOMESTICLI, C.NRSEQPRATCAR, C.IDCOOKCHILL, C.IDTPPRODCHILL, C.IDESTADO, C.CDCONTAINER, C.VRQTDEPRATEMB, C.IDATIVO,
                                    C.NRORG, C.NRORGINCLUSAO, C.NRORGULTATU, -- C.CDOPERINCLUSAO, C.CDOPERULTATU, 
                                    C.QTAPRODUZIR, C.QTEXTRA
                            FROM CARDACLIPLANPROD C
                            WHERE C.NRPLANPROD      =  P_NRPLANPROD AND
                                 C.CDPRODUTO        = P_CDPRODACABADO AND
                                 C.IDCOOKCHILL      = P_IDCOOKCHILL      AND
                                 C.IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                                 C.IDESTADO         = P_IDESTADO         AND
                                 C.NRORG            = P_NRORG)LOOP

        --Busca novo sequencial               
         NOVO_CODIGO_SC('CARDACLIPLANPROD',V_SEQUENCIAL);

        INSERT INTO CARDACLIPLANPROD
                    (NRCARDACLIPLANPROD, NRPLANPROD, CDPRODUTO, CDPRATO, CDFILAUXPRAT, CDFILIAL, CDSERVFILI, DTCONSUMO, CDCLIENTE, CDDESTFILI, CDTIPOPRAT,
                    QTCOMESTICLI, NRSEQPRATCAR, IDCOOKCHILL, IDTPPRODCHILL, IDESTADO, CDCONTAINER, VRQTDEPRATEMB, IDATIVO,
                    DTINCLUSAO, DTULTATU, NRORG, NRORGINCLUSAO, NRORGULTATU, CDOPERINCLUSAO, CDOPERULTATU,
                    QTAPRODUZIR, QTEXTRA)
        VALUES( V_SEQUENCIAL, C_CARDACLI.NRPLANPROD, C_CARDACLI.CDPRODUTO, C_CARDACLI.CDPRATO, C_CARDACLI.CDFILAUXPRAT, C_CARDACLI.CDFILIAL, C_CARDACLI.CDSERVFILI, C_CARDACLI.DTCONSUMO, C_CARDACLI.CDCLIENTE, C_CARDACLI.CDDESTFILI, C_CARDACLI.CDTIPOPRAT,
                    C_CARDACLI.QTCOMESTICLI, C_CARDACLI.NRSEQPRATCAR, C_CARDACLI.IDCOOKCHILL, C_CARDACLI.IDTPPRODCHILL, C_CARDACLI.IDESTADO, C_CARDACLI.CDCONTAINER, C_CARDACLI.VRQTDEPRATEMB, C_CARDACLI.IDATIVO,
                    SYSDATE, SYSDATE, C_CARDACLI.NRORG, C_CARDACLI.NRORGINCLUSAO, C_CARDACLI.NRORGULTATU, P_CDOPERADOR, P_CDOPERADOR,
                    ROUND(V_PERCENT_DIF * C_CARDACLI.QTAPRODUZIR,3), 0);

        END LOOP;

        UPDATE CARDACLIPLANPROD
        SET QTCOMESTICLI = ROUND(V_PERCENT_ALTERACAO * QTCOMESTICLI,3)
        WHERE NRPLANPROD       =  P_NRPLANPROD   AND
              CDPRODUTO        = P_CDPRODACABADO AND
              IDCOOKCHILL      = P_IDCOOKCHILL   AND
              IDTPPRODCHILL    = P_IDTPPRODCHILL AND
              IDESTADO         = P_IDESTADO      AND
              NRORG            = P_NRORG;
        */

        ------PLANRECPROD
        INSERT INTO PLANRECPROD
        (NRPLANPROD,CDPRODACABADO,CDFILIAL,DTCONSUMO, CDFILIAGREG, CDSERVFILIAGREG, CDPRODUTO, CDTIPOPRAT, QTPRODRECEPRAT,
        IDTPRECEITA, IDADIANTADO, NRSEQITRETI,IDATIVO, IDCOOKCHILL, IDFINALIZACAO, IDTPPRODCHILL, IDESTADO,
        NRORG, NRORGINCLUSAO, NRORGULTATU, DTINCLUSAO, DTULTATU, CDOPERINCLUSAO, CDOPERULTATU,
        VRUNITRECEPRAT, VRTOTALRECEPRAT, 
        NRSEQPRODACABADO, QTAPRODUZIR, QTLIBERADA, QTEXTRA, QTDEVOLVIDA)

        SELECT  NRPLANPROD,CDPRODACABADO,CDFILIAL,DTCONSUMO, CDFILIAGREG, CDSERVFILIAGREG, CDPRODUTO, CDTIPOPRAT, ROUND(V_PERCENT_DIF * QTPRODRECEPRAT,3),
                IDTPRECEITA, IDADIANTADO, NRSEQITRETI,IDATIVO, IDCOOKCHILL, IDFINALIZACAO, IDTPPRODCHILL, IDESTADO,
                NRORG, NRORGINCLUSAO, NRORGULTATU, SYSDATE, SYSDATE, P_CDOPERADOR, P_CDOPERADOR,
                VRUNITRECEPRAT,  ROUND(ROUND(V_PERCENT_DIF *  QTAPRODUZIR, 3) * VRUNITRECEPRAT,2), 
                V_NRSEQPRODACABADO,  ROUND(V_PERCENT_DIF * QTAPRODUZIR,3),  ROUND(V_PERCENT_DIF * QTLIBERADA,3), 0, 0
        FROM PLANRECPROD
        WHERE    NRSEQPRODACABADO = P_NRSEQPRODACABADO AND 
                 NRPLANPROD       = P_NRPLANPROD       AND 
                 CDPRODACABADO    = P_CDPRODACABADO    AND
                 IDCOOKCHILL      = P_IDCOOKCHILL      AND
                 IDFINALIZACAO    = P_IDFINALIZACAO    AND
                 IDESTADO         = P_IDESTADO         AND
                 IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                 NRORG            = P_NRORG;

    END IF;

    --====================================================================================================
   --
   -- QUANTIDADE A ALTERAR E MAIOR, ENTAO APENAS UPDATA O VALOR NO ORIGINAL E NAO CRIA NOVA LINHA
   --      
   --====================================================================================================      
      SELECT DECODE(QTAPRODUZIR, 0, QTPRODACABADO, QTAPRODUZIR) INTO V_OLD_QTAPRODUZIR
        FROM PLANPRODACABADO 
       WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND 
             NRPLANPROD       = P_NRPLANPROD       AND 
             CDPRODACABADO    = P_CDPRODACABADO    AND
             IDCOOKCHILL      = P_IDCOOKCHILL      AND
             IDFINALIZACAO    = P_IDFINALIZACAO    AND
             IDESTADO         = P_IDESTADO         AND
             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
             NRORG            = P_NRORG; 
             
             
      /*PERCENTUAL DE ALTERACAO PARA A NOVA LINHA COM O SALDO*/
      V_PERCENT_ALTERACAO := (P_QTAPRODUZIR / V_OLD_QTAPRODUZIR);
 
      UPDATE PLANPRODACABADO
         SET QTAPRODUZIR = P_QTAPRODUZIR,
             NRJUSTPLANPROD = P_NRJUSTIFICATIVA,
             QTPRODACABADO = DECODE(P_INSEREDIF, 'S', P_QTAPRODUZIR, QTPRODACABADO)
       WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND 
             NRPLANPROD       = P_NRPLANPROD       AND 
             CDPRODACABADO    = P_CDPRODACABADO    AND
             IDCOOKCHILL      = P_IDCOOKCHILL      AND
             IDFINALIZACAO    = P_IDFINALIZACAO    AND
             IDESTADO         = P_IDESTADO         AND
             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
             NRORG            = P_NRORG; 
      
      UPDATE PLANPRODFILIAGREGA
         SET QTAPRODUZIR = ROUND(V_PERCENT_ALTERACAO * DECODE(QTAPRODUZIR, 0, QTPRODSERV, QTAPRODUZIR),3),
             QTCOMENSSERV = ROUND(V_PERCENT_ALTERACAO * QTCOMENSSERV,3),
             VRTOTALPRODSERV = ROUND((V_PERCENT_ALTERACAO * VRTOTALPRODSERV),2),
             QTPRODSERV = DECODE(P_INSEREDIF, 'S', ROUND(V_PERCENT_ALTERACAO * QTPRODSERV,3), QTPRODSERV)
       WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND 
             NRPLANPROD       = P_NRPLANPROD       AND 
             CDPRODACABADO    = P_CDPRODACABADO    AND
             IDCOOKCHILL      = P_IDCOOKCHILL      AND
             IDFINALIZACAO    = P_IDFINALIZACAO    AND
             IDESTADO         = P_IDESTADO         AND
             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
             NRORG            = P_NRORG;

        /*IF P_INSEREDIF = 'N' THEN      
          UPDATE CARDACLIPLANPROD
             SET QTAPRODUZIR =  ROUND(V_PERCENT_ALTERACAO * QTAPRODUZIR,3)
                 --QTCOMESTICLI = ROUND(V_PERCENT_ALTERACAO * QTCOMESTICLI,3)
           WHERE NRPLANPROD =  P_NRPLANPROD AND
                 CDPRODUTO    = P_CDPRODACABADO AND
                 IDCOOKCHILL      = P_IDCOOKCHILL      AND
                 IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                 IDESTADO         = P_IDESTADO         AND
                 NRORG            = P_NRORG;
        END IF;*/
             
      UPDATE PLANRECPROD
         SET VRTOTALRECEPRAT =  ROUND(ROUND(V_PERCENT_ALTERACAO *  QTAPRODUZIR, 3) * VRUNITRECEPRAT,2),
             --VRTOTALRECEPRAT =  ROUND(V_PERCENT_ALTERACAO * VRTOTALRECEPRAT,2),
             QTAPRODUZIR    = ROUND(V_PERCENT_ALTERACAO *  DECODE(QTAPRODUZIR, 0, QTPRODRECEPRAT, QTAPRODUZIR),3),
             QTLIBERADA    = ROUND(V_PERCENT_ALTERACAO *  QTLIBERADA,3),
             QTPRODRECEPRAT = DECODE(P_INSEREDIF, 'S', ROUND(V_PERCENT_ALTERACAO *  QTPRODRECEPRAT,3), QTPRODRECEPRAT)
       WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND
             NRPLANPROD       = P_NRPLANPROD       AND 
             CDPRODACABADO    = P_CDPRODACABADO    AND
             IDCOOKCHILL      = P_IDCOOKCHILL      AND
             IDFINALIZACAO    = P_IDFINALIZACAO    AND
             IDESTADO         = P_IDESTADO         AND
             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
             NRORG            = P_NRORG;

      /*AJUSTANDO O ARREDONDAMENTO DAS QUANTIDADES E VALORES TOTAIS PLANPRODFILIAGREGA*/                        
      SELECT SUM(QTAPRODUZIR) INTO V_QTOTALPROD
        FROM PLANPRODFILIAGREGA
       WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND
             NRPLANPROD       = P_NRPLANPROD       AND 
             CDPRODACABADO    = P_CDPRODACABADO    AND
             IDCOOKCHILL      = P_IDCOOKCHILL      AND
             IDFINALIZACAO    = P_IDFINALIZACAO    AND
             IDESTADO         = P_IDESTADO         AND
             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
             NRORG            = P_NRORG;
      
      IF V_QTOTALPROD <> P_QTAPRODUZIR THEN
         V_DIFERENCA := P_QTAPRODUZIR - V_QTOTALPROD;
      
         /*ATUALIZA A PRIMEIRA LINHA QUE ENCONTRAR*/
         SELECT CDFILIAGREG, CDSERVFILIAGREG, DTCONSUMO
           INTO V_CDFILIAGREG, V_CDSERVFILIAGREG, V_DTCONSUMO
           FROM PLANPRODFILIAGREGA
          WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND
                NRPLANPROD       = P_NRPLANPROD       AND 
                CDPRODACABADO    = P_CDPRODACABADO    AND
                IDCOOKCHILL      = P_IDCOOKCHILL      AND
                IDFINALIZACAO    = P_IDFINALIZACAO    AND
                IDESTADO         = P_IDESTADO         AND
                IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                NRORG            = P_NRORG            AND
                ROWNUM = 1;
         
         UPDATE PLANPRODFILIAGREGA
            SET QTAPRODUZIR = QTAPRODUZIR + V_DIFERENCA
          WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO AND
                NRPLANPROD       = P_NRPLANPROD       AND 
                CDPRODACABADO    = P_CDPRODACABADO    AND
                IDCOOKCHILL      = P_IDCOOKCHILL      AND
                IDFINALIZACAO    = P_IDFINALIZACAO    AND
                IDESTADO         = P_IDESTADO         AND
                IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                CDFILIAGREG      = V_CDFILIAGREG      AND
                CDSERVFILIAGREG  = V_CDSERVFILIAGREG  AND
                DTCONSUMO        = V_DTCONSUMO        AND
                NRORG            = P_NRORG;
        
        /*IF P_INSEREDIF = 'N' THEN
			UPDATE CARDACLIPLANPROD 		
	           SET QTAPRODUZIR = QTAPRODUZIR + V_DIFERENCA		
	         WHERE NRPLANPROD       =  P_NRPLANPROD AND		
	             CDPRODUTO        = P_CDPRODACABADO AND		
	             IDCOOKCHILL      = P_IDCOOKCHILL      AND		
	             IDTPPRODCHILL    = P_IDTPPRODCHILL    AND		
	             IDESTADO         = P_IDESTADO         AND		
	             NRORG            = P_NRORG            AND		
	             ROWNUM = 1;
        END IF;*/
      END IF;
            
      /*AJUSTANDO O ARREDONDAMENTO DAS QUANTIDADES E VALORES TOTAIS PLANRECPROD*/
      FOR C_RECEITA IN (SELECT PF.CDFILIAGREG, PF.CDSERVFILIAGREG, PF.DTCONSUMO, PF.VRTOTALPRODSERV, SUM(PR.VRTOTALRECEPRAT) AS VRTOTALRECEPRAT
                          FROM PLANPRODFILIAGREGA PF 
                         INNER JOIN PLANRECPROD PR   ON (PF.NRSEQPRODACABADO = PR.NRSEQPRODACABADO) AND (PF.NRPLANPROD = PR.NRPLANPROD) AND
                                                        (PF.CDPRODACABADO = PR.CDPRODACABADO) AND (PF.CDFILIAGREG = PR.CDFILIAGREG) AND
                                                        (PF.CDSERVFILIAGREG = PR.CDSERVFILIAGREG) AND (PF.DTCONSUMO = PR.DTCONSUMO) AND
                                                        (PF.IDCOOKCHILL = PR.IDCOOKCHILL) AND (PF.IDFINALIZACAO = PR.IDFINALIZACAO) AND
                                                        (PF.IDESTADO = PR.IDESTADO) AND (PF.IDTPPRODCHILL = PR.IDTPPRODCHILL) AND
                                                        (PF.NRORG = PR.NRORG) AND (PR.VRUNITRECEPRAT > 0)
                         WHERE PF.NRSEQPRODACABADO = P_NRSEQPRODACABADO AND
                               PF.NRPLANPROD       = P_NRPLANPROD       AND 
                               PF.CDPRODACABADO    = P_CDPRODACABADO    AND
                               PF.IDCOOKCHILL      = P_IDCOOKCHILL      AND
                               PF.IDFINALIZACAO    = P_IDFINALIZACAO    AND
                               PF.IDESTADO         = P_IDESTADO         AND
                               PF.IDTPPRODCHILL    = P_IDTPPRODCHILL    AND
                               PF.NRORG            = P_NRORG           
                        GROUP BY PF.CDFILIAGREG, PF.CDSERVFILIAGREG, PF.DTCONSUMO, PF.VRTOTALPRODSERV 
                        HAVING PF.VRTOTALPRODSERV <> SUM(PR.VRTOTALRECEPRAT)) LOOP
 
         V_DIFERENCA := C_RECEITA.VRTOTALPRODSERV - C_RECEITA.VRTOTALRECEPRAT;  
            
         SELECT CDPRODUTO
           INTO V_CDPRODUTO
           FROM PLANRECPROD
          WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO        AND
                NRPLANPROD       = P_NRPLANPROD              AND 
                CDPRODACABADO    = P_CDPRODACABADO           AND
                IDCOOKCHILL      = P_IDCOOKCHILL             AND
                IDFINALIZACAO    = P_IDFINALIZACAO           AND
                IDESTADO         = P_IDESTADO                AND
                IDTPPRODCHILL    = P_IDTPPRODCHILL           AND
                CDFILIAGREG      = C_RECEITA.CDFILIAGREG     AND
                CDSERVFILIAGREG  = C_RECEITA.CDSERVFILIAGREG AND
                DTCONSUMO        = C_RECEITA.DTCONSUMO       AND
                NRORG            = P_NRORG                   AND
                ROWNUM = 1;
         
         UPDATE PLANRECPROD
            SET VRTOTALRECEPRAT =  VRTOTALRECEPRAT + V_DIFERENCA/*,
                QTAPRODUZIR  = ROUND(((VRTOTALRECEPRAT + V_DIFERENCA) / VRUNITRECEPRAT),2)*/
          WHERE NRSEQPRODACABADO = P_NRSEQPRODACABADO        AND
                NRPLANPROD       = P_NRPLANPROD              AND 
                CDPRODACABADO    = P_CDPRODACABADO           AND
                IDCOOKCHILL      = P_IDCOOKCHILL             AND
                IDFINALIZACAO    = P_IDFINALIZACAO           AND
                IDESTADO         = P_IDESTADO                AND
                IDTPPRODCHILL    = P_IDTPPRODCHILL           AND
                CDFILIAGREG      = C_RECEITA.CDFILIAGREG     AND
                CDSERVFILIAGREG  = C_RECEITA.CDSERVFILIAGREG AND
                DTCONSUMO        = C_RECEITA.DTCONSUMO       AND 
                CDPRODUTO        = V_CDPRODUTO               AND
                NRORG            = P_NRORG;
            
      END LOOP;  
      
      SELECT DECODE(COUNT(ROWNUM), 0, 'N', 'S') INTO P_CAPACIDADE 
      FROM CAPACIDADEPRODCOZ 
      WHERE NRPLANPROD = P_NRPLANPROD
            AND CDPRODUTO  = P_CDPRODACABADO
            AND NRSEQPRODACABADO = P_NRSEQPRODACABADO
            AND IDCOOKCHILL = P_IDCOOKCHILL
            AND IDFINALIZACAO = P_IDFINALIZACAO
            AND IDTPPRODCHILL = P_IDTPPRODCHILL
            AND IDESTADO = P_IDESTADO;
      
      IF(P_CAPACIDADE = 'S') THEN
          DELETE FROM CAPACIDADEPRODCOZ
              WHERE NRPLANPROD = P_NRPLANPROD
                    AND NRSEQPRODACABADO = P_NRSEQPRODACABADO
                    AND CDPRODUTO  = P_CDPRODACABADO
                    AND IDCOOKCHILL = P_IDCOOKCHILL
                    AND IDFINALIZACAO = P_IDFINALIZACAO
                    AND IDTPPRODCHILL = P_IDTPPRODCHILL
                    AND IDESTADO = P_IDESTADO;
      END IF;
      
END;
/