create or replace PROCEDURE REPLICA_SERVICO_CARD_PLA
(
	P_DTCARDORI IN DATE,
	P_CDFILIAL IN VARCHAR2,
	P_CDSERVFILI IN VARCHAR2,
	P_DTCARDDEST IN DATE,
	P_MAPADIAS IN VARCHAR2,
	P_CDOPERADOR IN VARCHAR2,
	P_CDFILIALLOGADA IN VARCHAR2,
	P_NRORG IN NUMBER,
    P_SOMENTE_VALIDACAO IN VARCHAR2,
	P_NRORDEM_ERROS IN OUT NUMBER,
	P_LISTAERROS IN OUT NUMBER
)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;	
   V_ULTDIA_DTMES_ATUAL DATE;
   V_ULTDIA_DTMES_ORIGEM DATE;
   V_CDCHAVE VARCHAR2(500);	
   V_IDCUSPADCAR VARCHAR2(1); 
   V_IDCARDIAUTIL VARCHAR2(1);
   V_TIPOCARDAPIO VARCHAR2(1);	
   V_IDMANCORCARDALT VARCHAR2(1);
   V_IDCARDDESCENTR VARCHAR2(1);
   V_IDSERVTRAN VARCHAR2(1);
   V_QTDE NUMBER;
   V_EXISTE_RETIRADA VARCHAR2(1);
   V_EXISTE_FATURAMENTO VARCHAR2(1);
   V_EXISTE_DISTRIBUICAO VARCHAR2(1);
   V_IDTIPOSERV VARCHAR2(1);
   V_NRCARDAPIO VARCHAR2(10);
   V_RECEITA_UTILIZADA VARCHAR2(1);
   V_DTLIBECARDMATR DATE;
   V_DTAPROCARDFILI DATE;
   V_DTAPROCARDMATR DATE;
   V_DTLIMAPROVFILI DATE;
   V_VRCUSTPADR FLOAT;
   V_IDUTILDTPROC VARCHAR2(1);
   V_DTORI DATE;
   V_DIA_SUBSTITUTO DATE;
   V_FERIADO_FILI_ORIGEM VARCHAR2(1);
   V_FERIADO_FILI_DESTINO VARCHAR2(1);   
   V_FERIADO_DESTINO VARCHAR2(1);
   V_DTPROCESSA DATE;
   V_DTINATIVAUTO DATE;   

   V_QTCOMESTICAR FLOAT;
   V_QTCLIESTICAR FLOAT;
   V_QTDOAESTICAR FLOAT;
   V_QTFUNESTICAR FLOAT;

   V_QTESTITURNO1 FLOAT;
   V_QTESTITURNO2 FLOAT;
   V_QTESTITURNO3 FLOAT;
   V_QTESTITURNO4 FLOAT;

   V_QTCOMESTISEG FLOAT;
   V_QTCOMESTITER FLOAT;
   V_QTCOMESTIQUA FLOAT;
   V_QTCOMESTIQUI FLOAT;
   V_QTCOMESTISEX FLOAT;
   V_QTCOMESTISAB FLOAT;
   V_QTCOMESTIDOM FLOAT;
   V_QTCOMESTIFER FLOAT;
   V_QTCLIESTISEG FLOAT;
   V_QTCLIESTITER FLOAT;
   V_QTCLIESTIQUA FLOAT;
   V_QTCLIESTIQUI FLOAT;
   V_QTCLIESTISEX FLOAT;
   V_QTCLIESTISAB FLOAT;
   V_QTCLIESTIDOM FLOAT;
   V_QTCLIESTIFER FLOAT;
   V_QTFUNESTISEG FLOAT;
   V_QTFUNESTITER FLOAT;
   V_QTFUNESTIQUA FLOAT;
   V_QTFUNESTIQUI FLOAT;
   V_QTFUNESTISEX FLOAT;
   V_QTFUNESTISAB FLOAT;
   V_QTFUNESTIDOM FLOAT;
   V_QTFUNESTIFER FLOAT;
   V_QTDOAESTISEG FLOAT;
   V_QTDOAESTITER FLOAT;
   V_QTDOAESTIQUA FLOAT;
   V_QTDOAESTIQUI FLOAT;
   V_QTDOAESTISEX FLOAT;
   V_QTDOAESTISAB FLOAT;
   V_QTDOAESTIDOM FLOAT;
   V_QTDOAESTIFER FLOAT;

   V_NMPRATO VARCHAR2(500);
   V_PRATO_AGENDADO_INATIVACAO VARCHAR2(1);
   V_INSERE_COMPCARD VARCHAR2(1);

   V_DIA_SEMANA VARCHAR2(2);
   V_TEXTO_DIA_SEMANA VARCHAR2(3);

   /* Contador dos erros dessa chamada da procedure, para dar rollback ou commit ao final*/
   V_NRORDEM_ERROS NUMBER;

	/* Procedure auxiliar para agilizar incluso na lista de erros */
   PROCEDURE I_L(MENSAGEM IN VARCHAR2)
   AS   
   BEGIN
   	  P_NRORDEM_ERROS := P_NRORDEM_ERROS + 1;
   	  V_NRORDEM_ERROS := V_NRORDEM_ERROS + 1;
      INSERE_LISTA_ERROS(P_CDFILIALLOGADA, MENSAGEM,
                         P_CDOPERADOR, P_NRORG, P_LISTAERROS,
                         P_NRORDEM_ERROS, V_CDCHAVE);
   END;

   /* Determina os campos de comensais*/
   PROCEDURE DETERMINA_COMENSAIS
   AS   	
   BEGIN
      V_QTCOMESTICAR := 0.00;
   	  V_QTCLIESTICAR := 0.00;
   	  V_QTDOAESTICAR := 0.00;
   	  V_QTFUNESTICAR := 0.00;
   	  IF V_FERIADO_DESTINO = 'S' THEN
         V_QTCOMESTICAR := V_QTCOMESTIFER;
   		 V_QTCLIESTICAR := V_QTCLIESTIFER;
   		 V_QTFUNESTICAR := V_QTFUNESTIFER;
   		 V_QTDOAESTICAR := V_QTDOAESTIFER;
   		 V_TEXTO_DIA_SEMANA := 'FER';
   	  ELSE
         IF V_DIA_SEMANA = '1' THEN
	   		V_QTCOMESTICAR := V_QTCOMESTIDOM;
	   		V_QTCLIESTICAR := V_QTCLIESTIDOM;
	   		V_QTFUNESTICAR := V_QTFUNESTIDOM;
	   		V_QTDOAESTICAR := V_QTDOAESTIDOM;
	   		V_TEXTO_DIA_SEMANA := 'DOM';
   		 ELSIF V_DIA_SEMANA = '2' THEN
   		    V_QTCOMESTICAR := V_QTCOMESTISEG;
	   		V_QTCLIESTICAR := V_QTCLIESTISEG;
	   		V_QTFUNESTICAR := V_QTFUNESTISEG;
	   		V_QTDOAESTICAR := V_QTDOAESTISEG;
	   		V_TEXTO_DIA_SEMANA := 'SEG';
   		 ELSIF V_DIA_SEMANA = '3' THEN
   			V_QTCOMESTICAR := V_QTCOMESTITER;
	   		V_QTCLIESTICAR := V_QTCLIESTITER;
	   		V_QTFUNESTICAR := V_QTFUNESTITER;
	   		V_QTDOAESTICAR := V_QTDOAESTITER;   		
	   		V_TEXTO_DIA_SEMANA := 'TER';
   		 ELSIF V_DIA_SEMANA = '4' THEN
   			V_QTCOMESTICAR := V_QTCOMESTIQUA;
	   		V_QTCLIESTICAR := V_QTCLIESTIQUA;
	   		V_QTFUNESTICAR := V_QTFUNESTIQUA;
	   		V_QTDOAESTICAR := V_QTDOAESTIQUA;   		
	   		V_TEXTO_DIA_SEMANA := 'QUA';
   		 ELSIF V_DIA_SEMANA = '5' THEN
   			V_QTCOMESTICAR := V_QTCOMESTIQUI;
	   		V_QTCLIESTICAR := V_QTCLIESTIQUI;
	   		V_QTFUNESTICAR := V_QTFUNESTIQUI;
	   		V_QTDOAESTICAR := V_QTDOAESTIQUI;   		
	   		V_TEXTO_DIA_SEMANA := 'QUI';
   		 ELSIF V_DIA_SEMANA = '6' THEN
   			V_QTCOMESTICAR := V_QTCOMESTISEX;
	   		V_QTCLIESTICAR := V_QTCLIESTISEX;
	   		V_QTFUNESTICAR := V_QTFUNESTISEX;
	   		V_QTDOAESTICAR := V_QTDOAESTISEX;
	   		V_TEXTO_DIA_SEMANA := 'SEX';
   		 ELSIF V_DIA_SEMANA = '7' THEN
   			V_QTCOMESTICAR := V_QTCOMESTISAB;
	   		V_QTCLIESTICAR := V_QTCLIESTISAB;
	   		V_QTFUNESTICAR := V_QTFUNESTISAB;
	   		V_QTDOAESTICAR := V_QTDOAESTISAB;   		
	   		V_TEXTO_DIA_SEMANA := 'SAB';
         END IF;
   	  END IF;

   	  SELECT MAX("1"),MAX("2"),MAX("3"),MAX("4")
   	    INTO V_QTESTITURNO1, V_QTESTITURNO2, V_QTESTITURNO3, V_QTESTITURNO4
		FROM
		(
		 SELECT CDTURNO, CASE WHEN V_FERIADO_DESTINO = 'S' THEN QTCOMESTTFER
		                      WHEN V_DIA_SEMANA = '1' THEN QTCOMESTTDOM
		     		          WHEN V_DIA_SEMANA = '2' THEN QTCOMESTTSEG
					            WHEN V_DIA_SEMANA = '3' THEN QTCOMESTTTER
					            WHEN V_DIA_SEMANA = '4' THEN QTCOMESTTQUA
					            WHEN V_DIA_SEMANA = '5' THEN QTCOMESTTQUI
					            WHEN V_DIA_SEMANA = '6' THEN QTCOMESTTSEX
					            WHEN V_DIA_SEMANA = '7' THEN QTCOMESTTSAB
		                END AS QTCOMENSAIS
		  FROM TURNOSERV
		 WHERE CDFILIAL = P_CDFILIAL
		   AND CDSERVFILI = P_CDSERVFILI
		) PIVOT
		(MAX(QTCOMENSAIS) FOR CDTURNO IN (1,2,3,4));


		V_QTESTITURNO1 := NVL(V_QTESTITURNO1, 0.00);
		V_QTESTITURNO2 := NVL(V_QTESTITURNO2, 0.00);
		V_QTESTITURNO3 := NVL(V_QTESTITURNO3, 0.00);
		V_QTESTITURNO4 := NVL(V_QTESTITURNO4, 0.00);
   END;

