CREATE OR REPLACE
     PROCEDURE GERA_RAZAO_PROV(
	  P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
     P_DATAINIC  IN CONTOPECTBL.DTFINALANC%TYPE,
     P_DATAFIM   IN CONTOPECTBL.DTFINALANC%TYPE,
     P_NRCTBTEMP IN CTBTEMP.NRCTBTEMP%TYPE,
     P_IDSALD IN LANCCONT.IDLANCCOME%TYPE,
     P_CDCONTCOME VARCHAR2,
     P_CDCONTINI VARCHAR2,
     P_CDCONTFIN VARCHAR2) 
IS

V_DTSOMA DATE;
V_NRSEQ INTEGER;
V_CONT INTEGER;
V_SALDO FLOAT;
V_SALDOANT FLOAT;
V_CDCONTCTBL VARCHAR2(20);
V_CDCONTCOME VARCHAR2(20);
V_SALDOEMABERTO FLOAT;
BEGIN
--RAISE_APPLICATION_ERROR(-20510,P_CDCONTCOME, TRUE);
      V_NRSEQ := 1;
      V_DTSOMA := LAST_DAY(P_DATAINIC)+1;
      V_DTSOMA := ADD_MONTHS(V_DTSOMA,-1);

      FOR SALDANT IN (SELECT CONT.CDCONTCTBL,
                              S.VRSALDEMPR,
                              S.DTSALDEMPR,
                              CONT.NMCONTCTBL
                        FROM SALDEMPR S, CONTCTBL CONT
                       WHERE(S.CDEMPRESA(+)  = P_CDEMPRESA)    AND
                            (S.CDCONTCTBL(+) = CONT.CDCONTCTBL) AND
                            (CONT.IDTIPOCONT = 'A')             AND
                            (S.DTSALDEMPR(+) = V_DTSOMA-1) AND
                            (CONT.IDCONTCCUS <> 'R')       AND
                            (CONT.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN))LOOP

          INSERT INTO CTBTEMP
             (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET,
             DTLANCCONTT,NRLANCCONTT,VRLANCAMENTT,CDDOCUCONTT,
             DSHISTLANCT, NRSEQCTB, IDCTBTEMP, VRSALDO, IDPART, CDEMPRESA)
          VALUES
             (P_NRCTBTEMP, SALDANT.CDCONTCTBL, '',
             SALDANT.DTSALDEMPR,'',SALDANT.VRSALDEMPR, '',
             '', TO_CHAR(V_NRSEQ), '0', SALDANT.VRSALDEMPR, 'S',P_CDEMPRESA);


         V_NRSEQ := V_NRSEQ+1;
         V_SALDOANT := SALDANT.VRSALDEMPR;
      END LOOP;

    

        FOR C_PROV IN (SELECT DISTINCT L.CDEMPRESA, 
                                       L.NRLANCCONT, 
                                       L.DTLANCCONT, 
                                       L.DSHISTLANC,
                                       L.VRLANCAMENT,
                                       L.CDDOCUCONT,
                                       L.CDCONTCTBL,
                                       L.CDCONTCOME,
                                       NF.NRLANCTONF,
                                       NF.NRNOTAFISC
                         FROM NOTAFISCAL NF, LANCCONT L
                        WHERE NF.CDEMPRESA   = L.CDEMPRESA
                          AND NF.NRLANCTBDOC = L.NRLANCCONT
                          AND L.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN
                          AND NF.CDEMPRESA   = P_CDEMPRESA
                          AND L.DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIM
                          AND ((INSTR(P_CDCONTCOME, '_'||L.CDCONTCOME||'_') > 0) OR (P_CDCONTCOME = 'T'))
                        UNION
                       SELECT DISTINCT L.CDEMPRESA, 
                                       L.NRLANCCONT, 
                                       L.DTLANCCONT, 
                                       L.DSHISTLANC,
                                       L.VRLANCAMENT,
                                       L.CDDOCUCONT,
                                       L.CDCONTCTBL,
                                       L.CDCONTCOME,
                                       'MANUAL' AS NRLANCTONF,
                                       L.CDDOCUCONT AS NRNOTAFISC
                         FROM LANCCONT L
                        WHERE L.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN
                          AND L.CDEMPRESA   = P_CDEMPRESA
                          AND L.DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIM
                          AND IDLANCINTE <> 'I'
                          AND L.VRLANCAMENT > 0
                          AND ((INSTR(P_CDCONTCOME, '_'||L.CDCONTCOME||'_') > 0) OR (P_CDCONTCOME = 'T'))
                          AND NOT EXISTS (SELECT 1
                                            FROM NOTAFISCAL NF
                                           WHERE NF.CDEMPRESA  = L.CDEMPRESA
                                             AND NF.NRLANCTBDOC = L.NRLANCCONT)
                        ORDER BY DTLANCCONT, VRLANCAMENT, DTLANCCONT, CDCONTCOME) LOOP
              IF NVL(V_SALDO,0) = 0 THEN
                 V_SALDO := NVL(V_SALDOANT,0)+C_PROV.VRLANCAMENT; 
              ELSE
                 V_SALDO := NVL(V_SALDO,0) + C_PROV.VRLANCAMENT;
              END IF;   
              INSERT INTO CTBTEMP
                 (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET,
                  DTLANCCONTT,NRLANCCONTT,VRLANCAMENTT,CDDOCUCONTT,
                  DSHISTLANCT, NRSEQCTB, IDCTBTEMP, VRSALDO, IDPART, CDEMPRESA)
              VALUES
                 (P_NRCTBTEMP, C_PROV.CDCONTCTBL, C_PROV.CDCONTCOME,
                 C_PROV.DTLANCCONT,C_PROV.NRLANCCONT,C_PROV.VRLANCAMENT, C_PROV.CDDOCUCONT, 
                 C_PROV.DSHISTLANC, TO_CHAR(V_NRSEQ), '1', V_SALDO, 'P', P_CDEMPRESA );


          V_NRSEQ := V_NRSEQ+1;
                   
           
          FOR C_BAIXA IN (SELECT DISTINCT L.CDEMPRESA, 
                                         L.NRLANCCONT, 
                                         L.DTLANCCONT, 
                                         L.DSHISTLANC,
                                         L.VRLANCAMENT,
                                         L.CDDOCUCONT,
                                         L.CDCONTCTBL,
                                         L.CDCONTCOME,
                                         'BAIXA' AS ID
                           FROM (SELECT DISTINCT BX.CDEMPRESA, BX.NRLCTBBPAG
                                   FROM NOTAFISCAL NF,
                                         BOLETPAGAR BP, 
                                         BAIXAPAGA BX,
                                         CONTAPAGAR CP
                                   WHERE NF.CDFILIAL    = CP.CDFILIAL
                                     AND NF.NRLANCTONF  = CP.NRLANCTONF
                                     AND CP.CDFILIBPAG  = BP.CDFILIAL
                                     AND CP.NRLANBOLPAG = BP.NRLANBOLPAG
                                     AND BX.CDFILIAL    = BP.CDFILIBAIPAG
                                     AND BX.NRBAIXAPAG  = BP.NRBAIXAPAG
                                     AND NF.NRLANCTONF  = C_PROV.NRLANCTONF)BX, LANCCONT L
                          WHERE BX.CDEMPRESA   = L.CDEMPRESA
                            AND BX.NRLCTBBPAG  = L.NRLANCCONT
                            AND L.CDCONTCTBL   = C_PROV.CDCONTCTBL
                            AND BX.CDEMPRESA   = P_CDEMPRESA
                            AND L.DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIM
                            AND L.CDCONTCOME   = C_PROV.CDCONTCOME
                            AND DSHISTLANC LIKE '%'||C_PROV.NRNOTAFISC||'%'
                          UNION
                         SELECT DISTINCT L.CDEMPRESA, 
                                         L.NRLANCCONT, 
                                         L.DTLANCCONT, 
                                         L.DSHISTLANC,
                                         L.VRLANCAMENT,
                                         L.CDDOCUCONT,
                                         L.CDCONTCTBL,
                                         L.CDCONTCOME,
                                         'MANUAL' AS ID
                           FROM LANCCONT L
                          WHERE L.CDCONTCTBL  = C_PROV.CDCONTCTBL
                            AND L.CDEMPRESA   = P_CDEMPRESA
                            AND L.DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIM
                            AND IDLANCINTE <> 'I'
                            AND L.VRLANCAMENT < 0
                            AND 'MANUAL'  = C_PROV.NRLANCTONF
                            AND L.CDCONTCOME  = C_PROV.CDCONTCOME
                            AND DSHISTLANC LIKE '%'||C_PROV.NRNOTAFISC||'%'
                            AND NOT EXISTS (SELECT 1
                                             FROM CTBTEMP C
                                             WHERE NRCTBTEMP     = P_NRCTBTEMP
                                               AND C.NRLANCCONTT = L.NRLANCCONT
                                               AND C.CDCONTCTBLT = L.CDCONTCTBL
                                               AND C.CDCONTCOMET = L.CDCONTCOME)
                          ORDER BY DTLANCCONT,VRLANCAMENT,  CDCONTCOME) LOOP
          V_SALDO := NVL(V_SALDO,0) +  C_BAIXA.VRLANCAMENT;                            
          INSERT INTO CTBTEMP
              (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET, 
               DTLANCCONTT,NRLANCCONTT, VRLANCAMENTT,
               CDDOCUCONTT, DSHISTLANCT, NRSEQCTB, IDCTBTEMP, VRSALDO, IDPART, CDEMPRESA)
              VALUES(P_NRCTBTEMP, C_BAIXA.CDCONTCTBL, C_BAIXA.CDCONTCOME, 
                     C_BAIXA.DTLANCCONT, C_BAIXA.NRLANCCONT, C_BAIXA.VRLANCAMENT,
                     C_BAIXA.CDDOCUCONT, C_BAIXA.DSHISTLANC, TO_CHAR(V_NRSEQ),'1',(NVL(V_SALDO,0)), 'B', P_CDEMPRESA);
  
              V_NRSEQ := V_NRSEQ+1;
              
          IF C_BAIXA.ID = 'BAIXA' THEN
            BEGIN
              SELECT SUM(VRLANCAMENTT)
                INTO V_SALDOEMABERTO
                FROM CTBTEMP
               WHERE CDCONTCTBLT  = C_BAIXA.CDCONTCTBL  
                 AND CDCONTCOMET  = C_BAIXA.CDCONTCOME
                 AND NRCTBTEMP    = P_NRCTBTEMP
                 --AND NRLANCCONTT  IN (C_PROV.NRLANCCONT, C_BAIXA.NRLANCCONT)
                 AND DSHISTLANCT LIKE '%'||C_PROV.NRNOTAFISC||'%';
            EXCEPTION 
                 WHEN NO_DATA_FOUND THEN
               V_SALDOEMABERTO := 0;
            END;
          ELSE
            BEGIN
              SELECT SUM(VRLANCAMENTT)
                INTO V_SALDOEMABERTO
                FROM CTBTEMP
               WHERE CDCONTCTBLT  = C_BAIXA.CDCONTCTBL  
                 AND CDCONTCOMET  = C_BAIXA.CDCONTCOME
                 AND NRCTBTEMP    = P_NRCTBTEMP
                 --AND NRLANCCONTT  IN (C_PROV.NRLANCCONT, C_BAIXA.NRLANCCONT)
                 AND DSHISTLANCT LIKE '%'||C_PROV.NRNOTAFISC||'%';
            EXCEPTION 
                 WHEN NO_DATA_FOUND THEN
               V_SALDOEMABERTO := 0;
            END;          
          END IF;  
          IF V_SALDOEMABERTO <> 0 THEN
            UPDATE CTBTEMP
               SET IDPART  = 'S'
             WHERE CDCONTCTBLT  = C_BAIXA.CDCONTCTBL 
               AND CDCONTCOMET  = C_BAIXA.CDCONTCOME
               AND NRCTBTEMP    = P_NRCTBTEMP
               --AND NRLANCCONTT  IN (C_PROV.NRLANCCONT, C_BAIXA.NRLANCCONT)
               AND DSHISTLANCT LIKE '%'||C_PROV.NRNOTAFISC||'%';        
          ELSE
            UPDATE CTBTEMP
               SET IDPART  = 'N'
             WHERE CDCONTCTBLT  = C_BAIXA.CDCONTCTBL 
               AND CDCONTCOMET  = C_BAIXA.CDCONTCOME
               AND NRCTBTEMP    = P_NRCTBTEMP
               --AND NRLANCCONTT  IN (C_PROV.NRLANCCONT, C_BAIXA.NRLANCCONT)
               AND DSHISTLANCT LIKE '%'||C_PROV.NRNOTAFISC||'%';                
          END IF;
      END LOOP;
     
   END LOOP;
    
    
END;
/