Skip to Content
0
Former Member
Apr 23, 2010 at 04:57 AM

Query

14 Views

Hi All,

I am trying to get a report generated through query which should give the following details;

Document number, Customer name, item, quantity, price, item cost, cogs value, sale value

the purpose of this query is to get the figures which would match with the GL and Trial balance. Also would it be possible to include the JE's (manual) in the same report.

The reports that I have created is showing differences and also have created doifferent reports to get the desired result. please assist if these queries could be combined.

COGS VALUE:

SELECT T0.[DocNum], T0.[DocDate],T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[StockPrice], T1.[StockPrice]*T1.[Quantity] as 'COGS ENTRY', T1.[Linetotal] as 'Sale VAlue' FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <= [%1]

this query does not consider the discounts and returns or credit memo's or the JE's. How can the desired data be achieved in one report? Please assist.

Thanks,

Joseph