create or replace PROCEDURE TROCA_CONSUMOPRATO_CARD
(
   P_LISTASERV    IN VARCHAR2,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_CDPRATO      IN VARCHAR2,
   P_CDFILAUXPRAT IN VARCHAR2,
   P_CONSUMO      IN VARCHAR2,
   P_VALOR        IN COMPCARD.VRPECONSCARD%TYPE,
   P_CDOPERADOR   IN VARCHAR2,
   P_NRLOGINTCARD OUT VARCHAR2
) IS
   V_NRLOGINTCARD VARCHAR2(20);
   V_CDCHAVE VARCHAR2(500);
   V_MENSAGEM VARCHAR2(4000);
BEGIN
   FOR C_COMP IN (
      SELECT A.NRORG, A.CDFILIAL, CDSERVFILI, DTCARDAPIO, NRCARDAPIO, CDTIPOPRAT, NMTIPOPRAT, NRSEQPRATCAR, P.CDFILAUXPRAT, P.CDPRATO, P.NMPRATO, QTCOMESTICAR, CONSUMO_PRATO_ATUAL, 
             CONSUMO_PRATO_NOVO, CONSUMO_TOTAL_PRATO_NOVO, CONSUMO_OUTROS_PRATOS, ROUND(((CONSUMO_TOTAL_PRATO_NOVO + CONSUMO_OUTROS_PRATOS)/100) * QTCOMESTICAR) AS COMENSAIS_NOVO,
             CASE WHEN (IDSOMACOMEST = 'S' AND (IDOPCCNFMES = 'N' AND IDCONCNFMES = 'S')) THEN 'S'
                  WHEN (IDSOMACOMEST = 'N' OR (IDOPCCNFMES = 'S' OR IDCONCNFMES = 'N')) THEN 'N' END AS VALIDA,
                  IDSOMACOMEST, IDOPCCNFMES, IDCONCNFMES
        FROM (SELECT CP.NRORG, CP.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, CP.NRCARDAPIO, CP.CDTIPOPRAT, TP.NMTIPOPRAT, CP.NRSEQPRATCAR, CP.CDFILAUXPRAT, CP.CDPRATO, C.QTCOMESTICAR, 
                     CP.VRPECONSCARD AS CONSUMO_PRATO_ATUAL, ROUND(DECODE(P_CONSUMO,'P',P_VALOR,(P_VALOR/C.QTCOMESTICAR)*100),2) AS CONSUMO_PRATO_NOVO,
                     SUM(CASE WHEN (CP.CDPRATO = P_CDPRATO AND CP.CDFILAUXPRAT = P_CDFILAUXPRAT) AND (IDSOMACOMEST = 'S' AND (IDOPCCNFMES = 'N' AND IDCONCNFMES = 'S')) 
                              THEN ROUND(DECODE(P_CONSUMO,'P',P_VALOR,(P_VALOR/C.QTCOMESTICAR)*100),2) ELSE 0 END)
                        OVER (PARTITION BY CP.CDFILIAL, CP.NRCARDAPIO, CP.CDTIPOPRAT) AS CONSUMO_TOTAL_PRATO_NOVO,
                     SUM(CASE WHEN (CP.CDPRATO = P_CDPRATO AND CP.CDFILAUXPRAT = P_CDFILAUXPRAT) OR (IDSOMACOMEST = 'N' OR (IDOPCCNFMES = 'S' OR IDCONCNFMES = 'N')) 
                              THEN 0 ELSE CP.VRPECONSCARD END) 
                        OVER (PARTITION BY CP.CDFILIAL, CP.NRCARDAPIO, CP.CDTIPOPRAT) AS CONSUMO_OUTROS_PRATOS,
                        PJ.IDSOMACOMEST, CNF.IDOPCCNFMES, CNF.IDCONCNFMES
                FROM COMPCARD CP INNER JOIN PARAMPLANEJA PJ ON PJ.CDFILIAL = CP.CDFILIAL 
                                 INNER JOIN CARDADIA C ON CP.CDFILIAL = C.CDFILIAL AND CP.NRCARDAPIO = C.NRCARDAPIO
                                 INNER JOIN LISTA L ON L.NRLISTA = P_LISTASERV AND C.CDFILIAL = SUBSTR(L.CDLISTA,0,4) AND C.CDSERVFILI = SUBSTR(L.CDLISTA,5,5)
                                 INNER JOIN CNFCAMES CNF ON CNF.CDFILIAL = C.CDFILIAL AND CNF.CDSERVFILI = C.CDSERVFILI AND 
                                                            CNF.DTREFECNFMES = C.DTMESCARD AND CNF.NRSEQCNFMES = CP.NRSEQPRATCAR
                                 INNER JOIN TIPOPRAT TP ON TP.CDTIPOPRAT = CP.CDTIPOPRAT                           
               WHERE (CP.CDTIPOPRAT IN (SELECT CPP.CDTIPOPRAT 
                                          FROM COMPCARD CPP 
                                         WHERE CPP.CDFILIAL = C.CDFILIAL AND CPP.NRCARDAPIO = C.NRCARDAPIO AND CPP.CDPRATO = P_CDPRATO AND CPP.CDFILAUXPRAT = P_CDFILAUXPRAT)
                                        ) AND 
                     (C.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL)
             ) A INNER JOIN PRATO P ON P.CDFILAUXPRAT = A.CDFILAUXPRAT AND P.CDPRATO = A.CDPRATO
       WHERE P.CDPRATO = P_CDPRATO AND P.CDFILAUXPRAT = P_CDFILAUXPRAT AND CONSUMO_PRATO_ATUAL <> CONSUMO_PRATO_NOVO
      ORDER BY CDFILIAL, CDSERVFILI, DTCARDAPIO, NRSEQPRATCAR
   ) LOOP
      IF((C_COMP.VALIDA = 'S') AND (C_COMP.CONSUMO_TOTAL_PRATO_NOVO + C_COMP.CONSUMO_OUTROS_PRATOS > 100)) THEN
         V_CDCHAVE := (C_COMP.CDFILIAL||'|'||C_COMP.CDSERVFILI||'|'||TO_CHAR(C_COMP.DTCARDAPIO,'DD/MM/YYYY')||'|'||C_COMP.CDTIPOPRAT);
         V_MENSAGEM := MSG('CONSISTE_COMENSAIS',C_COMP.CDFILIAL, C_COMP.CDSERVFILI, TO_CHAR(C_COMP.DTCARDAPIO,'DD/MM/YYYY'), C_COMP.CDTIPOPRAT||' - '||C_COMP.NMTIPOPRAT,
                            TO_CHAR(C_COMP.CONSUMO_TOTAL_PRATO_NOVO + C_COMP.CONSUMO_OUTROS_PRATOS)||'% - '||C_COMP.COMENSAIS_NOVO,C_COMP.QTCOMESTICAR);
         INSERE_LISTA_ERROS(C_COMP.CDFILIAL,V_MENSAGEM,P_CDOPERADOR, C_COMP.NRORG, V_NRLOGINTCARD, NULL, V_CDCHAVE);
      ELSE
         UPDATE COMPCARD
            SET VRPECONSCARD = C_COMP.CONSUMO_PRATO_NOVO
          WHERE CDFILIAL = C_COMP.CDFILIAL AND NRCARDAPIO = C_COMP.NRCARDAPIO AND NRSEQPRATCAR = C_COMP.NRSEQPRATCAR; 
      END IF;
   END LOOP;
   COMMIT;
   P_NRLOGINTCARD := V_NRLOGINTCARD;
END;
/