CREATE OR REPLACE PROCEDURE IMPORTAR_CARDBASE
(P_NRCARDAPIOBASEORI IN NUMBER,
P_NRCARDAPIOBASEDEST IN NUMBER,
P_COLUNAS IN VARCHAR2,
P_COMPOSICAO IN VARCHAR2,
P_REGRAS IN VARCHAR2,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER)
AS
   V_QTDIASORI NUMBER;
   V_QTDIASDEST NUMBER;
   V_QTDE NUMBER;
   V_QTDE_A_INCLUIR NUMBER;
   V_I NUMBER;
   V_MAX_SEMANA NUMBER;
   V_MAX_DIASEMANA NUMBER;
   V_SEMANA NUMBER;
   V_DIA NUMBER;
   V_NRDIACARDBASE NUMBER;
   V_NRCONFIGCARDBASE NUMBER;
   V_NRCOMPCARDBASE NUMBER;
   V_NRCOMPCARDBASEORI NUMBER;
   V_NRPRATCOMPCARDBASE NUMBER;
   V_NRREGIAOPRATCARDBASE NUMBER;
   V_NRADESAOCARDBASE NUMBER;
   V_NRSEGMERADESAOCBASE NUMBER;
   V_NRATIVCLIADESAOCBASE NUMBER;
   V_NRITEMADESAOCBASE NUMBER;
   V_NRGRAMATURACARDBASE NUMBER;
   V_NRGRAMATURACARDBASE_EXISTE NUMBER;
   V_NRITEMGRAMCARDBASE NUMBER;
   V_NRPARTSEGCARDBASE NUMBER;
   V_NRPARTSEG_EXISTE NUMBER;
   V_NRITPARTSEGCARDBASE NUMBER;
   V_NRAMOSTRACARDBASE NUMBER;
   V_NRDESCARTCARDBASE NUMBER;
   V_NRLIMPEZACARDBASE NUMBER;   
   V_NRORDEM NUMBER;
   V_CDADESAOCARDBASE VARCHAR2(5);
   V_CDPARTSEGCARDBASE VARCHAR2(5);
   V_NRAMOSTRACARDBASEORI NUMBER;
   V_NRCONFIGCARDBASEQ NUMBER;
   V_NRCONFIGCARDBASEF NUMBER;
   V_DE_PARA_CONFIG VARCHAR2(10);
   V_CNFQUENTEORI NUMBER; 
   V_CNFFRIOORI NUMBER;
BEGIN
--=========================================================
--=== Ajuste da quantidade de dias
--=========================================================
   -- Verifica necessidade de incrementar o nmero de dias      
   IF P_COMPOSICAO = 'S' THEN      
      SELECT COUNT(1)
        INTO V_QTDIASORI
        FROM DIACARDBASE
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEORI;

      SELECT COUNT(1), MAX(NRSEMANA)
        INTO V_QTDIASDEST, V_MAX_SEMANA
        FROM DIACARDBASE
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST;         

      IF V_QTDIASDEST < V_QTDIASORI THEN
         SELECT MAX(NRDIASEMANA)
           INTO V_MAX_DIASEMANA
           FROM DIACARDBASE
          WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST
            AND NRSEMANA = V_MAX_SEMANA;

         V_SEMANA := NVL(V_MAX_SEMANA, 1);
         V_DIA := NVL(V_MAX_DIASEMANA, 0) + 1;
         IF V_DIA > 7 THEN
            V_DIA := 1;
            V_SEMANA := V_SEMANA + 1;
         END IF;

         V_QTDE_A_INCLUIR := V_QTDIASORI - V_QTDIASDEST;

         V_I := 1;
         FOR V_I IN 1..V_QTDE_A_INCLUIR LOOP
            --Incluso dos dias no cardpio
            V_NRDIACARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('DIACARDBASE', 1));   

            INSERT INTO DIACARDBASE
            (NRDIACARDBASE, NRCARDAPIOBASE, NRSEMANA, NRDIASEMANA,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRDIACARDBASE, P_NRCARDAPIOBASEDEST, V_SEMANA, V_DIA,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);

            V_DIA := V_DIA + 1;
            IF V_DIA > 7 THEN
               V_DIA := 1;
               V_SEMANA := V_SEMANA + 1;
            END IF;
         END LOOP;
      END IF;
   END IF;