BEGIN
	V_NRORDEM_ERROS := 0;
	BEGIN
/*#######################################################################################################################################################################				
	   Parametros do sistema
#########################################################################################################################################################################*/

		/* Parametros da filial*/
		SELECT MAX(IDCUSPADCAR), NVL(MAX(IDCARDIAUTIL), 'N')
        INTO V_IDCUSPADCAR, V_IDCARDIAUTIL
        FROM PARAMPLANEJA
       WHERE CDFILIAL = P_CDFILIAL;

      /* Parametros gerais*/
      SELECT MAX(IDUTCARDPADR), MAX(IDMANCORCARDALT), MAX(IDUTILDTPROC)
      INTO   V_TIPOCARDAPIO, V_IDMANCORCARDALT, V_IDUTILDTPROC
      FROM   PARAMGERAL;

      V_TIPOCARDAPIO := NVL(V_TIPOCARDAPIO, 'N');
      V_IDMANCORCARDALT := NVL(V_IDMANCORCARDALT, 'N');

      /*Dados do servico de destino*/
      SELECT NVL(MAX(IDCARDDESCENTR), 'N'), NVL(MAX(IDSERVTRAN),'N'), NVL(MAX(IDTIPOSERV), 'N')
        INTO V_IDCARDDESCENTR, V_IDSERVTRAN, V_IDTIPOSERV
        FROM SERVICO
      WHERE  CDFILIAL   = P_CDFILIAL AND
             CDSERVFILI = P_CDSERVFILI;


		V_ULTDIA_DTMES_ATUAL := LAST_DAY(P_DTCARDDEST);
		V_ULTDIA_DTMES_ORIGEM := LAST_DAY(P_DTCARDORI);
		V_CDCHAVE := P_CDFILIAL || '|' || P_CDSERVFILI || '|' || TO_CHAR(P_DTCARDDEST, 'YYYYMMDD') || '|' || 'D';

      SELECT MAX(DTPROCESSA)
        INTO V_DTPROCESSA
        FROM PARAMFILIAL
       WHERE CDFILIAL = P_CDFILIAL;

      /* Comensais cadastrados no servico*/
      SELECT NVL(MAX(QTCOMESTISEG),0),NVL(MAX(QTCOMESTITER),0),NVL(MAX(QTCOMESTIQUA),0),NVL(MAX(QTCOMESTIQUI),0),NVL(MAX(QTCOMESTISEX),0),NVL(MAX(QTCOMESTISAB),0),NVL(MAX(QTCOMESTIDOM),0),NVL(MAX(QTCOMESTIFER),0),
             NVL(MAX(QTCLIESTISEG),0),NVL(MAX(QTCLIESTITER),0),NVL(MAX(QTCLIESTIQUA),0),NVL(MAX(QTCLIESTIQUI),0),NVL(MAX(QTCLIESTISEX),0),NVL(MAX(QTCLIESTISAB),0),NVL(MAX(QTCLIESTIDOM),0),NVL(MAX(QTCLIESTIFER),0),
             NVL(MAX(QTFUNESTISEG),0),NVL(MAX(QTFUNESTITER),0),NVL(MAX(QTFUNESTIQUA),0),NVL(MAX(QTFUNESTIQUI),0),NVL(MAX(QTFUNESTISEX),0),NVL(MAX(QTFUNESTISAB),0),NVL(MAX(QTFUNESTIDOM),0),NVL(MAX(QTFUNESTIFER),0),
             NVL(MAX(QTDOAESTISEG),0),NVL(MAX(QTDOAESTITER),0),NVL(MAX(QTDOAESTIQUA),0),NVL(MAX(QTDOAESTIQUI),0),NVL(MAX(QTDOAESTISEX),0),NVL(MAX(QTDOAESTISAB),0),NVL(MAX(QTDOAESTIDOM),0),NVL(MAX(QTDOAESTIFER),0)
        INTO V_QTCOMESTISEG,V_QTCOMESTITER,V_QTCOMESTIQUA,V_QTCOMESTIQUI,V_QTCOMESTISEX,V_QTCOMESTISAB,V_QTCOMESTIDOM,V_QTCOMESTIFER,
		       V_QTCLIESTISEG,V_QTCLIESTITER,V_QTCLIESTIQUA,V_QTCLIESTIQUI,V_QTCLIESTISEX,V_QTCLIESTISAB,V_QTCLIESTIDOM,V_QTCLIESTIFER,
		       V_QTFUNESTISEG,V_QTFUNESTITER,V_QTFUNESTIQUA,V_QTFUNESTIQUI,V_QTFUNESTISEX,V_QTFUNESTISAB,V_QTFUNESTIDOM,V_QTFUNESTIFER,
		       V_QTDOAESTISEG,V_QTDOAESTITER,V_QTDOAESTIQUA,V_QTDOAESTIQUI,V_QTDOAESTISEX,V_QTDOAESTISAB,V_QTDOAESTIDOM,V_QTDOAESTIFER
			  FROM SERVICO
			 WHERE CDFILIAL = P_CDFILIAL
			   AND CDSERVFILI = P_CDSERVFILI;		

      V_DTPROCESSA := NVL(V_DTPROCESSA, TRUNC(SYSDATE));

      IF NVL(V_IDUTILDTPROC, 'N') <> 'S' THEN
         V_DTPROCESSA := TRUNC(SYSDATE);
      END IF;

      /* Se o cardapio do mes de destino ja iniciou, nao deixa fazer a replicacao*/
		IF V_DTPROCESSA >= P_DTCARDDEST THEN
         /* Cardapio de destino ja foi iniciado*/
         I_L(MSG('CARDAPIO_EM_ANDAMENTO'));
      END IF;
      /* Se origem = destino, impede replicacao*/
      IF P_DTCARDORI = P_DTCARDDEST THEN
         /* Cardapio de origem nao pode ser utilizado como destino da replicacao*/
         I_L(MSG('ORIGEM_IGUAL_DESTINO'));
      END IF;
      /* Somente segue a importacao se origem nao e igual ao destino*/
      IF P_DTCARDORI <> P_DTCARDDEST THEN
