DROP TYPE BALAN_CONS_OFICIAL_TABLE
/
DROP TYPE BALAN_CONS_OFICIAL_TYPE

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

CREATE TYPE BALAN_CONS_OFICIAL_TABLE AS
TABLE OF BALAN_CONS_OFICIAL_TYPE
/

create or replace FUNCTION BALAN_CONS_OFICIAL (
  P_CDEMPRESA    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) RETURN BALAN_CONS_OFICIAL_TABLE PIPELINED IS
  OUT_RECORD BALAN_CONS_OFICIAL_TYPE := BALAN_CONS_OFICIAL_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL);
  CURSOR C_BALAN_CONS_OFI IS 
    (SELECT *
       FROM (SELECT LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL,
            SUM(NVL(LISTA_LANCCONT.CREDITO,0)) AS CREDITO, SUM(NVL(LISTA_LANCCONT.DEBITO,0)) AS DEBITO, NVL(LISTA_SALDEMPR.VRSALDINI,0) AS VRSALDINI,
            (NVL(LISTA_SALDEMPR.VRSALDINI,0) + SUM(NVL(LISTA_LANCCONT.CREDITO,0)) + SUM(NVL(LISTA_LANCCONT.DEBITO,0))) AS VRSALDFIN
      FROM (SELECT CDCONTCTBL, NMCONTCTBL, CDNVCONTCTBL
              FROM CONTCTBL
             WHERE (CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
               AND ((CDNVCONTCTBL <= P_CDNVCONTCTBL) OR ('0' = P_CDNVCONTCTBL))
               AND (IDCONTCCUS <> 'R')
             ORDER BY CDCONTCTBL) LISTA_CONTCTBL,

           (SELECT SUM(SALDEMPR.VRSALDEMPR + NVL(X.VRLANC, 0)) AS VRSALDINI,
                   SALDEMPR.CDCONTCTBL
              FROM CONTCTBL, SALDEMPR LEFT JOIN (SELECT NVL(SUM(L.VRLANCAMENT), 0) VRLANC, C.CDCONTCTBL
                                                   FROM LANCCONT L, CONTCTBL C
                                                  WHERE (L.CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
                                                    AND (L.CDEMPRESA IN (SELECT CDEMPRESA
                                                                           FROM EMPRESA
                                                                          WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))))
                                                    AND L.CDCONTCTBL LIKE C.CDCONTCTBL||'%'
                                                    AND (C.IDCONTCCUS <> 'R')
                                                    AND L.DTLANCCONT BETWEEN (LAST_DAY(ADD_MONTHS(P_DTINI, -1))+1) AND (P_DTINI-1)
                                                  GROUP BY C.CDCONTCTBL) X
                                             ON X.CDCONTCTBL = SALDEMPR.CDCONTCTBL
             WHERE (SALDEMPR.CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
               AND (SALDEMPR.CDCONTCTBL = CONTCTBL.CDCONTCTBL)
               AND (CONTCTBL.IDCONTCCUS <> 'R')
               AND (SALDEMPR.CDEMPRESA IN (SELECT CDEMPRESA
                                             FROM EMPRESA
                                            WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))))
               AND (SALDEMPR.DTSALDEMPR = (SELECT MAX(S.DTSALDEMPR)
                                              FROM SALDEMPR S
                                             WHERE (S.CDCONTCTBL = SALDEMPR.CDCONTCTBL)
                                               AND (S.CDEMPRESA IN (SELECT CDEMPRESA
                                                                     FROM EMPRESA
                                                                    WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0)
                                                                       OR  ('T' = P_CDEMPRESA))))
                                               AND (S.DTSALDEMPR < P_DTINI)))
             GROUP BY SALDEMPR.CDCONTCTBL) LISTA_SALDEMPR,

           (SELECT SUM(ROUND(DECODE(L.VRLANCAMENT, ABS(L.VRLANCAMENT), 0, L.VRLANCAMENT), 2)) AS DEBITO,
                   SUM(ROUND(DECODE(L.VRLANCAMENT, ABS(L.VRLANCAMENT), L.VRLANCAMENT, 0), 2)) AS CREDITO,
                   L.CDCONTCTBL
              FROM LANCCONT L, CONTCTBL CO
             WHERE (L.CDEMPRESA IN (SELECT CDEMPRESA
                                      FROM EMPRESA
                                     WHERE ((INSTR(P_CDEMPRESA, CDEMPRESA) > 0) OR ('T' = P_CDEMPRESA))))
               AND (L.DTLANCCONT BETWEEN P_DTINI   AND P_DTFIN)
               AND (L.CDCONTCTBL BETWEEN P_CONTINI AND P_CONTFIN)
               AND (CO.IDCONTCCUS <> 'R')
               AND (L.CDCONTCTBL = CO.CDCONTCTBL)
             GROUP BY L.CDCONTCTBL) LISTA_LANCCONT
             
     WHERE LISTA_LANCCONT.CDCONTCTBL(+) LIKE LISTA_CONTCTBL.CDCONTCTBL||'%'
       AND LISTA_SALDEMPR.CDCONTCTBL(+) = LISTA_CONTCTBL.CDCONTCTBL
     GROUP BY LISTA_CONTCTBL.CDCONTCTBL, LISTA_CONTCTBL.NMCONTCTBL, LISTA_CONTCTBL.CDNVCONTCTBL,            LISTA_SALDEMPR.VRSALDINI)

 WHERE CREDITO <> 0
    OR DEBITO  <> 0
    OR VRSALDINI <> 0
    OR VRSALDFIN <> 0);
BEGIN
  OPEN C_BALAN_CONS_OFI;
    LOOP
       FETCH C_BALAN_CONS_OFI INTO
         OUT_RECORD.CDCONTCTBL,
         OUT_RECORD.NMCONTCTBL,     
         OUT_RECORD.CDNVCONTCTBL,
         OUT_RECORD.VRTOTCRED,
         OUT_RECORD.VRTOTDEBI,
         OUT_RECORD.VRSALDINI,
         OUT_RECORD.VRSALDFIN;
       EXIT WHEN C_BALAN_CONS_OFI%NOTFOUND;
       PIPE ROW (OUT_RECORD);
    END LOOP;
  CLOSE C_BALAN_CONS_OFI;
  RETURN; 
 END;   
/

-- VERSAO --
BEGIN ATUVEROBJ('BALAN_CONS_OFICIAL','6.35.021'); END;
/

