/* Sistemas Integrados TECNISA
   Planejamento Alimenta��o Industrial

Nome: 7090_PLAPKG_UTIL_CONSPLAN_PLA.SQL

Descri��o:
Rotinas e cursores auxiliares para c�lculo do consumo planejado

Para aplicar e debugar
--
-- START C:\WIN_PRJ\SQL\ORACLE\7090_PLAPKG_UTIL_CONSPLAN_PLA.SQL;
-- SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('xx');

Cria��o:
26/02/99 - Zed

Manuten��es:
27/04/99 - Ederson - Corrigir o tipo dos parametros referentes ao
                     intervalo de produtos que nao podiam ser declarados
                     com o tipo da tabela, ou seja, VARCHAR2(10).
16/09/99 - Robert  - Altera��o do tipo de cursor PRODPRAT_RECTYPE para ter
                     mais tres campos para poder retirar a fun��o
                     QTDE_BRUTA_PRODUTO da cl�usula SELECT
*/

create or replace
PACKAGE UTIL_CONSPLAN_PLA AS
   /* CURSOR PRINCIPAL  */
   TYPE CONSPLAN_RECTYPE IS RECORD (
      DTDATA       DATA.DTDATA%TYPE,
      CDSERVFILI   SERVICO.CDSERVFILI%TYPE,
      HRINICATEN   SERVICO.HRINICATEN%TYPE,
      NRCARDAPIO   CARDADIA.NRCARDAPIO%TYPE,
      CDCLIENTE    CARDACLI.CDCLIENTE%TYPE,
      CDDESTFILI   CARDACLI.CDDESTFILI%TYPE,
      QTCOMESTICAR CARDADIA.QTCOMESTICAR%TYPE,
      QTESTITURNO1 CARDADIA.QTESTITURNO1%TYPE,
      QTESTITURNO2 CARDADIA.QTESTITURNO2%TYPE,
      QTESTITURNO3 CARDADIA.QTESTITURNO3%TYPE,
      QTESTITURNO4 CARDADIA.QTESTITURNO4%TYPE);
   TYPE CONSPLAN_CURTYPE IS REF CURSOR RETURN CONSPLAN_RECTYPE;
   /* COMPOSICAO DO CARDAPIO DO DIA */
   TYPE COMPCARD_RECTYPE IS RECORD (
      CDFILAUXPRAT COMPCARD.CDFILAUXPRAT%TYPE,
      CDPRATO      COMPCARD.CDPRATO%TYPE,
      VRPECONSCARD COMPCARD.VRPECONSCARD%TYPE,
     /* NRSEQPRATCAR COMPCARD.NRSEQPRATCAR%TYPE,*/
      CDTIPOPRAT   COMPCARD.CDTIPOPRAT%TYPE,
      CDSETORCOZ   SETOTIPOPRAT.CDSETORCOZ%TYPE);
   TYPE COMPCARD_CURTYPE IS REF CURSOR RETURN COMPCARD_RECTYPE;
   /* PRODUTOS DO PRATO */
   TYPE PRODPRAT_RECTYPE IS RECORD (
      CDPRODUTO  PRODUTO.CDPRODUTO%TYPE,
      QTPRODUTO  NUMBER(20, 08),
      SGUNIDADE  PRODUTO.SGUNIDADE%TYPE,
      VRPEFATCOC NUMBER,
      VRPEFATCOR NUMBER,
      HRTEMPPREP VARCHAR2(5),
      CDAPRERECE VARCHAR2(03),
      QTCOMPLPROD NUMBER(20, 08),
      QTBRUTAUNR NUMBER(20,08),
      IDTPREQPROD VARCHAR2(1));
   TYPE PRODPRAT_CURTYPE IS REF CURSOR RETURN PRODPRAT_RECTYPE;
   /* CONSUMO POR SERVI�O */
   TYPE CONSSERV_RECTYPE IS RECORD (
      CDFILIAL   CONSSERV.CDFILIAL%TYPE,
      CDSERVFILI CONSSERV.CDSERVFILI%TYPE,
      DTCONSSERV CONSSERV.DTCONSSERV%TYPE,
      CDPRODUTO  CONSSERV.CDPRODUTO%TYPE,
      QTCONSSERV CONSSERV.QTCONSSERV%TYPE,
      IDQTDECONS CONSSERV.IDQTDECONS%TYPE,
      IDPRODCONSFPE CONSSERV.IDPRODCONSFPE%TYPE,
      IDTPREQPROD CONSSERV.IDTPREQPROD%TYPE,
      QTUTILCENTRAL CONSSERV.QTUTILCENTRAL%TYPE);
   TYPE CONSSERV_CURTYPE IS REF CURSOR RETURN CONSSERV_RECTYPE;
   /* CURSOR PARA ARREDONDAMENTO */
   TYPE ARREDOND_RECTYPE IS RECORD (
      NRREQUCONS   VARCHAR2(10),
      CDFILIAL     FILIAL.CDFILIAL%TYPE,
      CDSERVFILI   SERVICO.CDSERVFILI%TYPE,
      DTDATA       DATE,
      IDTIPOCONS   VARCHAR2(01),
      CDFILAUXPRAT PRATO.CDFILAUXPRAT%TYPE,
      CDPRATO      PRATO.CDPRATO%TYPE,
      CDPRODUTO    PRODUTO.CDPRODUTO%TYPE,
      DTLIMITE     DATE,
      HRLIMITE     VARCHAR2(05),
      CDTIPOPRAT   TIPOPRAT.CDTIPOPRAT%TYPE,
      CDCLIENTE    CARDACLI.CDCLIENTE%TYPE,
      CDDESTFILI   CARDACLI.CDDESTFILI%TYPE,
      VRARREDREQ   PRODUTO.VRARREDREQ%TYPE,
      VRARREDCMP   PRODUTO.VRARREDCMP%TYPE,
      QTCOMENS     NUMBER,
      QTCOMENST1   NUMBER,
      QTCOMENST2   NUMBER,
      QTCOMENST3   NUMBER,
      QTCOMENST4   NUMBER);
   TYPE ARREDOND_CURTYPE IS REF CURSOR RETURN ARREDOND_RECTYPE;
   PROCEDURE ATUALIZA_PREVRETI(P_CDFILIAL     IN PREVRETI.CDFILIAL%TYPE,
                               R_CONSPLAN     IN CONSPLAN_RECTYPE,
                               P_IDPREVRETI   IN PREVRETI.IDPREVRETI%TYPE,
                               P_CDPRODUTO    IN PREVRETI.CDPRODUTO%TYPE,
                               P_DTLIMITERETI IN PREVRETI.DTLIMITERETI%TYPE,
                               P_HRLIMITERETI IN PREVRETI.HRLIMITERETI%TYPE,
                               P_QTPREVRETI   IN PREVRETI.QTPREVRETI%TYPE,
                               P_QTPRERETT1   IN PREVRETI.QTPRERETT1%TYPE,
                               P_QTPRERETT2   IN PREVRETI.QTPRERETT2%TYPE,
                               P_QTPRERETT3   IN PREVRETI.QTPRERETT3%TYPE,
                               P_QTPRERETT4   IN PREVRETI.QTPRERETT4%TYPE,
                               P_AJUSHRLIMI   IN VARCHAR2,
                               P_CDOPERADOR   IN PREVRETI.CDOPERADOR%TYPE,
                               P_IDUTREQUPRAT IN PARAMPLANEJA.IDUTREQUPRAT%TYPE,
                               P_CDTIPOPRAT   IN TIPOPRAT.CDTIPOPRAT%TYPE,
                               P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
                               P_CDPRATO      IN PRATO.CDPRATO%TYPE,
                               P_VRPEFATCOR   IN PREVRETI.VRPEFATCORRETI%TYPE,
                               P_QTBRUTAUNR   IN PREVRETI.QTPREVBRUTAUNR%TYPE,
                               P_SGUNADICIONAL IN PREVRETI.SGPREVUNIADIC%TYPE);
   PROCEDURE ATUALIZA_CONSPLAN(P_NRCONSPLAN   IN CONSPLAN.NRCONSPLAN%TYPE,
                               P_CDFILIAL     IN CONSPLAN.CDFILICPLA%TYPE,
                               R_CONSPLAN     IN CONSPLAN_RECTYPE,
                               P_IDCONSPLAN   IN CONSPLAN.IDCONSPLAN%TYPE,
                               P_CDFAPRATCPLA IN CONSPLAN.CDFAPRATCPLA%TYPE,
                               P_CDPRATCONSPL IN CONSPLAN.CDPRATCONSPL%TYPE,
                               P_CDAPRECONSPL IN CONSPLAN.CDAPRECONSPL%TYPE,
                               P_CDSETOCONSPL IN CONSPLAN.CDSETOCONSPL%TYPE,
                               P_CDPRODCPLA   IN CONSPLAN.CDPRODCPLA%TYPE,
                               P_DTLIMITECPLA IN CONSPLAN.DTLIMITECPLA%TYPE,
                               P_HRLIMITECPLA IN CONSPLAN.HRLIMITECPLA%TYPE,
                               P_CDTPPRATCPLA IN CONSPLAN.CDTPPRATCPLA%TYPE,
                               P_QTCONSPLAN   IN CONSPLAN.QTCONSPLAN%TYPE,
                               P_QTCONSPLANT1 IN CONSPLAN.QTCONSPLANT1%TYPE,
                               P_QTCONSPLANT2 IN CONSPLAN.QTCONSPLANT2%TYPE,
                               P_QTCONSPLANT3 IN CONSPLAN.QTCONSPLANT3%TYPE,
                               P_QTCONSPLANT4 IN CONSPLAN.QTCONSPLANT4%TYPE,
                               P_VRPECONSPLAN IN CONSPLAN.VRPECONSPLAN%TYPE,
                               P_QTCOMESTCPLA IN CONSPLAN.QTCOMESTCPLA%TYPE,
                               P_AJUSHRLIMI   IN VARCHAR2,
                               P_QTCOMPLPRODCPLA IN CONSPLAN.QTCOMPLPRODCPLA%TYPE,
                               P_VRPEFATCOR IN CONSPLAN.VRPEFATCORCONS%TYPE,
                               P_QTBRUTAUNR IN CONSPLAN.QTCONSBRUTAUNR%TYPE,
                               P_SGUNADICIONAL IN CONSPLAN.SGCONSUNIADIC%TYPE,
                               P_IDTPREQPROD IN CONSPLAN.IDTPREQPROD%TYPE,
                               P_QTUTILCENTRAL IN NUMBER DEFAULT NULL);
   PROCEDURE OPEN_CONSPLAN_CURSOR (C_CONSPLAN    IN OUT CONSPLAN_CURTYPE,
                                   P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
                                   P_DTINICIAL   IN DATE,
                                   P_DTFINAL     IN DATE,
                                   P_SERVICOS    IN CLOB,
                                   P_IDTIPOCONS  IN VARCHAR2,
                                   P_NRCONSPLAN  IN VARCHAR2);
   PROCEDURE OPEN_COMPCARD_CURSOR (C_COMPCARD   IN OUT COMPCARD_CURTYPE,
                                   P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE,
                                   P_NRCARDAPIO IN CARDADIA.NRCARDAPIO%TYPE);
   PROCEDURE OPEN_PRODPRAT_CURSOR (C_PRODPRAT     IN OUT PRODPRAT_CURTYPE,
                                   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                   P_CDSERVFILI   IN SERVICO.CDSERVFILI%TYPE,
                                   P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
                                   P_CDPRATO      IN PRATO.CDPRATO%TYPE,
                                   P_QTPRATO      IN NUMBER,
                                   P_CDPRODUTOINI IN VARCHAR2,
                                   P_CDPRODUTOFIN IN VARCHAR2,
                                   P_IDUTILFATCOR IN PARAMPLANEJA.IDUTILFATCOR%TYPE,
                                   P_IDUTILFATCOC IN PARAMPLANEJA.IDUTILFATCOC%TYPE,
                                   P_IDTIPCALCFAT IN PARAMPLANEJA.IDTIPCALCFAT%TYPE);
   PROCEDURE OPEN_CONSSERV_CURSOR (C_CONSSERV     IN OUT CONSSERV_CURTYPE,
                                   P_CDFILIAL     IN CONSSERV.CDFILIAL%TYPE,
                                   P_CDSERVFILI   IN CONSSERV.CDSERVFILI%TYPE,
                                   P_DTCONSSERV   IN CONSSERV.DTCONSSERV%TYPE,
                                   P_FERIADO      IN NUMBER,
                                   P_CDPRODUTOINI IN VARCHAR2,
                                   P_CDPRODUTOFIN IN VARCHAR2,
                                   P_IDTPCONSSERV IN VARCHAR2);
   PROCEDURE OPEN_ARREDOND_CURSOR (C_ARREDOND   IN OUT ARREDOND_CURTYPE,
                                   P_TIPOCURS   IN VARCHAR2,
                                   P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE,
                                   P_CDSERVICOS IN CLOB,
                                   P_DTINICIAL  IN DATE,
                                   P_DTFINAL    IN DATE,
                                   P_NRREQUCONS IN VARCHAR2,
                                   P_NRCONSPLAN IN VARCHAR2);