--=========================================================
--=== Incluso da configurao
--=========================================================      
   SELECT MAX(NRORDEM)
     INTO V_NRORDEM
     FROM CONFIGCARDBASE
    WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST;

   V_NRORDEM := NVL(V_NRORDEM, 0) + 1;

   V_DE_PARA_CONFIG := RETORNA_NOVO_CODIGO('LISTA', 1, 10);

   FOR C_CONF IN
   (
      SELECT C.NRCONFIGCARDBASE AS NRCONFIGCARDBASEORI, C.CDTIPOPRAT, C.DSCOMPLEMENTO,
             C.IDCLASSIFICACAO, C.NRORDEM, C.IDTEMPERATURA
      FROM CONFIGCARDBASE C
      WHERE C.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
        AND VALOR_NA_LISTA(C.NRCONFIGCARDBASE, NVL(P_COLUNAS, 'T')) > 0
      ORDER BY C.NRORDEM
   ) LOOP

      V_NRCONFIGCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('CONFIGCARDBASE', 1));

      INSERT INTO CONFIGCARDBASE
      (NRCONFIGCARDBASE, NRCARDAPIOBASE, CDTIPOPRAT,DSCOMPLEMENTO,
       IDCLASSIFICACAO, NRORDEM, IDTEMPERATURA,
       NRORG, NRORGINCLUSAO, NRORGULTATU,
       CDOPERINCLUSAO, CDOPERULTATU,
       DTINCLUSAO, DTULTATU)
      VALUES
      (V_NRCONFIGCARDBASE, P_NRCARDAPIOBASEDEST, C_CONF.CDTIPOPRAT, C_CONF.DSCOMPLEMENTO,
       C_CONF.IDCLASSIFICACAO, V_NRORDEM, C_CONF.IDTEMPERATURA,
       P_NRORG, P_NRORG, P_NRORG,
       P_CDOPERADOR, P_CDOPERADOR,
       SYSDATE, SYSDATE);


      
   --=========================================================
   --=== Incluso da composio, se solicitada
   --=========================================================
      IF P_COMPOSICAO = 'S' THEN

         FOR C_DIA IN
         (
            SELECT D.NRDIACARDBASE, DO.NRDIACARDBASE AS NRDIACARDBASEORI
              FROM DIACARDBASE D
                   INNER JOIN DIACARDBASE DO ON DO.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI AND
                                                DO.NRSEMANA = D.NRSEMANA AND
                                                DO.NRDIASEMANA = D.NRDIASEMANA
             WHERE D.NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST
               AND DO.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI                  
         ) LOOP
            SELECT MAX(NRCOMPCARDBASE)
              INTO V_NRCOMPCARDBASEORI
              FROM COMPCARDBASE
             WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
               AND NRCONFIGCARDBASE = C_CONF.NRCONFIGCARDBASEORI
               AND NRDIACARDBASE = C_DIA.NRDIACARDBASEORI;
            IF V_NRCOMPCARDBASEORI IS NOT NULL THEN
               V_NRCOMPCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('COMPCARDBASE', 1));

               INSERT INTO COMPCARDBASE
               (NRCOMPCARDBASE, NRCARDAPIOBASE, NRDIACARDBASE, NRCONFIGCARDBASE,
                NRORG, NRORGINCLUSAO, NRORGULTATU,
                CDOPERINCLUSAO, CDOPERULTATU,
                DTINCLUSAO, DTULTATU)
               VALUES
               (V_NRCOMPCARDBASE, P_NRCARDAPIOBASEDEST, C_DIA.NRDIACARDBASE, V_NRCONFIGCARDBASE,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE);

               FOR C_PRATOS IN
               (
                  SELECT NRPRATCOMPCARDBASE AS NRPRATCOMPCARDBASEORI, 
                         CDFILAUXPRAT, CDPRATO, IDPRATOEXIBICAO
                    FROM PRATCOMPCARDBASE
                   WHERE NRCOMPCARDBASE = V_NRCOMPCARDBASEORI                        
               ) LOOP
                  V_NRPRATCOMPCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('PRATCOMPCARDBASE', 1));

                  INSERT INTO PRATCOMPCARDBASE
                  (NRPRATCOMPCARDBASE, NRCOMPCARDBASE, 
                   CDFILAUXPRAT, CDPRATO, IDPRATOEXIBICAO,
                   NRORG, NRORGINCLUSAO, NRORGULTATU,
                   CDOPERINCLUSAO, CDOPERULTATU,
                   DTINCLUSAO, DTULTATU)
                  VALUES
                  (V_NRPRATCOMPCARDBASE, V_NRCOMPCARDBASE,
                   C_PRATOS.CDFILAUXPRAT, C_PRATOS.CDPRATO, C_PRATOS.IDPRATOEXIBICAO,
                   P_NRORG, P_NRORG, P_NRORG,
                   P_CDOPERADOR, P_CDOPERADOR,
                   SYSDATE, SYSDATE);

                  FOR C_REGIAO IN
                  (
                     SELECT CDREGIAO
                       FROM REGIAOPRATCARDBASE
                      WHERE NRPRATCOMPCARDBASE = C_PRATOS.NRPRATCOMPCARDBASEORI
                  ) LOOP
                     V_NRREGIAOPRATCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('REGIAOPRATCARDBASE', 1));

                     INSERT INTO REGIAOPRATCARDBASE
                     (NRREGIAOPRATCARDBASE, NRPRATCOMPCARDBASE, CDREGIAO,
                      NRORG, NRORGINCLUSAO, NRORGULTATU,
                      CDOPERINCLUSAO, CDOPERULTATU,
                      DTINCLUSAO, DTULTATU)
                     VALUES
                     (V_NRREGIAOPRATCARDBASE, V_NRPRATCOMPCARDBASE, C_REGIAO.CDREGIAO,
                      P_NRORG, P_NRORG, P_NRORG,
                      P_CDOPERADOR, P_CDOPERADOR, 
                      SYSDATE, SYSDATE);
                  END LOOP;                     
               END LOOP;
            END IF;
         END LOOP;
      END IF;

      INSERT INTO LISTA
      (NRLISTA, CDLISTA,
       NRORG, NRORGINCLUSAO, NRORGULTATU,
       CDOPERINCLUSAO, CDOPERULTATU,
       DTINCLUSAO, DTULTATU)
      VALUES
      (V_DE_PARA_CONFIG, TO_CHAR(C_CONF.NRCONFIGCARDBASEORI) || '|' || TO_CHAR(V_NRCONFIGCARDBASE),
       P_NRORG, P_NRORG, P_NRORG,
       P_CDOPERADOR, P_CDOPERADOR,
       SYSDATE, SYSDATE);

      V_NRORDEM := V_NRORDEM + 1;
   END LOOP;
