CREATE OR REPLACE PROCEDURE CALCULA_CREDSTANT_MG(
   P_NRSEQUMAPAPU   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE,
   P_CDEMPRESA      IN MAPAAPURAC.CDEMPRESA%TYPE,
   P_CDINSCRICAO    IN MAPAAPURAC.CDINSCESTA%TYPE)
AS
   V_NF              VARCHAR2(14);
   V_NRSEQITMAPR     VARCHAR2(10);
   V_NRSEQDETITMAPA  NUMBER;
   V_SEQPROD         VARCHAR2(30);
   V_CDAJCREDANTST   VARCHAR2(10);
   V_DSAJCREDANTST   VARCHAR2(400);
BEGIN
   /*APAGA OS DADOS GERADOS EM OUTRAS APURACOES*/
   DELETE FROM ITMAPARECUP
    WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
      AND IDTIPOITMAPR = 'OC'
      AND DSITEMMAPR = 'ICMS ST RETIDO ANTERIORMENTE';

   V_NF      := NULL;
   V_SEQPROD := NULL;
   /*BUSCA O MAIOR SEQUENCIAL DE AJUSTE, PARA EVITAR PROBLEMAS COM CHAVE DA TABELA*/
   BEGIN
      SELECT MAX(NRSEQITMAPR)
	     INTO V_NRSEQITMAPR
		  FROM ITMAPARECUP
       WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
	      AND IDTIPOITMAPR = 'OC';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
	      V_NRSEQITMAPR := NULL;
   END;

   BEGIN
      SELECT NVL(CDAJCREDANTST,'X'), DSAJCREDANTST
        INTO V_CDAJCREDANTST, V_DSAJCREDANTST
        FROM PARAMESTSPED
       WHERE CDEMPRESA = P_CDEMPRESA
         AND CDINSCESTA = P_CDINSCRICAO;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_CDAJCREDANTST := 'X';
   END;

   IF (V_CDAJCREDANTST = 'X') THEN
      V_DSAJCREDANTST := 'ICMS ST RETIDO ANTERIORMENTE';
   END IF;

   FOR C_AJ IN (SELECT VRIMPOSTOIT AS CREDITO, CDFILIAL, NRLANCTONF, NRSEQITPROD
                  FROM (SELECT SUM(INTG.VRIMPOSTOIT) AS VRIMPOSTOIT, T.CDFILIAL, T.NRLANCTONF, MIN(T.NRSEQITPROD) AS NRSEQITPROD, PR.CDPRODESTO
                          FROM ITEMPROIMPOS T, INTG_TRABITPROIMPOS INTG, IMPOSTO IM, ITEMPRODUTO P, PRODUTO PR, PRODORIGSTMG PO,
                               (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, N.NRACESSONFE, N.DTENTRSAID, N.DTEMISSAO, N.NRNOTAFISC, N.VRNOTAFISC, N.CDFILIRECE,
                                       I.CDIMPOSTO, N.CDTIPOOPER
                                  FROM IMPOSTONF I, NOTAFISCAL N
                                 WHERE I.CDFILIAL = N.CDFILIAL
                                   AND I.NRLANCTONF = N.NRLANCTONF
                                   AND I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                                   AND N.IDENTRSAIDOP = 'E') NF
                         WHERE T.CDFILIAL = NF.CDFILIAL
                           AND T.NRLANCTONF = NF.NRLANCTONF
                           AND T.CDIMPOSTO = NF.CDIMPOSTO
                           AND T.NRITPROIMPOS = '1'
                           AND T.CDFILIAL = P.CDFILIAL
                           AND T.NRLANCTONF = P.NRLANCTONF
                           AND T.NRSEQITPROD = P.NRSEQITPROD
                           AND P.CDPRODUTO = PO.CDPRODUTO
                           AND INTG.NRACESSONFE = NF.NRACESSONFE
                           AND LPAD(INTG.NRSEQIMPOIT,4,'0') = T.NRSEQITPROD
                           AND INTG.CDIMPOSTOIT = IM.CDIMPOSTO
                           AND IM.CDIMPOSTONFE= '80'
                           AND P.CDPRODUTO = PR.CDPRODUTO
                           AND PO.CDTIPOOPER = NF.CDTIPOOPER
                           GROUP BY  T.CDFILIAL, T.NRLANCTONF, PR.CDPRODESTO
                        UNION ALL
                        SELECT SUM(T.VRIMPOSTOIT) AS VRIMPOSTOIT, ENF.CDFILIAL, ENF.NRLANCTONF, MIN(ENF.NRSEQITPROD) AS NRSEQITPROD, PR.CDPRODESTO
                          FROM RELACPRODOL R, NOTAFISCAL N, ITEMPROIMPOS T, ITEMPRODUTO P, PRODUTO PR, IMPOSTO I,
                               (SELECT T.CDFILIAL, T.NRLANCTONF, T.NRSEQITPROD, P.CDPRODUTO, NRACESSONFE, CDFILIRECE, DTENTRSAID, DTEMISSAO, NRNOTAFISC,
                                       VRNOTAFISC, T.CDFISCOPERIT, T.CDSITUTRIBIT
                                  FROM ITEMPROIMPOS T, ITEMPRODUTO P, PRODORIGSTMG PO, ITEMENCARGO IT,
                                       (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF, N.NRACESSONFE, N.CDMODDOCFISC, N.CDTIPOOPER, IDENTRSAIDOP, DTENTRSAID,
                                               DTEMISSAO, NRNOTAFISC, VRNOTAFISC, CDFILIRECE, I.CDIMPOSTO
                                          FROM IMPOSTONF I, NOTAFISCAL N
                                         WHERE I.CDFILIAL = N.CDFILIAL
                                           AND I.NRLANCTONF = N.NRLANCTONF
                                           AND I.NRSEQUMAPAPU = P_NRSEQUMAPAPU
                                           AND N.IDENTRSAIDOP = 'E') NF
                                 WHERE T.CDFILIAL = NF.CDFILIAL
                                   AND T.NRLANCTONF = NF.NRLANCTONF
                                   AND T.CDIMPOSTO = NF.CDIMPOSTO
                                   AND T.NRITPROIMPOS = '1'
                                   AND T.CDFILIAL = P.CDFILIAL
                                   AND T.NRLANCTONF = P.NRLANCTONF
                                   AND T.NRSEQITPROD = P.NRSEQITPROD
                                   AND P.CDPRODUTO = PO.CDPRODUTO
                                   AND IT.CDFILIAL = T.CDFILIAL
                                   AND IT.NRLANCTONF = T.NRLANCTONF
                                   AND IT.CDENCARGO = '05'
                                   AND IT.VRITEMENCA > 0
                                   AND PO.CDTIPOOPER = NF.CDTIPOOPER) ENF
                         WHERE N.CDFILIAL = T.CDFILIAL
                           AND N.NRLANCTONF = T.NRLANCTONF
                           AND T.CDIMPOSTO = I.CDIMPOSTO
                           AND I.CDIMPOSTONFE = '80'
                           AND N.NRACESSONFE = ENF.NRACESSONFE
                           AND N.IDENTRSAIDOP = 'S'
                           AND T.CDFILIAL = P.CDFILIAL
                           AND T.NRLANCTONF = P.NRLANCTONF
                           AND T.NRSEQITPROD = P.NRSEQITPROD
                           AND  P.CDPRODUTO = R.CDPRODARVOL
                           AND  R.CDPRARVORIG = ENF.CDPRODUTO
                           AND ENF.CDPRODUTO = PR.CDPRODUTO
                           AND T.VRIMPOSTOIT > 0
                         GROUP BY ENF.CDFILIAL, ENF.NRLANCTONF, PR.CDPRODESTO)
                         ORDER BY CDFILIAL, NRLANCTONF, NRSEQITPROD) LOOP

      IF (V_NF IS NULL) OR (V_NF <> C_AJ.CDFILIAL||C_AJ.NRLANCTONF) THEN
         V_NRSEQITMAPR := LPAD(TO_NUMBER(NVL(V_NRSEQITMAPR,0)) + 1,10,'0');
	       INSERT INTO ITMAPARECUP
            (NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, DSITEMMAPR, VRITEMMAPR,
             NRLANCTONF, CDFILIAL, CDAJUSTEITMAPA, IDCOMPOEC197)
         VALUES
            (P_NRSEQUMAPAPU, 'OC', V_NRSEQITMAPR, V_DSAJCREDANTST, C_AJ.CREDITO,
             C_AJ.NRLANCTONF, C_AJ.CDFILIAL, V_CDAJCREDANTST, 'I');
         V_NF := C_AJ.CDFILIAL||C_AJ.NRLANCTONF;
      ELSE
         UPDATE ITMAPARECUP
            SET VRITEMMAPR = NVL(VRITEMMAPR,0) + C_AJ.CREDITO
          WHERE NRSEQUMAPAPU = P_NRSEQUMAPAPU
            AND IDTIPOITMAPR = 'OC'
            AND NRSEQITMAPR  = V_NRSEQITMAPR;
      END IF;

	   IF (V_SEQPROD IS NULL) OR (V_SEQPROD <> C_AJ.CDFILIAL||C_AJ.NRLANCTONF||C_AJ.NRSEQITPROD) THEN
			NOVO_CODIGO_NT('DETITMAPARECUP',V_NRSEQDETITMAPA);
			INSERT INTO DETITMAPARECUP
			  (NRSEQDETITMAPA, NRSEQUMAPAPU, IDTIPOITMAPR, NRSEQITMAPR, CDFILIAL, NRLANCTONF, NRSEQITPROD, NRITPROIMPOS, CDIMPOSTO,  VRIMPOSTOIT, IDSUBSTSOLIDST)
			VALUES
			  (V_NRSEQDETITMAPA, P_NRSEQUMAPAPU, 'OC', V_NRSEQITMAPR,  C_AJ.CDFILIAL,C_AJ.NRLANCTONF, C_AJ.NRSEQITPROD, '1', '00', C_AJ.CREDITO, 'S');
			V_SEQPROD := C_AJ.CDFILIAL||C_AJ.NRLANCTONF||C_AJ.NRSEQITPROD;
	   ELSE
	     UPDATE DETITMAPARECUP
           SET VRIMPOSTOIT    = NVL(VRIMPOSTOIT,0) + C_AJ.CREDITO
         WHERE NRSEQDETITMAPA = V_NRSEQDETITMAPA
           AND NRSEQUMAPAPU   = P_NRSEQUMAPAPU;
	   END IF;
   END LOOP;
END;
/