--  View to know the salesdoc detail
drop view tek_showsalesdetails;

create view tek_showsalesdetails as
select
    c.Data,null as idcabliq, c.Id as idcabecdoc, c.tipodoc, c.numdoc, c.serie, c.TotalMerc + c.TotalIva + c.TotalOutros as valorrec ,null as valorpagocredito, c.TotalMerc + c.TotalIva + c.TotalOutros as valorpagodebito,
    l.NumLinha as numlinha,
    (case when sv.IvaIncluido=1 then l.PrecoLiquido else l.PrecoLiquido*(1+ l.TaxaIva/100) end) as valorpago,
    dv.PagarReceber, l.Artigo, a.Descricao, l.Armazem,l.CDU_Espaco, c.Data as DataRec,c.DataVencimento
from
    cabecdoc c inner join
    linhasdoc l on (l.IdCabecDoc = c.id) inner join
    artigo a on (a.artigo = l.artigo) inner join
    DocumentosVenda dv on (c.TipoDoc = dv.Documento) inner join
    SeriesVendas sv on (sv.Serie = c.serie and sv.TipoDoc=c.tipodoc)
where dv.PagarReceber = 'R'
group by
    c.data,c.id, c.tipodoc, c.numdoc, c.serie, c.TotalMerc, c.totaliva, dv.PagarReceber, l.NumLinha,l.PrecoLiquido, l.TaxaIva ,l.Artigo,l.Armazem,l.CDU_Espaco,a.Descricao, c.DataVencimento,sv.ivaincluido
UNION ALL
select
    c.Data,null as idcabliq, c.Id as idcabecdoc, c.tipodoc, c.numdoc, c.serie, abs( c.TotalMerc + c.TotalIva + c.TotalOutros) as valorrec ,abs(c.TotalMerc + c.TotalIva + c.TotalOutros) as valorpagocredito, null as valorpagodebito,
    l.NumLinha as numlinha, 
    (case when sv.IvaIncluido=1 then abs(l.PrecoLiquido) else abs(l.PrecoLiquido*(1+ l.TaxaIva/100)) end) as valorpago,
    dv.PagarReceber, l.Artigo, a.Descricao, l.Armazem,l.CDU_Espaco, c.Data as DataRec,c.DataVencimento
from
    cabecdoc c inner join
    linhasdoc l on (l.IdCabecDoc = c.id) inner join
    artigo a on (a.artigo = l.artigo) inner join
    DocumentosVenda dv on (c.TipoDoc = dv.Documento) inner join
    SeriesVendas sv on (sv.Serie = c.serie and sv.TipoDoc=c.tipodoc)
where dv.PagarReceber = 'P'
group by
    c.data,c.id, c.tipodoc, c.numdoc, c.serie, c.TotalMerc, c.totaliva, dv.PagarReceber, l.NumLinha,l.PrecoLiquido, l.TaxaIva ,l.Artigo,l.Armazem,l.CDU_Espaco,a.Descricao, c.DataVencimento,sv.ivaincluido
;