/*#######################################################################################################################################################################				
   		Verifica Custo Padrao
#########################################################################################################################################################################
			 Custo padrao
			 Verifica se existe custo padaro para o servi?o de destino */
         IF V_IDCUSPADCAR = 'S' THEN
            SELECT MAX(VRCUSTPADR) AS VRCUSTPADR
              INTO V_VRCUSTPADR
              FROM CUSTPADR
             WHERE (CDFILIAL   = P_CDFILIAL) 
               AND (CDSERVFILI = P_CDSERVFILI)
               AND (DTREFCUSPADR = P_DTCARDDEST);

            IF V_VRCUSTPADR IS NULL THEN
               /* Custo padrao nao cadastrado no servico para o mes*/
               I_L(MSG('CUSTO_PADRAO_N_CAD'));
            END IF;
         END IF;

/*#######################################################################################################################################################################				
   		Aprovacao / Liberacao de Cardapio descentralizado
#########################################################################################################################################################################
			 Aprovacao do Cardapio - DESCENTRALIZADO				*/
         IF (V_TIPOCARDAPIO = 'D') AND (V_IDCARDDESCENTR = 'S') THEN


            /*Verifica se o servico ja esta LIBERADO pela matriz, APROVADO pela filial,
              APROVADO pela matriz ou se a data de LIMITE de aprovacao pela filial expirou*/
            BEGIN
               SELECT CTRL.DTLIBECARDMATR, CTRL.DTAPROCARDFILI, CTRL.DTAPROCARDMATR,
                      CALE.DTLIMAPROVFILI + NVL(QTDIAPRORAPROV , 0) AS DTLIMAPROVFILI
               INTO   V_DTLIBECARDMATR, V_DTAPROCARDFILI, V_DTAPROCARDMATR, V_DTLIMAPROVFILI
               FROM   CTRLAPROVCARD CTRL, CALENCARD CALE
               WHERE  CTRL.CDFILIAL       = P_CDFILIAL AND
                      CTRL.CDSERVFILI     = P_CDSERVFILI AND
                      CTRL.DTMESCARDAPROV = P_DTCARDDEST AND
                      CTRL.CDFILIAL       = CALE.CDFILIAL AND
                      CTRL.DTMESCARDAPROV = CALE.DTMESCALENCARD;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_DTLIBECARDMATR := NULL;
                  V_DTAPROCARDFILI := NULL;
                  V_DTAPROCARDMATR := NULL;
                  V_DTLIMAPROVFILI := NULL;
            END;
            /*Cardapio nao liberado pela MATRIZ*/
            IF V_DTLIBECARDMATR IS NULL THEN
               /* Cardapio nao liberado pela matriz para o mes*/
               I_L(MSG('CARD_N_LIBERADO_MATRIZ'));
            END IF;
            /*Cardapio ja aprovado pela FILIAL*/
            IF NOT V_DTAPROCARDFILI IS NULL THEN
               /* Cardapio ja aprovado pela unidade*/
               I_L(MSG('CARD_JA_APROVADO_FILIAL'));
            END IF;
            /*Cardapio ja aprovado pela MATRIZ*/
            IF NOT V_DTAPROCARDMATR IS NULL THEN
               /* Cardapio ja aprovado pela matriz*/
               I_L(MSG('CARD_JA_APROVADO_MATRIZ'));
            END IF;
            /*Data limite de aprovacao pela FILIAL expirada*/
            IF TRUNC(SYSDATE) > TRUNC(V_DTLIMAPROVFILI) THEN
               /* Data limite de aprovacao da unidade expirou*/
               I_L(MSG('LIMITE_APROVACAO_EXPIRADO_FILIAL'));
            END IF;
         END IF;
         V_EXISTE_RETIRADA := 'N';
         V_EXISTE_FATURAMENTO := 'N';
         V_EXISTE_DISTRIBUICAO := 'N';
