CREATE OR REPLACE PROCEDURE RAZAO_OFICIAL(
   P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
   P_CDCONTINI IN CONTCTBL.CDCONTCTBL%TYPE,
   P_CDCONTFIN IN CONTCTBL.CDCONTCTBL%TYPE,
   P_DTINICIAL IN CONTOPECTBL.DTFINALANC%TYPE,
   P_DTFINAL IN CONTOPECTBL.DTFINALANC%TYPE,
   P_RELESP    VARCHAR2 DEFAULT 'N',
   P_NRRAZAO OUT NUMBER)
  IS
   V_VRSALDOATUAL NUMBER;
   V_NRSEQRAZAO NUMBER;
   V_NRSEQRAZ VARCHAR2(20);
   V_NRRAZAO NUMBER;
   V_CDCONTINI VARCHAR2(100);
   V_CDCONTFIM VARCHAR2(100);
   V_CDCONTCTBL VARCHAR2(100);
   V_EXISTELANC VARCHAR2(1);
   V_SOMALANC NUMBER;
   V_NRLANCCONT VARCHAR2(20);
BEGIN
   V_CDCONTINI := P_CDCONTINI;
   V_CDCONTFIM := P_CDCONTFIN||'ZZZZZZZZZZZZZZZZZZZZ';
   V_VRSALDOATUAL := 0;
   NOVO_CODIGO_NT('RAZAOOFICIAL', V_NRSEQRAZ);
   V_NRRAZAO := TO_NUMBER(V_NRSEQRAZ);


   FOR C_SALDANT IN
       (SELECT NVL(S.VRSALDEMPR, 0) VRSALDEMPR, C.CDCONTCTBL
            FROM SALDEMPR S, CONTCTBL C
           WHERE S.CDEMPRESA = P_CDEMPRESA
             AND C.IDTIPOCONT = 'A'
             AND C.IDCONTCCUS <> 'R'
             AND C.CDCONTCTBL = S.CDCONTCTBL(+)
             AND C.CDCONTCTBL BETWEEN V_CDCONTINI AND V_CDCONTFIM
             AND S.DTSALDEMPR(+) = TO_DATE('01/'||TO_CHAR(P_DTINICIAL, 'MM/YYYY')) - 1
           ORDER BY C.CDCONTCTBL)LOOP
       NOVO_CODIGO_NT('SEQRAZAOOFICIAL', V_NRSEQRAZ);
       V_NRSEQRAZAO := TO_NUMBER(V_NRSEQRAZ);

         V_VRSALDOATUAL := abs(C_SALDANT.VRSALDEMPR);

         V_SOMALANC := 0;
         BEGIN
            SELECT SUM (VRLANCAMENT)
              INTO V_SOMALANC
              FROM LANCCONT
             WHERE CDCONTCTBL = C_SALDANT.CDCONTCTBL
               AND CDEMPRESA = P_CDEMPRESA
               AND DTLANCCONT BETWEEN TO_DATE('01/'||TO_CHAR(P_DTINICIAL, 'MM/YYYY')) AND P_DTINICIAL -1;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_SOMALANC := 0;
         END;
         V_VRSALDOATUAL := V_VRSALDOATUAL + NVL(V_SOMALANC,0);

       INSERT INTO RAZAO
          (NRRAZAO, NRSEQRAZAO, CDEMPRESA, CDCONTCTBL, IDVALOR, DTLANCCONT, NRLANCCONT, DSHISTLANC, VRLANCAMENT, VRSALDATUAL, CDCONTCTBLT)
       VALUES
          (V_NRRAZAO, V_NRSEQRAZAO, P_CDEMPRESA, C_SALDANT.CDCONTCTBL, 'S', NULL, 0, NULL, 0, V_VRSALDOATUAL, C_SALDANT.CDCONTCTBL);

         V_EXISTELANC := 'N';
      IF P_RELESP = 'S' THEN
         FOR C_LANC IN (SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, LANCCONT.VRLANCAMENT, LANCCONT.CDDOCUCONT, LANCCONT.DSHISTLANC,
                               LANCCONT.NRLANCCONT, LANCCONT.DTLANCCONT, LANCCONT.NRSEQLANC
                          FROM LANCCONT, CONTCTBL
                         WHERE(CDEMPRESA = P_CDEMPRESA)                     AND
                              (DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL) AND
                              (LANCCONT.CDCONTCTBL = C_SALDANT.CDCONTCTBL) AND
                              (CONTCTBL.CDCONTCTBL = LANCCONT.CDCONTCTBL)  AND
                              (CONTCTBL.IDCONTCCUS <> 'R')
                         ORDER BY LANCCONT.DTLANCCONT, LANCCONT.NRLANCCONT, LANCCONT.NRSEQLANC) LOOP
            NOVO_CODIGO_NT('SEQRAZAOOFICIAL', V_NRSEQRAZ);
            V_NRSEQRAZAO := TO_NUMBER(V_NRSEQRAZ);

            INSERT INTO RAZAO
               (NRRAZAO, NRSEQRAZAO, CDEMPRESA, CDCONTCTBL, IDVALOR, DTLANCCONT,
                NRLANCCONT, CDDOCUCONT, DSHISTLANC, VRLANCAMENT, VRSALDATUAL, IDPART, CDCONTCTBLT)
            VALUES
               (V_NRRAZAO, V_NRSEQRAZAO, P_CDEMPRESA, C_SALDANT.CDCONTCTBL, 'L', C_LANC.DTLANCCONT,
                C_LANC.NRLANCCONT, C_LANC.CDDOCUCONT, C_LANC.DSHISTLANC, C_LANC.VRLANCAMENT, V_VRSALDOATUAL, 'P', C_SALDANT.CDCONTCTBL);

          FOR C_CONTRAPARTIDA IN (SELECT LANCCONT.CDCONTCOME, LANCCONT.CDCONTCTBL, LANCCONT.VRLANCAMENT, LANCCONT.CDDOCUCONT, LANCCONT.DSHISTLANC,
                                           LANCCONT.NRLANCCONT, LANCCONT.DTLANCCONT, LANCCONT.NRSEQLANC
                                      FROM LANCCONT, CONTCTBL
                                     WHERE NRLANCCONT = C_LANC.NRLANCCONT
                                       AND CDEMPRESA = P_CDEMPRESA
                                       AND (LANCCONT.CDCONTCTBL = CONTCTBL.CDCONTCTBL)
                                       AND (CONTCTBL.IDCONTCCUS <> 'R')
                                       AND CONTCTBL.CDCONTCTBL <> C_LANC.CDCONTCTBL
                                     ORDER BY LANCCONT.DTLANCCONT, LANCCONT.NRLANCCONT, LANCCONT.NRSEQLANC) LOOP
              
              
              
             -- IF NVL(V_CDCONTCTBL,'T') <> C_CONTRAPARTIDA.CDCONTCTBL THEN 
                 NOVO_CODIGO_NT('SEQRAZAOOFICIAL', V_NRSEQRAZ);
                 V_NRSEQRAZAO := TO_NUMBER(V_NRSEQRAZ);
                 V_VRSALDOATUAL := V_VRSALDOATUAL + C_CONTRAPARTIDA.VRLANCAMENT;
               
                 INSERT INTO RAZAO
                    (NRRAZAO, NRSEQRAZAO, CDEMPRESA,  CDCONTCTBL, IDVALOR, DTLANCCONT,
                     NRLANCCONT, CDDOCUCONT, DSHISTLANC, VRLANCAMENT, VRSALDATUAL, IDPART, CDCONTCTBLT)
                 VALUES
                    (V_NRRAZAO, V_NRSEQRAZAO, P_CDEMPRESA, C_CONTRAPARTIDA.CDCONTCTBL, 'L', C_CONTRAPARTIDA.DTLANCCONT,
                     C_CONTRAPARTIDA.NRLANCCONT, C_CONTRAPARTIDA.CDDOCUCONT,  C_CONTRAPARTIDA.DSHISTLANC, C_CONTRAPARTIDA.VRLANCAMENT, V_VRSALDOATUAL,'C', C_SALDANT.CDCONTCTBL);
                 V_CDCONTCTBL := C_CONTRAPARTIDA.CDCONTCTBL;    
            --  END IF;       
            END LOOP;
         END LOOP;
      ELSE
         FOR C_LANCAMENTO IN
            (SELECT L.DSHISTLANC, L.VRLANCAMENT, L.NRLANCCONT, L.DTLANCCONT
               FROM LANCCONT L
              WHERE L.CDEMPRESA = P_CDEMPRESA
                AND L.DTLANCCONT BETWEEN P_DTINICIAL AND P_DTFINAL
                AND L.CDCONTCTBL = C_SALDANT.CDCONTCTBL)LOOP
            NOVO_CODIGO_NT('SEQRAZAOOFICIAL', V_NRSEQRAZ);
            V_NRSEQRAZAO := TO_NUMBER(V_NRSEQRAZ);
            V_VRSALDOATUAL := V_VRSALDOATUAL + C_LANCAMENTO.VRLANCAMENT;
            INSERT INTO RAZAO
               (NRRAZAO, NRSEQRAZAO,CDEMPRESA ,CDCONTCTBL, IDVALOR, DTLANCCONT,
                NRLANCCONT, DSHISTLANC, VRLANCAMENT, VRSALDATUAL)
            VALUES
               (V_NRRAZAO, V_NRSEQRAZAO, P_CDEMPRESA, C_SALDANT.CDCONTCTBL, 'L', C_LANCAMENTO.DTLANCCONT,
                C_LANCAMENTO.NRLANCCONT, C_LANCAMENTO.DSHISTLANC, C_LANCAMENTO.VRLANCAMENT, V_VRSALDOATUAL);
            COMMIT;
            V_EXISTELANC := 'S';

         END LOOP;
         IF ((NVL(C_SALDANT.VRSALDEMPR, 0) = 0) AND (V_EXISTELANC = 'N')) THEN
            DELETE
              FROM RAZAO
             WHERE CDCONTCTBL = C_SALDANT.CDCONTCTBL
               AND NRRAZAO = V_NRRAZAO;
         END IF;
      END IF;
   END LOOP;
   P_NRRAZAO := V_NRRAZAO;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('RAZAO_OFICIAL','6.35.016'); END;
/
