create or replace
PROCEDURE "ETL_PLAFUTURO" (P_DTINI  IN DATE,
                          P_DTFINA IN DATE)
IS
   V_COUNTER       NUMBER       NOT NULL := 0;
   V_COUNTERII     NUMBER       NOT  NULL := 0;
   V_COUNTSERVICO  NUMBER       NOT NULL := 0;
   V_TIPOFATU      VARCHAR2(30);
   V_DTFIM         DATE;
   V_DTINI         DATE;
   V_DTREFECUS      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_NMTIPOCUSTO1 := ' ';
   V_NMTIPOCUSTO2 := ' ';
   V_NMTIPOCUSTO3 := ' ';
  SELECT IDDATACUSTOBI INTO V_DATACUSTOBI
   FROM PARAMGERAL;
-----------------------------INICIO DA ROTINA DE DELE��O DE DADOS------------------------------------
   DELETE FROM DM_PLAFUTURO;
   COMMIT;
----------------------------------------FIM DA DELE��O DOS DADOS--------------------------------------
   V_DTINI := ADD_MONTHS(LAST_DAY(P_DTINI) + 1, -1);
   
   FOR C_NRTPCUS IN --> Seleciona 3 tipos de custo parametrizados
      (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_DATAFINAL  :=   V_DTFIM;

        FOR C_ETL IN
         (SELECT
                 FL.CDFILIAL        ,FL.NMFILIAL   ,FL.CDAREAGERE ,NVL(FL.DTINICIALBI, TO_DATE('01/01/2023','DD/MM/YYYY')) DTINICIALBI,
                 FL.CDSUPERVISOR    ,FL.CDGOPERAC  ,FL.CDEMPRESA  ,EM.NMEMPRESA,
                 FL.CDSEGMER        ,P.DTPROCESSA  ,P.IDCOMCUSPERC, P.CDTIPOFATUR
           FROM
                 FILIAL FL, PARAMFILIAL P, EMPRESA EM
           WHERE P.CDFILIAL = FL.CDFILIAL
             AND EM.CDEMPRESA = FL.CDEMPRESA
             AND FL.IDFUNCAOFILI IS NULL
            -- AND FL.DTINICIALBI  IS NOT NULL
         )
    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);
     /*PREENCHE OS DADOS NECESS�RIOS PARA CUSTO PLANEJADO - TABELA CONSPLAN*/
            CONSUMO_PLANEJADO_PLA(
                                  C_ETL.CDFILIAL,
                                  'T',
                                  V_DTINI,
                                  V_DATAFINAL,
                                  'T',
                                  'CFP',
                                  'N',
                                   0,
                                  'N',
                                   V_CDSEQUENCIAL,
                                  '0',
                                  'zzzzzzzzzz',
                                  '', --> Nenhum operador ser� necess�rio aqui
                                  '_'||TO_CHAR(V_DTINI, 'YYYYMMDD')||'_',
                                  '_'||TO_CHAR(V_DATAFINAL, 'YYYYMMDD')||'_'
                                  );


            FOR C_SERVICO IN             /*DADOS EM N�VEL DE SERVI�O*/
               (SELECT SE.CDSERVFILI, SE.NMSERVICO, SE.CDGRUPSERV,  SE.IDSERVPRINC,
                      SUM(DECODE(C_ETL.IDCOMCUSPERC, 'C', CA.QTCLIESTICAR, CA.QTCOMESTICAR) ) AS QTCOMESTICAR,
                      SUM(DECODE(C_ETL.IDCOMCUSPERC, 'C', CA.QTCLIEFETCAR, CA.QTCOMEFETCAR) ) 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
               ) LOOP

               --Verifica Data do �ltimo COMENSAIS EFETIVOS para o Servi�o--
               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;

                 V_DTREFECUS := ADD_MONTHS(LAST_DAY(V_DTINI) + 1, -1);
                  BEGIN
                     SELECT VRCUSTPADR
                     INTO   V_VRCUSTPADR
                     FROM   CUSTPADR
                     WHERE  CDFILIAL     = C_ETL.CDFILIAL
                       AND  CDSERVFILI   = C_SERVICO.CDSERVFILI
                       AND  DTREFCUSPADR = V_DTREFECUS;
                     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;

         /*Pre�o de Venda - Vem do �ltimo valor do hist�rico no m�dulo de faturamento*/
                  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);
                      EXCEPTION
                         WHEN OTHERS THEN
                           V_VRPRECVENDA := V_ZERO;
                      END;
                  END IF;
                  /*DADOS EM N�VEL DE PRODUTO*/
                  FOR C_PRODUTO IN
                     (
                     SELECT
                         PR.CDFILIAL,
                         PR.CDSERVFILI,
                         SUM(PR.CUSTOPLAN1) AS CUSTOPLAN1,
                         SUM(PR.CUSTOPLAN2) AS CUSTOPLAN2,
                         SUM(PR.CUSTOPLAN3) AS CUSTOPLAN3,
                         GPS.CDGRUPSERV,
                         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,
                            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
                               (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)
                                )
                          GROUP BY
                            CDFILIAL,
                            CDSERVFILI,
                            DTCARDAPIO,
                            CDPRODUTO ) PR
                      WHERE
                         FI.CDFILIAL   = PR.CDFILIAL   AND
                         SE.CDFILIAL   = PR.CDFILIAL   AND
                         SE.CDSERVFILI = PR.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.CDGRUPSERV,
                         SE.IDSERVPRINC
                      ) LOOP
                        --------------------------FATOS CALCULADOS----------------------------
                              IF C_SERVICO.QTCOMESTICAR > 0 THEN
                                 /* CUSTO PLANEJADO TOTAL - (Custo Planejado*Comensais Estimados) */
                                 V_VRCUSTPLANTOTAL1 := (C_PRODUTO.CUSTOPLAN1 * C_SERVICO.QTCOMESTICAR);
                                 V_VRCUSTPLANTOTAL2 := (C_PRODUTO.CUSTOPLAN2 * C_SERVICO.QTCOMESTICAR);
                                 V_VRCUSTPLANTOTAL3 := (C_PRODUTO.CUSTOPLAN3 * C_SERVICO.QTCOMESTICAR);
                              END IF;

                 CASE C_ETL.CDTIPOFATUR
                 WHEN '001' THEN V_TIPOFATU := 'Gest�o Direta';
                 WHEN '002' THEN V_TIPOFATU := 'Gest�o Mista';
                 WHEN '003' THEN V_TIPOFATU := 'Mandato';
                 WHEN '004' THEN V_TIPOFATU := 'Refei��es e Servi�os';
                 WHEN '005' THEN V_TIPOFATU := 'Taxa Fixa';
                 WHEN '006' THEN V_TIPOFATU := 'Gest�o Hospitalar';
                 WHEN '007' THEN V_TIPOFATU := 'Produtos e Servi�os';
                 WHEN '008' THEN V_TIPOFATU := 'Gest�o Laser';
                 WHEN '009' THEN V_TIPOFATU := 'Gest�o Merenda';
               ELSE V_TIPOFATU := 'Sem Tipo Faturamento';
              END CASE;


             INSERT INTO DM_PLAFUTURO
                (CDFILIAL, CDEMPRESA, CDAREAGERE,
                 CDSUPERVISOR, CDGOPERAC, CDGRUPSERV, DSTIPOFATU,
                 NMSERVICO, CDANOBI, CDMESBI,
                 DSDTPROCFILI, QTCOMEPLA, VRCUSTOPAD,
                 VRFATESTIM, VRMETATOT,
                 VRCUSTOPLA1, VRCUSTOPLA2, VRCUSTOPLA3,VRCUSVNDSERV)
             VALUES
                (C_ETL.CDFILIAL    , C_ETL.CDEMPRESA  , C_ETL.CDAREAGERE ,
                C_ETL.CDSUPERVISOR, C_ETL.CDGOPERAC   ,  C_SERVICO.CDGRUPSERV, V_TIPOFATU,
                C_SERVICO.NMSERVICO, V_ANO, V_MES, --> Nome do servi�o, m�s, ano
                TO_CHAR(C_ETL.DTPROCESSA, 'DD/MM/YYYY'), C_SERVICO.QTCOMESTICAR, V_VRCUSTPADR,  --> Data de processamento, Comensais estimados, Valor do custo padr�o

                (V_VRPRECVENDA *  C_SERVICO.QTCOMESTICAR),

                (V_VRCUSTPADR *   C_SERVICO.QTCOMESTICAR),
                C_PRODUTO.CUSTOPLAN1, C_PRODUTO.CUSTOPLAN2, C_PRODUTO.CUSTOPLAN3, --> Total Custo Planejado(Tipo de custo 1�/2�/3� Parametrizado no CVI)
                V_VRPRECVENDA); --> FAI
                END LOOP; --Fim do loop por produto
            END LOOP;   -- fim do loop por servico
            DELETE FROM CONSPLAN
             WHERE NRCONSPLAN = V_CDSEQUENCIAL;
         END IF;        -- Final da compara��o com data de BI
         COMMIT;        -- Commit para cada Filial
      END LOOP;         -- fim do loop por Filial
      COMMIT;           -- Commit para cada m�s

   V_DTINI := (LAST_DAY(V_DTINI) + 1);
   END LOOP;            -- loop por m�s
END;
/
