CREATE OR REPLACE PROCEDURE GERA_BALANCETE_GERENCIAL(
    P_CDEMPRESA   IN VARCHAR2, 
    P_CCUST       IN VARCHAR2, 
    P_FILIAL      IN VARCHAR2, 
    P_DTINICIAL   IN NOTAFISCAL.DTEMISSAO%TYPE, 
    P_DTFINAL     IN NOTAFISCAL.DTEMISSAO%TYPE, 
    P_DTSALDO     IN NOTAFISCAL.DTEMISSAO%TYPE, 
    P_CDCONTAINI  IN VARCHAR2, 
    P_CDCONTAFIN  IN VARCHAR2, 
    P_NIVEL       IN VARCHAR2,
    P_NRCTBBALGERTMP OUT VARCHAR2) 
AS 
V_COUNT          NUMBER;
V_NRCTBBALGERTMP NUMBER; 
V_VRCUSTCRED     FLOAT; 
V_VRCUSTDEBI     FLOAT; 
V_NIVELCCTBL     NUMBER; 
V_NIVEL          NUMBER; 
V_NIVELMAXCCTBL  NUMBER; 
V_CDCONTCTBL     VARCHAR2(20); 
BEGIN 
   DELETE FROM CTBBALGERTMP;
   NOVO_CODIGO_NT('NRSEQBALGERTMP', V_NRCTBBALGERTMP); 
   FOR C_LANC IN (SELECT LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL, FILIAL.CDINSCESTA, 
                         FILIAL.NMFILIAL, CENTCUST.NMCENTCUST, CONTCTBL.NMCONTCTBL, SUM(LANCCCUSTO.VRLANCCUST) VRCUST 
                    FROM FILIAL, CENTCUST, CONTCTBL, LANCCCUSTO 
                   WHERE 
                           (LANCCCUSTO.VRLANCCUST < 0)                                  AND 
                           (INSTR(P_CCUST, LANCCCUSTO.CDCENTCUST) > 0 OR (P_CCUST = 'T'))AND 
                           (INSTR(P_FILIAL, LANCCCUSTO.CDFILIAL) > 0 OR (P_FILIAL = 'T')) AND 
                           (INSTR(P_CDEMPRESA,LANCCCUSTO.CDEMPRESA) <> 0  OR (P_CDEMPRESA = 'T'))               AND 
                           (LANCCCUSTO.DTCOMPCUSTO BETWEEN P_DTINICIAL  AND P_DTFINAL)    AND 
                           (LANCCCUSTO.CDCONTCTBL  BETWEEN P_CDCONTAINI AND P_CDCONTAFIN) AND 
                           (LANCCCUSTO.CDCONTCTBL = CONTCTBL.CDCONTCTBL)               AND 
                           (FILIAL.CDFILIAL     = LANCCCUSTO.CDFILIAL)                  AND 
                           (CENTCUST.CDCENTCUST = LANCCCUSTO.CDCENTCUST) 
                   GROUP BY LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL, FILIAL.CDINSCESTA, 
                            FILIAL.NMFILIAL, CENTCUST.NMCENTCUST, CONTCTBL.NMCONTCTBL 
                   UNION 
                  SELECT LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL, FILIAL.CDINSCESTA, 
                         FILIAL.NMFILIAL, CENTCUST.NMCENTCUST, CONTCTBL.NMCONTCTBL, SUM(LANCCCUSTO.VRLANCCUST) VRCUST 
                    FROM FILIAL, CENTCUST, CONTCTBL, LANCCCUSTO 
                   WHERE   (LANCCCUSTO.VRLANCCUST > 0)                                  AND 
                           (INSTR(P_CCUST, LANCCCUSTO.CDCENTCUST) > 0 OR (P_CCUST = 'T'))AND 
                           (INSTR(P_FILIAL, LANCCCUSTO.CDFILIAL) > 0 OR (P_FILIAL = 'T')) AND 
                           (INSTR(P_CDEMPRESA,LANCCCUSTO.CDEMPRESA) <> 0  OR (P_CDEMPRESA = 'T')) AND
                           (LANCCCUSTO.DTCOMPCUSTO BETWEEN P_DTINICIAL  AND P_DTFINAL)    AND 
                           (LANCCCUSTO.CDCONTCTBL  BETWEEN P_CDCONTAINI AND P_CDCONTAFIN) AND 
                           (LANCCCUSTO.CDCONTCTBL = CONTCTBL.CDCONTCTBL)               AND 
                           (FILIAL.CDFILIAL      = LANCCCUSTO.CDFILIAL)                  AND 
                           (CENTCUST.CDCENTCUST  = LANCCCUSTO.CDCENTCUST) 
                   GROUP BY LANCCCUSTO.CDFILIAL, LANCCCUSTO.CDCENTCUST, LANCCCUSTO.CDCONTCTBL, FILIAL.CDINSCESTA, 
                            FILIAL.NMFILIAL, CENTCUST.NMCENTCUST, CONTCTBL.NMCONTCTBL)LOOP 
           BEGIN 
             SELECT TO_NUMBER(MAX(CDNVCONTCTBL)) 
               INTO V_NIVELMAXCCTBL 
               FROM CONTCTBL; 
             EXCEPTION 
                  WHEN NO_DATA_FOUND THEN 
                   V_NIVELMAXCCTBL := 0; 
           END;    
      
       IF P_NIVEL = 'T' THEN 
          V_NIVEL := V_NIVELMAXCCTBL; 
          WHILE V_NIVELMAXCCTBL >= V_NIVEL LOOP 
            BEGIN 
              SELECT MAX(LENGTH(CDCONTCTBL)) 
                INTO V_NIVELCCTBL 
                FROM CONTCTBL 
               WHERE CDNVCONTCTBL = V_NIVEL; 
              EXCEPTION 
                   WHEN NO_DATA_FOUND THEN 
                    V_NIVELCCTBL := 0; 
            END; 
             V_CDCONTCTBL := SUBSTR(C_LANC.CDCONTCTBL,1,V_NIVELCCTBL); 
             
             IF NOT V_CDCONTCTBL < P_CDCONTAINI THEN 
                 BEGIN 
                    SELECT COUNT(*) 
                      INTO V_COUNT 
                      FROM CTBBALGERTMP 
                     WHERE CDCONTCTBLT    = V_CDCONTCTBL 
                       AND CDFILIALT      = C_LANC.CDFILIAL 
                       AND CDCENTCUSTT    = C_LANC.CDCENTCUST 
                       AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
                 EXCEPTION 
                 WHEN NO_DATA_FOUND THEN 
                    V_COUNT := 0; 
                 END; 
                 V_VRCUSTCRED := 0; 
                 V_VRCUSTDEBI := 0; 
                 IF V_COUNT > 0 THEN 
                    IF NVL(C_LANC.VRCUST,0) < 0 THEN 
 
                       UPDATE CTBBALGERTMP 
                          SET VRDEBITOT = NVL(VRDEBITOT,0) + C_LANC.VRCUST 
                        WHERE CDCONTCTBLT    = V_CDCONTCTBL 
                          AND CDFILIALT      = C_LANC.CDFILIAL 
                          AND CDCENTCUSTT    = C_LANC.CDCENTCUST 
                          AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
                    ELSE 
                       UPDATE CTBBALGERTMP 
                          SET VRCREDITOT = NVL(VRCREDITOT,0) + C_LANC.VRCUST 
                        WHERE CDCONTCTBLT    = V_CDCONTCTBL 
                          AND CDFILIALT      = C_LANC.CDFILIAL 
                          AND CDCENTCUSTT    = C_LANC.CDCENTCUST 
                          AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
                    END IF; 
                 ELSE 
                    IF NVL(C_LANC.VRCUST,0) < 0 THEN 
                       V_VRCUSTDEBI := C_LANC.VRCUST; 
                    ELSE 
                       V_VRCUSTCRED := C_LANC.VRCUST; 
                    END IF; 
                    INSERT INTO CTBBALGERTMP 
                               (NRCTBBALGERTMP, CDCONTCTBLT, CDFILIALT, CDCENTCUSTT, DTMESSALDOT, 
                                VRSALDANTT, VRSALDATUAT, VRCREDITOT, VRDEBITOT) 
                         VALUES 
                              (V_NRCTBBALGERTMP, V_CDCONTCTBL, C_LANC.CDFILIAL, C_LANC.CDCENTCUST, P_DTFINAL, 
                               0, 0, V_VRCUSTCRED, V_VRCUSTDEBI); 
 
                END IF; 
             END IF; 
            V_NIVEL := V_NIVEL - 1; 
            IF V_NIVEL = 0 THEN 
               V_NIVEL := 1; 
               V_NIVELMAXCCTBL := 0; 
            END IF; 
          END LOOP; 
       ELSE 
         BEGIN 
           SELECT MAX(LENGTH(CDCONTCTBL)) 
             INTO V_NIVELCCTBL 
             FROM CONTCTBL 
            WHERE CDNVCONTCTBL = TO_NUMBER(P_NIVEL); 
           EXCEPTION 
                WHEN NO_DATA_FOUND THEN 
                 V_NIVELCCTBL := 0; 
         END; 
          V_CDCONTCTBL := SUBSTR(C_LANC.CDCONTCTBL,1,V_NIVELCCTBL); 
          IF NOT V_CDCONTCTBL < P_CDCONTAINI THEN 
             BEGIN 
                 SELECT COUNT(*) 
                   INTO V_COUNT 
                   FROM CTBBALGERTMP 
                  WHERE CDCONTCTBLT    = V_CDCONTCTBL 
                    AND CDFILIALT      = C_LANC.CDFILIAL 
                    AND CDCENTCUSTT    = C_LANC.CDCENTCUST 
                    AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
              EXCEPTION 
              WHEN NO_DATA_FOUND THEN 
                 V_COUNT := 0; 
              END; 
              V_VRCUSTCRED := 0; 
              V_VRCUSTDEBI := 0; 
             IF V_COUNT > 0 THEN 
                 IF NVL(C_LANC.VRCUST,0) < 0 THEN 
 
                    UPDATE CTBBALGERTMP 
                       SET VRDEBITOT = NVL(VRDEBITOT,0) + C_LANC.VRCUST 
                     WHERE CDCONTCTBLT    = V_CDCONTCTBL 
                       AND CDFILIALT      = C_LANC.CDFILIAL 
                       AND CDCENTCUSTT    = C_LANC.CDCENTCUST 
                       AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
                 ELSE 
                    UPDATE CTBBALGERTMP 
                       SET VRCREDITOT = NVL(VRCREDITOT,0) + C_LANC.VRCUST 
                     WHERE CDCONTCTBLT    = V_CDCONTCTBL 
                       AND CDFILIALT      = C_LANC.CDFILIAL 
                       AND CDCENTCUSTT    = C_LANC.CDCENTCUST 
                       AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
                 END IF; 
             ELSE 
                 IF NVL(C_LANC.VRCUST,0) < 0 THEN 
                    V_VRCUSTDEBI := C_LANC.VRCUST; 
                 ELSE 
                    V_VRCUSTCRED := C_LANC.VRCUST; 
                 END IF; 
                 INSERT INTO CTBBALGERTMP 
                            (NRCTBBALGERTMP, CDCONTCTBLT, CDFILIALT, CDCENTCUSTT, DTMESSALDOT, 
                             VRSALDANTT, VRSALDATUAT, VRCREDITOT, VRDEBITOT) 
                      VALUES 
                           (V_NRCTBBALGERTMP, V_CDCONTCTBL, C_LANC.CDFILIAL, C_LANC.CDCENTCUST, P_DTFINAL, 
                            0, 0, V_VRCUSTCRED, V_VRCUSTDEBI); 
 
             END IF; 
          END IF; 
       END IF; 
    END LOOP; 
    V_COUNT := 0; 
    FOR C_DATA IN (SELECT SALDCCUS.CDFILIAL,   FILIAL.NMFILIAL, FILIAL.CDINSCESTA, 
                          SALDCCUS.CDCENTCUST, CENTCUST.NMCENTCUST, 
                          SALDCCUS.CDCONTCTBL, CONTCTBL.NMCONTCTBL, 
                          SALDCCUS.VRSALDCCUS, SALDCCUS.DTSALDCCUS 
                     FROM FILIAL, CENTCUST, CONTCTBL, SALDCCUS 
                    WHERE (INSTR(P_CDEMPRESA,SALDCCUS.CDEMPRESA) <> 0  OR (P_CDEMPRESA = 'T')) AND
                          (INSTR(P_CCUST, SALDCCUS.CDCENTCUST) <> 0 or (P_CCUST ='T'))  AND 
                          (INSTR(P_FILIAL, SALDCCUS.CDFILIAL) <> 0 or (P_FILIAL ='T'))  AND 
                          (SALDCCUS.DTSALDCCUS  = (SELECT MAX(DTSALDCCUS) 
                                                     FROM SALDCCUS S 
                                                    WHERE (S.CDEMPRESA  = SALDCCUS.CDEMPRESA)    AND 
                                                          (S.CDCENTCUST = SALDCCUS.CDCENTCUST)   AND 
                                                          (S.CDFILIAL   = SALDCCUS.CDFILIAL)     AND 
                                                          (S.DTSALDCCUS  <= P_DTSALDO)           AND 
                                                          (S.CDCONTCTBL = SALDCCUS.CDCONTCTBL))) AND 
                          (SALDCCUS.CDCONTCTBL BETWEEN P_CDCONTAINI AND P_CDCONTAFIN) AND 
                          (FILIAL.CDFILIAL     = SALDCCUS.CDFILIAL)                              AND 
                          (CENTCUST.CDCENTCUST = SALDCCUS.CDCENTCUST)                            AND 
                          (CONTCTBL.CDCONTCTBL = SALDCCUS.CDCONTCTBL) 
                    ORDER BY SALDCCUS.CDFILIAL, SALDCCUS.CDCENTCUST, SALDCCUS.CDCONTCTBL)LOOP 
 
       BEGIN 
          SELECT COUNT(*) 
            INTO V_COUNT 
            FROM CTBBALGERTMP 
           WHERE CDCONTCTBLT    = C_DATA.CDCONTCTBL 
             AND CDFILIALT      = C_DATA.CDFILIAL 
             AND CDCENTCUSTT    = C_DATA.CDCENTCUST 
             AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
       EXCEPTION 
       WHEN NO_DATA_FOUND THEN 
          V_COUNT := 0; 
       END; 
       IF V_COUNT > 0 THEN 
          UPDATE CTBBALGERTMP 
             SET VRSALDANTT  = NVL(VRSALDANTT,0) + C_DATA.VRSALDCCUS, 
                 VRSALDATUAT = (VRDEBITOT+C_DATA.VRSALDCCUS+VRCREDITOT), 
                 DTMESSALDOT = C_DATA.DTSALDCCUS 
           WHERE CDCONTCTBLT    = C_DATA.CDCONTCTBL 
             AND CDFILIALT      = C_DATA.CDFILIAL 
             AND CDCENTCUSTT    = C_DATA.CDCENTCUST 
             AND NRCTBBALGERTMP = V_NRCTBBALGERTMP; 
 
       ELSE 
          INSERT INTO CTBBALGERTMP 
                     (NRCTBBALGERTMP, CDCONTCTBLT, CDFILIALT, CDCENTCUSTT, DTMESSALDOT, 
                      VRSALDANTT, VRSALDATUAT, VRCREDITOT, VRDEBITOT) 
               VALUES 
                    (V_NRCTBBALGERTMP, C_DATA.CDCONTCTBL, C_DATA.CDFILIAL, C_DATA.CDCENTCUST, C_DATA.DTSALDCCUS, 
                     C_DATA.VRSALDCCUS, C_DATA.VRSALDCCUS, 0, 0); 
 
       END IF; 
    END LOOP; 
    P_NRCTBBALGERTMP := V_NRCTBBALGERTMP; 
 END;
/

