CREATE OR REPLACE PROCEDURE BALANCETE_OFICIAL(
  P_CDEMPRESA IN EMPRESA.CDEMPRESA%TYPE,
  P_CDCONTINI IN CONTCTBL.CDCONTCTBL%TYPE,
  P_CDCONTFIN IN CONTCTBL.CDCONTCTBL%TYPE,
  P_DATAINIC  IN CONTOPECTBL.DTFINALANC%TYPE,
  P_DATAFIN   IN CONTOPECTBL.DTFINALANC%TYPE,
  P_NIVEL IN CONTCTBL.CDNVCONTCTBL%TYPE,
  P_IDENCERRAMENTO IN VARCHAR2 DEFAULT 'N',
  P_NRBALANCETE OUT NUMBER)
IS
  V_CONTAINICIAL VARCHAR2(100);
  V_CONTAFINAL VARCHAR2 (100);
  V_NRSEQBALANCETE VARCHAR2(20);
  V_NRSEQBAL NUMBER (20);
  V_DTSALDANT DATE;
  V_NRBALANCETE NUMBER;
  V_VRSALDANT NUMBER;
  V_VRCREDITO NUMBER;
  V_VRDEBITO NUMBER;
  V_LANCATUA VARCHAR2 (1);
BEGIN
   V_CONTAINICIAL := P_CDCONTINI;
   V_CONTAFINAL   := P_CDCONTFIN||'ZZZZZZZZZZZZZZZZZZZZ';
   DELETE FROM BALANCETE;
   NOVO_CODIGO_NT('BALANCETEOFICIAL', V_NRSEQBALANCETE);
   V_NRBALANCETE  := TO_NUMBER(V_NRSEQBALANCETE);
   V_DTSALDANT    := TO_DATE('01/'||TO_CHAR(P_DATAINIC, 'MM/YYYY')) -1;
   V_LANCATUA     := 'N';
   FOR C_CONTAS IN
      (SELECT CDCONTCTBL
         FROM CONTCTBL
        WHERE (CONTCTBL.CDCONTCTBL BETWEEN V_CONTAINICIAL AND V_CONTAFINAL)
          AND (CONTCTBL.CDNVCONTCTBL <= P_NIVEL)
          AND (CONTCTBL.IDCONTCCUS <> 'R')
        ORDER BY 1)LOOP
        	NOVO_CODIGO_NT('SEQBALANCETEOFICIAL', V_NRSEQBALANCETE);
         V_NRSEQBAL := TO_NUMBER(V_NRSEQBALANCETE);
        INSERT INTO BALANCETE
           (NRBALANCETE, NRSEQBAL, CDCONTCTBL, VRSALDANT, VRDEBITO, VRCREDITO, VRSALDATUAL, CDEMPRESA)
        VALUES
           (V_NRBALANCETE, V_NRSEQBAL, C_CONTAS.CDCONTCTBL, 0, 0, 0, 0, P_CDEMPRESA);
        BEGIN
           SELECT VRSALDEMPR
             INTO V_VRSALDANT
             FROM SALDEMPR
            WHERE (DTSALDEMPR = V_DTSALDANT)
              AND (CDEMPRESA = P_CDEMPRESA)
              AND (CDCONTCTBL = C_CONTAS.CDCONTCTBL);
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              V_VRSALDANT := 0;
        END;
        BEGIN
           SELECT SUM(VRDEBITO), SUM(VRCREDITO)
             INTO V_VRDEBITO, V_VRCREDITO
             FROM (SELECT 0 VRDEBITO, SUM(VRLANCAMENT) VRCREDITO
                     FROM LANCCONT, (SELECT CDCONTCTBL
                                       FROM CONTCTBL
                                      WHERE IDTIPOCONT = 'A'
                                        AND CDCONTCTBL LIKE C_CONTAS.CDCONTCTBL||'%'
                                        AND IDCONTCCUS <>'R') C
                    WHERE (CDEMPRESA = P_CDEMPRESA)
                      AND (LANCCONT.CDCONTCTBL LIKE C.CDCONTCTBL)
                      AND (DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIN)
                      AND (VRLANCAMENT > 0)
                    UNION ALL
                   SELECT SUM(VRLANCAMENT) VRDEBITO, 0 VRCREDITO
                     FROM LANCCONT, (SELECT CDCONTCTBL
                                       FROM CONTCTBL
                                      WHERE IDTIPOCONT = 'A'
                                        AND CDCONTCTBL LIKE C_CONTAS.CDCONTCTBL||'%'
                                        AND IDCONTCCUS <>'R') C
                    WHERE (CDEMPRESA = P_CDEMPRESA)
                      AND (LANCCONT.CDCONTCTBL LIKE C.CDCONTCTBL)
                      AND (DTLANCCONT BETWEEN P_DATAINIC AND P_DATAFIN)
                      AND (VRLANCAMENT < 0));
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              V_VRDEBITO := 0;
              V_VRCREDITO := 0;
        END;
        UPDATE BALANCETE
           SET VRSALDANT = V_VRSALDANT,
               VRDEBITO = V_VRDEBITO,
               VRCREDITO = V_VRCREDITO,
               VRSALDATUAL = V_VRSALDANT + V_VRDEBITO + V_VRCREDITO
         WHERE NRSEQBAL = V_NRSEQBAL;
        COMMIT;
        IF ((V_VRSALDANT <> 0 OR V_VRDEBITO <> 0 OR V_VRCREDITO <> 0) AND (V_LANCATUA = 'N')) THEN
            V_LANCATUA := 'S';
        END IF;
   END LOOP;
      DELETE
        FROM BALANCETE B
       WHERE VRSALDANT = 0
         AND VRDEBITO = 0
         AND VRCREDITO = 0
         AND VRSALDATUAL = 0
         AND NRBALANCETE = V_NRBALANCETE;
        /* AND EXISTS (SELECT 1
                       FROM CONTCTBL C
                      WHERE B.CDCONTCTBL = C.CDCONTCTBL
                        AND C.IDTIPOCONT = 'A')
                        ;*/
   
   COMMIT;
   P_NRBALANCETE := V_NRBALANCETE;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('BALANCETE_OFICIAL','6.35.031'); END;
/
