CREATE OR REPLACE PROCEDURE SERVICOS_TRANSPORTADOS
   (P_CDFILIAL   IN VARCHAR2, 
    P_SERVICOS   IN VARCHAR2,
    P_DTINICIAL  IN DATE,
    P_DTFINAL    IN DATE,
    P_CDOPERADOR IN VARCHAR2, 
    P_DTHRCALCST IN DATE) IS

   V_QTCOMESTIPRAT NUMBER;
   V_QTPADRAO      NUMBER;       /*QUANTIDADE POR PESSOA*/
   V_QTCONTAI      NUMBER;       /*QUANTIDADE POR CONTAINER*/
   V_QTTOTAPRATST  NUMBER;
   V_QTTOTACONTST  NUMBER;       /*TOTAL DE CONTAINERS*/
   V_QTPARCPRATST  NUMBER;       /*QUANTIDADE DOS CONTAINERS INCOMPLETOS*/
   V_QTCONTCOMPST  NUMBER;       /*QUANTIDADE DE CONTAINERS COMPLETOS*/
   V_QTCONTINCOST  NUMBER;       /*QUANTIDADE DE CONTAINERS INCOMPLETOS*/
   V_QTSUMPERPRAT  NUMBER;
   V_NRSERVTRAN    VARCHAR2(10);
   V_SEQUENCIAL    VARCHAR2(10);
   V_PRATO         VARCHAR2(10);
   V_QTCOMECLIST   NUMBER;
   V_DIASEMANA     VARCHAR2(1);
   V_QTDIAFERI     NUMBER;
   V_QTAMOSTRAUN   NUMBER;       /*QUANTIDADE DE AMOSTRAR POR UNIDADE*/
   V_PERCENTUAL    NUMBER;       /*PERCENTUAL MINIMO*/
   V_RECEITA       VARCHAR(2);   /*RECEITA UTILIZADA*/

   V_QTTOTAPRATSTCAMOS  NUMBER;  /*QUANTIDADE TOTAL com amostras*/
   V_QTTOTACONTSTCAMOS  NUMBER;  /*TOTAL DE CONTAINERS com amostras*/
   V_QTPARCPRATSTCAMOS  NUMBER;  /*QUANTIDADE DOS CONTAINERS INCOMPLETOS com amostras*/
   V_QTCONTCOMPSTCAMOS  NUMBER;  /*QUANTIDADE DE CONTAINERS COMPLETOS com amostras*/
   V_QTCONTINCOSTCAMOS  NUMBER;  /*QUANTIDADE DE CONTAINERS INCOMPLETOS com amostras*/

