/* TEKNISA

Nome: 7602_PMESTPO_PREVISAO_CONSUMO_PME.SQL

Descri��o:
C�lculo da Previs�o de Consumo atrav�s da stored procedure CONSUMO_PLANEJADO e
grava��o na tabela PREVCONS.

P_CDFILIAL     - Codigo da filial
P_SERVICOS     - String contendo sequ�ncia de servi�os a serem calculados
P_DTINICIAL    - Data inicial de c�lculo
P_DTFINAL      - Data final de c�lculo
P_PEACRESCIMO  - Percentual de acr�scimo
P_CDPRODUTOINI - C�digo do produto inicial (CDARVPROD)
P_CDPRODUTOFIN - C�digo do produto final   (CDARVPROD)
P_CDOPERADOR   - C�digo do operador que solicitou o c�lculo
P_DTHRCALCPC   - Data/hora do c�lculo
P_NRCONSPLAN   - N�mero sequencial a ser utilizado para grava��o dos dados na
                 tabela CONSPLAN (tabela de trabalho)
P_STDTINICIAL  - Lista de data inicial (em string) por servi�o
P_STDTFINAL    - Lista de data final (em string) por servi�o

Observa��o: o formato das strings de data inicial e de data final deve ser
"_YYYYMMDD_YYYYMMDD_YYYYMMDD_"
"_YYYYMMDD_YYYYMMDD_YYYYMMDD_"


Para aplicar e debugar
-- @C:\Web_409\Sql\End_Conv\Oracle\7602_PMESTPO_PREVISAO_CONSUMO_PME.SQL
-- START H:\WORKDIR\Ederson\BACKUP\MANUTEN��O\7130_PLASTP_PREVISAO_DE_CONSUMO_PLA.SQL;
-- SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('xx');

Cria��o:
18/05/2005 - Luiz Ot�vio de Souza

*/

CREATE OR REPLACE PROCEDURE PREVISAO_CONSUMO_PME
  (P_CDFILCENTPME IN VARCHAR2,
   P_CDTIPOCARD   IN VARCHAR2,   /*LISTA*/
   P_CDFILIAL     IN VARCHAR2,   /*LISTA*/
   P_CDSERVFILI   IN VARCHAR2,   /*LISTA*/
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_PEACRESCIMO  IN NUMBER,
   P_CDPRODUTOINI IN VARCHAR2,
   P_CDPRODUTOFIN IN OUT VARCHAR2,
   P_CDOPERADOR   IN VARCHAR2,
   P_DTHRCALCPC   IN DATE,
   P_NRCONSPLAN   IN VARCHAR2,
   P_IDARREDOND   IN VARCHAR2) IS

   V_CDARVINI     VARCHAR2(20);
   V_CDARVFIN     VARCHAR2(20);

   V_IDCOMCUSPERC VARCHAR2(01);
   V_IDCALPADCMP  VARCHAR2(01);
   V_CDTPCUSTCMP  VARCHAR2(02);
   V_INTPCUSTCMP  NUMBER;
   V_VRINDCALCMP  NUMBER;
   V_QTCOMES      NUMBER;
   V_VRCUSTO      NUMBER;
   V_VRCUSTOPERC  NUMBER;
   V_VRCUSTOPADR  NUMBER;
   V_CDTCACONSFIX VARCHAR2(10);
   V_CDTCACONSPER VARCHAR2(10);

   /* VARI�VEIS BIND */
   wF CHAR(01) := 'F';
   wP CHAR(01) := 'P';
   wC CHAR(01) := 'C';
