IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE
           O.NAME = 'MOVICLIE_SUB' AND
	   TYPE = 'V' 		  AND
	   U.UID = O.UID          AND
	   U.NAME = USER)

   DROP VIEW MOVICLIE_SUB
GO

CREATE VIEW MOVICLIE_SUB AS
SELECT
   A.CDFILIAL,A.DTMOVIMCLIE,A.CDCLIENTE,A.CDCONSUMIDOR,A.VRPESUBSIDIO,A.VRTETOSUBSID,
   ISNULL(A.VRMOVCLI,0)+ISNULL(A.VRMOVPESCONS,0) AS VRMOVCLI,
   ROUND((A.VRMOVCLI-A.TOTUSUARIO-A.TOTEXTRA),2) AS TOTSUBSIDIADO,
   A.NRSEQVENDA, A.CDCAIXA,A.TOTUSUARIO,
   CASE A.VRTETOSUBSID
      WHEN 0 THEN A.TOTEXTRA - (A.VRMOVCLI-A.TOTUSUARIO)
      ELSE A.TOTEXTRA
      END AS TOTEXTRA,
   A.TOTSETOR
FROM
   (SELECT
      MO.CDFILIAL, MO.DTMOVIMCLIE, MO.VRTETOSUBSID, MO.CDCLIENTE, MO.CDCONSUMIDOR,
      MO.VRMOVCLI, MO.VRPESUBSIDIO,MO.VRMOVPESCONS, MO.NRSEQVENDA, MO.CDCAIXA,
      CASE ISNULL(MO.VRTETOSUBSID,0)
      WHEN 0 THEN (100-ISNULL(MO.VRPESUBSIDIO,0)) / 100 * (ISNULL(MO.VRMOVCLI,0) + ISNULL(MO.VRMOVPESCONS,0))
      ELSE
         CASE SIGN(((ISNULL(MO.VRMOVCLI,0) + ISNULL(MO.VRMOVPESCONS,0))) - ISNULL(MO.VRTETOSUBSID,0))
         WHEN -1 THEN (100 - ISNULL(MO.VRPESUBSIDIO,0)) / 100 * (ISNULL(MO.VRMOVCLI,0) + ISNULL(MO.VRMOVPESCONS,0))
                 ELSE (100 - ISNULL(MO.VRPESUBSIDIO,0)) / 100 * ISNULL(MO.VRTETOSUBSID,0)
         END
      END AS TOTUSUARIO,

      CASE MO.VRTETOSUBSID
      WHEN 0 THEN (ISNULL(MO.VRMOVCLI,0) + ISNULL(MO.VRMOVPESCONS,0))
             ELSE
             CASE SIGN((ISNULL(MO.VRMOVCLI,0) + ISNULL(MO.VRMOVPESCONS,0) - VRTETOSUBSID))
             WHEN -1 THEN 0
                     ELSE
                    ((ISNULL(MO.VRMOVCLI,0) + ISNULL(MO.VRMOVPESCONS,0) - VRTETOSUBSID))
             END
      END AS TOTEXTRA,

      CASE CO.IDCONSUMIDOR
      WHEN 'S' THEN MO.VRMOVPESCONS
               ELSE 0
      END 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
GO
