create or replace PROCEDURE INSERE_LOTE_OTIMIZADOR
(
P_NRLISTA VARCHAR2,
P_CDOPERADOR VARCHAR2,
P_DTMESCARD DATE,
P_IDTIPORESTRICAO VARCHAR2,
P_DTREFERENCIA DATE,
P_CDTIPOCUSTO VARCHAR2,
P_CDAGRUPPROD VARCHAR2,
P_NRLOTE OUT VARCHAR2,
P_VERIFICA_ALERTA VARCHAR2 DEFAULT 'N',
P_VERIFICA_CUSTO VARCHAR2 DEFAULT 'N'
)
AS
V_NRCENARIOVINCULO NUMBER;
V_CDFILIAL VARCHAR2(4);
V_PERMITE_CONGELAR VARCHAR2(1);
V_EXISTE_CARDAPIO NUMBER;
V_QTDIAS NUMBER;
V_DIAS_CONG NUMBER;
V_NOVOCODIGO VARCHAR2(20);
V_NOVOCODIGO_LISTA VARCHAR2(20);
BEGIN
   V_CDFILIAL := ' ';
   V_QTDIAS := 0;
   /*INSERE NOVO LOTE*/
   NOVO_CODIGO('GERACAOCARD', V_NOVOCODIGO, 1);
   V_NOVOCODIGO := SUBSTR(V_NOVOCODIGO,11,10);
   INSERT INTO GERACAOCARD (NRGERACAOCARD, CDOPERADOR, DTMESGERACAOCARD, IDSTATUSGERACAO, DTINCLUSAO, IDTIPORESTRICAO)
        VALUES (V_NOVOCODIGO, P_CDOPERADOR, P_DTMESCARD, 'NAO_INICIADO', SYSDATE, P_IDTIPORESTRICAO);
   P_NRLOTE := V_NOVOCODIGO;
   FOR CUR_LISTA IN (SELECT L.CDFILIAL, L.CDSERVFILI, CDTIPOCUSTO, CP.VRCUSTPADR,
                            DECODE(NVL(PJ.VRPEMINDIFPLAPA,0),0,CP.VRCUSTPADR,CP.VRCUSTPADR - CP.VRCUSTPADR * PJ.VRPEMINDIFPLAPA / 100) AS VLR_CUSTO_MIN,
                            DECODE(NVL(PJ.VRPEMAXDIFPLAPA,0),0,CP.VRCUSTPADR,CP.VRCUSTPADR + CP.VRCUSTPADR * PJ.VRPEMAXDIFPLAPA / 100) AS VLR_CUSTO_MAX,
                            TRUNC(NVL(DTBASCONCUSPLAN, SYSDATE)) AS DTREFECUSTO
                       FROM (SELECT SUBSTR(CDLISTA, 1,4)AS CDFILIAL,SUBSTR(CDLISTA, 5,5) AS CDSERVFILI
                               FROM LISTA WHERE NRLISTA = P_NRLISTA
                            ) L LEFT JOIN PARAMPLANEJA PJ ON PJ.CDFILIAL = L.CDFILIAL
                                LEFT JOIN TIPOCUSTO TP ON TP.CDTIPOCUSTO = PJ.CDTIPOCUSTOPLA AND TP.IDUTTPCUSTO = 'S'
                                LEFT JOIN CUSTPADR CP ON CP.CDFILIAL = L.CDFILIAL AND CP.CDSERVFILI = L.CDSERVFILI AND CP.CDFILIAL = PJ.CDFILIAL AND CP.DTREFCUSPADR = P_DTMESCARD
                                LEFT JOIN CALENCARD CL ON CL.CDFILIAL = CP.CDFILIAL AND CL.DTMESCALENCARD = CP.DTREFCUSPADR
                        ORDER BY L.CDFILIAL, L.CDSERVFILI
   )LOOP

      SELECT MAX(CN.NRCENARIOCARD)
        INTO V_NRCENARIOVINCULO
        FROM CENARIOCARD CN
       WHERE CN.CDFILIAL = CUR_LISTA.CDFILIAL AND CN.DTMESCARD = P_DTMESCARD AND
             CN.IDTIPORESTRICAO = P_IDTIPORESTRICAO AND
             EXISTS(SELECT T.CDSERVORIG
                      FROM TIPOPRATCOMUMSERV T
                     WHERE T.CDFILIAL = CUR_LISTA.CDFILIAL AND
                           T.CDSERVFILI = CUR_LISTA.CDSERVFILI AND
                           CN.CDSERVFILI = T.CDSERVORIG);

      SELECT COUNT(*)
        INTO V_EXISTE_CARDAPIO
        FROM CNFCAMES
       WHERE CDFILIAL = CUR_LISTA.CDFILIAL AND
             CDSERVFILI = CUR_LISTA.CDSERVFILI AND
             DTREFECNFMES = P_DTMESCARD;

      IF V_EXISTE_CARDAPIO > 0 THEN
         SELECT CASE WHEN COUNT(*) > 0 THEN 'N' ELSE 'S' END AS PERMITE_CONGELAR
           INTO V_PERMITE_CONGELAR
           FROM(SELECT CN.CDFILIAL, CN.CDSERVFILI, CN.NRSEQCNFMES, CN.CDTIPOPRAT
                  FROM CNFCAMES CN LEFT JOIN CONFCARDMESATIVO CFA ON CFA.CDFILIAL = CN.CDFILIAL AND CFA.CDSERVFILI = CN.CDSERVFILI AND CN.NRSEQCNFMES = CFA.NRSEQCNFCARD
                 WHERE CN.DTREFECNFMES = P_DTMESCARD AND
                       CN.CDFILIAL = CUR_LISTA.CDFILIAL AND
                       CN.CDSERVFILI = CUR_LISTA.CDSERVFILI AND
                       SUBSTR(NVL(CFA.IDMESATIVO,'SSSSSSSSSSSS'), TO_NUMBER(TO_CHAR(TO_DATE(P_DTMESCARD), 'MM')),1) = 'S' AND
                       NOT EXISTS(SELECT CF.CDFILIAL, CF.CDSERVFILI, CF.NRSEQCNFCARD, CF.CDTIPOPRAT
                                    FROM CONFCARD CF
                                   WHERE CN.CDFILIAL = CF.CDFILIAL AND CN.CDSERVFILI = CF.CDSERVFILI AND CN.NRORDCNFMES = CF.NRORDCNFCARD AND CN.CDTIPOPRAT = CF.CDTIPOPRAT)
                UNION ALL
                SELECT CF.CDFILIAL, CF.CDSERVFILI, CF.NRSEQCNFCARD AS NRSEQCNFMES, CF.CDTIPOPRAT
                  FROM CONFCARD CF LEFT JOIN CONFCARDMESATIVO CFA ON CFA.CDFILIAL = CF.CDFILIAL AND CFA.CDSERVFILI = CF.CDSERVFILI AND CFA.NRSEQCNFCARD = CF.NRSEQCNFCARD
                 WHERE CF.CDFILIAL = CUR_LISTA.CDFILIAL AND
                       CF.CDSERVFILI = CUR_LISTA.CDSERVFILI AND
                       SUBSTR(NVL(CFA.IDMESATIVO,'SSSSSSSSSSSS'), TO_NUMBER(TO_CHAR(TO_DATE(P_DTMESCARD), 'MM')),1) = 'S' AND
                       NOT EXISTS(SELECT CN.CDFILIAL, CN.CDSERVFILI, CN.NRSEQCNFMES, CN.CDTIPOPRAT
                                    FROM CNFCAMES CN
                                   WHERE CN.DTREFECNFMES = P_DTMESCARD AND CN.CDFILIAL = CF.CDFILIAL AND CN.CDSERVFILI = CF.CDSERVFILI AND CN.NRORDCNFMES = CF.NRORDCNFCARD AND CN.CDTIPOPRAT = CF.CDTIPOPRAT));

         IF V_PERMITE_CONGELAR = 'S' AND P_CDAGRUPPROD IS NOT NULL THEN
            IF V_CDFILIAL <> CUR_LISTA.CDFILIAL THEN
               V_CDFILIAL := CUR_LISTA.CDFILIAL;

               SELECT MIN(TRUNC(DT.DTDATA)) - TO_DATE(P_DTMESCARD) AS QTDIAS
                 INTO V_QTDIAS
                 FROM DATA DT CROSS JOIN (SELECT TO_DATE(P_DTMESCARD) + NVL(MAX(TO_NUMBER(L.NRLIMISEGU)),0) AS DATALIMITE
                                            FROM PRODUTO P INNER JOIN PRSUBGRPROD S ON P.CDPRODUTO = S.CDPRODUTO AND S.CDGRUPPROD = P_CDAGRUPPROD
                                                           INNER JOIN (SELECT P.CDARVPROD, L.NRLIMISEGU
                                                                         FROM LIMISEGPROD L INNER JOIN PRODUTO P ON P.CDPRODUTO = L.CDPRODUTO AND L.CDFILIAL = CUR_LISTA.CDFILIAL
                                                                      ) L ON P.CDARVPROD LIKE L.CDARVPROD||'%'
                                           WHERE (P.CDPRODUTO = P.CDPRODESTO) AND (P.CDPRODUTO = P.CDPRODESTO) AND
                                                 (NOT EXISTS (SELECT F.CDPRODUTO
                                                                FROM PRODFILI F
                                                               WHERE (F.CDFILIAL = CUR_LISTA.CDFILIAL) AND (F.IDPRODATIV = 'I') AND (F.CDPRODUTO = P.CDPRODUTO)))
                                         ) B
                WHERE NOT EXISTS(SELECT CDFILIAL
                                   FROM FERICOMP
                                  WHERE TRUNC(DTFERICOMP, 'MM') = TO_DATE(P_DTMESCARD) AND CDFILIAL = CUR_LISTA.CDFILIAL AND DTFERICOMP = DT.DTDATA) AND
                                        DT.DTDATA >= B.DATALIMITE + (SELECT COUNT(DTFERICOMP) FROM FERICOMP WHERE (CDFILIAL = CUR_LISTA.CDFILIAL) AND (DTFERICOMP BETWEEN TO_DATE(P_DTMESCARD) AND B.DATALIMITE) );

            END IF;
            V_DIAS_CONG := V_QTDIAS;
         ELSE
            V_DIAS_CONG := 0;
         END IF;
      ELSE
         V_PERMITE_CONGELAR := 'N';
         V_DIAS_CONG := 0;
      END IF;
      INSERT INTO ITGERACAOCARD (NRITGERACAOCARD, NRGERACAOCARD, CDFILIAL, CDSERVFILI, DTREFERENCIA, CDTIPOCUSTO, VRCUSTOPADRAO, VRCUSTOMAX, VRCUSTOMIN, CDGRUPPROD,
                                 IDPERMITECONGELAR, QTDIASCONG, NRCENARIOVINCULO, IDSTATUSGERACAO)
           VALUES (RETORNA_NOVO_CODIGO('ITGERACAOCARD',1),V_NOVOCODIGO, CUR_LISTA.CDFILIAL, CUR_LISTA.CDSERVFILI, NVL(P_DTREFERENCIA, CUR_LISTA.DTREFECUSTO), NVL(P_CDTIPOCUSTO,CUR_LISTA.CDTIPOCUSTO), NVL(CUR_LISTA.VRCUSTPADR,0), NVL(CUR_LISTA.VLR_CUSTO_MAX,0), NVL(CUR_LISTA.VLR_CUSTO_MIN,0), P_CDAGRUPPROD,
                   V_PERMITE_CONGELAR, CASE WHEN V_DIAS_CONG > TO_NUMBER(TO_CHAR(LAST_DAY(P_DTMESCARD), 'DD')) THEN TO_NUMBER(TO_CHAR(LAST_DAY(P_DTMESCARD), 'DD')) ELSE V_DIAS_CONG END, V_NRCENARIOVINCULO, 'NAO_INICIADO');
   END LOOP;
   IF P_VERIFICA_ALERTA = 'S' THEN
      NOVO_CODIGO('LISTA', V_NOVOCODIGO_LISTA, 1);
      V_NOVOCODIGO_LISTA := SUBSTR(V_NOVOCODIGO_LISTA,11,10);
      INSERT INTO LISTA(CDLISTA, NRLISTA)
      (SELECT IT.NRITGERACAOCARD, V_NOVOCODIGO_LISTA
         FROM ITGERACAOCARD IT WHERE IT.NRGERACAOCARD = V_NOVOCODIGO);
      INSERE_ALERTA_OTIMIZADOR(V_NOVOCODIGO_LISTA,P_VERIFICA_CUSTO);
      DELETE FROM LISTA WHERE NRLISTA = V_NOVOCODIGO_LISTA;
   END IF;
   COMMIT;
END;
/