/*#######################################################################################################################################################################				
   		Existencia de retiradas, efetivos faturados ou distribuicao do cardapio
#########################################################################################################################################################################
          Verifica existencia de retiradas, efetivos faturados */ 
         FOR C_DIAS_CARD IN
         (
         	SELECT C.DTCARDAPIO, C.NRCARDAPIO, C.CDDIETPAD
         	  FROM CARDADIA C
         	 WHERE C.CDFILIAL = P_CDFILIAL
         	   AND C.CDSERVFILI = P_CDSERVFILI
               AND C.DTCARDAPIO BETWEEN P_DTCARDDEST AND V_ULTDIA_DTMES_ATUAL
         	 ORDER BY C.DTCARDAPIO
         ) LOOP
            /* Retirada*/
            SELECT COUNT(1)
              INTO V_QTDE
              FROM RETIRADA
             WHERE CDFILIAL = P_CDFILIAL
               AND NRCARDAPIO = C_DIAS_CARD.NRCARDAPIO;
            IF V_QTDE > 0 THEN
               /* Dia __PARAM0__ possui retirada lancada*/
            	I_L(MSG('EXISTE_RETIRADA', TO_CHAR(C_DIAS_CARD.DTCARDAPIO, 'DD/MM/YYYY')));
            	V_EXISTE_RETIRADA := 'S';
            END IF;
            /* Efetivos de planejamento aprovados */
            SELECT COUNT(1)
              INTO V_QTDE
              FROM EFETCLIE
             WHERE CDFILIAL = P_CDFILIAL
               AND CDSERVPLAN = P_CDSERVFILI
               AND DTEFETCLIE = C_DIAS_CARD.DTCARDAPIO
               AND NRAPURACFAT IS NOT NULL;               
            IF V_QTDE > 0 THEN
               /* Dia __PARAM0__ possui efetivos de planejamento ja apurados*/
            	I_L(MSG('EXISTE_FATURAMENTO_PLA', TO_CHAR(C_DIAS_CARD.DTCARDAPIO, 'DD/MM/YYYY')));
            	V_EXISTE_FATURAMENTO := 'S';
            END IF;               
         	/* Efetivos de faturamento aprovados */
         	SELECT COUNT(1)
              INTO V_QTDE
              FROM EFECLIFAT
             WHERE CDFILIAL = P_CDFILIAL
               AND CDSERVPLAN = P_CDSERVFILI
               AND DTFATURAM = C_DIAS_CARD.DTCARDAPIO
               AND NRAPURACFAT IS NOT NULL;
            IF V_QTDE > 0 THEN
               /* Dia __PARAM0__ possui efetivos de faturamento ja apurados*/
            	I_L(MSG('EXISTE_FATURAMENTO_FAI', TO_CHAR(C_DIAS_CARD.DTCARDAPIO, 'DD/MM/YYYY')));
            	V_EXISTE_FATURAMENTO := 'S';
            END IF;

            /* TODO: Validar tambem os efetivos do hospitalar */

            SELECT COUNT(*) 
              INTO V_QTDE
              FROM DISTCARD D
             WHERE D.CDFILIAL = P_CDFILIAL AND
                   D.NRCARDAPIO = C_DIAS_CARD.NRCARDAPIO;                  

            IF (V_QTDE > 0) THEN
               /* Dia __PARAM0__ possui distribuicao de embalagens lancada*/
               I_L(MSG('EXISTE_DISTRIBUICAO', TO_CHAR(C_DIAS_CARD.DTCARDAPIO, 'DD/MM/YYYY')));
            	V_EXISTE_DISTRIBUICAO := 'S';
            END IF;


         END LOOP;

