PROMPT CREATE OR REPLACE VIEW vw_invope
CREATE OR REPLACE VIEW vw_invope (
  empresa,
  filial,
  documento,
  usuario,
  a1cont,
  diva1cont,
  a2cont,
  diva2cont,
  a3cont,
  diva3cont,
  a4cont,
  diva4cont
) AS
select distinct empresa, filial, documento, usuario,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario1  = a.usuario
                ) as A1CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario1  = a.usuario
                      and cont1a   <> saldo
                ) as DIVA1CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario2  = a.usuario
                ) as A2CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario2  = a.usuario
                      and cont2a   <> saldo
                ) as DIVA2CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario3  = a.usuario
                ) as A3CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario3  = a.usuario
                      and cont3a   <> saldo
                ) as DIVA3CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario4  = a.usuario
                ) as A4CONT,
                (select count(*)
                   from vw_invbase
                    where documento = a.documento
                      and empresa   = a.empresa
                      and filial    = a.filial
                      and usuario4  = a.usuario
                      and cont4a   <> saldo
                ) as DIVA4CONT
from
      (select distinct documento, empresa, filial, usuario1 as usuario
        from vw_invbase
        where usuario1 is not null
        group by documento,
                 empresa,
                 filial,
                 usuario1
       union
       select distinct documento, empresa, filial, usuario2 as usuario
        from vw_invbase
        where usuario2 is not null
        group by documento,
                 empresa,
                 filial,
                 usuario2
       union
       select distinct documento, empresa, filial, usuario3 as usuario
        from vw_invbase
        where usuario3 is not null
        group by documento,
                 empresa,
                 filial,
                 usuario3
       union
       select distinct documento, empresa, filial, usuario4 as usuario
        from vw_invbase
        where usuario4 is not null
        group by documento,
                 empresa,
                 filial,
                 usuario4
      ) A
group by documento, empresa, filial, usuario
/