DROP TYPE BALAN_CONS_GERENCIAL_TABLE
/
DROP TYPE BALAN_CONS_GERENCIAL_TYPE

/

CREATE TYPE BALAN_CONS_GERENCIAL_TYPE AS
OBJECT(CDCONTCTBL    VARCHAR2(20),
       NMCONTCTBL    VARCHAR2(100),
       CDNVCONTCTBL  VARCHAR2(01),
       CDFILIAL      VARCHAR2(04),
       NMFILIAL      VARCHAR2(100),
       VRTOTCRED     FLOAT,    
       VRTOTDEBI     FLOAT,
       VRSALDINI     FLOAT,    
       VRSALDFIN     FLOAT)
/

CREATE TYPE BALAN_CONS_GERENCIAL_TABLE AS
TABLE OF BALAN_CONS_GERENCIAL_TYPE
/

CREATE OR REPLACE FUNCTION BALAN_CONS_GERENCIAL (
  P_CDEMPRESA    IN VARCHAR2,
  P_CDFILIAL     IN VARCHAR2,
  P_CDCENTCUST   IN VARCHAR2,
  P_CONTINI      IN CONTCTBL.CDCONTCTBL%TYPE,
  P_CONTFIN      IN CONTCTBL.CDCONTCTBL%TYPE,
  P_DTINI        IN DATE,
  P_DTFIN        IN DATE,
  P_CDNVCONTCTBL IN CONTCTBL.CDNVCONTCTBL%TYPE,
  P_CONSIDERAENC IN VARCHAR2) RETURN BALAN_CONS_GERENCIAL_TABLE PIPELINED IS
  OUT_RECORD BALAN_CONS_GERENCIAL_TYPE := BALAN_CONS_GERENCIAL_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

