Sales by Item in Sap Bo

[Former Member]

Hello Everyone

I need to create a Query to get the Sales by Item, im using OINV, INV1, OITM, i have invoices canceled, so is necessary to use ORIN, RIN1

Im using a UDF as a factor to get pieces to kilograms, thats why i need sales in quantity or pieces.

Any suggestions? 

Thanks for your help !!

[Former Member]

Hi Victor,

Good Day!

Maybe the query below will give you a hint:

SELECT

t0.DocDate as 'Posting Date' ,

t0.DocNum as 'A/R Invoice',

t1.ItemCode ,

t2.ItemName ,

t3.DocNum as 'Credit Note'

FROM OINV t0

join INV1 t1 ON t0.DocEntry=t1.DocEntry

join OITM t2 ON t1.ItemCode =t2.ItemCode

left join ORIN t3 ON t1.TrgetEntry = t3.DocNum

Thanks,

Paul C

[Former Member]

Thanks for your help, Paul

We have Credit Notes not related to Invoices, so i had to use DocDate, CardNum, ItemCode as key

The Final query i  got is:

SELECT       DocDate, CardCode, CardName, ItemCode, Dscription,                              SUM(Quantity) AS Quantity, U_KGSXPZA, Quantity * U_KGSXPZA AS Expr1

FROM            (SELECT        T0.DocDate, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T2.U_KGSXPZA

                          FROM            dbo.OINV AS T0 INNER JOIN

                                                    dbo.INV1 AS T1 ON T1.DocEntry = T0.DocEntry INNER JOIN

                                                    dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode

                          WHERE      (T0.DocType = 'I')

                          UNION ALL

                          SELECT        T0.DocDate, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, - T1.Quantity AS 'Quantity', T2.U_KGSXPZA

                          FROM            dbo.ORIN AS T0 INNER JOIN

                                                   dbo.RIN1 AS T1 ON T1.DocEntry = T0.DocEntry INNER JOIN

                                                   dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode

                          WHERE        (T0.DocType = 'I')) AS S

GROUP BY DocDate, CardCode, CardName, Dscription, ItemCode, U_KGSXPZA, Quantity * U_KGSXPZA

ORDER BY 'Quantity' DESC

Regards!!

[Former Member]

Hi Victor,

You don't have to use a UDF to get pieces or KGs. There are fields for that in SAP B1. It's usually not ideal to add UDFs unless really necessary because as you can imagine it's adding more data for the system to process.

In this case instead of using T1.Quantity you could just use T1.Weight1 (provided your items are set up with weights of course).

Just a thought. Maybe you have your reasons

Cheers.

[kothandaraman_nagarajan]

Hi,

Yes, it is necessary to use ORIN, RIN1.

Thanks & Regards,

Nagarajan