DROP FUNCTION NUTRIENTES_CARD_GRUPO
/

DROP TYPE NUTRIENTES_CARD_GRUPO_TABLE
/

DROP TYPE NUTRIENTES_CARD_GRUPO_TYPE
/

CREATE TYPE NUTRIENTES_CARD_GRUPO_TYPE AS OBJECT
  (CDFILIAL     VARCHAR2(4),
   CDSERVFILI   VARCHAR2(5),
   DTREFENUTR   DATE,
   CDPRATO      VARCHAR2(10),
   CDFILAUXPRAT VARCHAR2(04),
   CDPRODUTO    VARCHAR2(10),
   NMPRODUTO    VARCHAR2(50),	
   QTPRODPRATT  NUMBER,
   QTPROTLIQU   NUMBER,

   CDCALORIAS  VARCHAR2(3),
   NMCALORIAS  VARCHAR2(20),
   QTCALORIAS  FLOAT(126),
   
   CDCARBOHIDRA  VARCHAR2(3),
   NMCARBOHIDRA  VARCHAR2(20),
   QTCARBOHIDRA  FLOAT(126),

   CDPROTEINA  VARCHAR2(3),
   NMPROTEINA  VARCHAR2(20),
   QTPROTEINA  FLOAT(126),

   CDLIPIDIOS  VARCHAR2(3),
   NMLIPIDIOS VARCHAR2(20),
   QTLIPIDIOS  FLOAT(126),

   CDGORDURASATURADA  VARCHAR2(3),
   NMGORDURASATURADA VARCHAR2(20),
   QTGORDURASATURADA  FLOAT(126),   

   CDNUTRCALCVAR1  VARCHAR2(3),
   NMNUTRIENTEVAR1 VARCHAR2(20),
   QTNUTRCALCVAR1  FLOAT(126),
   
   CDNUTRCALCVAR2  VARCHAR2(3),
   NMNUTRIENTEVAR2 VARCHAR2(20),
   QTNUTRCALCVAR2  FLOAT(126),

   CDNUTRCALCVAR3  VARCHAR2(3),
   NMNUTRIENTEVAR3 VARCHAR2(20),
   QTNUTRCALCVAR3  FLOAT(126),

   CDNUTRCALCVAR4  VARCHAR2(3),
   NMNUTRIENTEVAR4 VARCHAR2(20),
   QTNUTRCALCVAR4  FLOAT(126)
  )
/
CREATE TYPE NUTRIENTES_CARD_GRUPO_TABLE AS TABLE OF NUTRIENTES_CARD_GRUPO_TYPE
/

create or replace FUNCTION NUTRIENTES_CARD_GRUPO
  (P_CDFILINUTR IN FILIAL.CDFILIAL%TYPE,
   P_CDSERVNUTR IN VARCHAR2,
   P_DTINICIAL IN DATE,
   P_DTFINAL IN DATE,
   P_LISTANUTR IN VARCHAR2,
   P_TIPOPRAT IN VARCHAR2,
   P_OPCAO IN VARCHAR2
   )
   RETURN NUTRIENTES_CARD_GRUPO_TABLE PIPELINED IS
   OUT_RECORD NUTRIENTES_CARD_GRUPO_TYPE := NUTRIENTES_CARD_GRUPO_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, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