--=========================================================
--=== Incluso das regras, se solicitado
--=========================================================
   IF P_REGRAS = 'S' THEN
   --=========================================================
   --=== Adeso
   --=========================================================
      SELECT MAX(CDADESAOCARDBASE)
        INTO V_CDADESAOCARDBASE
        FROM ADESAOCARDBASE
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST;

      V_CDADESAOCARDBASE := NVL(V_CDADESAOCARDBASE, '00000');

      V_CDADESAOCARDBASE := LPAD(TO_CHAR(TO_NUMBER(V_CDADESAOCARDBASE) + 1), 5, '0');

      FOR C_ADESAO IN
      (
         SELECT A.NRADESAOCARDBASE, A.NMADESAOCARDBASE, 
                A.CDGRTIPOPR, DD.NRDIACARDBASE AS NRDIACARDBASEDEST
           FROM ADESAOCARDBASE A
                LEFT JOIN DIACARDBASE D ON D.NRDIACARDBASE = A.NRDIACARDBASE
                LEFT JOIN DIACARDBASE DD ON DD.NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST AND
                                            D.NRSEMANA = DD.NRSEMANA AND
                                            D.NRDIASEMANA = DD.NRDIASEMANA 
          WHERE A.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
          ORDER BY A.CDADESAOCARDBASE
      ) LOOP
         -- Verifica que a regra no possua itens que no foram escolhidos para importao
         SELECT COUNT(1)
           INTO V_QTDE
         FROM ITEMADESAOCBASE I           
         WHERE I.NRADESAOCARDBASE = C_ADESAO.NRADESAOCARDBASE
          AND VALOR_NA_LISTA(I.NRCONFIGCARDBASE, NVL(P_COLUNAS, 'T')) = 0;
         -- Se possuir itens que no fazem parte do cardpio, avana para prxima regra
         IF V_QTDE > 0 THEN
            CONTINUE;
         END IF;

         -- Verifica se a regra  especfica por segmento, e se a regra possui segmento do cardapio base
         SELECT COUNT(1)
           INTO V_QTDE
           FROM SEGMERADESAOCBASE
          WHERE NRADESAOCARDBASE = C_ADESAO.NRADESAOCARDBASE;

         IF V_QTDE > 0 THEN
            SELECT COUNT(1)
              INTO V_QTDE
              FROM SEGMERADESAOCBASE S
                   INNER JOIN SEGMERCARDBASE SC ON SC.NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST AND
                                                   SC.CDSEGMER = S.CDSEGMER
             WHERE NRADESAOCARDBASE = C_ADESAO.NRADESAOCARDBASE;

            -- Se no atende nenhum dos segmentos, avana para prxima regra
            IF V_QTDE = 0 THEN
               CONTINUE;
            END IF;
         END IF;

         --ADESO
         V_NRADESAOCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('ADESAOCARDBASE', 1));

         INSERT INTO ADESAOCARDBASE
         (NRADESAOCARDBASE, NRCARDAPIOBASE, CDADESAOCARDBASE, 
          NMADESAOCARDBASE, CDGRTIPOPR, NRDIACARDBASE,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         VALUES
         (V_NRADESAOCARDBASE, P_NRCARDAPIOBASEDEST, V_CDADESAOCARDBASE,
          C_ADESAO.NMADESAOCARDBASE, C_ADESAO.CDGRTIPOPR, C_ADESAO.NRDIACARDBASEDEST,
          P_NRORG, P_NRORG, P_NRORG,
          P_CDOPERADOR, P_CDOPERADOR,
          SYSDATE, SYSDATE);

         -- SEGMENTO DE MERCADO
         FOR C_SEG IN
         (
            SELECT S.CDSEGMER
              INTO V_QTDE
              FROM SEGMERADESAOCBASE S
                   INNER JOIN SEGMERCARDBASE SC ON SC.NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST AND
                                                   SC.CDSEGMER = S.CDSEGMER
             WHERE NRADESAOCARDBASE = C_ADESAO.NRADESAOCARDBASE
         ) LOOP
            V_NRSEGMERADESAOCBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('SEGMERADESAOCBASE', 1));

            INSERT INTO SEGMERADESAOCBASE
            (NRSEGMERADESAOCBASE, NRADESAOCARDBASE, CDSEGMER,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRSEGMERADESAOCBASE, V_NRADESAOCARDBASE, C_SEG.CDSEGMER,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         END LOOP;

         -- RAMO DE ATIVIDADE
         FOR C_RAMO IN
         (
            SELECT A.NRATIVCLISEGMER
              INTO V_QTDE
              FROM ATIVCLIADESAOCBASE A
                   INNER JOIN ATIVCLISEGMER ATS ON ATS.NRATIVCLISEGMER = A.NRATIVCLISEGMER
                   INNER JOIN SEGMERADESAOCBASE S ON S.NRADESAOCARDBASE = V_NRADESAOCARDBASE AND
                                                     S.CDSEGMER = ATS.CDSEGMER
             WHERE A.NRADESAOCARDBASE = C_ADESAO.NRADESAOCARDBASE
         ) LOOP
            V_NRATIVCLIADESAOCBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('ATIVCLIADESAOCBASE', 1));

            INSERT INTO ATIVCLIADESAOCBASE
            (NRATIVCLIADESAOCBASE, NRADESAOCARDBASE, NRATIVCLISEGMER,             
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRATIVCLIADESAOCBASE, V_NRADESAOCARDBASE, C_RAMO.NRATIVCLISEGMER,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         END LOOP;
         
         --ITENS DA ADESO
         FOR C_ITEM IN
         (
            SELECT L.NRCONFIGCARDBASE, I.NRPORCIONAMENTO, I.VRPEADESAO
              FROM ITEMADESAOCBASE I
                   INNER JOIN (
                                 SELECT SUBSTR(CDLISTA, 1, INSTR(CDLISTA, '|') - 1) AS NRCONFIGCARDBASEORI,
                                        SUBSTR(CDLISTA, INSTR(CDLISTA, '|') + 1, 50) AS NRCONFIGCARDBASE
                                   FROM LISTA
                                  WHERE NRLISTA = V_DE_PARA_CONFIG
                              ) L ON L.NRCONFIGCARDBASEORI = I.NRCONFIGCARDBASE
             WHERE I.NRADESAOCARDBASE = C_ADESAO.NRADESAOCARDBASE
         ) LOOP
            V_NRITEMADESAOCBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('ITEMADESAOCBASE', 1));

            INSERT INTO ITEMADESAOCBASE
            (NRITEMADESAOCBASE, NRADESAOCARDBASE, NRCONFIGCARDBASE, 
             NRPORCIONAMENTO, VRPEADESAO,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES         
            (V_NRITEMADESAOCBASE, V_NRADESAOCARDBASE, C_ITEM.NRCONFIGCARDBASE, 
             C_ITEM.NRPORCIONAMENTO, C_ITEM.VRPEADESAO,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         END LOOP;

         V_CDADESAOCARDBASE := LPAD(TO_CHAR(TO_NUMBER(V_CDADESAOCARDBASE) + 1), 5, '0');
      END LOOP;

   --=========================================================
   --=== Gramatura
   --=========================================================
      FOR C_GRAMATURA IN
      (
         SELECT G.NRGRAMATURACARDBASE, G.CDGRTIPOPR, G.VRGRAMATURA, G.SGUNIDADE, 
                G.IDTIPOGRAMATURA, G.NRTIPOPUBLICO
           FROM GRAMATURACARDBASE G
          WHERE G.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
      ) LOOP
         -- Verifica se j existe a regra de gramatura solicitada no destino
         SELECT MAX(NRGRAMATURACARDBASE)
           INTO V_NRGRAMATURACARDBASE_EXISTE
           FROM GRAMATURACARDBASE
          WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST
            AND CDGRTIPOPR = C_GRAMATURA.CDGRTIPOPR
            AND VRGRAMATURA = C_GRAMATURA.VRGRAMATURA
            AND SGUNIDADE = C_GRAMATURA.SGUNIDADE
            AND (NRTIPOPUBLICO = C_GRAMATURA.NRTIPOPUBLICO OR
                 (NRTIPOPUBLICO IS NULL AND C_GRAMATURA.NRTIPOPUBLICO IS NULL));
         IF V_NRGRAMATURACARDBASE_EXISTE IS NOT NULL THEN
            CONTINUE;
         END IF;

         -- Verifica se a regra se aplica a grupo de tipo de prato selecionado para importao
         SELECT COUNT(1)
           INTO V_QTDE
           FROM CONFIGCARDBASE C
                INNER JOIN TIPOPRAT T ON C.CDTIPOPRAT = T.CDTIPOPRAT
          WHERE C.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
            AND T.CDGRTIPOPR = C_GRAMATURA.CDGRTIPOPR
            AND VALOR_NA_LISTA(C.NRCONFIGCARDBASE, NVL(P_COLUNAS, 'T')) > 0;
         IF V_QTDE = 0 THEN
            CONTINUE;
         END IF;

         V_NRGRAMATURACARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('GRAMATURACARDBASE', 1));

         INSERT INTO GRAMATURACARDBASE
         (NRGRAMATURACARDBASE, NRCARDAPIOBASE, CDGRTIPOPR,
          VRGRAMATURA, SGUNIDADE, 
          IDTIPOGRAMATURA, NRTIPOPUBLICO,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         VALUES
         (V_NRGRAMATURACARDBASE, P_NRCARDAPIOBASEDEST, C_GRAMATURA.CDGRTIPOPR,
          C_GRAMATURA.VRGRAMATURA, C_GRAMATURA.SGUNIDADE,
          C_GRAMATURA.IDTIPOGRAMATURA, C_GRAMATURA.NRTIPOPUBLICO,
          P_NRORG, P_NRORG, P_NRORG,
          P_CDOPERADOR, P_CDOPERADOR,
          SYSDATE, SYSDATE);

         --ITENS DA GRAMATURA
         FOR C_ITEM IN
         (
            SELECT I.CDGRUPPROD, I.CDSUBGRPROD, I.CDPRODUTO, 
                   I.NRPORCIONAMENTO, I.VRGRAMATURA,
                   I.NRGRUPOPRATO, I.NRTIPOPREPAROTCO
              FROM ITEMGRAMCARDBASE I
             WHERE I.NRGRAMATURACARDBASE = C_GRAMATURA.NRGRAMATURACARDBASE
         ) LOOP
            V_NRITEMGRAMCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('ITEMGRAMCARDBASE', 1));

            INSERT INTO ITEMGRAMCARDBASE
            (NRITEMGRAMCARDBASE, NRGRAMATURACARDBASE,               
             CDGRUPPROD, CDSUBGRPROD, CDPRODUTO, 
             NRPORCIONAMENTO, VRGRAMATURA,
             NRGRUPOPRATO, NRTIPOPREPAROTCO,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRITEMGRAMCARDBASE, V_NRGRAMATURACARDBASE,
             C_ITEM.CDGRUPPROD, C_ITEM.CDSUBGRPROD, C_ITEM.CDPRODUTO,
             C_ITEM.NRPORCIONAMENTO, C_ITEM.VRGRAMATURA,
             C_ITEM.NRGRUPOPRATO, C_ITEM.NRTIPOPREPAROTCO,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         END LOOP;
      END LOOP;
   --=========================================================
   --=== Particularidade por segmento
   --=========================================================
      SELECT MAX(CDPARTSEGCARDBASE)
        INTO V_CDPARTSEGCARDBASE
        FROM PARTSEGCARDBASE
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEORI;

      V_CDPARTSEGCARDBASE := NVL(V_CDPARTSEGCARDBASE, '00000');
      V_CDPARTSEGCARDBASE := LPAD(TO_CHAR(TO_NUMBER(V_CDPARTSEGCARDBASE) + 1), 5, '0');

      FOR C_PARTSEG IN
      (
         SELECT P.NRPARTSEGCARDBASE, P.NRATIVCLISEGMER
           FROM PARTSEGCARDBASE P
          WHERE P.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
      ) LOOP
         -- Verifica se a particularidade j existe no destino
         SELECT MAX(NRPARTSEGCARDBASE)
           INTO V_NRPARTSEGCARDBASE
           FROM PARTSEGCARDBASE
          WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST
            AND NRATIVCLISEGMER = C_PARTSEG.NRATIVCLISEGMER;

         -- Verifica que a regra no possua itens que no foram escolhidos para importao
         SELECT COUNT(1)
           INTO V_QTDE
         FROM ITPARTSEGCARDBASE I           
         WHERE I.NRPARTSEGCARDBASE = C_PARTSEG.NRPARTSEGCARDBASE
          AND VALOR_NA_LISTA(I.NRCONFIGCARDBASE, NVL(P_COLUNAS, 'T')) = 0;
         -- Se possuir itens que no fazem parte do cardpio, avana para prxima regra
         IF V_QTDE > 0 THEN
            CONTINUE;
         END IF;

         -- Verifica se a regra possui segmento do cardapio base
         SELECT COUNT(1)
           INTO V_QTDE
           FROM ATIVCLISEGMER A
                INNER JOIN SEGMERCARDBASE S ON S.NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST AND 
                                               S.CDSEGMER = A.CDSEGMER
          WHERE NRATIVCLISEGMER = C_PARTSEG.NRATIVCLISEGMER;
         -- Se no possuir segmentos 
         IF V_QTDE = 0 THEN
            CONTINUE;
         END IF;         

         IF V_NRPARTSEGCARDBASE IS NULL THEN
            V_NRPARTSEGCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('PARTSEGCARDBASE', 1));

            INSERT INTO PARTSEGCARDBASE
            (NRPARTSEGCARDBASE, NRCARDAPIOBASE, 
             CDPARTSEGCARDBASE, NRATIVCLISEGMER,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRPARTSEGCARDBASE, P_NRCARDAPIOBASEDEST,
             V_CDPARTSEGCARDBASE, C_PARTSEG.NRATIVCLISEGMER,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         END IF;

         -- ITENS DA PARTICULARIDADE
         FOR C_ITEM IN
         (
            SELECT L.NRCONFIGCARDBASE, I.VRPERCENTUAL
              FROM ITPARTSEGCARDBASE I
                   INNER JOIN (
                                 SELECT SUBSTR(CDLISTA, 1, INSTR(CDLISTA, '|') - 1) AS NRCONFIGCARDBASEORI,
                                        SUBSTR(CDLISTA, INSTR(CDLISTA, '|') + 1, 50) AS NRCONFIGCARDBASE
                                   FROM LISTA
                                  WHERE NRLISTA = V_DE_PARA_CONFIG
                              ) L ON L.NRCONFIGCARDBASEORI = I.NRCONFIGCARDBASE
             WHERE NRPARTSEGCARDBASE = C_PARTSEG.NRPARTSEGCARDBASE
         ) LOOP
            V_NRITPARTSEGCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('ITPARTSEGCARDBASE', 1));
            INSERT INTO ITPARTSEGCARDBASE
            (NRITPARTSEGCARDBASE, NRPARTSEGCARDBASE,               
             NRCONFIGCARDBASE,VRPERCENTUAL,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRITPARTSEGCARDBASE, V_NRPARTSEGCARDBASE,
             C_ITEM.NRCONFIGCARDBASE, C_ITEM.VRPERCENTUAL,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         END LOOP;

         V_CDPARTSEGCARDBASE := LPAD(TO_CHAR(TO_NUMBER(V_CDPARTSEGCARDBASE) + 1), 5, '0');
      END LOOP;
   --=========================================================
   --=== Amostra
   --=========================================================   
      SELECT MAX(A.NRAMOSTRACARDBASE), MAX(LQ.NRCONFIGCARDBASE), MAX(LF.NRCONFIGCARDBASE)
        INTO V_NRAMOSTRACARDBASEORI, V_NRCONFIGCARDBASEQ, V_NRCONFIGCARDBASEF
        FROM AMOSTRACARDBASE A
             LEFT JOIN (
                           SELECT SUBSTR(CDLISTA, 1, INSTR(CDLISTA, '|') - 1) AS NRCONFIGCARDBASEORI,
                                  SUBSTR(CDLISTA, INSTR(CDLISTA, '|') + 1, 50) AS NRCONFIGCARDBASE
                             FROM LISTA
                            WHERE NRLISTA = V_DE_PARA_CONFIG
                        ) LQ ON LQ.NRCONFIGCARDBASEORI = A.NRCONFIGCARDBASEQ
             LEFT JOIN (
                           SELECT SUBSTR(CDLISTA, 1, INSTR(CDLISTA, '|') - 1) AS NRCONFIGCARDBASEORI,
                                  SUBSTR(CDLISTA, INSTR(CDLISTA, '|') + 1, 50) AS NRCONFIGCARDBASE
                             FROM LISTA
                            WHERE NRLISTA = V_DE_PARA_CONFIG
                        ) LF ON LF.NRCONFIGCARDBASEORI = A.NRCONFIGCARDBASEF
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEORI;

      IF V_NRAMOSTRACARDBASEORI IS NOT NULL THEN
         SELECT MAX(NRAMOSTRACARDBASE), MAX(NRCONFIGCARDBASEQ), MAX(NRCONFIGCARDBASEF)
           INTO V_NRAMOSTRACARDBASE, V_CNFQUENTEORI, V_CNFFRIOORI
           FROM AMOSTRACARDBASE
          WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASEDEST;
         -- No insere amostra se j estiver configurado no destino
         IF V_NRAMOSTRACARDBASE IS NULL THEN
            V_NRAMOSTRACARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('AMOSTRACARDBASE', 1));

            INSERT INTO AMOSTRACARDBASE
            (NRAMOSTRACARDBASE, NRCARDAPIOBASE, 
             NRCONFIGCARDBASEQ, NRCONFIGCARDBASEF,
             NRORG, NRORGINCLUSAO, NRORGULTATU,
             CDOPERINCLUSAO, CDOPERULTATU,
             DTINCLUSAO, DTULTATU)
            VALUES
            (V_NRAMOSTRACARDBASE, P_NRCARDAPIOBASEDEST,
             V_NRCONFIGCARDBASEQ, V_NRCONFIGCARDBASEF,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE);
         ELSE
            V_NRCONFIGCARDBASEQ := CASE WHEN V_CNFQUENTEORI IS NULL THEN V_NRCONFIGCARDBASEQ ELSE V_CNFQUENTEORI END;
            V_NRCONFIGCARDBASEF := CASE WHEN V_CNFFRIOORI IS NULL THEN V_NRCONFIGCARDBASEF ELSE V_CNFFRIOORI END;

            UPDATE AMOSTRACARDBASE
               SET NRCONFIGCARDBASEQ = V_NRCONFIGCARDBASEQ,
                   NRCONFIGCARDBASEF = V_NRCONFIGCARDBASEF,
                   DTULTATU = SYSDATE,
                   CDOPERULTATU = P_CDOPERADOR,
                   NRORGULTATU = P_NRORG
             WHERE NRAMOSTRACARDBASE = V_NRAMOSTRACARDBASE;               
         END IF;
      END IF;
   --=========================================================
   --=== Descartaveis
   --=========================================================
      FOR C_DESCART IN
      (
         SELECT L.NRCONFIGCARDBASE, D.IDFORMACALCULO
           FROM DESCARTCARDBASE D
                INNER JOIN (
                           SELECT SUBSTR(CDLISTA, 1, INSTR(CDLISTA, '|') - 1) AS NRCONFIGCARDBASEORI,
                                  SUBSTR(CDLISTA, INSTR(CDLISTA, '|') + 1, 50) AS NRCONFIGCARDBASE
                             FROM LISTA
                            WHERE NRLISTA = V_DE_PARA_CONFIG
                        ) L ON L.NRCONFIGCARDBASEORI = D.NRCONFIGCARDBASE
          WHERE D.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
      ) LOOP
         V_NRDESCARTCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('DESCARTCARDBASE', 1));

         INSERT INTO DESCARTCARDBASE
         (NRDESCARTCARDBASE, NRCARDAPIOBASE, 
          NRCONFIGCARDBASE, IDFORMACALCULO,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         VALUES
         (V_NRDESCARTCARDBASE, P_NRCARDAPIOBASEDEST,
          C_DESCART.NRCONFIGCARDBASE, C_DESCART.IDFORMACALCULO,
          P_NRORG, P_NRORG, P_NRORG,
          P_CDOPERADOR, P_CDOPERADOR,
          SYSDATE, SYSDATE);
      END LOOP;
   --=========================================================
   --=== Limpeza
   --=========================================================
      FOR C_LIMPEZA IN
      (
         SELECT LC.NRCONFIGCARDBASE, L.QTINICIAL, L.QTFINAL
           FROM LIMPEZACARDBASE L
                INNER JOIN (
                           SELECT SUBSTR(CDLISTA, 1, INSTR(CDLISTA, '|') - 1) AS NRCONFIGCARDBASEORI,
                                  SUBSTR(CDLISTA, INSTR(CDLISTA, '|') + 1, 50) AS NRCONFIGCARDBASE
                             FROM LISTA
                            WHERE NRLISTA = V_DE_PARA_CONFIG
                        ) LC ON LC.NRCONFIGCARDBASEORI = L.NRCONFIGCARDBASE
          WHERE L.NRCARDAPIOBASE = P_NRCARDAPIOBASEORI
      ) LOOP
         V_NRLIMPEZACARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('LIMPEZACARDBASE', 1));

         INSERT INTO LIMPEZACARDBASE
         (NRLIMPEZACARDBASE, NRCARDAPIOBASE, 
          NRCONFIGCARDBASE, QTINICIAL, QTFINAL,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)   
         VALUES
         (V_NRLIMPEZACARDBASE, P_NRCARDAPIOBASEDEST,
          C_LIMPEZA.NRCONFIGCARDBASE, C_LIMPEZA.QTINICIAL, C_LIMPEZA.QTFINAL,
          P_NRORG, P_NRORG,P_NRORG,
          P_CDOPERADOR, P_CDOPERADOR,
          SYSDATE, SYSDATE);
      END LOOP;

   END IF;   
END;
/