/*#######################################################################################################################################################################				
   		Remocao dos dados ja existentes no cardapio de destino
#########################################################################################################################################################################
          Somente insere a CARDAMES E CNFCAMES se nao tiver retirada ou faturamento no periodo*/
         IF V_EXISTE_RETIRADA = 'N' AND  V_EXISTE_FATURAMENTO = 'N' AND V_EXISTE_DISTRIBUICAO = 'N' THEN
         	/* Apaga CARDADIA e COMPCARD*/
         	DELETE FROM COMPCARD WHERE EXISTS (SELECT 1
                                                 FROM CARDADIA
                                                WHERE CDFILIAL = COMPCARD.CDFILIAL AND
                                                      NRCARDAPIO = COMPCARD.NRCARDAPIO AND
                                                      CDFILIAL = P_CDFILIAL AND
                                                      CDSERVFILI = P_CDSERVFILI AND
                                                      DTCARDAPIO BETWEEN P_DTCARDDEST AND V_ULTDIA_DTMES_ATUAL);
				DELETE FROM CARDACLI WHERE EXISTS (SELECT 1
                                                 FROM CARDADIA
                                                WHERE CDFILIAL = CARDACLI.CDFILIAL AND
                                                      NRCARDAPIO = CARDACLI.NRCARDAPIO AND
                                                      CDFILIAL = P_CDFILIAL AND
                                                      CDSERVFILI = P_CDSERVFILI AND
                                                      DTCARDAPIO BETWEEN P_DTCARDDEST AND V_ULTDIA_DTMES_ATUAL);	         	
				DELETE FROM CARDADIA
             WHERE CDFILIAL = P_CDFILIAL AND
                   CDSERVFILI = P_CDSERVFILI AND
                   DTCARDAPIO BETWEEN P_DTCARDDEST AND V_ULTDIA_DTMES_ATUAL;

/*#######################################################################################################################################################################				
   			Preenchimento da CARDAMES e CNFCAMES
#########################################################################################################################################################################            
	          Insere CARDAMES*/
	         SELECT COUNT(1)
		        INTO V_QTDE
		        FROM CARDAMES
		       WHERE CDFILIAL = P_CDFILIAL
		         AND CDSERVFILI = P_CDSERVFILI
		         AND DTMESCARDAPIO = P_DTCARDDEST;

		      IF V_QTDE = 0 THEN
		         /* Insere CARDAMES*/
		         INSERT INTO CARDAMES
		         (CDFILIAL, CDSERVFILI, DTMESCARDAPIO)
		         VALUES
		         (P_CDFILIAL, P_CDSERVFILI, P_DTCARDDEST);
		      END IF;

				/* CNFCAMES*/
				DELETE FROM CNFCAMES
              WHERE CDFILIAL     = P_CDFILIAL AND
                    CDSERVFILI   = P_CDSERVFILI AND
                    DTREFECNFMES = P_DTCARDDEST;

            INSERT INTO CNFCAMES
            (CDFILIAL, CDSERVFILI, NRSEQCNFMES, DTREFECNFMES,
             CDTIPOPRAT, IDOPCCNFMES, IDIMPCNFMES, NRPAGCNFMES,
             VRPERCOMMES, NRORDCNFMES, DSCOMPLTPMES, IDCONCNFMES,
             IDTPPRATSEGM, IDTPPRATTERM, IDTPPRATQUAM, IDTPPRATQUIM,
             IDTPPRATSEXM, IDTPPRATSABM, IDTPPRATDOMM, IDTPPRATFERM)
            SELECT P_CDFILIAL, P_CDSERVFILI,
                   NRSEQCNFMES, P_DTCARDDEST,
                   CDTIPOPRAT, IDOPCCNFMES, IDIMPCNFMES, NVL(NRPAGCNFMES,1) NRPAGCNFMES,
                   NVL(VRPERCOMMES,0) VRPERCOMMES, NRORDCNFMES, DSCOMPLTPMES, IDCONCNFMES,
                   IDTPPRATSEGM, IDTPPRATTERM, IDTPPRATQUAM, IDTPPRATQUIM,
                   IDTPPRATSEXM, IDTPPRATSABM, IDTPPRATDOMM, IDTPPRATFERM
              FROM CNFCAMES             
             WHERE CDFILIAL = P_CDFILIAL
               AND CDSERVFILI = P_CDSERVFILI
               AND DTREFECNFMES = P_DTCARDORI;