END UTIL_CONSPLAN_PLA;
/

create or replace
PACKAGE BODY UTIL_CONSPLAN_PLA AS
   /* - Atualiza a tabela PREVRETI verificando se o registro j� existe ------ */
   PROCEDURE ATUALIZA_PREVRETI(P_CDFILIAL        IN PREVRETI.CDFILIAL%TYPE,
                               R_CONSPLAN        IN CONSPLAN_RECTYPE,
                               P_IDPREVRETI      IN PREVRETI.IDPREVRETI%TYPE,
                               P_CDPRODUTO       IN PREVRETI.CDPRODUTO%TYPE,
                               P_DTLIMITERETI    IN PREVRETI.DTLIMITERETI%TYPE,
                               P_HRLIMITERETI    IN PREVRETI.HRLIMITERETI%TYPE,
                               P_QTPREVRETI      IN PREVRETI.QTPREVRETI%TYPE,
                               P_QTPRERETT1      IN PREVRETI.QTPRERETT1%TYPE,
                               P_QTPRERETT2      IN PREVRETI.QTPRERETT2%TYPE,
                               P_QTPRERETT3      IN PREVRETI.QTPRERETT3%TYPE,
                               P_QTPRERETT4      IN PREVRETI.QTPRERETT4%TYPE,
                               P_AJUSHRLIMI      IN VARCHAR2,
                               P_CDOPERADOR      IN PREVRETI.CDOPERADOR%TYPE,
                               P_IDUTREQUPRAT    IN PARAMPLANEJA.IDUTREQUPRAT%TYPE,
                               P_CDTIPOPRAT      IN TIPOPRAT.CDTIPOPRAT%TYPE,
                               P_CDFILAUXPRAT    IN PRATO.CDFILAUXPRAT%TYPE,
                               P_CDPRATO         IN PRATO.CDPRATO%TYPE,
                               P_VRPEFATCOR      IN PREVRETI.VRPEFATCORRETI%TYPE,
                               P_QTBRUTAUNR      IN PREVRETI.QTPREVBRUTAUNR%TYPE,
                               P_SGUNADICIONAL   IN PREVRETI.SGPREVUNIADIC%TYPE) IS
      V_DTLIMITEPR   DATE;
      V_HRLIMITEPR   VARCHAR2(4);
      /*V_CONTAUXI     NUMBER;*/
      V_NRPRPREVRETI PREVRETI.NRPRPREVRETI%TYPE;
   BEGIN
      /*SELECT
         COUNT(*)
      INTO
         V_CONTAUXI
      FROM
         PREVRETI
      WHERE
         (CDFILIAL     = P_CDFILIAL)            AND
         (CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
         (DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
         (NRREQUEXTR   = '0000000000')          AND
         (IDPREVRETI   = P_IDPREVRETI)          AND
         (CDPRODUTO    = P_CDPRODUTO)           AND
         (CDTIPOPRAT   = P_CDTIPOPRAT)          AND
         (CDFILAUXPRAT = P_CDFILAUXPRAT)        AND
         (CDPRATO      = P_CDPRATO);  */
      /* - Verifica se o produto j� est� cadastrado ------------------------- */
     /* IF (V_CONTAUXI = 0) THEN */
      BEGIN
         IF (P_IDUTREQUPRAT <> 'S') THEN
            INSERT INTO PREVRETI
               (CDFILIAL, CDSERVFILI, DTPREVRETI, NRREQUEXTR, IDPREVRETI, CDPRODUTO, DTLIMITERETI, HRLIMITERETI,
                QTPREVRETI, QTPRERETT1, QTPRERETT2, QTPRERETT3, QTPRERETT4,
                QTPREVRETISA, QTPRERETT1SA, QTPRERETT2SA, QTPRERETT3SA, QTPRERETT4SA,
                CDOPERADOR, IDPREVTPPRAT, NRPRPREVRETI, VRPEFATCORRETI, QTPREVBRUTAUNR, SGPREVUNIADIC)
            VALUES
               (P_CDFILIAL, R_CONSPLAN.CDSERVFILI, R_CONSPLAN.DTDATA, '0000000000',
                P_IDPREVRETI, P_CDPRODUTO, TO_DATE(TO_CHAR(P_DTLIMITERETI, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
                P_HRLIMITERETI, P_QTPREVRETI,
                P_QTPRERETT1, P_QTPRERETT2, P_QTPRERETT3, P_QTPRERETT4,
                P_QTPREVRETI, P_QTPRERETT1, P_QTPRERETT2, P_QTPRERETT3, P_QTPRERETT4,
                P_CDOPERADOR, 'N', '0000', P_VRPEFATCOR, P_QTBRUTAUNR, P_SGUNADICIONAL);
            /* - Efetua o ajuste da data/hora limite --------------------------- */
            IF P_AJUSHRLIMI = 'S' THEN
               SELECT
                  MAX(DTLIMITERETI),
                  MAX(HRLIMITERETI)
               INTO
                  V_DTLIMITEPR,
                  V_HRLIMITEPR
               FROM
                  PREVRETI
               WHERE
                  (CDFILIAL     = P_CDFILIAL)            AND
                  (CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
                  (DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
                  (NRREQUEXTR   = '0000000000')          AND
                  (IDPREVRETI   = P_IDPREVRETI)          AND
                  (CDPRODUTO    = P_CDPRODUTO);
               IF V_DTLIMITEPR IS NULL THEN V_DTLIMITEPR := R_CONSPLAN.DTDATA; END IF;
               IF V_HRLIMITEPR IS NULL THEN V_HRLIMITEPR := '0000'; END IF;
               V_DTLIMITEPR := TO_DATE(TO_CHAR(V_DTLIMITEPR, 'DD/MM/YYYY')||V_HRLIMITEPR, 'DD/MM/YYYYHH24MI');
               IF P_DTLIMITERETI < V_DTLIMITEPR THEN
                  UPDATE
                     PREVRETI
                  SET
                     DTLIMITERETI = TO_DATE(TO_CHAR(P_DTLIMITERETI, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
                     HRLIMITERETI = P_HRLIMITERETI
                  WHERE
                     (CDFILIAL     = P_CDFILIAL)            AND
                     (CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
                     (DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
                     (NRREQUEXTR   = '0000000000')          AND
                     (IDPREVRETI   = P_IDPREVRETI)          AND
                     (CDPRODUTO    = P_CDPRODUTO);
               END IF;
            END IF; /* Ajuste da data/hora limite */
         END IF;
         IF (P_IDUTREQUPRAT = 'S') AND (NOT P_CDTIPOPRAT IS NULL) THEN
            SELECT
               NVL(MAX(PR.NRPRPREVRETI), 0) INTO V_NRPRPREVRETI
            FROM
               PREVRETI PR
            WHERE
               (PR.CDFILIAL     = P_CDFILIAL)            AND
               (PR.CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
               (PR.DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
               (PR.IDPREVRETI   = P_IDPREVRETI)          AND
               (PR.NRREQUEXTR   = '0000000000');
            V_NRPRPREVRETI := LPAD(TO_CHAR(TO_NUMBER(V_NRPRPREVRETI) + 1), 4, 0);
            INSERT INTO PREVRETI
               (CDFILIAL, CDSERVFILI, DTPREVRETI, NRREQUEXTR, IDPREVRETI, CDPRODUTO, DTLIMITERETI, HRLIMITERETI,
                QTPREVRETI, QTPRERETT1, QTPRERETT2, QTPRERETT3, QTPRERETT4,
                QTPREVRETISA, QTPRERETT1SA, QTPRERETT2SA, QTPRERETT3SA, QTPRERETT4SA,
                CDOPERADOR, CDTIPOPRAT, CDFILAUXPRAT, CDPRATO, IDPREVTPPRAT,
                NRPRPREVRETI, VRPEFATCORRETI, QTPREVBRUTAUNR, SGPREVUNIADIC)
            VALUES
               (P_CDFILIAL, R_CONSPLAN.CDSERVFILI, R_CONSPLAN.DTDATA, '0000000000',
                P_IDPREVRETI, P_CDPRODUTO, TO_DATE(TO_CHAR(P_DTLIMITERETI, 'DD/MM/YYYY'), 'DD/MM/YYYY'), P_HRLIMITERETI,
                P_QTPREVRETI, P_QTPRERETT1, P_QTPRERETT2, P_QTPRERETT3, P_QTPRERETT4,
                P_QTPREVRETI, P_QTPRERETT1, P_QTPRERETT2, P_QTPRERETT3, P_QTPRERETT4,
                P_CDOPERADOR, P_CDTIPOPRAT, P_CDFILAUXPRAT, P_CDPRATO, 'S', 
                V_NRPRPREVRETI, P_VRPEFATCOR, P_QTBRUTAUNR, P_SGUNADICIONAL);
            /* - Efetua o ajuste da data/hora limite --------------------------- */
            IF P_AJUSHRLIMI = 'S' THEN
               SELECT
                  MAX(DTLIMITERETI),
                  MAX(HRLIMITERETI)
               INTO
                  V_DTLIMITEPR,
                  V_HRLIMITEPR
               FROM
                  PREVRETI
               WHERE
                  (CDFILIAL     = P_CDFILIAL)            AND
                  (CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
                  (DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
                  (NRREQUEXTR   = '0000000000')          AND
                  (IDPREVRETI   = P_IDPREVRETI)          AND
                  (CDPRODUTO    = P_CDPRODUTO)           AND
                  (CDTIPOPRAT   = P_CDTIPOPRAT)          AND
                  (CDFILAUXPRAT = P_CDFILAUXPRAT)        AND
                  (CDPRATO      = P_CDPRATO);
               IF V_DTLIMITEPR IS NULL THEN V_DTLIMITEPR := R_CONSPLAN.DTDATA; END IF;
               IF V_HRLIMITEPR IS NULL THEN V_HRLIMITEPR := '0000'; END IF;
               V_DTLIMITEPR := TO_DATE(TO_CHAR(V_DTLIMITEPR, 'DD/MM/YYYY')||V_HRLIMITEPR, 'DD/MM/YYYYHH24MI');
               IF P_DTLIMITERETI < V_DTLIMITEPR THEN
                  UPDATE
                     PREVRETI
                  SET
                     DTLIMITERETI = TO_DATE(TO_CHAR(P_DTLIMITERETI, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
                     HRLIMITERETI = P_HRLIMITERETI
                  WHERE
                     (CDFILIAL     = P_CDFILIAL)            AND
                     (CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
                     (DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
                     (NRREQUEXTR   = '0000000000')          AND
                     (IDPREVRETI   = P_IDPREVRETI)          AND
                     (CDPRODUTO    = P_CDPRODUTO)           AND
                     (CDTIPOPRAT   = P_CDTIPOPRAT)          AND
                     (CDFILAUXPRAT = P_CDFILAUXPRAT)        AND
                     (CDPRATO      = P_CDPRATO);
               END IF;
            END IF; /* Ajuste da data/hora limite */
         END IF;
      /* ELSE /* - Soma a quantidade ------------------------------------------- */
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX THEN BEGIN
            UPDATE
               PREVRETI
            SET
               QTPREVRETI     = QTPREVRETI + P_QTPREVRETI,
               QTPRERETT1     = QTPRERETT1 + P_QTPRERETT1,
               QTPRERETT2     = QTPRERETT2 + P_QTPRERETT2,
               QTPRERETT3     = QTPRERETT3 + P_QTPRERETT3,
               QTPRERETT4     = QTPRERETT4 + P_QTPRERETT4,
               QTPREVRETISA   = QTPREVRETI + P_QTPREVRETI,
               QTPRERETT1SA   = QTPRERETT1 + P_QTPRERETT1,
               QTPRERETT2SA   = QTPRERETT2 + P_QTPRERETT2,
               QTPRERETT3SA   = QTPRERETT3 + P_QTPRERETT3,
               QTPRERETT4SA   = QTPRERETT4 + P_QTPRERETT4,
               QTPREVBRUTAUNR = QTPREVBRUTAUNR + P_QTBRUTAUNR
            WHERE
               (CDFILIAL     = P_CDFILIAL)            AND
               (CDSERVFILI   = R_CONSPLAN.CDSERVFILI) AND
               (DTPREVRETI   = R_CONSPLAN.DTDATA)     AND
               (NRREQUEXTR   = '0000000000')          AND
               (IDPREVRETI   = P_IDPREVRETI)          AND
               (CDPRODUTO    = P_CDPRODUTO)           AND
               (CDTIPOPRAT   = P_CDTIPOPRAT)          AND
               (CDFILAUXPRAT = P_CDFILAUXPRAT)        AND
               (CDPRATO      = P_CDPRATO);
         END;
      END; /* Verifica��o da exist�ncia do produto */
   END; /* ATUALIZA_PREVRETI */
   /* - Atualiza a tabela CONSPLAN verificando se o registro j� existe ------ */
   PROCEDURE ATUALIZA_CONSPLAN(P_NRCONSPLAN      IN CONSPLAN.NRCONSPLAN%TYPE,
                               P_CDFILIAL        IN CONSPLAN.CDFILICPLA%TYPE,
                               R_CONSPLAN        IN CONSPLAN_RECTYPE,
                               P_IDCONSPLAN      IN CONSPLAN.IDCONSPLAN%TYPE,
                               P_CDFAPRATCPLA    IN CONSPLAN.CDFAPRATCPLA%TYPE,
                               P_CDPRATCONSPL    IN CONSPLAN.CDPRATCONSPL%TYPE,
                               P_CDAPRECONSPL    IN CONSPLAN.CDAPRECONSPL%TYPE,
                               P_CDSETOCONSPL    IN CONSPLAN.CDSETOCONSPL%TYPE,
                               P_CDPRODCPLA      IN CONSPLAN.CDPRODCPLA%TYPE,
                               P_DTLIMITECPLA    IN CONSPLAN.DTLIMITECPLA%TYPE,
                               P_HRLIMITECPLA    IN CONSPLAN.HRLIMITECPLA%TYPE,
                               P_CDTPPRATCPLA    IN CONSPLAN.CDTPPRATCPLA%TYPE,
                               P_QTCONSPLAN      IN CONSPLAN.QTCONSPLAN%TYPE,
                               P_QTCONSPLANT1    IN CONSPLAN.QTCONSPLANT1%TYPE,
                               P_QTCONSPLANT2    IN CONSPLAN.QTCONSPLANT2%TYPE,
                               P_QTCONSPLANT3    IN CONSPLAN.QTCONSPLANT3%TYPE,
                               P_QTCONSPLANT4    IN CONSPLAN.QTCONSPLANT4%TYPE,
                               P_VRPECONSPLAN    IN CONSPLAN.VRPECONSPLAN%TYPE,
                               P_QTCOMESTCPLA    IN CONSPLAN.QTCOMESTCPLA%TYPE,
                               P_AJUSHRLIMI      IN VARCHAR2,
                               P_QTCOMPLPRODCPLA IN CONSPLAN.QTCOMPLPRODCPLA%TYPE,
                               P_VRPEFATCOR      IN CONSPLAN.VRPEFATCORCONS%TYPE,
                               P_QTBRUTAUNR      IN CONSPLAN.QTCONSBRUTAUNR%TYPE,
                               P_SGUNADICIONAL   IN CONSPLAN.SGCONSUNIADIC%TYPE,
                               P_IDTPREQPROD     IN CONSPLAN.IDTPREQPROD%TYPE,
                               P_QTUTILCENTRAL   IN NUMBER DEFAULT NULL) IS
      V_DTLIMITEPR   DATE;
      V_HRLIMITEPR   VARCHAR2(4);
      V_QTCOMESTCPLA CONSPLAN.QTCOMESTCPLA%TYPE;
      V_QTCONSPLAN   CONSPLAN.QTCONSPLAN%TYPE;
      V_QTCONSPLANT1 CONSPLAN.QTCONSPLAN%TYPE;
      V_QTCONSPLANT2 CONSPLAN.QTCONSPLAN%TYPE;
      V_QTCONSPLANT3 CONSPLAN.QTCONSPLAN%TYPE;
      V_QTCONSPLANT4 CONSPLAN.QTCONSPLAN%TYPE;
      V_QTCOMPLPRODCPLA CONSPLAN.QTCOMPLPRODCPLA%TYPE;
      v_QTBRUTAUNR CONSPLAN.QTCONSBRUTAUNR%type;
      V_SGUNADICIONAL CONSPLAN.SGCONSUNIADIC%TYPE;
   BEGIN
      V_QTCOMESTCPLA := P_QTCOMESTCPLA;
      IF V_QTCOMESTCPLA = 0 THEN
         V_QTCOMESTCPLA := 1;
      END IF;
      IF (P_IDCONSPLAN = 'F') THEN
         V_QTCONSPLAN   := 0;
         V_QTCONSPLANT1 := 0;
         V_QTCONSPLANT2 := 0;
         V_QTCONSPLANT3 := 0;
         V_QTCONSPLANT4 := 0;
         V_QTCOMPLPRODCPLA := 0;
         v_QTBRUTAUNR := 0;
      ELSE
         V_QTCONSPLAN   :=  P_QTCONSPLAN;
         V_QTCONSPLANT1 :=  P_QTCONSPLANT1;
         V_QTCONSPLANT2 :=  P_QTCONSPLANT2;
         V_QTCONSPLANT3 :=  P_QTCONSPLANT3;
         V_QTCONSPLANT4 :=  P_QTCONSPLANT4;
         V_QTCOMPLPRODCPLA := P_QTCOMPLPRODCPLA;
         v_QTBRUTAUNR := P_QTBRUTAUNR;
      END IF;
      UPDATE CONSPLAN
      SET
         QTCONSPLAN      = QTCONSPLAN   + V_QTCONSPLAN,
         QTCONSPLANT1    = QTCONSPLANT1 + V_QTCONSPLANT1,
         QTCONSPLANT2    = QTCONSPLANT2 + V_QTCONSPLANT2,
         QTCONSPLANT3    = QTCONSPLANT3 + V_QTCONSPLANT3,
         QTCONSPLANT4    = QTCONSPLANT4 + V_QTCONSPLANT4,
         QTCONSPLANSA    = QTCONSPLAN   + V_QTCONSPLAN,
         QTCONPLAT1SA    = QTCONSPLANT1 + V_QTCONSPLANT1,
         QTCONPLAT2SA    = QTCONSPLANT2 + V_QTCONSPLANT2,
         QTCONPLAT3SA    = QTCONSPLANT3 + V_QTCONSPLANT3,
         QTCONPLAT4SA    = QTCONSPLANT4 + V_QTCONSPLANT4,
         QTCOMPLPRODCPLA = QTCOMPLPRODCPLA + V_QTCOMPLPRODCPLA,
         QTCONSBRUTAUNR  = QTCONSBRUTAUNR + V_QTBRUTAUNR,
         QTUTILCENTRAL   = P_QTUTILCENTRAL
      WHERE
         (NRCONSPLAN   = P_NRCONSPLAN)                AND
         (CDFILICPLA   = P_CDFILIAL)                  AND
         (CDSERVCPLA   = R_CONSPLAN.CDSERVFILI)       AND
         (DTCONSPLAN   = R_CONSPLAN.DTDATA)           AND
         (IDCONSPLAN   = P_IDCONSPLAN)                AND
         (CDFAPRATCPLA = P_CDFAPRATCPLA)              AND
         (CDPRATCONSPL = P_CDPRATCONSPL)              AND
         (CDTPPRATCPLA = P_CDTPPRATCPLA)              AND
         (CDPRODCPLA   = P_CDPRODCPLA)                AND
         (CDCLIECPLA   = R_CONSPLAN.CDCLIENTE)        AND
         (CDDESTFICPLA = R_CONSPLAN.CDDESTFILI);
      IF SQL%NOTFOUND THEN
         INSERT INTO CONSPLAN
            (NRCONSPLAN, CDFILICPLA, CDSERVCPLA, DTCONSPLAN, IDCONSPLAN,
             CDFAPRATCPLA, CDPRATCONSPL, CDAPRECONSPL, CDSETOCONSPL, CDPRODCPLA, DTLIMITECPLA, HRLIMITECPLA,
             CDTPPRATCPLA, CDCLIECPLA, CDDESTFICPLA,
             QTCONSPLAN, QTCONSPLANT1, QTCONSPLANT2, QTCONSPLANT3, QTCONSPLANT4,
             QTCONSPLANSA, QTCONPLAT1SA, QTCONPLAT2SA, QTCONPLAT3SA, QTCONPLAT4SA,
             VRPECONSPLAN, QTCOMESTCPLA, QTCOMPLPRODCPLA, VRPEFATCORCONS, QTCONSBRUTAUNR, SGCONSUNIADIC, IDTPREQPROD, QTUTILCENTRAL)
         VALUES
            (P_NRCONSPLAN, P_CDFILIAL, R_CONSPLAN.CDSERVFILI, R_CONSPLAN.DTDATA, P_IDCONSPLAN,
             P_CDFAPRATCPLA, P_CDPRATCONSPL, P_CDAPRECONSPL, P_CDSETOCONSPL, P_CDPRODCPLA,
             TO_DATE(TO_CHAR(P_DTLIMITECPLA, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
             P_HRLIMITECPLA, P_CDTPPRATCPLA, R_CONSPLAN.CDCLIENTE, R_CONSPLAN.CDDESTFILI,
             P_QTCONSPLAN, P_QTCONSPLANT1, P_QTCONSPLANT2, P_QTCONSPLANT3, P_QTCONSPLANT4,
             P_QTCONSPLAN, P_QTCONSPLANT1, P_QTCONSPLANT2, P_QTCONSPLANT3, P_QTCONSPLANT4,
             P_VRPECONSPLAN, V_QTCOMESTCPLA, V_QTCOMPLPRODCPLA, P_VRPEFATCOR, P_QTBRUTAUNR, P_SGUNADICIONAL, P_IDTPREQPROD, P_QTUTILCENTRAL);
         IF P_AJUSHRLIMI = 'S' THEN /* Efetua o ajuste da data/hora limite */
            V_DTLIMITEPR := TO_DATE(TO_CHAR(P_DTLIMITECPLA, 'DD/MM/YYYY')||P_HRLIMITECPLA, 'DD/MM/YYYYHH24MI');
            IF P_DTLIMITECPLA < V_DTLIMITEPR THEN
               UPDATE
                  CONSPLAN
               SET
                  DTLIMITECPLA = TO_DATE(TO_CHAR(P_DTLIMITECPLA, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
                  HRLIMITECPLA = P_HRLIMITECPLA
               WHERE
                  (NRCONSPLAN   = P_NRCONSPLAN)          AND
                  (CDFILICPLA   = P_CDFILIAL)            AND
                  (CDSERVCPLA   = R_CONSPLAN.CDSERVFILI) AND
                  (DTCONSPLAN   = R_CONSPLAN.DTDATA)     AND
                  (IDCONSPLAN   = P_IDCONSPLAN)          AND
                  (CDFAPRATCPLA = P_CDFAPRATCPLA)        AND
                  (CDPRATCONSPL = P_CDPRATCONSPL)        AND
                  (CDTPPRATCPLA = P_CDTPPRATCPLA)        AND
                  (CDPRODCPLA   = P_CDPRODCPLA);
            END IF;
         END IF; /* Ajuste da data hora/limite */
      END IF;
   END; /* ATUALIZA_CONSPLAN */
   /* - Abre o cursor de acordo com o par�metro ----------------------------- */
   /* 1. Cursor a ser aberto                                                  */
   /* 2. Indicador do tipo de consumo 'Fixo' ou 'Percapita' ou 'Card�pio'     */
   PROCEDURE OPEN_CONSPLAN_CURSOR (C_CONSPLAN    IN OUT CONSPLAN_CURTYPE,
                                   P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
                                   P_DTINICIAL   IN DATE,
                                   P_DTFINAL     IN DATE,
                                   P_SERVICOS    IN CLOB,
                                   P_IDTIPOCONS  IN VARCHAR2,
                                   P_NRCONSPLAN  IN VARCHAR2) IS
   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;
      IF (P_IDTIPOCONS = 'C') THEN
      /* - Consumo do 'CARDAPIO' -------------------------------------------- */
         OPEN C_CONSPLAN FOR
            SELECT
               CA.DTCARDAPIO,
               CA.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               CA.NRCARDAPIO,
               'XXX' CDCLIENTE,
               'XXX' CDDESTFILI,
               CA.QTCOMESTICAR,
               CA.QTESTITURNO1,
               CA.QTESTITURNO2,
               CA.QTESTITURNO3,
               CA.QTESTITURNO4
            FROM
               CARDADIA   CA,
               SERVICO    SE/*,
               CONSPLANSE CO*/
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)       AND
               (CO.CDFILICPLASE = P_CDFILIAL)         AND
               (SE.CDFILIAL     = CO.CDFILICPLASE)    AND
               (SE.CDSERVFILI   = CO.CDSERVCPLASE)    AND
               (CA.CDFILIAL     = CO.CDFILICPLASE)    AND
               (CA.CDSERVFILI   = CO.CDSERVCPLASE)    AND
               (CA.DTCARDAPIO BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE) AND*/
               (SE.CDFILIAL     = P_CDFILIAL)     AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (CA.CDFILIAL     = SE.CDFILIAL)    AND
               (CA.CDSERVFILI   = SE.CDSERVFILI)  AND
               (CA.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               NOT EXISTS (SELECT * FROM CARDACLI
                           WHERE CDFILIAL   = CA.CDFILIAL AND
                                 NRCARDAPIO = CA.NRCARDAPIO)
            UNION
            SELECT
               CA.DTCARDAPIO,
               CA.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               CA.NRCARDAPIO,
               CC.CDCLIENTE,
               CC.CDDESTFILI,
               CC.QTCOMESTICLI QTCOMESTICAR,
               0 QTESTITURNO1,
               0 QTESTITURNO2,
               0 QTESTITURNO3,
               0 QTESTITURNO4
            FROM
               CARDACLI   CC,
               CARDADIA   CA,
               SERVICO    SE/*,
               CONSPLANSE CO*/
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)       AND
               (CO.CDFILICPLASE = P_CDFILIAL)         AND
               (SE.CDFILIAL     = CO.CDFILICPLASE)    AND
               (SE.CDSERVFILI   = CO.CDSERVCPLASE)    AND
               (CA.CDFILIAL     = CO.CDFILICPLASE)    AND
               (CA.CDSERVFILI   = CO.CDSERVCPLASE)    AND
               (CA.DTCARDAPIO BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE) AND*/
               (SE.CDFILIAL     = P_CDFILIAL)    AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (CA.CDFILIAL     = SE.CDFILIAL)   AND
               (CA.CDSERVFILI   = SE.CDSERVFILI) AND
               (CA.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               (CC.CDFILIAL   = CA.CDFILIAL)    AND
               (CC.NRCARDAPIO = CA.NRCARDAPIO)
            ORDER BY
               1, 2, 3, 4, 5, 6;
      ELSIF (P_IDTIPOCONS = 'P') THEN
      /* - Consumo por servi�o 'PERCAPITA' - proporcional aos comensais ----- */
         OPEN C_CONSPLAN FOR
            SELECT
               DT.DTDATA,
               SE.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               'PPPPPPPPPP' NRCARDAPIO,
               'XXX' CDCLIENTE,
               'XXX' CDDESTFILI,
               SUM(CA.QTCOMESTICAR) QTCOMESTICAR,
               SUM(CA.QTESTITURNO1) QTESTITURNO1,
               SUM(CA.QTESTITURNO2) QTESTITURNO2,
               SUM(CA.QTESTITURNO3) QTESTITURNO3,
               SUM(CA.QTESTITURNO4) QTESTITURNO4
            FROM
               DATA DT,
               SERVICO SE,
               /*CONSPLANSE CO,*/
               CARDADIA CA
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)    AND
               (CO.CDFILICPLASE = P_CDFILIAL)      AND
               (SE.CDFILIAL     = P_CDFILIAL)      AND
               (SE.CDSERVFILI   = CO.CDSERVCPLASE) AND*/
               (SE.CDFILIAL     = P_CDFILIAL)     AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (CA.CDFILIAL     = SE.CDFILIAL)    AND
               (CA.CDSERVFILI   = SE.CDSERVFILI)  AND
               (CA.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               (DT.DTDATA       = CA.DTCARDAPIO)  AND/*
               (DT.DTDATA       = CA.DTCARDAPIO)   AND
               (CA.CDFILIAL     = P_CDFILIAL)      AND
               (CA.CDSERVFILI   = CO.CDSERVCPLASE) AND
               (CA.DTCARDAPIO BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE) AND*/
               NOT EXISTS (SELECT * FROM CARDACLI
                           WHERE CDFILIAL   = CA.CDFILIAL AND
                                 NRCARDAPIO = CA.NRCARDAPIO)
            GROUP BY
               DT.DTDATA, SE.CDSERVFILI, RPAD(SE.HRINICATEN, 4, 0), CA.NRCARDAPIO, 5, 6
            UNION ALL
            SELECT
               DT.DTDATA,
               SE.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               'PPPPPPPPPP' NRCARDAPIO,
               CC.CDCLIENTE,
               CC.CDDESTFILI,
               SUM(CC.QTCOMESTICLI) QTCOMESTICAR,
               SUM(0) QTESTITURNO1,
               SUM(0) QTESTITURNO2,
               SUM(0) QTESTITURNO3,
               SUM(0) QTESTITURNO4
            FROM
               CARDACLI CC,
               DATA DT,
               SERVICO SE,
               /*CONSPLANSE CO,*/
               CARDADIA CA
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)    AND
               (CO.CDFILICPLASE = P_CDFILIAL)      AND
               (SE.CDFILIAL     = P_CDFILIAL)      AND
               (SE.CDSERVFILI   = CO.CDSERVCPLASE) AND
               (DT.DTDATA       = CA.DTCARDAPIO)   AND
               (CA.CDFILIAL     = P_CDFILIAL)      AND
               (CA.CDSERVFILI   = CO.CDSERVCPLASE) AND
               (CA.DTCARDAPIO BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE) AND
               (CC.CDFILIAL     = CA.CDFILIAL)     AND*/
               (SE.CDFILIAL     = P_CDFILIAL)     AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (CA.CDFILIAL     = SE.CDFILIAL)    AND
               (CA.CDSERVFILI   = SE.CDSERVFILI)  AND
               (CA.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               (DT.DTDATA       = CA.DTCARDAPIO)  AND
               (CC.CDFILIAL     = CA.CDFILIAL)    AND
               (CC.NRCARDAPIO   = CA.NRCARDAPIO)
            GROUP BY
               DT.DTDATA, SE.CDSERVFILI, RPAD(SE.HRINICATEN, 4, 0), CA.NRCARDAPIO, CC.CDCLIENTE, CC.CDDESTFILI
            UNION ALL
            SELECT
               DT.DTDATA,
               SE.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               NULL NRCARDAPIO,
               'XXX' CDCLIENTE,
               'XXX' CDDESTFILI,
               0 QTCOMESTICAR,
               0 QTESTITURNO1,
               0 QTESTITURNO2,
               0 QTESTITURNO3,
               0 QTESTITURNO4
            FROM
               DATA DT,
               SERVICO SE/*,
               CONSPLANSE CO*/
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)    AND
               (CO.CDFILICPLASE = P_CDFILIAL)      AND
               (SE.CDFILIAL = P_CDFILIAL)                              AND
               (SE.CDSERVFILI = CO.CDSERVCPLASE)                       AND
               (DT.DTDATA BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE) AND*/
               (SE.CDFILIAL     = P_CDFILIAL)     AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (DT.DTDATA BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               (NOT EXISTS (SELECT
                               CA.CDFILIAL
                            FROM
                               CARDADIA CA
                            WHERE
                               (CA.CDFILIAL   = SE.CDFILIAL)   AND
                               (CA.CDSERVFILI = SE.CDSERVFILI) AND
                               (CA.DTCARDAPIO = DT.DTDATA)))
            ORDER BY 1, 2, 3, 4, 5, 6;



      ELSIF (P_IDTIPOCONS = 'E') THEN 

         /* - Consumo por servi�o 'FIXO SOMENTE PARA OS PRODUTOS REPLICAREM POR PONTO DE ENTREGA' - proporcional aos comensais ----- */
         OPEN C_CONSPLAN FOR
            SELECT
               DT.DTDATA,
               SE.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               'FFFFFFFFFF' NRCARDAPIO,
               CC.CDCLIENTE,
               CC.CDDESTFILI,
               SUM(CC.QTCOMESTICLI) QTCOMESTICAR,
               SUM(0) QTESTITURNO1,
               SUM(0) QTESTITURNO2,
               SUM(0) QTESTITURNO3,
               SUM(0) QTESTITURNO4
            FROM
               CARDACLI CC,
               DATA DT,
               SERVICO SE,
               CARDADIA CA
            WHERE
               (SE.CDFILIAL     = P_CDFILIAL)     AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (CA.CDFILIAL     = SE.CDFILIAL)    AND
               (CA.CDSERVFILI   = SE.CDSERVFILI)  AND
               (CA.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               (DT.DTDATA       = CA.DTCARDAPIO)  AND
               (CC.CDFILIAL     = CA.CDFILIAL)    AND
               (CC.NRCARDAPIO   = CA.NRCARDAPIO)
            GROUP BY
               DT.DTDATA, SE.CDSERVFILI, RPAD(SE.HRINICATEN, 4, 0), CA.NRCARDAPIO, CC.CDCLIENTE, CC.CDDESTFILI;

      ELSE
      /* - Consumo por servi�o 'FIXO' - n�o considera os comensais ---------- */
         OPEN C_CONSPLAN FOR
            SELECT
               DT.DTDATA,
               SE.CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0) AS HRINICATEN,
               NULL NRCARDAPIO,
               'XXX' CDCLIENTE,
               'XXX' CDDESTFILI,
               0 QTCOMESTICAR,
               0 QTESTITURNO1,
               0 QTESTITURNO2,
               0 QTESTITURNO3,
               0 QTESTITURNO4
            FROM
               DATA DT,
               SERVICO SE/*,
               CONSPLANSE CO*/
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)    AND
               (CO.CDFILICPLASE = P_CDFILIAL)      AND
               (SE.CDFILIAL     = P_CDFILIAL)      AND
               (SE.CDSERVFILI   = CO.CDSERVCPLASE) AND
               (DT.DTDATA BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE)*/
               (SE.CDFILIAL     = P_CDFILIAL) AND
               (INSTR(P_SERVICOS,SE.CDSERVFILI) <> 0 OR (V_TODOS_SERV = 'T')) AND
               (DT.DTDATA BETWEEN P_DTINICIAL AND P_DTFINAL)
            ORDER BY
               DTDATA,
               CDSERVFILI,
               RPAD(SE.HRINICATEN, 4, 0);
      END IF;
   END; /* OPEN_CONSPLAN_CURSOR */
   PROCEDURE OPEN_COMPCARD_CURSOR (C_COMPCARD   IN OUT COMPCARD_CURTYPE,
                                   P_CDFILIAL   IN FILIAL.CDFILIAL%TYPE,
                                   P_NRCARDAPIO IN CARDADIA.NRCARDAPIO%TYPE) IS
   BEGIN
      OPEN C_COMPCARD FOR
         SELECT
            CC.CDFILAUXPRAT,
            CC.CDPRATO,
            SUM(CC.VRPECONSCARD) AS VRPECONSCARD,
            /*CC.NRSEQPRATCAR,*/
            CC.CDTIPOPRAT,
            ST.CDSETORCOZ
         FROM
            COMPCARD CC, SETOTIPOPRAT ST
         WHERE
            (CC.CDFILIAL   = P_CDFILIAL)      AND
            (CC.NRCARDAPIO = P_NRCARDAPIO)    AND
            (CC.CDFILIAL   = ST.CDFILIAL (+)) AND
            (CC.CDTIPOPRAT = ST.CDTIPOPRAT (+))
         GROUP BY
            CC.CDFILAUXPRAT,
            CC.CDPRATO,
            /*CC.NRSEQPRATCAR,*/
            CC.CDTIPOPRAT,
            ST.CDSETORCOZ;
   END; /* OPEN_COMPCARD_CURSOR */
   PROCEDURE OPEN_PRODPRAT_CURSOR (C_PRODPRAT     IN OUT PRODPRAT_CURTYPE,
                                   P_CDFILIAL     IN FILIAL.CDFILIAL%TYPE,
                                   P_CDSERVFILI   IN SERVICO.CDSERVFILI%TYPE,
                                   P_CDFILAUXPRAT IN PRATO.CDFILAUXPRAT%TYPE,
                                   P_CDPRATO      IN PRATO.CDPRATO%TYPE,
                                   P_QTPRATO      IN NUMBER,
                                   P_CDPRODUTOINI IN VARCHAR2,
                                   P_CDPRODUTOFIN IN VARCHAR2,
                                   P_IDUTILFATCOR IN PARAMPLANEJA.IDUTILFATCOR%TYPE,
                                   P_IDUTILFATCOC IN PARAMPLANEJA.IDUTILFATCOC%TYPE,
                                   P_IDTIPCALCFAT IN PARAMPLANEJA.IDTIPCALCFAT%TYPE) IS
   BEGIN
      OPEN C_PRODPRAT FOR
         SELECT
            R.CDPRODUTO,
            R.QTPRODUTO,
            R.SGUNIDADE,
            R.VRPEFATCOC,
            R.VRPEFATCOR,
            R.HRTEMPPREP,
            R.CDAPRERECE,
            R.QTCOMPLPROD,
            R.QTBRUTAUNR,
            R.IDTPREQPROD
         FROM PRODUTO,
            (SELECT
                RECESERV.CDPRODUTO,
                RECESERV.QTBRUTASE AS QTPRODUTO,
                RECESERV.SGUNIDADE,
                RECESERV.VRPEFATCOCSE AS VRPEFATCOC,
                RECESERV.VRPEFATCORSE AS VRPEFATCOR,
                RECESERV.HRTEMPPREPSE AS HRTEMPPREP,
                RECESERV.CDAPRERECE,
                RECESERV.QTCOMPLPRODSERV AS QTCOMPLPROD,
                RECESERV.QTBRUTASEUNR AS QTBRUTAUNR,
                RECESERV.IDTPREQPRODSERV AS IDTPREQPROD
             FROM
                RECESERV
             WHERE
                (RECESERV.CDFILAUXPRAT = P_CDFILAUXPRAT)    AND
                (RECESERV.CDPRATO      = P_CDPRATO)         AND
                (RECESERV.CDFILIAL     = P_CDFILIAL)        AND
                (RECESERV.CDSERVFILI   = P_CDSERVFILI)
             UNION ALL
             SELECT
                RECEFILI.CDPRODUTO,
                RECEFILI.QTBRUTALO AS QTPRODUTO,
                RECEFILI.SGUNIDADE,
                RECEFILI.VRPEFATCOCLO AS VRPEFATCOC,
                RECEFILI.VRPEFATCORLO AS VRPEFATCOR,
                RECEFILI.HRTEMPPREPLO AS HRTEMPPREP,
                RECEFILI.CDAPRERECE,
                RECEFILI.QTCOMPLPRODFILI AS QTCOMPLPROD,
                RECEFILI.QTBRUTALOUNR AS QTBRUTAUNR,
                RECEFILI.IDTPREQPRODFILI AS IDTPREQPROD
             FROM
                RECEFILI
             WHERE
                (RECEFILI.CDFILAUXPRAT = P_CDFILAUXPRAT)    AND
                (RECEFILI.CDPRATO      = P_CDPRATO)         AND
                (RECEFILI.CDFILIAL     = P_CDFILIAL)        AND
                 NOT EXISTS (SELECT
                                CDPRATO
                             FROM
                                RECESERV
                             WHERE
                                (CDFILAUXPRAT = P_CDFILAUXPRAT) AND
                                (CDPRATO      = P_CDPRATO)      AND
                                (CDFILIAL     = P_CDFILIAL)     AND
                                (CDSERVFILI   = P_CDSERVFILI)
                             )
             UNION ALL
             SELECT
                RECEPADR.CDPRODUTO,
                RECEPADR.QTBRUTAPR AS QTPRODUTO,
                RECEPADR.SGUNIDADE,
                RECEPADR.VRPEFATCOCPR AS VRPEFATCOC,
                RECEPADR.VRPEFATCORPR AS VRPEFATCOR,
                RECEPADR.HRTEMPPREPPR AS HRTEMPPREP,
                RECEPADR.CDAPRERECE,
                RECEPADR.QTCOMPLPRODPADR AS QTCOMPLPROD,
                RECEPADR.QTBRUTAPRUNR AS QTBRUTAUNR,
                RECEPADR.IDTPREQPRODPADR AS IDTPREQPROD
             FROM
                RECEPADR
             WHERE
                (RECEPADR.CDFILAUXPRAT = P_CDFILAUXPRAT)    AND
                (RECEPADR.CDPRATO      = P_CDPRATO)         AND
                 NOT EXISTS (SELECT
                                CDPRATO
                             FROM
                                RECEFILI
                             WHERE
                                (CDFILAUXPRAT = P_CDFILAUXPRAT) AND
                                (CDPRATO      = P_CDPRATO)      AND
                                (CDFILIAL     = P_CDFILIAL)
                             ) AND
                 NOT EXISTS (SELECT
                                CDPRATO
                             FROM
                                RECESERV
                             WHERE
                                (CDFILAUXPRAT = P_CDFILAUXPRAT) AND
                                (CDPRATO      = P_CDPRATO)      AND
                                (CDFILIAL     = P_CDFILIAL)     AND
                                (CDSERVFILI   = P_CDSERVFILI))
         ) R
         WHERE
            (R.CDPRODUTO = PRODUTO.CDPRODUTO) AND
            (PRODUTO.CDARVPROD BETWEEN P_CDPRODUTOINI AND P_CDPRODUTOFIN);
   END; /* OPEN_PRODPRAT_CURSOR */
   PROCEDURE OPEN_CONSSERV_CURSOR (C_CONSSERV     IN OUT CONSSERV_CURTYPE,
                                   P_CDFILIAL     IN CONSSERV.CDFILIAL%TYPE,
                                   P_CDSERVFILI   IN CONSSERV.CDSERVFILI%TYPE,
                                   P_DTCONSSERV   IN CONSSERV.DTCONSSERV%TYPE,
                                   P_FERIADO      IN NUMBER,
                                   P_CDPRODUTOINI IN VARCHAR2,
                                   P_CDPRODUTOFIN IN VARCHAR2,
                                   P_IDTPCONSSERV IN VARCHAR2) IS
   BEGIN
      OPEN C_CONSSERV FOR
         SELECT
            C.CDFILIAL,
            C.CDSERVFILI,
            C.DTCONSSERV,
            C.CDPRODUTO,
            C.QTCONSSERV,
            C.IDQTDECONS,
            C.IDPRODCONSFPE,
            C.IDTPREQPROD,
            C.QTUTILCENTRAL
          FROM PRODUTO,
              (SELECT
                  CONSSERV.CDFILIAL,
                  CONSSERV.CDSERVFILI,
                  CONSSERV.DTCONSSERV,
                  CONSSERV.CDPRODUTO,
                  CONSSERV.QTCONSSERV,
                  CONSSERV.IDQTDECONS,
                  CONSSERV.IDPRODCONSFPE,
                  CONSSERV.IDTPREQPROD,
                  DECODE(CONSSERV.IDTPREQPROD, 'D', CONSSERV.QTUTILCENTRAL, 0) AS QTUTILCENTRAL
               FROM
                  CONSSERV
               WHERE
                  CONSSERV.CDFILIAL   = P_CDFILIAL        AND
                  CONSSERV.CDSERVFILI = P_CDSERVFILI      AND
                  CONSSERV.DTCONSSERV = P_DTCONSSERV      AND
                  CONSSERV.IDTIPOCONS = '9'               AND
                  CONSSERV.IDQTDECONS = 'F'               AND
                  NOT EXISTS (SELECT
                                 CDFILIAL
                              FROM
                                 DIASEMCONS
                              WHERE
                                 CDFILIAL    = P_CDFILIAL   AND
                                 CDSERVFILI  = P_CDSERVFILI AND
                                 DTSCONSSERV = P_DTCONSSERV AND
                                 IDTCONSSERV IN ('F', 'A'))
               UNION ALL
               SELECT
                  CONSSERV.CDFILIAL,
                  CONSSERV.CDSERVFILI,
                  CONSSERV.DTCONSSERV,
                  CONSSERV.CDPRODUTO,
                  CONSSERV.QTCONSSERV,
                  CONSSERV.IDQTDECONS,
                  CONSSERV.IDPRODCONSFPE,
                  CONSSERV.IDTPREQPROD,
                  DECODE(CONSSERV.IDTPREQPROD, 'D', CONSSERV.QTUTILCENTRAL, 0) AS QTUTILCENTRAL
               FROM
                  CONSSERV
               WHERE
                  CONSSERV.CDFILIAL   = P_CDFILIAL        AND
                  CONSSERV.CDSERVFILI = P_CDSERVFILI      AND
                  CONSSERV.DTCONSSERV = P_DTCONSSERV      AND
                  CONSSERV.IDTIPOCONS = '9'               AND
                  CONSSERV.IDQTDECONS = 'P'               AND
                  NOT EXISTS (SELECT
                                 CDFILIAL
                              FROM
                                 DIASEMCONS
                              WHERE
                                 CDFILIAL    = P_CDFILIAL   AND
                                 CDSERVFILI  = P_CDSERVFILI AND
                                 DTSCONSSERV = P_DTCONSSERV AND
                                 IDTCONSSERV IN ('P', 'A'))
               UNION ALL
               SELECT
                  CONSSERV.CDFILIAL,
                  CONSSERV.CDSERVFILI,
                  CONSSERV.DTCONSSERV,
                  CONSSERV.CDPRODUTO,
                  CONSSERV.QTCONSSERV,
                  CONSSERV.IDQTDECONS,
                  CONSSERV.IDPRODCONSFPE,
                  CONSSERV.IDTPREQPROD,
                  DECODE(CONSSERV.IDTPREQPROD, 'D', CONSSERV.QTUTILCENTRAL, 0) AS QTUTILCENTRAL
               FROM
                  CONSSERV
               WHERE
                  CONSSERV.CDFILIAL   = P_CDFILIAL   AND
                  CONSSERV.CDSERVFILI = P_CDSERVFILI AND
                  DTCONSSERV IN (SELECT MAX(DTCONSSERV)
                                 FROM CONSSERV
                                 WHERE CDFILIAL    = P_CDFILIAL    AND
                                       CDSERVFILI  = P_CDSERVFILI  AND
                                       DTCONSSERV <= P_DTCONSSERV  AND
                                       IDTIPOCONS <> '9')          AND
                  ((IDTIPOCONS = TO_CHAR(P_DTCONSSERV, 'D') AND P_FERIADO = 0) OR
                   (IDTIPOCONS = '8' AND P_FERIADO <> 0)) AND
                  IDQTDECONS = 'F'                        AND
                  NOT EXISTS (SELECT
                                 CDFILIAL
                              FROM
                                 DIASEMCONS
                              WHERE
                                 CDFILIAL    = P_CDFILIAL   AND
                                 CDSERVFILI  = P_CDSERVFILI AND
                                 DTSCONSSERV = P_DTCONSSERV AND
                                 IDTCONSSERV IN ('F', 'A'))  AND
                 NOT EXISTS (SELECT
                                CDFILIAL
                             FROM
                                CONSSERV
                             WHERE
                                CDFILIAL   = P_CDFILIAL   AND
                                CDSERVFILI = P_CDSERVFILI AND
                                DTCONSSERV = P_DTCONSSERV AND
                                IDTIPOCONS = '9'          AND
                                IDQTDECONS = 'F')
               UNION ALL
               SELECT
                  CONSSERV.CDFILIAL,
                  CONSSERV.CDSERVFILI,
                  CONSSERV.DTCONSSERV,
                  CONSSERV.CDPRODUTO,
                  CONSSERV.QTCONSSERV,
                  CONSSERV.IDQTDECONS,
                  CONSSERV.IDPRODCONSFPE,
                  CONSSERV.IDTPREQPROD,
                  DECODE(CONSSERV.IDTPREQPROD, 'D', CONSSERV.QTUTILCENTRAL, 0) AS QTUTILCENTRAL
               FROM
                  CONSSERV
               WHERE
                  CONSSERV.CDFILIAL   = P_CDFILIAL        AND
                  CONSSERV.CDSERVFILI = P_CDSERVFILI      AND
                  DTCONSSERV IN (SELECT MAX(DTCONSSERV)
                                 FROM CONSSERV
                                 WHERE CDFILIAL    = P_CDFILIAL    AND
                                       CDSERVFILI  = P_CDSERVFILI  AND
                                       DTCONSSERV <= P_DTCONSSERV  AND
                                       IDTIPOCONS <> '9')          AND
                  ((IDTIPOCONS = TO_CHAR(P_DTCONSSERV, 'D') AND P_FERIADO = 0) OR
                   (IDTIPOCONS = '8' AND P_FERIADO <> 0)) AND
                  IDQTDECONS = 'P'                        AND
                  NOT EXISTS (SELECT
                                 CDFILIAL
                              FROM
                                 DIASEMCONS
                              WHERE
                                 CDFILIAL    = P_CDFILIAL   AND
                                 CDSERVFILI  = P_CDSERVFILI AND
                                 DTSCONSSERV = P_DTCONSSERV AND
                                 IDTCONSSERV IN ('P', 'A')) AND
                  NOT EXISTS (SELECT
                                 CDFILIAL
                              FROM
                                 CONSSERV
                              WHERE
                                 CDFILIAL   = P_CDFILIAL   AND
                                 CDSERVFILI = P_CDSERVFILI AND
                                 DTCONSSERV = P_DTCONSSERV AND
                                 IDTIPOCONS = '9'          AND
                                 IDQTDECONS = 'P')
            ) C
            WHERE
               C.CDPRODUTO = PRODUTO.CDPRODUTO AND
               PRODUTO.CDARVPROD BETWEEN P_CDPRODUTOINI AND P_CDPRODUTOFIN AND
               ((IDPRODCONSFPE = 'S' AND P_IDTPCONSSERV = 'E') OR (IDPRODCONSFPE <> 'S' AND P_IDTPCONSSERV <> 'E'))
        ORDER BY 6, 4;
   END; /* OPEN_CONSSERV_CURSOR */
   PROCEDURE OPEN_ARREDOND_CURSOR (C_ARREDOND    IN OUT ARREDOND_CURTYPE,
                                   P_TIPOCURS    IN VARCHAR2,
                                   P_CDFILIAL    IN FILIAL.CDFILIAL%TYPE,
                                   P_CDSERVICOS  IN CLOB,
                                   P_DTINICIAL   IN DATE,
                                   P_DTFINAL     IN DATE,
                                   P_NRREQUCONS  IN VARCHAR2,
                                   P_NRCONSPLAN  IN VARCHAR2) IS
   BEGIN
      IF P_TIPOCURS = 'P' THEN
         /* - Previs�o de retirada (PREVRETI) para a data e servi�o --------- */
         OPEN C_ARREDOND FOR
            SELECT
             PV.NRREQUEXTR, PV.CDFILIAL, PV.CDSERVFILI, PV.DTPREVRETI, PV.IDPREVRETI,
             NULL, NULL, PV.CDPRODUTO, PV.DTLIMITERETI, PV.HRLIMITERETI,
             NULL, 'XXX' CDCLIENTE, 'XXX' CDDESTFILI,
             PR.VRARREDREQ, PR.VRARREDCMP,
             PV.QTPREVRETI, PV.QTPRERETT1, PV.QTPRERETT2, PV.QTPRERETT3, PV.QTPRERETT4
          FROM
             /*CONSPLANSE CO,*/
             CONSPLAN CO,
             PRODUTO PR,
             PREVRETI PV
          WHERE/*
             (CO.NRCONSPLANSE = P_NRCONSPLAN)    AND
             (CO.CDFILICPLASE = P_CDFILIAL)      AND
             (PV.CDFILIAL     = P_CDFILIAL)      AND
             (PV.CDSERVFILI   = CO.CDSERVCPLASE) AND*/
             (CO.CDFILICPLA = P_CDFILIAL)    AND
             (CO.NRCONSPLAN = P_NRCONSPLAN)  AND
             (PV.CDFILIAL   = CO.CDFILICPLA) AND
             (PV.CDSERVFILI = CO.CDSERVCPLA) AND
             (PV.NRREQUEXTR = P_NRREQUCONS)  AND
             (PR.CDPRODUTO  = PV.CDPRODUTO)  AND
             /*(PV.DTPREVRETI BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE);*/
             (PV.DTPREVRETI BETWEEN P_DTINICIAL AND P_DTFINAL);
      ELSE
         /* - Consumo planejdo (CONSPLAN) para a data e servi�o ------------- */
         OPEN C_ARREDOND FOR
            SELECT
               CP.NRCONSPLAN, CP.CDFILICPLA, CP.CDSERVCPLA, CP.DTCONSPLAN, CP.IDCONSPLAN,
               CP.CDFAPRATCPLA, CP.CDPRATCONSPL, CP.CDPRODCPLA,
               CP.DTLIMITECPLA, CP.HRLIMITECPLA,
               CP.CDTPPRATCPLA, CP.CDCLIECPLA, CP.CDDESTFICPLA, PR.VRARREDREQ, PR.VRARREDCMP,
               SUM(CP.QTCONSPLAN) QTCONSPLAN, SUM(CP.QTCONSPLANT1) QTCONSPLANT1,
               SUM(CP.QTCONSPLANT2) QTCONSPLANT2, SUM(CP.QTCONSPLANT3) QTCONSPLANT3, SUM(CP.QTCONSPLANT4) QTCONSPLANT4
            FROM
               /*CONSPLANSE CO,*/
               PRODUTO PR,
               CONSPLAN CP
            WHERE/*
               (CO.NRCONSPLANSE = P_NRCONSPLAN)  AND
               (CO.CDFILICPLASE = P_CDFILIAL)    AND*/
               (CP.CDFILICPLA = P_CDFILIAL)   AND
               (CP.NRCONSPLAN = P_NRREQUCONS) AND
               (CP.DTCONSPLAN BETWEEN P_DTINICIAL AND P_DTFINAL) AND
               /*(CP.CDSERVCPLA = CO.CDSERVCPLASE) AND*/
               (PR.CDPRODUTO  = CP.CDPRODCPLA)
               /*(CP.DTCONSPLAN BETWEEN CO.DTINICCPLASE AND CO.DTFINACPLASE)*/
            GROUP BY
               CP.NRCONSPLAN, CP.CDFILICPLA, CP.CDSERVCPLA, CP.DTCONSPLAN,
               CP.IDCONSPLAN, CP.CDCLIECPLA, CP.CDDESTFICPLA, CP.CDFAPRATCPLA,
               CP.CDPRATCONSPL, CP.CDPRODCPLA, CP.DTLIMITECPLA, CP.HRLIMITECPLA,
               CP.CDTPPRATCPLA, PR.VRARREDREQ, PR.VRARREDCMP;
      END IF;
   END; /* OPEN_ARREDOND_CURSOR */
END UTIL_CONSPLAN_PLA;

/




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