CREATE OR REPLACE PROCEDURE ETLRESUFOOD_PASSFUTU (P_DTINI  IN DATE, P_DTFINA IN DATE)
IS
   V_COUNTER            NUMBER NOT NULL := 0;
   V_DTFIM              DATE;
   V_DTINI              DATE;
   V_DTINIANT           DATE;
   V_DATAFINAL          DATE;
   V_NRTIPOCUSTO1       NUMBER;
   V_NRTIPOCUSTO2       NUMBER;
   V_NRTIPOCUSTO3       NUMBER;
   V_VRCUSTPADR         FLOAT NOT NULL := 0;
   V_VRPRECVENDA        FLOAT NOT NULL := 0;
   V_ZERO               FLOAT := 0;
   V_CDSEQUENCIAL       VARCHAR2(20);
   V_CDCLIENTE          VARCHAR2(21) := NULL;
   V_MES                VARCHAR2(10);
   V_ANO                VARCHAR2(10);
   V_ANOANT             VARCHAR2(04);
   V_MESANT             VARCHAR2(02);
   V_BIM                VARCHAR2(10);
   V_TRI                VARCHAR2(10);
   V_SEM                VARCHAR2(10);
   V_DATACUSTOBI        VARCHAR2(01);
   V_VRCUSTPLANTOTAL1   FLOAT;
   V_VRCUSTPLANTOTAL2   FLOAT;
   V_VRCUSTPLANTOTAL3   FLOAT;
   V_VRPERCPLANTOTAL1   FLOAT;
   V_VRPERCPLANTOTAL2   FLOAT;
   V_VRPERCPLANTOTAL3   FLOAT;
   V_VRPERCREALTOTAL1   FLOAT;
   V_VRPERCREALTOTAL2   FLOAT;
   V_VRPERCREALTOTAL3   FLOAT;
   V_VRCUSTREALTOTAL1   FLOAT;
   V_VRCUSTREALTOTAL2   FLOAT;
   V_VRCUSTREALTOTAL3   FLOAT;
   V_VRTOTALCUSTOSTD    FLOAT;
   V_VRTOTFCPLAN1       FLOAT;
   V_VRTOTFCPLAN2       FLOAT;
   V_VRTOTFCPLAN3       FLOAT;
   V_VRTOTFCREAL1       FLOAT;
   V_VRTOTFCREAL2       FLOAT;
   V_VRTOTFCREAL3       FLOAT;
   V_VRTOTVENDSERV      FLOAT;
   V_VRTOTCUSTPADREST   FLOAT;
   V_VRTOTCUSTPADREFE   FLOAT;
   V_NMTIPOCUSTO1       TIPOCUSTO.NMTIPOCUSTO%TYPE;
   V_NMTIPOCUSTO2       TIPOCUSTO.NMTIPOCUSTO%TYPE;
   V_NMTIPOCUSTO3       TIPOCUSTO.NMTIPOCUSTO%TYPE;
   V_DTULTCOMEFE        DATE;

