on 09-09-2016 1:17 AM
Saludos amigos,
hace rato no venía por estos lados, espero estén todos bien.
Estamos teniendo un problema con una query, que consulta facturas, pero las notas de crédito las toma como otra factura mas, y bueno pues, suma el monto, duplicándolo
Existe alguna columna de la tabla OINV que me permita saber si el documento es una Nota de Crédito? o alguna columna que tenga el valor en negativo?
Gracias.
Hola Jose,
Hay dos temas respecto a su consulta dependiendo de la estructura que requiere:
1. Creo que desde la version 9 en adelante se incluye dentro de los documentos de marketing la columna de cancelado donde toma tres valores C, N, Y. Las facturas Y o N son positivas, es decir suman y las C son las que restan (facturas canceladas).
2. Si lo que requiere es colocarle al query las notas de credito le recomiendo copiar todo su super query y solo cambiar que en lugar de que lea la OINV y la INV! lo pase a leer la ORIN y la RIN1. Unicamente toca cambiar los totales y pasarlos a que se multipliquen por -1 para que le reste y el dato le cuadre con los estados financieros.
Espero serle de ayuda.
Atte. Rosa
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
La query de Camilo es casi lo que necesito, restar las "Devoluciones" (Notas de Crédito) de las "Facturas de Venta", si tengo 10.000.000 en Facturas de Venta, pero tengo 2.500.000 en Devoluciones, que me dé el resultado 7.500.000.
Se entiende?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Me pierdo con estas consultas inmensas.
Lo normal para obtener la cifra global es un query de este estilo:
SELECT sum(T0.[DocTotal]) as 'Totales' FROM OINV T0 WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]
union all
SELECT sum(T0.[DocTotal])*-1 as 'Totales' FROM ORIN T0 WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]
A partir de ahí puedes ir bajando en el nivel o detalles.
Añadir los clientes
SELECT t0.cardcode, t0.cardname, sum(T0.[DocTotal]) as 'Totales' FROM OINV T0 WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] group by t0.cardcode, t0.cardname
union all
SELECT t0.cardcode, t0.cardname, sum(T0.[DocTotal])*-1 as 'Totales' FROM ORIN T0 WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] group by t0.cardcode, t0.cardname
La query completa es la siguiente... espero no muera de un infarto nadie
Select
a.TipoCod,
a.TipoDes,
Cast ( Round( Sum(a.Total),3) As Float ) As 'RealAcu',
Cast ( Round( Sum(a.Cantidad),3) As Float ) As 'Cantidad',
Cast ( Round( Sum(a.TotalCosto),3) As Float ) As 'CostoAcu',
Cast ( Round( Sum(a.Total) - Sum(a.TotalCosto),3) As Float ) As 'Margen', 0 As 'VentaInc',
Cast ( Case When Sum(a.Total) > 0 Then (Round( (Sum(a.Total) - Sum(a.TotalCosto))/Sum(a.Total),2)*100) Else 0 End As Float ) As 'MargenPor' , 'A' AS 'Ordena'
From ( Select a.DocDate, a.Cardcode , c.CardName, d.ItmsGrpCod, e.ItmsGrpNam,
Case When d.U_VK_Marca IS NULL
Then 'ZZZZZZ'
Else w.[Code]
End As 'MarCod',
Case When d.U_VK_Marca IS NULL
Then 'Sin Marca'
Else w.[Name]
End As 'MarDes',
CAST ( Case When d.U_Tipo_Articulo IS NULL
Then '999999'
Else d.U_Tipo_Articulo
End AS Varchar(10)) As 'TipoCod',
Case When d.U_Tipo_Articulo IS NULL
Then 'No Clasificado'
Else
Case When v.[Name] = x.[Name]
Then v.[Name]
Else v.[Name]+' - '+x.[Name]
End
End As 'TipoDes',
b.ItemCode,
d.ItemName,
IsNull(z.CarCod,99) As 'CarCod',
IsNull(z.CarDes,'Sin Cartera') As 'CarDes',
IsNull(b.Quantity,0) As 'Cantidad',
((b.LineTotal-(Case When a.DiscPrcnt > 0 Then Round(b.LineTotal*a.DiscPrcnt/100,0) Else 0 End)) - (Round(IsNull(b.Quantity,0) * IsNull(b.GrossBuyPr,0), 0)) ) As 'Margen',
Round(IsNull(b.Quantity,0) * IsNull(b.GrossBuyPr,0), 0) As 'TotalCosto',
b.LineTotal-(Case When a.DiscPrcnt > 0 Then Round(b.LineTotal*a.DiscPrcnt/100,0) Else 0 End) As 'Total'
FROM
OINV a INNER JOIN INV1 b On a.DocEntry = b.DocEntry
LEFT JOIN OCRD c On a.CardCode = c.CardCode
LEFT JOIN OITM d On b.ItemCode = d.ItemCode
LEFT JOIN OITB e On d.ItmsGrpCod = e.ItmsGrpCod
LEFT JOIN [@TIPO_ARTICULO] v On SubString(d.U_Tipo_Articulo,1,4) = v.[Code]
LEFT JOIN [@TIPO_ARTICULO] x On d.U_Tipo_Articulo = x.[Code]
LEFT JOIN [@MARCA] w On d.U_VK_Marca = w.[Code]
LEFT JOIN ( Select a.CardCode, b.ProDes As 'CarDes',
Min(a.CarCod) As 'CarCod'
From ( Select a.CardCode, Min(a.CarCod) As 'CarCod'
From ( Select CardCode, Case When QryGroup55 = 'Y'
Then 55
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup56 = 'Y'
Then 56
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup59 = 'Y'
Then 59
Else -1
End As 'CarCod'
From OCRD Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup60 = 'Y'
Then 60
Else -1
End As 'CarCod'
From OCRD Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup61 = 'Y'
Then 61
Else -1
End As 'CarCod'
From OCRD Where CardType = 'C' Union All
Select CardCode, Case When QryGroup62 = 'Y'
Then 62
Else -1
End As 'CarCod'
From OCRD Where CardType = 'C' Union All
Select CardCode, Case When QryGroup63 = 'Y'
Then 63
Else -1
End As 'CarCod'
From OCRD Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup64 = 'Y'
Then 64
Else -1 End As 'CarCod'
From OCRD Where CardType = 'C' ) a
Where a.CarCod > 0
Group by a.CardCode ) a
Left Join ( Select a.GroupCode As 'ProCod',
a.GroupName As 'ProDes' From OCQG a )
b On a.CarCod = b.ProCod
Where a.CarCod > 0
Group by a.CardCode, b.ProDes )
z On a.CardCode = z.CardCode
Where 0 = 0 And a.DocDate between Convert(DateTime,'01-08-2016',103)
And Convert(DateTime,'31-08-2016',103)
And e.ItmsGrpCod in (101,102,103,104,108,121,122,123)
And a.CardCode = 'CE1007027'
And IsNull(z.CarCod,99) = '55'
And d.ItmsGrpCod = '104' Union All
Select
a.DocDate,
a.Cardcode,
c.CardName,
d.ItmsGrpCod,
e.ItmsGrpNam,
Case When d.U_VK_Marca IS NULL
Then 'ZZZZZZ'
Else w.[Code]
End As 'MarCod',
Case When d.U_VK_Marca IS NULL
Then 'Sin Marca'
Else w.[Name]
End As 'MarDes',
CAST ( Case When d.U_Tipo_Articulo IS NULL
Then '999999'
Else d.U_Tipo_Articulo
End AS Varchar(10)) As 'TipoCod',
Case When d.U_Tipo_Articulo IS NULL
Then 'No Clasificado'
Else
Case When v.[Name] = x.[Name]
Then v.[Name]
Else v.[Name]+' - '+x.[Name]
End
End As 'TipoDes',
b.ItemCode,
d.ItemName,
IsNull(z.CarCod,99),
IsNull(z.CarDes,'Sin Cartera'),
(IsNull(b.Quantity,0) * -1) As 'Cantidad',
(((b.LineTotal-(Case When a.DiscPrcnt > 0
Then Round(b.LineTotal*a.DiscPrcnt/100,0)
Else 0
End)) - (Round(IsNull(b.Quantity,0) * IsNull(b.GrossBuyPr,0), 0)) ) * -1 ) As 'Margen',
(Round(IsNull(b.Quantity,0) * IsNull(b.GrossBuyPr,0), 0) * -1) As 'TotalCosto',
(b.LineTotal-(Case When a.DiscPrcnt > 0
Then Round(b.LineTotal*a.DiscPrcnt/100,0)
Else 0
End))*-1 As 'Total'
From ORIN a
INNER JOIN RIN1 b On a.DocEntry = b.DocEntry
LEFT JOIN OCRD c On a.CardCode = c.CardCode
LEFT JOIN OITM d On b.ItemCode = d.ItemCode
LEFT JOIN OITB e On d.ItmsGrpCod = e.ItmsGrpCod
LEFT JOIN [@TIPO_ARTICULO] v On SubString(d.U_Tipo_Articulo,1,4) = v.[Code]
LEFT JOIN [@TIPO_ARTICULO] x On d.U_Tipo_Articulo = x.[Code]
LEFT JOIN [@MARCA] w On d.U_VK_Marca = w.[Code]
LEFT JOIN ( Select a.CardCode, b.ProDes As 'CarDes',
Min(a.CarCod) As 'CarCod'
From (Select a.CardCode, Min(a.CarCod) As 'CarCod'
From (Select CardCode, Case When QryGroup55 = 'Y'
Then 55
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup56 = 'Y'
Then 56
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup59 = 'Y'
Then 59
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup60 = 'Y'
Then 60
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup61 = 'Y'
Then 61
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup62 = 'Y'
Then 62
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup63 = 'Y'
Then 63
Else -1
End As 'CarCod'
From OCRD
Where CardType = 'C' Union All
Select CardCode,
Case When QryGroup64 = 'Y'
Then 64
Else -1
End As 'CarCod'
From OCRD Where CardType = 'C' ) a
Where a.CarCod > 0
Group by a.CardCode) a
Left Join (Select a.GroupCode As 'ProCod',
a.GroupName As 'ProDes'
From OCQG a)
b On a.CarCod = b.ProCod
Where a.CarCod > 0
Group by a.CardCode, b.ProDes) z On a.CardCode = z.CardCode Where 0 = 0
And b.BaseType <> 203
And a.DocDate between Convert(DateTime,'01-08-2016',103)
And Convert(DateTime,'31-08-2016',103)
And e.ItmsGrpCod in (101,102,103,104,108,121,122,123)
And a.CardCode = 'CE1007027'
And IsNull(z.CarCod,99) = '55'
And d.ItmsGrpCod = '104' ) a
Group by a.TipoCod, a.TipoDes
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola compañero.
Lo interesante sería que mostraras tu Query y así podamos aconsejarte qué puedes hacer para solucionar tu detalle...
Si lo que requieres es que, por ejemplo, RIN1.LineTotal sea negativo, puedes simplemente multiplicarlo por "-1" (RIN1.LineTotal * -1), de esta manera tendrás un importe negativo.
Sobre tu pregunta: No existe un campo en la tabla OINV que te diga si es una nota de crédito ya que, como te ha comentado Gonzalo, las notas de crédito se almacenan en una tabla diferente (ORIN). Lo que puedes hacer es vincular las tablas INV1 - RIN1, o bien trabajar con un "Union All", tal y como lo muestra el comentario de Camilo.
No olvides agradecer la aportaciones que amablemente te han realizado los compañeros, esto por medio de marcar sus respuestas como respuestas de ayuda o respuesta correcta.
Saludos.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
OINV son las facturas de venta.
ORIN son los abonos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.