CREATE OR REPLACE VIEW MOVICLIE_SUB AS
SELECT
   A.CDFILIAL,A.DTMOVIMCLIE,A.CDCLIENTE,A.CDCONSUMIDOR,A.VRPESUBSIDIO,A.VRTETOSUBSID,
   NVL(A.VRMOVCLI,0)+NVL(A.VRMOVPESCONS,0) AS VRMOVCLI,
   ROUND((A.VRMOVCLI-A.TOTUSUARIO-A.TOTEXTRA),2) AS TOTSUBSIDIADO,
   A.NRSEQVENDA, A.CDCAIXA,A.TOTUSUARIO,
   (DECODE(A.VRTETOSUBSID, 0, A.TOTEXTRA-(A.VRMOVCLI-A.TOTUSUARIO), TOTEXTRA)) AS TOTEXTRA,
   (TOTSETOR) AS TOTSETOR
FROM
   (SELECT
      MO.CDFILIAL, MO.DTMOVIMCLIE, MO.VRTETOSUBSID, MO.CDCLIENTE, MO.CDCONSUMIDOR,
      MO.VRMOVCLI, MO.VRPESUBSIDIO,MO.VRMOVPESCONS, MO.NRSEQVENDA, MO.CDCAIXA,
      DECODE
      ( NVL(MO.VRTETOSUBSID,0),
        0,
        (100-NVL(MO.VRPESUBSIDIO,0)) / 100 * (NVL(MO.VRMOVCLI,0) + NVL(MO.VRMOVPESCONS,0)),
        DECODE
        ( SIGN(((NVL(MO.VRMOVCLI,0) + NVL(MO.VRMOVPESCONS,0))) - NVL(MO.VRTETOSUBSID,0)),
          -1,
          (100 - NVL(MO.VRPESUBSIDIO,0)) / 100 * (NVL(MO.VRMOVCLI,0) + NVL(MO.VRMOVPESCONS,0)),
          (100 - NVL(MO.VRPESUBSIDIO,0)) / 100 * NVL(MO.VRTETOSUBSID,0)
        )
      ) AS TOTUSUARIO,
      DECODE
      ( NVL(MO.VRTETOSUBSID,0),
        0,
        (NVL(MO.VRMOVCLI,0) + NVL(MO.VRMOVPESCONS,0)),
         DECODE
         ( SIGN((NVL(MO.VRMOVCLI,0) + NVL(MO.VRMOVPESCONS,0) - VRTETOSUBSID)),
          -1,
           0,
           ((NVL(MO.VRMOVCLI,0) + NVL(MO.VRMOVPESCONS,0) - VRTETOSUBSID))
         )
      ) AS TOTEXTRA,
      DECODE(CO.IDCONSUMIDOR, 'S', MO.VRMOVPESCONS, 0) AS TOTSETOR
   FROM
      MOVICLIE MO, CONSUMIDOR CO, VENDA VE
   WHERE
      (MO.CDCLIENTE    = CO.CDCLIENTE   ) AND
      (MO.CDCONSUMIDOR = CO.CDCONSUMIDOR) AND
      (VE.CDFILIAL     = MO.CDFILIAL    ) AND
      (VE.CDCAIXA      = MO.CDCAIXA     ) AND
      (VE.NRSEQVENDA   = MO.NRSEQVENDA  ) AND
      (VE.IDSITUVENDA  = 'O'            )) A
/

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