/*#######################################################################################################################################################################				
	   		Processamento do mapa de dias
#########################################################################################################################################################################
		       Percorre os dias do mapa, ordenados por mes e dia do mes*/
				FOR C_DIAS IN
				(
					SELECT DTMES, DTDEST, MAX(DTORI) AS DTORI
					  FROM
								( 
								SELECT TO_DATE(SUBSTR(CDLISTA, 1, 8), 'YYYYMMDD') AS DTDEST,
								       TO_DATE(SUBSTR(CDLISTA, 10, 8), 'YYYYMMDD') AS DTORI,
								       TO_DATE(SUBSTR(CDLISTA, 1, 6), 'YYYYMM') AS DTMES
								  FROM LISTA L
								 WHERE L.NRLISTA = P_MAPADIAS			 
								)
					 WHERE DTMES = P_DTCARDDEST
					 GROUP BY DTMES, DTDEST
					 ORDER BY DTMES, DTDEST
				) LOOP
/*#######################################################################################################################################################################
	   			Tratativa de feriados (na origem e no destino)
#########################################################################################################################################################################*/
					V_DTORI := C_DIAS.DTORI;
					/* Feriado na origem*/
					V_FERIADO_FILI_ORIGEM := 'N';
					SELECT COUNT(1)
					  INTO V_QTDE
					  FROM FERIFILI
					 WHERE CDFILIAL = P_CDFILIAL
                	AND DTFERIFILI = V_DTORI;
               IF V_QTDE > 0 THEN
               	V_FERIADO_FILI_ORIGEM := 'S';
               END IF;
               /* Feriado de cardapio no destino*/
   				V_FERIADO_DESTINO := 'N';
   				SELECT COUNT(1)
					  INTO V_QTDE
					  FROM FERICARD
					 WHERE CDFILIAL = P_CDFILIAL
                	AND DTFERICARD = C_DIAS.DTDEST;
               IF V_QTDE > 0 THEN
               	V_FERIADO_DESTINO := 'S';
               END IF; 
               /* Feriado de filial no destino*/
   				V_FERIADO_FILI_DESTINO := 'N';
   				SELECT COUNT(1)
					  INTO V_QTDE
					  FROM FERIFILI
					 WHERE CDFILIAL = P_CDFILIAL
                	AND DTFERIFILI = C_DIAS.DTDEST;
               IF V_QTDE > 0 THEN
               	V_FERIADO_FILI_DESTINO := 'S';     
               END IF;

               IF V_FERIADO_FILI_ORIGEM = 'S' THEN
                  /* Pesquisa um dia de cardapio que seja do mesmo dia da semana, porem nao seja feriado na origem*/
                  SELECT MIN(DTCARDAPIO)
                    INTO V_DIA_SUBSTITUTO
                    FROM CARDADIA
                   WHERE CDFILIAL = P_CDFILIAL
                     AND CDSERVFILI = P_CDSERVFILI
                     AND DTCARDAPIO BETWEEN P_DTCARDORI AND V_ULTDIA_DTMES_ORIGEM
                     AND DTCARDAPIO <> V_DTORI
                     AND TO_CHAR(DTCARDAPIO, 'D') = TO_CHAR(V_DTORI, 'D')
                     AND NOT EXISTS (SELECT 1
                                       FROM FERIFILI
                                      WHERE FERIFILI.CDFILIAL = P_CDFILIAL
                                        AND CARDADIA.DTCARDAPIO = FERIFILI.DTFERIFILI);
                  IF V_DIA_SUBSTITUTO IS NOT NULL THEN
                     V_DTORI := V_DIA_SUBSTITUTO;
                  END IF;
               END IF;
               IF V_FERIADO_FILI_DESTINO = 'S' AND V_IDCARDIAUTIL = 'S' THEN
                  /* Se restringe cardapio a dia util e feriado no destino, nao preenche o dia*/
                  CONTINUE;
               END IF;   
/*#######################################################################################################################################################################				
					Percorre a CARDADIA para o dia atual
#########################################################################################################################################################################
					 Se for dieta, em um mesmo dia podem existir varios cardapios (por dieta)*/
					FOR C_CARDADIA IN
		         (
		         	SELECT C.DTCARDAPIO, C.NRCARDAPIO, C.CDDIETPAD, C.DSCARDAPIO,
		         	       C.QTCOMESTICAR, C.QTCLIESTICAR, C.QTFUNESTICAR, C.QTDOAESTICAR,
			                C.QTESTITURNO1, C.QTESTITURNO2, C.QTESTITURNO3, C.QTESTITURNO4
		         	  FROM CARDADIA C
		         	 WHERE C.CDFILIAL = P_CDFILIAL
		         	   AND C.CDSERVFILI = P_CDSERVFILI
		         	   AND C.DTCARDAPIO = V_DTORI
		         	   AND C.QTCOMESTICAR > 0
		         	 ORDER BY C.CDDIETPAD
		         ) LOOP