CURSOR C_BALAN_CONS_GER IS
  (SELECT LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL, LISTA_LANCCONT.CDFILIAL,
          F.NMFILIAL AS NMFILIAL, sum(NVL(LISTA_LANCCONT.CREDITO,0)) AS VRTOTCRED, sum(NVL(LISTA_LANCCONT.DEBITO,0)) AS VRTOTDEBI,
          sum(NVL(LISTA_SALDCCUS.VRSALDINI,0)) as VRSALDINI,
          sum(NVL(NVL(LISTA_SALDCCUS.VRSALDINI,0) + LISTA_LANCCONT.CREDITO + LISTA_LANCCONT.DEBITO,0))
  FROM (SELECT CDCONTCTBL, NMCONTCTBL, CDNVCONTCTBL
            FROM CONTCTBL
           WHERE (CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
             AND ((CDNVCONTCTBL <= P_CDNVCONTCTBL) OR ('0' = P_CDNVCONTCTBL))
           ORDER BY CDCONTCTBL) LISTA_CONTCTBL,


          (SELECT NVL(SUM(VRSALDCCUS), 0) AS VRSALDINI, C.CDCONTCTBL, SALD.CDFILIAL
            FROM CONTCTBL C,SALDCCUS SALD
           WHERE (SALD.CDEMPRESA  IN (SELECT CDEMPRESA
                                        FROM EMPRESA
                                       WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))))
             AND (SALD.CDFILIAL   IN (SELECT CDFILIAL
                                        FROM FILIAL
                                       WHERE ((INSTR(P_CDFILIAL, CDFILIAL) > 0) OR ('T' = P_CDFILIAL))))
             AND (SALD.CDCENTCUST IN (SELECT CDCENTCUST
                                         FROM CENTCUST
                                        WHERE ((INSTR(P_CDCENTCUST, CDCENTCUST) > 0) OR ('T' = P_CDCENTCUST))))
             AND (SALD.CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
             AND (C.CDCONTCTBL(+) = SALD.CDCONTCTBL)
             AND (SALD.DTSALDCCUS = (SELECT MAX(DTSALDCCUS)
                                       FROM SALDCCUS S
                                      WHERE (S.CDEMPRESA  = SALD.CDEMPRESA)
                                        AND (S.CDFILIAL   = SALD.CDFILIAL)
                                        AND (S.CDCENTCUST = SALD.CDCENTCUST)
                                        AND (S.CDCONTCTBL = SALD.CDCONTCTBL)
                                        AND (DTSALDCCUS < P_DTINI)))
           GROUP BY C.CDCONTCTBL, SALD.CDFILIAL
           ORDER BY CDCONTCTBL) LISTA_SALDCCUS,


          (SELECT NVL(SUM(ROUND(DECODE(L.VRLANCCUST, ABS(L.VRLANCCUST), L.VRLANCCUST, 0),2)),0) AS CREDITO,
                 NVL(SUM(ROUND(DECODE(L.VRLANCCUST, ABS(L.VRLANCCUST), 0, L.VRLANCCUST),2)),0) AS DEBITO,
                 L.CDCONTCTBL, L.CDFILIAL
            FROM CONTCTBL C, LANCCCUSTO L
           WHERE (L.CDEMPRESA  IN (SELECT CDEMPRESA
                                     FROM EMPRESA
                                    WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))))
             AND (L.CDFILIAL   IN (SELECT CDFILIAL
                                     FROM FILIAL
                                    WHERE ((INSTR(P_CDFILIAL, CDFILIAL) > 0) OR ('T' = P_CDFILIAL))))
             AND (L.CDCENTCUST IN (SELECT CDCENTCUST
                                     FROM CENTCUST
                                    WHERE ((INSTR(P_CDCENTCUST, CDCENTCUST) > 0) OR ('T' = P_CDCENTCUST))))
             AND (L.CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
             AND (L.CDEMPRESA  = L.CDEMPRESA)
             AND (L.DTCOMPCUSTO BETWEEN P_DTINI AND P_DTFIN)
             AND (C.CDCONTCTBL = L.CDCONTCTBL)
             AND P_CONSIDERAENC = 'S'
           GROUP BY L.CDCONTCTBL, L.CDFILIAL
           UNION ALL
           SELECT NVL(SUM(ROUND(DECODE(L.VRLANCCUST, ABS(L.VRLANCCUST), L.VRLANCCUST, 0),2)),0) AS CREDITO,
                 NVL(SUM(ROUND(DECODE(L.VRLANCCUST, ABS(L.VRLANCCUST), 0, L.VRLANCCUST),2)),0) AS DEBITO,
                 L.CDCONTCTBL, L.CDFILIAL
            FROM CONTCTBL C, LANCCCUSTO L, LANCCONT LC
           WHERE (L.CDEMPRESA  IN (SELECT CDEMPRESA
                                     FROM EMPRESA
                                    WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))))
             AND (L.CDFILIAL   IN (SELECT CDFILIAL
                                     FROM FILIAL
                                    WHERE ((INSTR(P_CDFILIAL, CDFILIAL) > 0) OR ('T' = P_CDFILIAL))))
             AND (L.CDCENTCUST IN (SELECT CDCENTCUST
                                     FROM CENTCUST
                                    WHERE ((INSTR(P_CDCENTCUST, CDCENTCUST) > 0) OR ('T' = P_CDCENTCUST))))
             AND (L.CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
             AND (L.CDEMPRESA  = L.CDEMPRESA)
             AND (L.DTCOMPCUSTO BETWEEN P_DTINI AND P_DTFIN)
             AND (C.CDCONTCTBL = L.CDCONTCTBL)
             AND L.CDEMPRESA = LC.CDEMPRESA
             AND L.NRLANCCONT = LC.NRLANCCONT
             AND L.NRSEQLANC = LC.NRSEQLANC
             AND LC.IDLANCINTE <> 'F'
             AND P_CONSIDERAENC = 'N'             
           GROUP BY L.CDCONTCTBL, L.CDFILIAL
           ORDER BY 3) LISTA_LANCCONT, FILIAL F
  WHERE LISTA_LANCCONT.CDFILIAL = LISTA_SALDCCUS.CDFILIAL(+)
    AND LISTA_LANCCONT.CDCONTCTBL = LISTA_SALDCCUS.CDCONTCTBL(+)
    AND LISTA_LANCCONT.CDCONTCTBL LIKE LISTA_CONTCTBL.CDCONTCTBL||'%'
    AND LISTA_LANCCONT.CDFILIAL = F.CDFILIAL
 GROUP BY LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL, LISTA_LANCCONT.CDFILIAL,
          F.NMFILIAL);

BEGIN

  OPEN C_BALAN_CONS_GER;
    LOOP
       FETCH C_BALAN_CONS_GER INTO
         OUT_RECORD.CDCONTCTBL,
         OUT_RECORD.NMCONTCTBL,
         OUT_RECORD.CDNVCONTCTBL,
         OUT_RECORD.CDFILIAL,
         OUT_RECORD.NMFILIAL,
         OUT_RECORD.VRTOTCRED,
         OUT_RECORD.VRTOTDEBI,
         OUT_RECORD.VRSALDINI,
         OUT_RECORD.VRSALDFIN;
       EXIT WHEN C_BALAN_CONS_GER%NOTFOUND;
       PIPE ROW (OUT_RECORD);
    END LOOP;
  CLOSE C_BALAN_CONS_GER;
  RETURN;
END;
/