Query for purchase vs sale

[Former Member]

Hello,

I need a query report that can tell me about AP doc number, ap item no., item description, ap Quantity, ap Assessable Value, AR doc number, ar Quantity.

The AP invoice number is also selected by the user at row level against each item on invoice? Can we make such report or two reports are required?

Or in simple I need to know how much item was purchased on ap invoice number 1 and how much was sold from ap invoice number 1?

BR,

RR

[malcolm_lamour]

Hi

I'm not sure if you have found a solution yet. but this is what i needed for a similar query.

SELECT T0.CardCode, T0.CardName,T3.ItmsGrpNam, T1.ItemCode, T1.Dscription, SUM(Invoices) as Invoices, SUM(IQty) as IQty,

sum(T1.LineTotal) AS 'Purchases', sum(T1.Quantity) AS 'PQty'

FROM OPCH T0

INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode

INNER JOIN OITB T3 ON T3.ItmsGrpCod = T2.ItmsGrpCod

LEFT JOIN OCRD T4 ON T4.CardCode=T2.CardCode

LEFT OUTER JOIN (SELECT T1.ItemCode,

sum(T1.LineTotal) AS 'Invoices', sum(T1.Quantity) AS 'IQty'

FROM OINV T0

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

INNER JOIN OITM T2 ON T2.ItemCode=T1.ItemCode

INNER JOIN OITB T3 ON T3.ItmsGrpCod = T2.ItmsGrpCod

LEFT JOIN OCRD T4 ON T4.CardCode=T2.CardCode

WHERE T1.[TargetType]!= 14

/* and  T0.[DocDate] >= @Startdate and  T0.[DocDate] <= @Enddate */

Group by T1.ItemCode

) i on i.ItemCode=T1.ItemCode

WHERE T1.[TargetType]!= 14 

/* and  T0.[DocDate] >= @Startdate and  T0.[DocDate] <= @Enddate */

Group by T0.CardCode, T0.CardName, T3.ItmsGrpNam, T1.ItemCode, T1.Dscription

regards

Malcolm