/*#######################################################################################################################################################################				
						Copia CARDADIA e comensais do cliente no dia de destino
#########################################################################################################################################################################*/
		         	V_NRCARDAPIO := RETORNA_NOVO_CODIGO('CARDAPIO'|| P_CDFILIAL, 1, 10);

		         	V_DIA_SEMANA := TO_CHAR(C_DIAS.DTDEST, 'D');

		         	DETERMINA_COMENSAIS;

                    V_INSERE_COMPCARD := 'S';

		         	IF V_QTCOMESTICAR = 0 THEN
                       IF V_FERIADO_FILI_DESTINO = 'S' OR V_FERIADO_DESTINO = 'S' THEN
                          /*SE E FERIADO DE CARDAPIO E NAO TEM COMENSAL, PASSA PARA O PROXIMO DIA*/
                          CONTINUE;
                       END IF;
		         	   /* Comensais nao cadastrados no servico para o dia __PARAM0__*/
                       I_L(MSG('COMENSAIS_'|| V_TEXTO_DIA_SEMANA || '_N_CAD', TO_CHAR(C_DIAS.DTDEST, 'DD/MM/YYYY')));
                       V_INSERE_COMPCARD := 'N';
                    ELSE
                       INSERT INTO CARDADIA
                        (CDFILIAL, CDSERVFILI, DTCARDAPIO, NRCARDAPIO,
                         CDDIETPAD, DTMESCARD, CDOPERADOR, DSCARDAPIO, DTULTATUACAR,
                         QTCOMESTICAR, QTCLIESTICAR, QTFUNESTICAR, QTDOAESTICAR,
                         QTESTITURNO1, QTESTITURNO2, QTESTITURNO3, QTESTITURNO4)
                       VALUES
                        (P_CDFILIAL, P_CDSERVFILI, C_DIAS.DTDEST, V_NRCARDAPIO,
                         C_CARDADIA.CDDIETPAD, P_DTCARDDEST, P_CDOPERADOR, C_CARDADIA.DSCARDAPIO, SYSDATE,
                         V_QTCOMESTICAR, V_QTCLIESTICAR, V_QTFUNESTICAR, V_QTDOAESTICAR,
                         V_QTESTITURNO1, V_QTESTITURNO2, V_QTESTITURNO3, V_QTESTITURNO4);
    
                            /* Insere pontos de entrega*/
                       FOR C_CARDACLI IN
                       (SELECT CDCLIENTE, CDDESTFILI, NVL(CASE WHEN V_FERIADO_DESTINO = 'S' THEN QTCOESTCSFER
                                                                             WHEN V_DIA_SEMANA = '1' THEN QTCOESTCSDOM
                                                                              WHEN V_DIA_SEMANA = '2' THEN QTCOESTCSSEG
                                                                              WHEN V_DIA_SEMANA = '3' THEN QTCOESTCSTER
                                                                              WHEN V_DIA_SEMANA = '4' THEN QTCOESTCSQUA
                                                                              WHEN V_DIA_SEMANA = '5' THEN QTCOESTCSQUI
                                                                              WHEN V_DIA_SEMANA = '6' THEN QTCOESTCSSEX
                                                                              WHEN V_DIA_SEMANA = '7' THEN QTCOESTCSSAB
                                                                       END, 0.00) AS QTCOMENSAIS
                                FROM DESTSRVCLI
                               WHERE CDFILIAL = P_CDFILIAL
                                 AND CDSERVFILI = P_CDSERVFILI
                       ) LOOP
                          IF C_CARDACLI.QTCOMENSAIS > 0 THEN
                             BEGIN    
                                 INSERT INTO CARDACLI
                                  (CDFILIAL, NRCARDAPIO, CDCLIENTE, CDDESTFILI, QTCOMESTICLI)
                                 VALUES
                                  (P_CDFILIAL, V_NRCARDAPIO, C_CARDACLI.CDCLIENTE, C_CARDACLI.CDDESTFILI, C_CARDACLI.QTCOMENSAIS);
                             EXCEPTION
                                WHEN DUP_VAL_ON_INDEX THEN
                                    UPDATE CARDACLI SET QTCOMESTICLI = C_CARDACLI.QTCOMENSAIS
                                     WHERE CDFILIAL = P_CDFILIAL AND NRCARDAPIO = V_NRCARDAPIO AND 
                                           CDCLIENTE = C_CARDACLI.CDCLIENTE AND CDDESTFILI = C_CARDACLI.CDDESTFILI;
                             END;        
                          END IF;
                       END LOOP;
                    END IF;

/*#######################################################################################################################################################################				
						Percorre COMPCARD
#########################################################################################################################################################################*/
					FOR C_COMPCARD IN
					(SELECT C.NRSEQPRATCAR, C.CDFILAUXPRAT, C.CDPRATO,
		                    C.VRPECONSCARD, C.CDTIPOPRAT, C.NRORDPRATCAR, C.IDPRATOESPE,
		                    C.CDPRATOMAT, C.CDFILAUXPRATMAT
		               FROM COMPCARD C
		              WHERE C.CDFILIAL = P_CDFILIAL
		                AND C.NRCARDAPIO = C_CARDADIA.NRCARDAPIO
					) LOOP
