/**
 * Faz a cópia de de uma composição para outra do cardápio base
 * @param {Number} P_NRCARDAPIOBASE Número da versão a ser duplicada
 * @param {Number} P_NRCOMPCARDBASE Número da versão de destino
 * @param {Number} P_NRDIACARDBASEDEST Número da organização logada
 * @param {Number} P_NRCONFIGCARDBASEDEST Número da organização logada
 * @param {Number} P_NRORG Número da organização
 * @param {Varchar2} P_CDOPERADOR Operador logado 
 * @param {Varchar2} P_SUBTITUIR Indica se substitui ou não o cardápio de destino caso o mesmo exista
 * revisada
 */
CREATE OR REPLACE PROCEDURE COPIA_PRATO_CARDBASE
(
   P_NRCARDAPIOBASE IN NUMBER,
	P_NRCOMPCARDBASE IN NUMBER,
	P_NRDIACARDBASEDEST IN NUMBER,
	P_NRCONFIGCARDBASEDEST IN NUMBER,
	P_NRORG IN NUMBER,
	P_CDOPERADOR IN VARCHAR2,
   P_SUBTITUIR IN VARCHAR2 DEFAULT 'S'
)
AS 
   V_QTDE NUMBER;  
   V_NRDIACARDBASEORI NUMBER;
   V_NRCONFIGCARDBASEORI NUMBER;
   V_NRCOMPCARDBASEDEST NUMBER;
   V_NRPRATCOMPCARDBASE NUMBER;   
   V_NRREGIAOPRATCARDBASE NUMBER;