V_CALORIAS VARCHAR2(3);
V_NMCALORIAS VARCHAR2(20);
V_CARBOHIDRATO VARCHAR2(3);
V_NMCARBOHIDRATO VARCHAR2(20);
V_PROTEINA VARCHAR2(3);
V_NMPROTEINA VARCHAR2(20);
V_LIPIDIO VARCHAR2(3);
V_NMGRASA VARCHAR2(20);
V_ACGRASA_SATURADA VARCHAR2(3);
V_NMACGRASA_SATURADA VARCHAR2(20);
V_NUTRVAR1 VARCHAR2(3);
V_NMNUTRVAR1 VARCHAR2(20);
V_NUTRVAR2 VARCHAR2(3);
V_NMNUTRVAR2 VARCHAR2(20);
V_NUTRVAR3 VARCHAR2(3);
V_NMNUTRVAR3 VARCHAR2(20);
V_NUTRVAR4 VARCHAR2(4);
V_NMNUTRVAR4 VARCHAR2(20);
V_NUTRIENTES VARCHAR2(40);
BEGIN

    OUT_RECORD.QTCALORIAS        := 0;
    OUT_RECORD.QTCARBOHIDRA      := 0;
    OUT_RECORD.QTPROTEINA        := 0;
    OUT_RECORD.QTLIPIDIOS        := 0;
    OUT_RECORD.QTGORDURASATURADA := 0;
    OUT_RECORD.QTNUTRCALCVAR1    := 0;
    OUT_RECORD.QTNUTRCALCVAR2    := 0;
    OUT_RECORD.QTNUTRCALCVAR3    := 0;
    OUT_RECORD.QTNUTRCALCVAR4    := 0;

   SELECT MAX(CDCALORIAS), MAX(CDCARBOHIDRA), MAX(CDPROTEINA), MAX(CDLIPIDIOS), MAX(CDGORDURASATURADA) 
     INTO V_CALORIAS, V_CARBOHIDRATO, V_PROTEINA, V_LIPIDIO, V_ACGRASA_SATURADA 
     FROM PARAMGERAL;

   FOR C_NUT IN (SELECT CDNUTRIENTE, NMNUTRIENTE FROM(
                 SELECT CDNUTRIENTE, NMNUTRIENTE 
                  FROM NUTRIENTE 
                 WHERE ((P_LISTANUTR = 'T') OR (INSTR(P_LISTANUTR, CDNUTRIENTE) <> 0)) 
                   AND NOT(CDNUTRIENTE IN (NVL(V_CALORIAS,'X'), NVL(V_CARBOHIDRATO,'X'), NVL(V_PROTEINA,'X'), NVL(V_LIPIDIO,'X')))
                 ORDER BY CDNUTRIENTE)
                WHERE ROWNUM < 5
                UNION ALL
                SELECT CDNUTRIENTE, NMNUTRIENTE 
                  FROM NUTRIENTE 
                 WHERE CDNUTRIENTE IN (NVL(V_CALORIAS,'X'), NVL(V_CARBOHIDRATO,'X'), NVL(V_PROTEINA,'X'), NVL(V_LIPIDIO,'X'), NVL(V_ACGRASA_SATURADA,'X')))                
   LOOP
      IF C_NUT.CDNUTRIENTE = V_CALORIAS THEN V_NMCALORIAS := C_NUT.NMNUTRIENTE;
      ELSIF C_NUT.CDNUTRIENTE = V_CARBOHIDRATO THEN V_NMCARBOHIDRATO := C_NUT.NMNUTRIENTE;
      ELSIF C_NUT.CDNUTRIENTE = V_PROTEINA THEN V_NMPROTEINA := C_NUT.NMNUTRIENTE;
      ELSIF C_NUT.CDNUTRIENTE = V_LIPIDIO THEN V_NMGRASA := C_NUT.NMNUTRIENTE;
      ELSIF C_NUT.CDNUTRIENTE = V_ACGRASA_SATURADA AND V_NMACGRASA_SATURADA IS NULL THEN V_NMACGRASA_SATURADA := C_NUT.NMNUTRIENTE;
      ELSIF V_NUTRVAR1 IS NULL THEN V_NUTRVAR1 := C_NUT.CDNUTRIENTE; V_NMNUTRVAR1 := C_NUT.NMNUTRIENTE;
      ELSIF V_NUTRVAR2 IS NULL THEN V_NUTRVAR2 := C_NUT.CDNUTRIENTE; V_NMNUTRVAR2 := C_NUT.NMNUTRIENTE;
      ELSIF V_NUTRVAR3 IS NULL THEN V_NUTRVAR3 := C_NUT.CDNUTRIENTE; V_NMNUTRVAR3 := C_NUT.NMNUTRIENTE;
      ELSE V_NUTRVAR4 := C_NUT.CDNUTRIENTE; V_NMNUTRVAR4 := C_NUT.NMNUTRIENTE;
      END IF;
   END LOOP;
   V_NUTRIENTES := NVL(V_CALORIAS,'') || '_' || NVL(V_CARBOHIDRATO,'') || '_' || NVL(V_PROTEINA,'') || '_' || NVL(V_LIPIDIO,'') || '_' || NVL(V_ACGRASA_SATURADA,'') || '_' || 
                   NVL(V_NUTRVAR1,'')|| '_' || NVL(V_NUTRVAR2,'')|| '_' || NVL(V_NUTRVAR3,'')|| '_' || NVL(V_NUTRVAR4,'') ;

   FOR C_SERV IN (SELECT CDFILIAL, CDSERVFILI, DTCARDAPIO, NRCARDAPIO 
                    FROM CARDADIA C  
                   WHERE (CDFILIAL = P_CDFILINUTR) AND
                         ((INSTR(P_CDSERVNUTR, CDSERVFILI) > 0) OR (P_CDSERVNUTR = 'T')) AND
                         (DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
                         (QTCOMESTICAR > 0)
                  ORDER BY DTCARDAPIO, CDSERVFILI)
   LOOP

      FOR C_CARD IN (SELECT CDPRATO,CDFILAUXPRAT,RC.CDPRODUTO,P.NMPRODUTO, NP.CDNUTRIENTE, QTPRODPRATT, QTPRODLIQ,
                            NVL((NP.QTNUTRPROD * 10 * NVL(DECODE(PRODFILI.VRPESOUNIDFI, 0, P.VRPESOUNID, PRODFILI.VRPESOUNIDFI), P.VRPESOUNID) * RC.QTCALCNUTR), 0) AS QTNUTPROD
                       FROM 
                       (SELECT CP.CDPRATO, CP.CDFILAUXPRAT, CP.CDTIPOPRAT, CP.NRSEQPRATCAR, RS.CDPRODUTO, RS.QTCALCNUTRSE AS QTCALCNUTR, RS.QTPRODRECSER AS QTPRODLIQ, RS.QTBRUTASEUNR AS QTPRODPRATT
                          FROM RECESERV RS INNER JOIN COMPCARD CP ON CP.CDPRATO = RS.CDPRATO AND CP.CDFILAUXPRAT = RS.CDFILAUXPRAT
                          WHERE CP.CDFILIAL = P_CDFILINUTR AND CP.NRCARDAPIO = C_SERV.NRCARDAPIO AND RS.CDFILIAL = CP.CDFILIAL AND RS.CDSERVFILI = C_SERV.CDSERVFILI AND
                                ((INSTR(P_TIPOPRAT, CP.CDTIPOPRAT) <> 0) OR (P_TIPOPRAT = 'T'))
                        UNION ALL
                        SELECT CP.CDPRATO, CP.CDFILAUXPRAT, CP.CDTIPOPRAT, CP.NRSEQPRATCAR, RF.CDPRODUTO, RF.QTCALCNUTRLO AS QTCALCNUTR, RF.QTPRODRECLOC AS QTPRODLIQ, RF.QTBRUTALOUNR AS QTPRODPRATT
                          FROM RECEFILI RF INNER JOIN COMPCARD CP ON CP.CDPRATO = RF.CDPRATO AND CP.CDFILAUXPRAT = RF.CDFILAUXPRAT
                         WHERE CP.CDFILIAL = P_CDFILINUTR AND CP.NRCARDAPIO = C_SERV.NRCARDAPIO AND RF.CDFILIAL = CP.CDFILIAL AND
                               ((INSTR(P_TIPOPRAT, CP.CDTIPOPRAT) <> 0) OR (P_TIPOPRAT = 'T')) AND
                               NOT EXISTS (SELECT CDPRATO FROM RECESERV WHERE (RECESERV.CDFILAUXPRAT = RF.CDFILAUXPRAT) AND (RECESERV.CDPRATO = RF.CDPRATO) AND (RECESERV.CDFILIAL = P_CDFILINUTR) AND (RECESERV.CDSERVFILI = C_SERV.CDSERVFILI))
                        UNION ALL      
                        SELECT CP.CDPRATO, CP.CDFILAUXPRAT, CP.CDTIPOPRAT, CP.NRSEQPRATCAR, RP.CDPRODUTO, RP.QTCALCNUTRPR AS QTCALCNUTR, RP.QTPRODPRAT AS QTPRODLIQ, RP.QTBRUTAPRUNR AS QTPRODPRATT
                          FROM RECEPADR RP INNER JOIN COMPCARD CP ON CP.CDPRATO = RP.CDPRATO AND CP.CDFILAUXPRAT = RP.CDFILAUXPRAT
                         WHERE CP.CDFILIAL = P_CDFILINUTR AND CP.NRCARDAPIO = C_SERV.NRCARDAPIO AND ((INSTR(P_TIPOPRAT, CP.CDTIPOPRAT) <> 0) OR (P_TIPOPRAT = 'T')) AND
                               NOT EXISTS (SELECT CDPRATO FROM RECEFILI WHERE (RECEFILI.CDFILAUXPRAT = RP.CDFILAUXPRAT) AND (RECEFILI.CDPRATO = RP.CDPRATO) AND (RECEFILI.CDFILIAL = P_CDFILINUTR)) AND
                               NOT EXISTS (SELECT CDPRATO FROM RECESERV WHERE (RECESERV.CDFILAUXPRAT = RP.CDFILAUXPRAT) AND (RECESERV.CDPRATO = RP.CDPRATO) AND (RECESERV.CDFILIAL = P_CDFILINUTR) AND (RECESERV.CDSERVFILI = C_SERV.CDSERVFILI))
                       ) RC INNER JOIN PRODFILI ON RC.CDPRODUTO = PRODFILI.CDPRODUTO AND PRODFILI.CDFILIAL = P_CDFILINUTR
                            INNER JOIN NUTRPROD NP ON NP.CDPRODUTO = RC.CDPRODUTO AND (INSTR(V_NUTRIENTES, CDNUTRIENTE) > 0)
                            INNER JOIN PRODUTO P ON P.CDPRODUTO = RC.CDPRODUTO
                            INNER JOIN CNFCAMES CF ON CF.CDFILIAL = P_CDFILINUTR AND CF.CDSERVFILI = C_SERV.CDSERVFILI AND
                                                      CF.CDTIPOPRAT = RC.CDTIPOPRAT AND CF.NRSEQCNFMES = RC.NRSEQPRATCAR AND CF.DTREFECNFMES = TRUNC(C_SERV.DTCARDAPIO,'MM')
                            AND ((CF.IDOPCCNFMES = P_OPCAO) OR (P_OPCAO = 'T'))
                       ORDER BY CDPRATO, CDFILAUXPRAT, RC.CDPRODUTO, NP.CDNUTRIENTE)
      LOOP
         IF (OUT_RECORD.CDFILIAL IS NOT NULL) AND ((OUT_RECORD.CDSERVFILI <> C_SERV.CDSERVFILI) OR (OUT_RECORD.DTREFENUTR <> C_SERV.DTCARDAPIO) OR 
            (OUT_RECORD.CDPRATO <> C_CARD.CDPRATO) OR (OUT_RECORD.CDFILAUXPRAT <> C_CARD.CDFILAUXPRAT) OR (OUT_RECORD.CDPRODUTO <> C_CARD.CDPRODUTO)) THEN
            PIPE ROW (OUT_RECORD);
            OUT_RECORD.QTCALORIAS        := 0;
            OUT_RECORD.QTCARBOHIDRA      := 0;
            OUT_RECORD.QTPROTEINA        := 0;
            OUT_RECORD.QTLIPIDIOS        := 0;
            OUT_RECORD.QTGORDURASATURADA := 0;
            OUT_RECORD.QTNUTRCALCVAR1    := 0;
            OUT_RECORD.QTNUTRCALCVAR2    := 0;
            OUT_RECORD.QTNUTRCALCVAR3    := 0;
            OUT_RECORD.QTNUTRCALCVAR4    := 0;
         END IF;
         OUT_RECORD.CDFILIAL     := P_CDFILINUTR;
         OUT_RECORD.CDSERVFILI   := C_SERV.CDSERVFILI;
         OUT_RECORD.DTREFENUTR   := C_SERV.DTCARDAPIO;
         OUT_RECORD.CDPRATO      := C_CARD.CDPRATO;
         OUT_RECORD.CDFILAUXPRAT := C_CARD.CDFILAUXPRAT;
         OUT_RECORD.CDPRODUTO    := C_CARD.CDPRODUTO;
         OUT_RECORD.NMPRODUTO    := C_CARD.NMPRODUTO;
         OUT_RECORD.QTPRODPRATT  := C_CARD.QTPRODPRATT;
         OUT_RECORD.QTPROTLIQU   := C_CARD.QTPRODLIQ;
         OUT_RECORD.CDCALORIAS   := V_CALORIAS;
         OUT_RECORD.NMCALORIAS         := V_NMCALORIAS;
         OUT_RECORD.CDCARBOHIDRA       := V_CARBOHIDRATO;
         OUT_RECORD.NMCARBOHIDRA       := V_NMCARBOHIDRATO;
         OUT_RECORD.CDPROTEINA         := V_PROTEINA;
         OUT_RECORD.NMPROTEINA         := V_NMPROTEINA;
         OUT_RECORD.CDLIPIDIOS         := V_LIPIDIO;
         OUT_RECORD.NMLIPIDIOS         := V_NMGRASA;
         OUT_RECORD.CDGORDURASATURADA  := V_ACGRASA_SATURADA;
         OUT_RECORD.NMGORDURASATURADA  := V_NMACGRASA_SATURADA;
         OUT_RECORD.CDNUTRCALCVAR1     := V_NUTRVAR1;
         OUT_RECORD.NMNUTRIENTEVAR1    := V_NMNUTRVAR1;
         OUT_RECORD.CDNUTRCALCVAR2     := V_NUTRVAR2;
         OUT_RECORD.NMNUTRIENTEVAR2    := V_NMNUTRVAR2;
         OUT_RECORD.CDNUTRCALCVAR3     := V_NUTRVAR3;
         OUT_RECORD.NMNUTRIENTEVAR3    := V_NMNUTRVAR3;
         OUT_RECORD.CDNUTRCALCVAR4     := V_NUTRVAR4;
         OUT_RECORD.NMNUTRIENTEVAR4    := V_NMNUTRVAR4;

         IF C_CARD.CDNUTRIENTE    = V_CALORIAS THEN OUT_RECORD.QTCALORIAS := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_CARBOHIDRATO THEN OUT_RECORD.QTCARBOHIDRA := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_PROTEINA THEN OUT_RECORD.QTPROTEINA := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_LIPIDIO THEN OUT_RECORD.QTLIPIDIOS := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_ACGRASA_SATURADA THEN OUT_RECORD.QTGORDURASATURADA := C_CARD.QTNUTPROD;
         END IF;
         IF C_CARD.CDNUTRIENTE = V_NUTRVAR1 THEN OUT_RECORD.QTNUTRCALCVAR1 := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_NUTRVAR2 THEN OUT_RECORD.QTNUTRCALCVAR2 := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_NUTRVAR3 THEN OUT_RECORD.QTNUTRCALCVAR3 := C_CARD.QTNUTPROD;
         ELSIF C_CARD.CDNUTRIENTE = V_NUTRVAR4 THEN OUT_RECORD.QTNUTRCALCVAR4 := C_CARD.QTNUTPROD;
         END IF;
      END LOOP;
   END LOOP;
   IF OUT_RECORD.CDFILIAL IS NOT NULL THEN PIPE ROW (OUT_RECORD); END IF;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('NUTRIENTES_CARD_GRUPO','6.49.034'); END;
/
