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;
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;

      -- Produtos
      SELECT COUNT(1)
        INTO V_QTDE
        FROM PRODUTOCARDTRAB
       WHERE NRCARDAPIOTRAB = P_NRCARDAPIOTRAB;

      IF V_QTDE > 0 THEN
         V_NRPRODUTOCARDTRAB := TO_NUMBER(RETORNA_NOVO_CODIGO('PRODUTOCARDTRAB', V_QTDE));

         INSERT INTO PRODUTOCARDTRAB
         (NRPRODUTOCARDTRAB, NRCARDAPIOTRAB,
          CDPRODUTO, CDMARCA, VRCUSTO,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         SELECT V_NRPRODUTOCARDTRAB + (ROW_NUMBER() OVER (ORDER BY P.NRPRODUTOCARDTRAB) - 1) AS NRPRODUTOCARDTRAB,
                V_NRCARDAPIOTRAB,
                P.CDPRODUTO, P.CDMARCA, P.VRCUSTO,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM PRODUTOCARDTRAB P
          WHERE P.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY P.NRPRODUTOCARDTRAB;
      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)
         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,
                IDPRODBASECARDTRAB, IDQTDEFIXCARDTRAB,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM RECEPRATCARDTRAB R
          WHERE R.NRCARDAPIOTRAB = P_NRCARDAPIOTRAB
          ORDER BY R.NRRECEPRATCARDTRAB;
      END IF;

      P_NRCARDAPIOTRABNOVO := V_NRCARDAPIOTRAB;
   END IF;
END;
/