DROP FUNCTION PRATOS_MYMENU
/

DROP TYPE PRATOS_MYMENU_TABLE
/

DROP TYPE PRATOS_MYMENU_TYPE
/

CREATE TYPE PRATOS_MYMENU_TYPE AS OBJECT
  (
   NRGRUPOPRATOPESQ NUMBER,
   NMGRUPOPRATOPESQ VARCHAR2(40),
   NRCARDPME        VARCHAR2(10),
   NRCARDAPIO       VARCHAR2(10),
   CDTIPOPRAT       VARCHAR2(2),
   NMTIPOPRAT       VARCHAR2(40),
   CDFILAUXPRAT     VARCHAR2(04),
   CDPRATO          VARCHAR2(10),
   NMPRATO          VARCHAR2(80),
   NRSEQPRATCAR     NUMBER(3),
   NRORDPRATCAR     NUMBER(15),
   CALORIAS_KCAL    FLOAT(126),
   URL_FOTOPRATO    VARCHAR2(2000),
   BLOB_FOTOPRATO   BLOB,
   NRFOTOPRATOBLOB  NUMBER,
   VRAVALIACAO      FLOAT(126),
   QTAVALIACAO      NUMBER,
   IDTIPORECEITA    VARCHAR2(1),
   DSPRATO          VARCHAR2(250),
   IDGLUTEN         VARCHAR2(1),
   IDLACTOSE        VARCHAR2(1),
   IDCONCNFMES      VARCHAR2(1),
   IDRESERVAMYMENU  VARCHAR2(1),
   CDMEDICASE       VARCHAR2(5),
   NMMEDICASE       VARCHAR2(40),
   QTMEDICASE       FLOAT(126)
  )
/


CREATE TYPE PRATOS_MYMENU_TABLE AS TABLE OF PRATOS_MYMENU_TYPE
/


