create or replace PROCEDURE GERA_AJUSTE_BOLIMP
   (P_CDFILIAL    IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF  IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_CDFILIRECE  IN NOTAFISCAL.CDFILIAL%TYPE
)

AS
   V_NRPARCELAS  NUMBER;
   V_VRTOTACONT  CONTAPAGAR.VRATUAPAG%TYPE;
   V_NRCONTADOR  NUMBER;
   V_VRACUMULADO CONTAPAGAR.VRATUAPAG%TYPE;
   V_VRAJUSTEBO  CONTAPAGAR.VRATUAPAG%TYPE;
   V_CDCLASSAJUS TIPOCTPAGAR.CDCLASSFINA%TYPE;
   V_EXISTE      INTEGER;
	V_COUNT_AJUSTE INTEGER;
	V_TIPO_CONTA_PROD CONTAPAGAR.CDTPCTPAGAR%TYPE;
	V_EXISTTPCONTA INTEGER;
   V_IDSISTEMA  VARCHAR2(05);

BEGIN
   BEGIN
     SELECT IDSISTEMA
       INTO V_IDSISTEMA
       FROM NOTAFISCAL
      WHERE CDFILIAL = P_CDFILIAL
        AND NRLANCTONF = P_NRLANCTONF;
   EXCEPTION
   		WHEN NO_DATA_FOUND THEN
			 V_IDSISTEMA  := 'REC';

	END;

   BEGIN
		SELECT COUNT(*) AS NRPARCELAS, SUM(VRATUAPAG) AS VRTOTACONT
		  INTO V_NRPARCELAS, V_VRTOTACONT
		  FROM CONTAPAGAR
		 WHERE CDFILIAL   = P_CDFILIAL AND
				 NRLANCTONF = P_NRLANCTONF;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			 V_NRPARCELAS := 0;
			 V_VRTOTACONT := 0;
	END;

	BEGIN
		SELECT COUNT(*) AS NRPARCELAS
		  INTO V_EXISTTPCONTA
		  FROM CONTAPAGAR
		  WHERE CDFILIAL   = P_CDFILIAL AND
				  NRLANCTONF = P_NRLANCTONF AND
				 CDTPCTPAGAR IS NOT NULL;
	EXCEPTION
		WHEN NO_DATA_FOUND THEN
			 V_EXISTTPCONTA := 0;
	END;
   IF V_IDSISTEMA <> 'TMP' THEN
      IF(V_EXISTTPCONTA > 0) THEN

         FOR C_AJUSTE IN(SELECT NVL(SUM(IT.VRIMPOSTOIT),0) AS VRTOTIMPONF,IP.CDPRODUTO, I.CDAJUCTPAG,T.DSAJUCTPAG
                           FROM ITEMPROIMPOS IT, IMPOSTO I,TIPOAJPAGAR T,ITEMPRODUTO IP
                          WHERE  IT.CDFILIAL    = P_CDFILIAL
                            AND IT.NRLANCTONF  = P_NRLANCTONF
                            AND IT.CDIMPOSTO   = I.CDIMPOSTO
                            AND IT.NRSEQITPROD = IP.NRSEQITPROD
                            AND IT.CDFILIAL = IP.CDFILIAL
                            AND IT.NRLANCTONF = IP.NRLANCTONF
                            AND I.CDAJUCTPAG   = T.CDAJUCTPAG
                            AND I.IDTRATIMPO   = 'R'
                            AND I.IDDESCRFONTE = 'P'
                            AND IT.CDFISCALIT = '1'
                         GROUP BY IP.CDPRODUTO,I.CDAJUCTPAG, T.DSAJUCTPAG) LOOP
            IF C_AJUSTE.VRTOTIMPONF > 0 THEN
               V_NRCONTADOR  := 1;
               V_VRACUMULADO := C_AJUSTE.VRTOTIMPONF;

               FOR C_CNTPAG IN (SELECT NRSEQVENPAG,  DTATUAVENPAG, VRATUAPAG,
                                       CDFILIBPAG, NRLANBOLPAG, CDEMPRESA,
                                       NRPARCELAPAG, CDCLASSFINA, CDFIMOVFLPAG, CDTPCTPAGAR
                                  FROM CONTAPAGAR
                                 WHERE CDFILIAL   = P_CDFILIAL
                                   AND NRLANCTONF = P_NRLANCTONF
                                 ORDER BY NRSEQVENPAG) LOOP

                  IF V_NRPARCELAS = V_NRCONTADOR THEN
                     V_VRAJUSTEBO := V_VRACUMULADO;
                     V_VRACUMULADO := 0;
                  ELSE
                     V_VRAJUSTEBO  := ROUND((C_AJUSTE.VRTOTIMPONF/V_VRTOTACONT) * C_CNTPAG.VRATUAPAG, 2);
                     V_VRACUMULADO := V_VRACUMULADO - V_VRAJUSTEBO;
                  END IF;

                  IF C_CNTPAG.CDCLASSFINA IS NOT NULL THEN
                     V_CDCLASSAJUS := C_CNTPAG.CDCLASSFINA;
                  END IF;

                  ATUALIZA_FLUXO_DE_CAIXA(C_CNTPAG.CDEMPRESA,
                                          P_CDFILIRECE,
                                          C_CNTPAG.DTATUAVENPAG,
                                          'E',
                                          C_CNTPAG.CDCLASSFINA,
                                          V_VRAJUSTEBO * -1,
                                          0);

                  V_COUNT_AJUSTE := 0;
                  BEGIN
                     SELECT COUNT(CDAJUCTPAG)
                       INTO V_COUNT_AJUSTE
                       FROM AJCTPAGAR
                      WHERE CDFILIAL = P_CDFILIAL
                        AND NRLANCTONF = P_NRLANCTONF
                        AND NRSEQVENPAG = C_CNTPAG.NRSEQVENPAG
                        AND NRPARCELAPAG = C_CNTPAG.NRPARCELAPAG
                        AND CDAJUCTPAG = C_AJUSTE.CDAJUCTPAG;
                   EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                      V_COUNT_AJUSTE := 0;
                   END;

                  IF(V_COUNT_AJUSTE = 0) THEN
                     INSERT INTO AJCTPAGAR
                       (CDFILIAL, NRLANCTONF, DTAJUSTEPAG, CDAJUCTPAG, NRSEQVENPAG, NRPARCELAPAG,
                       VRAJUSTEPAG, DSAJUSTEPAG,CDCLASSFINA, DTMOVFLUXO, IDMOVFLUXO, CDEMMFAJCNTP, CDFIMFAJCNTP, IDAJIMPRET)
                     VALUES
                       (P_CDFILIAL, P_NRLANCTONF, C_CNTPAG.DTATUAVENPAG, C_AJUSTE.CDAJUCTPAG, C_CNTPAG.NRSEQVENPAG, C_CNTPAG.NRPARCELAPAG,
                        V_VRAJUSTEBO, SUBSTR(C_AJUSTE.DSAJUCTPAG,1,20), V_CDCLASSAJUS, C_CNTPAG.DTATUAVENPAG, 'E', C_CNTPAG.CDEMPRESA, P_CDFILIRECE,'S');
                  ELSE
                     UPDATE AJCTPAGAR
                         SET VRAJUSTEPAG  = ROUND((VRAJUSTEPAG + V_VRAJUSTEBO),2)
                       WHERE CDFILIAL     = P_CDFILIAL
                         AND NRLANCTONF   = P_NRLANCTONF
                         AND NRSEQVENPAG  = C_CNTPAG.NRSEQVENPAG
                         AND NRPARCELAPAG = C_CNTPAG.NRPARCELAPAG
                         AND CDAJUCTPAG = C_AJUSTE.CDAJUCTPAG;
                  END IF;

                  UPDATE CONTAPAGAR
                     SET VRATUAPAG    = ROUND((VRATUAPAG - V_VRAJUSTEBO),2)
                   WHERE CDFILIAL     = P_CDFILIAL
                     AND NRLANCTONF   = P_NRLANCTONF
                     AND NRSEQVENPAG  = C_CNTPAG.NRSEQVENPAG
                     AND NRPARCELAPAG = C_CNTPAG.NRPARCELAPAG;

                  UPDATE BOLETPAGAR
                     SET VRBOLPAG      =  ROUND((VRBOLPAG - V_VRAJUSTEBO),2),
                         VRFINALBPAG   =  ROUND((VRFINALBPAG - V_VRAJUSTEBO),2)
                   WHERE CDFILIAL    = C_CNTPAG.CDFILIBPAG  AND
                         NRLANBOLPAG = C_CNTPAG.NRLANBOLPAG;

                  V_NRCONTADOR := V_NRCONTADOR + 1;
               END LOOP;
            END IF;
         END LOOP;
      ELSE
         FOR C_AJUSTE IN(SELECT NVL(SUM(IT.VRIMPOSTOIT),0) AS VRTOTIMPONF,IP.CDPRODUTO, I.CDAJUCTPAG,T.DSAJUCTPAG
                           FROM ITEMPROIMPOS IT, IMPOSTO I,TIPOAJPAGAR T,ITEMPRODUTO IP
                          WHERE  IT.CDFILIAL    = P_CDFILIAL
                            AND IT.NRLANCTONF  = P_NRLANCTONF
                            AND IT.CDIMPOSTO   = I.CDIMPOSTO
                            AND IT.NRSEQITPROD = IP.NRSEQITPROD
                            AND IT.CDFILIAL = IP.CDFILIAL
                            AND IT.NRLANCTONF = IP.NRLANCTONF
                            AND I.CDAJUCTPAG   = T.CDAJUCTPAG
                            AND I.IDTRATIMPO   = 'R'
                            AND I.IDDESCRFONTE = 'P'
                            AND IT.CDFISCALIT = '1'
                         GROUP BY IP.CDPRODUTO,I.CDAJUCTPAG, T.DSAJUCTPAG
                        ORDER BY IP.CDPRODUTO) LOOP
            IF C_AJUSTE.VRTOTIMPONF > 0 THEN
                  V_NRCONTADOR  := 1;
                  V_VRACUMULADO := C_AJUSTE.VRTOTIMPONF;
               BEGIN
                  SELECT max(TR.CDTPCTPAGAR), max(TR.CDCLASSFINA)
                    INTO V_TIPO_CONTA_PROD, V_CDCLASSAJUS
                    FROM (SELECT CDARVPROD
                            FROM ITEMPRODUTO I, PRODUTO P
                           WHERE CDFILIAL    = P_CDFILIAL AND
                                 NRLANCTONF  = P_NRLANCTONF AND
                                 I.CDPRODUTO = P.CDPRODUTO AND
                                 I.CDPRODUTO = C_AJUSTE.CDPRODUTO
                          HAVING VRTOTAITEM = (SELECT MAX(VRTOTAITEM)
                                                 FROM ITEMPRODUTO
                                                WHERE CDFILIAL    = P_CDFILIAL AND
                                                      NRLANCTONF  = P_NRLANCTONF AND
                                                      CDPRODUTO = C_AJUSTE.CDPRODUTO)) IT,
                        PRODUTO PR, TIPOCTPAGAR TR
                   WHERE TR.CDTPCTPAGAR = PR.CDTPCTPAGAR
                     AND PR.CDPRODUTO = (SELECT MAX(CDPRODUTO)
                                           FROM (SELECT CDPRODUTO, CDARVPROD
                                                   FROM PRODUTO
                                                  WHERE CDTPCTPAGAR IS NOT NULL) C
                                          WHERE (INSTR(IT.CDARVPROD,RTRIM(C.CDARVPROD)) = 1));
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_TIPO_CONTA_PROD := NULL;
                     V_CDCLASSAJUS := NULL;
               END;

               IF(V_TIPO_CONTA_PROD IS NULL) THEN
                  SELECT TP.CDTPCTPAGAR, TR.CDCLASSFINA
                    INTO V_TIPO_CONTA_PROD, V_CDCLASSAJUS
                    FROM TIPOOPERAC TP, NOTAFISCAL NF, TIPOCTPAGAR TR
                   WHERE TP.CDTIPOOPER = NF.CDTIPOOPER
                     AND NF.CDFILIAL = P_CDFILIAL
                     AND NF.NRLANCTONF = P_NRLANCTONF
                     AND TP.CDTPCTPAGAR = TR.CDTPCTPAGAR
                     AND TP.IDENTRSAIDOP = 'S';
               END IF;
               FOR C_CNTPAG IN (SELECT NRSEQVENPAG,  DTATUAVENPAG, VRATUAPAG,
                                       CDFILIBPAG, NRLANBOLPAG, CDEMPRESA,
                                       NRPARCELAPAG, CDCLASSFINA, CDFIMOVFLPAG, CDTPCTPAGAR
                                  FROM CONTAPAGAR
                                 WHERE CDFILIAL   = P_CDFILIAL
                                   AND NRLANCTONF = P_NRLANCTONF
                                 ORDER BY CDTPCTPAGAR DESC, NRSEQVENPAG ) LOOP
                 SELECT COUNT(*) AS NRPARCELAS, SUM(VRATUAPAG) AS VRTOTACONT
                   INTO V_NRPARCELAS, V_VRTOTACONT
                   FROM CONTAPAGAR
                   WHERE CDFILIAL   = P_CDFILIAL AND
                         NRLANCTONF = P_NRLANCTONF AND
                         CDTPCTPAGAR = C_CNTPAG.CDTPCTPAGAR;

                  IF(V_TIPO_CONTA_PROD = C_CNTPAG.CDTPCTPAGAR) THEN

                     IF V_NRPARCELAS = V_NRCONTADOR THEN
                        V_VRAJUSTEBO := V_VRACUMULADO;
                        V_VRACUMULADO := 0;
                     ELSE
                        V_VRAJUSTEBO  := ROUND((C_AJUSTE.VRTOTIMPONF/V_VRTOTACONT) * C_CNTPAG.VRATUAPAG, 2);
                        V_VRACUMULADO := V_VRACUMULADO - V_VRAJUSTEBO;
                     END IF;

                     IF C_CNTPAG.CDCLASSFINA IS NOT NULL THEN
                          V_CDCLASSAJUS := C_CNTPAG.CDCLASSFINA;
                     END IF;


                     ATUALIZA_FLUXO_DE_CAIXA(C_CNTPAG.CDEMPRESA,
                                             P_CDFILIRECE,
                                             C_CNTPAG.DTATUAVENPAG,
                                             'E',
                                             C_CNTPAG.CDCLASSFINA,
                                             V_VRAJUSTEBO * -1,
                                             0);

                     --Verifica se jA existe um ajuste com mesmo cAdigo
                     V_COUNT_AJUSTE := 0;
                     BEGIN
                        SELECT COUNT(CDAJUCTPAG)
                          INTO V_COUNT_AJUSTE
                          FROM AJCTPAGAR
                         WHERE CDFILIAL = P_CDFILIAL
                           AND NRLANCTONF = P_NRLANCTONF
                           AND NRSEQVENPAG = C_CNTPAG.NRSEQVENPAG
                           AND NRPARCELAPAG = C_CNTPAG.NRPARCELAPAG
                           AND CDAJUCTPAG = C_AJUSTE.CDAJUCTPAG;
                     EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                         V_COUNT_AJUSTE := 0;
                     END;

                     IF(V_COUNT_AJUSTE = 0) THEN
                        INSERT INTO AJCTPAGAR
                          (CDFILIAL, NRLANCTONF, DTAJUSTEPAG, CDAJUCTPAG, NRSEQVENPAG, NRPARCELAPAG,
                          VRAJUSTEPAG, DSAJUSTEPAG,CDCLASSFINA, DTMOVFLUXO, IDMOVFLUXO, CDEMMFAJCNTP, CDFIMFAJCNTP, IDAJIMPRET)
                        VALUES
                          (P_CDFILIAL, P_NRLANCTONF, C_CNTPAG.DTATUAVENPAG, C_AJUSTE.CDAJUCTPAG, C_CNTPAG.NRSEQVENPAG, C_CNTPAG.NRPARCELAPAG,
                           V_VRAJUSTEBO, SUBSTR(C_AJUSTE.DSAJUCTPAG,1,20), V_CDCLASSAJUS, C_CNTPAG.DTATUAVENPAG, 'E', C_CNTPAG.CDEMPRESA, P_CDFILIRECE,'S');
                     ELSE
                        UPDATE AJCTPAGAR
                            SET VRAJUSTEPAG  = ROUND((VRAJUSTEPAG + V_VRAJUSTEBO),2)
                          WHERE CDFILIAL     = P_CDFILIAL
                            AND NRLANCTONF   = P_NRLANCTONF
                            AND NRSEQVENPAG  = C_CNTPAG.NRSEQVENPAG
                            AND NRPARCELAPAG = C_CNTPAG.NRPARCELAPAG
                            AND CDAJUCTPAG = C_AJUSTE.CDAJUCTPAG;
                     END IF;

                     UPDATE CONTAPAGAR
                        SET VRATUAPAG    = ROUND((VRATUAPAG - V_VRAJUSTEBO),2)
                      WHERE CDFILIAL     = P_CDFILIAL
                        AND NRLANCTONF   = P_NRLANCTONF
                        AND NRSEQVENPAG  = C_CNTPAG.NRSEQVENPAG
                        AND NRPARCELAPAG = C_CNTPAG.NRPARCELAPAG;

                     UPDATE BOLETPAGAR
                        SET VRBOLPAG      =  ROUND((VRBOLPAG - V_VRAJUSTEBO),2),
                            VRFINALBPAG   =  ROUND((VRFINALBPAG - V_VRAJUSTEBO),2)
                      WHERE CDFILIAL    = C_CNTPAG.CDFILIBPAG  AND
                            NRLANBOLPAG = C_CNTPAG.NRLANBOLPAG;
                     V_NRCONTADOR := V_NRCONTADOR + 1;
                  END IF;
               END LOOP;
            END IF;
         END LOOP;
      END IF;
   ELSE
      BEGIN
         SELECT COUNT(*) AS NRPARCELAS, SUM(VRATUAREC) AS VRTOTACONT
           INTO V_NRPARCELAS, V_VRTOTACONT
           FROM CONTARECEBER
          WHERE CDFILIAL   = P_CDFILIAL AND
                NRLANCTONF = P_NRLANCTONF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
             V_NRPARCELAS := 0;
             V_VRTOTACONT := 0;
      END;
      FOR C_AJUSTE IN (SELECT ROUND(SUM((IT.VRPRODUTO*(DECODE(IT.CDFISCALIT,'1',IT.VRALIQIT,0))/100)),2) AS VRTOTIMPONF,IT.CDPRODUTO, I.CDAJUCTREC,T.DSAJUCTREC
                         FROM ITEMVENOUTIMPOS IT, IMPOSTO I,TIPOAJRECEBER T
                        WHERE IT.CDFILIALNFFIS    = P_CDFILIAL
                          AND IT.NRLANCTONFFIS  = P_NRLANCTONF
                          AND IT.CDIMPOSTO   = I.CDIMPOSTO
                          AND I.CDAJUCTREC   = T.CDAJUCTREC
                          AND I.IDTRATIMPO   = 'R'
                          AND I.IDDESCRFONTE = 'A'
                          AND IT.CDFISCALIT = '1'
                        GROUP BY IT.CDPRODUTO, I.CDAJUCTREC,T.DSAJUCTREC) LOOP
         IF C_AJUSTE.VRTOTIMPONF > 0 THEN
            V_NRCONTADOR  := 1;
            V_VRACUMULADO := C_AJUSTE.VRTOTIMPONF;
            FOR C_CNTREC IN (SELECT NRSEQVENREC,  DTATUAVENREC, VRATUAREC,
                                    CDFILIBOLREC, NRLANBOLREC, CDEMPRESA,
                                    NRPARCELAREC, CDCLASSFINA, CDFIMOVFLREC, CDTPCTRECEB
                               FROM CONTARECEBER
                              WHERE CDFILIAL   = P_CDFILIAL
                                AND NRLANCTONF = P_NRLANCTONF
                              ORDER BY NRSEQVENREC) LOOP

               IF V_NRPARCELAS = V_NRCONTADOR THEN
                  V_VRAJUSTEBO := V_VRACUMULADO;
                  V_VRACUMULADO := 0;
               ELSE
                  V_VRAJUSTEBO  := ROUND((C_AJUSTE.VRTOTIMPONF/V_VRTOTACONT) * C_CNTREC.VRATUAREC, 2);
                  V_VRACUMULADO := V_VRACUMULADO - V_VRAJUSTEBO;
               END IF;

               IF C_CNTREC.CDCLASSFINA IS NOT NULL THEN
                  V_CDCLASSAJUS := C_CNTREC.CDCLASSFINA;
               END IF;

               ATUALIZA_FLUXO_DE_CAIXA(C_CNTREC.CDEMPRESA,
                                       P_CDFILIRECE,
                                       C_CNTREC.DTATUAVENREC,
                                       'E',
                                       C_CNTREC.CDCLASSFINA,
                                       0,
                                       V_VRAJUSTEBO * -1);

               V_COUNT_AJUSTE := 0;
               BEGIN
                  SELECT COUNT(CDAJUCTREC)
                    INTO V_COUNT_AJUSTE
                    FROM AJCTRECEBER
                   WHERE CDFILIAL = P_CDFILIAL
                     AND NRLANCTONF = P_NRLANCTONF
                     AND NRSEQVENREC = C_CNTREC.NRSEQVENREC
                     AND NRPARCELAREC = C_CNTREC.NRPARCELAREC
                     AND CDAJUCTREC = C_AJUSTE.CDAJUCTREC;
                EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_COUNT_AJUSTE := 0;
                END;

               IF(V_COUNT_AJUSTE = 0) THEN
                  INSERT INTO AJCTRECEBER
                    (CDFILIAL, NRLANCTONF, DTAJUSTEREC, CDAJUCTREC, NRSEQVENREC, NRPARCELAREC,
                    VRAJUSTEREC, DSAJUSTEREC,CDCLASSFINA, DTMOVFLUXO, IDMOVFLUXO, CDEMMFAJCNTR, CDFIMFAJCNTR, IDAJIMPRET)
                  VALUES
                    (P_CDFILIAL, P_NRLANCTONF, C_CNTREC.DTATUAVENREC, C_AJUSTE.CDAJUCTREC, C_CNTREC.NRSEQVENREC, C_CNTREC.NRPARCELAREC,
                     V_VRAJUSTEBO, SUBSTR(C_AJUSTE.DSAJUCTREC,1,40), V_CDCLASSAJUS, C_CNTREC.DTATUAVENREC, 'E', C_CNTREC.CDEMPRESA, P_CDFILIRECE,'S');
               ELSE
                  UPDATE AJCTRECEBER
                      SET VRAJUSTEREC  = ROUND((VRAJUSTEREC + V_VRAJUSTEBO),2)
                    WHERE CDFILIAL     = P_CDFILIAL
                      AND NRLANCTONF   = P_NRLANCTONF
                      AND NRSEQVENREC  = C_CNTREC.NRSEQVENREC
                      AND NRPARCELAREC = C_CNTREC.NRPARCELAREC
                      AND CDAJUCTREC   = C_AJUSTE.CDAJUCTREC;
               END IF;

               UPDATE CONTARECEBER
                  SET VRATUAREC    = ROUND((VRATUAREC - V_VRAJUSTEBO),2)
                WHERE CDFILIAL     = P_CDFILIAL
                  AND NRLANCTONF   = P_NRLANCTONF
                  AND NRSEQVENREC  = C_CNTREC.NRSEQVENREC
                  AND NRPARCELAREC = C_CNTREC.NRPARCELAREC;

               UPDATE BOLETRECEBER
                  SET VRBOLREC      =  ROUND((VRBOLREC - V_VRAJUSTEBO),2),
                      VRFINALBREC   =  ROUND((VRFINALBREC - V_VRAJUSTEBO),2)
                WHERE CDFILIAL    = C_CNTREC.CDFILIBOLREC  AND
                      NRLANBOLREC = C_CNTREC.NRLANBOLREC;

               V_NRCONTADOR := V_NRCONTADOR + 1;
            END LOOP;
         END IF;
      END LOOP;
   END IF;
END;
/