create or replace PROCEDURE "ETL_PLANEJADORA"
IS
   V_CDFILIAL VARCHAR2(4);
   V_SERVICOS VARCHAR2(4000);
   V_DTINICIAL DATE;
   V_DTFINAL DATE;
   V_NRCONSPLAN VARCHAR2(20);
BEGIN                                            
   V_CDFILIAL := NULL;
   V_NRCONSPLAN := '';
   NOVO_CODIGO('ETL_CONSPLAN', V_NRCONSPLAN,1);
   V_NRCONSPLAN := SUBSTR(V_NRCONSPLAN,11,10);
   FOR CUR_FILIAL IN
      (SELECT
          SE.CDFILIAL, 
          SE.CDSERVFILI, 
          PFI.DTPROCESSA DTPROC,
          ADD_MONTHS(PFI.DTPROCESSA,-1) - TO_CHAR(PFI.DTPROCESSA,'DD')+1 DTPROC_INI,
          ADD_MONTHS(PFI.DTPROCESSA,2) - TO_CHAR(PFI.DTPROCESSA,'DD') DTPROC_FIM 
       FROM
          SERVICO SE INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                      FROM PARAMGERAL PG, PARAMFILIAL PF) PFI ON (SE.CDFILIAL = PFI.CDFILIAL)
       WHERE ((SE.IDSERVPRINC = 'S') OR (SE.IDINDICADOR = 'S'))
       ORDER BY SE.CDFILIAL, SE.CDSERVFILI)
   LOOP
      IF (NOT V_CDFILIAL IS NULL) AND (V_CDFILIAL <> CUR_FILIAL.CDFILIAL) THEN
         CONSUMO_PLANEJADO_PLA(V_CDFILIAL,
                               V_SERVICOS,
                               V_DTINICIAL,
                               V_DTFINAL,
                               'T',   /* GRAVACAO NA TABELA CONSPLAN */
                               'CFP', /* CARDAPIO, CONSUMO FIXO E PERCAPITA */
                               'N',   /* FOI INCLUIDO O PARAMETRO P_IDSEPPRATO */
                               0,
                               'N',
                               V_NRCONSPLAN,
                               '0',
                               'zzzzzzzzzz',
                               '',
                               '_'||TO_CHAR(V_DTINICIAL, 'YYYYMMDD')||'_',
                               '_'||TO_CHAR(V_DTFINAL, 'YYYYMMDD')||'_'
                                  );
         COMMIT;
         V_SERVICOS := CUR_FILIAL.CDSERVFILI; /*INICIA A VARIAVEL NOVAMENTE COM O VALOR DO PRIMEIRO SERVICO DA FILIAL*/
      ELSE
         V_SERVICOS := V_SERVICOS || '_' ||  CUR_FILIAL.CDSERVFILI; /*CONCATENA A LISTA DE SERVICOS COM OS SERVICOS PRINCIPAIS DE CADA FILIAL*/
      END IF;
      V_CDFILIAL := CUR_FILIAL.CDFILIAL;  /*ATUALIZA A VARIAVEL FILIAL*/
      V_DTINICIAL := CUR_FILIAL.DTPROC_INI;
      V_DTFINAL := CUR_FILIAL.DTPROC_FIM;
   END LOOP;
   /*CHAMA A CONSPLAN PARA A ULTIMA FILIAL DO LOOP*/
   CONSUMO_PLANEJADO_PLA(V_CDFILIAL,
                         V_SERVICOS,
                         V_DTINICIAL,
                         V_DTFINAL,
                         'T',   /* GRAVAC?O NA TABELA CONSPLAN */
                         'CFP', /* CARDAPIO, CONSUMO FIXO E PERCAPITA */
                         'N',   /* FOI INCLUIDO O PARAMETRO P_IDSEPPRATO */
                         0,
                         'N',
                         V_NRCONSPLAN,
                         '0',
                         'zzzzzzzzzz',
                         '',
                         '_'||TO_CHAR(V_DTINICIAL, 'YYYYMMDD')||'_',
                         '_'||TO_CHAR(V_DTFINAL, 'YYYYMMDD')||'_'
                            );
   COMMIT;
   /*LIMPA A TABELA DM_PLANEJADORA PARA ATUALIZAR AS INFORMACOES NA MESMA*/
   DELETE FROM DM_PLANEJADORA;
   /*INSERE NA TABELA DM_PLANEJADORA TODAS AS INFORMACOES DAS FILIAIS E SERVICOS*/
   INSERT INTO DM_PLANEJADORA 
   (CDFILIAL, DTPROCESSA, CDSERVFILI, VRPUOBJETIVO, VRPUPLANEJADO, VRPUTENDENCIA, QTCOMESTMES, QTCOMESTPOS,
    QTEFETIVOS, VRDIFOBJPLA, VRDIFOBJTEND, VRPUPLAANT, VRPUREALANT, VRPUPLAPRXMES, VRREALCARD, VRPLACARD,
    PUREALDIAANT, PUPLADIAANT, VRPADMESANT)
   (SELECT CDFILIAL,
          DTPROC,
          CDSERVFILI,
          PU_OBJETIVO,
          ROUND(DECODE(ESTIMADOS_MES,0,0,VRCUSTO_ATUAL / ESTIMADOS_MES),5) AS PU_PLANEJADO,
          ROUND(DECODE((ESTIMADOS_POS_DTPROC + EFETIVOS),0,0,(CUSTO_REAL_MES + VRCUSTO_POSDT) / (ESTIMADOS_POS_DTPROC + EFETIVOS)),5) AS PU_TENDENCIA,
          ESTIMADOS_MES,
          ESTIMADOS_POS_DTPROC,
          EFETIVOS,
          ROUND(VRCUSTO_ATUAL - (PU_OBJETIVO * ESTIMADOS_MES),5) AS DIF_OBJ_PLA,
          ROUND((CUSTO_REAL_MES + VRCUSTO_POSDT) - (PU_OBJETIVO * ESTIMADOS_MES),5) DIF_OBJ_TEND,
          ROUND(DECODE(ESTIMADOS_ANT,0,0, VRCUSTO_ANT / ESTIMADOS_ANT) ,5) AS PU_PLAN_ANT,
          ROUND(DECODE(EFETIVOS_ANT,0,0,CUSTO_REAL_ANT / EFETIVOS_ANT),5) PU_REAL_ANT,
          ROUND(DECODE(ESTIMADOS_PROX_MES,0,0,VRCUSTO_APOS / ESTIMADOS_PROX_MES),5) AS PU_PROX_MES,
          ROUND((CUSTO_REAL_MES_DTPROC_MENOS_UM),5) AS VRREALCARD, /*CUSTO REALIZADO DO PRIMEIRO DIA DO MES DA DATA DE PROCESSAMENTO MENOS UM ATE A DATA DE PROCESSAMENTO MENOS UM*/
          ROUND((CUSTO_PLA_MES_DTPROC_MENOS_UM),5) AS VRPLACARD, /*CUSTO PLANEJADO DO PRIMEIRO DIA DO MES DA DATA DE PROCESSAMENTO MENOS UM ATE A DATA DE PROCESSAMENTO MENOS UM*/
          ROUND(DECODE(EFETIVOS_DTPROC_MENOS_UM, 0, 0, (CUSTO_REAL_DTPROC_MENOS_UM / EFETIVOS_DTPROC_MENOS_UM)), 5) AS PU_REAL_DTPROC_MENOS_UM,/*PU REALIZADO REFERENTE AO DIA ANTERIOR A DATA DE PROCESSAMENTO*/
          ROUND(DECODE(ESTIMADOS_DTPROC_MENOS_UM, 0, 0, (CUSTO_PLA_DTPROC_MENOS_UM / ESTIMADOS_DTPROC_MENOS_UM)), 5) AS PU_PLA_DTPROC_MENOS_UM,/*PU PLANEJADO REFERENTE AO DIA ANTERIOR A DATA DE PROCESSAMENTO*/
          (PU_PADR_MES_DTPROC_MENOS_UM) AS PU_PADR_MES_DTPROC_MENOS_UM /* CUSTO PADRAO REFERENTE AO MES DA DATA DE PROCESSAMENTO - 1*/
      FROM
        (SELECT      
        SE.CDFILIAL, 
        PFI.DTPROCESSA DTPROC,
        SE.CDSERVFILI,
        NVL(CPR.VRCUSTPADR,0) PU_OBJETIVO , /*CUSTO PADRAO OBJETIVO UNITARIO*/
        NVL(CARD.ESTIMADOS_MES,0) ESTIMADOS_MES, /*TOTAL DE COMENSAIS ESTIMADOS DO MES ATUAL*/
        NVL(CARD.EFETIVOS,0) EFETIVOS,/*TOTAL DE EFETIVOS DO MES ATUAL*/
        NVL(CARD.EFETIVOS_ANT,0) EFETIVOS_ANT, /*TOTAL DE EFETIVOS DO MES ANTERIOR*/
        NVL(CARD.ESTIMADOS_ANT,0) ESTIMADOS_ANT, /*TOTAL DE COMENSAIS ESTIMADOS DO MES ANTERIOR*/
        NVL(CARD.ESTIMADOS_PROX_MES,0) ESTIMADOS_PROX_MES, /*TOTAL DE COMENSAIS ESTIMADOS DO PROXIMO MES*/    
        NVL(CARDPOS.ESTIMADOS_POS_DTPROC,0) ESTIMADOS_POS_DTPROC, /*TOTAL DE COMENSAIS ESTIMADOS DA DATA DE PROCESSAMENTO AO FINAL DO MES ATUAL*/
        NVL(PU_PLAN.VRCUSTO_ATUAL,0) VRCUSTO_ATUAL, /*CUSTO TOTAL PLANEJADO DO MES ATUAL*/ 
        NVL(PU_PLAN.VRCUSTO_ANT,0) VRCUSTO_ANT, /*CUSTO TOTAL PLANEJADO DO MES ANTERIOR*/
        NVL(PU_PLAN.VRCUSTO_APOS,0) VRCUSTO_APOS, /*CUSTO TOTAL PLANEJADO DO PROXIMO MES*/
        NVL(PU_PLAN.VRCUSTO_POSDT,0) VRCUSTO_POSDT, /*CUSTO TOTAL PLANEJADO APOS A DATA DE PROCESSAMENTO ATE O FIM DO MES*/
        NVL(CUSTO_REAL.CUSTO_REAL_MES,0) CUSTO_REAL_MES, /*CUSTO REALIZADO DO MES ATUAL (ATE A DATA DE PROCESSAMENTO)*/
        NVL(CUSTO_REAL.CUSTO_REAL_ANT,0) CUSTO_REAL_ANT, /*CUSTO REALIZADO DO MES ANTERIOR*/ 
        NVL(CPR_DTPROC_ANT.VRCUSTPADR,0) AS PU_PADR_MES_DTPROC_MENOS_UM , /* CUSTO PADRAO REFERENTE AO MES DA DATA DE PROCESSAMENTO - 1*/        
        NVL(CUSTO_REAL.CUSTO_REAL_MES_DTPROC_MENOS_UM, 0) AS CUSTO_REAL_MES_DTPROC_MENOS_UM, /*CUSTO REALIZADO DO PRIMEIRO DIA DA DATA DE PROCESSAMENTO - 1 ATE A DATA DE PROCESSAMENTO - 1*/
        NVL(PU_PLAN.CUSTO_PLA_MES_DTPROC_MENOS_UM, 0) AS CUSTO_PLA_MES_DTPROC_MENOS_UM, /*CUSTO PLANEJADO DO PRIMEIRO DIA DA DATA DE PROCESSAMENTO - 1 ATE A DATA DE PROCESSAMENTO - 1*/ 
        NVL(CARD.EFETIVOS_DTPROC_MENOS_UM,0) AS EFETIVOS_DTPROC_MENOS_UM,/*COMENSAIS EFETIVOS DO DIA ANTERIOR A DATA DE PROCESSAMENTO*/
        NVL(CARD.ESTIMADOS_DTPROC_MENOS_UM,0) AS ESTIMADOS_DTPROC_MENOS_UM,/*COMENSAIS ESTIMADOS DO DIA ANTERIOR A DATA DE PROCESSAMENTO*/
        NVL(CUSTO_REAL.CUSTO_REAL_DTPROC_MENOS_UM, 0) AS CUSTO_REAL_DTPROC_MENOS_UM, /*CUSTO REALIZADO DO DIA ANTERIOR A DATA DE PROCESSAMENTO*/
        NVL(PU_PLAN.CUSTO_PLA_DTPROC_MENOS_UM, 0) AS CUSTO_PLA_DTPROC_MENOS_UM /*CUSTO PLANEJADO DO DIA ANTERIOR A DATA DE PROCESSAMENTO*/        
         FROM (SELECT CDFILIAL, CDSERVFILI FROM SERVICO S WHERE ((IDSERVPRINC = 'S') OR (IDINDICADOR = 'S'))) SE    /*RETORNA CODIGO E NOME DO SERVICO*/
                        INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                      FROM PARAMGERAL PG , PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = SE.CDFILIAL)  /*RETORNA INFORMACOES REFERENTE AO OPERADOR, FILIAL E DATA DE PROCESSAMENTO*/
                        LEFT JOIN CUSTPADR CPR ON ((SE.CDFILIAL = CPR.CDFILIAL) AND  /* CUSTO PADRAO, OU PU_OBJETIVO*/
                                                   (SE.CDSERVFILI = CPR.CDSERVFILI) AND 
                                                   (((PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD')) + 1) = CPR.DTREFCUSPADR ))                                                     
                        LEFT JOIN CUSTPADR CPR_DTPROC_ANT ON ((SE.CDFILIAL = CPR_DTPROC_ANT.CDFILIAL) AND  /* CUSTO PADRAO REFERENTE AO MES DA DATA DE PROCESSAMENTO - 1*/
                                                              (SE.CDSERVFILI = CPR_DTPROC_ANT.CDSERVFILI) AND 
                                                              (TO_DATE('01'||TO_CHAR((PFI.DTPROCESSA  - 1), 'MM')||TO_CHAR((PFI.DTPROCESSA - 1), 'YYYY')) = CPR_DTPROC_ANT.DTREFCUSPADR ))                                               
                        /*SOMA DE COMENSAIS ESTIMADOS E EFETIVOS DO MES INTEIRO, DO MES ANTERIOR E DO PROXIMO MES*/                           
                        LEFT JOIN (SELECT 
                                      CD_MES.CDFILIAL, 
                                      CD_MES.CDSERVFILI, 
                                      CD_MES.ESTIMADOS_MES, 
                                      CD_REAL_MES.EFETIVOS, 
                                      CD_ANT.EFETIVOS_ANT, 
                                      CD_ANT.ESTIMADOS_ANT, 
                                      CD_APOS.ESTIMADOS_PROX_MES, 
                                      DTPROC_MENOS_UM.EFETIVOS_DTPROC_MENOS_UM, 
                                      DTPROC_MENOS_UM.ESTIMADOS_DTPROC_MENOS_UM 
                                     FROM
                                     (SELECT CD.CDFILIAL, CD.CDSERVFILI, SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMEFETCAR,0), NVL(CD.QTCLIEFETCAR,0))) EFETIVOS
                                                   FROM CARDADIA CD INNER JOIN SERVICO SE ON ((SE.CDFILIAL = CD.CDFILIAL) AND (SE.CDSERVFILI = CD.CDSERVFILI))
                                                                    INNER JOIN (SELECT PF.CDFILIAL,PF.IDCOMCUSPERC ,TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA
                                                                                  FROM PARAMGERAL PG, PARAMFILIAL PF) PFI ON (CD.CDFILIAL = PFI.CDFILIAL)
                                                  WHERE
                                                  (CD.DTCARDAPIO BETWEEN (PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD')+ 1) AND
                                                                                   (PFI.DTPROCESSA - 1))
                                                  GROUP BY CD.CDFILIAL, CD.CDSERVFILI)CD_REAL_MES
                                      FULL OUTER JOIN
                                      (SELECT CD.CDFILIAL, CD.CDSERVFILI, SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMESTICAR,0), NVL(CD.QTCLIESTICAR,0))) ESTIMADOS_MES
                                                   FROM CARDADIA CD INNER JOIN (SELECT DISTINCT CDFILICPLA, CDSERVCPLA, DTCONSPLAN FROM CONSPLAN WHERE NRCONSPLAN = V_NRCONSPLAN) CP
                                                   ON (CD.CDFILIAL = CP.CDFILICPLA AND CD.CDSERVFILI = CP.CDSERVCPLA AND DTCARDAPIO = DTCONSPLAN)
                                                                    INNER JOIN (SELECT PF.CDFILIAL,PF.IDCOMCUSPERC ,TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA
                                                                                  FROM PARAMGERAL PG, PARAMFILIAL PF) PFI ON (CD.CDFILIAL = PFI.CDFILIAL)
                                                  WHERE
                                                  (CD.DTCARDAPIO BETWEEN (PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD')+ 1) AND
                                                                                   LAST_DAY(PFI.DTPROCESSA))
                                                  GROUP BY CD.CDFILIAL, CD.CDSERVFILI) CD_MES
                                       ON ((CD_REAL_MES.CDFILIAL = CD_MES.CDFILIAL) AND (CD_REAL_MES.CDSERVFILI = CD_MES.CDSERVFILI))
                                       LEFT JOIN 
                                      (SELECT CD.CDFILIAL, CD.CDSERVFILI, SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMESTICAR,0), NVL(CD.QTCLIESTICAR,0))) ESTIMADOS_ANT,
                                                        SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMEFETCAR,0), NVL(CD.QTCLIEFETCAR,0))) EFETIVOS_ANT    
                                                   FROM CARDADIA CD INNER JOIN (SELECT DISTINCT CDFILICPLA, CDSERVCPLA, DTCONSPLAN FROM CONSPLAN WHERE NRCONSPLAN = V_NRCONSPLAN) CP
                                                   ON (CD.CDFILIAL = CP.CDFILICPLA AND CD.CDSERVFILI = CP.CDSERVCPLA AND DTCARDAPIO = DTCONSPLAN)
                                                   INNER JOIN (SELECT PF.CDFILIAL, PF.IDCOMCUSPERC, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                                  FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (CD.CDFILIAL = PFI.CDFILIAL) 
                                                  WHERE  (CD.DTCARDAPIO BETWEEN ADD_MONTHS((PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD')+ 1),-1) AND 
                                                                                   ADD_MONTHS(LAST_DAY(PFI.DTPROCESSA),-1))                                                        
                                                  GROUP BY CD.CDFILIAL, CD.CDSERVFILI) CD_ANT 
                                       ON ((CD_ANT.CDFILIAL = CD_MES.CDFILIAL) AND (CD_ANT.CDSERVFILI = CD_MES.CDSERVFILI)) 
                                       LEFT JOIN           
                                      (SELECT CD.CDFILIAL, CD.CDSERVFILI, SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMESTICAR,0), NVL(CD.QTCLIESTICAR,0))) ESTIMADOS_PROX_MES
                                                   FROM CARDADIA CD INNER JOIN (SELECT DISTINCT CDFILICPLA, CDSERVCPLA, DTCONSPLAN FROM CONSPLAN WHERE NRCONSPLAN = V_NRCONSPLAN) CP
                                                   ON (CD.CDFILIAL = CP.CDFILICPLA AND CD.CDSERVFILI = CP.CDSERVCPLA AND DTCARDAPIO = DTCONSPLAN)
                                                   INNER JOIN (SELECT PF.CDFILIAL, PF.IDCOMCUSPERC ,TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                                  FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (CD.CDFILIAL = PFI.CDFILIAL) 
                                                  WHERE  (CD.DTCARDAPIO BETWEEN ADD_MONTHS((PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD')+ 1),1) AND 
                                                                                ADD_MONTHS(LAST_DAY(PFI.DTPROCESSA),1))                                                    
                                                  GROUP BY CD.CDFILIAL, CD.CDSERVFILI) CD_APOS 
                                       ON ((CD_APOS.CDFILIAL = CD_MES.CDFILIAL) AND (CD_APOS.CDSERVFILI = CD_MES.CDSERVFILI))   
                                       LEFT JOIN
                                       /*CALCULO DO TOTAL DE EFETIVOS E ESTIMADOS DA DATA DE PROCESSAMENTO MENOS UM DIA*/
                                       (SELECT CD.CDFILIAL, CD.CDSERVFILI, SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMEFETCAR,0), NVL(CD.QTCLIEFETCAR,0))) EFETIVOS_DTPROC_MENOS_UM,
                                               SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMESTICAR,0), NVL(CD.QTCLIESTICAR,0))) ESTIMADOS_DTPROC_MENOS_UM
                                                   FROM CARDADIA CD 
                                                   INNER JOIN (SELECT PF.CDFILIAL,PF.IDCOMCUSPERC ,TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                                  FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (CD.CDFILIAL = PFI.CDFILIAL) 
                                                  WHERE  (CD.DTCARDAPIO = TO_DATE(PFI.DTPROCESSA - 1))                                                  
                                                  GROUP BY CD.CDFILIAL, CD.CDSERVFILI)DTPROC_MENOS_UM
                                        ON ((CD_MES.CDFILIAL = DTPROC_MENOS_UM.CDFILIAL) AND (CD_MES.CDSERVFILI = DTPROC_MENOS_UM.CDSERVFILI))
                                       /*FIM DO CALCULO DO TOTAL DE EFETIVOS E ESTIMADOS DA DATA DE PROCESSAMENTO MENOS UM DIA*/
                        )CARD ON ((SE.CDFILIAL = CARD.CDFILIAL) AND (SE.CDSERVFILI = CARD.CDSERVFILI))
                        /*FIM SOMA DE COMENSAIS ESTIMADOS E EFETIVOS DO MES INTEIRO, DO MES ANTERIOR E DO PROXIMO MES*/
                        /*SOMA DE COMENSAIS ESTIMADOS DA DTPROC ATE FIM DO MES*/                           
                        LEFT JOIN (SELECT CD.CDFILIAL, 
                                          CD.CDSERVFILI, 
                                          SUM(DECODE(PFI.IDCOMCUSPERC, 'T',NVL(CD.QTCOMESTICAR,0), NVL(CD.QTCLIESTICAR,0))) ESTIMADOS_POS_DTPROC                                          
                                     FROM CARDADIA CD  INNER JOIN (SELECT DISTINCT CDFILICPLA, CDSERVCPLA, DTCONSPLAN FROM CONSPLAN WHERE NRCONSPLAN = V_NRCONSPLAN) CP
                                                   ON (CD.CDFILIAL = CP.CDFILICPLA AND CD.CDSERVFILI = CP.CDSERVCPLA AND DTCARDAPIO = DTCONSPLAN)
                                     INNER JOIN (SELECT PF.CDFILIAL, PF.IDCOMCUSPERC, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                    FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (CD.CDFILIAL = PFI.CDFILIAL)
                                    WHERE (CD.DTCARDAPIO BETWEEN PFI.DTPROCESSA AND LAST_DAY(PFI.DTPROCESSA))
                                 GROUP BY CD.CDFILIAL, CD.CDSERVFILI
                                 )CARDPOS ON ((SE.CDFILIAL = CARDPOS.CDFILIAL) AND 
                                              (SE.CDSERVFILI = CARDPOS.CDSERVFILI))
                        /*FIM SOMA DE COMENSAIS ESTIMADOS DA DTPROC ATE FIM DO MES*/
                        /*CALCULA CUSTO TOTAL PLANEJADO ATUAL, DO MES ANTERIOR E DO PROXIMO MES*/
                        LEFT JOIN (SELECT C_ATUAL.CDFILICPLA, 
                                          C_ATUAL.CDSERVCPLA,
                                          C_PLA_MES_DTPROC_MENOS_UM.CUSTO_PLA_MES_DTPROC_MENOS_UM,
                                          C_ATUAL.VRCUSTO_ATUAL, 
                                          C_ANTES.VRCUSTO_ANT,  
                                          C_APOS.VRCUSTO_APOS, 
                                          C_POSDTPROC.VRCUSTO_POSDT, 
                                          C_PLA_DTPROC_MENOS_UM.CUSTO_PLA_DTPROC_MENOS_UM                                          
                                     FROM
                                      (SELECT
                                           CP.CDFILICPLA, CP.CDSERVCPLA, 
                                           SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, NVL(PP.CDTIPOCUSTOPLA, '03'), 
                                               DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CP.DTCONSPLAN),CP.DTCONSPLAN), SYSDATE) * CP.QTCONSPLAN) AS VRCUSTO_ATUAL
                                        FROM
                                           CONSPLAN CP LEFT JOIN CARDAMES CM ON ((CP.CDFILICPLA = CM.CDFILIAL) AND
                                                                                 (CP.CDSERVCPLA = CM.CDSERVFILI) AND 
                                                                                 ((CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1) = CM.DTMESCARDAPIO))
                                                       INNER JOIN PARAMPLANEJA PP ON (CP.CDFILICPLA = PP.CDFILIAL)
                                                       INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                     FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CP.CDFILICPLA)
                                        WHERE
                                           (CP.NRCONSPLAN    = V_NRCONSPLAN) AND
                                           (CP.DTCONSPLAN BETWEEN (PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD') + 1) AND LAST_DAY(PFI.DTPROCESSA))                                           
                                        GROUP BY
                                           CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)
                                        ORDER BY CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)) C_ATUAL 
                                        LEFT JOIN
                                        /*CALCULO DO CUSTO PLANEJADO DO PRIMEIRO DIA DA DATA DE PROCESSAMENTO - 1 ATE A DATA DE PROCESSAMENTO - 1*/                                        
                                       (SELECT
                                           CP.CDFILICPLA, CP.CDSERVCPLA, 
                                           SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, NVL(PP.CDTIPOCUSTOPLA, '03'), 
                                               DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CP.DTCONSPLAN),CP.DTCONSPLAN), SYSDATE) * CP.QTCONSPLAN) AS CUSTO_PLA_MES_DTPROC_MENOS_UM
                                        FROM
                                           CONSPLAN CP LEFT JOIN CARDAMES CM ON ((CP.CDFILICPLA = CM.CDFILIAL) AND
                                                                                 (CP.CDSERVCPLA = CM.CDSERVFILI) AND 
                                                                                 ((CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1) = CM.DTMESCARDAPIO))
                                                       INNER JOIN PARAMPLANEJA PP ON (CP.CDFILICPLA = PP.CDFILIAL)
                                                       INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                     FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CP.CDFILICPLA)
                                        WHERE
                                           (CP.NRCONSPLAN    = V_NRCONSPLAN) AND
                                           (CP.DTCONSPLAN BETWEEN TO_DATE('01'||TO_CHAR((PFI.DTPROCESSA  - 1), 'MM')||TO_CHAR((PFI.DTPROCESSA - 1), 'YYYY')) AND TO_DATE(PFI.DTPROCESSA - 1))                                           
                                        GROUP BY
                                           CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)
                                        ORDER BY CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)) C_PLA_MES_DTPROC_MENOS_UM ON ((C_ATUAL.CDFILICPLA = C_PLA_MES_DTPROC_MENOS_UM.CDFILICPLA) AND 
                                                                                                                                                                 (C_ATUAL.CDSERVCPLA = C_PLA_MES_DTPROC_MENOS_UM.CDSERVCPLA))
                                        /*FIM DO CALCULO DO CUSTO PLANEJADO DO PRIMEIRO DIA DA DATA DE PROCESSAMENTO - 1 ATE A DATA DE PROCESSAMENTO - 1*/                                        
                                      LEFT JOIN
                                      (SELECT
                                           CP.CDFILICPLA, CP.CDSERVCPLA,
                                           SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, NVL(PP.CDTIPOCUSTOPLA, '03'), 
                                               DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CP.DTCONSPLAN),CP.DTCONSPLAN), SYSDATE) * CP.QTCONSPLAN) AS VRCUSTO_ANT
                                        FROM
                                           CONSPLAN CP LEFT JOIN CARDAMES CM ON ((CP.CDFILICPLA = CM.CDFILIAL) AND
                                                                                 (CP.CDSERVCPLA = CM.CDSERVFILI) AND 
                                                                                 ((CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1) = CM.DTMESCARDAPIO))
                                                       INNER JOIN PARAMPLANEJA PP ON (CP.CDFILICPLA = PP.CDFILIAL)
                                                       INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                     FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CP.CDFILICPLA)
                                        WHERE
                                           (CP.NRCONSPLAN    = V_NRCONSPLAN) AND
                                           (CP.DTCONSPLAN BETWEEN ADD_MONTHS((PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD') + 1),-1) 
                                                              AND ADD_MONTHS(LAST_DAY(PFI.DTPROCESSA),-1))
                                        GROUP BY
                                           CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)
                                        ORDER BY CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)) C_ANTES ON ((C_ATUAL.CDFILICPLA = C_ANTES.CDFILICPLA) AND 
                                                                                                                                               (C_ATUAL.CDSERVCPLA = C_ANTES.CDSERVCPLA))
                                      LEFT JOIN  
                                      (SELECT
                                           CP.CDFILICPLA, CP.CDSERVCPLA,
                                           SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, NVL(PP.CDTIPOCUSTOPLA, '03'), 
                                               DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CP.DTCONSPLAN),CP.DTCONSPLAN), SYSDATE) * CP.QTCONSPLAN) AS VRCUSTO_APOS
                                        FROM
                                           CONSPLAN CP LEFT JOIN CARDAMES CM ON ((CP.CDFILICPLA = CM.CDFILIAL) AND
                                                                                 (CP.CDSERVCPLA = CM.CDSERVFILI) AND 
                                                                                 ((CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1) = CM.DTMESCARDAPIO))
                                                       INNER JOIN PARAMPLANEJA PP ON (CP.CDFILICPLA = PP.CDFILIAL)
                                                       INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                     FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CP.CDFILICPLA)
                                        WHERE
                                           (CP.NRCONSPLAN    = V_NRCONSPLAN) AND
                                           (CP.DTCONSPLAN BETWEEN ADD_MONTHS((PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD') + 1),1) 
                                                              AND ADD_MONTHS(LAST_DAY(PFI.DTPROCESSA),1))
                                        GROUP BY
                                           CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)
                                        ORDER BY CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)) C_APOS ON ((C_ATUAL.CDFILICPLA = C_APOS.CDFILICPLA) AND 
                                                                                                                                               (C_ATUAL.CDSERVCPLA = C_APOS.CDSERVCPLA))
                                      LEFT JOIN
                                      (SELECT
                                           CP.CDFILICPLA, CP.CDSERVCPLA,
                                           SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, NVL(PP.CDTIPOCUSTOPLA, '03'), 
                                               DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CP.DTCONSPLAN),CP.DTCONSPLAN), SYSDATE) * CP.QTCONSPLAN) AS VRCUSTO_POSDT
                                        FROM
                                           CONSPLAN CP LEFT JOIN CARDAMES CM ON ((CP.CDFILICPLA = CM.CDFILIAL) AND
                                                                                 (CP.CDSERVCPLA = CM.CDSERVFILI) AND 
                                                                                 ((CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1) = CM.DTMESCARDAPIO))
                                                       INNER JOIN PARAMPLANEJA PP ON (CP.CDFILICPLA = PP.CDFILIAL)
                                                       INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                     FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CP.CDFILICPLA)
                                        WHERE
                                           (CP.NRCONSPLAN    = V_NRCONSPLAN) AND
                                           (CP.DTCONSPLAN BETWEEN (PFI.DTPROCESSA) 
                                                              AND LAST_DAY(PFI.DTPROCESSA))
                                        GROUP BY
                                           CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)
                                        ORDER BY CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)) C_POSDTPROC ON ((C_POSDTPROC.CDFILICPLA = C_ATUAL.CDFILICPLA) AND 
                                                                                                                                                  (C_POSDTPROC.CDSERVCPLA = C_ATUAL.CDSERVCPLA)) 
                                        LEFT JOIN
                                        /*CUSTO PLANEJADO DA DATA DE PROCESSAMENTO MENOS UM DIA*/
                                       (SELECT
                                           CP.CDFILICPLA, CP.CDSERVCPLA, 
                                           SUM(CUSTO_PRODUTO(CP.CDFILICPLA, CP.CDPRODCPLA, NVL(PP.CDTIPOCUSTOPLA, '03'), 
                                               DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CP.DTCONSPLAN),CP.DTCONSPLAN), SYSDATE) * CP.QTCONSPLAN) AS CUSTO_PLA_DTPROC_MENOS_UM
                                        FROM
                                           CONSPLAN CP LEFT JOIN CARDAMES CM ON ((CP.CDFILICPLA = CM.CDFILIAL) AND
                                                                                 (CP.CDSERVCPLA = CM.CDSERVFILI) AND 
                                                                                 ((CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1) = CM.DTMESCARDAPIO))
                                                       INNER JOIN PARAMPLANEJA PP ON (CP.CDFILICPLA = PP.CDFILIAL)
                                                       INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                     FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CP.CDFILICPLA)
                                        WHERE
                                           (CP.NRCONSPLAN    = V_NRCONSPLAN) AND
                                           (CP.DTCONSPLAN = TO_DATE(PFI.DTPROCESSA - 1))  
                                        GROUP BY
                                           CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)
                                        ORDER BY CP.CDFILICPLA, CP.CDSERVCPLA, (CP.DTCONSPLAN - TO_CHAR(CP.DTCONSPLAN, 'DD') + 1)) C_PLA_DTPROC_MENOS_UM ON ((C_PLA_DTPROC_MENOS_UM.CDFILICPLA = C_ATUAL.CDFILICPLA) AND 
                                                                                                                                                            (C_PLA_DTPROC_MENOS_UM.CDSERVCPLA = C_ATUAL.CDSERVCPLA)) 
                                        /* FIM DO CUSTO PLANEJADO DA DATA DE PROCESSAMENTO MENOS UM DIA*/
                                  ) PU_PLAN ON ((SE.CDFILIAL = PU_PLAN.CDFILICPLA) AND 
                                                (SE.CDSERVFILI = PU_PLAN.CDSERVCPLA))
                        /*FIM CALCULA CUSTO TOTAL PLANEJADO ATUAL, DO MES ANTERIOR E DO PROXIMO MES*/ 
                        /*CALCULA CUSTO REAL DO MES ATUAL ATE A DATA DE PROCESSAMENTO E DO ANTERIOR*/
                        LEFT JOIN (SELECT CUS_MES.CDFILIAL, 
                                          CUS_MES.CDSERVFILI,                                          
                                          CUS_MES.CUSTO_REAL_MES,
                                          CUS_MES_DTPROC_MENOS_UM.CUSTO_REAL_MES_DTPROC_MENOS_UM,
                                          CUS_ANT.CUSTO_REAL_ANT,
                                          C_REAL_DTPROC_MENOS_UM.CUSTO_REAL_DTPROC_MENOS_UM                                          
                                     FROM 
                                        (SELECT
                                          CD.CDFILIAL, 
                                          CD.CDSERVFILI,
                                          ROUND(SUM(IR.QTRETIRADA * CUSTO_PRODUTO(CD.CDFILIAL, IR.CDPRODUTO, NVL(PP.CDTIPOCUSTOREA, '03'),
                                                                  DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CD.DTCARDAPIO),CD.DTCARDAPIO), SYSDATE)),5) AS CUSTO_REAL_MES
                                           FROM
                                              RETIRADA R INNER JOIN CARDADIA CD ON ((R.CDFILIAL = CD.CDFILIAL)      AND 
                                                                                    (R.NRCARDAPIO = CD.NRCARDAPIO))
                                                         INNER JOIN ITEMRETI IR ON ((IR.CDFILIAL = R.CDFILIAL)     AND 
                                                                                    (IR.NRCARDAPIO = R.NRCARDAPIO) AND 
                                                                                    (IR.NRRETIRADA = R.NRRETIRADA))
                                                         INNER JOIN ITLANCTOEST IL ON ((IL.CDFILIAL = IR.CDFIITLANEST) AND 
                                                                                       (IL.NRLANCESTQ = IR.NRLANCESTQ) AND 
                                                                                       (IL.NRSEQUITEM = IR.NRSEQUITEM))
                                                         INNER JOIN PARAMPLANEJA PP ON (PP.CDFILIAL = CD.CDFILIAL)
                                                         INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                       FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CD.CDFILIAL)
                                                         LEFT JOIN CARDAMES CM ON ((CD.CDFILIAL   =  CM.CDFILIAL)  AND 
                                                                                   (CD.CDSERVFILI = CM.CDSERVFILI) AND 
                                                                                   (CD.DTMESCARD  = CM.DTMESCARDAPIO))
                                          WHERE
                                              (CD.DTCARDAPIO   BETWEEN (PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD') + 1) 
                                                                   AND (PFI.DTPROCESSA - 1)) AND                                            
                                              IL.IDEVOLESTOQ = 'S'
                                          GROUP BY CD.CDFILIAL, CD.CDSERVFILI) CUS_MES 
                                          LEFT JOIN
                                          /*CALCULO DO CUSTO REALIZADO DO PRIMEIRO DIA DA DATA DE PROCESSAMENTO - 1 ATE A DATA DE PROCESSAMENTO - 1*/
                                          (SELECT
                                          CD.CDFILIAL, 
                                          CD.CDSERVFILI,
                                          ROUND(SUM(IR.QTRETIRADA * CUSTO_PRODUTO(CD.CDFILIAL, IR.CDPRODUTO, NVL(PP.CDTIPOCUSTOREA, '03'),
                                                                  DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CD.DTCARDAPIO),CD.DTCARDAPIO), SYSDATE)),5) AS CUSTO_REAL_MES_DTPROC_MENOS_UM
                                           FROM
                                              RETIRADA R INNER JOIN CARDADIA CD ON ((R.CDFILIAL = CD.CDFILIAL)      AND 
                                                                                    (R.NRCARDAPIO = CD.NRCARDAPIO))
                                                         INNER JOIN ITEMRETI IR ON ((IR.CDFILIAL = R.CDFILIAL)     AND 
                                                                                    (IR.NRCARDAPIO = R.NRCARDAPIO) AND 
                                                                                    (IR.NRRETIRADA = R.NRRETIRADA))
                                                         INNER JOIN ITLANCTOEST IL ON ((IL.CDFILIAL = IR.CDFIITLANEST) AND 
                                                                                       (IL.NRLANCESTQ = IR.NRLANCESTQ) AND 
                                                                                       (IL.NRSEQUITEM = IR.NRSEQUITEM))
                                                         INNER JOIN PARAMPLANEJA PP ON (PP.CDFILIAL = CD.CDFILIAL)
                                                         INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                       FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CD.CDFILIAL)
                                                         LEFT JOIN CARDAMES CM ON ((CD.CDFILIAL   =  CM.CDFILIAL)  AND 
                                                                                   (CD.CDSERVFILI = CM.CDSERVFILI) AND 
                                                                                   (CD.DTMESCARD  = CM.DTMESCARDAPIO))
                                            WHERE (CD.DTCARDAPIO BETWEEN TO_DATE('01'||TO_CHAR((PFI.DTPROCESSA  - 1), 'MM')||TO_CHAR((PFI.DTPROCESSA - 1), 'YYYY')) AND TO_DATE(PFI.DTPROCESSA - 1))  AND
                                              IL.IDEVOLESTOQ = 'S'
                                          GROUP BY CD.CDFILIAL, CD.CDSERVFILI) CUS_MES_DTPROC_MENOS_UM 
                                          /* FIM DO CALCULO DO CUSTO REALIZADO DO PRIMEIRO DIA DA DATA DE PROCESSAMENTO - 1 ATE A DATA DE PROCESSAMENTO - 1*/
                                     ON ((CUS_MES.CDFILIAL = CUS_MES_DTPROC_MENOS_UM.CDFILIAL) AND (CUS_MES.CDSERVFILI = CUS_MES_DTPROC_MENOS_UM.CDSERVFILI))     
                                     LEFT JOIN 
                                        (SELECT
                                          CD.CDFILIAL, 
                                          CD.CDSERVFILI,
                                          ROUND(SUM(IR.QTRETIRADA * CUSTO_PRODUTO(CD.CDFILIAL, IR.CDPRODUTO, NVL(PP.CDTIPOCUSTOREA, '03'),
                                                                  DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CD.DTCARDAPIO),CD.DTCARDAPIO), SYSDATE)),5) AS CUSTO_REAL_ANT
                                           FROM
                                              RETIRADA R INNER JOIN CARDADIA CD ON ((R.CDFILIAL = CD.CDFILIAL)      AND 
                                                                                    (R.NRCARDAPIO = CD.NRCARDAPIO))
                                                         INNER JOIN ITEMRETI IR ON ((IR.CDFILIAL = R.CDFILIAL)     AND 
                                                                                    (IR.NRCARDAPIO = R.NRCARDAPIO) AND 
                                                                                    (IR.NRRETIRADA = R.NRRETIRADA))
                                                         INNER JOIN ITLANCTOEST IL ON ((IL.CDFILIAL = IR.CDFIITLANEST) AND 
                                                                                       (IL.NRLANCESTQ = IR.NRLANCESTQ) AND 
                                                                                       (IL.NRSEQUITEM = IR.NRSEQUITEM))
                                                         INNER JOIN PARAMPLANEJA PP ON (PP.CDFILIAL = CD.CDFILIAL)
                                                         INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                       FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CD.CDFILIAL)
                                                         LEFT JOIN CARDAMES CM ON ((CD.CDFILIAL   =  CM.CDFILIAL)  AND 
                                                                                   (CD.CDSERVFILI = CM.CDSERVFILI) AND 
                                                                                   (CD.DTMESCARD  = CM.DTMESCARDAPIO))
                                           WHERE
                                           (CD.DTCARDAPIO   BETWEEN ADD_MONTHS((PFI.DTPROCESSA - TO_CHAR(PFI.DTPROCESSA, 'DD') + 1),-1) 
                                                                   AND ADD_MONTHS((LAST_DAY(PFI.DTPROCESSA)),-1)) AND
                                              IL.IDEVOLESTOQ = 'S'
                                           GROUP BY
                                              CD.CDFILIAL, 
                                              CD.CDSERVFILI) CUS_ANT 
                                     ON ((CUS_MES.CDFILIAL = CUS_ANT.CDFILIAL) AND (CUS_MES.CDSERVFILI = CUS_ANT.CDSERVFILI))
                                     LEFT JOIN
                                     /*CALCULO DO CUSTO REALIZADO DA DATA DE PROCESSAMENTO MENOS UM DIA*/
                                        (SELECT
                                          CD.CDFILIAL, 
                                          CD.CDSERVFILI,
                                          ROUND(SUM(IR.QTRETIRADA * CUSTO_PRODUTO(CD.CDFILIAL, IR.CDPRODUTO, NVL(PP.CDTIPOCUSTOREA, '03'),
                                                                  DECODE(PP.IDEXIBECUSTO, 'R', NVL(CM.DTREFECUSTMES, CD.DTCARDAPIO),CD.DTCARDAPIO), SYSDATE)),5) AS CUSTO_REAL_DTPROC_MENOS_UM
                                           FROM
                                              RETIRADA R INNER JOIN CARDADIA CD ON ((R.CDFILIAL = CD.CDFILIAL)      AND 
                                                                                    (R.NRCARDAPIO = CD.NRCARDAPIO))
                                                         INNER JOIN ITEMRETI IR ON ((IR.CDFILIAL = R.CDFILIAL)     AND 
                                                                                    (IR.NRCARDAPIO = R.NRCARDAPIO) AND 
                                                                                    (IR.NRRETIRADA = R.NRRETIRADA))
                                                         INNER JOIN ITLANCTOEST IL ON ((IL.CDFILIAL = IR.CDFIITLANEST) AND 
                                                                                       (IL.NRLANCESTQ = IR.NRLANCESTQ) AND 
                                                                                       (IL.NRSEQUITEM = IR.NRSEQUITEM))
                                                         INNER JOIN PARAMPLANEJA PP ON (PP.CDFILIAL = CD.CDFILIAL)
                                                         INNER JOIN (SELECT PF.CDFILIAL, TRUNC(DECODE(PG.IDUTILDTPROC, 'N', SYSDATE, NVL(PF.DTPROCESSA,SYSDATE))) DTPROCESSA 
                                                                       FROM PARAMGERAL PG, PARAMFILIAL PF WHERE PG.CDPARGERAL = '0001') PFI ON (PFI.CDFILIAL = CD.CDFILIAL)
                                                         LEFT JOIN CARDAMES CM ON ((CD.CDFILIAL   =  CM.CDFILIAL)  AND 
                                                                                   (CD.CDSERVFILI = CM.CDSERVFILI) AND 
                                                                                   (CD.DTMESCARD  = CM.DTMESCARDAPIO))
                                            WHERE (CD.DTCARDAPIO = TO_DATE(PFI.DTPROCESSA - 1))  AND
                                              IL.IDEVOLESTOQ = 'S'
                                          GROUP BY CD.CDFILIAL, CD.CDSERVFILI) C_REAL_DTPROC_MENOS_UM                                     
                                     ON ((CUS_MES.CDFILIAL = C_REAL_DTPROC_MENOS_UM.CDFILIAL) AND (CUS_MES.CDSERVFILI = C_REAL_DTPROC_MENOS_UM.CDSERVFILI))
                                     /*FIM DO CALCULO DO CUSTO REALIZADO DA DATA DE PROCESSAMENTO MENOS UM DIA*/
                                  )CUSTO_REAL ON ((SE.CDFILIAL = CUSTO_REAL.CDFILIAL) AND
                                                  (SE.CDSERVFILI = CUSTO_REAL.CDSERVFILI))
                        /*FIM CALCULA CUSTO REAL DO MES ATUAL ATE A DATA DE PROCESSAMENTO E DO ANTERIOR*/
       ORDER BY SE.CDFILIAL, DTPROCESSA, SE.CDSERVFILI) PLANEJADORA);
   DELETE FROM CONSPLAN
    WHERE NRCONSPLAN = V_NRCONSPLAN;
   COMMIT;
