PROMPT CREATE OR REPLACE VIEW vw_invres
CREATE OR REPLACE VIEW vw_invres (
  documento,
  empresa,
  filial,
  totalitens,
  totalencerrados,
  total1acont,
  pend1acont,
  pend2acont,
  total2acont,
  pend2alib,
  pend3acont,
  total3acont,
  pend3alib,
  pend4acont,
  total4acont,
  pend4alib
) AS
select distinct
        documento,
        empresa,
        filial,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
         ) as TOTALITENS,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and status = '8'
         ) as TOTALENCERRADOS,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario1 is not null
         ) as TOTAL1ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario1 is null
               and status       = 1
         ) as TOTAL1APEND,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario1 is null
               and status       = 2
         ) as PEND2ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario2 is not null
         ) as TOTAL2ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario1 is not null
               and status       = 21
         ) as PEND2ALIB,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario2 is null
               and status       = 3
         ) as PEND3ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario3 is not null
         ) as TOTAL3ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario2 is not null
               and status       = 31
         ) as PEND3ALIB,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario3 is null
               and status       = 4
         ) as PEND4ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario4 is not null
         ) as TOTAL4ACONT,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and usuario3 is not null
               and status       = 41
         ) as PEND4ALIB
  from
        vw_invbase a
/

