-- ============================================================
--   DBMS NAME:      ORACLE VERSION 7.X
--   CREATED ON:     25/08/97  10:22
--   PROGRAMADOR:    LEANDRO AUGUSTO
-- ============================================================

-- ============================================================
--   VIEW: EXTRATO_CONSUMIDOR_VIEW
-- ============================================================
CREATE OR REPLACE VIEW EXTRATO_CONSUMIDOR_VIEW
(CDCLIENTE,NRINSJURCLIE,IDTPIJURCLIE,NMFANTCLIE,CDCONSUMIDOR,NMCONSUMIDOR,CDTIPOCONS,CDCCUSCLIE,VRPESUBCONS,
 MAXDTMOVCLI,VRMOVCLICONSUMO,VRMOVCLIOUTR,VRCREDITOS, VRAJUSTEDEBITOS,VRMOVPESCONSUMO,VRMOVPESOUTR,VRCREDITOSPES,VRAJUSTEDEBPES,
 VRSALDOCONS, VRSALDPECONS, DTMOVCLI) AS
   SELECT A.CDCLIENTE   , A.NRINSJURCLIE,   A.IDTPIJURCLIE, A.NMFANTCLIE,
          A.CDCONSUMIDOR, A.NMCONSUMIDOR,   A.CDTIPOCONS  , A.CDCCUSCLIE, A.VRPESUBCONS,
          A.MAXDTMOVCLI , A.VRMOVCLICONSUMO,A.VRMOVCLIOUTR, A.VRCREDITOS, A.VRAJUSTEDEBITOS,A.VRMOVPESCONSUMO,
          A.VRMOVPESOUTR, A.VRCREDITOSPES,A.VRAJUSTEDEBPES,
          M.VRSALDOCONS , M.VRSALDPECONS, A.DTMOVCLI
   FROM
      MOVICLIE M,
      (SELECT M.CDCLIENTE, CL.NRINSJURCLIE, CL.IDTPIJURCLIE, CL.NMFANTCLIE,
              M.CDCONSUMIDOR, C.NMCONSUMIDOR, C.CDTIPOCONS, C.CDCCUSCLIE, C.VRPESUBCONS,
              MAX(M.DTMOVCLI) AS MAXDTMOVCLI, TRUNC(M.DTMOVCLI) AS DTMOVCLI,
              SUM(DECODE(M.NRSEQVENDA,NULL,0,DECODE(M.IDTIPMOCVLI,'D',(-1)*M.VRMOVCLI))) AS VRMOVCLICONSUMO,
              SUM(DECODE(M.IDTIPMOCVLI,'C',M.VRMOVCLI,DECODE(M.IDTIPMOCVLI,'A',(-1)*M.VRMOVCLI,DECODE(M.IDTIPMOCVLI||NVL(M.NRSEQVENDA,'D'),'DD',(-1)*M.VRMOVCLI,0)))) AS VRMOVCLIOUTR,
              SUM(DECODE(M.IDTIPMOCVLI,'C',M.VRMOVCLI)) AS VRCREDITOS,
              SUM(DECODE(M.IDTIPMOCVLI,'A',(-1)*M.VRMOVCLI,DECODE(M.IDTIPMOCVLI||NVL(M.NRSEQVENDA,'D'),'DD',(-1)*M.VRMOVCLI,0))) AS VRAJUSTEDEBITOS,
              SUM(DECODE(M.NRSEQVENDA,NULL,0,DECODE(M.IDTIPMOCVLI,'D',(-1)*M.VRMOVPESCONS))) AS VRMOVPESCONSUMO,
              SUM(DECODE(M.IDTIPMOCVLI,'C',M.VRMOVPESCONS,DECODE(M.IDTIPMOCVLI,'A',(-1)*M.VRMOVPESCONS,DECODE(M.IDTIPMOCVLI||NVL(M.NRSEQVENDA,'D'),'DD',(-1)*M.VRMOVPESCONS,0)))) AS VRMOVPESOUTR,
              SUM(DECODE(M.IDTIPMOCVLI,'C',M.VRMOVPESCONS)) AS VRCREDITOSPES,
              SUM(DECODE(M.IDTIPMOCVLI,'A',(-1)*M.VRMOVPESCONS,DECODE(M.IDTIPMOCVLI||NVL(M.NRSEQVENDA,'D'),'DD',(-1)*M.VRMOVPESCONS,0))) AS VRAJUSTEDEBPES
       FROM MOVICLIE M, CONSUMIDOR C, CLIENTE CL
       WHERE (M.CDCLIENTE    = CL.CDCLIENTE  ) AND
             (C.CDCLIENTE    = M.CDCLIENTE   ) AND
             (C.CDCONSUMIDOR = M.CDCONSUMIDOR)
       GROUP BY M.CDCLIENTE, CL.NRINSJURCLIE, CL.IDTPIJURCLIE, CL.NMFANTCLIE,
                M.CDCONSUMIDOR, C.NMCONSUMIDOR, C.CDTIPOCONS, C.CDCCUSCLIE,C.VRPESUBCONS,TRUNC(M.DTMOVCLI)) A
   WHERE M.CDCLIENTE    = A.CDCLIENTE
     AND M.CDCONSUMIDOR = A.CDCONSUMIDOR
     AND M.DTMOVCLI     = A.MAXDTMOVCLI
/



-- VERSAO --
BEGIN ATUVEROBJ('EXTRATO_CONSUMIDOR_VIEW','4.05.136'); END;
/

