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_NRORG         NUMBER;
BEGIN
   V_NRORG := NVL(SYS_CONTEXT('nrorg_ctx','nrorg'),1);
   FOR C_NUTRTRAB IN (
      SELECT CD.CDFILIAL, CD.CDSERVFILI, CD.DTCARDAPIO,
             SUBSTR(LTRIM(CD.CDDIETPAD||'XXXX'),1,4) AS CDDIETPAD,
             CC.NRSEQPRATCAR, 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, CDPRATO, CDFILAUXPRAT, CDPRODUTO
      ) LOOP
         
      INSERT INTO NUTRTRAB
         (NRSEQUNUTR,
          CDFILINUTR,
          CDSERVNUTR,
          DTREFENUTR,
          CDDIETNUTR,
          NRSEQPRATNUT,
          CDTPPRATNUTR,
          CDFAPRATNUTR,
          CDPRATNUTR,
          CDPRODNUTR,
          CDNUTRCALC,
          QTPRODNUTR,
          QTNUTRCALC,
          QTPROTLIQU,
          VRCALORNUTR,
          VRPECONSNUTR,
          SGUNIDPRODT,
          QTPRODPRATT,
          NRORG,
          NRORGULTATU,
          NRORGINCLUSAO)
      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,
          V_NRORG,
          V_NRORG,
          V_NRORG);

   END LOOP;
   IF P_IDNDPCAL = 'S' THEN
      UPDATE
         NUTRTRAB
      SET
         VRCALORNUTR = QTNUTRCALC
      WHERE
         CDNUTRCALC = (SELECT MAX(CDCALORIAS) FROM PARAMGERAL) AND
         NRSEQUNUTR = P_NRSEQUNUTR;
   END IF;
END;
/