BEGIN
   FOR C_SERVICO IN
      (SELECT 
          SE.CDSERVFILI 
       FROM 
          SERVICO SE, 
          (SELECT SUBSTR(CDLISTA, 5, 5) AS CDSERVFILI, 
                  SUBSTR(CDLISTA, 1, 4) AS CDFILIAL
             FROM LISTA 
			   WHERE NRLISTA = P_SERVICOS) LSER 
       WHERE
          (SE.CDFILIAL     = P_CDFILIAL) AND
          (LSER.CDSERVFILI = SE.CDSERVFILI) AND
          (LSER.CDFILIAL   = SE.CDFILIAL))
   LOOP
      DELETE SERVTRAN
      WHERE
         CDFILIAL   = P_CDFILIAL AND
         CDSERVFILI = C_SERVICO.CDSERVFILI AND
         DTSERVTRAN BETWEEN P_DTINICIAL AND P_DTFINAL;

      /* REGISTRA O CALCULO NA TABELA DE LOG */
      INSERT INTO LOGSERVTRAN
         (CDFILIAL,
          CDOPERADOR,
          DTHRCALCST,
          CDSERVFILI,
          DTINICIALST,
          DTFINALST)
      VALUES
         (P_CDFILIAL,
          P_CDOPERADOR,
          P_DTHRCALCST,
          C_SERVICO.CDSERVFILI,
          P_DTINICIAL,
          P_DTFINAL);

      /* GRAVA A TABELA SERVTRAN E ETIQUETRAN DE ACORDO COM OS CARDAPIOS */
      FOR C_CARDAPIO IN
         (SELECT
             CO.CDFILIAL,
             CO.NRCARDAPIO,
             CO.CDFILAUXPRAT,
             CO.CDPRATO,
             SUM(CO.VRPECONSCARD) AS VRPECONSCARD,
             CA.CDSERVFILI,
             CA.DTCARDAPIO,
             SUM(CA.QTCOMESTICAR) AS QTCOMESTICAR,
             NVL(CA.CDDIETPAD,'XXXX') AS CDDIETPAD,
             NVL(PT.VRARREDTRANS, 0) AS VRARREDTRANS,
             PT.SGUNIDADE
          FROM
             COMPCARD CO,
             CARDADIA CA,
             PRATO PT
          WHERE
             (CA.CDFILIAL   = P_CDFILIAL)                      AND
             (CA.CDSERVFILI = C_SERVICO.CDSERVFILI)            AND
             (CA.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL) AND
             (CO.CDFILIAL   = CA.CDFILIAL)                     AND
             (CO.NRCARDAPIO = CA.NRCARDAPIO)                   AND
             (CO.CDPRATO    = PT.CDPRATO)                      AND
             (CO.CDFILAUXPRAT = PT.CDFILAUXPRAT)
GROUP BY CO.CDFILIAL, CO.NRCARDAPIO, CO.CDFILAUXPRAT, CO.CDPRATO, CA.CDSERVFILI, CA.DTCARDAPIO, NVL(CA.CDDIETPAD,'XXXX'), NVL(PT.VRARREDTRANS, 0), PT.SGUNIDADE)
      LOOP
         V_DIASEMANA := TO_CHAR(C_CARDAPIO.DTCARDAPIO, 'D');
         V_QTDIAFERI := 0;

         SELECT
            COUNT(*) INTO V_QTDIAFERI
         FROM
            FERIFILI
         WHERE
            CDFILIAL   = P_CDFILIAL AND
            DTFERIFILI = C_CARDAPIO.DTCARDAPIO;

         IF (V_QTDIAFERI = 1) THEN
            V_DIASEMANA := 8;
         END IF;

         FOR C_DESTINO IN
            (SELECT
                D.CDCLIENTE,
                D.CDDESTFILI,
                DECODE(V_DIASEMANA, '1', D.QTCOESTCSDOM,
                                    '2', D.QTCOESTCSSEG,
                                    '3', D.QTCOESTCSTER,
                                    '4', D.QTCOESTCSQUA,
                                    '5', D.QTCOESTCSQUI,
                                    '6', D.QTCOESTCSSEX,
                                    '7', D.QTCOESTCSSAB,
                                    '8', D.QTCOESTCSFER) AS V_QTCOEST

             FROM
                DESTSRVCLI D
             WHERE
                D.CDFILIAL   = P_CDFILIAL  AND
                D.CDSERVFILI = C_SERVICO.CDSERVFILI AND
                NOT EXISTS (SELECT
                     C.CDFILIAL
                  FROM
                     CARDACLI CC,
                     CARDADIA C
                  WHERE
                     C.CDFILIAL   = P_CDFILIAL            AND
                     C.CDSERVFILI = C_SERVICO.CDSERVFILI  AND
                     C.DTCARDAPIO = C_CARDAPIO.DTCARDAPIO AND
                     C.CDFILIAL   = CC.CDFILIAL AND
                     C.NRCARDAPIO = CC.NRCARDAPIO)

             UNION ALL

             SELECT
                CC.CDCLIENTE,
                CC.CDDESTFILI,
                CC.QTCOMESTICLI AS V_QTCOEST
             FROM
                CARDACLI CC,
                CARDADIA C
             WHERE
                C.CDFILIAL   = P_CDFILIAL            AND
                C.CDSERVFILI = C_SERVICO.CDSERVFILI  AND
                C.DTCARDAPIO = C_CARDAPIO.DTCARDAPIO AND
                C.CDFILIAL   = CC.CDFILIAL AND
                C.NRCARDAPIO = CC.NRCARDAPIO
             ORDER BY 1, 2)
         LOOP
            BEGIN
               SELECT
                  S.CDPRATO
               INTO
                  V_PRATO
               FROM
                  SERVTRAN S
               WHERE
                  (S.CDFILIAL     = P_CDFILIAL)              AND
                  (S.CDSERVFILI   = C_CARDAPIO.CDSERVFILI)   AND
                  (S.DTSERVTRAN   = C_CARDAPIO.DTCARDAPIO)   AND
                  (S.CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT) AND
                  (S.CDPRATO      = C_CARDAPIO.CDPRATO)      AND
                  (S.CDDESTFILI   = C_DESTINO.CDDESTFILI)    AND
                  (S.CDCLIENTE    = C_DESTINO.CDCLIENTE);
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_QTCOMECLIST   := C_DESTINO.V_QTCOEST;
                  V_QTCOMESTIPRAT := ROUND((C_CARDAPIO.VRPECONSCARD / 100 * C_DESTINO.V_QTCOEST));

                  BEGIN
                     SELECT
                        RECEITA
                     INTO
                        V_RECEITA
                     FROM
                       (SELECT
                           'RS' AS RECEITA /*RECEITA POR SERVICO*/
                        FROM
                           RECESERV
                        WHERE
                           CDFILIAL     = C_CARDAPIO.CDFILIAL     AND
                           CDSERVFILI   = C_CARDAPIO.CDSERVFILI   AND
                           CDPRATO      = C_CARDAPIO.CDPRATO      AND
                           CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT

                        UNION

                        SELECT
                           'RL' AS RECEITA /*RECEITA LOCAL*/
                        FROM
                           RECEFILI RF
                        WHERE
                           RF.CDFILIAL     = C_CARDAPIO.CDFILIAL     AND
                           RF.CDPRATO      = C_CARDAPIO.CDPRATO      AND
                           RF.CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT AND
                           NOT EXISTS (SELECT
                                          RS.CDPRATO
                                       FROM
                                          RECESERV RS
                                       WHERE
                                          RS.CDFILIAL     = RF.CDFILIAL     AND
                                          RS.CDSERVFILI   = C_CARDAPIO.CDSERVFILI AND
                                          RS.CDPRATO      = RF.CDPRATO      AND
                                          RS.CDFILAUXPRAT = RF.CDFILAUXPRAT)

                        UNION

                        SELECT
                           'RP' AS RECEITA /*RECEITA PADRAO*/
                        FROM
                           RECEPADR RP
                        WHERE
                           RP.CDPRATO      = C_CARDAPIO.CDPRATO AND
                           RP.CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT AND
                           NOT EXISTS (SELECT
                                          RS.CDPRATO
                                       FROM
                                          RECESERV RS
                                       WHERE
                                          RS.CDFILIAL     = C_CARDAPIO.CDFILIAL   AND
                                          RS.CDSERVFILI   = C_CARDAPIO.CDSERVFILI AND
                                          RS.CDPRATO      = RP.CDPRATO            AND
                                          RS.CDFILAUXPRAT = RP.CDFILAUXPRAT)      AND
                           NOT EXISTS (SELECT
                                          RF.CDPRATO
                                       FROM
                                          RECEFILI RF
                                       WHERE
                                          RF.CDFILIAL     = C_CARDAPIO.CDFILIAL AND
                                          RF.CDPRATO      = RP.CDPRATO      AND
                                          RF.CDFILAUXPRAT = RP.CDFILAUXPRAT))
                     WHERE ROWNUM = 1;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN NULL;
                  END;

                  V_QTPADRAO    := NULL;
                  V_QTCONTAI    := NULL;
                  V_QTAMOSTRAUN := NULL;
                  V_PERCENTUAL  := NULL;

                  IF V_RECEITA = 'RS' THEN
                     SELECT
                        MAX(QTPADRAOSERV),
                        MAX(QTCONTAISERV),
                        MAX(QTAMOSTRSERV),
                        MAX(VRPEMINSERV)
                     INTO
                        V_QTPADRAO,
                        V_QTCONTAI,
                        V_QTAMOSTRAUN,
                        V_PERCENTUAL
                     FROM
                        PRATSERV
                     WHERE
                        CDFILIAL     = C_CARDAPIO.CDFILIAL   AND
                        CDSERVFILI   = C_CARDAPIO.CDSERVFILI AND
                        CDPRATO      = C_CARDAPIO.CDPRATO    AND
                        CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT;
                  ELSE
                     IF V_RECEITA = 'RL' THEN
                        SELECT
                           MAX(QTPADRAOFILI),
                           MAX(QTCONTAIFILI),
                           MAX(QTAMOSTRFILI),
                           MAX(VRPEMINFILI)
                        INTO
                           V_QTPADRAO,
                           V_QTCONTAI,
                           V_QTAMOSTRAUN,
                           V_PERCENTUAL
                        FROM
                           PRATFILI
                        WHERE
                           CDFILIAL     = C_CARDAPIO.CDFILIAL AND
                           CDPRATO      = C_CARDAPIO.CDPRATO  AND
                           CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT;
                     ELSE
                        IF V_RECEITA = 'RP' THEN
                           SELECT
                              MAX(QTPADRAOPRAT),
                              MAX(QTCONTAIPRAT),
                              MAX(QTAMOSTRPADR),
                              MAX(VRPEMINPADR)
                           INTO
                              V_QTPADRAO,
                              V_QTCONTAI,
                              V_QTAMOSTRAUN,
                              V_PERCENTUAL
                           FROM
                              PRATPADR
                           WHERE
                              CDPRATO      = C_CARDAPIO.CDPRATO AND
                              CDFILAUXPRAT = C_CARDAPIO.CDFILAUXPRAT;
                        END IF;
                     END IF;
                  END IF;

                  /* BUSCA A QUANTIDADE DE AMOSTRAS PADRAO*/
                  IF V_QTAMOSTRAUN IS NULL THEN
                     BEGIN
                        SELECT
                           QTAMOSTRAUN
                        INTO
                           V_QTAMOSTRAUN
                        FROM
                           UNIDMEDIFILI
                        WHERE
                           CDFILIAL  = C_CARDAPIO.CDFILIAL AND
                           SGUNIDADE = C_CARDAPIO.SGUNIDADE;
                     EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                           V_QTAMOSTRAUN := 0;
                     END;
                  END IF;

                  IF V_PERCENTUAL IS NULL THEN
                     BEGIN
                        SELECT
                           VRPEMINCONT
                        INTO
                           V_PERCENTUAL
                        FROM
                           PARAMPLANEJA
                        WHERE
                           CDFILIAL = P_CDFILIAL;
                     EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                           V_PERCENTUAL := 0;
                     END;
                  END IF;

                  IF NOT ((V_QTPADRAO IS NULL) OR (V_QTPADRAO = 0)) AND
                     NOT ((V_QTCONTAI IS NULL) OR (V_QTCONTAI = 0)) THEN

                     /* ==== CALCULO SEM amostra ============================ */
                     V_QTTOTAPRATST := 0;
                     V_QTPARCPRATST := 0; /*QUANTIDADE DO CONTAINER INCOMPLETO*/
                     V_QTTOTACONTST := 0; /*TOTAL DE CONTAINERS*/
                     V_QTCONTCOMPST := 0; /*TOTAL DE CONTAINERS COMPLETOS*/
                     V_QTCONTINCOST := 0; /*TOTAL DE CONTAINERS INCOMPLETOS*/

                     V_QTTOTAPRATST := ROUND((V_QTPADRAO * V_QTCOMESTIPRAT),5);

                     IF (C_CARDAPIO.VRARREDTRANS <> 0) THEN
                        IF (V_QTTOTAPRATST > 0) AND (MOD(V_QTTOTAPRATST, C_CARDAPIO.VRARREDTRANS) > 0) THEN
                           V_QTTOTAPRATST := (FLOOR(V_QTTOTAPRATST/C_CARDAPIO.VRARREDTRANS) + 1) * C_CARDAPIO.VRARREDTRANS;
                        END IF;
                     END IF;

                     /*CALCULO PARA VERIFICAR A QUANTIDADE DE CONTAINERS COMPLETOS E INCOMPETOS*/
                     IF V_QTTOTAPRATST >= V_QTCONTAI THEN
                        V_QTPARCPRATST := MOD(V_QTTOTAPRATST, V_QTCONTAI);
                        V_QTTOTACONTST := TRUNC(V_QTTOTAPRATST / V_QTCONTAI);
                        V_QTCONTCOMPST := V_QTTOTACONTST;

                        IF V_QTPARCPRATST > 0 THEN
                           V_QTCONTINCOST := 1;
                           V_QTTOTACONTST := V_QTTOTACONTST + 1;

                           IF V_PERCENTUAL > 0 THEN
                              IF V_QTPARCPRATST < ((V_PERCENTUAL / 100) * V_QTCONTAI) THEN
                                 V_QTPARCPRATST := (V_QTCONTAI + V_QTPARCPRATST) / 2;
                                 V_QTCONTINCOST := 2;
                                 V_QTCONTCOMPST := V_QTCONTCOMPST - 1;
                              END IF;
                           END IF;
                        END IF;
                     ELSE
                        V_QTPARCPRATST := V_QTTOTAPRATST;
                        V_QTTOTACONTST := 1;
                        V_QTCONTCOMPST := 0;
                        V_QTCONTINCOST := 1;
                     END IF;
                     /* === FIM CALCULO SEM AMOSTRA ============================ */

                     /* ==== CALCULO COM amostra ============================ */
                     V_QTTOTAPRATSTCAMOS := 0; /*QUANTIDADE TOTAL com amostras*/
                     V_QTPARCPRATSTCAMOS := 0; /*QUANTIDADE DO CONTAINER INCOMPLETO com amostras*/
                     V_QTTOTACONTSTCAMOS := 0; /*TOTAL DE CONTAINERS com amostras*/
                     V_QTCONTCOMPSTCAMOS := 0; /*TOTAL DE CONTAINERS COMPLETOS com amostras*/
                     V_QTCONTINCOSTCAMOS := 0; /*TOTAL DE CONTAINERS INCOMPLETOS com amostras*/

                     V_QTTOTAPRATSTCAMOS := V_QTTOTAPRATST + V_QTAMOSTRAUN;

                     /*CALCULO PARA VERIFICAR A QUANTIDADE DE CONTAINERS COMPLETOS E INCOMPETOS*/
                     IF V_QTTOTAPRATSTCAMOS >= V_QTCONTAI THEN
                        V_QTPARCPRATSTCAMOS := MOD(V_QTTOTAPRATSTCAMOS, V_QTCONTAI);
                        V_QTTOTACONTSTCAMOS := TRUNC(V_QTTOTAPRATSTCAMOS / V_QTCONTAI);
                        V_QTCONTCOMPSTCAMOS := V_QTTOTACONTSTCAMOS;

                        IF V_QTPARCPRATSTCAMOS > 0 THEN
                           V_QTCONTINCOSTCAMOS := 1;
                           V_QTTOTACONTSTCAMOS := V_QTTOTACONTSTCAMOS + 1;

                           IF V_PERCENTUAL > 0 THEN
                              IF V_QTPARCPRATSTCAMOS < ((V_PERCENTUAL / 100) * V_QTCONTAI) THEN
                                 V_QTPARCPRATSTCAMOS := (V_QTCONTAI + V_QTPARCPRATSTCAMOS) / 2;
                                 V_QTCONTINCOSTCAMOS := 2;
                                 V_QTCONTCOMPSTCAMOS := V_QTCONTCOMPSTCAMOS - 1;
                              END IF;
                           END IF;
                        END IF;
                     ELSE
                        V_QTPARCPRATSTCAMOS := V_QTTOTAPRATSTCAMOS;
                        V_QTTOTACONTSTCAMOS := 1;
                        V_QTCONTCOMPSTCAMOS := 0;
                        V_QTCONTINCOSTCAMOS := 1;
                     END IF;
                     /* === FIM CALCULO COM AMOSTRA ============================ */

                    /*************************************************************************/

                     IF ((V_QTCOMESTIPRAT = 0) OR (V_QTCOMESTIPRAT IS NULL)) THEN
                        V_QTSUMPERPRAT := 0;
                     ELSE
                        V_QTSUMPERPRAT := ((100 * V_QTCOMESTIPRAT)/C_CARDAPIO.QTCOMESTICAR);
                     END IF;

                  /* ------- Gera o sequencial da tabela dos servicos transportados -------*/

                     SELECT
                        MAX(NRSERVTRAN)
                     INTO
                        V_NRSERVTRAN
                     FROM
                        SERVTRAN
                     WHERE
                        (CDFILIAL   = P_CDFILIAL) AND
                        (CDSERVFILI = C_CARDAPIO.CDSERVFILI);

                     IF (V_NRSERVTRAN IS NULL) THEN
                        V_SEQUENCIAL := '0000000001';
                     ELSE
                        V_SEQUENCIAL := LPAD(TO_CHAR(TO_NUMBER(V_NRSERVTRAN) + 1), 10, '0');
                     END IF;

                     INSERT INTO SERVTRAN
                        (CDFILIAL,
                         CDSERVFILI,
                         DTSERVTRAN,
                         NRSERVTRAN,
                         CDDESTFILI,
                         CDCLIENTE,
                         CDFILAUXPRAT,
                         CDPRATO,
                         QTTOTAPRATST,
                         QTPARCPRATST,
                         QTTOTACONTST,
                         QTCONTCOMPST,
                         VRPECONSST,
                         QTPADRAOST,
                         QTCONTAIST,
                         CDDIETPADST,
                         QTCOMESTIST,
                         QTCOMECLIST,
                         QTAMOSTRAST,
                         QTTOTACONTSTCAMOS,
                         QTCONTCOMPSTCAMOS)
                      VALUES
                         (C_CARDAPIO.CDFILIAL,
                          C_CARDAPIO.CDSERVFILI,
                          C_CARDAPIO.DTCARDAPIO,
                          V_SEQUENCIAL,
                          C_DESTINO.CDDESTFILI,
                          C_DESTINO.CDCLIENTE,
                          C_CARDAPIO.CDFILAUXPRAT,
                          C_CARDAPIO.CDPRATO,
                          V_QTTOTAPRATST,
                          V_QTPARCPRATST,
                          V_QTTOTACONTST,
                          V_QTCONTCOMPST,
                          V_QTSUMPERPRAT,
                          V_QTPADRAO,
                          V_QTCONTAI,
                          C_CARDAPIO.CDDIETPAD,
                          V_QTCOMESTIPRAT,
                          V_QTCOMECLIST,
                          V_QTAMOSTRAUN,
                          V_QTTOTACONTSTCAMOS,
                          V_QTCONTCOMPSTCAMOS);

                     GERA_ETIQUETRAN(C_CARDAPIO.CDFILIAL,
                                     C_CARDAPIO.CDSERVFILI,
                                     C_CARDAPIO.CDDIETPAD,
                                     C_DESTINO.CDDESTFILI,
                                     V_SEQUENCIAL,
                                     C_DESTINO.CDCLIENTE,
                                     C_CARDAPIO.DTCARDAPIO,
                                     C_CARDAPIO.CDFILAUXPRAT,
                                     C_CARDAPIO.CDPRATO,
                                     V_QTCONTCOMPSTCAMOS,
                                     V_QTCONTAI,
                                     V_QTCONTINCOSTCAMOS,
                                     V_QTPARCPRATSTCAMOS);
                  END IF;
            END;
         END LOOP; /*LOOP DOS DESTINOS*/
      END LOOP; /*LOOP DOS CARDAPIOS*/
   END LOOP; /*LOOP DOS SERVICOS*/
END;
/