create or replace
PROCEDURE TRAB_RELCARDMES( 
   P_NRSEQTRABCARD IN VARCHAR2,
   P_NRLISTASERV IN VARCHAR2,
   P_DTMESINIC IN DATE,
   P_DTMESFIM IN DATE,
   P_IDNOMEFANT IN VARCHAR2,
   P_IDIMPDIASSEM IN VARCHAR2,
   P_QTCOLUNA IN FLOAT
) IS

   V_CONT NUMBER;
   V_PAGINA NUMBER;
   V_CDPRATO VARCHAR2(10); 
   V_CDFILAUXPRAT VARCHAR2(04);
   V_QTCOMENSAIS FLOAT;
   V_VRPECONSCARD FLOAT;
   V_NMPRATO VARCHAR2(40);
   V_CDDIETPAD VARCHAR2(04);
   V_ROW NUMBER;
   V_CDFILISERV VARCHAR2(9);

BEGIN
   V_CDFILISERV := ' ';

   /* SELECIONA OS SERVICOS E SUAS DIETAS PADROES CASO EXISTAM */
   FOR C_SERVICO IN (
      SELECT SE.CDFILIAL, SE.CDSERVFILI, DT.DTMES, DI.CDDIETPAD
      FROM SERVICO SE, (SELECT DISTINCT CA.CDFILIAL, CA.CDSERVFILI, CA.CDDIETPAD
                        FROM DIETSERV DS, CARDADIA CA, (SELECT SUBSTR(CDLISTA, 1, 4) AS CDFILIAL,
                                                                SUBSTR(CDLISTA, 5, 5) AS CDSERVFILI
                                                         FROM LISTA
                                                          WHERE NRLISTA = P_NRLISTASERV) LS
                        WHERE CA.CDFILIAL = LS.CDFILIAL
                          AND CA.CDSERVFILI = LS.CDSERVFILI
                          AND CA.DTCARDAPIO BETWEEN P_DTMESINIC AND P_DTMESFIM
                          AND CA.CDFILIAL   = DS.CDFILIAL(+)
                          AND CA.CDSERVFILI = DS.CDSERVFILI(+)
                          AND CA.CDDIETPAD = DS.CDDIETPAD(+)) DI,
                       (SELECT SUBSTR(CDLISTA, 1, 4) AS CDFILIAL,
                               SUBSTR(CDLISTA, 5, 5) AS CDSERVFILI
                          FROM LISTA
                         WHERE NRLISTA = P_NRLISTASERV) LS,
                       (SELECT DISTINCT DTMES
                          FROM DATA
                         WHERE DTMES BETWEEN P_DTMESINIC AND P_DTMESFIM) DT
      WHERE SE.CDFILIAL = LS.CDFILIAL
        AND SE.CDSERVFILI = LS.CDSERVFILI
        AND SE.CDFILIAL = DI.CDFILIAL
        AND SE.CDSERVFILI = DI.CDSERVFILI
      ORDER BY SE.CDFILIAL, SE.CDSERVFILI, DT.DTMES)
   LOOP

      IF (V_CDFILISERV <> C_SERVICO.CDFILIAL||C_SERVICO.CDSERVFILI) THEN
         V_ROW := 0;
      END IF;

      V_CONT := 0;
      V_PAGINA := 1;

      /* SELECIONA AS CONFIGURACOES DOS SERVICOS (TIPO DE PRATO) */
      FOR C_CONFIG IN(
         SELECT CF.CDFILIAL, CF.CDSERVFILI, CF.DTREFECNFMES, CF.NRSEQCNFMES,
                CF.NRORDCNFMES, CF.CDTIPOPRAT, CF.IDOPCCNFMES, TI.NMTIPOPRAT
          FROM CNFCAMES CF, TIPOPRAT TI
          WHERE CF.CDFILIAL = C_SERVICO.CDFILIAL
            AND CF.CDSERVFILI = C_SERVICO.CDSERVFILI
            AND CF.DTREFECNFMES =  C_SERVICO.DTMES
            AND CF.IDIMPCNFMES = 'S'
            AND CF.CDTIPOPRAT = TI.CDTIPOPRAT
          ORDER BY CF.NRORDCNFMES)
      LOOP

         V_CONT := V_CONT + 1;

         /* SELECIONA OS DIAS MES */ 
         FOR C_DATA IN(
            SELECT DTDATA, DTMES
            FROM DATA
            WHERE DTMES = C_CONFIG.DTREFECNFMES)
         LOOP

            /* VERIFICA SE O PARAMETRO "IMPRIME NOME FANTASIA" ESTA MARCADA */ 
            IF P_IDNOMEFANT = 'S' THEN
               BEGIN
                  SELECT CC.CDPRATO, CC.CDFILAUXPRAT, (CC.VRPECONSCARD * QTCOMESTICAR / 100), CC.VRPECONSCARD, NVL(SUBSTR(FA.NMPRATO, 1, 40), PT.NMPRATO), CA.CDDIETPAD
                    INTO V_CDPRATO, V_CDFILAUXPRAT, V_QTCOMENSAIS, V_VRPECONSCARD, V_NMPRATO, V_CDDIETPAD
                  FROM CARDADIA CA, COMPCARD CC, PRATO PT,
                      (SELECT NMFANTASIASE AS NMPRATO, CDPRATO, CDFILAUXPRAT
                       FROM PRATSERV
                       WHERE CDFILIAL = C_CONFIG.CDFILIAL
                         AND CDSERVFILI = C_CONFIG.CDSERVFILI
                         AND NOT NMFANTASIASE IS NULL
                       UNION
                       SELECT P1.NMFANTASIAPF AS NMPRATO, P1.CDPRATO, P1.CDFILAUXPRAT
                       FROM PRATFILI P1
                       WHERE P1.CDFILIAL = C_CONFIG.CDFILIAL
                         AND NOT P1.NMFANTASIAPF IS NULL
                         AND NOT EXISTS (SELECT P.NMFANTASIASE
                                         FROM PRATSERV P
                                         WHERE P.CDFILAUXPRAT = P1.CDFILAUXPRAT
                                           AND P.CDPRATO      = P1.CDPRATO
                                           AND P.CDFILIAL     = P1.CDFILIAL
                                           AND P.CDSERVFILI   = C_SERVICO.CDSERVFILI
                                           AND NOT P.NMFANTASIASE IS NULL)
                       UNION
                       SELECT P2.NMFANTASIAPR AS NMPRATO, P2.CDPRATO, P2.CDFILAUXPRAT
                       FROM PRATPADR P2
                       WHERE NOT P2.NMFANTASIAPR IS NULL
                         AND NOT EXISTS (SELECT P.NMFANTASIASE
                                         FROM PRATSERV P
                                         WHERE P.CDFILAUXPRAT = P2.CDFILAUXPRAT
                                           AND P.CDPRATO      = P2.CDPRATO
                                           AND P.CDFILIAL     = C_CONFIG.CDFILIAL
                                           AND P.CDSERVFILI   = C_CONFIG.CDSERVFILI
                                           AND NOT P.NMFANTASIASE IS NULL)
                        AND NOT EXISTS (SELECT P.NMFANTASIAPF
                                        FROM PRATFILI P
                                        WHERE P.CDFILAUXPRAT = P2.CDFILAUXPRAT
                                          AND P.CDPRATO      = P2.CDPRATO
                                          AND P.CDFILIAL     = C_CONFIG.CDFILIAL
                                          AND NOT P.NMFANTASIAPF IS NULL)) FA
                  WHERE CA.CDFILIAL = C_CONFIG.CDFILIAL
                    AND CA.CDSERVFILI = C_CONFIG.CDSERVFILI
                    AND ((CA.CDDIETPAD = C_SERVICO.CDDIETPAD) OR (CA.CDDIETPAD IS NULL))
                    AND CA.DTCARDAPIO = C_DATA.DTDATA
                    AND CC.CDFILIAL = CA.CDFILIAL
                    AND CC.NRCARDAPIO = CA.NRCARDAPIO
                    AND CC.NRSEQPRATCAR = C_CONFIG.NRSEQCNFMES
                    AND PT.CDPRATO = CC.CDPRATO
                    AND PT.CDFILAUXPRAT = CC.CDFILAUXPRAT
                    AND FA.CDPRATO(+) = CC.CDPRATO
                    AND FA.CDFILAUXPRAT(+) = CC.CDFILAUXPRAT;

            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_CDPRATO := NULL;
                     V_CDFILAUXPRAT := NULL;
                     V_QTCOMENSAIS := NULL;
                     V_VRPECONSCARD := NULL;
                     V_NMPRATO := NULL;
                     V_CDDIETPAD := NULL;
               END;
            ELSE
               BEGIN
                  SELECT CC.CDPRATO, CC.CDFILAUXPRAT, (CC.VRPECONSCARD * QTCOMESTICAR / 100), CC.VRPECONSCARD, PT.NMPRATO, CA.CDDIETPAD
                    INTO V_CDPRATO, V_CDFILAUXPRAT, V_QTCOMENSAIS, V_VRPECONSCARD, V_NMPRATO, V_CDDIETPAD
                  FROM   CARDADIA CA, COMPCARD CC, PRATO PT
                  WHERE CA.CDFILIAL = C_CONFIG.CDFILIAL
                    AND CA.CDSERVFILI = C_CONFIG.CDSERVFILI
                    AND ((CA.CDDIETPAD = C_SERVICO.CDDIETPAD) OR (CA.CDDIETPAD IS NULL))
                    AND CA.DTCARDAPIO = C_DATA.DTDATA
                    AND CC.CDFILIAL = CA.CDFILIAL
                    AND CC.NRCARDAPIO = CA.NRCARDAPIO
                    AND CC.NRSEQPRATCAR = C_CONFIG.NRSEQCNFMES
                    AND PT.CDPRATO = CC.CDPRATO
                    AND PT.CDFILAUXPRAT = CC.CDFILAUXPRAT;
            EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_CDPRATO := NULL;
                     V_CDFILAUXPRAT := NULL;
                     V_QTCOMENSAIS := NULL;
                     V_VRPECONSCARD := NULL;
                     V_NMPRATO := NULL;
                     V_CDDIETPAD := NULL;
               END;
            END IF;

            IF V_CONT = 1 THEN
               INSERT INTO TRABCARDMES(NRSEQTRABCARD, NRSEQUITEMTRAB, CDFILIAL, CDSERVFILI, NRSEQCARDMES, NRORDCARDMES,
                                       CDDIETPAD, DTCARDAPIO, DTMESCARD, IDOPCCNFMES1, CDTIPOPRAT1, NRPAGINA,
                                       CDPRATO1, CDFILAUXPRAT1, QTCOMENS1, VRPERCENT1, NMTIPOPRAT1, NMPRATO1)
               VALUES(P_NRSEQTRABCARD, V_ROW, C_CONFIG.CDFILIAL, C_CONFIG.CDSERVFILI, C_CONFIG.NRSEQCNFMES, C_CONFIG.NRORDCNFMES,
                    C_SERVICO.CDDIETPAD, C_DATA.DTDATA, C_DATA.DTMES, C_CONFIG.IDOPCCNFMES, C_CONFIG.CDTIPOPRAT, V_PAGINA,
                       V_CDPRATO, V_CDFILAUXPRAT, V_QTCOMENSAIS, V_VRPECONSCARD, C_CONFIG.NMTIPOPRAT, V_NMPRATO);
               V_ROW := V_ROW + 1;    
               
            ELSE
               UPDATE TRABCARDMES
               SET IDOPCCNFMES2  = DECODE(V_CONT, 2, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES2),
                   CDTIPOPRAT2   = DECODE(V_CONT, 2, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT2),
                   CDPRATO2      = DECODE(V_CONT, 2, V_CDPRATO, CDPRATO2),
                   CDFILAUXPRAT2 = DECODE(V_CONT, 2, V_CDFILAUXPRAT, CDFILAUXPRAT2),
                   QTCOMENS2     = DECODE(V_CONT, 2, V_QTCOMENSAIS, QTCOMENS2),
                   VRPERCENT2    = DECODE(V_CONT, 2, V_VRPECONSCARD, VRPERCENT2),
                   NMTIPOPRAT2   = DECODE(V_CONT, 2, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT2),
                   NMPRATO2      = DECODE(V_CONT, 2, V_NMPRATO, NMPRATO2),
                   IDOPCCNFMES3  = DECODE(V_CONT, 3, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES3),
                   CDTIPOPRAT3   = DECODE(V_CONT, 3, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT3),
                   CDPRATO3      = DECODE(V_CONT, 3, V_CDPRATO, CDPRATO3),
                   CDFILAUXPRAT3 = DECODE(V_CONT, 3, V_CDFILAUXPRAT, CDFILAUXPRAT3),
                   QTCOMENS3     = DECODE(V_CONT, 3, V_QTCOMENSAIS, QTCOMENS3),
                   VRPERCENT3    = DECODE(V_CONT, 3, V_VRPECONSCARD, VRPERCENT3),
                   NMTIPOPRAT3   = DECODE(V_CONT, 3, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT3),
                   NMPRATO3      = DECODE(V_CONT, 3, V_NMPRATO, NMPRATO3),
                   IDOPCCNFMES4  = DECODE(V_CONT, 4, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES4),
                   CDTIPOPRAT4   = DECODE(V_CONT, 4, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT4),
                   CDPRATO4      = DECODE(V_CONT, 4, V_CDPRATO, CDPRATO4),
                   CDFILAUXPRAT4 = DECODE(V_CONT, 4, V_CDFILAUXPRAT, CDFILAUXPRAT4),
                   QTCOMENS4     = DECODE(V_CONT, 4, V_QTCOMENSAIS, QTCOMENS4),
                   VRPERCENT4    = DECODE(V_CONT, 4, V_VRPECONSCARD, VRPERCENT4),
                   NMTIPOPRAT4   = DECODE(V_CONT, 4, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT4),
                   NMPRATO4      = DECODE(V_CONT, 4, V_NMPRATO, NMPRATO4),
                   IDOPCCNFMES5   = DECODE(V_CONT, 5, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES5),
                   CDTIPOPRAT5    = DECODE(V_CONT, 5, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT5),
                   CDPRATO5       = DECODE(V_CONT, 5, V_CDPRATO, CDPRATO5),
                   CDFILAUXPRAT5  = DECODE(V_CONT, 5, V_CDFILAUXPRAT, CDFILAUXPRAT5),
                   QTCOMENS5      = DECODE(V_CONT, 5, V_QTCOMENSAIS, QTCOMENS5),
                   VRPERCENT5     = DECODE(V_CONT, 5, V_VRPECONSCARD, VRPERCENT5),
                   NMTIPOPRAT5    = DECODE(V_CONT, 5, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT5),
                   NMPRATO5       = DECODE(V_CONT, 5, V_NMPRATO, NMPRATO5),
                   IDOPCCNFMES6   = DECODE(V_CONT, 6, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES6),
                   CDTIPOPRAT6    = DECODE(V_CONT, 6, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT6),
                   CDPRATO6       = DECODE(V_CONT, 6, V_CDPRATO, CDPRATO6),
                   CDFILAUXPRAT6  = DECODE(V_CONT, 6, V_CDFILAUXPRAT, CDFILAUXPRAT6),
                   QTCOMENS6      = DECODE(V_CONT, 6, V_QTCOMENSAIS, QTCOMENS6),
                   VRPERCENT6     = DECODE(V_CONT, 6, V_VRPECONSCARD, VRPERCENT6),
                   NMTIPOPRAT6    = DECODE(V_CONT, 6, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT6),
                   NMPRATO6       = DECODE(V_CONT, 6, V_NMPRATO, NMPRATO6),
                   IDOPCCNFMES7   = DECODE(V_CONT, 7, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES7),
                   CDTIPOPRAT7    = DECODE(V_CONT, 7, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT7),
                   CDPRATO7       = DECODE(V_CONT, 7, V_CDPRATO, CDPRATO7),
                   CDFILAUXPRAT7  = DECODE(V_CONT, 7, V_CDFILAUXPRAT, CDFILAUXPRAT7),
                   QTCOMENS7      = DECODE(V_CONT, 7, V_QTCOMENSAIS, QTCOMENS7),
                   VRPERCENT7     = DECODE(V_CONT, 7, V_VRPECONSCARD, VRPERCENT7),
                   NMTIPOPRAT7    = DECODE(V_CONT, 7, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT7),
                   NMPRATO7       = DECODE(V_CONT, 7, V_NMPRATO, NMPRATO7),
                   IDOPCCNFMES8   = DECODE(V_CONT, 8, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES8),
                   CDTIPOPRAT8    = DECODE(V_CONT, 8, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT8),
                   CDPRATO8       = DECODE(V_CONT, 8, V_CDPRATO, CDPRATO8),
                   CDFILAUXPRAT8  = DECODE(V_CONT, 8, V_CDFILAUXPRAT, CDFILAUXPRAT8),
                   QTCOMENS8      = DECODE(V_CONT, 8, V_QTCOMENSAIS, QTCOMENS8),
                   VRPERCENT8     = DECODE(V_CONT, 8, V_VRPECONSCARD, VRPERCENT8),
                   NMTIPOPRAT8    = DECODE(V_CONT, 8, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT8),
                   NMPRATO8       = DECODE(V_CONT, 8, V_NMPRATO, NMPRATO8),
                   IDOPCCNFMES9   = DECODE(V_CONT, 9, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES9),
                   CDTIPOPRAT9    = DECODE(V_CONT, 9, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT9),
                   CDPRATO9       = DECODE(V_CONT, 9, V_CDPRATO, CDPRATO9),
                   CDFILAUXPRAT9  = DECODE(V_CONT, 9, V_CDFILAUXPRAT, CDFILAUXPRAT9),
                   QTCOMENS9      = DECODE(V_CONT, 9, V_QTCOMENSAIS, QTCOMENS9),
                   VRPERCENT9     = DECODE(V_CONT, 9, V_VRPECONSCARD, VRPERCENT9),
                   NMTIPOPRAT9    = DECODE(V_CONT, 9, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT9),
                   NMPRATO9       = DECODE(V_CONT, 9, V_NMPRATO, NMPRATO9),
                   IDOPCCNFMES10  = DECODE(V_CONT, 10, C_CONFIG.IDOPCCNFMES, IDOPCCNFMES10),
                   CDTIPOPRAT10   = DECODE(V_CONT, 10, C_CONFIG.CDTIPOPRAT, CDTIPOPRAT10),
                   CDPRATO10      = DECODE(V_CONT, 10, V_CDPRATO, CDPRATO10),
                   CDFILAUXPRAT10 = DECODE(V_CONT, 10, V_CDFILAUXPRAT, CDFILAUXPRAT10),
                   QTCOMENS10     = DECODE(V_CONT, 10, V_QTCOMENSAIS, QTCOMENS10),
                   VRPERCENT10    = DECODE(V_CONT, 10, V_VRPECONSCARD, VRPERCENT10),
                   NMTIPOPRAT10   = DECODE(V_CONT, 10, C_CONFIG.NMTIPOPRAT, NMTIPOPRAT10),
                   NMPRATO10      = DECODE(V_CONT, 10, V_NMPRATO, NMPRATO10)  
               WHERE CDFILIAL = C_CONFIG.CDFILIAL
                 AND CDSERVFILI = C_CONFIG.CDSERVFILI
                 AND ((CDDIETPAD = C_SERVICO.CDDIETPAD) OR (CDDIETPAD IS NULL))
                 AND DTCARDAPIO = C_DATA.DTDATA
                 AND NRPAGINA = V_PAGINA;

            END IF;
         END LOOP;

         IF V_CONT = P_QTCOLUNA THEN
            V_CONT := 0;
            V_PAGINA := V_PAGINA + 1;
         END IF;

      END LOOP;
      COMMIT;
      V_CDFILISERV := C_SERVICO.CDFILIAL||C_SERVICO.CDSERVFILI;
   END LOOP;

   IF P_IDIMPDIASSEM = 'N' THEN
      DELETE TRABCARDMES
      WHERE NRSEQTRABCARD = P_NRSEQTRABCARD
        AND CDPRATO1 IS NULL
        AND CDPRATO2 IS NULL
        AND CDPRATO3 IS NULL
        AND CDPRATO4 IS NULL
        AND CDPRATO5 IS NULL
        AND CDPRATO6 IS NULL
        AND CDPRATO7 IS NULL
        AND CDPRATO8 IS NULL
        AND CDPRATO9 IS NULL
        AND CDPRATO10 IS NULL;
   END IF;

   COMMIT;
END;
/