drop view vw_invbase;
CREATE VIEW vw_invbase (
  documento,
  status,
  empresa,
  filial,
  departamento,
  deposito,
  setor,
  corredor,
  endereco,
  produto,
  usuario1,
  usuario2,
  usuario3,
  usuario4,
  saldo,
  cont1a,
  cont2a,
  cont3a,
  cont4a
) AS
select
        DOCCAB_SEQ         as documento,
        INVITM_STATUS      as status,
        EMPRES_CODIGO      as empresa,
        FILIAL_CODIGO      as filial,
        DEPART_CODIGO      as departamento,
        DEPOSI_CODIGO      as deposito,
        SETOR_CODIGO       as setor,
        CORRED_CODIGO      as corredor,
        ENDERE_CODFANTASIA as endereco,
        PRDCAD_CODIGO      as produto,
        INVITM_USU1ACONT   as usuario1,
        INVITM_USU2ACONT   as usuario2,
        INVITM_USU3ACONT   as usuario3,
        INVITM_USU4ACONT   as usuario4,
        INVITM_SALDOWMS    as saldo,
        INVITM_QDE1ACONT   as cont1a,
        INVITM_QDE2ACONT   as cont2a,
        INVITM_QDE3ACONT   as cont3a,
        INVITM_QDE4ACONT   as cont4a
  from invitm
 where INVITM_STATUS != 9;

drop view vw_invres;
CREATE 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 usuario2 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 usuario3 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 usuario4 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;
		
drop view vw_invope;
CREATE  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;
		
