create or replace PROCEDURE INSERE_ALERTA_OTIMIZADOR
(
P_NRLISTA VARCHAR2, /*LISTA DE ITENS DO LOTE*/
P_VERIFICA_CUSTO VARCHAR2 /*S: SIM, N: NAO*/
)
AS
V_IDUTRPADCA VARCHAR2(1);
V_CDNVPRATO VARCHAR2(1);
V_NOVOCODIGO VARCHAR2(20);
V_EXISTE NUMBER;
V_COUNT_ALERTA NUMBER;
V_CUSTO NUMBER;
V_CUSTO_4 NUMBER;

BEGIN
   DELETE
     FROM ALERTAGERACAOCARD
    WHERE NRITGERACAOCARD IN (SELECT CDLISTA FROM LISTA WHERE NRLISTA = P_NRLISTA);
    COMMIT;
   SELECT IDUTRPADCA
     INTO V_IDUTRPADCA
     FROM PARAMGERAL;

   SELECT MAX(CDNVPRATO)
     INTO V_CDNVPRATO
     FROM PRATO;

   FOR CUR_LISTA IN (SELECT G.IDTIPORESTRICAO, G.DTMESGERACAOCARD, IT.NRITGERACAOCARD, IT.CDFILIAL, IT.CDSERVFILI, IT.CDTIPOCUSTO, IT.DTREFERENCIA
                       FROM ITGERACAOCARD IT INNER JOIN GERACAOCARD G ON IT.NRGERACAOCARD = G.NRGERACAOCARD
                                             INNER JOIN LISTA L ON L.CDLISTA = IT.NRITGERACAOCARD AND L.NRLISTA = P_NRLISTA
   ) LOOP

      FOR CUR_PRATOS IN (SELECT CDFILAUXPRAT, CDPRATO, NMPRATO, NRGRUPOPRATOSERV, TIPORECEITA, IDCOMPLEXIDADE, IDPREPREPARO, IDCOR, NRINGREDIENTEPRINC1, NRINGREDIENTEPRINC2, CDAPRERECE, NRFORMAPREPARO
                           FROM (SELECT P.CDFILAUXPRAT, P.CDPRATO, P.NMPRATO, G.NRGRUPOPRATOSERV, PFA.IDCOMPLEXIDADE, PFA.IDPREPREPARO, PFA.IDCOR, PFA.NRINGREDIENTEPRINC1, PFA.NRINGREDIENTEPRINC2, PFA.CDAPRERECE, PFA.NRFORMAPREPARO,
                                        (SELECT DISTINCT 'S' AS IDTIPO
                                           FROM RECESERV LEFT JOIN PRATSERV ON PRATSERV.CDPRATO = RECESERV.CDPRATO AND PRATSERV.CDFILAUXPRAT = RECESERV.CDFILAUXPRAT AND PRATSERV.CDFILIAL = RECESERV.CDFILIAL AND PRATSERV.CDSERVFILI = RECESERV.CDSERVFILI
                                          WHERE RECESERV.CDFILAUXPRAT = P.CDFILAUXPRAT AND RECESERV.CDPRATO = P.CDPRATO AND RECESERV.CDFILIAL = CUR_LISTA.CDFILIAL AND RECESERV.CDSERVFILI = CUR_LISTA.CDSERVFILI AND NVL(PRATSERV.IDATIVO,'S') = 'S'
                                         UNION ALL
                                         SELECT DISTINCT 'L' AS IDTIPO
                                           FROM RECEFILI LEFT JOIN PRATFILI ON PRATFILI.CDPRATO = RECEFILI.CDPRATO AND PRATFILI.CDFILAUXPRAT = RECEFILI.CDFILAUXPRAT AND PRATFILI.CDFILIAL = RECEFILI.CDFILIAL
                                          WHERE RECEFILI.CDFILAUXPRAT = P.CDFILAUXPRAT AND RECEFILI.CDPRATO = P.CDPRATO AND RECEFILI.CDFILIAL = CUR_LISTA.CDFILIAL AND NVL(PRATFILI.IDATIVO,'S') = 'S' AND
                                                NOT EXISTS (SELECT CDPRATO FROM RECESERV WHERE CDFILAUXPRAT = P.CDFILAUXPRAT AND CDPRATO = P.CDPRATO AND CDFILIAL = CUR_LISTA.CDFILIAL AND CDSERVFILI = CUR_LISTA.CDSERVFILI)
                                         UNION ALL
                                         SELECT DISTINCT 'P' AS IDTIPO
                                           FROM RECEPADR LEFT JOIN PRATPADR ON PRATPADR.CDPRATO = RECEPADR.CDPRATO AND PRATPADR.CDFILAUXPRAT = RECEPADR.CDFILAUXPRAT
                                          WHERE RECEPADR.CDFILAUXPRAT = P.CDFILAUXPRAT AND RECEPADR.CDPRATO = P.CDPRATO AND NVL(PRATPADR.IDATIVO,'S') = 'S' AND V_IDUTRPADCA = 'S' AND
                                                NOT EXISTS (SELECT CDPRATO FROM RECEFILI WHERE CDFILAUXPRAT = P.CDFILAUXPRAT AND CDPRATO = P.CDPRATO AND CDFILIAL = CUR_LISTA.CDFILIAL) AND
                                                NOT EXISTS (SELECT CDPRATO FROM RECESERV WHERE CDFILAUXPRAT = P.CDFILAUXPRAT AND CDPRATO = P.CDPRATO AND CDFILIAL = CUR_LISTA.CDFILIAL AND CDSERVFILI = CUR_LISTA.CDSERVFILI)) AS TIPORECEITA
                                   FROM PRATO P LEFT JOIN (SELECT GS.NRGRUPOPRATOSERV, G.NMGRUPOPRATO, IT.NRITGRUPOPRATOSERV, IT.CDPRATO, IT.CDFILAUXPRAT
                                                             FROM GRUPOPRATO G INNER JOIN GRUPOPRATOSERV GS ON G.NRGRUPOPRATO = GS.NRGRUPOPRATO AND G.IDTIPORESTRICAO = CUR_LISTA.IDTIPORESTRICAO AND
                                                                                                               G.CDFILIAL = CUR_LISTA.CDFILIAL AND GS.CDSERVFILI = CUR_LISTA.CDSERVFILI
                                                                               INNER JOIN ITGRUPOPRATOSERV IT ON IT.NRGRUPOPRATOSERV = GS.NRGRUPOPRATOSERV
                                                           ) G ON G.CDFILAUXPRAT = P.CDFILAUXPRAT AND G.CDPRATO = P.CDPRATO
                                                LEFT JOIN PARAMPRATFILIAL PFA ON PFA.CDFILIAL = CUR_LISTA.CDFILIAL AND PFA.CDFILAUXPRAT = P.CDFILAUXPRAT AND PFA.CDPRATO = P.CDPRATO
                                  WHERE (P.CDFILAUXPRAT = 'P' OR P.CDFILAUXPRAT = CUR_LISTA.CDFILIAL) AND (P.CDNVPRATO = V_CDNVPRATO) AND (P.IDATIVO = 'S')
                                )
                          WHERE TIPORECEITA IS NOT NULL
      )LOOP
         IF CUR_PRATOS.NRGRUPOPRATOSERV IS NULL THEN
            SELECT COUNT(GS.NRGRUPOPRATOSERV)
              INTO V_EXISTE
              FROM ITGRUPOPRATOSERV IT INNER JOIN GRUPOPRATOSERV GS ON IT.NRGRUPOPRATOSERV = GS.NRGRUPOPRATOSERV
                                       INNER JOIN GRUPOPRATO G ON G.NRGRUPOPRATO = GS.NRGRUPOPRATO
             WHERE IT.CDPRATO = CUR_PRATOS.CDPRATO AND IT.CDFILAUXPRAT = CUR_PRATOS.CDFILAUXPRAT AND G.CDFILIAL = CUR_LISTA.CDFILIAL AND G.IDTIPORESTRICAO = CUR_LISTA.IDTIPORESTRICAO;
            IF V_EXISTE = 0 THEN
               NOVO_CODIGO('ALERTAGERACAOCARD', V_NOVOCODIGO, 1);
               V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
               INSERT INTO ALERTAGERACAOCARD (NRALERTAGERACAOCARD, NRITGERACAOCARD, IDTIPOALERTA, CODALERTA, DSALERTA, DTINCLUSAO)
               VALUES (V_NOVOCODIGO, CUR_LISTA.NRITGERACAOCARD, 'PRATO_SEM_GRUPO', CASE WHEN CUR_PRATOS.CDFILAUXPRAT = 'P' THEN 'P' ELSE 'L' END ||' - '||FORMATPRATMASK(CUR_PRATOS.CDPRATO),CUR_PRATOS.NMPRATO , SYSDATE);
            END IF;
         END IF;
         IF CUR_PRATOS.NRGRUPOPRATOSERV IS NOT NULL THEN
            FOR C_COLUNAS IN(SELECT CF.NRSEQCNFCARD, CF.IDUTILPERCENTCONS, PF.NRATRATIVIDADEPRATO, H.NRHISTCONSUMOPRATO
                               FROM CONFCARDGRUPOPRATO CG INNER JOIN CONFCARD CF ON CF.CDFILIAL = CG.CDFILIAL AND CF.CDSERVFILI = CG.CDSERVFILI AND CF.NRSEQCNFCARD = CG.NRSEQCNFCARD
                                                          LEFT JOIN CONFCARDMESATIVO CFA ON CFA.CDFILIAL = CG.CDFILIAL AND CFA.CDSERVFILI = CG.CDSERVFILI AND CFA.NRSEQCNFCARD = CG.NRSEQCNFCARD
                                                          LEFT JOIN PARAMPRATFILIAL PF ON PF.CDFILIAL = CG.CDFILIAL AND PF.CDFILAUXPRAT = CUR_PRATOS.CDFILAUXPRAT AND PF.CDPRATO = CUR_PRATOS.CDPRATO
                                                          LEFT JOIN HISTCONSUMOPRATO H ON H.CDFILIAL = CG.CDFILIAL AND H.CDSERVFILI = CG.CDSERVFILI AND H.CDPRATO = CUR_PRATOS.CDPRATO AND H.CDFILAUXPRAT = CUR_PRATOS.CDFILAUXPRAT
                              WHERE CG.CDFILIAL = CUR_LISTA.CDFILIAL AND CF.CDSERVFILI = CUR_LISTA.CDSERVFILI AND CG.NRGRUPOPRATOSERV = CUR_PRATOS.NRGRUPOPRATOSERV AND
                                    SUBSTR(NVL(CFA.IDMESATIVO,'SSSSSSSSSSSS'), TO_NUMBER(TO_CHAR(TO_DATE(CUR_LISTA.DTMESGERACAOCARD), 'MM')),1) = 'S'
            )LOOP
               IF C_COLUNAS.IDUTILPERCENTCONS = 'ATRATIVIDADE' AND C_COLUNAS.NRATRATIVIDADEPRATO IS NULL THEN
                  NOVO_CODIGO('ALERTAGERACAOCARD', V_NOVOCODIGO, 1);
                  V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
                  INSERT INTO ALERTAGERACAOCARD (NRALERTAGERACAOCARD, NRITGERACAOCARD, IDTIPOALERTA, CODALERTA, DSALERTA, DTINCLUSAO)
                  VALUES (V_NOVOCODIGO, CUR_LISTA.NRITGERACAOCARD, 'PRATO_SEM_ATRATIVIDADE', CASE WHEN CUR_PRATOS.CDFILAUXPRAT = 'P' THEN 'P' ELSE 'L' END ||' - '||FORMATPRATMASK(CUR_PRATOS.CDPRATO),CUR_PRATOS.NMPRATO , SYSDATE);
               END IF;

               IF C_COLUNAS.IDUTILPERCENTCONS = 'HISTORICO' AND C_COLUNAS.NRHISTCONSUMOPRATO IS NULL THEN
                  SELECT COUNT(*)
                    INTO V_EXISTE
                    FROM COMPCARD CP INNER JOIN CARDADIA C ON CP.CDFILIAL = C.CDFILIAL AND CP.NRCARDAPIO = C.NRCARDAPIO AND C.CDFILIAL = CUR_LISTA.CDFILIAL AND C.CDSERVFILI = CUR_LISTA.CDSERVFILI
                                     INNER JOIN SERVICO S ON S.CDFILIAL = C.CDFILIAL AND S.CDSERVFILI = C.CDSERVFILI
                   WHERE CP.CDPRATO = CUR_PRATOS.CDPRATO AND CP.CDFILAUXPRAT = CUR_PRATOS.CDFILAUXPRAT AND
                         (C.DTMESCARD BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MM'),(NVL(NRMESHISTCONS,6)*-1)) AND CUR_LISTA.DTMESGERACAOCARD);
                  IF V_EXISTE = 0 THEN
                     NOVO_CODIGO('ALERTAGERACAOCARD', V_NOVOCODIGO, 1);
                     V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
                     INSERT INTO ALERTAGERACAOCARD (NRALERTAGERACAOCARD, NRITGERACAOCARD, IDTIPOALERTA, CODALERTA, DSALERTA, DTINCLUSAO)
                     VALUES (V_NOVOCODIGO, CUR_LISTA.NRITGERACAOCARD, 'PRATO_SEM_HISTORICO', CASE WHEN CUR_PRATOS.CDFILAUXPRAT = 'P' THEN 'P' ELSE 'L' END ||' - '||FORMATPRATMASK(CUR_PRATOS.CDPRATO),CUR_PRATOS.NMPRATO , SYSDATE);
                  END IF;
               END IF;
            END LOOP;
            IF CUR_PRATOS.IDCOMPLEXIDADE IS NULL OR CUR_PRATOS.IDPREPREPARO IS NULL OR CUR_PRATOS.IDCOR IS NULL OR CUR_PRATOS.NRINGREDIENTEPRINC1 IS NULL OR
               CUR_PRATOS.NRINGREDIENTEPRINC2 IS NULL OR CUR_PRATOS.CDAPRERECE IS NULL OR CUR_PRATOS.NRFORMAPREPARO IS NULL THEN
               NOVO_CODIGO('ALERTAGERACAOCARD', V_NOVOCODIGO, 1);
               V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
               INSERT INTO ALERTAGERACAOCARD (NRALERTAGERACAOCARD, NRITGERACAOCARD, IDTIPOALERTA, CODALERTA, DSALERTA, DTINCLUSAO)
               VALUES (V_NOVOCODIGO, CUR_LISTA.NRITGERACAOCARD, 'PRATO_SEM_CARACTERISTICA', CASE WHEN CUR_PRATOS.CDFILAUXPRAT = 'P' THEN 'P' ELSE 'L' END ||' - '||FORMATPRATMASK(CUR_PRATOS.CDPRATO),CUR_PRATOS.NMPRATO , SYSDATE);
            END IF;

            IF P_VERIFICA_CUSTO = 'S' THEN
               BEGIN
                  SELECT CUSTO_PRATO(CUR_LISTA.CDFILIAL, CUR_LISTA.CDFILIAL, CUR_LISTA.CDTIPOCUSTO, CUR_LISTA.DTREFERENCIA, CUR_PRATOS.CDFILAUXPRAT, CUR_PRATOS.CDPRATO, CUR_LISTA.CDSERVFILI, 'S', NULL, NULL, -3) VRPRATO,
                         CUSTO_PRATO(CUR_LISTA.CDFILIAL, CUR_LISTA.CDFILIAL, CUR_LISTA.CDTIPOCUSTO, ADD_MONTHS(CUR_LISTA.DTREFERENCIA,-4), CUR_PRATOS.CDFILAUXPRAT, CUR_PRATOS.CDPRATO, CUR_LISTA.CDSERVFILI, 'S', NULL, NULL, 0) VRPRATO_DESATU
                    INTO V_CUSTO, V_CUSTO_4
                    FROM DUAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_CUSTO := 0;
                     V_CUSTO_4 := 0;
               END;

               IF V_CUSTO_4 <= 0 OR V_CUSTO = 0 THEN
                  NOVO_CODIGO('ALERTAGERACAOCARD', V_NOVOCODIGO, 1);
                  V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
                  INSERT INTO ALERTAGERACAOCARD (NRALERTAGERACAOCARD, NRITGERACAOCARD, IDTIPOALERTA, CODALERTA, DSALERTA, DTINCLUSAO)
                  VALUES (V_NOVOCODIGO, CUR_LISTA.NRITGERACAOCARD, 'PRATO_CUSTO_ZERO', CASE WHEN CUR_PRATOS.CDFILAUXPRAT = 'P' THEN 'P' ELSE 'L' END ||' - '||FORMATPRATMASK(CUR_PRATOS.CDPRATO),CUR_PRATOS.NMPRATO , SYSDATE);
               ELSIF V_CUSTO <= 0 THEN
                  NOVO_CODIGO('ALERTAGERACAOCARD', V_NOVOCODIGO, 1);
                  V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
                  INSERT INTO ALERTAGERACAOCARD (NRALERTAGERACAOCARD, NRITGERACAOCARD, IDTIPOALERTA, CODALERTA, DSALERTA, DTINCLUSAO)
                  VALUES (V_NOVOCODIGO, CUR_LISTA.NRITGERACAOCARD, 'PRATO_CUSTO_DESATU', CASE WHEN CUR_PRATOS.CDFILAUXPRAT = 'P' THEN 'P' ELSE 'L' END ||' - '||FORMATPRATMASK(CUR_PRATOS.CDPRATO),CUR_PRATOS.NMPRATO , SYSDATE);
               END IF;
            END IF;
         END IF;
      END LOOP;

      SELECT COUNT(1)
        INTO V_COUNT_ALERTA
        FROM ALERTAGERACAOCARD
       WHERE NRITGERACAOCARD = CUR_LISTA.NRITGERACAOCARD;

      UPDATE ITGERACAOCARD
         SET IDSTATUSALERTA = CASE WHEN V_COUNT_ALERTA = 0 THEN 'OK' ELSE 'ERRO' END,
             DTALERTA = SYSDATE
       WHERE NRITGERACAOCARD = CUR_LISTA.NRITGERACAOCARD;
   END LOOP;
   COMMIT;
END;
/