/* Sistemas Integrados TECNISA
   Planejamento Alimenta��o Industrial

Nome: PREVISAO_DE_CONSUMO_PLA

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:\WIN_PRJ\SQL\ORACLE\7130_PLASTP_PREVISAO_DE_CONSUMO_PLA.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:
20/07/98 - Ederson

*/

create or replace PROCEDURE PREVISAO_DE_CONSUMO_PLA 
  (P_CDFILIAL     IN VARCHAR2, 
   P_SERVICOS     IN CLOB, 
   P_DTINICIAL    IN DATE, 
   P_DTFINAL      IN DATE, 
   P_PEACRESCIMO  IN NUMBER, 
   P_CDPRODUTOINI IN VARCHAR2, 
   P_CDPRODUTOFIN IN VARCHAR2, 
   P_CDOPERADOR   IN VARCHAR2, 
   P_DTHRCALCPC   IN DATE, 
   P_NRCONSPLAN   IN VARCHAR2, 
   P_IDARREDOND   IN VARCHAR2, 
   P_STDTINICIAL  IN VARCHAR2 DEFAULT NULL, 
   P_STDTFINAL    IN VARCHAR2 DEFAULT NULL) IS 
 
   V_DTINICIAL    DATE; 
   V_DTFINAL      DATE; 
   V_COUNTSERVICO NUMBER; 
   V_CDARVINI     VARCHAR2(20); 
   V_CDARVFIN     VARCHAR2(20); 
   V_SERVICOS     CLOB; 
 
   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_CDFILIAL     VARCHAR2(04); 
   V_CDSERVFILI   VARCHAR2(05); 
   V_IDUTCARDPADR VARCHAR2(01); 
   V_CDPRODUTOINI VARCHAR2(10); 
   V_CDPRODUTOFIN VARCHAR2(10); 
   V_CDSERVDEPEND VARCHAR2(05);
   V_TODOS_SERV VARCHAR2(1);
