CREATE OR REPLACE PROCEDURE GERA_AJUSTE_DEVOL_CARGA(
   P_CDIMPOSTOCARGA IN MAPAAPURAC.CDIMPOSTO%TYPE,
   P_CDEMPRESA      IN MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDINSCESTA     IN MAPAAPURAC.CDINSCESTA%TYPE,
   P_DTINICIAL      IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_DTFINAL        IN MAPAAPURAC.DTFIMPERIODO%TYPE,
   P_NRSEQUMAPAPU   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDAJUSTEITSEF_DEVCRED    IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDITPROCEDENCIA_DEVCRED  IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDITEMOCOR_DEVCRED       IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_DSITEMOCOR_DEVCRED       IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_DSFLEGALOCOR_DEVCRED     IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDAJUSTEITSEF_DEVESTOR   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDITPROCEDENCIA_DEVESTOR IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDITEMOCOR_DEVESTOR      IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_DSITEMOCOR_DEVESTOR      IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_DSFLEGALOCOR_DEVESTOR    IN MAPAAPURAC.NRSEQUMAPAPU%TYPE)
AS
   V_NRSEQAJUSTE       ITMAPARECUP.NRSEQITMAPR%TYPE;
   V_NRSEQDETITMAPA    DETITMAPARECUP.NRSEQDETITMAPA%TYPE;  
   V_CDAJOUTCREDDEV    VARCHAR2(20);
   V_DSOUTCREDDEV      VARCHAR2(250);
   V_CDAJESTODEBDEV    VARCHAR2(20);
   V_DSESTDEBDEV       VARCHAR2(250);
   V_CDAJUSTE          VARCHAR2(20);
   V_DSAJUSTE          VARCHAR2(250);
   V_IDTPAJUSTE        VARCHAR2(2);
