on 01-28-2016 10:24 PM
Buenas tardes estimado todos.
Estoy tratando de generar un query para un reporte de ventas por vendedor por grupo de articulo el cual nativamente SAP BUSINESS ONE únicamente por vendedor.
mi query esta obviando las facturas canceladas, las facturas creadas por cancelacion ( facturas de reversión) , notas de crédito canceladas , notas de crédito creadas por cancelación (notas de crédito de reversión)
--------------QUERY---
SELECT A.clave_articulo,A.DESCR, A.LIN_PROD,A.CLAVE_CLPV, A.PRECIO,A.MES, A.ANO,A.CLAVE_CLNOM[NOMBRE],a.VEND,SUM(A.CAJA)[CAJA], SUM(A.TOTAL)[TOTAL], A.CATEGORIA
FROM(
select t1.ItemCode[clave_articulo],t1.Dscription[DESCR],t5.FirmName[LIN_PROD],t0.CardCode[CLAVE_CLPV], t1.PriceBefDi[PRECIO], DATEPART(mm,t0.docdate)[MES],DATEPART(yyyy,t0.docdate)[ANO], t0.CardName[CLAVE_CLNOM] , t4.SlpName[VEND], t1.Quantity[CAJA], t1.LineTotal[TOTAL],t3.ItmsGrpNam[CATEGORIA]
from [productiva].[dbo].[oinv] t0 inner join [productiva].[dbo].inv1 t1 on t0.DocEntry= t1.DocEntry
inner join [productiva].[dbo].oitm t2 on t1.ItemCode = t2.ItemCode
INNER JOIN [productiva].[dbo].OITB T3 ON T3.[ItmsGrpCod] = T2.[ItmsGrpCod]
inner join [productiva].[dbo].oslp t4 on t4.SlpCode = t0.SlpCode
INNER JOIN [productiva].[dbo].OMRC T5 ON T5.[FirmCode] = T2.[FirmCode]
where t0.CANCELED = 'n' and t0.DocNum not in (SELECT distinct T0.[DocNum]
FROM [productiva].[dbo].OINV T0 INNER JOIN [productiva].[dbo].INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseRef] in (SELECT a.docnum
FROM [productiva].[dbo].OINV a
WHERE a.[CANCELED] = 'y'))
UNION
select t1.ItemCode[clave_articulo],t1.Dscription,t5.FirmName,t0.CardCode, t1.PriceBefDi, DATEPART(mm,t0.docdate),DATEPART(yyyy,t0.docdate), t0.CardName , t4.SlpName, t1.Quantity*-1, t1.LineTotal*-1,t3.ItmsGrpNam
from [productiva].[dbo].oRIN t0 inner join [productiva].[dbo].RIN1 t1 on t0.DocEntry= t1.DocEntry
inner join [productiva].[dbo].oitm t2 on t1.ItemCode = t2.ItemCode
INNER JOIN [productiva].[dbo].OITB T3 ON T3.[ItmsGrpCod] = T2.[ItmsGrpCod]
inner join [productiva].[dbo].oslp t4 on t4.SlpCode = t0.SlpCode
INNER JOIN [productiva].[dbo].OMRC T5 ON T5.[FirmCode] = T2.[FirmCode]
where t0.CANCELED = 'n' and t0.DocNum not in (SELECT distinct T0.[DocNum]
FROM [productiva].[dbo].ORIN T0 INNER JOIN [productiva].[dbo].RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseRef] in (SELECT a.docnum
FROM [productiva].[dbo].ORIN a
WHERE a.[CANCELED] = 'y'))
) AS A
GROUP BY A.clave_articulo,A.DESCR, A.LIN_PROD,A.CLAVE_CLPV, A.PRECIO,A.MES, A.ANO,A.CLAVE_CLNOM,a.VEND, A.CATEGORIA
-----------FIN QUERY -------
Lastimosamente cuando hago una comparacion de montos con los resultados nativos del reporte ANÁLISIS DE VENTAS de SAP no cuadran.
Por favor si pueden darme una mano si el query esta mal o como debo sacar esta información teniendo en cuenta que tengo facturas y notas de crédito canceladas.
Gracias de antemano.
Hola Marco quizás ya lo resolviste, en caso de que no sea así espero que esto te sirva.
Usa UNION ALL.
SELECT
K.GroupName TipoCliente, K.SlpName Vendedor, SUM(K.LineTotal)Importe
FROM(
SELECT O.DocDate, O.CardCode, O.CardName, TC.GroupName, GA.ItmsGrpNam, OL.ItemCode, OL.Dscription, OL.unitMsr, OL.Quantity, OL.Price, OL.LineTotal, V.SlpName
FROM OINV O , INV1 OL,OITM A, OITB GA, OSLP V, OCRD T, OCRG TC
WHERE O.DocEntry=OL.DocEntry
AND OL.ItemCode=A.ItemCode
AND GA.ItmsGrpCod=A.ItmsGrpCod
AND V.SlpCode = OL.SlpCode
AND O.CardCode = T.CardCode
AND T.GroupCode = TC.GroupCode
AND GA.ItmsGrpCod = '139'
AND O.CANCELED = 'N'
AND O.DocDate BETWEEN '20160801' AND '20160831'
UNION ALL
SELECT O1.DocDate, O1.CardCode, O1.CardName, TC1.GroupName, GA1.ItmsGrpNam, OL1.ItemCode, OL1.Dscription, OL1.unitMsr, OL1.Quantity*-1 AS Quantity, OL1.Price*-1 AS Price, OL1.LineTotal*-1 AS LineTotal, V1.SlpName
FROM ORIN O1,RIN1 OL1, OITM A1, OITB GA1, OSLP V1, OCRD T1, OCRG TC1
WHERE O1.DocEntry=OL1.DocEntry
AND OL1.ItemCode=A1.ItemCode
AND GA1.ItmsGrpCod=A1.ItmsGrpCod
AND V1.SlpCode = OL1.SlpCode
AND O1.CardCode = T1.CardCode
AND T1.GroupCode = TC1.GroupCode
AND GA1.ItmsGrpCod = '139'
AND O1.CANCELED = 'N'
AND O1.DocDate BETWEEN '20160801' AND '20160831'
) AS K
GROUP BY K.GroupName, K.SlpName
ORDER BY 1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.