create or replace FUNCTION PRATOS_MYMENU 
(
    P_CDFILCENTPME IN FILIAL.CDFILIAL%TYPE,
    P_CDTIPOCARD IN TIPOCARD.CDTIPOCARD%TYPE,
    P_CDFILIAL IN FILIAL.CDFILIAL%TYPE,
    P_CDSERVFILI IN SERVICO.CDSERVFILI%TYPE,
    P_IDTIPOSERV IN VARCHAR2,
    P_CDDIETPAD IN DIETPADR.CDDIETPAD%TYPE,
    P_DTCARDAPIO IN DATE,
    P_NRORG IN NUMBER,
    P_NRLOCALPESQ IN NUMBER DEFAULT NULL
)
RETURN PRATOS_MYMENU_TABLE PIPELINED IS
OUT_RECORD PRATOS_MYMENU_TYPE := PRATOS_MYMENU_TYPE
(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
V_NRCARDAPIO       VARCHAR2(10);
V_URL_FOTOPRATO    VARCHAR2(2000);
V_BLOB_FOTOPRATO   BLOB;
V_NRFOTOPRATOBLOB  NUMBER;
V_VRAVALIACAO      FLOAT(126);
V_QTAVALIACAO      NUMBER;
V_NMFANTASIA       VARCHAR2(80);
V_NRCARDIAPME      VARCHAR2(10);
V_NRCARDPME        VARCHAR2(10);
V_DSPRATO          VARCHAR2(250);
V_NRGLUTEN         NUMBER;
V_NRLACTOSE        NUMBER;
V_IDGLUTEN         VARCHAR2(1);
V_IDLACTOSE        VARCHAR2(1);
V_CDMEDICASE       VARCHAR2(5);
V_NMMEDICASE       VARCHAR2(40);
V_QTMEDICASE       FLOAT(126);

BEGIN
   SELECT NRGLUTEN, NRLACTOSE
     INTO V_NRGLUTEN, V_NRLACTOSE
     FROM PARAMGERAL; 
   /*SE O SERVICO FOR DE MERENDA ENTAO BUSCA OS DADOS DOS CARDAPIOS PARA MERENDA*/
   IF P_IDTIPOSERV = 'M' THEN      
      /*RETORNA O NRO DO CARDAPIO DA FILIAL (OBS: RETORNA APENAS SE O CARDAPIO FOR ESPECIFICO PARA A FILIAL AGREGADA)*/
      BEGIN
         SELECT C.NRCARDPME, CD.NRCARDIAPME 
           INTO V_NRCARDPME, V_NRCARDIAPME 
           FROM CARDAPIOPME C 
              INNER JOIN CARDIAPME CD 
                 ON C.NRORG = CD.NRORG AND 
                    CD.CDFILCENTPME = C.CDFILCENTPME AND 
                    C.NRCARDPME = CD.NRCARDPME AND
                    CD.CDFILIAL = P_CDFILIAL AND
                    TRUNC(TO_DATE(P_DTCARDAPIO)) = CD.DTCARDIAPME AND
                    CD.CDSERVFILI = P_CDSERVFILI
          WHERE C.CDFILCENTPME = P_CDFILCENTPME AND
                C.CDTIPOCARD = P_CDTIPOCARD  AND EXISTS (SELECT CDPRATO 
                                                            FROM COMPCARDPME CP 
                                                           WHERE CP.CDFILCENTPME = P_CDFILCENTPME AND 
                                                                 CP.NRCARDPME = C.NRCARDPME AND 
                                                                 CP.NRCARDIAPME = CD.NRCARDIAPME);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NRCARDIAPME := NULL;
      END;
      /*SE NAO EXISTE CARDAPIO ESPECIFICO DA FILIAL BUSCA OS DADOS DO CARDAPIO DA CENTRAL*/
      IF V_NRCARDIAPME IS NULL THEN
         /*RETORNA O NRO DO CARDAPIO DA CENTRAL*/
         BEGIN
            SELECT C.NRCARDPME, CD.NRCARDIAPME 
              INTO V_NRCARDPME, V_NRCARDIAPME 
              FROM CARDAPIOPME C 
                 INNER JOIN CARDIAPME CD 
                    ON C.NRORG = CD.NRORG AND 
                       CD.CDFILCENTPME = C.CDFILCENTPME AND 
                       C.NRCARDPME = CD.NRCARDPME AND
                       CD.CDFILIAL = P_CDFILCENTPME AND
                       TRUNC(TO_DATE(P_DTCARDAPIO)) = CD.DTCARDIAPME AND
                       CD.CDSERVFILI = P_CDSERVFILI
             WHERE C.CDFILCENTPME = P_CDFILCENTPME AND
                   C.CDTIPOCARD = P_CDTIPOCARD;   
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRCARDPME := NULL;
               V_NRCARDIAPME := NULL;
         END;
      END IF;
      IF V_NRCARDIAPME IS NOT NULL THEN
      /*SE ENCONTROU CARDAPIO ENTAO BUSCA SEUS PRATOS*/
         FOR C_PRATOS_PME IN (
            SELECT COMP.NRGRUPOPRATOPESQ AS NRGRUPOPRATOPESQ,
                   TP.NMGRUPOPRATOPESQ,
                   TPR.CDTIPOPRAT,
                   TPR.NMTIPOPRAT,
                   COMP.CDFILAUXPRAT,
                   COMP.CDPRATO,
                   PR.NMPRATO,
                   COMP.NRSEQPRACPME AS NRSEQPRATCAR,
                   COMP.NRORDPRACPME AS NRORDPRATCAR,
                   ROUND(NVL(SUM(NT.VRCALORNUTR),0),2)AS CALORIAS_KCAL,
                   RECEITA_UTILIZADA(COMP.CDPRATO,COMP.CDFILAUXPRAT,P_CDFILIAL, P_CDSERVFILI,P_CDFILCENTPME, P_CDTIPOCARD) AS IDTIPORECEITA,
                   'N' AS IDCONCNFMES
                   /*RECEITA_UTILIZADA(COMP.CDPRATO,COMP.CDFILAUXPRAT,P_CDFILIAL, P_CDSERVFILI) AS IDTIPORECEITA*/
              FROM     
                  (SELECT NVL(CPP.NRSEQPRACPME, CP.NRSEQPRACPME) AS NRSEQPRACPME, 
                         NVL(CPP.NRORDPRACPME, CP.NRORDPRACPME) AS NRORDPRACPME, 
                         NVL(CPP.CDTIPOPRAT, CP.CDTIPOPRAT) AS CDTIPOPRAT, 
                         NVL(CPP.CDFILAUXPRAT,CP.CDFILAUXPRAT) AS CDFILAUXPRAT, 
                         NVL(CPP.CDPRATO,CP.CDPRATO) AS CDPRATO, 
                         CPP.NRGRUPOPRATOPESQ
                    FROM COMPCARDPME CP INNER JOIN CONFCARDPME CNF ON CNF.CDFILCENTPME = P_CDFILCENTPME AND 
                                                                      CNF.NRCARDPME = V_NRCARDPME AND 
                                                                      CNF.CDSERVFILI = P_CDSERVFILI AND
                                                                      CNF.NRSEQCNFCPME = CP.NRSEQPRACPME AND
                                                                      CNF.CDTIPOPRAT = CP.CDTIPOPRAT 
                                        LEFT JOIN COMPCARDPESQPME CPP ON CPP.CDFILCENTPME = P_CDFILCENTPME AND
                                                                         CPP.NRCARDPME = V_NRCARDPME AND   
                                                                         CPP.NRCARDIAPME = CP.NRCARDIAPME AND
                                                                         CPP.NRSEQPRACPME = CP.NRSEQPRACPME
                   WHERE CP.CDFILCENTPME = P_CDFILCENTPME AND
                         CP.NRCARDPME = V_NRCARDPME AND
                         CP.NRCARDIAPME = V_NRCARDIAPME AND
                         NVL(CPP.IDEXIBEPRATOPESQ, NVL(CNF.IDIMPCNFCPME, 'S')) = 'S'      
                  UNION ALL 
                  SELECT NRSEQPRACPME, NRORDPRACPME, CDTIPOPRAT, CDFILAUXPRAT, CDPRATO, NRGRUPOPRATOPESQ
                    FROM COMPCARDPESQPME
                   WHERE CDFILCENTPME = P_CDFILCENTPME AND 
                         NRCARDPME = V_NRCARDPME AND 
                         NRCARDIAPME = V_NRCARDIAPME AND 
                         NRSEQPRACPME IS NULL AND 
                         IDEXIBEPRATOPESQ = 'S'
                   ) COMP
                   LEFT JOIN GRUPOPRATOPESQ TP ON TP.NRGRUPOPRATOPESQ = COMP.NRGRUPOPRATOPESQ AND TP.NRORG = P_NRORG
                   INNER JOIN PRATO PR ON PR.CDPRATO = COMP.CDPRATO AND PR.CDFILAUXPRAT = COMP.CDFILAUXPRAT
                   INNER JOIN TIPOPRAT TPR ON COMP.CDTIPOPRAT = TPR.CDTIPOPRAT 
                   LEFT JOIN TABLE (NUTRIENTE_PRATO_PME(P_CDFILCENTPME, P_CDTIPOCARD, P_CDFILIAL,P_CDSERVFILI,NULL,'S',COMP.CDPRATO,COMP.CDPRATO)) NT
                      ON (NT.CDFAPRATNUTR = COMP.CDFILAUXPRAT AND NT.CDPRATNUTR = COMP.CDPRATO) 
            GROUP BY COMP.NRGRUPOPRATOPESQ,
                     TP.NMGRUPOPRATOPESQ,
                     TPR.CDTIPOPRAT,
                     TPR.NMTIPOPRAT,
                     COMP.CDFILAUXPRAT,
                     COMP.CDPRATO,
                     PR.NMPRATO,
                     COMP.NRSEQPRACPME,
                     COMP.NRORDPRACPME
            ORDER BY COMP.NRGRUPOPRATOPESQ, COMP.NRORDPRACPME
         ) LOOP
            /*BUSCA O NOME FANTASIA DO PRATO*/
            IF C_PRATOS_PME.IDTIPORECEITA = 'S' THEN
               BEGIN
                  SELECT NMFANTASIASE, DSPRATO
                    INTO V_NMFANTASIA, V_DSPRATO
                    FROM PRATSERV
                   WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                         CDPRATO = C_PRATOS_PME.CDPRATO AND
                         CDFILIAL = P_CDFILIAL AND
                         CDSERVFILI = P_CDSERVFILI;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS_PME.NMPRATO;
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RS.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECESERV RS INNER JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO
             WHERE RS.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   RS.CDFILIAL = P_CDFILIAL AND
                   RS.CDSERVFILI = P_CDSERVFILI AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RS.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECESERV RS INNER JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO
             WHERE RS.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   RS.CDFILIAL = P_CDFILIAL AND
                   RS.CDSERVFILI = P_CDSERVFILI AND
                   A.NRALERGENICO = V_NRGLUTEN;                        
            END IF; 
            IF C_PRATOS_PME.IDTIPORECEITA = 'F' THEN
               BEGIN
                  SELECT NMFANTASIAPF, DSPRATO
                    INTO V_NMFANTASIA, V_DSPRATO
                    FROM PRATFILI
                   WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                         CDPRATO = C_PRATOS_PME.CDPRATO AND
                         CDFILIAL = P_CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS_PME.NMPRATO;
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RF.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECEFILI RF INNER JOIN ALERGENICOPROD A ON RF.CDPRODUTO = A.CDPRODUTO
             WHERE RF.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RF.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   RF.CDFILIAL = P_CDFILIAL AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RF.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECEFILI RF INNER JOIN ALERGENICOPROD A ON RF.CDPRODUTO = A.CDPRODUTO
             WHERE RF.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RF.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   RF.CDFILIAL = P_CDFILIAL AND
                   A.NRALERGENICO = V_NRGLUTEN;  
            END IF;
            IF C_PRATOS_PME.IDTIPORECEITA = 'T' THEN
               BEGIN
                  SELECT NMFANTASIAPRTP, DSPRATO
                    INTO V_NMFANTASIA, V_DSPRATO
                    FROM PRATTPCARD
                   WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                         CDPRATO = C_PRATOS_PME.CDPRATO AND
                         CDFILCENTPME = P_CDFILCENTPME AND 
                         CDTIPOCARD =  P_CDTIPOCARD;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS_PME.NMPRATO;
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RT.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECETPCARD RT INNER JOIN ALERGENICOPROD A ON RT.CDPRODUTO = A.CDPRODUTO
             WHERE RT.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RT.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   RT.CDFILCENTPME = P_CDFILCENTPME AND 
                   RT.CDTIPOCARD =  P_CDTIPOCARD AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RT.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECETPCARD RT INNER JOIN ALERGENICOPROD A ON RT.CDPRODUTO = A.CDPRODUTO
             WHERE RT.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RT.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   RT.CDFILCENTPME = P_CDFILCENTPME AND 
                   RT.CDTIPOCARD =  P_CDTIPOCARD AND
                   A.NRALERGENICO = V_NRGLUTEN;  
            END IF;
            IF C_PRATOS_PME.IDTIPORECEITA = 'P' THEN
               BEGIN
                  SELECT NMFANTASIAPR, DSPRATO
                    INTO V_NMFANTASIA, V_DSPRATO
                    FROM PRATPADR
                   WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                         CDPRATO = C_PRATOS_PME.CDPRATO;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS_PME.NMPRATO;
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RP.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECEPADR RP INNER JOIN ALERGENICOPROD A ON RP.CDPRODUTO = A.CDPRODUTO
             WHERE RP.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RP.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RP.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECEPADR RP INNER JOIN ALERGENICOPROD A ON RP.CDPRODUTO = A.CDPRODUTO
             WHERE RP.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                   RP.CDPRATO = C_PRATOS_PME.CDPRATO AND
                   A.NRALERGENICO = V_NRGLUTEN;                 
            END IF;
            /*BUSCA A FOTO DO PRATO*/
            BEGIN
              SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                INTO V_BLOB_FOTOPRATO, V_NRFOTOPRATOBLOB
                FROM
              (
                SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                  FROM FOTOPRATOBLOB S
                 WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT
                   AND CDPRATO = C_PRATOS_PME.CDPRATO
                   AND CDFILIAL = P_CDFILIAL
                   AND CDSERVFILI = P_CDSERVFILI                 
                   AND IDRECEITA = 'S'
                 UNION ALL
                SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                  FROM FOTOPRATOBLOB F
                 WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT
                   AND CDPRATO = C_PRATOS_PME.CDPRATO
                   AND CDFILIAL = P_CDFILIAL
                   AND IDRECEITA = 'F'
                   AND NOT EXISTS
                   (SELECT 1 FROM FOTOPRATOBLOB S
                     WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT
                       AND CDPRATO = C_PRATOS_PME.CDPRATO
                       AND CDFILIAL = P_CDFILIAL
                       AND CDSERVFILI = P_CDSERVFILI
                       AND IDRECEITA = 'S')
                 UNION ALL
                SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                  FROM FOTOPRATOBLOB P
                 WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT
                   AND CDPRATO = C_PRATOS_PME.CDPRATO
                   AND IDRECEITA = 'P'
                   AND NOT EXISTS
                   (SELECT 1 FROM FOTOPRATOBLOB S
                     WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT
                       AND CDPRATO = C_PRATOS_PME.CDPRATO
                       AND CDFILIAL = P_CDFILIAL
                       AND CDSERVFILI = P_CDSERVFILI
                       AND IDRECEITA = 'S')
                   AND NOT EXISTS
                   (SELECT 1 FROM FOTOPRATOBLOB S
                     WHERE CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT
                       AND CDPRATO = C_PRATOS_PME.CDPRATO
                       AND CDFILIAL = P_CDFILIAL                     
                       AND IDRECEITA = 'F')
              );
           EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_BLOB_FOTOPRATO := NULL;
                V_NRFOTOPRATOBLOB := NULL;
           END;
            V_URL_FOTOPRATO := NULL;
            /*TO DO  SELECT PARA BUSCAR QUANTIDADE DE AVALIACOES*/
            BEGIN
               SELECT ROUND(AVG(VRAVALIACAOPRATO),2), COUNT(VRAVALIACAOPRATO)
                 INTO V_VRAVALIACAO, V_QTAVALIACAO
                 FROM AVALIACAOPRATO AV
                WHERE AV.CDFILAUXPRAT = C_PRATOS_PME.CDFILAUXPRAT AND
                      AV.CDPRATO      = C_PRATOS_PME.CDPRATO AND
                     (AV.NRLOCALPESQ  = P_NRLOCALPESQ OR P_NRLOCALPESQ IS NULL);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_VRAVALIACAO := NULL; 
                  V_QTAVALIACAO := 0;
            END;
            /*INSERIR NA TABELA DE RETORNO OS DADOS DOS PRATOS*/
            OUT_RECORD.NRGRUPOPRATOPESQ := C_PRATOS_PME.NRGRUPOPRATOPESQ; 
            OUT_RECORD.NMGRUPOPRATOPESQ := C_PRATOS_PME.NMGRUPOPRATOPESQ;
            OUT_RECORD.NRCARDPME        := V_NRCARDPME;
            OUT_RECORD.NRCARDAPIO       := V_NRCARDIAPME;
            OUT_RECORD.NMTIPOPRAT       := C_PRATOS_PME.NMTIPOPRAT;
            OUT_RECORD.CDFILAUXPRAT     := C_PRATOS_PME.CDFILAUXPRAT;
            OUT_RECORD.CDPRATO          := C_PRATOS_PME.CDPRATO;
            OUT_RECORD.NMPRATO          := NVL(V_NMFANTASIA, C_PRATOS_PME.NMPRATO);
            OUT_RECORD.NRSEQPRATCAR     := C_PRATOS_PME.NRSEQPRATCAR;
            OUT_RECORD.NRORDPRATCAR     := C_PRATOS_PME.NRORDPRATCAR;
            OUT_RECORD.CALORIAS_KCAL    := C_PRATOS_PME.CALORIAS_KCAL;
            OUT_RECORD.URL_FOTOPRATO    := V_URL_FOTOPRATO;
            OUT_RECORD.BLOB_FOTOPRATO   := V_BLOB_FOTOPRATO;
            OUT_RECORD.NRFOTOPRATOBLOB  := V_NRFOTOPRATOBLOB;
            OUT_RECORD.VRAVALIACAO      := V_VRAVALIACAO;
            OUT_RECORD.QTAVALIACAO      := V_QTAVALIACAO;
            OUT_RECORD.IDTIPORECEITA    := C_PRATOS_PME.IDTIPORECEITA;
            OUT_RECORD.DSPRATO          := V_DSPRATO;
            OUT_RECORD.IDGLUTEN         := V_IDGLUTEN;
            OUT_RECORD.IDLACTOSE        := V_IDLACTOSE;
            OUT_RECORD.IDCONCNFMES      := C_PRATOS_PME.IDCONCNFMES;
            OUT_RECORD.IDRESERVAMYMENU  := NULL;
            OUT_RECORD.CDMEDICASE       := NULL;
            OUT_RECORD.NMMEDICASE       := NULL;
            OUT_RECORD.QTMEDICASE       := NULL;
            PIPE ROW (OUT_RECORD);
         END LOOP;
      END IF;
   ELSE
   /*SE O SERVICO FOR DE RESTAURANTE OU HOSPITALAR ENTAO BUSCA OS DADOS DE PRATOS PARA RESTAURANTE OU HOSPITALAR*/
      IF P_CDDIETPAD IS NULL THEN      
         BEGIN
            SELECT MAX(NRCARDAPIO) INTO
               V_NRCARDAPIO
              FROM CARDADIA
             WHERE CDFILIAL   = P_CDFILIAL   AND
                   CDSERVFILI = P_CDSERVFILI AND
                   DTCARDAPIO = P_DTCARDAPIO AND
                   NRORG      = P_NRORG;  
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRCARDAPIO := '0';
         END;
      ELSE
         BEGIN
            SELECT MAX(NRCARDAPIO) INTO
               V_NRCARDAPIO
              FROM CARDADIA
             WHERE CDFILIAL   = P_CDFILIAL   AND
                   CDSERVFILI = P_CDSERVFILI AND
                   DTCARDAPIO = P_DTCARDAPIO AND
                   CDDIETPAD  = P_CDDIETPAD  AND
                   NRORG      = P_NRORG;  
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NRCARDAPIO := '0';
         END;
      END IF;
      FOR C_PRATOS IN (
            SELECT COMP.NRGRUPOPRATOPESQ,
                   TP.NMGRUPOPRATOPESQ,
                   TPR.CDTIPOPRAT,
                   TPR.NMTIPOPRAT || CASE WHEN COMP.IDOPCCNFMES = 'S' THEN ' (Opcao)' ELSE '' END AS NMTIPOPRAT,
                   COMP.CDFILAUXPRAT,
                   COMP.CDPRATO,
                   PR.NMPRATO,
                   COMP.NRSEQPRATCAR,
                   COMP.NRORDPRATCAR,
                   ROUND(NVL(SUM(NT.VRCALORNUTR),0),2)AS CALORIAS_KCAL,
                   RECEITA_UTILIZADA(COMP.CDPRATO,COMP.CDFILAUXPRAT,P_CDFILIAL, P_CDSERVFILI) AS IDTIPORECEITA,
                   COMP.IDCONCNFMES, COMP.IDRESERVAMYMENU
              FROM (SELECT NVL(CPP.NRSEQPRATCAR, CP.NRSEQPRATCAR) AS NRSEQPRATCAR,
                           NVL(CPP.NRORDPRATCAR, CP.NRORDPRATCAR) AS NRORDPRATCAR, 
                           NVL(CPP.CDFILAUXPRAT, CP.CDFILAUXPRAT) AS CDFILAUXPRAT, 
                           NVL(CPP.CDPRATO,CP.CDPRATO) AS CDPRATO, CPP.NRGRUPOPRATOPESQ, NVL(CPP.CDTIPOPRAT, CP.CDTIPOPRAT) AS CDTIPOPRAT, 
                           IDOPCCNFMES, IDCONCNFMES, NVL(CPP.IDRESERVAMYMENU, CNF.IDRESERVAMYMENU) AS IDRESERVAMYMENU
                      FROM COMPCARD CP INNER JOIN CNFCAMES CNF ON (CNF.CDFILIAL = CP.CDFILIAL AND CNF.NRSEQCNFMES = CP.NRSEQPRATCAR AND 
                                                                   CNF.CDSERVFILI = P_CDSERVFILI AND CNF.DTREFECNFMES = TRUNC(TO_DATE(P_DTCARDAPIO), 'MM') AND 
                                                                   CNF.NRORG  = P_NRORG)
                                       LEFT JOIN COMPCARDPESQ CPP ON(CP.CDFILIAL = CPP.CDFILIAL AND CP.NRCARDAPIO = CPP.NRCARDAPIO AND 
                                                                     CP.NRSEQPRATCAR = CPP.NRSEQPRATCAR AND CP.NRORG = CPP.NRORG)
                     WHERE CP.CDFILIAL = P_CDFILIAL AND CP.NRCARDAPIO = V_NRCARDAPIO AND NVL(IDEXIBEPRATOPESQ, NVL(CNF.IDIMPCNFMES, 'S')) = 'S'
                    UNION ALL 
                    SELECT CPQ.NRSEQPRATCAR, CPQ.NRORDPRATCAR, CPQ.CDFILAUXPRAT, CPQ.CDPRATO, CPQ.NRGRUPOPRATOPESQ, CPQ.CDTIPOPRAT, 'N' AS IDOPCCNFMES,
                           (SELECT NVL(MAX(IDCONCNFMES),'N')
                              FROM CNFCAMES CN
                             WHERE CN.CDFILIAL = CPQ.CDFILIAL AND
                                   CN.CDSERVFILI = P_CDSERVFILI AND
                                   CN.CDTIPOPRAT = CPQ.CDTIPOPRAT AND 
                                   CN.DTREFECNFMES = TRUNC(TO_DATE(P_DTCARDAPIO), 'MM')) AS IDCONCNFMES, CPQ.IDRESERVAMYMENU
                      FROM COMPCARDPESQ CPQ
                     WHERE CDFILIAL = P_CDFILIAL AND NRCARDAPIO = V_NRCARDAPIO AND NRSEQPRATCAR IS NULL  AND IDEXIBEPRATOPESQ = 'S'
                   ) COMP
                   LEFT JOIN GRUPOPRATOPESQ TP ON TP.NRGRUPOPRATOPESQ = COMP.NRGRUPOPRATOPESQ AND TP.NRORG = P_NRORG
                   INNER JOIN PRATO PR ON PR.CDPRATO = COMP.CDPRATO AND PR.CDFILAUXPRAT = COMP.CDFILAUXPRAT
                   INNER JOIN TIPOPRAT TPR ON COMP.CDTIPOPRAT = TPR.CDTIPOPRAT 
                   LEFT JOIN TABLE (NUTRIENTE_PRATO(P_CDFILIAL,P_CDSERVFILI,NULL,'S',COMP.CDPRATO,COMP.CDPRATO)) NT
                      ON (NT.CDFILINUTR = P_CDFILIAL AND NT.CDSERVNUTR = P_CDSERVFILI AND NT.CDFAPRATNUTR = COMP.CDFILAUXPRAT AND NT.CDPRATNUTR = COMP.CDPRATO)
            GROUP BY COMP.NRGRUPOPRATOPESQ,
                     TP.NMGRUPOPRATOPESQ,
                     TPR.CDTIPOPRAT,
                     TPR.NMTIPOPRAT || CASE WHEN COMP.IDOPCCNFMES = 'S' THEN ' (Opcao)' ELSE '' END,
                     COMP.CDFILAUXPRAT,
                     COMP.CDPRATO,
                     PR.NMPRATO,
                     COMP.NRSEQPRATCAR,
                     COMP.NRORDPRATCAR,
                     COMP.IDCONCNFMES,
                     COMP.IDRESERVAMYMENU
            ORDER BY COMP.NRGRUPOPRATOPESQ, TO_NUMBER(NVL(COMP.NRORDPRATCAR,9999))
      ) LOOP
         IF C_PRATOS.IDTIPORECEITA IS NULL THEN
            V_NMFANTASIA := C_PRATOS.NMPRATO;
         ELSE
            /*BUSCA O NOME FANTASIA DO PRATO E SUA MEDIDA CASEIRA*/
            IF C_PRATOS.IDTIPORECEITA = 'S' THEN
               BEGIN
                  SELECT PS.NMFANTASIASE, PS.DSPRATO, PS.CDMEDICASE, M.NMMEDICASE, PS.QTMEDICASEPRATSERV AS QTMEDICASE
                    INTO V_NMFANTASIA, V_DSPRATO, V_CDMEDICASE, V_NMMEDICASE, V_QTMEDICASE
                    FROM PRATSERV PS LEFT JOIN MEDICASE M ON PS.CDMEDICASE = M.CDMEDICASE
                   WHERE PS.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                         PS.CDPRATO = C_PRATOS.CDPRATO AND
                         PS.CDFILIAL = P_CDFILIAL AND
                         PS.CDSERVFILI = P_CDSERVFILI;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS.NMPRATO;
                     V_DSPRATO := NULL;
                     V_CDMEDICASE := NULL;
                     V_NMMEDICASE := NULL;
                     V_QTMEDICASE := NULL;
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RS.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECESERV RS INNER JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO
             WHERE RS.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS.CDPRATO AND
                   RS.CDFILIAL = P_CDFILIAL AND
                   RS.CDSERVFILI = P_CDSERVFILI AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RS.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECESERV RS INNER JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO
             WHERE RS.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS.CDPRATO AND
                   RS.CDFILIAL = P_CDFILIAL AND
                   RS.CDSERVFILI = P_CDSERVFILI AND
                   A.NRALERGENICO = V_NRGLUTEN;      
            END IF; 
            IF C_PRATOS.IDTIPORECEITA = 'F' THEN
               BEGIN
                  SELECT PF.NMFANTASIAPF, PF.DSPRATO, PF.CDMEDICASE, M.NMMEDICASE, PF.QTMEDICASEPRATFILI AS QTMEDICASE
                    INTO V_NMFANTASIA, V_DSPRATO, V_CDMEDICASE, V_NMMEDICASE, V_QTMEDICASE
                    FROM PRATFILI PF LEFT JOIN MEDICASE M ON PF.CDMEDICASE = M.CDMEDICASE
                   WHERE PF.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                         PF.CDPRATO = C_PRATOS.CDPRATO AND
                         PF.CDFILIAL = P_CDFILIAL;                       
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS.NMPRATO;
                     V_DSPRATO := NULL;
                     V_CDMEDICASE := NULL;
                     V_NMMEDICASE := NULL;
                     V_QTMEDICASE := NULL;                     
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RF.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECEFILI RF INNER JOIN ALERGENICOPROD A ON RF.CDPRODUTO = A.CDPRODUTO
             WHERE RF.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RF.CDPRATO = C_PRATOS.CDPRATO AND
                   RF.CDFILIAL = P_CDFILIAL AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RF.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECEFILI RF INNER JOIN ALERGENICOPROD A ON RF.CDPRODUTO = A.CDPRODUTO
             WHERE RF.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RF.CDPRATO = C_PRATOS.CDPRATO AND
                   RF.CDFILIAL = P_CDFILIAL AND
                   A.NRALERGENICO = V_NRGLUTEN;                 
            END IF;
            IF C_PRATOS.IDTIPORECEITA = 'P' THEN
               BEGIN
                  SELECT PP.NMFANTASIAPR, PP.DSPRATO, PP.CDMEDICASE, M.NMMEDICASE, PP.QTMEDICASEPRATPADR AS QTMEDICASE
                    INTO V_NMFANTASIA, V_DSPRATO, V_CDMEDICASE, V_NMMEDICASE, V_QTMEDICASE
                    FROM PRATPADR PP LEFT JOIN MEDICASE M ON PP.CDMEDICASE = M.CDMEDICASE
                   WHERE PP.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                         PP.CDPRATO = C_PRATOS.CDPRATO;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_NMFANTASIA := C_PRATOS.NMPRATO;
                     V_DSPRATO := NULL;
                     V_CDMEDICASE := NULL;
                     V_NMMEDICASE := NULL;
                     V_QTMEDICASE := NULL;                      
               END;
            /*BUSCA SE O PRATO TEM LACTOSE*/
            SELECT CASE WHEN MAX(RP.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDLACTOSE
              FROM RECEPADR RP INNER JOIN ALERGENICOPROD A ON RP.CDPRODUTO = A.CDPRODUTO
             WHERE RP.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RP.CDPRATO = C_PRATOS.CDPRATO AND
                   A.NRALERGENICO = V_NRLACTOSE;            
            /*BUSCA SE O PRATO TEM GLUTEN*/       
            SELECT CASE WHEN MAX(RP.CDPRODUTO) IS NULL THEN 'N' ELSE 'S' END
              INTO V_IDGLUTEN
              FROM RECEPADR RP INNER JOIN ALERGENICOPROD A ON RP.CDPRODUTO = A.CDPRODUTO
             WHERE RP.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RP.CDPRATO = C_PRATOS.CDPRATO AND
                   A.NRALERGENICO = V_NRGLUTEN;         
            END IF;
         END IF;         
         /*BUSCA A FOTO DO PRATO*/
         BEGIN
            SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
              INTO V_BLOB_FOTOPRATO, V_NRFOTOPRATOBLOB
              FROM
            (
              SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                FROM FOTOPRATOBLOB S
               WHERE CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT
                 AND CDPRATO = C_PRATOS.CDPRATO
                 AND CDFILIAL = P_CDFILIAL
                 AND CDSERVFILI = P_CDSERVFILI                 
                 AND IDRECEITA = 'S'
               UNION ALL
              SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                FROM FOTOPRATOBLOB F
               WHERE CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT
                 AND CDPRATO = C_PRATOS.CDPRATO
                 AND CDFILIAL = P_CDFILIAL
                 AND IDRECEITA = 'F'
                 AND NOT EXISTS
                 (SELECT 1 FROM FOTOPRATOBLOB S
                   WHERE CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT
                     AND CDPRATO = C_PRATOS.CDPRATO
                     AND CDFILIAL = P_CDFILIAL
                     AND CDSERVFILI = P_CDSERVFILI
                     AND IDRECEITA = 'S')
               UNION ALL 
              SELECT FGFOTOPRATO, NRFOTOPRATOBLOB
                FROM FOTOPRATOBLOB P
               WHERE CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT
                 AND CDPRATO = C_PRATOS.CDPRATO
                 AND IDRECEITA = 'P'
                 AND NOT EXISTS
                 (SELECT 1 FROM FOTOPRATOBLOB S
                   WHERE CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT
                     AND CDPRATO = C_PRATOS.CDPRATO
                     AND CDFILIAL = P_CDFILIAL
                     AND CDSERVFILI = P_CDSERVFILI
                     AND IDRECEITA = 'S')
                 AND NOT EXISTS
                 (SELECT 1 FROM FOTOPRATOBLOB S
                   WHERE CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT
                     AND CDPRATO = C_PRATOS.CDPRATO
                     AND CDFILIAL = P_CDFILIAL                     
                     AND IDRECEITA = 'F')
            );
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_BLOB_FOTOPRATO := NULL;
              V_NRFOTOPRATOBLOB := NULL;
         END;
         V_URL_FOTOPRATO := NULL;
         /*SELECT PARA BUSCAR QUANTIDADE DE AVALIACOES*/
         BEGIN
            SELECT ROUND(AVG(VRAVALIACAOPRATO),2), COUNT(VRAVALIACAOPRATO)
              INTO V_VRAVALIACAO, V_QTAVALIACAO
              FROM AVALIACAOPRATO AV
             WHERE AV.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   AV.CDPRATO      = C_PRATOS.CDPRATO AND
                   (AV.NRLOCALPESQ  = P_NRLOCALPESQ OR P_NRLOCALPESQ IS NULL);
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_VRAVALIACAO := NULL; 
               V_QTAVALIACAO := 0;
         END;
         /*INSERIR NA TABELA DE RETORNO OS DADOS DOS PRATOS*/
         OUT_RECORD.NRGRUPOPRATOPESQ := C_PRATOS.NRGRUPOPRATOPESQ; 
         OUT_RECORD.NMGRUPOPRATOPESQ := C_PRATOS.NMGRUPOPRATOPESQ;
         OUT_RECORD.NRCARDPME        := NULL;
         OUT_RECORD.NRCARDAPIO       := V_NRCARDAPIO;
         OUT_RECORD.CDTIPOPRAT       := C_PRATOS.CDTIPOPRAT;
         OUT_RECORD.NMTIPOPRAT       := C_PRATOS.NMTIPOPRAT;
         OUT_RECORD.CDFILAUXPRAT     := C_PRATOS.CDFILAUXPRAT;
         OUT_RECORD.CDPRATO          := C_PRATOS.CDPRATO;
         OUT_RECORD.NMPRATO          := NVL(V_NMFANTASIA, C_PRATOS.NMPRATO);
         OUT_RECORD.NRSEQPRATCAR     := C_PRATOS.NRSEQPRATCAR;
         OUT_RECORD.NRORDPRATCAR     := C_PRATOS.NRORDPRATCAR;
         OUT_RECORD.CALORIAS_KCAL    := C_PRATOS.CALORIAS_KCAL;
         OUT_RECORD.URL_FOTOPRATO    := V_URL_FOTOPRATO;
         OUT_RECORD.BLOB_FOTOPRATO   := V_BLOB_FOTOPRATO;
         OUT_RECORD.NRFOTOPRATOBLOB  := V_NRFOTOPRATOBLOB;
         OUT_RECORD.VRAVALIACAO      := V_VRAVALIACAO;
         OUT_RECORD.QTAVALIACAO      := V_QTAVALIACAO;
         OUT_RECORD.IDTIPORECEITA    := C_PRATOS.IDTIPORECEITA;
         OUT_RECORD.DSPRATO          := V_DSPRATO;
         OUT_RECORD.IDGLUTEN         := V_IDGLUTEN;
         OUT_RECORD.IDLACTOSE        := V_IDLACTOSE;
         OUT_RECORD.IDCONCNFMES      := C_PRATOS.IDCONCNFMES;
         OUT_RECORD.IDRESERVAMYMENU  := C_PRATOS.IDRESERVAMYMENU;
         OUT_RECORD.CDMEDICASE       := V_CDMEDICASE;
         OUT_RECORD.NMMEDICASE       := V_NMMEDICASE;
         OUT_RECORD.QTMEDICASE       := V_QTMEDICASE;
         PIPE ROW (OUT_RECORD);
      END LOOP;
   END IF;
END;
/