BEGIN
   V_CDARVINI := P_CDPRODUTOINI;
   V_CDARVFIN := RPAD(P_CDPRODUTOFIN, 10, 'z');


   /* - APAGA OS REGISTROS ANTERIORES DA TABELA PREVCONSPME - */
   DELETE FROM PREVCONSPME
   WHERE
      (CDFILCENTPME = P_CDFILCENTPME) AND
      (INSTR(NVL(P_CDTIPOCARD, CDTIPOCARD), CDTIPOCARD) > 0) AND
      (INSTR(NVL(P_CDFILIAL,   CDFILIAL  ), CDFILIAL  ) > 0) AND
      (INSTR(NVL(P_CDSERVFILI, CDSERVFILI), CDSERVFILI) > 0) AND
      (DTPREVCONPME BETWEEN P_DTINICIAL AND P_DTFINAL ) AND
      (EXISTS (SELECT CDPRODUTO
                      FROM PRODUTO
                      WHERE CDARVPROD BETWEEN V_CDARVINI AND V_CDARVFIN
                        AND PREVCONSPME.CDPRODUTO = PRODUTO.CDPRODUTO));

   COMMIT;

   /* CALCULA OS DADOS ATRAV�S DA STORED PROCEDURE CONSUMO PLANEJADO */

   CONSUMO_PLANEJADO_PME(P_CDFILCENTPME,
                         P_CDTIPOCARD,
                         P_CDFILIAL,
                         P_CDSERVFILI,
                         P_DTINICIAL,
                         P_DTFINAL,
                         'C',   /* GRAVA��O NA TABELA CONSPLAN */
                         'CFP', /* CARD�PIO, CONSUMO FIXO E PERCAPITA */
                         'N',   /* N�O SEPARAR POR SETOR */
                         P_PEACRESCIMO,
                         P_IDARREDOND,
                         P_NRCONSPLAN,
                         V_CDARVINI,
                         V_CDARVFIN,
                         P_CDOPERADOR);

   COMMIT;

   SELECT MAX(IDCALPADCMP), MAX(CDTPCUSTCMP), MAX(VRINDCALCMP)
   INTO   V_IDCALPADCMP, V_CDTPCUSTCMP, V_VRINDCALCMP
   FROM PARAMPLANEJA
   WHERE (CDFILIAL = P_CDFILCENTPME);

   V_INTPCUSTCMP := TO_NUMBER(V_CDTPCUSTCMP);

   SELECT MAX(IDCOMCUSPERC)
   INTO V_IDCOMCUSPERC
   FROM PARAMFILIAL
   WHERE (CDFILIAL = P_CDFILCENTPME);

   IF (V_IDCALPADCMP = 'S') THEN
      FOR CUR_SERVICO IN
         (SELECT C.CDFILICPLA AS CDFILIAL,
                 C.CDSERVCPLA AS CDSERVFILI,
                 C.CDTPCARDPME AS CDTIPOCARD
          FROM CONSPLAN C, FILIAL F
          WHERE (C.NRCONSPLAN   = P_NRCONSPLAN)   AND
                (F.CDFILCENTPME = P_CDFILCENTPME) AND
                (C.CDFILICPLA   = F.CDFILIAL)
          GROUP BY C.CDFILICPLA, C.CDSERVCPLA, C.CDTPCARDPME)
      LOOP
         V_VRCUSTO := 0.0;
         SELECT
            SUM(CUSTO_PRODUTO(P_CDFILCENTPME, CONSPLAN.CDPRODCPLA, V_INTPCUSTCMP, CONSPLAN.DTCONSPLAN, SYSDATE) * CONSPLAN.QTCONSPLAN)
         INTO V_VRCUSTO
         FROM CONSPLAN
         WHERE (CONSPLAN.NRCONSPLAN  = P_NRCONSPLAN)           AND
               (CONSPLAN.CDFILICPLA  = CUR_SERVICO.CDFILIAL)   AND
               (CONSPLAN.CDSERVCPLA  = CUR_SERVICO.CDSERVFILI) AND
               (CONSPLAN.CDTPCARDPME = CUR_SERVICO.CDTIPOCARD);

         IF (V_VRCUSTO IS NULL) THEN
            V_VRCUSTO := 0.0;
         END IF;

         SELECT SUM(NVL(C.VRCUSPADTCAR * V_VRINDCALCMP, 0.0))
         INTO V_VRCUSTOPADR
         FROM CUSTPADTCARD C,
              (SELECT CDFILCENTPME, CDTIPOCARD, CDSERVFILI, MAX(DTREFCUSTCAR) AS DTREFCUSTCAR
               FROM CUSTPADTCARD
               WHERE (CDFILCENTPME = P_CDFILCENTPME)         AND
                     (CDTIPOCARD   = CUR_SERVICO.CDTIPOCARD) AND
                     (CDSERVFILI   = CUR_SERVICO.CDSERVFILI) AND
                     (DTREFCUSTCAR <= P_DTFINAL)
               GROUP BY CDFILCENTPME, CDTIPOCARD, CDSERVFILI) D
         WHERE (C.CDFILCENTPME = D.CDFILCENTPME) AND
               (C.CDTIPOCARD   = D.CDTIPOCARD)   AND
               (C.CDSERVFILI   = D.CDSERVFILI)   AND
               (C.DTREFCUSTCAR = D.DTREFCUSTCAR);

         SELECT SUM(DECODE(V_IDCOMCUSPERC,
                           wC, QTCLIESTCPME,
                               QTCOMESTCPME))
         INTO V_QTCOMES
         FROM CARDAPIOPME C, CARDIAPME CD
         WHERE (C.CDFILCENTPME = P_CDFILCENTPME)              AND
               (C.CDTIPOCARD   = CUR_SERVICO.CDTIPOCARD)      AND
               (CD.CDFILIAL    = CUR_SERVICO.CDFILIAL)        AND
               (C.CDFILCENTPME = CD.CDFILCENTPME)             AND
               (C.NRCARDPME    = CD.NRCARDPME)                AND
               (CD.CDFILIAL    <> CD.CDFILCENTPME)            AND
               (CD.CDSERVFILI  = CUR_SERVICO.CDSERVFILI)      AND
               (CD.DTCARDIAPME BETWEEN P_DTINICIAL AND P_DTFINAL);

         V_VRCUSTOPERC := 0;
         IF (V_QTCOMES <> 0.0) AND (V_VRCUSTO <> 0.0) THEN
            V_VRCUSTOPERC := (V_VRCUSTO/V_QTCOMES);
         END IF;

         IF (V_VRCUSTOPADR > V_VRCUSTOPERC) THEN
            /* INSERE NA TABELA PREVCONS AS INFORMA��ES CALCULADAS NA CONSPLAN */
            INSERT INTO PREVCONSPME
               (CDFILCENTPME, CDTIPOCARD, CDFILIAL, CDSERVFILI, CDPRODUTO, DTPREVCONPME, QTPREVCONPME)
               (SELECT
                  P_CDFILCENTPME, CDTPCARDPME, CDFILICPLA, CDSERVCPLA, CDPRODCPLA, DTCONSPLAN, SUM(QTCONSPLAN)
                FROM CONSPLAN
                WHERE(NRCONSPLAN  = P_NRCONSPLAN)           AND
                     (CDFILICPLA  = CUR_SERVICO.CDFILIAL)   AND
                     (CDTPCARDPME = CUR_SERVICO.CDTIPOCARD) AND
                     (CDSERVCPLA  = CUR_SERVICO.CDSERVFILI)
                GROUP BY 1, CDTPCARDPME, CDFILICPLA, CDSERVCPLA, CDPRODCPLA, DTCONSPLAN);
         END IF;
      END LOOP;
   ELSE
      INSERT INTO PREVCONSPME
        (CDFILCENTPME, CDTIPOCARD, CDFILIAL, CDSERVFILI, CDPRODUTO, DTPREVCONPME, QTPREVCONPME)
        (SELECT
            P_CDFILCENTPME, CDTPCARDPME, CDFILICPLA,
            CDSERVCPLA, CDPRODCPLA, DTCONSPLAN, SUM(QTCONSPLAN)
         FROM CONSPLAN
         WHERE
            (NRCONSPLAN   = P_NRCONSPLAN  ) AND
            (CDFILCENTPME = P_CDFILCENTPME) AND
            (INSTR(NVL(P_CDTIPOCARD, CDTPCARDPME), CDTPCARDPME) > 0) AND
            (INSTR(NVL(P_CDFILIAL,   CDFILICPLA ), CDFILICPLA ) > 0) AND
            (INSTR(NVL(P_CDSERVFILI, CDSERVCPLA ), CDSERVCPLA ) > 0) AND
            (DTCONSPLAN BETWEEN P_DTINICIAL AND P_DTFINAL)
         GROUP BY
            1, CDTPCARDPME, CDFILICPLA,
            CDSERVCPLA, CDPRODCPLA, DTCONSPLAN);
   END IF;

   COMMIT;

      DELETE FROM CONSPLAN
      WHERE
         NRCONSPLAN   = P_NRCONSPLAN AND
         CDFILCENTPME = P_CDFILCENTPME ;

   COMMIT;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('PREVISAO_CONSUMO_PME','6.49.022'); END;
/