/*#######################################################################################################################################################################           
                     Existencia de pratos a inativar no mes atraves de agendamento
#########################################################################################################################################################################*/            

                     V_RECEITA_UTILIZADA := RECEITA_UTILIZADA(C_COMPCARD.CDPRATO, C_COMPCARD.CDFILAUXPRAT,  P_CDFILIAL, P_CDSERVFILI);

                     V_PRATO_AGENDADO_INATIVACAO := 'N';

                     SELECT MAX(DTINATIVAUTO), MAX(CDPRATO || ' - ' || NMPRATO)
                       INTO V_DTINATIVAUTO, V_NMPRATO
                       FROM PRATO
                      WHERE CDFILAUXPRAT = C_COMPCARD.CDFILAUXPRAT
                        AND CDPRATO = C_COMPCARD.CDPRATO;

                     IF V_DTINATIVAUTO IS NOT NULL AND V_DTINATIVAUTO <= C_DIAS.DTDEST THEN
                        /* Prato __PARAM0__ nao relacionado ao dia __PARAM1__ pois foi agendado para inativacao em __PARAM2__*/
                        I_L(MSG('PRATO_AGENDADO_INATIVACAO', V_NMPRATO, TO_CHAR(C_DIAS.DTDEST, 'DD/MM/YYYY'), TO_CHAR(V_DTINATIVAUTO, 'DD/MM/YYYY')));
                        V_PRATO_AGENDADO_INATIVACAO := 'S';
                     END IF;

                     IF V_RECEITA_UTILIZADA = 'P' THEN
                        SELECT MAX(DTINATIVAUTO)
                          INTO V_DTINATIVAUTO
                          FROM PRATPADR
                         WHERE CDFILAUXPRAT = C_COMPCARD.CDFILAUXPRAT
                           AND CDPRATO = C_COMPCARD.CDPRATO;
                        IF V_DTINATIVAUTO IS NOT NULL AND V_DTINATIVAUTO <= C_DIAS.DTDEST THEN
                           /* Prato __PARAM0__ nao relacionado ao dia __PARAM1__ pois sua receita padrao foi agendada para inativacao em __PARAM2__*/
                           I_L(MSG('RECEITA_PADRAO_AGENDADA_INATIVACAO', V_NMPRATO, TO_CHAR(C_DIAS.DTDEST, 'DD/MM/YYYY'), TO_CHAR(V_DTINATIVAUTO, 'DD/MM/YYYY')));
                           V_PRATO_AGENDADO_INATIVACAO := 'S';
                        END IF;
                     ELSIF V_RECEITA_UTILIZADA = 'F' THEN
                        SELECT MAX(DTINATIVAUTO)
                          INTO V_DTINATIVAUTO
                          FROM PRATFILI
                         WHERE CDFILAUXPRAT = C_COMPCARD.CDFILAUXPRAT
                           AND CDPRATO = C_COMPCARD.CDPRATO
                           AND CDFILIAL = P_CDFILIAL;
                        IF V_DTINATIVAUTO IS NOT NULL AND V_DTINATIVAUTO <= C_DIAS.DTDEST THEN
                           /* Prato __PARAM0__ nao relacionado ao dia __PARAM1__ pois sua receita local foi agendada para inativacao em __PARAM2__*/
                           I_L(MSG('RECEITA_LOCAL_AGENDADA_INATIVACAO', V_NMPRATO, TO_CHAR(C_DIAS.DTDEST, 'DD/MM/YYYY'), TO_CHAR(V_DTINATIVAUTO, 'DD/MM/YYYY')));
                           V_PRATO_AGENDADO_INATIVACAO := 'S';
                        END IF;
                     ELSIF V_RECEITA_UTILIZADA = 'S' THEN
                        SELECT MAX(DTINATIVAUTO)
                          INTO V_DTINATIVAUTO
                          FROM PRATSERV
                         WHERE CDFILAUXPRAT = C_COMPCARD.CDFILAUXPRAT
                           AND CDPRATO = C_COMPCARD.CDPRATO
                           AND CDFILIAL = P_CDFILIAL
                           AND CDSERVFILI = P_CDSERVFILI;
                        IF V_DTINATIVAUTO IS NOT NULL AND V_DTINATIVAUTO <= C_DIAS.DTDEST THEN
                           /* Prato __PARAM0__ nao relacionado ao dia __PARAM1__ pois sua receita por servico foi agendada para inativacao em __PARAM2__*/
                           I_L(MSG('RECEITA_SERVICO_AGENDADA_INATIVACAO', V_NMPRATO, TO_CHAR(C_DIAS.DTDEST, 'DD/MM/YYYY'), TO_CHAR(V_DTINATIVAUTO, 'DD/MM/YYYY')));
                           V_PRATO_AGENDADO_INATIVACAO := 'S';
                        END IF;
                     END IF;

                     IF (V_PRATO_AGENDADO_INATIVACAO = 'N') AND (V_INSERE_COMPCARD = 'S') THEN
   							INSERT INTO COMPCARD 
   							(CDFILIAL, NRCARDAPIO, NRSEQPRATCAR, CDFILAUXPRAT, CDPRATO,
                         VRPECONSCARD, CDTIPOPRAT, NRORDPRATCAR, IDPRATOESPE,
                         CDPRATOMAT, CDFILAUXPRATMAT)
                        VALUES
                        (P_CDFILIAL, V_NRCARDAPIO, C_COMPCARD.NRSEQPRATCAR, C_COMPCARD.CDFILAUXPRAT, C_COMPCARD.CDPRATO,
                         C_COMPCARD.VRPECONSCARD, C_COMPCARD.CDTIPOPRAT, C_COMPCARD.NRORDPRATCAR, C_COMPCARD.IDPRATOESPE,
                         C_COMPCARD.CDPRATOMAT, C_COMPCARD.CDFILAUXPRATMAT);
                     END IF;
		            END LOOP;			         
		         END LOOP;					
				END LOOP;				
         END IF;
      END IF;

      REGISTRA_LOG_REPLICACARDPLA(
         P_DTCARDORI, P_CDFILIAL,
         P_CDSERVFILI, P_DTCARDDEST,
         CASE WHEN (V_NRORDEM_ERROS > 0) THEN 'S' ELSE 'N' END,
         P_CDOPERADOR, P_NRORG
      );

   	IF V_NRORDEM_ERROS > 0 OR P_SOMENTE_VALIDACAO = 'S' THEN
       ROLLBACK;
	ELSE
       COMMIT;
	END IF;

	EXCEPTION
		WHEN OTHERS THEN
		   I_L(SQLERRM);
		   ROLLBACK;
	END;
END;
/