CREATE OR REPLACE PROCEDURE COPIA_CARDAPIOTRAB
(
P_NRCARDAPIOTRAB IN NUMBER,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER,
P_NRCARDAPIOTRABNOVO OUT NUMBER
)
AS
   V_QTDE NUMBER;
   V_NRCARDAPIOTRAB NUMBER;
   V_NRCONFIGCARDTRAB NUMBER;
   V_NRDIACARDTRAB NUMBER;
   V_NRPRODUTOCARDTRAB NUMBER;
   V_NRCOMPCARDTRAB NUMBER;
   V_NRRECEPRATCARDTRAB NUMBER;
   V_NRPRODLISTAPRECOCARDTRAB NUMBER;
   V_NRLISTAPRECOTCO NUMBER;
   V_NRPRODLISTAPRECOTCO NUMBER;
BEGIN
   SELECT COUNT(1)
     INTO V_QTDE
     FROM CARDAPIOTRAB
    WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;
   IF V_QTDE > 0 THEN
      V_NRCARDAPIOTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('CARDAPIOTRAB', 1));

      INSERT INTO CARDAPIOTRAB
      (NRCARDAPIOTRAB, CDREGIAO, DTCUSTO, CDTIPOCUSTO,
       IDORIGEM, NRCARDAPIOBASE, NRLISTAPRECOTCO,
       NRORG, NRORGINCLUSAO, NRORGULTATU,
       CDOPERINCLUSAO, CDOPERULTATU,
       DTINCLUSAO, DTULTATU)
      SELECT V_NRCARDAPIOTRAB, C.CDREGIAO, C.DTCUSTO, C.CDTIPOCUSTO,
             C.IDORIGEM, C.NRCARDAPIOBASE, C.NRLISTAPRECOTCO,
             P_NRORG, P_NRORG, P_NRORG,
             P_CDOPERADOR, P_CDOPERADOR,
             SYSDATE, SYSDATE
        FROM CARDAPIOTRAB C 
       WHERE C.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;

      -- Configuracao
      SELECT COUNT(1)
        INTO V_QTDE
        FROM CONFIGCARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;

      IF V_QTDE > 0 THEN
         V_NRCONFIGCARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('CONFIGCARDTRAB', V_QTDE));

         INSERT INTO CONFIGCARDTRAB
         (NRCONFIGCARDTRAB, NRCARDAPIOTRAB, CDTIPOPRAT,
          DSCOMPLEMENTO, IDCLASSIFICACAO, NRORDEM, 
          IDTEMPERATURA, NRCONFIGCARDBASE,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         SELECT V_NRCONFIGCARDTRAB + (ROW_NUMBER() OVER (ORDER BY C.NRCONFIGCARDTRAB) - 1) AS NRCONFIGCARDTRAB,
                V_NRCARDAPIOTRAB,
                C.CDTIPOPRAT, C.DSCOMPLEMENTO, C.IDCLASSIFICACAO, C.NRORDEM,
                C.IDTEMPERATURA, C.NRCONFIGCARDBASE,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM CONFIGCARDTRAB C
          WHERE C.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY C.NRCONFIGCARDTRAB;
      END IF;

      -- Dias
      SELECT COUNT(1)
        INTO V_QTDE
        FROM DIACARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;

      IF V_QTDE > 0 THEN
         V_NRDIACARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('DIACARDTRAB', V_QTDE));

         INSERT INTO DIACARDTRAB
         (NRDIACARDTRAB, NRCARDAPIOTRAB,
          NRSEMANA, NRDIASEMANA, QTCOMENSAIS,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         SELECT V_NRDIACARDTRAB + (ROW_NUMBER() OVER (ORDER BY D.NRDIACARDTRAB) - 1) AS V_NRDIACARDTRAB,
                V_NRCARDAPIOTRAB,
                D.NRSEMANA, D.NRDIASEMANA, D.QTCOMENSAIS,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM DIACARDTRAB D
          WHERE D.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY D.NRDIACARDTRAB;            
      END IF;

      -- Composicao
      SELECT COUNT(1)
        INTO V_QTDE
        FROM COMPCARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;

      IF V_QTDE > 0 THEN
         V_NRCOMPCARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('COMPCARDTRAB', V_QTDE));

         INSERT INTO COMPCARDTRAB
         (NRCOMPCARDTRAB, NRCARDAPIOTRAB,
          NRDIACARDTRAB, NRCONFIGCARDTRAB,
          CDFILAUXPRAT, CDPRATO, VRPECONSCARDTRAB,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         SELECT V_NRCOMPCARDTRAB + (ROW_NUMBER() OVER (ORDER BY C.NRCOMPCARDTRAB) - 1) AS NRCOMPCARDTRAB, 
                V_NRCARDAPIOTRAB,
                DDEST.NRDIACARDTRABDEST,
                CNFDEST.NRCONFIGCARDTRABDEST,
                C.CDFILAUXPRAT, C.CDPRATO, C.VRPECONSCARDTRAB, 
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM COMPCARDTRAB C
                INNER JOIN 
                (SELECT D.NRDIACARDTRAB, 
                        V_NRDIACARDTRAB + (ROW_NUMBER() OVER (ORDER BY D.NRDIACARDTRAB) - 1) AS NRDIACARDTRABDEST                   
                   FROM DIACARDTRAB D
                  WHERE D.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
                  ORDER BY D.NRDIACARDTRAB
                ) DDEST ON DDEST.NRDIACARDTRAB = C.NRDIACARDTRAB
                INNER JOIN
                (SELECT C.NRCONFIGCARDTRAB,
                        V_NRCONFIGCARDTRAB + (ROW_NUMBER() OVER (ORDER BY C.NRCONFIGCARDTRAB) - 1) AS NRCONFIGCARDTRABDEST
                   FROM CONFIGCARDTRAB C
                  WHERE C.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
                  ORDER BY C.NRCONFIGCARDTRAB
                ) CNFDEST ON CNFDEST.NRCONFIGCARDTRAB = C.NRCONFIGCARDTRAB
          WHERE C.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY C.NRCOMPCARDTRAB;
      END IF;

      -- Receitas
      SELECT COUNT(1)
        INTO V_QTDE
        FROM RECEPRATCARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;

      IF V_QTDE > 0 THEN
         V_NRRECEPRATCARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('RECEPRATCARDTRAB', V_QTDE));

         INSERT INTO RECEPRATCARDTRAB
         (NRRECEPRATCARDTRAB, NRCARDAPIOTRAB,
          CDFILAUXPRAT, CDPRATO,            
          CDPRODUTO, QTPRODRECCARDTRAB,
          VRPEFATCOCCARDTRAB, VRPEFATCORCARDTRAB,
          SGUNIDADE, QTBRUTACARDTRABUNR,
          IDPRODBASECARDTRAB, IDQTDEFIXCARDTRAB,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU, IDINCLUIDO)
         SELECT V_NRRECEPRATCARDTRAB + (ROW_NUMBER() OVER (ORDER BY R.NRRECEPRATCARDTRAB) - 1) AS NRRECEPRATCARDTRAB,
                V_NRCARDAPIOTRAB,
                R.CDFILAUXPRAT, R.CDPRATO,
                CDPRODUTO, QTPRODRECCARDTRAB,
                VRPEFATCOCCARDTRAB, VRPEFATCORCARDTRAB,
                SGUNIDADE, QTBRUTACARDTRABUNR,
                R.IDPRODBASECARDTRAB, R.IDQTDEFIXCARDTRAB,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE, R.IDINCLUIDO
           FROM RECEPRATCARDTRAB R
          WHERE R.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY R.NRRECEPRATCARDTRAB;
      END IF;

      -- Lista de Precos
      SELECT COUNT(1)
        INTO V_QTDE
        FROM PRODLISTAPRECOCARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;
      
      IF V_QTDE > 0 THEN
         V_NRPRODLISTAPRECOCARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('PRODLISTAPRECOCARDTRAB', V_QTDE));

         INSERT INTO PRODLISTAPRECOCARDTRAB
            (NRPRODLISTAPRECOCARDTRAB, NRCARDAPIOTRAB,
            NRPRODLISTAPRECOTCO, NRLISTAPRECOTCO,
            IDESCOLHIDO,  IDIMPORTADO,  CDPRODESTO,  
            VRCUSTO,  VRPEACRESCIMO,  CDMARCA,  
            CDREGIAO,  CDFORNECED,  CDPRODUTO,  
            DTINIVALITM,  DTFINVALITM,  VRPRECUNIITM,  
            VRPRECOITMPI,  VRPRECECOITM,  VRFATOCONV,  
            IDFORNECEDOROL,  IDCUSTOPELAMEDIA,  IDFORNEPRIOTCO,
            CDFILIAL, NRCOLEPRECO, DTCUSTO,
            NRORG,  NRORGINCLUSAO,  NRORGULTATU,  
            DTINCLUSAO,  DTULTATU,  
            CDOPERINCLUSAO,  CDOPERULTATU)
         SELECT V_NRPRODLISTAPRECOCARDTRAB + (ROW_NUMBER() OVER (ORDER BY P.NRPRODLISTAPRECOCARDTRAB) - 1) AS NRPRODLISTAPRECOCARDTRAB,
                V_NRCARDAPIOTRAB,
                P.NRPRODLISTAPRECOTCO, P.NRLISTAPRECOTCO,
                P.IDESCOLHIDO,  P.IDIMPORTADO,  P.CDPRODESTO,  
                P.VRCUSTO,  P.VRPEACRESCIMO,  P.CDMARCA,  
                P.CDREGIAO,  P.CDFORNECED,  P.CDPRODUTO,  
                P.DTINIVALITM,  P.DTFINVALITM,  P.VRPRECUNIITM,  
                P.VRPRECOITMPI,  P.VRPRECECOITM,  P.VRFATOCONV,  
                P.IDFORNECEDOROL,  P.IDCUSTOPELAMEDIA,  P.IDFORNEPRIOTCO,
                P.CDFILIAL, P.NRCOLEPRECO, P.DTCUSTO,
                P_NRORG, P_NRORG, P_NRORG,
                SYSDATE, SYSDATE,
                P_CDOPERADOR, P_CDOPERADOR                
           FROM PRODLISTAPRECOCARDTRAB P
          WHERE P.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY P.NRPRODLISTAPRECOCARDTRAB;
      END IF;

      -- Produtos
      FOR C_PRODUTOS IN 
      (
         SELECT CDPRODUTO, CDMARCA, VRCUSTO, NRPRODLISTAPRECOCARDTRAB
           FROM PRODUTOCARDTRAB P
          WHERE P.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
      ) LOOP
         V_NRPRODUTOCARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('PRODUTOCARDTRAB', 1));
         V_NRPRODLISTAPRECOCARDTRAB := NULL;
         -- Obtem a nova referencia a lista de precos, no novo cardapio de trabalho
         IF C_PRODUTOS.NRPRODLISTAPRECOCARDTRAB IS NOT NULL THEN
            SELECT MAX(NRLISTAPRECOTCO), MAX(NRPRODLISTAPRECOTCO)
              INTO V_NRLISTAPRECOTCO, V_NRPRODLISTAPRECOTCO
              FROM PRODLISTAPRECOCARDTRAB
             WHERE NRPRODLISTAPRECOCARDTRAB = C_PRODUTOS.NRPRODLISTAPRECOCARDTRAB;
            
            IF V_NRPRODLISTAPRECOTCO IS NOT NULL THEN
               SELECT MAX(NRPRODLISTAPRECOCARDTRAB)
                 INTO V_NRPRODLISTAPRECOCARDTRAB
                 FROM PRODLISTAPRECOCARDTRAB
                WHERE NRCARDAPIOTRAB = V_NRCARDAPIOTRAB
                  AND CDPRODESTO = C_PRODUTOS.CDPRODUTO
                  AND NRLISTAPRECOTCO = V_NRLISTAPRECOTCO
                  AND NRPRODLISTAPRECOTCO = V_NRPRODLISTAPRECOTCO
                  AND IDESCOLHIDO = 'S';
            END IF;
         END IF;
         
         INSERT INTO PRODUTOCARDTRAB
         (NRPRODUTOCARDTRAB, NRCARDAPIOTRAB,
          CDPRODUTO, CDMARCA, VRCUSTO,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU, NRPRODLISTAPRECOCARDTRAB)
         VALUES
         (V_NRPRODUTOCARDTRAB, V_NRCARDAPIOTRAB,
          C_PRODUTOS.CDPRODUTO, C_PRODUTOS.CDMARCA, C_PRODUTOS.VRCUSTO,
          P_NRORG, P_NRORG, P_NRORG,
          P_CDOPERADOR, P_CDOPERADOR,
          SYSDATE, SYSDATE, V_NRPRODLISTAPRECOCARDTRAB);
      END LOOP;

      P_NRCARDAPIOTRABNOVO := V_NRCARDAPIOTRAB;
   END IF;
END;
/