create or replace PROCEDURE DISTRIBUI_PRODUCAO_COZ(
P_OPERACAO IN VARCHAR2 /*G: geracao C: Cancelamento*/,
P_CDCENTRAL IN FILIAL.CDFILIAL%TYPE,
P_NRDISTPROD IN DISTPROD.NRDISTPROD%TYPE,
P_DTINI IN DATE,
P_DTFIN IN DATE,
P_NRLISTA_SERV  IN VARCHAR2,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER,
P_RETORNO OUT VARCHAR2,
P_CONSUMO IN VARCHAR2 DEFAULT 'CFP'
)
AS
   V_CHAVE VARCHAR2(20);
   V_QTDECARD NUMBER;
   V_NRDISTPROD NUMBER;
   V_NRDISTCARD NUMBER;
   V_RETORNO VARCHAR2(400);
   V_MSGERRO VARCHAR2(4000);
   V_NRCONSPLAN VARCHAR2(20);
   V_QTDEPROD NUMBER;
   V_QTTOTCOMENS NUMBER;
   V_NRSEQPRATCAR NUMBER;
   V_SEQUENCIAL VARCHAR2(20);
   V_COMENSAIS NUMBER;
   V_QTPRATO NUMBER;
   V_IDEFETIVADA VARCHAR2(1);