BEGIN

  SELECT CDAJOUTCREDDEV, DSOUTCREDDEV, CDAJESTODEBDEV, DSESTDEBDEV
    INTO V_CDAJOUTCREDDEV, V_DSOUTCREDDEV, V_CDAJESTODEBDEV, V_DSESTDEBDEV
    FROM INSCRESTAD
   WHERE CDEMPRESA    = P_CDEMPRESA
     AND CDINSCESTA   = P_CDINSCESTA;  

  FOR C_ENTRADA IN (SELECT SUM(VRIMPOSTOIT) AS VRIMPCARGATRIBI, I.CDFILIAL, I.NRLANCTONF 
                      FROM ITEMPROIMPOS IT, CFOPPADRAO C, (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                                                 FROM NOTAFISCAL NF, IMPOSTONF IP
                                                                WHERE IP.NRSEQMAPOIMP = P_NRSEQUMAPAPU
                                                                  AND IP.CDFILIAL     = NF.CDFILIAL
                                                                  AND IP.NRLANCTONF   = NF.NRLANCTONF
                                                                  AND NF.IDENTRSAIDOP = 'E') I
                       WHERE IT.CDFILIAL     = I.CDFILIAL
                         AND IT.NRLANCTONF   = I.NRLANCTONF
                         AND IT.CDIMPOSTO    = P_CDIMPOSTOCARGA
                         AND C.CDCFOPPADRAO  = IT.CDFISCOPERIT
                         AND C.IDTIPOCFOP    = 'D'
                        GROUP BY I.CDFILIAL, I.NRLANCTONF) LOOP

        BEGIN
          SELECT CASE WHEN MIN(NF.DTEMISSAO) BETWEEN P_DTINICIAL AND P_DTFINAL THEN 'OC' ELSE 'ED' END
            INTO V_IDTPAJUSTE
            FROM ITEMNOTAFISC IT, NOTAFISCAL NF
           WHERE IT.CDFILIAL     = C_ENTRADA.CDFILIAL
             AND IT.NRLANCTONF   = C_ENTRADA.NRLANCTONF
             AND IT.CDFILIALIT   = NF.CDFILIAL
             AND IT.NRLANCTONFIT = NF.NRLANCTONF;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
             V_IDTPAJUSTE   := NULL;
        END;

        IF V_IDTPAJUSTE IS NOT NULL THEN
          /*BUSCA O MAIOR SEQUENCIAL DO TIPO DE AJUSTE A SER INSERIDO*/
          V_NRSEQAJUSTE := 0;
          BEGIN
             SELECT MAX(I.NRSEQITMAPR)
               INTO V_NRSEQAJUSTE
               FROM ITMAPARECUP I
              WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                AND I.IDTIPOITMAPR = V_IDTPAJUSTE;
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
                V_NRSEQAJUSTE := 0;
          END;

          IF V_IDTPAJUSTE = 'OC' THEN
             V_CDAJUSTE := V_CDAJOUTCREDDEV;
             V_DSAJUSTE := V_DSOUTCREDDEV;
          ELSE
             V_CDAJUSTE := V_CDAJESTODEBDEV;
             V_DSAJUSTE := V_DSESTDEBDEV;
          END IF;

          V_NRSEQAJUSTE := NVL(V_NRSEQAJUSTE,0)+1;

          IF SUBSTR(V_CDAJUSTE,3,2) IN ('30','33','34','35','37','38','40','43','44','45','46',
                                        '47','48','50','53','54','55','56','57','58','70','72') THEN

              INSERT INTO ITMAPARECUP
                 (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDFILIAL, NRLANCTONF,
                  CDAJUSTEITMAPA, IDCARGATRIBUTARIA, IDCOMPOEC197, IDESTODBCRDEV, CDAJUSTEITSEF, CDITPROCEDENCIA, CDITEMOCOR, DSITEMOCOR, DSFLEGALOCOR)
              VALUES
                 (P_NRSEQUMAPAPU, V_IDTPAJUSTE, LPAD(V_NRSEQAJUSTE,10,'0'), V_DSAJUSTE, C_ENTRADA.VRIMPCARGATRIBI, C_ENTRADA.CDFILIAL, C_ENTRADA.NRLANCTONF,
                  V_CDAJUSTE, 'S', 'I', 'S',
                  DECODE(V_IDTPAJUSTE,'OC',P_CDAJUSTEITSEF_DEVCRED, P_CDAJUSTEITSEF_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_CDITPROCEDENCIA_DEVCRED,P_CDITPROCEDENCIA_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_CDITEMOCOR_DEVCRED,P_CDITEMOCOR_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_DSITEMOCOR_DEVCRED,P_DSITEMOCOR_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_DSFLEGALOCOR_DEVCRED,P_DSFLEGALOCOR_DEVESTOR));

              /*BUSCA OS ITENS PARA O AJUSTE*/
			  FOR C_ITENS IN (SELECT SUM(VRIMPOSTOIT) AS VRIMPCARGATRIBI, IT.NRSEQITPROD
			                    FROM ITEMPROIMPOS IT, CFOPPADRAO C
							  WHERE IT.CDFILIAL     = C_ENTRADA.CDFILIAL
							    AND IT.NRLANCTONF   = C_ENTRADA.NRLANCTONF
								AND IT.CDIMPOSTO    = P_CDIMPOSTOCARGA
                                AND C.CDCFOPPADRAO  = IT.CDFISCOPERIT
                                AND C.IDTIPOCFOP    = 'D') LOOP
				  NOVO_CODIGO('DETITMAPARECUP',V_NRSEQDETITMAPA);

				  INSERT INTO DETITMAPARECUP
				   (NRSEQDETITMAPA, NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, CDFILIAL,
					NRLANCTONF, NRSEQITPROD, CDIMPOSTO, NRITPROIMPOS, VRIMPOSTOIT)
				  VALUES
				   (TO_NUMBER(V_NRSEQDETITMAPA), P_NRSEQUMAPAPU, V_IDTPAJUSTE, LPAD(V_NRSEQAJUSTE,10,'0'), C_ENTRADA.CDFILIAL,
					C_ENTRADA.NRLANCTONF, C_ITENS.NRSEQITPROD, P_CDIMPOSTOCARGA, NULL, C_ITENS.VRIMPCARGATRIBI);
			  END LOOP;
          ELSE
              INSERT INTO ITMAPARECUP
                 (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR, CDFILIAL, NRLANCTONF,
                  CDAJUSTEITMAPA, IDCARGATRIBUTARIA, IDCOMPOEC197, IDESTODBCRDEV, CDAJUSTEITSEF, CDITPROCEDENCIA, CDITEMOCOR, DSITEMOCOR, DSFLEGALOCOR)
              VALUES
                 (P_NRSEQUMAPAPU, V_IDTPAJUSTE, LPAD(V_NRSEQAJUSTE,10,'0'), V_DSAJUSTE, C_ENTRADA.VRIMPCARGATRIBI, C_ENTRADA.CDFILIAL, C_ENTRADA.NRLANCTONF,
                  V_CDAJUSTE, 'S', 'N', 'S',
                  DECODE(V_IDTPAJUSTE,'OC',P_CDAJUSTEITSEF_DEVCRED, P_CDAJUSTEITSEF_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_CDITPROCEDENCIA_DEVCRED,P_CDITPROCEDENCIA_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_CDITEMOCOR_DEVCRED,P_CDITEMOCOR_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_DSITEMOCOR_DEVCRED,P_DSITEMOCOR_DEVESTOR),
                  DECODE(V_IDTPAJUSTE,'OC',P_DSFLEGALOCOR_DEVCRED,P_DSFLEGALOCOR_DEVESTOR));
           END IF;
        END IF;
    END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('GERA_AJUSTE_DEVOL_CARGA','6.49.025'); END;
/

