on 02-17-2009 8:14 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
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.