BEGIN
   SELECT MAX(NRDIACARDBASE), MAX(NRCONFIGCARDBASE)
     INTO V_NRDIACARDBASEORI, V_NRCONFIGCARDBASEORI
     FROM COMPCARDBASE
    WHERE NRCOMPCARDBASE = P_NRCOMPCARDBASE
      AND NRCARDAPIOBASE = P_NRCARDAPIOBASE;
   
   IF V_NRDIACARDBASEORI IS NOT NULL AND 
      V_NRCONFIGCARDBASEORI IS NOT NULL AND
      (V_NRDIACARDBASEORI <> P_NRDIACARDBASEDEST OR 
      V_NRCONFIGCARDBASEORI <> P_NRCONFIGCARDBASEDEST) THEN
      
      SELECT MAX(NRCOMPCARDBASE)
        INTO V_NRCOMPCARDBASEDEST
        FROM COMPCARDBASE
       WHERE NRCARDAPIOBASE = P_NRCARDAPIOBASE
         AND NRDIACARDBASE = P_NRDIACARDBASEDEST
         AND NRCONFIGCARDBASE = P_NRCONFIGCARDBASEDEST;

      IF V_NRCOMPCARDBASEDEST IS NOT NULL THEN

         /*Se não pode substituir sai da função*/
         IF P_SUBTITUIR = 'N' THEN
            RETURN;
         ELSE
            DELETE FROM REGIAOPRATCARDBASE R
             WHERE EXISTS (SELECT 1
                             FROM PRATCOMPCARDBASE P
                            WHERE P.NRPRATCOMPCARDBASE = R.NRPRATCOMPCARDBASE
                              AND P.NRCOMPCARDBASE = V_NRCOMPCARDBASEDEST);
            
            DELETE FROM PRATCOMPCARDBASE
             WHERE NRCOMPCARDBASE = V_NRCOMPCARDBASEDEST;
         END IF;
      ELSE      
         V_NRCOMPCARDBASEDEST := TO_NUMBER(RETORNA_NOVO_CODIGO('COMPCARDBASE', 1));
         INSERT INTO COMPCARDBASE
         (NRCOMPCARDBASE, NRCARDAPIOBASE, NRDIACARDBASE, NRCONFIGCARDBASE,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         VALUES
         (V_NRCOMPCARDBASEDEST, P_NRCARDAPIOBASE, P_NRDIACARDBASEDEST, P_NRCONFIGCARDBASEDEST,
          P_NRORG, P_NRORG, P_NRORG,
          P_CDOPERADOR, P_CDOPERADOR,
          SYSDATE, SYSDATE);         
      END IF;

      SELECT COUNT(1)
        INTO V_QTDE
        FROM PRATCOMPCARDBASE
       WHERE NRCOMPCARDBASE = P_NRCOMPCARDBASE;

      IF V_QTDE > 0 THEN
         V_NRPRATCOMPCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('PRATCOMPCARDBASE', V_QTDE));

         INSERT INTO PRATCOMPCARDBASE
         (NRPRATCOMPCARDBASE, NRCOMPCARDBASE, 
          CDFILAUXPRAT, CDPRATO, IDPRATOEXIBICAO,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         SELECT V_NRPRATCOMPCARDBASE + (ROW_NUMBER() OVER (ORDER BY P.NRPRATCOMPCARDBASE) - 1),
                V_NRCOMPCARDBASEDEST,
                P.CDFILAUXPRAT, P.CDPRATO, P.IDPRATOEXIBICAO,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM PRATCOMPCARDBASE P                
          WHERE P.NRCOMPCARDBASE = P_NRCOMPCARDBASE
          ORDER BY P.NRPRATCOMPCARDBASE; --Importante para inclusão múltipla de região
      END IF;

      -- Regiões
      SELECT COUNT(1)
        INTO V_QTDE
        FROM REGIAOPRATCARDBASE R
             INNER JOIN PRATCOMPCARDBASE P ON P.NRPRATCOMPCARDBASE = R.NRPRATCOMPCARDBASE
       WHERE P.NRCOMPCARDBASE = P_NRCOMPCARDBASE;

      IF V_QTDE > 0 THEN
         V_NRREGIAOPRATCARDBASE := TO_NUMBER(RETORNA_NOVO_CODIGO('REGIAOPRATCARDBASE', V_QTDE));

         INSERT INTO REGIAOPRATCARDBASE
         (NRREGIAOPRATCARDBASE, NRPRATCOMPCARDBASE, CDREGIAO,
          NRORG, NRORGINCLUSAO, NRORGULTATU,
          CDOPERINCLUSAO, CDOPERULTATU,
          DTINCLUSAO, DTULTATU)
         SELECT V_NRREGIAOPRATCARDBASE + (ROW_NUMBER() OVER (ORDER BY R.NRREGIAOPRATCARDBASE) - 1),
                PD.NRPRATCOMPCARDBASEDEST,
                R.CDREGIAO,
                P_NRORG, P_NRORG, P_NRORG,
                P_CDOPERADOR, P_CDOPERADOR,
                SYSDATE, SYSDATE
           FROM REGIAOPRATCARDBASE R
                INNER JOIN PRATCOMPCARDBASE P ON P.NRPRATCOMPCARDBASE = R.NRPRATCOMPCARDBASE                
                INNER JOIN 
                (SELECT P.NRPRATCOMPCARDBASE,
                        V_NRPRATCOMPCARDBASE + (ROW_NUMBER() OVER (ORDER BY P.NRPRATCOMPCARDBASE) - 1) AS NRPRATCOMPCARDBASEDEST
                   FROM PRATCOMPCARDBASE P
                  WHERE P.NRCOMPCARDBASE = P_NRCOMPCARDBASE
                  ORDER BY P.NRPRATCOMPCARDBASE) PD ON PD.NRPRATCOMPCARDBASE = P.NRPRATCOMPCARDBASE
          WHERE P.NRCOMPCARDBASE = P_NRCOMPCARDBASE
          ORDER BY R.NRREGIAOPRATCARDBASE;
      END IF;
   END IF;       
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('COPIA_PRATO_CARDBASE','6.35.009'); END;
/