END;
/    
-- ===============================================================================
-- JOB_NAME: JOB_ETL_PLANEJADORA
-- ===============================================================================
DECLARE
   V_JOB NUMBER;
   L_JOB_EXISTS NUMBER;
BEGIN
   FOR C_JOBS IN
      (SELECT JOB
       FROM USER_JOBS
       WHERE WHAT LIKE '%ETL_PLANEJADORA%'
   ) LOOP
      DBMS_JOB.REMOVE(C_JOBS.JOB);
   END LOOP;

   SELECT COUNT(*) INTO L_JOB_EXISTS
   FROM USER_SCHEDULER_JOBS
   WHERE JOB_NAME = 'JOB_ETL_PLANEJADORA';

   IF L_JOB_EXISTS = 1 THEN
       DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_ETL_PLANEJADORA');
   END IF;

   BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
         JOB_NAME => 'JOB_ETL_PLANEJADORA',
         JOB_TYPE => 'PLSQL_BLOCK',
         JOB_ACTION => 'BEGIN ETL_PLANEJADORA(); COMMIT; END;',
         START_DATE => SYSDATE + 1,
         REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;',
         JOB_CLASS => '"DEFAULT_JOB_CLASS"',
         ENABLED => TRUE,
         AUTO_DROP => FALSE,
         COMMENTS => 'Job criado para planejadora.');
   END;
  COMMIT;   
END;
/