/* 
FUN��O NUTRIENTE_CARDAPIO

Descri��o:
Fuin��o que calcula a quantidade de cada nutriente nos produtos e,
para a caloria, ser�o somadas as quantidades dos nutrientes que a comp�em,
multiplicado pelos seus respectivos fatores nutricionais,
desde que esses nutrientes perten�am ao mesmo card�pio, prato e produto da caloria.
A f�rmula da caloria ser�: Caloria = Somat�rio (Quantidade do nutriente x fator nutricional).

Par�metros:
P_FILIAL     - Codigo da Filial.
P_SERVICOS   - String contendo sequencia de Servi�os a serem calculados.
P_NUTRIENTES - String contendo Nutrientes a serem calculados.
P_DTINICIAL  - Data inicial.
P_DTFINAL    - Data final.

Cria��o: Rodrigo Matheus Silv�rio
*/

DROP FUNCTION NUTRIENTE_CARDAPIO
/
DROP TYPE TABELA_NUTRTRAB 
/
DROP TYPE LINHA_NUTRTRAB
/
CREATE TYPE LINHA_NUTRTRAB  AS OBJECT
    (CDFILINUTR VARCHAR2(4),
    NMFILINUTR VARCHAR2 (30),
    CDSERVNUTR VARCHAR2(5), 
    NMSERVNUTR VARCHAR2 (40),
    DTREFENUTR DATE, 
    CDDIETNUTR VARCHAR2(4), 
    NRSEQPRATNUT NUMBER, 
    CDTPPRATNUTR VARCHAR2(2), 
    NMTPPRATNUTR VARCHAR2(15), 
    CDFAPRATNUTR VARCHAR2(4), 
    CDPRATNUTR VARCHAR2(10), 
    NMPRATNUTR VARCHAR2(40),
    CDPRODNUTR VARCHAR2(10), 
    NMPRODNUTR VARCHAR2(50), 
    CDNUTRCALC VARCHAR2(3), 
    NMNUTRCALC VARCHAR2(20),
    QTPRODNUTR FLOAT, 
    QTNUTRCALC FLOAT, 
    QTPRODLIQ FLOAT, 
    VRCALORNUTR FLOAT, 
    VRPECONSNUTR FLOAT, 
    SGUNIDPRODT VARCHAR2(10), 
    QTPRODPRATT FLOAT,
    CDGRUPNUTR VARCHAR2(2),
    NMGRUPNUTR VARCHAR2(20),
    IDCALORIAS VARCHAR2(1),
    IDNUTRRELEV VARCHAR2(1),
    VRFATORNUTR FLOAT,
    NMNUTRREDU VARCHAR2 (4),
    IDKCAL VARCHAR (1),
    NRORDEMNUTR NUMBER)
/
CREATE TYPE TABELA_NUTRTRAB AS TABLE OF LINHA_NUTRTRAB
/
CREATE OR REPLACE FUNCTION
  NUTRIENTE_CARDAPIO
  (P_FILIAL       IN VARCHAR2, 
   P_SERVICOS     IN VARCHAR2, 
   P_NUTRIENTES   IN VARCHAR2, 
   P_DTINICIAL    IN DATE, 
   P_DTFINAL      IN DATE)
