CREATE OR REPLACE PROCEDURE CANCELA_APURAC_FACM
(
P_CDFILIAL IN VARCHAR2,
P_CDCIA IN VARCHAR2,
P_NRAPURACFATM IN VARCHAR2,
P_DATAINI IN DATE,
P_DATAFIN IN DATE,
P_CDOPERADOR IN VARCHAR2,
P_NRORG IN NUMBER DEFAULT 1
)
AS
V_CDCLIENTE VARCHAR2(21);
V_DTENTRADA DATE;
V_IDNACCIA VARCHAR2(1);
V_EXISTE_NF_APROVADA_EMITIDA VARCHAR2(4);
BEGIN
   SELECT MAX(CDCLIENTE), MAX(IDNACCIA)
     INTO V_CDCLIENTE, V_IDNACCIA
     FROM COMPANHIA
    WHERE CDCIA = P_CDCIA;

   SELECT MAX(N.CDFILIAL)
     INTO V_EXISTE_NF_APROVADA_EMITIDA
	  FROM CTRLAPURFATM C
	       INNER JOIN NOTAFISCAL N ON N.CDFILIAL = C.CDFILIAL AND
	                                 N.NRAPURACFATM = C.NRAPURACFATM
	 WHERE C.CDFILIAL = P_CDFILIAL   AND
	       C.CDCLIENTE = V_CDCLIENTE   AND
	       ((C.DTINIAPURAC = P_DATAINI) OR (C.DTFIMAPURAC = P_DATAFIN))  AND
	       (C.NRAPURACFATM = P_NRAPURACFATM OR P_NRAPURACFATM = 'T') AND
	       N.IDSTATUS IN ('A', 'E') AND
         N.IDSTATUSNFE IN ('T', 'A', 'D');

   IF V_EXISTE_NF_APROVADA_EMITIDA IS NOT NULL THEN
      /*Operacao bloqueada. Existe nota fiscal emitida ou transferida eletronicamente no periodo informado.*/
      RAISE_APPLICATION_ERROR(-20501, 'APPERRMSGSTR' || 'APP-00047' || 'APPERRMSGEND' , TRUE);
   END IF;

   FOR CUR_NOTAS IN
   (
   	SELECT N.CDFILIAL, N.NRLANCTONF, N.VRNOTAFISC, N.DTENTRSAID,
	       N.IDSTATUS, N.IDSTATUSNFE, N.NRAPURACFATM
	  FROM CTRLAPURFATM C
	       INNER JOIN NOTAFISCAL N ON N.CDFILIAL = C.CDFILIAL AND
	                                 N.NRAPURACFATM = C.NRAPURACFATM
	 WHERE C.CDFILIAL = P_CDFILIAL   AND
	       C.CDCLIENTE = V_CDCLIENTE   AND
	       ((C.DTINIAPURAC = P_DATAINI) OR (C.DTFIMAPURAC = P_DATAFIN))  AND
	       (C.NRAPURACFATM = P_NRAPURACFATM OR P_NRAPURACFATM = 'T') AND
	       N.IDSTATUS IS NOT NULL
	 ORDER BY N.NRLANCTONF
   ) LOOP

		SELECT MAX(DTEMISDEL) AS DTEMISDEL
		 INTO V_DTENTRADA
		 FROM SERVPLANOM S, DELIVERYM D
		WHERE (S.CDFILIALNF = CUR_NOTAS.CDFILIAL)   AND
		      (S.NRLANCTONF = CUR_NOTAS.NRLANCTONF) AND
		      (S.CDFILIAL   = D.CDFILIAL)    AND
		      (S.NRDELIVERY = D.NRDELIVERY);

		IF V_DTENTRADA IS NULL THEN
		  V_DTENTRADA := CUR_NOTAS.DTENTRSAID;
		END IF;

		UPDATE DESPERCIA
			  SET VRUTINOTA = (SELECT NVL(VRUTINOTA,0) - NVL(SUM(VRITEMENCA),0)
		                     FROM (SELECT VRITEMENCA
		                             FROM ITEMENCARGO
		                             WHERE CDFILIAL    = CUR_NOTAS.CDFILIAL   AND
		                                   NRLANCTONF  = CUR_NOTAS.NRLANCTONF AND
		                                   CDENCARGO IN ('12','13')    AND
		                                   V_IDNACCIA = '1'
				                     UNION
				                    
				                     SELECT VRENMOEDESTR
				                       FROM ITEMENCARGO
				                      WHERE CDFILIAL    = CUR_NOTAS.CDFILIAL   AND
				                            NRLANCTONF  = CUR_NOTAS.NRLANCTONF AND
				                            CDENCARGO IN ('12','13')    AND
				                            V_IDNACCIA = '2'))
		WHERE CDCIA    = P_CDCIA AND
		      CDFILIAL = CUR_NOTAS.CDFILIAL AND
		      V_DTENTRADA BETWEEN DTINIDESC AND DTFIMDESC;

		UPDATE DELIVERYM
		  SET IDSITDEL = '1',
		      IDDELIEXPSAP = 'N',
		      NRORGULTATU = P_NRORG,
		      CDOPERULTATU = P_CDOPERADOR,
		      DTULTATU = SYSDATE
		WHERE (CDFILIAL, NRDELIVERY) IN (SELECT CDFILIAL, NRDELIVERY
		                                   FROM SERVPLANOM
		                                  WHERE CDFILIALNF = CUR_NOTAS.CDFILIAL AND
		                                        NRLANCTONF = CUR_NOTAS.NRLANCTONF);


		UPDATE SERVPLANOM
		   SET IDSITSERSP = '1',
		       NRORGULTATU = P_NRORG,
		       CDOPERULTATU = P_CDOPERADOR,
		       DTULTATU = SYSDATE
		 WHERE CDFILIALNF = CUR_NOTAS.CDFILIAL       
		   AND NRLANCTONF = CUR_NOTAS.NRLANCTONF
		   AND EXISTS (SELECT 1 FROM SERVICOCIAFILI 
		                WHERE NRSERVICOCIAFILI = SERVPLANOM.NRSERVICOCIAFILI
		                  AND CDCLASSE = SERVPLANOM.CDCLASSE
		                  AND CDCICLO = SERVPLANOM.CDCICLO 
		                  AND IDFATSE <> '2'
		                  AND NVL(IDFATVENDADEVOL, 'NFVENDA') = 'NFDEVOLUCAO');

		UPDATE SERVPLANOM
		   SET CDFILIALNF = NULL,
		       NRLANCTONF = NULL,
		       NRORGULTATU = P_NRORG,
		       CDOPERULTATU = P_CDOPERADOR,
		       DTULTATU = SYSDATE
		 WHERE CDFILIALNF = CUR_NOTAS.CDFILIAL AND
		       NRLANCTONF = CUR_NOTAS.NRLANCTONF;

		DELETE CONTARECEBER
		 WHERE (CDFILIBOLREC IS NULL)   AND
		       (CDFILIAL   = CUR_NOTAS.CDFILIAL) AND
		       (NRLANCTONF = CUR_NOTAS.NRLANCTONF);

		DELETE ITEMPRODUTO
		 WHERE (CDFILIAL   = CUR_NOTAS.CDFILIAL) AND
		       (NRLANCTONF = CUR_NOTAS.NRLANCTONF);

		DELETE NOTAFISCAL
			 WHERE (IDSTATUS   = 'P') AND
		       (CDFILIAL   = CUR_NOTAS.CDFILIAL) AND
		       (NRLANCTONF = CUR_NOTAS.NRLANCTONF);
   END LOOP;   

   UPDATE CIAFILI
      SET DTULTFECH = P_DATAINI - 1
    WHERE (CDFILIAL = P_CDFILIAL) AND
          (CDCIA = P_CDCIA);
END;
/