CREATE OR REPLACE PROCEDURE GERA_RAZAO_TEMP(
	   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,
      P_RELESP    VARCHAR2 DEFAULT 'N') 
IS

V_DTSOMA DATE;
V_NRSEQ INTEGER;
V_CONT INTEGER;
V_SALDO FLOAT;
V_CDCONTCTBL VARCHAR2(20);
V_CDCONTCOME VARCHAR2(20);
BEGIN

      V_NRSEQ := 1;
      V_DTSOMA := LAST_DAY(P_DATAINIC)+1;
      V_DTSOMA := ADD_MONTHS(V_DTSOMA,-1);

      FOR SALDANT IN (
          SELECT S.DTSALDCOME, S.CDSALDCOME, S.CDCONTCTBL, S.VRSALDCOME
          FROM SALDCOME S
          WHERE (S.CDEMPRESA  = P_CDEMPRESA) AND
                (S.IDSALDCOME = P_IDSALD)    AND
                (S.VRSALDCOME <> 0)          AND
                ((INSTR(P_CDCONTCOME, '_'||S.CDSALDCOME||'_') > 0) OR (P_CDCONTCOME = 'T')) AND
                (CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN) AND
                (S.DTSALDCOME IN (SELECT MAX(DTSALDCOME)
                                         FROM SALDCOME
                                         WHERE(DTSALDCOME <= P_DATAINIC) AND
                                              (CDEMPRESA  = P_CDEMPRESA) AND
                                              (CDSALDCOME = S.CDSALDCOME)AND
                                              (IDSALDCOME = P_IDSALD)    AND
                                              (CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN) AND
                                              (CDCONTCTBL = S.CDCONTCTBL))) )LOOP

          INSERT INTO CTBTEMP
             (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET,
             DTLANCCONTT,NRLANCCONTT,VRLANCAMENTT,CDDOCUCONTT,
             DSHISTLANCT, NRSEQCTB, IDCTBTEMP)
          VALUES
             (P_NRCTBTEMP, SALDANT.CDCONTCTBL, SALDANT.CDSALDCOME,
             SALDANT.DTSALDCOME,'',SALDANT.VRSALDCOME, '','', TO_CHAR(V_NRSEQ), '0');


         V_NRSEQ := V_NRSEQ+1;

      END LOOP;

     IF P_DATAINIC <> V_DTSOMA THEN
        FOR SOMALANC IN (
            SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, SUM(LANCCONT.VRLANCAMENT) AS VALOR
            FROM LANCCONT,CONTCTBL
            WHERE(LANCCONT.CDEMPRESA = P_CDEMPRESA)                    AND
                 (LANCCONT.IDLANCCOME = P_IDSALD)                      AND
                 (LANCCONT.DTLANCCONT BETWEEN V_DTSOMA AND P_DATAINIC -1) AND
                 (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL) AND
                 (CONTCTBL.IDCONTCCUS <> 'R') AND
                 (CONTCTBL.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN) AND
                 ((INSTR(P_CDCONTCOME, '_'||LANCCONT.CDCONTCOME||'_') > 0) OR (P_CDCONTCOME = 'T'))
            GROUP BY LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL) LOOP

            SELECT COUNT(*) INTO V_CONT
            FROM CTBTEMP
            WHERE (CTBTEMP.NRCTBTEMP = P_NRCTBTEMP) AND
                  (CTBTEMP.CDCONTCOMET = SOMALANC.CDCONTCOME)AND
                  (CTBTEMP.CDCONTCTBLT = SOMALANC.CDCONTCTBL);

            IF V_CONT = 0 THEN
               INSERT INTO CTBTEMP
                  (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET, DTLANCCONTT,NRLANCCONTT,
                   VRLANCAMENTT,CDDOCUCONTT, DSHISTLANCT, NRSEQCTB, IDCTBTEMP)
               VALUES(P_NRCTBTEMP, SOMALANC.CDCONTCTBL, SOMALANC.CDCONTCOME,
                   (P_DATAINIC - 1),'',SOMALANC.VALOR,'','', LPAD(TO_CHAR(V_NRSEQ),10,'0'), '0');

               V_NRSEQ := V_NRSEQ+1;

            ELSE
               UPDATE CTBTEMP
               SET VRLANCAMENTT = VRLANCAMENTT + SOMALANC.VALOR
               WHERE (CTBTEMP.NRCTBTEMP = P_NRCTBTEMP) AND
                     (CTBTEMP.CDCONTCOMET = SOMALANC.CDCONTCOME)AND
                     (CTBTEMP.CDCONTCTBLT = SOMALANC.CDCONTCTBL);

            END IF;

        END LOOP;

    END IF;

    FOR LANC IN (
        SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, LANCCONT.VRLANCAMENT, LANCCONT.CDDOCUCONT, LANCCONT.DSHISTLANC,
               LANCCONT.NRLANCCONT, LANCCONT.DTLANCCONT
        FROM LANCCONT, CONTCTBL
        WHERE(CDEMPRESA = P_CDEMPRESA)                     AND
             (IDLANCCOME = P_IDSALD)                       AND
             (DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIM) AND
             (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL) AND
             (CONTCTBL.IDCONTCCUS <> 'R') AND
             (CONTCTBL.CDCONTCTBL BETWEEN P_CDCONTINI AND P_CDCONTFIN) AND
             ((INSTR(P_CDCONTCOME, '_'||CDCONTCOME||'_') > 0) OR (P_CDCONTCOME = 'T'))) LOOP

        INSERT INTO CTBTEMP
            (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET, DTLANCCONTT,NRLANCCONTT,
             VRLANCAMENTT,CDDOCUCONTT, DSHISTLANCT, NRSEQCTB, IDCTBTEMP, IDPART)
            VALUES(P_NRCTBTEMP, LANC.CDCONTCTBL, LANC.CDCONTCOME, LANC.DTLANCCONT,
                   LANC.NRLANCCONT, LANC.VRLANCAMENT,LANC.CDDOCUCONT, LANC.DSHISTLANC, LPAD(TO_CHAR(V_NRSEQ),10,'0'),'1','P');

            V_NRSEQ := V_NRSEQ+1;
        IF ((P_IDSALD = 'B') AND (P_RELESP = 'S')) THEN
            IF ((NVL(V_CDCONTCOME,'T') <> LANC.CDCONTCOME) AND (NVL(V_CDCONTCTBL,'T') <> LANC.CDCONTCTBL)) THEN
               BEGIN
                  SELECT NVL(VRLANCAMENTT,0), CDCONTCOMET, CDCONTCTBLT
                    INTO V_SALDO, V_CDCONTCOME, V_CDCONTCTBL
                    FROM CTBTEMP
                   WHERE CDCONTCOMET = LANC.CDCONTCOME
                     AND NRCTBTEMP   = P_NRCTBTEMP
                     AND CDCONTCTBLT = LANC.CDCONTCTBL
                     AND IDCTBTEMP = '0';
               EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                    V_SALDO := 0;
                    V_CDCONTCOME := NULL;
                    V_CDCONTCTBL := NULL;
               END;
            END IF;
           FOR C_CONTRAPARTIDA IN (SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, LANCCONT.VRLANCAMENT, LANCCONT.CDDOCUCONT, LANCCONT.DSHISTLANC,
                                          LANCCONT.NRLANCCONT, LANCCONT.DTLANCCONT
                                     FROM LANCCONT, CONTCTBL
                                    WHERE NRLANCCONT = LANC.NRLANCCONT
                                      AND CDEMPRESA = P_CDEMPRESA
                                      AND (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL)
                                      AND (CONTCTBL.IDCONTCCUS <> 'R')
                                      AND CONTCTBL.CDCONTCTBL <> LANC.CDCONTCTBL
                                    ORDER BY LANCCONT.DTLANCCONT, LANCCONT.NRLANCCONT, CONTCTBL.CDCONTCTBL, LANCCONT.VRLANCAMENT) LOOP
               
                     IF V_SALDO < 0 AND C_CONTRAPARTIDA.VRLANCAMENT < 0 THEN
                        V_SALDO := NVL(V_SALDO,0) + C_CONTRAPARTIDA.VRLANCAMENT;
                     ELSIF V_SALDO > 0 AND C_CONTRAPARTIDA.VRLANCAMENT < 0 THEN
                        V_SALDO := NVL(V_SALDO,0) + C_CONTRAPARTIDA.VRLANCAMENT;
                     ELSE
                        V_SALDO := NVL(V_SALDO,0) + C_CONTRAPARTIDA.VRLANCAMENT;
                     END IF;   
                  
                  
               INSERT INTO CTBTEMP
                   (NRCTBTEMP,CDCONTCTBLT,CDCONTCOMET, DTLANCCONTT,NRLANCCONTT,
                    VRLANCAMENTT,CDDOCUCONTT, DSHISTLANCT, NRSEQCTB, IDCTBTEMP, VRSALDO, IDPART)
                   VALUES(P_NRCTBTEMP, C_CONTRAPARTIDA.CDCONTCTBL, C_CONTRAPARTIDA.CDCONTCOME, C_CONTRAPARTIDA.DTLANCCONT,
                          C_CONTRAPARTIDA.NRLANCCONT, C_CONTRAPARTIDA.VRLANCAMENT,C_CONTRAPARTIDA.CDDOCUCONT, C_CONTRAPARTIDA.DSHISTLANC, LPAD(TO_CHAR(V_NRSEQ),10,'0'),'1', V_SALDO, 'C');



              V_NRSEQ := V_NRSEQ+1;

           END LOOP;
        END IF;
      COMMIT;

     END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('GERA_RAZAO_TEMP','5.30.000'); END;
/

