IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'SALDO_VENDA_ANTEC' AND
                 TYPE = 'V' 		        AND
                 U.UID = O.UID           AND
                 U.NAME = USER)
DROP VIEW SALDO_VENDA_ANTEC
GO
CREATE VIEW SALDO_VENDA_ANTEC AS
   SELECT A.CDFILIAL, A.CDCLIENTE, A.CDSERVFILI ,CONVERT(DATE, A.DTVENDA) AS DTVENDA, A.DIFERENCA
   FROM 
   (
   SELECT 
     VE.CDFILIAL, VE.CDSERVFILI, VE.DTVENDA,VE.CDCLIENTE,
     (ISNULL(VE.QTREFEICAO,0)-ISNULL(EF.QTEFETIVOS,0)) AS DIFERENCA
     
   FROM CTRLVENDAANTEC VE 
           LEFT JOIN SUM_EFETIVOS EF ON
              VE.CDFILIAL = EF.CDFILIAL AND
              VE.CDCLIENTE = EF.CDCLIENTE AND
              VE.CDSERVFILI = EF.CDSERVFILI AND
              CONVERT(DATE, VE.DTVENDA) = EF.DTFATURAM, 
        CLIENFILIAL CF

   WHERE       
           VE.CDFILIAL = CF.CDFILIAL AND           
           VE.CDCLIENTE = CF.CDCLIENTE AND           
           CF.IDUTILADIANT = 'S'
           
   UNION ALL

   SELECT
     EF.CDFILIAL,EF.CDSERVFILI, EF.DTFATURAM AS DTVENDA, CF.CDCLIENTE,  
     (0 -ISNULL(EF.QTEFETIVOS,0)) AS DIFERENCA
   FROM SUM_EFETIVOS EF, CLIENFILIAL CF

   WHERE 
         EF.CDFILIAL = CF.CDFILIAL  AND  
         EF.CDFILIAL = CF.CDFILIAL AND
         EF.CDCLIENTE = CF.CDCLIENTE AND
         CF.IDUTILADIANT = 'S' AND
         NOT EXISTS(SELECT VE.CDFILIAL
                    FROM CTRLVENDAANTEC VE
                    WHERE VE.CDFILIAL = EF.CDFILIAL
                    AND   VE.CDFILIAL = CF.CDFILIAL
                    AND   VE.CDCLIENTE = CF.CDCLIENTE
                    AND   VE.CDSERVFILI = EF.CDSERVFILI
                    AND   VE.CDCLIENTE  = EF.CDCLIENTE
                    AND   CONVERT(DATE, VE.DTVENDA)= EF.DTFATURAM)
   )  A  
GO

-- VERSAO --
EXEC ATUVEROBJ 'SALDO_VENDA_ANTEC','4.11.162';
GO