Skip to Content

Reporte de ventas por vendedor por grupo de articulo

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Aug 22, 2016 at 04:39 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.