BEGIN

   V_MES          := TO_CHAR(P_DTINI,'MM');
   V_ANO          :=  TO_CHAR(P_DTINI,'YYYY');
   V_NMTIPOCUSTO1 := ' ';
   V_NMTIPOCUSTO2 := ' ';
   V_NMTIPOCUSTO3 := ' ';
   V_DTINI        := ADD_MONTHS(LAST_DAY(P_DTINI) + 1, -1);

 FOR C_DEL_SRV IN (SELECT ROWID REG_DEL
                     FROM DM_RESUFOOD_PASSFUTU
                    WHERE TO_DATE('01'||CDMESBI||CDANOBI, 'DD/MM/YYYY') >= V_DTINI)LOOP

      V_COUNTER := V_COUNTER +1;

      DELETE FROM DM_RESUFOOD_PASSFUTU WHERE C_DEL_SRV.REG_DEL = ROWID;

      IF V_COUNTER >= 2000 THEN
         COMMIT;
         V_COUNTER := 0;
      END IF;
   END LOOP;
   COMMIT;
   V_COUNTER := 0;

   SELECT IDDATACUSTOBI INTO V_DATACUSTOBI FROM PARAMGERAL;

   FOR C_NRTPCUS IN
      (SELECT ROWNUM AS LINHA, NRTIPOCUSTO, NMTIPOCUSTO
         FROM(SELECT TO_NUMBER(CDTIPOCUSTO) NRTIPOCUSTO, NMTIPOCUSTO
                FROM TIPOCUSTO
               WHERE IDTPCUSTOGSC = 'S'
               ORDER BY NRTIPOCUSTO))LOOP

      IF C_NRTPCUS.LINHA = 1 THEN
         V_NRTIPOCUSTO1    := C_NRTPCUS.NRTIPOCUSTO;
         V_NMTIPOCUSTO1    := C_NRTPCUS.NMTIPOCUSTO;
      ELSIF C_NRTPCUS.LINHA = 2 THEN
         V_NRTIPOCUSTO2    := C_NRTPCUS.NRTIPOCUSTO;
         V_NMTIPOCUSTO2    := C_NRTPCUS.NMTIPOCUSTO;
      ELSIF C_NRTPCUS.LINHA = 3 THEN
         V_NRTIPOCUSTO3    := C_NRTPCUS.NRTIPOCUSTO;
         V_NMTIPOCUSTO3    := C_NRTPCUS.NMTIPOCUSTO;
      END IF;
   END LOOP;

   WHILE (V_DTINI  <=  P_DTFINA) LOOP

      V_DTFIM      :=   LAST_DAY( V_DTINI);
      V_MES        :=   TO_CHAR(V_DTFIM,'MM');
      V_ANO        :=   TO_CHAR(V_DTFIM,'YYYY');
      V_BIM        :=   TO_CHAR(TRUNC((V_MES - 1) / 2) + 1);
      V_TRI        :=   TO_CHAR(TRUNC((V_MES - 1) / 3) + 1);
      V_SEM        :=   TO_CHAR(TRUNC((V_MES - 1) / 6) + 1);
            
      V_DATAFINAL := TRUNC(V_DTFIM);

      FOR C_ETL IN (SELECT FL.CDFILIAL, FL.NMFILIAL, FL.DTINICIALBI, FL.CDEMPRESA, EM.NMEMPRESA,
                           P.IDCOMCUSPERC, TRUNC(DECODE((SELECT IDUTILDTPROC FROM PARAMGERAL WHERE CDPARGERAL = '0001'), 'N', SYSDATE, NVL(P.DTPROCESSA,SYSDATE))) AS DTPROCESSA,
                           FL.CDAREAGERE, NVL(AREAGERE.NMAREAGERE,'SEM CADASTRO') NMAREAGERE,
                           FL.CDSUPERVISOR, NVL(SUPERVISOR.NMOPERADOR,'SEM CADASTRO') NMSUPERVISOR,
                           FL.CDGOPERAC, NVL(GERENTE_OPERACIONAL.NMOPERADOR,'SEM CADASTRO') NMGERENTE_OPERACIONAL,
                           FL.CDSEGMER, NVL(SEGMERCADO.DSSEGMER,'SEM CADASTRO') NMSEGMERCADO,
                           CDPLANEJADORA, NVL(PLANEJADORA.NMOPERADOR,'SEM CADASTRO') NMPLANEJADORA,
                           CDGUNIDADE, NVL(GERENTE_UNIDADE.NMOPERADOR,'SEM CADASTRO') NMGUNIDADE
                      FROM FILIAL FL
                     INNER JOIN PARAMFILIAL P
                        ON P.CDFILIAL = FL.CDFILIAL
                     INNER JOIN EMPRESA EM
                        ON EM.CDEMPRESA = FL.CDEMPRESA
                      LEFT JOIN OPERADOR SUPERVISOR
                        ON FL.CDSUPERVISOR = SUPERVISOR.CDOPERADOR
                      LEFT JOIN SEGMERCADO
                        ON FL.CDSEGMER = SEGMERCADO.CDSEGMER
                      LEFT JOIN OPERADOR GERENTE_OPERACIONAL
                        ON FL.CDGOPERAC = GERENTE_OPERACIONAL.CDOPERADOR
                      LEFT JOIN AREAGERE
                        ON FL.CDAREAGERE = AREAGERE.CDAREAGERE
                      LEFT JOIN OPERADOR PLANEJADORA
                        ON FL.CDPLANEJADORA = PLANEJADORA.CDOPERADOR
                      LEFT JOIN OPERADOR GERENTE_UNIDADE
                        ON FL.CDGUNIDADE = GERENTE_UNIDADE.CDOPERADOR
                     WHERE FL.IDFUNCAOFILI IS NULL
                       AND FL.DTINICIALBI  IS NOT NULL
                       AND FL.CDFILIAL NOT IN(SELECT CDFILIAL
                                                    FROM FILIAL
                                                   WHERE IDFILCENTPME = 'S'
                                                     AND IDTPFILIALCENT = 'M')
                       AND FL.CDFILIAL NOT IN(SELECT  FL.CDFILIAL CDMERENDA
                                                    FROM FILIAL FL
                                                   INNER JOIN (SELECT CDFILIAL CDCENTRAL, NMFILIAL NMCENTRAL FROM FILIAL WHERE IDFILCENTPME = 'S' AND IDTPFILIALCENT = 'M') CENTRAL
                                                      ON FL.CDFILCENTPME = CENTRAL.CDCENTRAL
                                                   INNER JOIN PARAMFILIAL P
                                                      ON P.CDFILIAL = CENTRAL.CDCENTRAL
                                                   INNER JOIN EMPRESA EMP
                                                      ON EMP.CDEMPRESA = FL.CDEMPRESA))LOOP

         IF ADD_MONTHS(LAST_DAY(C_ETL.DTINICIALBI) + 1, -1) <= V_DTINI THEN

            NOVO_CODIGO('CONSPLAN'||C_ETL.CDFILIAL, V_CDSEQUENCIAL,1);
            V_CDSEQUENCIAL := SUBSTR(V_CDSEQUENCIAL,11,10);

            CONSUMO_PLANEJADO_PLA(C_ETL.CDFILIAL,'T', V_DTINI, V_DATAFINAL, 'T', 'CFP', 'N', 0, 'N', V_CDSEQUENCIAL, '0', 'zzzzzzzzzz', '',  '_'||TO_CHAR(V_DTINI, 'YYYYMMDD')||'_', '_'||TO_CHAR(V_DATAFINAL, 'YYYYMMDD')||'_' );

            FOR C_SERVICO IN(SELECT S.CDSERVFILI, S.NMSERVICO, S.CDGRUPSERV, S.IDSERVPRINC, S.IDINDICADOR,
                                    SUM(S.QTCOMESTICAR) AS QTCOMESTICAR, SUM(S.QTCOMEFETCAR) AS QTCOMEFETCAR
                               FROM( --PEDRO
                                    SELECT SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV,  SE.IDSERVPRINC, SE.IDINDICADOR,
                                    NVL(SUM(DECODE(C_ETL.IDCOMCUSPERC, 'C', CA.QTCLIESTICAR, CA.QTCOMESTICAR)),0) AS QTCOMESTICAR,
                                    NVL(SUM(DECODE(C_ETL.IDCOMCUSPERC, 'C', CA.QTCLIEFETCAR, CA.QTCOMEFETCAR)),0) AS QTCOMEFETCAR
                               FROM CARDADIA CA, SERVICO SE
                              WHERE CA.CDFILIAL = C_ETL.CDFILIAL
                                AND CA.CDFILIAL = SE.CDFILIAL
                                AND CA.CDSERVFILI = SE.CDSERVFILI
                                AND CA.DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL
                              GROUP BY SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV, SE.IDSERVPRINC, SE.IDINDICADOR
                              
                              UNION --jefferson

                             SELECT SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV,  SE.IDSERVPRINC, SE.IDINDICADOR,
                                    0 AS QTCOMESTICAR,
                                    0 AS QTCOMEFETCAR
                                    FROM CONSPLAN CP, SERVICO SE
                              WHERE CP.CDFILICPLA = C_ETL.CDFILIAL
                                AND CP.CDFILICPLA = SE.CDFILIAL
                                AND CP.CDSERVCPLA = SE.CDSERVFILI
                                AND CP.DTCONSPLAN BETWEEN V_DTINI AND V_DATAFINAL
                                AND CP.NRCONSPLAN = V_CDSEQUENCIAL
                                AND CP.IDCONSPLAN = 'F'
                              GROUP BY SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV, SE.IDSERVPRINC, SE.IDINDICADOR)S
                            GROUP BY S.CDSERVFILI, S.NMSERVICO, S.CDGRUPSERV, S.IDSERVPRINC, S.IDINDICADOR) LOOP

               BEGIN
                  SELECT MAX(DTCARDAPIO) DTULTCOMEFE
                    INTO V_DTULTCOMEFE
                    FROM CARDADIA CA, SERVICO SE
                   WHERE CA.CDFILIAL   = C_ETL.CDFILIAL
                     AND CA.CDFILIAL   = SE.CDFILIAL
                     AND CA.CDSERVFILI = SE.CDSERVFILI
                     AND QTCOMEFETCAR > 0
                     AND CA.DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL
                     AND SE.CDSERVFILI = C_SERVICO.CDSERVFILI
                   GROUP BY SE.CDSERVFILI;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_DTULTCOMEFE := '';
               END;

               BEGIN
                  SELECT VRCUSTPADR
                    INTO V_VRCUSTPADR
                    FROM CUSTPADR
                   WHERE CDFILIAL     = C_ETL.CDFILIAL
                     AND CDSERVFILI   = C_SERVICO.CDSERVFILI
                     AND DTREFCUSPADR = V_DTINI;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_VRCUSTPADR := V_ZERO;
               END;

               BEGIN
                  SELECT CF.CDCLIENTE
                    INTO V_CDCLIENTE
                    FROM CLIENFILIAL CF, CLIENTE B
                   WHERE CF.CDFILIAL = C_ETL.CDFILIAL
                     AND CF.CDCLIENTE = B.CDCLIENTE
                     AND CF.IDCLIENPRIN = 'S';
               EXCEPTION
                  WHEN OTHERS THEN
                     V_CDCLIENTE := NULL;
               END;

               V_VRPRECVENDA := V_ZERO;
               IF (V_CDCLIENTE IS NOT NULL) THEN
                  BEGIN
                     SELECT MAX(VRSERVFATHIS) VRPRECOVEND
                       INTO V_VRPRECVENDA
                       FROM HISTSERVFATU
                      WHERE CDFILIAL     = C_ETL.CDFILIAL
                        AND CDSERVFILI   = C_SERVICO.CDSERVFILI
                        AND CDCLIENTE    = V_CDCLIENTE
                        AND DTSRVFATUHIS = (SELECT MAX(DTSRVFATUHIS)
                                              FROM HISTSERVFATU
                                             WHERE CDFILIAL     = C_ETL.CDFILIAL
                                               AND CDCLIENTE    = V_CDCLIENTE
                                               AND CDSERVFILI   = C_SERVICO.CDSERVFILI
                                               AND TRUNC(DTSRVFATUHIS) <= V_DATAFINAL)
                        AND DTALTEFATHIS = (SELECT MAX(DTALTEFATHIS)
                                              FROM HISTSERVFATU H, (SELECT MAX(DTSRVFATUHIS) AS DTSRVFATUHIS
                                                                      FROM HISTSERVFATU
                                                                     WHERE CDFILIAL = C_ETL.CDFILIAL
                                                                       AND CDCLIENTE = V_CDCLIENTE
                                                                       AND CDSERVFILI = C_SERVICO.CDSERVFILI
                                                                       AND DTSRVFATUHIS <= V_DATAFINAL) X
                                             WHERE H.CDFILIAL     = C_ETL.CDFILIAL
                                               AND H.CDCLIENTE    = V_CDCLIENTE
                                               AND H.CDSERVFILI   = C_SERVICO.CDSERVFILI
                                               AND H.DTSRVFATUHIS = X.DTSRVFATUHIS);
                  EXCEPTION
                     WHEN OTHERS THEN
                        V_VRPRECVENDA := V_ZERO;
                  END;
               END IF;

               FOR C_PRODUTO IN(
                  SELECT
                     PR.CDFILIAL,
                     PR.CDSERVFILI,
                     SUM(PR.CUSTOPLAN1) AS CUSTOPLAN1,
                     SUM(PR.CUSTOPLAN2) AS CUSTOPLAN2,
                     SUM(PR.CUSTOPLAN3) AS CUSTOPLAN3,
                     SUM(PR.CUSTOREAL1) AS CUSTOREAL1,
                     SUM(PR.CUSTOREAL2) AS CUSTOREAL2,
                     SUM(PR.CUSTOREAL3) AS CUSTOREAL3,
                     GPS.CDGRUPSERV,
                     GPS.NMGRUPSERV,
                     SE.IDSERVPRINC
                  FROM
                     FILIAL FI, SERVICO SE, GRUPSERV GPS,
                     (SELECT
                        CDFILIAL,
                        CDSERVFILI,
                        DTCARDAPIO
                      FROM
                        CARDADIA
                      WHERE
                        CDFILIAL = C_ETL.CDFILIAL AND
                        (CDSERVFILI = C_SERVICO.CDSERVFILI) AND
                        (DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                      GROUP BY
                        CDFILIAL,
                        CDSERVFILI,
                        DTCARDAPIO) CD,
                     (SELECT
                        CDFILIAL,
                        CDSERVFILI,
                        DTCARDAPIO,
                        SUM(CUSTOPLAN1) CUSTOPLAN1,
                        SUM(CUSTOPLAN2) CUSTOPLAN2,
                        SUM(CUSTOPLAN3) CUSTOPLAN3,
                        SUM(CUSTOREAL1) CUSTOREAL1,
                        SUM(CUSTOREAL2) CUSTOREAL2,
                        SUM(CUSTOREAL3) CUSTOREAL3,
                        CDPRODUTO
                      FROM
                        (SELECT
                           CP.CDFILICPLA CDFILIAL,
                           CP.CDSERVCPLA CDSERVFILI,
                           CP.DTCONSPLAN DTCARDAPIO,
                           (CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO1,
                                          DECODE(V_DATACUSTOBI, '0', CD.DTREFECUSTMES, CP.DTCONSPLAN),
                                          SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN1,
                           (CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO2,
                                          DECODE(V_DATACUSTOBI, '0', CD.DTREFECUSTMES, CP.DTCONSPLAN),
                                          SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN2,
                           (CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO3,
                                          DECODE(V_DATACUSTOBI, '0', CD.DTREFECUSTMES, CP.DTCONSPLAN),
                                          SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN3,
                           0 AS CUSTOREAL1,
                           0 AS CUSTOREAL2,
                           0 AS CUSTOREAL3,
                           CP.CDPRODCPLA CDPRODUTO
                        FROM
                           CONSPLAN CP, PARAMGERAL PG,
                           (SELECT CD.CDFILIAL, CD.CDSERVFILI, CD.DTMESCARD, MAX(CM.DTREFECUSTMES) AS DTREFECUSTMES
                            FROM CARDADIA CD, CARDAMES CM
                            WHERE (CD.CDFILIAL = C_ETL.CDFILIAL)
                              AND (CD.CDSERVFILI = C_SERVICO.CDSERVFILI)
                              AND (CD.DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                              AND (CD.CDFILIAL   = CM.CDFILIAL(+)     )
                              AND (CD.CDSERVFILI = CM.CDSERVFILI(+)   )
                              AND (CD.DTMESCARD  = CM.DTMESCARDAPIO(+))
                            GROUP BY CD.CDFILIAL, CD.CDSERVFILI, CD.DTMESCARD) CD
                        WHERE
                           (CP.CDFILICPLA   = C_ETL.CDFILIAL)   AND
                           (CP.NRCONSPLAN   = V_CDSEQUENCIAL)   AND
                           (CD.CDFILIAL     = CP.CDFILICPLA)    AND
                           (CD.CDSERVFILI   = CP.CDSERVCPLA) AND
                           (PG.CDPARGERAL   = '0001')        AND
                           (CP.IDCONSPLAN <> 'F') AND 
                           (CD.DTMESCARD    = ADD_MONTHS(LAST_DAY(CP.DTCONSPLAN) + 1, -1))
                        UNION ALL
                        SELECT
                           CD.CDFILIAL,
                           CD.CDSERVFILI,
                           CD.DTCARDAPIO,
                           DECODE(V_NRTIPOCUSTO1, NULL, 0, ROUND(CNE.VRPLANNESTO * (RNE.PERATENESTO/100) * (CD.QTCOMESTICAR/H.TOTCOMEN), 5)) AS CUSTOPLAN1,
                           DECODE(V_NRTIPOCUSTO2, NULL, 0, ROUND(CNE.VRPLANNESTO * (RNE.PERATENESTO/100) * (CD.QTCOMESTICAR/H.TOTCOMEN), 5)) AS CUSTOPLAN2,
                           DECODE(V_NRTIPOCUSTO3, NULL, 0, ROUND(CNE.VRPLANNESTO * (RNE.PERATENESTO/100) * (CD.QTCOMESTICAR/H.TOTCOMEN), 5)) AS CUSTOPLAN3,
                           0 AS CUSTOREAL1,
                           0 AS CUSTOREAL2,
                           0 AS CUSTOREAL3,
                           CNE.CDPRODUTO AS CDPRODUTO
                        FROM
                           (SELECT
                              DTMESCARD,
                              CDSERVFILI,
                              SUM(QTCOMESTICAR) AS TOTCOMEN
                           FROM
                              CARDADIA
                           WHERE
                              (CDFILIAL = C_ETL.CDFILIAL) AND
                              (CDSERVFILI = C_SERVICO.CDSERVFILI) AND
                              (DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                           GROUP BY
                              DTMESCARD,
                              CDSERVFILI
                           ) H,
                           CARDADIA  CD,
                           RATENESTO RNE,
                           CUSTNESTO CNE,
                           DATA      DT
                        WHERE
                           (DT.DTDATA BETWEEN V_DTINI AND V_DATAFINAL)                            AND
                           (TO_CHAR(DT.DTDATA, 'MMYYYY') = TO_CHAR(CNE.DTCUSTNESTO, 'MMYYYY')) AND
                           (CNE.CDFILIAL     = C_ETL.CDFILIAL)                                AND
                           (RNE.CDSERVFILI = C_SERVICO.CDSERVFILI)                            AND
                           (CNE.CDPRODUTO    = RNE.CDPRODUTO)                                  AND
                           (CD.CDFILIAL      = RNE.CDFILIAL)                                   AND
                           (CD.CDSERVFILI    = RNE.CDSERVFILI)                                 AND
                           (CD.DTCARDAPIO    = DT.DTDATA)                                      AND
                           (H.DTMESCARD      = CD.DTMESCARD)                                   AND
                           (H.CDSERVFILI     = RNE.CDSERVFILI)                                 AND
                           (NOT H.TOTCOMEN IS NULL AND H.TOTCOMEN <> 0)
                        UNION ALL
                        SELECT
                           C.CDFILIAL,
                           C.CDSERVFILI,
                           C.DTCARDAPIO,
                           0 CUSTOPLAN1,
                           0 CUSTOPLAN2,
                           0 CUSTOPLAN3,
                           DECODE(V_NRTIPOCUSTO1, NULL, 0, (C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, V_NRTIPOCUSTO1, C.DTCARDAPIO, SYSDATE))) AS CUSTOREAL1,
                           DECODE(V_NRTIPOCUSTO1, NULL, 0, (C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, V_NRTIPOCUSTO2, C.DTCARDAPIO, SYSDATE))) AS CUSTOREAL2,
                           DECODE(V_NRTIPOCUSTO1, NULL, 0, (C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, V_NRTIPOCUSTO3, C.DTCARDAPIO, SYSDATE))) AS CUSTOREAL3,
                           C.CDPRODUTO
                        FROM
                           (SELECT
                           CA.CDFILIAL,
                           CA.CDSERVFILI,
                           CA.DTCARDAPIO,
                           SUM(IR.QTRETIRADA * PRA.VRFATOCONV) AS QTRETIRADA,
                           PRB.CDPRODUTO
                         FROM
                           CARDADIA CA,
                           PRODUTO  PRA,
                           PRODUTO  PRB,
                           ITEMRETI IR,
                           ITLANCTOEST IL
                         WHERE
                           (IL.CDFILIAL    = IR.CDFIITLANEST)                  AND
                           (IL.NRLANCESTQ  = IR.NRLANCESTQ)                    AND
                           (IL.NRSEQUITEM  = IR.NRSEQUITEM)                    AND
                           (IL.IDEVOLESTOQ = 'S')                              AND
                           (PRB.CDPRODUTO = PRA.CDPRODESTO)                  AND
                           (NOT IR.QTRETIRADA IS NULL AND IR.QTRETIRADA <> 0)  AND
                           (PRA.CDPRODUTO = IR.CDPRODUTO)                    AND
                           (CA.CDFILIAL   = IR.CDFILIAL)                     AND
                           (CA.CDFILIAL   = C_ETL.CDFILIAL )                 AND
                           (CA.DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)   AND
                           (CA.CDSERVFILI = C_SERVICO.CDSERVFILI)            AND
                           (CA.NRCARDAPIO = IR.NRCARDAPIO)
                         GROUP BY
                           CA.CDFILIAL,
                           CA.CDSERVFILI,
                           CA.DTCARDAPIO,
                           PRB.CDPRODUTO
                           ) C
                        UNION ALL
                        SELECT
                           RNE.CDFILIAL,
                           RNE.CDSERVFILI,
                           DT.DTDATA AS DTCARDAPIO,
                           0 AS CUSTOPLAN1,
                           0 AS CUSTOPLAN2,
                           0 AS CUSTOPLAN3,
                           DECODE(V_NRTIPOCUSTO1, NULL, 0, ROUND(CNE.VRREALNESTO * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL1,
                           DECODE(V_NRTIPOCUSTO2, NULL, 0, ROUND(CNE.VRREALNESTO * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL2,
                           DECODE(V_NRTIPOCUSTO3, NULL, 0, ROUND(CNE.VRREALNESTO * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL3,
                           CNE.CDPRODUTO AS CDPRODUTO
                        FROM
                           (SELECT
                              DTMESCARD,
                              CDSERVFILI,
                              SUM(QTCOMEFETCAR) AS TOTCOMEN
                           FROM
                              CARDADIA
                           WHERE
                              (CDFILIAL = C_ETL.CDFILIAL) AND
                              (CDSERVFILI = C_SERVICO.CDSERVFILI) AND
                              (DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                           GROUP BY
                              DTMESCARD,
                              CDSERVFILI
                           ) TC,
                           CARDADIA  CA,
                           RATENESTO RNE,
                           CUSTNESTO CNE,
                           DATA DT
                        WHERE
                           (DT.DTDATA BETWEEN V_DTINI AND V_DATAFINAL)                       AND
                           (CNE.CDFILIAL    = C_ETL.CDFILIAL)                                 AND
                           (TO_CHAR(DT.DTDATA, 'MMYYYY') = TO_CHAR(CNE.DTCUSTNESTO, 'MMYYYY')) AND
                           (NOT CNE.VRREALNESTO IS NULL)                                       AND
                           (RNE.CDFILIAL    = C_ETL.CDFILIAL)                                 AND
                           (RNE.CDSERVFILI = C_SERVICO.CDSERVFILI)                            AND
                           (RNE.CDPRODUTO   = CNE.CDPRODUTO)                                   AND
                           (CA.CDFILIAL     = C_ETL.CDFILIAL)                                 AND
                           (CA.CDSERVFILI   = RNE.CDSERVFILI)                                  AND
                           (CA.DTCARDAPIO   = DT.DTDATA)                                       AND
                           (TC.DTMESCARD    = CA.DTMESCARD)                                    AND
                           (TC.CDSERVFILI   = CA.CDSERVFILI)                                   AND
                           (NOT TC.TOTCOMEN IS NULL AND TC.TOTCOMEN <> 0)
                        UNION ALL
                        SELECT
                           RNE.CDFILIAL,
                           RNE.CDSERVFILI,
                           DT.DTDATA       AS DTCARDAPIO,
                           0 AS CUSTOPLAN1,
                           0 AS CUSTOPLAN2,
                           0 AS CUSTOPLAN3,
                           DECODE(V_NRTIPOCUSTO1, NULL, 0, ROUND(TCN.VRTOTAITEM * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL1,
                           DECODE(V_NRTIPOCUSTO2, NULL, 0, ROUND(TCN.VRTOTAITEM * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL2,
                           DECODE(V_NRTIPOCUSTO3, NULL, 0, ROUND(TCN.VRTOTAITEM * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5)) AS CUSTOREAL3,
                           TCN.CDPRODUTO CDPRODUTO
                        FROM
                           DATA DT,
                           RATENESTO RNE,
                           CARDADIA CA,
                           (SELECT
                              DTMESCARD,
                              CDSERVFILI,
                              SUM(QTCOMEFETCAR) AS TOTCOMEN
                            FROM
                              CARDADIA
                            WHERE
                              (CDFILIAL = C_ETL.CDFILIAL) AND
                              (CDSERVFILI = C_SERVICO.CDSERVFILI) AND
                              (DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                            GROUP BY
                              DTMESCARD,
                              CDSERVFILI
                           ) TC,
                           (SELECT
                              TO_CHAR(NF.DTENTRSAID, 'MMYYYY') AS DTMESNF,
                              IP.CDPRODUTO,
                              SUM(IP.VRTOTAITEM) AS VRTOTAITEM
                           FROM
                              NOTAFISCAL  NF,
                              ITEMPRODUTO IP
                           WHERE
                              (IP.CDFILIAL     = NF.CDFILIAL)                       AND
                              (IP.NRLANCTONF   = NF.NRLANCTONF)                     AND
                              (NF.CDFILIRECE   = C_ETL.CDFILIAL)                   AND
                              (NF.IDENTRSAIDOP = 'E')                               AND
                              (NF.DTENTRSAID BETWEEN V_DTINI AND V_DATAFINAL)
                           GROUP BY
                              TO_CHAR(NF.DTENTRSAID, 'MMYYYY'),
                              IP.CDPRODUTO
                           ) TCN
                        WHERE
                           (DT.DTDATA BETWEEN V_DTINI AND V_DATAFINAL)    AND
                           (TO_CHAR(DT.DTDATA, 'MMYYYY') = TCN.DTMESNF)   AND
                           (RNE.CDFILIAL    = C_ETL.CDFILIAL)             AND
                           (RNE.CDSERVFILI = C_SERVICO.CDSERVFILI)        AND
                           (CA.CDFILIAL     = C_ETL.CDFILIAL)             AND
                           (CA.CDSERVFILI   = RNE.CDSERVFILI)             AND
                           (CA.DTCARDAPIO   = DT.DTDATA)                  AND
                           (CA.DTCARDAPIO   = DT.DTDATA)                  AND
                           (TCN.CDPRODUTO   = RNE.CDPRODUTO)              AND
                           (TC.DTMESCARD    = CA.DTMESCARD)               AND
                           (TC.CDSERVFILI   = CA.CDSERVFILI)              AND
                           (NOT TC.TOTCOMEN IS NULL AND TC.TOTCOMEN <> 0) AND
                           (NOT EXISTS (SELECT CDFILIAL
                                        FROM CUSTNESTO
                                        WHERE
                                          CDFILIAL    = C_ETL.CDFILIAL AND
                                          DTCUSTNESTO = DT.DTDATA AND
                                          CDPRODUTO   = RNE.CDPRODUTO AND
                                          NOT VRREALNESTO IS NULL))
                        )
                      GROUP BY
                        CDFILIAL,
                        CDSERVFILI,
                        DTCARDAPIO,
                        CDPRODUTO 
                        --jefferson
                        UNION ALL 
                        
                        SELECT CP.CDFILICPLA CDFILIAL, CP.CDSERVCPLA CDSERVFILI, CP.DTCONSPLAN DTCARDAPIO, 
                               SUM(DECODE(V_NRTIPOCUSTO1, NULL, 0, ((CP.QTCONSPLAN * PRA.VRFATOCONV) * 
                                 CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO1, CP.DTCONSPLAN, SYSDATE)))) AS CUSTOPLAN1, 
                               SUM(DECODE(V_NRTIPOCUSTO2, NULL, 0, ((CP.QTCONSPLAN * PRA.VRFATOCONV) *
                                 CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO2, CP.DTCONSPLAN, SYSDATE)))) AS CUSTOPLAN2, 
                               SUM(DECODE(V_NRTIPOCUSTO3, NULL, 0, ((CP.QTCONSPLAN * PRA.VRFATOCONV) *
                                 CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO3, CP.DTCONSPLAN, SYSDATE)))) AS CUSTOPLAN3,
                               0 CUSTOREAL1,
                               0 CUSTOREAL2,
                               0 CUSTOREAL3,
                               CP.CDPRODCPLA CDPRODUTO
                          FROM CONSPLAN CP INNER JOIN PRODUTO PRA ON CP.CDPRODCPLA = PRA.CDPRODUTO
                         WHERE CP.CDFILICPLA = C_ETL.CDFILIAL
                           AND CP.DTCONSPLAN BETWEEN V_DTINI AND V_DATAFINAL
                           AND CP.NRCONSPLAN = V_CDSEQUENCIAL
                           AND CP.IDCONSPLAN = 'F'
                           AND CP.CDSERVCPLA = C_SERVICO.CDSERVFILI
                         GROUP BY CP.CDFILICPLA, CP.CDSERVCPLA, CP.DTCONSPLAN, CP.CDPRODCPLA
                           --jefferson
                     ) PR
                  WHERE
                     FI.CDFILIAL   = PR.CDFILIAL   AND
                     SE.CDFILIAL   = PR.CDFILIAL   AND
                     SE.CDSERVFILI = PR.CDSERVFILI AND
                     SE.CDSERVFILI  = C_SERVICO.CDSERVFILI AND
                     CD.CDFILIAL(+)   = PR.CDFILIAL   AND
                     CD.CDSERVFILI(+) = PR.CDSERVFILI AND
                     CD.DTCARDAPIO(+) = PR.DTCARDAPIO AND
                     SE.CDGRUPSERV = GPS.CDGRUPSERV(+)
                  GROUP BY
                     PR.CDFILIAL,
                     PR.CDSERVFILI,
                     GPS.NMGRUPSERV,
                     GPS.CDGRUPSERV,
                     SE.IDSERVPRINC
                  )
               LOOP

                  BEGIN
                     IF C_SERVICO.QTCOMESTICAR > 0 THEN

                        V_VRPERCPLANTOTAL1 := (C_PRODUTO.CUSTOPLAN1 / C_SERVICO.QTCOMESTICAR);
                        V_VRPERCPLANTOTAL2 := (C_PRODUTO.CUSTOPLAN2 / C_SERVICO.QTCOMESTICAR);
                        V_VRPERCPLANTOTAL3 := (C_PRODUTO.CUSTOPLAN3 / C_SERVICO.QTCOMESTICAR);

                        V_VRCUSTPLANTOTAL1 := (C_PRODUTO.CUSTOPLAN1 * C_SERVICO.QTCOMESTICAR);
                        V_VRCUSTPLANTOTAL2 := (C_PRODUTO.CUSTOPLAN2 * C_SERVICO.QTCOMESTICAR);
                        V_VRCUSTPLANTOTAL3 := (C_PRODUTO.CUSTOPLAN3 * C_SERVICO.QTCOMESTICAR);
                        IF V_VRPRECVENDA > 0 THEN

                           V_VRTOTFCPLAN1 := (C_PRODUTO.CUSTOPLAN1 / (V_VRPRECVENDA * C_SERVICO.QTCOMESTICAR));
                           V_VRTOTFCPLAN2 := (C_PRODUTO.CUSTOPLAN2 / (V_VRPRECVENDA * C_SERVICO.QTCOMESTICAR));
                           V_VRTOTFCPLAN3 := (C_PRODUTO.CUSTOPLAN3 / (V_VRPRECVENDA * C_SERVICO.QTCOMESTICAR));
                        ELSE
                           V_VRTOTFCPLAN1  := V_ZERO;
                           V_VRTOTFCPLAN2  := V_ZERO;
                           V_VRTOTFCPLAN3  := V_ZERO;
                        END IF;
                     ELSE
                        V_VRPERCPLANTOTAL1 := V_ZERO;
                        V_VRPERCPLANTOTAL2 := V_ZERO;
                        V_VRPERCPLANTOTAL3 := V_ZERO;
                        V_VRCUSTPLANTOTAL1 := V_ZERO;
                        V_VRCUSTPLANTOTAL2 := V_ZERO;
                        V_VRCUSTPLANTOTAL3 := V_ZERO;
                        V_VRTOTFCPLAN1     := V_ZERO;
                        V_VRTOTFCPLAN2     := V_ZERO;
                        V_VRTOTFCPLAN3     := V_ZERO;
                     END IF;

                     IF (C_SERVICO.QTCOMEFETCAR > 0)  THEN

                        V_VRPERCREALTOTAL1 := (C_PRODUTO.CUSTOREAL1 / C_SERVICO.QTCOMEFETCAR);
                        V_VRPERCREALTOTAL2 := (C_PRODUTO.CUSTOREAL2 / C_SERVICO.QTCOMEFETCAR);
                        V_VRPERCREALTOTAL3 := (C_PRODUTO.CUSTOREAL3 / C_SERVICO.QTCOMEFETCAR);

                        V_VRCUSTREALTOTAL1 := (C_PRODUTO.CUSTOREAL1 * C_SERVICO.QTCOMEFETCAR);
                        V_VRCUSTREALTOTAL2 := (C_PRODUTO.CUSTOREAL2 * C_SERVICO.QTCOMEFETCAR);
                        V_VRCUSTREALTOTAL3 := (C_PRODUTO.CUSTOREAL3 * C_SERVICO.QTCOMEFETCAR);
                        IF V_VRPRECVENDA > 0 THEN

                           V_VRTOTALCUSTOSTD := ((V_VRCUSTPADR * C_SERVICO.QTCOMEFETCAR) / (V_VRPRECVENDA * C_SERVICO.QTCOMEFETCAR ));

                           V_VRTOTFCREAL1 := (C_PRODUTO.CUSTOREAL1 / (V_VRPRECVENDA * C_SERVICO.QTCOMEFETCAR));
                           V_VRTOTFCREAL2 := (C_PRODUTO.CUSTOREAL2 / (V_VRPRECVENDA * C_SERVICO.QTCOMEFETCAR));
                           V_VRTOTFCREAL3 := (C_PRODUTO.CUSTOREAL3 / (V_VRPRECVENDA * C_SERVICO.QTCOMEFETCAR));
                        ELSE
                           V_VRTOTALCUSTOSTD := V_ZERO;
                           V_VRTOTFCREAL1    := V_ZERO;
                           V_VRTOTFCREAL2    := V_ZERO;
                           V_VRTOTFCREAL3    := V_ZERO;
                        END IF;
                     ELSE
                        V_VRPERCREALTOTAL1 := V_ZERO;
                        V_VRPERCREALTOTAL2 := V_ZERO;
                        V_VRPERCREALTOTAL3 := V_ZERO;
                        V_VRCUSTREALTOTAL1 := V_ZERO;
                        V_VRCUSTREALTOTAL2 := V_ZERO;
                        V_VRCUSTREALTOTAL3 := V_ZERO;
                        V_VRTOTALCUSTOSTD  := V_ZERO;
                        V_VRTOTFCREAL1     := V_ZERO;
                        V_VRTOTFCREAL2     := V_ZERO;
                        V_VRTOTFCREAL3     := V_ZERO;
                     END IF;

                     V_VRTOTVENDSERV := (V_VRPRECVENDA * C_SERVICO.QTCOMEFETCAR);

                     V_VRTOTCUSTPADREST := V_VRCUSTPADR * C_SERVICO.QTCOMESTICAR;

                     V_VRTOTCUSTPADREFE := V_VRCUSTPADR * C_SERVICO.QTCOMEFETCAR;

                  END;

                  INSERT INTO DM_RESUFOOD_PASSFUTU
                        ( CDFILCENTPME,
                          NMFILCENTPME,
                          CDFILIAL,
                          NMFILIAL,
                          CDAREAGERE,
                          NMAREAGERE,
                          CDSUPERVISOR,
                          NMSUPERVISOR,
                          CDGOPERAC,
                          NMGOPERAC,
                          CDEMPRESA,
                          NMEMPRESA,
                          CDSEGMER,
                          NMSEGMERCADO,
                          CDPLANEJADORA,
                          NMPLANEJADORA,
                          CDGUNIDADE,
                          NMGUNIDADE,
                          CDGRUPSERV,
                          NMGRUPSERV,
                          CDFILISERVFILI,
                          NMSERVICO,
                          CDTIPOCARD,
                          NMTIPOCARD,
                          CDANOBI,
                          CDMESBI,
                          QTCOMESTSERV,
                          QTCOMEFESERV,
                          VRCUSPADSERV,
                          DSDTPROCFILI,
                          DSTPSERV,
                          VRCUSVNDSERV,
                          VRPERTOTCUSTSTD,
                          VRTOTAVENDASERV,
                          VRCUSPADEST,
                          VRCUSPADEFE,
                          VRCUSPLASERV1,
                          VRCUSPLASERV2,
                          VRCUSPLASERV3,
                          VRCUSREASERV1,
                          VRCUSREASERV2,
                          VRCUSREASERV3,
                          VRPERCPLATOTSER1,
                          VRPERCPLATOTSER2,
                          VRPERCPLATOTSER3,
                          VRPERCREATOTSER1,
                          VRPERCREATOTSER2,
                          VRPERCREATOTSER3,
                          VRCUSPLATOTSER1,
                          VRCUSPLATOTSER2,
                          VRCUSPLATOTSER3,
                          VRCUSREATOTSER1,
                          VRCUSREATOTSER2,
                          VRCUSREATOTSER3,
                          VRPERFCPLANS1,
                          VRPERFCPLANS2,
                          VRPERFCPLANS3,
                          VRPERFCPREALS1,
                          VRPERFCPREALS2,
                          VRPERFCPREALS3,
                          DSDTULTCOMEFE,
                          DTULTAPURAC,
                          CDMESBI_MMYYYY,
                          DSMESBI_MMYYYY,
                          DSBIMESTRE,
                          DSTRIMESTRE,
                          DSQUADIMESTRE,
                          DSSEMESTRE
                        )
                  VALUES
                        ( NULL,
                          'N/A',
                          C_ETL.CDFILIAL,
                          C_ETL.NMFILIAL,
                          C_ETL.CDAREAGERE,
                          C_ETL.NMAREAGERE,
                          C_ETL.CDSUPERVISOR,
                          C_ETL.NMSUPERVISOR,
                          C_ETL.CDGOPERAC,
                          C_ETL.NMGERENTE_OPERACIONAL,
                          C_ETL.CDEMPRESA,
                          C_ETL.NMEMPRESA,
                          C_ETL.CDSEGMER,
                          C_ETL.NMSEGMERCADO,
                          C_ETL.CDPLANEJADORA,
                          C_ETL.NMPLANEJADORA,
                          C_ETL.CDGUNIDADE,
                          C_ETL.NMGUNIDADE,
                          C_SERVICO.CDGRUPSERV,
                          C_PRODUTO.NMGRUPSERV,
                          C_ETL.CDFILIAL||C_SERVICO.CDSERVFILI,
                          C_SERVICO.NMSERVICO,
                          NULL,
                          'N/A',
                          V_ANO,
                          V_MES,
                          NVL(C_SERVICO.QTCOMESTICAR, 0),
                          NVL(C_SERVICO.QTCOMEFETCAR,0),
                          NVL(V_VRCUSTPADR, 0),
                          NVL(TO_CHAR(C_ETL.DTPROCESSA, 'DD/MM/YYYY'), 0),
                          NVL(DECODE(C_SERVICO.IDSERVPRINC,'S', 'Servico Principal','Outros Servicos'),0),
                          NVL(V_VRPRECVENDA, 0),
                          NVL(V_VRTOTALCUSTOSTD, 0),
                          NVL(V_VRTOTVENDSERV,0),
                          NVL(V_VRTOTCUSTPADREST, 0),
                          NVL(V_VRTOTCUSTPADREFE, 0),
                          NVL(C_PRODUTO.CUSTOPLAN1,0),
                          NVL(C_PRODUTO.CUSTOPLAN2, 0),
                          NVL(C_PRODUTO.CUSTOPLAN3, 0),
                          NVL(C_PRODUTO.CUSTOREAL1,0),
                          NVL(C_PRODUTO.CUSTOREAL2, 0),
                          NVL(C_PRODUTO.CUSTOREAL3, 0),
                          NVL(V_VRPERCPLANTOTAL1,0),
                          NVL(V_VRPERCPLANTOTAL2, 0),
                          NVL(V_VRPERCPLANTOTAL3, 0),
                          NVL(V_VRPERCREALTOTAL1,0),
                          NVL(V_VRPERCREALTOTAL2, 0),
                          NVL(V_VRPERCREALTOTAL3, 0),
                          NVL(V_VRCUSTPLANTOTAL1,0),
                          NVL(V_VRCUSTPLANTOTAL2, 0),
                          NVL(V_VRCUSTPLANTOTAL3, 0),
                          NVL(V_VRCUSTREALTOTAL1,0),
                          NVL(V_VRCUSTREALTOTAL2, 0),
                          NVL(V_VRCUSTREALTOTAL3, 0),
                          NVL(V_VRTOTFCPLAN1,0),
                          NVL(V_VRTOTFCPLAN2, 0),
                          NVL(V_VRTOTFCPLAN3, 0),
                          NVL(V_VRTOTFCREAL1,0),
                          NVL(V_VRTOTFCREAL2,0),
                          NVL(V_VRTOTFCREAL3, 0),
                          TO_CHAR(V_DTULTCOMEFE, 'DD/MM/YYYY'),
                          V_DATAFINAL,
                          TO_CHAR(TO_DATE(V_DATAFINAL),'MM/YYYY'),--CDMESBI_MMYYYY
                          TO_CHAR(TO_DATE(V_DATAFINAL),'MON/YYYY'),--DSMESBI_MMYYYY
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 2) + 1)||' Bim',--DSBIMESTRE
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 3) + 1)||' Tri',--DSTRIMESTRE
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 4) + 1)||' Qua',--DSQUADIMESTRE
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 6) + 1)||' Sem'--DSSEMESTRE
                        );
                        COMMIT;
               END LOOP;--PRODUTO
            END LOOP;--SERVICO

            DELETE FROM CONSPLAN WHERE NRCONSPLAN = V_CDSEQUENCIAL;

         END IF;--DTINICIALBI
         COMMIT;
      END LOOP;--C_ETL
      COMMIT;

      
      FOR C_CENTRAL_MERENDA IN(SELECT CENTRAL.CDCENTRAL, CENTRAL.NMCENTRAL, P.IDCOMCUSPERC, FL.CDFILIAL CDMERENDA, P.DTPROCESSA
                                 FROM FILIAL FL
                                INNER JOIN (SELECT CDFILIAL CDCENTRAL, NMFILIAL NMCENTRAL FROM FILIAL WHERE IDFILCENTPME = 'S' AND IDTPFILIALCENT = 'M') CENTRAL
                                   ON FL.CDFILCENTPME = CENTRAL.CDCENTRAL
                                INNER JOIN PARAMFILIAL P
                                   ON P.CDFILIAL = CENTRAL.CDCENTRAL
                                INNER JOIN EMPRESA EMP
                                   ON EMP.CDEMPRESA = FL.CDEMPRESA)LOOP-- WHERE FL.CDFILIAL IN ('2040','2011','2357')

         NOVO_CODIGO('CONSPLAN'||C_CENTRAL_MERENDA.CDMERENDA, V_CDSEQUENCIAL,1);
         V_CDSEQUENCIAL := SUBSTR(V_CDSEQUENCIAL,11,10);

         --CONSUMO_PLANEJADO_PME(CENTRAL DE MERENDA, TIPO DE CARDAPIO, FILIAL MERENDA, SERVICO, PERIODO INICIAL, PERIODO FINAL, ?, CALCULO(C-CARDAPIO/F-CONSUMO FIXO/P-CONSUMO PERCAPITA), ?, ACRESCIMO, ARREDONDAMENTO, SEQUENCIAL, PRODUTO INICIAL, PRODUTO FINAL, OPERADOR)

         CONSUMO_PLANEJADO_PME( C_CENTRAL_MERENDA.CDCENTRAL, NULL, C_CENTRAL_MERENDA.CDMERENDA, NULL, V_DTINI, V_DATAFINAL, 'T', 'CFP', 'N', 0, 'N', V_CDSEQUENCIAL, '0', 'zzzzzzzzzz', '');

         FOR C_TIPOCARD IN (SELECT TC.CDFILCENTPME, TC.CDTIPOCARD, TC.NMTIPOCARD, TC.IDTPCARDATIV, TC.DTULTATUTCAR, TC.DSPASTATCARD, TC.NRPASTATCARD
                              FROM TIPOCARD TC, FILITIPOCARD FTC
                             WHERE TC.CDTIPOCARD = FTC.CDTIPOCARD
                               AND TC.CDFILCENTPME = FTC.CDFILCENTPME
                               AND FTC.CDFILIAL = C_CENTRAL_MERENDA.CDMERENDA
                               AND TC.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL)LOOP

               FOR C_FILISERV IN (SELECT FILITC.CDFILIAL CDFILIAL_MERENDA, FILITC.IDTCARATFILI, FILITC.DTULTATUFTCA, FILITC.IDFATUPROD, SERVTC.CDSERVFILI, SERVTC.DTULTATUSVTC, SERVTC.CDPRODUTO,
                                         PLAN_CARD.NMSERVICO, PLAN_CARD.CDGRUPSERV, PLAN_CARD.NMGRUPSERV, PLAN_CARD.IDSERVPRINC, PLAN_CARD.IDINDICADOR, PLAN_CARD.QTCOMESTICAR, PLAN_CARD.QTCOMEFETCAR, CUSTPADR.VRCUSPADTCAR,
                                         FL.CDFILCENTPME CDFILI_CENTRAL, FL.CDFILIAL CDFILI_MERENDA, FL.NMFILIAL, FL.CDAREAGERE, NVL(AREAGERE.NMAREAGERE,'SEM CADASTRO') NMAREAGERE, FL.DTINICIALBI,
                                         FL.CDSUPERVISOR, NVL(SUPERVISOR.NMOPERADOR,'SEM CADASTRO') NMSUPERVISOR, FL.CDGOPERAC, NVL(GERENTE_OPERACIONAL.NMOPERADOR,'SEM CADASTRO') NMGERENTE_OPERACIONAL, FL.CDEMPRESA, EM.NMEMPRESA,
                                         FL.CDSEGMER, NVL(SEGMERCADO.DSSEGMER,'SEM CADASTRO') NMSEGMERCADO, P.IDCOMCUSPERC, CDPLANEJADORA, NVL(PLANEJADORA.NMOPERADOR,'SEM CADASTRO') NMPLANEJADORA, CDGUNIDADE, NVL(GERENTE_UNIDADE.NMOPERADOR,'SEM CADASTRO') NMGUNIDADE
                                    FROM FILITIPOCARD FILITC
                                   INNER JOIN SERVTIPOCARD SERVTC
                                      ON FILITC.CDFILCENTPME = SERVTC.CDFILCENTPME
                                     AND FILITC.CDTIPOCARD   = SERVTC.CDTIPOCARD
                                   INNER JOIN (SELECT CDFILCENTPME, CDFILI_MERENDA, CDSERVFILI, NMSERVICO, CDGRUPSERV, NMGRUPSERV, IDSERVPRINC, IDINDICADOR,
                                                      SUM(QTCOMESTICAR) QTCOMESTICAR, SUM(QTCOMEFETCAR) QTCOMEFETCAR
                                                 FROM (SELECT CA.CDFILCENTPME, CA.CDFILIAL CDFILI_MERENDA, SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV, GRSRV.NMGRUPSERV, SE.IDSERVPRINC, SE.IDINDICADOR,
                                                              SUM(DECODE(C_CENTRAL_MERENDA.IDCOMCUSPERC, 'C', CA.QTCLIESTCPME, CA.QTCOMESTCPME) ) AS QTCOMESTICAR,
                                                              SUM(DECODE(C_CENTRAL_MERENDA.IDCOMCUSPERC, 'C', CA.QTCLIEFECPME, CA.QTCOMEFECPME) ) AS QTCOMEFETCAR
                                                         FROM CARDIAPME CA, SERVICO SE, GRUPSERV GRSRV
                                                        WHERE CA.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                                                          AND CA.CDFILIAL     = SE.CDFILIAL
                                                          AND CA.CDSERVFILI   = SE.CDSERVFILI
                                                          AND GRSRV.CDGRUPSERV(+) = SE.CDGRUPSERV
                                                          AND CA.DTCARDIAPME BETWEEN V_DTINI AND V_DATAFINAL
                                                        GROUP BY CA.CDFILCENTPME, CA.CDFILIAL, SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV, GRSRV.NMGRUPSERV, SE.IDSERVPRINC, SE.IDINDICADOR
                              
                                                        UNION ALL --jefferson

                                                       SELECT CP.CDFILCENTPME CDFILCENTPME, CP.CDFILICPLA CDFILI_MERENDA, SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV, GRSRV.NMGRUPSERV, SE.IDSERVPRINC, SE.IDINDICADOR,
                                                              0 AS QTCOMESTICAR, 
                                                              0 AS QTCOMEFETCAR
                                                         FROM CONSPLAN CP, SERVICO SE, GRUPSERV GRSRV
                                                        WHERE CP.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                                                          AND CP.CDFILICPLA = SE.CDFILIAL
                                                          AND CP.CDSERVCPLA = SE.CDSERVFILI
                                                          AND CP.DTCONSPLAN BETWEEN V_DTINI AND V_DATAFINAL
                                                          AND CP.NRCONSPLAN = V_CDSEQUENCIAL
                                                          AND CP.IDCONSPLAN = 'F'
                                                          AND CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                                                          AND CP.CDTPCARDPME  = C_TIPOCARD.CDTIPOCARD
                                                          AND GRSRV.CDGRUPSERV(+) = SE.CDGRUPSERV
                                                        GROUP BY CP.CDFILCENTPME, CP.CDFILICPLA, SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV, GRSRV.NMGRUPSERV, SE.IDSERVPRINC, SE.IDINDICADOR) X
                                                GROUP BY CDFILCENTPME, CDFILI_MERENDA, CDSERVFILI, NMSERVICO, CDGRUPSERV, NMGRUPSERV, IDSERVPRINC, IDINDICADOR) PLAN_CARD
                                      ON PLAN_CARD.CDFILCENTPME   = FILITC.CDFILCENTPME
                                     AND PLAN_CARD.CDFILI_MERENDA = FILITC.CDFILIAL
                                     AND PLAN_CARD.CDSERVFILI     = SERVTC.CDSERVFILI
                                    LEFT JOIN (SELECT NVL(VRCUSPADTCAR,V_ZERO) VRCUSPADTCAR, CDFILCENTPME, CDTIPOCARD, CDSERVFILI
                                                 FROM CUSTPADTCARD CUST_CARD
                                                WHERE CUST_CARD.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                                                  AND CUST_CARD.CDTIPOCARD   = C_TIPOCARD.CDTIPOCARD
                                                  AND CUST_CARD.DTREFCUSTCAR = (SELECT MAX(DTREFCUSTCAR)
                                                                             FROM CUSTPADTCARD
                                                                            WHERE CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                                                                              AND CDSERVFILI= CUST_CARD.CDSERVFILI
                                                                              AND CDTIPOCARD   = C_TIPOCARD.CDTIPOCARD)) CUSTPADR
                                      ON CUSTPADR.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                                     AND CUSTPADR.CDTIPOCARD   = C_TIPOCARD.CDTIPOCARD
                                     AND CUSTPADR.CDSERVFILI   = SERVTC.CDSERVFILI
                                   INNER JOIN FILIAL FL
                                      ON FL.CDFILIAL = FILITC.CDFILIAL
                                   INNER JOIN PARAMFILIAL P
                                      ON FL.CDFILIAL = P.CDFILIAL
                                   INNER JOIN EMPRESA EM
                                      ON FL.CDEMPRESA = EM.CDEMPRESA
                                   LEFT JOIN OPERADOR SUPERVISOR
                                      ON FL.CDSUPERVISOR = SUPERVISOR.CDOPERADOR
                                   LEFT JOIN SEGMERCADO
                                      ON FL.CDSEGMER = SEGMERCADO.CDSEGMER
                                   LEFT JOIN OPERADOR GERENTE_OPERACIONAL
                                      ON FL.CDGOPERAC = GERENTE_OPERACIONAL.CDOPERADOR
                                   LEFT JOIN AREAGERE
                                      ON FL.CDAREAGERE = AREAGERE.CDAREAGERE
                                   LEFT JOIN OPERADOR PLANEJADORA
                                      ON FL.CDPLANEJADORA = PLANEJADORA.CDOPERADOR
                                   LEFT JOIN OPERADOR GERENTE_UNIDADE
                                      ON FL.CDGUNIDADE = GERENTE_UNIDADE.CDOPERADOR
                                   WHERE SERVTC.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                                     AND SERVTC.CDTIPOCARD   = C_TIPOCARD.CDTIPOCARD
                                     AND FILITC.CDFILIAL = C_CENTRAL_MERENDA.CDMERENDA) LOOP
                  BEGIN
                     SELECT MAX(DTCARDIAPME) DTULTCOMEFE
                       INTO V_DTULTCOMEFE
                       FROM CARDIAPME CA
                      WHERE DECODE(C_FILISERV.IDCOMCUSPERC, 'C', CA.QTCLIEFECPME, CA.QTCOMEFECPME) > 0
                        AND CA.DTCARDIAPME BETWEEN V_DTINI AND V_DATAFINAL
                        AND CA.CDSERVFILI   = C_FILISERV.CDSERVFILI
                        AND CA.CDFILIAL     = C_FILISERV.CDFILIAL_MERENDA
                        AND CA.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL
                      GROUP BY CA.CDSERVFILI;
                  EXCEPTION
                     WHEN OTHERS THEN
                        V_DTULTCOMEFE := '';
                  END;

                  BEGIN
                     SELECT CF.CDCLIENTE
                     INTO   V_CDCLIENTE
                     FROM   CLIENFILIAL CF, CLIENTE B
                     WHERE  CF.CDFILIAL = C_FILISERV.CDFILIAL_MERENDA
                       AND CF.CDCLIENTE = B.CDCLIENTE
                       AND CF.IDCLIENPRIN = 'S';
                  EXCEPTION
                     WHEN OTHERS THEN
                        V_CDCLIENTE := NULL;
                  END;

                  V_VRPRECVENDA := V_ZERO;

                  IF (V_CDCLIENTE IS NOT NULL) THEN

                     BEGIN
                        SELECT MAX(VRSERVFATHIS) VRPRECOVEND
                          INTO V_VRPRECVENDA
                          FROM HISTSERVFATU
                         WHERE CDFILIAL     = C_FILISERV.CDFILIAL_MERENDA
                           AND CDSERVFILI   = C_FILISERV.CDSERVFILI
                           AND CDCLIENTE    = V_CDCLIENTE
                           AND DTSRVFATUHIS = (SELECT MAX(DTSRVFATUHIS)
                                                 FROM HISTSERVFATU
                                                WHERE CDFILIAL     = C_FILISERV.CDFILIAL_MERENDA
                                                  AND CDCLIENTE    = V_CDCLIENTE
                                                  AND CDSERVFILI   = C_FILISERV.CDSERVFILI
                                                  AND TRUNC(DTSRVFATUHIS) <= V_DATAFINAL)
                           AND DTALTEFATHIS = (SELECT MAX(DTALTEFATHIS)
                                                 FROM HISTSERVFATU H, (SELECT MAX(DTSRVFATUHIS) AS DTSRVFATUHIS
                                                                         FROM HISTSERVFATU
                                                                        WHERE CDFILIAL = C_FILISERV.CDFILIAL_MERENDA
                                                                          AND CDCLIENTE = V_CDCLIENTE
                                                                          AND CDSERVFILI = C_FILISERV.CDSERVFILI
                                                                          AND DTSRVFATUHIS <= V_DATAFINAL) X
                                                WHERE H.CDFILIAL     = C_FILISERV.CDFILIAL_MERENDA
                                                  AND H.CDCLIENTE    = V_CDCLIENTE
                                                  AND H.CDSERVFILI   = C_FILISERV.CDSERVFILI
                                                  AND H.DTSRVFATUHIS = X.DTSRVFATUHIS);
                     EXCEPTION
                        WHEN OTHERS THEN
                           V_VRPRECVENDA := V_ZERO;
                     END;
                  END IF;

                  FOR C_PRODUTO IN(
                        SELECT CDFILIAL, NMFILIAL, CDSERVFILI, NMSERVICO, SUM(CUSTOPLAN1)  AS CUSTOPLAN1, SUM(CUSTOPLAN2)  AS CUSTOPLAN2, SUM(CUSTOPLAN3)  AS CUSTOPLAN3, SUM(QTCONSPLAN) AS QTCONSPLAN, SUM(QTCOMESTICAR) AS QTCOMESTICAR, SUM(QTCLIESTICAR) AS QTCLIESTICAR,
                              SUM(CUSTOREAL1)  AS CUSTOREAL1, SUM(CUSTOREAL2)  AS CUSTOREAL2, SUM(CUSTOREAL3)  AS CUSTOREAL3, SUM(QTRETIRADA) AS QTRETIRADA, SUM(QTCOMEFETCAR) AS QTCOMEFETCAR, SUM(QTCLIEFETCAR) AS QTCLIEFETCAR
                         FROM(SELECT FI.CDFILIAL AS CDFILIAL,
                                     FI.NMFILIAL     AS NMFILIAL,
                                     SE.CDSERVFILI   AS CDSERVFILI,
                                     SE.NMSERVICO    AS NMSERVICO,
                                     CD.DTCARDAPIO   AS DTCARDAPIO,
                                     SUM(PR.CUSTOPLAN1)   AS CUSTOPLAN1,--alteracao TIAGO
                                     SUM(PR.CUSTOPLAN2)   AS CUSTOPLAN2,--alteracao TIAGO
                                     SUM(PR.CUSTOPLAN3)   AS CUSTOPLAN3,--alteracao TIAGO
                                     SUM(PR.CUSTOREAL1)   AS CUSTOREAL1,--alteracao TIAGO
                                     SUM(PR.CUSTOREAL2)   AS CUSTOREAL2,--alteracao TIAGO
                                     SUM(PR.CUSTOREAL3)   AS CUSTOREAL3,--alteracao TIAGO
                                     SUM(PR.QTCONSPLAN)   AS QTCONSPLAN,--alteracao TIAGO
                                     SUM(PR.QTRETIRADA)   AS QTRETIRADA,--alteracao TIAGO
                                     CD.QTCOMESTICAR AS QTCOMESTICAR,
                                     CD.QTCLIESTICAR AS QTCLIESTICAR,
                                     CD.QTCOMEFETCAR AS QTCOMEFETCAR,
                                     CD.QTCLIEFETCAR AS QTCLIEFETCAR
                                FROM FILIAL FI, SERVICO SE, (SELECT CD.CDFILCENTPME, CD.CDFILIAL, CD.CDSERVFILI, CD.DTCARDIAPME DTCARDAPIO, SUM(CD.QTCOMESTCPME) QTCOMESTICAR, SUM(CD.QTCLIESTCPME) QTCLIESTICAR, SUM(CD.QTCOMEFECPME) QTCOMEFETCAR, SUM(CD.QTCLIEFECPME) QTCLIEFETCAR
                                                               FROM CARDAPIOPME CP, CARDIAPME CD
                                                              WHERE (CP.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL)
                                                                AND (CP.CDTIPOCARD  = C_TIPOCARD.CDTIPOCARD)
                                                                AND (CP.CDFILCENTPME = CD.CDFILCENTPME)
                                                                AND (CP.NRCARDPME    = CD.NRCARDPME)
                                                                AND (CD.CDFILIAL    = C_FILISERV.CDFILIAL_MERENDA)
                                                                AND (CD.CDSERVFILI   = C_FILISERV.CDSERVFILI)
                                                                AND (CD.DTCARDIAPME BETWEEN V_DTINI AND V_DATAFINAL)
                                                              GROUP BY CD.CDFILCENTPME, CD.CDFILIAL, CD.CDSERVFILI, CD.DTCARDIAPME) CD
                                LEFT JOIN(SELECT CDFILIAL, CDSERVFILI, DTCARDAPIO, SUM(CUSTOPLAN1) CUSTOPLAN1, SUM(CUSTOPLAN2) CUSTOPLAN2, SUM(CUSTOPLAN3) CUSTOPLAN3,
                                                 SUM(CUSTOREAL1) CUSTOREAL1, SUM(CUSTOREAL2) CUSTOREAL2, SUM(CUSTOREAL3) CUSTOREAL3, CDPRODUTO, SUM(QTCONSPLAN) QTCONSPLAN, SUM(QTRETIRADA) QTRETIRADA, CDARVPROD, NMPRODUTO, SGUNIDADE
                                            FROM(SELECT CP.CDFILICPLA CDFILIAL, CP.CDSERVCPLA CDSERVFILI, CP.DTCONSPLAN DTCARDAPIO,
                                                        (CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO1, CP.DTCONSPLAN, SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN1,
                                                        (CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO2, CP.DTCONSPLAN, SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN2,
                                                        (CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO3, CP.DTCONSPLAN, SYSDATE) * CP.QTCONSPLAN) AS CUSTOPLAN3,
                                                        0 AS CUSTOREAL1, 0 AS CUSTOREAL2, 0 AS CUSTOREAL3, CP.CDPRODCPLA CDPRODUTO, CP.QTCONSPLAN, 0 AS QTRETIRADA, PD.CDARVPROD, PD.NMPRODUTO, PD.SGUNIDADE
                                                   FROM CONSPLAN CP, PRODUTO  PD
                                                  WHERE (CP.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL)
                                                    AND (CP.CDFILICPLA = C_FILISERV.CDFILIAL_MERENDA)
                                                    AND (CP.NRCONSPLAN = V_CDSEQUENCIAL)
                                                    AND (PD.CDPRODUTO  = CP.CDPRODCPLA)
                                                    AND (CP.CDTPCARDPME = C_TIPOCARD.CDTIPOCARD)
                                                  UNION ALL
                                                 SELECT CD.CDFILIAL, CD.CDSERVFILI, CD.DTCARDIAPME AS DTCARDAPIO,
                                                        ROUND(CNE.VRPLANNESTO * (RNE.PERATENESTO/100) * (CD.QTCOMESTCPME/H.TOTCOMEN), 5) AS CUSTOPLAN1, 0 AS CUSTOPLAN2, 0 AS CUSTOPLAN3,
                                                        0 AS CUSTOREAL1, 0 AS CUSTOREAL2, 0 AS CUSTOREAL3,
                                                        CNE.CDPRODUTO AS CDPRODUTO, 0 AS QTCONSPLAN, 0 AS QTRETIRADA, PDA.CDARVPROD, PDA.NMPRODUTO, PDA.SGUNIDADE
                                                   FROM (SELECT CD.DTCARDIAPME - TO_CHAR(CD.DTCARDIAPME, 'DD') + 1 DTMESCARD, CD.CDSERVFILI, SUM(CD.QTCOMESTCPME) AS TOTCOMEN
                                                           FROM CARDAPIOPME CP, CARDIAPME CD
                                                          WHERE (CP.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL)
                                                            AND (CP.CDTIPOCARD   = C_TIPOCARD.CDTIPOCARD)
                                                            AND (CP.CDFILCENTPME = CD.CDFILCENTPME)
                                                            AND (CP.NRCARDPME    = CD.NRCARDPME)
                                                            AND (CD.CDFILIAL     = C_FILISERV.CDFILIAL_MERENDA)
                                                            AND (CD.CDSERVFILI   = C_FILISERV.CDSERVFILI)
                                                            AND (CD.DTCARDIAPME BETWEEN V_DTINI AND V_DATAFINAL)
                                                          GROUP BY CD.DTCARDIAPME, CD.CDSERVFILI) H, CARDAPIOPME CP, CARDIAPME  CD, RATENESTO RNE, CUSTNESTO CNE, DATA DT, PRODUTO PDA
                                                  WHERE (DT.DTDATA BETWEEN V_DTINI AND V_DATAFINAL)
                                                    AND (TO_CHAR(DT.DTDATA, 'MMYYYY') = TO_CHAR(CNE.DTCUSTNESTO, 'MMYYYY'))
                                                    AND (CNE.CDFILIAL = C_FILISERV.CDFILIAL_MERENDA)
                                                    AND (CP.CDFILCENTPME = C_CENTRAL_MERENDA.CDCENTRAL)
                                                    AND (CP.CDTIPOCARD = C_TIPOCARD.CDTIPOCARD)
                                                    AND (RNE.CDSERVFILI = C_FILISERV.CDSERVFILI)
                                                    AND (CP.CDFILCENTPME = CD.CDFILCENTPME)
                                                    AND (CP.NRCARDPME    = CD.NRCARDPME)
                                                    AND (CNE.CDPRODUTO    = RNE.CDPRODUTO)
                                                    AND (CD.CDFILIAL      = RNE.CDFILIAL)
                                                    AND (CD.CDSERVFILI    = RNE.CDSERVFILI)
                                                    AND (CD.DTCARDIAPME   = DT.DTDATA)
                                                    AND (H.DTMESCARD      = CD.DTCARDIAPME - TO_CHAR(CD.DTCARDIAPME, 'DD') + 1)
                                                    AND (H.CDSERVFILI     = RNE.CDSERVFILI)
                                                    AND (NOT H.TOTCOMEN IS NULL AND H.TOTCOMEN <> 0)
                                                  UNION ALL
                                                  --CUSTO REAL
                                                 SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, 0 CUSTOPLAN1, 0 AS CUSTOPLAN2, 0 AS CUSTOPLAN3,
                                                       (C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, V_NRTIPOCUSTO1, C.DTCARDAPIO, SYSDATE)) AS CUSTOREAL1,
                                                       (C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, V_NRTIPOCUSTO2, C.DTCARDAPIO, SYSDATE)) AS CUSTOREAL2,
                                                       (C.QTRETIRADA * CUSTO_PRODUTO(C.CDFILIAL, C.CDPRODUTO, V_NRTIPOCUSTO3, C.DTCARDAPIO, SYSDATE)) AS CUSTOREAL3,
                                                        C.CDPRODUTO, 0 QTCONSPLAN, C.QTRETIRADA, C.CDARVPROD, C.NMPRODUTO, C.SGUNIDADE
                                                   FROM(SELECT CA.CDFILIAL, CA.CDSERVFILI, CA.DTCARDAPIO, SUM(IR.QTRETIRADA * PRA.VRFATOCONV) AS QTRETIRADA,
                                                               PRB.CDPRODUTO, PRB.CDARVPROD, PRB.NMPRODUTO, PRB.SGUNIDADE
                                                          FROM CARDADIA CA, PRODUTO  PRA, PRODUTO  PRB, ITEMRETI IR, ITLANCTOEST IL
                                                         WHERE (PRB.CDPRODUTO = PRA.CDPRODESTO)
                                                           AND (PRA.CDPRODUTO = IR.CDPRODUTO)
                                                           AND (CA.CDFILIAL   = IR.CDFILIAL)
                                                           AND (NOT IR.QTRETIRADA IS NULL AND IR.QTRETIRADA <> 0)
                                                           AND (IL.CDFILIAL    = IR.CDFIITLANEST)
                                                           AND (IL.NRLANCESTQ  = IR.NRLANCESTQ)
                                                           AND (IL.NRSEQUITEM  = IR.NRSEQUITEM)
                                                           AND (IL.IDEVOLESTOQ = 'S')
                                                           AND (CA.CDFILIAL = C_FILISERV.CDFILIAL_MERENDA)
                                                           AND (CA.DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                                                           AND (CA.CDSERVFILI = C_FILISERV.CDSERVFILI)
                                                           AND (CA.NRCARDAPIO = IR.NRCARDAPIO)
                                                         GROUP BY CA.CDFILIAL, CA.CDSERVFILI, CA.DTCARDAPIO, PRB.CDPRODUTO, PRB.CDARVPROD, PRB.NMPRODUTO, PRB.SGUNIDADE) C
                                                  UNION ALL
                                                 SELECT RNE.CDFILIAL, RNE.CDSERVFILI, DT.DTDATA AS DTCARDAPIO, 0 AS CUSTOPLAN1, 0 AS CUSTOPLAN2, 0 AS CUSTOPLAN3,
                                                        ROUND(CNE.VRREALNESTO * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5) AS CUSTOREAL1, 0 AS CUSTOREAL2, 0 AS CUSTOREAL3,
                                                        CNE.CDPRODUTO AS CDPRODUTO, 0 AS QTCONSPLAN, 0 AS QTRETIRADA, PDB.CDARVPROD, PDB.NMPRODUTO, PDB.SGUNIDADE
                                                   FROM(SELECT DTMESCARD, CDSERVFILI, SUM(QTCOMEFETCAR) AS TOTCOMEN
                                                          FROM CARDADIA
                                                         WHERE (CDFILIAL = C_FILISERV.CDFILIAL_MERENDA)
                                                           AND (DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                                                           AND (CDSERVFILI = C_FILISERV.CDSERVFILI)
                                                         GROUP BY DTMESCARD, CDSERVFILI) TC, PRODUTO PDB, CARDADIA CA, RATENESTO RNE, CUSTNESTO CNE, DATA DT
                                                  WHERE (DT.DTDATA BETWEEN V_DTINI AND V_DATAFINAL)
                                                    AND (CNE.CDFILIAL = C_FILISERV.CDFILIAL_MERENDA)
                                                    AND (TO_CHAR(DT.DTDATA, 'MMYYYY') = TO_CHAR(CNE.DTCUSTNESTO, 'MMYYYY'))
                                                    AND (NOT CNE.VRREALNESTO IS NULL)
                                                    AND (RNE.CDFILIAL    = CNE.CDFILIAL)
                                                    AND (RNE.CDSERVFILI  = C_FILISERV.CDSERVFILI)
                                                    AND (RNE.CDPRODUTO   = CNE.CDPRODUTO)
                                                    AND (CA.CDFILIAL     = RNE.CDFILIAL)
                                                    AND (CA.CDSERVFILI   = RNE.CDSERVFILI)
                                                    AND (CA.DTCARDAPIO   = DT.DTDATA)
                                                    AND (TC.DTMESCARD    = CA.DTMESCARD)
                                                    AND (TC.CDSERVFILI   = CA.CDSERVFILI)
                                                    AND (NOT TC.TOTCOMEN IS NULL AND TC.TOTCOMEN <> 0)
                                                  /*UNION ALL
                                                 SELECT RNE.CDFILIAL, RNE.CDSERVFILI, DT.DTDATA AS DTCARDAPIO, 0 AS CUSTOPLAN1, 0 AS CUSTOPLAN2, 0 AS CUSTOPLAN3,
                                                        ROUND(TCN.VRTOTAITEM * (RNE.PERATENESTO/100) * (CA.QTCOMEFETCAR/TC.TOTCOMEN), 5) AS CUSTOREAL1, 0 AS CUSTOREAL2, 0 AS CUSTOREAL3,                                                     0 AS CUSTOREAL2, 0 AS CUSTOREAL3,
                                                        TCN.CDPRODUTO CDPRODUTO, 0 AS QTCONSPLAN, 0 AS QTRETIRADA, PDC.CDARVPROD, PDC.NMPRODUTO, PDC.SGUNIDADE
                                                   FROM DATA DT, RATENESTO RNE, CARDADIA CA, PRODUTO PDC,
                                                       (SELECT DTMESCARD, CDSERVFILI, SUM(QTCOMEFETCAR) AS TOTCOMEN
                                                          FROM CARDADIA
                                                         WHERE (CDFILIAL = C_FILISERV.CDFILIAL_MERENDA)
                                                           AND (DTCARDAPIO BETWEEN V_DTINI AND V_DATAFINAL)
                                                           AND (CDSERVFILI = C_FILISERV.CDSERVFILI)
                                                         GROUP BY DTMESCARD, CDSERVFILI) TC,
                                                       (SELECT TO_CHAR(NF.DTENTRSAID, 'MMYYYY') AS DTMESNF, IP.CDPRODUTO, SUM(IP.VRTOTAITEM) AS VRTOTAITEM
                                                          FROM NOTAFISCAL  NF, ITEMPRODUTO IP
                                                         WHERE (IP.CDFILIAL     = NF.CDFILIAL)
                                                           AND (IP.NRLANCTONF   = NF.NRLANCTONF)
                                                           AND (NF.CDFILIRECE   = C_FILISERV.CDFILIAL_MERENDA)
                                                           AND (NF.IDENTRSAIDOP = 'E')
                                                           AND (NF.DTENTRSAID BETWEEN V_DTINI AND V_DATAFINAL)
                                                         GROUP BY TO_CHAR(NF.DTENTRSAID, 'MMYYYY'), IP.CDPRODUTO) TCN
                                                  WHERE (DT.DTDATA BETWEEN V_DTINI AND V_DATAFINAL)
                                                    AND (TO_CHAR(DT.DTDATA, 'MMYYYY') = TCN.DTMESNF)
                                                    AND (RNE.CDFILIAL    = C_FILISERV.CDFILIAL_MERENDA)
                                                    AND (RNE.CDSERVFILI  = C_FILISERV.CDSERVFILI)
                                                    AND (CA.CDFILIAL     = RNE.CDFILIAL)
                                                    AND (CA.CDSERVFILI   = RNE.CDSERVFILI)
                                                    AND (CA.DTCARDAPIO   = DT.DTDATA)
                                                    AND (TCN.CDPRODUTO   = RNE.CDPRODUTO)
                                                    AND (TC.DTMESCARD    = CA.DTMESCARD)
                                                    AND (TC.CDSERVFILI   = CA.CDSERVFILI)
                                                    AND (NOT TC.TOTCOMEN IS NULL AND TC.TOTCOMEN <> 0)
                                                    AND (NOT EXISTS (SELECT CDFILIAL
                                                                       FROM CUSTNESTO
                                                                      WHERE (CDFILIAL   = C_FILISERV.CDFILIAL_MERENDA)
                                                                        AND DTCUSTNESTO = DT.DTDATA
                                                                        AND CDPRODUTO   = RNE.CDPRODUTO
                                                                        AND NOT VRREALNESTO IS NULL))*/
                                                 )GROUP BY CDFILIAL, CDSERVFILI, DTCARDAPIO, CDARVPROD, CDPRODUTO, NMPRODUTO, SGUNIDADE
                                          )PR
                                  ON CD.CDFILIAL   = PR.CDFILIAL
                                 AND CD.CDSERVFILI = PR.CDSERVFILI
                                 AND CD.DTCARDAPIO = PR.DTCARDAPIO
                               WHERE FI.CDFILIAL   = CD.CDFILIAL
                                 AND SE.CDFILIAL   = CD.CDFILIAL
                                 AND SE.CDSERVFILI = CD.CDSERVFILI
                             GROUP BY FI.CDFILIAL,--alteracao TIAGO
                             FI.NMFILIAL,--alteracao TIAGO
                             SE.CDSERVFILI,--alteracao TIAGO
                             SE.NMSERVICO,--alteracao TIAGO
                             CD.DTCARDAPIO,--alteracao TIAGO
                             CD.QTCOMESTICAR,--alteracao TIAGO
                             CD.QTCLIESTICAR,--alteracao TIAGO
                             CD.QTCOMEFETCAR,--alteracao TIAGO
                             CD.QTCLIEFETCAR --alteracao TIAGO
                             
                             --jefferson pme
                             UNION ALL
                             
                             SELECT FI.CDFILIAL AS CDFILIAL,
                                     FI.NMFILIAL     AS NMFILIAL,
                                     SE.CDSERVFILI   AS CDSERVFILI,
                                     SE.NMSERVICO    AS NMSERVICO,
                                     CP.DTCARDAPIO   AS DTCARDAPIO,
                                     SUM(CP.CUSTOPLAN1)   AS CUSTOPLAN1,
                                     SUM(CP.CUSTOPLAN2)   AS CUSTOPLAN2,
                                     SUM(CP.CUSTOPLAN3)   AS CUSTOPLAN3,
                                     SUM(CP.CUSTOREAL1)   AS CUSTOREAL1,
                                     SUM(CP.CUSTOREAL2)   AS CUSTOREAL2,
                                     SUM(CP.CUSTOREAL3)   AS CUSTOREAL3,
                                     SUM(CP.QTCONSPLAN)   AS QTCONSPLAN,
                                     SUM(CP.QTRETIRADA)   AS QTRETIRADA,
                                     CP.QTCOMESTICAR AS QTCOMESTICAR,
                                     CP.QTCLIESTICAR AS QTCLIESTICAR,
                                     CP.QTCOMEFETCAR AS QTCOMEFETCAR,
                                     CP.QTCLIEFETCAR AS QTCLIEFETCAR
                                FROM FILIAL FI, SERVICO SE, 
                                     (SELECT CP.CDFILICPLA CDFILIAL, CP.CDSERVCPLA CDSERVFILI, CP.DTCONSPLAN DTCARDAPIO, 
                                             SUM(DECODE(V_NRTIPOCUSTO1, NULL, 0, ((CP.QTCONSPLAN * PRA.VRFATOCONV) * 
                                               CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO1, CP.DTCONSPLAN, SYSDATE)))) AS CUSTOPLAN1, 
                                             SUM(DECODE(V_NRTIPOCUSTO2, NULL, 0, ((CP.QTCONSPLAN * PRA.VRFATOCONV) *
                                               CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO2, CP.DTCONSPLAN, SYSDATE)))) AS CUSTOPLAN2, 
                                             SUM(DECODE(V_NRTIPOCUSTO3, NULL, 0, ((CP.QTCONSPLAN * PRA.VRFATOCONV) *
                                               CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, V_NRTIPOCUSTO3, CP.DTCONSPLAN, SYSDATE)))) CUSTOPLAN3,
                                             0 CUSTOREAL1,
                                             0 CUSTOREAL2,
                                             0 CUSTOREAL3,
                                             CP.CDPRODCPLA CDPRODUTO, CP.NRCONSPLAN, CDFILCENTPME,
                                             0 QTCOMESTICAR, 0 QTCLIESTICAR, 0 QTCOMEFETCAR, 0 QTCLIEFETCAR, SUM(CP.QTCONSPLAN) QTCONSPLAN,
                                             0 QTRETIRADA
                                        FROM CONSPLAN CP INNER JOIN PRODUTO PRA ON CP.CDPRODCPLA = PRA.CDPRODUTO
                                       WHERE CP.CDFILICPLA = C_FILISERV.CDFILIAL_MERENDA
                                         AND CP.DTCONSPLAN BETWEEN V_DTINI AND V_DATAFINAL
                                         AND CP.NRCONSPLAN = V_CDSEQUENCIAL
                                         AND CP.CDSERVCPLA = C_FILISERV.CDSERVFILI
                                         AND CP.IDCONSPLAN = 'F'
                                         AND CP.CDFILCENTPME = C_TIPOCARD.CDFILCENTPME
                                         AND CP.CDTPCARDPME  = C_TIPOCARD.CDTIPOCARD
                                       GROUP BY CP.CDFILICPLA, CP.CDSERVCPLA, CP.DTCONSPLAN, CP.CDPRODCPLA, CP.NRCONSPLAN, CDFILCENTPME) CP
                               WHERE CP.CDFILIAL = FI.CDFILIAL
                                 AND CP.CDFILIAL = SE.CDFILIAL
                                 AND CP.CDSERVFILI = SE.CDSERVFILI 
                               GROUP BY FI.CDFILIAL, FI.NMFILIAL, SE.CDSERVFILI, SE.NMSERVICO,
                                        CP.DTCARDAPIO, CP.QTCOMESTICAR, CP.QTCLIESTICAR, CP.QTCOMEFETCAR,
                                        CP.QTCLIEFETCAR
                             --jefferson pme
                             
                             ) PRIN
                         GROUP BY CDFILIAL, NMFILIAL, CDSERVFILI, NMSERVICO--, QTCOMESTICAR, QTCLIESTICAR, QTCOMEFETCAR, QTCLIEFETCAR  --alteracao Tiago
                         ORDER BY CDFILIAL, CDSERVFILI) LOOP

                  BEGIN
                     IF C_FILISERV.QTCOMESTICAR > 0 THEN

                        V_VRPERCPLANTOTAL1 := (C_PRODUTO.CUSTOPLAN1 / C_FILISERV.QTCOMESTICAR);
                        V_VRPERCPLANTOTAL2 := (C_PRODUTO.CUSTOPLAN2 / C_FILISERV.QTCOMESTICAR);
                        V_VRPERCPLANTOTAL3 := (C_PRODUTO.CUSTOPLAN3 / C_FILISERV.QTCOMESTICAR);

                        V_VRCUSTPLANTOTAL1 := (C_PRODUTO.CUSTOPLAN1 * C_FILISERV.QTCOMESTICAR);
                        V_VRCUSTPLANTOTAL2 := (C_PRODUTO.CUSTOPLAN2 * C_FILISERV.QTCOMESTICAR);
                        V_VRCUSTPLANTOTAL3 := (C_PRODUTO.CUSTOPLAN3 * C_FILISERV.QTCOMESTICAR);
                        IF V_VRPRECVENDA > 0 THEN

                           V_VRTOTFCPLAN1 := (C_PRODUTO.CUSTOPLAN1 / (V_VRPRECVENDA * C_FILISERV.QTCOMESTICAR));
                           V_VRTOTFCPLAN2 := (C_PRODUTO.CUSTOPLAN2 / (V_VRPRECVENDA * C_FILISERV.QTCOMESTICAR));
                           V_VRTOTFCPLAN3 := (C_PRODUTO.CUSTOPLAN3 / (V_VRPRECVENDA * C_FILISERV.QTCOMESTICAR));
                        ELSE
                           V_VRTOTFCPLAN1  := V_ZERO;
                           V_VRTOTFCPLAN2  := V_ZERO;
                           V_VRTOTFCPLAN3  := V_ZERO;
                        END IF;
                     ELSE
                        V_VRPERCPLANTOTAL1 := V_ZERO;
                        V_VRPERCPLANTOTAL2 := V_ZERO;
                        V_VRPERCPLANTOTAL3 := V_ZERO;
                        V_VRCUSTPLANTOTAL1 := V_ZERO;
                        V_VRCUSTPLANTOTAL2 := V_ZERO;
                        V_VRCUSTPLANTOTAL3 := V_ZERO;
                        V_VRTOTFCPLAN1     := V_ZERO;
                        V_VRTOTFCPLAN2     := V_ZERO;
                        V_VRTOTFCPLAN3     := V_ZERO;
                     END IF;

                     IF (C_FILISERV.QTCOMEFETCAR > 0)  THEN

                        V_VRPERCREALTOTAL1 := (C_PRODUTO.CUSTOREAL1 / C_FILISERV.QTCOMEFETCAR);
                        V_VRPERCREALTOTAL2 := (C_PRODUTO.CUSTOREAL2 / C_FILISERV.QTCOMEFETCAR);
                        V_VRPERCREALTOTAL3 := (C_PRODUTO.CUSTOREAL3 / C_FILISERV.QTCOMEFETCAR);

                        V_VRCUSTREALTOTAL1 := (C_PRODUTO.CUSTOREAL1 * C_FILISERV.QTCOMEFETCAR);
                        V_VRCUSTREALTOTAL2 := (C_PRODUTO.CUSTOREAL2 * C_FILISERV.QTCOMEFETCAR);
                        V_VRCUSTREALTOTAL3 := (C_PRODUTO.CUSTOREAL3 * C_FILISERV.QTCOMEFETCAR);
                        IF V_VRPRECVENDA > 0 THEN

                           V_VRTOTALCUSTOSTD := ((C_FILISERV.VRCUSPADTCAR * C_FILISERV.QTCOMEFETCAR) /
                                                 (V_VRPRECVENDA * C_FILISERV.QTCOMEFETCAR ));

                           V_VRTOTFCREAL1 := (C_PRODUTO.CUSTOREAL1 / (V_VRPRECVENDA * C_FILISERV.QTCOMEFETCAR));
                           V_VRTOTFCREAL2 := (C_PRODUTO.CUSTOREAL2 / (V_VRPRECVENDA * C_FILISERV.QTCOMEFETCAR));
                           V_VRTOTFCREAL3 := (C_PRODUTO.CUSTOREAL3 / (V_VRPRECVENDA * C_FILISERV.QTCOMEFETCAR));
                        ELSE
                           V_VRTOTALCUSTOSTD := V_ZERO;
                           V_VRTOTFCREAL1    := V_ZERO;
                           V_VRTOTFCREAL2    := V_ZERO;
                           V_VRTOTFCREAL3    := V_ZERO;
                        END IF;
                     ELSE
                        V_VRPERCREALTOTAL1 := V_ZERO;
                        V_VRPERCREALTOTAL2 := V_ZERO;
                        V_VRPERCREALTOTAL3 := V_ZERO;
                        V_VRCUSTREALTOTAL1 := V_ZERO;
                        V_VRCUSTREALTOTAL2 := V_ZERO;
                        V_VRCUSTREALTOTAL3 := V_ZERO;
                        V_VRTOTALCUSTOSTD  := V_ZERO;
                        V_VRTOTFCREAL1     := V_ZERO;
                        V_VRTOTFCREAL2     := V_ZERO;
                        V_VRTOTFCREAL3     := V_ZERO;
                     END IF;


                     V_VRTOTVENDSERV := (V_VRPRECVENDA * C_FILISERV.QTCOMEFETCAR);


                     V_VRTOTCUSTPADREST := C_FILISERV.VRCUSPADTCAR * C_FILISERV.QTCOMESTICAR;


                     V_VRTOTCUSTPADREFE := C_FILISERV.VRCUSPADTCAR * C_FILISERV.QTCOMEFETCAR;

                  INSERT INTO DM_RESUFOOD_PASSFUTU
                        ( CDFILCENTPME,
                          NMFILCENTPME,
                          CDFILIAL,
                          NMFILIAL,
                          CDAREAGERE,
                          NMAREAGERE,
                          CDSUPERVISOR,
                          NMSUPERVISOR,
                          CDGOPERAC,
                          NMGOPERAC,
                          CDEMPRESA,
                          NMEMPRESA,
                          CDSEGMER,
                          NMSEGMERCADO,
                          CDPLANEJADORA,
                          NMPLANEJADORA,
                          CDGUNIDADE,
                          NMGUNIDADE,
                          CDGRUPSERV,
                          NMGRUPSERV,
                          CDFILISERVFILI,
                          NMSERVICO,
                          CDTIPOCARD,
                          NMTIPOCARD,
                          CDANOBI,
                          CDMESBI,
                          QTCOMESTSERV,
                          QTCOMEFESERV,
                          VRCUSPADSERV,
                          DSDTPROCFILI,
                          DSTPSERV,
                          VRCUSVNDSERV,
                          VRPERTOTCUSTSTD,
                          VRTOTAVENDASERV,
                          VRCUSPADEST,
                          VRCUSPADEFE,
                          VRCUSPLASERV1,
                          VRCUSPLASERV2,
                          VRCUSPLASERV3,
                          VRCUSREASERV1,
                          VRCUSREASERV2,
                          VRCUSREASERV3,
                          VRPERCPLATOTSER1,
                          VRPERCPLATOTSER2,
                          VRPERCPLATOTSER3,
                          VRPERCREATOTSER1,
                          VRPERCREATOTSER2,
                          VRPERCREATOTSER3,
                          VRCUSPLATOTSER1,
                          VRCUSPLATOTSER2,
                          VRCUSPLATOTSER3,
                          VRCUSREATOTSER1,
                          VRCUSREATOTSER2,
                          VRCUSREATOTSER3,
                          VRPERFCPLANS1,
                          VRPERFCPLANS2,
                          VRPERFCPLANS3,
                          VRPERFCPREALS1,
                          VRPERFCPREALS2,
                          VRPERFCPREALS3,
                          DSDTULTCOMEFE,
                          DTULTAPURAC,
                          CDMESBI_MMYYYY,
                          DSMESBI_MMYYYY,
                          DSBIMESTRE,
                          DSTRIMESTRE,
                          DSQUADIMESTRE,
                          DSSEMESTRE
                        )
                  VALUES
                        ( C_CENTRAL_MERENDA.CDCENTRAL,
                          C_CENTRAL_MERENDA.NMCENTRAL,
                          C_FILISERV.CDFILIAL_MERENDA,
                          C_FILISERV.NMFILIAL,
                          C_FILISERV.CDAREAGERE,
                          C_FILISERV.NMAREAGERE,
                          C_FILISERV.CDSUPERVISOR,
                          C_FILISERV.NMSUPERVISOR,
                          C_FILISERV.CDGOPERAC,
                          C_FILISERV.NMGERENTE_OPERACIONAL,
                          C_FILISERV.CDEMPRESA,
                          C_FILISERV.NMEMPRESA,
                          C_FILISERV.CDSEGMER,
                          C_FILISERV.NMSEGMERCADO,
                          C_FILISERV.CDPLANEJADORA,
                          C_FILISERV.NMPLANEJADORA,
                          C_FILISERV.CDGUNIDADE,
                          C_FILISERV.NMGUNIDADE,
                          C_FILISERV.CDGRUPSERV,
                          C_FILISERV.NMGRUPSERV,
                          C_FILISERV.CDFILIAL_MERENDA||C_FILISERV.CDSERVFILI,
                          C_FILISERV.NMSERVICO,
                          C_TIPOCARD.CDTIPOCARD,
                          C_TIPOCARD.NMTIPOCARD,
                          V_ANO,
                          V_MES,
                          NVL(C_FILISERV.QTCOMESTICAR,0),
                          NVL(C_FILISERV.QTCOMEFETCAR,0),
                          NVL(C_FILISERV.VRCUSPADTCAR,0),
                          NVL(TO_CHAR(C_CENTRAL_MERENDA.DTPROCESSA, 'DD/MM/YYYY'), 0),
                          NVL(DECODE(C_FILISERV.IDSERVPRINC,'S', 'Servico Principal','Outros Servicos'),0),
                          NVL(V_VRPRECVENDA, 0),
                          NVL(V_VRTOTALCUSTOSTD, 0),
                          NVL(V_VRTOTVENDSERV,0),
                          NVL(V_VRTOTCUSTPADREST, 0),
                          NVL(V_VRTOTCUSTPADREFE, 0),
                          NVL(C_PRODUTO.CUSTOPLAN1,0),
                          NVL(C_PRODUTO.CUSTOPLAN2, 0),
                          NVL(C_PRODUTO.CUSTOPLAN3, 0),
                          NVL(C_PRODUTO.CUSTOREAL1,0),
                          NVL(C_PRODUTO.CUSTOREAL2, 0),
                          NVL(C_PRODUTO.CUSTOREAL3, 0),
                          NVL(V_VRPERCPLANTOTAL1,0),
                          NVL(V_VRPERCPLANTOTAL2, 0),
                          NVL(V_VRPERCPLANTOTAL3, 0),
                          NVL(V_VRPERCREALTOTAL1,0),
                          NVL(V_VRPERCREALTOTAL2, 0),
                          NVL(V_VRPERCREALTOTAL3, 0),
                          NVL(V_VRCUSTPLANTOTAL1,0),
                          NVL(V_VRCUSTPLANTOTAL2, 0),
                          NVL(V_VRCUSTPLANTOTAL3, 0),
                          NVL(V_VRCUSTREALTOTAL1,0),
                          NVL(V_VRCUSTREALTOTAL2, 0),
                          NVL(V_VRCUSTREALTOTAL3, 0),
                          NVL(V_VRTOTFCPLAN1,0),
                          NVL(V_VRTOTFCPLAN2, 0),
                          NVL(V_VRTOTFCPLAN3, 0),
                          NVL(V_VRTOTFCREAL1,0),
                          NVL(V_VRTOTFCREAL2,0),
                          NVL(V_VRTOTFCREAL3, 0),
                          TO_CHAR(V_DTULTCOMEFE, 'DD/MM/YYYY'),
                          V_DATAFINAL,
                          TO_CHAR(TO_DATE(V_DATAFINAL),'MM/YYYY'),--CDMESBI_MMYYYY
                          TO_CHAR(TO_DATE(V_DATAFINAL),'MON/YYYY'),--DSMESBI_MMYYYY
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 2) + 1)||' Bim',--DSBIMESTRE
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 3) + 1)||' Tri',--DSTRIMESTRE
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 4) + 1)||' Qua',--DSQUADIMESTRE
                          TO_CHAR(TRUNC((TO_CHAR(TO_DATE(V_DATAFINAL),'MM') - 1) / 6) + 1)||' Sem'--DSSEMESTRE
                        );
                        COMMIT;
            END;--alteracao tiago
          END LOOP;--PRODUTO
        END LOOP;--FILISERV
      
      END LOOP;--TIPOCARD
      DELETE FROM CONSPLAN WHERE NRCONSPLAN = V_CDSEQUENCIAL;
      END LOOP;--CENTRAL
      V_ANOANT := TO_CHAR (ADD_MONTHS(V_DTINI, -1),'YYYY');
      V_MESANT := TO_CHAR (ADD_MONTHS(V_DTINI, -1),'MM');
      FOR ANT IN
         (SELECT * FROM DM_RESUFOOD_PASSFUTU
          WHERE CDANOBI >= V_ANOANT
            AND  CDMESBI >= V_MESANT
          ORDER BY  CDEMPRESA, CDFILIAL, CDGRUPSERV, CDFILISERVFILI, DSTPSERV, CDANOBI, CDMESBI
         )
      LOOP
         V_DTINIANT := ADD_MONTHS(TO_DATE( (ANT.CDANOBI||ANT.CDMESBI||'01'),'YYYY/MM/DD'),+1);
         V_MES  :=  TO_CHAR(V_DTINIANT,'MM');
         V_ANO  :=  TO_CHAR(V_DTINIANT,'YYYY');
         UPDATE DM_RESUFOOD_PASSFUTU
         SET VRPLAMESANT1     = ANT.VRCUSPLASERV1,
             VRREALMESANT1    = ANT.VRCUSREASERV1,
             QTCOMEPLAMESANT  = ANT.QTCOMESTSERV,
             QTCOMEREALMESANT = ANT.QTCOMEFESERV
         WHERE CDEMPRESA = ANT.CDEMPRESA
           AND CDFILIAL  = ANT.CDFILIAL
           AND ((CDGRUPSERV IS NULL AND ANT.CDGRUPSERV IS NULL) OR (CDGRUPSERV = ANT.CDGRUPSERV))
           AND CDFILISERVFILI = ANT.CDFILISERVFILI
           AND DSTPSERV = ANT.DSTPSERV
           AND CDANOBI = V_ANO
           AND CDMESBI = V_MES;
      END LOOP;
      V_DTINI := ADD_MONTHS(V_DTINI,1);
   --END LOOP;
   END LOOP;--WHILE
END;
/