cancel
Showing results for 
Search instead for 
Did you mean: 

Transaction listing for sales

Former Member
0 Kudos

Dear All.

I am trying to obtain a transaction listing that has the cost price and selling as well as Gp. If I use the INV1_BasePrice all I get in my query is the letter (E). Any indication of how I perform this. My query is as below

Select

T0.[DocDate], T0.[CardName], T0.[DocNum], T0.[SlpCode], T2.[SlpName], T1.[ItemCode], T1.[Dscription], T1.[Dscription], T1.[Quantity], T1.[Price]

From

[dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

Where

T0.[DocDate] > [%0] and T0.[DocDate] <[%1]

Groupe by

T0.[DocDate], T0.[DocDueDate],T0.[DocNum], T0.[SlpCode],T2.[SlpName], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price]

Many thanks Martin

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Dear Martin,

Please try this one:


Select T0.DocDate, T0.CardName, T0.DocNum, T0.SlpCode, T2.SlpName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.GrssProfit
From dbo.OINV T0 
INNER JOIN dbo.INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN dbo.OSLP T2 ON T0.SlpCode = T2.SlpCode
Where T0.DocDate > '[%0]' and T0.DocDate < '[%1]'
Group by
T0.DocDate, T0.DocDueDate,T0.DocNum, T0.SlpCode,T2.SlpName, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price, T1.GrssProfit

Thanks,

Gordon

0 Kudos

Dear Martin,

I will give you an example of how to get the gross profit, however please take in consideration that you have to check what settings you have in your Business One.

SELECT T1.Price,T2.WhsCode, T2.AvgPrice,

(((t1.price - t2.AvgPrice)/t1.price)*100) FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry, OITW T2 WHERE T0.DocNum = '115' and T1.ItemCode = 'a1004' and t2.WhsCode = '01'

Please note that in order to find the gross profit I have assumed the following:

in the document settings, general tab the base price origin for the gross profit is Item cost and the Calculate % is Profit/Sales price.

If these settings are different, then also the way the gross profit is calculated must change.

For information about the Gross profit I would suggest you to read the following 2 EES:

https://psd.sap-ag.de/PEC/calendar/

and write gross profit in the search field. Also, please choose dates from 2005 to 2009.

Kind Regards,

Marcella Rivi

SAP Business One Forums Team