CREATE OR REPLACE PROCEDURE NUTRIENTE_DO_CARDAPIO_PLA
  (P_FILIAL       IN VARCHAR2,
   P_SERVICOS     IN VARCHAR2,
   P_NUTRIENTES   IN VARCHAR2,
   P_NRSEQUNUTR   IN VARCHAR2,
   P_IDNDPCAL     IN VARCHAR2,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE) IS

   V_QTCARBOIDRATO NUMBER;
   V_QTPROTEINA    NUMBER;
   V_QTLIPIDEO     NUMBER;
   V_QTALCOOL      NUMBER;
   V_QTCALORIA     NUMBER;

   V_CDFILINUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDSERVNUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_DTREFENUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDDIETNUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_NRSEQPRATNUT VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDTPPRATNUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDFAPRATNUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDPRATNUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDPRODNUTR VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
   V_CDNUTRCALC VARCHAR2(20) := '_X_X_X_X_X_X_X_X_X_X';
BEGIN
   FOR C_NUTRTRAB IN (
      SELECT CD.CDFILIAL, CD.CDSERVFILI, CD.DTCARDAPIO, TO_CHAR(DTCARDAPIO, 'YYYYMMDD') DTCARDREF,
             SUBSTR(LTRIM(CD.CDDIETPAD||'XXXX'),1,4) AS CDDIETPAD,
             CC.NRSEQPRATCAR, TO_CHAR(CC.NRSEQPRATCAR) AS NRSEQNUT, CC.CDTIPOPRAT, CC.CDFILAUXPRAT, CC.CDPRATO,
             R.CDPRODUTO, R.SGUNIDADE AS SGUNIDPROD, NP.CDNUTRIENTE,
             NVL(R.QTCALCNUTR,0) AS QTPRODUTO,
             NVL((NP.QTNUTRPROD * 10 * NVL(DECODE(PRODFILI.VRPESOUNIDFI, 0, R.VRPESOUNID, PRODFILI.VRPESOUNIDFI), R.VRPESOUNID) * R.QTCALCNUTR), 0) AS QTNUTPROD,
             NVL(CC.VRPECONSCARD,0) VRPECONSCARD,
             R.QTBRUTAUNR AS QTPRODPRATT, R.QTPRODLIQ
        FROM CARDADIA CD, COMPCARD CC, TABLE(
              RECEITA_UTIL(P_FILIAL, CD.CDSERVFILI, CC.CDPRATO, CC.CDFILAUXPRAT, 
                           '0', 'Z', CC.CDPRATO,0,0,'N')
              ) R, PRODFILI, NUTRPROD NP
       WHERE CD.CDFILIAL = P_FILIAL
         AND (R.CDPRATOPAI = CC.CDPRATO)
         AND (R.CDFILAUXPRAT = CC.CDFILAUXPRAT)
         AND VALOR_NA_LISTA(CD.CDSERVFILI, P_SERVICOS) > 0
         AND CD.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
         AND CC.CDFILIAL = CD.CDFILIAL
         AND CC.NRCARDAPIO = CD.NRCARDAPIO 
         AND (INSTR(P_NUTRIENTES, NP.CDNUTRIENTE) <> 0 OR P_NUTRIENTES IS NULL)
         AND NP.CDPRODUTO = R.CDPRODUTO
         AND PRODFILI.CDFILIAL = P_FILIAL
         AND PRODFILI.CDPRODUTO = R.CDPRODUTO
       ORDER BY CDFILIAL, CDSERVFILI, DTCARDAPIO, NRSEQPRATCAR, CDDIETPAD, CDTIPOPRAT, CDFILAUXPRAT, CDPRATO, CDPRODUTO, CDNUTRIENTE
      ) LOOP

      IF V_CDFILINUTR <> C_NUTRTRAB.CDFILIAL
      OR V_CDSERVNUTR <> C_NUTRTRAB.CDSERVFILI
      OR V_DTREFENUTR <> C_NUTRTRAB.DTCARDREF
      OR V_CDDIETNUTR <> C_NUTRTRAB.CDDIETPAD
      OR V_NRSEQPRATNUT <> C_NUTRTRAB.NRSEQNUT
      OR V_CDTPPRATNUTR <> C_NUTRTRAB.CDTIPOPRAT
      OR V_CDFAPRATNUTR <> C_NUTRTRAB.CDFILAUXPRAT
      OR V_CDPRATNUTR <> C_NUTRTRAB.CDPRATO
      OR V_CDPRODNUTR <> C_NUTRTRAB.CDPRODUTO
      OR V_CDNUTRCALC <> C_NUTRTRAB.CDNUTRIENTE THEN
         V_CDFILINUTR := C_NUTRTRAB.CDFILIAL;
         V_CDSERVNUTR := C_NUTRTRAB.CDSERVFILI;
         V_DTREFENUTR := C_NUTRTRAB.DTCARDREF;
         V_CDDIETNUTR := C_NUTRTRAB.CDDIETPAD;
         V_NRSEQPRATNUT := C_NUTRTRAB.NRSEQNUT;
         V_CDTPPRATNUTR := C_NUTRTRAB.CDTIPOPRAT;
         V_CDFAPRATNUTR := C_NUTRTRAB.CDFILAUXPRAT;
         V_CDPRATNUTR := C_NUTRTRAB.CDPRATO;
         V_CDPRODNUTR := C_NUTRTRAB.CDPRODUTO;
         V_CDNUTRCALC := C_NUTRTRAB.CDNUTRIENTE;

         INSERT INTO NUTRTRAB
         (NRSEQUNUTR,
          CDFILINUTR,
          CDSERVNUTR,
          DTREFENUTR,
          CDDIETNUTR,
          NRSEQPRATNUT,
          CDTPPRATNUTR,
          CDFAPRATNUTR,
          CDPRATNUTR,
          CDPRODNUTR,
          CDNUTRCALC,
          QTPRODNUTR,
          QTNUTRCALC,
          QTPROTLIQU,
          VRCALORNUTR,
          VRPECONSNUTR,
          SGUNIDPRODT,
          QTPRODPRATT)
      VALUES
         (P_NRSEQUNUTR,
          C_NUTRTRAB.CDFILIAL,
          C_NUTRTRAB.CDSERVFILI,
          C_NUTRTRAB.DTCARDAPIO,
          C_NUTRTRAB.CDDIETPAD,
          C_NUTRTRAB.NRSEQPRATCAR,
          C_NUTRTRAB.CDTIPOPRAT,
          C_NUTRTRAB.CDFILAUXPRAT,
          C_NUTRTRAB.CDPRATO,
          C_NUTRTRAB.CDPRODUTO,
          C_NUTRTRAB.CDNUTRIENTE,
          C_NUTRTRAB.QTPRODUTO,
          C_NUTRTRAB.QTNUTPROD,
          C_NUTRTRAB.QTPRODLIQ,
          0,
          C_NUTRTRAB.VRPECONSCARD,
          C_NUTRTRAB.SGUNIDPROD,
          C_NUTRTRAB.QTPRODPRATT);
      ELSE
         UPDATE NUTRTRAB
         SET QTPRODNUTR = QTPRODNUTR + C_NUTRTRAB.QTPRODUTO,
         QTNUTRCALC = QTNUTRCALC + C_NUTRTRAB.QTNUTPROD,
         QTPROTLIQU = QTPROTLIQU + C_NUTRTRAB.QTPRODLIQ,
         QTPRODPRATT = QTPRODPRATT + C_NUTRTRAB.QTPRODPRATT
         WHERE NRSEQUNUTR = P_NRSEQUNUTR
         AND CDFILINUTR = C_NUTRTRAB.CDFILIAL
         AND CDSERVNUTR = C_NUTRTRAB.CDSERVFILI
         AND DTREFENUTR = C_NUTRTRAB.DTCARDAPIO
         AND CDDIETNUTR = C_NUTRTRAB.CDDIETPAD
         AND NRSEQPRATNUT = C_NUTRTRAB.NRSEQPRATCAR
         AND CDTPPRATNUTR = C_NUTRTRAB.CDTIPOPRAT
         AND CDFAPRATNUTR = C_NUTRTRAB.CDFILAUXPRAT
         AND CDPRATNUTR = C_NUTRTRAB.CDPRATO
         AND CDPRODNUTR = C_NUTRTRAB.CDPRODUTO
         AND CDNUTRCALC = C_NUTRTRAB.CDNUTRIENTE;

      END IF;      

   END LOOP;

   /* ------------------------- CALCULO DO DNPCal --------------------------- */
   IF P_IDNDPCAL = 'S' THEN
      /* ---------------- GRAVA O VALOR DE PROTEINA LIQUIDA (NPU) ----------------- */
     /* UPDATE
         NUTRTRAB
      SET
         QTPROTLIQU = (SELECT
                          NVL(MAX(VRFATCALNDPC) * QTNUTRCALC,0)
                       FROM
                          NDPCAL
                       WHERE
                          CDPRODUTO = (SELECT
                                          MAX(CDPRODUTO)
                                       FROM
                                          NDPCAL
                                       WHERE
                                          INSTR(NUTRTRAB.CDPRODNUTR,CDPRODUTO) = 1
                                      )
                       )
      WHERE
         CDNUTRCALC = (SELECT MAX(CDPROTEINA) FROM PARAMGERAL) AND
         NRSEQUNUTR = P_NRSEQUNUTR;
     */

      /* --------------------- GRAVA O VALOR DE CALORIAS -------------------- */
      UPDATE
         NUTRTRAB
      SET
         VRCALORNUTR = QTNUTRCALC
      WHERE
         CDNUTRCALC = (SELECT MAX(CDCALORIAS) FROM PARAMGERAL) AND
         NRSEQUNUTR = P_NRSEQUNUTR;
   END IF;
END;
/