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,
    P_RETORNAAVALIACAO IN VARCHAR2 DEFAULT 'S'
)
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, NULL);
V_URL_FOTOPRATO    VARCHAR2(2000);
V_BLOB_FOTOPRATO   BLOB;
V_NRFOTOPRATOBLOB  NUMBER;
V_VRAVALIACAO      FLOAT(126);
V_QTAVALIACAO      NUMBER;
V_NMFANTASIA       VARCHAR2(80);
V_DSPRATO          VARCHAR2(250);
V_NRGLUTEN         NUMBER;
V_NRLACTOSE        NUMBER;
V_IDGLUTEN         VARCHAR2(10);
V_IDLACTOSE        VARCHAR2(10);
V_CDCALORIAS       VARCHAR2(3);
V_CALORIAS         FLOAT(126);
V_CDMEDICASE       VARCHAR2(5); 
V_NMMEDICASE       VARCHAR2(40);
V_QTMEDICASE       FLOAT(126);

BEGIN
   SELECT NRGLUTEN, NRLACTOSE, CDCALORIAS
     INTO V_NRGLUTEN, V_NRLACTOSE, V_CDCALORIAS
     FROM PARAMGERAL; 

   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,
           RECEITA_UTILIZADA(COMP.CDPRATO,COMP.CDFILAUXPRAT,P_CDFILIAL,P_CDSERVFILI) AS IDTIPORECEITA,
           COMP.IDCONCNFMES, COMP.IDRESERVAMYMENU, COMP.IDOPCCNFMES,
           COMP.NRCARDAPIO
     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, C.NRCARDAPIO
             FROM COMPCARD CP INNER JOIN CARDADIA C ON C.CDFILIAL = CP.CDFILIAL AND C.NRCARDAPIO = CP.NRCARDAPIO
                              INNER JOIN CNFCAMES CNF ON (CNF.CDFILIAL = CP.CDFILIAL AND CNF.NRSEQCNFMES = CP.NRSEQPRATCAR AND 
                                                          CNF.CDSERVFILI = C.CDSERVFILI AND CNF.DTREFECNFMES = C.DTMESCARD)
                              LEFT JOIN COMPCARDPESQ CPP ON(CP.CDFILIAL = CPP.CDFILIAL AND CP.NRCARDAPIO = CPP.NRCARDAPIO AND 
                                                            CP.NRSEQPRATCAR = CPP.NRSEQPRATCAR)
            WHERE C.NRORG = P_NRORG AND C.CDFILIAL = P_CDFILIAL AND C.CDSERVFILI = P_CDSERVFILI AND C.DTCARDAPIO = P_DTCARDAPIO AND NVL(CPP.IDEXIBEPRATOPESQ, NVL(CNF.IDIMPCNFMES, 'S')) = 'S'
           UNION ALL 
           SELECT CPQ.NRSEQPRATCAR, CPQ.NRORDPRATCAR, CPQ.CDFILAUXPRAT, CPQ.CDPRATO, CPQ.NRGRUPOPRATOPESQ, CPQ.CDTIPOPRAT, 'N' AS IDOPCCNFMES,
                  NVL((SELECT MAX(IDCONCNFMES)
                     FROM CNFCAMES CN
                    WHERE CN.CDFILIAL = CPQ.CDFILIAL AND
                          CN.CDSERVFILI = C.CDSERVFILI AND
                          CN.CDTIPOPRAT = CPQ.CDTIPOPRAT AND 
                          CN.DTREFECNFMES = C.DTMESCARD),'N') AS IDCONCNFMES, CPQ.IDRESERVAMYMENU, C.NRCARDAPIO 
             FROM COMPCARDPESQ CPQ INNER JOIN CARDADIA C ON C.CDFILIAL = CPQ.CDFILIAL AND C.NRCARDAPIO = CPQ.NRCARDAPIO
            WHERE C.CDFILIAL = P_CDFILIAL AND C.CDSERVFILI = P_CDSERVFILI AND C.DTCARDAPIO = P_DTCARDAPIO 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 AND PR.IDATIVO = 'S'
                 INNER JOIN TIPOPRAT TPR ON COMP.CDTIPOPRAT = TPR.CDTIPOPRAT
   ) LOOP

      IF C_PRATOS.IDTIPORECEITA = 'P' THEN   
          BEGIN
             SELECT MAX(PS.NMFANTASIAPR) AS NMFANTASIAPR, MAX(A.CDPRODUTO) AS LACTOSE, MAX(A2.CDPRODUTO) AS GLUTEN, MAX(DSPRATO), MAX(PS.CDMEDICASE), MAX(M.NMMEDICASE), MAX(PS.QTMEDICASEPRATPADR),
                    SUM(NP.QTNUTRPROD * 10 * NVL(DECODE(PF.VRPESOUNIDFI, 0, P.VRPESOUNID, PF.VRPESOUNIDFI),P.VRPESOUNID) * RS.QTCALCNUTRPR) AS QTCALORIAS, MAX(FG.FGFOTOPRATOURL) , NULL AS FGFOTOPRATO, MAX(NRFOTOPRATOBLOB)
               INTO V_NMFANTASIA, V_IDLACTOSE, V_IDGLUTEN, V_DSPRATO, V_CDMEDICASE, V_NMMEDICASE, V_QTMEDICASE, V_CALORIAS, V_URL_FOTOPRATO, V_BLOB_FOTOPRATO, V_NRFOTOPRATOBLOB 
               FROM RECEPADR RS LEFT JOIN PRATPADR PS ON PS.CDFILAUXPRAT = RS.CDFILAUXPRAT AND PS.CDPRATO = RS.CDPRATO
                                INNER JOIN PRODUTO P ON P.CDPRODUTO = RS.CDPRODUTO
                                LEFT JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO AND A.NRALERGENICO = V_NRLACTOSE
                                LEFT JOIN ALERGENICOPROD A2 ON RS.CDPRODUTO = A2.CDPRODUTO AND A2.NRALERGENICO = V_NRGLUTEN
                                LEFT JOIN PRODFILI PF ON PF.CDFILIAL = P_CDFILIAL AND PF.CDPRODUTO = RS.CDPRODUTO
                                LEFT JOIN NUTRPROD NP ON NP.CDPRODUTO = RS.CDPRODUTO AND NP.CDNUTRIENTE = V_CDCALORIAS
                                LEFT JOIN FOTOPRATOBLOB FG ON FG.CDFILAUXPRAT = RS.CDFILAUXPRAT AND FG.CDPRATO = RS.CDPRATO AND IDRECEITA = 'P'
                                LEFT JOIN MEDICASE M ON PS.CDMEDICASE = M.CDMEDICASE
             WHERE RS.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS.CDPRATO;
           EXCEPTION
              WHEN NO_DATA_FOUND THEN
                 V_NMFANTASIA := NULL;
                 V_DSPRATO := NULL;
                 V_CDMEDICASE := NULL; 
                 V_NMMEDICASE := NULL;
                 V_QTMEDICASE := NULL;
                 V_NRLACTOSE :=  NULL;
                 V_NRGLUTEN :=  NULL; 
                 V_CALORIAS :=  NULL;
                 V_URL_FOTOPRATO :=  NULL;
                 V_BLOB_FOTOPRATO :=  NULL;
                 V_NRFOTOPRATOBLOB :=  NULL; 
           END;
      ELSIF C_PRATOS.IDTIPORECEITA = 'F' THEN
          BEGIN
             SELECT MAX(PS.NMFANTASIAPF) AS NMFANTASIAPF, MAX(A.CDPRODUTO) AS LACTOSE, MAX(A2.CDPRODUTO) AS GLUTEN, MAX(DSPRATO), MAX(PS.CDMEDICASE), MAX(M.NMMEDICASE), MAX(PS.QTMEDICASEPRATFILI),
                    SUM(NP.QTNUTRPROD * 10 * NVL(DECODE(PF.VRPESOUNIDFI, 0, P.VRPESOUNID, PF.VRPESOUNIDFI),P.VRPESOUNID) * RS.QTCALCNUTRLO) AS QTCALORIAS, MAX(FG.FGFOTOPRATOURL) , NULL AS FGFOTOPRATO, MAX(NRFOTOPRATOBLOB)
               INTO V_NMFANTASIA, V_IDLACTOSE, V_IDGLUTEN, V_DSPRATO, V_CDMEDICASE, V_NMMEDICASE, V_QTMEDICASE, V_CALORIAS, V_URL_FOTOPRATO, V_BLOB_FOTOPRATO, V_NRFOTOPRATOBLOB 
               FROM RECEFILI RS LEFT JOIN PRATFILI PS ON PS.CDFILAUXPRAT = RS.CDFILAUXPRAT AND PS.CDPRATO = RS.CDPRATO AND PS.CDFILIAL = RS.CDFILIAL
                                INNER JOIN PRODUTO P ON P.CDPRODUTO = RS.CDPRODUTO
                                LEFT JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO AND A.NRALERGENICO = V_NRLACTOSE
                                LEFT JOIN ALERGENICOPROD A2 ON RS.CDPRODUTO = A2.CDPRODUTO AND A2.NRALERGENICO = V_NRGLUTEN
                                LEFT JOIN PRODFILI PF ON PF.CDFILIAL = P_CDFILIAL AND PF.CDPRODUTO = RS.CDPRODUTO
                                LEFT JOIN NUTRPROD NP ON NP.CDPRODUTO = RS.CDPRODUTO AND NP.CDNUTRIENTE = V_CDCALORIAS
                                LEFT JOIN FOTOPRATOBLOB FG ON FG.CDFILAUXPRAT = RS.CDFILAUXPRAT AND FG.CDPRATO = RS.CDPRATO AND IDRECEITA = 'F'
                                LEFT JOIN MEDICASE M ON PS.CDMEDICASE = M.CDMEDICASE
             WHERE RS.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS.CDPRATO AND
                   RS.CDFILIAL = P_CDFILIAL;
           EXCEPTION
              WHEN NO_DATA_FOUND THEN
                 V_NMFANTASIA := NULL;
                 V_DSPRATO := NULL;
                 V_CDMEDICASE := NULL;
                 V_NMMEDICASE := NULL;
                 V_QTMEDICASE := NULL;
                 V_NRLACTOSE :=  NULL;
                 V_NRGLUTEN :=  NULL; 
                 V_CALORIAS :=  NULL;
                 V_URL_FOTOPRATO :=  NULL;
                 V_BLOB_FOTOPRATO :=  NULL;
                 V_NRFOTOPRATOBLOB :=  NULL; 
           END;
      ELSIF C_PRATOS.IDTIPORECEITA = 'S' THEN
          BEGIN
             SELECT MAX(PS.NMFANTASIASE) AS NMFANTASIAPF, MAX(A.CDPRODUTO) AS LACTOSE, MAX(A2.CDPRODUTO) AS GLUTEN, MAX(DSPRATO), MAX(PS.CDMEDICASE), MAX(M.NMMEDICASE), MAX(PS.QTMEDICASEPRATSERV),
                    SUM(NP.QTNUTRPROD * 10 * NVL(DECODE(PF.VRPESOUNIDFI, 0, P.VRPESOUNID, PF.VRPESOUNIDFI),P.VRPESOUNID) * RS.QTCALCNUTRSE) AS QTCALORIAS, MAX(FG.FGFOTOPRATOURL) , NULL AS FGFOTOPRATO, MAX(NRFOTOPRATOBLOB)
               INTO V_NMFANTASIA, V_IDLACTOSE, V_IDGLUTEN, V_DSPRATO, V_CDMEDICASE, V_NMMEDICASE, V_QTMEDICASE, V_CALORIAS, V_URL_FOTOPRATO, V_BLOB_FOTOPRATO, V_NRFOTOPRATOBLOB 
               FROM RECESERV RS LEFT JOIN PRATSERV PS ON PS.CDFILAUXPRAT = RS.CDFILAUXPRAT AND PS.CDPRATO = RS.CDPRATO AND PS.CDFILIAL = RS.CDFILIAL AND PS.CDSERVFILI = RS.CDSERVFILI
                                INNER JOIN PRODUTO P ON P.CDPRODUTO = RS.CDPRODUTO
                                LEFT JOIN ALERGENICOPROD A ON RS.CDPRODUTO = A.CDPRODUTO AND A.NRALERGENICO = V_NRLACTOSE
                                LEFT JOIN ALERGENICOPROD A2 ON RS.CDPRODUTO = A2.CDPRODUTO AND A2.NRALERGENICO = V_NRGLUTEN
                                LEFT JOIN PRODFILI PF ON PF.CDFILIAL = P_CDFILIAL AND PF.CDPRODUTO = RS.CDPRODUTO
                                LEFT JOIN NUTRPROD NP ON NP.CDPRODUTO = RS.CDPRODUTO AND NP.CDNUTRIENTE = V_CDCALORIAS
                                LEFT JOIN FOTOPRATOBLOB FG ON FG.CDFILAUXPRAT = RS.CDFILAUXPRAT AND FG.CDPRATO = RS.CDPRATO AND IDRECEITA = 'S'
                                LEFT JOIN MEDICASE M ON PS.CDMEDICASE = M.CDMEDICASE
             WHERE RS.CDFILAUXPRAT = C_PRATOS.CDFILAUXPRAT AND
                   RS.CDPRATO = C_PRATOS.CDPRATO AND
                   RS.CDFILIAL = P_CDFILIAL AND
                   RS.CDSERVFILI = P_CDSERVFILI;
           EXCEPTION
              WHEN NO_DATA_FOUND THEN
                 V_NMFANTASIA := NULL;
                 V_DSPRATO := NULL;
                 V_CDMEDICASE := NULL;
                 V_NMMEDICASE := NULL;
                 V_QTMEDICASE := NULL;
                 V_NRLACTOSE :=  NULL;
                 V_NRGLUTEN :=  NULL; 
                 V_CALORIAS :=  NULL;
                 V_URL_FOTOPRATO :=  NULL;
                 V_BLOB_FOTOPRATO :=  NULL;
                 V_NRFOTOPRATOBLOB :=  NULL; 
           END;      
      ELSE
         V_NMFANTASIA := C_PRATOS.NMPRATO;
         V_DSPRATO := NULL;
         V_CDMEDICASE := NULL;
         V_NMMEDICASE := NULL;
         V_QTMEDICASE := NULL;
         V_NRLACTOSE :=  NULL;
         V_NRGLUTEN :=  NULL; 
         V_CALORIAS :=  NULL;
         V_URL_FOTOPRATO :=  NULL;
         V_BLOB_FOTOPRATO :=  NULL;
         V_NRFOTOPRATOBLOB :=  NULL; 
      END IF;
      /*SELECT PARA BUSCAR QUANTIDADE DE AVALIACOES*/
      IF P_RETORNAAVALIACAO = 'S' THEN
          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;
      ELSE
         V_VRAVALIACAO := NULL; 
         V_QTAVALIACAO := 0;
      END IF;
      /*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       := C_PRATOS.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    := ROUND(NVL(V_CALORIAS,0),2);
      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         := CASE WHEN V_IDGLUTEN IS NULL THEN 'N' ELSE 'S' END;
      OUT_RECORD.IDLACTOSE        := CASE WHEN V_IDLACTOSE IS NULL THEN 'N' ELSE 'S' END;
      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;
      OUT_RECORD.IDOPCCNFMES      := C_PRATOS.IDOPCCNFMES;
      PIPE ROW (OUT_RECORD);
   END LOOP;
END;
/