PROMPT CREATE OR REPLACE VIEW vw_invend
CREATE OR REPLACE VIEW vw_invend (
  documento,
  empresa,
  filial,
  departamento,
  deposito,
  setor,
  corredor,
  totalitensxend,
  totalencerrados,
  total1acont,
  total2acont,
  total3acont,
  total4acont
) AS
select distinct
        documento,
        empresa,
        filial,
        departamento,
        deposito,
        setor,
        corredor,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and departamento = a.departamento
               and deposito     = a.deposito
               and setor        = a.setor
               and corredor     = a.corredor
         ) as TOTALITENSXEND,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and departamento = a.departamento
               and deposito     = a.deposito
               and setor        = a.setor
               and corredor     = a.corredor
               and status = '8'
         ) as TOTALENCERRADOS,
        (
            select count(*)
              from vw_invbase
             where documento    = a.documento
               and empresa      = a.empresa
               and filial       = a.filial
               and departamento = a.departamento
               and deposito     = a.deposito
               and setor        = a.setor
               and corredor     = a.corredor
               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 departamento = a.departamento
               and deposito     = a.deposito
               and setor        = a.setor
               and corredor     = a.corredor
               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 departamento = a.departamento
               and deposito     = a.deposito
               and setor        = a.setor
               and corredor     = a.corredor
               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 departamento = a.departamento
               and deposito     = a.deposito
               and setor        = a.setor
               and corredor     = a.corredor
               and usuario4 is not null
         ) as TOTAL4ACONT
  from
        vw_invbase a
/