BEGIN
   IF P_OPERACAO = 'G' THEN
      V_MSGERRO := '';
      
      /*Inclui registro da distribuicao*/
      NOVO_CODIGO('DISTPROD', V_CHAVE, 1);
      V_NRDISTPROD := TO_NUMBER(V_CHAVE);
      
      INSERT INTO DISTPROD
      (NRDISTPROD, CDCENTRAL, DTINIPROD, DTFINPROD, NRORG)
      VALUES
      (V_NRDISTPROD, P_CDCENTRAL, P_DTINI, P_DTFIN, P_NRORG);
      
      V_QTDECARD := 0;
      /*Busca os cardapios contemplados pelo filtro informado*/
      IF (SUBSTR(P_CONSUMO,1,1) = 'C') THEN
		  FOR C_CARD IN
		  (SELECT C.NRCARDAPIO, C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, A.CDCLIENTE, A.CDDESTFILI, 
				  D.NRDISTCARD, NVL(A.QTCOMESTICLI, 0) AS QTCOMESTICLI, C.QTCOMESTICAR AS QTCOMESTICAR,  NVL((SELECT SUM(NVL(QTCOMESTICLI,0))
																								FROM CARDACLI AUX 
																							   WHERE AUX.CDFILIAL = C.CDFILIAL 
																								 AND AUX.NRCARDAPIO = C.NRCARDAPIO
																								 AND AUX.QTCOMESTICLI > 0),0) QTCOMESTCLITOTCARD
			 FROM CARDADIA C
				  INNER JOIN LISTA LS ON LS.NRLISTA = P_NRLISTA_SERV AND
										 SUBSTR(LS.CDLISTA, 1, 4) = C.CDFILIAL AND
										 SUBSTR(LS.CDLISTA, 5, 5) = C.CDSERVFILI      
				  LEFT JOIN CARDDISTPROD CD ON CD.CDFILIAL = C.CDFILIAL AND
											   CD.NRCARDAPIO = C.NRCARDAPIO
				  LEFT JOIN DISTCARD D ON D.CDFILIAL = C.CDFILIAL AND
										  D.NRCARDAPIO = C.NRCARDAPIO
				  LEFT JOIN CARDACLI A ON A.CDFILIAL   = C.CDFILIAL   AND
										  A.NRCARDAPIO = C.NRCARDAPIO AND
										  A.QTCOMESTICLI > 0
			WHERE C.DTCARDAPIO BETWEEN P_DTINI AND P_DTFIN
			  AND CD.CDFILIAL IS NULL
			  AND D.CDFILIAL IS NULL
			  AND C.NRORG = P_NRORG
			  AND NVL(QTCLIESTICAR,0) > 0
			  AND EXISTS (SELECT CP.CDFILIAL, CP.NRCARDAPIO 
							FROM COMPCARD CP 
						   WHERE CP.CDFILIAL = C.CDFILIAL AND CP.NRCARDAPIO = C.NRCARDAPIO
							 AND (E_AGREGADA(P_CDCENTRAL, CP.CDFILIAL, C.CDSERVFILI, CP.CDTIPOPRAT, C.DTCARDAPIO ) > 0))
		  ) LOOP
			 

			 /*=======================================*/
			 /*===== VERIFICACAO DO SERVICO E DO CARDAPIO =====*/
			 /*=======================================*/         
			 
			 IF C_CARD.CDCLIENTE IS NOT NULL AND (C_CARD.QTCOMESTICLI <= 0) AND (C_CARD.QTCOMESTCLITOTCARD = 0) THEN
				V_RETORNO := 'ERRO_COMESTICLI_ZERO';
			 ELSIF (C_CARD.QTCOMESTICAR <= 0) THEN
				V_RETORNO := 'ERRO_COMESTICAR_ZERO';
			 ELSE
			 
				IF C_CARD.CDCLIENTE IS NOT NULL THEN
				   V_QTTOTCOMENS := C_CARD.QTCOMESTICLI;
				ELSE
				   V_QTTOTCOMENS := C_CARD.QTCOMESTICAR;
				END IF;


				/*============================*/
				/*===== DISTRIBUICAO EXISTENTE =====*/
				/*============================*/  
				IF C_CARD.NRDISTCARD IS NOT NULL THEN
				   /*Consiste se houve alteracao do cardapio que invalide a distribuicao : */
				   /*   Mudanca de prato, exclusao de uma coluna ou inclusao de uma coluna na elaboracao*/
				   BEGIN
					  SELECT NVL(C.NRSEQPRATCAR, CNF.NRSEQPRATCAR)
						INTO V_NRSEQPRATCAR
						FROM COMPCARD C
							FULL OUTER JOIN CNFDISTCARD CNF
							   ON CNF.NRSEQPRATCAR = C.NRSEQPRATCAR AND
								  CNF.NRDISTCARD = C_CARD.NRDISTCARD
					   WHERE C.CDFILIAL = C_CARD.CDFILIAL
						 AND C.NRCARDAPIO = C_CARD.NRCARDAPIO
						 AND ((C.CDPRATO <> CNF.CDPRATO) OR (C.CDFILAUXPRAT <> CNF.CDFILAUXPRAT) OR (CNF.NRDISTCARD IS NULL) OR (C.NRSEQPRATCAR IS NULL));
				   
				   EXCEPTION
					  WHEN NO_DATA_FOUND THEN
						 V_NRSEQPRATCAR := NULL;
				   END;
			 
				   /*Indica que houve mudanca nos pratos do cardapio*/
				   IF V_NRSEQPRATCAR IS NOT NULL THEN
					  V_RETORNO := 'ERRO_PRAT_CARD_ALT';
				   ELSE
					 V_RETORNO := C_CARD.NRDISTCARD;   
				   END IF;
				ELSE   
				 

				   /*Inclui distribuicao do cardapio*/
				   /*Busca novo sequencial*/               
				   NOVO_CODIGO_SC('DISTCARD',V_SEQUENCIAL);
				   V_NRDISTCARD := TO_NUMBER(V_SEQUENCIAL);
				
				   INSERT INTO DISTCARD
				   (NRDISTCARD, CDFILIAL,NRCARDAPIO, CDCLIENTE, CDDESTFILI, QTCOMENSAIS)
				   VALUES
				   (V_NRDISTCARD, C_CARD.CDFILIAL, C_CARD.NRCARDAPIO, C_CARD.CDCLIENTE, C_CARD.CDDESTFILI, V_QTTOTCOMENS);
				
				
				   FOR C_PRATOS IN
				   (SELECT C.NRSEQPRATCAR, C.CDFILAUXPRAT, C.CDPRATO, C.VRPECONSCARD,
						   ROUND(NVL(QTPORPESSOA, 0),5) QTPORPESSOA, ROUND(NVL(QTTOTEXTRA, 0),5) QTTOTEXTRA, REC.CDPRODPRODUCAO,
                           C.CDTIPOPRAT
					  FROM COMPCARD C           
						   /*Peso unitario e quantidade de amostra (extra) do prato*/
						   LEFT JOIN
						   (SELECT CDFILAUXPRAT, CDPRATO, QTPADRAOPRAT AS QTPORPESSOA, QTAMOSTRPADR AS QTTOTEXTRA, 'P' AS RECE_UTIL
							  FROM PRATPADR WHERE CDPRODPRODUCAO IS NOT NULL            
							UNION ALL 
							SELECT CDFILAUXPRAT, CDPRATO, QTPADRAOFILI AS QTPORPESSOA, QTAMOSTRFILI AS QTTOTEXTRA, 'F' AS RECE_UTIL
							  FROM PRATFILI WHERE CDFILIAL = C_CARD.CDFILIAL AND CDPRODPRODUCAO IS NOT NULL
							UNION ALL
							SELECT CDFILAUXPRAT, CDPRATO, QTPADRAOSERV AS QTPORPESSOA, QTAMOSTRSERV AS QTTOTEXTRA, 'S' AS RECE_UTIL
							  FROM PRATSERV WHERE CDFILIAL = C_CARD.CDFILIAL AND CDSERVFILI = C_CARD.CDSERVFILI AND CDPRODPRODUCAO IS NOT NULL
						   ) P ON P.CDFILAUXPRAT = C.CDFILAUXPRAT 
							  AND P.CDPRATO = C.CDPRATO 
							  AND RECEITA_UTILIZADA(C.CDPRATO, C.CDFILAUXPRAT, C_CARD.CDFILIAL, C_CARD.CDSERVFILI) = P.RECE_UTIL
						   INNER JOIN 
						   (SELECT DISTINCT R.CDFILAUXPRAT, R.CDPRATO, 'F' AS RECE_UTIL, CDPRODPRODUCAO 
							  FROM RECEFILI R INNER JOIN PRATFILI P ON (R.CDPRATO = P.CDPRATO AND R.CDFILIAL = P.CDFILIAL) 
							 WHERE IDTPREQPRODFILI = 'P' AND R.CDFILIAL = C_CARD.CDFILIAL AND P.CDPRODPRODUCAO IS NOT NULL
							UNION ALL 
							SELECT DISTINCT R.CDFILAUXPRAT, R.CDPRATO, 'S' AS RECE_UTIL, CDPRODPRODUCAO 
							  FROM RECESERV R INNER JOIN PRATSERV P ON (R.CDPRATO = P.CDPRATO AND R.CDFILIAL = P.CDFILIAL AND R.CDSERVFILI = P.CDSERVFILI) 
							 WHERE R.IDTPREQPRODSERV = 'P' AND R.CDFILIAL = C_CARD.CDFILIAL AND R.CDSERVFILI = C_CARD.CDSERVFILI AND P.CDPRODPRODUCAO IS NOT NULL
							UNION ALL   
							SELECT DISTINCT R.CDFILAUXPRAT, R.CDPRATO, 'P' AS RECE_UTIL, CDPRODPRODUCAO 
							  FROM RECEPADR R INNER JOIN PRATPADR P ON (R.CDPRATO = P.CDPRATO)
							 WHERE R.IDTPREQPRODPADR = 'P' AND CDPRODPRODUCAO IS NOT NULL
						   ) REC ON REC.CDFILAUXPRAT = C.CDFILAUXPRAT 
								AND REC.CDPRATO = C.CDPRATO 
								AND RECEITA_UTILIZADA(C.CDPRATO, C.CDFILAUXPRAT, C_CARD.CDFILIAL, C_CARD.CDSERVFILI) = REC.RECE_UTIL 
					 WHERE C.CDFILIAL = C_CARD.CDFILIAL 
					   AND C.NRCARDAPIO = C_CARD.NRCARDAPIO 
					   AND (E_AGREGADA(P_CDCENTRAL, C.CDFILIAL, C_CARD.CDSERVFILI, C.CDTIPOPRAT,C_CARD.DTCARDAPIO ) > 0)
				   ) LOOP
					   
					  V_COMENSAIS := ROUND((C_PRATOS.VRPECONSCARD/100) * V_QTTOTCOMENS, 3);         
					  V_QTPRATO := ROUND(V_COMENSAIS * C_PRATOS.QTPORPESSOA, 5);
					  
					  /*Insere a configuracao do cardapio considerada na distribuicao, bem como outras grandezas envolvidas*/
					  INSERT INTO CNFDISTCARD
					  (NRDISTCARD, NRSEQPRATCAR, CDFILAUXPRAT, CDPRATO, QTTOTCOMENSAIS, 
					   QTTOTPRATO, QTPORPESSOA, QTTOTEXTRA, IDCADAUTO, CDPRODPRODUCAO, CDTIPOPRAT)
					  VALUES
					  (V_NRDISTCARD, C_PRATOS.NRSEQPRATCAR, C_PRATOS.CDFILAUXPRAT, C_PRATOS.CDPRATO, V_COMENSAIS, 
					   V_QTPRATO, C_PRATOS.QTPORPESSOA, C_PRATOS.QTTOTEXTRA, 'S', C_PRATOS.CDPRODPRODUCAO, C_PRATOS.CDTIPOPRAT);

				   END LOOP;
				   V_RETORNO := V_NRDISTCARD;
				END IF;
			 END IF;
			 /*Verifica se ocorreu algum erro durante o processo*/
			 IF SUBSTR(V_RETORNO, 1, 4) = 'ERRO' THEN
				 V_NRDISTCARD := NULL;
				 V_MSGERRO := V_MSGERRO || V_RETORNO || '-' || C_CARD.CDFILIAL || '-' || C_CARD.CDSERVFILI || '-' || C_CARD.DTCARDAPIO || ';';
			 ELSE
				 V_NRDISTCARD := TO_NUMBER(V_RETORNO);
				 /*V_MSGERRO := NULL;*/
			 
				 /*Insere cardapio no controle da distribuicao da producao*/
				 INSERT INTO CARDDISTPROD
				 (NRORG, NRDISTPROD, CDFILIAL, NRCARDAPIO, CDSERVFILI, NRDISTCARD, DSMENSAGEM)
				 VALUES
				 (P_NRORG, V_NRDISTPROD, C_CARD.CDFILIAL, C_CARD.NRCARDAPIO, C_CARD.CDSERVFILI, V_NRDISTCARD, SUBSTR(V_MSGERRO,1,400));
				 
				 V_QTDECARD := V_QTDECARD + 1;
			 END IF;
			 
		  END LOOP;
      END IF;
      
      /*gera consplan para buscar produtos de distribuicao, complemento, consumo fixo e percapita para distribuir*/
      CONSPLAN_GERAL(P_NRLISTA_SERV, P_DTINI, P_DTFIN, P_CONSUMO, 'P', 0, 'N', '0', 'ZZZZZZZZZZ',
      P_CDOPERADOR, 'GERANOVCOD', V_NRCONSPLAN);
      
      IF V_NRCONSPLAN IS NOT NULL THEN

         INSERT INTO PRODDISTPROD (NRPRODDISTPROD ,NRDISTPROD, CDFILIAL, CDSERVFILI, DTCARDAPIO, CDCLIENTE,
                                   CDDESTFILI, QTCOMENSAIS, CDPRODUTO, QTTOTCOMENSAIS, QTTOTPRODUTO, QTPORPESSOA, IDTIPOPROD, 
                                   CDFILAUXPRAT, CDPRATO, CDTIPOPRAT)
         (/*CONSUMO FIXO E PERCAPITA*/
         SELECT ROWNUM AS NRPRODDISTPROD, A.NRDISTPROD, A.CDFILIAL, A.CDSERVFILI, A.DTCARDAPIO, CASE A.CDCLIENTE WHEN 'XXX' THEN NULL ELSE A.CDCLIENTE END AS CDCLIENTE, 
                 CASE A.CDDESTFILI WHEN 'XXX' THEN NULL ELSE A.CDDESTFILI END AS CDDESTFILI, A.QTCOMENSAIS, 
                 A.CDPRODUTO, A.QTTOTCOMENSAIS,A.QTTOTPRODUTO, A.QTPORPESSOA, A.IDTIPOPROD,
                 A.CDFILAUXPRAT, A.CDPRATO, A.CDTIPOPRAT
         FROM(        
         SELECT V_NRDISTPROD AS NRDISTPROD, CDFILIAL, CDSERVFILI, DTCARDAPIO, CDCLIENTE, CDDESTFILI, QTCOMENSAIS, 
                 CDPRODUTO, QTTOTCOMENSAIS,SUM(QTTOTPRODUTO) AS QTTOTPRODUTO, SUM(QTPORPESSOA) AS QTPORPESSOA, IDTIPOPROD,
                 CDFILAUXPRAT, CDPRATO, CDTIPOPRAT
          FROM
          (    
          SELECT CP.CDFILICPLA AS CDFILIAL, CP.CDSERVCPLA AS CDSERVFILI, CP.DTCONSPLAN AS DTCARDAPIO,  
                 CP.CDCLIECPLA AS CDCLIENTE, CP.CDDESTFICPLA AS CDDESTFILI, ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3) AS QTCOMENSAIS, 
                 CP.CDPRODCPLA AS CDPRODUTO, ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3) AS QTTOTCOMENSAIS, 
                 ROUND(CP.QTCONSPLAN,5) AS QTTOTPRODUTO, ROUND((CP.QTCONSPLAN / ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3)),5) AS QTPORPESSOA, 
                 CASE IDTPREQPROD WHEN 'E'  
                    THEN 'ENTREGADIRETA' 
                    ELSE CASE CP.IDCONSPLAN WHEN 'F' THEN 'FIXO' ELSE 'DISTRIBUICAO' END  
                END AS IDTIPOPROD,
                CP.CDFAPRATCPLA AS CDFILAUXPRAT, CP.CDPRATCONSPL AS CDPRATO, CP.CDTPPRATCPLA AS CDTIPOPRAT
          FROM CONSPLAN CP 
          WHERE (CP.NRCONSPLAN = V_NRCONSPLAN) 
            AND (IDCONSPLAN IN ('F','P')) 
            AND (E_AGREGADA(P_CDCENTRAL, CP.CDFILICPLA, CP.CDSERVCPLA, CP.CDTPPRATCPLA, CP.DTCONSPLAN) > 0) 
            AND (IDTPREQPROD IN ('D', 'E')) 
          UNION ALL 
           
          SELECT CP.CDFILICPLA AS CDFILIAL, CP.CDSERVCPLA AS CDSERVFILI, CP.DTCONSPLAN AS DTCARDAPIO,  
                 CP.CDCLIECPLA AS CDCLIENTE, CP.CDDESTFICPLA AS CDDESTFILI, ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3) AS QTCOMENSAIS, 
                 CP.CDPRODCPLA AS CDPRODUTO, ROUND((CP.VRPECONSPLAN/100) * ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3), 3) AS QTTOTCOMENSAIS, 
                 ROUND(CP.QTCONSPLAN,5) AS QTTOTPRODUTO, ROUND(CP.QTCONSPLAN / ((CP.VRPECONSPLAN/100) * ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3)),5) AS QTPORPESSOA, 
                 CASE IDTPREQPROD WHEN 'E' THEN 'ENTREGADIRETA' ELSE 'DISTRIBUICAO' END AS IDTIPOPROD,
                 CP.CDFAPRATCPLA AS CDFILAUXPRAT, CP.CDPRATCONSPL AS CDPRATO, CP.CDTPPRATCPLA AS CDTIPOPRAT
          FROM CONSPLAN CP 
          /*INNER JOIN (SELECT CDFILAUXPRAT,CDPRATO, 'P' AS REC_UTIL FROM PRATPADR WHERE CDPRODPRODUCAO IS NOT NULL 
                      UNION ALL 
                      SELECT CDFILAUXPRAT, CDPRATO, 'F' AS REC_UTIL FROM PRATFILI WHERE CDPRODPRODUCAO IS NOT NULL 
                      UNION ALL 
                      SELECT CDFILAUXPRAT, CDPRATO, 'S' AS REC_UTIL FROM PRATSERV WHERE CDPRODPRODUCAO IS NOT NULL)R 
                   ON (R.CDFILAUXPRAT = CP.CDFAPRATCPLA AND 
                       R.CDPRATO      = CP.CDPRATCONSPL AND 
                       R.REC_UTIL = RECEITA_UTILIZADA(CP.CDPRATCONSPL, CP.CDFAPRATCPLA, CP.CDFILICPLA, CP.CDSERVCPLA)) */
          WHERE (CP.NRCONSPLAN = V_NRCONSPLAN) AND  
                (IDCONSPLAN = 'C') AND  
                (E_AGREGADA(P_CDCENTRAL, CP.CDFILICPLA, CP.CDSERVCPLA, CP.CDTPPRATCPLA, CP.DTCONSPLAN) > 0) AND 
                (IDTPREQPROD IN ('D', 'E')) AND (
                EXISTS (SELECT P.CDPRATO 
                          FROM PRATPADR P 
                         WHERE P.CDFILAUXPRAT = CP.CDFAPRATCPLA AND
                               P.CDPRATO      = CP.CDPRATCONSPL AND
                               P.CDPRODPRODUCAO IS NOT NULL) OR
                EXISTS (SELECT P.CDPRATO 
                          FROM PRATFILI P 
                         WHERE P.CDFILAUXPRAT = CP.CDFAPRATCPLA AND
                               P.CDPRATO      = CP.CDPRATCONSPL AND
                               P.CDFILIAL     = CP.CDFILICPLA AND
                               P.CDPRODPRODUCAO IS NOT NULL) OR  
                EXISTS (SELECT P.CDPRATO 
                          FROM PRATSERV P 
                         WHERE P.CDFILAUXPRAT = CP.CDFAPRATCPLA AND
                               P.CDPRATO      = CP.CDPRATCONSPL AND
                               P.CDFILIAL     = CP.CDFILICPLA AND
                               P.CDSERVFILI   = CP.CDSERVCPLA AND
                               P.CDPRODPRODUCAO IS NOT NULL))
        UNION ALL 
         
        SELECT CP.CDFILICPLA AS CDFILIAL, CP.CDSERVCPLA AS CDSERVFILI, CP.DTCONSPLAN AS DTCARDAPIO,  
           CP.CDCLIECPLA AS CDCLIENTE, CP.CDDESTFICPLA AS CDDESTFILI, ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3) AS QTCOMENSAIS, 
           CP.CDPRODCPLA AS CDPRODUTO, ROUND((CP.VRPECONSPLAN/100) * ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3), 3) AS QTTOTCOMENSAIS, 
           ROUND(CP.QTCOMPLPRODCPLA * ((CP.VRPECONSPLAN/100) * ROUND(NVL(CP.QTCOMESTDIE,CP.QTCOMESTCPLA), 3)),5) AS QTTOTPRODUTO, ROUND(CP.QTCOMPLPRODCPLA,5) AS QTPORPESSOA, 
           'DISTRIBUICAO' AS IDTIPOPROD,
           CP.CDFAPRATCPLA AS CDFILAUXPRAT, CP.CDPRATCONSPL AS CDPRATO, CP.CDTPPRATCPLA AS CDTIPOPRAT
        FROM CONSPLAN CP 
       /* INNER JOIN (SELECT CDFILAUXPRAT,CDPRATO, 'P' AS REC_UTIL FROM PRATPADR WHERE CDPRODPRODUCAO IS NOT NULL AND IDFINALPADR = 'F' 
                UNION ALL 
                SELECT CDFILAUXPRAT, CDPRATO, 'F' AS REC_UTIL FROM PRATFILI WHERE CDPRODPRODUCAO IS NOT NULL AND IDFINALFILI = 'F' 
                UNION ALL 
                SELECT CDFILAUXPRAT, CDPRATO, 'S' AS REC_UTIL FROM PRATSERV WHERE CDPRODPRODUCAO IS NOT NULL AND IDFINALSERV = 'F')R 
          ON (R.CDFILAUXPRAT = CP.CDFAPRATCPLA AND 
              R.CDPRATO      = CP.CDPRATCONSPL AND 
              R.REC_UTIL = RECEITA_UTILIZADA(CP.CDPRATCONSPL, CP.CDFAPRATCPLA, CP.CDFILICPLA, CP.CDSERVCPLA)) */
        WHERE (CP.NRCONSPLAN = V_NRCONSPLAN) AND  
              (IDCONSPLAN = 'C') AND  
              (E_AGREGADA(P_CDCENTRAL, CP.CDFILICPLA, CP.CDSERVCPLA, CP.CDTPPRATCPLA, CP.DTCONSPLAN) > 0) AND 
              (IDTPREQPROD = 'P') AND  
              (CP.QTCOMPLPRODCPLA > 0) AND (
                EXISTS (SELECT P.CDPRATO 
                          FROM PRATPADR P 
                         WHERE P.CDFILAUXPRAT = CP.CDFAPRATCPLA AND
                               P.CDPRATO      = CP.CDPRATCONSPL AND
                               P.CDPRODPRODUCAO IS NOT NULL AND
                               P.IDFINALPADR = 'F') OR
                EXISTS (SELECT P.CDPRATO 
                          FROM PRATFILI P 
                         WHERE P.CDFILAUXPRAT = CP.CDFAPRATCPLA AND
                               P.CDPRATO      = CP.CDPRATCONSPL AND
                               P.CDFILIAL     = CP.CDFILICPLA AND
                               P.CDPRODPRODUCAO IS NOT NULL AND
                               P.IDFINALFILI = 'F') OR  
                EXISTS (SELECT P.CDPRATO 
                          FROM PRATSERV P 
                         WHERE P.CDFILAUXPRAT = CP.CDFAPRATCPLA AND
                               P.CDPRATO      = CP.CDPRATCONSPL AND
                               P.CDFILIAL     = CP.CDFILICPLA AND
                               P.CDSERVFILI   = CP.CDSERVCPLA AND
                               P.CDPRODPRODUCAO IS NOT NULL AND
                               P.IDFINALSERV = 'F')  )            
              ) A
        GROUP BY CDFILIAL, CDSERVFILI, DTCARDAPIO, CDCLIENTE, CDDESTFILI, QTCOMENSAIS, 
              CDPRODUTO, QTTOTCOMENSAIS, QTTOTPRODUTO, QTPORPESSOA, IDTIPOPROD,
              CDFILAUXPRAT, CDPRATO, CDTIPOPRAT
        ORDER BY CDFILIAL, CDSERVFILI, DTCARDAPIO, CDCLIENTE, CDDESTFILI, QTCOMENSAIS, 
                 CDPRODUTO, QTTOTCOMENSAIS, IDTIPOPROD
              ) A LEFT JOIN PRODDISTPROD PD ON PD.CDFILIAL = A.CDFILIAL
                                           AND PD.CDSERVFILI = A.CDSERVFILI
                                           AND PD.DTCARDAPIO = A.DTCARDAPIO
                                           AND NVL(PD.CDCLIENTE, 'XXX') = NVL(A.CDCLIENTE, 'XXX')
                                           AND NVL(PD.CDDESTFILI, 'XXX') = NVL(A.CDDESTFILI, 'XXX')
                                           AND PD.CDPRODUTO = A.CDPRODUTO
                                           AND PD.IDTIPOPROD = A.IDTIPOPROD
 		 WHERE PD.NRDISTPROD IS NULL);

         DELETE FROM CONSPLAN WHERE NRCONSPLAN = V_NRCONSPLAN;     
      END IF;
      SELECT COUNT(1) INTO V_QTDEPROD FROM PRODDISTPROD WHERE NRDISTPROD = V_NRDISTPROD;
      
      /*Se nenhum cardapio foi contemplado pelos filtros da procedure, apaga
        a distribuicao*/
      IF V_QTDECARD = 0 AND V_QTDEPROD = 0 THEN 
      
         DELETE FROM PRODDISTPROD
          WHERE NRDISTPROD = V_NRDISTPROD
           AND NRORG = P_NRORG;
           
         DELETE FROM DISTPROD 
         WHERE NRDISTPROD = V_NRDISTPROD
           AND NRORG = P_NRORG;
         
         V_NRDISTPROD := 0;
      END IF;
      
      /*Retorna a chave da distribuicao da producao*/
      P_RETORNO := SUBSTR(TO_CHAR(V_NRDISTPROD) || ';' || V_MSGERRO,1,400);
   
   /*Exclusao da distribuicao da producao*/
   ELSE
      /* VERIFICA SE CANCELOU A EFETIVACAO*/
      SELECT NVL(IDEFETIVADA,'N') INTO V_IDEFETIVADA
        FROM DISTPROD 
       WHERE CDCENTRAL = P_CDCENTRAL
         AND NRDISTPROD = P_NRDISTPROD;
      IF V_IDEFETIVADA = 'S' THEN
        P_RETORNO := 'ERRO_DIST_EFET';
        --EFETIVA_DISTRIBUICAO(P_NRDISTPROD,'C',P_CDCENTRAL,P_CDOPERADOR,NULL,NULL,NULL,P_NRORG,NULL,P_RETORNO);
      ELSE
          /*Apaga cada uma das distribuicoes dos cardapios distribuidos*/
          FOR C_CARD IN
          (SELECT NRDISTCARD
             FROM CARDDISTPROD
            WHERE NRDISTPROD = P_NRDISTPROD
              AND NRDISTCARD IS NOT NULL
              AND NRORG = P_NRORG
          ) LOOP
    
             FOR C_EMB IN
             (SELECT NREMBDISTCARD FROM EMBDISTCARD
              WHERE NRDISTCARD = C_CARD.NRDISTCARD
                AND NRORG = P_NRORG
             ) LOOP
    
                DELETE FROM ITEMBDISTCARD
                 WHERE NREMBDISTCARD = C_EMB.NREMBDISTCARD
                   AND NRORG = P_NRORG;
    
    
             END LOOP;
    
             DELETE FROM EMBDISTCARD
              WHERE NRDISTCARD = C_CARD.NRDISTCARD
                AND NRORG = P_NRORG;   
    
             DELETE FROM CNFDISTCARD
              WHERE NRDISTCARD = C_CARD.NRDISTCARD
                AND NRORG = P_NRORG;
    
             DELETE FROM DISTCARD
              WHERE NRDISTCARD = C_CARD.NRDISTCARD
                AND NRORG = P_NRORG;
          END LOOP;
      
		  DELETE FROM ITEMBDISTPROD
		   WHERE NRDISTPROD = P_NRDISTPROD
			 AND NRORG = P_NRORG;  

          DELETE FROM EMBDISTPROD
		   WHERE NRDISTPROD = P_NRDISTPROD
			 AND NRORG = P_NRORG;          

          /*apaga os itens da distribuicao para produtos de distribuicao, complemento, consumo fixo e percapita*/
          DELETE FROM PRODDISTPROD
           WHERE NRDISTPROD = P_NRDISTPROD;
    
          /*Apaga os itens da distribuicao da producao*/
          DELETE FROM CARDDISTPROD
           WHERE NRDISTPROD = P_NRDISTPROD  
             AND NRORG = P_NRORG;
    
          /*Apaga a distribuicao da producao*/
          DELETE FROM DISTPROD
           WHERE NRDISTPROD = P_NRDISTPROD
             AND NRORG = P_NRORG;
       END IF;
    END IF;
END;