CREATE OR REPLACE PROCEDURE NUTRIENTE_DO_PRATO_PLA
  (P_FILIAL       IN VARCHAR2,
   P_SERVICOS     IN VARCHAR2,
   P_NUTRIENTES   IN VARCHAR2,
   P_NRSEQUNUTR   IN VARCHAR2,
   P_IDNDPCAL     IN VARCHAR2,
   P_CDPRATOINI   IN VARCHAR2,
   P_CDPRATOFIN   IN VARCHAR2) 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);
   INSERT INTO NUTRTRAB
      (NRSEQUNUTR,
       CDFILINUTR,
       CDSERVNUTR,
       DTREFENUTR,
       CDDIETNUTR,
       NRSEQPRATNUT,
       CDTPPRATNUTR,
       CDFAPRATNUTR,
       CDPRATNUTR,
       CDPRODNUTR,
       CDNUTRCALC,
       QTPRODNUTR,
       QTNUTRCALC,
       QTPROTLIQU,
       VRCALORNUTR,
       VRPECONSNUTR,
       NRORG,
       NRORGINCLUSAO,
       NRORGULTATU)
   SELECT
      P_NRSEQUNUTR,
      SE.CDFILIAL,
      SE.CDSERVFILI,
      '01/01/01',
      'XXXX',
      0,
      'XX',
      R.CDFILAUXPRAT,
      R.CDPRATO,
      R.CDPRODUTO,
      NP.CDNUTRIENTE,
      R.QTCALCNUTR AS QTPRODUTO,
      NP.QTNUTRPROD * 10 * NVL(DECODE(PRODFILI.VRPESOUNIDFI, 0, R.VRPESOUNID, PRODFILI.VRPESOUNIDFI), R.VRPESOUNID) * R.QTCALCNUTR,
      0,
      0,
      100,V_NRORG,V_NRORG,V_NRORG
   FROM
      SERVICO SE, NUTRPROD NP, PRODFILI, PRATO P,
       TABLE (RECEITA_UTIL(P_FILIAL, SE.CDSERVFILI, P.CDPRATO, P.CDFILAUXPRAT,'0', 'Z', P.CDPRATO,0,0,'N') ) R
   WHERE (SE.CDFILIAL = P_FILIAL) 
    AND (INSTR(P_SERVICOS, SE.CDSERVFILI) <> 0)                    
    AND  (P.CDPRATO BETWEEN P_CDPRATOINI AND P_CDPRATOFIN)
    AND  (P.CDFILAUXPRAT = 'P' OR P.CDFILAUXPRAT = SE.CDFILIAL)
    AND  (R.CDPRATOPAI = P.CDPRATO)                                  
    AND  (R.CDFILAUXPRAT = P.CDFILAUXPRAT)
    AND  (NP.CDPRODUTO = R.CDPRODUTO)  
    AND  ((INSTR(P_NUTRIENTES, NP.CDNUTRIENTE) <> 0) OR (P_NUTRIENTES IS NULL))    
    AND  (PRODFILI.CDFILIAL = SE.CDFILIAL)                         
    AND  (PRODFILI.CDPRODUTO = R.CDPRODUTO);

   /* ------------------------- 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;
      UPDATE
         NUTRTRAB
      SET
         VRCALORNUTR = QTNUTRCALC
      WHERE
         CDNUTRCALC = (SELECT MAX(CDCALORIAS) FROM PARAMGERAL) AND
         NRSEQUNUTR = P_NRSEQUNUTR;

   END IF;
END;
/