Add "Base price" field from "Gross Profit of Outgoing Invoice" window to query?

[jeroenw]

Hi all,

We want a list with all invoiced articles of a certain period. This data can be retrieved from SAP with the query below.

However, this query is missing 1 field and I don't know how to add it.

The missing field is "Base Price" from the "Gross Profit of Outgoing Invoice" window (screenshot below). How can I add this field?

SELECT 
T0.[DocNum], 
T0.[DocDate], 
T0.[CardCode],
T0.[CardName], 
T1.[ItemCode], 
T1.[Dscription],
T3.[ItmsGrpNam],  
T1.[Quantity], 
T1.[WhsCode], 
T1.[PriceBefDi], 
T1.[DiscPrcnt], 
T1.[LineTotal], 
T1.[TreeType], 
T4.[CardName] 

FROM OINV T0  
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod 
INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode 


WHERE 
T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] AND 
T1.[TreeType] <> 'I' 

ORDER BY 
T0.[DocNum]

[LoHa · Accepted solution]

Hi Jeroen,

try T1.GrossBuyPr

regards Lothar

[jeroenw · Accepted solution]

Hi Lothar,

Thanks for your reply. I tried but that field is empty.

[Johan_H · Accepted solution]

Hi jeroenw,

Would you please double check, because that is the field the Base Price is stored in. If you see a price on screen, it should be in that field.

By the way, what does this do:

AND 
T1.[TreeType] <> 'I'

Regards,

Johan