RETURN TABELA_NUTRTRAB PIPELINED 
IS
   V_OUT_RECORD LINHA_NUTRTRAB := LINHA_NUTRTRAB (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_QTNUTRKCAL FLOAT;
   V_CDFILIAL VARCHAR2(4);
   V_CDSERVFILI VARCHAR2(5);
   V_DTCARDAPIO DATE;
   V_CDTIPOPRAT VARCHAR2(2);
   V_CDPRATO VARCHAR2(10);
   V_CDPRODUTO VARCHAR2(10);
   CURSOR C_NUTRTRAB IS --Cursor C_NUTRTRAB que seleciona todas as informa��es referentes aos nutrientes do card�pio 
      SELECT
         CD.CDFILIAL, 
         FI.NMFILIAL,
         CD.CDSERVFILI,
         SE.NMSERVICO,
         CD.DTCARDAPIO, 
         SUBSTR(LTRIM(CD.CDDIETPAD||'XXXX'),1,4) AS CDDIETPAD, 
         CC.NRSEQPRATCAR, 
         CC.CDTIPOPRAT,
         TP.NMTIPOPRAT, 
         CC.CDFILAUXPRAT, 
         CC.CDPRATO, 
         PT.NMPRATO,
         RS.CDPRODUTO,
         PR.NMPRODUTO,
         NP.CDNUTRIENTE,
         NU.NMNUTRIENTE, 
         NVL(RS.QTCALCNUTRSE,0) AS QTPRODUTO,                  
         NVL((NP.QTNUTRPROD * 10 * NVL(DECODE(PF.VRPESOUNIDFI, 0, PR.VRPESOUNID, PF.VRPESOUNIDFI), PR.VRPESOUNID) * RS.QTCALCNUTRSE), 0) AS QTNUTPROD,
         RS.QTPRODRECSER AS QTPRODLIQ,
         0 AS VRCALORNUTR,
         NVL(CC.VRPECONSCARD,0) AS VRPECONSCARD, 
         RS.SGUNIDADE AS SGUNIDPROD,
         RS.QTBRUTASEUNR AS QTPRODPRATT,
         NU.CDGRUPNUTR,
         GN.NMGRUPNUTR,
         NU.IDCALORIAS,
         NU.IDNUTRRELEV,
         NU.VRFATORNUTR,
         NU.NMREDUZIDO,
         NU.IDKCAL,
         NU.NRORDEMNUTR
      FROM 
         PRODFILI PF, 
         PRODUTO PR LEFT JOIN NUTRPROD NP
            ON (PR.CDPRODUTO = NP.CDPRODUTO)
                    LEFT JOIN NUTRIENTE NU
            ON (NU.CDNUTRIENTE = NP.CDNUTRIENTE), 
         COMPCARD CC, 
         RECESERV RS, 
         CARDADIA CD, 
         FILIAL FI,
         SERVICO SE,
         PRATO PT, 
         TIPOPRAT TP,
         GRUPNUTR GN
      WHERE 
         (CD.CDFILIAL = P_FILIAL)                           AND 
         VALOR_NA_LISTA(CD.CDSERVFILI, P_SERVICOS) > 0      AND
         (CD.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL)  AND 
         (CD.CDFILIAL        = CC.CDFILIAL)                 AND 
         (CD.NRCARDAPIO      = CC.NRCARDAPIO)               AND 
         (CD.CDFILIAL        = RS.CDFILIAL)                 AND 
         (CD.CDSERVFILI      = RS.CDSERVFILI)               AND 
         (CC.CDFILAUXPRAT    = RS.CDFILAUXPRAT)             AND 
         (CC.CDPRATO         = RS.CDPRATO)                  AND 
         (PF.CDFILIAL  = P_FILIAL)                    AND 
         (PF.CDPRODUTO = PR.CDPRODUTO)                AND 
         ((INSTR(P_NUTRIENTES,NU.CDNUTRIENTE) <> 0) OR (P_NUTRIENTES = 'T'))          AND 
         (RS.CDPRODUTO = PR.CDPRODUTO)                      AND
         (CD.CDFILIAL        = FI.CDFILIAL)                 AND
         (CD.CDFILIAL        = SE.CDFILIAL)                 AND
         (CD.CDSERVFILI      = SE.CDSERVFILI)               AND  
         (CC.CDPRATO         = PT.CDPRATO)                  AND
         (CC.CDFILAUXPRAT    = PT.CDFILAUXPRAT)             AND
         (CC.CDTIPOPRAT      = TP.CDTIPOPRAT)               AND
         ((NU.CDGRUPNUTR      = GN.CDGRUPNUTR) OR (NU.CDGRUPNUTR IS NULL))
               
      UNION ALL 
      
      SELECT
         CD.CDFILIAL, 
         FI.NMFILIAL,
         CD.CDSERVFILI,
         SE.NMSERVICO, 
         CD.DTCARDAPIO, 
         SUBSTR(LTRIM(CD.CDDIETPAD||'XXXX'),1,4) AS CDDIETPAD, 
         CC.NRSEQPRATCAR, 
         CC.CDTIPOPRAT,
         TP.NMTIPOPRAT,
         CC.CDFILAUXPRAT, 
         CC.CDPRATO, 
         PT.NMPRATO, 
         RF.CDPRODUTO,
         PR.NMPRODUTO,         
         NP.CDNUTRIENTE,
         NU.NMNUTRIENTE, 
         NVL(RF.QTCALCNUTRLO,0) AS QTPRODUTO, 
         NVL((NP.QTNUTRPROD * 10 * NVL(DECODE(PF.VRPESOUNIDFI, 0, PR.VRPESOUNID, PF.VRPESOUNIDFI), PR.VRPESOUNID) * RF.QTCALCNUTRLO), 0) AS QTNUTPROD,
         RF.QTPRODRECLOC AS QTPRODLIQ,
         0 AS VRCALORNUTR,
         NVL(CC.VRPECONSCARD,0) AS VRPECONSCARD,
         RF.SGUNIDADE AS SGUNIDPROD,
         RF.QTBRUTALOUNR AS QTPRODPRATT,
         NU.CDGRUPNUTR,
         GN.NMGRUPNUTR,
         NU.IDCALORIAS,
         NU.IDNUTRRELEV,
         NU.VRFATORNUTR,
         NU.NMREDUZIDO,
         NU.IDKCAL,
         NU.NRORDEMNUTR 
      FROM 
         PRODFILI PF, 
         PRODUTO PR LEFT JOIN NUTRPROD NP
            ON (PR.CDPRODUTO = NP.CDPRODUTO)
                    LEFT JOIN NUTRIENTE NU
            ON (NU.CDNUTRIENTE = NP.CDNUTRIENTE), 
         COMPCARD CC, 
         RECEFILI RF, 
         CARDADIA CD,  
         FILIAL FI,
         SERVICO SE,
         PRATO PT, 
         TIPOPRAT TP,
         GRUPNUTR GN 
      WHERE 
         (CD.CDFILIAL = P_FILIAL)                           AND 
         VALOR_NA_LISTA(CD.CDSERVFILI, P_SERVICOS) > 0      AND
         (CD.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL)  AND 
         (CD.CDFILIAL        = CC.CDFILIAL)                 AND 
         (CD.NRCARDAPIO      = CC.NRCARDAPIO)               AND 
         (CD.CDFILIAL        = RF.CDFILIAL)                 AND 
         (CC.CDFILAUXPRAT    = RF.CDFILAUXPRAT)             AND 
         (CC.CDPRATO         = RF.CDPRATO)                  AND 
         (PF.CDFILIAL  = P_FILIAL)                  AND 
         (PF.CDPRODUTO = PR.CDPRODUTO)                AND 
         ((INSTR(P_NUTRIENTES,NU.CDNUTRIENTE) <> 0) OR (P_NUTRIENTES = 'T'))          AND 
         (RF.CDPRODUTO = PR.CDPRODUTO)                      AND 
         (RF.CDPRODUTO = PR.CDPRODUTO)                      AND 
         NOT EXISTS (SELECT 
                        CDPRATO 
                     FROM 
                        RECESERV 
                     WHERE 
                        (CDFILAUXPRAT = CC.CDFILAUXPRAT) AND 
                        (CDPRATO      = CC.CDPRATO)      AND 
                        (CDFILIAL     = CD.CDFILIAL)     AND 
                        (CDSERVFILI   = CD.CDSERVFILI) 
                     )                                      AND
         (CD.CDFILIAL        = FI.CDFILIAL)                 AND
         (CD.CDFILIAL        = SE.CDFILIAL)                 AND
         (CD.CDSERVFILI      = SE.CDSERVFILI)               AND
         (CC.CDPRATO         = PT.CDPRATO)                  AND
         (CC.CDFILAUXPRAT = PT.CDFILAUXPRAT)                AND
         (CC.CDTIPOPRAT      = TP.CDTIPOPRAT)               AND
         ((NU.CDGRUPNUTR      = GN.CDGRUPNUTR) OR (NU.CDGRUPNUTR IS NULL))
         
      UNION ALL 
      
      SELECT
         CD.CDFILIAL, 
         FI.NMFILIAL,
         CD.CDSERVFILI,
         SE.NMSERVICO,
         CD.DTCARDAPIO, 
         SUBSTR(LTRIM(CD.CDDIETPAD||'XXXX'),1,4) AS CDDIETPAD, 
         CC.NRSEQPRATCAR, 
         CC.CDTIPOPRAT,
         TP.NMTIPOPRAT, 
         CC.CDFILAUXPRAT, 
         CC.CDPRATO, 
         PT.NMPRATO, 
         RP.CDPRODUTO,
         PR.NMPRODUTO,          
         NP.CDNUTRIENTE,
         NU.NMNUTRIENTE,
         NVL(RP.QTCALCNUTRPR,0) AS QTPRODUTO, 
         NVL((NP.QTNUTRPROD * 10 * NVL(DECODE(PF.VRPESOUNIDFI, 0, PR.VRPESOUNID, PF.VRPESOUNIDFI), PR.VRPESOUNID) * RP.QTCALCNUTRPR), 0) AS QTNUTPROD,
         RP.QTPRODPRAT AS QTPRODLIQ,
         0 AS VRCALORNUTR,
         NVL(CC.VRPECONSCARD,0) VRPECONSCARD, 
         RP.SGUNIDADE AS SGUNIDPROD,
         RP.QTBRUTAPRUNR AS QTPRODPRATT,
         NU.CDGRUPNUTR,
         GN.NMGRUPNUTR,
         NU.IDCALORIAS,
         NU.IDNUTRRELEV,
         NU.VRFATORNUTR,
         NU.NMREDUZIDO,
         NU.IDKCAL,
         NU.NRORDEMNUTR
      FROM 
         PRODFILI PF, 
         PRODUTO PR LEFT JOIN NUTRPROD NP
            ON (PR.CDPRODUTO = NP.CDPRODUTO)
                    LEFT JOIN NUTRIENTE NU
            ON (NU.CDNUTRIENTE = NP.CDNUTRIENTE), 
         COMPCARD CC, 
         RECEPADR RP, 
         CARDADIA CD, 
         FILIAL FI,
         SERVICO SE,
         PRATO PT, 
         TIPOPRAT TP,
         GRUPNUTR GN 
      WHERE 
         (CD.CDFILIAL = P_FILIAL)                           AND 
         VALOR_NA_LISTA(CD.CDSERVFILI, P_SERVICOS) > 0      AND
         (CD.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL)  AND 
         (CD.CDFILIAL        = CC.CDFILIAL)                 AND 
         (CD.NRCARDAPIO      = CC.NRCARDAPIO)               AND 
         (RP.CDFILAUXPRAT    = 'P')                         AND 
         (CC.CDPRATO         = RP.CDPRATO)                  AND 
         (PF.CDFILIAL  = P_FILIAL)                    AND 
         (PF.CDPRODUTO = PR.CDPRODUTO)                AND 
         ((INSTR(P_NUTRIENTES,NU.CDNUTRIENTE) <> 0) OR (P_NUTRIENTES = 'T'))          AND 
         (RP.CDPRODUTO = PR.CDPRODUTO)                      AND 
         (RP.CDPRODUTO = PR.CDPRODUTO)                      AND 
         NOT EXISTS (SELECT 
                        CDPRATO 
                     FROM 
                        RECEFILI 
                     WHERE 
                        (CDFILAUXPRAT = CC.CDFILAUXPRAT) AND 
                        (CDPRATO      = CC.CDPRATO)      AND 
                        (CDFILIAL     = CD.CDFILIAL) 
                     ) AND 
         NOT EXISTS (SELECT 
                        CDPRATO 
                     FROM 
                        RECESERV 
                     WHERE 
                        (CDFILAUXPRAT = CC.CDFILAUXPRAT) AND 
                        (CDPRATO      = CC.CDPRATO)      AND 
                        (CDFILIAL     = CD.CDFILIAL)     AND 
                        (CDSERVFILI   = CD.CDSERVFILI))     AND
         (CD.CDFILIAL        = FI.CDFILIAL)                 AND
         (CD.CDFILIAL        = SE.CDFILIAL)                 AND
         (CD.CDSERVFILI      = SE.CDSERVFILI)               AND
         (CC.CDPRATO         = PT.CDPRATO)                  AND
         (CC.CDFILAUXPRAT = PT.CDFILAUXPRAT)                AND
         (CC.CDTIPOPRAT      = TP.CDTIPOPRAT)               AND
         ((NU.CDGRUPNUTR      = GN.CDGRUPNUTR) OR (NU.CDGRUPNUTR IS NULL))       
      ORDER BY
         CDFILIAL,
         CDSERVFILI,
         DTCARDAPIO,
         CDPRATO,
         CDPRODUTO,
         IDKCAL;
      
BEGIN 
   /*Inicializa��o das vari�veis*/   
   V_CDFILIAL := ' ';
   V_CDSERVFILI := ' ';
   V_DTCARDAPIO := SYSDATE;
   V_CDTIPOPRAT := ' ';
   V_CDPRATO := ' ';
   V_CDPRODUTO := ' '; 
   OPEN C_NUTRTRAB;
   LOOP                   
      FETCH  C_NUTRTRAB INTO -- Insere cada linha do cursor C_NUTRTRAB na tabela virtual 
         V_OUT_RECORD.CDFILINUTR,
         V_OUT_RECORD.NMFILINUTR,
         V_OUT_RECORD.CDSERVNUTR,
         V_OUT_RECORD.NMSERVNUTR,
         V_OUT_RECORD.DTREFENUTR,
         V_OUT_RECORD.CDDIETNUTR,
         V_OUT_RECORD.NRSEQPRATNUT, 
         V_OUT_RECORD.CDTPPRATNUTR, 
         V_OUT_RECORD.NMTPPRATNUTR,  
         V_OUT_RECORD.CDFAPRATNUTR, 
         V_OUT_RECORD.CDPRATNUTR, 
         V_OUT_RECORD.NMPRATNUTR,
         V_OUT_RECORD.CDPRODNUTR,
         V_OUT_RECORD.NMPRODNUTR, 
         V_OUT_RECORD.CDNUTRCALC, 
         V_OUT_RECORD.NMNUTRCALC,  
         V_OUT_RECORD.QTPRODNUTR,
         V_OUT_RECORD.QTNUTRCALC,
         V_OUT_RECORD.QTPRODLIQ, 
         V_OUT_RECORD.VRCALORNUTR, 
         V_OUT_RECORD.VRPECONSNUTR, 
         V_OUT_RECORD.SGUNIDPRODT, 
         V_OUT_RECORD.QTPRODPRATT,
         V_OUT_RECORD.CDGRUPNUTR,
         V_OUT_RECORD.NMGRUPNUTR,
         V_OUT_RECORD.IDCALORIAS,
         V_OUT_RECORD.IDNUTRRELEV,
         V_OUT_RECORD.VRFATORNUTR,
         V_OUT_RECORD.NMNUTRREDU,
         V_OUT_RECORD.IDKCAL,
         V_OUT_RECORD.NRORDEMNUTR;
         
         IF (V_OUT_RECORD.CDFILINUTR <> V_CDFILIAL) OR --CASO FILIAL,
            (V_OUT_RECORD.CDSERVNUTR <> V_CDSERVFILI) OR --OU SERVI�O,
            (V_OUT_RECORD.DTREFENUTR <> V_DTCARDAPIO) OR --OU DATA DO CARD�PIO,  
            (V_OUT_RECORD.CDTPPRATNUTR <> V_CDTIPOPRAT) OR-- OU TIPO DE PRATO,           
            (V_OUT_RECORD.CDPRATNUTR <> V_CDPRATO) OR-- OU PRATO,
            (V_OUT_RECORD.CDPRODNUTR <> V_CDPRODUTO) THEN-- OU PRODUTO MUDEM
               V_QTNUTRKCAL := 0; -- A VARIAV�L QUE QUE ARMAZENA A QUANTIDADE DE CALORIA � INICIALIZADA PARA A PR�XIMA CALORIA
               V_CDFILIAL := V_OUT_RECORD.CDFILINUTR; -- E AS VARIAV�IS FILIAL,
               V_CDSERVFILI := V_OUT_RECORD.CDSERVNUTR; -- SERVI�O,               
               V_DTCARDAPIO := V_OUT_RECORD.DTREFENUTR; -- DATA DO CARD�PIO,
               V_CDTIPOPRAT := V_OUT_RECORD.CDTPPRATNUTR; --TIPO DE PRATO, 
               V_CDPRATO := V_OUT_RECORD.CDPRATNUTR; -- PRATO E
               V_CDPRODUTO := V_OUT_RECORD.CDPRODNUTR; --PRODUTO RECEBEM OS NOVOS VALORES       
         END IF;
         
         IF V_OUT_RECORD.IDCALORIAS = 'S' THEN  -- SE O NUTRIENTE COMP�E O C�CULO DE CALORIA ,    
            V_QTNUTRKCAL := V_QTNUTRKCAL + (V_OUT_RECORD.QTNUTRCALC * V_OUT_RECORD.VRFATORNUTR); -- SEU VALOR � ACRESCENTADO A VARIAV�L QUE QUE ARMAZENA A QUANTIDADE DE CALORIA
         END IF;
               
         IF V_OUT_RECORD.IDKCAL = 'S' THEN --Verifica se o nutriente � caloria
            V_OUT_RECORD.QTNUTRCALC := V_QTNUTRKCAL; -- O valor da caloria � inserida em seu respectivo campo
         END IF;
         
         EXIT WHEN  C_NUTRTRAB%NOTFOUND;
         PIPE ROW (V_OUT_RECORD);
   END LOOP; 
   CLOSE  C_NUTRTRAB;  
   RETURN;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('NUTRIENTE_CARDAPIO','5.75.000'); END;
/