CREATE OR REPLACE VIEW SALDO_VENDA_ANTEC (CDFILIAL,CDCLIENTE, CDSERVFILI, DTVENDA, DIFERENCA)
AS SELECT A.CDFILIAL, A.CDCLIENTE, A.CDSERVFILI ,TRUNC(A.DTVENDA) AS DTVENDA, A.DIFERENCA
FROM 
(
SELECT 
  VE.CDFILIAL, VE.CDSERVFILI, VE.DTVENDA,VE.CDCLIENTE,
  (NVL(VE.QTREFEICAO,0)-NVL(EF.QTEFETIVOS,0)) AS DIFERENCA
  
FROM CTRLVENDAANTEC VE, SUM_EFETIVOS EF, CLIENFILIAL CF

WHERE       
        VE.CDFILIAL = CF.CDFILIAL AND
        VE.CDFILIAL = EF.CDFILIAL(+) AND
        VE.CDCLIENTE = CF.CDCLIENTE AND
        VE.CDCLIENTE = EF.CDCLIENTE(+) AND
        VE.CDSERVFILI = EF.CDSERVFILI(+) AND
        TRUNC(VE.DTVENDA) = EF.DTFATURAM(+) AND
        CF.IDUTILADIANT = 'S'
        
UNION ALL

SELECT
  EF.CDFILIAL,EF.CDSERVFILI, EF.DTFATURAM AS DTVENDA, CF.CDCLIENTE,  
  (0 -NVL(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   TRUNC(VE.DTVENDA)= EF.DTFATURAM)
)  A
/

-- VERSAO --
BEGIN ATUVEROBJ('SALDO_VENDA_ANTEC','5.06.050'); END;
/