BEGIN
   /*Comando criado por que campos do tipo CLOB não podem ser comparados a valor em consultas SQL*/
   IF P_SERVICOS = 'T' THEN
      V_TODOS_SERV := 'T';
   END IF;
   
   V_SERVICOS := NULL; 
   V_CDARVINI := P_CDPRODUTOINI; 
   V_CDARVFIN := RPAD(P_CDPRODUTOFIN, 20, 'z'); 
 
   SELECT MAX(IDUTCARDPADR) 
      INTO V_IDUTCARDPADR 
   FROM PARAMGERAL; 
 
   IF (V_IDUTCARDPADR = 'S') THEN 
      SELECT MAX(CDSERVFILI) 
        INTO V_CDSERVDEPEND 
        FROM SERVICO 
       WHERE CDFILIAL = P_CDFILIAL 
         AND IDCARDDESCENTR = 'S'; 
 
       IF V_CDSERVDEPEND IS NOT NULL THEN 
          V_IDUTCARDPADR := 'D'; 
       END IF; 
   END IF; 
 
   IF (V_IDUTCARDPADR = 'S') THEN 
      SELECT MAX(CDFILIAL) 
      INTO   V_CDFILIAL 
      FROM   FILIAL 
      WHERE  IDFILCENTPME = 'S' AND 
             CDFILIAL = P_CDFILIAL AND 
             CDFILCENTPME IS NULL; 
      IF V_CDFILIAL IS NOT NULL THEN 
         RETURN; 
      END IF; 
 
      SELECT MIN(CDSERVFILI) 
      INTO   V_CDSERVFILI 
      FROM 
         (SELECT 
             S.CDSERVFILI, 
             NVL(C.IDCARDLIBAGRE, 'N') AS IDCARDLIBAGRE 
          FROM 
             SERVICO S, 
             CARDAMES C 
          WHERE 
             S.CDFILIAL = P_CDFILIAL AND 
             ((INSTR(P_SERVICOS, S.CDSERVFILI) <> 0) OR (V_TODOS_SERV = 'T')) AND 
             S.CDFILIAL     = C.CDFILIAL(+)   AND 
             S.CDSERVFILI   = C.CDSERVFILI(+) AND 
             (C.DTMESCARDAPIO(+) BETWEEN P_DTINICIAL AND P_DTFINAL) 
             AND IDTIPOSERV = 'N') 
      WHERE  IDCARDLIBAGRE = 'N'; 
      IF (V_CDSERVFILI IS NOT NULL) THEN 
         Raise_Application_Error(-20502, 'Cardápio não liberado para produção. Serviço: '||V_CDSERVFILI, False); 
      END IF; 
   END IF; 
 
   SELECT MAX(CDPRODUTO) INTO V_CDPRODUTOINI FROM PRODUTO WHERE CDARVPROD = P_CDPRODUTOINI; 
   SELECT MAX(CDPRODUTO) INTO V_CDPRODUTOFIN FROM PRODUTO WHERE CDARVPROD = P_CDPRODUTOFIN; 
 
 
   V_COUNTSERVICO := 2; 
   FOR CUR_SERVICO IN(
      SELECT SE.CDSERVFILI
      FROM SERVICO SE
      WHERE
           (SE.CDFILIAL   = P_CDFILIAL) AND
           (SE.IDSERVATIV = 'S')        AND
           (SE.IDSERVTRAN = 'N') AND
           ((INSTR(P_SERVICOS, SE.CDSERVFILI) > 0) OR V_TODOS_SERV = 'T')

      UNION
      SELECT SE.CDSERVFILI
      FROM SERVICO SE, FILIAL FI
      WHERE
           (FI.CDFILIAL   = P_CDFILIAL) AND
           (FI.CDFILCENTPME IS NOT NULL) AND
           (FI.IDFILCENTPME = 'N') AND
           (SE.CDFILIAL = FI.CDFILIAL) AND
           (SE.IDSERVATIV = 'S')        AND
           (SE.IDSERVTRAN = 'S') AND
           ((INSTR(P_SERVICOS, SE.CDSERVFILI) > 0) OR V_TODOS_SERV = 'T')
            AND EXISTS(SELECT F.CDFILIAL
                       FROM FILIAL F
                       WHERE F.CDFILIAL = FI.CDFILCENTPME
                       AND F.IDTPFILIALCENT <> 'C')
      UNION
      SELECT SE.CDSERVFILI
      FROM SERVICO SE, FILIAL FI
      WHERE
           (FI.CDFILIAL   = P_CDFILIAL) AND
           (FI.CDFILCENTPME IS NULL) AND
           (FI.IDFILCENTPME = 'N') AND
           (SE.CDFILIAL = FI.CDFILIAL) AND
           (SE.IDSERVATIV = 'S')        AND
           (SE.IDSERVTRAN = 'S') AND
           ((INSTR(P_SERVICOS, SE.CDSERVFILI) > 0) OR V_TODOS_SERV = 'T')

      ORDER BY CDSERVFILI
   )
   LOOP
      IF  V_SERVICOS IS NULL THEN
          V_SERVICOS :=  CUR_SERVICO.CDSERVFILI || '_' ;
      ELSE
          V_SERVICOS := V_SERVICOS || CUR_SERVICO.CDSERVFILI || '_' ;
      END IF;
      IF P_STDTINICIAL IS NULL THEN
         V_DTINICIAL    := P_DTINICIAL;
         V_DTFINAL      := P_DTFINAL;
      ELSE
         V_DTINICIAL    := TO_DATE(SUBSTR(P_STDTINICIAL, V_COUNTSERVICO, 8), 'YYYYMMDD');
         V_DTFINAL      := TO_DATE(SUBSTR(P_STDTFINAL, V_COUNTSERVICO, 8), 'YYYYMMDD');
         V_COUNTSERVICO := V_COUNTSERVICO + 9;
      END IF;

      INSERT INTO LOGPREVCONS
         (CDFILIAL,
          CDOPERADOR,
          DTHRCALCPC,
          NRCONSPLANPC,
          CDSERVFILI,
          DTINICIALPC,
          DTFINALPC,
          CDPRODINICPC,
          CDPRODFINAPC,
          PEACRESCIMPC)
      VALUES
         (P_CDFILIAL,
          P_CDOPERADOR,
          P_DTHRCALCPC,
          P_NRCONSPLAN,
          CUR_SERVICO.CDSERVFILI,
          V_DTINICIAL,
          V_DTFINAL,
          V_CDPRODUTOINI,
          V_CDPRODUTOFIN,
          P_PEACRESCIMO);
 
 
      DELETE FROM PREVCONS 
      WHERE 
         (CDFILIAL   = P_CDFILIAL)                      AND 
         (CDSERVFILI = CUR_SERVICO.CDSERVFILI)          AND 
         (DTPREVCONS BETWEEN V_DTINICIAL AND V_DTFINAL) AND 
         (EXISTS (SELECT CDPRODUTO 
                    FROM PRODUTO 
                   WHERE CDARVPROD BETWEEN V_CDARVINI AND V_CDARVFIN 
                           AND PRODUTO.CDPRODUTO = PREVCONS.CDPRODUTO)); 
   END LOOP; 
 
   COMMIT; 
 
   IF(V_SERVICOS IS NOT NULL) THEN 
 
       CONSUMO_PLANEJADO_PLA(P_CDFILIAL, 
                             V_SERVICOS, 
                             P_DTINICIAL, 
                             P_DTFINAL, 
                             'C', 
                             'CFP', 
                             'N', 
                             P_PEACRESCIMO, 
                             P_IDARREDOND, 
                             P_NRCONSPLAN, 
                             V_CDARVINI, 
                             V_CDARVFIN, 
                             P_CDOPERADOR, 
                             P_STDTINICIAL, 
                             P_STDTFINAL); 
     
       COMMIT; 
     
       SELECT MAX(IDCALPADCMP), MAX(CDTPCUSTCMP), MAX(VRINDCALCMP) INTO 
              V_IDCALPADCMP, V_CDTPCUSTCMP, V_VRINDCALCMP 
       FROM PARAMPLANEJA 
       WHERE (CDFILIAL = P_CDFILIAL); 
     
       V_INTPCUSTCMP := TO_NUMBER(V_CDTPCUSTCMP); 
     
       SELECT MAX(IDCOMCUSPERC) INTO V_IDCOMCUSPERC 
       FROM PARAMFILIAL 
       WHERE (CDFILIAL = P_CDFILIAL); 
     
       IF (V_IDCALPADCMP = 'S') THEN 
          FOR CUR_SERVICO IN 
             (SELECT CDFILICPLA AS CDFILIAL, 
                     CDSERVCPLA AS CDSERVFILI 
              FROM CONSPLAN 
              WHERE 
                 (CDFILICPLA = P_CDFILIAL) AND 
                 (NRCONSPLAN = P_NRCONSPLAN) 
              GROUP BY CDFILICPLA, 
                       CDSERVCPLA 
          ) LOOP 
             V_VRCUSTO := 0.0; 
             SELECT 
                SUM(CUSTO_PRODUTO(CONSPLAN.CDFILICPLA, CONSPLAN.CDPRODCPLA, V_INTPCUSTCMP, CONSPLAN.DTCONSPLAN, SYSDATE) * CONSPLAN.QTCONSPLAN) INTO V_VRCUSTO 
             FROM CONSPLAN 
             WHERE (CONSPLAN.CDFILICPLA = P_CDFILIAL)              AND 
                   (CONSPLAN.NRCONSPLAN = P_NRCONSPLAN)            AND 
                   (CONSPLAN.CDFILICPLA = CUR_SERVICO.CDFILIAL)    AND 
                   (CONSPLAN.CDSERVCPLA = CUR_SERVICO.CDSERVFILI); 
     
             IF (V_VRCUSTO IS NULL) THEN V_VRCUSTO := 0.0; END IF; 
     
             SELECT SUM(NVL(VRCUSTPADR * V_VRINDCALCMP, 0.0)) INTO V_VRCUSTOPADR 
             FROM CUSTPADR 
             WHERE 
                (CDFILIAL   = CUR_SERVICO.CDFILIAL)   AND 
                (CDSERVFILI = CUR_SERVICO.CDSERVFILI) AND 
                (TO_CHAR(DTREFCUSPADR, 'MM/YYYY') = TO_CHAR(P_DTFINAL, 'MM/YYYY')); 
     
             SELECT 
                SUM(DECODE(V_IDCOMCUSPERC, 
                           'C', QTCLIESTICAR, 
                                QTCOMESTICAR)) INTO V_QTCOMES 
             FROM 
                CARDADIA 
             WHERE 
                (CDFILIAL   = CUR_SERVICO.CDFILIAL)   AND
                (CDSERVFILI = CUR_SERVICO.CDSERVFILI) AND
                (DTCARDAPIO 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

                FOR C_CONSPLAN IN (
                   SELECT CDFILICPLA,
                          CDSERVCPLA,
                          CDPRODCPLA,
                          DTCONSPLAN,
                          SUM(QTCONSPLAN) AS QTCONSPLAN,
                          P_CDOPERADOR AS CDOPERADOR
                   FROM CONSPLAN
                   WHERE(CDFILICPLA = P_CDFILIAL)
                     AND(NRCONSPLAN = P_NRCONSPLAN)
                     AND(CDFILICPLA = CUR_SERVICO.CDFILIAL)
                     AND(CDSERVCPLA = CUR_SERVICO.CDSERVFILI)
                     AND EXISTS (SELECT CDSERVCPLA
                                 FROM SERVICO
                                 WHERE CONSPLAN.CDFILICPLA = SERVICO.CDFILIAL
                                  AND CONSPLAN.CDSERVCPLA = SERVICO.CDSERVFILI
                                  AND SERVICO.IDSERVATIV = 'S')
                   GROUP BY CDFILICPLA,
                            CDSERVCPLA,
                            CDPRODCPLA,
                            DTCONSPLAN
                ) LOOP

                   BEGIN
                      INSERT INTO PREVCONS (CDFILIAL, CDSERVFILI, CDPRODUTO, DTPREVCONS,  QTPREVCONS,  CDOPERADOR)
                      VALUES(C_CONSPLAN.CDFILICPLA, C_CONSPLAN.CDSERVCPLA, C_CONSPLAN.CDPRODCPLA, C_CONSPLAN.DTCONSPLAN, C_CONSPLAN.QTCONSPLAN, C_CONSPLAN.CDOPERADOR);
                   EXCEPTION
                      WHEN DUP_VAL_ON_INDEX THEN
                         NULL;
                   END;
                END LOOP;
                
             END IF;
          END LOOP;
       ELSE

          FOR C_CONSPLAN IN (
             SELECT CDFILICPLA,
                    CDSERVCPLA,
                    CDPRODCPLA,
                    DTCONSPLAN,
                    SUM(QTCONSPLAN) AS QTCONSPLAN,
                    P_CDOPERADOR AS CDOPERADOR
             FROM
                CONSPLAN
             WHERE
                CDFILICPLA = P_CDFILIAL   AND
                NRCONSPLAN = P_NRCONSPLAN AND
                EXISTS (SELECT CDSERVCPLA
                          FROM SERVICO
                         WHERE CONSPLAN.CDFILICPLA = SERVICO.CDFILIAL
                           AND CONSPLAN.CDSERVCPLA = SERVICO.CDSERVFILI
                           AND SERVICO.IDSERVATIV = 'S')
             GROUP BY
                CDFILICPLA,
                CDSERVCPLA,
                CDPRODCPLA,
                DTCONSPLAN
          ) LOOP

             BEGIN
                INSERT INTO PREVCONS (CDFILIAL, CDSERVFILI, CDPRODUTO, DTPREVCONS, QTPREVCONS, CDOPERADOR)
                VALUES(C_CONSPLAN.CDFILICPLA, C_CONSPLAN.CDSERVCPLA, C_CONSPLAN.CDPRODCPLA, C_CONSPLAN.DTCONSPLAN, C_CONSPLAN.QTCONSPLAN, C_CONSPLAN.CDOPERADOR);
             EXCEPTION
                WHEN DUP_VAL_ON_INDEX THEN
                   NULL;
             END;
          END LOOP;

       END IF;

       COMMIT;

       DELETE FROM CONSPLAN
       WHERE 
          CDFILICPLA = P_CDFILIAL AND 
          NRCONSPLAN = P_NRCONSPLAN; 
     
       COMMIT; 
    END IF; 
END; 
/

-- VERSAO --
BEGIN ATUVEROBJ('PREVISAO_DE_CONSUMO_PLA','0.00.000'); END;
/